Google Sheets約4分で読めます

スプレッドシートで未提出者を調べる|名簿と回答一覧をIDで比べる方法

スプレッドシートで未提出者を調べるを表したイメージ
記事の内容を表したイメージ(AI生成)

名簿と回答一覧を突き合わせて未提出者を特定したい。しかし氏名で比べると表記ゆれや同姓同名が混入し、催促先を誤りやすくなります。

この記事では、提出対象者のIDと回答のIDを照合する方法を説明します。照合にはGoogle スプレッドシートのCOUNTIF関数を使います。架空データで手順を確認し、運用前にチェックすべき点も整理します。


なぜ氏名でなくIDで照合するか

氏名での照合は、漢字・ひらがなの揺れ、同姓同名、入力ミスの影響を受けます。社員番号や学籍番号など一意に振られたIDを使うと、これらの問題を避けられます。IDがない場合は、氏名やメールだけで同一人物と決めず、対象者ごとに重複しない管理用IDを決めてから照合します。


確認の順序

照合を始める前に、次の順序で表の状態を確認します。この順序を守ることで、数式が返す結果の意味をあとで誤解しにくくなります。

  1. 名簿側のID列に空欄や重複がないか確認する
  2. 回答側のID列に空欄や名簿にないIDがないか確認する
  3. 名簿の各IDについて回答件数を数える
  4. 0件(未提出候補)・1件(回答記録あり)・複数件(複数の回答記録)を分け、対象外IDと空欄は別集計にする

この順序は、数式を入れる前に表の品質を把握するためのものです。


架空例で構造を確認する

以下は編集部が作成した練習用の架空データです。実在の人物・業務実績とは無関係ですS2。

名簿(targets)

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

ID
M1
M2
M3
M4

回答一覧(responses)

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

ID
M1
M1
M3
X1
(空欄)

この2表をIDで照合すると、次の結果が得られます。

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

区分該当ID
未提出候補(0件)M2、M4
提出済み(1件)M3
重複回答(2件以上)M1
対象外IDX1
空欄1行

この結果はPythonの標準ライブラリによるローカル検算で確認していますS2。Excel・Google Sheetsの実機での動作確認は実施していません。


COUNTIFで回答件数を数える

Google スプレッドシートのCOUNTIF関数は、指定した範囲の中で条件に一致するセルの数を返しますS1。

架空例の構成に合わせた数式の候補を示します。

前提となるシート構成

  • シート名「対象者」のA列:名簿のID(A2から下に並ぶ、空欄なし)
  • シート名「回答」のA列:回答のID(A2:A6、空欄や対象外IDを含む)

数式候補(対象者シートのB2セルに入力)

=COUNTIF(回答!$A$2:$A$6,A2)

この数式は、回答シートのA2:A6の中でA2の値と一致する行数を返します。B2を下にコピーすると、名簿の各IDについて回答件数が並びます。

注意点

  • COUNTIFは大文字・小文字を区別しませんS1。M1とm1は同じとみなされます。大文字・小文字の違いで別IDを表す運用なら、この式では区別できません。同じIDの表記ゆれなのかを先に確認し、別IDを一つにまとめないでください。
  • 回答が増えた場合は、数式の参照範囲($A$2:$A$6)をデータの末尾まで広げる必要があります。固定したままだと新しい行が集計に入りません。
  • 上記の数式はExcel・Google Sheetsでの実機検証を実施していません。ご自身の環境で動作を確認してから運用に使用してください。

空欄・対象外IDの扱い

回答一覧のID列に空欄があるかを確認します。ただし、空欄が「本人未入力」なのか「フォームの不具合」なのかはデータだけでは判断できません。回答の収集方法を確認した上で対処してください。

名簿にないID(架空例では X1)が回答一覧にある場合、そのIDは対象者の件数集計には入りません。対象外IDは別に残し、回答原本や提出経路を確認してください。照合結果だけでは送信した本人までは確定できません。


件数0は「未提出の証拠」ではない

COUNTIFで0が返った場合、それはこの表の中に一致するIDがないことを示すだけです。提出されたのに別のシートに記録された、IDの入力ミスで別の行として入った、といった可能性は数式だけでは排除できません。0件の人には、提出経路や記録方法もあわせて確認することを勧めます。


配布素材について

記事に添付している targets.csv と responses.csv は、上記の架空例と同じ構成の平文データですS2。書式・入力規則・数式・セル保護を含む完成テンプレートではありません。CSVをスプレッドシートで直接開く際は、先頭ゼロや日付形式の自動変換に注意してください。


手順のまとめ

  1. 名簿のID列に空欄・重複がないことを確認する
  2. 回答一覧のID列に空欄・対象外IDがないかを確認する
  3. 名簿シートに集計列を追加し、=COUNTIF(回答!$A$2:$A$6,A2) で回答件数を出す(末尾の6は実際のデータに合わせる)
  4. 0件・複数件・空欄・対象外IDを区別して整理する
  5. 0件の人には件数以外の確認も行う

IDが整理されていれば、この手順で未提出者の候補を絞り込めます。同姓同名や表記ゆれによる誤催促を減らす出発点として使ってください。

この記事の確認用ファイル

架空データの記入例や、手元で確認するための素材です。

参考資料

公式資料と架空データを使って編集しています。配布CSVは値と見出しのみで、数式・書式・保護設定を含む完成ブックではありません。Excel・Sheetsの実機操作は未検証です。

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

記事一覧へ