Excel シート間のデータの照合

質問

Excelで、シート間のお客様データ（だいたい各1万件）を照合します。下記は現在の照合方法ですが、これでは時間がかかるうえ手作業が多く発生しミスにつながります。頻繁に行う作業なので、関数でも、マクロでも、とにかくもう少し簡単にできる方法がありましたら、どうぞご教授ください。よろしくお願いします！！
【目的】
シート「sheet2008」には2008年度のデータ。シート「sheet2007」には「sheet2008」と同じ形式の2007年度のデータが入っています。シート「sheet2008」に、そのお客様の2007年度の担当営業マンを表示させたいのです。
【例】
列A（電話番号）： 011-231-1112
列B（名前）：佐藤　一郎
列C（住所）：北海道札幌市中央区北1-1-1
列D（担当営業マン）：鈴木
新規の列（2007年度の担当営業マン）：鈴木
　・「sheet2008」「sheet2007」はほぼ同じデータですが、一部のお客様は名前が変わっていたり、住所が変わっていたりします。
　・「sheet2007」にないお客様が「sheet2008」にあったり、その逆があったりして、各シートのデータ件数は一致しません。
　・名前が同じでも住所が違うデータ、電話番号が同じでも担当営業マンが違うデータは別者として扱います。
　・「顧客ID」のような“必ずユニークな情報”は存在しません。
【現在の照合方法】
（1)「sheet2008」の各列の前に空白列を挿入する。
　（データの1行目はタイトル行…B1：電話番号／D1:名前/F：住所／H：担当営業マン）
　（データの2行目以降はデータ）
　　　　列A（空白行）：
　　　　列B（空白行）： 011-231-1112
　　　　列C（空白行）：
　　　　列D（名前）：佐藤　一郎
　　　　列E（空白行）：
　　　　列F（住所）：北海道札幌市中央区北1-1-1
　　　　列G（空白行）：
　　　　列H（担当営業マン）：鈴木
（２）「sheet2007」を列Aの電話番号で昇順に並べ替える。
（３）「sheet2008」の電話番号が「sheet2007」にあるかを調べる。
　　　　A2：「=IF(B2=(VLOOKUP(Sheet2008!$B2,Sheet2007!$A:$D,1,0)),"○","▲")」
（４）（３）で調べた「sheet2008」の電話番号と同じ行にある名前／住所が「sheet2007」にあるかを調べる。
　　　　C2：「=IF(D2=(VLOOKUP(Sheet2008!$B2,Sheet2007!$A:$D,2,0)),"○","▲")」
　　　　E2:「=IF(F2=(VLOOKUP(Sheet2008!$B2,Sheet2007!$A:$D,3,0)),"○","▲")」
（５）電話番号／名前／住所がすべて一致するデータについて、「sheet2007」にある担当営業マンの値を列Gに表示させる。
　　　　G2:「=IF((AND(A2="○",C2="○",E2="○"))=TRUE,(VLOOKUP($B2,Sheet2007!$A:$D,4,0)),"▲")
（６）"▲"やエラー値で表示される計算結果について、目視で確認する。
（終了）

回答No.1

pascal3141 · ベストアンサー

（５）電話番号／名前／住所がすべて一致するデータについて、２００７年度の担当者を２００８年度シートの新規の列に表示させるだけでしたら、各シートのＡ列に作業列をつくり、そこに、＝　電話番号　＆　名前　＆　住所　の形で、３つを合成した検索データを作ります。あとはＶｌｏｏｋｕｐで、一致する物だけ担当者名を表示すれば、１回で済みますが。

お礼

そうすると（１）～（５）の手順はだいぶ省略されますね、ありがとうございます！

回答No.2

imogasi

こういう作業はアクセスのSQLでも使わないと難しい。
また顧客IDが無いので、問題を難しくしている。
これらは仕事関係のエクセルの利用のケースで、私の持論の、エクセルは仕事に使うにはVBAのプログラムを組めることが必要という持論に当てはまるケースだ。
＞頻繁に行う作業なので
内容から、年度ごとのデータと思うが、頻繁とは？
ーー
また目的が、Sheet2008に営業マンの氏名を入力するのか、紙ベースに
なっている帳票に書き込むのか。
ーー
氏名だけでマッチングしてもよいと思うが、念入りにやるには、２００７年シートで、氏名＋電話番号列でソートし、同じヒトと判断！する人を抜き出す。
それ以外は別人。別人には佐藤　一郎１（２，３・・）とサブ番号つきに手作業で直す。１万人程度だと、同姓同名はそんなに多く出ないと思う。
重複分なども判断して削除する。
ーー
同じく２００８データについても、同じ作業をして、２００７のデータとその処理を参考に生かして、同姓同名分をサブ番号つきに直す。
これで両者VLOOKUP関数（またはMATCH関数が使える土台が出来たことになる。
ーーー
VLOOKUP関数をシートに入れる（式を複写する）のは、手数と、メモリを食うので下記のVBAで処理するのも一案。
ーー
ALT+F11キー
メニュうーの
挿入ー標準モジュール
この
標準モジュールの画面に、下記を貼り付け。
Sub test02()
On Error GoTo err1
d = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
For i = 2 To d
x = WorksheetFunction.Match(Worksheets("Sheet1").Cells(i, "B"), _
Worksheets("Sheet2").Range("B1:B20000"), 0)
On Error GoTo err1
Worksheets("Sheet1").Range("D" & i) = Worksheets("Sheet2").Range("D" & x)
GoTo p1
err1:
Worksheets("Sheet1").Cells(i, "D") = "NF"
p1:
Next i
End Sub
実行はVBE画面でF５キーを押す。
列がB,Dの文字を使っているところなので、実情に合わせて修正にこと。
------------
質問者の場合に合わせての列などの修正は下記と比べて考えてください。
データ
Sheet2　（２００７年は）
電話 氏名 住所 担当
011-231-1112 佐藤　一郎 北海道札幌市中央区北1-1-1 鈴木
011-231-1113 近藤　一郎 北海道札幌市北区北1-1-2 山田
011-231-1115 木村　健 秋田県秋田市紙町２３ 鈴木

Sheet1　（２００８年は）
電話 氏名 住所 担当
011-231-1113 近藤　一郎 北海道札幌市北区北1-1-2 ○○
011-231-1115 木村　健 秋田県秋田市紙町２３ ○○
011-231-1112 佐藤　一郎 北海道札幌市中央区北1-1-1 ○○
011-231-1114 北野　太郎 青森県青森市北島１－３４ ○○

となっていて、○○のところ絵担当者を入れることを考えている。
実行結果は
Sheet1
電話 氏名 住所 担当
011-231-1113 近藤　一郎 北海道札幌市北区北1-1-2 山田
011-231-1115 木村　健 秋田県秋田市紙町２３ 鈴木
011-231-1112 佐藤　一郎 北海道札幌市中央区北1-1-1 鈴木
011-231-1114 北野　太郎 青森県青森市北島１－３４ NF
ーーーー
重複氏名行は
電話 氏名 住所 担当
011-231-1113 近藤　一郎 北海道札幌市北区北1-1-2 山田
011-231-1115 木村　健 秋田県秋田市紙町２３ 鈴木 1
011-231-1112 佐藤　一郎 北海道札幌市中央区北1-1-1 鈴木 1
011-231-1114 北野　太郎 青森県青森市北島１－３４ NF
木村　健 1
佐藤　一郎 1
F２に　=IF(COUNTIF($B$2:$B$20000,B2)=1,"",1)
と入れて下方向に式を複写。F列＋氏名で並べ替え。
上の方の行に重複氏名が出るので、その他住所情報などで、同姓同名の別人か、同一人か判断！や調査する。

お礼

丁寧に解説いただきありがとうございます！　さっそくVBAでも実行してみて、処理できました。ご指摘のとおり顧客IDが無いので、それぞれのシートでサブ番号つきに手作業で直すのが、面倒でも近道かもしれませんね。