【図解】ABC分析をエクセル(Excel)で簡単に行う方法！

そもそもABC分析とは？簡単な復習

具体的なエクセルの操作に入る前に、ABC分析の基本を簡単におさらいしておきましょう。

ABC分析とは、「パレートの法則（80:20の法則）」を応用した分析手法です。

例えば、「売上の8割は、全商品の2割が生み出している」といった傾向を可視化し、商品を以下の3つのクラスに分類して管理の優先順位をつけます。

• Aクラス（重要）： 売上や在庫金額の累積比率が約70%〜80%までの品目。最優先で管理すべき。

• Bクラス（普通）： 累積比率が80%〜90%程度の品目。

• Cクラス（下位）： 残りの下位品目。

このランク付けを行うためには、データを並び替えて「累積比率」を計算する必要があります。これらはエクセルが得意とする作業です。

より詳しいABC分析の理論やパレート図との関係については、以下の記事でも解説しています。
ABC分析とパレート図の関係とは？作り方から分析のコツまで徹底解説

【実践】エクセルでABC分析表を作る5つのステップ

それでは、実際にエクセルを使ってABC分析表を作成してみましょう。ここでは「商品ごとの売上データ」を例に進めます。

[今回の例として使用する元データ]

STEP1：データを準備して売上順に並べ替える（降順）

まずは、商品名と売上金額が入力されたデータを用意します。

1. データ範囲を選択します。

2. 「データ」タブにある「並べ替え」をクリックします。

3. 「最優先されるキー」を「売上金額」、「順序」を「降順（大きい順）」にしてOKを押します。

これで、売上が高い商品が一番上に来るようにデータが整理されました。

STEP2：合計金額を計算する（SUM関数）

次に、売上の合計金額を算出します。これは構成比を出すために必要です。
表の一番下に「合計」行を作り、SUM関数を入力します。

=SUM(B2:B11)

STEP3：構成比と累積比率を計算する

ここがABC分析の肝となる部分です。

1. 構成比（％）
その商品の売上が、全体の中でどれくらいの割合かを出します。
= 個別の売上 / 合計売上 で計算できます。
※計算式を下にコピーする際、合計売上のセルがずれないように「絶対参照（$マーク）」を使うのがポイントです（例：=B2/$B$12）。

2. 累積比率（％）
上位の商品から順に、構成比を足し合わせていった数値です。

• 1番目の商品：その商品の構成比と同じ

• 2番目以降の商品：= ひとつ上の商品の累積比率 + その商品の構成比

累積比率について詳しく知りたい方は、こちらの解説も参考にしてください。
パレート図の「累積比率」とは？見方と計算方法、グラフの活用法をわかりやすく解説

STEP4：ABCのランクを判定する（IF関数）

累積比率が出たら、いよいよランク付けです。目視で入力しても良いですが、データが多い場合はIF関数を使うと自動で判定できます。

一般的な基準（A：〜70%、B：〜90%、C：それ以降）で設定する場合、以下の数式を「ランク」列に入力します。

=IF(D2<=0.7,"A",IF(E2<=0.9,"B","C"))

※D2セルに累積比率が入っている場合
これで、自動的にA、B、Cの文字が表示されます。

STEP5：パレート図を作成して可視化する

表ができたら、視覚的にわかりやすくするために「パレート図（棒グラフと折れ線グラフの複合グラフ）」を作成しましょう。

【最新のExcel（2016以降）を使っている場合】

1. 「商品名」と「売上金額」の列を選択します。

2. 「挿入」タブ ＞ 「統計グラフの挿入（ヒストグラムのアイコン）」をクリック。

3. 「パレート図」を選択します。

これだけで、自動的に累積比率線が入ったパレート図が作成されます。

【古いExcelや、より細かくカスタマイズしたい場合】
「商品名」「売上金額」「累積比率」の3列を選択し、複合グラフ（棒グラフ＋第2軸の折れ線グラフ）として作成する必要があります。

詳しい手順については、以下の記事で「完全ガイド」としてまとめていますので、ぜひ併せてご覧ください。
Excel（エクセル）でのパレート図の作り方を完全ガイド！

エクセルより簡単！「xGrapher」なら3ステップで完了

ここまでエクセルでの作成方法をご紹介しましたが、「数式を入れるのが面倒」「グラフの軸調整がうまくいかない」「もっときれいなグラフを資料に使いたい」と感じた方もいるかもしれません。

そんな方におすすめなのが、無料で使えるオンライングラフ作成ツール「xGrapher」です。

xGrapherを使えば、面倒な関数入力やグラフ設定は一切不要。データを貼り付けるだけで、プレゼン資料にもそのまま使える美しいパレート図やABC分析の結果が得られます。

xGrapherでの作成手順

1. xGrapherのパレート図作成ツールにアクセスします。

2. エクセルのデータをコピーして、ツールの入力欄にペーストします。（並べ替えは自動）

3. 「並べ替え」ボタンを押すとグラフに反映されます。

4. 「ABC分析ハイライト」にチェックを入れると簡単に色付けまで可能です。

これだけで、自動的に累積比率が計算され、色分けされた見やすいグラフが完成します。画像としてのダウンロードもワンクリックです。

▼ 面倒な設定なしで今すぐパレート図を作る
xGrapherでパレート図を作成する（無料）

ABC分析の結果をどう活用する？

表やグラフが完成したら、そこからアクションプランを考えることが重要です。

Aクラス（最重要品目）

全体の売上の大部分を占める重要な商品です。

• 対策： 欠品は許されません。在庫を厚めに持ち、発注頻度を高めて細かく管理します。品質管理や販売促進のリソースもここに集中させます。

Bクラス（準重要品目）

Aクラスに次ぐ商品群です。

• 対策： 現状維持を基本としつつ、Aクラスへの昇格を狙える商品がないか分析します。定期発注方式などで効率的に管理します。

Cクラス（下位品目）

品目数は多いですが、売上貢献度は低いです。

• 対策： 過剰在庫になっていないか厳しくチェックします。場合によっては取り扱いの中止（カット）や、発注点を下げて在庫圧縮を検討します。

パレート図とは？見方から簡単な作り方まで、わかりやすく徹底解説！

まとめ

ABC分析は、エクセルの基本的な関数（SUM、IFなど）と並べ替え機能を使えば、誰でも作成することができます。

1. データを降順に並べ替える

2. 構成比と累積比率を計算する

3. 累積比率をもとにABCランクを判定する

4. パレート図で可視化する

この手順をマスターすれば、在庫削減や売上アップのための戦略が立てやすくなります。

もし、「エクセルの操作に時間をかけたくない」「報告書用にデザイン性の高いグラフがすぐに欲しい」という場合は、ぜひxGrapherを活用してみてください。分析作業の時間を大幅に短縮し、本来の目的である「戦略を考える時間」を増やすことができます。

関連記事: QC7つ道具・新QC7つ道具とは？違いと使い方を徹底解説【初心者向け】

ABC分析とエクセルに関するよくある質問（Q&A）

Q1. エクセルのバージョンが古くてもパレート図は作れますか？

A1. はい、可能です。Excel 2016以降ではワンクリックで作成できる「パレート図」機能がありますが、それ以前のバージョンでも「複合グラフ」機能を使うことで作成できます。「売上（棒グラフ）」と「累積比率（第2軸の折れ線グラフ）」を組み合わせることで同じグラフが表現可能です。

Q2. ABCのランク分けの基準（％）は決まっていますか？

A2. 一般的には「A：〜70-80%」「B：〜90%」「C：〜100%」がよく使われますが、絶対的な決まりではありません。自社の取扱品目数や業界の特性に合わせて、管理しやすい割合に調整して構いません。

Q3. ABC分析表を作るために絶対に必要なデータは何ですか？

A3. 「分析したい項目（商品名や品番など）」と「数値データ（売上金額、在庫金額、出荷数など）」の2つが最低限必要です。

Q4. エクセル以外（Googleスプレッドシートなど）でもできますか？

A4. はい、Googleスプレッドシートでも基本的に同じ手順・同じ関数でABC分析が可能です。ただし、グラフ作成のメニュー位置などはエクセルと異なる場合があります。

Q5. データ量が多すぎてエクセルが重い場合はどうすればいいですか？

A5. データが数万行を超える場合、エクセルの動作が重くなることがあります。その場合は、ピボットテーブルを使ってデータをカテゴリごとに集計して行数を減らすか、データベースソフトの使用を検討してください。