2025/5/7

エクセルで構成比率や累積構成比(累積比率)を算出：ABC分析・パレート図に応用【コピペ用テンプレートあり】

Thumbnail for エクセルで構成比率や累積構成比(累積比率)を算出：ABC分析・パレート図に応用【コピペ用テンプレートあり】

はじめに

エクセルで、 構成比や累積構成比を算出し、する方法を紹介します。 これらを計算することで、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列をデータと同じ行までコピー（オートフィル）してください。

ABCDEFGHIJKLM
1データ並び替えデータランク設定
2ラベルX順位順位=A2=B2構成比 (%)累積構成比 (%)ランクランク下限
3=G3/SUM(G:G)*100=SUM(G$3:G3)/SUM(G:G)*100=INDEX($L$3:$L$5,MATCH(I3,$M$3:$M$5))A0
4a100=RANK.EQ(B4,B:B)+COUNTIF(B$1:B2,B4)=ROW()-ROW(E$3)=INDEX(A:A,MATCH($E4,$C:$C,0))=INDEX(B:B,MATCH($E4,$C:$C,0))=G4/SUM(G:G)*100=SUM(G$3:G4)/SUM(G:G)*100=INDEX($L$3:$L$5,MATCH(I4,$M$3:$M$5))B80
5b500=RANK.EQ(B5,B:B)+COUNTIF(B$1:B4,B5)=ROW()-ROW(E$3)=INDEX(A:A,MATCH($E5,$C:$C,0))=INDEX(B:B,MATCH($E5,$C:$C,0))=G5/SUM(G:G)*100=SUM(G$3:G5)/SUM(G:G)*100=INDEX($L$3:$L$5,MATCH(I5,$M$3:$M$5))C90
6c300=RANK.EQ(B6,B:B)+COUNTIF(B$1:B5,B6)=ROW()-ROW(E$3)=INDEX(A:A,MATCH($E6,$C:$C,0))=INDEX(B:B,MATCH($E6,$C:$C,0))=G6/SUM(G:G)*100=SUM(G$3:G6)/SUM(G:G)*100=INDEX($L$3:$L$5,MATCH(I6,$M$3:$M$5))
7d1000=RANK.EQ(B7,B:B)+COUNTIF(B$1:B6,B7)=ROW()-ROW(E$3)=INDEX(A:A,MATCH($E7,$C:$C,0))=INDEX(B:B,MATCH($E7,$C:$C,0))=G7/SUM(G:G)*100=SUM(G$3:G7)/SUM(G:G)*100=INDEX($L$3:$L$5,MATCH(I7,$M$3:$M$5))
8e300=RANK.EQ(B8,B:B)+COUNTIF(B$1:B7,B8)=ROW()-ROW(E$3)=INDEX(A:A,MATCH($E8,$C:$C,0))=INDEX(B:B,MATCH($E8,$C:$C,0))=G8/SUM(G:G)*100=SUM(G$3:G8)/SUM(G:G)*100=INDEX($L$3:$L$5,MATCH(I8,$M$3:$M$5))
9
10
Jspreadsheet CE

算出手順

構成比と累積構成比の算出、 A, B, Cのランク付けを行う手順は以下の通りです。

1. データを並び替え

データを昇順に並び替えるため、以下の処理を行います。

  • i. データを降順で順位付け
  • ii. 順位に従って、データを表示
i. データを降順で順位付け

RANK.EQ()、COUNTIF()関数を使用して、各データの順位を算出します。 それぞれの関数の役割は次の通りです。

  • RANK.EQ()関数 : データの降順の順位を算出。ただし同順位があると重複してしまう。
  • COUNTIF()関数 : 重複数をカウント。同順位があるときの重複を回避するために使用。

RANK.EQ関数

第2引数の範囲のデータ列中で、第1引数のデータが何番目に位置するかを算出。
同順位の場合は、同順位内の最上位の値が返される。
第3引数は0または1で、降順または昇順のどちらで順位付けするかを設定。

例：=RANK.EQ(B2,B:B)

  • 第1引数 (B2): 順位付けするデータ
  • 第2引数 (B:B): 順位付けするデータ列の全体範囲
  • 第3引数 : 降順(0) or 昇順(1) を設定（省略時は降順）
ii. 順位に従って、データを表示

MATCH()、INDEX()関数を使用して、順位に従ってデータを表示します。 それぞれの関数の役割は次の通りです。

  • MATCH()関数 : iで算出した順位が、1のとき, 2のとき,... の行番号を取得。
  • INDEX()関数 : 取得した行番号のデータを表示。

MATCH関数

指定した範囲の中で検索し、最初にヒットした相対位置を返す。

例：=MATCH(3,$C:$C,0)

  • 第1引数 (3): 検索値（必須）
  • 第2引数 ($C:$C): 検索範囲（必須）
  • 第3引数 (0): 照合の型を 0, -1, 1 で指定（オプション）
    • 0: 完全一致したセルを返す
    • 1: 検索値以下の最大値のセルを返す（デフォルト）
    • -1: 検索値以上の最小値のセルを返す

INDEX関数

指定した範囲の中で、m番目（m行n列目）の値を返す
（m, nはそれぞれ第1引数、第2引数で指定）。

例：=INDEX($A:$A,2)) A列の2行目の値を返す

  • 第1引数 ($A:$A): 抽出する範囲（必須）
  • 第2引数 (2): 範囲から抽出する要素番号・行番号（必須）
  • 第3引数 : 範囲から抽出する列番号（オプション）

2a. 構成比を算出

構成比は以下によって算出します。
構成比 = データ値 / データの合計

データの合計は、SUM()関数を使用し 全ての データ値を加算します。 パーセント表示にするため、ここでは100倍しています。

2b. 累積構成比を算出

累積構成比は以下によって算出します。
累積構成比 = ある値までの累積値 / データの合計

ある値までの累積値は、SUM()関数を使用し 最初 ~ ある値 の データ値を加算します。 データの合計は、SUM()関数を使用し 全ての データ値を加算します。 パーセント表示にするため、ここでは100倍しています。

3. A, B, Cのランク付け

MATCH()、INDEX()関数を使用して、 累積構成比の値と ランク設定 欄の値を紐づけし、 対応したランクを表示します。 それぞれの関数の役割は次の通りです。

  • MATCH()関数 : 累積構成比の値と、ランク設定 欄の 下限 値を比較し、行番号を取得。
    • ※ 第3引数を空（または1設定）にすると、下限 値 以下 の行番号が取得される
  • INDEX()関数 : 取得した行番号のデータを表示。