Excelの売上データや在庫データを整理すると、商品ごとの重要度を判断したい場面があります。
そのようなときに役立つ手法が、売上額や構成比、累積構成比を使って商品をA・B・Cに分類するABC分析です。
ABC分析は難しい統計処理ではなく、元データの並べ替え、構成比の計算、累積比率の計算、分類用の数式を順番に設定すれば作成できます。
さらにパレート図を組み合わせると、どの商品群へ優先的に時間や予算を配分すべきかを、ひと目で共有しやすくなります。
ABC分析では、売上額や利益額の大きい順にデータを並べ、累積構成比を基準として重要度を分類します。
一般的には累積構成比が70％までをA、70％超から90％までをB、90％超をCとする考え方が使われます。
この記事では、1行目に見出しがある販売実績表を例に、ExcelでABC分析表とパレート図を作る方法を解説します。
エクセルでABC分析を行う手順
それではまず、ABC分析の結果を出すための基本手順について解説していきます。
| 商品名 | 売上額 | 構成比 | 累積構成比 | 分類 |
|---|---|---|---|---|
| 商品A | 520,000 | 35.1% | 35.1% | A |
| 商品B | 340,000 | 23.0% | 58.1% | A |
| 商品C | 220,000 | 14.9% | 73.0% | B |
分析対象となる数値の選定
ABC分析を始める前に、何を基準として優先順位を付けるのかを決めましょう。
小売業や営業部門では売上額がよく使われますが、売上が大きくても利益率が低い商品がある場合は、粗利益額を使うほうが実態に合うかもしれません。
在庫管理では、年間出庫金額、使用金額、在庫金額などを基準にすることがあります。
分析の目的と計算に使う数値がずれていると、分類結果を行動に結び付けにくくなります。
たとえば発注頻度を見直したいなら年間使用金額、販売促進の重点を決めたいなら売上額や粗利益額が候補になります。
元データは、A列に商品名、B列に売上額という形で準備すると、その後の操作をスムーズに進められます。
降順での並べ替え
続いては、重要なデータが上に並ぶように売上額を降順で並べ替える操作を確認していきます。
表内のセルを1つ選択し、データタブの並べ替えとフィルターから降順を選択します。
並べ替えの対象範囲を確認する画面が表示されたときは、見出しを含む表全体を選択してください。
1行目にヘッダーがある場合は、先頭行をデータの見出しとして使用する設定を有効にします。
ABC分析では、基準となる金額を必ず大きい順に並べることが重要です。
昇順のままで累積構成比を計算すると、重要度の低い商品から累積されてしまい、A・B・Cの意味が逆転します。
【操作のポイント】並べ替え後は、商品名だけでなく構成比や分類列も同じ行のまま移動しているかを確認しましょう。
分析列の追加
続いては、構成比、累積構成比、分類という3列を追加する方法を確認していきます。
売上額がB列にあり、データが2行目から始まる場合は、C1に構成比、D1に累積構成比、E1に分類と入力します。
列見出しは、後からグラフを作成するときにも使われるため、意味が分かる名前にしておくと安心です。
表の構成例は、A列が商品名、B列が売上額、C列が構成比、D列が累積構成比、E列がABC分類です。
売上額以外の列は数式で算出するため、金額データを更新しても分析結果を再利用できます。
データ件数が多い場合は、表をテーブルとして書式設定しておく方法も便利です。
テーブルでは新しい行を追加したときに数式が引き継がれやすく、月次の分析作業を効率化できます。
構成比と累積構成比の数式
続いては、ABC分析の判定に必要な構成比と累積構成比の計算について解説していきます。
| セル | 入力内容 | 役割 |
|---|---|---|
| C2 | =B2/SUM($B$2:$B$11) | 個別商品の構成比 |
| D2 | =SUM($C$2:C2) | 上位からの累積構成比 |
| E2 | =IF(D2<=70%,”A”,IF(D2<=90%,”B”,”C”)) | 重要度の分類 |
構成比を求める計算式
それではまず、売上額に対する各商品の構成比を求める数式について解説していきます。
C2セルには、売上額B2を売上額全体の合計で割る数式を入力します。
=B2/SUM($B$2:$B$11)
この式では、B2が商品Aの売上額を表し、SUM($B$2:$B$11)が全商品の売上額合計を表します。
ドル記号を付けた$B$2:$B$11は絶対参照です。
数式を下方向へコピーしても、合計範囲だけは固定するために絶対参照を使用します。
数式を入力したら、ホームタブのパーセントスタイルを選択し、小数点以下の表示桁数を必要に応じて調整しましょう。
構成比の合計は、端数表示を除けば100％になります。
累積構成比を求める計算式
続いては、上位商品からの割合を積み上げる累積構成比の数式を確認していきます。
D2セルには、次の数式を入力します。
=SUM($C$2:C2)
開始位置であるC2だけを絶対参照にし、終点のC2は相対参照にすることがポイントです。
D2ではC2だけが合計され、D3へコピーするとSUM($C$2:C3)となり、1行目の商品から3行目の商品までの構成比が累積されます。
同様にD4ではC2からC4までが加算されるため、下へ進むほど累積構成比が大きくなります。
最終行の累積構成比が100％付近になることを確認すると、計算範囲の入力ミスを見つけやすくなります。
【操作のポイント】累積構成比の数式は、先頭セルだけを入力してからフィルハンドルを下へドラッグすると効率的です。
オートフィルによる数式のコピー
続いては、先頭セルの数式を最終行まで反映するオートフィルについて確認していきます。
C2セルまたはD2セルを選択すると、セル右下に小さな四角形のフィルハンドルが表示されます。
フィルハンドルを下へドラッグするか、隣接列に連続データがある場合はダブルクリックすると、数式を下の行までコピーできます。
コピー後は、C列の各構成比とD列の累積構成比がパーセント表示になっているかを確認しましょう。
オートフィル後にD列の途中で数値が下がっている場合は、売上額の並べ替え順または数式の参照範囲を再確認します。
小数点以下の表示だけを丸めても、内部的な数値は保持されます。
分類の境界を正確に判定したい場合は、セル表示を整数パーセントにしていても、元の計算値を使って判定できます。
IF関数によるA・B・C分類
続いては、累積構成比を使ってA・B・Cへ自動分類する方法について解説していきます。
| 累積構成比 | 分類 | 管理の考え方 |
|---|---|---|
| 70％以下 | A | 重点管理 |
| 70％超から90％以下 | B | 通常管理 |
| 90％超 | C | 簡易管理 |
IF関数の基本式
それではまず、累積構成比を判定するIF関数の基本式について解説していきます。
E2セルに次の数式を入力します。
=IF(D2<=70%,”A”,IF(D2<=90%,”B”,”C”))
最初のIF関数では、D2セルが70％以下ならAを表示します。
70％を超える場合は次のIF関数に進み、90％以下ならB、90％を超えるならCを表示する仕組みです。
判定に使う70％と90％は固定された唯一の正解ではなく、業種や管理目的に応じて調整できます。
たとえば重点管理の対象をより絞りたい場合は、A分類の境界を60％に設定する方法もあります。
分類境界の扱い
続いては、累積構成比が境界付近になったときの扱いについて確認していきます。
累積構成比が68％の商品までをAにすると、次の商品を加えた時点で75％になる場合があります。
このとき、式の結果では75％の商品はBになりますが、その商品自体の売上規模が非常に大きいこともあります。
分類は機械的なラベルではなく、優先順位を考えるための出発点として活用することが大切です。
実務では、境界をまたぐ大口商品をAとして扱う、またはAプラスとして別管理することもあります。
分類ルールを変更した場合は、関係者が同じ基準で判断できるように、シート内に判定基準を記載しておくとよいでしょう。
【操作のポイント】境界値を別セルに入力して参照すれば、70％や90％の基準を後から変更しやすくなります。
条件付き書式による見やすさ
続いては、分類結果を見やすくする条件付き書式について確認していきます。
E列の分類セルを選択し、ホームタブの条件付き書式から新しいルールを作成します。
セルの値がAなら目立つ色、Bなら中間的な色、Cなら淡い色というように設定すると、分析表を見た瞬間に重点商品を把握できます。
色だけで判断しにくい人にも伝わるように、セル内には必ずA・B・Cの文字を残しておくと安心です。
条件付き書式は分析結果を変える機能ではなく、判断の速さを高めるための表示機能です。
印刷して会議資料に使う場合は、白黒印刷でも分類が読めるように、文字や罫線の区別も意識しましょう。
パレート図とグラフの作成
続いては、ABC分析の結果を視覚化するパレート図とグラフの作成について解説していきます。
| 商品名 | 売上額 | 累積構成比 |
|---|---|---|
| 商品A | 520,000 | 35.1% |
| 商品B | 340,000 | 58.1% |
| 商品C | 220,000 | 73.0% |
複合グラフの設定
それではまず、売上額と累積構成比を組み合わせる複合グラフの設定について解説していきます。
商品名、売上額、累積構成比の3列を、見出しを含めて選択します。
挿入タブから組み合わせグラフを選び、売上額を集合縦棒、累積構成比を折れ線に設定しましょう。
累積構成比の系列には第2軸を設定します。
パレート図では、棒グラフで金額の大きさを示し、折れ線グラフで累積構成比の伸び方を示します。
第2軸を使わないと、金額とパーセントという単位の異なる値が同じ目盛りで表示され、折れ線が読み取りにくくなります。
【操作のポイント】元データが金額の降順になっていることを確認してからグラフを作成すると、左から右へ重要度が下がる自然なパレート図になります。
第2軸の表示範囲
続いては、累積構成比を示す第2軸の表示範囲について確認していきます。
グラフ右側の第2軸をクリックし、軸の書式設定を開きます。
最小値を0％、最大値を100％に設定すると、累積構成比の到達位置を正しく読み取れます。
最大値が自動設定のままだと、90％や100％付近の差が誇張されたり、逆に小さく見えたりする場合があります。
主単位を10％または20％にすると、70％と90％の分類基準を確認しやすくなります。
必要ならグラフ上に70％と90％の補助線を別系列で追加し、A・B・Cの境界を視覚的に示す方法もあります。
パレート図の読み取り
続いては、完成したパレート図の読み取り方について確認していきます。
棒が高い商品ほど、売上や利益に与える影響が大きい商品です。
折れ線が70％へ到達するまでの範囲が、一般的なA分類の候補になります。
少数の商品で売上全体の大部分を占めている場合、A分類の商品への欠品対策や販売施策が大きな効果を生む可能性があります。
一方でC分類の商品は重要ではないという意味ではありません。
品ぞろえ、顧客満足、関連販売、将来性などの観点もあるため、ABC分析だけで販売中止を決めるのではなく、ほかの情報と組み合わせて判断しましょう。
ABC分析テンプレートの活用
続いては、作成した分析表をテンプレートとして継続活用する方法について解説していきます。
| 入力する項目 | 数式で作る項目 | 更新時の作業 |
|---|---|---|
| 商品名と売上額 | 構成比と累積構成比と分類 | 売上額の貼り替えと降順並べ替え |
入力欄と計算欄の分離
それではまず、更新しやすいABC分析テンプレートの構成について解説していきます。
商品名と売上額の列を入力欄として扱い、構成比、累積構成比、分類の列は数式を残す計算欄として分けます。
入力欄の背景を淡い色にし、数式欄の背景を別の色にすると、修正してよいセルを判断しやすくなります。
テンプレートを保存する前に、C列からE列の数式が必要な行数までコピーされているかを確認しましょう。
毎月の販売実績を貼り替える運用では、古いデータを上書きする前に元データの保存先を分けておくと、過去の分析結果を比較できます。
テーブル機能による自動拡張
続いては、行数が変わるデータに対応するテーブル機能について確認していきます。
分析対象の範囲を選択し、挿入タブまたはホームタブからテーブルとして書式設定を実行します。
テーブルにすると、最終行の下へ新しい商品を入力した際に、数式列の計算式が自動的に広がる場合があります。
商品数が毎回変わる場合は、固定範囲の数式よりもテーブル参照を使うとメンテナンスの負担を減らせます。
ただし、累積構成比は並べ替え順の影響を受けます。
データを追加した後は、売上額の降順並べ替えを行い、累積構成比と分類が正しい順で再計算されているかを確認してください。
【操作のポイント】テンプレートには分析基準の対象期間と、A分類・B分類の境界値も記載しておくと引き継ぎに役立ちます。
分析結果の比較
続いては、月別や四半期別のABC分析結果を比較する方法について確認していきます。
同じ商品でも、季節、価格改定、キャンペーン、取引先の変化によって分類が変わることがあります。
月ごとの分類を横に並べると、A分類を維持している商品、急に順位が上がった商品、売上が低下した商品を見つけやすくなります。
単月だけのABC分析では偶然の増減も含まれるため、複数期間の推移を確認すると判断の精度が高まります。
在庫の発注量や営業活動へ反映する場合は、分類だけでなく、前年同期比、在庫日数、利益率も併記すると実用性が高まります。
まとめ エクセルでABC分析を行う方法（グラフ・パレート図・テンプレート）
ExcelでABC分析を行うときは、まず商品名と売上額などの基準となるデータを用意し、金額の大きい順に並べ替えます。
次に構成比を計算し、上位から加算する累積構成比を作成します。
累積構成比にIF関数を組み合わせれば、A・B・Cの分類を自動化できます。
重要な流れは、降順で並べ替えること、構成比の合計範囲を絶対参照にすること、累積構成比を先頭から加算することです。
売上額の棒グラフと累積構成比の折れ線グラフを組み合わせると、パレート図として重点商品の範囲を分かりやすく伝えられます。
テンプレート化しておけば、毎月の売上データを入れ替えるだけで、重点管理すべき商品や在庫を見直す材料を得られます。
ABC分析の分類基準は目的に応じて調整し、利益率や在庫日数なども併せて確認しましょう。
数式とパレート図を活用し、限られた時間や予算を重要度の高い業務へ配分していきましょう。
