[엑셀] 시트별 데이터 통합하기(엑셀VBA활용)

오늘은 업무 자동화로 알아두시면 도움될 시트별 데이터 통합하는 방법을 안내드리겠습니다.

 

데이터 통합하는 방법은 정말 여러가지 방법이 있는데요

엑셀 파워쿼리, VBA가 가능하며 또 Python으로 엑셀 통합도 가능합니다.

모든 부분은 천천히 다뤄보겠지만 이번엔 엑셀VBA를 통해서 시트별 데이터를 통합해보겠습니다.

 

---

파이썬을 통해 시트별 데이터 통합하는 방법도 업로드 하였습니다.

2022.11.06 - [파이썬(Python)] - [파이썬/Python] 분리된 엑셀 시트 하나의 시트로 통합하기

[파이썬/Python] 분리된 엑셀 시트 하나의 시트로 통합하기

 

한 폴더에 있는 파일 병합은 파워쿼리로 다루어본 적이 있습니다. 아래 글을 참고해주세요 !

2022.05.09 - [엑셀(Excel)] - [파워쿼리 5강] 엑셀 파일 병합_자동화 기본편

[파워쿼리 5강] 엑셀 파일 병합_자동화 기본편

---

오늘의 목표 : 각 시도별 시트를 '통합'이라는 빈 시트에 합친 데이터로 보여주는 것입니다.

 

예시데이터

아래 예시 데이터를 보게되면 시트별로 '통합', '서울특별시', '부산광역시', '대구광역시' 시트로 구성되어 있습니다.

또한 데이터 내에 서울특별시 시트의 경우, 시도명이 '서울특별시'만 있습니다.

예시 데이터(아파트매매 공공데이터)

'통합' 시트는 빈 시트이며, 부산광역시 시트는 '시도명' 열이 부산광역시로, 대구광역시 시트의 경우, 대구광역시로 이루어진 데이터입니다.

※ 예시 데이터는 공공데이터 기반으로 보여드립니다.

예시데이터 다른 시트 확인

 

 

1. VBA코드 ① : For ~ Next문 사용

 

먼저 가장 쉬운 방법으로 For ~ Next  반복문을 이용한 VBA코드 입니다.

Sub Data_Merge_1()
    '변수 설정
    Dim i As Long
    
    'VBA 화면 업데이트 일시중지 -> 나중에 True로 변경해줘야 함    
    Application.ScreenUpdating = False
    
    'Sheet1 초기화 --> 추가로 VBA 실행 시 중복되지 않기 위함
    Sheet1.Cells.Clear
    
   
    For i = 2 To Worksheets.Count 'WorkSheets.Count 현재 워크시트 갯수
        Sheets(i).Range("a1").CurrentRegion.Copy Sheet1.Cells(Rows.Count, "a").End(xlUp)
        '시트2의 a1셀에서 Ctrl + a 누른 값을 카피해서 Sheet1셀 'a'열 맨 위에붙어 복사붙여넣기 해줘
    
    Next
    Application.ScreenUpdating = True
End Sub

가. 변수설정

  - Dim i as Long : i라는 변수를 Long타입으로 지정(Long타입은 '정수'입니다)

 

  나. Application.ScreenUpdating = False

  - VBA 실행 시 화면이 변경되는 걸 막아줍니다. 깔끔하게 보기 위함입니다.

 

  다. Sheet1.Cells.Clear

 - Sheet1의 내용을 모두 지워달라는 명령어입니다.  안쓰게 되면 VBA를 Run할 때마다 데이터가 중복으로 붙습니다.

 

  라. 반복문

  - For ~ Next문으로 작성하였습니다. 

  - Worksheets.Count는 워크시트의 전체 숫자입니다. 현재 '통합', '서울특별시', '부산광역시', '대구광역시' 라서 워크시트 숫자는 4 입니다.

  - For ~ Next를 통해 For i  = 2 to Worksheets.Count의 뜻은 2부터 4까지 반복해달라는 뜻입니다.

VBA 한 줄 설명

 

1-1. VBA코드 ① 결과

통합 시트 데이터 중간 쯤까지 확인해보니 서울특별시와 부산광역시가 잘 합쳐진 걸 볼 수 있습니다.

데이터 통합시트 확인

앗.. 하지만 머릿글이 포함된채로 들어가있네요

Offset과 Resize를 이용해서 Copy할 때 헤더를 제외하고 들고왔어야 했는데 그냥 들고오다보니 이런 문제가 있습니다.

 

2. VBA코드(For each ~ Next문 사용)

다음은 For each ~ Next 문을 사용하여 통합작업을 실시해보았습니다.

 

Sub MergeData_2()
    '변수설정
    Dim sh As Worksheet
    Dim i As Long
    'VBA 화면 업데이트 일시중지 -> 나중에 True로 변경해줘야 함 
    Application.ScreenUpdating = False
    '시트 초기화
    Sheet1.Cells.Clear
    'For each ~ Next 구문으로 반복문 시행
    i = 2
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> ActiveSheet.Name Then '취합시트를 빼고
            Sheets(i).Range("a1").CurrentRegion.Copy Sheet1.Cells(Rows.Count, "a").End(xlUp)
            i = i + 1
        End If
    Next

    Application.ScreenUpdating = True
End Sub

VBA코드 ①과 차이나는 부분은 노란색 표시로 구분해두었습니다.

For Each ~ Next 구문을 통한 시트 통합

먼저 변수부분에서 Worksheet 변수가 추가되었습니다.

 

다음으로 반복문을 For ~ Next가 아닌 For Each 개체변수 in 전체범위로 수정되었습니다.

수정된 구문의 경우 If 문을 통해 sh.Name이 ActiveSheet (현재 활성화된 시트 = '통합'시트)와 다를 경우만 복사 붙여넣기를 한다고 되어 있습니다.

 

위와 거의 동일하니 참고만 해주시면 될 것 같습니다.

 

3. VBA코드(Do ~ Loop문 사용)

Sub MergeDate_3()
    Dim cnt As Long
    Dim i As Long
    cnt = Worksheets.Count
    
    Application.ScreenUpdating = False
    Sheet1.Cells.Clear
    
    i = 2
    Do While i <= cnt
        Sheets(i).Range("a1").CurrentRegion.Copy Sheet1.Cells(Rows.Count, "a").End(xlUp)
        i = i + 1
    Loop

    Application.ScreenUpdating = True
End Sub

Do ~ Loop 구문은 기본적으로 무한반복입니다.

다만 i <= cnt로 조건을 잡아두었습니다.

i의 경우 처음에 2라고 지정해두었으며, cnt의 경우 위 변수에서 Worksheets.Count를 통해 현재 파일에서는 4로 나타납니다.(시트명 : 통합, 서울특별시, 부산광역시, 대구광역시 / 총 4개)

 

그 외에는 모든 코드가 동일합니다.

4. VBA코드_시트명 삽입

마지막 코드는 좀 더 실무적합하게 작성하였습니다.

항상 통합할 때는 시트명도 같이 데이터로 들어가줘야 더욱 통합이 용이합니다.

 

시트명이 데이터로 유입

Sub MergeData_4()

    Dim i As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
    Sheet1.Cells.Clear
    
    Sheet1.Range("a1:q1") = Array("시트명", "도로명", "법정동", "지번", "아파트", "건축년도", "층", "전용면적", "년", "월", "일", "거래금액", "일련번호", "거래유형", "중개사소재지", "시도명", "시군구명")
    
    For i = 2 To Worksheets.Count
        Sheets(i).Range("a1").CurrentRegion.Copy Sheet1.Cells(Rows.Count, "b").End(xlUp)
        Set rng = Sheet1.Range("a1").CurrentRegion
        rng.SpecialCells(xlCellTypeBlanks) = Sheets(i).Name
    Next
    Application.ScreenUpdating = True
End Sub

먼저 열 이름을 배열로 받습니다.

Sheet1.Range("a1:q1") = Array("시트명", "도로명", "법정동", "지번", "아파트", "건축년도", "층", "전용면적", "년", "월", "일", "거래금액", "일련번호", "거래유형", "중개사소재지", "시도명", "시군구명")
 

 

다음 코드는 빈칸 부분만 시트이름으로 채운다는 뜻입니다.

rng.SpecialCells(xlCellTypeBlanks) = Sheets(i).Name

 

이외 코드는 맨 위에 For ~ Next문과 동일하여 따로 다루지는 않겠습니다.

 

시트 통합은 엑셀 자동화로 알아두시면 매우 편한 작업입니다.

통합하는 방법을 통해 요긴하게 쓰셨으면 좋겠습니다.

 

※ 본 코드는 엑셀 디자인의 배영자님 코드를 참고하여 재구성 하였습니다.