エクセルで構成比率や累積構成比(累積比率)を算出：ABC分析・パレート図に応用【コピペ用テンプレートあり】
はじめに
エクセルで、 構成比や累積構成比を算出し、する方法を紹介します。
下にテンプレート・サンプルのセルデータがあるので、
累積比率、累積構成比とは
構成比とは、 ある値の全体に対する割合 です。
| 名称 | 説明 | 数式表現 |
|---|---|---|
| 構成比（構成比率） | ある値の全体に対する割合 | ある値 ÷ 全体の合計 |
| 累積構成比（累積比、累積比率） | ある値までの累積値（累計値）の割合 / 構成比の累積値（累計値） | ある値までの累積値 ÷ 全体の合計 |
いつ使うのか？
構成比や累積構成比（とくに後者）は、 ABC分析やパレート図の作成に応用できます。
ABC分析・パレート図とは
ABC分析とは、 全体を占める割合が大きいものから順にA, B, Cのランクを付ける手法 です。
データを昇順に並べ、累積構成比の値を算出し、その値を使ってランク付けします。
累積構成比をグラフ化したものがパレート図 です。
エクセルでの算出方法
以下の 全セルをコピー ボタンをクリックし、エクセルの A1 セルに貼り付けると以下が算出されます。
H列 : 構成比I列 : 累積構成比J列 : A, B, Cのランク付け（ ABC分析 用）
次のような組合せグラフを作成すると パレート図 になります。
- 縦棒グラフ：
F列 vs.G列 - 折れ線グラフ：
F列 vs.I列
データを書き換える場合は、 A, B列を書き換え、 C ~ J列をデータと同じ行までコピー（オートフィル）してください。
算出手順
構成比と累積構成比の算出、
1. データを並び替え
データを昇順に並び替えるため、以下の処理を行います。
- i. データを降順で順位付け
- ii. 順位に従って、データを表示
i. データを降順で順位付け
RANK.EQ()、COUNTIF()関数を使用して、各データの順位を算出します。
RANK.EQ()関数 : データの降順の順位を算出。ただし同順位があると重複してしまう。COUNTIF()関数 : 重複数をカウント。同順位があるときの重複を回避するために使用。
数値, 参照, 順序)参照範囲の中で、数値が何番目に大きいのか（or 小さいのか）ランクを表示する。
同じ数値の場合は、最上位ランクが返される
（例：3位と4位の値が同じ → 2つの3位を返す）。
数値参照順序0 : 降順、0以外：昇順。（省略時 0）ii. 順位に従って、データを表示
MATCH()、INDEX()関数を使用して、順位に従ってデータを表示します。
MATCH()関数 : iで算出した順位が、1のとき, 2のとき,... の行番号を取得。INDEX()関数 : 取得した行番号のデータを表示。
検査値, 検査範囲, 照合の種類)指定した検査範囲の中を検索し、最初に検査値に マッチしたセルの相対位置を返す。
検査値検査範囲照合の種類0：完全一致でマッチ、1：検査値以下の最大値にマッチ、-1：検査値以上の最小値にマッチ）。配列, 行番号, 列番号)配列範囲の中で、行番号の位置の値を返す
（配列が2次元の場合、行番号 列番号の位置の値を返す）。
配列行番号配列から抽出する要素の行番号。列番号配列から抽出する要素の列番号。2a. 構成比を算出
構成比は以下によって算出します。
構成比 = データ値 / データの合計
データの合計は、SUM()関数を使用し 全ての データ値を加算します。
2b. 累積構成比を算出
累積構成比は以下によって算出します。
累積構成比 = ある値までの累積値 / データの合計
ある値までの累積値は、SUM()関数を使用し 最初 ~ ある値 の データ値を加算します。 データの合計は、SUM()関数を使用し 全ての データ値を加算します。
3. A, B, Cのランク付け
MATCH()、INDEX()関数を使用して、 ランク設定 欄の値を紐づけし、
MATCH()関数 : 累積構成比の値と、ランク設定欄の下限値を比較し、行番号を取得。- ※ 第3引数を空（または
1設定）にすると、下限値 以下 の行番号が取得される
- ※ 第3引数を空（または
INDEX()関数 : 取得した行番号のデータを表示。