지점별 매출이 시트마다 나눠져 있거나, 월별로 1월·2월·3월… 시트가 계속 쌓여갈 때 여러분은 어떻게 취합하고 계신가요? 보고서 마감일이 다가오면 보통 시트 탭을 하나씩 넘겨가며 범위를 복사해서 마스터 시트에 붙여넣는 작업을 반복하곤 합니다. 시트가 몇 개 없을 땐 버틸 만하지만, 지점이나 담당자가 늘어 시트가 수십 개를 넘어가면 단순 복붙에만 한두 시간이 훌쩍 지나가고, 그 과정에서 행 하나가 어긋나거나 수식이 밀려도 알아차리기 어렵습니다.
엑셀에서 여러 시트나 파일을 하나로 합치는 방법은 크게 세 가지가 있습니다. 그런데 검색해보면 Power Query(파워쿼리), 데이터 통합(Consolidate), 피벗 테이블 같은 온갖 용어가 섞여 나와 "도대체 내 업무엔 뭘 써야 하지?"라는 생각이 먼저 듭니다. 방법 선택을 잘못하면 뒤로 갈수록 작업이 꼬이기 마련입니다. 그래서 이 글에서는 먼저 내 목적에 맞는 방법 선택 기준을 명확히 잡고, 실무에서 90% 이상 쓰게 되는 파워쿼리(Power Query)로 시트를 1초 만에 자동 통합하는 정석을 클릭 순서와 M함수 팁까지 빠짐없이 정리해 드리겠습니다.
복사-붙여넣기 대신 이 세 가지 중 하나를 고르세요
선택 기준은 단순합니다. 데이터를 '어떤 형태로' 모으고 싶은지에 따라 갈립니다.
- 모든 원시 데이터를 한 장의 마스터 표로 아래로 길게 쌓고 싶다 → Power Query(파워쿼리) 시트 통합 ★ (지점별 거래 내역, 월별 매출처럼 시트마다 행 데이터가 있는 경우 무조건 이것이 정답입니다.)
- 행 누적이 아니라 합계·개수 등 요약 비교표만 필요하다 → 데이터 탭의 '통합(Consolidate)' (각 시트에서 특정 항목의 숫자만 취합해 대시보드나 요약표를 만들 때 씁니다.)
- 한 파일 안의 시트가 아니라 여러 개의 엑셀 파일로 쪼개져 있다 → Power Query의 '폴더에서 데이터 가져오기' (파일 자체가 부서별·월별로 저장되어 있는 상황입니다.)
실무에서 야근을 유발하는 대부분의 취합 업무는 첫 번째 상황입니다. 아래에서는 파워쿼리를 사용해 한 파일 안의 여러 시트를 클릭 몇 번으로 자동 통합하는 진짜 정석을 살펴보겠습니다.

▲ 내 상황에 맞는 취합 방식 3가지 한눈에 비교
시작 전 필수 체크: 원본 시트 3가지 정돈 (5분 소요)
통합의 성패는 원본 데이터의 규격이 결정합니다. 다른 사람에게 취합받은 파일이라면 작업을 시작하기 전에 아래 세 가지만 꼭 확인해 주세요.
- 머리글은 반드시 '한 줄'이어야 합니다: 두 줄로 된 복잡한 제목이나 셀 병합된 표 머리글이 있으면 열 이름을 인식하지 못하고 데이터가 깨집니다.
- 불필요한 빈 행과 빈 열을 없앱니다: 보기 좋게 꾸미려고 데이터 중간에 넣어둔 공백 행·열은 쿼리에서 null 데이터로 처리되므로 정리해 두는 것이 좋습니다.
- 시트마다 열 이름을 동일하게 맞춥니다: 파워쿼리는 열 이름을 기준으로 데이터를 아래로 쌓습니다. 열 순서가 다른 것은 알아서 맞춰주지만, 이름이 '금액'과 '매출액', 혹은 '수량'과 '수량 '(뒤에 공백)처럼 다르면 서로 다른 열로 분리되어 빈칸이 생깁니다.
Power Query로 시트 한 번에 자동 통합하기: 진짜 실무 정석
인터넷 팁을 보다 보면 시트마다 일일이 표(Ctrl+T)를 만들고, 파워쿼리에서 시트마다 새 원본을 30번 눌러 불러온 뒤 '쿼리 추가'로 합치라는 비효율적인 설명이 많습니다. 하지만 시트가 20~30개가 넘어가면 이 역시 손이 너무 많이 가는 노가다에 불과합니다.
실무에서는 M함수 단 한 줄, Excel.CurrentWorkbook()을 활용해 파일 안의 모든 데이터를 한 방에 긁어오는 방식을 사용합니다.
1단계: 빈 쿼리 열고 M함수 입력하기
통합 결과를 담을 엑셀 파일에서 데이터 탭 → 데이터 가져오기 → 기타 원본에서 → 빈 쿼리를 클릭합니다. 그러면 파워쿼리 편집기 창이 열립니다.
상단 수식 입력줄에 다음 수식을 그대로 입력하고 Enter를 누릅니다 (대소문자를 정확히 구분해야 합니다).
= Excel.CurrentWorkbook()
엔터를 치는 순간, 현재 통합문서 안에 들어있는 모든 표(Table)와 명명된 범위 목록이 Content(테이블 데이터)와 Name(시트/표 이름) 두 개의 열로 깔끔하게 나타납니다.
2단계: [매우 중요] 무한 루프(자가 참조) 방지 필터 걸기
이 단계는 실무에서 가장 중요한 핵심 팁입니다. 나중에 이 쿼리를 엑셀 시트에 로드하면 그 결과 표 역시 현재 통합문서의 표가 됩니다. 따라서 필터를 걸어두지 않으면 새로 고침할 때마다 쿼리가 자기 자신의 결과 표까지 다시 읽어 들여 데이터가 2배, 4배로 계속 불어나는 무한 참조 오류가 발생합니다.
이를 방지하기 위해 Name 열의 필터 화살표를 누르고, 텍스트 필터 → 같지 않음을 선택한 뒤, 생성할 마스터 쿼리 이름(예: '통합결과' 또는 '마스터')을 입력하여 결과 표가 쿼리 대상에서 제외되도록 만듭니다. 취합에서 제외할 다른 시트가 있다면 여기서 함께 체크 해제해 줍니다.
3단계: Content 열 확장(Expand) 클릭
필터 정리가 끝났다면 Content 열 헤더 우측에 있는 양방향 확장 아이콘(↔)을 클릭합니다.
- 합칠 열 목록이 체크된 상태에서, 맨 아래의 "원래 열 이름을 접두사로 사용" 체크를 반드시 해제합니다.
- 확인을 누르면 모든 시트의 데이터가 순식간에 하나의 표로 아래로 길게 펼쳐집니다.
4단계: 시트 출처 열 정리 및 데이터 형식 지정
시트를 합쳐놓고 나면 나중에 데이터 검증을 위해 "이 행이 몇 월 시트에서 왔는지" 출처를 확인해야 할 때가 많습니다. 수동으로 쿼리를 합쳤다면 시트명을 일일이 적어야 했겠지만, 이 방식에서는 Name 열에 원본 시트/표의 이름이 이미 자동으로 들어가 있습니다!
Name 열의 이름을 더블클릭해 '지점명'이나 '월' 등으로 알맞게 바꿔주기만 하면 됩니다. 마지막으로 날짜 열은 [날짜], 금액 열은 [정수] 또는 [10진수]로 열 머리글 왼쪽의 아이콘을 눌러 형식을 바르게 지정해 줍니다.
5단계: 닫기 및 로드 & 다음 달 새로 고침
편집기 좌측 상단의 홈 탭 → 닫기 및 로드를 클릭하면 새 워크시트에 모든 시트가 합쳐진 마스터 표가 완성됩니다.
이제 다음 달 마감 때는 복사-붙여넣기를 할 필요가 전혀 없습니다. 기존 시트에 새 데이터를 덧붙이거나 새로운 월 시트를 추가한 뒤, 결과 표 안에서 마우스 오른쪽 버튼 → 새로 고침만 누르면 파워쿼리가 알아서 원본 시트들을 다시 읽어 1초 만에 최신 데이터로 업데이트합니다.

▲ 파워쿼리 M함수 시트 통합 5단계 실행 로드맵
상황별 대안: 파일이 여러 개이거나 요약만 필요할 때
1. 시트가 아니라 파일이 폴더에 여러 개라면: 폴더에서 데이터 가져오기
부서별·지점별로 엑셀 파일 자체가 따로따로 폴더에 들어있는 경우라면 파일을 일일이 열 필요가 없습니다. 데이터 탭 → 데이터 가져오기 → 파일에서 → 폴더에서를 선택하고 폴더 경로를 지정합니다. 파일 목록 미리보기 창에서 데이터 변환을 누른 뒤, Content 열의 [파일 결합] 아이콘을 누르고 시트를 지정하면 모든 파일의 데이터가 자동으로 하나의 표로 병합됩니다. 새 파일이 생기면 폴더에 집어넣고 새로 고침만 누르면 끝납니다.
2. 행 데이터가 아니라 요약 수치만 필요하다면: 데이터 탭의 '통합(Consolidate)'
예를 들어 각 지점별 시트에 동일한 양식으로 계정별 비용 합계가 들어가 있고, 마스터 시트에 각 계정의 전체 합계 요약표만 한 장 만들고 싶다면 쿼리를 짤 필요 없이 엑셀 기본 기능을 쓰면 빠릅니다. 요약표를 넣을 빈 셀을 클릭하고 데이터 탭 → 데이터 도구 → 통합을 엽니다. 함수를 '합계'로 두고 시트별 범위를 하나씩 추가한 뒤, [첫 행]과 [왼쪽 열]에 체크하고 확인을 누르면 마스터 요약표가 자동 생성됩니다. 단, 이 방식은 결과가 정적 값으로 고정되므로 원본이 바뀌면 새로 고침이 되지 않고 다시 실행해야 한다는 점을 유의해야 합니다.
실무에서 가장 자주 마주치는 파워쿼리 통합 3대 오류
파워쿼리로 시트를 합치다 보면 종종 예상치 못한 오류가 발생합니다. 아래 세 가지만 기억해 두시면 1분 안에 문제를 해결할 수 있습니다.
- 행 개수가 2배, 3배로 기하급수적으로 늘어나는 경우: 앞서 설명한 자가 참조(무한 루프) 문제입니다. 쿼리가 로드된 결과 표까지 다시 데이터로 읽어 들인 것이므로, 쿼리 단계의 Name 열 필터에서 결과 표 이름을 반드시 제외하도록 조건을 수정해 주세요.
- 특정 열만 데이터가 비어 있고 null로 채워지는 경우: 시트마다 머리글 이름이 미세하게 다른 경우입니다. '금액'과 '매출액'처럼 단어가 다르거나, 머리글 뒤에 눈에 보이지 않는 공백('수량 ')이 들어있으면 파워쿼리는 다른 열로 취급합니다. 원본 머리글 텍스트를 정확히 통일해 주면 해결됩니다.
- 숫자 열인데 피벗이나 함수에서 합계 계산이 안 되는 경우: 원본 데이터에 빈칸이나 하이픈(-) 등이 섞여 있어 파워쿼리가 해당 열을 텍스트 형식으로 인식했기 때문입니다. 파워쿼리 편집기에서 해당 열의 헤더 아이콘(ABC)을 클릭해 [정수] 또는 [10진수]로 형식을 변경해 주세요.
정리: 한 번의 자동화 세팅이 매달의 칼퇴를 만듭니다
매달 마감 때마다 수십 개의 시트를 열어가며 복사-붙여넣기를 반복하고 계셨다면, 이번 달에는 파워쿼리의 = Excel.CurrentWorkbook()을 활용해 자동화 쿼리를 딱 한 번만 만들어 보시길 권합니다.
처음 쿼리를 설계하고 필터를 세팅하는 데 들어가는 시간은 10분에 불과하지만, 다음 달부터는 새로운 시트가 몇 개가 추가되든 마우스 우클릭 → [새로 고침] 한 번으로 모든 취합 작업이 1초 만에 완료됩니다. 단순 반복 작업에 쓰던 소중한 시간을 아껴 더 중요한 분석과 업무에 집중해 보세요.
'엑셀·스프레드시트' 카테고리의 다른 글
| 매입·매출 관리 엑셀: 월별 집계와 거래처별 요약 한 번에 보기 (0) | 2026.09.17 |
|---|---|
| 두 엑셀 파일 차이 찾기: Spreadsheet Compare로 변경 내용 비교하기 (0) | 2026.09.05 |