タイダログ

もっと怠けますか？ (y/n)

トップ > 業務 > Google フォームの「回答の並べ替え→抽出→色分け」を自動化する

Google フォームの「回答の並べ替え→抽出→色分け」を自動化する

業務 業務-効率化

Google フォームの回答を出席番号順に並べ替えたりフィルターをかけたり色分けしたりする憂鬱で溜息が出るほど退屈な作業を消失させるべく、自動化の方法をまとめました。

GAS は使わず、関数と条件付き書式で実現します。

Microsoft Forms 版はこちら。

taidalog.hatenablog.com

taidalog.hatenablog.com

作業環境

やりたいこと

  1. アンケートや欠席連絡等を Google フォームで受け付ける
  2. 回答を Google スプレッドシートで閲覧する
  3. 当日の回答だけを取り出し、「学年→クラス→番号」で自動ソートする
  4. 回答を学年ごとに色分けする
  5. 3 と4 をリアルタイムかつ自動で行う

完成図

完成図

※ このページに登場する人名は、「たいだ」と「まじめ」以外は「なんちゃって個人情報」さんで作成しました。ありがとうございました。
なんちゃって個人情報

手順

  1. Google にログインする
  2. Google フォームでアンケートを作成する
  3. フォームの回答をスプレッドシートに紐づける
  4. フォームに回答を入力してみる
  5. スプレッドシートに数式を入力する
  6. スプレッドシートをカスタマイズする
  7. フォームの回答用 URL を取得する
  8. スプレッドシートを閲覧専用で共有する
  9. 引き継ぎをする

Google にログインする

まずは Google にログインします。Google のトップページを開いた時に、画面右上に自分の名前やアイコンが出ていれば既にログインしています。

ログインしていない場合は、画面右上の「ログイン」の文字をクリックしてください。仕事で使うなら職場のアカウントでログインしてくださいね。

Google フォームでアンケートを作成する

Google のトップページの画面右上にある点9個のボタンをクリックしてください。Google のサービス一覧が出ます。ちなみにこの点9個のボタンをチョコレートボタンと呼ぶそうです。

この一覧から「フォーム」を探してクリックすると、「Google フォーム」というページに移動します。

Google フォームを開く図

左上にある「＋」ボタン（空白と書いてあるやつ）をクリックしてください。そうするとフォームの作成画面が開きます。

「空白」をクリックする図

フォームを編集する前に、真っ先にタイトルを付けてください。これは回答者から見えるタイトルです。今回は「コース希望調査」としました。

タイトルを付ける図

次に、ファイル名を付けましょう。画面左上の「無題のフォーム」という文字をクリックしてください。すると先程決めたタイトルに変わるはずです。

ファイル名を付ける図

「タイトル」が回答者から見える名前なのに対して、「ファイル名」は回答者からは見えません。年度を入れるなど、適宜変えても大丈夫です。

ここまで出来たら質問を追加してください。今回は例として以下の質問を設定しました。

  • 学年
  • クラス
  • 番号
  • 名前
  • 希望コース
  • 希望日

全体像

注意
Web フォームを用いて情報を集める際は、教育委員会や学校ごとのセキュリティポリシー等を確認の上、禁則事項に触れないようにお気をつけください。
この記事を参考にしたことによるいかなる不利益に対しても私は責任を負いません。自己責任でお願いします。

目的に合わせて思う存分やっていただければいいのですが、質問は短く、できれば単語にするといいと思います。「自分の番号を2桁で入力してください。」よりも「番号」の方がいいということです。

なぜかというと、後ほどスプレッドシートで回答を閲覧する際に、質問が長いとセルに収まらないからです。

質問が長い図

質問がセルに収まっていない図

どうしても文で書きたければ「説明」に書きましょう。

説明に書く図

このフォームの保存場所は、デフォルトでは Google ドライブの「マイドライブ」です。保存場所はファイル名の右のフォルダアイコンをクリックするとわかります。

フォルダアイコンの図

フォームの回答をスプレッドシートに紐づける

フォーム編集画面の上部にある [回答] → [スプレッドシートにリンク] → [作成] の順でクリックしてください。新しいスプレッドシートが開きます。

スプレッドシートとの紐付けの図 (1)

スプレッドシートとの紐付けの図 (2)

先ほどのフォームへの入力内容は、このシートに転記されます。フォームの質問がそのまま列の見出しになっていますね。タイトルを長くしすぎるとシートが見辛くなりますので短くしましょう。

ちなみに「タイムスタンプ」はフォームの質問に関係なく自動で入る項目です。タイムスタンプの列を削除してしまうと不都合なことになります。具体的には、今回のテーマの「日付での抽出」ができなくなります。絶対に削除しないでください。非表示にするのは OK です。

スプレッドシートと紐付いた図

もし [スプレッドシートにリンク] が [スプレッドシートで表示] だった場合、すでに紐付けができています。クリックしてスプレッドシートを開いてください。

フォームの質問の順番を変更した場合は、スプレッドシートを作り直さないと順番が反映されません。以下の手順で作り直してください。

  1. フォームの [回答] → [スプレッドシートで表示] の右の点3つのボタン → [フォームのリンクを解除] の順でクリック
  2. スプレッドシートのファイルを削除（シートではなくファイル）
  3. 再度フォームで [回答] → [スプレッドシートにリンク] → [作成]

上記の通りに操作すると、変更後の順番を反映したスプレッドシートができるはずです。それまでの回答は消えません。ただし、フォームの運用中にこれをするのは怖いので、長期休業中等に行うことをお勧めします。

フォームに回答を入力してみる

スプレッドシートに数式を入力する前に、フォームにサンプルデータを入力しましょう。

フォームの編集画面に戻って、右上の「プレビュー」から何件か回答を入力してみてください。するとスプレッドシートにその回答が入るはずです。

シートに回答が入った図

スプレッドシートに数式を入力する

それでは、出席番号順に並べ替えたりフィルターをかけたりする数式を入力しましょう。

現在、スプレッドシートには [フォームの回答 1] というシートのみがある状態だと思います。このシートは回答の元データが溜まっていく場所ですので、編集は厳禁です（非表示にしておきたいくらいです）。

古い回答を削除する？　とんでもない！　数式が壊れます！

[フォームの回答 1] シートには回答がひたすら溜まっていくだけにして、並べ替え等の作業は別シートで行います。

シート左下の「＋」ボタンをクリックしてシートを追加してください。シート名は [シート1] になっていると思います（変更しても大丈夫です）。数式はこのシートに入力します。

[シート1] の A1 セルに以下の通り入力し、

=ArrayFormula('フォームの回答 1'!1:1)

A1 セルに数式を入力した図

同じく [シート1] の A2 セルに以下の数式を入力してください。

=IFERROR(SORT(FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A>=TODAY()),2,TRUE(),3,TRUE(),4,TRUE()),"まだ回答がないようです。")

A2 セルに数式を入力した図

もし日付によるフィルターが不要で、学年等でソートするだけでいい場合、A2 セルには以下の数式を入力してください。

=SORT('フォームの回答 1'!A2:G,2,TRUE(),3,TRUE(),4,TRUE())

数式の入力はこれでおしまいです。もう少し説明が見たい方は「数式の説明」にワープしてどうぞ。別によければこのまま進んでください。

スプレッドシートをカスタマイズする

セルの表示形式を変更する

日付が入っている列を見てください。下の画像では A 列と G 列です。おかしなことになっていますね……暴走？

日付がおかしい図

これは、「シリアル値」が直接セルに出ている状態です。シリアル値とは、スプレッドシート上で日時を表すために用いる数値です。たとえば「2023年4月8日午後12時29分15秒」は「45025.85695」のように表されます。整数部分が日付、小数点以下が時刻です。

これでは訳がわかりませんので表示形式を変えましょう。A 列の見出しをクリックして列全体を選択し、以下の順でクリックします。

[表示形式] タブ → [数字] → [日時]

列全体の表示形式を変える図

G 列も変更しましょう。G 列の見出しをクリックして列全体を選択し、表示形式を変えます。G 列は時刻がいらないので [日付] 形式にしましょう。

[表示形式] タブ → [数字] → [日付]

設定が終わるとこうなります。これで訳がわかる。

表示形式を変えた図

条件付き書式

文字だけでは見辛いので、条件付き書式で学年別に色分けしましょう。

[シート1] のA2 セルから G101 セルまでを範囲選択した状態で、[表示形式] タブ → [条件付き書式] の順でクリックしてください。

条件付き書式の設定の図

画面右側に「条件付き書式設定ルール」が出現するので、以下のように各学年分の色を設定してください。

項目 設定内容
範囲に適用 A2:G101
セルの書式設定の条件… カスタム数式
値または数式 =$B2=1
書式設定のスタイル 「塗りつぶし」で学年カラーを指定

学年で色分けした図

3学年分の設定をした図

もちろん新たに入ってきた回答にも色が付きます。

新しい回答にも色が付いた図

シートを目立たせる

確実に自動ソートシート ([シート1]) を開いてほしいので、シートを右クリックして名前をつけて、見出しに色を付けましょう。

シート見出しに名前と色を付けた図

元データシートを隠す

閲覧限定で共有するとはいえ（後述）、万が一にも元データを変更されては困ります。元データを保護するには [フォームの回答 1] を右クリックして [シートを非表示] で非表示にしておくのが手っ取り早いと思います。

元データのシートを非表示にした図

回答データの向きを変える

(2023/04/29 追記ここから)

質問項目が増えたり、記述式の質問が複数あったりした場合、一人分の回答が横長になっていると読みにくいと感じることがあると思います。

回答が横長で読みにくい図

TRANSPOSE 関数を使って回答の縦横を入れ替えて、回答を読みやすくする方法を書きます。必要に応じてやってみてください。

まず、新しいシートを作り、A1 セルに以下の数式を入力し、

=TRANSPOSE(ArrayFormula('フォームの回答 1'!1:1))

B1 セルに以下の数式を入力してください。

=TRANSPOSE(IFERROR(SORT(FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A>=TODAY()),2,TRUE(),3,TRUE(),4,TRUE()),"まだ回答がないようです。"))

そうすると、このように回答の縦横が入れ替わった状態になるはずです。

回答の縦横が入れ替わった図

縦横の入れ替えはこれだけです。あとはこの記事の「セルの書式設定を変更する」と「条件付き書式」、「シートを目立たせる」を参考にシートの見た目を整えて、少しだけ追加の設定をしたら完成です。

手順をすでに書いた箇所は、前述の内容との相違点のみを書きます。

まず、書式設定の変更は、以下の図のように日時や日付の入った行全体を選択して行ってください（縦横を入れ替えたので、日時や日付が横一列に並んでいます）。今回の例では 1 行目と 7 行目を変更します。行全体を選択するには、行番号の部分をクリックします。

行全体の表示形式を変える図

日時がセルに収まらないと思いますので、列の幅を適当に広げてください。

日時が収まるように列の幅を広げた図

条件付き書式（学年の色分け）は、B1 セルをクリックした状態で [表示形式] タブ → [条件付き書式] の順でクリックして以下のように設定してください。

項目 設定内容
範囲に適用 B1:7
セルの書式設定の条件… カスタム数式
値または数式 =B$2=1
書式設定のスタイル 「塗りつぶし」で学年カラーを指定

※ [範囲に適用] には B1:7 と入力しますが、[完了] をクリックすると B1:Z7 のような範囲に置き換わります。それで結構です。
※ [値または数式] の数式は、学年に応じて =B$2=2 などに変更してください。

学年で色分けした図

3学年分の設定をした図

ここまでが既出の内容です。加えて、以下のような記述式の質問を見やすくするためにあと2点だけ設定します。

記述式の質問の図

まず、全ての質問の行全体を選択します。今回は 1 行目から 8 行目まで全部です。

1 行目の行番号の部分をクリックしたらマウスから指を離さずにそのまま 8 行目までマウスを動かしてください（ここまで一息で言う）。

行全体を選択した状態で下図の通り [テキストを折り返す] → [折り返す] の順でクリックしてください。

複数行を選択してテキストを折り返す図

そのまま、行全体を選択した状態で、下図の通り [垂直方向の配置] → [上] の順でクリックしてください。セル内でテキストが上揃えになります。

複数行を選択してテキストを上揃えにする図

こうなるはずです。

こうなるは図

記述式の質問が増えても、縦だとそれなりに見やすいのではないでしょうか。

記述式の質問が増えた図（縦）

横のままだとこうです。

記述式の質問が増えた図（横）

視線の移動距離が抑えられて、視認性が向上していると思います。

(2023/04/29 追記ここまで)

これでスプレッドシートの作成は完了です。お疲れ様でした！

フォームの回答用 URL を取得する

フォームにアクセスして回答してもらえるようにしましょう。

画面右上の [送信] ボタン → [リンクマーク] → [コピー] の順でクリックしてリンクを取得してください。これを相手に送れば回答してもらえます。「URL を短縮」にチェックを入れると、紙に URL を併記するのによいです。

回答用 URL を取得する図

スプレッドシートを閲覧専用で共有する

スプレッドシートを閲覧限定で共有して、複数人で回答を見られるようにしましょう。方法は2つあります。

1つ目の方法は、閲覧用リンクを共有する方法です。楽ですが、万が一リンクが外部に漏れると情報漏洩に繋がります。

2つ目の方法は、ユーザーを指定してアクセス権を与える方法です。手間がかかりますが、外部に漏れる可能性はぐっと低くなるはずです。

1つ目の方法、閲覧用リンクを共有する手順は以下の通りです。

画面右上の [共有] ボタンをクリックすると以下のような画面が出てきます。

共有画面の図

この画面の「制限付き」の部分をクリックし、「リンクを知っている全員」をクリックします。

リンクの設定画面の図

戻った画面の左下の「リンクのコピー」をクリックして、共有用の URL を取得してください。これを然るべき相手に然るべき方法で渡せば、ここまでに作成したスプレッドシートを閲覧してもらうことができます。

リンクのコピーの図

2つ目の方法、ユーザーを指定してアクセス権を与える手順は以下の通りです。

画面右上の [共有] ボタンをクリックすると以下のような画面が出てきます。

共有画面の図

この画面の「ユーザーやグループを追加」の部分に共有相手の Google アカウントを打ち込み、右下の「送信」をクリックします。全員分です。仕事の場合は職場のアカウントを使ってくださいね。権限を「閲覧者」にすることをお忘れなく。

アクセス権付与の図

実は、職場の全員に対して比較的楽にアクセス権を付与する方法があるので、また今度書きます。

とりあえず、これで作業は終了です。お疲れ様でした！

「引き継ぎをする」は、必要なタイミングで行ってください。

引き継ぎをする

異動や退職等で必要になったら行ってください。
※引き継げるのは質問だけです。回答やスプレッドシートは引き継げきません。

引き継ぎと言っていますが、作成したフォームをそのまま他の人に譲渡することはできないようです。以下の手順でフォームを複製して使ってもらいます。

  1. 旧担当者が、新担当者をフォームの協働編集者に追加する
  2. 新担当者が、フォームのコピーを作成する
  3. 新担当者が、コピーしたフォームで「フォームの回答をスプレッドシートに紐づける」以降の作業を行う

協働編集者への追加とフォームのコピーの作成は、こちらのページが分かりやすいです。

fucchan-blog.com

数式の説明

以下、説明兼覚書です。時間のある時にお読みください。

ArrayFormula() 関数

[シート1] シートの A1 セルに

='フォームの回答 1'!1:1

と入力すると、[フォームの回答 1] シートの A1 セルしか表示されません。数式を A1 セルにしか入力していないからです。

ここで、[シート1] シートの A1 セルに

=ArrayFormula('フォームの回答 1'!1:1)

と入力すると、[フォームの回答 1] シートの1行目全て（今回は A1 セルから G1 セルまで）が [シート1] シートの1行目に表示されます。

ArrayFormula 関数を使うと、ひとつのセルに入力した数式の結果を、複数のセルに展開することができます。Excel の配列数式やスピルにあたるものです。

support.google.com

A2:A

Excel でもスプレッドシートでも、A:A で A 列全体を指定できますが、スプレッドシートでは A2:A で「A2 セルから A 列の一番下のセルまで」を指定できます。便利ですね、この書き方。

日時でフィルターをかける

フォームの回答には、「タイムスタンプ」という名前で、回答した日時が付きます。この日時でフィルターをかければ、その日の回答のみを取り出すことや、特定の日以降の回答を取り出すことなどができます。欠席連絡のように毎日実施しているアンケートなどで役に立つと思います。

日時でフィルターするのは少しややこしいので頑張ってください。

当日の回答だけフィルターする数式

当日の回答だけフィルターする数式は以下の2つが考えられます。2つの数式は同じ結果になります1。

=FILTER('フォームの回答 1'!A2:G,INT('フォームの回答 1'!A2:A)=TODAY())
=FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A>=TODAY())

TODAY 関数は今日の日付を返し、INT 関数は数値の小数点以下を切り捨てて整数にします。

INT 関数を使っているのは、日時の「シリアル値」の整数部分を取得するためです。シリアル値とは、スプレッドシート上で日時を表すために用いる数値です。たとえば「2023年4月8日午後12時29分15秒」は「45025.85695」のように表されます。整数部分が日付、小数点以下が時刻です。今回は日付部分（整数部分）のみが必要なので、時刻（小数点以下）を切り捨てます。詳しくはお調べください。

以下の数式は #N/A エラーになるか、思った結果になりません。

=FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A=TODAY())

TODAY 関数は現在の日付を返す関数です。2023年4月9日に使えば 2023/04/09 00:00:00（を表すシリアル値、45025）を返します。時刻の部分は 00:00:00 ぴったりになっていますので、時刻の部分がタイムスタンプと一致せず、該当データなし (#N/A) になります。今日の 00:00:00 ぴったりに回答した人がいれば別ですが。

特定の日の回答だけフィルターする数式

どこか別のセル（ここでは A1 セル）に日付を入力しておいて、それを参照します。

=FILTER('フォームの回答 1'!A2:G,INT('フォームの回答 1'!A2:A)=A1)

特定の日の回答だけ取り出した図

A 列のタイムスタンプが日付＋時刻の「日時」であるのに対し、A1 セルが時刻なし（厳密には時刻が 00:00:00）の「日付」のため、'フォームの回答 1'!A2:A=A1 では一致しません。そのため INT 関数でタイムスタンプの時刻を切り捨てて「日付」にすることでマッチさせています。

数式に直接日付を入れることは、「出来なくはない。ただし推奨は出来ない」という感じです。DATEVALUE 関数を使います。ただし長いし書き換えがめんどうなのでおすすめしません。

=FILTER('フォームの回答 1'!A2:G,INT('フォームの回答 1'!A2:A)=DATEVALUE("2023/04/08"))

DATEVALUE 関数を使わないとエラーになります（↓この数式はエラーを返す）。日時はシリアル値として扱いましょう。というか日付を指定するなら別のセルに入れましょう。

=FILTER('フォームの回答 1'!A2:G,INT('フォームの回答 1'!A2:A)="2023/04/08")

特定の日以降の回答だけフィルターする数式

特定の日「以降」の回答を抽出する場合は、以下のように、数式の最後の =C1 または =DATEVALUE() の = を >= に書き換えます。INT 関数は不要です。

=FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A>=A1)
=FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A>=DATEVALUE("2023/04/01"))

ソートしてフィルターする数式

今日のデータだけフィルターして、「学年→クラス→番号」の順でソートします。エラー処理を考えなければ、以下の数式でできます。楽ですね。2

=SORT(FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A>=TODAY()),2,TRUE(),3,TRUE(),4,TRUE())

内側から見ていきましょう。

まずは FILTER 関数で、

FILTER('フォームの回答 1'!A2:G,'フォームの回答 1'!A2:A>=TODAY())

'フォームの回答 1'!A2:G のデータのうち、'フォームの回答 1'!A2:A（タイムスタンプ）が TODAY()（今日の 00:00:00）以降のものを抽出します。

次に SORT 関数で、

=SORT(<FILTER関数の部分>,2,TRUE(),3,TRUE(),4,TRUE())

FILTER 関数の結果をソートします。2,TRUE(),3,TRUE(),4,TRUE() と指定しているので、左から 2 列目（学年）を昇順でソートし、さらに 3 列目（クラス）を昇順でソートし、さらに 4 列目（番号）を昇順でソートしています。

FILTER 関数でフィルタした結果、該当するデータがなかった場合エラーになるので、IFERROR 関数で「まだ回答がないようです。」等と表示するといいと思います。

余談ですが、Excel では「学年」「クラス」「番号」を数値として扱うために VALUE 関数が必要でした。また、FILTER 関数の戻り値を SORTBY 関数に渡すのがややこしくて、LET 関数まで持ち出してこのような数式になっていました（もっと楽に書ける方、教えてください）。

=LET(sortedTable,SORTBY(T_form,VALUE(T_form[学年]),1,VALUE(T_form[クラス]),1,VALUE(T_form[番号]),1),FILTER(sortedTable,INDEX(sortedTable,0,3)>=TODAY()))

「SORTBY 関数内で VALUE 関数を使って「学年」「クラス」「番号」を数値化してソートし、その結果を LET 関数内で sortedTable という変数に格納して、FILTER 関数に渡して、INDEX 関数で列を指定して、シリアル値が TODAY 関数以上のものを抽出する数式」です。ナゾナゾみたいに数式を解き明かすの スキでしょう？

自由記述の結果を COUNTIF する数式

ネタです。私の推し関数 COUNTIF 関数を使いたかったんです。

グラデーション文字の作成はこちらのサイト様を使わせていただきました。ありがとうございました。

sunafukey.fc2web.com

好きな海の生き物のアンケートを取り、以下の回答が集まったとします。

好きな海の生き物の回答の図

シートを追加し、A1 セルと B1 セルに見出しを付け、

新しいシートに見出しを付けた図

A2 セルに以下の数式を入れて、

=UNIQUE('フォームの回答 1'!B2:B)

A2 セルに数式を入れた図

B2 セルに以下の数式を入れます。

=ArrayFormula(COUNTIF('フォームの回答 1'!B2:B,OFFSET(A2,0,0,COUNTA(A2:A))))

B2 セルに数式を入れた図

あれ、カタカナの「ペンギン」が3件で、ひらがなの「ぺんぎん」が2件のはずなのに、どちらも5件になってる……え、スプレッドシートの COUNTIF 関数は大文字と小文字やひらがなとカタカナの区別をしないの！？

support.google.com

まあその方が都合がいいときもあるでしょうが……そうですか……ちょっと動揺してしまいました。

ちなみに、単に回答の集計をするだけならフォームの [回答] を見ればよいです。グラフ化もしてくれて超便利。

[回答] で集計結果を見る図

結び

えぇ……大文字と小文字やひらがなとカタカナを区別しないの……COUNTIF 関数……

区別しない方が便利なときもあるだろうけど、ひらがなとカタカナは異なる文字なんだから、区別するのが正しいんじゃない？

便利なのはわかるけどさ、たとえば =COUNTIF(A2:A,"ペンギン",FALSE()) みたいに第3引き数で区別するかどうかを選ばせてくれるとかさ……そういう風にしてほしかったな。

重複して数えてるから合計が変わってるよ？　それでいいの？

Excel の時は区別してくれたじゃん。あの時とは別人なの？

初めから区別する気がないっていうのは……

ちょっとどうかと思うな……

……まぁそれでも好きなんですけどね。

そして PowerShell も大文字小文字の区別が雑なんですけどね！

"Penguin" -eq "penguin"
# True

F# はちゃんと区別してくれる。

"Penguin" = "penguin"
// false

参考

更新履歴

  1. 厳密に言うと 'フォームの回答 1'!A2:A>=TODAY() は「タイムスタンプのシリアル値が今日の 00:00:00 の値以上である」という意味ですので、翌日以降を含んでしまいます。ですがタイムスタンプが翌日以降になっているなんてことは、タイムトラベルか時空操作か超能力はたまた何かの陰謀でもないと起こらないでしょうから、今回は問題ないことにしました。宇宙人、未来人、異世界人、超能力者が在籍している学校さんにおかれましては INT 関数をお使いください。↩
  2. 楽とは。↩