ExcelでできるABC分析のやり方｜関数・グラフの作成手順
ABC分析は専用ツールがなくても、使い慣れたExcel(エクセル)だけで十分に実践できます。
この記事では、ExcelでABC分析を行う手順を、関数の使い方からパレート図の作成まで、順を追って解説します。並べ替え・累積構成比の計算・IF関数でのランク付け・グラフ化という流れを押さえれば、誰でも自分のデータでABC分析表を作れるようになります。
ABC分析とは、商品や顧客を売上などの貢献度に応じてA・B・Cの3ランクに分類し、重点的に管理すべき対象を見極める手法です。「上位2割の商品が売上の8割を生む」というパレートの法則を実務に応用したものです。
ランク分けの一般的な基準は、累積構成比を使って次のように設定します。
この基準(70%・90%)は目安であり、扱う商材や方針に応じて調整して構いません。Excelで作っておけば、しきい値を変えてもすぐ再計算できます。
ここからは、商品ごとの売上データを例に、実際の作成手順を5ステップで説明します。次のような表が手元にある前提で進めます(A列=商品名、B列=売上)。
まず、売上(B列)を降順に並べ替えます。データ範囲を選択し、「データ」タブ →「並べ替え」で、売上を基準に大きい順を指定します。ABC分析は構成比の高い順に積み上げるため、この並べ替えが出発点になります。
C列に各商品の構成比(全体に占める割合)を求めます。売上合計に対する各行の割合を出す式は次のとおりです。
=B2/SUM($B$2:$B$100)
分母の合計範囲は、$をつけて絶対参照にするのがポイントです。これで下方向にコピーしても合計範囲がずれません。セルの表示形式をパーセントにしておくと見やすくなります。
D列に、上から順に構成比を足し上げた累積構成比を求めます。1行目だけ構成比そのまま、2行目以降は「ひとつ上の累積+自分の構成比」とします。2行目(D3)に入れる式の例は次のとおりです。
=D2+C3
先頭セル(D2)には「=C2」と入れ、D3以降に上の式をコピーします。最終行が100%になっていれば計算は正しくできています。
E列に、累積構成比をもとにランクを自動で振ります。IF関数を入れ子にして、しきい値(70%・90%)で判定します。E2に入れる式の例は次のとおりです。
=IF(D2<=70%,"A",IF(D2<=90%,"B","C"))
この式は、累積構成比が70%以下ならA、90%以下ならB、それ以外はCを返します。しきい値を変えたいときは、式の70%・90%の数値を直すだけです。IFS関数が使える環境なら、より読みやすく書くこともできます。
数字だけでなく、パレート図(棒グラフと累積構成比の折れ線を重ねたグラフ)にすると、どこまでがAランクかが視覚的に分かります。Excelのバージョンによって作り方が2通りあります。
この方法は最も手軽ですが、累積線のしきい値表示など細かい調整には向きません。
自作の複合グラフは手間がかかる分、見せ方を自由に調整できます。レポートで境界を明示したいときに向いています。
一度作った表は、テンプレートとして保存しておくと次回以降がぐっと楽になります。運用しやすくするポイントは次のとおりです。
ExcelでのABC分析は、①売上の降順に並べ替え、②構成比を計算、③累積構成比を計算、④IF関数でランク判定、⑤パレート図で可視化、という流れで誰でも実践できます。
一度テンプレートを作ってしまえば、データを差し替えるだけで毎月の分析が回せます。まずは手元の売上データで、構成比と累積構成比の2列を足すところから始めてみてください。
NPSアンケートの作り方を設問設計・集計方法・改善へのつなげ方の3ステップで解説。推奨度の質問例、推奨者・批判者の計算式、平均の見方も紹介します。
ABC分析に行動経済学を掛け合わせ、アンカリング・社会的証明・損失回避など購買行動を動かす心理バイアスを解説。A/B/Cランク別の打ち手と実践ステップも紹介します。
ARPPU（課金ユーザーあたり平均収益）の意味と計算方法を解説。分母に無料ユーザーを含めるARPUとの違いを具体例で整理し、「課金率×ARPPU」の分解やセグメント分析など課金ユーザー分析での活用法、ARPPUを高める考え方までわかりやすく...
