今回は、ExcelのPIVOTBY関数を使って、数式だけでクロス集計表を作る方法と、引数を使い分けるコツを紹介します。
PIVOTBY関数は比較的新しい関数で、主にMicrosoft 365のExcelで使えます。この記事はWindows版のMicrosoft 365のExcelを前提にしています。セルに「=PIV」と入力しても候補に表示されない場合は、お使いのExcelがこの関数に対応していない可能性があります。
PIVOTBY関数とは
PIVOTBY関数は、データを行と列の2つの軸でグループ化し、値を集計する関数です。たとえば売上の明細から「商品別×年別の売上合計」のような表を、数式ひとつで作れます。
できあがる表はピボットテーブルに似ていますが、Microsoftの説明でも、PIVOTBY関数はピボットテーブル機能とは直接の関係がないとされています。違いを整理すると次のようになります。
- ピボットテーブルは画面操作で作り、元データが変わったら更新操作が必要
- PIVOTBY関数は数式なので、元データを書き換えると結果も再計算される
- PIVOTBY関数の結果は複数のセルに広がって表示される（スピル）
集計表を報告書の一部として常に最新の状態にしておきたいときや、ほかの数式と組み合わせたいときに、PIVOTBY関数が役立ちます。
PIVOTBY関数の書式
PIVOTBY関数の書式は次のとおりです。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数, [フィールドヘッダー], [行の合計の深さ], [行の並べ替え順序], [列の合計の深さ], [列の並べ替え順序], [フィルター配列], [相対])
引数は多く見えますが、必須なのは最初の4つだけです。
- 行フィールド：行の見出しにしたい項目の範囲（例：商品名の列）
- 列フィールド：列の見出しにしたい項目の範囲（例：年の列）
- 値：集計したい数値の範囲（例：売上の列）
- 関数：集計の方法。SUM、AVERAGE、COUNTなどの関数名を指定する
残りの引数は、見出しの扱い、合計の表示、並べ替え、絞り込みなどを調整したいときに使います。
基本の使い方：商品別×年別の売上を集計する
ここでは、次のような売上データがA1:D101にあるとします。1行目は見出しです。
- A列：商品
- B列：地域
- C列：年
- D列：売上
商品を行に、年を列にして売上を合計するには、空いているセルに次のように入力します。
=PIVOTBY(A2:A101,C2:C101,D2:D101,SUM)
入力すると、商品名が縦に、年が横に並んだ集計表が表示されます。関数の引数には「SUM」のように関数名だけを書き、かっこは付けない点がポイントです。合計の代わりに平均を出したい場合はAVERAGE、件数を数えたい場合はCOUNTAなどに置き換えます。
スピルする範囲を空けておく
PIVOTBY関数の結果は、入力したセルから右下へ広がって表示されます。広がる先のセルにデータが入っていると、結果が表示されずにエラーになります。集計表を置く場所の右側と下側は空けておくのが基本です。項目が増えると表も大きくなるので、余裕をもって配置しましょう。
オプション引数を使いこなすコツ
見出し行を含めて指定する
範囲を見出し行から選んだ場合は、5つ目の引数「フィールドヘッダー」で扱いを指定できます。
- 0：見出しなし
- 1：見出しあり、結果には表示しない
- 2：見出しなし、見出しを生成する
- 3：見出しあり、結果にも表示する
たとえば次の式では、1行目の見出しを含めて範囲を指定し、結果にも見出しを表示します。
=PIVOTBY(A1:A101,C1:C101,D1:D101,SUM,3)
省略した場合は、値の範囲の1つ目がテキストで2つ目が数値なら見出しありと判断されます。意図しない判定を避けたいときは、明示的に指定しておくと安心です。
合計行・合計列の表示を切り替える
6つ目の引数「行の合計の深さ」と8つ目の引数「列の合計の深さ」で、総計や小計の表示を調整できます。
- 0：合計を表示しない
- 1：総計を表示する
- 2：総計と小計を表示する
- -1、-2：合計を上側に表示する
小計を出すには、行フィールド（または列フィールド）に2列以上を指定する必要があります。たとえば行フィールドにA列とB列をまとめて指定すると、商品と地域の2階層の表になり、小計を表示できるようになります。
売上の多い順に並べ替える
7つ目の引数「行の並べ替え順序」には、並べ替えの基準となる列の番号を指定します。番号は、行フィールドの列から数え始め、その後に値の列が続きます。マイナスを付けると降順になります。
行フィールドが1列、値が1列の場合、値の列は2番目にあたるので、売上の多い順にするには-2を指定します。
=PIVOTBY(A2:A101,C2:C101,D2:D101,SUM,,,-2)
途中の引数を省略するときは、カンマだけを続けて書きます。
条件で絞り込んでから集計する
10番目の引数「フィルター配列」には、集計に含める行をTRUE／FALSEで示す配列を指定します。たとえば地域が「東日本」の行だけを集計したい場合は、次のように書けます。
=PIVOTBY(A2:A101,C2:C101,D2:D101,SUM,,,,,,B2:B101=”東日本”)
フィルター配列の長さは、行フィールドや列フィールドと同じにそろえる必要があります。範囲の行数がずれているとエラーの原因になるので、開始行と終了行を合わせることを意識しましょう。
構成比を表示する
売上の金額ではなく、全体に占める割合を見たい場合は、関数にPERCENTOFを指定し、11番目の引数「相対」で何に対する割合かを選びます。
- 0：列の合計に対する割合（既定）
- 1：行の合計に対する割合
- 2：総計に対する割合
- 3、4：親の列合計、親の行合計に対する割合
年ごとの商品構成を比べたいなら列の合計に対する割合、商品ごとの年別推移を見たいなら行の合計に対する割合、というように目的に合わせて選びます。結果のセルにはパーセント表示の書式を設定しておくと読みやすくなります。
PIVOTBY関数を使うときの工夫
- 元データをテーブルに変換し、構造化参照で指定すると、行を追加したときに範囲を修正する手間が減る
- 行フィールドと列フィールドに同じ項目を指定しないよう、どの軸で比較したいかを先に決める
- 集計方法を変えるときは、関数の引数だけを差し替えればよい
- 複数の集計を並べたい場合は、関数の引数に関数を並べた配列を指定できる
- 行方向だけの集計で十分な場合は、同じ系統のGROUPBY関数を使う方がシンプルになる
まとめ
PIVOTBY関数を使うと、行と列の2つの軸でデータをまとめたクロス集計表を数式ひとつで作れます。必須の引数は行フィールド、列フィールド、値、関数の4つで、まずはSUMで合計を出すところから試すとわかりやすいでしょう。
慣れてきたら、見出しの扱い、合計の表示、並べ替え、フィルター配列、構成比といったオプション引数を加えていくと、目的に合った集計表に仕上げられます。元データの変更に合わせて結果が自動で更新される点を活かして、定期的に作る集計表の作業を効率化してみてください。