Excelで会計・販売・仕入・在庫管理を行うシステムを構築するための手順
小規模なシステムであればExcelを活用することで十分対応が可能です。
以下に、システムの構築手順、使用する関数の説明、サンプルといった要素を含めて解説します。
1. 目的と要件の明確化
システムの目的と要件を明確にします。
今回は、会計・販売・仕入・在庫管理が目的です。各システムの機能として、以下のようなものを考えます。
・会計
売上・仕入・経費を把握し、収支を管理する。
・販売
商品ごとの売上や取引先別の売上を確認する。
・仕入
仕入先別、商品の仕入れ状況を管理する。
・在庫
現在の在庫数量とその価値を把握する。
この要件に基づき、必要なデータ項目やシートを準備します。
Excelで“時短”システム構築術――案件管理の効率化を簡単に実現しよう！ ↓
2. 基本データシートの作成
各システムが必要とする基本データを管理するために、以下のようなシートを作成します。
❶商品マスター
商品名、単価、在庫単位などを管理。
❷取引先マスター
取引先の名前、住所、電話番号などを管理。
❸売上データ
販売日、商品、数量、単価、取引先などの売上情報を入力。
❹仕入データ
仕入日、商品、数量、単価、仕入先などの仕入情報を入力。
❺在庫管理データ
各商品の在庫数量や在庫価値を把握するためのデータ。
3. 使用する関数の説明とサンプル
Excelでの会計・販売・仕入・在庫システムでは、SUMIF、VLOOKUP、IF、COUNTIF、SUMPRODUCT などの関数を使います。
ここで各関数について説明し、そのサンプルを紹介します。
3.1 SUMIF関数
用途
指定された範囲に基づいて合計を求める。
構文
=SUMIF(範囲, 条件, 合計範囲)
例
売上データシートにおいて、ある商品Aの売上合計を求めたい場合
=SUMIF(B2:B100, "商品A", D2:D100)
ここで、B2:B100は商品名が入った範囲、"商品A"は条件、D2:D100は数量が入った範囲です。
Excelで“時短”システム構築術――案件管理の効率化を簡単に実現しよう！ ↓
3.2 VLOOKUP関数
用途
表の中から指定した値を検索し、その値に対応する情報を取得。
構文
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
例
売上データシートにおいて、商品コードから単価を自動取得する場合
=VLOOKUP(A2, 商品マスター!A2:C100, 3, FALSE)
ここで、A2は検索値（商品コード）、商品マスター!A2:C100は検索範囲、3は単価の列番号です。
3.3 IF関数
用途
条件に応じて異なる値を返す。
構文
=IF(条件, 真の場合の値, 偽の場合の値)
例
在庫数量が0以下である場合、「在庫切れ」と表示する
=IF(D2<=0, "在庫切れ", "在庫あり")
3.4 COUNTIF関数
用途
範囲内で条件を満たすセルの個数をカウント。
構文
=COUNTIF(範囲, 条件)
例
売上データにおいて、特定の取引先への売上数をカウントする場合
=COUNTIF(C2:C100, "取引先A")
3.5 SUMPRODUCT関数
用途
複数の範囲の積を合計することで条件付き合計を求める。
構文
=SUMPRODUCT((範囲1=条件1)*(範囲2=条件2)*合計範囲)
例
特定の商品と特定の取引先の売上合計を求める場合
=SUMPRODUCT((B2:B100="商品A")*(C2:C100="取引先A")*D2:D100)
Excelで“時短”システム構築術――案件管理の効率化を簡単に実現しよう！ ↓
4. 各システムの計算・集計シートの作成
4.1 会計シートの作成
目的
売上、仕入、経費のデータをもとに、収支を集計する。
❶売上合計、仕入合計、経費合計をそれぞれSUMIF関数で計算します。
❷売上 - （仕入 + 経費）として純利益を求めます。
4.2 販売シートの作成
目的
商品ごと、取引先ごとの売上集計を行う。
❶商品別売上：商品名をキーとしてSUMIF関数で売上数量と金額を集計。
❷取引先別売上：取引先をキーとしてSUMIF関数で売上金額を集計。
❸グラフを用意して、売上推移を視覚的に表示。
4.3 仕入シートの作成
目的
仕入先ごと、商品ごとの仕入状況を把握する。
❶商品別仕入
SUMIF関数で商品ごとの仕入数量と金額を計算。
❷仕入先別仕入
仕入先をキーとしてSUMIF関数で仕入金額を集計。
❸在庫状況を把握するため、仕入数量を在庫数量に加算する。
4.4 在庫シートの作成
目的
現在の在庫数量および在庫価値を把握する。
❶初期在庫に対して、売上データから販売数量を減算し、仕入データから仕入数量を加算することで現在の在庫数量を求める。
❷商品ごとの在庫価値は、在庫数量に単価を掛けることで計算。
5. 入力時の注意点
データの入力ミスを防ぐために、データ検証機能を使って、入力可能な値の制限をかけます。
たとえば、「数量」は0以上の整数のみを許可する、「取引先」はリストから選択するように設定するといった工夫が効果的です。
6. ダッシュボードの作成
最後に、各システムの情報を一つのシートに集約し、わかりやすく表示するダッシュボードを作成します。
❶会計状況の可視化
利益や売上の推移を棒グラフや折れ線グラフで表示します。
❷販売実績の可視化
商品別、取引先別の売上を円グラフで視覚化します。
❸日在庫状況の可視化
在庫数量や在庫価値を棒グラフで表示し、在庫が少ない商品に対して警告表示を行います。
まとめ
Excelを使った会計・販売・仕入・在庫システムの構築方法について、基本的な手順と使用する関数を説明しました。
これにより、小規模な業務における効率的な管理が可能となります。