PIVOTBY¶
種類: MS Excel 互換関数 · ネイティブ導入: Microsoft 365
行キーと列キーでデータをピボットし値を集計します。集計子はテキスト(SUM, COUNT…)。
構文¶
=PIVOTBY(行フィールド, 列フィールド, 値, 関数, [見出し], [行合計深さ], [行並び順], [列合計深さ], [列並び順], [フィルター配列], [基準])
引数¶
| 引数 | 必須 | 説明 |
|---|---|---|
| 行フィールド | 必須 | 行キー列(複数可) |
| 列フィールド | 必須 | 列キー列(複数可) |
| 値 | 必須 | 集計する値列(複数可) |
| 関数 | 必須 | 集計子テキスト: SUM/AVERAGE/COUNT/COUNTA/MAX/MIN/PRODUCT/MEDIAN/MODE.SNGL/STDEV.S/STDEV.P/VAR.S/VAR.P/CONCAT/ARRAYTOTEXT/PERCENTOF + 拡張(パラメータ=relative_to): TEXTJOIN/LARGE/SMALL/PERCENTILE·QUARTILE(.INC/.EXC) のいずれか |
| 見出し | 省略可能 | 0 なし・非表示, 1 あり・非表示, 2 なし・表示, 3 あり・表示 (既定 自動) |
| 行合計深さ | 省略可能 | 0 なし, 1 総計(既定), 2 総計+小計; 負数=上部 |
| 行並び順 | 省略可能 | 行の並び: 列番号、負数は降順 |
| 列合計深さ | 省略可能 | 0 なし, 1 合計列(既定), 2 合計+小計列; 負数=左 |
| 列並び順 | 省略可能 | ±フィールド番号(複数可)、負数は降順 |
| フィルター配列 | 省略可能 | 行ごとの TRUE/FALSE |
| 基準 | 省略可能 | PERCENTOF の分母 0~2(既定 0 総計); 拡張集計子ではパラメータ: TEXTJOIN 区切り, LARGE/SMALL k, PERCENTILE p(0~1), QUARTILE 0~4 |
戻り値¶
列フィールドごとに 1 行の見出し行を含む、行キー × 列キーのクロス集計表を 2 次元配列としてスピルで返し、既定で合計行・列が付きます。row_fields・col_fields・values はネイティブと同様、それぞれ複数列を受け付けます。集計子やオプション値が無効な場合、または PERCENTOF の relative_to が 0~2 の範囲外の場合は #VALUE!、フィルターを通過した行がない場合は #N/A を返し、データのない交差セルは小計行・列でも空文字列になります。
例¶
| 数式 | 結果 | 説明 |
|---|---|---|
=PIVOTBY({"a";"b";"a"},{"x";"x";"y"},{10;20;30},"SUM") |
{"","x","y","Total";"a",10,30,40;"b",20,"",20;"Total",30,30,60} | 行×列の合計ピボット |
=PIVOTBY({"a";"a";"b"},{"x","p";"x","q";"y","p"},{10;20;30},"SUM") |
{"","x","x","y","Total";"","p","q","p","";"a",10,20,"",30;"b","","",30,30;"Total",10,20,30,60} | 列フィールド 2 つ → 見出し 2 行 |
解説¶
- function 引数はネイティブのラムダの代わりにテキストで指定します。サポートされる集計子は SUM/AVERAGE/COUNT/COUNTA/MAX/MIN/PRODUCT/MEDIAN/MODE.SNGL/STDEV.S/STDEV.P/VAR.S/VAR.P/CONCAT/ARRAYTOTEXT/PERCENTOF の 16 種(ネイティブと同一)で、EGTools 拡張として TEXTJOIN/LARGE/SMALL/PERCENTILE・QUARTILE(.INC/.EXC) もサポートします - 拡張集計子のパラメーター(TEXTJOIN の区切り文字、LARGE/SMALL の k、PERCENTILE の p、QUARTILE の 0~4)は relative_to 引数で渡します(それ以外の名前は #VALUE!)。
- row_fields・col_fields・values はそれぞれ複数列を受け付けます(ネイティブと同一): 列フィールドごとに見出し行が 1 行ずつ増え、各列グループの下に values の列数分の値列がネストされます。値が 2 列以上でも、合計・小計「列」のデータセルは値列ごとに集計して埋めます - ネイティブはこれらのセルを空欄のままにするため、この部分のみネイティブの出力と異なります(意図的な差異)。field_headers 2・3(表示モード)では、先頭に列フィールド名を ", " で連結した行、列キー行、行フィールド名と値名の行の順で見出しが出力されます。
- row_total_depth・col_total_depth の 2 は、最初の行/列フィールドのブロックごとの小計行・列を追加します(その軸にフィールドが 2 つ以上必要。満たさない場合は 1 として動作)。負数は合計を上部・左側に配置します。row_sort_order・col_sort_order は ±フィールド番号(配列も可)で、並べ替えはフィールドの階層順を保ったまま指定フィールドの方向のみを変えます。
- PERCENTOF の relative_to は本実装では 0(列合計基準、既定)/1(行合計基準)/2(総合計基準)のみをサポートし、3・4(親基準)は #VALUE! を返します。合計行/列のラベルは UI 言語に従います(韓国語 UI: 합계/총합계、英語 UI: Total/Grand Total)。
- 対応: Excel 2010+。ネイティブ関数がない旧バージョンでは
PIVOTBYの名前のまま（ドロップイン）、ネイティブ関数がある新しい Excel ではEG.PIVOTBYとして登録されます。