Excelで在庫管理表を作る場合、「初期在庫」「注文データ」「残り在庫」をシート分けすると管理しやすくなります。特にSUMIF関数を使うと、注文データを追加するだけで残り在庫を自動計算できるため、初心者でも実用的な在庫管理表を作れます。この記事では、シンプルで壊れにくい在庫管理表の作り方を、実例付きでわかりやすく解説します。
まずは3つのシートを作る
基本構成は以下の3シートがおすすめです。
| シート名 | 役割 |
|---|---|
| シート1 | 初期在庫一覧 |
| シート2 | 注文・出庫データ入力 |
| シート3 | 残り在庫表示 |
この構成にすると、入力作業と計算結果を分けられるので管理しやすくなります。
シート1「初期在庫」を作る
まずは在庫の元データを作ります。
例
| A列 | B列 |
|---|---|
| 商品名 | 初期在庫 |
| りんご | 20 |
| ぶどう | 30 |
| 梨 | 40 |
ここは基本的に最初だけ入力します。
シート2「注文データ」を作る
ここには注文や出庫があるたびに追加していきます。
例
| A列 | B列 |
|---|---|
| 商品名 | 出庫数 |
| りんご | 1 |
| りんご | 3 |
| ぶどう | 20 |
| ぶどう | 10 |
| 梨 | 12 |
入力は追加形式にすると、履歴管理もしやすくなります。
存在しない商品名を入力すると集計できないため、商品名は統一するのが重要です。
シート3で残り在庫を自動計算する
ここでSUMIF関数を使います。
シート3の例
| A列 | B列 |
|---|---|
| りんご | 残り在庫 |
B2セルに以下の式を入れます。
=Sheet1!B2-SUMIF(Sheet2!A:A,A2,Sheet2!B:B)
意味は以下の通りです。
- Sheet1!B2 → 初期在庫
- SUMIF → 注文数の合計
- 差し引き → 残り在庫
これを下にコピーすると、他の商品も自動計算できます。
実際の計算結果イメージ
| 商品 | 初期在庫 | 注文合計 | 残り |
|---|---|---|---|
| りんご | 20 | 4 | 16 |
| ぶどう | 30 | 30 | 0 |
| 梨 | 40 | 12 | 28 |
シート2に追加入力するだけで、自動更新されます。
入力ミスを減らす方法
在庫管理では商品名の入力ゆれが非常に多いです。
例えば以下のような違いです。
- りんご
- リンゴ
- りんご （空白あり）
これを防ぐには「データ入力規則」のプルダウンを使うのがおすすめです。
おすすめ設定
- 商品一覧を別表に作る
- データタブ → データの入力規則
- リスト指定
これで商品名を選択式にできます。
在庫が0以下なら色を変える方法
条件付き書式を使うと、在庫不足がすぐ分かります。
設定例
- 残り在庫セルを選択
- 条件付き書式
- 「セルの値が0以下」
- 赤色表示
実務ではかなり便利な機能です。
慣れてきたら追加できる機能
基本構成ができたら、さらに便利にできます。
- 入庫管理
- 日付管理
- 担当者記録
- 自動グラフ化
- 在庫不足アラート
まずはシンプル構成から始めるのがおすすめです。
まとめ
Excelで在庫管理表を作る場合は、「初期在庫」「注文入力」「残り在庫」の3シート構成がわかりやすく管理しやすいです。
特にSUMIF関数を使えば、注文データを追加するだけで残り在庫を自動計算できます。
最初はシンプルに作り、慣れてから入力規則や条件付き書式を追加していくと、実用的な在庫管理表に育てやすくなります。
コメント