アオのAI×Excel仕事術

Excelで在庫管理表を作る方法｜入出庫を記録するだけで現在庫と発注タイミングが分かる（無料テンプレート付き）

Excel×AIの業務効率化｜公開 2026年10月23日

在庫の数を、1つの表に直接「残り何個」と書き換えて管理していないでしょうか。この方法だと、いつ・何個出ていったのかが残らず、数が合わないときに原因を追えません。

この記事では、入ってきた数と出ていった数を記録するだけで、現在の在庫と発注のタイミングが自動で分かる在庫管理表の作り方を説明します。使う関数は SUMIFS、INDEX、MATCH、IF の4つだけです。

作るのが面倒な人のために、この記事と同じ作りのテンプレートを用意しました。

在庫管理表テンプレートをダウンロード（Excel形式）

テンプレートは筆者が作成したもので、無料で自由に使えます。サンプルの商品と記録は記入例です。

完成形：3つのシートに分ける

在庫管理表は、次の3つのシートに分けるのがポイントです。

シート役割入力する人
商品マスタ商品コード・商品名・単位・期首在庫・発注点の一覧商品が増えたときだけ
入出庫記録「いつ・何を・入庫か出庫か・何個」を1行ずつ記録毎日
在庫一覧現在庫と「要発注」を自動で表示入力しない（見るだけ）

「記録」と「集計」を分けておくと、在庫一覧を誰かがうっかり書き換えて数が狂う、ということがなくなります。数が合わないときも、入出庫記録を見ればどこで食い違ったかを追えます。

手順1：商品マスタを作る

1枚目のシートの名前を「商品マスタ」にして、1行目に見出しを入れます。

ABCDE
1商品コード商品名単位期首在庫発注点
2A001コピー用紙 A4箱2010
3A002ボールペン 黒本5020
  • 商品コード：商品ごとに重ならない記号を付けます。商品名は表記ゆれ（「A4用紙」「コピー用紙A4」など）が起きやすいので、集計にはコードを使います。
  • 期首在庫：記録を始める時点の在庫数です。最初に一度、棚卸しをして実際の数を入れます。
  • 発注点：この数以下になったら発注する、という目安です。「発注してから届くまでに使う量＋少しの余裕」で決めます。

手順2：入出庫記録を作る

2枚目のシートの名前を「入出庫記録」にします。

ABCDEF
1日付商品コード商品名（自動）区分数量備考
22026/10/1A001コピー用紙 A4出庫5総務へ
32026/10/3A002ボールペン 黒入庫30定期発注分

数量は、入庫も出庫もプラスの数で入力します。入庫か出庫かは「区分」の列で分けます。

商品名を自動で表示する（INDEX＋MATCH）

C2には、商品コードから商品名を引いてくる数式を入れます。

=IF(B2="","",IFERROR(INDEX(商品マスタ!$B$2:$B$201,MATCH(B2,商品マスタ!$A$2:$A$201,0)),"コード未登録"))
  • MATCH(B2, 商品マスタのA列, 0) で、商品コードが商品マスタの何行目にあるかを探します
  • INDEX(商品マスタのB列, その行) で、同じ行の商品名を取り出します
  • 商品マスタにないコードを入れたときは「コード未登録」と表示されるので、入力ミスにすぐ気づけます

XLOOKUP が使える Excel なら =XLOOKUP(B2,商品マスタ!A:A,商品マスタ!B:B,"コード未登録") でも同じことができます。INDEX＋MATCH は古いバージョンのExcelでも動くので、社内で使う表ならこちらが無難です。

入力ミスを防ぐ（データの入力規則）

区分の列（D列）は「入庫」「出庫」以外が入ると集計されません。D列を選んで「データ」→「データの入力規則」→「リスト」を選び、元の値に 入庫,出庫 と入れておくと、選ぶだけで入力できます。

商品コードの列（B列）も同じように、元の値を =商品マスタ!$A$2:$A$201 にすると、登録済みのコードから選べるようになります。

手順3：在庫一覧で現在庫を計算する

3枚目のシートの名前を「在庫一覧」にして、見出しを入れます。

ABCDEFGHI
1商品コード商品名単位期首在庫入庫合計出庫合計現在庫発注点判定

A〜D列とH列は、商品マスタの値をそのまま表示します。

A2: =IF(商品マスタ!A2="","",商品マスタ!A2)
B2: =IF($A2="","",商品マスタ!B2)
D2: =IF($A2="","",商品マスタ!D2)
H2: =IF($A2="","",商品マスタ!E2)

入庫・出庫の合計（SUMIFS）

この表の中心になる数式です。入出庫記録から「この商品コード」かつ「入庫」の数量だけを合計します。

E2: =IF($A2="","",SUMIFS(入出庫記録!$E$2:$E$1001,入出庫記録!$B$2:$B$1001,$A2,入出庫記録!$D$2:$D$1001,"入庫"))
F2: =IF($A2="","",SUMIFS(入出庫記録!$E$2:$E$1001,入出庫記録!$B$2:$B$1001,$A2,入出庫記録!$D$2:$D$1001,"出庫"))

SUMIFS は SUMIFS(合計する範囲, 条件の範囲1, 条件1, 条件の範囲2, 条件2) の形で、条件をいくつでも追加できます。ここでは「商品コードが一致」と「区分が入庫（出庫）」の2つの条件を使っています。

現在庫と判定

G2: =IF($A2="","",D2+E2-F2)
I2: =IF($A2="","",IF(G2<0,"数量を確認",IF(G2<=H2,"要発注","")))

現在庫は「期首在庫＋入庫合計−出庫合計」です。判定の列は、現在庫が発注点以下なら「要発注」、マイナスになっていたら「数量を確認」と表示します。マイナスの在庫は実際にはありえないので、入力ミスのサインになります。

作った数式を、下の行（商品の数より多めに）までコピーすれば完成です。

「要発注」を赤くする（条件付き書式）

在庫一覧のA2からI列の最終行までを選び、「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。数式に =$I2="要発注" と入れ、塗りつぶしを薄い赤にすると、発注が必要な商品の行全体が目立つようになります。

サンプルで動きを確認する

テンプレートに入っているサンプルでは、在庫一覧は次のようになります。

商品名期首在庫入庫出庫現在庫発注点判定
コピー用紙 A420013710要発注
ボールペン 黒503008020
クリアファイル A4200012080100要発注
段ボール 小8050557530
梱包用テープ1507810要発注

入出庫記録に1行足すだけで、ここの数字がすぐに変わります。

運用のコツ

  • 月に1回は棚卸しをする：記録漏れや数え間違いは必ず起きます。実際の数と現在庫がずれていたら、差を「入庫」か「出庫」で記録して合わせ、備考に「棚卸し調整」と書いておきます。
  • 記録は後回しにしない：まとめて入力しようとすると、だいたい漏れます。出し入れしたその場で1行入れるのが一番確実です。
  • 行は削除しない：間違えた記録は消さずに、逆の区分で同じ数を記録して打ち消すと、履歴が残ります。

AIに手伝ってもらう

数式を自分で組み立てるのが大変なときは、ChatGPTやClaudeに「シート名と列の構成」を伝えて数式を作ってもらうのも手です。

在庫管理表を作っています。「入出庫記録」シートのB列に商品コード、D列に「入庫」か「出庫」、E列に数量があります。「在庫一覧」シートのA2の商品コードについて、出庫数量の合計を出すExcelの数式を作ってください。

このように、シート名・列・入っている値を具体的に書くと、そのまま使える数式が返ってきやすくなります。返ってきた数式は、サンプルの数字で手計算と合うかを必ず確かめてから使いましょう。

Excelでの管理が限界になったら

このテンプレートは、1〜数人で、商品数が数百点くらいまでの管理を想定しています。次のような状況になったら、クラウドの在庫管理システムを検討するタイミングです。

  • 複数の人が同時に入力して、ファイルの取り合いや上書きが起きる
  • 倉庫や店舗が複数あり、場所ごとの在庫を分けて見たい
  • バーコードやスマホで入出庫を記録したい
  • ネットショップの在庫と自動で連動させたい

まずはExcelで「記録する習慣」を作っておくと、システムに移るときもデータをそのまま活かせます。

在庫管理表テンプレートをダウンロード（Excel形式）