Googleスプレッドシート QUERY関数 超応用例 1（まずは使ってみよう）
Googleスプレッドシートを代表する最強関数 QUERY関数について書きます。全何回になるのやらｗ
超応用例も登場しますが、基本も抑えた内容でいこうかなと。今回は基本的なことだけ触れているので、既に使いこなしている方には物足りないかもしれません。
このシリーズを読めばQUERY関数を完全理解できる（カモ）！
QUERY関数は長くなりそうなんで、単独で「【基本から裏技、超応用】どこよりも詳しい 超 QUERY関数」としてマガジンとしてまとめまています。
きっと、どこよりも詳しいはず！（役に立つとは言ってない）
先週の noteは GASのカスタム関数を作成する時のTipsについて書きました。
1. QUERY関数の概要
一応 QURY関数がどんなもので、なにが便利なのか？といった概要をまずは理解しましょう。
1-1. QUERY関数とは
QUERY関数とはExcelにはないGoogleスプレッドシート独自の関数で、グループ集計やピボット集計が出来る非常に強力な関数です。
データベースに対するクエリ（問い合わせ）のように、データの抽出、フィルタリング、集計、並べ替えなどを柔軟に行うことができます。
QUERY(データ, クエリ, [見出し])
QUERY関数には3つの引数があり
■データ
クエリを実行するセル範囲、または配列
例： A:Z、{シート1!A:D;シート2!A:D}、IMPORTRANGE("******","シート3!A:Z")
■クエリ
実行するクエリ構文。出力する列の指定、条件でフィルタ、並べ替え、集計などを指示できる。
例： select Col1、where Col2 = 'りんご’ order by Col1 asc sum(Col3) など
■見出し[省略可]
データの見出し行数の指定。省略時は自動判定。
例： 1、0、FALSE など
このようになっています。
一番ポイントとなるのが 第2引数の クエリで、通常のシート関数とは違う SQLチックな クエリ構文が独特すぎて苦手という人も多いようです。
1-2. QUERY関数を学ぶには
まずは公式ページを確認するのが一番です。
またクエリ言語について書かれたリファレンスは非常に参考になります
でも、公式読んで理解できる人ならこのnote不要ですよね。
mirのnoteでは公式の情報を嚙み砕いて説明し、さらに次回以降は公式では触れてないような特殊な使い方QUERY曲芸も紹介していきます。
1-3. QUERY関数の魅力
まず、QUERY関数を使うとピボットテーブルのようなグループ集計やピボット集計が簡単に出来ます。
ピボットテーブルが同じスプレッドシート内の1つのセル範囲を対象とした集計しか出来ないのに対して、IMPORTRANGE関数を組み合わせて 他のスプレッドシートを対象として集計したり、複数のシートの連結した配列を対象とした集計が出来るQUERY関数は非常に強力です。
また、QUERY関数はQUERY関数だけで 他の様々な関数を組み合わせた処理と同じようなことが出来るのも魅力です。
Googleスプレッドシート最強クラスの関数である FILTER関数、SORT関数、UNIQUE関数 、SUMIFS関数 、これらを組み合わせた処理が QUERY関数だけで実現できます。
さらにQUERY関数は他の関数では対応出来ない特殊な処理が可能です。これは超応用例で紹介していきます。
もちろんFILTERやSORTも凄い関数なんですが、他の関数とは設計思想が違うというか血筋が違うといえる QUERY関数は、異世界転生でチート付与された関数と言えるかもしれません。
1-4. QUERY関数はExcelでは使えない？
QUERY関数はExcelには存在しませんが、365版のExcelにはQUERY関数のような集計処理が出来る新関数が実装されました。
それが、GROUPBY関数、PIVOTBY関数です。
現在は無料のWeb版Excelでも使えるので、興味があるけど365を契約してないんだよなーって人はWeb版Excelで試してみましょう！
=PIVOTBY(A:.A,B:.B,C:.C,SUM)
PIVOTBY関数以外に トリム参照とイータ縮小ラムダを使用していますが、こんな短い記述で リストデータをクロス表にしてピボット集計することが出来ます。
同じ処理をする場合、Googleスプレッドシートの QUERY関数だと
=QUERY(A:C,"select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col2")
PIVOTBY関数に比べ記述がどうしても長くなってしまいますし、
なにより QUERY関数では対応できない総計行や小計行の出力が PIVOTBY関数、GROUPBY関数では可能となっています。
ただ PIVOTBY関数は引数が なんと11個もある複雑な関数でもあるんで、きちんと理解して使いこなすのはなかなか大変そうです。
総計行の表示など GROUPBY関数やPIVOTBY関数の方が優れてる点もありますが、一方で QUERY関数の方が優れている点もあります。
この辺りも次回以降触れていきたいと思います。
2. QUERY関数を利用する際の注意点
QUERY関数は非常に強力で便利な関数ですが、なんでもかんでもQUERY関数を使えばよいというわけではありません。
たとえば 上のように 第2引数も省略して =QUERY(A:C) と式を入れて、 =ARRAYFROMULA(A:C) と同じように使えると 紹介しているサイトもあります。
QUERY関数がスピルする（配列を返す）関数であるという特性を活かした、一番シンプルな使い方と言えるかもしれません。
しかし、これは理由は下で書いてますが、使うべきではありません。
2-1. QUERY関数は自動で異物を排除してしまう
QUERY関数はデータベースの考え方に基づいた関数です。通常データベースは列（カラム）毎に属性（データの型）が決まっています。
たとえば上の表だと A列の「名前」は文字列型ですね。で、B列の「予約日」は日付の型、C列の「予約数」は 数値型 となっています。
しかし、予約日の列に「未定」と文字列を入れたり、予約数の列に「2～5」と文字列を入れてしまうと、これは列の型に合ってない異物と見なされます。
=QUERY(A1:C10)
このようにQUERY関数を通すと、未定や 2～5といった 列の型にあっていないデータは存在を無視され空白として返ります。
これがQUERY関数の自動異物排除です。この仕様を知らず結構ハマる人がいます。
列ごとの型は データの割合から判断しているようです。
上のGIF動画では「予約数」という数値型の列に a,b,c と文字列のデータを増やしていきますが、 cまで入れた段階では 予約数の列は
数値 4 : 文字列 4 ですが、型の判定は数値のままで 文字列のセルは無視されて空白となっています。
dを入れて 数値 4 ：文字列 5 と文字列が多数派となった段階で QUERY関数は 予約数の列は文字列型という判断に切り替わります。
すると、数値は空白にはならず 文字列の数字として表示されるように変わります。
まとめると
■QUERY関数は 列単位のデータ割合から、その列の型を自動で定義する
■列の型に合わないデータがあった場合は異物として処理される
■数値や日付型の列に 文字列のデータがあった場合は空白に置き換わる
■文字列型の列に 数値データがあった場合は文字列としての数字となる
こんな仕様になっています。
これを知らずにQUERY関数を使ってしまうと、集計ミスが発生するので気を付けましょう。
基本的には 元のデータを列毎の型を意識した綺麗なデータとしておくことが大事です。
その際に便利なのが Googleスプレッドシートのテーブル機能。
テーブル化して列の型を指定しておくことで、
その型ではない値が入力されるととエラーが付きます。
ただ エラーは出るけど入力は出来ちゃうんですよね。。縛りが弱いのが残念。
2-2. QUERY関数の第1引数は自動で配列処理されない
どうしてもデータは綺麗に出来ず、元データの列の型に合わないデータが混在するのを許容して、そのままQUERY関数で処理したい。
こんな場合はどうすればよいか？
とりあえずの回避策としては、全てテキスト型にしてしまう方法があります。
Googleスプレッドシートには、セルに表示された値をそのまま文字列に変換する TO_TEXT関数という便利な関数があります。
こちらを使えば良いですね。
ただ
=QUERY(TO_TEXT(A1:C10))
このようにセル範囲に対して TO_TEXT関数を使おうとするとエラーが出ます。
これがQUERY関数の注意点2つ目です。
=QUERY(A:C) で結果が複数セルに結果が展開される（スピルする）ので、内部の配列処理も出来るもんだと勘違いする人がいますが、まったく別モノです。
FILTER関数やSORT関数は、式内は自動でARRAYFORMULAを付けた時と同じ配列処理効果が適用されましたが、QUERY関数の第1引数は配列処理効果はありません。
つまり
■QUERY関数の第1引数の範囲・配列に対して配列処理をする場合は ARRAYFORMULAを組み合わせる必要がある
ということです。
=ARRAYFORMULA(QUERY(TO_TEXT(A1:C10)))
とりあえず 異物と見なされたデータを含めてQUERY関数で出力する方法はわかりましたね。
2-3. QUERY関数は VLOOKUPやXLOOKUPの代わりではない
注意点の3つ目は他の関数との比較に関してです。
Excel時代から人気関数 VLOOKUP関数とQUERY関数を比較して、「VLOOKUPは古い！QUERY関数を使うべき！」みたいな煽りをたまに見かけますが、これは違和感があります。
検索で最初にヒットした1件だけを返すVLOOKUPに対して、QUERY関数は 基本的には条件に合致したデータを全て（複数ヒット時は複数件）返します。
どちらかと言えばFILTER関数に近い処理が出来る関数と言えます。
つまり考え方としては
こんな感じで キー（名前）が複数あって、それぞれの名前でテーブルを検索して最初に一致した行の対応するデータを取得したい！
こんな時は ARRAYFORMULA + VLOOKUP （またはXLOOKUP）
条件に一致したデータが複数あれば、それを全て取得したい。とか、複雑な条件や複数の条件でデータを取得したい！という場合は FILTER関数。
また先ほどのような列の型がそろっていないデータを条件でフィルタする場合も、QUERYのように勝手に異物排除で空白にされない FILTER関数を使うと良いです。
そして、絞り込みと合わせて order by 句で 並び替えを行いたい、見出し行も取得したい、group by や pivot 句で 集計したい。
こんな時は QUERY関数がよいとなります。
■やりたいこと、最終的にどのように結果を出力したいか？に合わせて QUERY関数を使うべきか、それ以外の関数を使った方が良いかを判断する
優劣ではなく、やりたいことに適した関数を選び上手に使い分けるのが大事ってことですね。
1. QUERY関数の自動異物排除に注意
2. QUERY関数内の配列処理にはARRAYFORMULAを組み合わせる
3. QUERY関数以外を使った方が便利なこともある。他の関数と使い分ける
QUERY関数を使う上での3つの注意点でした。
3. QUERY関数の基本の使い方ステップ
クエリ構文を詳しく掘り下げるのは次回からやるとして、初回である今回はサンプルデータを使って、QUERY関数の基本の使い方をざっくりと学んで終わりにしましょう。
こんなことをやりたい！って時にQUERY関数をどう使うか？が学べます。
タイプ 区分 数量
1 A 100
1 B 8
1 C 4
1 A 10
1 B 10
1 C 4
1 A 2
1 B 5
1 C 16
1 A 0
1 B 3
1 C 4
1 A 2
1 B 3
2 C 4
2 A 2
2 B 3
2 C 4
2 A 3
2 B 3
2 C 4
2 A 2
2 B 3
2 C 4
2 C 4👆こちらをコピーして A1 セルに貼り付けて利用してください。
対象範囲（第1引数）を A:CとしてQUERY関数を作っていきましょう。
3-1. selectで 出力する列を指定する
第1引数を A:C として その中のA列だけを出力したい場合は
=QUERY(A:C,"select A")
このように指定します。第2引数のクエリは 文字列とする必要があるので "ダブルクォートで括って記述します。
出力する列を指定するのは selectを使います。QUERYの句は 大文字、小文字を区別しないので SELECT でも Select でもOK。
また、指定方法は上のような A,Bといった列のアルファベットを指定する以外にも
=QUERY(A:C,"select Col1")
Col1,Col2と範囲内の列番号で指定する方法もあります。mir的にはコチラがおススメです。（理由は次回触れます）
ちなみに この Col1,Col2 の番号は 第1引数で指定した範囲内の一番左の列を1としてカウントした列番号です。
=QUERY(C:E,"select Col3") この式の場合、A列を1としてC列が3ではなく、Cを1列目として考えるのでCol3は E列を指します。
どちらを使うにせよ 列のアルファベットは大文字、Col1,Co2指定の場合は最初のCだけ大文字であとは小文字と、ここは大文字・小文字が厳格に区別されます。
この AやBといった列のアルファベットや Col1,Col2 といった列番号を 識別子というんですが、公式にも
このように書いてあります。
これを守らないとエラーになるで注意。
もし 2つ以上の列を指定したい場合は、カンマで区切って記述します。
=QUERY(A:C,"select Col1,Col3")
列を絞らずに全ての列を出力したいということであれば、
=QUERY(A:C,"select *")
"select *" としてもよいですし、select句を省略してしまうのがさらに簡単です。
select句については次回以降 掘り下げていく予定。
3-2. whereで 条件フィルタする
クエリの中でもっとも複雑なのが、条件を指定して合致した行だけに絞り込む where句です。
とりあえず今回は簡単な使い方だけ。
=QUERY(A:C,"where Col2 ='A'")
このように記述することで、列2が A のデータだけに絞り込むことが出来ます。
QUERY関数でテキストとの一致を条件とする場合は
='テキスト'
とシングルクォートでテキストを括ります。
また、イコールだけでなく 数値の列に対して
以下 <= 、 以上 >=
より大きい > 、 より小さい <
など比較演算子も利用可能です。
=QUERY(A:C,"where Col3 >5")
このように 列3が 5より大きいデータだけに絞り込む、といったことが出来ます。 ※数値は イコールの場合でも シングルクォートで括りません。
先ほどの select句と合わせると
=QUERY(A:C,"select Col1,Col2 where Col3 >5")
このように 出力するのは 列1、列2だけで 列3は出力せずにフィルタ条件にだけ使用する、といったことも出来ます。
select句とwhere句のように別の句を書いていく際は、 半角スペースを空けて記述していきます。またこれも次回以降触れますが、並び順もルールがあるので注意が必要です。
where句でもっともよく使うのが「空白ではない」という記述でしょう。
「空白ではない」は色々書き方がありますが、mir は
" is not null"
という記述をお勧めします。ちと長いですが、これが一番間違いありません。
これを使って A:Cと指定した時の空の行を排除し、データのある行だけを集計対象とすることが出来ます。
where句に関しても次回以降じっくり掘り下げていきます。
3-3. group by で グループ集計する
=QUERY(A:C,"select Col2,sum(Col3) where Col1 is not null group by Col2")
いよいよ集計に入りましょう。列2の区分ごとの 数量の合計を集計したい！QUERY関数が活きる場面ですね。
QUERY関数が無かったらB列をUNIQUEしてからTOCOLで空白除去、それを条件にARRAYFORMULAでSUMIFして左右連結みたいな面倒な式になります。
=ARRAYFORMULA(LET(x,TOCOL(UNIQUE(B:B),1),y,IF(x="区分","合計",SUMIF(B:B,x,C:C)),{x,y}))
グループ集計する際は group by を使うのですが、この時必ず select で グループ化する列 の指定、そして 集計する列に対して集計関数を指定する必要があります。
つまり
こういうことです。sum(Col3) 👈 この sum が集計関数となります。
group by で、区分がUNIQUE関数のように一意化され、それぞれの区分ごとの数量の合計が集計されます。
また group by した場合は、自動的に グループ化した項目の昇順に並び替えがされます。
3-4. order by で並び替える
この集計したデータを 数量が大きいものを上にするように並び替えたい！
これを実現するのが order by です。
=QUERY(A:C,"select Col2,sum(Col3) where Col1 is not null group by Col2 order by sum(Col3) desc")
並べ替えの指示は order by sum(Col3) desc
この部分なんですが、並べ替えのキーとする列は Col3 ではなく select で指定した sum(Col3) となります。
で 昇順、降順を
昇順（上が小 → 下が大） asc
降順（上が大 → 下が小） desc
このように asc か desc かで指定します。
これは割と簡単ですね。
3-5. label で見出しを修正する
並べ替えできたけど、見出しのこの 「sum 数量」ってところを「合計」って表示に出来ないの？って言われるかもしれません。
見出しの修正をするのは label です。
=QUERY(A:C,"select Col2,sum(Col3) where Col1 is not null group by Col2 order by sum(Col3) desc label sum(Col3) '合計'")
label sum(Col3) '合計'
👆 ここで selectした 列 sum(Col3) のラベル（見出し）を 合計にせよと指示しています。見出し名は ' シングルクォートで括ります。
見出しの修正が出来ました。
3-6. pivot でクロス表を生成する
区分ごとに集計したけど、やっぱりタイプも条件に加えてクロス表（ピボット集計）したいなー。
となったら、pivot の出番です。
いよいよ、QUERY関数らしくなってきました。
=QUERY(A:C,"select Col2,sum(Col3) where Col1 is not null group by Col2 pivot Col1")
3-4,3-5 で入れた order by と label は一旦削除して
pivot Col1
を一番最後に追記。
これだけでクロス表が完成です。
pivotで指定した Col1のタイプも 自動で ユニーク処理と並び替えがされます。
でも 左上のここがイマイチだなーと思ったら 先ほどの labelを使って
=QUERY(A:C,"select Col2,sum(Col3) where Col1 is not null group by Col2 pivot Col1 label Col2 '区分 \ タイプ'")
こんな感じで仕上げれば良いですね。
これが、よく使うQUERY関数の基本ステップとなります。
まずはここまで出来れば今回はOKです。
次回はQUERY関数のよりディープな領域へ
QUERY関数がどんなものか？なにができるか？を、まずはざっくりと理解できたでしょうか？
今回は mirのnoteにしては珍しく、基本的な内容だけで終わりましたｗ
次回からQUERY関数のディープな領域に入っていきます！
ただし、まだ mirの環境では反映されてませんが テーブル機能のアップデートもあったようなので、途中で違うネタ、旬のネタを挟みながらシリーズを書いていくことになるかと思います。
早く面白ネタ知りたい！って人も、じっくりお付き合いください。
