[QUERY関数]日付をセル参照する際はTEXT関数を使う
QUERY関数の小ネタです。
下記のようなスプレッドシートがあり、
列A-Eは元データ、列G-Kには指定した期間のデータを抽出し表示したい、とします。
結論_TEXT関数を用いる
結論を先に言うと、
日付(from/to)を設定するセルを別途用意し、TEXT関数で参照します。
QUERY関数は以下のように構成します。
=QUERY(データ範囲," where 日付列 = date '"& TEXT(参照セル,"yyyy-MM-dd") &"' ")
※「date」の後のTEXT関数は「'」「''」「&」で囲みます。
上記のスプレッドシートに於いては、セルG3に以下のQUERY関数を入力します。
=QUERY(A2:E," where D >= date '"& TEXT(H2,"yyyy-MM-dd") &"' and D <= date '"& TEXT(J2,"yyyy-MM-dd") &"' ")
これによりセルH2～J2の期間にあるデータを抽出することが可能です。
QUERY関数で日付をセル参照する際のモヤモヤポイント
以上のようにTEXT関数を用いることに至った理由をメモします。
１，日付は"yyyy-MM-dd"(ハイフンで連結)にしなければならない
QUERY関数内に日付をベタ打ちするとすれば以下の通りとなります。
( "yyyy/MM/dd" ではNG)
=QUERY(A2:E,"where D >= date'2021-04-01' and D <= date'2021-04-30'")
2，セル参照する際には表示形式を「書式なしテキスト」にしなければならない
関数内に日付をベタ打ちするのはメンテ性に欠けるので別途参照セルを用いるとします。
この場合、参照するセルは"yyyy-MM-dd"にしつつ、数字の表示形式を「書式なしテキスト」にする必要があります。
QUERY関数はシンプルにすみますが、上記の制約は煩わしくなることが多いです。
=QUERY(A2:E,"where D >= date'"&H2&"' and D <= date'"&J2&"'")
以上の理由により、QUERY関数内にTEXT関数を用いることをお勧めします。
※2022年9月に技術同人誌「会社員がVLOOKUPの次に覚えるQUERY関数超入門」を出版いたしました。