서로 다른 구조의 두 Excel 파일의 데이터를 병합하는 방법은 무엇입니까?

Question

재무 데이터가 포함된 두 개의 매우 큰 Excel 파일이 있습니다. 한 파일의 데이터를 다른 파일의 데이터와 결합해야 합니다. 첫 번째 파일의 모든 줄에는 카테고리 코드가 할당되어 있습니다. 두 번째 파일의 일부 줄에는 동일한 코드가 있을 수 있습니다.
첫 번째 파일의 모든 줄을 두 번째 파일의 동일한 코드를 사용하여 일치하는 모든 줄과 결합해야 합니다. 파일의 열 수가 다릅니다.

이 문제를 어떻게 해결해야 합니까?

Answer 1

먼저 데이터를 정렬하는 데 필요한 파일에 일부 열을 추가한 다음 가장 작은 파일부터 가장 큰 파일까지 데이터를 잘라내어 붙여넣은 다음 카테고리 코드별로 정렬합니다.

VBA에서 이를 수행하는 한 가지 방법은 다음과 같습니다. 이 코드는 NACE 값을 보유한 셀이 동일한 경우에만 복사되지만 필요에 따라 수정할 수 있습니다. 지금은 전체 행을 첫 번째 통합 문서에 복사합니다.

Private Sub CopyRows()

Dim FirstSheet As Range
Dim SecondSheet As Range
Dim s1col As Integer, s2col As Integer
Dim nextrow As Integer, secondendrow As Integer
Dim copyrow As Range, col As Range
Dim firstsheetrow As Range, secondsheetrow As Range
Dim NACE() As String, Limit As Integer, Index As Integer
Dim testrange As Range

Set FirstSheet = ActiveSheet.UsedRange
Set SecondSheet = Workbooks("Book2").Sheets("Sheet1").UsedRange

For Each col In FirstSheet.Columns
    If Not col.Cells(1).Find("NACE") Is Nothing Then
        s1col = col.Column
        Exit For
    End If
Next col

For Each col In SecondSheet.Columns
    If Not col.Cells(1).Find("NACE") Is Nothing Then
        s2col = col.Column
        Exit For
    End If
Next col


''//Fill NACE array with distinct entries from first sheet
nextrow = FirstSheet.Rows.Count + 1

ReDim Preserve NACE(1 To 1)
NACE(1) = FirstSheet.Rows(2).Cells(1, s1col).Value

For Each firstsheetrow In FirstSheet.Range("3:" & nextrow - 1).Rows
    Limit = UBound(NACE)
    If instrArray(NACE, firstsheetrow.Cells(1, s1col).Value) = 0 Then
        ReDim Preserve NACE(1 To Limit + 1)
        NACE(Limit + 1) = firstsheetrow.Cells(1, s1col).Value
    End If
Next firstsheetrow

''//Copy lines from second sheet that match a NACE value on the first sheet
secondendrow = SecondSheet.Rows.Count

For Each secondsheetrow In SecondSheet.Range("2:" & secondendrow).Rows
    Index = instrArray(NACE, secondsheetrow.Cells(1, s2col).Value)
    If Index > 0 Then
        secondsheetrow.Copy
        ActiveSheet.Rows(nextrow).PasteSpecial (xlPasteValues)
    End If
Next secondsheetrow

End Sub

이 코드는 메인 루틴을 지원하기 위해 모듈에 들어가야 합니다:

Public Declare Sub CopyMemory Lib "kernel32" _
   Alias "RtlMoveMemory" _
  (pDest As Any, _
   pSrc As Any, _
   ByVal ByteLen As Long)

Public Function GetArrayDimensions(ByVal arrPtr As Long) As Integer

   Dim address As Long
  'get the address of the SafeArray structure in memory

   CopyMemory address, ByVal arrPtr, ByVal 4

  'if there is a dimension, then
  'address will point to the memory
  'address of the array, otherwise
  'the array isn't dimensioned
   If address <> 0 Then

     'fill the local variable with the first 2
     'bytes of the safearray structure. These
     'first 2 bytes contain an integer describing
     'the number of dimensions
      CopyMemory GetArrayDimensions, ByVal address, 2

   End If

End Function

Public Function VarPtrArray(arr As Variant) As Long

  'Function to get pointer to the array
   CopyMemory VarPtrArray, ByVal VarPtr(arr) + 8, ByVal 4

End Function

Function instrArray(strArray, strWanted, _
    Optional CaseCrit As Boolean = False, _
    Optional FirstOnly As Boolean = True, _
    Optional Location As String = "exact") As Long
     '
     '****************************************************************************************
     '       Title       instrArray
     '       Target Application:  any
     '       Function:   searches string array for some "wanted" text
     '       Limitations:
     '       Passed Values:
     '           strArray    [in, string array]  array to be searched
     '           strWanted   [in, string]  text for which strArray is searched
     '           CaseCrit    [in, Boolean, Optional]
     '               if true, case (upper/lower) of each character is critical and must match
     '               if false, case is not critical {default}
     '           FirstOnly   [in, Boolean, Optional]
     '               if true, proc exits after first instance is found {default}
     '               if false, proc search to end of array and last instance # is returned
     '           Location    [in, string, Optional] text matching constraint:
     '               = "any"     as long as strWanted is found anywhere in strArray(k),i.e.,
     '                               instr(strArray(k),strWanted) > 0, then instrArray = K
     '               = "left"    match is successful only if
     '                               Left(strArray(K),Len(strWanted) = StrWanted
     '               = "right"    match is successful only if
     '                               Right(strArray(K),Len(strWanted) = StrWanted
     '               = "exact"    match is successful only if
     '                               strArray(K) = StrWanted       {default}
     '
     '****************************************************************************************
     '
     '
    Dim I       As Long
    Dim Locn    As String
    Dim strA    As String
    Dim strB    As String

    instrArray = 0
    Locn = LCase(Location)
    Select Case FirstOnly
        Case True
            For I = LBound(strArray) To UBound(strArray)
                Select Case CaseCrit
                Case True
                    strA = strArray(I):     strB = strWanted
                Case False
                    strA = LCase(strArray(I)):  strB = LCase(strWanted)
                End Select
                If instrArray2(Locn, strA, strB) > 0 Then
                    instrArray = I
                    Exit Function
                End If
            Next I
        Case False
            For I = UBound(strArray) To LBound(strArray) Step -1
                Select Case CaseCrit
                Case True
                    strA = strArray(I):     strB = strWanted
                Case False
                    strA = LCase(strArray(I)):  strB = LCase(strWanted)
                End Select
                If instrArray2(Locn, strA, strB) > 0 Then
                    instrArray = I
                    Exit Function
                End If
            Next I
    End Select

End Function

Function instrArray2(Locn, strA, strB)
     '
     '****************************************************************************************
     '       Title       instrArray2
     '       Target Application:  any
     '       Function    called by instrArray to complete test of strB in strA
     '       Limitations:    NONE
     '       Passed Values:
     '           Locn    [input, string] text matching constraint (see instrArray)
     '           strA    [input, string] 1st character string
     '           strB    [input, string] 2nd character string
     '
     '****************************************************************************************
     '
     '

    Select Case Locn
    Case "any"
        instrArray2 = InStr(strA, strB)
    Case "left"
        If Left(strA, Len(strB)) = strB Then instrArray2 = 1
    Case "right"
        If Right(strA, Len(strB)) = strB Then instrArray2 = 1
    Case "exact"
        If strA = strB Then instrArray2 = 1
    Case Else
    End Select

End Function

유틸리티 코드가 발견되었습니다여기그리고여기.

Answer 2

이러한 종류의 작업은 Microsoft 액세스의 의미이며 "왼쪽 조인"이라고 합니다. 하지만 Excel에서는 vlookup을 사용하거나 match 및 index 기능을 사용하여 이 작업을 수행할 수 있습니다. 개인적으로 나는 일치/인덱스를 선호합니다.

Sheet1 A:F가 첫 번째 파일이고 두 번째 파일을 Sheet2 A1:Q500에 저장한다고 가정합니다. 귀하의 코드가 둘 다의 A열에 있다고 가정해 보겠습니다. 그런 다음 G2의 sheet1에 다음을 입력하십시오.

=MATCH(A2,Sheet2!A$1:A$500,0)

그런 다음 H2 유형에 다음을 입력합니다.

=INDEX(Sheet2!B$1:B$500,$G2)

그런 다음 이것을 끌어서 아래로 모두 끌어 놓습니다.

Answer 3

두 파일의 크기에 따라 Excel 파일에서 쿼리를 사용해 볼 수도 있습니다.

• 첫 번째 Excel 테이블의 이름을 정의합니다(수식 탭 -> 이름 정의).
• 두 번째 Excel 테이블의 이름 정의
• 데이터 탭으로 이동하여 "기타 소스에서"를 선택하고 드롭다운에서 "Microsoft Query에서"를 선택합니다.
• 통합 문서 파일을 선택하고 열을 수동으로 병합할지 확인하세요.
• 다음 창 "Excel 파일에서 쿼리"에서 첫 번째 테이블의 첫 번째 열을 두 번째 테이블의 첫 번째 열에 끌어다 놓으면 이 열 사이에 링크가 생성됩니다.
• 파일 메뉴로 이동하여 "MS Office Excel로 데이터 반환"을 클릭하면 데이터 가져오기 대화 상자가 나타납니다.
• 일치하는 데이터를 가져올 시트를 선택하세요.
• 확인을 클릭하세요. -> 두 테이블의 열과 일치하는 데이터가 표시됩니다.