関数でクロス集計をする (PIVOTBY 関数) 1 で基本をご紹介した PIVOTBY 関数の、並べ替えとフィルターについてご紹介します。
PIVOTBY 関数は、テーブルのデータをもとに、エリア × 担当者 や 日付 × 商品 など、ピボットテーブルのようなわかりやすいクロス集計表を数式で作成するときに使える関数で、ピボットテーブルを作成せずに数式でグループごとのクロス集計を行えます。
たとえば下図では、表の [日付] と [サイズ] の組み合わせごとの、部門ごとの出荷数量の合計を算出しています。
4/3 の S2 のD1 の出荷数量の合計が 20 になっていることがわかります。
なお、既定で [日付] の昇順、[サイズ] の昇順に並んでいることがわかります。
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 など。 |
並べ替えを指定する
先ほどの数式を編集して、第 7 引数に「-2」を指定して、2列目 ([サイズ]) の降順にします。 [日付] の昇順で並び、同じ日付の中で、サイズの降順に並びます。
フィルターで絞り込む
第 10 引数に、比較演算子を使った論理式を用いて、フィルター処理を指定できます。
たとえば下図では、部門が D1 のデータに絞り込んでいます。
PIVOTBY 関数は、ピボットテーブルのようなわかりやすいクロス集計表を数式で作成するときに使える関数で、ピボットテーブルを作成せずに数式でグループごとのクロス集計を行えます。引数による指定次第で、並べ替えやフィルターなども設定できるため、1つの関数、数式で、効率よくテーブルのデータをクロス集計表にできます。
ちょっと慣れは必要だけど。
石田 かのこ
