Excelで在庫を正しく管理するポイントは、現在庫を手入力することではありません。商品マスタと入出庫の履歴を分け、履歴から現在庫を計算できる形にすることです。入庫、出庫、返品、棚卸し、調整を同じルールで記録すれば、在庫が変わった理由を追跡でき、担当者が交代しても運用を引き継ぎやすくなります。
この記事では、Excelの在庫管理表を作るときのシート構成、入力項目、SUMIFSやIFERRORなどの関数、入力ミスを抑える設定、日次・月次の運用ルールを順番に解説します。Excelを使い続ける場合の改善方法と、複数拠点や同時編集で限界を感じたときにシステム化を検討する基準も整理します。
この記事で分かること
- Excelで在庫管理表を作るときに必要なシートと項目
- 商品マスタ、入出庫履歴、在庫一覧を分ける理由
- SUMIFS、IF、IFERROR、XLOOKUPなどを使った在庫計算の考え方
- 入力規則、テーブル、保護設定で入力ミスを抑える方法
- 棚卸し、返品、在庫調整を記録する運用ルール
- Excelのまま改善する範囲と在庫管理システムへ移行する判断基準
Excelで在庫管理する基本的な考え方
在庫管理表を作る前に、「在庫」とは何を指すのかを決めます。倉庫に置かれている数量だけを在庫とするのか、入荷済みで検品中の商品も含めるのか、受注済みで出荷を待っている商品をどのように扱うのかで、必要な列と計算式が変わります。販売可能な数量と物理的に存在する数量を分けるだけでも、欠品の見落としを減らせます。
最初に、業務で使う数量の定義を書き出します。たとえば「現在庫」は入庫と出庫を反映した手元の数量、「引当済み」は注文や製造に確保した数量、「販売可能数」は現在庫から引当済みを差し引いた数量とします。用語の定義が担当者ごとに違うと、同じセルを見ても判断が一致しません。管理表の先頭や別の設定シートに定義を残し、変更したときは履歴を記録します。
また、商品コードを在庫管理のキーとして扱います。商品名は表記ゆれが起きやすく、同じ名前でも規格や容量が違う商品があります。商品コード、単位、保管場所を一意に決め、入出庫履歴では商品コードを選択して入力する仕組みにします。コードのない商品を自由入力で追加すると、似た商品が別行に分かれ、集計結果が実態より少なく見える原因になります。
まず業務の流れを一枚に書く
管理表を作る前に、商品が動く流れを「入荷」「検品」「入庫」「移動」「出庫」「返品」「棚卸し」のように並べます。すべての業務を一度に自動化する必要はありませんが、在庫数が増減するタイミングを漏れなく定義することが重要です。仕入先から届いた時点で増やすのか、検品後に増やすのかを決めておかないと、同じ入荷を二重に計上することがあります。
店舗や倉庫が複数ある場合は、拠点間移動も独立した処理として扱います。出庫先を空欄にしたまま数量を減らすのではなく、移動元と移動先を一つの伝票番号で結びます。移動元では出庫、移動先では入庫として集計できるため、全拠点の合計と拠点別の内訳を照合しやすくなります。保管場所の管理を詳しく考える場合は、倉庫在庫管理システムの考え方も参考になります。
管理表のシート構成と作り方
小規模な在庫管理であっても、一つのシートに商品名、入庫数、出庫数、現在庫をすべて直接入力する構成は避けます。おすすめは「商品マスタ」「入出庫履歴」「在庫一覧」「設定・集計」の四つに分ける方法です。シートを分けると入力場所が明確になり、計算式を誤って上書きするリスクも下げられます。
| シート | 役割 | 主な項目 |
|---|---|---|
| 商品マスタ | 管理対象の商品を定義する | 商品コード、商品名、規格、単位、保管場所、発注点、無効フラグ |
| 入出庫履歴 | 在庫が動いた事実を一行ずつ残す | 日付、伝票番号、商品コード、区分、数量、拠点、担当者、備考 |
| 在庫一覧 | 履歴を集計して現在の状態を確認する | 商品コード、商品名、現在庫、引当数、販売可能数、発注点、判定 |
| 設定・集計 | 選択肢や月次集計を管理する | 区分、拠点、担当者、棚卸し日、集計期間、入力ルール |
商品マスタに持たせる項目
商品マスタでは、一行を一商品として扱います。必須項目は商品コード、商品名、単位です。箱と個、ケースと本のように単位が混在する場合は、基準単位を決めて換算します。仕入れは箱、出荷は個という業務なら、箱入数を別列に持ち、入庫時に基準単位へ変換する方法が考えられます。変換を担当者の暗算に任せると、数量の誤りを見つけにくくなるためです。
発注点や安全在庫を使う場合は、商品マスタに基準値を登録します。ただし、基準値を入力しただけで適切な発注になるわけではありません。販売量、納期、季節変動、最低発注数などを確認し、設定を見直す日を決めておきます。使用しなくなった商品は行を削除せず、無効フラグで新規入力の候補から外します。履歴に残る商品コードを削除すると、過去の集計や監査の確認ができなくなるためです。
入出庫履歴は一行一取引にする
入出庫履歴では、在庫が動くたびに一行を追加します。日付、伝票番号、商品コード、区分、数量を最低限の必須項目とし、拠点や保管場所、担当者、取引先、備考を業務に応じて追加します。月ごとにシートを分けると、年間集計や商品別の検索が複雑になるため、Excelのテーブル機能で一つの一覧を伸ばしていく方が扱いやすい場合があります。
数量は正の値で入力し、入庫と出庫の区分で増減を判断する方法が分かりやすいです。別の方法として、増減数量列に入庫は正、出庫は負の値を入れる方法もあります。どちらを採用する場合も、入力ルールを混在させないことが大切です。返品や棚卸し調整は通常の出庫・入庫と区別できる区分を用意し、後から理由を追える状態にします。
Excel関数で現在庫と発注判定を計算する
現在庫を手入力せず、入出庫履歴から計算することで、変更理由が残ります。最も基本的な考え方は、対象商品の入庫数量の合計から出庫数量の合計を差し引くことです。履歴に拠点や期間の条件がある場合は、商品コードだけでなく拠点や日付も条件に含めます。
SUMIFSで商品別の入庫・出庫を集計する
入出庫履歴をExcelのテーブルにして、テーブル名を「入出庫履歴」とします。区分列に「入庫」「出庫」を入力する場合、在庫一覧のA2に商品コードがあるときの現在庫は、次のような式で計算できます。
=SUMIFS(入出庫履歴[数量],入出庫履歴[商品コード],A2,入出庫履歴[区分],"入庫")-SUMIFS(入出庫履歴[数量],入出庫履歴[商品コード],A2,入出庫履歴[区分],"出庫")
この式は、商品コードがA2と一致する履歴だけを集計し、入庫合計と出庫合計の差を求めます。返品を入庫として扱うか、返品という区分のまま別集計するかは、検品後に販売可能へ戻す業務かどうかで決めます。検品待ちの返品を現在庫へ含めると出荷可能数を過大に見積もる可能性があるため、状態を別列にする方法もあります。
拠点別・期間別に絞り込む
拠点列を持つ場合は、条件に拠点を追加します。たとえば在庫一覧のB2に拠点コードがあるなら、SUMIFSの条件範囲に拠点列、条件にB2を指定します。日次や月次の集計では、開始日と終了日をセルに置き、日付列に対して「開始日以上」「終了日以下」の条件を設定します。期間の境界を式へ直接書き込まず、集計条件をセルで管理すると、確認者が対象期間を把握しやすくなります。
全拠点の在庫と拠点別在庫を同じ表で扱う場合、「全拠点」という文字を履歴へ入力して合計に混ぜないよう注意します。全拠点は集計結果であり、取引の拠点ではありません。拠点間移動は移動元の出庫と移動先の入庫を同じ伝票番号で登録することで、拠点別の残高と全体残高を両方確認できます。
IFとIFERRORで発注点を表示する
現在庫が商品マスタの発注点を下回ったら、在庫一覧に「発注確認」と表示することができます。たとえば現在庫がC2、発注点がD2の場合は、=IF(C2<=D2,"発注確認","通常")のような判定にします。安全在庫を含めて販売可能数で判断する場合は、どの数量を基準にするのかを明示します。表示が出たら即発注するのか、担当者が納期や発注残を確認してから発注するのかもルール化します。
商品コードから商品名や発注点を参照するときは、XLOOKUPが使える環境なら商品マスタとの対応を式にできます。古いExcelとの互換性が必要な場合は、VLOOKUPやINDEXとMATCHを選ぶこともあります。参照先にコードがない場合にエラー表示が並ぶと確認しづらいため、IFERRORで「マスタ未登録」と表示し、入力漏れを修正できるようにします。
| 目的 | 関数の例 | 確認すること |
|---|---|---|
| 商品別の入庫合計 | SUMIFS | 商品コードと区分が正しくそろっているか |
| 発注要否の表示 | IF | 現在庫、引当、発注点のどれを比較するか |
| 商品マスタの参照 | XLOOKUPまたはVLOOKUP | コード重複や未登録がないか |
| 未登録時の表示 | IFERROR | エラーを隠すだけでなく、修正対象を表示しているか |
関数は正しい式でも、元データの入力が統一されていなければ正しい結果になりません。区分の「出庫」と「出荷」、拠点名の全角と半角、余分なスペースなどは別の値として集計されます。式を複雑にする前に、入力規則とマスタで選択肢を固定する方が効果的です。在庫数だけでなく、判断に使う指標まで整理したい場合は、在庫管理を見える化する方法も確認してください。
入力ミスを防ぐExcelの設定
在庫表の精度は、関数の巧拙より入力方法に左右されます。誰でも自由に商品名や区分を書ける状態では、表記ゆれや誤入力が起きます。入力するセルと計算するセルを見た目で区別し、入力者が選ぶだけで済む項目を増やすことが運用しやすさにつながります。
入力規則で商品コードと区分を選択式にする
商品コード、区分、拠点、担当者は、データの入力規則によるリスト選択にします。区分は「入庫」「出庫」「返品入庫」「棚卸し増」「棚卸し減」など、実際の処理に必要な候補だけを用意します。候補が増えすぎると選択に迷うため、似た区分を細かく分ける必要があるかを現場で確認します。
商品コードを選ぶと商品名や単位を表示する構成にすれば、商品名の直接入力を減らせます。入力規則の元データは設定シートや商品マスタに置き、表の途中に候補を直接書かないようにします。商品を追加するときはマスタへ登録してから履歴へ入力する順番を守り、未登録商品が取引履歴に入り込まないようにします。
テーブルと条件付き書式を使う
入出庫履歴はテーブル化すると、行を追加したときに書式や計算列が自動で広がりやすくなります。フィルターで期間や商品を絞り込めるため、棚卸し対象を確認する作業にも使えます。テーブルの列名を式で参照すれば、列番号を数えて式を組む必要がなく、列の追加による参照ミスを抑えられます。
在庫一覧には条件付き書式を設定し、現在庫が発注点以下の場合、マスタ未登録、負の在庫、棚卸し未確認などを色で示します。色だけに頼ると印刷や色覚の違いで見落とす可能性があるため、「発注確認」「要確認」の文字も表示します。負の在庫は必ず誤りとは限りませんが、出庫を先に登録した、入庫が未登録になっているなどの確認対象として扱います。
計算セルを保護し、変更履歴を残す
在庫一覧の計算式や商品マスタのコード列は、入力者が上書きできないようにシート保護を設定します。保護を強くしすぎると、担当者が必要な修正をできず、別ファイルへコピーして管理するようになることがあります。入力セルだけ編集可能にし、マスタの追加や式の変更は管理者が行う運用が現実的です。
重要な変更は、別シートに変更日、対象、変更前、変更後、理由、担当者を記録します。Excelの変更履歴だけに頼るのではなく、棚卸しで数量を合わせた場合や、誤入力を訂正した場合に理由を残すことが大切です。ファイルのコピーを「最終版」「最終版2」のように増やすと、どれが正本か分からなくなるため、保存場所とファイル名のルールも決めます。
在庫管理を続けるための運用ルール
管理表は作った日よりも、日々の入力が続くかどうかが重要です。誰が、いつ、どの処理を登録し、どのタイミングで差異を確認するのかを決めます。担当者の経験だけに任せず、簡潔な手順書と確認項目を用意すると、休暇や異動があっても業務を止めにくくなります。
日次で行うこと
入庫と出庫が発生した日は、原則として当日中に履歴へ登録します。まとめて後日入力すると、実在庫との差が出た日を特定できず、伝票や納品書を探す作業が増えます。入力担当者は、伝票番号、商品コード、数量、区分、拠点を確認し、登録後に在庫一覧の数量が業務の認識と合っているかを確認します。
出荷が先に行われる場合や、現場で紙を使う場合は、仮登録の扱いを決めます。「未入力のままにしない」「仮登録は当日中に確定する」「仮登録を一覧で抽出する」など、例外を管理表上で見えるようにします。口頭で伝えただけの入出庫は、後から追跡できないため正式な在庫取引として扱わないルールにします。
週次・月次で行うこと
週次では、発注点を下回った商品、負の在庫、長期間動いていない商品、未確定の仮登録を確認します。数値が異常に見える行を放置せず、入出庫履歴、伝票、現場の実物を順に照合します。月次では、棚卸しの対象範囲と基準日を決め、帳簿上の数量と実在庫を比較します。
棚卸し差異が出た場合、現在庫のセルを直接書き換えず、「棚卸し増」または「棚卸し減」の履歴を追加します。差異数量、確認者、理由を残せば、次回の棚卸しで同じ場所や商品を重点的に確認できます。原因が分からないまま調整だけを繰り返すと、差異が見えなくなるため、調整区分と通常の入出庫区分を分けます。
担当者と権限を決める
入力担当者、承認担当者、マスタ管理者を分けると、誤入力の発見と修正がしやすくなります。人数が少ない場合も、入力後に別の人が数量と伝票を確認する時間を決めておくと、同じ人が入力から承認まで行うことによる見落としを抑えられます。誰がどの拠点の在庫を更新するか、統合ファイルへ反映する時刻はいつかも、運用ルールに含めます。
複数人が同時に入力する場合は、ファイルを共有する方法と、各担当が別ファイルへ入力して統合する方法のどちらが安全かを検討します。別ファイル方式は衝突を避けやすい一方、統合漏れが起きます。共有方式は一つの正本を保ちやすい一方、同時編集や通信状態、権限設定の確認が必要です。実際の人数と利用環境に合わせ、試行期間を設けて決めます。
発注点を使う運用では、現在庫だけでなく発注済み数量や入荷予定も確認します。発注済みを考慮せずに発注すると、同じ商品を重複して注文する可能性があります。発注管理まで一体で整えたい場合は、在庫管理と発注管理を連携する方法で、発注点、安全在庫、リードタイムの整理方法を確認できます。
Excelの在庫管理で起きやすい問題
Excelは柔軟ですが、運用範囲が広がると構造上の負担が増えます。問題が発生してから式を継ぎ足すのではなく、どの状態で改善策を見直すかを事前に決めておくと、現場が混乱しにくくなります。
ファイルが増え、正本が分からなくなる
拠点や担当者ごとにコピーを作ると、それぞれの数字は更新されても全体を集計する時点で差が生まれます。「本部集計用」「倉庫用」「作業用」など複数のファイルがある場合は、どのファイルが入力元で、どのファイルが参照用かを明確にします。ファイル名に日付を付けるだけでは正本の判別ができないため、保存場所、編集者、確定時刻を管理します。
同時編集と権限管理が難しくなる
入力者が増えると、保存の競合や誤上書き、保護解除パスワードの共有といった問題が起きます。クラウド上のExcelを使う場合でも、同時編集できることと、在庫の整合性が保てることは別です。複数人が同じ商品を同時に出庫する、承認前の取引を販売可能数に含める、といった業務には、処理の順序や確定状態を管理する仕組みが必要になります。
履歴が多くなり、集計や引き継ぎに時間がかかる
履歴が増えると、シートを開く時間、式の再計算、バックアップ容量、検索の負担が増します。行数だけで直ちに移行を決めるのではなく、月次集計にかかる時間、差異の調査に必要な時間、担当者が式を修正できるかを確認します。商品・拠点・ロット・有効期限などの条件が増え、関数を追加しても誰も検証できない状態になったら、仕組みの見直しを検討する段階です。
複数の販売チャネルや倉庫をまたぐ場合は、Excel単体で連携すること自体が負担になることがあります。販売、受注、倉庫の数字を一つの流れで扱う考え方は、EC在庫管理とは何かの記事でも整理しています。
Excelから在庫管理システムへ移行する判断基準
Excelを使っているからすぐにシステムへ移行すべき、ということではありません。商品数、拠点数、入力者数、取引量、求める履歴の細かさ、他システムとの連携、停止できない業務の範囲を整理し、Excelの改善で解消できる問題と、仕組みの変更が必要な問題を分けます。
| 確認項目 | Excelを改善しやすい状態 | システム化を検討したい状態 |
|---|---|---|
| 利用人数 | 少人数で入力担当が決まっている | 複数拠点の担当者が同時に更新する |
| 業務範囲 | 入出庫と棚卸しが中心 | 受注、発注、出荷、返品、移動を連携する |
| 履歴 | 伝票単位の履歴で足りる | 承認、取消、状態変更を追跡する必要がある |
| データ連携 | 手動のCSV取込で対応できる | 販売・会計・生産などと継続的に連携する |
| 運用負担 | 管理者が式と設定を確認できる | 担当者の退職時に仕組みが維持できない |
移行を考えるときは、現在のExcelをそのままシステムへ移すのではなく、不要な列、重複した商品コード、使われていない区分、手作業で補正している箇所を整理します。まず一つの拠点や一つの業務で試し、在庫の定義と操作手順が固まってから対象を広げます。既存のExcelやAccessの構造を調べて、修正と再構築のどちらが適切かを検討する場合は、Access在庫管理の限界と移行方法も判断材料になります。
移行前に整理するデータ
移行前には商品マスタ、拠点・保管場所、現在庫、未処理の入出庫、発注残、取引先、単位換算を一覧にします。商品コードが重複している場合は、新しいコードへ統合する対応表を作ります。過去の履歴をすべて移行するか、一定期間だけ移行して旧ファイルを参照用に保存するかも決めます。移行後に過去の数字を追跡する必要がある業務では、旧データの保存場所と参照権限を残します。
現行表の数式や色分けが、実は業務ルールを表していることもあります。担当者しか意味を説明できない列を残すと、移行後に判断が再現できません。列ごとに「何のためにあるか」「誰が入力するか」「どの処理で使うか」を確認し、不要な情報は整理しながら、必要なルールは仕様へ置き換えます。
Excel在庫管理を定着させる手順
最初から完璧な管理表を作ろうとすると、入力項目が増えて現場で使われなくなることがあります。次の順番で小さく整え、運用しながら見直します。
- 対象を決める。管理する商品、拠点、入出庫の範囲を決め、対象外の業務を明記します。
- 用語と単位を決める。現在庫、販売可能数、引当数、棚卸し差異の意味をそろえます。
- 商品マスタを整える。重複コードや表記ゆれを整理し、無効商品の扱いを決めます。
- 履歴入力を始める。一行一取引のルールで、区分、数量、伝票番号、担当者を残します。
- 集計式を検証する。入庫一件、出庫一件、返品、移動、棚卸し調整を実際に入力し、結果を実物や伝票と照合します。
- 例外を記録する。未登録、負の在庫、入力遅れ、数量差異を一覧にし、対応者と期限を決めます。
- 定期的に見直す。発注点、区分、権限、バックアップ、保存場所を業務の変化に合わせて更新します。
検証では、通常の取引だけでなく、取消、返品、拠点間移動、部分出荷、棚卸し差異のような例外を試します。式が合っていても例外処理の登録方法が決まっていなければ、実務では数字がずれます。テスト用のコピーを使い、実データを壊さないように検証結果と修正内容を残します。
よくある質問
Excelだけで在庫管理を始めても問題ありませんか？
商品数、拠点数、入力者が限られ、入出庫の流れを明確にできるなら、Excelから始める方法はあります。現在庫の直接入力ではなく履歴から集計する構成にし、バックアップと棚卸しのルールを決めてください。複数人が同時に更新する、受注や発注と連携する、承認履歴を残すといった要件が増えたら、Excelの改善だけで対応できるかを見直します。
現在庫を直接書き換えてはいけないのですか？
直接書き換えると、変更前の数量、変更理由、変更者を追跡しにくくなります。棚卸し差異や誤入力を直す場合も、調整区分の履歴を追加して現在庫を計算する方が、後から確認しやすい構成です。緊急時に直接修正する場合は、修正前後と理由を変更履歴へ記録し、承認者を決めてください。
SUMIFSの結果が実際の在庫と合わないときは、何を確認しますか？
まず基準日時点をそろえ、入出庫履歴に登録漏れがないかを確認します。次に、商品コードの余分な空白、区分の表記ゆれ、数量の単位、拠点条件、返品や棚卸し調整の扱いを確認します。履歴と伝票を一行ずつ照合し、原因を直した後に再計算します。現在庫セルを合わせるだけでは、次の集計でも同じ差異が起きます。
どのタイミングで在庫管理システムを検討すべきですか？
ファイルの正本が分からない、複数拠点の数字が合わない、入力や集計に時間がかかる、担当者しか式を直せない、販売や発注との二重入力が増えた、といった状態が続く場合は検討のタイミングです。商品数だけで判断せず、必要な履歴、同時利用、連携先、運用を止められる時間を整理して、段階導入の範囲を決めます。
まとめ
Excelで在庫管理をするなら、商品マスタ、入出庫履歴、在庫一覧を分け、履歴から現在庫を計算する構成から始めます。商品コードと単位をそろえ、区分や拠点を入力規則で選べるようにすると、表記ゆれを抑えられます。SUMIFSで入庫と出庫を集計し、IFで発注点を判定するだけでも、現在庫を直接書き換える運用から一歩進められます。
続けるためには、当日入力、棚卸し差異の調整履歴、バックアップ、権限、例外の確認をルールにします。拠点や利用者が増え、受注・発注・出荷との連携や承認履歴が必要になったら、現在の表で何が負担になっているかを整理してからシステム化の範囲を決めます。Excelを使う期間にも、将来移行できるように商品コードと取引履歴を整えておくことが、データを活かす準備になります。
