在庫管理をエクセルで行う方法：表の作り方・テンプレートと限界のサイン

在庫管理をExcelで始めるとき、品目名と現在庫数だけを並べた表では、返品・移動・棚卸し差異が発生した時点で「なぜこの数になったのか」を追いにくくなります。BtoB商材では、出荷できる在庫と検品・確認中の在庫を分け、保管場所ごとの所在も把握できる設計が必要です。

この記事では、履歴から在庫を集計するExcel在庫管理表の作り方、数式、日々の運用ルールを解説します。自社で使える履歴型テンプレートを再現し、Excelを続けるか仕組みを見直すかを判断できるようになります。

在庫一覧を直接更新するのではなく、入出庫履歴を根拠に集計し、日次確認と棚卸しで精度を保ちます。運用上の課題が増えた場合は、Excelの再設計や仕組みの見直しを検討します。

01SectionExcelで在庫管理を始める前に、管理単位と記録時点を決める

表を作る前に決めるべきなのは、何を1つの在庫として数えるか、いつ在庫を増減させるかです。ここが曖昧だと、関数を増やしても帳簿在庫と実在庫が合わなくなります。

基本キーは「品目コード＋保管場所」

標準の集計キーは、品目コード＋保管場所です。たとえば同じ部材でも東京倉庫と大阪倉庫に保管されていれば、在庫一覧では別行として管理します。保管場所を分けない場合も、保管場所列に「共通」などの固定値を入れると、後から拠点別管理へ広げる際に構造を保ちやすくなります。

在庫区分は、まず次の2つに絞ります。

• 出荷可能：検品済みで、出荷・仕入発注判断に使う在庫
• 隔離・保留：返品直後、検品待ち、品質確認中など、出荷判断に含めない在庫

ロット番号、使用期限、シリアル番号は、取引条件や追跡要件がある品目だけに追加します。すべての品目に最初から加えると入力負荷が増え、未入力が増えるおそれがあります。

在庫一覧は直接更新せず、履歴から集計する

現在庫を直接書き換える方式は始めやすい一方、変更理由や入力漏れを追跡しにくい方法です。標準モデルでは、初期在庫を含むすべての増減を「入出庫履歴」に記録し、在庫一覧はその履歴を集計する照会用シートとします。

在庫一覧は直接更新せず、履歴を根拠に集計します。この原則により、在庫差異が出たときに、いつ・どの品目・どの保管場所で変動したのかを確認できます。

記録時点も統一します。たとえば入庫は検品完了時、出庫は出荷確定時、返品は検品結果が確定した時点など、業務責任者が定めた時点で記録します。伝票作成時と実物移動時が異なる場合は、どちらを在庫変動の基準にするかを明文化してください。

02Section履歴を残せる在庫管理表の基本設計

履歴型では、数量をすべて正数で入力し、増減方向を「在庫区分」と「区分」の組み合わせで決めます。マイナス記号を人が入力する方式より、入力ルールと数式を統一しやすくなります。

返品・隔離・廃棄を出荷可能在庫と混同しない

返品された品目は、検品済みで良品復帰できる場合だけ出荷可能在庫へ加算します。検品前の返品は「隔離・保留」として受け入れ、出荷可能在庫には含めません。廃棄確定前の品目も、標準では隔離・保留で扱います。

棚卸し差異は、在庫一覧の数値を上書きして合わせるのではなく、棚卸し調整シートに差異原因と承認を残します。その後、確定した調整だけを「棚卸増」または「棚卸減」として入出庫履歴へ登録します。

移動と振替は、同じ番号を使う2行で記録する

移動は保管場所を、振替は在庫区分を変える処理です。いずれも同じ番号・品目・数量で対になる2行を記録すると、後から照合しやすくなります。

保管場所間の移動と、隔離・保留から出荷可能への振替は似ていますが、変える対象が異なります。

• 移動：保管場所を変える処理。同じ在庫区分のまま、移動元・移動先を2行で記録します。
• 振替：在庫区分を変える処理。同じ保管場所で、隔離・保留の減少と出荷可能の増加を2行で記録します。

移動番号ごとに、日次締めで「2行あるか」「移動元と移動先が異なるか」「品目コード・在庫区分・数量が一致するか」を確認します。検品場所へ隔離品を移しただけでは、出荷可能在庫へ振り替えません。

03Sectionコピーして作れる履歴型テンプレートの仕様

標準テンプレートは5シートで構成します。入出庫履歴を中心にして、マスタ、集計、棚卸し、入力候補を分ける設計です。

確定する列順と入力列

品目マスタは、A:品目コード、B:品目名、C:単位、D:分類、E:安全在庫（品目合計）、F:発注点（品目合計）、G:標準保管場所、H:ロット管理、I:期限管理、J:シリアル管理の順です。

入出庫履歴はテーブル名をtbl履歴とし、A:処理日、B:伝票番号／移動・振替番号、C:品目コード、D:保管場所、E:在庫区分、F:区分、G:数量、H:ロット番号、I:使用期限、J:シリアル番号、K:相手先／関連先、L:理由コード、M:入力者、N:確認者、O:備考の順にします。数量は常に正数です。

在庫一覧は、A:品目コード、B:保管場所、C:品目名、D:単位、E:場所別出荷可能在庫、F:場所別隔離・保留在庫、G:場所別総物量参考、H:品目合計出荷可能在庫、I:安全在庫、J:発注点、K:仕入発注判定、L:場所別補充点、M:場所別補充判定、N:注記、O:在庫キーの順です。入力列はA・B・L・N列とし、それ以外の列に数式を設定します。L:場所別補充点は、拠点間の移動補充を管理したい保管場所の行にだけ入力し、不要な行は空欄のままにします。O2は=A2&"|"&B2とし、重複チェックに使います。

棚卸し調整は、A:棚卸日、B:棚卸番号、C:品目コード、D:保管場所、E:在庫区分、F:帳簿在庫、G:実在庫、H:差異、I:差異理由、J:調査結果、K:調整区分、L:履歴伝票番号、M:承認者の順です。差異H2は=G2-F2で計算します。

設定シートには保管場所、在庫区分、入出庫区分、差異理由、担当者の候補一覧を置きます。保管場所、在庫区分、区分、理由コードには、データの入力規則の公式説明を確認して入力規則を設定します。貼り付けデータや既存の誤りは自動修正されないため、日次確認は必要です。

P-001で検算する

数式は実データを入れる前に、P-001（部材A）で検算します。安全在庫20、発注点30、保管場所は東京倉庫・大阪倉庫とします。

東京倉庫には、出荷可能として初期在庫100、入庫20、出庫30、移動出庫10、棚卸減2、隔離から良品への振替入庫3を、隔離・保留として顧客返品・隔離受入5、隔離から良品への振替出庫3を記録します。大阪倉庫には同じ移動番号で出荷可能／移動入庫10を記録します。

期待結果は、東京倉庫が出荷可能81・隔離2・総物量83、大阪倉庫が出荷可能10・隔離0・総物量10です。品目合計の出荷可能在庫は91のため仕入発注判定は「適正」です。大阪倉庫の行のL列（場所別補充点）に15を入力した場合、場所別出荷可能在庫10がこれを下回るため、M列の場所別補充判定は「移動補充確認」となります。

04Section在庫を集計し、入力ミスを減らす関数と設定

履歴型の中心は、複数条件に合う数量を合計するSUMIFSです。関数の仕様はSUMIFS 関数の公式説明で確認できます。以下の式は、前述の列見出し、テーブル名、区分名をそのまま使う前提です。

E2・F2：保管場所別の出荷可能在庫と隔離・保留在庫

在庫一覧のE2には、出荷可能区分の増加・減少を集計します。

=SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"初期在庫")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"入庫")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"顧客返品・良品復帰")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"隔離→良品振替（入庫）")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"棚卸増")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"移動入庫")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"出庫")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"仕入先返品（良品）")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"廃棄（良品）")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"棚卸減")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"移動出庫")

F2には隔離・保留区分を集計します。

=SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"初期在庫")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"顧客返品・隔離受入")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"棚卸増")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"移動入庫")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"隔離→良品振替（出庫）")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"仕入先返品（隔離）")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"廃棄（隔離）")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"棚卸減")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[保管場所],$B2,tbl履歴[在庫区分],"隔離・保留",tbl履歴[区分],"移動出庫")

区分名は、設定シートの候補値、入出庫履歴の入力値、集計式で完全に一致させます。P-001の東京倉庫では、顧客返品・隔離受入の5から隔離→良品振替（出庫）の3を減算するため、F2の結果は5-3=2です。

H2・K2：品目合計で仕入発注を判断する

H2は、E2と同じ考え方で、保管場所を条件に含めずに出荷可能在庫を集計します。加算・減算する区分はE2と同じです。

=SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"初期在庫")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"入庫")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"顧客返品・良品復帰")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"隔離→良品振替（入庫）")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"棚卸増")
+SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"移動入庫")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"出庫")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"仕入先返品（良品）")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"廃棄（良品）")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"棚卸減")
-SUMIFS(tbl履歴[数量],tbl履歴[品目コード],$A2,tbl履歴[在庫区分],"出荷可能",tbl履歴[区分],"移動出庫")

K2には次の式を入れます。

=IF(H2<=J2,"仕入発注確認","適正")

仕入発注は、場所別在庫ではなく品目合計の出荷可能在庫H2と発注点J2で判断します。場所別に不足している場合は、仕入発注ではなく、L列の場所別補充点と移動元の在庫を確認して拠点間の移動補充を検討します。

場所別の移動補充は、M2で判定します。場所別補充点L2が空欄の行は判定の対象外です。

=IF($L2="","",IF($E2<=$L2,"移動補充確認","適正"))

XLOOKUP、テーブル、条件付き書式を補助に使う

C2の品目名は、XLOOKUP 関数の公式説明を確認し、次のように参照できます。

=XLOOKUP($A2,品目マスタ!$A:$A,品目マスタ!$B:$B,"")

XLOOKUPを使えない環境では、品目コード列が参照範囲の先頭列にある場合に限り、VLOOKUPを代替候補にできます。

=IFERROR(VLOOKUP($A2,品目マスタ!$A:$F,2,FALSE),"")

IFERRORで空欄を返す場合も、未登録の品目コードを見逃さないよう日次確認で空欄を抽出します。

入出庫履歴はExcel テーブルの公式説明を参照してテーブル化します。発注確認、マイナス在庫、入力不備には条件付き書式の公式説明を使えますが、確認者や承認手順の代わりにはなりません。

05Sectionテンプレートを選び、変更する前の確認点

標準は履歴型です。ただし、管理要件によっては別の構成が適します。単一シート型は、更新者と在庫変動が少ない暫定運用には向きますが、返品・移動・差異理由を追う用途には不向きです。

保管場所別型は、複数倉庫・棚・営業所の所在確認が必要な場合に、履歴型へ保管場所管理と、移動を対になる2行で記録するルールを加えたものです。ロット・期限型はロット単位の受入・出荷・棚卸しを一貫して記録できる場合、シリアル型は個体ごとの所在や保証履歴が必要な場合に検討します。

品目名、単位、保管場所候補、安全在庫、発注点は自社運用に合わせて変更できます。一方で、在庫区分、区分候補、入力規則、集計式を変える場合は、P-001のような通常・返品・移動・棚卸しを含むデータで必ず再検算してください。

06Section在庫を正確に保つための入力・棚卸し・ファイル運用

在庫精度は数式だけでは保てません。入荷・出荷・移動の入力者と確認者を決め、少人数でも入力時点と日次締め時点を分けて確認します。

日次締めと棚卸し調整を分ける

日次締めでは、未入力、マイナス在庫、移動・振替で対になる行の欠落、未登録品目、理由コード不足を確認します。棚卸しでは、計数、差異確認、原因調査、承認、履歴への調整登録を分けます。

保存先・権限・引継ぎを決める

正本ファイルの保存先、閲覧・編集権限、バックアップ、日次締め後の保存版、数式変更履歴、引継ぎ手順を定めます。OneDriveまたはSharePoint上のMicrosoft 365ファイルでは、条件が整えばOfficeファイルのバージョン履歴で過去版の確認・復元ができます。

数式セルは、ワークシート保護の公式説明を確認して保護対象にできます。ただし、保護や版履歴は誤変更の抑制・復旧を補助するもので、承認、責任分担、例外処理、引当の整合を自動化するものではありません。

KPIの目標値は、品目単価、欠品や誤出荷の影響、棚卸し頻度、現状値を踏まえて決めます。

07SectionExcelを続けるか、再設計・仕組み見直しへ進むか

Excelの適性は品目数だけでは判断できません。同時編集、拠点、即時性、追跡、連携、権限、属人化を合わせて確認し、必要な対応を選びます。

Excelの限界は品目数だけでなく、同時編集、拠点、更新の即時性、引当、追跡要件、連携、権限、差異の影響、属人化を合わせて判断します。

Excel継続では履歴入力、日次締め、棚卸し調整を守れることを確認します。再設計では、品目コード・保管場所・区分を統一し、直接更新を履歴登録へ置き換えます。

受注・出荷・在庫を別々の表へ転記している場合は、業務ごとの正本を整理してください。販売管理との役割分担は、販売管理をエクセルで行う方法も参考になります。

共同編集や版履歴は共同作業と復旧の補助です。即時引当、例外承認、部門間の入力責任まで必要な場合は、Excel運用の改善だけで対応できるかを慎重に見極めます。

08Section仕組みを見直す場合の要件整理から受入判断まで

機能比較だけで進めず、返品・隔離・移動・棚卸し調整などの例外処理を含めてテストし、受入条件に照らして判断します。

販売管理・ERPを含む仕組みを検討する場合、機能名だけで比較を始めると、返品や隔離、移動、棚卸し調整といった例外処理が後から漏れやすくなります。要件定義、移行、テスト、運用を含む考え方は、IPA「ユーザのための要件定義ガイド 第2版」も確認先になります。

販売管理・ERPを含む仕組みでは、在庫だけでなく受注、発注、購買、会計との連携範囲も検討対象になります。選定時の基本的な観点は、販売管理システムの基礎と選び方で確認できます。

Excel再設計、要件整理・データ整備支援、販売管理・ERPを含む導入は、対象業務と支援範囲が異なります。同じ費用項目として比較せず、上記の条件をそろえて確認してください。契約条件、利用規模、連携要件、データ整備、教育・保守範囲によって費用や進め方は変わるためです。

自社でExcelを改善する場合は、品目マスタ、保管場所一覧、初期在庫、入出庫区分、棚卸し差異、更新担当を先に整えます。外部相談を検討する場合は、現状フロー、利用者・拠点、差異事例、例外処理、連携先、優先要件、受入条件を整理しておくと、比較や要件確認を進めやすくなります。

在庫管理を含む業務の見直しを相談する

Excel運用の再設計だけで対応できるか、販売管理・在庫管理を含む仕組みの検討が必要か迷う場合は、現状フロー、拠点・利用者数、例外処理、連携先、優先要件を整理して相談先を比較しましょう。

会計・販売・在庫管理システム導入2964社掲載

会計・販売・在庫管理システム導入の相談先を探す