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