二つの名簿リストを名前をキーに対照させ、両方の名簿リストに乗っていない人
(片方の名簿リストにだけ載っている人)を簡単に特定できるようにする方法をご存知
の方、教えてください。(両リストに名前は必ず書かれていますが、名前の書かれた方は全く
同じではなく、姓と名の間にスペースが有ったり、無かったりしています。
また片方のリストには余分な情報(出身地等)が書かれています。
「簡単に特定できるようにする方法」というのは、VBA を使わないようにという感じでしょうか。ここでは、関数を使った方法を紹介します。
リストが2つのリスト Seet1, Sheet2 に分かれており、各々のA列に姓名が代入されているとします。
*1.名前の余分なスペースを取り除き、別の列に代入する。
Seet1!A, Sheet2!A の各々に TRIM 関数を使って全角・半角スペースを取り除いた結果を、Seet1!G, Sheet2!G に代入する。
*2.Sheet1!A の各々のセルを基準に、Sheet1!B の中に一致するものがあるかどうか検索する。
Sheet1!A の1つ1つのセルに VLOOKUP 関数を適用し、Sheet1!B の全範囲に一致する者があるかどうか検索し、その結果を Sheet1!H に代入する。
スペースがあると一致の判断が難しいので、スペースをなくした列を作ります。
=SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")
これで A1の文字列の途中にある全角と半角の空白をなくします。
これを 最初にセットしたセルをコピーして 全行分に貼り付けます。
この空白をカットしたものを 二つの名簿ともやります。
次に二つの名簿の氏名を比較します。
たとえば A列と D列にそれぞれの名簿の氏名があるとして説明します。
A列のとなり B列に Aの名簿の存在チェックを入れます。同様に
D列のとなり E列に Dの名簿の存在チェックを入れます。
B列は
=COUNTIF(D:D,A1)
E列は
=COUNTIF(A:A,D1)
とやって 全行、それぞれ先頭セルのコピーを貼り付けます。
そうすれば、存在しないものは 0になり、存在するものは 1以上になります。
これで 特定できます。
回答有難うございます。
入力データーの左側から3文字分だけを参照する、といった条件をつけたいのですが、
可能であれば教えてください。
まず両方のリストの整合を取る必要があると考えます。また、どの程度の手間をかけてやるべきなのか、同姓同名をどこまで無視できるか検討してください。
空白が有ったり無かったりしているようですから、それはTRIM関数を使って除けばいいでしょう。
もう片方のリストですが出身地とかの情報をどう除くかです。ちゃんと書いてあれば正しい解答ができるんですが。例えば
山田 太郎(北海道)
といった具合に括弧でくくられているなら、FIND関数で"("の位置を調べてその1つ手前までが名前となります。
通常の名寄せは名前だけではやりません。電話番号も使ったりします。簡単にやろうとすれば社保庁の二の舞になりますよ。
回答、アドバイスありがとうございます。
山田 太郎(北海道)のように
出身地の前は括弧でくくられています。FIND関数で名前だけ取り出す方法を教えていただけ
たらありがたいのですが。
A1セルに「山田 太郎(北海道)」と入っているのであれば、
=LEFTB(A1,FINDB("(",A1,1))
の数式を入れると「山田 太郎」と表示されるはずです。
また、データ→区切り文字→区切り位置→その他「(」→完了とすると北海道と山田太郎で分けられます。
ありがとうございました。
回答有難うございます。
もしVBAを使ったほうが簡単なのであれば、教えてください。