Google Formの利用法 マスタとリンクしデータシート新規作成

出欠にマスタデータを追加する方法

Google フォームの回答から「メールアドレスごとの最新版だけ」を抽出したデータシートを作り、それをマスターの番号(連番・管理番号など)で並べ替えて出力シートに作る手順を解説します。

今回の流れは 「2段階(①最新版だけのデータシート作成 → ②マスター番号でソートして出力シート生成)」 に分けると、数式がシンプルになり、途中の確認やメンテナンスもしやすくなります。

1. シートの役割と構成例

  • フォームの回答 1 (フォーム自動生成)
    • A 列:タイムスタンプ / B 列:メールアドレス / C 列以降:回答内容(例: C 列=出欠、 D 列=備考)
  • マスター
    • A 列:メールアドレス(結合キー) / B 列:番号(ソートキー:例 1, 2, 3... や 001, 002... ) / C 列:氏名 / D 列:所属
  • データ シート(新しく作成①:回答の「最新版だけ」を抽出したシート)
  • 出力 シート(新しく作成②:マスターの番号でソートして結合した最終シート)

2. ステップ①: データ シートを作る(回答の「最新版だけ」を抽出)

  1. 新しいシートを追加し、名前を データ にします。
  2. 1行目( A1:D1 )に見出しを入れます(例: タイムスタンプ , メールアドレス , 出欠 , 備考 )。
  3. 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. 新しいシートを追加し、名前を 出力 にします。
  2. 1行目( A1:H1 )に見出しを入れます。
    • 例: A1 = 番号 、 B1 = 氏名 、 C1 = 所属 、 D1 = メールアドレス 、 E1 = タイムスタンプ 、 F1 = 出欠 、 G1 = 備考 、H1 = その他
  3. 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. 画面左下の + をクリックして新しいシートを追加し、名前を 新データ にします。
  2. 1行目( A1:G1 )に見出しを入れます。
    • 例: A1 = 番号 、 B1 = 氏名 、 C1 = 所属 、 D1 = メールアドレス 、 E1 = 最新回答日時 、 F1 = 出欠 、 G1 = 備考
  3. 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) で囲めば番号順に並べ替えられます。

\ 最新情報をチェック /