【Googleスプレッドシート】ピボットテーブルの計算フィールドを完全理解2 累計&GETPIVOTDATA関数も

前回に続いて

コレがわかれば、Googleスプレッドシートのピボットテーブルが3倍活用できるようになる！

「計算フィールド」を完全理解する記事です。

計算フィールドに加え、今回は ピボットテーブルの結果を参照して利用したい時に必須となる GETPIVOTDATA関数についても触れていきます。

今回のnoteの一部は、 関数超応用例シリーズの GETPIVOTDATA関数 超応用例 といえます！

計算フィールドとGETPIVOTDATA関数を完全理解して、ピボットテーブルをもっと活用していきましょう！

ピボットテーブルで累計を実現する方法は、最後にお題形式で登場します。

先週のnoteは 【Googleスプレッドシート】ピボットテーブルの計算フィールドを完全理解 の1回目、未読の方は 今回の記事の前に👇コチラを読んでおくことをお勧めします。

「計算フィールド」完全理解のポイント（おさらい）

ピボットテーブルを自由自在に活用する為には、自分でカスタム数式を組める「計算フィールド」を理解する必要があります。

計算フィールドを完全理解する為の4つのポイントが

1. 構造化参照のようにカラム名でデータを取得できる

2. カラム名で取得したデータは、縦横の条件でフィルタされた配列である

3. 他のセルにスピルする式は不可

4. 元データや他のセルを参照する場合は絶対参照とする必要がある

これです。

計算フィールドの中身の配列をイメージする

4つのポイントの個々の詳細は前回説明していますが、要は

ピボットテーブルエディタで 行、列を設定した後、「値」の 追加から「計算フィールドを選択し設定で

このようにした場合は

ピボットテーブルの計算フィールドの中身は、このように行・列の条件でフィルタされた「配列」なっているってことです。

ピボットテーブルで部分的なユニーク処理

この配列がイメージ出来れば、たとえば普通に数式でやろうとすると少し難解な

「指定した列だけの重複を統合して、残りの列は一番上の値をもってくる」

といった処理も ピボットテーブルだと簡単に実現できるのがわかりますよね。

この場合、ピボットテーブルの行を 列1、列2 として、値を 計算フィールドでそれぞれ、 ='列3'　、　='列4'　とするだけです。

自動的に配列の先頭が出力される仕様を理解していれば、他に関数を組み合わせる必要がないってことがわかります。

この部分的なユニーク処理は UNIQUE関数だけでは実現出来ませんし、FILTER関数やXLOOKUP関数を組み合わせても、なかなか苦労する処理だったりします。

もし数式で実現するなら、Googleスプレッドシートの場合はSORTN関数の出番ですね。

この元ネタはExcelクイズとして Xで m.tanaka 様 が出題されてたものです。ちょうど ピボットテーブルのお題として最適なので使わせていただきました。

では先週の復習ってことで、このネタの応用編を一発目のお題としてみましょう。

列1、列2 の重複を統合して 列3、列4は一番上ではなく最後のデータ（一番下）を出力したい、といった場合はどうでしょうか？

Q1. 表（範囲A1:D10 1行目は見出し）を列1、列2の重複をまとめた上で、列3、列4については一番下（最後）の値を表示したい

左の表をピボットテーブルを用いて 列1、列2の組みわせで重複を統合した上で 列3、列4は 最後の値を出力した 表にしたい！ってお題です。

販売データから 顧客毎の最後に購入した日、購入商品を 一覧にしたい、といったケースとか、先頭より最後の値の方が需要がありそうですよね。

👇以下のデータをA1セルを選択して貼り付けして、チャレンジしてみましょう！

列1	列2	列3	列4
A	a	1	500
A	a	1	200
A	b	2	300
A	b	1	400
B	a	1	300
B	a	1	200
B	a	2	100
B	b	1	300
B	b	2	400

ピボットテーブルは簡単に出来ちゃうって人は、シート関数を組み合わせて数式で実現する方法も考えてみましょう！

↓↓
ここから回答です。
↓↓

A1. 表（範囲A1:D10 1行目は見出し）を列1、列2の重複をまとめた上で、列3、列4については一番下（最後）の値を表示する

ピボットテーブルで実現する回答です。

範囲 A1:D10 を選択した状態で メニューから 挿入 ＞ピボットテーブルとして、出力先のセルを選択。

ピボットテーブルエディタで 行 を 列1、列2 と指定。「総計を表示」のチェックは外し、「行ラベルを繰り返す」にはチェックを入れておきます。

こうすることで 全行にラベルが表示されます

そして「値」に計算フィールドを2つ追加して、それぞれ

とします。

一番下を取得する方法は、マイナスで逆（下）から指定できる CHOOSEROWS関数が便利ですね。（Googleスプレッドシートには ExcelのTAKE関数がありません）

最後に 生成されたピボットテーブルのセルを選択して 列名を「列3」「列4」と元の表と同じ名前にすればOK

ここで元データの表の見出し行を参照しようと =C1 と入れると

「ピボットテーブル内のセルに数式を含めることはできません。」

とエラーになるので注意。

ピボットテーブルの見出しは、手入力かコピペとしましょう。

ピボットテーブルで数式が入れられるのは、エディタの計算フィールドだけです。

（オマケ別解）A1. 表（範囲A1:D10 1行目は見出し）を列1、列2の重複をまとめた上で、列3、列4については一番下（最後）の値を表示する

オマケで数式で実現する方法の回答も。こちらは SORTN関数の応用が一番シンプルです。

一番最後（下）じゃなくて、列3、列4の一番上の値を出力であれば、

=SORTN(A2:D,100,2,1,1,2,1)

で実現できるので、じゃあ A2:Dの部分を先に逆順に並び替えておけばいけますね！

というわけで、SORT(A2:D,ROW(A2:D),0)　ROW関数の行番号を降順（0またはFLASE指定）とする、この式を SORTNの第1引数 にいれちゃえばOK。

今回はピボットテーブル回なんで細かい説明は省きますが、Excelには無いSORTN関数は マイナーだけど非常に強力な関数で、mirの 推しカン（推してる関数）の一つです！

GETPIVOTDATA関数も理解しておこう

せっかくのピボットテーブル回なんで、ピボットテーブルの結果を他のセルで参照する際に必要となる GETPIVOTDATA関数についても触れておきましょう。

Googleスプレッドシートの GETPIVOTDATA関数とは

GETPIVOTDATA関数は、ピボットテーブル内の「値」フィールド部分をフレキシブルに参照する関数です。

ピボットテーブルをほぼ使わない人だと、まったく使ったことない関数じゃないでしょうか？

Googleスプレッドシートだと、ピボットテーブルは利用してるけど、この関数は使ったことないって人も多いかもしれません。

実はこの関数、Excelでピボットテーブルを参照する場合は、意識せずに使ってるんですよね。

このように Excelの場合は イコールを入れてから マウスクリックででピボットテーブル内のセル（例えば 総計のセル）を選択すると、

通常なら =H16 となるところが

自動で GETPIVOTDATA関数を使った式になります。

これによって、データの増減でピボットテーブルのサイズが変わって総計のセル位置が変わっても 参照がズレることがないわけです。

Excel界のレジェンド田中先生も「ピボットテーブルは途中経過として作成するもの」「GETPIVOTDATA関数を知らなかったら、ピボットテーブルを活用することはできない」と書かれています。（これは諸説あると思いますが）

一方、Web版のExcelやGoogleスプレッドシートは ピボットテーブル内のセルを参照しても 自動で GETPIVOTADATA関数の表記にはなりません。

普通に イコールでセルを選択しても 通常の参照と変わらないA1表記になります。

自力（手入力）でGETPIVOTDATA関数を組むとなると、ちょっとハードルたかいですよね。。

このせいもあって、あまりGoogleスプレッドシートではGETPIVOTDATA関数は使われていない印象です。

そもそもピボットテーブル自体も QUERY関数で代替できるケースが多いので使ってない人も多そう。。

とは言え、ピボットテーブルを扱う上でGETPIVOTDATA関数を理解していると便利ですし、この後登場しますが Excelと違ってGoogleスプレッドシートならではの GETPIVOTDATA関数の超応用例もあります。

せっかくの機会なんで、ざっくりと理解しちゃいましょう。

GETPIVOTDATA関数の使い方

GETPIVOTDATA関数で、わかりにくいのが引数の設定

👆なんだかよくわからんですよね。この第1、第2 引数を

こう読み変えてみましょう。値が1列のシンプルなピボットテーブルの「総計」を取得するケースなら、第3引数以降は不要です。

さらに言えば「総計」が1つしないピボットテーブルなら、第1引数すら省略（空白）でもOKです。

例えば F2セルを起点として展開されるピボットテーブルを対象とした場合は、

これで G6セルの総計が取得できちゃうんですねー。こんなことが出来るのはGoogleスプレッドシートだけです。

もちろんピボットテーブルの値が2列になって「総計」が2つの場合は、

第1引数は必須となります。

ここで勘違いしやすいんですが、第1引数は ピボットテーブルの値の列名をテキストで指定する必要があります。

このように販売価格を集計した列がピボットテーブル側に1つしかないと 元データの列名 "販売価格"を第1引数で指定しても「総計」が取得出来ちゃうんですが、これは不具合が出るケースがあるので、避けた方がよいです。

たとえば

元データの販売価格の列を集計した列がピボットテーブルに2つ以上ある場合は、このようにエラーとなります。

第1引数はピボットテーブル側の 値の列名を使うようにしましょう。

列の移動がないのであれば、第1引数はセル参照としてもOK

では、総計じゃなくて ぶどう の販売価格の合計（はんばいサム）を取得したい場合はどうすればよいでしょうか？

ここで 第3引数以降が必要になります。

ぶどうの販売価格の合計 2200を取得したい場合は

"商品"が「ぶどう」である "はんばいサム"の値を返せ、このように指定すれば良いってことです。

引数がほぼ文字列指定なんで、ホント違和感がありますよね。

条件が2つ以上の場合は、これを繰り返せばOK

日付データを扱う場合は注意で 参照にしてしまうとシリアル値化されてしまうので

TEXT関数で "yyyy/mm/dd" と成形するか、TO_TEXT関数を使って

このように表示されたままの文字列として日付を取得する必要があります。

また GETPIVOTDATA関数の引数に ピボットテーブルをセル参照して使うことが出来るは Googleスプレッドシートのみで、Excelでこの方法は使えません。

※セル参照を配列化すればExcelでも条件部分をセルから取得できます

GETPIVOTDATA関数の使い方、引数は理解できたでしょうか？

Googleスプレッドシート GETPIVOTDATA関数 超応用例

GoogleスプレッドシートのGETPIVOT関数は Excelのものと少し違っていて、それを踏まえて使いこなす必要があります。

ポイントは この3つ。

GETPIVOTDATA関数の特徴1. ARRAYFORMULAでスピらない（配列を返せない）

Excelの場合は、GETPIVOTDATA関数で 第1引数や、第4引数に配列を指定することで結果をスピルさせることが出来ます。

ここでセル参照はダメですが、配列ならOKというケースがある Excel関数の複雑な特徴を知っていれば

文字列の場合は 参照の配列に &"" を付加することで、配列化して結果をスピらせるといったテクニックが GETPIVOTDATA関数でも活用できます。

しかし、残念ながら Googleスプレッドシートの GETPIVOTDATA関数は ARRAYFORMULAを付けてもスピりません（配列を返せません）

ピボットテーブルの行や列を参照してスピらせたい場合は

このように LAMBDAヘルパー関数のMAPを使う必要があります。

Excelの参照の配列の考え方も難しいですが、GoogleスプレッドシートだとMAPを使わないとスピらないのもなかなか悩ましですね。

ただ、違いを理解できれば、GETPIVOTDATA関数も一つの式で配列処理が出来るってことはわかりましたね！

GETPIVOTDATA関数の特徴2. 結果は参照を返す （: で範囲取得ができる）

👆こちらをご覧ください。
Googleスプレッドシートで ピボットテーブルに対して

=ROW(GETPIVOTDATA(,$F$2))

と GETPIVOTDATA関数の結果に対してROW関数を使うことで、参照しているピボットテーブルの総計のセルの行番号 8を返しているのがわかりますね。

Googleスプレッドシートの場合は GETPIVOTDATA関数は参照を返すという特徴があります。

一方 ExcelのGETPIVOTDATA関数は値を返しており、参照返しではありません。

では、Googleスプレッドシートで参照返しが出来るとなにが便利か？

: 参照演算子 を使って、ピボットテーブル範囲をまるっと取得することが出来ちゃうってことです！

F2セルを起点とする 値が1列、もしくは総計があるピボットテーブルであれば

このように起点となる $F$2と GETPIVOTDTATA関数で取得した総計（ピボットテーブルの右下）を 参照演算子で繋げることで、ピボットテーブルを丸ごと取得できます。

ARRAYFORMULAいらずで展開するんですね。

ピボットテーブルの拡大、縮小は 名前付き範囲化しても検知できないので、結構便利に使えるんじゃないでしょうか？

ただし総計がない場合は 一番右下をGETPIVOTDATAで取得するのに一工夫必要です。

GETPIVOTDATA関数の特徴3．ピボットテーブル内の計算フィールドで使える

ピボットテーブルを参照する GETPIVOTDATA関数が、自身のピボットテーブルの計算フィールドで使えるのも Googleスプレッドシートの特徴です。

超応用例って感じですね。

たとえば

ピボットテーブルの計算フィールドで

='販売価格'-'原価'

として 集計をSUMで設定した 値を追加して「利益」としています。

ここで、ピボットテーブル内の隣の列に この「利益」に ×0.8 したものを 実質利益として追加したいと思った場合、計算フィールドでどうやって利益の値を参照すればよいか？

もちろん、この程度の計算なら もう1回同じ式を記述して

=('販売価格'-'原価')*0.8

でいいんですが、もっと複雑な式を入れていた場合は、出来れば「利益」の値を参照たいところ。

これを実現するのが、ピボットテーブル内GETPIVOTDATAです。

計算フィールドを追加して

とすると、上の画像のように 総計以外は 利益が参照できます。

わかりづらいですが、1つ目の "商品"（ダブルクォートの方）は、見出し名による指定で、2つ目の '商品'（シングルクォート）は、

行毎に、この商品（中身は配列）を呼び出したものです。

で、総計の行は中身は元データの商品が全て入った配列なので

総計の行の '商品' は、一番先頭の りんご を返ししまうので商品が 「りんご」の「利益」の値が  総計に入ってしまうわけです。

これを避ける為に '商品'の配列の中身に対して ユニークな個数が1より大きい場合とそれ以外で分岐させるIFの式を用意します。

ここで使えるのがCOUNTUNIQUE関数です。

IF(COUNTUNIQUE('商品')>1,

こちらを使って

こんな式を組むことで

総計を含めて 利益の値をそのまま参照して、

利益の値 ×0.8 をピボットテーブルに追加することができました。

これを前回のQ1 グループ毎の割合のお題に活用すると

https://note.com/mir4545/n/nb7fd87271c5e#9ddbe679-1423-439a-ae3c-90285112a381

簡単とは言えませんが、こんな式になります。

ただし、このピボットテーブル内 GETPIVOTDATA関数は注意点があって

GETPIVOTDATA関数で参照する値は 計算フィールドで入れたものである必要があります。

普通に 値で 数量を追加して SUＭとした 計算フィールドでない場合は

なぜか循環参照扱いで1つ目のセルが #REF!エラーとなります。

このテクニックは最後のお題でも使えますが、正直使わない方が簡単ですｗ

どっかで使えそうなんで紹介しましたが、あまり良いお題が思いつきませんでした。

もっと複雑な集計をしている人だと活用できそうな気がするんでが・・・。

Googleスプレッドシートのピボットテーブルで累計を実現する

前回の最後のお題を自力でトライした人、とにかくピボットテーブルで累計を作る答えが欲しい人、お待たせしました。

ピボットテーブルで累計に挑戦してみましょう。

Q2. ピボットテーブルをで、年・月ごと支店ごとに合計を集計して、その隣に累計を表記したい

いままで学んだ知識を組み合わせてピボットテーブルの累計生成にチャレンジしてみましょう！

左のデータ、今回はテーブル化ではなくお尻を指定せず 範囲を A:Cとして、画像右側の

行を 年、月でグループ化
列を支店名
どちらも総計を表示
合計の隣に累計列

このようなピボットテーブルを作成してみましょう。

👇元の表のデータ

日付	支店	売上
2024/04/16	A	100
2024/04/16	B	200
2024/04/26	A	150
2024/04/26	B	100
2024/05/08	A	100
2024/05/08	B	150
2024/05/22	A	200
2024/05/22	B	200
2024/06/10	A	150
2024/06/10	B	150
2024/06/26	A	100
2024/06/26	B	100
2024/07/10	A	100
2024/07/10	B	100
2024/07/24	A	100
2024/07/24	B	100
2024/08/01	A	200
2024/08/01	B	300
2024/08/20	A	200
2024/08/20	B	100
2024/09/12	A	200
2024/09/12	B	150
2024/09/27	A	150
2024/09/27	B	100
2024/10/03	A	150
2024/10/03	B	300
2024/10/22	A	200
2024/10/22	B	300
2024/11/07	A	150
2024/11/07	B	100
2024/11/27	A	100
2024/11/27	B	100
2024/12/12	A	300
2024/12/12	B	200
2024/12/26	A	300
2024/12/26	B	100
2025/01/14	A	100
2025/01/14	B	300
2025/01/30	A	200
2025/01/30	B	300

やってみましょう！

↓↓
ここから回答です。
↓↓

A2. ピボットテーブルをで、年・月ごと支店ごとに合計を集計して、その隣に累計を表記する

回答です。一応全手順を書いておきます。

ピボット累計というシート名として、まずA:Cを選択した状態で

挿入 ＞ ピボットテーブル を選択。挿入先を 既存のシートで E2（これはどこでもよい） を選択

ピボットテーブルエディタで、行を 日付、列を 支店とする。（今回は総計あり）

値 に「売上」を追加、集計は初期値がSUMになっているので、そのままでＯＫ

空白も集計されてしまっているので、先にフィルタで空白を除外しておきましょう。

フィルタの項目で「追加」から、とりあえず日付を選択し 条件でフィルタで「空白ではないセル」とします。

これで余計な空白行・列の集計が消せました。

続いて日付のグループ化ですが、これは ピボットテーブルエディタではなく、グループ化したい日付が表示されているピボットテーブルのセルを適当に選択した状態で右クリックし

ピボット日付グループを作成　＞　年-月

と選びましょう。

ここはGoogleスプレッドシートにしては選択肢が結構充実してる気がします。

グループ化され、売上を年-月毎、支店ごとに合計したピボットテーブルが完成しました。

ここから累計に入っていきます。まずは王道の回答から。

サイドバーのピボットテーブルエディタを閉じてしまっている場合は、ピボットテーブルの左下の鉛筆（編集）アイコンをクリックしてピボットテーブルエディタを起動し

値の「追加」で「計算フィールド」を選択

集計の欄が初期値はSUMになってるので、カスタムに変えてから

今回は 条件が日付と支店の2条件なんでSUMIFSを使います。

とりあえず作ってみた式がこんな感じ。

グループ化していても'日付 - 年-月' ではなく、'日付' でピボットテーブルの行は取得できます。

'日付' の中身は 2024-5月の行の場合は、条件に合致する

が配列で入っているので、この中で一番大きな（一番後ろの）日付を MAX関数で取得して

$A:$A,"<="&MAX('日付')

その行の日付配列の中の最大値 以下の A:A（日付）という条件にすれば

累計が実現できますね。

ただ、この式だと A支店、B支店 の累計は問題ありませんが、総計の累計が正しい値となりません。

これは 総計だと '支店' の中身が A支店とB支店が混在する配列になっているけど、先に登場する A支店のみを条件にSUMIFSで計算されてしまっている為です。

前回のグループ系の割合を算出するお題では、ここでUNIQUE関数で 中身の配列の重複を排除し、ARRAYFORMULA関数やSUMPRODUCT関数を組み合わせて内部を配列処理させましたが

https://note.com/mir4545/n/nb7fd87271c5e#5936265a-1be9-43c4-976f-2787089e7b13

残念ながらGoogleスプレッドシートのSUMIFSはスピらない関数である為、ARRAYFORMULAが効きません。

というわけで、回答の一つ目は 支店をUNIQUEしたものを LAMBDAヘルパー関数のMAPに入れて、各支店ごとに算出したものを最後にSUMする

■累計の計算フィールドの回答1

👆こんな式が有効です。

さらに回答2つ目は、支店の部分はCOUNTIF関数を使った条件で 1以上か0を返させ、SUMPRODUCT関数でまとめて処理する もっとも短い（と思われる）

■累計の計算フィールドの回答2

👆こんな式もアリです。

一応、せっかく先ほど学んだんでピボットテーブル内 GETPIVOTDATA関数の参照を使った

👆こんな式にする方法もあります。やってることは先頭行を固定して下にフィルする累計の方式なんですが・・・

やはり 今回のような累計でGETPIVOTDATA関数は、複雑になるだけであまり意味がないですね。

とりあえず無理、難しい、不可能と思われていた Googleスプレッドシートのピボットテーブルで累計ができました～。

【オマケ】年毎で累計をリセットしたい

お題ではありませんが、年毎に累計をリセットする方法も書いておきましょう。

まず行に日付を追加して 年でグループ化、さらにもう1回行に日付を追加して、今度は月でグループ化

列に支店、値に 売上を追加（集計を SUM）とすれば

Excelのピボットテーブルのような ⊞ボタン、⊟ボタンで開閉できて、年単位で小計が算出されるピボットテーブルが生成できてます。

ここに累計を加えるので、値に「計算フィールド」を追加して、集計を「カスタム」とし

数式をこんな感じで入れます。

先ほどの累計のSUMPRODUCT関数の回答の式がベースです。

年を条件に負荷して年毎の累計とし、かつ 総計にも対応できるように

COUNTIF(UNIQUE(YEAR('日付')),YEAR($A:$A))

この式を条件に追加しています。

ちなみに COUNTIFを2つ使うならCOUNTIFSにまとめられないの？と思うかもしれませんが、配列サイズが違うとエラーが出るので、ここはそれぞれCOUNTIFで処理した方が良いです。

また、SUMPRODUCT関数を使っているので、内部は自動で配列処理されるので ARRAYFORMULAは必要ありません。

https://note.com/mir4545/n/n07e6091e0e28#cd5d489a-e6f6-4f5f-9a1a-9109e00ab6b4

これでピボットテーブルに年単位でリセットされる累計を追加することができました。

まぁ、Excelだったら 値フィールドの設定から計算の種類で「累計」を選択するだけなんで、さくっと出来ちゃうんですがｗ

「Googleスプレッドシートのピボットテーブルで累計は無理」なんて書き込みも見かけますが、

Googleスプレッドシートのピボットテーブルでも累計は出来る！

ただし、ちょっとハードルが高くて

• 計算フィールドの理解

• 配列を扱う関数の理解

が必要。

ということがわかったんじゃないでしょうか。

Googleスプレッドシートのピボットテーブル 完全理解へ

2週にわたって、Googleスプレッドシートのピボットテーブルを完全理解する為に必要となる

• 計算フィールド

• 配列を扱う数式

• GETPIVOTDATA関数

• 日付グループ等、その他ピボットテーブルの設定

を深く書いてみましたが、いかがだったでしょうか？

最後まで読んでいただいた方は、今までピボットテーブルでは無理そうと思ってたことが「出来そう」に変わった人もいるでしょうか？

そんなマインドの変化に繋がっていれば幸いです。

次回は GASをやるか、そろそろ正規表現REGEX系関数か QUERY関数か・・・。悩み中。