마이크로소프트 엑셀(Microsoft Excel)을 사용하다 보면 여러 워크시트에 흩어져 있는 유사한 데이터를 하나의 통합 문서로 모아 정리해야 하는 경우가 자주 발생합니다. 데이터를 통합하면 관리가 한결 수월해지고, 분석과 보고서 작성 효율도 크게 향상됩니다.
이 글에서는 엑셀에서 여러 워크시트를 하나의 통합 문서로 결합하는 다양한 방법을 단계별로 자세히 소개합니다. 예제 파일을 내려받아 직접 따라 해 보실 수도 있습니다.
엑셀에서 여러 워크시트를 하나의 통합 문서로 합치는 4가지 방법
여러 시트에 나뉘어 있는 데이터를 하나의 통합 문서로 모으고 싶다면 아래에서 설명하는 네 가지 방법을 활용해 보세요.
1. 파워 쿼리(Power Query)를 활용한 워크시트 결합
여러 엑셀 워크시트의 데이터를 하나의 통합 문서로 병합하는 가장 강력하고 효율적인 방법은 파워 쿼리(Power Query)를 사용하는 것입니다. 단, 서로 다른 워크시트의 데이터를 통합하려면 각 시트의 데이터 형식이 동일해야 합니다. 즉, 행 구성과 열 순서가 같아야 원활하게 병합됩니다.
예를 들어 하나의 통합 문서에 세 개의 서로 다른 워크시트가 있다고 가정해 봅시다. 이제 이들을 하나의 통합 문서로 합쳐 보겠습니다.
진행 단계:
- 먼저 리본 메뉴에서 데이터(Data) 탭으로 이동하여 파워 쿼리를 실행할 준비를 합니다.
- 다음으로 데이터 가져오기 및 변환(Get & Transform Data) 그룹에서 데이터 가져오기(Get Data)를 클릭합니다.
- 데이터 가져오기 드롭다운 메뉴에서 기타 원본(From Other Sources)을 선택합니다.
- 이어서 기타 원본 하위 메뉴에서 빈 쿼리(Blank Query)를 클릭합니다.
- 그러면 파워 쿼리 편집기(Power Query Editor)가 열립니다.
- 쿼리 편집기의 수식 입력줄에 아래 수식을 입력합니다.
=Excel.CurrentWorkbook()- Enter 키를 누르면 통합 문서 전체에 있는 모든 테이블 이름과 명명된 범위, 연결 정보가 화면에 표시됩니다.
주의할 점은 파워 쿼리 수식은 대소문자를 구분한다는 것입니다. 수식을 정확하게 입력하지 않으면 오류가 발생하므로 꼼꼼히 확인하세요.
- 이제 Content 헤더 셀에서 양쪽 방향 화살표 아이콘을 클릭합니다.
- ‘원래 열 이름을 접두사로 사용(Use original column name as prefix)’ 옵션의 체크를 해제합니다.
- 확인(OK) 버튼을 눌러 창을 닫습니다.
- 위 과정을 거치면 모든 워크시트의 내용이 하나의 통합 문서로 병합됩니다.
- 마지막으로 파워 쿼리를 닫으면 모든 데이터가 하나의 통합 문서에 깔끔하게 정리된 것을 확인할 수 있습니다.
2. 엑셀 ‘통합(Consolidate)’ 도구로 워크시트 병합하기
엑셀에 기본으로 탑재된 통합(Consolidate) 도구는 데이터를 요약·통합하는 가장 간편한 방법입니다. 이 기능을 사용하면 여러 시트에 흩어져 있는 데이터를 하나의 통합 문서로 손쉽게 모을 수 있습니다.
진행 단계:
- 먼저 리본 메뉴에서 데이터(Data) 탭으로 이동합니다.
- 데이터 도구(Data Tools) 그룹에서 통합(Consolidate)을 클릭합니다.
- 함수(Function) 목록에서 데이터를 요약할 함수를 선택합니다. 여기서는 합계(Sum)를 사용하겠습니다.
- 참조(Reference) 입력란 옆의 대화 상자 확장(Expand Dialog) 아이콘을 눌러 첫 번째 워크시트에서 통합할 범위를 지정합니다.
- 추가(Add) 버튼을 클릭해 해당 범위를 ‘모든 참조’ 목록에 등록합니다. 통합하려는 나머지 범위에 대해서도 같은 작업을 반복합니다.
- 레이블 사용(Use labels in) 항목에서 필요에 따라 첫 행(Top row) 또는 왼쪽 열(Left column)에 체크합니다.
- 원본 데이터가 변경될 때 통합 결과도 자동으로 갱신되기를 원한다면 원본 데이터에 연결(Create links to source data)에 체크하세요. 그러면 엑셀이 원본 스프레드시트와의 연결을 자동으로 생성합니다.
- 마지막으로 확인(OK)을 클릭합니다.
참고로, 통합 결과는 빈 시트에 저장하는 것이 좋습니다. 만약 기존 시트에 이미 데이터가 있다면 통합된 정보가 들어갈 빈 행과 열을 미리 확보해 두어야 합니다.
- 작업이 완료되면 각 워크시트의 값들이 하나의 통합 문서로 합쳐진 것을 확인할 수 있습니다.
3. ‘이동 또는 복사(Move or Copy)’ 기능으로 워크시트 합치기
여러 워크시트를 하나의 통합 문서로 옮기려면 엑셀의 이동 또는 복사(Move or Copy) 기능을 활용하는 것도 좋은 방법입니다. 몇 번의 클릭만으로 다른 통합 문서에 있는 시트를 현재 문서로 가져올 수 있습니다.
진행 단계:
- 먼저 통합할 대상이 되는 통합 문서들을 모두 엽니다. 그런 다음 원본 통합 문서에서 복사하거나 이동할 워크시트를 선택합니다.
- 선택한 시트 탭에서 마우스 오른쪽 버튼을 클릭하고, 나타나는 메뉴에서 이동 또는 복사(Move or Copy)를 선택합니다.
- 이동 또는 복사 대화 상자가 열리면 통합 문서(To book) 드롭다운 메뉴에서 시트를 옮길 대상 통합 문서를 선택합니다. 여기서는 Book1을 선택하겠습니다.
- 다음 시트 앞에 삽입(Before sheet) 목록에서 MainSheet를 지정하고, 복사본 만들기(Create a copy)에 체크한 뒤 확인(OK)을 클릭합니다.
- 이렇게 하면 두 개 이상의 통합 문서에 있는 여러 워크시트가 하나의 문서로 이동 또는 복사되어 합쳐집니다.
4. 엑셀 VBA 매크로로 여러 워크시트 통합하기
엑셀 VBA를 활용하면 리본 메뉴의 기능처럼 동작하는 코드를 직접 작성해 반복 작업을 자동화할 수 있습니다. 여러 통합 문서에 이름이 같은 Sheet1이 있고, 이 데이터들을 모두 하나의 통합 문서로 모으고 싶다고 가정해 봅시다. VBA 코드를 사용하면 클릭 한 번으로 이 작업을 처리할 수 있습니다.
진행 단계:
- 먼저 리본 메뉴에서 개발 도구(Developer) 탭으로 이동합니다.
- 코드(Code) 그룹에서 Visual Basic을 클릭해 VBA 편집기(Visual Basic Editor)를 엽니다. 또는 키보드 단축키 Alt + F11을 눌러도 됩니다.
- 또 다른 방법으로, 워크시트 탭에서 마우스 오른쪽 버튼을 클릭한 후 코드 보기(View Code)를 선택해도 VBA 편집기로 바로 이동할 수 있습니다.
- VBA 편집기가 열리면 코드를 작성할 준비가 된 것입니다.
- 메뉴 모음의 삽입(Insert) 드롭다운에서 모듈(Module)을 클릭합니다.
- 그러면 통합 문서에 새로운 모듈이 생성됩니다.
- 아래의 VBA 코드를 복사하여 붙여넣습니다.
VBA 코드:
Sub Copy_Worksheets()
Dim wrkbk As Workbook
Dim Str As String
Str = "Sheet1"
For Each wrkbk In Workbooks
If wrkbk.Name <> ThisWorkbook.Name Then
wrkbk.Worksheets(Str).Copy_
Before:= ThisWorkbook.Sheets(1)
End If
Next
Set wrkbk = Nothing
End Sub- 이후 실행(Run Sub) 버튼을 클릭하거나 키보드 단축키 F5를 눌러 코드를 실행합니다.
- 코드가 실행되면 열려 있는 모든 통합 문서의 Sheet1 데이터가 하나의 통합 문서로 자동으로 병합됩니다.
마무리
지금까지 살펴본 네 가지 방법—파워 쿼리, 통합 도구, 이동 또는 복사, VBA 매크로—를 활용하면 엑셀에서 여러 워크시트를 하나의 통합 문서로 손쉽게 결합할 수 있습니다. 상황과 데이터 규모에 맞는 방법을 선택해 활용해 보시기 바랍니다.
궁금한 점이나 제안 사항이 있다면 댓글로 알려주세요. 더 많은 엑셀 활용 팁은 ExcelDemy.com 블로그의 다른 글에서도 확인하실 수 있습니다!
함께 읽으면 좋은 글
- CMD 명령어로 엑셀 파일을 하나로 병합하는 방법 (4단계)
- 엑셀 파일을 워드 문서에 병합하기
- 열 기준으로 엑셀 파일 병합하는 방법 (3가지 방법)
- 엑셀 파일을 우편 라벨로 병합하는 쉬운 방법