ERP 엑셀 데이터 비교, 두 파일에서 수량 차이와 빠진 기록 찾는 방법

ERP에서 내려받은 엑셀과 현장 기록을 비교하려면, 먼저 양쪽에서 ‘같은 입고·작업 건’을 찾는 기준부터 맞춰야 합니다. 기준이 정해지면 수량이 서로 다른 항목과 한쪽에만 있는 기록을 나눠 확인할 수 있습니다. 이 비교 과정은 엑셀에 들어 있는 파워 쿼리(데이터를 불러와 정리하고 연결하는 기능)로 만들 수 있습니다.

자재 입고를 마감하는데 ERP와 현장 엑셀의 합계가 맞지 않는 상황을 떠올려 볼까요? 두 파일을 나란히 띄워 놓고 같은 줄끼리 맞춰 보면, 정렬 순서가 다르거나 현장에서 입고 한 건을 여러 줄로 나눠 적었을 때 엉뚱한 거래끼리 비교하게 됩니다. 합계를 맞추는 데만 집중하다 보면 정작 어느 기록을 고쳐야 하는지도 판단하기 어렵고요.

이 글에서는 가상의 입고 자료로 두 엑셀 파일에서 차이를 찾고 그 원인을 좁혀 가는 방법을 설명합니다.

엑셀 두 파일 비교 전, 같은 입고를 찾는 ‘비교 키’부터 정하세요

입고 자료에서 품목코드가 같다고 해서 같은 거래는 아닙니다. 같은 품목이 하루에 여러 번 들어올 수 있기 때문이죠. 반대로 입고번호 하나에 여러 품목이 들어 있다면 입고번호만으로도 거래를 구분하기 어렵습니다.

그래서 이 글에서는 입고번호와 품목코드를 함께 보고 같은 입고 항목을 찾습니다. 이처럼 서로 다른 자료에서 같은 대상을 찾아 주는 값을 ‘비교 키’라고 합니다. 비교 키로는 품목명보다 코드처럼 처음부터 구분 용도로 관리하는 값이 적합합니다. 회사에서 창고나 로트(같은 조건으로 생산·입고된 묶음)별로 나눠 관리한다면, 그 항목까지 비교 키에 더해야 합니다.

비교 키를 정했다면 아래 기준도 함께 맞춥니다. 예시는 10월 20일 자재 입고분을 10월 21일 오전 9시에 받은 두 파일로 비교한다고 가정합니다.

비교 키가 비어 있거나 수량이 숫자로 인식되지 않는 행은 따로 떼어 확인 대상으로 남겨 둡니다. 빈 수량을 0으로 채우면 ‘아직 모르는 수량’이 ‘0개’로 처리되기 때문입니다.

파워 쿼리로 ERP 엑셀 데이터를 비교하는 4단계

아래 안내는 Windows용 Microsoft 365 데스크톱 Excel을 기준으로 합니다. 사용하는 버전과 운영체제에 따라 메뉴 이름이나 지원 기능이 다를 수 있습니다.

예시로 쓸 자료는 아래 두 표입니다. 설명을 위해 만든 가상 자료로, 현장에서는 R203 입고를 오전·오후 두 번에 나눠 하차하면서 두 줄로 적었다고 가정합니다.

ERP 파일 — 입고 항목별 기록

현장 파일 — 입고를 기록한 순서대로 적은 상세 내역

이번에 사용할 기능은 여러 파일의 행을 아래로 이어 붙이는 ‘추가’가 아니라, 비교 키가 같은 자료끼리 옆으로 이어 붙이는 ‘병합’입니다. 병합 방식은 양쪽 파일의 모든 행을 남기는 ‘전체 외부(Full outer)’를 고릅니다.

1단계: 두 파일 불러오고 품목코드·수량 형식 맞추기

ERP에서 내려받은 파일과 현장 기록 파일은 원본을 그대로 보관하고, 비교에 쓸 사본을 따로 만듭니다. 각 사본에서 머리글을 포함한 데이터 범위를 선택해 삽입 → 표로 지정한 뒤 저장합니다. 예시 자료를 직접 만들어 따라 해 본다면, 앞자리 0이 사라지지 않도록 품목코드 001·002·003은 처음부터 텍스트로 입력해 주세요.

이제 비교 결과를 담을 새 통합 문서를 열고 데이터 → 데이터 가져오기 → 파일에서 → Excel 통합 문서에서를 선택합니다. ERP 자료의 비교용 사본을 고른 뒤 탐색 창에서 해당 표를 선택하고 데이터 변환을 누르면 파워 쿼리 편집기가 열립니다. 이 쿼리(파워 쿼리에서 자료를 불러와 가공하는 작업 단위)의 이름은 ERP_원본으로 정합니다. 시트에 표를 펼치지 않고 연결만 해 두려면 닫기 및 다음으로 로드 → 연결만 만들기를 선택합니다. 현장 자료의 사본도 같은 방식으로 가져와 현장_원본이라는 이름을 붙입니다.

쿼리 이름의 ‘원본’은 집계하기 전의 상세 자료라는 뜻일 뿐, 보관해 둔 원본 파일을 고친다는 의미는 아닙니다.

파워 쿼리 편집기에서는 열 제목 왼쪽에 있는 자료형 아이콘을 보고 아래 항목을 확인합니다.

• 입고번호·품목코드: 양쪽 모두 텍스트 형식으로 지정합니다. 코드 앞뒤에 불필요한 공백이 있다면 회사의 코드 관리 규칙에 맞게 정리합니다.

• 수량: 이번 예시에서는 정수로 지정합니다.

• 빈 값·오류: 비교 키가 비었거나 수량이 오류로 바뀐 행은 앞서 정한 대로 따로 떼어 두고, 뺀 행 수와 수량을 기록합니다. 이번 예시에는 이런 행이 없습니다.

주의할 점이 하나 있습니다. 파워 쿼리가 자동으로 추가한 ‘변경된 유형’ 단계에서 001이 이미 숫자 1로 바뀌었다면, 나중에 텍스트로 다시 바꿔도 사라진 0은 돌아오지 않습니다. 이때는 편집기 오른쪽 ‘적용된 단계’ 목록에서 코드가 온전히 남아 있던 앞 단계로 돌아가, 숫자로 바꾼 설정을 고쳐야 합니다.

2단계: 그룹화로 입고번호·품목코드별 수량 집계하기

원본 쿼리는 그대로 두고, 편집기 왼쪽 쿼리 목록에서 각 원본 쿼리를 마우스 오른쪽 버튼으로 눌러 참조(Reference)를 선택합니다. 원본 쿼리의 결과를 이어받는 새 쿼리가 만들어지면, 이름을 각각 ERP_집계, 현장_집계로 정합니다. 이렇게 하면 원본 쿼리에는 기록 ID와 상세 행이 그대로 남고, 비교용 합계는 집계 쿼리에서만 만들 수 있습니다.

집계하기 전에 같은 키가 여러 줄인 기록부터 확인합니다. R203처럼 실제로 나눠 들어온 물량이라면 합산하고, 같은 줄을 실수로 복사한 중복 입력이라면 합치지 말고 원본부터 정정해야 합니다.

이제 두 집계 쿼리에서 각각 홈 → 그룹화 → 고급을 엽니다. 그룹 기준으로 입고번호와 품목코드 두 열을 지정하고, 아래 표처럼 집계 항목 두 개를 만듭니다.

그룹화를 마치면 현장의 R203·001은 수량 60개, 현장기록수 2인 한 줄로 정리됩니다.

마지막으로, 병합한 뒤에도 어느 쪽 열인지 구분할 수 있도록 열 이름을 바꿉니다. ERP 쪽 입고번호·품목코드는 ERP입고번호·ERP품목코드로, 현장 쪽은 현장입고번호·현장품목코드로 바꿔 주세요.

3단계: 전체 외부 병합으로 한쪽에만 있는 기록까지 남기기

파워 쿼리 편집기에서 홈 → 쿼리 병합 → 새 쿼리로 병합을 선택하고, 병합 창에서 다음 순서대로 설정합니다.

1. 위쪽 표는 ERP_집계, 아래쪽 표는 현장_집계로 지정합니다.

2. 위쪽 표에서 ERP입고번호 열을 클릭한 뒤, Ctrl 키를 누른 채 ERP품목코드 열을 클릭합니다.

3. 아래쪽 표에서도 같은 방법으로 현장입고번호, 현장품목코드 순서로 선택합니다. 열 제목에 표시되는 선택 순서 1·2가 위아래 표에서 서로 짝이 맞아야 합니다.

4. 조인 종류(연결 방식)는 전체 외부(양쪽의 모든 행)를 선택합니다. 코드는 정확히 일치해야 하므로 ‘유사 항목 일치’는 사용하지 않습니다.

5. 확인을 누르면 현장 자료가 접힌 상태로 담긴 열이 새로 생깁니다. 이 열 제목의 확장 버튼을 눌러 현장입고번호·현장품목코드·현장수량·현장기록수를 펼치고, ‘원래 열 이름을 접두사로 사용’은 체크를 해제합니다. 새 쿼리의 이름은 입고_비교로 정합니다.

병합 결과에는 같은 키끼리 연결된 항목과 함께, ERP에만 있는 R204와 현장에만 있는 R205도 남습니다.

4단계: 사용자 지정 열로 수량 차이와 기록 유무 표시하기

병합하고 나면 상대편에 기록이 없는 칸에는 null이 표시됩니다. null은 ‘값이 없다’는 뜻이지, 수량이 0개라는 뜻이 아닙니다. 그래서 수량을 비교하기 전에 기록이 양쪽에 모두 있는지부터 구분해야 합니다.

열 추가 → 사용자 지정 열을 열고, 새 열 이름을 비교상태로 정한 뒤 아래 식을 입력합니다. 이 식은 엑셀 셀에 입력하는 IF 함수가 아니라 파워 쿼리 전용 수식(M 식)입니다. 앞 단계에서 열 이름을 안내대로 바꿨다면 그대로 쓸 수 있습니다.

if [ERP입고번호] = null then "현장만 존재"
else if [현장입고번호] = null then "ERP만 존재"
else if [ERP수량] = [현장수량] then "수량 일치"
else "수량 차이"

식은 위에서부터 차례로 조건을 확인합니다. ERP 기록이 없으면 ‘현장만 존재’, 현장 기록이 없으면 ‘ERP만 존재’, 양쪽 수량이 같으면 ‘수량 일치’, 나머지는 ‘수량 차이’로 표시하는 식입니다.

같은 방법으로 수량차이 열도 추가합니다. 이 열은 양쪽에 기록이 모두 있을 때만 ‘현장수량 − ERP수량’을 계산하고, 한쪽 기록이 없으면 null로 비워 둡니다.

if [ERP입고번호] = null or [현장입고번호] = null then null
else [현장수량] - [ERP수량]

두 열을 추가했다면 비교상태는 텍스트, 수량차이는 정수 형식으로 지정합니다. 그 결과를 정리하면 아래 표와 같습니다. 읽기 쉽도록 입고번호와 품목코드를 한 칸에 묶고 null은 ‘기록 없음’으로 풀어 썼지만, 실제 쿼리에는 원본을 추적할 수 있도록 양쪽 식별 열을 모두 남겨 둡니다.

이제 수량이 다른 1항목과 한쪽에만 있는 2항목을 나눠서 확인할 수 있습니다. 단, ‘수량 일치’는 이번에 정한 비교 단위에서 합계가 같다는 뜻일 뿐입니다. 세부 기록 하나하나나 실제 물량까지 모두 맞는다는 의미는 아닙니다.

차이를 해석하기 전, 비교 결과에서 빠진 항목부터 검산하세요

차이가 난 원인을 살펴보기 전에, 비교하는 과정에서 기록이 빠지거나 수량이 부풀지 않았는지부터 확인해야 합니다. 이를 위해 차이 나는 항목만 걸러 보지 말고 전체 결과를 워크시트에 표로 불러옵니다.

파워 쿼리 편집기에서 홈 → 닫기 및 로드를 눌러 Excel로 돌아온 다음, 데이터 → 쿼리 및 연결을 엽니다. 목록에서 입고_비교를 마우스 오른쪽 버튼으로 눌러 다음으로 로드를 선택하고, 가져오기 창에서 표와 새 워크시트를 고른 뒤 확인을 누릅니다. 이미 표로 불러와 있다면 그 결과를 그대로 쓰면 됩니다.

불러온 표에서 아래 숫자를 차례로 확인합니다. 필터가 걸려 있으면 화면에 보이는 행 수와 실제 전체 행 수가 다를 수 있으니 주의해 주세요.

• 행 수: 병합 결과는 모두 5항목입니다. 이 중 ERP입고번호가 채워진 행과 현장입고번호가 채워진 행은 각각 4개로, 집계한 항목 수와 같아야 합니다.

• 합계: ERP기록수·현장기록수 합계는 원본 행 수인 4와 5, ERP수량·현장수량 합계는 원본 수량인 280개와 255개 그대로여야 합니다.

두 파일의 전체 수량 차이 255 − 280 = -25개는 양쪽에 모두 있는 항목의 차이 -5개, ERP에만 있는 40개, 현장에만 있는 20개로 나눠 설명할 수 있습니다. 이는 합계가 왜 다른지 쪼개 본 것일 뿐, 실제 자재가 25개 부족하다는 뜻은 아닙니다. 실제 재고는 품목별로 따져야 합니다. 비교표의 수량차이 열은 양쪽에 기록이 있는 항목만 계산하므로, 그 열의 합계는 -5개입니다.

실제 자료에서 병합 후 수량이 늘었다면, 같은 키가 양쪽에 여러 행씩 남아 여러 번 짝지어졌을 수 있습니다. 결과에서 중복 행부터 지우지 말고 집계 기준과 상세 행을 먼저 확인하세요.

검산을 마쳤다면 비교상태 필터에서 ‘수량 일치’를 빼고, 먼저 확인할 3항목만 추려 봅니다. 이때도 원본과 전체 비교 결과는 지우지 않고 그대로 남겨 둡니다.

수량 차이와 한쪽에만 있는 기록, 바로 고치지 말고 원인부터 확인하세요

비교표가 알려 주는 것은 어디를 확인해야 하는지까지입니다. 어느 쪽이 맞고 왜 달라졌는지까지 증명해 주지는 않습니다. 그러니 한쪽 숫자를 다른 쪽에 덮어쓰기 전에, 아래처럼 항목별 근거부터 확인해야 합니다.

결과를 넘겨받은 담당자가 항목마다 확인 근거·조치·재확인 시각을 따로 기록해 두면, 같은 차이가 다시 나왔을 때 처음부터 조사하는 수고를 줄일 수 있습니다. 기록을 정정했거나 새 전표가 반영됐다면, 같은 조회 조건으로 자료를 다시 내려받아 새 비교용 사본으로 다시 비교합니다. 이때 이전 원본과 비교 결과도 함께 보관해 둡니다.

ERP·현장 데이터 대조가 반복된다면, 연결 기준과 자동화를 검토하세요

한 번 비교하는 일은 엑셀만으로도 충분히 시작할 수 있습니다. 하지만 매번 같은 파일을 내려받아 같은 코드를 맞추는 일이 반복된다면, 어떤 자료를 어떤 규칙으로 연결하고 차이는 누가 확인할지부터 정리해 둘 필요가 있습니다.

조슈아앤컴퍼니는 제조업 조립 공정의 생산관리 프로젝트에서 기존 문서와 ERP 자료를 서로 맞추는 기준을 만들고, 작업계획 발행이나 실적 집계 같은 반복 업무를 자동화했습니다. 이때 식별 기준과 업무 규칙은 현장 담당자와 함께 정리했습니다. 실제로 구축하는 범위는 회사가 쓰는 자료와 업무 규칙에 따라 달라집니다.

같은 항목을 찾아 정해진 계산을 되풀이하는 일이라면, 미리 정한 규칙대로 처리하는 자동화를 검토해 볼 수 있습니다. 다만 양쪽 코드를 맞춰 볼 기준이 없거나 예외를 어떻게 처리할지 불분명하다면, 그 기준부터 정해야 합니다. AI 도입은 자료를 대조한 다음 단계에서 검색·설명·판단을 도와줄 기능이 필요한지에 따라 검토하면 됩니다.

어떤 ERP 자료와 현장 파일을 비교하는지, 같은 항목을 무엇으로 찾는지, 매번 어떤 차이를 확인하는지 알려 주세요. ‘ERP 입고 내역과 현장 엑셀을 대조할 때마다 분할 입고와 반영 시점 차이를 다시 확인한다’처럼 간단히 적어 주셔도 됩니다. 지금 쓰는 자료와 확인 과정을 바탕으로 연결 기준과 반복 대조의 자동화 방안을 함께 검토하겠습니다.