본문 바로가기
카테고리 없음

엑셀 시트 여러개 통합하는 법 (파워 쿼리/스크립트)

by think15151 2025. 5. 30.

 

엑셀을 사용하다 보면 여러 부서나 팀에서 개별적으로 작성한 시트들을 하나의 파일로 모아야 할 때가 꽤나 많습니다. 특히 프로젝트별 데이터, 월별 보고서, 일별 작업일지 등 수십 개에서 많게는 수백 개의 시트를 하나로 합치는 작업을 수작업으로 진행할 경우에는 시간도 너무 오래 걸리고 오류 발생 가능성도 큽니다.

이 글에서는 엑셀에서 수백 개 시트를 효율적으로 통합할 수 있는 파워 쿼리(Power Query) 활용법과 VBA 스크립트를 통한 자동화 방법을 단계별로 알려드리도록 하겠습니다. 초보자부터 숙련자까지 실무에 바로 적용하실 수 있게 쉽게 정리해보겠습니다.

1. 파워 쿼리로 다중 시트 통합하기

파워 쿼리는 엑셀 2016 이후 버전에 기본 탑재된 데이터 자동화 도구로, 반복 작업을 손쉽게 처리할 수 있습니다. 수백 개 시트를 통합할 때 가장 간단하고 오류가 적은 방법 중 하나입니다.

1-1. 파워 쿼리 사용 조건

  • 엑셀 2016 이상 (엑셀 2010/2013은 추가 설치 필요)
  • 모든 시트의 데이터 구조(열 구성)가 동일해야 함

1-2. 단계별 사용법

  1. 상단 메뉴에서 [데이터] → [데이터 가져오기] → [다른 원본에서] → [Excel 통합 문서] 선택
  2. 병합할 엑셀 파일을 열고, 모든 시트 가져오기 옵션 선택
  3. 파워 쿼리 편집기에서 [기타 옵션] 클릭 후 불필요한 열(예: Sheet Name 등) 제거
  4. [닫기 및 로드]를 누르면 모든 시트가 하나로 합쳐져 새로운 시트로 출력됨

1-3. 파워 쿼리 활용 팁

  • 데이터가 주기적으로 업데이트되는 경우, 새로 고침만 클릭하면 자동으로 최신 데이터 반영
  • 시트마다 헤더가 중복된다면 파워 쿼리 단계에서 첫 행 제거 처리 가능
  • 파일 경로를 고정해두면 다른 엑셀 파일에도 동일한 설정 적용 가능

2. VBA 스크립트로 자동화 통합하기

엑셀 VBA(Visual Basic for Applications)는 반복 작업을 코드로 자동화할 수 있는 강력한 기능입니다. 특히 파워 쿼리가 지원되지 않거나, 좀 더 유연한 방식으로 시트를 통합하고자 할 때 유용합니다.

2-1. VBA 기본 스크립트 예제

아래는 모든 시트의 데이터를 하나로 합치는 기본 VBA 코드입니다.

Sub MergeSheets()
    Dim ws As Worksheet
    Dim wsMaster As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Set wsMaster = Worksheets.Add
    wsMaster.Name = "통합시트"
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsMaster.Name Then
            Set rng = ws.UsedRange
            lastRow = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row + 1
            rng.Copy wsMaster.Cells(lastRow, 1)
        End If
    Next ws
    MsgBox "모든 시트 통합이 완료되었습니다."
End Sub

 

2-2. 코드 실행 방법

  1. 엑셀에서 [Alt + F11] 눌러 VBA 편집기 열기
  2. 상단 메뉴에서 [삽입] → [모듈] 클릭
  3. 위의 코드를 붙여넣고 저장
  4. [Alt + F8]을 눌러 ‘MergeSheets’ 매크로 실행

2-3. 추가 커스터마이징 팁

  • 특정 시트만 합치고 싶다면 If ws.Name Like "보고서*" 조건문 추가
  • 헤더 중복 방지: 첫 번째 시트만 헤더 복사 후 나머지는 데이터만 복사
  • 병합된 시트 색상 또는 구분선 추가로 구분 가능

3. 시트 통합 후 검증 및 유지보수 팁

시트 통합 후에는 반드시 데이터가 정상적으로 병합되었는지 확인하는 과정이 필요합니다. 특히 수백 개의 시트를 병합할 경우 다음 사항을 점검하는 것이 좋습니다.

3-1. 중복 데이터 확인

  • 엑셀의 [조건부 서식 → 중복 값] 기능으로 빠르게 중복 데이터 탐색
  • 또는 고급 필터로 유니크 값만 추출

3-2. 요약 테이블 작성

시트별 데이터 수, 합계 등을 요약할 피벗 테이블을 바로 생성해 데이터 검증 및 보고서 작성에 활용합니다.

3-3. 자동화 유지보수

  • 파워 쿼리: 새 시트가 추가되면 새로 고침으로 반영
  • VBA: 코드 내에서 동적으로 시트를 탐색하므로 파일명 변경 시에도 문제없이 동작

 

엑셀에서 다수의 시트를 하나로 통합하는 작업은 단순 반복이긴 하지만, 데이터 양이 많아질수록 자동화의 필요성이 커집니다. 파워 쿼리는 비코딩 환경에서도 간편하게 통합할 수 있다는 장점이 있고, VBA는 보다 유연하고 복잡한 조건의 통합에도 대응할 수 있는 강력한 도구라고 할 수 있습니다.

이번에 소개한 방법을 활용하면 여러 개의 시트를 빠르고 안정적으로 병합할 수 있으며, 이후에 동일한 작업이 반복되더라도 손쉽게 관리할 수 있겠죠? 지금 바로 적용해서 업무 효율을 높여보시기 바라겠습니다.

티스토리툴바