Excel PIVOTBY関数:2つの軸でグループ化してクロス集計表を作成する
PIVOTBY関数は、2024年9月のアップデートで新たに追加された関数です。この関数は、ピボットテーブルに似た機能を持ち、データの集計に役立ちます。
データ更新が不要であり、ピボットテーブルよりも動作が軽快であるため、定期的に表を更新し、同じルールで集計する業務にはPIVOTBY関数がより適しています。
一方で、スポット的な集計作業や、複数の異なる軸でデータを集計し結果を確認したい場合には、ピボットテーブルの方が適しています。
集計の軸が1つの場合は、GROUPBY関数を使用します。
仕様
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])| 引数 | 省略時の値 | 説明 |
|---|---|---|
| row_fields | 省略不可 | グループ化する軸(行)を指定します。 ヘッダー行を含めるかは任意ですが、valuesと合わせる必要があります。 |
| col_fields | 省略不可 | グループ化する軸(列)を指定します。 ヘッダー行を含めるかは任意ですが、valuesと合わせる必要があります。 |
| values | 省略不可 | 集計する値を指定します。 ヘッダー行を含めるかは任意ですが、row_fieldsと合わせる必要があります。 |
| function | 省略不可 | 集計方法を指定します。 |
| field_headers | ヘッダー行の扱いを指定します。 | |
| row_total_depth | 行の小計と総計を表示するかを指定します。 | |
| row_sort_order | 行の並び順を指定します。 | |
| col_total_depth | 列の小計と総計を表示するかを指定します。 | |
| col_sort_order | 列の並び順を指定します。 | |
| filter_array | フィルター条件を指定します。 | |
| relative_to | 0 | function が2つの数値を必要とする場合、2つめの数値を選択します、 |
基本的な使用例
最も簡単な使用方法では4つの引数を指定します。
- 1つ目の引数：グループ化の軸(行)となる範囲
- 2つ目の引数：グループ化の軸(列)となる範囲
- 3つ目の引数：集計する値の範囲
- 4つ目の引数：集計方法(SUMなど。後述)
関数の結果はスピルとして自動拡張されます。
引数
第4引数：functionと 第11引数：relative_to
第4引数：functionで集計方法を指定します。
- SUM:合計値
- PERCENTOF：総計からの比率
- AVERAGE：平均値
- MEDIAN：中央値
- COUNT：空白以外の件数
- COUNTA：空白込みの件数
- MAX：最大値
- MIN：最小値
- PRODUCT：乗算
- ARRAYTOTEXT：文字結合。カンマ区切りテキスト
- CONCAT：文字結合
- STDEV.S：標準偏差(標本)
- STDEV.P：標準偏差(母集団全体)
- VAR.S：分散(標本)
- VAR.P：分散(母集団全体)
- MODE.SNGL：最頻値
- LAMBDA：任意の集計方法
PERCENTOF：総計からの比率
PERCENTOFで簡単に構成比を出すことが可能です。
次のような結果になります。初期状態では分母は列の合計です。
第11引数：relative_toによって、分母をどの合計にするかを指定します。
- 0: 列の合計 (既定値)
- 1: 行の合計
- 2: 総計
- 3: 親列の合計
- 4: 親行の合計
relative_to=1。行の合計が分母
3と4は小計が分母になります。
ARRAYTOTEXTとCONCAT：文字列結合
文字列結合は次のような結果になります。
そのまま利用するよりも、数式のデバッグに重宝します。
LAMBDA：任意の集計方法
LAMBDA関数を利用して独自の集計を行います。扱いが難しく、利用頻度も低いですが、高度な集計が可能です。
例えば次のような数式では3の倍数の数値のみを合計します。
=PIVOTBY(A1:A19,B1:B19,D1:D19,LAMBDA(a,SUM(IF(MOD(value,3)=0,a,0))) )
LAMBDA関数には2つ目の引数を設定可能です。
この場合、2つ目の引数は合計値になります。
第11引数：relative_toによって、どの合計にするかを指定します。（PERCENTOFと同じ）
- 0: 列の合計 (既定値)
- 1: 行の合計
- 2: 総計
- 3: 親列の合計
- 4: 親行の合計
=PIVOTBY(A1:A19,B1:B19,D1:D19,LAMBDA(value,total,SUM(total)),,0,,0)
第5引数：field_headers
ヘッダーの扱いを指定する引数です。
利用頻度が高いのは、省略か3でしょう。
| 指定 | 指定範囲1行目の扱い | 結果のヘッダー行 |
|---|---|---|
| 省略 | 自動判別。ヘッダーでもデータでも良い。 | 非表示 |
| 0 | データ行 | 非表示 |
| 1 | ヘッダー行 | 非表示 |
| 2 | データ行 | 表示 |
| 3 | ヘッダー行 | 表示 |
row_fieldsとcol_fieldsとvaluesの1行目がヘッダー行であれば、省略か 1 か 3 を指定します。
1行目がデータ行の場合は省略か 0 か 2 を指定します。
2の場合、「行フィールド」や「列フィールド」、「値」がヘッダーになります。
第6引数：row_total_depthと第7引数：col_total_depth
小計や総計を表示するかを指定します。
小計を表示するには軸の範囲が2列以上、必要です。
省略した場合は、総計表示で小計は自動判別のようですが、小計表示されるのを確認できませんでした。
また第11引数：field_relationshipが1の場合は小計が表示されません。
| 指定 | 総計と小計の表示位置 | 小計 |
|---|---|---|
| 省略 | 下 | 自動判別？ |
| 0 | 無 | 無 |
| 1 | 下 | 無 |
| 2 | 下 | 有 |
| -1 | 上 | 無 |
| -2 | 上 | 有 |
小計を表示する 2 と -2 の例です。月と区分が空白になっている部分が小計です。
total_depth:2
第7引数：row_sort_orderと第9引数：col_sort_order
第7引数と第9引数で並び順を指定します。
列数を指定し、正数であれば昇順、負数であれば降順です。
例えば-2を指定すると結果の2列目が降順になります。
=PIVOTBY(A2:A19,B2:C19,D2:D19,SUM,,,,,-2)ただし階層が維持されているため、先に1列目のソートがかかっています。
{}で配列指定をすると複数列のソートを指定できます。
=PIVOTBY(A2:A19,B2:C19,D2:D19,SUM,,,,,{-1,-2})
第10引数：filter_array
フィルター条件を指定します。
単純な利用方法はフィルター用の列を用意して、TRUEかFALSEを設定します。
TRUEのみ集計対象になります。
=PIVOTBY(A2:A19,B2:C19,D2:D19,SUM,,,,,,E2:E19)
FILTER関数と同様の記述ルールで数式として設定可能です。
関数は複雑になりますが、定期的なセル操作の手間を減らす効果があります。
例えば次のように設定すると、年が2021以上で区分がBのみを集計対象にします。
=PIVOTBY(A2:A19,B2:C19,D2:D19,SUM,,,,,,(A2:A19>=2021)*(C2:C19="B"))
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。