PIVOTBY ● 縦横グループ化してクロス集計する (2)行/列と値の指定方法

［Microsoft 365］

Excelの PIVOTBY（ピボット・バイ）関数は、表のデータをもとにクロス集計を行う関数です。クロス集計の縦横の項目は第1引数［行フィールド］と第2引数［列フィールド］で、集計対象の値は第3引数［値］で指定します。ここではこれらの引数のさまざまな指定方法を紹介します。

PIVOTBY関数の基本的な使い方やその他の引数については、以下の記事を参考にしてください。

1 書式
2 使用例1…基本的なクロス集計
3 使用例2…［行フィールド］［列フィールド］［値］に離れた列を指定する
4 使用例3…テーブルの列を指定する
5 使用例4…別表から表引きしたデータでグループ化する
6 使用例5…日付から「年月」を取り出して月ごとに集計する
7 使用例6…計算結果を集計する

書式

縦横グループ化してクロス集計する
=PIVOTBY(行フィールド, 列フィールド, 値, 関数, ［ヘッダー］, ［行集計深さ］, ［行並べ替え］, ［列集計深さ］, ［列並べ替え］, ［フィルター］, ［対象］)

［行フィールド］をグループ化して縦軸に、［列フィールド］をグループ化して横軸に配置し、［関数］で指定した方法で［値］を集計します。関数式は、動的配列数式として入力されます。

■引数

引数	説明
行フィールド	グループ化して縦軸に配置する項目を配列やセル参照で指定する。複数の項目を指定可能。
列フィールド	グループ化して横軸に配置する項目を配列やセル参照で指定する。複数の項目を指定可能。
値	集計対象の項目を配列やセル参照で指定する。複数の項目を指定可能。
関数	集計方法を指定する。選択肢は SUM、PERCENTOF、AVERAGE、MEDIAN、COUNT、COUNTA、MAX、MIN、PRODUCT、ARRAYTOTEXT、CONCAT、SRDEV.S、STDEV.P、VAR.S、VAR.P、MODE.SNGL、LAMBDA。
ヘッダー	引数［行フィールド］［列フィールド］［値］を指定する際に項目名を含めて指定したか、戻り値に項目名を表示するかを次の数値で指定する。省略時はExcelが自動判断する。 0 ：［行フィールド］［列フィールド］［値］に項目名が含まれない。戻り値に項目名を表示しない。 1 ：［行フィールド］［列フィールド］［値］に項目名が含まれる。戻り値に項目名を表示しない。 2 ：［行フィールド］［列フィールド］［値］に項目名が含まれない。戻り値に「行フィールド1」「列フィールド1」「値1」のような項目名を自動表示する。 3 ：［行フィールド］［列フィールド］［値］に項目名が含まれる。戻り値に項目名を表示する。
行集計深さ	小計行や総計行を表示するかどうかを次の数値で指定する。省略時はExcelが自動判断する。 0 ：小計行や総計行を表示しない。 1 ：総計行を表示する。 2 ：小計行と総計行を表示する。 -1 ：上部に総計行を表示する。 -2 ：上部に小計行と総計行を表示する。
行並べ替え	行単位で並べ替える際の基準となる項目の列の番号を、昇順の場合は正数で、降順の場合は負数で指定する。列の番号は、［行フィールド］と［値］を横に並べて左から数えた番号で指定する。省略時は［行フィールド］の昇順で並べ替えられる。
列集計深さ	小計列や総計列を表示するかどうかを次の数値で指定する。省略時はExcelが自動判断する。 0 ：小計列や総計列を表示しない。 1 ：総計列を表示する。 2 ：小計列と総計列を表示する。 -1 ：左部に総計列を表示する。 -2 ：左部に小計列と総計列を表示する。
列並べ替え	列単位で並べ替える際の基準となる項目の列の番号を、昇順の場合は正数で、降順の場合は負数で指定する。列の番号は、［列フィールド］と［値］を横に並べて左から数えた番号で指定する。省略時は［列フィールド］の昇順で並べ替えられる。
フィルター	集計対象を絞り込むための抽出条件を指定する。
対象	［関数］に「PERCENTOF」のように引数を2つ持つ関数を指定した場合に、2番目の関数に渡す内容を次の数値で指定する。 0 ：列の合計（既定値） 1 ：行の合計 2 ：総合計 3 ：上位の列項目の合計 4 ：上位の行項目の合計

使用例1…基本的なクロス集計

下図では、第1引数［行フィールド］に「分類」「商品」、第2引数［列フィールド］に「地域」、第3引数［値］に「売上」のセルを指定しています。縦軸に「分類」「商品」、横軸に「地域」が配置され、売上がクロス集計されます。

=PIVOTBY(行フィールド, 列フィールド, 値, 関数)

セル I1 | =PIVOTBY(D2:E27,B2:B27,F2:F27,SUM)

図1　分類、商品ごと地域ごとに売上を集計する。
集計結果の数値のセル（セルK2～M6）には、手動で通貨表示形式が設定してある。

使用例2…［行フィールド］［列フィールド］［値］に離れた列を指定する

［行フィールド］［列フィールド］［値］には、それぞれ複数の項目を指定できます。隣り合った項目の場合は、「D2:E27」のようにひとまとめで指定します。離れた項目を指定したい場合は、HSTACK関数を使用します。この関数は、引数に指定した複数の配列を左右に結合する関数です。

下図ではHSTACK関数を使用して、B列の「地域」とD列の「店舗」を結合してから、［行フィールド］に指定しています。

=PIVOTBY(行フィールド, 列フィールド, 値, 関数)

セル I1 | =PIVOTBY(HSTACK(B2:B27,D2:D27),E2:E27,G2:G27,SUM)

図2　元表の離れた位置にある項目を指定して売上を集計する。

［値］にHSTACK関数を指定することもできます。下図では「数量」のセルと「売上」のセルを結合して、［値］に指定しています。

セル I1 | =PIVOTBY(D2:D27,B2:B27,HSTACK(E2:E27,G2:G27),SUM)

図3　元表の離れた位置にある「数量」「売上」を集計する。地区ごとに数量と売上の集計値が横に並ぶ。集計結果のN列とO列の合計が空欄になるのはバグ？

上図のクロス集計表では、数量と売上の区別がつきません。このようなときは、［行フィールド］［列フィールド］［値］に元表の1行目の項目名のセルを含めて指定し、第5引数［ヘッダー］に「3」を指定すると、下図のように集計表上に「地域」「商品」「数量」「売上」の文字が表示されるので、数値の種類が区別できます。