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