実地棚卸と帳簿在庫の差異調整をエクセルで行う方法と原因特定の限界

実地棚卸と帳簿在庫の差異は、品目コードをキーに実地棚卸表と帳簿在庫表を突合すれば、原因の大半をエクセルだけで特定できます。ただし品目数や拠点が増えると、突合そのものに時間が溶ける限界が来ます。手順と原因の切り分け方、限界が来るサインまで実務目線でまとめます。

「棚卸差異」で検索する人の多くは、差異が出たこと自体より、「なぜ出たのか毎回一から調べ直している」ことに疲れています。原因を特定しないまま帳簿を実数に合わせて終わらせると、翌期も同じ差異が繰り返されるだけです。私たちが在庫を抱える中小企業から相談を受ける際も、実地棚卸のたびに差異行を1件ずつ目視で追いかけ、数日がかりになっているケースをよく見ます。この記事では、エクセルでの突合手順と原因の切り分け方に加えて、その先にある「エクセルでは苦しくなるタイミング」まで踏み込みます。

帳簿の数字と実地の現物は、そもそもズレて当たり前。まず全体像から押さえる。

実地棚卸と帳簿在庫の差異が生まれる基本の仕組み

差異は「記録起因（伝票のタイミングのズレ）」と「現物起因（実際の数量の変化）」の2種類に大別できます。この2つを最初に切り分けるだけで、調べる場所がまったく変わります。

帳簿在庫は「前回の実地棚卸数 ± 入出庫伝票の積み上げ」で計算される理論値です。一方、実地棚卸は特定の日に実際に数えた実数です。この2つが一致しないのは当然で、差異そのものは異常ではありません。問題は、差異が「どちらの理由で」発生したかを切り分けずに、毎回すべての差異行を同じ重さで疑ってしまうことです。

記録起因の代表例は、入荷検収の計上が翌日にずれ込む、出荷実績の入力が締め後になる、返品の戻し忘れといったタイミングのズレです。これらは在庫が動いた「事実」自体は正しく、帳簿への反映タイミングだけが実地棚卸日とズレているケースです。現物起因は、検品ミスでの数え間違い、保管中の破損、盗難や紛失など、実際にモノの数が変わったケースです。

差異は「記録起因」か「現物起因」かで、調べる場所がまったく変わる。

実地棚卸表と帳簿在庫表を突合する実務手順（エクセル）

品目コードをキーにXLOOKUP（またはVLOOKUP）で両表を突合し、差異列と差異率を自動計算するのが基本形です。

具体例で手順を示します。前提はすべてここで開示します。ある卸売業のB5ノート（品目コードNB-105）について、帳簿在庫は480冊、実地棚卸で数えた実数は462冊でした。差異は「実地462冊 − 帳簿480冊 = −18冊」、差異率は「18冊 ÷ 帳簿480冊 = 3.75%」です。電卓で18÷480を計算しても同じ数字になります。この時点ではまだ原因は分かりません。次の見出しで原因側を切り分けます。

手順はシンプルです。①品目マスタ（品目コード・品名）を1シートに整備する。②実地棚卸シートに、その日数えた実数を品目コードとひも付けて入力する。③帳簿在庫表（在庫管理システムやレジのエクスポート）を別シートに置く。④突合用のシートで、品目コードをキーにXLOOKUPで帳簿在庫を呼び出し、「実地棚卸数−帳簿在庫」で差異列を作る。⑤差異率列を追加し、条件付き書式で一定率（たとえば2%）を超えた行だけ色をつける。この⑤があるだけで、数百行から異常行だけを一瞬で絞り込めます。

差異率は「実地−帳簿」を帳簿で割るだけ。電卓でも検算できる。

棚卸差異の原因を特定する4つの切り口

差異原因は入力ミス・検品漏れ・出荷計上漏れ・ロス（破損盗難）の4つに集約され、切り口ごとに調べる場所が異なります。

差異行を1件ずつ「なぜ起きたか」から考えると時間が溶けます。先に4つの切り口を用意し、どれに当てはまるかを機械的に振り分けたほうが速いです。

ピボットテーブルで「担当者別」「品目カテゴリ別」「入出荷日別」に差異を集計すると、特定の切り口に偏りが出ることがほとんどです。特定の担当者が入力した品目にだけ差異が集中していれば入力ミスの可能性が高く、特定の日以降に差異が急増していれば計上ルールの変更やシステム切り替えが疑われます。全件を横並びで見るのではなく、まず「どの軸で偏っているか」を先に見つけるのが原因特定を速める近道です。

原因を4つの型に振り分けてから調べると、現場確認の件数が減る。

原因特定にエクセルで時間がかかる理由と限界が表面化するタイミング

SKU数や拠点が増えると、突合と消し込みに要する時間が品目数に比例以上に増えるのがエクセル運用の構造的な限界です。

実際に在庫管理の相談を受けた卸売業では、四半期ごとの実地棚卸のたびに担当者1名が3日近くかけて差異行を1件ずつ追いかけていました。あとから原因を分類してみると、実に6割ほどが単純な計上タイミングのズレで、現物を疑う必要すらないケースでした。全件を同じ重さで疑って現場に確認を取りに行っていたことが、時間がかかっていた最大の要因だったのです。原因の型を先に切り分けるだけで、この手戻りの多くは防げます。

とはいえ、型を切り分けても限界は来ます。品目数が数百SKUを超え、拠点や倉庫が複数に分かれると、突合シートは1枚では収まらなくなり、拠点ごとにファイルが分かれて「最新版はどれ」問題が起きます。XLOOKUPも数千行を超えると再計算が重くなり、ファイルを開くだけで時間がかかるようになります。さらに、差異の履歴（いつ・誰が・どの原因で調整したか）を残そうとすると、シートがどんどん複雑化し、結局は突合作業に詳しい1人しか触れないブラックボックスになりがちです。この属人化が進んだ状態こそ、エクセル運用が限界に近づいているサインです。

自社の実地棚卸がこの状態に近づいているかどうかは、直近の棚卸にかかった時間と担当者の人数を数えるだけである程度分かります。判断に迷う場合は、初月無料の経営AI診断（通常30万円相当）で、今の突合作業のどこに時間が偏っているかを一緒に可視化し、エクセル改善で足りるのか仕組みを変えるべきかまで整理できます。

差異行を1件ずつ疑う作業が、そのままエクセル運用の時間コストになる。

差異調整を早める実務のコツ（今日からできる3つ）

「差異の記録を残す」「ピボットで偏りを見る」「条件付き書式で異常だけ表示する」の3つで、原因特定の体感時間は大きく縮まります。

1つ目は、差異が出た品目に「日付・担当者・原因分類」を記録するルールを作ることです。原因を記録せず調整だけして終わらせると、次回また同じ調査を1からやり直すことになります。2つ目は、月次や四半期ごとにその記録をピボットテーブルで集計し、担当者別・原因分類別の偏りを確認する習慣です。3つ目は、差異率が一定のしきい値（たとえば2%）を超えた行だけ条件付き書式で目立たせ、担当者が「全部見る」のではなく「色がついた行だけ見る」状態を作ることです。

この3つはいずれも今のエクセルファイルに手を加えるだけで始められ、大掛かりなシステム導入は不要です。ただし、この工夫を積み重ねても品目数や拠点数の増加には限界があります。まずは自社の実地棚卸にかかっている時間と、差異原因の記録が残っているかどうかを棚卸ししてみるところから始めてください。

記録・集計・可視化の3つだけで、原因特定の体感時間は大きく縮む。

まとめ

実地棚卸と帳簿在庫の差異は、品目コードでの突合と、記録起因・現物起因という2つの切り口を用意するだけで、原因特定にかかる時間を大きく減らせます。差異行を1件ずつ疑うのではなく、日付・担当者・品目という3項目を記録し、ピボットテーブルと条件付き書式で偏りを機械的に絞り込む。これがエクセルでできる現実的な最善手です。それでも品目数や拠点が増え、突合そのものに時間が溶けるようになったら、それはエクセルの構造的な限界のサインです。自社の状況を客観的に整理したいときは、初月無料の経営AI診断（通常30万円相当）で、差異調整のどこに時間が偏っているかを一緒に可視化することから始められます。

関連記事

• 棚卸表をエクセルで作る方法 卸売・小売の実務テンプレと自動集計のコツ — 関連: 実地棚卸表そのものの作り方
• 在庫評価を先入先出法と移動平均法でエクセル計算する方法と限界 — 関連: 棚卸後の評価額計算
• 在庫回転率・滞留在庫をエクセルで分析する方法　発注点と欠品を防ぐ実務 — 関連: 差異データの活用先
• 在庫管理をエクセルで自動化する方法　関数とマクロの実装と限界 — 関連: 突合作業の自動化の発展形
• 在庫管理システムの費用相場と選び方 失敗しない脱エクセルの判断基準 — 関連: エクセルの限界を超えた次の一歩