チェックシートの未回答者をExcelで抽出する方法｜名簿と回答済みリストを突き合わせる

「チェックシートを送った相手のうち、まだ回答していないのは誰か」。

Excelとメールで回答を集めていると、回答期限が近づくたびにこの確認が必要になります。受信トレイから回答済みの相手を拾い、名簿と見比べて未回答者を探す方法でも運用できますが、対象者が増えるほど確認に時間がかかります。

未回答者をExcelで出す方法自体は難しくありません。基本は、依頼先の名簿と回答済みの一覧を、同じIDで照合するだけです。

この記事では、チェックシートの未回答者をExcelで抽出する方法と、照合するときに間違えやすい点を確認します。そのうえで、Excelの関数では解消できない「回答が届くたびに管理表を更新する作業」まで考えます。

最初に用意するのは、次の2つの一覧です。

一覧	入れておく情報
依頼先の名簿	回答者ID、会社名・氏名、メールアドレス、回答期限など
回答済みリスト	回答者ID、回答日など

たとえば「名簿」シートのA列に回答者ID、「回答済み」シートのA列にも回答者IDを置いているとします。

名簿シートのD2セルに、次の式を入れます。

=IF(COUNTIF(回答済み!$A:$A,A2)>0,"回答済み","未回答")

この式では、名簿にある回答者IDが「回答済み」シートに存在すれば「回答済み」、存在しなければ「未回答」と表示します。あとはD列を「未回答」で絞り込めば、まだ回答していない相手だけを確認できます。

FILTER関数を使える環境なら、未回答者だけを別の一覧として取り出す方法もあります。

=FILTER(A2:D100,D2:D100="未回答","未回答者なし")

対象人数が多くても、1件ずつ目で突き合わせるより、名簿側に回答状態を持たせたほうが確認しやすくなります。

照合するときに大事なのは、「何を一致させるか」です。

会社名や氏名だけで照合すると、同姓同名、表記の違い、旧字体、スペースの有無などで一致しない場合があります。取引先であれば会社名の「株式会社」の有無、社内であれば姓名の間のスペースだけでも別の文字列として扱われます。

できれば、次のような重複しにくい値を1つ決めて使います。

• 回答者ID
• 社員番号
• 取引先コード
• メールアドレス
• システム上で発行した一意のID

名簿と回答済みリストの両方で同じIDを使っておけば、「名前は似ているが別人」「同じ会社に複数の回答者がいる」といった状況でも照合しやすくなります。

メールアドレスを使う場合も、担当者変更や共有アドレスへの切り替えがあれば更新が必要です。何を照合キーにするかを途中で変えず、依頼時から回答回収まで同じ値を持たせることが重要です。

未回答者だけを探すなら、「回答済み／未回答」の2つでも確認できます。ただ、実際のチェックシート運用では、それだけでは状況を判断しにくくなります。

Excelで管理する場合は、少なくとも次の状態を分けておくと、状況を判断しやすくなります。

状態	意味
未送信	名簿にはあるが、まだ依頼していない
未回答	依頼済みで、回答を受け取っていない
回答済み	回答を受け取った
対象外	契約終了、重複登録などで今回の確認対象ではない

「対象外」を未回答のまま残すと、未回答者数に本来追いかける必要のない相手まで含まれます。依頼対象から外した理由も一緒に記録しておくと、あとから一覧を見た人が判断しやすくなります。

一方、「回答中」はExcelとメールだけでは自動的に判断できません。相手がファイルを開いて社内確認を進めていても、回答が届くまでは依頼側から見ると未回答のままです。途中状態まで把握したい場合は、回答画面と管理側の状態がつながっている仕組みが必要になります。

COUNTIFやFILTERを使えば、名簿と回答済みリストの突き合わせはかなり簡単になります。

ただし、メール添付で回答を受け取っている場合、回答済みリストそのものは誰かが更新しなければなりません。

たとえば、午前10時に回答がメールで届いても、管理表への記録が午後になれば、その間はExcel上では「未回答」です。その状態で一覧を使って催促すると、すでに回答した相手まで対象にしてしまいます。

ここで発生しているのは、Excelの計算式の問題ではありません。

• 依頼先はExcelにある
• 回答はメールに届く
• 回答済みかどうかはExcelへ転記する
• 催促対象は転記後のExcelから判断する

というように、回答が届く場所と、回答状況を管理する場所が分かれていることが手作業を生みます。

未回答者を抽出する式を整えても、回答の到着とステータス更新が別々のままであれば、最新状態を保つための作業は残ります。

Excelを使っているから問題、というわけではありません。

依頼先が少なく、確認頻度も低く、1人の担当者が同じ方法で回収しているのであれば、Excelとメールで十分に管理できます。COUNTIFなどで未回答者を出せるようにしておけば、毎回名簿を最初から見比べる必要もありません。

一方、次のような状態になると、関数を増やすだけでは管理しにくくなります。

• 同じチェックを定期的に繰り返している
• 回答者や依頼先が増えている
• 複数の担当者で回収状況を見る
• 回答途中かどうかも把握したい
• 回答が届いた直後の状態を一覧へ反映したい
• 未回答者だけに催促したい
• 回答済みと内容確認済みを分けて管理したい

この段階では、「Excelをもっと作り込むか」ではなく、回答と回答状況を同じ場所で持つかを考えるほうが管理しやすくなります。

回収全体の設計や催促まで含めて見直したい場合は、チェックシートの回収管理｜未回答の把握と催促の進め方で、回答期限、ステータス、催促、回収後の確認までをまとめています。

回答ファイルや差し戻し履歴、過去回答まで含めたExcel管理全体の課題は、セキュリティチェックをExcelで管理する限界｜ファイル管理・履歴管理で起きる課題で扱っています。

マモリスでは、チェックシートの回答者を登録し、回答URLから回答してもらうため、依頼した相手と回答状況を同じ流れの中で管理できます。

依頼側では、回答前・回答中・回答済みの状態を一覧で確認できるため、受信トレイから回答を探してExcelの名簿へ「済」を転記する必要はありません。回答者はログイン不要のURLから回答でき、途中で保存して後から再開できます。

未回答者への催促や、回答後の確認・差し戻しは別の工程です。この記事では「誰がまだ回答していないかを把握する」までに絞っています。催促のタイミングや未回答のまま残った場合の扱いは、回収管理・催促の記事で確認してください。

Q1. Excelで未回答者を出すなら、どの関数が簡単ですか？

回答済みかどうかを名簿側へ表示するだけなら、COUNTIFを使う方法が分かりやすいです。回答済みリストに同じIDが存在するかを数え、0件なら「未回答」と表示できます。未回答者だけを別表に出したい場合は、FILTER関数を組み合わせます。

Q2. 氏名を使って回答済みかどうかを照合しても問題ありませんか？

氏名だけでの照合は避けたほうが安全です。同姓同名や表記差があるため、社員番号、取引先コード、メールアドレスなど、重複しにくい値を使います。

Q3. Googleスプレッドシートでも同じ方法を使えますか？

COUNTIFやFILTERを使って同じ考え方で照合できます。ただし、GoogleスプレッドシートのFILTER関数には、Excelの「該当データがない場合に表示する文字列」を指定する第3引数がありません。該当者がいない場合のエラー表示を変えたいときは、IFERRORなどと組み合わせます。

Q4. Excelだけで「回答中」と「まだ着手していない」を分けられますか？

メールでExcelファイルを配布・回収する方式では、回答が届く前の進み具合を依頼側から自動で判別することはできません。途中状態を管理したい場合は、回答画面側の進捗と管理画面が連動する仕組みが必要です。

Q5. 未回答者を抽出した後、いつ催促すればよいですか？

催促の時期は、回答期限や確認に必要な期間、相手側の社内確認にかかる時間によって変わります。期限前のリマインドと期限後の催促を分け、依頼前にルールを決めておく方法は「チェックシートの回収管理」で詳しく説明しています。

Excelで未回答者を確認するだけなら、依頼先の名簿と回答済みリストを共通のIDで照合すれば対応できます。COUNTIFで回答状態を表示し、フィルターやFILTER関数で未回答者だけを取り出せば、目視で1件ずつ照合する作業を減らせます。

ただし、メールで回答を受け取っている場合、回答済みリストの更新は残ります。未回答者の抽出に時間がかかっているときは、関数だけでなく、「回答が届く場所」と「回答状況を管理する場所」が分かれていないかも確認してください。

Excelで十分に回る間は、無理に管理方法を変える必要はありません。回答者や確認頻度が増え、最新状態を保つための転記や突き合わせが続くようになったら、回答と回答状況を同じ場所で管理する方法を検討するタイミングです。