개발하는 디자이너의 DIY 일상

이번에 소개해드릴 부분은 시트간 데이터를 비교하는 방법입니다.

VBA 로 뭔가를 하게되면서 가장 유용하게 많이 사용하는 것 중 하나죠.

예를 들면 Sheet1 에 어떤 데이터 리스트가 있고 Sheet2 에 똑같은 유형의 업데이트 된 다른 리스트가 있다고 했을 때 두 데이터를 비교해서 차이가 있는 것을 마킹한다던가 특정 값이 더 높은 데이터를 찾는다든가 아니면 B 의 시트에서 A에 공통으로 있는 항목만 찾는 등의 작업을 할때 아주 유용한 개발 내용입니다. 이런 류의 작업을 눈으로 사람이 한다는건 데이터의 양에 따라 다르기는 하지만 데이터가 1000개 또는 10000개가 넘는다면 정말 말도 안되는 작업이죠.

일단 기본 개념은 아래와 같습니다.

• 변수 선언
  • 두개의 시트를 지정할 변수를 선언한다. (worksheet)
    • 만약 두개의 시트가 각각 다른 엑셀문서에 존재한다면 엑셀 문서지정을 위한 변수를 선언한다. (workbook)
  • 두 시트에 비교해야할 영역을 설정하기 위한 변수를 각각 선언한다. (range)
• 실제 코드 부분
  • 엑셀문서와 시트를 설정한다
  • 각각의 영역을 설정한다
  • A 영역을 반복하여 도는 For 구분을 만든다
    • A 영역을 한번 반복하는 동안 B 영역을 반복하는 For 구분을 삽입한다.
    • 필요한 조건문을 작성하여 조건에 해당되는 경우 특정 액션을 수행한다.

이런 식이 됩니다.

처음 보시는 분은 어리둥절 할 수도 있지만 막상 몇번 코딩을 해보면 아주 간단한 구문 입니다.

아래 간단한 예제를 한번 볼까요?

하나의 엑셀파일에 있는 두개의 시트를 비교하는 방법입니다.

Sub check()

    Dim shtA As Worksheet
    Dim shtB As Worksheet
    
    Dim rngA As Range
    Dim rngB As Range

    Set shtA = Worksheets("Sheet1") '워크시트의 이름을 넣으세요'
    Set shtB = Worksheets("Sheet2") '워크시트의 이름을 넣으세요'
    
    Set rngA = shtA.Range("A2") '첫번째 시트의 비교할 시작행의 번호를 넣으세요'
    Set rngA = shtA.Range(rngA, rngA.End(xlDown)) '세로로 데이터가 있는 끝까지 자동으로 설정합니다'
        
    Set rngB = shtB.Range("A2") '두번째 시트의 비교할 시작행의 번호를 넣으세요'
    Set rngB = shtB.Range(rngB, rngB.End(xlDown)) '세로로 데이터가 있는 끝까지 자동으로 설정합니다'
    
    For Each c In rngA
        c.Interior.Color = xlNone '초기 색상을 없애 줍니다'        
        For Each d In rngB
        	if c.value = d.value then '해당되는 칸의 값이 같을 경우'
            	
                ' 오른쪽 바로 옆칸의 값이 다를경우'
                if c.offset(0,1).value <> d.offset(0,1).value then
                	c.Interior.Color = RGB(255,0,0) '빨간색으로 셀을 색칠한다'
                end if
                
                '동일한 값이 한번이 있고 넘기려면 반복을 끝낸다'
                Exit For                
            end if        
        next d        
    Next c
    
End Sub

간단하지요?

반복문안에서 다시 반복을 하면서 값을 찾아내는 방법입니다. 간단하지만 아주 유용한 방법입니다.

다른 엑셀파일에 있는 정보를 비교하려면?

만약 두개의 다른 엑셀 시트에 있는 시트를 비교해야 한다면 위에서 소개한 코드에 워크북을 설정해주는 부분만 추가하면 됩니다.

워크북은 아래와 같이 설정하게 되죠.

Sub check()
	
    Dim bookA as Workbook '워크북 변수'
    Dim bookB as Workbook '워크북 변수'
    
    Dim shtA As Worksheet
    Dim shtB As Worksheet
    
    Dim rngA As Range
    Dim rngB As Range
    
    set bookA = Workbooks("firstExcelFile.xlsx")'첫번째 엑셀 파일명을 입력, 확장자 포함'
    set bookB = Workbooks("secondExcelFile.xlsx")'두번째 엑셀 파일명을 입력, 확장자 포함'
    
    set shtA = bookA.Worksheets("Sheet1")
    set shtB = bookB.Worksheets("Sheet2")
    
    ' 이하 동일 '
    

역시 간단하죠?

저런 방식으로 두개의 엑셀 파일 또는 그 이상의 엑셀 파일을 지정하여 값을 비교할 수 있습니다.

다른 시트의 값을 찾아 원본 시트에 동일한 명칭에 가져오는 방법

만약 반복 구문을 도는 동안 두번째 데이터의 값에서 동일한 명칭을 찾아 그에 해당되는 값을 첫번째 시트에 넣는다면 아래와 같이 하면 됩니다. 실제로 찾아서 값을 넣은 개수도 확인할 수 있도록 해보겠습니다. 

Sub check()

    Dim shtA As Worksheet
    Dim shtB As Worksheet
    
    Dim rngA As Range
    Dim rngB As Range
	
    Dim matchingNumber as integer '매칭된 대상 개수 파악용'
    
    Set shtA = Worksheets("Sheet1") '워크시트의 이름을 넣으세요'
    Set shtB = Worksheets("Sheet2") '워크시트의 이름을 넣으세요'
    
    Set rngA = shtA.Range("A2") '첫번째 시트의 비교할 시작행의 번호를 넣으세요'
    If Len(rngA.Offset(1, 0)) Then ' 만약 시트에 데이터가 딱 한줄일 경우 아래쪽까지 range 를 설정하지 않는다.'
    	Set rngA = shtA.Range(rngA, rngA.End(xlDown)) '세로로 데이터가 있는 끝까지 자동으로 설정합니다'
    end if
    
    Set rngB = shtB.Range("A2") '두번째 시트의 비교할 시작행의 번호를 넣으세요'
    if  Len(rngB.Offset(1, 0)) Then ' 만약 시트에 데이터가 딱 한줄일 경우 아래쪽까지 range 를 설정하지 않는다.'
    	Set rngB = shtB.Range(rngB, rngB.End(xlDown)) '세로로 데이터가 있는 끝까지 자동으로 설정합니다'
    end if
    
    For Each c In rngA
        c.Interior.Color = xlNone '초기 색상을 없애 줍니다'        
        For Each d In rngB
        	if c.value = d.value then '해당되는 칸의 값이 같을 경우'
            	'명칭이 같은 정보의 바로 옆 같의 값을 복제하여 가져온다'
                c.offset(0,1).value = d.offset(0,1).value
                
                matchingNumber = matchingNumber + 1
                
                '동일한 값이 한번이라도 있는 경우 바로 종료 하려면 아래 주석을 풀어주세요'
                'Exit For    '            
            end if        
        next d        
    Next c
    
    MsgBox("매칭된 개수는 " & matchingNumber & " 입니다.")
    
End Sub

이렇게 하면 다른시트에서 비교한뒤 동인한 명칭을 갖는 행의 값을 가져오면서 가져온 개수를 알 수 있습니다.

참 쉽죠?

궁금하거나 다른 문의사항이 있으면 뎃글로 남겨주시면 감사하겠습니다.

감사합니다.

2019/06/01 - [DEV/VBA] - 엑셀 VBA 시작하기

2019/08/01 - [DEV/VBA] - [vba] 현재 엑셀 시트의 선택한 영역을 vba 스크립트에서 가져오기

2019/07/12 - [DEV/VBA] - [vba] For 구문 이용하기와 offset 사용 해 보기

2019/08/30 - [DEV/VBA] - [VBA]RGB 색상 값이 들어있는 셀에 셀 색상 지정하기

2019/09/02 - [DEV/VBA] - [vba] 엑셀 시트의 모든 색상의 셀 색상 제거하기

2019/10/24 - [DEV/VBA] - [vba] 버튼(단추)를 이용하여 스크립트 실행하기