Microsoft Excelに新関数・PIVOTBY関数が登場しました！
PIVOTBY関数は、ピボットテーブルの機能を備えた関数で、膨大なデータを簡単にクロス集計することができます。
例えば、売上データにPIVOTBY関数を使用することで、縦軸に各製品、横軸に年月別の売上高といったデータ加工が簡単に行えます。
この記事では、エクセルのPIVOTBY関数の使い方と、実務に役立つ事例集を紹介します。
エクセルのPIVOTBY関数とは？
PIVOTBY関数の読み方は、「ピボット・バイ関数」です。
エクセルのPIVOTBY関数とは、下の図のように、指定したセル範囲のデータをクロス集計し、さらに並べ替えやフィルターをかけることができる関数です。
クロス集計とは、2つの項目を縦軸と横軸で「クロス」させて表を作成し、相互の関係を明らかにするための集計・分析方法のことです。
以下のリンク記事でピボットテーブルという機能を紹介しましたが、ピボットテーブル機能を関数で実行することができるのがPIVOTBY関数です。
PIVOTBY関数で使用する主な集計方法は、以下となります。特に、①のSUM(合計)を利用する機会が多くなります。
PIVOTBYBY関数で指定できる主な集計方法
- SUM・・・合計
- AVERAGE・・・平均
- COUNT・・・数値セルの個数
- COUNTA・・・空白セルを除くすべてのセルの個数
ピボットテーブルとPIVOTBY関数の違いですが、基本的にその差はほとんどありません。
ただ、PIVOTBY関数はピボットテーブルと比べて、以下のようなメリットがあります。
PIVOTBY関数のメリット
１．元データを更新すると、随時クロス集計の結果もタイムリーに更新
２．出力するセル範囲を自在に調整可能
３．テーブル機能と組み合わせて利用することで、元データのサイズに自動で対応
PIVOTBY関数を使用できるExcelバージョンは、25/5月時点でMicrosoft365(サブスクリプション)のみとなっています。
PIVOTBY関数を使用できないユーザーは、ピボットテーブルを使用してください。
PIVOTBY関数の書式と引数
PIVOTBY関数の書式と引数は、以下のとおりです。
PIVOTBY関数を利用しようとすると、引数は英語で表記されます。
英語版
=PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth],[row_sort_order],[col_total_depth],[col_sort_order],[filter_array],[relative_to])
日本語版
分かりやすいように日本語版で表すと、以下の通りです。
=PIVOTBY(行フィールド,列フィールド,値,関数,[ヘッダー],[行集計],[行の並べ替え],[列集計],[列の並べ替え],[フィルター],[対象])
PIVOTBY関数の引数は11個あり、利用するのが難しそうに感じるかもしれませんが、[ ]で囲まれた後半7つの引数は省略可能です。
そのため、簡単なクロス集計であれば、前半の４つの引数「行フィールド」「列フィールド」「値」「関数」で利用できます。
PIVOTBY関数の引数のそれぞれの内容は、以下の表のとおりです。
引数の具体的な使い方は、以降の見出しを読み進めていただければイメージがつくと思いますので、ここで理解できなくても大丈夫です。
| 引数 | 説明 | |
| 1 | 行フィールド row_fields | グループ化する縦軸の配列を指定。複数の列を指定することも可能。 |
| 2 | 列フィールドcol_fields | グループ化する横軸の配列を指定。複数の列を指定することも可能。 |
| 3 | 値 values | クロス集計する対象の配列を指定。複数の列を指定することも可能。 |
| 4 | 関数 function | クロス集計の方法を指定する。選択出来る関数は以下のとおり。 SUM、PERCENTOF、AVERAGE、MEDIAN、COUNT、COUNTA、MAX、MIN、PRODUCT、ARRAYTOTEXT、CONCAT、SRDEV.S、STDEV.P、VAR.S、VAR.P、MODE.SNGL、LAMBDA |
| 5 | ヘッダー field_headers | 引数「行(列)フィールド」「値」を指定した際、見出しを含めて指定したかどうかによって、「0」～「3」の数値を指定。省略した場合、エクセルが自動で集計方法を判断する。 0 ： 見出しが含まれない⇒戻り値に列見出しを表示しない 1 ：見出しが含まれる⇒戻り値に列見出しを表示しない 2 ： 列見出しが含まれない⇒戻り値に列見出しを自動表示 3 ： 列見出しが含まれる⇒戻り値に列見出しを表示 |
| 6 | 行集計row_total_depth | 小計行や総計行を表示するかどうか以下の数値の中から指定する。省略した場合、エクセルが自動で集計方法を判断する。 0 ： 小計行や総計行を表示しない 1 ： 総計行を表示する 2 ： 小計行と総計行を表示する -1 ： 上部に総計行を表示する -2 ： 上部に小計行と総計行を表示する |
| 7 | 行の並べ替えrow_sort_order | 行単位で並べ替えしたいフィールドが上から何行目か指定する。昇順の場合は正数で、降順の場合は負数で入力する。省略した時は「行フィールド」が昇順で並べ替えられる |
| 8 | 列集計col_total_depth | 小計列や総計列を表示するかどうか以下の数値の中から指定する。省略した場合、エクセルが自動で集計方法を判断する。 0 ： 小計列や総計列を表示しない 1 ： 総計列を表示する 2 ： 小計列と総計列を表示する -1 ： 左側に総計列を表示する -2 ： 左側に小計列と総計列を表示する |
| 9 | 列の並べ替えcol_sort_order | 列単位で並べ替えしたいフィールドが左から何列目か指定する。昇順の場合は正数で、降順の場合は負数で入力する。省略した時は「列フィールド」が昇順で並べ替えられる |
| 10 | フィルター filter_array | 省略可能。集計対象を絞り込むための抽出条件を指定 |
| 11 | 対象 relative_to | 引数「関数」で「PERCENTOF」を使用する場合に使用。2 つの引数を必要とする集計関数を使用する場合、集計関数の 2 番目の引数に提供される値を制御 0 ： 列の合計（既定値） 1 ： 行の合計 2 ： 総合計 3 ： 上位の列項目の合計 4 ： 上位の行項目の合計 |
PIVOTBY関数の基本的な使い方
それでは、実際にPIVOTBY関数を使ってみましょう。
以下の売上データを使って、PIVOTBY関数の使い方と手順を紹介します。
手順１．PIVOTBY関数を挿入
まず、クロス集計の結果を表示させたいセルに、PIVOTBY関数を挿入します。
=PIVOTBY(
手順２．PIVOTBY関数の第１引数を指定
PIVOTBY関数の第１引数「行フィールド(row_fields)」を指定します。「地域」列をクロス集計の縦軸に設定したいので、C1:C25のセル範囲をドラッグで選択します。
=PIVOTBY(C1:C25,
手順３．PIVOTBY関数の第２引数を指定
PIVOTBY関数の第２引数「列フィールド(col_fields)」を指定します。「商品名」列をクロス集計の横軸に設定したいので、E1:E25のセル範囲をドラッグで選択します。
=PIVOTBY(C1:C25,E1:E25,
手順４．PIVOTBY関数の第３引数を指定
PIVOTBY関数の第３引数「値(values)」を指定します。「販売数量」を集計したいので、F1:F25のセル範囲をドラッグで選択します。
=PIVOTBY(C1:C25,E1:E25,F1:F25,
手順５．PIVOTBY関数の第４引数を指定
PIVOTBY関数の第４引数「関数(function)」を指定します。販売数量の合計で集計したいので、「SUM」と入力します。
=PIVOTBY(C1:C25,E1:E25,F1:F25,SUM)
手順６．PIVOTBY関数を確定
数式が完成したので、「Enter」キーでPIVOTBY関数を確定します。
そうすると、スピル機能が働いて、隣接しているセル範囲に「縦軸：地域」の「横軸：商品名」の販売数量のクロス集計が表示されます。
PIVOTBY関数を使った事例
PIVOTBY関数の５つ目以降の引数は省略可能ですが、これらの引数を使用すると、さまざまなデータ加工が可能となります。
ここからは、PIVOTBY関数を使った応用編の事例を紹介します。
PIVOTBY関数/事例１．テーブル機能を利用
PIVOTBY関数とテーブル機能を組み合わせた事例を紹介します。
PIVOTBY関数の第１～３引数に列番号を直接指定し、売上データをクロス集計してみます(例⇒C:C、E:E)。そうすると、クロス集計した結果に、「0」の列や行が表示されます。
=PIVOTBY(C:C,E:E,F:F,SUM)
これは、第１～３引数で指定したセル範囲に空白のデータが含まれていることが原因です。
「0」の行(列)が発生しないようにしたい場合は、元データにテーブル機能を設定します。
テーブルを使用した表に対してPIVOTBY関数を使用すると、後から最下行にデータを追加しても、自動でクロス集計のセル範囲が更新されるメリットがあります。
手順１．表にテーブル機能を設定
表内の任意のセルで「Ctrl＋T」キーを押して、テーブルを設定します。
テーブル機能の設定方法が分からない方は、以下の記事を参考にしてください。
手順２．PIVOTBY関数の第１引数を指定
PIVOTBY関数の第１引数「行フィールド(row_fields)」を指定します。「地域」を基準にデータを集計したいので、C2セルか最下行までドラッグして選択します。そうすると、テーブルの名前と列の名称が自動で表示されます。
=PIVOTBY(テーブル1[地域],
手順３．PIVOTBY関数の第２、３引数を指定
同様に、第２引数「列フィールド(col_fields)」と第３引数「値(values)」を指定します。
=PIVOTBY(テーブル1[地域],テーブル1[商品名],テーブル1[販売数量],
手順４．PIVOTBY関数の第４引数を指定
PIVOTBY関数の第３引数「関数(function)」を指定します。合計で集計したいので、「SUM」と入力します。
=PIVOTBY(テーブル1[地域],テーブル1[商品名],テーブル1[販売数量],SUM)
手順５．PIVOTBY関数の完成
数式が完成したので、「Enter」キーでPIVOTBY関数を確定します。そうすると、テーブルを元データとしたクロス集計が完成します。
手順６．最下行にデータを追加
元表の最下行にデータを追加してみます。そうすると、テーブル範囲は自動で拡張されるので、PIVOTBY関数の結果も自動で反映されます。
このように、元表にテーブルを設定しておくと、テーブルの設定範囲が自動で調整されます。
引数の設定も簡単になるので、テーブルの操作に慣れている方は、ぜひPIVOTBY関数と一緒に利用してみてください。
PIVOTBY関数/事例２．複数条件でクロス集計
PIVOTBY関数の第１引数「行フィールド」や第２引数「列フィールド」に複数の列を範囲指定することで、より高度なクロス集計が可能です。
例えば、下のように、第１引数に「C1: D25」と隣接している２列を指定します。
=PIVOTBY(C1:D25,E1:E25,F1:F25,SUM)
そうすると、下のように、縦軸項目が「地域」「販売店舗」のクロス集計が完成します。
もし、複数の列が離れている場合は、HSTACK関数を使用します。
HSTACK関数とは、「異なる列のデータを横方向に統合して１つにまとめる関数」です。
=HSTACK(配列1,配列2‥‥)
⇩HSTACK関数の詳しい使い方
下のように、PIVOTBY関数の第１引数にHSTACK関数を挿入して、B列とD列の２列を指定します。
=PIVOTBY(HSTACK(B1:B25,D1:D25),E1:E25,F1:F25,SUM)
そうすると、上の図のように、離れている２列の「地域」「販売店舗」を縦軸とした複数列のクロス集計を行うことができます。
同様の方法で、PIVOTBY関数は第２引数「列フィールド」、第３引数「値」も複数列を指定することが可能です。
PIVOTBY関数/事例３．合計行(列)を省略する
PIVOTBY関数の第６引数「行集計(row_total_depth)」と第8引数「列集計(col_total_depth)」に「0」を指定すると、クロス集計の結果の合計行(列)を非表示にすることができます。
0 ： 小計や総計を表示しない
1 ： 総計を表示する(デフォルト)
2 ： 小計と総計を表示する
-1 ： 上部(左部)に総計を表示する
-2 ： 上部(左部)に小計と総計を表示する
小計と総計を表示したいときは、第６引数と第８引数に「0」を指定します。第5、7引数は入力しないので、「0」と手前には「,」の入力が必要です。
=PIVOTBY(C1:C25,E1:E25,F1:F25,SUM,,0,,0)
クロス集計に小計行を表示したい場合は、第６引数に「２」を入力すれば、下の図のように表示することができます。
=PIVOTBY(C1:D25,E1:E25,F1:F25,SUM,,2)
PIVOTBY関数/事例４．月単位の日付で集計
PIVOTBY関数の第１引数「行フィールド」に、日付列を入力すると、下の図のように、日別にデータが集計されてしまいます。
=PIVOTBY(B1:B21,E1:E21,F1:F21,SUM)
こんな時、PIVOTBY関数の第１引数の中にTEXT関数を挿入することで、年月別にデータを集計することができます。
=PIVOTBY(TEXT(B2:B21,"yyyy/mm"),E2:E21,F2:F21,SUM)
TEXT関数はセルの表示形式を変更するエクセルの定番関数です。以下の記事で詳しい使い方を紹介しているので、合わせて参考にしてください。
PIVOTBY関数/事例５．フィルターで空白行による「0」を非表示
PIVOTBY関数の第１～３引数に空白行のセル範囲が含まれていると、下の図のように、集計結果に「0」が表示されます。
=PIVOTBY(C:C,E:E,F:F,SUM)
上記で紹介したように元データにテーブル機能を使うことで、空白行を集計の対象から除外することができますが、もう一つの方法を紹介します。
PIVOTBY関数の第10引数「フィルター(filter_array)」に、「C列が空白以外の場合(C:C<>””)」というフィルター条件を数式で設定すれば、0を非表示にすることができます。
=PIVOTBY(C:C,E:E,F:F,SUM,,,,,,C:C<>"")
第５引数から第９引数は指定しない場合、第10引数の手前には「,」(カンマ)は６つ必要です。
そうすると、上記のように、C列に空白セルが含まれていてもPIVOTBY関数の集計結果に0が表示されることは無くなります。
PIVOTBY関数/事例６．昇順/降順で並べ替え
PIVOTBY関数の第7引数「行の並べ替え」か第9引数「列の並べ替え」を使うことで、見出しや合計列(行)を昇順か降順で並べ替えすることができます。
並べ替えしたい列(行)がフィールド上、左(上)から数えて何列目(何行目)か数値で指定します。昇順の場合は正数、降順の場合は負数で入力します。
例えば、下の図に対して、PIVOTBY関数で第7引数に「-2」と入力すると、合計列を降順で並べ替えすることができます。
=PIVOTBY(C1:C17,E1:E17,F1:F17,SUM,,,-2)
PIVOTBY関数とGROUPBY関数の違い
PIVOTBY関数も便利ですが、実務ではGROUPBY関数という新関数が非常に役立ちます。
エクセルのGROUPBY関数は、指定したセル範囲のデータを様々な方法で集計することが出来る関数です。
集計方法は合計だけでなく、平均値やセルの個数など様々な計算方法を指定することができます。
将来、GROUPBY関数は、XLOOKUP関数やCOUNTIF関数やSUMIF関数などに並ぶほどの定番関数になることが予想されています。
下の記事で紹介しているので、Microsoft365を利用されているユーザーは、合わせて参考にしてください。
