ExcelでABC分析を行う方法：簡単な5つのステップ
ABC分析は、年間消費金額に基づいて在庫品目を3つのクラスに分類する手法です。Aクラスは、品目数は少ないものの金額の大部分を占めるものです。Cクラスは、品目数は多いものの金額はわずかしか占めないもので、Bクラスはその中間に位置します。Excelでは、年間金額、全体に占める割合、累計比率、そして各クラスを割り当てる数式を含む1つのテーブルを作成するだけで、この分析を行うことができます。
このガイドでは、各クラスの意味、Excelでの5つのステップ、具体的な計算例、および結果をグラフ化する方法について解説します。また、基準値（カットオフ）の選び方や、分析完了後に各クラスをどのように管理すべきかについても説明します。
ABC分析とは
ABC分析とは、どの品目に最も注意を払うべきかを決定するための手法です。これは、「少数の品目が支出の大部分を占める」というシンプルなパターンに基づいています。
Management Sciences for Health（MSH）による2012年の医薬品支出の分析と管理に関する章では、これが分かりやすく説明されています。そこでは、「比較的少数の品目が年間消費額の大部分を占めている」と指摘されており、さらに「この現象の分析はパレート分析、あるいはより一般的にはABC分析として知られている」と付け加えられています。
同章では、品目は「年間消費額に基づいて3つのカテゴリ（A、B、C）に分類できる」と説明されています。この手法は、医薬品、予備部品、あるいは小売製品のいずれを在庫管理する場合でも同じです。
見落としがちなポイントが1つあります。これらのクラスは永続的なラベルではないということです。MSHは、「消費パターンが変化すれば、次回ABC分析を行う際にその品目は別のカテゴリに分類される可能性がある」と指摘しています。そのため、ABC分析は単発のプロジェクトとしてではなく、定期的なチェックとして行うのが最も効果的です。
A、B、Cクラスの意味
MSHの章では、各クラスの一般的な範囲を以下のように示しています。
| クラス | 品目数の割合 | 年間金額の割合 | 一般的な意味 |
|---|---|---|---|
| A | 10 to 20 percent | 75 to 80 percent | 品目数は少なく、金額の大部分を占める |
| B | 10 to 20 percent | 15 to 20 percent | 中間グループ |
| C | 60 to 80 percent | 5 to 10 percent | 品目数は多く、金額のわずかしか占めない |
これらは一般的な範囲であり、厳格なルールではありません。MSHは「これらの境界線はある程度柔軟である」と述べています。同資料の例では、代わりに資金の70 percentを占める品目をAクラスに設定しています。
クラス分けの基準となる値は年間消費金額であり、1年間に使用された数量に単価を掛けたものです。大量に消費される安価な品目がAクラスに入ることもあれば、年に1回しか使われない高価な品目がCクラスに入ることもあります。
American Journal of Business Educationの2014年の記事では、金額のみを基準にすることに疑問を投げかけています。教科書が「金額規模のみを唯一の基準として重視している」と主張し、他の基準も追加することを推奨しています。とはいえ、最初のステップとしては、MSHの章で採用されている金額ベースの手法が基本となります。
開始する前に必要なもの
ExcelでABC分析を行うには、品目ごとに以下の数列を用意するだけです。
- 品目名またはSKU。 1品目につき1行。
- 年間の使用数量または購入数量。 すべての品目で同じ12ヶ月の期間を使用します。
- 単価。 数量をカウントする際と同じ単位における、1単位あたりのコスト。
MSHは、期間を一致させることの重要性を強調しています。「不適切な比較を避けるため、すべての品目で必ず同じ評価期間を使用してください。」また、パッケージのサイズを混在させるのではなく、錠剤や1箱など、コストと数量で同じ基本単位を使用することを推奨しています。
データが在庫管理システムや購買システムから取得できる場合は、CSVまたはExcelファイルとしてエクスポートします。対象期間中に動きがなかった品目は削除するか、そのまま残してCクラスに分類されるようにします。
ExcelでABC分析を行う方法
以下の5つのステップは、MSHの章で紹介されている手法をExcelの数式に適応させたものです。この例では、1行目にタイトル、2行目にヘッダーを配置し、3行目から12行目に10個の品目を並べています。A列、B列、C列にはそれぞれ、品目名、年間数量、単価が入力されています。
ステップ1：品目、数量、単価をリストアップする
品目ごとに、名前、年間数量、単価を1行ずつ入力または貼り付けます。後でテーブルを簡単に並べ替えられるように、2行目にヘッダーを追加します。
次に進む前にデータを確認してください。単価の空白、マイナスの数量、重複するSKUなどがあると、合計値が歪んでしまいます。通常は、各列に簡易フィルターをかけることでこれらを見つけることができます。
同じ品目を異なる価格で複数回購入した場合は、一貫した1つのコストを使用します。MSHは、実際の単価を追跡するのが難しい場合の最も正確な代替案として、「加重平均または先入先出（FIFO）平均」を挙げています。
ステップ2：年間金額と全体に占める割合を計算する
D列で、数量に単価を掛けて各品目の年間金額を算出します。D3セルに=B3*C3と入力し、数式を下にコピーします。
E列で、各金額をすべての金額の合計で割って、その割合を算出します。E3セルに=D3/SUM($D$3:$D$12)と入力し、下にコピーします。ドル記号（$）を付けることで、数式をコピーしても合計範囲が固定されます。E列の書式を、小数点以下2桁のパーセンテージに設定します。
MSHがこの精度を推奨するのには理由があります。同資料の言葉を借りれば、「複数の品目の金額が僅差で並んでいる場合があり、多くの品目が総価値の1 percent未満を占めることになるため」です。
ステップ3：金額の大きい順に品目を並べ替える
ヘッダーを含むテーブル全体を選択し、D列を基準に大きい順（降順）で並べ替えます。Excelでは、「データ」タブから「並べ替え」を選択し、最優先されるキーを「D列」、順序を「降順」に設定します。
数式を使用したい場合は、SORT関数で並べ替えられたコピーを返すことができます。Microsoftの構文は=SORT(範囲,[並べ替え順序],[並べ替え基準],[並べ替え方向])で、並べ替え基準に-1を指定すると降順になります。このテーブルの場合、=SORT(A3:E12,4,-1)と入力すると、4番目の列を基準に値の高い順に並べ替えられます。
このステップを終えると、年間金額が最も高い品目が一番上に配置されます。この順序に並べることで、次のステップで行う累計比率の計算が意味を持つようになります。
ステップ4：累計比率を追加する
F列に、割合の累計（累積構成比）を追加します。F3セルに=SUM($E$3:E3)と入力し、下にコピーします。範囲の最初の部分は固定され、2番目の部分はコピーするにつれて1行ずつ広がっていきます。
最終行は100 percentになるはずです。もしそうならない場合は、D列とE列に空白のセルや文字列が混ざっていないか確認してください。
この列はABC分析の核心です。各行より上にある品目が、全体金額の何パーセントを占めているかを示します。
ステップ5：A、B、Cクラスを割り当てる
G列で、数式を使って各品目にラベルを割り当てます。基準値を80 percentと95 percentにする場合、G3セルに以下を入力して下にコピーします。
=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")
IFS関数は、各条件を順番にチェックし、最初に真（TRUE）になった条件に対応する値を返します。Microsoftの公式サンプルでも同じパターンが使われており、最後のその他すべてを拾う条件としてTRUEが指定されています。累計比率が80 percent以下の品目はA、95 percent以下の品目はB、それ以外はCになります。
最後に、=COUNTIF(G3:G12,"A")を使って各クラスの品目数をカウントします（BとCについても同様に行います）。カウントした数値を上記の一般的な範囲と比較してください。もしAクラスの品目数が多すぎてチームで管理しきれない場合や、逆に少なすぎる場合は、基準値を調整します。
具体的な計算例
以下は、年間金額順に並べ替えられた10個の品目の例を示すテーブルです。数値は架空のサンプルデータです。
| 品目 | 年間数量 | 単価 | 年間金額 | 割合 | 累計比率 | クラス |
|---|---|---|---|---|---|---|
| SKU-01 | 1,200 | $45.00 | $54,000 | 36.00% | 36.00% | A |
| SKU-02 | 3,000 | $12.00 | $36,000 | 24.00% | 60.00% | A |
| SKU-03 | 500 | $40.00 | $20,000 | 13.33% | 73.33% | A |
| SKU-04 | 8,000 | $1.50 | $12,000 | 8.00% | 81.33% | B |
| SKU-05 | 2,000 | $4.00 | $8,000 | 5.33% | 86.67% | B |
| SKU-06 | 600 | $10.00 | $6,000 | 4.00% | 90.67% | B |
| SKU-07 | 1,000 | $5.00 | $5,000 | 3.33% | 94.00% | B |
| SKU-08 | 1,500 | $3.00 | $4,500 | 3.00% | 97.00% | C |
| SKU-09 | 700 | $5.00 | $3,500 | 2.33% | 99.33% | C |
| SKU-10 | 400 | $2.50 | $1,000 | 0.67% | 100.00% | C |
年間総金額は$150,000です。リストの30 percentにあたる3つの品目が、総金額の73.33 percentを占めてAクラスに分類されています。4つの品目がBクラスに分類され、残りの3つの品目（金額の6 percentに相当）がCクラスに分類されています。
注目すべき点が2つあります。SKU-04は数量が圧倒的に多いですが、単価が安いためBクラスに分類されています。また、品目数がわずか10個であるため、各クラスの割合は一般的な範囲とは一致していませんが、短いリストではこれは正常なことです。
結果をグラフ化する方法
グラフを作成すると、会議などでパターンを視覚的に説明しやすくなります。MSHは、品目数に対する累計比率をプロットすることを推奨しており、これによりおなじみのABC曲線が描かれます。
Excelには、このためのグラフが標準で用意されています。Microsoftはパレート図を「降順に並べ替えられた棒グラフと、累積比率を示す折れ線グラフの両方を含むグラフ」と説明しています。作成するには、品目名と年間金額を選択し、「挿入」から「統計グラフの挿入」、「パレート図」を選択します。
80 percentや95 percentなどの基準値の位置に2本の水平線またはラベルを追加すると、各クラスがどこから始まるのかが一目で分かります。AIを使ったパレート図の作成方法については、当社のPareto chart with AI作成ガイドで詳しく解説しています。
基準値（カットオフ）の選び方
唯一の正しい基準値というものは存在しません。MSHは、基準値の選択は「リスト内の品目間で数量と金額がどのように分散しているか」に依存すると説明しています。また、「ABC分析の結果をどのように活用するか」によっても異なります。
実質的な限界となるのは管理能力です。MSHは「Aクラスへの品目の割り当ては、管理能力に基づいて行われなければならない」と率直に述べています。もしチームが月に細かくチェックできるのが50品目までであるなら、Aクラスに300品目も割り当ててしまっては分析の意味がありません。
代表的なアプローチをいくつか紹介します：
- 金額基準。 金額の累計80 percentまでをA、95 percentまでをB、残りをCとする方法。これは上記で使用した手法です。
- 品目数基準。 金額順で上位20 percentの品目をA、次の30 percentをB、残りをCとする方法。
- 固定リスト。 金額の割合に関わらず、上位25品目または50品目を一律でAクラスに設定する方法。
どのアプローチを選ぶにしても、それを明文化し、毎回同じ基準を適用してください。今四半期のクラスと前四半期のクラスを比較して意味があるのは、基準値が同じ場合に限られます。
各クラスの管理方法
ABC分析の目的は、金額の大きい部分に労力を集中させることです。MSHの章では、分析結果の活用方法として以下のような例を挙げています：
- Aクラスの品目をより頻繁に発注する。 MSHは、Aクラスの品目を「より頻繁に、かつ小ロットで発注することで、在庫維持コストの削減につながる」と述べています。
- Aクラスの価格交渉を優先する。 同章によると、「分析でAクラスに分類された品目の価格を引き下げることができれば、大幅なコスト削減につながる」とのことです。
- Aクラスの在庫をより頻繁に棚卸しする。 MSHは、「循環棚卸はABC分析に基づいて行うべきであり、Aクラスの品目についてはより頻繁に棚卸を実施する必要がある」と指摘しています。
- Aクラスの発注ステータスを監視する。 Aクラスの品目が予期せず欠品すると、コストのかかる緊急調達を余儀なくされる可能性があります。
Cクラスの品目については、発注頻度を下げてまとめて発注する、棚卸の頻度を減らすなど、よりシンプルなルールを適用できます。Bクラスはその中間に位置します。もし滞留在庫が懸念される場合は、当社のspot slow-moving inventory（滞留在庫の特定方法）に関するガイドをこの分析と併せて活用することをお勧めします。
AIを使ってより迅速に行う
Excelでの作業は、データがクリーンな状態であれば数分で完了します。しかし、エクスポートしたデータをクリーンアップし、この作業を毎四半期繰り返すとなると、より多くの時間がかかります。
AIワークスペースを使用すれば、1回の指示で計算と並べ替えを同時に行うことができます。在庫や購買のエクスポートデータをPowerdrill Bloomにアップロードし、設定したい基準値でのABC分析を自然言語で依頼するだけです。各品目の年間金額、割合、累計比率、クラスの算出に加え、パレート図の作成も依頼できます。
その後は、通常の表計算シートと同様に確認を行います。年間総金額が自身の計算と一致しているか確認し、各クラスから2つほどの品目を抽出してスポットチェックを行います。当社のExcel AI assistantのページでは、このようなスプレッドシート作業について詳しく解説しています。より幅広い予測ツールについては、こちらのAI tools for inventory and demand forecasting（在庫および需要予測に最適なAIツール）のまとめをご覧ください。
避けるべきよくある間違い
- 異なる対象期間を混在させる。 ある品目は12ヶ月、別の品目は6ヶ月といった混在があると、割合の計算が意味をなさなくなります。
- 金額ではなく数量を使用する。 クラス分けは数量単独ではなく、数量に単価を掛けた金額に基づきます。
- 累計比率を出す前に並べ替えを忘れる。 並べ替えられていないリストで累計比率を計算すると、品目が誤ったクラスに分類されてしまいます。
- クラスを永続的なものとして扱う。 品目はクラス間を移動するため、四半期ごと、または毎年分析を再実行してください。
- 管理能力を無視した基準値の設定。 Aクラスのリストが長すぎて細かく管理できない場合、Bクラスと変わらない管理レベルになってしまいます。
- 重要な安価品を無視する。 安価な品目であっても、欠品すると業務が停止する可能性があります。MSHの章では、ABC分析と併せて、重要度（不可欠、必要、非必須）による個別の評価を行うことを推奨しています。
エクスポートした品目リストが整理されていない場合は、Powerdrill Bloomをお試しいただき、最初のABCテーブルとグラフを作成してみてください。
よくある質問
在庫管理におけるABC分析とは何ですか？
ABC分析は、年間消費金額に基づいて品目を3つのクラスに分類する手法です。Aクラスは、品目数は少ないものの金額の大部分を占めるものです。Cクラスは、品目数は多いものの金額はわずかしか占めないもので、Bクラスはその中間に位置します。これにより、チームは金額規模の大きい部分に管理労力を集中させることができます。
ExcelでABC分析を計算するにはどうすればよいですか？
各品目の年間数量に単価を掛け、それを合計金額で割って各品目の割合を算出します。金額の大きい順に並べ替え、割合の累計比率を追加し、IFSなどの数式を使ってクラスを割り当てます。基準値として80 percentと95 percentを設定することは、MSHの章に示されている一般的な範囲に合致します。
ABC分析における一般的な割合はどのくらいですか？
一般的なガイドラインでは、Aクラスは品目数の10〜20 percentを占め、金額の75〜80 percentを占めます。Bクラスは品目数の10〜20 percentを占め、金額の15〜20 percentを占めます。Cクラスは品目数の60〜80 percentを占め、金額の5〜10 percentを占めます。
ExcelでのABC分類の数式は何ですか？
F列に累計比率があり、データが3行目から始まっている場合は、=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")を使用します。自身の基準値に合わせて0.8や0.95を変更してください。IF関数を入れ子（ネスト）にすることでも同様の処理が可能です。
なぜABC分析が重要なのですか？
在庫資金の大部分がどこに費やされているかを明らかにし、チームがそれらの品目をより綿密に管理できるようにするためです。代表的な活用方法としては、Aクラスの品目の発注頻度を上げる、価格交渉を優先する、棚卸の頻度を増やすなどが挙げられます。また、計画に合致しない支出を特定するのにも役立ちます。
情報源： Management Sciences for Health, MDS-3 Chapter 40: Analyzing and controlling pharmaceutical expenditures · Ravinder and Misra, ABC Analysis for Inventory Management (2014) · Microsoft Support, SORT function · Microsoft Support, IFS function · Microsoft Support, Create a Pareto chart.