PIVOTBY関数は、Excelでデータを行と列でグループ化し、引数に指定した関数によって値を集計するための関数です。数式を使用して、ピボットテーブルのようにデータを要約することに役立ちます。
こちらの記事では、2024年9月に製品版のExcelで登場したPIVOTBY関数について、基本的な使い方から便利な使い方までを紹介します。
指定した行フィールドと列フィールドに基づいてデータを
できる関数です。
結果は、スピルされて複数行列にわたり表示されます。
PIVOTBY関数の基本構文は下記の通りです。（2024年9月時点引数は英語表記）
※[]のついた引数は省略可能
行のグループ化と行ヘッダーの生成に使用される値を含む列指向の配列または範囲。
列のグループ化と列ヘッダーの生成に使用される値を含む列指向の配列または範囲。
集計する列指向の配列またはデータ範囲。
値を集計する方法（SUMやAVERAGE等）や定義するラムダ関数など。
row_fields、col_fields、valuesにヘッダーがあるかどうか。
省略するとデータからヘッダーのありなしを自動判断となる。
0 ： いいえ（ヘッダーなし、ヘッダー生成しない）行の総計や小計を計算するかどうか。省略すると自動（総計と、可能な場合は小計）となる。
0 ： 合計なし
1 ： 総計
2 ： 総計と小計
-1 ： 上部に総計
-2 ： 上部に総計と小計
小計の場合、フィールドには少なくとも 2つの列が必要
行の並べ替え方法を示す数値。数値が負の場合、行は降順または逆順に並べ替えられます。
例えば2列目の行を降順に並べたい場合、「-2」と記載し、昇順に並べたい場合は「2」と記載。
列の総計や小計を計算するかどうか。省略すると自動（総計と、可能な場合は小計）となる。
0 ： 合計なし
1 ： 総計
2 ： 総計と小計
-1 ： 上部に総計
-2 ： 上部に総計と小計
列の並べ替え方法を示す数値。数値が負の場合、列は降順または逆順に並べ替えられます。
例えば列を降順に並べたい場合、「-1」と記載し、昇順に並べたい場合は「1」と記載。
結果をフィルターする場合に使用。
FILTER関数の「条件」と同じ記載で、TRUEまたはFLASEで返される配列数式を使用。
2 つの引数を必要とする集計関数を使用する場合、集計関数の 2 番目の引数に提供される値を制御します。通常はPERCENTOF関数と一緒に使用します。省略すると規定値となります。
0: 列合計 (規定値)
1: 行の合計
2: 総計
3: 親 Col Total
4: 親行合計
例えば、以下のようなデータで、「部署」と「グループ」のクロス集計で「勤務時間」の「合計」を計算します。
【H2】セル：=PIVOTBY(E2:E21,F2:F21,C2:C21,SUM)
E列を行、F列を列としてグループ化し、C列の合計を計算しています。
functionの引数では、一例として以下のような計算方法を指定できます。
下記で、使い方を説明いたします。
行、列、値の引数にヘッダーを含んでいる場合、ヘッダーの引数を指定することで、表示非表示を切り替えることができます。
【H2】セル：=PIVOTBY(E1:E21,F1:F21,C1:C21,SUM,3)
ヘッダーの引数に「3 ： はい、表示します（ヘッダーあり、ヘッダー出力する）」を選択することで、結果にヘッダーを表示することが可能です。
総計や行または列に複数列が含まれる場合の小計の計算を指定します。
【H2】セル：=PIVOTBY(E2:F21,,C2:C21,SUM,,-2)
列の引数を省略し、行集計の引数として「-2 ： 上部に総計と小計」を指定しています。
並べ替えをどのようにするか指定します。数値（1から始まる）は「行または列フィールド」に対応しています。シートの列番号とは関係なくあくまで、各フィールド内での順番です。並べ替え順序の数値は、行フィールドと値で通し番号となっており以下と考えます。
【H2】セル：=PIVOTBY(E2:E21,F2:F21,C2:C21,SUM,0,0,-2)
「-2」を指定しているので、2列目（勤務時間の合計）の行を基準に、降順に並べ替えます。
結果をフィルターする際に使用します。
【H2】セル：=PIVOTBY(E2:E21,F2:F21,C2:C21,SUM,0,0,,,,E2:E21="財務")
引数として、「E2:E21="財務"」と記載することで、E列が「財務」のみのデータのピボットを返すことができます。条件の書き方はFILTER関数と同様です。（下記記事参照）
ピボットテーブルの計算方法の「行集計に対する比率、列集計に対する比率」などを計算したい場合に使用します。通常はPERCENTOF関数と一緒に使用します。
【H2】セル：=PIVOTBY(E2:E21,F2:F21,C2:C21,PERCENTOF)
functionの引数に「PERCENTOF」を使用することで、計算結果が比率になります。relative_toの引数を省略すると、値が「列合計に対する比率」となります。
【H2】セル：=PIVOTBY(E2:E21,F2:F21,C2:C21,PERCENTOF,,,,,,,1)
relative_toの引数に「1」を指定し、値を「行集計に対する比率」としています。
それぞれの引数を範囲で指定している場合、データが増えると手動で数式を修正し範囲を変更する必要があります。元のデータをテーブル化しておくことで、データが増えてもPIVOTBY関数の結果が自動更新されるようになります。
【H2】セル：=PIVOTBY(テーブル1[対応部署],テーブル1[グループ],テーブル1[勤務時間],SUM)
テーブルを参照しているため、各引数がテーブルの名称と列名となります。常にテーブルの範囲を参照しているため、最終行に新しいデータが増えた場合でも数式を更新する必要がありません。
【H2】セル：=PIVOTBY(テーブル1[対応部署],テーブル1[グループ],テーブル1[勤務時間],SUM)
テーブルの最終行に、対応部署「HR」、グループ「2グループ」のデータを追加していますが、数式は変更していません。
PIVOTBY関数は、Excelのデータ分析をより柔軟かつ効率的に行うための強力なツールです。この記事で紹介した基本的な使い方を参考に、ぜひ実際の業務やプロジェクトで活用してみてください。PIVOTBY関数をマスターすることで、データの洞察力が飛躍的に向上し、より迅速かつ正確な意思決定が可能になるでしょう。
当社では、『Microsoft認定トレーナー』資格を持つEXCEL女子による「Excel研修」を提供しております。書籍化するなど注目いただいている研修です。
社員教育に時間を割けない、Excelスキルの底上げをしたい、最新のExcel関数を活用したいなどなどお悩みでしたら、ぜひお気軽にお問い合わせください。
コクー（EXCEL女子の教育担当講師）の『社員研修サービス』