関数でクロス集計をする （PIVOTBY 関数）その 1

PIVOTBY 関数を使うと、テーブルのデータをもとに、エリア × 担当者や日付 × 商品など、ピボットテーブルのようなわかりやすいクロス集計表を 1 つの数式で作成できます。
ピボットテーブルを作成せずに数式でグループごとの集計を行えるため、元データが変更されたとき、「更新」作業が不要です。

PIVOTBY 関数は、Microsoft 365 の Excel で使える関数で、SORT や FILTER などと同様にスピルに対応しており、引数による指定次第で集計方法や並べ替え、見出し、小計や合計の表示なども設定できます。

Excelに限らずですが、「BY」というキーワードのつく名前の関数があります。

この「BY」は「〜ごとに」という意味で、データを指定した単位でまとめて処理することを表していると覚えるとよいでしょう。
GROUPBY や PIVOTBYはなにごとに集計するのかの単位を指定する関数で、BYROWやBYCOLは行ごとや列ごとに処理を行う関数です。

PIVOTBY 関数の構文

必須なのは先頭の 4 つです。どの列をクロス集計表の行の項目として、どの列を列の項目として使い、どのように集計するか指定します。先ほどの数式では、先頭の 4 つ以外は省略しています。

=PIVOTBY (行フィールド, 列フィールド, 値, 関数, [フィールドヘッダー], [行の集計], [行の並べ替え順], [列の集計], [列の並べ替え順],[フィルター], [2 つ目の引数の基準値])

	引数	説明
1	行フィールド (必須)	横方向 (行) は何ごとにまとめるのか、行のグループ化に使う列を指定する。複数列の指定も可能。
2	列フィールド (必須)	縦方向 (列) は何ごとにまとめるのか、列のグループ化に使う列を指定する。複数列の指定も可能。
3	値 (必須)	集計する列を指定する。合計や件数を求めるデータが格納されている列。複数列の指定も可能。
4	関数 (必須)	SUM、COUNT、AVERAGE、PERCENTOF などを使って、値の集計方法を指定する。
5	[フィールドヘッダー]	見出し行の表示 / 非表示を指定する。省略するか 1 を指定すると表示されない。
6	[行の集計]	行の総計や小計の表示 / 非表示を指定する。省略するか 1 を指定すると総計のみ表示される。
7	[行の並べ替え順]	結果の行の並べ替え順 (昇順 / 降順) を列の位置で指定する。「-1」だと 1列目を降順で並べ替えられる。複数列をキーにする場合は、{-1，-2} のように括弧で指定する。
8	[列の集計]	列の総計や小計の表示 / 非表示を指定する。省略するか 1 を指定すると総計のみ表示される。
9	[列の並べ替え順]	結果の列の並べ替え順 (昇順 / 降順) の行の位置で指定する。「-1」だと 1 行目を降順で並べ替えられる。複数行をキーにする場合は、{-1，-2} のように括弧で指定する。
10	[フィルター]	特定のエリアなど、「エリア列が "エリアA"」のように行を絞り込む条件を指定する。
11	[2つ目の引数の基準値]	関数で 2 つの引数が指定されている場合に、2番目の引数の値に指定される基準値の種類を指定する。PERCENTOF など。

PIVOTBY による集計

ここではシンプルに、必須の引数だけを指定して、[出荷履歴テーブル] の [エリア] ごとの [担当 ID] ごとの [出荷数量] 列の値の合計を算出します。行の見出しにエリアを、列の見出しに担当 ID を表示するようにします。1 行目の見出しも範囲に含めてやってみます。

第 1 引数の 行フィールド に、何ごとに行をグループ化するのか、見出しも含めて列を指定します。

複数の列を指定することもできます。
第 2 引数の 列フィールド に、何ごとに列をグループ化するのか、見出しも含めて列を指定します。
第 3 引数の値に、集計する列を指定します。
第 4 引数の関数に、集計方法に合う関数を指定し、数式を確定します。
エリアごとの担当ID ごとの出荷数量の合計値が表示されます。
見出しはなし、総計は表示されています。

複数列を指定した場合はこんな感じ↓
日付の昇順で並び、日付内でエリアの昇順に並んでいます。
数式を編集状態にして、第 5 引数に「3」を指定します。
見出しが表示されます

複数列を指定した場合はこんな感じ↓
テーブルのデータが変更されたり、追加や削除が行われたりしたら、反映されます。