名簿を検索するときに、 同姓同名 のデータがあると困りますよね。その対策として考えられるのが、名前と生年月日で検索するという方法。今回は XLOOKUP 関数を使って 名簿を検索する 同姓同名 の対応を行います。 CONCATENATE 関数と SUBSTITUTE 関数で検索文字列を作成します
Excelで名簿のデータベース（データを入れたシート）からある人の住所を調べたいときは、氏名で検索する。というのが普通ですよね。
しかし、同姓同名の人って結構居たりしませんか？
特に、人数が多い苗字ではかなりの確率で同姓同名が存在しますよね。
そして、名簿への登録人数が多くなればなるほど、この同姓同名の人が居る確率が高くなります。
そうなると、検索がうまくいきませんよね。
そこで、すぐに考えつくのが、名前と生年月日を組み合わせて検索のキーとする方法です。
この方法でも、完璧ではないのですが、同年同日に生まれた同姓同名の人が名簿に存在する確率は、かなり低くなります。
いやいや、完璧じゃなきゃ！！というときは、ユニークな個人番号を振って、その番号で検索するということになってしまいます。
今回は、その氏名と生年月日を組み合わせて、名簿を検索する方法を解説していきます。
CONCATENATE と SUBSTITUTE で同姓同名対策 検索文字列を作成
使う関数はXLOOKUP関数とCONCATENATE関数、SUBSTITUTE関数を使います。
CONCATENATE関数で氏名と生年月日の文字列連結してテーブルに追加し、検索対象文字列を作成します。
検索対象文字列を作成するときに、空白文字を取り除くため、SUBSTITUTE関数をネストして使います。
そして、XLOOKUP関数を使って、入力された氏名と生年月日を連結させて検索を行い、該当の住所を抜き出します。
SUBSTITUTE関数をネストして空白を除去する方法については、過去の記事「VLOOKUP関数で名前から名簿を検索する（2）」も参考にしてみてください。
データベース（テスト用）作成
簡易的にテスト用のデータベースを作成します。
カラムは氏名、生年月日、住所の3カラムとします。
行数は3行にしています。
同姓同名の実験のため「坂本 昭夫」さんが二人登録されています。
そして、一人目の坂本さんは全角の空白が苗字と名前の間に入っていて、二人目の方は、半角の空白が苗字と名前の間に入っています。
今回の検索では、これらの空白入りと空白なしの場合も、検索可能となっています。
検索のシートを次の様に作成しました。
XLOOKUP 検索シート
検索をするシートはデータベースとは別のシートに作成しています。
左のブルーの部分へ、氏名と生年月日を入力すると、右のオレンジの部分へ結果が表示されます。
検索文字列 作成 CONCATENATE SUBSTITUTE
組み合わせて検索するにあたり、データベースに細工を施します。
カラムを追加して、検索対象となる文字列を作成します。
検索文字列は、氏名と生年月日（シリアル値）を連結した文字列とします。
D3を見ていただくと分かりますが、日付は1900年1月1日を1としての通算日数のシリアル数値です。
名前から空白を除いた文字列とシリアル値の文字列を連結した文字列が作成されました。
SUBSTITUTE関数のネストは、内側のSUBSTITUTE関数で全角の空白文字があれば削除し、その結果の文字列を更に、外側のSUBSTITUTE関数で半角の空白文字を削除しています。
その、空白文字を削除した文字列と、生年月日のシリアル値の文字列を連結して検索文字としています。
XLOOKUP 同姓同名 検索式を作成する
検索のページで検索の式を作成します。
XLOOKUP関数の第一引数は検索値です。
SUBSTITUTE関数のネストで検索の氏名から空白を除去した文字列と、生年月日のシリアル値を連結した文字列を指定しています。
第二引数は、検索範囲です。
データベースシート（data）のD列（検索文字のカラム）を指定しています。
第三引数は、戻り範囲です。
データベースシート（data）のC列（住所）を指定しています。
氏名入力のセルから空白を取り除き、生年月日のシリアル値と連結。その値をデータベースの検索文字の列から検索して、一致した行の住所を表示する。という仕組みです。
もし一致するものが無い場合は、XLOOKUP関数の第四引数[見つからない場合]が表示されます。
”見つかりません”を設定しています。
同姓同名 対策まとめ
- まず、データベースに名前と生年月日のシリアル値を連結した検索文字を追加する
- 名前から、SUBSTITUTE関数のネストを使って、空白文字を削除する
- XLOOKUP関数で見つからない場合を指定すると、IFERROR関数を使わずに、指定の文字列を表示できる
以上です。
名前と生年月日の一覧表へ、データベースから該当する住所を抜き出す場合は、今回のXLOOKUP関数の式を各行の住所のセルへコピペすることで、簡単に作成できます。
それでは、また。
コメント