いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)QUERY関数の条件でセル参照をする方法

 Googleスプレッドシートの第2引数、特にWHERE句でセルを参照し、その値を抽出条件にする方法についてです。

  • QUERY関数の第2引数に使う「" "」(ダブルクォーテーション)の内側にセル番地を含めるとエラーになります。
  • セル番地を「" "」の外に置き「&」で連結するとともに、値を適宜「' '」で囲む必要があります。

手順

失敗例

 次の画像では見出しを含むB2:D7の範囲を抽出対象とし、B列の値がF3セルの値と一致するものを抽出しようとしています。
 しかし結果はエラーとなっています(H2セル)。

 H2セル(エラー)

=QUERY(B2:D7,"WHERE B=F3",1)

 QUERY関数では第2引数の内容を「" "」で囲み、文字列として指定します。
 しかしこの中にセル番地を直接記入してもセル番地とは解釈してくれず、F3セルの値を参照できません。

文字列を参照する例

 上記失敗例の訂正版です。
 B2:D7の範囲を抽出対象とし、B列の値がF3セルの値と一致するものを抽出しています。

 H2セル

=QUERY(B2:D7,"WHERE B='"&F3&"'",1)

 やや複雑になっていますが、F3を「" "」の外に置き、「&」で連結することでセル参照をしています。ただしそれだけだと結果的に「"WHERE B=近藤 竜也"」となり、少し足りません。氏名の文字列を「' '」で囲む必要があります。
 そこで「"WHERE B='"&F3&"'"」としています。これにより「"WHERE B='近藤 竜也'"」と解釈され、抽出は成功します。細かい記号が多くなるので注意深く式を作成する必要があります。



 次の例もほぼ変わりませんが、WHERE~CONTAINS句を使って部分一致抽出を行っています。

 H2セル

=QUERY(B2:D7,"WHERE B CONTAINS '"&F3&"'",1)

 QUERY関数は抽出だけでなく集計やソートまでできる万能な関数ですが、句の順番など制約が厳しく覚えるのはなかなか大変です。しかしこれは慣れるしかありません。

数値を参照する例

 ここではC列の値がF3セルの数値と一致するものを抽出しています。

 H2セル

=QUERY(B2:D7,"WHERE C>="&F3,1)

 数値の場合は値を「' '」で囲む必要がないので文字列の例より簡単です。
 セル参照を「" "」の外に出して&でつなぐだけでOKです。

日付を参照する例

 ここではD列の値がF3セルの日付と一致するものを抽出しています。

 H2セル

=QUERY(B2:D7,"WHERE D>= DATE "&TEXT(F3,"'YYYY-MM-DD'"),1)

 日付は特に厄介です。まず比較演算子(ここでは「>=」)の次に「DATE」の句を置く必要があります。
 また、「2001/6/1」のようにスラッシュで区切る形式のままではダメで、「2001-6-1」のようにハイフンで区切る形式とし、さらに文字列の例と同じく「' '」で囲む必要があります。これらをTEXT関数で一括して行っています。
 なお参照先のF3セルもD列の値も「YYYY/M/D」形式(月と日付が1桁)になっていますが、「YYYY-M-D」のように合わせる必要はありません。