QUERY関数の条件が空白セルでもエラーにしない方法

# はじめに

A～C列の元データに対して、D列の条件を指定し抽出した結果をE～G列に参照します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/ae513d09-b4c0-bc87-d4d9-e8db2f5fc117.png)


このとき、D列の条件「天気」「気温最高値」「気温最低値」のうち、
「天気」と「気温最低値」だけで条件を設定したいとき、
条件を指定しなくていい「気温最高値」を空白にするとエラーになってしまいます。
そこで、どの条件が空白になってもエラーにならないような方法を考えました。

### これがエラーにならないようにしたい
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/03d2cd49-1dd4-34d4-5ce8-ea71236e0dc3.png)

E1セルの数式（エラーになる）
```
=QUERY(A1:C,
"select A, B, C where B = '"&D2&"'
and C <= "&D4&"
and C >= "&D6&"",1)
```
※セル参照`"&D4&"`するときにセルを"&&"で囲む理由は[過去の記事](https://qiita.com/matsuko003/items/f153410b04ddb6ad1c31)で説明しています。

# 完成イメージ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/05d3ce71-570a-962c-8093-35494b7a6964.png)


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&"'
<font color='red'>"&</font>IF(D4="","","and C <= "&D4&"")
&IF(D6="","","and C >= "&D6&"")<font color='red'>&"</font>
",1)

完成です。
「最低気温値」「最高気温値」どちらを空白にしても、エラーにならなくなりました。
ちなみに、どっちも空白にしても「天気」の条件が入っている限りエラーになりません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/3b6865c2-109a-95a3-e017-e919f885af0a.png)

![無題.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/57f7fd68-4d6b-ca03-0d49-b505e5bde212.png)



# 補足：すべての条件が空白セルのときの対応方法

今回のように、必ず設定される条件が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でつなげる前の条件がないので、エラーになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/517658f1-7caf-cc61-fcf2-a1b20547ea2b.png)


## 解決するためには
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)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/af953101-e174-da1e-9ba3-695b69b52689.png)


どの条件のセルを空白にしてもエラーが起きなくなりました。
ちなみに、すべての条件を空白にすると、元データがそのまま反映されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3128319/11facc4e-67d2-5708-a21b-fff650123326.png)


# おわりに
QUERY関数のwhere句の条件を柔軟に対応できるようにしたいと思いましたが、IF文などの数式と組み合わせている記事があまりなく、調べるときに大変だったので、自分なりにまとめました。

# 参考記事
・[スプレッドシートでフィルタ機能を使わずに絞り込みを行う](https://qiita.com/kou1209/items/467ca07e8bbbf88fc8b8)