前回の話で、数値条件でデータを抜き出したり、それを並び替えたりする方法は分かりました！
これって日付でもできるんですか？
人事っぽくて良い着眼点ね！
その通り、同じ方法で条件指定や並び替えが可能よ。
但し、「date」という単語を頭につけたり、少しルールがあるのでササッと覚えてしまいましょう！
人事業務では、日付ベースで管理する情報が多くあります。
特に、毎月の給与計算では、日付によって支給する対象や方法が変わるため、重要なファクターになります。
- 入社者・退職者
- 給与計算期間と入社日・退職日を確認
- 支給有無
- 日割計算
- 社保控除
などなど…
- 給与計算期間と入社日・退職日を確認
- 休職者
- 給与計算期間と休職期間を確認
- 支給有無
- 日割計算
- 社保控除
- 手当・控除の停止・再開
- 住民税の徴収方法切り替え
などなど…
- 給与計算期間と休職期間を確認
従業員数が増えるほど誤支給などのリスクも高まるため、あらかじめ管理体制を整えておきましょう。
- QUERY関数で「いつからいつまで」という日付範囲を指定してデータ抽出できる
- TODAY関数とEOMONTH関数で（今日から数えた）月数を指定して、その月初・月末の日付を取得できる
- QUERY関数に組み入れると、月替りで日付範囲をリアルタイム更新できる
- 例えば、毎月の給与計算に対応する休職者リストを、手入力無しで自動生成できる
- QUERY関数に組み入れると、月替りで日付範囲をリアルタイム更新できる
- TODAY関数とEOMONTH関数で（今日から数えた）月数を指定して、その月初・月末の日付を取得できる
基本｜【date】日付指定の方法
QUERY関数で日付指定する際のルールは以下の通りです。
- date '指定する日付'
- 日付として読み込むために、「'」で文字列にしてから、頭に「date」をつける
- 数式内で指定する日付形式は「YYYY-MM-DD」
- それぞれ「-」で区切る
- 日本語にすると「年-月-日」
- 例：2021-5-15
「デート（date）したらバー（-）でチョメチョメ（' '）」と覚えました！
きもすぎ。
今回も、読売ジャイアンツを例に見ていきましょう。
指定した日付以降・以前
「2000年以降に生まれた選手」を抜き出す場合、以下のようになります。
数値のように不等号で範囲指定できるんですね！
dateで日付として読み込んでしまえば、後のルールは同じよ。
せっかくなので復習しましょう！
以下の文字列をコピーして使用して下さい。（緑文字を置き換え）
=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列>=date'YYYY-MM-DD'")
- >（〜より後）
- <（〜より前）
- >=（〜以降）
- <=（〜以前）
- 貼り付けたいセルにカーソルを合わせて「fx」欄に貼り付け
- キーボード「F2」ボタン（セル編集）を押してから貼り付けでも可
【not】一部を除いた期間
「2000年以降に生まれた選手」を消去法で抜き出す場合、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where not 検索列<date'YYYY-MM-DD'")
【order by】日付による並び替え
「2000年以降に生まれた選手」を「生まれ順（生年月日が早い順）」に並び替えると、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列>=date'YYYY-MM-DD' order by 検索列 asc")
- order by 列（指定した列で並び替え）
- ＋ asc（昇順で）
- ＋ desc（降順で）
【and】指定した期間「いつからいつまで」
「2000年に生まれた選手」を抜き出して「生まれ順（生年月日が早い順）」に並び替えると、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列>=date'YYYY-MM-DD' and 検索列<date'yyyy-mm-dd' order by 検索列 asc")
- 特定の月を期間指定する場合は「検索列>=当月初 and 検索列<翌月初」
- 「<翌月初」にすれば、自動的に「前月末日まで」が指定される
- 「<=当月末」にすると、月毎に日付を修正する手間が発生する
- 「<翌月初」にすれば、自動的に「前月末日まで」が指定される
- 給与計算など、末日の日付が必要な場合は「EOMONTH関数」を活用する（このあと紹介）
実践｜給与計算における休職者の整理
これまでの内容で、QUERY関数を使って「日付で範囲を指定・抽出する方法」は分かりましたね。
後半は、実務で使えるようになるために、人事の月次タスクである「給与計算」を取り上げて解説していきます。
給与計算において、特に注意すべきポイントは「休職者」です。
冒頭でも触れましたが、休職期間によって日割計算や手当・控除の休止・再開など、様々な要素が絡みます。
今回は、以下の前提でケーススタディを作成しました。
デフォルトで従業員コード順だと、休職ステータスもバラバラで使いづらいですよね…。
いちいち日付を照らし合わせるのは疲れるし、時間の無駄よ。
関数で自動的にリストアップできるように仕組み化しましょう！
初級編：手入力による日付指定
休職中の従業員を抽出
休職データより「休職開始日が2021年4月1日以前」かつ「休職終了日が2021年4月30日以降」の従業員を抜き出すと、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select* where 検索列<=date'2021-4-1' and 検索列>=date'2021-4-30'")
- 1営業日も勤務していない休職者を抽出
- 少なくとも4/1と4/30は休職していることが条件になる
休職入りの従業員を抽出
休職データより「休職開始日が2021年4月1日より後」かつ「休職終了日が2021年4月30日以降」の従業員を抜き出すと、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select* where 検索列>date'2021-4-1' and 検索列>=date'2021-4-30'")
- 月初に1営業日以上勤務している休職者を抽出
- 少なくとも4/1は勤務しており、4/30は休職していることが条件になる
休職明けの従業員を抽出
休職データより「休職開始日が2021年4月1日以前」かつ「休職終了日が2021年4月30日より前」の従業員を抜き出すと、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select* where 検索列<=date'2021-4-1' and 検索列<date'2021-4-30'")
- 月末に1営業日以上勤務している休職者を抽出
- 少なくとも4/1は休職しており、4/30は勤務していることが条件になる
なお、日付条件をセル参照する方法については、こちらの記事で解説しています。
中級編：関数による日付指定
月次タスクの場合、数式の日付を毎月修正するのは手間ですよね。
そんな作業を自動化できる方法について解説します。
QUERY関数の日付部分を、これから紹介する２つの関数に置き換えるだけなので、是非ご活用下さい。
【TODAY/EOMONTH】日付を表す関数
まずは、日付を表す２つの関数を紹介します。
- TODAY関数（今日の日付）
- =today()
- 上記でも表示可能だが、QUERY関数で使用するにはTEXT「YYYY-MM-DD」形式に変換する必要がある
- =today()
=text(today(),"YYYY-MM-DD")
- EOMONTH関数（月末の日付）
- =eomonth("開始日",月数)
- 月数は、当月なら0
- 前月なら-1、前々月なら-2…
- 翌月なら1、翌々月なら2…
- こちらもQUERY関数で使用するには、開始月を「YYYY-MM-DD」形式に変換する必要がある
- 月数は、当月なら0
- =eomonth("開始日",月数)
=text(eomonth("開始日",月数),"YYYY-MM-DD")
次に、この２つの関数を組み合わせます。
具体的には、EOMONTH関数の「開始日」をTODAY関数に置き換えます。
そうすることで、給与計算などに必要な月初・月末日付のリアルタイム更新が可能になります。
今回の例では、給与計算の作業月（today）が５月であるため、以下の数式で４月３０日（前月末）という日付を表示できます。
=text(eomonth(today(),-1),"YYYY-MM-DD")
また、月初の日付については、その１ヶ月前の末日に＋１することで抽出できます。
同様に、５月に作業している前提で４月１日（前月初）という日付を表示するには以下のようになります。
=text(eomonth(today(),-2)+1,"YYYY-MM-DD")
仕上げに、QUERY関数に接続するため、数式を「"&〜&"」で括ります。
以上で準備は整ったので、休職中・休職入り・休職明けの３パターンについて、自動抽出する方法を見ていきましょう。
休職中の従業員を抽出（日付自動）
休職データより「休職開始日が2021年4月1日以前」かつ「休職終了日が2021年4月30日以降」の従業員を抜き出すと、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select* where 検索列<=date'"&TEXT(eomonth(today(),-2)+1,"YYYY-MM-DD")&"' and 検索列>=date'"&TEXT(eomonth(today(),-1),"YYYY-MM-DD")&"'")
休職入りの従業員を抽出（日付自動）
休職データより「休職開始日が2021年4月1日より後」かつ「休職終了日が2021年4月30日以降」の従業員を抜き出すと、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select* where 検索列>date'"&TEXT(eomonth(today(),-2)+1,"YYYY-MM-DD")&"' and 検索列>=date'"&TEXT(eomonth(today(),-1),"YYYY-MM-DD")&"'")
休職明けの従業員を抽出（日付自動）
休職データより「休職開始日が2021年4月1日以前」かつ「休職終了日が2021年4月30日より前」の従業員を抜き出すと、以下のようになります。
=query('シート名'!$この列から:$この列まで,"select* where 検索列><=date'"&TEXT(eomonth(today(),-2)+1,"YYYY-MM-DD")&"' and 検索列<date'"&TEXT(eomonth(today(),-1),"YYYY-MM-DD")&"'")
以上、日付の範囲指定について、ケーススタディも交えながら解説しました。
また、QUERYを使った便利な数式をすぐにコピペで使える「テンプレ集」も用意しているので、是非ご活用下さい。
