【小休止】Googleフォームの回答を日付で絞り込む（タイムスタンプ番外編）

mir

2022年9月21日 20:56

土日どっちかで更新している雑談記事の方が最新ネタを扱ってるせいかPVが多いですが、こっちが本編記事です。

最新ネタの雑談記事の方 ↓

前回までの記事で、Googleスプレッドシートで使える 自動タイムスタンプ機能の紹介を全3回にわたって書いてきました。

こっちが本編の前回の記事 ↓

次のシリーズに行く前の小休止ということでタイムスタンプに関連する、1回で完結する 軽めのネタを挟もうかと思います。

タイムスタンプ番外編です。

Q. スプレッドシートに出力されたGoogleフォームからの回答を日にちを指定して、他のシートで絞り込んで見たい。

タイムスタンプは使わないけど、Googleフォームは使ってるよ。という人も多いんじゃないでしょうか？

おそらく最も触れる機会の多い タイムスタンプの一つが、Googleフォームからの回答をスプレッドシートに書き出した時に A列に自動で付与されるタイムスタンプでしょう。

これを日付で絞り込みたいというのが今回の要件です。

・抽出したいデータは フォームの回答 1 という名前のシート AからC列
・A列がタイムスタンプになっている
・1行目はタイトル行となっている
・シート2 の B2に入れた日付で絞り込んだデータを
・シート2の A4以下に表示させたい（A3:C3はタイトル）

今回のお題の要件

これを以下のようにしたいと

タイムスタンプは回答者が回答した日時を自動で記録してくれるので、大変便利な機能ですが、日付だけじゃなく 「時・分・秒」も書き込まれます。

これもありがたいのですが、別シートで指定した日にち（たとえば本日）のデータだけ見たい！という初心者が躓く部分でもあります。

とりあえずそれっぽい式を書いて見るモノの、時刻が入ってるせいでうまく抽出できない・・・。

挙句、知恵袋などで　↓

【至急】あるExcelのスプレッドシートにデータがあるのですが、ある列をある日付で絞り込もうとしたけどうまくいきません。ネットで色々調べたけどダメでした。識者の方、出来れば具体的な式を教えてください。

知恵袋あるある

こんな感じで、読み手がまったく理解できない質問をする人も少なくありません。

抽象的な質問で具体的な回答を求めるのは実に愚かなことだ。

ググレカス（Guglecus、生没年未詳）

人によっては、抽出作業用に 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フォームの日付指定抽出でした。

日付関連のネタつながりで、次回は関数カレンダーを取り上げようかなと。

■このシリーズの次の記事

この記事が気に入ったらサポートをしてみませんか？