目次
はじめに
QUERY 関数は複数の条件を指定可能な検索関数だ。
VLOOKUP や INDEX と MATCH の組み合わせで実現していた難しい検索式も、QUERY 関数を使えば一つの式だけで実現できてしまうかもしれない。
検索結果が複数個になる場合も、全ての結果を取得することが可能だ。
QUERY 関数のこのような特徴は、検索対象や検索条件の指定に、独自の SQL ライクな言語を採用したことが理由として大きい。
言語ゆえの柔軟性と守備範囲の広さが大きく影響しているのだ。
もちろんその反面、検索式の構築には相応の労力が必要となるが、SQL の基本的な知識があれば超えるべきハードルは高くはない。
QUERY 関数の簡単な例
最初に、一つの条件でデータを検索する例を示そう。
実際にはこのようなケースは VLOOKUP 関数や、INDEX 関数と MATCH 関数を組み合わせて実現することが多いと思われるが、QUERY 関数のもっとも簡単な例として取り上げることにする。
以下のような表があったとしよう。
この表から、条件に合致する建物名を取得する課題を想定してみよう。
検索の条件として、住所が「大阪府」である建物名を取得したいものとする。
その場合の、QUERY 関数を用いた式は以下のようになる。
=QUERY(A2:D9, "SELECT D WHERE A = '大阪府'")
上式の第1引数は表の範囲を示している。
第2引数は検索条件で、「Google Visualization API のクエリ言語」という固有の言語によって記述されている。
ここでの意味は、A列が「大阪府」であるような行のD列の値を求めよ、というものになる。
上記は、SQLになじみのない方にとっては、難解な判じ物のように感じてしまうかもしれない。
一方で、SQLに慣れている方なら、容易に意味が想像できるのではないだろうか。
実際に検索した結果は以下の通りとなる。
A11セルに入力した上式によって7行目のデータが検索され、D列の「大阪城」が取得されていることが分かるだろう。
なお、検索言語の仕様は以下のURLで公開されている。
現状は英語版のみしか公開されていないようだが、機械翻訳などを使えば理解は比較的容易だろう。
https://developers.google.com/chart/interactive/docs/querylanguage
VLOOKUP 関数を使った記述例
上記を VLOOKUP 関数を使って記述すると、以下のようになる。
=VLOOKUP("大阪府", A2:D9, 4, FALSE)
当然ながら得られる結果は全く同じとなる。
上記の式の詳しい説明は、記事の趣旨と異なるため省かせていただく。
概ねの意味は、文字列「大阪府」をA2からD9の範囲内の1列目で検索し、見つかった行の4番目の値を取得するという内容になる。
INDEX 関数と MATCH 関数を使った記述例
上記を INDEX 関数と MATCH 関数を使って記述すると以下のようになる。
=INDEX(D2:D9, MATCH("大阪府", A2:A9, 0))
こちらも得られる結果は同じだ。
ここでも上記の式の詳しい説明は、記事の趣旨と異なるため省かせていただく。
概ねの意味は、文字列「大阪府」がA2からA9の範囲で何番目に位置するかを取得し、D2からD9の範囲でその位置に存在する値を取得するという内容になる。
複数の条件で検索する
続いては、複数の条件でデータを検索する例を示そう。
前出の表を再度利用することにする。
今度は検索の条件として、住所が「東京都」でかつ「港区」である建物名を取得したいものとする。
その場合の、QUERY 関数を用いた式は以下のようになる。
=QUERY(A2:D9, "SELECT D WHERE A = '東京都' AND B = '港区'")
ここでの意味は、A列が「東京都」でかつ、B列が「港区」であるような行のD列の値を求めよというものになる。
結果は以下のようになる。
A列に適用する条件と、B列に適用する条件を「AND」で連結することにより、両方の条件を満たすデータのみを検索している。
OR条件で検索する
上記は複数の条件を「AND」で連結したが、「OR」による連結も可能だ。
今度は検索の条件として、住所が「北海道」または「沖縄県」であるような建物名を取得したいものとする。
その場合の、QUERY 関数を用いた式は以下のようになる。
=QUERY(A2:D9, "SELECT D WHERE A = '北海道' OR A = '沖縄県'")
ここでの意味は、A列が「北海道」または、A列が「沖縄県」であるような行のD列の値を求めよというものになる。
上式では条件を適用するが同じA列になるが、特に問題はない。
なお、結果は以下のようになる。
式が入力されているのはこれまで通りA11セルだが、今回は条件に合致するデータが2つ存在するため、結果の値がA11セルとA12セルの2か所に表示されている。
このように、複数個のデータが抽出された場合は、式が入力されているセルから下方向にデータが連続して表示される。
ちなみに、A12セルが空白でない場合はエラーとなってしまうため注意しよう。
答えが表示されるエリアは、あらかじめ空けておく必要があるのだ。
ANDとORの組み合わせで検索する
これまで条件が2つまでのケースを示したが、実はこの数には論理的な制限はない。
増やせるだけ増やすことができる。
また、()を用いることにより、条件の一部をグループ化することができる。
以下は、住所が「愛知県」でかつ「名古屋市」でかつ「中区」でないか、または「常滑市」であるような建物名を取得する、という複数の条件が複雑に絡み合った式だ。
=QUERY(A2:D11, "SELECT D WHERE A = '愛知県' AND ((B = '名古屋市' AND C <> '中区') OR B = '常滑市')")
このような複雑な条件でも検索することが可能になっている。
なお、これまで使ってきた表にデータを数行追加している。
結果は以下のようになる。
また、上式ですでに使っているが、値の照合には等号（=）のみでなく不等号（<>）を使うことなども可能だ。
仕様の詳細は、前出の検索言語のURLを参照いただきたいが、SQLの基礎知識があれば想像することは容易だろう。
複数の列を検索する
QUERY 関数では、複数の列を同時に検索することも可能だ。
やり方は簡単で、検索したい列をカンマ区切りで並べればよいだけだ。
以下に例を示そう。
都道府県が「福岡県」の行の、「市町村」と「建物名」を検索する式だ。
=QUERY(A2:D11, "SELECT B, D WHERE A = '福岡県'")
検索結果は以下のようになる。
複数の列を検索した場合、上記のように結果は式が入力されているセルから見て右側に展開される。
応用編：ちょっとしたテクニック
ここから先は、応用編として、検索時のちょっとしたテクニックを紹介する。
正規表現を使って検索する例
まずは正規表現を使って検索する方法について紹介する。
正規表現は、文字列を照合する部分で利用できる。
等号「=」の代わりに「MATCHES」と記述し、それに続いて正規表現文字列を指定する。
具体的な例を示そう。
以下は、建物名に「城」の文字が含まれる建物を検索する式だ。
正規表現を使った例としてはシンプルだが、あくまでも例として参考にしていただきたい。
=QUERY(A2:D11, "SELECT D WHERE D MATCHES '.*城.*'")
結果は以下のようになる。
正規表現パターンの文法についてここで詳しく紹介することはしないが、上の例のような基本的なパターンは一通り網羅されているようだ。
LIKEを使って検索する例
QUERY 関数の検索では、SQLではおなじみの LIKE も使用可能だ。
正規表現を使うまでもない単純な検索には、手軽に使える LIKE の方が向いているだろう。
LIKE はいわゆるワイルドカードによる検索をサポートする機能だ。
上で取り上げた、建物名に「城」の文字が含まれる建物を検索するような用途は、LIKE を使っても記述することが可能だ。
具体的な式は以下のようになる。
=QUERY(A2:D11, "SELECT D WHERE D LIKE '%城%'")
ワイルドカードを意味する文字が、正規表現の場合とは異なり「%」になっていることに注意しよう。
一見すると分かりにくいのだが、一般的なSQLや正規表現の仕様を踏襲したのだと思われる。
結果は以下のようになる。
正規表現を使った場合と同じ結果になっていることが分かるだろう。
元の表が変更されてもメンテナンス不要な式を作る
便利な QUERY 関数だが、欠点もある。
そのうちの一つが、元の表が変更されると思惑通りの結果が得られなくなるケースがあることだ。
これは検索対象の列や検索条件で参照する列を、範囲で指定するのではなく文字列で指定する仕様になっているからだ。
例えば前出の、建物名に「城」の文字が含まれる建物を検索するようなケースでは、D列の前に列が挿入されたり削除されたりすると動作しなくなってしまう。
列名が変更されても、文字列中の「SELECT D WHERE D」のような表記が変わらないからだ。
それでは実際にB列とC列の間に新しい列を挿入してみよう。
すると、結果は以下のようになってしまう。
この問題を解決するには、検索対象や検索条件で参照する列を、動的に生成するような式にすればよい。
このような用途には、CELL 関数が使える。
CELL 関数は、セル名を文字列で取得できる関数なので、セルが移動すれば得られる文字列も動的に変化する。
これを応用すれば、列名を動的に生成する式を作ることができる。
以下は、上記の式を CELL 関数を使って書き直した例となる。
=QUERY(A2:E11, "SELECT " & MID(CELL("ADDRESS", E:E), 2, 1) & " WHERE " & MID(CELL("ADDRESS", E:E), 2, 1) & " LIKE '%城%'")
やや複雑な式となるので、労力に見合う価値があるかどうかは着手前に検討した方がよいだろう。
まとめ
Google スプレッドシートの QUERY 関数について紹介した。
SQLライクな構文を用いて、複数の条件で検索する例などを示した。
運用に際して有用なテクニックなども紹介した。