PIVOTBY 関数は、表を「行」と「列」の 2 つの見出しでまとめて、クロス集計表を作る関数です。=PIVOTBY(分類の列, 担当の列, 金額の列, SUM) と書くと、分類を縦、担当を横にした合計の表ができます。ピボットテーブルと違って式なので、元の表を書き換えるとすぐ結果も変わります。
この記事では、PIVOTBY の基本から、件数にする・合計の行と列を消す・割合で見る方法まで、実際の Excel で確かめた結果で説明します。PIVOTBY は Microsoft 365 の新しい関数です。縦の 1 方向だけでよいときは GROUPBY 関数を使います。
PIVOTBY 関数の書き方
=PIVOTBY(行フィールド, 列フィールド, 値, 関数, [フィールドヘッダー], [行の合計の深さ], [行の並べ替え順序], [列の合計の深さ], [列の並べ替え順序], [フィルター配列], [相対])| 引数 | 意味 |
|---|---|
| 行フィールド | 縦に並べる見出しの列（分類など） |
| 列フィールド | 横に並べる見出しの列（担当など） |
| 値 | 集計する列（金額など） |
| 関数 | SUM・COUNT・AVERAGE・PERCENTOF など |
| 行の合計の深さ（省略可） | いちばん下の合計行を出すか（0 で出さない） |
| 列の合計の深さ（省略可） | いちばん右の合計列を出すか（0 で出さない） |
| フィルター配列（省略可） | 集計する行の条件 |
結果は右下に広がって（スピル）表示されます。結果を出す場所の右と下は空けておきます。
1. 分類×担当の合計表を作る
分類（A 列）を縦、担当（C 列）を横にして、金額（D 列）を合計します。
=PIVOTBY(A2:A11,C2:C11,D2:D11,SUM)
1 行目に担当（佐藤・山田・鈴木・合計）、左の列に分類が並びました。たとえば果物×鈴木は 2,500、右下の総合計は 7,250 です。
野菜×鈴木のように、データが 1 件も無い組み合わせは空欄になりました。0 を入れたいときは、結果を別の式で受けて IF(…="",0,…) にします。
2. 件数の表にする
4 つ目の関数を COUNT にすると、組み合わせごとの件数の表になります。
=PIVOTBY(A2:A11,C2:C11,D2:D11,COUNT)
果物×鈴木は 2 件、全体は 10 件でした。AVERAGE にすれば平均、MAX にすれば最大の表になります。
3. 合計の行と列を消す
6 つ目（行の合計の深さ）と 8 つ目（列の合計の深さ）に 0 を入れると、いちばん下の合計行と、いちばん右の合計列が消えます。カンマの数をまちがえやすいので、式をそのまま写すのが確実です。
=PIVOTBY(A2:A11,C2:C11,D2:D11,SUM,,0,,0)
結果は、担当（佐藤・山田・鈴木）の見出しと 3 つの分類だけの、4 行×4 列の表になりました。
4. 割合で見る（PERCENTOF）
関数を PERCENTOF にすると、割合の表になります。セルの表示形式はパーセントにします。
=PIVOTBY(A2:A11,C2:C11,D2:D11,PERCENTOF)
試すと、割合は「全体」ではなく「その列（担当）の合計」に対するものでした。たとえば佐藤さんの列の飲料は 16.3%（350÷2,150）、鈴木さんの列の果物は 92.6%（2,500÷2,700）です。いちばん下の合計行はどの列も 100.0% になります。
いちばん右の合計列は、全体（7,250）に対する分類の割合で、果物は 59.3% でした。PERCENTOF だけで使う方法は PERCENTOF 関数の使い方で説明しています。
【実機で検証】PIVOTBY でつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| SUMIFS で果物×鈴木 | =SUMIFS(PIVOTBY!D2:D11,PIVOTBY!A2:A11,"果物",PIVOTBY!C2:C11,"鈴木") | 2,500 |
| PIVOTBY の果物×鈴木 | =INDEX(PIVOTBY(PIVOTBY!A2:A11,PIVOTBY!C2:C11,PIVOTBY!D2:D11,SUM),3,4) | 2,500 |
| データの無い野菜×鈴木は空欄か | =INDEX(PIVOTBY(PIVOTBY!A2:A11,PIVOTBY!C2:C11,PIVOTBY!D2:D11,SUM),4,4)="" | TRUE |
| SUM で金額の総合計 | =SUM(PIVOTBY!D2:D11) | 7,250 |
| PIVOTBY の右下（総合計） | =INDEX(PIVOTBY(PIVOTBY!A2:A11,PIVOTBY!C2:C11,PIVOTBY!D2:D11,SUM),5,5) | 7,250 |
| 列の並べ替え順序を -2 にしたときの先頭 | =INDEX(PIVOTBY(PIVOTBY!A2:A11,PIVOTBY!C2:C11,PIVOTBY!D2:D11,SUM,,,,,-2),1,2) | 鈴木 |
PIVOTBY の果物×鈴木（2,500）は、SUMIFS（2,500）と同じでした。右下の総合計（7,250）も、金額を SUM した値（7,250）と同じです。
データの無い組み合わせが空欄かを調べると TRUE でした。空欄は 0 ではないので、割り算などに使うときは注意します。
9 つ目の列の並べ替え順序に -2 を入れると、担当が合計の大きい順に並び、先頭は「鈴木」になりました。
よくある質問
PIVOTBY が使えない（#NAME?）ときは？
PIVOTBY は Microsoft 365 の新しい関数です。Excel 2021 以前では使えないので、SUMIFS で表を作るか、ピボットテーブルを使います。
ピボットテーブルとの違いは？
PIVOTBY は式なので、元の表を書き換えるとすぐに結果が変わり、「更新」の操作がいりません。そのかわり、ドラッグで見出しを入れ替える操作はできないので、式の引数を書き換えます。
#SPILL! になるときは？
結果が広がる右と下のセルに何か入っていると #SPILL! になります。PIVOTBY は縦にも横にも広がるので、まわりを広めに空けておきます。
コメント