Help us understand the problem. What is going on with this article?

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

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

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

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

 スクリーンショット 2016-10-05 23.10.27.png

単純和結合

 単純に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列を結合する例を思い浮かべれば分かるでしょう)。

 結果はこうなります。

スクリーンショット 2016-10-05 23.26.43.png

ピボット

 最後にピボットを織り交ぜてみます。ここでは縦軸にカテゴリ、横軸に月をとってやってみます。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月まで収めています。

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

スクリーンショット 2016-10-05 23.33.19.png

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

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

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

余談

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

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした