今回は、ExcelのPERCENTOF関数を使って、データの一部が全体に占める割合の計算を行う方法を紹介します。
売上の構成比、部署ごとの経費の比率、商品カテゴリ別のシェアなど、「全体のうちどれくらいか」を求める場面は多くあります。これまではSUM関数を2つ組み合わせて割り算をする方法が一般的でしたが、PERCENTOF関数を使うと、1つの関数で同じ計算を書けます。
この記事は、Microsoftの公式ヘルプで適用先とされているExcel for Microsoft 365を前提にしています。買い切り版のExcelでは、この関数を利用できない場合があります。
PERCENTOF関数の基本
PERCENTOF関数は、指定した一部の値を合計し、それを全体の値の合計で割った結果を返す関数です。
構文
=PERCENTOF(data_subset, data_all)
- data_subset(必須):割合を求めたい一部のデータ
- data_all(必須):全体を構成するデータ
SUM関数との関係
PERCENTOF関数は、論理的には=SUM(data_subset)/SUM(data_all)と同じ計算です。つまり、「一部の合計÷全体の合計」を1つの関数で表したものと考えると理解しやすくなります。
SUM関数で書く場合と比べると、次のような違いがあります。
- 数式の意図が関数名から読み取りやすい
- 割り算の記号やSUM関数を2回書く必要がない
- 後述するGROUPBY関数やPIVOTBY関数の集計方法として指定できる
基本的な使い方
たとえば、A列に商品名、B列に売上金額が入っている表があるとします(データはB2:B11)。このうち、上から2つの商品(B2:B3)が全体に占める割合を求めるには、次のように入力します。
=PERCENTOF(B2:B3, B2:B11)
結果は0.25のような小数で表示されます。パーセント表示にしたい場合は、セルを選択して[ホーム]タブの[パーセントスタイル]を適用します。小数点以下の桁数は[小数点以下の表示桁数を増やす]で調整できます。
1行ずつ構成比を求める
各商品の構成比を一覧にしたい場合は、C2セルに次の数式を入れて下方向へコピーします。
=PERCENTOF(B2, $B$2:$B$11)
全体の範囲を絶対参照にしておくのがポイントです。絶対参照にしないと、数式をコピーしたときに全体の範囲までずれてしまい、正しい割合になりません。範囲を選択した状態でF4キーを押すと、絶対参照の記号をまとめて付けられます。
条件に合うデータの割合を求める
「特定のカテゴリだけの割合」を求めたい場合は、FILTER関数と組み合わせる方法があります。FILTER関数で条件に合う値だけを取り出し、それをPERCENTOF関数の一部のデータとして渡します。
たとえば、A列が「カテゴリ」、B列が「売上」の表で、カテゴリが「文具」の売上が全体に占める割合を求めるには、次のように書けます。
=PERCENTOF(FILTER(B2:B11, A2:A11=”文具”), B2:B11)
条件をセル参照にしておけば、セルの値を変えるだけで別のカテゴリの割合に切り替えられます。
=PERCENTOF(FILTER(B2:B11, A2:A11=E1), B2:B11)
GROUPBY関数と組み合わせてグループ別の構成比を出す
PERCENTOF関数は、GROUPBY関数やPIVOTBY関数と一緒に使われることが多い関数です。GROUPBY関数の集計方法を指定する引数には、SUMやAVERAGE、COUNTなどと並んでPERCENTOFを指定できます。
=GROUPBY(A2:A11, B2:B11, PERCENTOF)
この数式では、A列のカテゴリごとにデータがまとめられ、各カテゴリの売上が全体に占める割合が一覧で表示されます。カテゴリ別に合計を出してから割り算をする、という2段階の作業を1つの数式で済ませられます。
合計と割合を並べて表示する
GROUPBY関数の集計方法には、複数の関数をまとめて指定することもできます。HSTACK関数で関数を横に並べると、合計と割合を隣り合った列で表示できます。
=GROUPBY(A2:A11, B2:B11, HSTACK(SUM, PERCENTOF))
金額と構成比を同時に確認できるため、報告用の集計表を作るときに役立ちます。結果の割合の列には、パーセントスタイルを適用しておくと読み取りやすくなります。
PIVOTBY関数ではクロス集計の割合に使える
PIVOTBY関数は、行と列の2つの軸でデータをまとめる関数です。集計方法にPERCENTOFを指定すると、「地域×商品」のようなクロス集計表で、それぞれの組み合わせが占める割合を確認できます。
PERCENTOF関数を使うときのコツ
- パーセントスタイルを先に設定しておく:結果は小数で返るため、表示形式を整えておくと読み間違いを防げます。
- 全体の範囲は絶対参照にする:数式をコピーして使う場合は、全体の範囲を固定しておきます。
- テーブルに変換しておく:データ範囲を[挿入]>[テーブル]でテーブルにしておくと、行を追加したときにも参照範囲を広げやすくなります。
- 合計値で検算する:1行ずつ構成比を出した場合は、構成比の列をSUM関数で合計して1(100%)になるかを確かめると、範囲の指定ミスに気づけます。
- 全体の合計が0にならないか確認する:全体の合計で割る計算のため、全体が0のデータでは正しい割合を求められません。
- 共有相手の環境を確認する:PERCENTOF関数に対応していないバージョンのExcelでファイルを開くと、数式が正しく計算されない場合があります。
まとめ
PERCENTOF関数は、「一部の合計÷全体の合計」という割合の計算を1つの関数で書ける関数です。
- 構文は=PERCENTOF(一部のデータ, 全体のデータ)
- 計算の内容は=SUM(一部)/SUM(全体)と同じ
- 結果は小数で返るため、パーセントスタイルで表示を整える
- FILTER関数と組み合わせると、条件に合うデータの割合を求められる
- GROUPBY関数やPIVOTBY関数の集計方法に指定すると、グループ別の構成比を一度に出せる
Excel for Microsoft 365を使っている場合は、構成比を求める数式をPERCENTOF関数に置き換えて、数式の読みやすさを比べてみてください。