みなさんこんにちは！
もり（@moripro3）です。
GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています！
前回は、where句とlike演算子を使用して、「○○を含む」「先頭が○○で始まる」などの部分的な検索をする方法を紹介しました。
今回も引き続きwhere句を使用したデータ抽出です。where句にはいろいろな使い方があるんですよ。
これまでの記事で抽出条件に指定したデータは「文字列」と「数値」でしたね。もう一つ、オフィスワーカーが実務でよく使うデータ形式があります。それが「日付」です！
納期に追われるオフィスワーカーなら、こんなデータ抽出がサクッとできたら便利ですね。
- 今日期限のタスク一覧は？
- 今月末期限のタスク一覧は？
さぁ、今回のポイントは「where句で日付抽出」です。いってみましょう！
where句の条件に日付を指定する方法
where句の条件に日付データを指定する書き方がこちらです。
ポイントは2つです。
- where句の条件に日付を指定するときは「date」というキーワードを付ける
- dateに続けて指定する日付は、’YYYY-MM-DD’の文字列形式とする
公式ドキュメント（英語版）の説明がこちらです。
Use the keyword date followed by a string literal in the format yyyy-MM-dd. Example: date “2008-03-18”
【訳】キーワードdateに続けて「yyyy-MM-dd」形式の文字列リテラルを使用します。
※「文字列リテラル」は「文字列」のことです。
それでは、このタスク管理表をサンプルにして、日付で抽出する方法を紹介していきます。C列の期限を使用しますよ。
日付を直接指定する方法
まずは、最もシンプルな書き方を見てみましょう。タスク管理表から、指定の日付以前のタスク一覧を抽出します。
もう一度２つのポイントを確認します。
- where句の条件に日付を指定するときは「date」というキーワードを付ける
- dateに続けて指定する日付は、’YYYY-MM-DD’の文字列形式とする
このポイントを押さえて、期限が「2019年3月4日」以前のタスク一覧を抽出するwhere句がこちらです。月・日が1ケタの数値の場合、先頭にゼロを付けます。
where C <= date '2019-03-04'
2019/03/04期限のタスク一覧が取得できました。
日付をシングルクォートで囲って文字列形式にするのがポイントです。
今日期限のタスク一覧を自動で抽出する
今日期限のタスク一覧を抽出する場合、上記で紹介したように日付を直接指定すると、日付を毎日書き換えなければなりませんね。これではQUERY関数のメリットが感じられません。
そこで、スプレッドシートの他の関数と組み合わせて、毎日、自動で今日期限のタスクを抽出できる方法を紹介します。
スプレッドシートの2つの関数を使用します。
- TODAY関数
- TEXT関数
TODAY関数で今日日付を取得する
TODAY関数とは、現在の日付に対応するシリアル値を返す関数です。
引数は無しです。
スプレッドシートの任意のセルに=TODAY()と入力すると、当日の日付が表示されます。
TEXT関数で日付を文字列形式に変換する
TEXT関数とは、指定の表示形式に従って、数値を文字列に変換する関数です。
2つの引数を指定します。
- 数値 = 変換する番号、日付、時刻
- 表示形式 = 変換する数値の表示形式を、二重引用符で囲って指定
where句では、日付を文字列形式で指定するルールですね。そこで、TEXT関数を使用して、TODAY関数の返り値を文字列に変換します。
where句の日付条件を関数で指定する
それでは、上記のTODAY関数,TEXT関数を、QUERY関数と合体させてデータを取得します。
もう一度where句の書き方を確認します。
dateに続く「日付」の部分を「TEXT関数の返り値」に置き換えます。下記図の①を、②の書き方に置き換えます。where句と関数を結合する“&が必要です。
where句の書き方がこちらです。
where C <= date '"&TEXT(TODAY(),"YYYY-MM-DD")&"'
※この画面キャプチャは2019年3月4日に取得したものです。
翌日(2019年3月5日)の状態がこちらです。
少し長くてむずかしく感じるかもしれませんが、一度関数を入力すれば、条件に一致する行が自動で取得できるのが便利ですね！
今月期限のタスク一覧を自動で抽出する
つづいて「今月期限のタスク一覧」の抽出方法も紹介します。
この記事を書いているのは2019年3月なので、「2019/3/31期限のタスク一覧」を取得します。
まず、日付を直接指定する方法です。
日付データは date ‘YYYY-MM-DD’ と指定するので、こう書きますね。
でも、この書き方では、月が替わるたびに日付を書き換える必要があります…
これも関数で自動設定しておきましょう！
EOMONTH関数で月末日付を求める
月末の日付を求めるEOMONTH関数を使用します。
引数を2つ指定します。
- 開始日 = 起点となる日付 ← TODAY関数を使う
- 月数 = 開始日の何か月後(前)か ← 当月は0を指定
EOMONTH関数のスプレッドシートでの使い方がこちらです。
where句の月末日付を関数で指定する
ちょっと複雑になりそうですが、順を追って一つずつ組み立てていきましょう！
where句の構成は、この3段階で考えます。
- EOMONTH(TODAY(),0) で、日付の2019/3/31を取得する
- TEXT(EOMONTH(TODAY(),0), “YYYY-MM-DD”)で、日付→文字列「2019-03-31」に変換する
- 文字列の「2019-03-31」をwhere句と結合する
まとめると、where句の記述はこのようになります。
where C <= date '"&TEXT(EOMONTH(TODAY(),0),"YYYY-MM-DD")&"'
これで、月が替わるたびに自動で「今月末期限のタスク一覧」が取得できますね！
まとめ
今回の記事では、where句の条件に「日付」を指定して、行を抽出する方法を紹介しました。
QUERY関数で日付抽出するときのポイントをもう一度おさらいしておきましょう！
- where句の条件に日付を指定するときは「date」というキーワードを付ける
- dateに続けて指定する日付は、’YYYY-MM-DD’の文字列形式とする
TODAY関数などと組み合わせると、条件を書き換えなくてもデータが自動で取得できるので、ぜひ使ってみてください！
「日付」とセットで扱われるデータに「時刻」がありますね。社員の勤怠管理表などでも使います。
さて次回は、○時○分以前のデータ、などの「時刻」を条件にしたデータ抽出の方法を紹介します。
連載目次：GoogleスプレッドシートQUERY関数をマスターしよう
スプレッドシートのQUERY関数を使って、データ抽出・集計を効率化する方法を紹介しています。
- スプレッドシートのQUERY関数を使う最初の一歩！クエリを理解する
- QUERY関数の基本！別シートのデータからselect句で列を取得する方法
- 【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する
- 【QUERY関数】where句とlike演算子を使用して指定の文字を含む行を抽出する
- 【QUERY関数】where句で日付データを条件にして行を抽出する
- 【QUERY関数】where句で時刻データを条件にして行を抽出する
- 【QUERY関数】where句でand,orを使用して複数条件を指定する
- 【QUERY関数】order by句で抽出結果を昇順・降順ソートする
- 【QUERY関数】group by句とcount関数で列のデータ数をカウントする
- 【QUERY関数】group by句とsum関数で列の値の合計値を求める
- 【QUERY関数】group by句とavg関数で列の値の平均値を求める
- 【QUERY関数】group by句とmax・min関数で列の最大値・最小値を求める
- 【QUERY関数】group by句で複数の列をグループ化して集計する
