みなさんこんにちは！
もり（@moripro3）です。
GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています！
前回は、where句とdateキーワードを使用して、「日付」を条件にしたデータ抽出の方法を紹介しました。
さぁ、日付ときたら…次は「時刻」ですよ！
イベントの運営・システム開発の本番移行など、一日のスケジュールが分刻みの場合は、こんなデータ抽出ができると便利ですね。
- ○時までに完了すべきタスクは？
- ○時以降に着手するタスクは？
今回のポイントは「where句で時刻抽出」です。いってみましょう！
where句の条件に時刻を指定する方法
QUERY関数でwhere句の条件に「時刻」を指定する書き方がこちらです。
ポイントは2つです。
- where句の条件に時刻を指定するときは「timeofday」のキーワードを付ける
- timeofdayに続けて指定する時刻は、’HH:mm:ss’の文字列形式とする(HHは24時間表記)
公式ドキュメント（英語版）の説明がこちらです。
Use the keyword timeofday followed by a string literal in the format HH:mm:ss[.SSS] Example: timeofday “12:30:45”.
【訳】キーワードtimeofdayに続けて「HH:mm:ss」形式の文字列を使用します。
今回のサンプルは工程管理表です。各工程の作業に開始時刻と終了時刻が指定されています。この「時刻」を条件にしてデータ抽出をしていきます。
指定の時刻を基準としてデータを抽出する
まずは基本の使い方です。where句の条件に「時刻」を直接指定する方法です。
終了時刻が12時以前のデータを抽出する
工程管理表から「12時までに完了すべき作業」を抽出します。where句の条件は、C列「終了時刻」が12時以前となりますね。
時刻抽出のポイントは、timeofdayに続く「時刻」を、秒まで指定することです。うっかり’12:00’と書いてしまいそうですが、秒の指定がないとエラーになります。
Timeofday literals should be of form HH:mm:ss[.SSS]
→HH:mm:ssの形式で指定してください
必ず、’HH:mm:ss’ で秒数まで指定しましょう。秒数に特別なこだわりがなければ00秒で大丈夫です。
これを踏まえたwhere句の書き方がコチラです。timeofdayのキーワードに続けて、時刻をシングルクォートで囲って文字列形式で指定します。
where C <= timeofday '12:00:00'
終了時刻が12時以前の行が取得できました。
開始時刻が18時以降のデータを抽出する
つぎに、B列の開始時刻が18時以降のデータを抽出してみます。秒数まで指定して ’18:00:00′ と書きます。
18時「以降」なので、比較演算子の >= を使用します。
where B >= timeofday '18:00:00'
開始時刻が18時以降の行が取得できました。
現在時刻を基準としてデータを抽出する
作業の内容によっては、「現在時刻」を基準としたデータ抽出が必要な場面があります。引数に時刻を直接指定する書き方では、その都度書き換えなければなりません。
スプレッドシートの関数を使用して現在時刻を自動取得する方法を紹介していきます。
NOW関数で現在時刻のシリアル値を取得する
NOW関数は、現在の日付と時刻に対応するシリアル値を返します。
NOW関数の返り値は「シリアル値」ですが、where句の条件で、時刻は「文字列」で指定する必要がありましたね。
- timeofdayに続けて指定する時刻は、’HH:mm:ss’の文字列形式とする(HHは24時間表記)
そこで、TEXT関数を使用して、NOW関数の返り値を’HH:mm:ss’の文字列に変換します。
TEXT関数でシリアル値を文字列に変換する
TEXT関数とは、指定の表示形式に従って、数値を文字列に変換する関数です。
2つの引数を指定します。
- 数値 = 変換する番号、日付、時刻
- 表示形式 = 変換する数値の表示形式を、二重引用符で囲って指定
これで、現在時刻を文字列HH:mm:ssに変換できました。
where句の条件に現在時刻を指定する
キーワードtimeofdayにつづけてTEXT関数の返り値を指定します。”&で結合します。
where C <= timeofday '"&TEXT(NOW(),"HH:mm:ss")&"'
※NOW関数は、自動で時刻を刻まずに、スプレッドシートで最後に再計算したときの日付と時刻を返します。そのため、現在の状態を確認したいときは、F5キーで再読み込みして、スプレッドシートを最新の状態にしましょう。
まとめ
今回の記事では、where句の条件に「時刻」を指定して、行を抽出する方法を紹介しました。
QUERY関数で時刻抽出するときのポイントをもう一度おさらいしておきましょう！
- where句の条件に時刻を指定するときは「timeofday」のキーワードを付ける
- timeofdayに続けて指定する時刻は、’HH:mm:ss’の文字列形式とする(HHは24時間表記)
一日のスケジュールがタイトに組まれている仕事で役立ちそうですね。また、勤怠表などでも、抽出条件に時刻を指定することで、早出した人の一覧・残業した人の一覧が取得できます。
ここまでのシリーズでは、where句に「単一条件」を指定してきました。単一条件とは「条件が一つだけ」という意味です。
ですが、実務では、このように複数条件でデータ抽出をすることがありますね。
- 期限が今日、かつ、ステータスが未着手
- ステータスが未着手、または、対応中
次回は、複数条件でのデータ抽出を紹介していきます。
連載目次：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句で複数の列をグループ化して集計する
