ABC分析をExcelで行う方法【在庫・得意先の絞り込みに】

「在庫が多すぎる」「営業がすべての客先を平等に回っている」——リソースの使い方に濃淡がない状態を数字で正すのがABC分析です。売上や粗利の累積構成比で対象をA・B・Cにランク分けし、上位に集中し下位を簡素化する。考え方は単純ですが、Excelでの正しい作り方（と、やりがちな間違い）は意外と知られていません。

この記事では、累積構成比の計算からパレート図の作成、在庫削減・営業リソース配分への活用例までを解説します。

ABC分析の基本

「売上の8割は2割の商品が生む」というパレートの法則を前提に、構成比の累積で3ランクに分けます。しきい値は目的に応じて調整して構いません。

ランク	累積構成比の目安	管理方針の例（在庫の場合）
A	〜70%	重点管理。毎週発注・欠品厳禁・安全在庫を厚く
B	70〜90%	標準管理。定期発注・月次確認
C	90〜100%	簡素管理。まとめ発注・取扱い中止の検討対象

Excelでの作り方

ステップ1：集計と降順ソート

売上明細から商品別（得意先別）の年間売上をSUMIFで集計し、金額の大きい順に並べ替えます。ここが並んでいないと累積構成比が意味を失います。Microsoft 365ならSORT関数で自動化できます。

商品別売上：=SUMIF(明細!商品コード,A2,明細!金額)
自動ソート（365）：=SORT(集計範囲,2,-1)

ステップ2：構成比・累積構成比・ランク判定

構成比：=B2/SUM($B$2:$B$100)
累積構成比：=SUM($B$2:B2)/SUM($B$2:$B$100)
ランク：=IFS(累積構成比<=0.7,"A",累積構成比<=0.9,"B",TRUE,"C")

累積構成比は「先頭から当該行までの合計÷総合計」。ランクはIFSでしきい値判定するだけです。条件付き書式でA=緑・B=黄・C=グレーに塗ると一覧性が上がります。

ステップ3：パレート図を描く

• 「商品名・売上・累積構成比」の3列を選択→挿入→組み合わせグラフ
• 売上=集合縦棒（主軸）、累積構成比=折れ線（第2軸、最大値100%に固定）
• Excel 2016以降なら「挿入→統計グラフ→パレート図」でも一発作成可（細かい書式調整は組み合わせグラフのほうが柔軟）

数値例：在庫600品目のABC分析

ランク	品目数	売上構成比	在庫金額	打ち手
A	85品目（14%）	70%	1,200万円	欠品防止を最優先
B	160品目（27%）	20%	900万円	発注ロットの見直し
C	355品目（59%）	10%	1,100万円	まとめ発注・整理候補

典型的なのは「売上構成比10%のC品目が在庫金額の3分の1を占めている」という発見です。C品目の在庫を半減できれば550万円のキャッシュが生まれます。ABC分析の成果は表ではなく、この「打ち手と金額」まで落とし込んで初めて出ます。滞留在庫の扱いは棚卸記事も参照してください。（関連記事：No.84 実地棚卸の集計表）

活用パターン

• 在庫管理：Aは発注頻度を上げて1回あたりを小さく、Cはまとめ発注で管理コストを削減
• 営業リソース配分：得意先を粗利額でABC分析し、A先への訪問頻度を厚く、C先は電話・メール対応へ切り替え（関連記事：No.83 商品別・得意先別の粗利管理）
• 経理業務の効率化：経費科目や取引先を金額でABC分析し、チェックの濃淡（A=全件確認、C=サンプル確認）を設計する——事務所の月次監査にも応用できます

実務の注意点・つまずきポイント

• 評価軸は目的に合わせる：在庫削減なら「在庫金額」、営業配分なら「粗利額」、欠品防止なら「出荷頻度」。売上だけでランク付けすると薄利多売品を過大評価します。
• C判定＝即廃番ではない：A商品の付属品・補修部品など、C品目でもA商品の販売を支えるものがあります。ランクは機械判定、最終判断は定性情報とセットで。
• 新商品はランク保留にする：発売直後の商品は実績が少なくC判定になりがちです。「発売6か月未満は判定対象外」のフラグを設けましょう。
• 期間は12か月が基本：季節性のある商売で3か月データを使うと、季節商品の評価が歪みます。逆に季節ごとの入替え判断には四半期別の分析を併用します。
• 定期的に更新する：ランクは動きます。四半期〜半期ごとに更新し、B→Aに上がった商品の欠品と、A→Bに落ちた商品の過剰在庫を早期に捕まえてください。

よくある質問

Q. 品目数が少ない会社でも意味がありますか？

20〜30品目程度でも、構成比の偏りを数字で確認する価値はあります。品目が少ない場合は商品ではなく「得意先×商品」の組合せで分析すると、粒度が細かくなり発見が増えます。

Q. ピボットテーブルでもできますか？

できます。値フィールドの設定で「計算の種類→比率の累計」を選ぶと累積構成比が一発で出ます。明細データが毎月増える運用ならピボット版のほうが更新が楽です。（関連記事：No.38 ピボットテーブルの経理活用）

Q. AとBの境目（70%・90%）に根拠はありますか？

法則的な必然はなく、経験則上の慣行です。重要なのはしきい値の精密さではなく、ランクごとに管理方法を実際に変えること。品目数が極端に偏る場合は「A=上位20品目」のように品目数で切る方法も実務的です。しきい値セルをマスタ化しておけば、自社に合う区切りを試しながら調整できます。

ABC分析テンプレートのご案内

明細貼り付け→自動ソート→累積構成比・ランク判定→パレート図・ランク別サマリーまで自動生成するABC分析Excelテンプレートをご用意しています。評価軸（売上/粗利/在庫金額/出荷頻度）の切替と新商品の判定除外フラグも組み込み済みです。

まとめ

• ABC分析は降順ソート→構成比→累積構成比→しきい値判定の4手順で作れる
• パレート図は縦棒＋第2軸の折れ線（累積構成比）の組み合わせグラフで描く
• 評価軸は目的で変える。在庫は在庫金額、営業は粗利額が基本
• C判定=即廃番ではなく、補完関係・新商品などの定性判断を残す
• 四半期〜半期で更新し、ランク変動を欠品・過剰在庫の早期警報として使う