Googleスプレッドシート QUERY関数 超応用例 7（where句 徹底解析1）

Googleスプレッドシートの最強関数 QUERY関数について書いたnoteの第7回です。

👇これまでのQUERY関数シリーズは マガジンにまとめています。

前回はQUERY関数から少し離れて、PC版Googleドライブの導入方法、推奨設定、Googleドライブ内のExcelをExcelとして開く方法を紹介しました。

QUERY関数 where句 の基本

今回は QUERY関数の 隠しアイテム skippingを含めた 全11の言語句の2番目、where句を取り上げます。

$$\begin{array}{lll} \text{No}&\text{句}&\text{使用量}\\ \hline \text{1}&\text{select}&\text{出力する列を指定 （類似関数 CHOOSECOLS）}\\ \hline \text{\textbf2}&\text{\textbf{where}}&\text{\textbf{条件でデータをフィルタ （類似関数 FILTER）}}\\ \hline \text{3}&\text{group by}&\text{行方向（縦）にグループ化（類似関数 UNIQUE）}\\ \hline \text{4}&\text{pivot}&\text{列方向（横）にグループ化（類似関数 UNIQUE）}\\ \hline \text{5}&\text{order by}&\text{データを並び替え（類似関数 SORT）}\\ \hline \text{6}&\text{skipping}&\text{〇行おきにデータを出力する}\\ \hline \text{7}&\text{limit}&\text{〇行目までデータを出力}\\ \hline \text{8}&\text{offset}&\text{〇行目からデータを出力}\\ \hline \text{9}&\text{label}&\text{列のラベル名を変更する}\\ \hline \text{10}&\text{format}&\text{特定の列の値を書式設定}\\ \hline \text{11}&\text{options}&\text{追加オプションを設定。（使わない）}\\ \hline \end{array}$$

QUERY関数において、もっとも重要な句と言える where句の基本をしっかり理解しましょう！

where句内の記述は適切にスペースを入れよう

QUERY関数の第2引数 クエリ文の where句は、対象の列 や 条件を指定して、比較演算式を満たす（TRUEとなる） 行だけを抽出します。

たとえば、上の画像は

という式が入っていますが、これは

2列目（区分）が B という文字列と一致する 行だけを抽出せよ という指示になります。

ちなみに where句内のスペースは見やすくする為のもので、

このように Col2='B'の部分は スペース無しで詰めても問題ありません。

ただ、where やこの後登場する 高度な比較演算子オプションの contains、 like などの英単語は、必ず 前後にスペースを入れて区切る必要があります。

whereCol2='B'　とwhereの後ろにスペースを入れず詰めてしまうと、

QUERY関数がエラーを返します。

where句内の記述は 適切にスペースを入れる

と覚えた方がよいでしょう。

ただし注意点として

こようにシングルクォート内では、 ' B '  と余計なスペースは入れてはいけません。

シングルクォートで括った文字列は表記そのままで扱われる為、この式は 列2で　(半角スペース)B(半角スペース)　と一致するセルを探してしまうからです。

シングルクォートの中では余計なスペースは入れない

これも覚えておきましょう！

where句の役割は FILTER関数

QUERY関数のwhere句を使った式は、たいていは FILTER関数で置き換えることが出来ます。

たとえば 👆 この式だったら

となります。

QUERY関数の where句は FILTER関数と同じ役割 と言えます。

ただし、FILTER関数では 見出し行を自動で出力することは出来ません。

データ部分をフィルタしつつ、見出し付きで結果を返せるのは QUERY関数の利点です。

もちろんQUERY関数ではなく、FILTER関数を使った方がよいケースも多々あります。それらはFILTER関数の回で触れています。

where句の条件は 型を意識。文字列はシングルクォートで括る

上で登場したQUERY関数のwhere句の例

👆この式ですが、条件である B を '（シングルクォート）で括っています。

これをキチンと理解せず、なんとなく「where句は 条件をシングルクォートで括るもの」と誤って覚えてしまうと

こんな式を書いてしまい「あれ 3列目の数量が10のデータは存在するのにQUERY関数で抽出できない」となります。

👆上の式が正しい結果を返さないのは、条件の10 を '10' とシングルクォートで括ったせいで、数値型である3列目の中から 10という数字文字列と一致する行を探している為です。

これは当然見つからないので、結果は見出しのみとなります。

where句の条件は 文字列の時は  '（シングルクォート）で括る。数値やブール値（TRUE,FALSE）の場合は 括らない

というルールになっています。これを覚えておきましょう。

数値の時は

このようにシングルクォートで括らずにそのまま記述します。

同じく チェックボックスでチェックが付いてる行（データ）だけをQUERY関数で抽出したい場合は

チェックボックスは TRUE、FALSEを返す ブール値型なので、'TRUE' とシングルクォートで括るのではなく、

こちらも  TRUE とそのまま記述します。

ちなみにブール値の場合は 大文字、小文字を気にする必要なく true でも OK。

日付、日時、時刻 型のケースは特殊なので、今回は触れず別の回でまとめます。

また where句の条件は シート関数の =一致よりも格段に厳密です。文字列はアルファベットの大文字、小文字を区別するので注意しましょう。

where句で使う列は select句で出力対象にしなくてもOK

チェックボックスの where句の事例ですが、「D列（4列目）のチェックボックスは、抽出する条件には使いたいけど 結果として出力したくないんだけど」ってケースもあるあるです。

where句で使う列は select句の出力対象にしなくてもOK

このように Col4は selectの対象とはせず、where句でのみ利用するといったことが可能です。

ただし 複数の句を使う場合は 並び順のルールに注意です。必ず select句が先、where句が後とする必要があります。（省略時は除く）

クエリ文は 言語句の順番が大事

where句で使えるオプション

ここまでは = を使って一致を判定する例を紹介してきましたが、where句では条件を記述する様々なオプションが利用可能です。

今回はとりあえず、使える演算子の一覧を紹介しておきます。

✅基本の比較演算子
スプレッドシート上でも使える 等合・不等号に加え、プログラミングのような != による「一致しない」 も使えます。それ以外は特に問題ないですね。

$$\begin{array}{ll} \text{比較演算子}&\text{解説}\\ \hline \text{<=}&\text{〇〇以下}\\ \hline \text{ <}&\text{〇〇より小さい}\\ \hline \text{ >}&\text{〇〇より大きい}\\ \hline \text{ >=}&\text{〇〇以上}\\ \hline \text{ =}&\text{〇〇と一致する}\\ \hline \text{ !=}&\text{〇〇と一致しない}\\ \hline \text{ <>}&\text{〇〇と一致しない}\\ \hline \end{array}$$

✅複雑な文字列比較演算子
「含む」や前方一致、後方一致、ワイルドカードを利用した一致、さらに正規表現を利用した一致判定が可能です。

$$\begin{array}{ll} \text{複雑な文字列比較演算子}&\text{解説}\\ \hline \text{contains}&\text{〇〇を含む}\\ \hline \text{starts with}&\text{〇〇から始まる}\\ \hline \text{ends with}&\text{〇〇で終わる}\\ \hline \text{like}&\text{ ワイルドカードを使った一致判定}\\ \hline \text{matches}&\text{正規表現を使った一致判定}\\ \hline \end{array}$$

✅論理演算子
and、or で 複数条件を組み合わせることができます。
さらに notで 条件を満たさない とすることも可能。

$$\begin{array}{ll} \text{論理演算子}&\text{解説}\\ \hline \text{and}&\text{条件1、条件2をどちらも満たす}\\ \hline \text{or}&\text{条件1、条件2のどちらかを満たす}\\ \hline \text{not}&\text{条件1を満たさない}\\ \hline \end{array}$$

これらに加え、複数条件の時は (　) カッコで 条件を括ることで、 AND、ORといった論理演算子を優先順位付けすることが可能です。

個々の利用例や 複雑な文字列比較演算子の活用方法、where句で複数条件を組み合わせる応用例は、次回紹介とします。

where句で空白データを除外する

QUERY関数の where句でもっとも利用するのが 「空白ではない」データのみを抽出する（空白を除外する）という処理です。

👆画像はわかりやすくする為、途中に 1列目が空白のデータを挟んでいますが

where句の空白除外は、QUERY関数の 第1引数は A:C や A2:D など お尻を決めない範囲指定として データの増減に対応させることが多いので、👆の画像のような 下の方のデータの入っていない行を結果から除外するといった使い方が一般的です。

最新のExcelだったら、TRIMRANG関数や トリム参照を使うケースですね。

この QUERY関数の where句による 空白除外はちょっと特殊で、上で登場した様々な演算子のオプションではなく、判定する列を指定して

このように is not null と記述します。

実は オプションの基本の比較演算子を使った 「一致しない」とする

や一致するを否定する

という記述で空白除外をしようとすると

このように正しく空白ではない データを出力することが出来ません。

これは 空白という条件を '' と シングルクォート2つで記述してしまうと、上に書いた通り

シングルクォートで括る → 文字列型　

というルールで、数値型の列で利用できない条件となってしまう為です。

つまり、これらの 空白を '' で表した条件記述は

👆 Col2 つまり文字列型の列を対象とすれば、正しく機能するってことです。

文字列型の時しか使えないのはちょっと困りますよね。

というわけで QUERY関数の空白除外は　 is not null　で覚えてしまいましょう。

「どこかの列にデータが入ってるかもしれない」といった、列の指定が難しいケースは QUERY関数の where句を使った空白除外には むいてません。

必ず対象とする列を指定する必要があります。

また利用機会は少ないですが、逆に空白であるを条件としたい場合は

このように is null と記述します。

where句は なにが扱えるか？

なんとなく where句は

このように　前に 列指定がきて、比較演算子を挟んで 後ろに条件となる 値 がくるイメージじゃないでしょうか？

実は、そんなことはありません。

where句は もっと自由だ

where句の 比較演算子の 前後は、どちらも

・A,B,C または Col1,Col2,Col3 といった列指定
・算術演算子で加工した列
・スカラー関数で加工した列
・単体の値

これらが使えます。

また、いずれも セル参照を使ったり、シート関数を組み合わせた数式で生成することが可能です。

一方、where句で使えないものとしては

・集計関数で加工した列
・配列

があります。

詳しく見ていきましょう。

where句は 列同士も比較できる

通常は 列2が Bであるを条件として記述する場合は、
 where Col2 = 'B'　としますが、これを

このように逆に書いても 同じ結果となります。

まぁ =A1=B1 と =B1=A1 は同じなんだから、言われてみれば当然って感じですよね。

さらに 前後両方を列指定とした

このような式で、列2と列3が一致する行のみに絞り込む といった条件記述が可能です。

どちらも空白の No 12の列も抽出されてますね。

比較演算子の 前後をどちらも 列とした場合は、行単位で それぞれの列の値を比較していく、配列処理のイメージです。

where句とスカラー関数を組み合わせて 大文字・小文字を区別しない判定 

上でも書きましたが  where Col2 = 'B' とした時、2列目が b と小文字になっている行は B とは区別され 対象外となります。

👆 だと No3の行は  2列目が b なので、

=QUERY(A1:C15,"where Col2 = 'B'")

では大文字・小文字が区別されて抽出まれませんが、

=FILTER(A1:C15,B1:B15="B")

だと大文字・小文字を区別せず抽出されているのがわかりますね。

上でも書きましたがシート関数の = イコール一致はやや甘めですが、QUERY関数の クエリ文の中の = イコール一致は厳密なのです。

もし 「QUERY関数の where句でも 大文字・小文字を区別せずに条件判定して抽出したい！」という場合は、

select句でも登場した スカラー関数 の UPPER または LOWER を組み合わせましょう。

upper関数は 指定した列のアルファベットを全て大文字に揃える関数なので、

このように書くことで、Col2のアルファベットを大文字にした変換した列と B が一致するか？という条件となり、大文字・小文字を区別せず b の No3の行も含めて抽出することが出来ます。

同様に

=QUERY(A1:C15,"where Col2 = Col3")

では大文字・小文字の違いで抽出できなかった No 7,10,13 の行が

=QUERY(A1:C15,"where upper(Col2) = upper(Col3)")

このように クエリ文で使える upper関数を使い、どちらも 大文字に揃えて比較することで、大文字・小文字を区別せず 2列目と3列目が一致する行を全て抽出できます。

where句で算術演算子を使った判定 【ミニお題 1】

where句では、selet句と同じように数値型の列に対して + - * / といった算術演算子を組み合わせて加工した列を利用できます。

ここは簡単なお題形式で理解を深めましょう。

QUERY関数は、基本はデータベース型の表（リスト表）に使う関数ですが、画像のような クロス集計表に対して使うことも出来ます。

この表から以下をQUERY関数で出力する、というお題にチャレンジしてみましょう。

👇データはコチラ（A1セルに貼って利用）

なまえ	第1回テスト	第2回テスト
田中	80	95
山田	90	85
鈴木	100	100
佐藤	60	65
高橋	50	70
伊藤	85	95

なお、第1引数はA1:C7として、 出力するデータは select句は使わず 丸ごととし、テストは全員が 2回受けているものとします。

考えてみましょう！

↓↓
回答はここから。
↓↓

【ミニお題 回答】

出来ましたか？

④は平均といえば AVERAGE関数、または集計関数の avgを使いたいところですが 集計関数は基本的に「列」に使うものですし、AVERAGE関数も「行毎」とする為には BYROW関数を組み合わせる必要があり、手数がかかります。というわけで、

(Col2+Col3)/2　

この程度の平均値計算なら、QUERY関数内で演算子で計算しちゃった方が楽です。（先に足し算をするのでカッコを忘れずに！）

where句で単体の値は両方に入れることもできる

これは当たり前だと思うかもしれませんが、where句では 単体の値を

 where Col2 = 'B'　・・・　後ろが単体の値
 where 'B' = Col2　・・・　前が単体の値

どちらか片方で使うだけではなく、

👆両方に値を使うことも出来ます。

これは当然 文字B と 文字Bは一致するので、第1引数のデータを一切フィルタせずそのまま全て返すことになります。

同じく

'B' = 'C' とすれば、これは一致しないので データ部分は一つも抽出されず見出しのみが返ります。

普通はこんな使い方はしませんが、

select * のように where 1 = 1 で全データ出力 となることを知っておくと、どこかで応用できるかもしれません。

where句の 条件に使うテキストをセル参照とする

QUERY関数のwhere句において、条件となる単体の値（テキスト）をセル参照にしたい！というケースはよくあります。

👆のようにプルダウンを組み合わせると非常に便利ですね。

ここでややこしいのが、where の条件で文字列をセル参照で利用するケース。

H1セルをwhere句で使う 条件の単体文字列としてセル参照する場合、

こんな式になります。

クォート部分が理解しづらいかもしれませんが、これは

このように 3つのパートに分解して ＆で連結している状態です。

わかりますかね？

さらにややこしいのは先の「基本」の部分でも書きましたが、where句で対象とする列が文字列型か数値かによって記述が変わってくる点です。

文字列型の場合は

このように シングルクォートでセル参照部分を括るように式を作る必要がありますが、

1列目のNoのような 数値型の列を対象とする場合は、

シングルクォートは不要なので

このような記述になります。

H1の後ろになにか付けたくなりますが、後ろに続くクエリ文がなければこれだけです。

この where句の 条件部分を セル参照とするやり方は、QUERY関数を使う上で非常に重要なポイントです。

文字列型、数値型それぞれの記述を理解しましょう。（さらに 日付や日時、時間も書き方のお作法があるので非常にややこしいのです。）

where句で 条件をセル参照とした時に セルが空白だったら全データを返したい【ミニお題 2】

では、ここでもう一つミニお題をいってみましょう。

この式は H1のプルダウンが未選択（空白）の場合、クエリ文は

となり、イコールの後ろに何もないので、エラーを返します。

これを回避して 条件となる H1セルが空白の場合は フィルタせず全てのデータをそのまま返すとしたい場合、どのように式をアレンジしたらよいでしょうか？

where句の単体の値は セル参照だけでなく、シート関数を組み合わせた数式で生成できることを利用した問題です。

データは 👇こちらを A1にコピペして活用ください。

No	区分1	区分2
1	A	A
2	a	C
3	b	D
4	B	B
5	c	D
6	C	D
7	d	D
8	D	D
9	A	B
10	B	b
11	A	B
12		
13	B	b
14	C	C

ただし、　=QUERY(A1:C15,"where Col1 = " &　式のここまでは、変更せずそのまま利用するものとします。

考えてみましょう！

※簡単だという人は  =QUERY(A1:C15,"where Col2 = '" & H1 & "' " ) こちらの文字列型の2列目を条件対象とする式の場合も同様に H1セルが空の時に全データを返す式を考えてみましょう。

↓↓
回答はここから。
↓↓

【ミニお題 回答】

とりあえず IFで分岐させる方法は思いつくんじゃないでしょうか？

ただ H1="" の 空白の時、じゃあ何を返せばよいか？で止まってしまった人もいるのでは？

実はここは  "Col1" という文字列を返すことで解決できます。

この時 クエリ句は

"where Col1 = Col1"

となり、これは全ての行が一致するので 全データ出力となるわけです。

オマケお題の

=QUERY(A1:C15,"where Col2 = '" & H1 & "' " )

の方ですが、文字列型の列の場合は シングルクォートが必要になるので、このシングルクォートで括る処理を IF関数内に入れ込んで

👆こんな式にするか、TEXT関数を使ったテクニックで

このように 空白の時（0扱い）は Col2を 文字列の時（H1が選択されている時）は '@' （H1の値を シングルクォートで括った文字列）を返すとする方法があります。

TEXT関数の セミコロンで区切る

この分岐は QUERY関数シリーズの第6回にも登場しましたね。

A2【別解】. セルでカンマ区切りで列番号の数字を入れた列を、その順番でQUERY関数で出力する

QUERY関数のwhere句のセル参照は、参照するセルが空白だった時の対処方法もセットで覚えておくと便利です。

where句では集計関数は使えない

ここまで where句の自由度の高さをお伝えしてきましたが、where句でこれ使えないんかーい！というものもあります。

その一つが select句で登場した 集計関数です。

select句合わせ技1. 集計関数で列を集計

👆上の画像は 第2回テスト（3列目）の点数が、平均点以上の人を抽出しようとした式

なんですが、「CANNOT_BE_IN_WHERE: AVG(`Col3`)」（WHERE句の中でAVGは使えない）ってエラーが返ってます。

avgだけでなく sum や count 、max,minなど集計関数を where 句と組み合わせて使うことは出来ません。

SQLでいうところの、サブクエリみたいなことは出来ないってことですね。

ただ先ほどのように単体の値をシート関数を組み合わせた数式で生成して where句で利用することは出来るので、

QUERY関数で 第2回テスト（3列目）の点数が、平均以上の人を抽出したい場合は

こんな感じで AVERAGE関数で3列目のデータ部分 C2:C7の平均値を取得して、それをクエリ文と組み合わせて対処することが可能です。

式としての美しさに欠けますが、とりあえずシート関数を組み合わせたり、QUERY関数を2回使うといった方法で、このようなケースにも対応できます。

where句では 配列は使えない

FILTER関数は、条件式の部分で 第1引数の元データと関係ない 同じ行数（または同じ列数）の一次元配列を利用することが可能です。

たとえば上の式では、

として、行番号が奇数のデータだけを抽出しています。

しかし QUERY関数は where句で 配列を扱えません。

このようにARRAYFORMULAをつけて ROW(A1:A7) で1～7まで縦に展開される行番号配列を生成して、5と一致するという条件を記述しても、

エラーにはならないものの、見出しのみ出力され条件に一致する結果なしとなります。

これは QUERY関数内では、配列は 先頭の値のみが使われる仕様である為です。

つまり ROW(A1:A7) は {1;2;3;4;5;6;7} という縦配列なんですが、QUERY関数内では 先頭の 1という値として扱われているということです。

そうすると中身は where 1 = 5 という条件式になってるわけですから、当然結果なし（見出しのみ）となりますね。

=5 のところを =1 と変えれば where 1 = 1 ですから、これは全ての結果が返ります。

では、QUERY関数では FILTER式でやったような 行番号が奇数の列だけを返すといった処理は出来ないのか？

これも工夫次第で対応可能です。最後にこれをお題として考えてみましょう！

QUERY関数で 行番号が奇数の行だけを抽出できるか？【ミニお題 3】

QUERY関数で A1:C7のデータの 奇数行のみを出力するには、どのような式を組めばよいでしょうか？

対象データは ミニお題1と同じなのでそちらを利用ください。

なお、QUERY関数には隠れ句の skipping がありますが、今回はこれは使わないものとします。

考えてみましょう！

↓↓
回答はここから。
↓↓

【ミニお題 回答】

👆コチラです！

まずQUERY関数のwhere句では 配列は使えませんが、第1引数のデータから取り出した Col1,Col2といった列であれば 配列として処理ができます。

だから先に、処理に使う行番号配列を元の第1引数だった A1:C7 と中カッコで 横に連結させちゃいましょう。

{A1:C7,ARRAYFORMULA(ROW(A1:A7))}

この時、単にROW(A1:A7)では配列としてスピらないので、ARRAYFORMULAが必要となります。

※通常はARRAYFORMULAは大外に付けるべきですが、今回はQUERY関数のお題ということで、一番外側がQUERYになるようにしました。

これで第1引数は 4列あるデータになったんですが、表示させるのは1～3列目なので、select句を使って  select Col1,Col2,Col3 とします。

今回は3列なんでベタ打ちでいいですが、もっと列が多い場合は 数式で生成する方法もあります。

Q1. QUERY関数のselect句で 20列のデータの 1列目を除いた 2列目～20列目を指定する記述の文字列を数式で生成したい

データにドッキングした行番号の列 Col4は where句だけで使います。

次に「奇数である」という条件ですが、さすがに クエリ文の中で 奇数ならTRUEを返す ISODD関数は使えませんが、QUERY関数には隠れ演算子の 

% 剰余演算子　があるので、

4列目の数値を 2で割った余りが 1と一致するか？ で 奇数判定をします。

where Col4 % 2 = 1

これで QUERY関数でも奇数行だけを抽出ができました。

もちろん QUERY関数の %演算子を知らなくても、第1引数の段階で4列目は2で割った余の配列としておいて

クエリ文では where Col4 = 1 とする方法でも対処できます。

ま、それでも手間が多いと思うんで この手の処理は FILTER関数でやった方が良いですね。使い分けが大事です。

次回、where句で 高度な比較演算子と 複数条件を使おう！

今回のまとめです。

✅where句の記述は適切にスペースを入れる、ただしシングルクォート内は余計なスペースを入れない

✅where句の条件が 文字列型の時はシングルクォートで括る、数値は括らない

✅where句の文字列は大文字・小文字を区別する。区別せず検索したい時はスカラー関数の UPPER か LOWERを使う

✅where句で使う列はselect句で出力対象にしなくてもOK

✅QUERY関数の空白除去は where Col1 is not null を使う

✅where句では列同士の比較も出来る Col2 = Col3

✅where句内で 算術演算子を組み合わせた記述も出来る
　where (Col2+Col3)/2 >= 80

✅where句で条件の値をセル参照にする場合は、文字列型のシングルクォートを両側に残すことに注意 "where Col2 ='"&H1&"'"

✅where句の条件セル参照でセルが空白だった時の対処は IF関数（またはTEXT関数）で

✅where句の条件ではクエリ文の集計関数は使えない、代わりにシート関数を組み合わせる

✅where句の条件では配列は使えない。条件として使いたい配列は第1引数のデータに先に連結しておく

次回はwhere句の続き。contains や like、正規表現の使える matchesなどの 高度な文字列比較演算子、そして複数条件を制御する AND、OR を組み合わせた where句のさらなる応用 に入っていきます。