EXCELで３つのシートのデータを統合したい

質問

２つの会社(A社,B社)が合併し、それぞれ別の基幹システム(旧システム)を使っていた為、
新たに全く別の基幹システム(新システム)を作成中なのですが、
A社、B社共通の取引先もあれば、それぞれ独自の取引先もあり、
取引先のデータを新システムに移行させる為、エクセルで一覧を作成中です。

１つのエクセルファイルに以下のような３つシートがあります。
シート(1)
A社得意先コード　取引先名　郵便番号　住所　　　　　　　　　取引条件　・・・・
000001　　　　　　(株)○○○　111-1111　○○県○○市・・・　20日締め　・・・
000002　　　　　　(株)▲▲▲　・・・・

シート(2)
Ｂ社得意先コード　　取引先名　郵便番号　住所　　　　　　　　　取引条件　・・・・
000001-00　　　　　(株)○○○　111-1111　○○県○○市・・・　20日締め　・・・
000002-00　　　　　(株)□□□　・・・・

シート(3)
A社得意先コード　B社得意先コード　新システム得意先コード
000001　　　　　　000001-00　　　　000001-000
000002　　　　　　　　　　　　　　　 　000002-000
　　　　　　　　　 　000002-00　　　　000003-000

シート(1)(2)のデータをシート(3)のコード対比表を基にシート(3)にくっつけたいのです。
A社得意先コード　B社得意先コード　新システム得意先コード　(1)取引先名　(1)郵便番号　(1)住所　(1)取引条件　・・・・　(2)取引先名　(2)郵便番号　(2)住所　(2)取引条件　・・・・
000001　　　　　　000001-00　　　　000001-000　(株)○○○　111-1111　○○県○○市・・・　20日締め　・・・　(株)○○○　111-1111　○○県○○市・・・　20日締め　・・・
(ここ見づらくてすいません)

シート(3)に統合より新たにシートを作成した方がやりやすければそれでもかまいません。
シート(1)(2)にそれぞれ約6000行、80項目くらいあり、VLOOKUPでちまちまやるのは作業ミスを起こしそうであまりやりたくありません。

EXCEL2007を使用しております。
何かいい方法がありましたらご教示願います。

回答No.1

MackyNo1 · ベストアンサー

＞シート(1)(2)にそれぞれ約6000行、80項目くらいあり、VLOOKUPでちまちまやるのは作業ミスを起こしそうであまりやりたくありません。

シート（３）のD列以降に該当データを表示したいなら、行数に関係なく以下のようなVLOOKUP関数で「確実に」表示できます（右方向および下方向にオートフィル）。

=IFERROR(VLOOKUP(A2,Sheet1!A:Z,COLUMN(B1),0),VLOOKUP(B2,Sheet2!A:Z,COLUMN(B1),0))

少し気になるのは、2つのシートに重複するデータがある場合、住所や郵便番号は共通で問題ないのですが、たとえば「取引条件」が異なる場合は、どのように表示するのでしょうか？

私なら、以下のような関数で同じ列の上段にSheet1の条件、下段にSheet2の条件を表示します。

=IFERROR(VLOOKUP(A2,Sheet1!A:Z,COLUMN(B1),0),"")&CHAR(10)&IFERROR(VLOOKUP(B2,Sheet2!A:Z,COLUMN(B1),0),"")

お礼

早速のご回答ありがとうございました。
これいい方法ですね。IFERRORとCOLUMN関数を組み合わせるのは思いつかなかったです。
ただ、これだと、シート(1)かシート(2)のデータがある方をシート(3)の後ろにくっつけてしまいますよね。
分かりにくかったかと思いますが、シート(3)の取引先コードの後ろにシート(1)⇒シート(2)の順にデータを両方くっつけたかったのです。
なので、シート(1)の部分用に
=IFERROR(VLOOKUP($A2,Sheet1!$A:Z,COLUMN(B1),0),"")
とシート(2)の部分用に
=IFERROR(VLOOKUP($B2,Sheet2!$A:Z,COLUMN(B1),0),"")
と分けてやる事でうまくいきました。
あとは結果がちゃんと合っているか確認したら、今日中に完成できそうです。

取引条件等は統一済みなので、新システムへデータを取り込むために1行にしたかったのと、
今後のデータ運用に使えるように(1)(2)それぞれのデータを両方載せたかったので、
このようなめんどくさい事をしました。

ご協力ありがとうございました。

回答No.5

tom04

こんばんは！
VBAでの一例です。

Sheet1・Sheet2のデータをSheet3にまとめるようにしてみました。
各Sheetとも1行目はタイトル行でデータは2行目以降にあるとします。
（1行目の項目は入力済みとします）
会社名と住所のみで検索しています。

Alt+F11キー　→　画面左下の「This Workbook」をダブルクリック　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j, k As Long
Dim ws1, ws2, ws3 As Worksheet
Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)
Set ws3 = Worksheets(3)
Application.ScreenUpdating = False
k = ws3.UsedRange.Rows.Count
If k > 1 Then
ws3.Rows(2 & ":" & k).ClearContents
End If
ws3.Columns("A:C").Insert
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
With ws3.Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
.Value = ws1.Cells(i, 1)
.Offset(, 1) = ws1.Cells(i, 2) & "_" & ws1.Cells(i, 4)
With .Offset(, 3)
.Value = ws1.Cells(i, 1)
.NumberFormatLocal = "000000"
End With
.Offset(, 6) = ws1.Cells(i, 2)
.Offset(, 7) = ws1.Cells(i, 3)
.Offset(, 8) = ws1.Cells(i, 4)
.Offset(, 9) = ws1.Cells(i, 5)
End With
Next i
For j = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
With ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = Val(Left(ws2.Cells(j, 1), 6))
.Offset(, 2) = ws2.Cells(j, 2) & "_" & ws2.Cells(j, 4)
.Offset(, 4) = ws2.Cells(j, 1)
.Offset(, 10) = ws2.Cells(j, 2)
.Offset(, 11) = ws2.Cells(j, 3)
.Offset(, 12) = ws2.Cells(j, 4)
.Offset(, 13) = ws2.Cells(j, 5)
End With
Next j
k = ws3.UsedRange.Rows.Count
Range(ws3.Cells(2, 1), ws3.Cells(k, 14)).Sort key1:=ws3.Cells(1, 1), order1:=xlAscending
For j = k To 2 Step -1
If WorksheetFunction.CountIf(ws3.Columns(2), ws3.Cells(j, 3)) Then
i = WorksheetFunction.Match(ws3.Cells(j, 3), ws3.Columns(2), False)
With ws3.Cells(i, 5)
.Value = ws3.Cells(j, 5)
.Offset(, 6) = ws3.Cells(j, 11)
.Offset(, 7) = ws3.Cells(j, 12)
.Offset(, 8) = ws3.Cells(j, 13)
.Offset(, 9) = ws3.Cells(j, 14)
End With
ws3.Rows(j).Delete (xlUp)
End If
Next j
For i = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
ws3.Cells(i, 6) = WorksheetFunction.Text(i - 1, "000000") & "-000"
Next i
ws3.Columns("A:C").Delete (xlToLeft)
ws3.Columns.AutoFit
Application.ScreenUpdating = True
End Sub 'この行まで

Sheet1・Sheet2のデータ変更があるたびにマクロを実行してください。
※　一旦マクロを実行すると元に戻せませんので別Bookにコピーしてマクロを試してみてください。
※　ご希望通りにならなかったらごめんなさいね。m(_ _)m

お礼

ご回答ありがとうございました。
実行結果は求めるものに近いので、とりあえず足りない項目分の式を追加して完成させたいと思います。
欲を言えば、コードで検索してもらえると一番よかったのですが・・・
マクロを使えるとほんと便利ですよね。
今年はマクロの勉強頑張りたいと思います。
ありがとうございました！

回答No.4

keithin

VLOOKUPを並べるので特にミス無く出来ると思いますが，敢えて生データを駆動する方法でやってみるなら。

シート３全体をＡ列（Ａコード）の昇順で並べ替える
シート１全体をＡ列（Ａコード）の昇順で並べ替える
念のためシート３の
Ｄ２：
=(A2<>Sheet1!A2)*1
以下コピー
SUM(D:D)が「ゼロではない」ときは，シート１または３の一覧のどちらかに漏れがあるので，チェックする
完全に整合したら，シート１から丸ごとデータをコピーしてシート３にドンと貼り付ける

改めて
シート３全体をＢ列（Ｂコード）の昇順で並べ替える
シート２全体をＡ列（Ｂコード）の昇順で並べ替える
念のためシート３の
Ｄ２：
=(B2<>Sheet2!A2)*1
以下コピー
SUM(D:D)が「ゼロではない」ときは，シート２または３の一覧のどちらかに漏れがあるので，チェックする
完全に整合したら，シート２から丸ごとデータをコピーしてシート３にドンと貼り付ける

必要に応じてシート３全体をＣ列（新コード）昇順で並べ替えて完成。

お礼

ご回答ありがとうございます。
最初はこれも考えましたが、ちゃんと並び替えされていなかったりして
うまくコードと取引先情報の整合がとれないんじゃないか心配だったので、やめました。
間違いが許されないデータなだけに確実な方法を探してみたくて・・・
ありがとうございました。

回答No.3

AkiraHari

取引先データの統合は結構面倒です。
Excelの機能でとかAccessでならばという以前の問題が大変なのです。
同じ相手なら取引先名は同じと思うでしょうが、実際はそう単純にいきません。
例えば「ＪＲ東日本」という会社名をどのように入力されているかです。
正式名称は「東日本旅客鉄道株式会社」です。
そこで次なケースが考えられます。
東日本旅客鉄道株式会社
東日本旅客鉄道(株)
東日本旅客鉄道(株)
JR東日本
ＪＲ東日本
さらに会社名に付加情報を記入している場合もあります。
住所にしても「1丁目1番地1号」とか「1-1-1」とかさまざまです。

そこで、2つのリストをまとめて、電話番号や住所でソートし、目で確認するしかないでしょう。
電話番号でソートする際には区切り文字を統一してから行います。
勿論、データは統一ルールで入力されているというなら、単純です。ソート後に前の行と同じであればしるしを付けるようにしておけば簡単に解ります。

お礼

早速のご回答ありがとうございます。
おっしゃる通り、前段階のデータを統一するのはかなり大変でした。
これはすでに終わらせているので、新システムへ取り込む為と
後々の運用を考えてのデータ作りをしている段階です。

＞勿論、データは統一ルールで入力されているというなら、単純です。ソート後に前の行と同じであればしるしを付けるようにしておけば簡単に解ります。

これがどういう意味なのかがちょっと気になりますが・・・

回答No.2

noname#217196

MS Accessを使って当該ワークシートにリンクテーブルを張り、クエリでまとめてからクエリ結果をExcelファイル形式で出力するのが一番楽だと思います。

シート1とシート3を旧取引先コードで結合し、新取引コードと企業情報をクエリ結果１とします。
同様にシート2とシート3からクエリ結果2を得ます。

差分クエリを使いクエリ結果1からクエリ結果2を新取引先コードで差分したクエリ結果3を得ます。（クエリ結果1のうちクエリ結果2と違う新取引先コードのレコードだけ抽出されたのがクエリ結果3）
クエリ結果2とクエリ結果3を結合したクエリ結果4をExcelファイル形式で保存します。

MS Accessはクエリウィザードがあるので、SQLの知識なしでもこの程度ならウィザードに従えばそれぞれのクエリを作成できます。

お礼

早速のご回答ありがとうございます。
そうですよね。Access使った方が簡単ですよね。
ただ、Accessが自分のパソコンに入っておらず、他の人のを借りないといけないので、
Excelで出来る方法を探していました。
パソコンが空いたら試してみます。
ありがとうございました。