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順位順位ラベルX構成比 (%)累積構成比 (%)ランクランク下限
300AA0
4a10051d100045.45545.455AB80
5b50022b50022.72768.182AC90
6c30033c30013.63681.818B
7d100014e30013.63695.455C
8e30045a1004.545100C

算出手順

構成比と累積構成比の算出、 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()関数 : 取得した行番号のデータを表示。