. Microsoft Forms の「回答の並べ替え→抽出→色分け」を自動化する【Excel 編】 - タイダログ
Microsoft Forms の「回答の並べ替え→抽出→色分け」を自動化する【Excel 編】 - タイダログ
Microsoft Forms の「回答の並べ替え→抽出→色分け」を自動化する【Excel 編】 - タイダログ

【新】Microsoft Forms の「回答の並べ替え→抽出→色分け」を自動化する【Excel 編】

新たに作成したテーブルには「テーブル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)

「番号」の先頭にシングルクォーテーション ( ' ) が付いています。これは数値を文字列にする記号です。数値としては 4 の方が先ですが、文字列になると "22" の方が先になります。期待する並び順にするために、文字列を数値に変換する VALUE 関数を使いましょう。

=SORTBY(T_form,VALUE(T_form[番号]),1) 日時でフィルターをかける 当日の回答だけフィルターする数式

当日の回答だけフィルターするには、 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")) =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")) ソートしてフィルターする数式 =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 する数式

ネタです。私の推し関数 C O U N T I F 関 数 を使いたかったんです。

=UNIQUE(T_kanji[今年の漢字予想])

こちらをひとつめのセルの右隣に入れます。 H2 はひとつめのセルに合わせて変えてください。

=COUNTIF(T_kanji[今年の漢字予想],H2#)

H2# の # は「スピル範囲演算子」といいます。お調べあそばせ。

結び

参考

  • 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

更新履歴

  • 「完成図」の文言を変更 (2022/02/01)
  • 目次の下に「こちらもどうぞ」を追加 (2023/04/24)
  1. 厳密に言うと T_form[完了時刻]>=TODAY() は「『完了時刻』のシリアル値が今日の 00:00:00 の値以上である」という意味ですので、翌日以降を含んでしまいます。ですがアンケートの回答時間が翌日以降になっているなんてことは、タイムトラベルか時空操作か超能力はたまた何かの陰謀でもないと起こらないでしょうから、今回は問題ないことにしました。宇宙人、未来人、異世界人、超能力者が在籍している学校さんにおかれましては INT 関数をお使いください。↩
  2. 楽とは。↩
Googleフォームの未回答者のリストアップを、自動で、リアルタ… GAS で書いた function を Google スプレッドシートから呼び出… Google カレンダーに登録した予定までの日数を Google スライド… Google フォームの回答を出席番号順に並べ替えたりフィルターを… Microsoft Forms の回答一覧をダウンロードして出席番号順に並… 最終更新: 2025-05-07 08:00

怠惰な情報科教員。 PowerShell と F# が好き。 高校「情報Ⅰ」の学習サイト taidalab を作りました。

  • Excel (1)
  • Excel-関数 (1)
  • その他 (3)
  • プログラミング (34)
  • プログラミング-F# (10)
  • プログラミング-GAS (2)
  • プログラミング-JavaScript (1)
  • プログラミング-PowerShell (14)
  • プログラミング-VBA (7)
  • 情報科 (4)
  • 情報科-プログラミング教育 (2)
  • 情報科-情報のデジタル化 (2)
  • 業務 (12)
  • 業務-効率化 (11)
  • 業務-標準装備でがんばる (1)
📎📎📎📎📎📎📎📎📎📎