ここ数年で Microsoft Forms などの Web フォームでアンケートを取ることが増えてきました。いちいち回答の一覧をダウンロードして出席番号順に並べ替えたりフィルターをかけたりする作業が憂鬱で溜息が出るほど退屈なので、消失させるべく自動化の方法を調べてまとめました。
VBA は使わず、関数と条件付き書式で実現します。
Forms と Excel のそれぞれで操作が必要です。この記事は Excel 編です。Forms 編はこちら。
本記事は、過去の記事を加筆修正したものです。
Microsoft Forms の「回答の並べ替え→抽出→色分け」を自動化する【Excel 編】 - タイダログ
動作環境
- Microsoft 365
やりたいこと
- アンケートや欠席連絡等を Microsoft Forms で受け付ける
- 回答を Excel ファイルで閲覧する
- 当日の回答だけを取り出し、「学年→クラス→番号」で自動ソートする
- 回答を学年ごとに色分けする
- 3 と4 をリアルタイムかつ自動で行う
完成図
※ 氏名は「なんちゃって個人情報」さんで作成しました。ありがとうございました。
なんちゃって個人情報
手順
- Excel ファイルを作成する（Forms 編）
- Excel ファイルにフォームを追加する（Forms 編）
- Forms の回答用 URL を取得する（Forms 編）
- 数式を入力する
- カスタマイズする
- Excel の閲覧用 URL を取得する
- フォームの引き継ぎをする（Forms 編）
Excel ファイルを作成する
Forms 編で紹介しました。
Excel ファイルにフォームを追加する
Forms 編で紹介しました。
Forms の回答用 URL を取得する
Forms 編で紹介しました。
数式を入力する
今回の並べ替えと抽出の自動化には、Microsoft 365 および Excel 2021 以降で利用可能な SORT 関数や SORTBY 関数、FILTER 関数、LET 関数を使います。
デスクトップ版の Excel で開く
ファイルの作成は Excel Online で行いましたが、数式の編集はデスクトップ版の Excel の方が楽です。というより、一部の機能は Excel Online では使えないようなのでデスクトップ版がほぼ必須です。Excel Online の画面上部にあるタブの右の方の [編集] ボタンの「デスクトップ アプリケーションで開く」をクリックします。
テーブル名を変更する
フォームへの回答は、Excel ファイルのテーブルに転記されます。このことについては Forms 編の「回答の行き先」および「フォームに回答を入力してみる」を参照してください。テーブルについての説明は Excel 編の「テーブルについて」へどうぞ。
テーブルにはデフォルトで「テーブル1」という名前が付いていますが、さすがにこれでは愛着が湧かない上に、全角だと数式に入力しづらいので、今回は T_form に変えます。[テーブル デザイン] タブを開いて左端にある [プロパティ] グループ内の「テーブル名」から変更できます。
数式を入力する
※ 数式は、テーブルがある 'Form1' シートではなく、白紙の 'Sheet1' シートに入力してください。
※ テーブル名は 'T_form' に変更してあるものとします。
※ テーブルの見出し（≒フォームの質問のタイトル）は、以下の通りになっているものとします。
| 列 | 見出し |
|---|---|
| A | ID |
| B | 開始時刻 |
| C | 完了時刻 |
| D | メール |
| E | 名前 |
| F | 学年 |
| G | クラス |
| H | 番号 |
| I | 氏名 |
| J | 希望コース |
| K | 希望日 |
'Sheet1' シートの A1 セルに以下の通り入力し、
=T_form[#見出し]
同じく 'Sheet1' シートの A2 セルに以下の数式を入力してください。
=LET(sortedTable,SORTBY(T_form,VALUE(T_form[学年]),1,VALUE(T_form[クラス]),1,VALUE(T_form[番号]),1),FILTER(sortedTable,INDEX(sortedTable,0,3)>=TODAY()))
もし日付によるフィルターが不要で、学年等でソートするだけでいい場合、A2 セルには以下の数式を入力してください。
=SORTBY(T_form,VALUE(T_form[学年]),1,VALUE(T_form[クラス]),1,VALUE(T_form[番号]),1)
セルの表示形式を変更する
日付が入っている列を見てください。下の画像では B 列、C 列、および K 列です。おかしなことになっていますね……暴走？
Excel 内では日時を「シリアル値」という数値として扱っています。整数部分が日付を、小数点以下が時刻を表しています。現在、そのシリアル値が直接セルに出ている状態です。
これでは訳がわかりませんので表示形式を変えましょう。日付のセル全体を選択して、以下の順でクリックします。
[ホーム] タブ > [数値] グループの [数値の書式] プルダウンボックス > 「その他の表示形式(M)」
「セルの書式設定」ダイアログで、このようにしてください。
時刻がいらない列は「短い日付形式」にしましょう。
[ホーム] タブ > [数値] グループの [数値の書式] プルダウンボックス >「短い日付形式」
設定が終わるとこうなります。これで訳がわかる。
数式の入力はこれでおしまいです。もう少し説明が見たい方は「説明」にワープしてどうぞ。別によければこのまま進んでください。
カスタマイズする
条件付き書式
文字だけでは見辛いので、条件付き書式で学年別に色分けしましょう。
もちろん新たに増えた回答にも色が付きます。
シートを目立たせる
確実に自動ソートシート ('Sheet1') を開いてほしいので、シートに名前をつけて見出しに色を付けましょう。
テーブルシートを隠す
閲覧限定で共有するとはいえ（後述）、万が一テーブルのデータを変更されては困ります。元データを保護するにはテーブルのシート ('Form1') を非表示にしておくのが手っ取り早いと思います。
ここでひとつ注意してください。このファイルを開くと、Excel はテーブルのシート ('Form1') を開こうとするようです。そこで 'Form1' が非表示にしてある場合どうなるかというと……セルが真っ白になります。
（自宅の Excel でそれっぽく再現しました。実際は白いです）
どこまでも広がる色彩のない平面。どことなく閉鎖的な空間ですね。
こんなものを見た人は動揺してしまうでしょうから、先ほどのように自動ソートシートの見出しを赤く目立たせておきましょう。
Excel の閲覧用 URL を取得する
Excel ファイルを閲覧限定で共有しましょう。画面右上の [共有] ボタンをクリックすると以下のような画面が出てきます。
この画面の「リンクを知って入れば誰でも編集できます」の部分をクリックし、次の画面を開きます。
この画像にはありませんが、「このリンクを使用できる対象ユーザー」の中に「リンクを知っている○○のユーザー」というのがあると思いますので、それを選択してください（○○には自治体名などが入っていると思います）。加えて「その他の設定」の「編集を許可する」のチェックを外します。最後に「適用」をクリックします（ひとつ前の画面に戻ります）。
戻った画面の左下の「リンクのコピー」をクリックして、共有用の URL を取得してください。これを然るべき相手に然るべき方法で渡せば、ここまでに作成した Excel ファイルを閲覧してもらうことができます。
とりあえず、ここまでできれば作業は終了です。お疲れ様でした！
「フォームの引き継ぎ」は、必要なタイミングで行ってください。
フォームの引き継ぎをする
Forms 編で紹介しました。
説明
以下、説明兼覚書です。時間のある時にお読みください。
テーブルについて
フォームへの回答は、以下のような青い領域に転記されます。この領域を「テーブル」と呼びます。
一見ただのおしゃれな色付きセルですが、強力な機能をいくつも備えています。今回関わってくる機能はこの辺りです。
- セルのアドレスではなく、テーブル名で範囲を指定できる
- 列番号ではなく、見出しで列を指定できる
- データの追加に合わせて、範囲が自動で広がる
新たに作成したテーブルには「テーブル1」のような連番の名前が付いています。数式内でセル範囲を指定するとき、=COUNT(Form1!A2:K11) のようにセルのアドレスで指定するのではなく =COUNT(テーブル1) という名前で指定することができます。さらに、このテーブルの「学年」の列を指定する場合も、=COUNT(Form1!F2:F11) ではなく =COUNT(テーブル1[学年]) というふうに、テーブル名[列の見出し] の形で指定できます。これにより数式が読みやすくなり、エラーの修正も楽になることが期待できます。
数式のエラーに時間を取られた挙句、=COUNT(Form1!A2:K11) のようなアドレスの指定が間違っていて憤慨した経験が、誰しも幾度となくあるかと思います。表にデータを追加したときに数式を更新しないとそうなってしまうのですが、テーブルを使うとデータに合わせて自動で範囲が広がるため、そのようなエラーを防げます。
構造化参照
先程も触れましたが、セルに次の通り入力すると、テーブルの見出し行を取り出すことができます。
=T_form[#見出し]
セルに次の通り入力すると、テーブルの「氏名」列を取り出すことができます。
=T_form[氏名]
こういうのを「構造化参照」と呼びます。他にもいろんなことができます。調べてみてください。
テーブル名を途中まで入力すると候補が出ます。「テーブル名は T_ で始める」という風に統一しておくと候補を出しやすくなるのでおすすめです。矢印キーで選択して、Tab キーで確定できます。
※ Excel Online ではできないようです
※ 図は、1つのファイル内で2つのテーブルを扱っている例です
数式について
文字列を数値としてソートする
以下のようなデータが入っているテーブル T_form を
「番号」列で昇順にソートすると、
=SORTBY(T_form,T_form[番号],1)
次の結果になります。
順番がおかしいですね……22番の「たいだ」が4番の「まじめ」より先に来ています。一度テーブルを見てみましょう。
「番号」の先頭にシングルクォーテーション (') が付いています。これは数値を文字列にする記号です。数値としては 4 の方が先ですが、文字列になると "22" の方が先になります。期待する並び順にするために、文字列を数値に変換する VALUE 関数を使いましょう。
=SORTBY(T_form,VALUE(T_form[番号]),1)
日時でフィルターをかける
Forms の回答には、「開始時刻」と「完了時刻」という名前で、回答した日時が付きます。この日時でフィルターをかければ、その日の分の回答のみを取り出すことや、特定の日以降の回答を取り出すことなどができます。欠席連絡のように毎日実施しているアンケートなどで役に立つと思います。
日時でフィルターするのは少しややこしいので頑張ってください。
当日の回答だけフィルターする数式
当日の回答だけフィルターするには、FILTER 関数と、今日の日付を返す TODAY 関数、それから数値の小数点以下を切り捨てて整数にする INT 関数を使います。以下の2つの数式は同じ結果になります1。
=FILTER(T_form,INT(T_form[完了時刻])=TODAY())
=FILTER(T_form,T_form[完了時刻]>=TODAY())
INT 関数を使っているのは、日時のシリアル値の整数部分を取得するためです。Excel 内では日時を「シリアル値」という数値として扱っています。整数部分が日付を、小数点以下が時刻を表しています。今回は日付部分のみが必要なので、小数点以下を切り捨てます。詳しくはお調べください。
以下の数式は #CALC! エラーになるか、思った結果になりません。
=FILTER(T_form,T_form[完了時刻]=TODAY())
TODAY 関数は現在の日付を返す関数です。2022年2月1日に使えば 2022/02/01 00:00:00（を表すシリアル値、44593）を返します。時刻の部分は 00:00:00 ぴったりになっていますので、時刻の部分が「完了時刻」と一致せず、該当データなし (#CALC!) になります。今日の 00:00:00 ぴったりに回答した人がいれば別ですが。
特定の日の回答だけフィルターする数式
どこか別のセル（ここでは C1 セル）に日付を入力しておいて、それを参照します。
=FILTER(T_form,INT(T_form[完了時刻])=C1)
数式に直接日付を入れることは、「出来なくはない。ただし推奨は出来ない」という感じです。DATEVALUE 関数を使います。ただし長いし書き換えがめんどうなのでおすすめしません。
=FILTER(T_form,INT(T_form[完了時刻])=DATEVALUE("2022/2/1"))
DATEVALUE 関数を使わないとエラーになります（↓この数式はエラーを返す）。日時はシリアル値として扱いましょう。というか日付を指定するなら別のセルに入れましょう。
=FILTER(T_form,INT(T_form[完了時刻])="2022/2/1")
特定の日以降の回答だけフィルターする数式
特定の日「以降」の回答を抽出する場合は、以下のように、数式の最後の =C1 または =DATEVALUE() の = を >= に書き換えます。INT 関数は不要です。
=FILTER(T_form,T_form[完了時刻]>=C1)
=FILTER(T_form,T_form[完了時刻]>=DATEVALUE("2022/1/31"))
ソートしてフィルターする数式
今日のデータだけフィルターして、「学年→クラス→番号」の順でソートします。
これでできれば楽なんですが2、
=SORT(SORT(SORT(FILTER(T_form,T_form[完了時刻]>=TODAY()),8,1),7,1),6,1)
「番号」を数値化する必要があるためこうしています。一応「学年」と「クラス」も数値化しておきましょう。一桁なら不要ですけど。一応ね。
=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 関数を使いたかったんです。
グラデーション文字の作成はこちらのサイト様を使わせていただきました。ありがとうございました。
この数式を適当なセルに入れて、
※テーブルに隣接するセルは避けてください。理由はやってみたらわかります。
=UNIQUE(T_kanji[今年の漢字予想])
こちらをひとつめのセルの右隣に入れます。H2 はひとつめのセルに合わせて変えてください。
=COUNTIF(T_kanji[今年の漢字予想],H2#)
H2# の # は「スピル範囲演算子」といいます。お調べあそばせ。
結び
データが自分からやってくる。そしてこちらの都合のいいように並んでくれる。そんな喜ばしいことが、関数を数個並べるだけで実現出来るんですよ。これはもうExcel 関数を始めるしかない！
参考
- SORT 関数 - Microsoft サポート
- SORTBY 関数 - Microsoft サポート
- FILTER 関数 - Microsoft サポート
- LET 関数 - Microsoft サポート
- UNIQUE 関数 - Microsoft サポート
- excel - Sortby function combined with Unique and filter functions: how to use it correclty? - Stack Overflow
