［Microsoft 365］
Excelの PIVOTBY（ピボット・バイ）関数の使い方を紹介します。
PIVOTBY関数はMicrosoft 365の新関数です。この関数を使うと、表の項目をグループ化して縦軸と横軸に配置し、クロス集計表を作成できます。ここでは基本的なグループ集計の方法と、PIVOTBY関数の引数の使い方を紹介します。
PIVOTBY関数によるさまざまな集計方法については、以下の記事も参考にしてください。
なお、グループ化した項目を縦軸に配置した単純なグループ集計をしたい場合は、GROUPBY関数を使用してください。
目次
書式
縦横グループ化してクロス集計する
=PIVOTBY(行フィールド, 列フィールド, 値, 関数, ［ヘッダー］, ［行集計深さ］, ［行並べ替え］, ［列集計深さ］, ［列並べ替え］, ［フィルター］, ［対象］)
［行フィールド］をグループ化して縦軸に、［列フィールド］をグループ化して横軸に配置し、［関数］で指定した方法で［値］を集計します。関数式は、動的配列数式として入力されます。
■引数
| 引数 | 説明 |
|---|---|
| 行フィールド | グループ化して縦軸に配置する項目を配列やセル参照で指定する。複数の項目を指定可能。 |
| 列フィールド | グループ化して横軸に配置する項目を配列やセル参照で指定する。複数の項目を指定可能。 |
| 値 | 集計対象の項目を配列やセル参照で指定する。複数の項目を指定可能。 |
| 関数 | 集計方法を指定する。選択肢は SUM、PERCENTOF、AVERAGE、MEDIAN、COUNT、COUNTA、MAX、MIN、PRODUCT、ARRAYTOTEXT、CONCAT、SRDEV.S、STDEV.P、VAR.S、VAR.P、MODE.SNGL、LAMBDA。 |
| ヘッダー | 引数［行フィールド］［列フィールド］［値］を指定する際に項目名を含めて指定したか、戻り値に項目名を表示するかを次の数値で指定する。省略時はExcelが自動判断する。 0 ： ［行フィールド］［列フィールド］［値］に項目名が含まれない。戻り値に項目名を表示しない。 1 ： ［行フィールド］［列フィールド］［値］に項目名が含まれる。戻り値に項目名を表示しない。 2 ： ［行フィールド］［列フィールド］［値］に項目名が含まれない。戻り値に「行フィールド1」「列フィールド1」「値1」のような項目名を自動表示する。 3 ： ［行フィールド］［列フィールド］［値］に項目名が含まれる。戻り値に項目名を表示する。 |
| 行集計深さ | 小計行や総計行を表示するかどうかを次の数値で指定する。省略時はExcelが自動判断する。 0 ： 小計行や総計行を表示しない。 1 ： 総計行を表示する。 2 ： 小計行と総計行を表示する。 -1 ： 上部に総計行を表示する。 -2 ： 上部に小計行と総計行を表示する。 |
| 行並べ替え | 行単位で並べ替える際の基準となる項目の列の番号を、昇順の場合は正数で、降順の場合は負数で指定する。列の番号は、［行フィールド］と［値］を横に並べて左から数えた番号で指定する。 省略時は［行フィールド］の昇順で並べ替えられる。 |
| 列集計深さ | 小計列や総計列を表示するかどうかを次の数値で指定する。省略時はExcelが自動判断する。 0 ： 小計列や総計列を表示しない。 1 ： 総計列を表示する。 2 ： 小計列と総計列を表示する。 -1 ： 左部に総計列を表示する。 -2 ： 左部に小計列と総計列を表示する。 |
| 列並べ替え | 列単位で並べ替える際の基準となる項目の列の番号を、昇順の場合は正数で、降順の場合は負数で指定する。列の番号は、［列フィールド］と［値］を横に並べて左から数えた番号で指定する。 省略時は［列フィールド］の昇順で並べ替えられる。 |
| フィルター | 集計対象を絞り込むための抽出条件を指定する。 |
| 対象 | ［関数］に「PERCENTOF」のように引数を2つ持つ関数を指定した場合に、2番目の関数に渡す内容を次の数値で指定する。 0 ： 列の合計（既定値） 1 ： 行の合計 2 ： 総合計 3 ： 上位の列項目の合計 4 ： 上位の行項目の合計 |
使用例…基本的なグループ集計
ここでは、下図の売上表を例として、PIVOTBY関数の使い方を説明します。
図1 クロス集計のもとになる表。
PIVOTBY関数には多くの引数がありますが、指定が必須なのは［行フィールド］［列フィールド］［値］［関数］の4つです。［行フィールド］に「商品」のセル範囲、［列フィールド］に「地域」のセル範囲、［値］に「売上」のセル範囲、［関数］に「SUM」を指定すれば、縦軸に商品、横軸に地域を配置して売上を合計できます。数式は、集計結果に応じた範囲に自動でスピル（隣接するセルに数式が自動拡張すること）します。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数)
セルH1 | =PIVOTBY(E2:E27,B2:B27,F2:F27,SUM)
図2 セルH1にPIVOTBY関数を入力して［Enter］キーを押すと、数式がセルK6までスピルして、商品ごと地域ごとに売上を合計したクロス集計表が表示される。
なお、PIVOTBY関数を修正したいときは、入力時と同様に単一のセルH1を選択して数式を修正し、［Enter］キーで確定してください。
メモ
第5引数［ヘッダー］の使い方
第5引数［ヘッダー］では、
- 引数［行フィールド］［列フィールド］［値］を指定する際に項目名（元表の列見出しのセル）を含めて指定したか
- 戻り値に項目名を表示するか
の2点を下表の数値で指定します。この引数を省略した場合はExcelが自動判断します。
| 設定値 | 説明 |
|---|---|
| 0 | ［行フィールド］［列フィールド］［値］に項目名が含まれない。 戻り値に項目名を表示しない。 |
| 1 | ［行フィールド］［列フィールド］［値］に項目名が含まれる。 戻り値に項目名を表示しない。 |
| 2 | ［行フィールド］［列フィールド］［値］に項目名が含まれない。 戻り値に「行フィールド1」「列フィールド1」「値1」のような項目名を自動表示する。 |
| 3 | ［行フィールド］［列フィールド］［値］に項目名が含まれる。 戻り値に項目名を表示する。 |
例えば［ヘッダー］に「3」を指定すると、元の表に入力されている項目名を集計結果に表示できます。［行フィールド］［列フィールド］［値］を指定する際に、項目名のセルを忘れずに含めてください。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数, ［ヘッダー］)
セルH1 | =PIVOTBY(E1:E27,B1:B27,F1:F27,SUM,3)
図3 第5引数［ヘッダー］に「3」を指定すると、集計表内に「商品」「地域」「売上」といった項目名が表示される。
第6引数［行集計深さ］と第8引数［列集計深さ］の使い方
第6引数［行集計深さ］では、クロス集計表に小計行や総計行を表示するかどうかを指定します。また、第8引数［列集計深さ］では、クロス集計表に小計列や総計列を表示するかどうかを指定します。これらの引数を省略した場合はExcelが自動判断します。
| 設定値 | 説明 |
|---|---|
| 0 | 小計や総計を表示しない。 |
| 1 | 総計を表示する。 |
| 2 | 小計と総計を表示する。 |
| -1 | 上部（左部）に総計を表示する。 |
| -2 | 上部（左部）に小計と総計を表示する。 |
なお、クロス集計表に小計行を表示するには、［行フィールド］に少なくとも2項目指定する必要があります。また、小計列を表示するには、［列フィールド］に少なくとも2項目指定する必要があります。［行フィールド］や［列フィールド］に3項目以上を指定した場合は、「3」「-3」などを指定可能です。
下図では［行フィールド］に「分類」と「商品」の2項目を指定し、さらに［行集計深さ］に「2」を指定して、小計行と総計行を表示しています。上位の階層である分類ごとに小計行が表示され、一番下に総計行が表示されます。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数,, ［行集計深さ］)
セルH1 | =PIVOTBY(D2:E27,B2:B27,F2:F27,SUM,,2)
図4 分類と商品の2階層で集計し、第6引数［行集計深さ］に「2」を指定して小計行と総計行を表示する。
さらに下図では［列フィールド］に「店舗」のセル範囲を追加し、［列集計深さ］に「2」を指定して、小計列と総計列を表示しています。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数,, ［行集計深さ］,, ［列集計深さ］)
セルH1 | =PIVOTBY(D2:E27,B2:C27,F2:F27,SUM,,2,,2)
図5 地域と店舗の2階層で集計し、第8引数［列集計深さ］に「2」を指定して小計列と総計列を表示する。
図4のPIVOTBY関数の引数［集計深さ］を変えると、集計結果は次のように変化します。
図6 「0」を指定した場合、合計行は表示されない。
図7 「1」を指定した場合は下端に、「-1」を指定した場合は上端に合計行が表示される。
図8 「2」を指定した場合は下側に、「-2」を指定した場合は上側に小計行と総計行が表示される。
第7引数［行並べ替え］と第9引数［列並べ替え］の使い方
第7引数［行並べ替え］では、行単位の並べ替えの基準となる項目の列番号を、［行フィールド］と［値］を横に並べて左から数えた番号で指定します。昇順（小さい順）で並べ替える場合は列番号をそのまま指定し、降順（大きい順）で並べ替える場合は列番号の前に「-」を付けて指定します。省略した場合は［行フィールド］の昇順で並べ替えられます。
同様に、第9引数［列並べ替え］では、列単位の並べ替えの基準となる項目の列番号を、［列フィールド］と［値］を横に並べて左から数えた正（昇順）／負（降順）の番号で指定します。省略した場合は［列フィールド］の昇順で並べ替えられます。
ここでは、［行フィールド］に「地域」「店舗」の2項目、［列フィールド］に「分類」の1項目、［値］に「売上」を指定した集計結果を、行列とも売上の高い順に並べ替えます。
行単位の並べ替えでは、「地域」「店舗」「売上」の3項目のうち3番目の「売上」の降順で並べ替えるので、第7引数［行並べ替え］に「-3」を指定します（昇順に並べ替えたい場合は「3」を指定）。
行が「地域」「店舗」の2階層になりますが、上位の「地域」の並べ替えが優先されます。まず全体が「地域」の売上順に並び（ここでは「関東」→「近畿」）、次にそれぞれの「地域」の中で「店舗」の売上順に並びます。
また、列単位の並べ替えでは、「分類」「売上」の2項目のうち2番目の「売上」の降順で並べ替えるので、第9引数［列並べ替え］に「-2」を指定します（昇順に並べ替えたい場合は「2」を指定）。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数,,, ［行並べ替え］,, ［列並べ替え］)
セルH1 | =PIVOTBY(B2:C27,D2:D27,F2:F27,SUM,,,-3,,-2)
図9 行は、「地域」「店舗」「売上」のうち3番目の「売上」の降順で並べ替える。「地域」の売上順→「店舗」の売上順に並ぶ。
列は、「分類」「売上」のうち2番目の「売上」の降順で並べ替える。
ちなみに、［行集計深さ］に「1」を指定して小計行を非表示にした場合も、まず全体が「地域」の売上順に並び、次にそれぞれの「地域」の中で「店舗」の売上順に並ぶというルールは変わりません。
メモ
第10引数［フィルター］の使い方
第10引数［フィルター］では、集計対象のデータの抽出条件を指定します。条件の指定方法はFILTER関数と同じです。「FILTER関数 ● 表や配列からデータを抽出する (2)抽出条件の指定例」を参考にしてください。
下図では、「A2:A27>=DATE(2025,2,1)」という条件を指定して、2025年2月1日以降のデータだけを集計しています。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数,,,,,, ［フィルター］)
セルH1 | =PIVOTBY(E2:E27,B2:B27,F2:F27,SUM,,,,,,A2:A27>=DATE(2025,2,1))
図10 第10引数［フィルター］を使用して、2025年2月1日以降の売上を集計する。
■抽出条件の指定例
| 指定例 | 説明 |
|---|---|
| F2:F27>=5000 | 売上が「5000」以上 |
| A2:A27<DATE(2025,2,1) | 日付が「2025/2/1」より前 |
| RIGHT(E2:E27,2)="弁当" | 商品の末尾が「弁当」 |
| (B2:B27="近畿")*(D2:D27="単品") | 地域が「近畿」かつ分類が「単品」 |
| (E2:E27="サラダ")+(E2:E27="味噌汁") | 商品が「サラダ」または商品が「味噌汁」 |
第11引数［対象］の使い方
第11引数［対象］では、［関数］に引数を2つ持つ関数を指定した場合に、2番目の引数に渡す内容を次の数値で指定します。
| 設定値 | 説明 |
|---|---|
| 0 | 列の合計（既定値） |
| 1 | 行の合計 |
| 2 | 総合計 |
| 3 | 上位の列項目の合計 |
| 4 | 上位の行項目の合計 |
［関数］を入力する際に表示される選択肢の中で、PERCENTOF関数は［数値］［数値全体］という2つの引数を持ち、「PERCENTOF(数値,数値全体)」の書式で［数値全体］を100％としたときの［数値］の割合を求めます。［関数］に「PERCENTOF」を指定した場合、第11引数［対象］で何を100%として計算するかを指定します。
「0」を指定または省略
［対象］に「0」を指定するか、指定を省略した場合、各列の合計を基準に割合が計算されます。なお、以下の図では集計結果の数値に［パーセントスタイル］の表示形式を手動で設定してあります。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数,,,,,,, ［対象］)
セルH1 | =PIVOTBY(E2:E27,B2:B27,F2:F27,PERCENTOF,,,,,,,0)
図11 「0」を指定すると各列の合計を100%として割合が計算される。
「1」を指定
［対象］に「1」を指定した場合、各行の合計を基準に割合が計算されます。
セルH1 | =PIVOTBY(E2:E27,B2:B27,F2:F27,PERCENTOF,,,,,,,1)
図12 「1」を指定すると各行の合計を100%として割合が計算される。
「2」を指定
［対象］に「2」を指定した場合、総合計を基準に割合が計算されます。
セルH1 | =PIVOTBY(E2:E27,B2:B27,F2:F27,PERCENTOF,,,,,,,2)
図12 「2」を指定すると総合計を100%として割合が計算される。
「3」を指定
［対象］に「3」を指定した場合、［列フィールド］の階層の中で、1つ上の階層の数値を基準に割合が計算されます。
=PIVOTBY(行フィールド, 列フィールド, 値, 関数,,行集計深さ,,列集計深さ,,, ［対象］)
セルH1 | =PIVOTBY(D2:E27,B2:C27,F2:F27,PERCENTOF,,2,,2,,,3)
図12 「3」を指定すると1つ上の階層を100%として割合が計算される。
「4」を指定
［対象］に「4」を指定した場合、［行フィールド］の階層の中で、1つ上の階層の数値を基準に割合が計算されます。
セルH1 | =PIVOTBY(D2:E27,B2:C27,F2:F27,PERCENTOF,,2,,2,,,4)
図12 「4」を指定すると1つ上の階層を100%として割合が計算される。