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

Question

２つの会社(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を使用しております。
何かいい方法がありましたらご教示願います。

MackyNo1 · Accepted Answer

＞シート(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),"")

tom04 · Answer

こんばんは！
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

keithin · Answer

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

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

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

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

AkiraHari · Answer

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

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

Fewhellton · Answer

MS Accessを使って当該ワークシートにリンクテーブルを張り、クエリでまとめてからクエリ結果をExcelファイル形式で出力するのが一番楽だと思います。

シート1とシート3を旧取引先コードで結合し、新取引コードと企業情報をクエリ結果１とします。
同様にシート2とシート3からクエリ結果2を得ます。

差分クエリを使いクエリ結果1からクエリ結果2を新取引先コードで差分したクエリ結果3を得ます。（クエリ結果1のうちクエリ結果2と違う新取引先コードのレコードだけ抽出されたのがクエリ結果3）
クエリ結果2とクエリ結果3を結合したクエリ結果4をExcelファイル形式で保存します。

MS Accessはクエリウィザードがあるので、SQLの知識なしでもこの程度ならウィザードに従えばそれぞれのクエリを作成できます。

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

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

こんばんは！

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

取引先データの統合は結構面倒です。

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング