はじめに
Formsの回答結果の集計に時間がかかって困っています…。
Formsは回答時間順に並んでいますからね。
いつも使っている名簿順とかで回答を整理したいですよね…。
Microsoft Formsで集めた回答は、回答時間順に並んでいるため、回答状況の把握が少々面倒ですね。
この記事では、
Excelの XLOOKUP 関数 と COUNTIFS 関数 を使って、
Forms の回答を 名簿順に整理し、集計する方法まで をご紹介します。
※また、回答ミスによって再度回答させたときに、最新の回答を参照するようにもします。
名簿順に並べ替えるためのExcelシートを作成
以下のように Forms の回答が蓄積されるエクセルを使います。
①：Forms の画面右上「応答を表示」をクリック
②：画面右上にある「Forms のタイトル」になっているエクセルをクリック
上の画像で灰色になっている部分をクリックしましょう。
その横のドロップダウンからではありませんので注意。
以下のような Forms の回答を蓄積したエクセルが開きます。
※疑似データを使用しています。
③：Excel の Sheet2 に名簿を追加する
新たに Sheet2 を追加し、Sheet２ に「クラス」「番号」「名前」「メールアドレス」の項目を設けた名簿を用意します。
「クラス」～「メール」までの見出しを含めた名簿全体を選択し、「 Ctrl＋T 」でテーブル化します。
※「先頭行をテーブルの見出しにする」にチェック。
E１、F１、G1・・・には Forms の質問を入力します。
E列から右は「範囲」にしておきましょう。
テーブル化してしまうと、
このあと使うXLOOKUP関数がうまく機能しません。
④：XLOOKUP 関数を使って名簿順に Forms の回答を整理する
E２には以下のような関数を入力します（詳細は後程。先に具体例を見せます）。
=XLOOKUP(テーブル2[@メール],テーブル1[メール],テーブル1[[英語を受験しますか？]:[数学について教えてください。]],,,-1)すると以下のように、Forms の回答が、名簿順に整理された状態で表示されます。
このように回答を名簿順に整理できると便利ですよね。
では具体例を使ってやり方を説明しますね。
XLOOKUP 関数の基本構文
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])※コンマで区切られた”検索値”や”検索範囲”のようなものを”引数”と言います。
以下のように各引数を設定していきますが、[ ]が付いた引数は必要なければ空欄でOKです。
今回は[ ]がついた引数の内、検索モードだけ設定しています。
| 引数 | 説明 |
|---|---|
| 検索値 | 探したい値（例：名簿内の特定のメールアドレス） |
| 検索範囲 | 検索対象の列（例：Forms回答内のメール列） |
| 戻り値範囲 | 見つかったときに返す値の列（例：Forms回答内内容） |
| [検索モード] | Formsの回答一覧で検索する順番（例：-1） |
検索モードの引数に「-1」を設定しておけば、
Formsの回答一覧の最も新しい回答を名簿に引っ張ってこられます。
（テーブルの下から検索してくれます）
私は模試の受験科目をFormsで生徒に調査するのですが、
「受験科目を変更したい」と言われることがありましたので、
検索モードを「-1」にしています。
そうすれば、最新の回答結果を集計できるので、
生徒は再入力するだけでOK。集計係も楽です。
XLOOKUP 関数の引数の解説
=XLOOKUP(テーブル2[@メール],テーブル1[メール],テーブル1[[英語を受験しますか？]:[数学について教えてください。]],,,-1)この式は、「疑似データ（クラス番号順）」シートの名簿のメールアドレスをもとに、
「疑似データ（ Forms 回答）」シートの Forms 回答一覧から、
アドレスを使って、該当する生徒の「英語の受験状況」から「数学までの受験状況」を探して表示します。
※テーブル１とは「疑似データ（ Forms 回答）」シートにあるテーブルのことで、
テーブル２とは「疑似データ（クラス番号順）」シートにあるテーブルの事です。
[@メール] は、名簿が Excel のテーブル形式 になっている場合に使える書き方です。
通常のセル参照（例：A2）と違い、列名（ここでは見出しの「メール」）で指定できるため、
参照先がわかりやすく、ミスが減り、データ追加にも強いのが特徴です。
「@」は「メール」列のある１つのセルを表していて、
XLOOKUP 関数を入れた行と同じ行の「メール」列の値１つを参照します
（ E2 に入力した XLOOKUP 関数が参照しているのは D2 のメールアドレス）。
実際に操作している様子はこちら⇓
これで未入力生徒の確認、生徒の入力内容の確認がスムーズになります。
このエクセルを編集可能で他の先生へ共有すればどの先生も入力状況をリアルタイムで確認できます。
閲覧のみ可能な状態で共有すると、作成者がエクセルを開かなければ最新の状態に更新されませんので注意しましょう。
共同編集は PC が苦手な先生がデータを消してしまうリスクもありますので気をつけましょう。
【おまけ】スピル（ Spill ）について
- 上の例では E2 にしか XLOOKUP 関数を入力していないのに、結果が F2 や G2 にもに展開されています。
- このように関数の結果が複数セルに自動展開されることを「スピル」と呼びます。
- スピルは、複数行のデータを一度に処理したいときに非常に便利です。
- XLOOKUP 関数では「戻り値範囲」を複数列にしたため、スピルされました。
※スピルで展開される先に何か入力されていると、「＃スピル！」というエラーがでます。
その時はスピルで展開される先のセルを空白にしましょう。
※「テーブル」にスピル機能を含んだ関数は使用できません。「範囲」に式を入力しましょう。
このシートを活用すればFormsの回答を集計しやすくなります。
COUNTIFS 関数でクラスごとの集計をする
Forms の回答の内、条件に合った回答の数を知りたいときってありますよね。
今回は「クラス」と「受験科目」の２つの条件で集計する方法を紹介します。
先ほどと同じ Forms の回答一覧を集計して以下のような集計表を作成します。
たとえば、英語の表の左上の９は「A組」かつ「英語を受験する」生徒の数を表しており、
セルには以下のような関数が入力されています。
=COUNTIFS($A:$A,J$2,$E:$E,$I3)この関数は
A列に入力されているクラスの中で、J2 セルに入力されているクラスと一致し、かつ、
E列に入力されている英語の受験科目についての回答が I3 セルに入力されている「受験する」と一致する回答の数
を数えています。
※「＄」マークはセルに入力した関数を隣のセルに反映させるときに
参照セルがずれてしまうのを防ぐ役目をしています。
基本構文
=COUNTIFS(条件範囲1,条件1,条件範囲2,条件2,...)| 引数 | 説明 |
|---|---|
| 条件範囲 | 判定対象の列（例：クラス列） |
| 条件 | 判定する値（例：”A”） |
COUTIFS関数は「条件範囲」と「条件」を交互に設定するだけで複数の条件を満たすセルの数を数えることができる便利な関数です。
XLOOKUP 関数と COUNTIFS 関数のメリットまとめ
- 未回答者の発見が簡単
- クラスごとの回答状況が一目でわかる
- 他の校務（健康調査、希望調査など）にも応用可能
- テーブル化とスピルで作業効率アップ
おわりに
Forms と Excel を組み合わせることで、校務の効率化が大きく進みます。
XLOOKUP 関数 と COUNTIFS 関数は、校務の定番関数として覚えておくと便利です。
ぜひ、日々の業務に取り入れてみてください！
こちらの記事では、「今日回答されたものを抽出する方法」を
まとめています。毎日入力してもらうようなFormsに使えます。
様々な雑務処理の効率化については
こちらでまとめています。
こうした効率化の取り組みは、とても価値があると思います。
一方で、
「実際の学校でどう進めるか」で立ち止まることも少なくありません。
現場の状況に合わせて一緒に整理することもできます。
→ 研修・相談についてはこちら
コメント