엑셀 지점별 실적 파일, 복사·붙여넣기 없이 자동으로 합치기

지점별 주간 실적 파일을 매주 열어서 복사하고 붙여넣는 대신, 파워 쿼리의 [폴더에서] 기능으로 폴더 안의 파일을 한 번에 합치는 방법을 정리합니다. 폴더에 파일을 넣고 새로 고침만 누르면 되고, 마스터 파일을 열 때 자동으로 새로 고치도록 설정하는 방법까지 다룹니다.

이번 영상에서 습득할 수 있는 주요 내용

1. 사전 준비: 폴더 구성과 양식 확인

폴더 안에 여러 개의 파일들이 있습니다(지점실적_1주차.xlsx, 지점실적_2주차.xlsx, 지점실적_3주차.xlsx). 각 파일에는 '주간실적'이라는 시트가 있고, 열 머리글은 날짜, 지점, 제품, 담당자, 수량, 매출입니다.

행 수는 파일마다 다릅니다. 1주차 파일에는 12행, 2주차에는 14행, 3주차에는 11행이 들어 있습니다.

꼭 확인할 조건

이 자동화에서 가장 중요한 조건은 모든 파일의 양식이 같아야 한다는 점입니다. 구체적으로는 다음 세 가지만 맞으면 됩니다.

반면에 행 수는 파일마다 달라도 괜찮습니다. 또한 원본 데이터를 '표'로 만들어 둘 필요도 없고, 일반 엑셀 범위 그대로 사용해도 됩니다.

마스터 파일은 폴더 바깥에 두세요

합친 결과를 보여 줄 마스터 파일은 합칠 파일들이 있는 폴더 바깥에 따로 두어야 합니다. 마스터 파일이 같은 폴더 안에 있으면 파워 쿼리가 마스터 파일 자기 자신까지 읽으려고 해서 문제가 생길 수 있기 때문입니다.

2. 폴더 연결하고 파일 합치기

이제 마스터 파일을 열겠습니다. 다른 파일의 내용을 합치는 용도이므로 아무 내용도 없는 빈 파일이면 됩니다.

파워 쿼리 편집기가 열립니다. 화면이 낯설어 보이지만 클릭 몇 번이면 끝납니다.

세 개 파일의 데이터가 하나로 합쳐진 미리 보기가 나타납니다. 맨 앞에 Source.Name이라는 열이 새로 생긴 것도 확인하실 수 있습니다. 각 행이 어느 파일에서 왔는지 알려 주는 열이며, 나중에 이 열이 유용하게 쓰입니다.

마지막으로 [홈] 탭 > [닫기 및 로드]를 누르면 마스터 파일에 표가 만들어집니다. 데이터가 모두 들어왔는지 확인해 보시기 바랍니다.

이 과정은 처음 한 번만 하면 됩니다.

3. 새 파일을 넣으면 자동으로 반영됩니다

이제 4주차 파일이 도착했다고 가정해 보겠습니다. 4주차 파일을 '지점별_주간실적' 폴더에 넣고, 마스터 파일로 돌아와 [데이터] 탭의 [모두 새로 고침]을 누릅니다.

데이터 행이 늘어났습니다. 쿼리를 다시 만들 필요도, 새 파일을 엑셀에 알려 줄 필요도 없습니다. 폴더에 넣고 새로 고침, 이게 전부입니다.

파일을 폴더에서 제거하면 데이터에도 반영됩니다

이번에는 반대로 4주차 파일을 폴더에서 다른 곳(예: 바탕 화면)으로 옮기고 다시 새로 고침을 해 보겠습니다. 4주차 데이터가 함께 사라지고 행 수가 다시 줄어듭니다.

이처럼, 마스터 파일은 폴더 안의 파일 상태를 그대로 보여 주는 거울 같은 존재입니다. 잘못 넣은 파일이 있다면 폴더에서 빼기만 하면 됩니다. 매번 '이미 붙였던가?'를 고민하거나 데이터를 덮어쓸 걱정도 없어집니다.

4. 새로 고침도 자동으로: 파일을 열 때 자동 갱신

새로 고침 버튼을 누르는 것조차 번거롭다면 이것마저도 자동으로 만들 수 있습니다.

이제 마스터 파일을 저장하고 닫습니다. 그리고 앞에서 옮겨 둔 4주차 파일을 다시 폴더에 넣고 마스터 파일을 열어 보면, 열자마자 알아서 50행으로 합쳐져 있는 것을 확인할 수 있습니다.

같은 탭에는 '새로 고침 간격' 옵션도 있습니다. 마스터 파일을 열어 둔 채로 파일이 수시로 들어오는 환경이라면, 몇 분마다 자동으로 확인하도록 설정할 수도 있습니다.

5. 파일명에서 '주차' 정보를 뽑고 피벗까지 자동 갱신 [멤버십 회원용]

지금까지 만든 마스터 표에는 '몇 주차 데이터인지'를 알려 주는 열이 없습니다. 그래서 주차별로 집계하려고 하면 불편합니다. 하지만 Source.Name 열 정보를 활용하면 파일명에서 주차를 자동으로 뽑아낼 수 있고, 이를 기준으로 만든 피벗 테이블과 피벗 차트까지 새로 고침 한 번으로 갱신할 수 있습니다. 새 주차 파일을 폴더에 넣고 새로 고침하면 피벗 테이블의 열이 하나 더 늘어나는 것까지 확인합니다.

매주 하던 취합과 보고서 갱신이 '새로 고침' 한 번으로 끝납니다. 구체적인 설정 과정은 멤버십 전용 풀 버전에서 단계별로 보여 드립니다.

6. 자동화가 갑자기 멈췄을 때 점검 포인트 [멤버십 회원용]

현업에서는 파일이 항상 깔끔하게 들어오지 않습니다. 어떤 지점은 시트 이름을 바꿔서 보내기도 하고, 열 머리글이 달라지기도 합니다. 새로 고침이 갑자기 안 되거나 합계가 평소와 다르게 나올 때 가장 먼저 확인해야 할 점검 포인트 세 가지와, 문제가 된 파일을 처리하는 방법을 풀 버전에서 정리해 드립니다.

MVP TIP — 폴더 자동 합치기를 안정적으로 쓰려면?

Microsoft Excel MVP로서 현업에서 이 자동화를 운영하며 알게 된, 문제를 미리 막아 주는 습관을 정리했습니다.

1. 마스터 파일은 반드시 폴더 바깥에 두세요

마스터 파일이 합칠 파일과 같은 폴더에 있으면 자기 자신까지 읽으려고 해서 문제가 생길 수 있습니다. 처음부터 폴더 바깥에 두는 구조로 만들어 두세요.

2. 폴더에 넣기 전에 양식부터 확인하는 습관을 들이세요

시트 이름, 열 머리글, 기본 구조 이 세 가지가 같아야 합니다. 각 지점에 보낼 양식 파일을 하나로 고정해서 배포하면, 문제의 대부분을 미리 막을 수 있습니다.

3. 파일 이름 규칙을 처음부터 정해 두세요

'지점실적_1주차.xlsx'처럼 모든 파일의 이름을 같은 규칙으로 통일해 두면, 합친 데이터에서 파일명을 활용할 수 있는 길이 열립니다. 규칙 없이 제각각인 이름은 나중에 바로잡기가 훨씬 어렵습니다.

4. 잘못 들어온 파일은 폴더에서 빼면 됩니다

마스터 파일은 폴더의 상태를 그대로 반영하므로, 잘못 들어온 파일은 폴더에서 빼고 새로 고침하면 데이터도 함께 빠집니다. 별도로 데이터를 지우는 작업이 필요 없습니다.

자주 묻는 질문 (FAQ)

마치며

폴더에 파일만 넣고 새로 고침하면 지점별 실적 파일이 자동으로 합쳐지는 방법에 대해 알아보았습니다. 매주 복사·붙여넣기로 취합하느라 번거로우셨던 분들은 오늘 알려드린 방법을 여러분의 업무에 적용해 보시기 바랍니다.

파일명에서 주차를 뽑아 피벗 테이블까지 자동으로 갱신하는 방법과, 파일 양식이 어긋났을 때의 점검 방법이 궁금하시다면 멤버십 회원용 풀 버전 영상에서 확인해보세요.