みなさんこんにちは。
今日は、Googleフォームで集めた回答を、名簿の出席番号順に並べる方法を紹介します。
GIGAスクール構想で、生徒にタブレットが配付されたり、教員にもGoogleのアカウントが割り振られたりしたことで、様々なことがデジタル化されましたよね。その一つがGoogleフォームを使用したアンケートです。
これまで紙で印刷して集計していた作業と比べれば圧倒的に楽ですが、まだまだ課題もあります。
それは、Googleフォームの回答は送信された順番に表示されるからです。
回答内容を見るだけなら、それで困りません。でも、未回答の生徒を確認しようとすると、名簿と回答シートを見比べることになります。
これが地味に面倒なんですよね。
「私のクラスの生徒は回答しているかな？」といった把握に手間がかかっています。
そこで、回答シートの隣に名簿を置いて、関数で回答を引っ張ればいいのではないかと思いました。
結論から言うと、フォームに連携されているスプレッドシートへ「名簿順」シートを1枚追加し、関数を貼るだけです。回答が届くたびに名簿順の表示も更新され、回答がない生徒は「未回答」のまま残ります。
今回の記事を活かしてスプレッドシートを作成すれば、1人の先生が1度作成するだけで、すべての担任が楽にアンケート結果や送信状況をチェックすることができます。
完成するとこうなる
新しく作る「名簿順」シートには、A列から順に名簿を入力します。
| 列 | 内容 |
|---|---|
| A列 | 学年 |
| B列 | 学級 |
| C列 | 出席番号 |
| D列 | 氏名 |
| E列 | 回答状況 |
| F列以降 | フォームに入力された内容 |
回答状況には、次のいずれかが表示されます。
| 表示 | 意味 |
|---|---|
| 回答済 | 学年・学級・出席番号・氏名が完全一致 |
| ⚠ 出席番号不一致 | 学年・学級・氏名は一致しているが、番号が違う |
| ⚠ 所属情報要確認 | 氏名は一意に一致しているが、学年や学級が違う |
| ⚠ 氏名不一致 | 学年・学級・番号は一致しているが、氏名が名簿にない |
| 未回答 | 表示できる回答がない |
同じ生徒が複数回答している場合は、まず一致度の高い回答を優先します。同じ一致度の回答が複数ある場合だけ、タイムスタンプが最も新しいものを表示します。
つまり、一致度が先、送信時刻はその次です。
作成の手順
1．フォームと名簿を用意する
Googleフォームには、次の4項目を用意します。質問名も、この表記にそろえてください。
- 学年
- 学級
- 出席番号
- 氏名
学年・学級・出席番号は、できるだけ選択式にして、すべて必須回答にします。「1」と「1年」、「01」と「1」のように表記が変わると、同じ情報として比較できないためです。
自由記述にしても、「数値」に限定するなど、工夫が必要です。
氏名は自由記述でも構いません。今回の関数では、名字と名前の間にある半角スペースと全角スペースを除いて比較します。そのため、「山田太郎」「山田 太郎」「山田 太郎」は同じ氏名として扱います。
2．回答先のスプレッドシートに新しいシートを用意する
①新しいシートを追加する
スプレッドシート左下の「+」ボタンから新しいシートを追加します。この記事では、シート名を「名簿順」とします。
なお、この記事内の画像では、見やすいようにシートに装飾を施しています。
②見出しを入力する
A1〜E1へ、次の見出しを入れます。
学年｜学級｜出席番号｜氏名｜回答状況
③名簿を貼り付ける
A2〜D列には、出席番号順の名簿を貼り付けます。
④関数を貼り付ける
1：見出しのための関数を貼り付ける
F1セルには、フォーム回答シートの見出しを表示するため、次の関数を入れます。
F1セルに関数を入れると、G,H,I…と、それ移行の列にも見出しが自動で入力されます。
=ARRAYFORMULA('フォームの回答 1'!A1:Z1)
2：回答を表示するための関数を貼り付ける
次の関数をE2セルに貼り付けます。そのあと、E2セルを名簿の最終行までコピーしてください。関数をコピーするのはE列だけです。F列より右には、各生徒の回答が自動的に広がります。
少し長い関数ですが、みなさんが書き換える場所は基本的にありません。そのままコピーして使えます。
クリックしてコードを表示
=IF(
$A2="",
"",
LET(
headers,'フォームの回答 1'!$A$1:$Z$1,
response_width,COUNTA(headers),
responses,
ARRAY_CONSTRAIN(
'フォームの回答 1'!$A$2:$Z$1000,
999,
response_width
),
time_col,
IFNA(
MATCH("タイムスタンプ",headers,0),
MATCH("Timestamp",headers,0)
),
grade_col,MATCH("学年",headers,0),
class_col,MATCH("学級",headers,0),
number_col,MATCH("出席番号",headers,0),
name_col,MATCH("氏名",headers,0),
timestamps,
CHOOSECOLS(responses,time_col),
grades,
ARRAYFORMULA(
IFERROR(
VALUE(
REGEXREPLACE(
TO_TEXT(CHOOSECOLS(responses,grade_col)),
"[^0-9]",
""
)
),
""
)
),
classes,
ARRAYFORMULA(
IFERROR(
VALUE(
REGEXREPLACE(
TO_TEXT(CHOOSECOLS(responses,class_col)),
"[^0-9]",
""
)
),
""
)
),
numbers,
ARRAYFORMULA(
IFERROR(
VALUE(
REGEXREPLACE(
TO_TEXT(CHOOSECOLS(responses,number_col)),
"[^0-9]",
""
)
),
""
)
),
response_names,
ARRAYFORMULA(
REGEXREPLACE(
TO_TEXT(CHOOSECOLS(responses,name_col)),
"[ ]",
""
)
),
roster_grades,
ARRAYFORMULA(
IFERROR(
VALUE(
REGEXREPLACE(
TO_TEXT($A$2:$A$500),
"[^0-9]",
""
)
),
""
)
),
roster_classes,
ARRAYFORMULA(
IFERROR(
VALUE(
REGEXREPLACE(
TO_TEXT($B$2:$B$500),
"[^0-9]",
""
)
),
""
)
),
roster_names,
ARRAYFORMULA(
REGEXREPLACE(
TO_TEXT($D$2:$D$500),
"[ ]",
""
)
),
my_grade,
IFERROR(
VALUE(
REGEXREPLACE(TO_TEXT($A2),"[^0-9]","")
),
""
),
my_class,
IFERROR(
VALUE(
REGEXREPLACE(TO_TEXT($B2),"[^0-9]","")
),
""
),
my_number,
IFERROR(
VALUE(
REGEXREPLACE(TO_TEXT($C2),"[^0-9]","")
),
""
),
my_name,
REGEXREPLACE(
TO_TEXT($D2),
"[ ]",
""
),
valid,
timestamps<>"",
response_name_exists,
MAP(
response_names,
LAMBDA(
n,
IF(
n="",
FALSE,
IFNA(
MATCH(n,roster_names,0)>0,
FALSE
)
)
)
),
same_name_in_class,
SUMPRODUCT(
--(roster_grades=my_grade),
--(roster_classes=my_class),
--(roster_names=my_name)
),
my_name_count,
SUMPRODUCT(
--(roster_names=my_name)
),
scores,
ARRAYFORMULA(
IF(
valid*
(grades=my_grade)*
(classes=my_class)*
(numbers=my_number)*
(response_names=my_name),
4,
IF(
valid*
(grades=my_grade)*
(classes=my_class)*
(response_names=my_name)*
(same_name_in_class=1),
3,
IF(
valid*
(response_names=my_name)*
(my_name_count=1),
2,
IF(
valid*
(grades=my_grade)*
(classes=my_class)*
(numbers=my_number)*
(response_name_exists=FALSE),
1,
0
)
)
)
)
),
ranked,
IFERROR(
SORT(
FILTER(
HSTACK(responses,scores),
scores>0
),
response_width+1,
FALSE,
time_col,
FALSE
),
""
),
best_score,
IFERROR(
INDEX(
ranked,
1,
response_width+1
),
0
),
status,
IF(
best_score=4,
"回答済",
IF(
best_score=3,
"⚠ 出席番号不一致",
IF(
best_score=2,
"⚠ 所属情報要確認",
IF(
best_score=1,
"⚠ 氏名不一致",
"未回答"
)
)
)
),
selected,
IF(
best_score=0,
"",
ARRAY_CONSTRAIN(
ranked,
1,
response_width
)
),
HSTACK(status,selected)
)
)関数は、見出しの「学年」「学級」「出席番号」「氏名」を探して列を判断しています。そのため、フォームでメールアドレスを収集して列が増えても、4項目の見出しが同じであれば使えます。
回答がZ列より右まである場合は、F1とE2の関数にある Z を AZ などへ変更してください。回答が1000件を超える場合は、1000 も必要な行数へ変更します。
どの回答が優先されるのか
例えば、5番の佐々木さんが、自分の氏名を入力したまま、出席番号だけを「6番」と間違えたとします。
この回答は、6番の生徒の行には表示されません。「学年・学級・氏名」が一致する佐々木さんの行へ表示され、回答状況には「⚠ 出席番号不一致」と出ます。
もし6番の生徒が正しい情報で回答していれば、そちらは6番の行に「回答済」と表示されます。後から届いた誤回答によって、正しい回答が上書きされることはありません。
完全一致する回答が複数ある場合は、その中で最も新しい回答を表示します。
完全一致がなくても、氏名から生徒を1人に絞れる場合は、回答内容を仮表示します。ただし、学年・学級・出席番号に違いがあるので、オレンジ色の警告として扱うのがよいと思います。
一方で、同じ氏名の生徒が複数いるなど、候補を1人に決められない回答は自動で割り当てません。無理にどちらかへ表示するより、元の回答シートを確認する方が安全だからです。
警告が表示されたらどうする？
警告が出た回答は、管理者が氏名・タイムスタンプ・入力内容を確認します。学校アカウントのメールアドレスを収集している場合は、それも本人確認に使えます。
本人を特定できた場合は、「フォームの回答 1」シートにある誤入力を修正すると、名簿順シートの表示も変わります。修正したセルにはメモを付けておくと、後から管理者による修正だと分かります。
本人を特定できない場合は、その回答を正式な回答済みとして扱わず、再回答を依頼します。
ここは自動化しすぎない方がいいと思っています。関数ができるのは、候補を見つけて警告するところまでです。最後に誰の回答なのかを判断するのは、管理者の仕事として残しておいた方が安全です。
うまく動かないとき
#REF! が表示される
F列より右に文字や数式が入っていると、回答を横方向へ展開できません。E列の関数以外は空けておきます。
回答シートの名前が違う
回答シートの名前が「フォームの回答 1」ではない場合は、関数内のシート名を実際の名前へ変更してください。
見出しが見つからない
フォームの質問名が「クラス」「番号」「名前」などになっている場合は、関数内の「学級」「出席番号」「氏名」を実際の見出しへ変更します。
同姓同名の生徒がいる
同じ学級に同姓同名の生徒がいる場合は、完全一致する回答だけを採用します。番号まで間違っている回答は自動判定できないので、学校アカウントのメールアドレスや固有IDで確認してください。
まとめ
Googleフォームの回答は、送信された順番でスプレッドシートに記録されます。
今回紹介した方法を使えば、送信順にバラバラに並んでいる回答を、名簿の出席番号順に表示できます。
- 回答を出席番号順に確認できる
- 未回答の生徒も名簿に残る
- 複数回回答した場合は、最新の回答を表示できる
- 入力ミスがある回答は、警告付きで確認できる
名簿と回答シートを何度も見比べなくても、誰が回答し、誰がまだ回答していないのかを一覧で確認できます。
最近、学校現場ではGoogleフォームでアンケートや参加確認を集める機会が多いので、特に便利な仕組みだと思います。
アンケートの集計などは、教員の本当の仕事ではありません。
仕組みで楽にできる部分は楽にして、先生が直接生徒とかかわる時間を増やせるお手伝いができれば嬉しいです。
記事の内容で分かりにくかったところや、うまく動かなかったところがあれば、コメント欄で教えてください。
「学校でこんなことに困っている」「こんなツールがあれば便利」といったアイデアも歓迎しています。いただいた声は、今後の記事やツールづくりの参考にさせていただきます。
個別に伝えたい内容がある場合は、XのDMでも大丈夫です。
※生徒氏名・学校名・アンケートの回答など、個人を特定できる情報は送らないようお願いいたします。
コメント