棚卸表をエクセルで作る方法 卸売・小売の実務テンプレと自動集計のコツ

棚卸表はエクセルで十分に運用できます。品目マスタと集計シートを分離し差異を自動計算する型を作れば、手入力ミスも集計の手間も大きく減らせます。

棚卸表に最低限必要な項目とシート構成

棚卸表は「品目マスタ」「実地棚卸記録」「差異集計」の3シートに分けると、入力ミスが減り自動集計もしやすくなります。

多くの中小卸売・小売業の現場で見かけるのは、1枚のシートに品目名・単価・在庫数・棚卸メモまで全部詰め込んだ棚卸表です。一見シンプルですが、行の挿入や削除のたびにSUM関数の参照範囲がズレたり、前月の数字を上書きして復元できなくなったりする事故が起きやすくなります。品目の基本情報（マスタ）と、月ごとに変わる数値（実地棚卸記録）を分けておくだけで、この手のトラブルはかなり防げます。

具体的には、品目マスタには品目コード・品目名・単位・帳簿在庫数・単価の列を用意します。実地棚卸記録には品目コード・実地棚卸数・棚卸日・担当者を入力する列だけを置き、差異集計シートで両方をVLOOKUPまたはXLOOKUPで突き合わせて、差異数・差異金額・差異率を自動計算する構成にします。品目コードを軸にした3シート構成は、業種を問わず在庫管理棚卸表の基本形として応用が利きます。

列構成の目安は次の通りです。

• 品目マスタ: 品目コード／品目名／カテゴリ／単位／帳簿在庫数／単価
• 実地棚卸記録: 品目コード／実地棚卸数／棚卸日／担当エリア／担当者
• 差異集計: 品目コード（VLOOKUP参照）／差異数／差異率／差異金額／要確認フラグ

この列構成さえ決めてしまえば、あとは毎月同じシートを複製するだけで運用できます。逆に、この初期設計を飛ばして「とりあえず1枚のシートで始める」と、品目数が増えたタイミングで列の並び替えや式の貼り直しが発生し、かえって手間が増えます。

エクセルでの棚卸表の作り方（実務テンプレ手順）

テンプレは「品目マスタ→棚卸記録→差異集計」の順で作ると、翌月以降も同じフォーマットを使い回せます。

まず品目マスタを一度だけ丁寧に作り込みます。品目コードは重複のない連番か既存のバーコード体系を流用し、品目名は「データの入力規則」でプルダウン選択にしておくと、棚卸当日の入力担当者が手入力で品目名を間違えるミスを防げます。単位（個・箱・kgなど）も同様にプルダウン化しておくと、後の集計で単位違いの品目が紛れ込む事故を避けられます。

次に棚卸記録シートを月ごとに複製して使います。原本の品目マスタシートには直接データを入力せず、必ず「コピーして使う専用シート」を用意するのがポイントです。棚卸当日は紙の帳票やハンディターミナルで数えた実地棚卸数を、この複製シートに入力するだけにしておくと、当日の作業者が構造を意識せずに済み、入力ミスも減ります。最後に差異集計シートで品目コードをキーに品目マスタと棚卸記録を結合すれば、テンプレとしてそのまま使い回せる状態になります。

差異分析を自動化する数式・関数のコツ

差異数はVLOOKUPまたはXLOOKUPと引き算だけで自動化でき、差異率が大きい品目だけを条件付き書式で抽出すれば原因追及が早まります。

差異集計シートの基本式はシンプルです。差異数は「=実地棚卸数-帳簿在庫数」、差異金額は「=差異数×単価」、差異率は「=差異数/帳簿在庫数」で計算できます。この3列を用意したうえで、差異率が±2%を超える行だけ自動的に赤くなるよう条件付き書式を設定しておくと、数百品目あっても目視でチェックすべき行がひと目でわかります。

実際にご相談いただいた卸売業のクライアントでは、月次棚卸のたびに数万円〜数十万円分の在庫差異が発生していましたが、原因が特定できないまま「誤差」として処理され続けていました。差異集計シートを担当者別・棚別にピボットテーブルで集計し直したところ、特定の担当者が受け持つ棚でだけ差異率が突出して高いことが判明し、検品手順そのものに問題があったとわかったケースです。差異を自動計算できる型を作るだけでなく、ピボットテーブルで切り口を変えて集計する習慣をセットにすることで、原因追及のスピードが大きく変わります。

棚卸表運用でよくある失敗と対策

手入力のダブルカウントとシートのコピペ壊れが二大失敗パターンで、入力規則とテンプレ固定で防げます。

よくある失敗の1つ目は、同じ品目を複数の担当者が別々にカウントしてしまうダブルカウントです。倉庫や棚を担当者ごとに明確に分担し、実地棚卸記録シートに「担当エリア」の列を追加して集計時にフィルタできるようにしておくだけで、この種のミスは大幅に減らせます。2つ目は、シートをコピーする際にセルの結合や行挿入で数式が壊れてしまうケースです。テンプレシートは数式を含む列を保護（シート保護機能でロック）しておき、入力担当者が触れるのはデータ入力欄だけに限定するのが安全です。

自社の棚卸フローのどこにボトルネックがあるのか、日々の業務に追われていると自分たちだけでは気づきにくいものです。そうした場合は、初月無料の経営AI診断で在庫管理の工程を一度可視化し、どこにムダや属人化が潜んでいるかを整理してから改善に着手する方法もあります。

エクセル運用の限界を感じたときの判断基準

品目数が数百を超える、拠点が複数ある、リアルタイムの在庫把握が必要になった、のいずれかに当てはまった時点がエクセルからの卒業ラインです。

エクセルでの棚卸表は、品目数が数十〜百程度、拠点が1つ、月次や週次の集計で十分なうちは非常にコストパフォーマンスの良い方法です。一方で、複数拠点でファイルを共有し始めると「誰かが編集中で開けない」「最新版がどれかわからない」といった管理コストが急増します。さらに、EC受注と連動してリアルタイムに在庫数を把握する必要が出てきた場合は、エクセルの手動更新では追いつかなくなります。

こうした兆候が出てきたら、慌ててシステムを入れ替えるのではなく、まず自社の在庫管理フロー全体のどこがボトルネックになっているのかを棚卸しすることが先決です。初月無料の経営AI診断では、現状の棚卸・在庫管理の工程を可視化したうえで、エクセルの改善で十分なのか、部分的な自動化や専用システムへの移行が必要なのかを、実務目線で整理する提案までご一緒しています。

なお、システム移行を検討する場合でも、いきなり全品目・全拠点を対象にする必要はありません。差異率が高く出ている一部のカテゴリだけを先に自動化し、効果を確認してから範囲を広げるという段階的なアプローチのほうが、現場の混乱も導入コストも抑えられます。エクセルで作った差異集計の型は、そのままシステム選定時の要件整理にも使えるため、無駄にはなりません。

関連記事

• Claude Codeで業務自動化 中小企業の導入ステップとROI試算 — 関連: 在庫管理を含む業務自動化の実践ステップ
• EC・小売のAI活用 接客チャット・在庫問い合わせ・レビュー対応 — 関連: 小売・EC現場の在庫関連AI活用事例
• 物流・運送業のAI活用 配車・問い合わせ・伝票処理の自動化 — 関連: 卸売・物流の現場業務自動化
• 町工場・製造業のAI活用 見積・図面対応・問い合わせの自動化 — 関連: 属人業務の可視化と自動化
• 中小企業向けAI導入の初期費用と相場感 — 関連: 業務改善投資のコスト視点

よくある質問

棚卸表はエクセルで無料で作れますか？

作れます。品目マスタ・実地棚卸記録・差異集計の3シート構成にすれば、追加ソフトを使わずにVLOOKUPやXLOOKUP、ピボットテーブルだけで自動集計まで完結します。ただし拠点数や品目数が増えると複数人での同時編集や共有に無理が出やすく、その段階で在庫管理システムへの移行を検討する企業が多いです。

棚卸の差異はどこまで許容すべきですか？

業種にもよりますが、卸売・小売の現場感覚では帳簿在庫数に対して1〜2%以内の差異率であれば誤差の範囲として扱われることが多いです。それを超える差異が特定の品目や担当者に集中している場合は、入力ミスや検品漏れなど原因側の問題を疑い、条件付き書式で自動的に抽出できるようにしておくと早期発見につながります。

棚卸表テンプレを毎月使い回すコツは？

品目マスタのシートを更新用の原本として固定し、実地棚卸記録は月ごとに複製したシートに入力する運用にすることです。原本シートに直接入力すると前月データを上書きしてしまう事故が起きやすいため、ファイル名やシート名に年月を入れてバックアップを残すルールを最初に決めておくと安全です。

棚卸表を手作業からどのタイミングで自動化すべきですか？

品目数が数百点を超える、拠点や倉庫が複数に分かれている、月次ではなく週次・日次でリアルタイムの在庫把握が必要になった、のいずれかに当てはまった時点がエクセル運用の卒業ラインです。ファイル破損や属人化のリスクが顕在化する前に、自社の在庫管理フロー全体を見直すタイミングとして検討することをおすすめします。