GoogleスプレッドシートのQUERY関数が面白い

つい先週までは冷房を付けていたのですが、急に冷え込んで冬の到来を感じています。そろそろ湯たんぽや暖房のお世話になりたい季節です。

さて、昨日の社内勉強会で「ExcelやGoogleスプレッドシートのピボットテーブル機能が便利」という話題がありました。これに関連して、以前使ったときに便利だったQUERY関数について思い出したので、有名かもしれませんが今回はそのTipsです。

まず、次のような表がGoogleスプレッドシートにあります。どの会社のどの人が何円の何を買ったのか、みたいな購入記録です。シート名は「購入記録テーブル」としています。

このデータから、各会社に対してまとめて請求するための請求書を作りたいとします。請求書はこのような見た目です。

請求金額を表示するだけであればSUMIFで合計金額を計算すれば佳いのですが、ここでは明細表を表示したいのがポイントです。

これをQUERY関数で実装します。明細表の左上、セルB13に次の関数を入力します。

=QUERY(‘購入記録テーブル’!A:F, “SELECT A,C,D,F,E WHERE B='” & B4 & “‘”)

第2引数の”SELECT …”が肝です。このようにSQLっぽい文法は「購入記録テーブルのうちB列が”ここに会社名を入れる”と一致する行について、A,C,D,F,E列を表示」という指定です。クエリ式が文字列でないといけないので、セルB4の参照は文字列結合で記述しています。

ここで、セルB4に実際の会社名を入れてみると、その会社名と「購入記録テーブル」のB列（所属カラム）が一致する行のみを表示してくれます。

ちなみに例として分かりづらいですが、購入記録テーブルで何もないF列を含めているのは、請求書での見た目の都合です。請求書の明細表で品目が表示に2列分（D列とE列）を使っていて、”SELECT A,C,D,E”だと請求書のE列に金額が表示されてしまうため、品目と金額の間に空欄のF列を表示するようにしています。

SQLっぽいクエリ式から分かるように、より複雑なクエリも記述可能です。さらに、IMPORTRANGE関数を併用すると、別のスプレッドシートファイルを参照することもできます。

クエリを文字列で記述しなくてはならない点やエラーが分かりづらい点などの使いづらさはありますが、応用次第でいろいろなことが可能です。例えば、購入日が10月のみの請求書を作成したり、他のデータと請求書Noを連携して参照するようにしたりできそうです。

以上、QUERY関数の簡単な例と紹介でした。

たまにはバックエンドエンジニアとしてSQLと格闘する日々を忘れ、スプレッドシート職人としてクエリを書くのも気分転換に良いかもしれません。