Google Formの利用法 マスタとリンクしデータシート新規作成
出欠にマスタデータを追加する方法
Google フォームの回答から「メールアドレスごとの最新版だけ」を抽出したデータシートを作り、それをマスターの番号(連番・管理番号など)で並べ替えて出力シートに作る手順を解説します。
今回の流れは 「2段階(①最新版だけのデータシート作成 → ②マスター番号でソートして出力シート生成)」 に分けると、数式がシンプルになり、途中の確認やメンテナンスもしやすくなります。
1. シートの役割と構成例
フォームの回答 1(フォーム自動生成)A列:タイムスタンプ /B列:メールアドレス /C列以降:回答内容(例:C列=出欠、D列=備考)
マスターA列:メールアドレス(結合キー) /B列:番号(ソートキー:例1, 2, 3...や001, 002...) /C列:氏名 /D列:所属
データシート(新しく作成①:回答の「最新版だけ」を抽出したシート)出力シート(新しく作成②:マスターの番号でソートして結合した最終シート)
2. ステップ①: データ シートを作る(回答の「最新版だけ」を抽出)
- 新しいシートを追加し、名前を
データにします。 - 1行目(
A1:D1)に見出しを入れます(例:タイムスタンプ,メールアドレス,出欠,備考)。 A2セルに、メールアドレスごとの最新回答(タイムスタンプが最も新しい行)だけを抽出する数式を入力します。
Excel
=LET(
raw, FILTER('フォームの回答 1'!A2:D, 'フォームの回答 1'!B2:B<>""),
norm_email, ARRAYFORMULA(LOWER(TRIM(INDEX(raw, 0, 2)))),
with_norm, HSTACK(raw, norm_email),
sorted_desc, SORT(with_norm, 1, FALSE),
latest_with_norm, SORTN(sorted_desc, 9^9, 2, COLUMNS(with_norm), TRUE),
CHOOSECOLS(latest_with_norm, SEQUENCE(COLUMNS(raw)))
)
この数式の動き
FILTER(..., 'フォームの回答 1'!B2:B<>""):メールアドレスが空の行を除外します。norm_email:大文字・小文字や前後スペースを揃えたメールアドレスを作ります。SORT(with_norm, 1, FALSE):タイムスタンプ(1列目)の降順(新しい順)に並べ替えます。SORTN(..., 9^9, 2, COLUMNS(with_norm), TRUE):正規化済みメールアドレス列を基準に重複を削り、各アドレスの1件目(=最新回答)だけを残します。CHOOSECOLS(..., SEQUENCE(COLUMNS(raw))):作業用につけた末尾の正規化メール列を取り除き、元の列数(A:D)に戻します。
3. ステップ②: 出力 シートを作る(マスターと結合し、マスターの番号でソート)
- 新しいシートを追加し、名前を
出力にします。 - 1行目(
A1:H1)に見出しを入れます。- 例:
A1=番号、B1=氏名、C1=所属、D1=メールアドレス、E1=タイムスタンプ、F1=出欠、G1=備考 、H1 = その他
- 例:
A2セルに、最新回答データ(データシート)とマスターをメールで結合し、マスターの番号(昇順)でソートして出力する数式を入力します。
Excel
=LET(
d_ts, 'データ'!A2:A,
d_emails, 'データ'!B2:B,
d_ans, 'データ'!C2:D,
m_keys, ARRAYFORMULA(LOWER(TRIM('マスター'!A2:A))),
m_no, 'マスター'!B2:B,
m_info, 'マスター'!C2:D,
joined, MAP(d_ts, d_emails, SEQUENCE(ROWS(d_emails)), LAMBDA(t, e, idx,
IF(t="", {"","","","","","",""},
LET(
k, LOWER(TRIM(e)),
no_val, XLOOKUP(k, m_keys, m_no, 999999),
info_val, XLOOKUP(k, m_keys, m_info, {"未登録", ""}),
HSTACK(no_val, info_val, e, t, INDEX(d_ans, idx))
)
)
)),
valid_rows, FILTER(joined, INDEX(joined, 0, 5)<>""),
SORT(valid_rows, 1, TRUE)
)
この数式のポイント
- マスターの番号を1列目(
no_val)に配置: 最後にSORT(valid_rows, 1, TRUE)とすることで、1列目(マスターの番号)の昇順(小さい順)に自動で並びます。 - マスタ未登録メールの扱い:
XLOOKUPの見つからないときの値を999999にしているため、マスターに未登録のメールアドレスがあってもエラーにならず、ソート時に一番下へ回ります(番号が数値ではなく文字列・コードの場合は""や"ZZZZ"など運用に合わせて調整してください)。 - ソート基準の切り替え: もし「マスターの番号順ではなく、回答の新しい順に戻したい」と思ったら、
SORT(valid_rows, 1, TRUE)の第2引数を5(タイムスタンプ列)にし、第3引数をFALSEに変えるだけで切り替えられます。
実務上のチェックポイント
- マスター側の「番号」と「メールアドレス」の重複: マスターシートに同じメールアドレスが複数行あると、最初に見つかった行の番号・情報が使われます。マスターのメールアドレスは一意になるよう管理してください。
- 番号の並び順(数値 vs 文字列):
1, 2, 10のような番号を昇順ソートするとき、すべて数値として入っていれば1 → 2 → 10の順になりますが、一部が文字列だと文字列順(1 → 10 → 2)になることがあります。番号列は数値(または桁揃えしたコード)で統一しておくのが確実です。
マスターに出欠を追加する方法は?
先ほどは「Google フォームの回答(最新版)を軸にして、マスターの情報を付け足し、マスターの番号でソートする」流れでしたが、今回は逆に 「マスターデータ(名簿・台帳など)を軸にして、その行ごとに最新のフォーム回答を引っ張り出し、新しいデータシートを作る」 方法を解説します。
この形にすると、マスターデータの並び順がそのまま基準になり、まだ回答していない人(未回答者)も漏れなく一覧に載せられるのが大きな利点です。
1. シートの構成例
マスター(基準となる台帳・名簿)A列:番号(またはID・管理番号) /B列:メールアドレス(※結合キー) /C列:氏名 /D列:所属
フォームの回答 1(フォーム自動生成)A列:タイムスタンプ /B列:メールアドレス /C列以降:回答内容(例:C列=出欠、D列=備考)
新データシート(新しく作成するシート)
※マスターのメールアドレスが A 列にある場合は、数式内の m_emails を 'マスター'!A2:A 、 m_base を 'マスター'!B2:D のように列位置に合わせて読み替えてください。
2. 新しいシートを作る手順
- 画面左下の
+をクリックして新しいシートを追加し、名前を新データにします。 - 1行目(
A1:G1)に見出しを入れます。- 例:
A1=番号、B1=氏名、C1=所属、D1=メールアドレス、E1=最新回答日時、F1=出欠、G1=備考
- 例:
A2セルに、次の数式を入力します。
Excel
=LET(
m_no, 'マスター'!A2:A,
m_emails, 'マスター'!B2:B,
m_info, 'マスター'!C2:D,
r_ts, 'フォームの回答 1'!A2:A,
r_emails, ARRAYFORMULA(LOWER(TRIM('フォームの回答 1'!B2:B))),
r_ans, 'フォームの回答 1'!C2:D,
/* 回答データを「タイムスタンプ降順(新しい順)」に並べ替え */
order_desc, SORTN(
HSTACK(r_ts, r_emails, r_ans, SEQUENCE(ROWS(r_ts))),
ROWS(r_ts), 0, 1, FALSE
),
s_emails, INDEX(order_desc, 0, 2),
s_ans, CHOOSECOLS(order_desc, 3, 4),
s_ts, INDEX(order_desc, 0, 1),
MAP(m_no, m_emails, SEQUENCE(ROWS(m_no)), LAMBDA(no, em, idx,
IF(AND(no="", em=""), {"","","","","","",""},
LET(
k, LOWER(TRIM(em)),
/* 新しい順に並んでいるため、XLOOKUPで最初に見つかる=最新回答 */
hit_ts, IF(k="", "", XLOOKUP(k, s_emails, s_ts, "未回答")),
hit_ans, IF(k="", {"",""}, XLOOKUP(k, s_emails, s_ans, {"",""})),
HSTACK(no, INDEX(m_info, idx), em, hit_ts, hit_ans)
)
)
))
)
3. この方法のメリットと注意点
- 未回答者が自動で把握できる: マスター側の全員が並ぶため、回答がまだ来ていない人は
最新回答日時に"未回答"と表示され、回答内容(出欠・備考)は空欄になります。 - 「最新回答」の判定がシンプルになる: 事前に回答テーブル全体を
SORTN(..., ROWS(r_ts), 0, 1, FALSE)で「タイムスタンプの新しい順」に並べておけば、XLOOKUPは上から探して最初に見つかった行(=最新回答)を自動で返します(複数回回答していても最新1件だけが採用されます)。 - ソート操作が不要: そもそも
マスターシートの行順(番号順など)をベースにMAPで回しているため、マスター側が番号順に並んでいれば、出力シートもそのまま番号順になります。もしマスター側が番号順になっていない場合は、全体をSORT(..., 1, TRUE)で囲めば番号順に並べ替えられます。

