[Spreadsheet] QUERY関数のピボットテーブルで空値を０にしたい場合はGASのカスタム関数が手っ取り早い #GoogleAppsScript

はじめに

例えば下記のようなテーブルがあり、QUERY関数によりピボットテーブルを生成すると、該当する値がない場合、セル表示はデフォルトで空値となります。

年月	日	店舗	売上
2022/01	5	新宿本店	234.52
2022/01	5	品川店	316.29
2022/01	9	新宿本店	533.2
2022/01	12	渋谷店	189.74
2022/01	12	新宿本店	308.33
2022/01	13	新宿本店	941.7
2022/01	15	秋葉原店	531.02
2022/01	15	新宿本店	738.31
2022/01	19	新宿本店	882.17
2022/01	25	新宿本店	648.42
2022/02	9	渋谷店	617.2
2022/02	27	渋谷店	756.89
2022/02	3	新宿本店	474.67
2022/02	4	渋谷店	399.78
2022/02	7	秋葉原店	327.17
2022/02	18	品川店	933.41
2022/02	23	新宿本店	747.81
2022/02	25	新宿本店	957.23
2022/02	29	新宿西店	882.51
2022/03	2	品川店	107.06
2022/03	5	新宿本店	928.45
2022/03	7	品川店	436.88
2022/03	13	新宿本店	768.51
2022/03	14	新宿本店	392.35
2022/03	15	秋葉原店	939.15
2022/03	17	新宿本店	672.48
2022/03	18	渋谷店	796.45
2022/03	27	新宿本店	577.32
2022/03	28	秋葉原店	486.62
2022/03	28	新宿西店	748.6
2022/04	9	新宿本店	641.63
2022/04	11	渋谷店	484.6
2022/04	18	新宿本店	379.32
2022/04	19	渋谷店	229.46
2022/04	19	渋谷店	683.45
2022/04	20	秋葉原店	483.97
2022/04	21	新宿西店	172.66
2022/05	2	渋谷店	762.27
2022/05	2	新宿本店	829.09
2022/05	3	新宿西店	408.55
2022/05	13	渋谷店	405.12
2022/05	14	新宿本店	281.38
2022/05	20	新宿西店	251.13
2022/05	23	新宿本店	744.74
2022/05	25	新宿本店	170.67
2022/05	28	品川店	447.55
2022/05	28	新宿本店	707.69

上記テーブルに対して、店舗ごとの月次の売上集計を行いたいため、以下のようなQUERY関数を実行します。

数式サンプル01

=QUERY(A1:D,"SELECT C,SUM(D) WHERE A IS NOT NULL GROUP BY C PIVOT MONTH(A)+1 LABEL SUM(D) '月' FORMAT SUM(D) '#,##0.00'")

以下のような表示結果となります。

店舗	1月	2月	3月	4月	5月
品川店	316.29	933.41	543.94		447.55
新宿本店	4,286.65	2179.71	3,339.11	1,020.95	2,733.57
新宿西店		882.51	748.6	172.66	659.68
渋谷店	189.74	1,773.87	796.45	1,397.51	1,167.39
秋葉原店	531.02	327.17	1,425.77	483.97

表示されたPIVOTテーブルの空値の部分を0に置き換えたい場合、QUERY関数のオプション¹やSQLライクなクエリ言語では対応できません²。Excelではアプリケーションの設定変更で対応可能なようですが、スプレッドシートの場合、設定で何とかするということもできないようです。

関数の組み合わせで実現している例

関数の組み合わせで実現している例もありますが、正直なところ、すぐには数式の内容を解釈することができませんでした。

基本の手順

サイトで紹介されている基本の手順を要約すると、ヘッダー行表示とデータ部分表示のQUERY文を分割し、配列書式｛｝を使用して連結表示させるというもので、基本構造としては以下のようになります。なお、便宜上インデントとコメントを記述しています。

数式の基本構造

={
 ' ヘッダー行のみ抽出するQUERY
 QUERY(範囲,"PIVOTテーブルを生成しヘッダー行のみ表示するクエリ");
 ' ２行目以降を表示するQUERY
 ARRAYFORMULA(N(QUERY(QUERY(PIVOTテーブル生成クエリ結果,"「範囲」の全表示からヘッダー行を非表示にするクエリ" )))
}

ただし、上記は２行目以降のデータセルすべてがN()にて数値に置き換えられるため、文字列を含むセル、具体的には「店舗」列が0になります。

1月	2月	3月	4月	5月
316.29	933.41	543.94	0	447.55
4,286.65	2179.71	3,339.11	1,020.95	2,733.57
0	882.51	748.6	172.66	659.68
189.74	1,773.87	796.45	1,397.51	1,167.39
531.02	327.17	1,425.77	483.97	0

文字列カラムへの対応

上記の回避策として、２行目以降の表示方法に工夫が必要になります。こちらも要約すれば、文字列が表示されるカラム、数値が表示されるカラムを分けて連結表示したものをさらに別で抽出したヘッダー行部分と連結表示させるということになります。

以下、サンプルデータを希望通りの表示にするため、サイト情報を参考にして修正した数式サンプルになります。注意点として、２行目以降の表示では、ColN表示にする必要があることと、列方向への連結なので、,で区切ります。

また、文字列カラムが２列になるなど、表示イメージによってはSEQUENCE()の引数を修正するなどの手直しが必要になります。

数式サンプル02

' 店舗ごとの月次合計表示
={
' ヘッダー行の表示
 QUERY(A1:D,"SELECT C,SUM(D) WHERE A IS NOT NULL GROUP BY C PIVOT MONTH(A)+1 LIMIT 0 LABEL SUM(D) '月'");
' 店舗列の表示
 QUERY(
  QUERY(A1:D,"SELECT C,SUM(D) WHERE A IS NOT NULL GROUP BY C PIVOT MONTH(A)+1"),"SELECT Col1 OFFSET 1",0
 ),
' ２列目以降の表示
 ARRAYFORMULA(
  N(
   QUERY(
    QUERY(A1:D,"SELECT C,SUM(D) WHERE A IS NOT NULL GROUP BY C PIVOT MONTH(A)+1 LABEL SUM(D) '月' FORMAT SUM(D) '#,##0.00'"
   ),"SELECT "&TEXTJOIN(",",TRUE,"Col"&SEQUENCE(1,COUNTUNIQUE(C2:C),2))&" OFFSET 1",0)
  )
 )
}

店舗	1月	2月	3月	4月	5月
品川店	316.29	933.41	543.94	0	447.55
新宿本店	4,286.65	2179.71	3,339.11	1,020.95	2,733.57
新宿西店	0	882.51	748.6	172.66	659.68
渋谷店	189.74	1,773.87	796.45	1,397.51	1,167.39
秋葉原店	531.02	327.17	1,425.77	483.97	0

筆者曰く、

Even though the formula seems complex, it’s simple to apply.

とのことですが、構造が理解できればシンプルに思えるのかもしれません。似たようなQUERY文を複数回記述する必要があるため、スマートさには欠けるかな、という印象です。どうしても関数のみで実装したいとか、しなければならない場合にはお試しください。

GASのカスタム関数を利用する

前置きが非常に長くなりましたが、本題です。そして短いです。GASにて、例えばNULLTOZEROという名前の関数を作成したという前提で、通常通りPIVOTテーブルを生成するQUERY文を記述します。=QUERY()
次にNULLTOZEROで先のQUERY文をラップします。=NULLTOZERO(QUERY())
空値を0にしたPIVOTテーブルが展開されます。

Sample.gs

// QUERY関数の結果を引数に取り、空値を０に置換して返す
// @param {array} arr array[][]
// @return {array} array[][]
const NULLTOZERO = arr => arr.map((row, i) => (i === 0)? row: row.map(col => !col? 0: col));

数式サンプル03

=NULLTOZERO(QUERY(A1:D,"SELECT C,SUM(D) WHERE A IS NOT NULL GROUP BY C PIVOT MONTH(A)+1 LABEL SUM(D) '月'"))

ヘッダー行は無視して空値のセルのみmapで置き換えるというシンプルなロジック構造であり、0ではなく任意の数値や文字列に置き換えるといったカスタマイズも容易です。

最後に

今回はQUERY関数の結果を引数に取るように設計しましたが、NULLTOZERO(A2:D)といった具合に集計対象範囲を引数として、GASにてPIVOTテーブルを生成して返す方法でも良いと思います。

この場合、QUERY関数よりも柔軟なテーブル内容の表示が可能となりますが、反面、PIVOTテーブルの構造変更時はGASを修正する必要があることと、内容によってコーディングボリュームが増え、ロジックも複雑になることがデメリットとして挙げられます。

いずれの場合でも共通事項として、主に初回実行時の「承認」作業が不要なため、気軽に設置できますが、実行時間は３０秒以内という制限があります。また、Loading...状態のままで処理が実行されなかったりすることもあります³。

故に、私見ではありますが、特にデータボリュームが大きいときなどは、GASで全工程を処理するよりも、QUERYの結果を引数として処理したほうが効率が良いと思います。

JavascriptやGASに慣れているから、ということもありますが、QUERY関数のピボットテーブルで空値を０にしたい場合はGASのカスタム関数が手っ取り早いと思います。

[参考]

そもそもそのようなオプションはありません ↩
ISNULL()関数などが該当すると思いますが、非対応となっています ↩
概ねリロードで解消します ↩