Excelで在庫管理表を作成する方法:ステップ・バイ・ステップ

Excelで在庫管理表を作成するには、2つのテーブルを作成します。1つ目はアイテムを一覧表示するテーブルで、アイテムID、品名、単価、期首在庫、発注点の列を設けます。2つ目はすべての入出庫履歴を記録するテーブルで、日付、アイテムID、区分(InまたはOut)、数量の列を設けます。その後、SUMIFS関数を使って入庫数と出庫数を合計し、それらを引き算して手元在庫を算出します。
発注点以下のアイテムにフラグを立てるステータス列を追加し、それらの行を条件付き書式でハイライトします。上部にある簡単なサマリーには、総在庫価値と再発注が必要なアイテム数が表示されます。
このガイドでは、テーブル構造から数式、アラートの設定まで、各ステップを解説します。また、小さなオフィス用品の備品室を想定した具体的な例や、エクスポートデータから管理表をより素早く作成する方法についても紹介します。手順は2026年11月18日に確認したMicrosoftのサポートページに基づいています。
在庫管理表の役割
在庫管理表は、いつでも次の3つの疑問に答えてくれます。
- 各アイテムの在庫はどれくらいあるか? これは手元在庫(現在庫)です。
- 在庫の価値はいくらか? これは単価に基づく在庫価値です。
- 何を注文する必要があるか? これらは発注点以下のアイテムです。
優れた管理表は履歴も保持します。在庫数が合わないときは、入出庫履歴をさかのぼって、いつ在庫が出入りしたかを確認できます。
Excelでの在庫管理は、中小規模の在庫に適しています。備品室、小さなお店、オフィス用品、予備部品、イベント機材などの管理に最適です。複数の拠点にまたがる数千点ものアイテムを追跡するようになった場合は、通常、専用の在庫管理システムを導入する方が適しています。
管理表の設計:2つのテーブル
数式を入力する前に、在庫管理表の構造を決めましょう。シンプルな管理表の多くは、1つのテーブルに数量列を1つだけ設け、それを手動で上書きしていく形式をとっています。しかし、これではすぐに破綻してしまいます。いつ、なぜ数値が変更されたのかが誰にも分からなくなるからです。
より優れた設計は、アイテムリストと入出庫履歴を分けることです。
| テーブル | 1行あたりのデータ | 主な列 |
|---|---|---|
| アイテム | 製品または部品 | アイテムID、品名、カテゴリ、単価、期首在庫、発注点 |
| 入出庫履歴 | 在庫の変動 | 日付、アイテムID、区分(InまたはOut)、数量、備考 |
「アイテム」テーブルには、めったに変更されない基本情報を保持します。「入出庫履歴」テーブルは毎日データが増えていきます。「アイテム」テーブルの数式が「入出庫履歴」テーブルを読み込み、現在の在庫を計算します。
この設計には3つのメリットがあります。数値を上書きする必要がありません。すべての変更に日付が残ります。そして、「3月にどれだけの在庫が出庫されたか」といった質問に後から答えることができます。
また、在庫管理表を安全に共有できるようになります。スタッフは「入出庫履歴」テーブルに行を追加するだけで済みます。数式が入った「アイテム」テーブルはロックしたままにしておくことができます。
各テーブルは個別のシートに配置します。数式を読みやすくするために、シート名を「Items」と「Movements」に設定します。
アイテムリストをテーブルとして作成する
まず「Items」シートから始めます。1行目にヘッダー(Item ID、Name、Category、Unit cost、Opening stock、Reorder point)を入力します。その後、アイテムごとに1行ずつデータを入力します。
次に、この範囲をExcelのテーブルに変換します。Microsoftはその理由を「関連するデータのグループの管理と分析を容易にするために、セルの範囲をExcelのテーブルに変換できます」と説明しています。作成手順は以下の通りです。
- データ内の任意のセルを選択します。
- ホーム > テーブルとして書式設定を選択します。
- テーブルスタイルを選択します。
- 「先頭行をテーブルのヘッダーとして使用する」にチェックが入っていることを確認し、OKを選択します。
テーブルデザインタブで、テーブル名を「Items」に変更します。もう一方のシートの入出庫履歴でも同様の操作を行い、名前を「Movements」にします。
テーブルを使用すると、2つのメリットがあります。行を追加すると自動的にテーブルが拡張されます。また、テーブル列内の数式は自動的に下方向へコピーされます。Microsoftは、1つのセルに入力された数式は「そのテーブル列の他のすべてのセルに即座に適用されます」と述べています。
アイテムIDは「A-101」のように短く一意のものにしてください。品名は表記揺れが発生しやすいため、品名の代わりに常にアイテムIDを使用するようにします。
入出庫履歴にドロップダウンを追加する
入力ミスは、在庫数が合わなくなる最大の原因です。ドロップダウンリストを使用すれば、そのほとんどを防ぐことができます。
Microsoftはその目的を「データの入力規則を使用して、ユーザーがセルに入力するデータの種類や値を制限します(ドロップダウンリストなど)」と簡潔に説明しています。Type列の設定手順は以下の通りです。
- MovementsテーブルのType列を選択します。
- データ > データの入力規則を選択します。
- 設定タブの入力値の種類ボックスでリストを選択します。
- 元の値ボックスに「In,Out」と入力します。
- ドロップダウンリストから選択するにチェックが入っていることを確認し、OKを選択します。
Item ID列でも同様の操作を行いますが、元の値にはItemsテーブルのItem ID列を指定します。これにより、存在するアイテムのみを選択できるようになります。
ドロップダウンは入力作業のスピードアップにもつながります。備品室の担当者は、アイテムと区分を選択し、数量を入力するだけで、数秒で納品を記録できます。
数量(Quantity)列を制限することもできます。入力値の種類ボックスで整数を選択し、最小値を「1」に設定します。これにより、空白やマイナスの入力がブロックされます。
SUMIFSで手元在庫を計算する
次に、Itemsテーブルに計算列を追加します。SUMIFS関数は、複数の条件を満たす数値を合計します。Microsoftによる構文は以下の通りです。
SUMIFS(合計対象範囲, 条件範囲1, 条件1, [条件範囲2, 条件2], ...)
「Stock in」という列を追加します。その最初のセルに以下を入力します。
=SUMIFS(Movements[Quantity],Movements[Item ID],[@[Item ID]],Movements[Type],"In")
これにより、Movementsテーブル内の、アイテムIDがその行と一致し、かつ区分が「In」であるすべての数量が合計されます。テーブルは、すべてのアイテムに対してこの数式を自動的に下方向へコピーします。
同じ数式を使って「Stock out」列を追加しますが、「In」を「Out」に変更します。
次に、「On hand」列を追加します。
=[@[Opening stock]]+[@[Stock in]]-[@[Stock out]]
最後に、「Stock value」列を追加します。
=[@[On hand]]*[@[Unit cost]]
数式を作成したら、1つのアイテムを手動で確認してみましょう。Movementsテーブルをそのアイテムでフィルターし、InとOutの行を合計して、その結果をOn hand列と比較します。
発注が必要なアイテムにフラグを立てる
在庫管理表は、次に何をすべきかを教えてくれるときに最も役立ちます。次の数式を使って「Status」列を追加します。
=IF([@[On hand]]<=[@[Reorder point]],"Reorder","OK")
これで、各行に「Reorder」または「OK」と表示されます。発注点は、注文が届くまでの期間と、その期間中の消費量に基づいて設定してください。
条件付き書式を使って、「Reorder」の行を目立たせます。
- Status列を選択します。
- ホーム > 条件付き書式 > セルの強調表示ルール > 指定の文字列を選択します。
- 「Reorder」と入力し、薄い赤などの塗りつぶしの色を選択します。
- OKを選択します。
Microsoftは、「指定の文字列」ダイアログボックスが表示されたら、「適用する書式設定オプションを入力し、[OK] をクリックします」と説明しています。ハイライトは、在庫が変動するたびに自動的に更新されます。
また、発注を行う前にStatus列を並べ替えたりフィルターしたりして、Reorderの行だけを表示させることもできます。
XLOOKUPでアイテムの詳細を検索する
入出庫履歴にはアイテムIDが表示されますが、これだけでは判別しにくい場合があります。検索列を追加することで、各履歴の横に品名を表示できます。
Microsoftは、XLOOKUP関数を「テーブルまたは範囲を行ごとに検索する」方法と説明しています。その構文は以下の通りです。
XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
Movementsテーブルに「Name」列を追加し、以下を入力します。
=XLOOKUP([@[Item ID]],Items[Item ID],Items[Name],"Unknown ID")
存在しないIDが入力された場合、セルには「Unknown ID」と表示されます。これにより、エラーを簡単に見つけることができます。
Microsoftは「XLOOKUP は Excel 2016 および Excel 2019 では使用できません」と指摘しています。これらのバージョンでは、INDEX関数とMATCH関数を組み合わせるか、最初の列にアイテムIDを配置してVLOOKUP関数を使用してください。
上部にサマリーを追加する
在庫管理表の最後の仕上げはサマリーです。簡単なサマリーがあれば、スクロールしなくても誰でも在庫状況を一目で把握できます。Itemsテーブルの上部に数行追加するか、別の「Summary」シートを作成してセルを追加します。
以下の数式を使用します。
- 総在庫価値:
=SUM(Items[Stock value]) - 発注対象アイテム数:
=COUNTIF(Items[Status],"Reorder") - 今月の出庫数:
=SUMIFS(Movements[Quantity],Movements[Type],"Out",Movements[Date],">="&DATE(2026,11,1))
COUNTIF関数はシンプルなパターンに従います。Microsoftの言葉を借りれば、「どこを検索するか」と「何を検索するか」を指定します。
カテゴリ別の在庫価値を示す棒グラフを追加することもできます。サマリーの範囲を選択し、挿入 > グラフを選択します。サマリーは、重要な3〜5個の数値に絞ってコンパクトにまとめましょう。
具体的な例:オフィス用品の備品室
以下は、実際に運用されている在庫管理表の具体例です。ある小規模オフィスで5つの備品アイテムを追跡しています。11月前の「Items」テーブルは以下のようになっています。
| アイテムID | 品名 | 単価 | 期首在庫 | 発注点 |
|---|---|---|---|---|
| A-101 | ノート A5 | $2.50 | 120 | 50 |
| A-102 | ボールペン(箱) | $6.00 | 40 | 20 |
| B-201 | コピー用紙(締) | $4.20 | 80 | 30 |
| B-202 | トナーカートリッジ | $45.00 | 12 | 5 |
| C-301 | 梱包用ダンボール(中) | $0.80 | 300 | 100 |
11月中に、Movementsテーブルには以下の変動が記録されました。ノート:出庫85、入庫50。ペン:出庫25。用紙:出庫60、入庫40。トナー:出庫9。ダンボール:出庫180。
数式を実行すると、計算列は以下のように表示されます。
| アイテムID | 入庫数 | 出庫数 | 手元在庫 | 在庫価値 | ステータス |
|---|---|---|---|---|---|
| A-101 | 50 | 85 | 85 | $212.50 | OK |
| A-102 | 0 | 25 | 15 | $90.00 | Reorder |
| B-201 | 40 | 60 | 60 | $252.00 | OK |
| B-202 | 0 | 9 | 3 | $135.00 | Reorder |
| C-301 | 0 | 180 | 120 | $96.00 | OK |
この例は、入出庫履歴がなぜ重要であるかも示しています。コピー用紙は手元に60締あり一見問題なさそうですが、履歴を見ると1ヶ月で60締が出庫されています。このペースが続けば、用紙は数週間以内に発注点に達します。数量列が1つしかない管理表では、このようなトレンドは隠れてしまいます。
1つの行を手動で確認してみましょう。ノートは120から始まり、50増えて85減ったため、120 + 50 − 85 = 85となります。これはOn hand列と一致しています。
サマリーには、総在庫価値が$785.50、発注対象アイテム数が2と表示されます。ペンとトナーはどちらも発注点を下回っているため、今週の注文リストに追加されます。
AIを使ってExcelで在庫管理表を作成する
手動で管理表を作成するには1〜2時間かかります。特に時間がかかるのはデータの処理です。POSシステムからエクスポートしたデータのクレンジング、品名の照合、重複したIDの修正などです。
AIツールを使えば、これらのクレンジングや分析を自動で行うことができます。ここでは、Powerdrill Bloomを使用した3つのステップによる方法を紹介します。
ステップ 1:Excel、CSV、またはPDFファイルをアップロードする
在庫のエクスポートデータ、仕入先リスト、または先月の棚卸シートをアップロードします。Excel AI Assistantは、スプレッドシートファイルを直接処理できます。
アイテムリストと入出庫履歴の両方がある場合は、別々のファイルであっても、両方をアップロードしてください。
ステップ 2:自然言語で管理表の作成を依頼する
必要な内容を説明します。例えば、「手元在庫、在庫価値、および20個以下のアイテムに対する発注フラグを備えた在庫管理表を作成してください」と指示します。また、今週注文が必要なアイテムをリストアップするよう依頼することもできます。
「どのカテゴリが最も多くの資金を占めているか」といった追加の質問をすることも可能です。
ステップ 3:結果を確認してエクスポートする
いくつかのアイテムの手元在庫数を前回の棚卸数と照合します。その後、クレンジングされたテーブルをExcelにエクスポートするか、分析結果をチーム向けの簡単なレポートやスライドに変換します。
これは、在庫管理の更新が滞ってしまった場合に便利です。販売システムから毎月エクスポートされるデータから実際の出庫状況を把握し、それを履歴と比較することができます。
日常の更新には2テーブル設計を維持してください。迅速なデータクレンジングや月次レビューが必要な場合に、AIを活用するステップを取り入れましょう。
避けるべきよくある間違い
在庫管理表における問題のほとんどは、数式ではなく、データの入力方法に起因します。以下は、Excelでの在庫管理で特によく発生する問題です。
- 数量の上書き。 代わりに、すべての変動を新しい履歴として記録してください。
- IDの代わりに品名を使用する。 「ペン」と「ペン(箱)」が別々のアイテムになってしまいます。一意のIDを使用してください。
- 期首在庫の未入力。 開始時の在庫数がなければ、すべての手元在庫の数値が誤ったものになります。
- 単位の混在。 同一アイテムについて、箱単位とバラ単位の両方で追跡することは避け、どちらか一方に統一してください。
- 定期的な棚卸の未実施。 毎月または四半期ごとに、管理表の数値と実際の在庫数を比較してください。
- 発注点の見直し不足。 使用量や仕入先のリードタイムが変化した場合は、発注点を更新してください。
- 履歴行の削除。 ミスを修正するために履歴を削除してはいけません。代わりに、備考を添えて修正用の入力を行ってください。
- 数式の上書き。 誰も数式を数値で上書きできないように、Itemsシートを保護してください。
棚に長く置かれすぎているアイテムを見つける方法については、当社のガイド「滞留在庫を見つける方法」をご覧ください。需要予測については、「在庫および需要予測に最適なAIツール」のリストをご覧ください。
在庫データが整理されていないエクスポートデータである場合は、Powerdrill Bloomをお試しください。データをクレンジングして、すぐに使える管理表を作成できます。
よくある質問
Excelでシンプルな在庫管理表を作成するにはどうすればよいですか?
アイテムID、品名、単価、期首在庫、発注点を含む「Items」テーブルを作成します。次に、各変動を記録する「Movements」テーブルを追加します。その後、SUMIFS関数を使用して、入庫数、出庫数、および手元在庫を計算します。
Excelで手元在庫を計算する数式は何ですか?
手元在庫は、期首在庫に入庫数を足し、出庫数を引いたものです。SUMIFS関数を使用して、入出庫履歴からアイテムごとに入庫数量と出庫数量を合計します。
Excelで在庫不足のフラグを立てるにはどうすればよいですか?
手元在庫が発注点以下になったときに「Reorder」を返すIF数式を使用して、Status列を追加します。その後、条件付き書式を使ってそれらのセルをハイライトします。
在庫管理表はどのくらいの頻度で更新すべきですか?
変動が発生するたびに、または少なくとも1日に1回は記録してください。その後、毎月または四半期ごとに管理表と実際の在庫数を比較し、差異がある場合は調整入力を行って修正します。
Excelで複数の拠点の在庫を追跡することはできますか?
はい、可能です。Movementsテーブルに「Location(拠点)」列を追加し、SUMIFS関数の2つ目の条件として拠点を指定します。これにより、アイテムごとに拠点別の手元在庫数を算出できます。
情報源: Microsoft サポート、Excel テーブルの概要 · Microsoft サポート、セルにデータの入力規則を適用する · Microsoft サポート、SUMIFS 関数 · Microsoft サポート、XLOOKUP 関数 · Microsoft サポート、条件付き書式を使用して Excel で情報を強調表示する · Microsoft サポート、Microsoft Excel で COUNTIF 関数を使用する