はじめに
複雑な条件を指定してデータを抽出しよう！
クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。
このように、指定範囲から条件を指定してデータを取り出す関数は、EXCELでもお馴染みのデータベース関数各種がありますが、このクエリ関数を使うことによって、より柔軟に複雑な条件を簡単に指定することが出来るようになります。
EXCELにはない関数なので、せっかくスプレッドシートを使うなら覚えておきたい関数ですね(^^)
今回は、クエリ関数で複雑な条件を指定してデータを抽出する方法を紹介させていただきます！
これまでのシリーズはこちら
使い方
サンプル
※なんちゃって個人情報さんから頂いたサンプルですm(_ _)m
この元データを「範囲」に指定して、次の「表示用」シートのA1セルにQuery関数をいれていきます。
特定の文字列や数値を含むデータの抽出
条件を指定する場合、特定の文字列を含んだデータだけ抽出したい場合ありますよね。いわゆる「あいまい検索」の方法です。
「contains」を使った書き方
まずは「 contains」を使った方法です。
構文
=QUERY(範囲,”select 列 where 列 contains ‘文字列'”)
※第2引数の「式(select～)」は全体を「”」ダブルクォーテーション、文字列は「’」シングルクォーテーションで囲っています。
それでは、サンプルで試してみましょう。
「G列：都道府県」に「府」が含まれているデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where G contains ‘府'”)
上の数式を、「表示用」シートのA1セルに入れて下さい。
図のように、「G列：都道府県」で「府」が含まれているデータだけが中抽出されます。
それでは逆に、特定文字列が含まれていない場合の書き方を見てみましょう。
「G列：都道府県」に「府」が含まれていないデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where not G contains ‘府'”)
上の数式を、「表示用」シートのA1セルに入れて下さい。
※「not」は「where」の後ですので、入れる場所に注意して下さい。
「G列：都道府県」で「府」が含まれていないデータだけが中抽出されましたね。
「like」を使った書き方
次に「 like」を使った書き方です。
構文
=QUERY(範囲,”select 列 where 列 like ‘%”&”文字列”&”%'”)
※第2引数の「式(select～)」は全体を「”」ダブルクォーテーション、「%～%」は「’」シングルクォーテーション、「&」は「”」ダブルクォーテーションで囲っています。
それでは、サンプルで試してみましょう。
「G列：都道府県」に「府」が含まれているデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where G like ‘%”&”府”&”%'”)
上の数式を、「表示用」シートのA1セルに入れて下さい。
この方法でも、「contains」の時と同様に、「G列：都道府県」で「府」が含まれているデータだけが中抽出されます。
それでは「like」を使って、特定文字列が含まれていない場合の書き方です。
「G列：都道府県」に「府」が含まれていないデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where not G like ‘%”&”府”&”%'”)
上の数式を、「表示用」シートのA1セルに入れて下さい。
※「not」は「contains」の時と同じで「where」の後です。
starts with
「starts with」を使うことで、特定の文字列から始まるデータの抽出が出来ます。いわゆる「前方一致」の検索です。
構文
=QUERY(範囲,”select 列 where 列 starts with ‘文字列'”)
※第2引数の「式(select～)」は全体を「”」ダブルクォーテーション、文字列は「’」シングルクォーテーションで囲っています。
それでは、サンプルで試してみましょう。
「A列：名前」が「小」で始まるデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where A starts with ‘小'”)
上の数式を、「表示用」シートのA1セルに入れて下さい。
「A列：名前」が「小」で始まるデータだけが抽出されました。
「not」バージョン
「A列：名前」が「小」で始まらないデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where not A starts with ‘小'”)
次のように書くことで「like」を使っても同じことが実現できます。
【前方一致：likeバージョン】
「A列：名前」が「小」で始まるデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where A like ‘”&”小”&”%'”)
あいまい検索の式の最初の「％」を削除します。
サンプルに入れてみましょう。
「starts with」のときと同じ結果が表示されますね。
「not」バージョン
「A列：名前」が「小」で始まらないデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where not A like ‘”&”小”&”%'”)
ends with
「ends with」を使うことで、特定の文字列で終わるデータの抽出が出来ます。いわゆる「広報一致」の検索です。
構文
=QUERY(範囲,”select 列 where 列 ends with ‘文字列'”)
※第2引数の「式(select～)」は全体を「”」ダブルクォーテーション、文字列は「’」シングルクォーテーションで囲っています。
それでは、サンプルで試してみましょう。
「A列：名前」が「子」で終わるデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where A ends with ‘子'”)
上の数式を、「表示用」シートのA1セルに入れて下さい。
図のように「A列：名前」が「子」で終わるデータが抽出されます。
「not」バージョン
「A列：名前」が「小」で終わらないデータだけ抽出する場合はこちらです。
=QUERY(‘クエリ関数Data’!A1:H,”select * where not A ends with ‘子'”)
こちらも「like」を使って同じことが実現できます。
【後方一致：likeバージョン】
「A列：名前」が「小」で終わるデータだけ抽出する場合
=QUERY(‘クエリ関数Data’!A1:H,”select * where A like ‘%”&”子”&”‘”)
あいまい検索の式の最後の「％」を削除します。
サンプルに入れてみましょう。
「ends with」のときと同じ結果が表示されますね。
「not」バージョン
「A列：名前」が「小」で終わらないデータだけ抽出する場合はこちらです。
=QUERY(‘クエリ関数Data’!A1:H,”select * where not A like ‘%”&”子”&”‘”)
複数条件の指定
論理演算子の「and」や「or」を使つことで、複数の条件を指定することができます。
構文
=QUERY(範囲,”select 列 where 条件 and 条件”)
=QUERY(範囲,”select 列 where 条件 or 条件”)
条件を「and」でつなぐと「かつ（両方の条件を満たす）」、「or」でつなぐと「または（いずれか一方を満たす）」を表します。
サンプルで、次の条件を抽出してみましょう。
「C列：性別が女」かつ、「D列：年齢が36未満」
=QUERY(‘クエリ関数Data’!A1:H,”select * where C=’女’ and D<36″)
上の数式を、入力して下さい。
条件通りの結果が抽出出来ました。このように、複数条件の指定が可能です。条件指定には、先に説明した、「like」「contains」「starts with」「ends with」などの条件しても可能なので、組み合わせることでかなり柔軟に条件の指定ができそうですね^_^
まとめ
ここまで紹介した方法で、かなり柔軟に条件指定ができるようになります。次回は、クエリ関数を便利に使う方法を紹介したいと思います！
おまけ
Google先生の説明
コメント