はじめに
A～C列の元データに対して、D列の条件を指定し抽出した結果をE～G列に参照します。
このとき、D列の条件「天気」「気温最高値」「気温最低値」のうち、
「天気」と「気温最低値」だけで条件を設定したいとき、
条件を指定しなくていい「気温最高値」を空白にするとエラーになってしまいます。
そこで、どの条件が空白になってもエラーにならないような方法を考えました。
これがエラーにならないようにしたい
E1セルの数式（エラーになる）
=QUERY(A1:C,
"select A, B, C where B = '"&D2&"'
and C <= "&D4&"
and C >= "&D6&"",1)
※セル参照"&D4&"するときにセルを"&&"で囲む理由は過去の記事で説明しています。
完成イメージ
E1セルの数式
=QUERY(A1:C,
"select A, B, C where B = '"&D2&"'
"&IF(D4="","","and C <= "&D4&"")
&IF(D6="","","and C >= "&D6&"")&"
",1)
今回はB列「天気」の条件が必ず設定されるという前提で、
C列「気温最高値」と「気温最低値」が空欄になる可能性を考慮します。
以下順番に説明していきます。
条件にIFで分岐を入れる
D4セルが空白だったら、空を設定、それ以外の時はC列の気温がD4セルの値よりも低い気温のデータを抽出する、IF文を作成します。
まずは、D4セルが空白のときは、空を設定する式を作成します。
IF(D4="","",〇〇)
次に、空白以外のとき（〇〇部分）にC列＜＝D4セルのデータを抽出する条件を設定します。
C列＜＝D4セルの条件はC <= "&D4&"ですが、
B列「天気」の条件が必ず設定される
ため、条件をandでつなげます。(直前に条件が必ず存在するので先頭にandをつける)
and C <= "&D4&"
さらに、and C <= "&D4&"はQUERY関数用の式なので、外側を「"」で囲みます。
"and C <= "&D4&""を一番最初に作成したIF文の〇〇部分に代入すると以下のようになります。
IF(D4="","","and C <= "&D4&"")
この作成したIF文の前後の他の条件も入れるため、「&」で繋げます。
&IF(D4="","","and C <= "&D4&"")&
D6セルの「最低気温値」についても条件を作成し、&でつなげます。
&IF(D4="","","and C <= "&D4&"")
&IF(D6="","","and C >= "&D6&"")&
QUERY関数の中に組み込みます。
=QUERY(A1:C,
"select A, B, C where B = '"&D2&"'
"&IF(D4="","","and C <= "&D4&"")
&IF(D6="","","and C >= "&D6&"")&"
",1)
※組み込むときに、外側の「&」を「"」で囲んであげます。
理屈はセル参照"&D4&"と同じです。
=QUERY(A1:C,
"select A, B, C where B = '"&D2&"'
"&IF(D4="","","and C <= "&D4&"")
&IF(D6="","","and C >= "&D6&"")&"
",1)
完成です。
「最低気温値」「最高気温値」どちらを空白にしても、エラーにならなくなりました。
ちなみに、どっちも空白にしても「天気」の条件が入っている限りエラーになりません。
補足：すべての条件が空白セルのときの対応方法
今回のように、必ず設定される条件が1つでもあるときは上記のような対応で問題ないのですが、すべての条件に対して、空白セルの判定を行いたいときは、少し工夫が必要になります。
たとえば、D2セル「天気」の条件も空白セルになることを考慮した場合、以下のような式を作成したとします。
=QUERY(A1:C,
"select A, B, C where
"&IF(D2="",""," B = '"&D2&"'")
&IF(D4="","","and C <= "&D4&"")
&IF(D6="","","and C >= "&D6&"")&"
",1)
これだと、D2セルが空白かつD4セルが空白のとき、式が
=QUERY(A1:C,"select A, B, C where and C >= "&D6&"",1)
となり、andでつなげる前の条件がないので、エラーになります。
解決するためには
where句の直後に必ず存在する条件を入力します。
例えば、1=1や'a'='a'です。
加えて、「天気」の条件の頭にもandをつけます。
=QUERY(A1:C,
"select A, B, C where 1=1
"&IF(D2="","","and B = '"&D2&"'")
&IF(D4="","","and C <= "&D4&"")
&IF(D6="","","and C >= "&D6&"")&"
",1)
どの条件のセルを空白にしてもエラーが起きなくなりました。
ちなみに、すべての条件を空白にすると、元データがそのまま反映されます。
おわりに
QUERY関数のwhere句の条件を柔軟に対応できるようにしたいと思いましたが、IF文などの数式と組み合わせている記事があまりなく、調べるときに大変だったので、自分なりにまとめました。