googleスプレッドシートは非常に便利なので、仕事でもプライベートでも活用しているのだが、独自機能のqueryで返ってくる結果の扱いが難しいことがある。
今回しばらく悩んだのは、日曜・祝日以外は開けている施設の利用者数の平均を、平日と土曜に分けて取得したい、という問題。なお、各日のデータには「平日」「土曜」の区分があらかじめつけてある。
まずは次のような式を書いた。
=query([曜日と利用者数の入っているセル範囲],"select [曜日の列] sum([利用者数の列]) group by [曜日の列]")
これで、自動的に平日の利用者数の合計と、土曜の利用者数の合計が２行に分けて表示される。ここまではいい。問題はこの後である。
queryで得た平日の利用者数の合計を参照して、平日の開設日数の合計で割って平均を出そうとすると、なぜか返り値がゼロになるのである（苦）。ちなみに、関数バーのなかでは想定通りの計算結果が出る。なんだこれは。
そこでいくつかテストをしてみたり、他の人のtipsをネットで調べるなどしてみた。
その結果として、queryの結果として表示されているデータが数値として処理されないケースがあるということが分かった。ゼロ除算状態になってしまうので返り値ゼロということである。
これを回避するには、queryの結果自体を先に計算対象としてしまうという策が有効なので、式が増えてしまうのだが、以下のような書き方に変更した。
=sum(query([曜日と利用者数の入っているセル範囲],"select [利用者数の列] where [曜日の列] ='平日'"))
これで、平日の利用者数を合計した数値として返り値が出る。この値は別の場所で参照可能なので、別途平均が計算できるようになった。もちろん土曜日分の式は、上記の「平日」を「土曜」に置き換えたものを別のセルに入力しておく必要がある。
多分、最初のquery文を改良して一気に計算してしまうことも可能な気がするのだが、今のところそちらの方が面倒そうなのでやらないでおく。