[Googleスプレッドシート]QUERYで空白行を含めて集計する・SQLのCOALESCEっぽく置き換えする方法

今回は無理やりネタです。

  1. セルの値を条件に、データをQUERYで集計
  2. 集計した値をさらに集計する

というケースで、QUERYの結果が空の場合に、#N/Aが返ってきて表全体が#N/Aになってしまう悲劇を防ぎます。

具体例

社員の販売表があるとします。

1行目は「hogeさんがAという商品を10個売った」という意味ですね。

上司から、「これを集計してね」と言われます。
「社員名簿があるから、その右側にくっつけて」と。

社員名簿

期待される表その1

上司
「あ、さらに商品別売上と社員別売上も出しておいて」

期待される表その2

※この期待される表は、結果をソートしたり、いじったりがしにくい構造になっており、正直あまり良くない表の組み方です。
基本的には、このような表を作らないようにしましょう。

とはいえ仕事をしているとそうもいかないことがありえます。そんな時、今回の方法を試してみてください。

QUERYで、行ラベル x 列ラベルを条件に集計

QUERYを使ってやってみます。

=QUERY($A$5:$C,"SELECT SUM(C) WHERE A = '"&$E5&"' AND B = '"&G$4&"' label SUM(C) ''")


※商品別売上、社員別売上はそれぞれ行/列をSUM()しているだけです。

一部正しく取れています。
とはいえ、売上表に含まれていない、QUERYが空白を返す箇所では#N/Aが返ってきてしまいます。
それにより、商品別売上も社員別売上も#N/Aになってしまいます。

解決策 : IFERROR()を使って空の時の初期値を設定する

=IFERROR(QUERY($A$5:$C,"SELECT SUM(C) WHERE A = '"&$E5&"' AND B = '"&G$4&"' label SUM(C) ''"),0)

うまくいきました。
IFERROR()は、IFERRORの中の値がエラーの時、任意の値で置き換える関数です。

今回だと、QUERY関数の結果が空白で、#N/Aとなる場合は、0で置き換えています。

SQLのCOALESCE(最初に見つかったNULLではない値を表示する機能。これを使えばデータが存在しない場合は初期値で置き換えることができる)は、Googleスプレッドシートでは使えないようですが、IFERRORを駆使してそれっぽいことをやってみました。

別の解決策 : SUMIFS()を使う

身も蓋もないですが今回のケースは無理にQUERYを使うよりもSUMIFSの方が楽かもしれません。

以上です。

参考

開発COALESCE, Google Spreadsheet, Googleスプレッドシート, QUERY, SUMIFS