들어가기 전에
여러 워크시트의 데이터를 수동으로 누적하는 것은 시간이 많이 걸리고 오류가 발생하기 쉽습니다. 더구나 데이터 세트를 정기적으로 업데이트해야 하는 경우 이 과정은 더욱 번거로워질 수 있습니다. Excel의 파워 쿼리 도구를 사용하면 이 모든 작업을 자동으로 수행하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Combining Excel Workbooks Is Easier Than You Think With This Powerful Tool
- URL: https://www.howtogeek.com/microsoft-excel-combining-workbooks-easier-than-you-think-powerful-tool/
1단계: 결합할 워크북 구성
Excel 파일을 하나의 워크시트로 결합하기 전에 꼭 거쳐야 할 몇 가지 중요한 단계가 있습니다.
(1) 결합하려는 모든 데이터세트가 동일한 구조로 되어 있는지 확인하세요. 특히, 열 개수와 열 머리글이 동일해야 하며, 워크시트 탭 이름도 동일해야 합니다. 또한, 파일 이름이 유사한 구문을 따르면 작업이 훨씬 더 간단해질 것입니다.
열의 순서가 같을 필요는 없으며, 각 시트의 행의 개수는 다를 수 있습니다.
(2) 파일이 같은 폴더에 저장되었는지 확인하세요. 가능하면 폴더에는 병합할 파일만 넣어두는 것이 좋습니다. 이렇게 하면 이후 단계에서 과정이 더 깔끔하고 수월해집니다. 만약 이 폴더에 다른 파일도 저장 해야 하는 경우, 데이터 병합에 적합한 파일만 선택되도록 하는 방법에 대해서도 나중에 소개합니다.
2단계: Excel을 열고 관련 폴더에 연결
위의 전제 조건에 따라 파일이 폴더에 올바르게 정리되면 데이터를 가져올 준비가 된 것입니다.
(1) 새 Excel 통합 문서를 열고 빈 워크시트를 시작한 다음, F12를 눌러 결합하려는 개별 파일이 있는 폴더와 다른 폴더에 파일을 저장합니다.
(2) [데이터] 탭 - [데이터 가져오기] - [파일에서] - [폴더에서]를 클릭합니다.
(3) 찾아보기 대화 상자에서 결합하려는 파일이 저장된 폴더를 찾아 선택한 다음 [열기]를 클릭합니다.
3단계: 결합하려는 파일 선택
이 단계에서 수행하는 작업은 선택한 폴더에 저장된 내용에 따라 달라집니다. 병합할 파일만 있는 경우에는 CASE 1, 폴더에 병합하지 않을 파일도 함께 있는 경우에는 CASE 2를 참고합니다.
CASE 1: 폴더에는 병합하려는 파일만 있는 경우
선택한 폴더가 결합하려는 파일 전용이면 통합 문서 병합 과정이 더 깔끔하고 간편해집니다. 불필요한 파일을 필터링할 필요가 없기 때문입니다. 파일을 결합할 준비가 되었으므로 "결합"을 클릭하고 나타나는 드롭다운 메뉴에서 "데이터 결합 및 변환"을 클릭합니다.
이제 Excel에서 데이터를 간략하게 평가한 후 파일 결합 대화 상자가 나타나고 4단계로 넘어갈 준비가 됩니다.
CASE 2: 폴더에 병합하지 않을 파일이 함께 있는 경우
선택한 폴더에 병합하려는 파일 외의 다른 파일이 있는 경우, 2단계에서 폴더를 찾으면 세부 정보에 해당 파일이 나열됩니다. 이 경우, 데이터를 병합하기 전에 파일 선택 영역을 변경해야 하므로 "데이터 변환"을 클릭해야 합니다.
이 옵션을 클릭하면 Microsoft Excel 내의 별도 창인 Power Query 편집기가 실행됩니다. 여기서 결합 프로세스에 포함하지 않으려는 파일을 필터링할 수 있습니다.
이 경우, 연락처 세부 정보라는 이름의 파일을 제외해야 하므로 이름 열 헤더의 필터 드롭다운 화살표를 클릭하고 해당 파일의 선택을 취소한 다음 "확인"을 클릭합니다.
이제 결합하려는 파일만 나열되므로 콘텐츠 열에서 아래쪽으로 두 번 화살표를 클릭하여 파일 결합 대화 상자를 시작하세요.
4단계: 파일 결합
파일 결합 대화 상자에서 주의해야 할 몇 가지 사항이 있습니다.
(1) 기본적으로 샘플 파일로 지정된 통합 문서는 목록에서 첫 번째로 선택한 통합 문서입니다. 샘플 파일은 파일을 결합할 때 데이터 구조를 결정하므로, 모든 파일의 구조가 일관적이라면 이 옵션을 그대로 두어도 됩니다.
(2) 통합 문서에 워크시트 탭이 두 개 이상 있는 경우 왼쪽의 표시 옵션 창에 나열됩니다. 따라서 지정된 통합 문서에서 결합하려는 모든 탭의 이름을 동일하게 지정하는 것이 중요합니다. 또한, 데이터를 Excel 표 형식으로 서식을 지정하고 이름을 지정한 경우 여기에 이름 목록이 표시됩니다.
하지만 이 예에서는 각 통합 문서에 '점수'라는 워크시트가 하나만 있으므로 이 옵션을 선택하세요. 오른쪽 창에서 선택한 샘플 파일의 미리 보기를 확인한 후 "확인"을 클릭하여 확인하고 Power Query 편집기를 실행하세요.
5단계: 데이터 변환
Excel에서 파워 쿼리 편집기를 처음 여는 경우 처음에는 다소 어려울 수 있습니다. 하지만 편집기의 각 영역이 어떤 역할을 하는지 이해하려고 노력하다 보면, 이 편집기가 직관적인 도구라는 것을 깨닫게 될 것입니다.
왼쪽 창에는 모든 쿼리가 있습니다. 여러 Excel 통합 문서의 데이터를 결합할 때는 두 가지 쿼리에만 집중하면 됩니다. 데이터세트를 변경하려는 경우 템플릿으로 사용되는 '샘플 파일 변환' 쿼리와 목록 하단에 추가된 쿼리로 파일을 통합하는 쿼리입니다. 하지만 원하는 작업에 따라 두 쿼리 모두 데이터 변환에 유용합니다.
(1) 변환 샘플 파일 쿼리에서 각 열 머리글의 숫자 서식 아이콘을 클릭하여 Excel에 포함된 데이터 유형을 지정합니다. 이 예에서 첫 번째, 세 번째, 네 번째 열에는 정수가, 두 번째 열에는 날짜가 포함되어 있습니다.
[참고] Power Query 편집기의 오른쪽에 있는 쿼리 설정 창에서 데이터 변환 과정에서 수행하는 각 단계를 확인하고 필요한 경우 제거하세요.
(2) 다음으로, 추가된 쿼리에서 소스 이름 열은 필요하지 않지만 각 팀 점수에 대한 연도가 있으면 편리할 것입니다.
(3) 이 열을 변환하여 나머지 파일 이름은 제거하고 연도만 유지할 수 있습니다. 이렇게 하려면 열 머리글을 클릭하여 열의 모든 값을 선택한 후 리본의 '변환' 탭에서 '추출'을 클릭한 다음 '구분 기호 앞 텍스트'를 클릭합니다.
(4) 이 경우 연도는 그대로 두고 첫 번째 공백 이후의 모든 내용을 삭제하겠습니다. 구분 기호 텍스트 필드에 공백 한 칸을 입력합니다. 그런 다음 "고급 옵션"을 클릭하고 "입력 시작 부분부터"를 선택합니다. 이제 "확인"을 클릭합니다.
(5) 마지막으로 열 머리글을 두 번 클릭하여 이름을 Year 로 바꾸고 데이터 유형을 "정수"로 변경합니다.
(6) 결합된 데이터 목록을 아래로 스크롤하여 오류가 없거나 추가로 변환해야 할 사항이 없는지 확인합니다.
6단계: 결합된 데이터를 새 워크시트에 로드
이제 통합 문서의 데이터가 결합되고 변환되었으므로 일반 Excel 워크시트에서 어떻게 보이는지 확인해 보겠습니다.
(1) Power Query 편집기 창의 홈 탭에서 "닫기 및 다음으로 로드" 분할 단추의 위쪽 절반을 클릭합니다.
이제 데이터가 서식이 지정된 Excel 표 형태로 새 워크시트에 로드되므로 리본의 표 디자인 탭에서 표의 모양과 속성을 서식 지정할 수 있습니다.
Power Query 편집기에서 쿼리를 추가로 변경하려면 데이터 탭에서 쿼리 및 연결 창을 활성화한 후 쿼리를 마우스 오른쪽 버튼으로 클릭하고 "편집"을 선택합니다.
Power Query 편집기에서 필요한 편집을 마친 후 "닫기 및 로드"를 다시 클릭하여 결과 데이터 세트를 업데이트합니다.
7단계: 추가 통합 문서에서 더 많은 데이터 추가
마지막으로, 2024년 데이터가 입력되었고 이 새로운 정보를 기존 쿼리에 추가하려고 한다고 가정해 보겠습니다. 놀랍게도 Power Query 편집기에서 필요한 단계를 이미 완료했기 때문에, 1단계에서 설명한 모든 기준을 통합 문서가 충족하는지 확인하고 Excel에서 버튼 하나만 클릭하여 표를 새로 고치기만 하면 됩니다.
(1) 데이터가 포함된 통합 문서를 이전 연도의 데이터가 있는 폴더로 옮기고 통합 문서 이름, 워크시트 구조, 탭 이름이 다른 파일과 일관성을 유지하도록 합니다.
(2) 그런 다음 이전에 추가된 데이터가 있는 통합 문서를 열고 쿼리 및 연결 창(리본의 데이터 탭에서 "쿼리 및 연결"을 클릭하여 활성화할 수 있음)에서 기본 쿼리 옆에 있는 "새로 고침" 아이콘을 클릭합니다.
(3) Excel에서 이 업데이트를 완료하는 데 몇 초가 걸린 후 아래로 스크롤하여 표 하단에 추가 데이터가 추가된 것을 확인합니다.
파워 쿼리를 새로 고치기 전에 표에 적용한 서식을 유지하려면 리본의 '표 디자인' 탭에서 '속성'을 클릭하고 '셀 서식 유지'가 선택되어 있는지 확인하세요. 열 너비를 변경한 후 새로 고칠 때마다 포함된 데이터에 맞게 자동으로 조정되는 것을 원하지 않으면 '열 너비 조정'의 선택을 해제하세요.
마치며
폴더에 저장된 여러 통합 문서의 데이터를 추가할 수 있을 뿐만 아니라, 여러 Excel 시트 간에 데이터 연결을 만들고 쿼리를 결합하여 단일 통합 문서 내에 여러 Excel 시트의 데이터를 쌓을 수 있습니다. 또한, Power Query 편집기를 사용하여 웹에서 테이블을 가져올 수도 있습니다.
'Excel' 카테고리의 다른 글
| 전문가처럼 색상별로 Excel 셀 계산하는 3가지 방법 (0) | 2025.11.12 |
|---|---|
| AI 비서 "노트북LM" 사용 이후 달라진 Excel 데이터 분석 방식 (0) | 2025.11.11 |
| Excel에서 시간을 절약해주는 7가지 파워 쿼리 팁 (0) | 2025.11.07 |
| 사람들이 잘 사용하지 않지만 꼭 써야 하는 강력한 Excel 기능 6가지 (0) | 2025.11.05 |
| ChatGPT & Excel: 데이터 분석을 완전히 혁신할 8가지 기능 (0) | 2025.11.04 |
