小売店の在庫管理をエクセルでやる方法｜発注点・棚卸の実務設計と限界

結論：発注点管理とABC分析を組めば中小小売でも実用に耐える

小売の在庫管理エクセルは、発注点の自動判定とABC分析による優先順位づけをセットで組み込めば、1〜2店舗・SKU数300〜500程度までは実用に耐える。欠品・売れ残り・複数店舗の在庫移動という小売特有の3課題を、関数の設計でどこまで潰せるかが分かれ目になる。

小売の在庫管理相談で最も多いのが「エクセルは限界だと思うが、何が限界で何がまだやれるのか切り分けられていない」というものだ。SaaS営業に相談すると当然「乗り換えましょう」という話になるが、実際に現場のエクセルを見ると、発注点のロジックが入っていないだけで、構造自体は悪くないケースが大半だった。

小売特有の事情は3つある。SKU数がアパレルや雑貨だと数百〜数千に及ぶこと、天候・季節・催事で需要が振れ欠品と売れ残りが同時に起きること、複数店舗を持つと在庫が分散し「どの店に何がいくつあるか」の可視化が単店より一段難しくなることだ。この3つを踏まえた実装設計を、以下で具体的に見ていく。

発注点管理：IF関数で「発注すべきSKU」を自動抽出する

発注点=1日あたり平均販売数×リードタイム(日)+安全在庫。この数式をエクセルに組み込み、現在庫が発注点を下回ったSKUをIF関数で自動抽出すれば、担当者が全SKUを目視確認する必要がなくなる。

具体的な列構成は、SKU・商品名・現在庫数・直近30日販売数(自動計算)・1日平均販売数・リードタイム・安全在庫・発注点・発注要否、の9列で組む。1日平均販売数は=直近30日販売数/30、発注点は=1日平均販売数*リードタイム+安全在庫、発注要否は=IF(現在庫数<=発注点,"発注","-")という3本の数式で足りる。

ここでつまずくのが安全在庫の設定だ。全SKU一律にすると、動きの遅い商品まで過剰に確保してしまい在庫金額が膨らむ。実際にある雑貨店の案件では、全SKU一律「7日分」で安全在庫を設定していたため、年に数回しか動かない商品まで常時7日分の在庫を抱え、在庫金額の2割が実質死蔵在庫になっていた。ABC分析で優先度を分けたうえで、A商品は7日分、C商品は3日分と差をつけるだけで、この無駄はかなり削れる。

もう1つの実務ポイントは、リードタイムを仕入先ごとに別列で管理すること。同じ商品でも仕入先を切り替えるとリードタイムが変わるため、リードタイムを固定値で埋め込むと発注点がずれる。仕入先マスタを別シートに置き、VLOOKUPで引く構成にしておくと、仕入先変更時の修正が1箇所で済む。

無料相談を受けた自社の経営AI診断の場では、発注点の数式そのものより「安全在庫の日数を誰がどう決めたか根拠がない」という相談が多い。数式は組めても、その前提となる需要変動の把握が抜けているケースが実際には大半を占める。

ABC分析：優先順位をつけないと在庫改善は進まない

ABC分析は累計販売金額の上位70%をA、次の20%をB、残りをCに分類する。分類自体はSUMIFSとRANK関数で半日程度で組めるが、分類後にA商品だけ発注点を週次で見直す運用ルールを作らないと、分析しただけで終わる。

エクセルでの実装は、まずSKU別の月間販売金額を=SUMIFS(売上シート!金額列,売上シート!SKU列,対象SKU)で集計し、金額の降順で並べ替えて累計構成比を計算、70%までがA・90%までがB・残りがCという構成比のしきい値でランク付けする。IFS関数を使えば=IFS(累計構成比<=70%,"A",累計構成比<=90%,"B",TRUE,"C")の1本で分類できる。

ABC分析は「分類して終わり」にする担当者が非常に多い。分類後の運用として機能させるには、Aランクは週次で発注点を見直す、Bランクは月次、Cランクは季節の変わり目のみ確認する、という頻度差をルール化することが不可欠だ。分類だけして頻度を変えなければ、結局全SKUを毎回同じ手間で見ることになり、分析の意味がなくなる。

複数店舗を持つ場合は、店舗ごとにABC分類が変わる点にも注意したい。全社合計では死に筋(C)でも、特定店舗では主力(A)という商品は珍しくない。全店合算でABC分析をすると、この店舗差が消えて発注判断を誤らせる。店舗別シートで分類し、本部の一覧シートに集約する2階層構成にすることで、この誤判定を防げる。

複数店舗の在庫可視化：シート分割と集計シートの二層構造

複数店舗展開の在庫管理エクセルは、店舗別シート＋全店集約シートの二層構造で組む。1シートに全店舗を並べる構成は、店舗数が3を超えたあたりから更新漏れと二重入力が急増する。

店舗別シートでは各店舗の在庫担当者が自店分の現在庫数だけを更新し、全店集約シートはSUMIFで各店舗シートの数値を自動集計する構成にする。これにより、店舗担当者は自分の店舗のことだけ考えればよく、本部側は集約シートを見るだけで全社の在庫状況を把握できる。

店舗間の在庫移動が発生した場合の記録も重要だ。移動元・移動先・SKU・数量・日付を記録する専用シートを1枚用意し、各店舗の在庫数式に移動分を反映させる。この移動記録シートを作らずに現在庫数を手動で書き換えていると、後から棚卸したときに数字が合わなくなり、原因を追えなくなる。これが小売の在庫エクセルで最も崩れやすいポイントだ。

店舗数が増えるとシート間の参照式が重くなり、ファイルを開くたびに数秒〜数十秒の再計算待ちが発生するようになる。体感としては店舗数5〜6・SKU数500を超えたあたりから動作が重くなり始めるケースが多く、これはエクセルという道具そのものの限界であり、関数設計の工夫だけでは解決しない。

棚卸の実務設計：差異チェックを自動化する

棚卸はエクセルシートに理論在庫と実地在庫の2列を用意し、差異列で自動比較する。差異が一定数量を超えたSKUだけを抽出すれば、全SKUを目視確認する手間を省ける。

理論在庫は発注点管理シートの現在庫数をそのまま参照し、実地在庫は棚卸当日にカウントした数値を手入力する。差異列は=実地在庫-理論在庫で計算し、条件付き書式で差異がゼロでない行を色付けすれば、確認すべきSKUが一目で分かる。差異が大きいSKU(目安として理論在庫の10%以上)だけをリストアップし、原因(検品漏れ・レジ入力ミス・万引き・移動記録漏れ)を突き合わせる運用にすると、棚卸1回あたりの作業時間を大きく圧縮できる。

棚卸の頻度は、A商品は月次、B・C商品は四半期という差をつけている小売店が多い。全SKUを毎回同じ頻度で棚卸するのは工数的に非現実的であり、ここでもABC分析の分類結果がそのまま棚卸計画に使える。分析を1度組めば発注・棚卸・在庫確認の3工程で使い回せる点が、エクセル運用の効率化の核になる。

まとめ：自社最適化の視点でエクセルの限界を見極める

小売の在庫管理エクセルは、発注点管理・ABC分析・二層構造の店舗集約・棚卸差異チェックの4点を組み込めば、中小規模であれば十分に実務で機能する。乗り換えを検討する前に、まずこの4点が自社のエクセルに入っているかを確認する価値は大きい。

一方で、店舗数・SKU数が一定規模を超えると、シート参照の重さや二重入力のリスクがエクセルという道具の限界として顕在化する。この境界線は業種・商材によって異なり、汎用のSaaS導入がベストとは限らない。自社の発注ロジックや店舗運用の癖に合わせてどこを自動化すべきかを見極めたい場合は、初月無料の経営AI診断(通常30万円相当)で現状の業務を可視化し、どこにAIを組み込むと効果が出るか具体的な改善提案までご一緒することもできる。

よくある質問

小売店の在庫管理はエクセルで何店舗まで対応できますか？

目安は1〜2店舗・SKU数300〜500程度まで。それ以上になると発注点の再計算漏れ・店舗間の在庫移動の突合作業が手作業の限界を超え、欠品と過剰在庫が同時発生しやすくなる。店舗数よりSKU数×更新頻度の掛け算で判断するのが実務的。

発注点はどうやってエクセルで計算すればいいですか？

発注点=1日あたり平均販売数×リードタイム(日)+安全在庫で計算する。安全在庫は欠品許容度に応じて平均販売数の3〜7日分を目安に設定し、IF関数で在庫数が発注点を下回ったSKUを自動で赤字表示させると発見漏れを防げる。季節変動が大きい商品は月別平均で分けて管理する。

ABC分析はどの程度の手間で小売の在庫管理に組み込めますか？

累計販売金額をSUMIFSとRANK関数で算出し、上位70%をA、次の20%をB、残りをCに分類する作業自体は半日程度で組める。ただし分類後にA商品だけ発注点を週次で見直す運用ルールを作らないと、分析しただけで終わり在庫改善につながらないケースが多い。

エクセルの在庫管理をやめるべきタイミングの見極め方は？

店舗間の在庫移動が週3回以上発生する、棚卸の突合に半日以上かかる、発注担当者が不在だと発注が止まる、のいずれか2つに当てはまったら移行を検討する時期。SaaS導入前に、まず自社の発注ロジックをエクセルで一度言語化しておくと、システム選定時の要件定義が具体的になり遠回りが減る。

関連記事

• 在庫管理をエクセルで自動化する方法｜関数・マクロの限界と脱エクセルの判断 — 関連: 自動化の関数設計と限界点
• 棚卸表をエクセルで作る方法｜卸売・小売の実務テンプレと自動集計のコツ — 関連: 棚卸シートの実装
• 在庫管理表エクセルの無料テンプレート｜業種別の選び方と自作のコツ — 関連: テンプレート選定の視点
• 在庫管理エクセルのマクロ化はどこまで有効か｜VBAの限界と脱エクセルの分岐点 — 関連: マクロ化の適用範囲
• 中小企業向け AI 導入の初期費用と相場感 — 関連: エクセル運用の次に検討するAI導入コストの目安