Google フォームの回答のタイムスタンプを「年月」別に集計するための処理をマクロとARRAYFORMULA 関数で効率化する

2022年12月22日 23:22

22日目です。残すところ、あと3記事になりました。
この記事は22日の19時に書き始めています。やばいです。
記事タイトルも、標準機能ではなく、応用的なことを書いていますね。
完全に…
ネタっなくなったね
です。

さて、今日はGoogle フォームの機能の解説記事なんですけど、
私の著書の『Google ドライブの教科書2022』を2022年12月23日(金) 17時から12月28日 16時まで無料にしていますので、ぜってぇみてくれよな！

残すところあと3日です。残りの日のネタをひねり出せるか、楽しみに書いています。

Google フォームへの回答のタイムスタンプの値を使用して集計したい

Google フォーム、使っていますか？Google フォームで作成したフォームから回答された内容には必ず「タイムスタンプ」が表示されます。

回答を送信すると、その回答が送信されたタイムスタンプが日時形式で表示されます。

このタイムスタンプ列の値を別の列で年月形式に表示することで、Google スプレッドシートのピボットテーブルで便利に集計できるようにしましょう。

C列に年月という列を作成し、
タイムスタンプの列の値を参照して、表示形式を「年月」となるようにしてみましょう。

このようにして年月の表示形式にして、別の回答が送信されるとどうなるでしょうか？送信してみます。

新しく回答を送信しても「年月」の列には何も表示されません。そりゃあそうです。新しい回答が送信されて追加された行には、数式を定義していなかったのです。

回答が追加されたとき、回答が存在する行すべてに対して関数を定義し、回答が存在しない行には何もしない、ということができたら便利なのですが…

まず、IF 関数を使用して、タイムスタンプ列に値が存在しているなら、その値を表示し、そうでないなら空白、という内容を定義しましょう。
こんな感じです。

=IF(A2<>"",A2,
"")

このように、各セルに対して関数を入力することで、タイムスタンプ列に入力があれば、年月列に値が表示され、タイムスタンプ列が空白なら、年月列も空白だという定義ができたかに思えますね。

新たにフォームの回答を送信してみましょう。

こんな風になるんです。ひどくない？
関数を定義した列の、左側に回答が送信されるのではなくて、
回答がある最下部に行を追加して、そこに回答が追加されるのです。
そういうことではないのです。
新しい回答の行が追加されたとき、最初から値を表示させるようにしたいのです。

そこで、ARRAYFORMULA 関数が登場するのです。
ARRAYFORMULA 関数は、範囲の開始セルにだけ定義した内容を、
その範囲すべてに反映させるという感じの関数です。
Excel でいうところの、スピルのようなものです。

年月列の先頭の行でEnter キーを押して編集モードにして、
Windows なら、Ctrl + Shift + Enter を押しましょう。
※なんにもならなかったら、手入力して

=ArrayFormula(IF(A2<>"",A2,""))

このようになります。

関数を、開始セルから下方向に列全体までとして定義します。
こんな感じに関数を編集しましょう。

=ArrayFormula(
IF(A2:A<>"",A2:A,"")
)

年月列にすでに入力した内容を消去してみましょう。
すると、なんということでしょう？

こうすることで、タイムスタンプが入力されている列には、タイムスタンプ列の内容が表示され、入力されていない列にはなにも表示されないということが実現できます。

列全体に対して、もう一度、表示形式を変更してみましょう。

この状態で、新しいフォームの回答を送信してみましょう。

新しい回答は、回答の送信があった最下部に追加され、
ARRAYFORMULA 関数によって処理結果が表示されるのですが、
表示形式が設定されていない状態（シリアル値）として表示されるのです。
惜しい、あと少し！

Google スプレッドシートにもExcel と同じように、マクロの記録機能があります。
列全体を指定して表示形式を変更するという操作を、マクロの記録で、記録してみましょう。

拡張機能＞マクロ＞マクロの記録をクリックしましょう

マクロとして記録したい操作を行い、完了したら「保存」ボタンをクリックします。

保存されたマクロは、
拡張機能＞マクロ＞マクロを管理
の箇所から、マクロ名をクリックすることで使用できます。

マクロの内容を確認したい場合は、
拡張機能＞ Apps Script
をクリックしてみましょう。

マクロを記録しただけでは、Google フォームから回答が送信されたとき、自動的にマクロを実行することはできません。
マクロを実行するために、
拡張機能＞マクロ＞マクロを管理＞マクロ名をクリック
という操作をするのは大変です。

そんな時は、
図形描画でボタンを作成し、そのボタンにGoogle Apps Script を紐づける
Google Apps Script のトリガーを、フォームが送信されたとき、として、フォームが送信されたら自動でマクロを実行するという方法で、さらなる効率化ができます。

スクリプトを割り当てましょう
※マクロの記録によって生成された Google Apps Script の名前を変更しています。

画像に対してスクリプトを割り当てたら、ボタンをクリックしてみましょう。
すると、指定したスクリプトが実行されます。

画像へのスクリプトの割り当てを変更したいときは、画像を右クリックして「…」をクリックしましょう。
画像をクリックすると、スクリプトが実行されるので、右クリックをしましょう。

Google スプレッドシートにもマクロの記録機能やスピルのような機能があるので、Excel でできていたことがGoogle スプレッドシートでもできるんだなあって、感心しますよね。
自分も、自分以外の人も、便利に効率的に編集できるように、いろんな機能を活用しましょう！

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