여러 엑셀 파일 합치기, 파워 쿼리로 자재 입고 자료 취합하는 방법

같은 열 구조로 작성된 여러 엑셀 파일은 파워 쿼리의 ‘폴더에서 가져오기’로 한 표에 모을 수 있습니다. 처음에 가져올 파일과 처리 방법을 정해 두면, 그다음부터는 원본 파일을 저장하고 새로 고침만 해도 결과가 갱신됩니다.

매일 ERP에서 입고 내역을 내려받아 주간 보고서를 만드는 상황을 생각해 보겠습니다. 날짜별 파일을 하나씩 열어 복사하면, 어디까지 붙였는지, 수정본은 다시 반영했는지 일일이 확인하는 일까지 따라오죠. 파일의 열 구성이 같다면 폴더에 모아 한 번에 가져오는 방법을 쓸 수 있습니다.

이 글에서는 날짜별 입고 자료를 취합하는 업무를 중심으로, 파일 준비부터 결합, 새로 고침, 결과 확인까지 차례로 설명해 드리겠습니다.

엑셀 파일 합치기, 행을 모으는 작업과 표를 연결하는 작업

먼저 합친 결과가 어떤 모습이어야 하는지부터 정해야 합니다. 같은 ‘합치기’라도 날짜별 입고 내역을 아래로 쌓는 일과 입고 내역 옆에 품목 정보를 붙이는 일은 서로 다른 작업이기 때문입니다.

추가는 열 이름을 기준으로 기록을 이어 붙이고, 병합은 선택한 열에서 값이 일치하는 행끼리 두 표를 연결합니다. 이 글에서는 이 가운데 첫 번째, 날짜별 입고 내역을 하나의 표로 모으는 추가 방식을 다룹니다.

파워 쿼리로 여러 엑셀 파일 합치는 순서

아래 절차는 Windows용 Microsoft 365 데스크톱 Excel을 기준으로 설명합니다. 다른 버전이나 Mac에서는 지원 기능과 메뉴가 다를 수 있습니다.

1. 원본 표와 취합 파일 준비하기

먼저 날짜별 입고 파일을 자재입고_원본처럼 취합용으로 정한 폴더에 모읍니다. 각 파일에 그날의 신규 내역만 있는지, 이전 날짜까지 누적된 내역이 들어 있는지부터 확인해 주세요. 아래 절차는 일별 신규 내역을 모으는 방식입니다. 누적 내역을 그대로 쌓으면 같은 입고가 여러 번 포함될 수 있습니다.

파일마다 열 이름과 자료형도 맞춥니다. 입고 자료라면 입고일·입고번호·품목코드·입고수량 등을 같은 열 이름으로 관리합니다. 기존 파일을 기준으로 다음을 준비해 주세요.

1. 첫 행에는 열 이름을 두고, 그 아래에는 한 행에 입고 상세 한 건씩 기록합니다. 제목용 병합 셀이나 중간 합계 행은 데이터 범위에서 뺍니다.

2. 품목코드는 텍스트 값으로 보존합니다. 앞자리 0이 있는 코드를 새로 입력한다면 먼저 열을 텍스트 형식으로 지정하세요. 셀 서식으로 0을 화면에만 보이게 한 숫자와는 구분해야 합니다.

3. 머리글과 데이터 범위를 함께 선택한 뒤 삽입 → 표를 누르고 ‘머리글 포함’에 체크합니다. 이어서 표 디자인 → 표 이름에서 각 파일의 표 이름을 tblReceipt로 맞추고, 저장한 뒤 파일을 닫습니다.

폴더 커넥터는 샘플에서 선택한 표를 나머지 파일에서도 찾아 읽으므로, 각 파일에 같은 이름의 표를 준비해야 합니다.

취합 결과를 담을 파일은 자재입고_취합.xlsx라는 이름으로 만들어 원본 폴더 밖에 저장해 주세요. 원본 폴더 안에 두면 취합한 결과가 다시 원본으로 읽혀 들어갈 수 있습니다.

2. 폴더를 선택하고 결합할 파일만 남기기

취합 파일에서 데이터 → 데이터 가져오기 → 파일에서 → 폴더에서를 선택하고 자재입고_원본 폴더를 지정합니다. 파일 목록이 나오면 데이터 변환을 눌러 파워 쿼리 편집기를 엽니다.

편집기에서 각 열의 필터 화살표를 눌러 다음 조건을 지정합니다.

• 확장명(Extension): .xlsx만 선택합니다.

• 이름(Name): 파일명이 입고_로 시작하도록 맞췄다면, ‘텍스트 필터 → 시작 문자’에 입고_를 입력합니다. 실제 파일의 공통 접두사에 맞게 지정하고, ~$로 시작하는 임시 파일은 제외합니다.

• 폴더 경로(Folder Path): 하위 폴더의 파일이 섞이지 않도록 원본 폴더 경로 하나만 선택합니다.

이름을 접두사로 거르는 데도 이유가 있습니다. 파일명을 날짜별로 하나씩 체크해 고르면 다음 날 추가되는 파일은 걸러지고 맙니다. 파일을 먼저 필터링한 뒤 결합하는 순서는 Microsoft의 폴더 가져오기 안내에도 나와 있습니다.

다만 접두사 조건만으로는 어느 파일이 최신본인지 가릴 수 없습니다. 이름이 입고_로 시작하면 수정본이나 백업본도 조건을 통과할 수 있죠. 그러니 원본 폴더에는 날짜별로 현재 사용하는 파일 하나만 두고, 이전 버전이나 백업본은 폴더 밖에 따로 보관하세요.

3. 샘플 파일에서 가져올 표 선택하기

필터를 마쳤다면 Content 열 머리글에 있는 파일 결합 아이콘을 누릅니다. ‘파일 결합’ 창이 열리면 샘플 파일을 살펴보고, 가져올 대상으로 tblReceipt를 선택한 뒤 확인을 누릅니다.

처음 설정할 때는 ‘오류가 있는 파일 건너뛰기’에 체크하지 않는 편이 좋습니다. 오류 난 파일을 건너뛰고 나머지만 결합하면, 결과만 봐서는 자료 일부가 빠졌다는 사실을 알아차리기 어렵습니다. 오류가 나면 해당 파일의 이름과 표 이름부터 살펴보세요.

결합이 끝나면 각 파일에 같은 변환을 적용하는 보조 쿼리와 최종 결과 쿼리가 만들어집니다.

4. 품목코드·날짜·수량의 자료형 확인하기

다음으로 최종 결과 쿼리에서 각 열 머리글 왼쪽의 자료형 아이콘을 살펴봅니다. 입고일은 날짜, 입고번호·품목코드는 텍스트로 맞춥니다. 입고수량은 개 단위의 정수인지, 무게나 길이처럼 소수가 필요한 값인지에 맞춰 지정하고, 파일마다 단위가 같은지도 확인합니다.

특히 품목코드 001이 1로 바뀌지 않았는지 꼭 확인하세요. 파워 쿼리는 Excel 자료를 가져올 때 자료형을 자동으로 감지해 지정하기도 하므로, 오른쪽 ‘적용된 단계’ 목록의 변경된 유형(Changed Type) 단계도 함께 살펴봐야 합니다.

코드가 이미 숫자로 바뀌었다면, 마지막 단계에서 텍스트로 되돌려도 사라진 앞자리 0은 돌아오지 않습니다. 이럴 때는 ‘적용된 단계’에서 품목코드가 처음 1로 바뀌는 단계를 찾아 그 숫자 변환을 수정하거나 삭제하고, 001이 아직 남아 있는 단계에서 품목코드를 텍스트로 지정합니다. 보조 쿼리인 ‘샘플 파일 변환’과 최종 쿼리에서도 뒤따르는 단계가 코드를 다시 숫자로 바꾸지 않는지도 점검해 주세요. 원본 파일에서부터 1로 저장돼 있었다면 품목 기준표 등을 보고 원래 코드를 찾아야 합니다.

마지막으로, 각 행이 어느 파일에서 왔는지 되짚을 수 있도록 Source.Name 같은 원본 파일명 열은 지우지 말고 남겨 두세요. 이 열의 이름은 환경에 따라 다르게 표시될 수 있습니다.

5. 시트에 불러오고 원본과 대조하기

홈 → 닫기 및 로드를 눌러 결과를 시트로 불러옵니다. 처음 설정할 때는 날짜별 파일 몇 개부터 모아 보고, 원본과 결과를 나란히 대조해 주세요.

• 파일별 데이터 행 수: 머리글·합계 행을 제외한 원본 기록이 빠짐없이 들어왔는지 확인합니다. 원본 파일명 열로 나눠 보면 누락된 파일을 찾기 쉽습니다.

• 코드와 값: 품목코드의 앞자리 0, 날짜, 수량과 단위가 원본대로 유지됐는지 확인합니다.

• 품목별 수량: 같은 기간·같은 단위의 원본과 비교합니다. 전체 합계가 맞더라도 일부 품목의 누락과 다른 품목의 중복이 서로 상쇄될 수 있습니다.

전체 합계와 행 수는 수식으로 검산할 수 있습니다. 시트에 불러온 결과 표 안의 아무 셀이나 선택하고, 표 디자인 → 표 이름에서 이름을 tblCombined로 지정합니다. 원본 파일의 tblReceipt와는 별개의 표입니다. 결과 표와 떨어진 빈 셀에는 아래 수식을 하나씩 입력해 주세요.

• 전체 입고수량: =SUM(tblCombined[입고수량])

• 데이터 행 수: =ROWS(tblCombined[입고번호])

이처럼 표 이름과 열 이름으로 범위를 가리키는 방식을 구조화 참조라고 하며, 표에 행이 추가되거나 삭제되면 참조 범위도 함께 바뀝니다. 수식의 결과를 원본 파일들의 행 수·합계와 비교하고, 새 파일을 추가한 뒤에도 새 행까지 계산에 포함되는지 확인해 주세요. 여러 품목을 합산할 때는 단위를 맞추고 품목별 대조도 함께 진행합니다.

같은 기록이 두 번 포함되지 않았는지도 살펴보세요. 입고번호 하나에 여러 품목 행이 딸려 있다면 번호만으로 중복을 판단할 수 없습니다. 실제 자료에서 입고 상세 한 건을 식별하는 기준을 정해 대조해야 합니다.

새 파일과 수정본은 어떻게 반영할까?

새 날짜의 파일은 저장한 뒤 새로 고침하기

다음 날 입고 파일을 내려받았다면, 기존과 같은 열 구조와 tblReceipt 표 이름으로 저장해 원본 폴더에 넣습니다. 앞서 설정한 파일명 규칙을 따르고 파일이 필터 조건에 포함되는지도 확인합니다.

그다음 취합 파일에서 데이터 → 모두 새로 고침을 누릅니다. 작업이 끝나면 원본 파일명 열에 새 파일이 포함됐는지, 해당 파일의 행 수와 품목별 수량이 결과에 반영됐는지 대조합니다.

결과에 새 파일의 내용이 보이지 않는다면 파일을 저장한 폴더 위치, 파일 필터 조건, 표 이름과 열 구조, 오류 메시지를 차례로 점검해 주세요.

수정본은 이전 버전과 함께 더하지 않기

이미 취합한 입고 내역에 정정이 생겼다면, 취합 대상인 현재 원본 파일의 해당 기록을 고쳐 저장합니다. 결과 표에서 숫자만 수정하면 다음 새로 고침 때 원본의 값으로 돌아갈 수 있습니다.

다시 새로 고침한 뒤에는 정정한 기록이 바뀌었는지, 구버전이 함께 남아 중복으로 들어오지 않았는지 확인합니다. 단순한 수량 정정인지, 행 추가·취소까지 있는지에 따라 기대하는 행 수 변화도 달라집니다.

중복이 의심될 때도 주의가 필요합니다. 품목코드가 같다는 이유만으로 행을 지우면, 같은 품목이 여러 번 정상 입고된 기록까지 사라집니다. 그러니 어떤 기록을 같은 건으로 볼지, 날짜별 최신본만 쓸지, 취소·정정 거래를 따로 남길지부터 정해야 합니다. 매일 전체 누적 내역을 내려받는 업무라면, 일별 신규 내역을 쌓는 이번 방식과는 처음부터 다르게 설계해야 하고요.

파일을 합친 뒤에도 확인이 남는다면

지금까지 본 것처럼, 정해진 규칙에 따라 자료를 모으는 작업은 AI 없이 엑셀 기능만으로도 충분히 처리할 수 있습니다.

반면 파일마다 품목 이름이 다르게 적혀 있거나, 합친 입고 내역을 생산계획의 어느 작업과 연결해야 할지 불분명하다면 확인할 범위가 훨씬 넓어집니다. 여러 파일을 한 표로 모으는 것은 시작일 뿐, 그 자료로 자재가 부족한지·작업을 시작할 수 있는지 판단하는 일은 그다음 단계에서 따로 설계해야 합니다.

ERP와 MES의 역할 차이와 시스템 간 연결 문제는 ERP·MES 차이와 현황 집계에서 더 자세히 살펴볼 수 있습니다.

조슈아앤컴퍼니는 제조업 조립 공정의 생산관리 과제에서 기존 문서와 ERP에서 내려받은 자료를 연결하는 기준을 세우고, 작업계획 발행과 실적 집계 같은 반복 업무를 자동화한 경험이 있습니다. 이 과정에서도 서로 다른 자료에서 같은 작업을 찾아내는 기준과 현장 담당자의 확인이 함께 필요했습니다.

파일을 합친 뒤에도 매번 품목을 다시 찾고 수량을 대조하느라 시간을 쓰고 있다면, 취합하는 파일의 종류, 반복해서 만드는 결과물, 가장 오래 걸리는 확인 작업을 알려 주세요. 지금 쓰고 있는 문서와 업무 흐름을 바탕으로 어떤 규칙을 정하고 어떤 처리를 자동화할 수 있을지 함께 살펴보겠습니다.