【GAS】QUERY関数を使って複数シートの情報を統合する

　いまいち具体例がイケていないんですが、いまAさん、Bさんの1年間の昼食、夕食のカテゴリ（和洋中）を元データとし、QUERY関数でデータを統合する例として、複数の同型シートを和結合したうえでピボットで集計というのをやってみたいと思います。

　QUERY関数の基本的な使い方はGoogle スプレッドシートからデータをSQLライクに取得してグラフを描くが参考になるかと思います。また類似の記事として複数の Google スプレッドシートのデータを集約するも挙げておきます（こちらはスクリプトを使用した例です）。

　具体例ですがスクリーンショットを見てもらった方が早いですね。こんな感じのシートが2枚（「Aさん」シート、「Bさん」シート）あるということです。

　

単純和結合

　単純に2枚のシートを縦に結合します。具体的には範囲を;（セミコロン）でつなぎ、全体を{}で包めばよいだけです。「A2:C」とC列は具体的な番号を指定せず、行の増加に対応できるようにしつつ、where句で空白行は除くという処理をやっています。これをしないと縦に結合はできるんですがテーブルとテーブルの間に大きな空白ができてしまいます。

　また、カラム指定は通常のA,B,Cといった具体的な列番号ではなく、Col1,Col2,Col3のようにします。なお、全カラム抽出の場合、select句は省略できます。

=QUERY({'Aさん'!A2:C;'Bさん'!A2:C},"where Col1 is not null")

Col1が文字列なら
where Col1 != ''
です。上記はCo1が数値の場合で使い分けに注意しましょう。

　

　範囲が固定されているのであれば、実はQUERY関数を使わなくても、下記のような書き方で結合できます。

={'Aさん'!A2:C367;'Bさん'!A2:C367}

セルフ結合

　同じテーブルを和結合することもできます。いま、Aさんの昼食と夕食を1列に並べるとするならば、以下のようにします。式は見やすくするために改行しています。

=QUERY({
QUERY('Aさん'!A2:C,"select A,B");
QUERY('Aさん'!A2:C,"select A,C")},
"where Col1 is not null order by Col1")

　セルフ結合と言いましたが、別の観点から見ると、範囲自体にQUERY式が入っており、一種のインラインビューのようなこともできるということが分かるでしょう。SQLでよくFROM句に他のテーブルを加工したもの持ってきたりしますよね。あれと同じです。なお、order by句で日付順に並べています。

　また、内側（結合前の範囲）のQUERY関数ではselect A,Bのように具体的な列番号をしていますが外側（結合後）のQUERYではCol1,Col2のようにする点にも注意しましょう。よくよく考えてみれば当然で結合後はそれがアルファベットの何列目かをそもそも定義することが困難です（シート1のA〜C列、シート2のD〜F列を結合する例を思い浮かべれば分かるでしょう）。

　結果はこうなります。

ピボット

　最後にピボットを織り交ぜてみます。ここでは縦軸にカテゴリ、横軸に月をとってやってみます。Aさん、Bさんの昼食と夕食を和結合し、月ごとに合計何食になっているかどうかを集計します。

=QUERY({
QUERY('Aさん'!A2:C,"select 'Aさん',A,B");
QUERY('Aさん'!A2:C,"select 'Aさん',A,C");
QUERY('Bさん'!A2:C,"select 'Bさん',A,B");
QUERY('Bさん'!A2:C,"select 'Bさん',A,C")},
"select Col3,count(Col1) 
where Col1 is not null and Col2 is not null 
group by Col3 
pivot month(Col2)+1")

　結果はこうなります。12ヶ月ありますが、横に長すぎなのでスクリーンショットでは6月まで収めています。

　注意したいのは横軸に１，２，３・・・と月が出力されていますがこれは「文字列」だということです。えー？っと思うかもしれませんがこれを知らないと後々この表をもとにHLOOKUPなどでこの表から特定の値なり範囲なりを取得するときにハマるかもしれません（かくいう私も2，3回同じことでハマりました）。

他のスプレッドシートのシートも統合できる

　IMPORTRANGE関数を混ぜるとできます。すなわち、下記のような形でできるということです。具体例は割愛します（すみません、正直な話、ちょっと力尽きてきました）。

=QUERY({
QUERY(IMPORTRANGE("スプレッドシートキー",範囲),"select文");
QUERY(IMPORTRANGE("スプレッドシートキー",範囲),"select文")
},"select文")

余談

　まだまだQUERY関数は応用範囲が広く、ほんの一部しか紹介できませんでした。本当にキリがないので。ところで私がGASに興味を持つきっかけとなったのも実はこのQUERY関数で、特に今回紹介した結合やピボットを知って「シート上でこんなSQLライクなことができるのか！」と感動したものです。

結局アマゾン電子書籍化（Kindle出版）しました

　というわけで遂に「QUERY関数：最強の関数を使いこなそう！」というタイトルでKindle出版しました。QUERY関数を自分なりに体系的にまとめてみました。ここに投稿してから2年半があっという間に過ぎていますがその間いろいろ熟成してきたのでまとめてみたといったところです。まあその間も出したいなあとは思っていたのですけど。。