Google フォーム約5分で読めます

Googleフォームの回答を名簿と照合する|未提出者の抽出

Googleフォームの回答を名簿と照合するを表したイメージ
記事の内容を表したイメージ(AI生成)

Googleフォームで参加確認や同意書の提出を依頼したとき、「誰がまだ出していないか」を調べる必要が生じます。フォームの回答スプレッドシートには提出した人だけが並んでいるため、名簿と突き合わせなければ未提出者は分かりません。

この記事では、既存の名簿とフォーム回答を照合して未提出者を抽出する手順を説明します。使うのはGoogleスプレッドシートのCOUNTIF関数です。マクロや外部ツールは使わず、スプレッドシートの操作だけで完結できます。照合の鍵となる列の選び方と、同姓同名・表記ゆれが起きたときの対処も合わせて示します。


照合に使う「鍵」の列を決める

名簿とフォーム回答を突き合わせるには、両方に共通して存在し、重複しない値が入った列が必要です。これを照合キーと呼びます。

照合キーの候補として使いやすいものと注意点を整理します。

表は横にスクロールできます

候補使いやすい条件注意点
メールアドレスGoogleアカウントで収集した場合は自動入力される個人用と職場用が混在すると不一致が出る
社員番号・学籍番号組織内で一意に決まっているフォームで入力してもらう必要がある
氏名追加設定なしで使える同姓同名・全角半角・姓名の間のスペースで不一致が出る

最も確実なのは社員番号や学籍番号などの固有IDです。フォームに「番号を入力してください」という質問を設けるだけで済みます。メールアドレスは自動収集できるため手間が少ない反面、収集設定がオフになっていると使えません。氏名を使う場合は後述する表記ゆれへの対処が必要です。

フォームの設定画面で「メールアドレスを収集する」がオンになっているかどうかは、フォーム編集画面の設定から確認できます。画面の表示は版や設定により異なりますので、公式のヘルプで確認してください。


作業前の準備

照合を始める前に、次の点を確認します。

  1. 名簿のコピーを保存する 元のファイルを別名で保存し、元の状態に戻せるようにします。スプレッドシートのコピーは「ファイル」メニューから作成できます。
  2. フォームの回答スプレッドシートを開く フォーム編集画面の「回答」タブから「スプレッドシートに表示」を選ぶと、回答が自動入力されたシートが開きます。
  3. 照合キーの列を確認する 名簿側の列と回答スプレッドシート側の列の両方に、照合キーが存在するか確かめます。

名簿と回答を一つのスプレッドシートにまとめると作業が楽になります。新しいスプレッドシートを作成し、名簿を「名簿」シート、フォーム回答を「回答」シートとして貼り付けてください。個人情報を含む名簿を外部のサービスや生成AIに貼り付ける前に、情報管理のルールを確認してください。


COUNTIF関数で未提出者を抽出する手順

照合の考え方はシンプルです。名簿の各行について「この人の照合キーが、回答シートの照合キー列に一件以上あるか」を調べます。一件もなければ未提出です。これをCOUNTIF関数で数えます。

関数の書き方

名簿シートに「提出確認」列を追加し、次の数式を入力します(架空の例です)。

名簿シートのA列に照合キー(例:社員番号)が入っているとします。回答シートのB列に同じ社員番号が入っているとします。

=COUNTIF(回答!$B:$B, A2)
  • 回答!$B:$B は回答シートのB列全体を参照します。$をつけることで数式を下にコピーしても列がずれません。
  • A2 は名簿シートの現在の行の照合キーです。

この数式は「回答シートのB列にA2の値が何個あるか」を返します。1以上なら提出済み、0なら未提出です。

ExcelとGoogleスプレッドシートはCOUNTIF自体の書き方は同じですが、別シートの参照の区切り文字が異なります(Excelでは回答!$B:$B、Googleスプレッドシートでも同様の書き方で動作しますが、環境によって確認が必要です)。

「未提出」だけを見やすくする

数式を全行に入力したあと、0が入っている行を探します。フィルター機能を使い「提出確認」列を0で絞り込むと、未提出者だけを表示できます。

数式の結果を「未提出」「提出済み」と表示したい場合は、IFで包みます。

=IF(COUNTIF(回答!$B:$B, A2)=0, "未提出", "提出済み")

氏名を照合キーにするときの表記ゆれ対処

氏名を照合キーにする場合、次のような不一致が起きやすいです。

  • スペースの有無・種類:「山田 太郎」(全角スペース)と「山田太郎」(スペースなし)は別の文字列として扱われます。
  • 全角・半角の違い:カタカナや数字で起きやすいです。

事前に両方の列を統一しておくのが確実です。スペースを取り除くにはSUBSTITUTE関数が使えます。

=SUBSTITUTE(A2, " ", "")

全角スペースと半角スペースが混在する場合は二重にかけます。

=SUBSTITUTE(SUBSTITUTE(A2, " ", ""), " ", "")

ただし、SUBSTITUTE関数を名簿列そのものに使うと名簿が変わってしまいます。元の列は残したまま、新しい列に変換後の値を出力し、その列を照合キーとして使ってください。

同姓同名が名簿に存在する場合は、氏名だけでは照合できません。所属や生年月日など別の情報を組み合わせるか、回答フォームに番号の入力を追加する方がよいでしょう。


件数の確認と照合結果の見直し

未提出者の抽出が終わったら、次の点で結果の妥当性を確認します。

  • 名簿の件数と回答数の合計を確認する 名簿が50件でフォーム回答が50件あっても、一人が複数回答していれば未提出者が残ります。COUNTIF関数が2以上を返している行がないか確かめます。
  • 回答が名簿にない人を確認する 転入や後から追加されたメンバーが回答していると、名簿には存在しないまま回答スプレッドシートに入ります。逆に、回答シート側から名簿を同じ手順で照合すると「名簿にない回答者」を抽出できます。
  • 照合キーの誤入力を確認する 回答フォームで番号を手入力してもらう場合、入力ミスがあると不一致になります。COUNTIFが0を返す行のキー値を直接見て、明らかな誤入力があれば回答者に確認します。

次の行動

この記事の手順を試すには、まず名簿とフォーム回答を同じスプレッドシートに別シートとして用意し、元ファイルのコピーを保存してから始めてください。照合キーに使う列を決めたら、名簿シートに「提出確認」列を追加してCOUNTIF数式を入力します。0が返った行が未提出者ですので、フィルターで絞り込んでリストを完成させてください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ