［Microsoft 365］
Excelの PIVOTBY（ピボット・バイ）関数は、表のデータをもとにクロス集計を行う関数です。集計の計算方法は、第4引数［関数］で指定します。ここでは［関数］のさまざまな指定方法を紹介します。
PIVOTBY関数の基本的な使い方やその他の引数については、以下の記事を参考にしてください。
目次
書式
縦横グループ化してクロス集計する
=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 ： 上位の行項目の合計 |
第4引数［関数］の選択肢
PIVOTBY関数の第4引数［関数］は、集計の計算方法を指定するための引数です。「SUM」を指定すれば合計、「AVERAGE」を指定すれば平均、という具合にSUM関数やAVERAGE関数の機能を使用した集計が行われます。ポップヒントの選択肢から選ぶだけで簡単に入力できます。
図1 第4引数［関数］はポップヒントから選択できる。
選択肢には下表の種類があります。表中の「イータ縮小ラムダ」「明示的なLAMBDA関数」については、本記事の最後に解説します。
■［関数］の選択肢
| 選択肢（イータ縮小ラムダ） | 説明 | 明示的なLAMBDA関数 |
|---|---|---|
| SUM | 合計 | LAMBDA(x,SUM(x)) |
| PERCENTOF | 割合 | LAMBDA(x,y,PERCENTOF(x,y)) |
| AVERAGE | 平均 | LAMBDA(x,AVERAGE(x)) |
| MEDIAN | 中央値 | LAMBDA(x,MEDIAN(x)) |
| COUNT | 数値の数 | LAMBDA(x,COUNT(x)) |
| COUNTA | データ数 | LAMBDA(x,COUNTA(x)) |
| MAX | 最大値 | LAMBDA(x,MAX(x)) |
| MIN | 最小値 | LAMBDA(x,MIN(x)) |
| PRODUCT | 積 | LAMBDA(x,PRODUCT(x)) |
| ARRAYTOTEXT | 「,」で区切った文字列結合 | LAMBDA(x,ARRAYTOTEXT(x)) |
| CONCAT | 文字列結合 | LAMBDA(x,CONCAT(x)) |
| STDEV.S | 不偏標準偏差 | LAMBDA(x,STDEV.S(x)) |
| STDEV.P | 標本標準偏差 | LAMBDA(x,STDEV.P(x)) |
| VAR.S | 不偏分散 | LAMBDA(x,VAR.S(x)) |
| VAR.P | 標本分散 | LAMBDA(x,VAR.P(x)) |
| MODE.SNGL | 最頻値 | LAMBDA(x,MODE.SNGL(x)) |
| LAMBDA | 独自に定義した関数 |
使用例1…基本的なクロス集計
下図では、第1引数［行フィールド］に「商品」、第2引数［列フィールド］に「地域」、第3引数［値］に「売上」のセルを指定し、第4引数［関数］に「SUM」を指定しています。縦軸に「商品」、横軸に「地域」が配置され、売上がクロス集計されます。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数)
セルH2 | =PIVOTBY(E3:E26,B3:B26,F3:F26,SUM)
図2 商品ごと地域ごとに売上を集計する。
集計結果の数値のセル（セル I3～K7）には、手動で桁区切りスタイルが設定してある。
使用例2…平均を求める
クロス集計では合計を求めることがほとんどだと思いますが、PIVOTBY関数では合計以外の計算方法も使えます。下図では、［関数］に「AVERAGE」を指定して、組ごと科目ごとに平均点を求めています。
セルG2 | =PIVOTBY(C3:C26,D3:D26,E3:E26,AVERAGE)
図3 「AVERAGE」を使用して、組ごと科目ごとに平均点を求める。
PIVOTBY関数では、どの種類の計算をした場合も見出しには「合計」「総計」などと表示されます。上のクロス集計表でも、セルJ2とセルG6の見出しに「合計」と表示されています。次の図では、MAP関数とLAMBDA関数を使用して、「合計」の文字を「平均」に変えてみました。
セルG2 | =MAP(PIVOTBY(C3:C26,D3:D26,E3:E26,AVERAGE),LAMBDA(x,IF(x="合計","平均",x)))
図4 「合計」の文字を「平均」に変える。
使用例3…データ数を求める
下図では「COUNT」を使用して、受験者数を求めています。COUNT関数は数値データのみを数える関数なので、［値］には数値データのセル範囲を指定します。
セルG2 | =PIVOTBY(C3:C26,D3:D26,E3:E26,COUNT)
図5 「COUNT」を使用して、組ごと科目ごとに受験者数を求める。
ちなみに［値］に「氏名」欄のセルB3～B26を指定した場合は、［関数］に「COUNTA」を指定します。COUNTA関数は、文字、数値、日付など、あらゆるデータの数を求める関数です。
メモ
使用例4…「最高点と最低点」のように2つの関数を指定する
1列分の［値］に対して複数の計算方法で集計するには、HSTACK関数を使用します。この関数は、引数に指定した複数の配列を左右に結合する関数です。
下図では、組ごと科目ごとに得点の最高点、最低点を求めています。
セルG2 | =PIVOTBY(C3:C26,D3:D26,E3:E26,HSTACK(MAX,MIN))
図6 HSTACK関数を使用して「MAX」「MIN」を計算する。
LAMBDA関数とイータ縮小ラムダ
ヘルプによると、PIVOTBY関数の第4引数［関数］には「LAMBDA関数またはetaの縮小ラムダ」を指定するとあります。［関数］のポップヒントには「SUM」「AVERAGE」「COUNT」などの関数名が並びますが、これらはLAMBDA関数の数式を簡略化したもので「イータ縮小ラムダ関数」と呼びます。
例えばイータ縮小ラムダ関数「SUM」は、「LAMBDA(x,SUM(x))」というLAMBDA関数式の簡略版です。PIVOTBY関数の第4引数［関数］には本来「LAMBDA(x,SUM(x))」と記述するところを「SUM」と簡略記述してもよいことになっています。次の2つの数式は同じ結果を戻します。
セルH2 | =PIVOTBY(E3:E26,B3:B26,F3:F26,SUM)
セルH2 | =PIVOTBY(E3:E26,B3:B26,F3:F26,LAMBDA(x,SUM(x)))
図7 ［関数］にはイータ縮小ラムダ関数とLAMBDA関数のどちらを指定してもよい。