農場の資材管理システムを作るために活用したいと思い、QUERY関数の使い方を学んでいます。
前回は、QUERY関数の基本の活用方法について整理しました。
今回の記事では、実データを使ってみた際に複雑な条件での抽出が必要になったので、その際にやったことを整理しておきます。QUERY関数の応用編となります。
スプレッドシートをデータベースにした資材管理のシステムを作るのに、QUERY関数を使うとうまくいく気がしている。
— おおさき🥔小さなIT活用で快適な農場づくりを (@massa_potato) 2022年9月17日
ノンプログラマーのためのスキルアップ研究会 | 会社員がVLOOKUPの次に覚えるQUERY関数超入門 #技術書典 https://t.co/wFKypNKWbx
やりたいこと
以下のような「単価情報」シートがあります。肥料や農薬などの購入資材の規格や単価などが入ったデータベースです。
「単価情報」シート
（営農管理システム「アグリノート」から出力したcsvファイルをスプレッドシートに読み込んだものです）
この「単価情報」シートには「適用開始日」と「適用終了日」という列があり、その資材単価が適用される期間を示しています。ただし、この期間については以下のことに注意します。
- 同じ資材に対し複数の単価が存在するが、期間が重複することはない
- 「適用終了日」が空白のものは現在もその単価が適用中である
ここで今回やりたいことは、新たに「単価情報（期間抽出）」というシートを作成し、指定した日付（例：本日2022年9月23日）に当てはまる単価を抽出した新たなデータベースをQUERY関数を使って作成したいというものです。
完成したシートと関数
結果からいくと、以下のような方法で、複雑な条件抽出を実現することができました。
新たに「単価情報（期間抽出）」シートを作成し、ここに「単価情報」シートから今日の日付に該当する単価情報をQUERY関数で抽出します。
セルA1
=QUERY('単価情報'!A:K, "select * where J <= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"' and (K >= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"' or K is null)")
第2引数のクエリ部分がかなり複雑ですね。クエリは以下のような構造になっています。
select * where 条件① and (条件② or 条件③)
- 条件①：J列の「適用開始日」が、今日の日付よりも前
- 条件②：K列の「適用開始日」が、今日の日付よりも後
- 条件③：K列の「適用開始日」が空白
順番に整理していきます。
QUERY関数を使っていろいろな条件で抽出する
今日の日付と比較して抽出を行う方法
まず、シンプルにJ列の「適用開始日」が今日の日付より前にあるデータをQUERY関数で抽出してみます。
=QUERY('単価情報'!A:K, "select * where J <= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"'")
今回のデータだと、全ての単価情報が今日の日付よりも前に適用開始となっているので、元のデータベースと同じものになります。
今日の日付での抽出方法は、こちらの記事を参考にさせていただきました。
同様の表記方法で、K列の「適用終了日」が今日の日付より後にあるデータを抽出する関数は、以下のようになります。
=QUERY('単価情報'!A:K, "select * where K >= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"'")
今回のデータだと、何もデータが抽出されません。
これは、「適用終了日」が空白になっているものが抽出されていないためです。実際には、「適用終了日」が空白になっているものはその単価が適用されているので、このような行を抽出できるようにしたいです。
空白の有無で抽出を行う方法
指定列が空白になっているデータを抽出したい場合は、以下のようにwhere句にis nullを使います。
=QUERY('単価情報'!A:K, "select * where K is null")
逆に空白のデータを取り除きたい場合は、is not nullが使えます。
=QUERY('単価情報'!A:K, "select * where K is not null")
今回のデータでは、前者のis nullを使います。
2つ以上の条件で抽出を行う方法
最後に、ここまでで見てきた条件を組み合わせます。複数の条件を組み合わせるには、where句の抽出条件にandとorを使います。今回の抽出条件でクエリを書くと、以下のような構造になります。
select * where 条件① and (条件② or 条件③)
- 条件①：J列の「適用開始日」が、今日の日付よりも前
J <= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"'
- 条件②：K列の「適用開始日」が、今日の日付よりも後
K >= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"'
- 条件③：K列の「適用開始日」が空白
K is null
これを関数にまとめると、以下のようになります。
=QUERY('単価情報'!A:K, "select * where J <= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"' and (K >= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"' or K is null)")
QUERY関数で複数条件を指定して抽出する方法は、こちらの記事を参考にさせていただきました。
おわりに
いろいろと試しながら実現できました。実運用でかなり重宝しそうです。
参考
- 公式ドキュメント
- いつも隣にITのお仕事
- カワムラさん著「会社員がVLOOKUPの次に覚えるQUERY関数超入門」
