【小休止】Googleフォームの回答を日付で絞り込む （タイムスタンプ番外編）
土日どっちかで更新している雑談記事の方が最新ネタを扱ってるせいかPVが多いですが、こっちが本編記事です。
最新ネタの雑談記事の方 ↓
前回までの記事で、Googleスプレッドシートで使える 自動タイムスタンプ機能の紹介を 全3回にわたって書いてきました。
こっちが本編の前回の記事 ↓
次のシリーズに行く前の 小休止ということでタイムスタンプに関連する、1回で完結する 軽めのネタを挟もうかと思います。
タイムスタンプ番外編です。
Q. スプレッドシートに出力されたGoogleフォームからの回答を 日にちを指定して、他のシートで絞り込んで見たい。
タイムスタンプは使わないけど、Googleフォームは使ってるよ。という人も多いんじゃないでしょうか？
おそらく最も触れる機会の多い タイムスタンプの一つが、Googleフォームからの回答を スプレッドシートに書き出した時に A列に自動で付与されるタイムスタンプでしょう。
これを日付で絞り込みたいというのが今回の要件です。
・抽出したいデータは フォームの回答 1 という名前のシート AからC列
・A列がタイムスタンプになっている
・1行目はタイトル行となっている
・シート2 の B2に入れた 日付 で絞り込んだデータを
・シート2の A4以下に表示させたい（A3:C3はタイトル）
これを以下のようにしたいと
タイムスタンプは 回答者が回答した日時を自動で記録してくれるので、大変便利な機能ですが、日付だけじゃなく 「時・分・秒」も書き込まれます。
これもありがたいのですが、別シートで指定した日にち（たとえば本日）のデータだけ見たい！という初心者が躓く部分でもあります。
とりあえずそれっぽい式を書いて見るモノの、時刻が入ってるせいで うまく抽出できない・・・。
挙句、知恵袋などで ↓
【至急】あるExcelのスプレッドシートにデータがあるのですが、ある列をある日付で絞り込もうとしたけどうまくいきません。ネットで色々調べたけどダメでした。識者の方、出来れば具体的な式を教えてください。
こんな感じで、読み手がまったく理解できない質問をする人も少なくありません。
抽象的な質問で具体的な回答を求めるのは実に愚かなことだ。
人によっては、抽出作業用に 1列追加して まずは関数で日付の部分だけを抜き出すことを試みるものの、フォームの回答が新しく入ると 入れていた関数が反映されない！なんてパターンに陥ることも。
フォームの新しい回答が入った際の関数適用に関しては、最初から Arrayforomula で配列式としておけば解決するのですが、そもそも今回のケースは単に日付で絞り込むだけなので作業列を用意する必要はありません。
では、どの関数を使うのがよいのしょうか？
↓ここから回答
A.指定した日付で絞り込むなら FILTER関数 + INT関数がベスト
Googleスプレッドシートで、条件でデータ範囲や配列を絞り込む関数 の2大巨頭と言えば、
FILTER関数
QUERY関数
です。
どちらの関数を使っても タイムスタンプから 日付のみで絞り込んでの抽出は出来るんですが、今回は FILTER関数を使う方法がおススメです。
理由としては3つあって
FILTERは ARRAYFORMULAを使わなくてもスピる
QUERY関数で日付を条件にすると記述が面倒
シンプルに条件を絞るだけなので、QUERYを使うまでもない
解説は後にして式と動きを確認しましょう。
//A4に入れる式
=FILTER('フォームの回答 1'!A2:C,INT('フォームの回答 1'!A2:A)=B1)タイトル行 の部分（シート2のA3:C3）は、先に タイトルを入れておいてください。関数は自動で展開されるので、 A4セルに入れればOKです。
今回は 日時データの日付部分のみを B2の指定日と一致しているかを条件にしたいので、INT関数を組み合わせています。
INT関数を使う理由
これは、
INT が小数点以下を切り捨てる関数
であること、
そして 日付データは 中身はシリアル値という数値であり、
1日（=24時間）は、 1 で、それ以下（〇時〇分〇秒 の部分）は小数
として扱われていることの理解が必要となります。
この部分は次回のネタでも解説します。
今回のような
日付データ （2022/09/01） と、
日時データ （2022/09/01 09:02:20) を比較する際、
2022/09/01 は、 2022/09/01 00:00:00 と見なされます。
ようは、9月1日 になった瞬間 0時0分0秒 ですね。
だから
2022/09/01 は 2022/09/01 09:02:20 とイコールではないけど、
2022/09/01 =< 2022/09/01 09:02:20 は成り立ちます。
日付部分が同じかどうかを調べるなら、
2022/09/01 09:02:20 の 09:02:20 の部分（シリアル値の小数部分）を、
取っ払ってあげる必要があります。これが INTの役割です。
ざっくり言ってしまえば、
タイムスタンプ（日時）を INTに入れれば、日付のみになる
ってことです。
ここは大丈夫ですよね？
1. FILTERは ARRAYFORMULAを使わなくてもスピる
INT関数は通常は1つのセルを対象として動く関数なので、
=INT( A2 )
は動いても、
=INT( A2:A )
だとエラーになります。（最新のExcelは自動でスピります）
A2:Aというセル範囲に適用させたい場合、Googleスプレッドシートだと、
=ARRAYFORMULA( INT( A2:A ) )
と、上記のように Arrayformula を組み合わせる必要があります。
ただ、今回使った FILTER関数他、幾つかの配列を扱う関数と組み合わせた場合は、ARRAYFORMULA不要で、自動で範囲に対して処理が適用されるのです。これでだいぶ記述が短くなっています。
ちなみに、QUERY関数は残念ながら特殊で、範囲や結果に関数を組み合わせた配列処理をする場合は、ARRAYFORMULAが必要となります。
（余談）タイトル行も FILTER式内に含めたい場合
タイトル行 の部分（シート2のA3:C3）もFILTER関数に含められないのか？
といった場合は、以下のように 今ある条件に「または 1行目である」という条件を付加すればOKです。
注意点としては、配列処理の場合 AND,ORは使えないので、
「または」(OR）の部分は + を使います。
※ちなみに 「かつ」（AND）の場合は * (かける）で代用します。
これは FLASEが ０ １以上は TRUEという考え方がベースになります。機会があれば別の記事で触れていきます。
↓というわけで、こんな式にすればタイトル行を込みに出来ます。
//A3に入れる式
=FILTER('フォームの回答 1'!A:C,(INT('フォームの回答 1'!A:A)=B1)+(ROW('フォームの回答 1'!A:A)=1))もちろん 今回の B1セルの参照を 直接 TODAY() に置き換えて、自動で「今日のデータだけ」を表示させることも可能。
2. QUERY関数で日付を条件にすると記述が面倒
QUERY関数は独特のお作法があって、日付を扱うときの記述が面倒なんです。
B1参照そのままでは扱えないので、条件となる B1セルの日付データを QUERY関数で使える型に成型してあげる必要があります。
以下のような感じ。
date '"&TEXT(B1,"yyyy-MM-dd")&"'"
この部分の解説は、 GASやシート関数入門のバイブル的 存在「いつも隣にITのお仕事」さんがわかりやすいです。
ただ、もちろん A列には余計な 〇時〇分〇秒があるので、そのままイコールでは出来ません。さらに一工夫が必要です。
QUERYで記述する場合
//1.不等号ではさみうち
=Query('フォームの回答 1'!A:C,"where A >= date '"&TEXT(B1,"yyyy-MM-dd")&"' AND A < date '"&TEXT(B1+1,"yyyy-MM-dd")&"'")
//2.toDate() で日付に変換
=Query('フォームの回答 1'!A:C,"where toDate(A) = date '"&TEXT(B1,"yyyy-MM-dd")&"'")
この辺りのQuery関数で使える記述については、 経営管理deプログラミング さんが、とても詳しいです。
ちなみに1つめの記述の挟み撃ち方法ですが、後半を
A < date '"&TEXT(B1 +1 ,"yyyy-MM-dd")&"'")
としています。 この +1 は終了日を指定した際に必要になります。
FILTER関数で、今回のようにタイムスタンプの列に対して 期間 〇日から〇日迄という、開始日と終了日を指定して抽出する時 も同様 に終了日は +1 がっ必要。
先に説明した通り、日時に対して日付と不等号で絞り込む際、終了日の B1 がたとえば 2022/09/01 となっていた場合、これは 2022/09/01 00:00:00 を表します。
つまり「終了日 2022/09/01 より小さい」と条件を記述すると、
2022/09/01 07:30:10 や 2022/09/01 10:15:32 といった 9/1 のデータが
全て対象外となってしまいます。
気持ちとしては、終了日が 2022/09/01 だったら、2022/09/01 23:59:59 までのデータが欲しいはずです。
よって +1 で翌日にしてから 「その日付より小さい」という条件を 後半に記述し AND で繋げることで、今回の場合だと
2022/09/01 00:00:00 以上 かつ、2022/09/02 00:00:00 より小さい
A >= 2022/09/01 AND A ＜ 2022/09/02
↓ つまり
日付が 2022/09/01
としています。
3. シンプルに条件を絞るだけなので、QUERYを使うまでもない
最後の理由ですが、これはその通りで、Googleスプレッドシートを代表する最強関数 QUERYの真価はグループ化、ピボットによる集計表作成にあります。
今回のようなデータの並びはそのままで 単に絞り込むだけなら
こんなザコは QUERY様 が直々に相手するまでもありませんぜ。
俺たちが可愛がってやるぜ。ヒャッハー！ (下っ端感ｗ）
というわけです。
個人的にはFILTERも四天王クラスの実力者だと思ってるんで、上記のようなザコいセリフをはかないと思いますが・・・。
とりあえず、今回のようなケースは 他のシンプルな関数の処理で十分ってことです。
（余談）QUERY関数を使った方がよいケース
もし今回の要件が 同じスプレッドシート内の別シートへの出力ではなく、
他のスプレッドシートへ出力したい。となると、話は変わってきます。
理由としては importrange関数が登場し、記述が長くなるから。
FILTERだと 条件部分でも importrange の再記述が必要になり非常に長く煩雑な式になります。
importrange が絡んでくるケースでは、QUERYのチート能力の一つ 「抽出する列の指定、条件の対象とする列の指定 を Col1,Col2とシンプル化」 がとても有効です。
今後、関数ネタの時はたびたび出てくることになりますが、とにかく QUERYは バーチャルな配列操作に関しては圧倒的に強いのです。
でも、最新の LAMBDA を組み合わせることで、今後は他の関数でも無駄な繰り返し記述を排除したシンプルな式で実現できるようになっていきますね。
長くなってしまいましたが、以上「小ネタ」タイムスタンプ関連の番外編、Googleフォームの 日付指定抽出でした。
日付関連のネタつながりで、次回は関数カレンダーを取り上げようかなと。
