ExcelでABC分析する方法｜累積・境界判定・更新の検算シート
与謝秀作
ExcelでABC分析するには、品目ごとの評価額を集計し、降順に並べて構成比・累積構成比・ランクを計算します。最初に決めるのは、評価指標・集計期間・境界を跨ぐ品目の扱いです。数式が動いても、この3点が違えば分類結果は変わります。
この記事では「その品目を加える前の累積構成比」が70%未満ならA、70%以上90%未満ならB、90%以上ならCとします。70%と90%は説明用の方針で、すべての事業に共通する基準ではありません。分類の目的はABC分析の基本、80対20の考え方はパレートの法則で確認できます。
元データを集計し、全列を一緒に並べ替える
- 売上額、粗利額、使用金額など、今回の評価指標を1つ選びます。
- 同じ期間・通貨・税・返品の条件で、品目IDごとに1行へ集計します。
- 同名別商品や表記揺れを、名前ではなく品目IDで確認します。
- 品目ID・金額を含む表全体を、評価額の降順で並べ替えます。同額なら品目ID順などの固定ルールを使います。
金額列だけを並べ替えると商品名と金額の対応が壊れます。また、明細行をそのまま分類すると、同じ商品が複数ランクに出るため、先に商品単位へまとめます。粗利で判断したいのに売上額で分類しないよう、列名に評価指標と単位を書いてください。
列としきい値を準備する
以下は架空の10品目・税抜き円建ての売上です。A1を「品目ID」、B1を「売上額（円）」、C1を「構成比」、D1を「累積構成比」、E1を「ランク」とします。データは2〜11行です。H2へ0.7、H3へ0.9を入力し、0＜H2＜H3＜1を確認します。B1は数値ではなく文字の見出しにしてください。
分類には「追加前」の累積を使います。下表の追加前列は説明・検算用で、E列の式は前の行までの金額を直接合計します。
品目ID | 売上額（円） | 構成比 | 累積 | 追加前累積 | ランク |
|---|---|---|---|---|---|
P01 | 3,000,000 | 30% | 30% | 0% | A |
P02 | 2,200,000 | 22% | 52% | 30% | A |
P03 | 1,600,000 | 16% | 68% | 52% | A |
P04 | 1,000,000 | 10% | 78% | 68% | A |
P05 | 700,000 | 7% | 85% | 78% | B |
P06 | 500,000 | 5% | 90% | 85% | B |
P07 | 400,000 | 4% | 94% | 90% | C |
P08 | 300,000 | 3% | 97% | 94% | C |
P09 | 200,000 | 2% | 99% | 97% | C |
P10 | 100,000 | 1% | 100% | 99% | C |
総額は10,000,000円です。P04は追加後の累積が78%でも、追加前は68%なのでAです。P06は追加後90%、追加前85%なのでB。P07は追加前90%でCとなります。境界を跨ぐ品目を上位へ残すこのルールでは、Aの構成比が70%を超えることがあります。
構成比・累積・ランクの数式を入れる
C2、D2、E2へ次の式を入力し、11行目までコピーします。カンマ区切りの標準的な関数表記で示しています。環境によって区切り記号が異なる場合は、利用するExcelの表記に合わせてください。
セル | 入力する式 |
|---|---|
C2 |
|
D2 |
|
E2 |
|
$B$2:$B$11は総額の範囲を固定します。D2の$B$2:B2は開始位置を固定し、終点だけを下へ動かす累積です。Microsoftの累積合計の説明でも、開始位置を固定したSUMの使い方を確認できます。
E2の$B$1:B1は最初のデータ行より前までの金額です。B1は文字なのでSUMでは加算されず、最初の品目の追加前金額は0になります。下へコピーすると終点がB2、B3と動きます。しきい値はH2・H3を絶対参照し、丸めた百分率を引き算せず、金額のまま比較します。
IF(D2<=70%,...)のように追加後の累積で判定する方式は、境界を跨ぐ品目の分類が変わります。別の方針として選ぶことはできますが、同じレポート内で混ぜないでください。
10品目の期待値に戻して検算する
確認項目 | この例の期待値 | 一致しないときの確認 |
|---|---|---|
総額 | 10,000,000円 | 欠けた行、重複、範囲外データ |
最終累積 | 100% | 総額と累積の対象範囲 |
Aランク | 4品目・7,800,000円 | P04が追加前68%でAか |
Bランク | 2品目・1,200,000円 | P06が追加前85%でBか |
Cランク | 4品目・1,000,000円 | P07が追加前90%でCか |
最終累積100%だけでは、元データが正しい証拠にはなりません。誤って同じ商品を二重に足しても、分子と分母の範囲が同じなら100%になります。品目数、品目ID、期間、元帳や集計元の総額とも照合します。A＋B＋Cの金額が総額へ戻ることも確かめてください。
パレート図と分類表を使い分ける
対応するExcelでは、文字のカテゴリ列と数値列を選び、パレート図を挿入できます。Microsoftのパレート図の公式手順を、使用するバージョン・OSに合わせて確認してください。数値2列だけを選ぶとヒストグラムとして区間に集約される場合があり、品目別の図とは意味が変わります。
今回の分類表と同じ順序・累積を見せたい場合は、A列・B列・D列を使い、金額の棒と累積比率の線を組み合わせる方法もあります。線の軸を0〜100%へ設定し、商品が勝手にまとめられていないか確認します。グラフは集中度を見せる道具で、ABCの境界ルールを決めるものではありません。
ゼロ・負値・データ追加への対処
- 総額0：比率を算出できません。IFERRORで0%へ置き換えず、対象期間や未計上を確認します。
- 負値：返品・調整などの理由を確認します。通常の非負金額による累積が前提なので、無断で除外せず、返品込み・別集計などの方針を決めます。
- 欠損・文字の金額：ゼロ売上と分けて修正します。SUMが文字を無視したまま総額を作らないか点検します。
- 同額：品目ID等の第2キーを固定します。境界付近の同額を同一ランクにする別方針なら、そのルールも明文化します。
- 追加行：固定範囲は新しい行を自動では含みません。表をExcelテーブルで管理し参照を調整するか、すべての総額・累積・分類式の範囲を更新します。
単にB100まで広げておく方法は、100行を超えるデータや別の集計表の混入を防げません。更新のたびに元データ件数・総額・最終行・式のコピー・グラフ範囲を確認し、再度降順に並べます。
Cランクだから廃止するとは限りません。新商品、補完品、欠品期間も確かめ、ABC分類後の判断ミスを防ぐ確認表へ進んで施策を決めてください。分析から生まれた施策や担当を整理する際は、Xtrategyの機能を確認できます。