申し込んだのに来なかった人は誰?2つの名簿をExcelで照合する式と、全角・半角の落とし穴

来なかった人を自動で探す。全角・半角の違いで見落とさない式 文字列

研修やイベントのあと、「申し込んだのに来なかった人は誰?」を申込名簿と受付名簿を見比べて探す作業、地味に大変ですよね。目で追っていくと、どうしても見落としが出てしまいます(>_<)

結論から書くと、両方の名簿に空白をぜんぶ消した「照合用」の列を作って、この式で比べれば一発です!

照合用の列: =SUBSTITUTE(SUBSTITUTE(氏名," ",""),"　","")
来なかった人: =FILTER(申込[氏名],ISNA(XMATCH(申込[照合用],参加[照合用])),"全員参加")

「照合用の列なんて作らなくても、名前をそのまま比べればいいのでは?」と思いますよね。でも実際にやってみると、空白の入れ方が違うだけの人まで「来なかった」と出てしまいました。

さらに試していくと、関数によって全角・半角の扱いが違うこともわかったので、その一覧表も載せています💡

🎬 実際の Excel 画面で、2つの名簿を照らし合わせている動画もあります(4分くらい)。関数ごとの違いの一覧も映しているので、こちらからどうぞ♪

動画で見る:2つの名簿の照合と、全角・半角の落とし穴(音声あり・章ごとに見られます)

照らし合わせる2つの名簿

申込はフォームから集めたので、姓と名の間はすべて半角の空白です。一方、当日の受付は手入力なので、空白がなかったり、全角だったり、後ろに入っていたりとバラバラです。

申込シート。フォームから集めた申込者10人の氏名と所属。姓と名の間は半角の空白
申込シート(10人)。テーブル名「申込」
参加シート。当日受付で手入力した9人の氏名と受付時刻。空白の入れ方がばらばら
参加シート(9人)。テーブル名「参加」。「青木一郎」「石井　花子(全角の空白)」「加藤由美」「斎藤 学 (後ろに空白)」

本当に来なかったのは遠藤さんと小林さんの2人、申込なしで来たのは中村さん1人です。

名前をそのまま比べると、来た人まで「来なかった」ことに

まずは氏名をそのまま比べてみました。COUNTIF を使う式と、XMATCH を使う式の2通りです。

=FILTER(申込[氏名],COUNTIF(参加[氏名],申込[氏名])=0,"なし")
=FILTER(申込[氏名],ISNA(XMATCH(申込[氏名],参加[氏名])),"なし")
空白をそろえずに比べた結果。COUNTIFでは6人、XMATCHでは5人が来なかった人として出てしまう
本当は2人なのに、COUNTIF では6人、XMATCH では5人

本当は2人なのに、COUNTIF では6人、XMATCH では5人も出てきてしまいました(^^;

どちらも、空白がない「青木一郎」「加藤由美」と、後ろに空白がある「斎藤 学 」を別人と判断しています。さらに COUNTIF は、全角の空白の「石井　花子」まで別人扱いでした。同じ名簿なのに、使う関数で結果が変わるということなんです。

関数によって「同じ」の基準が違う!一覧表

そこで、よく使う比べ方を並べて、2つの文字が「同じ」と判断されるかを1つずつ試してみました。○は同じと見なした(見つかった)、×は別のものと見なした(見つからなかった)です。

比べた2つ=EXACTCOUNTIFVLOOKUP
MATCH		XLOOKUP
XMATCH		UNIQUE
ABC / abc(大文字・小文字)○×○○○○
B-301 / Ｂ-301(英字の全角・半角)××××○○
カトウ / ｶﾄｳ(カタカナの全角・半角)××××○○
石井 花子 / 石井　花子(空白の全角・半角)××××○○
斎藤 学 / 斎藤 学 (後ろの空白)××××××

XLOOKUP・XMATCH・UNIQUE は全角と半角の違いを同じものとして扱い、VLOOKUP・MATCH・COUNTIF・「=」は区別しました。一方で、空白が「あるか、ないか」の違いは、どの関数でも別のものになりました。

どの関数を使っても結果が同じになるように、比べる前に空白を消してそろえておくのがいちばん確実です。

手順1:両方の名簿に「照合用」の列を足す

両方の名簿をテーブル(Ctrl + T)にして、名前を「申込」「参加」にしておきます。それぞれの右に「照合用」という列を足して、この式を入れます。テーブルなので、1行目に入れれば下の行にも自動で入りますよ♪

=SUBSTITUTE(SUBSTITUTE([@氏名]," ",""),"　","")
参加の表に、空白をすべて消した照合用の列を足したところ
照合用の列。空白がすべて消えて、両方の名簿で同じ形になった

内側の SUBSTITUTE で半角の空白を、外側で全角の空白を消しています。"　" の中は全角の空白です。

TRIM 関数を使わないのは、途中の空白が残ってしまうからです。前後と途中に全角の空白を入れた「　石井　　花子　」を TRIM すると、前後の空白は消えましたが、途中の2つの空白は1つにまとまって残りました(「石井　花子」の5文字)。「石井花子」と比べるには、途中の空白も消す必要があるんです。

手順2:片方にしかいない人を出す

来なかった人:     =FILTER(申込[氏名],ISNA(XMATCH(申込[照合用],参加[照合用])),"全員参加")
申込なしで来た人: =FILTER(参加[氏名],ISNA(XMATCH(参加[照合用],申込[照合用])),"なし")
照合用の列で比べた完成形。来なかった人は遠藤美咲と小林恵、申込なしで来た人は中村優
来なかったのは遠藤さんと小林さん、申込なしは中村さん

今度はちゃんと、遠藤さん・小林さんの2人と、中村さんの1人だけが出てきました🙌

  • XMATCH(申込[照合用],参加[照合用]):申込の1人ずつについて、参加の名簿の何番目にいるかを探す。いなければ #N/A
  • ISNA(…):#N/A(=見つからなかった)なら TRUE
  • FILTER(申込[氏名],…):TRUE の人の、元の氏名だけを並べる

照合用の列を増やしたくない場合は、LET を使って1つの式にまとめても、同じ2人が出ました。

=LET(a,申込[氏名],b,参加[氏名],
 ka,SUBSTITUTE(SUBSTITUTE(a," ",""),"　",""),
 kb,SUBSTITUTE(SUBSTITUTE(b," ",""),"　",""),
 FILTER(a,ISNA(XMATCH(ka,kb)),"全員参加"))

名簿の横に「参加・未参加」を出して、色も付ける

一覧を別に出すより、申込の名簿に印を付けたいときは、申込の表に「出欠」の列を足してこの式を入れます。

=IF(COUNTIF(参加[照合用],[@照合用])=0,"未参加","参加")

COUNTIF は古い Excel にもある関数なので、FILTER や XMATCH がない版でも使える形です。照合用の列で比べているので、全角・半角の空白の違いも問題になりません。

さらに、申込の表(A2:D11)を選んで「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選び、=$D2="未参加" と入れて塗りつぶしの色を決めると、未参加の人の行に色が付きました✨

申込の表に出欠の列を足し、未参加の2人の行に色を付けたところ
未参加の2人の行に色が付いた

同じ人が2回受付されていないかもチェック

受付で同じ人を2回入力してしまっても、上の式では「参加」と出るだけなので気づけません。サンプルで「上田健」をもう1行足してみましたが、出欠は「参加」のままでした。

重複は、参加の表の横にこの式を入れると見つけられました。

=UNIQUE(FILTER(参加[照合用],COUNTIF(参加[照合用],参加[照合用])>1,"なし"))

「上田健」が表示されました。重複がなければ「なし」と出ます✅

サンプルファイルはこちら

「申込」「参加」シートは照合用・出欠の列と色付けを入れた状態、「照合」シートには、来なかった人・申込なしで来た人を出す式が入っています。氏名はすべて架空です。

meibo-shogo-sample.xlsxダウンロード

マクロは入っていません。ダウンロードしたファイルが「保護ビュー」で開いたら、「編集を有効にする」を押すと式が動きます。

名簿の見比べ、これで目が疲れずにすみますように(*^^*)

この記事の式は、Microsoft 365 の Excel(ビルド 16.0.20527.20150、Windows 11)で、2026年11月8日にすべて試しました。

名簿の照合や出欠の集計を、ボタン1つで終わるようにしたい場合は、ココナラで Excel の自動化のご依頼も受けています。

タイトルとURLをコピーしました