【Googleスプレッドシート】ピボットテーブルの計算フィールドを完全理解

今回は Googleスプレッドシートの ピボットテーブル機能、その中でも「計算フィールド」に特化した記事です。

Googleスプレッドシートの集計において、どうしても 目立つ QUERY関数 と比べると、やや存在感の薄いピボットテーブル。

このピボットテーブルを使いこなす上で、重要なポイントが

計算フィールド という数式を自由に組める機能。

でも「これ、わけわかんないよー」(しかも数式入れる欄がせまーい！！）って人が多い、躓くポイントだったりもします。

ネット上で「計算フィールド」について検索しても、シンプルな使い方に触れたサイトはありますが、詳しい情報が見当たりません。

ちなみに

で検索すると、3番目に表示されるのは

mirの知恵袋の回答だったりしますｗ

でも、この計算フィールドを完全理解すれば 自由自在な集計が可能となり

👆 QUERY関数では対応できないテキストを扱った集計や

👆 ピボットテーブルの通常機能では実現できない グループ計に対する割合を算出したり

👆 Googleスプレッドシートのピボットテーブルで対応するのは難しいと言われる 累計 を算出

することが可能となります！

これらはシート関数を組み合わせた複雑な数式を組むよりも、ピボットテーブルの計算フィールドを活用した方が圧倒的に簡単です。

※もちろん関数の知識や配列の理解がそれなりには必要です

今回のnoteを最後まで読んで、計算フィールドを完全理解しましょう！

上の3つの例は お題形式で後半で触れていきます。（ただし 累計に関しては次週となります）

先週のnoteは Googleスプレッドシート、Googleドライブの「公開」機能の続きを書きました。

「計算フィールド」完全理解のポイント

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

👆列名にシングルクォート付けて、こんな風に使うんでしょ。という人も、実はなんとなくわかった気になってるだけかもしれません。

最初に計算フィールド完全理解のポイント4つを書いておきます。

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

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

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

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

これらを後ほど解説していきます。

ピボットテーブルについて少しだけ

 「ピボットテーブルとは？」から入ってしまうと「計算フィールド」の説明にたどり着くまでが長すぎるので、

今回はGoogleスプレッドシートのピボットテーブルは知ってる、多少使える人を対象として、ほぼ「計算フィールド」だけに特化して書いています。

でも、少しだけピボットテーブルの理解もしておきましょう。

「ピボットテーブル」の基本は他のサイトを参照

ピボットテーブルがよくわかってません。という人は、先にピボットテーブルの基本的な使い方を学んでおきましょう。

👆この辺りのサイトを読めば、なんとなく理解できると思います。

Googleスプレッドシートのピボットテーブルは、Excelのピボットテーブルを使ったことがある人なら、基本的な集計は問題なく出来ると思います。

ただ、集計した表のカスタマイズ等ではExcelには及ばず、複数のテーブルをピボット集計といったことも出来ません。

Excelと比べてGoogleスプレッドシートのピボットテーブルの良い点は、手動で「更新」する必要なく、リアルタイムで元データに連動して自動で更新される点、そして今回紹介する「計算フィールド」の自由度があげられます。

ピボットテーブルとQUERY関数の違い

Googleスプレッドシートの最強関数 QUERY関数とピボットテーブルの違い、使い分けについても少しだけ触れておきましょう。

QUERY関数は、group by や pivot句を使ってピボットテーブルと同じような集計表を生成することが可能です。

では、QUERY関数が使いこなせればピボットテーブルは不要か？というと、そんなことは無くて、QUERY関数単体では実現できない処理がピボットテーブルでは簡単に対応できることもあります。

ピボットテーブルを使うメリット（QUERY関数では出来ないこと）を4点ほどまとめました。

1．ピボットテーブルは集計行を作成できる

1つは 集計行、集計列を生成できるか？という違いがあります。

QUERY関数は残念ながら 上の画像のような グループ計や総計といった行や列を生成することが出来ません。

一方、ピボットテーブル機能であれば サイドバーの設定から

総計を表示にチェックを入れるだけで、簡単に実現できます。

2. ピボットテーブルはダブルクリックで集計データのソースを確認できる

ピボットテーブルでは集計データのセルをクリックすると、その結果の元になるソースデータ（詳細）が別シートとして立ち上がります。

これはQUERY関数というか、シート関数には出来ない処理です。

3. ピボットテーブルは集計関数以外の関数が利用可能

「え、ピボットテーブルでこんなこと出来るの？」

という人もいるかもしれませんが、実はピボットテーブルは今回説明する「計算フィールド」を使えば、集計関数以外の様々な関数を利用して表を作ることが可能です。

QUERY関数で集計する場合は、sum, count, avg, max, min のみ可能で、他の関数は使えませんし、上のようなテキストを集計表にすることは出来ません。

4. 表が自動で見やすく装飾される

QUERY関数で生成された表は自動で色が付いたり線が引かれるといったことは出来ませんが、ピボットテーブルであれば 表が自動で見やすく装飾されます。

このピボットテーブルの色味（カラーバランス）は、メニューの

表示形式　＞　テーマ

からテーマを変えることで、好みに合わせて調整可能。

ただし、テーマはスプレッドシート単位（ブック単位）でグラフや他の表にも影響するので注意。

これ以外にも、不慣れな人にはハードルが高いQUERY関数 SQL構文を記述するのに比べ、GUIで割と簡単に操作できるピボットテーブルの方が扱いやすいといった点も魅力です。

もちろんQUERY関数の方も、ピボットテーブルでは対応できない 複数テーブルや複数シートのデータを連結したテーブル や 数式で加工した配列、IMPORTRANGEと組み合わせて他のスプレッドシートから取得したデータを元に集計できる点が非常に強力ですし、それ以外にも様々な利点があります。

これらはQUERY関数のメリットは noteで「QUERY関数」特集の中で取り上げています。

「計算フィールド」完全理解への道

冒頭に書いた 計算フィールドを理解する為の4つのポイントを説明していきます。

サンプルデータがあった方が分かりやすいので

タイプ	区分	数量
1	A	2
1	B	8
1	C	4
1	A	10
1	B	10
1	C	4
1	A	2
1	B	5
1	C	16
1	A	0
1	B	3
1	C	4
1	A	2
1	B	3
2	C	4
2	A	2
2	B	3
2	C	4
2	A	3
2	B	3
2	C	4
2	A	2
2	B	3
2	C	4
2	C	4

こちらをスプレッドシートにコピペ、テーブル化してテーブル名を sample としておいてください。

👇テーブルに関してはコチラを参照

基本のピボットテーブルを作成する

ピボットテーブルの作成は メニューの

挿入 ＞ ピボットテーブル

を選択します。

最初にダイアログが立ち上がり、元になるデータ範囲とピボットテーブルの挿入先を選択します。

この時、先に元になるデータをテーブルにしておけば、 テーブル内の適当なセルを選択した状態で 挿入 ＞ ピボットテーブル で、データ範囲が自動でテーブル名指定となるので便利です。

データ範囲、挿入先を決めると ダイアログは閉じられ、サイドバーにピボットテーブルエディタが表示されます。

ここで列の見出し（カラム）である タイプ、区分、数量を使って

行、列、値、フィルタ

を設定して 集計表を作成していくわけです。

左側にタイプ、区分と表示させて数量の合計を表にしたい場合は、こんな感じの手順になります。

もし縦横でクロス集計としたい場合は、横に並べたい項目を「列」の方に入れればOK

行 ⇔ 列 の切り替えをしたい場合、ドラッグ＆ドロップで行に入れたものを列に動かす、といったことも出来ます。

「値」の部分の集計関数の選択肢は色々ありますが、

SUM、COUNTA（COUNT）、AVERAGE、MAX、MINくらいしか使わないかも。

COUNTUNIQUEが用意されているのが、Googleスプレッドシートって感じですね。

今回は以下の形でSUMで合計を集計した表を、まず作るところからスタートしましょう。

ピボットテーブル空白除外の注意点

ピボットテーブルのデータ範囲を A:C のように指定した場合や テーブルの下の方に空白行がある場合は、

ピボットテーブルがこのように空白行のデータも集計してしまいます。

これを除外する為には、「値」の下の「フィルタ」を使います。

フィルタの「追加」ボタンを押して、適当な列（とりあえず「タイプ」）を選択して、条件でフィルタ　＞　空白ではない　とすればOK。

空白が除外されました。

ここで初心者がやりがちなのが、値の一覧の 空白部分のチェックをはずして空白除外するフィルターとしちゃう方法。

これは✖（ダメ）です。

これでも空白は除外されますが、これはタイプを 1と2だけに絞り込むというフィルター設定なので、後からタイプ 3がデータに追加されても新たに追加されたタイプ 3はピボットテーブルに反映されません。

空白除去は必ず「条件でフィルタ」を使いましょう。

これで基本のピボットテーブルが出来ました。

「計算フィールド」完全理解1. 構造化参照のようにカラム名でデータを取得できる

それでは試しに 値に計算フィールドを追加していきましょう。

計算フィールドを追加すると

こんな感じで「数式」の欄には =0 がいった状態で 集計は SUMが選択され、表示方法は「デフォルト」がセットされた 「計算フィールド1」という項目が追加されます。

表側にも1列追加されてますね。

で、この後どうすんの？

ってなりますよね。。。

チュートリアルなしのオープンワールドのゲームに放り出された感じで、ここから何をどうしたらいいの？ってなる人が続出してるんじゃないかと。

集計は「SUＭ」と「カスタム」の切り替えが出来て

表示方法は デフォルト以外に、「行集計に対する割合」、「列集計に対する割合」、「総計に対する割合」が選択できます。

集計に対する「割合」ってことで、今回のお題で使えそうにも見えますが、実はこの機能ではグループ計に対する割合は計算できません。

では、ポイント1の 「カラム名でデータを取得できる」を実践してみましょう。

数式の =0 の 0を消して カラム名「数量」という文字を = の後ろに入力します。

わかりましたでしょうか？

数量　と入力して最後に Enter すると、

数量　➡　'数量'

と シングルクォートで自動で括られ、ピボットテーブル側の計算フィールドの列に 隣の 数量のSUMと同じ数字が入りました。

では、集計をSUM ではなく「カスタム」にするとどうなるか？

ここで、いきなり謎の数字に変わります。

この数字の意味が理解できれば、計算フィールドは怖くありません。ここをポイント2で解説していきます。

この説明の前に、じゃあこの状態でSUM関数を組み合わせたらどうなるか？をやってみましょう。

数式を　=SUM('数量')　とすと、先ほどの 集計がSUMに設定された状態で ='数量' の時と同じ結果が返りました。

これで 集計のSUMを使わず、自由に式が組める 完全理解に一歩近づきましたね！

注意点として カラム名として参照に使えるのは、あくまでも 元データの列の見出し（エディタの右側に表示されている文字）のみです。

自分で名前を変えた列名を使ったり、日付グループ化して 「日付 - 年-月」という列があったとしても、='日付 - 年-月'　としても 参照は出来ません。

「計算フィールド」完全理解2. カラム名で取得したデータは、縦横の条件でフィルタされた配列である

それではSUMを使わずに ='数量' で表示された、この値が一体なんなのか？を説明していきましょう。

この値の意味を知るのに、もっともわかりやすい方法がTEXTJOIN関数を使う方法です。

数式フィールドの式をこのようにしてみましょう。

すると

このような結果が返ります。

これは 元データの 数量の列の値を タイプ、区分でフィルタしたものですよね・・・。

タイプ1、区分A の計算フィールドの結果 2,10,2,0,2 は、元データの sampleテーブルを タイプを1、区分をAでフィルタした時の 数量 を カンマで連結したものと同じです。

そして  2,10,2,0,2 を合計すると、右の 数量のSUM の値 16になるのがわかりますね。

これがポイント2の「カラム名で取得したデータは、縦横の条件でフィルタされた配列」の意味です。

これはクロス表にした場合も同じ結果となります。

総計のセルには 全ての数量の値が入っています。

つまり ='数量' で取得できるデータ（中身）は、その行の タイプ、区分で元データをフィルタした 数量 の配列である。

ただし 他のセルに結果がスピル（展開）されない為、単純に ='数量' とした時は 配列の先頭の値だけが表示されている。

というわけですね。

中身が実は配列だということがわかりましたね。

「計算フィールド」完全理解3. 他のセルにスピルする式は不可

では、配列が展開できるようにピボットテーブル内の一番右側で計算フィールドを使って 横に展開できるように TOROW関数を組み合わせたらどうなるか？

残念ながら 右側の列は空いてますが 横に配列が展開（スピル）されません。

というわけで

配列を1つの値に集約できる SUMや TEXTJOINなどの関数を組み合わせて、欲しい結果を得るのがポイントってことですね。

「計算フィールド」完全理解4. 元データや他のセルを参照する場合は絶対参照とする必要がある

それでは カラム名ではなく 任意のセルを参照したい場合は、どうすればよいか？を見ていきましょう。

上の画像のようにH1セルの数字 2を掛けた数量の合計をピボットテーブルに入れたい場合はどうすればよいでしょうか？

普通に =SUM('数量')*H1 としてしまうと 確定した瞬間に

=SUM('数量')*#REF!

と セル参照部分が REFエラーになってしまいます。

ピボットテーブル側の結果は正しく2倍になっていますが、このエラーをなんとかしたい。ここで必要になるのが絶対参照です。

セル参照を絶対参照で記述することで、エラーにならずピボットテーブル外のセルを参照させることができます。

これを活用することで、元データを参照した式を組むことができます。

ただしピボットテーブル内の値をセル参照しようとすると、循環参照となってエラーになります。

また計算フィールドの結果（列）を別の計算フィールドで参照したいケースもあるかと思いますが、計算フィールドで設定した列名を参照で使うことも出来ないようです。（なにか方法が発見できたらお知らせします）

そして、ピボットテーブルの範囲はテーブル名で指定が出来ましたが、残念ながら計算フィールド内では テーブルの構造化参照は利用できませんでした。

ちなみに、この「計算フィールド 1」という列の名前は、セル上で名前を変更できます。

計算フィールドの4つのポイント、理解できましたでしょうか？

「計算フィールド」を活用したピボットテーブルお題

それではお題に入っていきましょう！

Q1. ピボットテーブルでグループ計に対する割合を表示させたい

今回は このピボットテーブルに 1列追加して「タイプ毎の数量のSUMに対する各数量のSUMの割合を計算させたい」ってお題です。

これ、シンプルの総計に対する割合だったら「表示方法」で列集計に対する割合、もしくは総計に対する割合 とすれば、

👆 簡単に出来ちゃうんです。

でも、今回のお題はグループ計に対する割合、つまり タイプ1 区分Ａ の数量 16の右には 16÷73 = 21.9% を表示させたいってことです。

👆こんな感じにしたい。

ポイントは 総計の行が100%となっている点です。

どうでしょう、計算フィールドを使って出来そうでしょうか？

少し難しいって人は、総計の割合が100%にならなくてもよいので、まずは作ってみましょう。

チャレンジしてみましょう！

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

A1. ピボットテーブルでグループ計に対する割合を表示させたい

回答です。

もしくは

ちょっと複雑ですが、こんな式でもOK。

最後に表示形式を %とすればOK

解説していきましょう。

まず

グループ毎の合計の値である 73、36を 各行で分母として使いたいのですが、これはピボットテーブル内から取得することは出来ません。

元データを参照して A列（タイプ）が ピボットテーブルを行単位で見た時の「タイプ」と一致するという条件に合致するC列（数値）を合計したいので

SUMIF関数を使い、かつ ポイントで学んだ カラム名での取得とセルの絶対参照での取得を利用して

SUMIF($A:$A,'タイプ',$C:$C)

こう書けるかなと考えます。さらにここから割合を算出するってことで、

こんな式が思いつく人が多いんじゃないでしょうか？

でも、これだと 総計の部分が

149.32% になってしまいます。これはなぜか？

それは SUMIF内で使っている 'タイプ'　の中身が、こちらもポイントで学んだ通り 実際は 👇 のような配列である為です。

SUMIF($A:$A,'タイプ',$C:$C)　の計算をした時、SUMIF自体は内部で配列処理が出来る関数ではないので、 'タイプ' が返す配列の先頭の値が使われます。

なので、総計以外は 配列の先頭の値を条件に使えば 問題なく計算できますが、総計の行だけは タイプが1と2の2種類があるうちの先頭の1のみがSUMIFの条件として使用されるため

109/73 = 1.4931… となってしまうわけです。

これを回避する為に まず UNIQUE関数で ユニークな値にした上で、

配列処理が出来る関数 ARRAYFORMULAを組み合わせてSUMIFを配列対応させた上で最後にSUMするか、

ARRAYFORMULAいらずで内部で配列処理が出来る SUMPRODUCT関数を組み合わせるという方法が思いつきます。

SUMIFを外してSUMPRODUCTだけで完結する式にしたら、もっと短くなるんでは？と考えましたが

総計の行の UNIQUE('タイプ')　の中身が {1;2} と横ではなく 縦並び の配列となっているので、ここをTOROW関数で横方向に変換しなくてはいけない。

また、数量の行を $C:$C で取得するとタイトル行が文字列である為エラーとなるので2行目から取得する $C$2:$C とする必要がある。

ってことで、

=SUM('数量')/SUMPRODUCT(($A$2:$A=TOROW(UNIQUE('タイプ')))*$C$2:$C)

式が短くならず、やや複雑になってしまいました。

数式を組む部分は少し難しいかもしれませんが、計算フィールドを利用して グループ計に対する割合をピボットテーブルに入れることが出来ましたね。

Q2. ピボットテーブルで文字列を改行区切りで集計して当番表を作りたい

もう一つお題をいってみましょう。こちらの方が簡単です。

掃除当番のテーブルから ピボットテーブルで 右のようなクロス集計の当番表を生成してみましょう。

同じ日の同じ場所に2人以上の担当がいる場合は改行でそのセルに入れるものとします。

データは以下をコピペして、「掃除当番」というテーブルにして利用ください。

日付	場所	担当
02/03	トイレ	田中
02/03	トイレ	山田
02/03	お風呂	佐藤
02/04	お風呂	鈴木
02/04	キッチン	田中
02/04	キッチン	山田
02/05	お風呂	佐藤
02/05	お風呂	鈴木
02/05	トイレ	田中
02/06	キッチン	山田
02/06	キッチン	川端
02/06	トイレ	鈴木
02/07	お風呂	田中
02/07	キッチン	山田
02/07	お風呂	佐藤
02/08	キッチン	松尾

考えてみましょう！

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

A2. ピボットテーブルで文字列を改行区切りで集計して当番表を作りたい

回答です。

ピボットテーブルで範囲を「掃除当番」のテーブルとして、

行を　日付
列を　場所

とします。

最後に値に 計算フィールド（集計 カスタム）を入れ、

数式を

として セルの編集で「計算フィールド 1」を「当番表」に書き換えれば完成です。

TEXTJOIN関数で CHAR(10) を第1引数に指定することで、改行を区切り文字として挟んで配列を文字列にすることが出来ます。

あとは文字位置を調整して見栄えをよくすればOK。

こんな簡単に出来ちゃうのに、しっかり表へのデータの追加や編集に合わせてピボットテーブルも更新されます。

これ Googleフォームの回答テーブルと組み合わせれば、バイトのシフト希望を自動で集計するとか・・・色々面白いことが出来そうですよね！

【余談】当番表をシート関数を駆使して数式で行う場合

これをGoogleスプレッドシートで 一つの数式でやろうとすると、かなり難易度が高くなります。

幾つかやり方はありますが、一例として上のピボットテーブルの処理を踏襲した QUERY関数で集計表を作ってから中身をMAPで1つ1つFILTERしてTEXTJOINという方法だと

=LET(
  x,A:C,
  y,QUERY(x,"select Col1,count(Col1) where Col1 is not null group by Col1 pivot Col2"),
  ARRAYFORMULA(MAP(
    y,SEQUENCE(ROWS(y))*N(y)^0,SEQUENCE(1,COLUMNS(y))*N(y)^0,
    LAMBDA(v,r,c,
      IF(OR(r=1,c=1,v=""),v,
        TEXTJOIN(CHAR(10),TRUE,FILTER(INDEX(x,,3),INDEX(x,,1)=INDEX(y,r,1),INDEX(x,,2)=INDEX(y,1,c)))
      )
    )
  ))
)

こんな式になります。

機会があれば解説しますが、今回は割愛。

一方、最新版のExcelには GROUPBY関数、PIVOTBY関数というGoogleスプレッドシートのQUERY関数に対抗しうる最強集計関数が実装されており

さらにTRIMRANGE関数やトリム参照という データが入っていない不要な行・列を簡単に除外できる記述方法も登場しているので

今回の処理が

こんなシンプルな式で出来ちゃいます。

PIVOTBY関数 恐ろしい子・・・

これらのExcelの新関数は、現在は無料のWeb版Excelでも使えます。

無料のWeb版Excelの最近の進化スピードはGoogleスプレッドシート以上かもしれません。

正規表現のREGEX系関数含め、Googleスプレッドシートの関数との違いを早めに書かないとなーと思っております。

とりあえずGoogleスプレッドシートの場合は、現状では今回のような処理は ピボットテーブルを使うことをお勧めします。

ちなみに 逆の処理をやりたい（右のようなクロス表を左のリスト表形式にしたい）という場合、つまりアンピボットと言われる処理だと、Excelならパワークエリでやる方法が簡単ですが、Googleスプレッドシートの場合は数式でゴリゴリやる必要があります。

ピボットテーブルの「計算フィールド」完全理解

今回はGoogleスプレッドシートのピボットテーブルで「計算フィールド」を活用する方法を紹介しました。

あらためて

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

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

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

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

この4つのポイントをおさえて「計算フィールド」を完全理解すれば、一味違うピボット集計が出来ることがわかりましたね。

ピボットテーブル内での累計算出は 次回へ

今回は長くなってしまったので、Googleスプレッドシートのピボットテーブルで計算フィールドを使って累計を算出する方法は次回とします。

ただ、ここまで色々学んできた「計算フィールド」の理解と「シート関数」の知識を組み合わせれば

こんな感じで累計列を自力で作れると思います。

👇元の表のデータ

日付	支店	売上
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

次週解説までに興味のある方はサンプルの元データを置いておくのでチャレンジしてみてください！