エクセルで名簿の照らし合わせの仕方を教えてください

質問

エクセルで作ってある
名簿の照らし合わせをやっているのですが
じつは手作業なのです。

Ａの表に載っている人が
Ｂの表にも載っているという場合
の見つけ方を教えてください
よろしくお願いします

Ａの表はイベント参加不適格者で１２００人程があり
Ｂの表はイベント参加申込者で１６０００人程です
参加不適格者が参加申し込みをしていた場合チケットを送らずに
今回は参加を見合わせてもらう趣旨の電話連絡をするための洗い出し
作業になります
表は右セルから番号/名前/住所/電話番号
の順で入っています
１２００回の検索はきついです
いっぺんにやる方法は無いでしょうか
困っています
よろしくお願いします

回答No.7

noname#79209

老婆心ながら．．．
イベント参加不適格者の内容とイベント参加申込者の内容は同じなのでしょうか？
人間の目で見て同じと判断できても、
「山田 太郎」と「山田　太郎」（左は半角スペース、右は全角スペース）のように異なってしまうと計算式は別物と判断します。
入力する人が異なったりすると「スペースの全角、半角、数」、「ハイフンと長音」「住所などの分かち書きの区切り位置」などが微妙に異なることも有ります。
このように、双方のデータを整形し同一の書式や約束事に統一しないとかえって手間がかかってしまいます。

回答No.6

kigoshi

No.4です。
エクセル初心者の方だったんですね。
以下の手順通りやればできます。

もうちょっと頑張ってみましょう。

１）インターネットの画面（この画面）で
　　=SUBSTITUTE(SUBSTITUTE(A2,"　","")," ","")
　　この式↑を直接マウスで選択し反転させ、[Ctrl]を押しながら[C]を押します。
２）エクセルに移って、Ａ表のB2セルを選択し入れ[Ctrl]を押しながら[V]を押したあと
　　[Enter]を押します。
３）もう一度B2セルを選択し、[Ctrl]を押しながら[C]を押します。
４）B3セルを選択し[Shift]を押しながら[↓]を押し続け、Ａ列に名前が無くなる行まで
　　選択します。
５）[Ctrl]を押しながら[V]を押します。
　　※ここまででＡ表のＢ列に空白を削除したものが入ってくれるはずです。
６）Ｂ表でも同様に１）～５）を行います。

７）Ｂ表のC2セルに　=IF(COUNTIF(Ａ表!B:B,B2)>0,"▲","◎")　を入れ[Enter]を押
　　します。
８）もう一度C2セルを選択し、[Ctrl]を押しながら[C]を押します。
９）C3セルを選択し[Shift]を押しながら[↓]を押し続け、Ａ列に名前が無くなる行まで
　　選択します。
10）[Ctrl]を押しながら[V]を押します。
　　※ここまでで不適格者には▲がついてくれています。以下、念のため式を値に置き換え
　　　ます
11）Ｂ表で[Ctrl]を押しながら[A]を押します。
12)[Ctrl]を押しながら[C]を押します。
13)[編集]→[形式を選択して貼り付け]→[値(V)]にチェック→[ＯＫ]

これで完了です。

お役に立ちますように。

回答No.5

zap35

＃０１です。
＞照合に使用する値
とは今回の場合は「名前」に当たります

=IF(COUNTIF(Ａ表!A:A,A1)>0,"ＮＧ","ＯＫ")
のＡ表!A:Aは名前がＡ表のＡ列にあることを意味します。Ｂ列にあるならＡ表!B:Bに変わります。

COUNTIF関数のヘルプを見ていただくと詳しいことが書いてありますよ

補足

わたしには凄く難しくて・・・

表のレイアウトを仮にですが例として作りました

Ａ表（見合わせてもらう方・・不適格者）は２人として、このような表ので
A　　Ｂ
1 名前 空白
２入来　空白
3 安田　空白

Ｂ表（参加申込者）は６人として、このような表のもので
A　　Ｂ
1 名前　空白
2 甲斐　空白
3 高田　空白
4 入来　空白
5 陽方　空白
6 西川　空白
７河野　空白

という場合
Ｂ表のセルの列「Ｂ」をクリックして
=IF(COUNTIF(Ａ表!A:A,A2)>0,"ＮＧ","ＯＫ")
を打ち込めば良いと言うことでしょうか

やっては見たのですが
エラーともいえない説明にならない吹き出しが出てきてしまいました
あなたの説明ではなくわたしのやり方が違うのはわかっているのですが
こちらの表をもとにして
もう一度教えてください

回答No.4

kigoshi

No.2のご回答にもありますように、２つの表のレイアウトが不明なので直接的な回答ができないかとおもいます。
> 表は右セルから番号/名前/住所/電話番号
と書かれていますが、「左から」ではないのですね。
Ａ列：電話番号
Ｂ列：住所
Ｃ列：名前
Ｄ列：番号
ということでよろしいでしょうか。
もし違うのでしたら、具体的なレイアウトを書き込まれると、具体的な式を教えてもらえると思います。
また、２つの表とも同じレイアウトなのかどうかも補足した方がよろしいと思います。

名前が一致しているものを探したい（つまり「照合に使用する値」は名前）ということでよろしいでしょうか。
その場合２つの表で名前の表記は一致していると仮定して良いでしょうか。
姓と名の間の空白や漢字表記は統一されていることが前提となることを了解しておかないと漏れが生じる可能性があります。
具体的には「高沢 一郎」「高沢　一郎」「髙澤一郎」「高澤　一郎」は全て別名となってしまいます。

もし、漢字表記は大丈夫ということでしたら、姓と名の間および氏名前後の空白を削除（ツメ）することは可能です。
=SUBSTITUTE(SUBSTITUTE(A1,"　","")," ","")
最低でもこの加工をいちど通してからNo.1～3のご回答にあるようなCOUNTIFを使用した検索をされた方がよいと思います。

補足

SUBSTITUTE とは文字列中の指定された文字をほかの文字に置き換える関数なのですね
確かに必要なことだと思います
全てのセルを指定してこれ
=SUBSTITUTE(SUBSTITUTEA1,"　","")," ","")
を打ち込んでエンターを押せばよいのでしょうか
やってみたのですが数式を計算できませんの吹き出しが出てしまいました
あなたの説明ではなくわたしのやり方が違うのはわかっているのですが
こちらの表をもとにして
もう一度教えてください
お願いします

回答No.3

suekun

あちゃ・・・無視されちゃいましたね。＾＾；

1200件のデーターに対して、重複があればその電話番号とか
連絡先とかを拾ってくる数式なんですがね。

もう一度だけ説明して、それでも無視なら手を引きます。

元データー16000人分。

「シートB」
　　　　A　　　B　　　C　　　D
1　　　番号　 名前　 住所　 電話
2　　　001　　山田　 東京　 033-・・
～～～～～～～～～～～～～～～
16001　16000　斉藤　 大阪　 066-

不適合者　1200人分
「シートA」

　　　　A　　　B
1　　　名前
2　　　伊藤
3　　　鈴木
～～～～～～～～～～～
1201　 山下

シートA　B2セルに、
=IF(COUNTIF(SheetB!$B$2:$B$16001,A2),OFFSET(SheetB!$B$1,MATCH(A2,SheetB!$B$2:$B$16001,0)-1,2),"")

これを1200件分、下にフィルコピーすれば、名前の横（B列）に
電話番号がでる仕組なのですけどね。

件数が多いので、少し重いかもしれませんが・・・

補足

すいません　無視していないです
なかなかうまくいかなくて
明日日曜日に頑張ってみようと思っていたものですから
すみません

回答No.2

suekun

表の配置が見えないので、実際の状況に合わせて下さい。

=IF(COUNTIF(16千の名簿番号のある範囲,1.2千の名簿の番号のある範囲の一番上のセル),OFFSET(16千の名簿番号の一番上のセル,MATCH(1.2千の名簿の番号のある範囲の一番上のセル,16千の名簿番号のある範囲,0)-1,1),"")

=IF(COUNTIF(A:A,C2),OFFSET($A$1,MATCH(C2,A:A,0)-1,1),"")
この数式は、
1、16千の名簿範囲がA：Aとした場合で、尚且つ最初のセルがA1だった場合です。
2、1.2千の名簿番号の始まりがC2だった場合です。
3、範囲とOFFSETの基準セルは絶対参照です。
4、番号の右となりにある情報を返します。
　（以外の電話番号とかが欲しければ、
OFFSET($A$1,MATCH(C2,A:A,0)-1,1),"")　の　-1,の後ろの1を
変更して下さい。

回答No.1

zap35

氏名で照合するのですか？　それとも会員ＩＤのようなものがあるのですか。

Ａ表、Ｂ表共にＡ列に照合に使用する値があるとき、Ｂ表の空いている列に
=IF(COUNTIF(Ａ表!A:A,A1)>0,"ＮＧ","ＯＫ")
を入力し下方向にコピーすれば照合できます。

補足

会員ＩＤはまだ無いです
今後の課題となっています

「Ａ表、Ｂ表共にＡ列に照合に使用する値があるとき、Ｂ表の空いている列に=IF(COUNTIF(Ａ表!A:A,A1)>0,"ＮＧ","ＯＫ")

ちょっと・・難しくて・・・
「照合に使用する値」とは具体的にわかりやすい解釈ですとどんな意味でしょうか

=IF(COUNTIF(Ａ表!A:A,A1)>0,"ＮＧ","ＯＫ")
を実行してみます
すばやいご返答ありがとうございます