見出し画像

【解説動画・図解あり】Google フォームの回答のタイムスタンプを「年月」別に集計するための処理をマクロとARRAYFORMULA 関数で効率化する

Teeda

文章でわかりにくい人はショート動画も観てみて！

図解で理解したい人はこちら！

この記事はGoogle Workspace 標準機能 Advent Calendar 2022の22日目の記事です。

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

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

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


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

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

フォームの回答画面から回答を送信
送信された回答をGoogle スプレッドシートに記録

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

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

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

年月 を表示する列を作成。タイムスタンプ列の値を参照して表示。表示形式を「年月」に

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

新しい回答の行の「年月」には何も表示されない

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

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

ARRAYFORMULA とIF 関数でやってみよう

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

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

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

IF 関数で広い範囲に対して定義

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

回答の最下部に、新しい行が追加されちゃう

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

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

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

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

このようになります。

関数をARRAYFORMULA で囲む

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

=ArrayFormula(
IF(A2:A<>"",A2:A,"")
)
ARRAYFORMULA 関数で再定義。範囲にすでにデータが入力されているのでエラーが表示される

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

ARRAYFORMULA 関数によって、指定した範囲に処理結果が表示される

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

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

列全体を指定し、表示形式 ＞ 数字 ＞ 年月 の形式を指定

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

表示形式が設定されていない！？

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

マクロで表示形式を変更しよう

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

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

マクロを記録する

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

記録したい操作をする
保存ボタンをクリック後、マクロに名前を付けて、保存をクリック

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

拡張機能 ＞ マクロ ＞ マクロを管理 の中にマクロがある

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

Apps Script にマクロの記録 による処理が定義されている

さらなる効率化

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

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

挿入 ＞ 図形描画 で図形を挿入し、挿入した図形の右上「…」＞スクリプトを割り当て をクリック

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

スクリプトを割り当てて、確定
名前を変更して保存した マクロの記録によって生成されたGoogle Apps Script

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

ボタンをクリックすると、年月 列の表示形式が変更される

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

まとめ

Google Workspace 標準機能 Advent Calendar 2022の22日目の記事でした。

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

いいなと思ったら応援しよう！