Excelで売上データを「部門 × 月」や「担当者 × 商品」のようにクロス集計したい場面、よくありますよね。ピボットテーブルを作るのは少し手間がかかるし、数式で組もうとするとSUMIFS関数を大量にコピーすることになります。PIVOTBY関数を使えば、たった1つの数式で2軸のクロス集計表を自動生成できます。この記事では基本の書き方から実務で使える応用例、エラー対処法まで丸ごと解説します。
この記事は次のような人におすすめ
- 「部門 × 商品」のようなクロス集計表を数式だけで作りたい
- ピボットテーブルより手軽にクロス表を作成したい
- PIVOTBY関数とGROUPBY関数の違いを知りたい
ExcelのPIVOTBY関数とは？
PIVOTBY（ピボットバイ）関数は、データを行と列の2軸でグループ化し、クロス集計表を自動生成する関数です。「PIVOT（軸）」+「BY（〜で指定）」で、「指定した軸で回転させる」という意味があります。
たとえば売上データの「部門」を行、「商品」を列に配置して「金額」をSUMで合計してみましょう。部門 × 商品のクロス表が数式ひとつで完成します。結果はスピル（動的配列）で出力されます。スピルとは、数式の結果が複数セルに自動展開される機能です。データが増えても数式の修正は不要ですよ。
PIVOTBY関数はMicrosoft 365で利用できる関数です。Excel 2021以前のバージョンでは使用できません。
PIVOTBY関数の書き方（構文と引数）
基本構文
=PIVOTBY(行フィールド, 列フィールド, 値, 集計関数, [ヘッダー], [行集計], [行の並び順], [列集計], [列の並び順], [フィルター配列], [基準])引数の説明
| 引数 | 必須/省略可 | 説明 |
|---|---|---|
| 行フィールド | 必須 | 行の見出しに使う列（部門列など） |
| 列フィールド | 必須 | 列の見出しに使う列（商品列や月列など） |
| 値 | 必須 | 集計対象の列（金額列など） |
| 集計関数 | 必須 | SUM、AVERAGE、COUNTなどの関数名をカッコなしで指定 |
| ヘッダー | 省略可 | 0=なし、1=あり（非表示）、2=あり（表示）、3=自動生成 |
| 行集計 | 省略可 | 0=なし、1=総計、2=総計＋小計。負値で上部に表示 |
| 行の並び順 | 省略可 | 並べ替え列を番号で指定。負値で降順 |
| 列集計 | 省略可 | 0=なし、1=総計、2=総計＋小計。負値で左側に表示 |
| 列の並び順 | 省略可 | 並べ替え列を番号で指定。負値で降順 |
| フィルター配列 | 省略可 | 行を絞り込むTRUE/FALSEの配列 |
| 基準 | 省略可 | PERCENTOF使用時の割合基準（0=列合計、1=行合計、2=総計） |
NOTE
必須の引数は4つです。まずは「行フィールド」「列フィールド」「値」「集計関数」の基本形をおさえましょう。
PIVOTBY関数の基本的な使い方
次のような売上データがA1:D13にあるとします（1行目がヘッダー）。
| 部門 | 商品 | 月 | 金額 |
|---|---|---|---|
| 営業部 | 商品A | 4月 | 50000 |
| 開発部 | 商品B | 4月 | 30000 |
| 営業部 | 商品B | 5月 | 45000 |
| 開発部 | 商品A | 5月 | 25000 |
| 総務部 | 商品A | 4月 | 15000 |
| 営業部 | 商品A | 5月 | 60000 |
| 総務部 | 商品B | 6月 | 20000 |
| 開発部 | 商品B | 6月 | 35000 |
| 営業部 | 商品B | 6月 | 40000 |
| 総務部 | 商品A | 6月 | 18000 |
| 開発部 | 商品A | 4月 | 32000 |
| 営業部 | 商品A | 6月 | 55000 |
部門 × 商品のクロス集計表を作るには、次のように書きます。
=PIVOTBY(A2:A13,B2:B13,D2:D13,SUM)結果はスピルで展開され、以下のようなクロス表が自動生成されます。
| 商品A | 商品B | |
|---|---|---|
| 営業部 | 165000 | 85000 |
| 開発部 | 57000 | 65000 |
| 総務部 | 33000 | 20000 |
行フィールドにA2:A13（部門列）、列フィールドにB2:B13（商品列）を指定します。値にD2:D13（金額列）、集計関数にSUMを指定すれば完成です。部門と商品の2軸で集計されたクロス表がこれだけで出来上がります。
TIP
集計関数はSUMのほかにAVERAGE、COUNT、MAX、MINなども使えます。カッコは付けずに関数名だけを指定してください。
ヘッダー付きで出力する
ヘッダー行を含めた範囲を指定し、第5引数に3を設定すると、結果にもヘッダーが付きます。
=PIVOTBY(A1:A13,B1:B13,D1:D13,SUM,3)1行目・1列目に「部門」「商品A」「商品B」のようなヘッダーが自動表示されます。報告用の表を作るときに便利ですよ。
実践的な使い方・応用例
部門 × 商品の売上クロス集計
先ほどの基本例に総計行と総計列を追加してみましょう。第6引数（行集計）と第8引数（列集計）にそれぞれ1を指定します。
=PIVOTBY(A2:A13,B2:B13,D2:D13,SUM,,1,,1)| 商品A | 商品B | 総計 | |
|---|---|---|---|
| 営業部 | 165000 | 85000 | 250000 |
| 開発部 | 57000 | 65000 | 122000 |
| 総務部 | 33000 | 20000 | 53000 |
| 総計 | 255000 | 170000 | 425000 |
行と列の両方に総計が付くので、全体の構成がひと目で分かります。省略する引数の位置はカンマだけで飛ばしてくださいね。
担当者 × 月の受注件数マトリクス
データの行フィールドを担当者、列フィールドを月にすれば、担当者ごと・月ごとの件数表が作れます。集計関数をCOUNTに変えるだけです。
=PIVOTBY(A2:A13,C2:C13,D2:D13,COUNT)| 4月 | 5月 | 6月 | |
|---|---|---|---|
| 営業部 | 1 | 2 | 2 |
| 開発部 | 2 | 1 | 1 |
| 総務部 | 1 | 0 | 2 |
列フィールドをC列（月）に変えただけで、まったく違うクロス表が出力されます。集計関数を切り替えれば合計・平均・件数など自在に対応できますよ。
カテゴリ × 四半期の売上比率表
PERCENTOF関数と組み合わせると、各セルを「全体に対する割合」で表示できます。第4引数にPERCENTOFを、第11引数（基準）に2（総計基準）を指定します。
=PIVOTBY(A2:A13,B2:B13,D2:D13,PERCENTOF,,,,,,,2)各セルの値が「総計に対する割合」として表示されます。たとえば営業部 × 商品Aが全体の何%を占めるかがすぐに分かります。構成比の分析をしたいときに試してみてください。
特定条件でフィルターする
第10引数にTRUE/FALSEの配列を渡すと、条件を絞ったクロス集計ができます。金額が30000以上のデータだけで集計する場合です。
=PIVOTBY(A2:A13,B2:B13,D2:D13,SUM,,,,,,(D2:D13>=30000))条件に合う行だけが集計対象になります。フィルター配列は元データと同じ行数にしてください。
よくあるエラーと対処法
#NAME? エラー
関数名のスペルミス、またはPIVOTBY関数に対応していないバージョンで発生します。PIVOTBY関数はMicrosoft 365専用です。Excel 2021以前ではこのエラーが表示されます。
対処法: バージョンを確認してください。スペルが正しいかもあわせてチェックしましょう。
#VALUE! エラー
行フィールド・列フィールド・値の行数が一致しない場合に発生します。ヘッダーの有無が揃っていないケースが多いです。
対処法: 3つの範囲の行数を揃えてください。ヘッダー行を含めるなら全部含める、除くなら全部除くように統一します。
#SPILL! エラー
スピル先のセル範囲に他のデータが入っている場合に発生します。PIVOTBY関数は行と列の両方向に展開するため、広い範囲が必要です。
対処法: 数式を入力するセルの右下方向に十分な空きスペースを確保してください。
#CALC! エラー
フィルター配列で全行が除外された場合や、空の配列が渡された場合に発生します。
対処法: フィルター条件を見直して、少なくとも1行はTRUEになるようにしてください。
GROUPBYとPIVOTBYの使い分け
PIVOTBY関数と同時期に追加されたGROUPBY関数も、データを集計する関数です。出力形式が大きく異なるので、目的に応じて使い分けましょう。
| 項目 | GROUPBY関数 | PIVOTBY関数 |
|---|---|---|
| 出力形式 | 縦方向のリスト | 行 × 列のクロス集計表 |
| グループ化の軸 | 行方向のみ（1軸） | 行と列の2軸 |
| 必須引数 | 3つ（行・値・関数） | 4つ（行・列・値・関数） |
| 得意な場面 | 部門ごとの合計など1軸の集計 | 部門 × 月のクロス表 |
| 引数の数 | 8個 | 11個 |
判断基準はシンプルですよ。
- 「〜ごとの合計を縦に並べたい」 → GROUPBY関数
- 「〜ごと × 〜ごとの表を作りたい」 → PIVOTBY関数
たとえば「部門ごとの売上合計」ならGROUPBY関数で十分です。「部門 × 四半期の売上表」のように2つの軸が必要なら、PIVOTBY関数を選びましょう。
PIVOTBYの結果を特定の列で並べ替えたい場合は、SORTBY関数も組み合わせて使えます。
まとめ
PIVOTBY関数は「行と列の2軸でデータをグループ化し、クロス集計表を自動生成する」関数です。
- 行フィールド・列フィールド・値・集計関数の4つを指定するだけで、クロス表が完成する
- 総計行・総計列の追加、ソート、フィルターなどオプション引数も充実している
- PERCENTOF関数と組み合わせれば、構成比の分析もできる
- 1軸の縦リスト集計ならGROUPBY関数、2軸のクロス表ならPIVOTBY関数と使い分ける
- Microsoft 365専用の関数なので、バージョンを確認してから使う
ピボットテーブルを作るほどでもないクロス集計は、PIVOTBY関数で数式化しておきましょう。データ更新時も自動で再計算されるので手間が省けます。ぜひ試してみてください。
関連記事
関数一覧
Excel関数の一覧は下記の記事で確認できます。
エラー値が表示される場合は、下記の記事も参考にしてみてください。
