【Excel】パレート図を使ったABC分析

2025年3月10日

この記事では、Excelでパレート図を作成する方法を紹介します。なお、ピボットテーブルを使ったパレート図の作成方法は下記記事をご覧ください。

パレート図を使ったABC分析

ABC分析とは、製品や顧客を売上やコストなど特定のデータに基づいて、重要度の高い順にA・B・Cという3つのグループに分類することです。グループ分けすることで、重点的に資源を投下すべき領域が明確になります。

その際、パレート図を使って可視化することで、製品や顧客の重要度を視覚的に把握し、グループ分けすることができます。パレート図は、以下のようなデータの大きさを降順に棒グラフで表示し、累積構成比を折れ線グラフで表した複合グラフです。

パレート図を見れば、どの顧客が売上に大きく寄与しているか一見してわかり、重点的に取り組む領域が明確になります。

たとえば、以下のような顧客別売上データに基づき、パレート図を作成します。

パレート図に表示させるためのデータとして、列に「ランク」と「累積構成比率」を追加します。ランクには各顧客にA・B・Cの分類を記載します。累積構成比率には、ある項目が全体の合計に対して占める割合を、上位の項目から順に加算したものを表示させます。

次にこのデータを売上の大きい順に並べ替えます。「売上」の列にカーソルを合わせた状態で、データタブの降順に並べ替え（Z→A）をクリックします。

これで売上の大きい順にデータを並べ替えることができます。

次に累積構成比率を計算します。一番上の顧客は「売上/売上合計」で計算します。これが全体の売上に対する構成比率を表します。

=C2/SUM($C$2:$C$21)

2番目以降のデータでは、「前の項目の累計構成比率 + その項目の構成比率」となるように数式を入力します。

=D2+C3/SUM($C$2:$C$21)

下にコピーすれば、累積構成比率が表示されます。計算が正しければ、最後の値が100%になっているはずです。

次にこの累積構成比率に基づき、各顧客のランクを付けていきます。ここでは、VLOOKUP関数を使って効率的にランクを付けていきます。ランクの基準値と該当のランクを別の表に整理します。ここでは、以下のように基準を設けてA・B・Cのランク分けを行います。

A・B・Cのランク分けの基準は一般的には以下のように分けられます。分析の目的や業界によって適宜変更いただければと思います。

ランクを表示させる方法として、VLOOKUP関数の検索方法に「近似一致（TRUE）」を使用します。これで、「～以上」という範囲で検索して、該当する値を表示させることができます。

VLOOKUP(検索値,範囲,列番号,[検索方法])
検索値で指定した値を、範囲の左端列から検索する。検索値と一致するデータの左から列番号目の値を抽出し表示する。検索方法に「FALSE」を指定すれば、完全に一致するデータを検索する。「TRUE」を指定すれば、「～以上」と数値を区切って該当する区分を特定する。

ランクを表示させるセルに以下のように数式を入力します。検索値には「累積構成比率」を指定し、検索範囲にはランクの基準を記載した表を選択します。検索方法に「TRUE」を指定すれば、「0%以上」「80%以上」「90%以上」で検索することができます。

=VLOOKUP(D2,$F$2:$G$4,2,TRUE)