엑셀 비교: 두 파일의 누락·중복·금액 차이를 따로 찾는 방법

AI Editter

두 엑셀 파일의 합계가 같아도 정산이 맞았다고 결론 내릴 수는 없습니다. 한 거래가 빠지고 같은 금액의 다른 거래가 두 번 들어가면 합계만으로는 발견되지 않습니다. 행 순서까지 달라졌다면 셀을 나란히 놓고 비교하는 방법도 맞지 않습니다.

이 글은 동일한 거래가 두 파일에 각각 한 번씩 있어야 하는 상황을 다룹니다. 주문 원장과 거래처 정산표를 대조할 때 사용할 수 있습니다. 한 주문에 여러 품목이 있는 자료는 먼저 주문번호와 품목번호를 함께 키로 쓰거나, 주문 단위로 집계해야 합니다. 비교 단위가 다른 채로 수식부터 넣으면 정상 거래까지 오류로 잡힙니다.

셀 위치를 비교할지 거래 내용을 비교할지

행 순서가 완전히 같은 서식의 변경 사항을 찾을 때는 셀 위치 비교가 맞습니다. 반면 거래처가 행을 정렬하거나 주문을 추가한 두 파일은 같은 행 번호를 비교하면 정상 거래까지 다른 것으로 표시됩니다. 이 글은 주문번호·거래번호처럼 의미 있는 키를 기준으로 비교합니다. 원본에 키가 없으면 날짜·거래처·금액만 묶어 임의 식별자로 쓰기 전에 실제로 한 거래를 구별하는지 확인해야 합니다.

합계는 800원 차이인데, 실제로는 세 가지 문제

다음은 설명을 위해 만든 가상 자료입니다. 두 표를 각각 시트의 A1부터 붙여 넣고 표로 변환한 뒤, 표 이름을 원장과 정산으로 지정합니다. 거래번호는 두 표 모두 텍스트, 금액은 숫자로 맞춥니다.

표 머리글은 두 표 모두 거래번호, 금액으로 입력합니다. 원장은 47,500원, 정산표는 48,300원입니다. 차이는 800원이지만 원인은 다음처럼 나뉩니다.

TX-102를 XLOOKUP으로 찾으면 첫 번째 9,000원만 가져올 수 있습니다. 반대로 SUMIFS만 사용하면 18,000원이 되어 정상처럼 보입니다. 건수와 금액을 별도 열로 검사해야 하는 이유입니다.

수식을 넣기 전에 비교 규칙부터 고정하기

거래번호가 대소문자를 구분하는 식별자인지, 앞의 0이 의미가 있는지 확인합니다. 00125를 숫자 125로 바꿨다면 표시 형식을 바꾸는 것만으로 원래 식별자를 복원할 수 없습니다. 원본을 다시 가져와야 할 수 있습니다.

공백을 제거할 때도 원본 열을 덮어쓰지 않습니다. 원본거래번호와 비교키를 나란히 두고 어떤 변환을 했는지 남깁니다. AB 12와 AB12가 다른 거래일 수도 있으므로 모든 공백을 무조건 지우면 안 됩니다.

금액 열에는 빈칸, "미정" 같은 문자, 숫자 0을 구별해 둡니다. 미정 금액을 0으로 바꾸면 원장과 맞지 않는 이유가 사라집니다. 외화나 소수 금액이라면 어느 단계에서 몇 자리로 반올림하는지 먼저 정합니다.

양쪽 표에서 건수를 먼저 센다

원장에 정산건수 열을 추가하고 다음 수식을 입력합니다.

=COUNTIF(정산[거래번호],[@거래번호])

0이면 정산표에 없고, 1이면 한 행, 2 이상이면 확인할 대상입니다. 이어서 원장 내부 중복도 확인합니다.

=COUNTIF(원장[거래번호],[@거래번호])

정산표에도 반대로 원장건수를 만듭니다.

=COUNTIF(원장[거래번호],[@거래번호])

이 역방향 검사를 생략하면 TX-106처럼 정산표에만 있는 거래가 빠집니다. COUNTIF는 일반적인 텍스트 비교에서 대소문자를 구분하지 않습니다. 대소문자를 구분하는 키라면 이 방식의 적용 범위 밖이므로, 구분 규칙을 지원하는 대사 도구나 별도 검증 로직을 사용합니다.

금액 차이는 중복 판정과 분리해서 계산한다

원장에 정산합계와 차이 열을 만듭니다. 합계가 0이어도 정산건수 열을 함께 봅니다.

=SUMIFS(정산[금액],정산[거래번호],[@거래번호])

=[@정산합계]-[@금액]

원장 금액은 모두 숫자이고, 정산 금액도 비어 있지 않은 숫자인지 먼저 필터로 점검해야 합니다. SUMIFS는 조건에 맞는 행을 합산하는 함수이지, 누락된 거래나 문자 금액을 알려 주는 검증 함수는 아닙니다. 함수의 인수와 범위 조건은 Microsoft SUMIFS 문서에서 확인할 수 있습니다.

최종 상태는 일치, 원장에만 있음, 정산에만 있음, 키 중복, 금액 불일치, 금액 확인 필요로 나눕니다. 중복과 금액 불일치가 동시에 있으면 두 사유를 모두 남기거나, 중복을 해결한 뒤 금액 판정을 다시 수행합니다. 하나의 정상/오류 열로 너무 일찍 합치면 원인을 추적하기 어렵습니다.

매달 반복한다면 Power Query로 양방향 누락을 한 번에 확인

① 첫 번째 표의 거래번호 열을 선택합니다. ② 두 번째 표의 거래번호 열을 선택합니다. ③ Join kind에서 Full outer(전체 외부)를 선택합니다. ④ OK를 누른 뒤 확장·검증합니다. 복합 키는 양쪽에서 같은 순서로 선택해야 합니다.

두 표를 쿼리로 가져온 후, 각 쿼리에서 먼저 거래번호별 행 수와 금액 합계를 구합니다. 그룹화 전에 금액 형식 변환 오류와 빈 거래번호를 별도 쿼리로 분리합니다. 오류 행을 삭제한 결과만 보고서에 넣으면 그달 매출이 실제보다 작아질 수 있습니다.

① 홈 → 쿼리 병합 → 새 쿼리로 병합을 선택합니다. ② 위아래 표에서 동일한 비교키를 선택합니다. 복합키라면 같은 순서로 여러 열을 선택합니다. ③ 조인 종류를 전체 외부 조인으로 지정합니다. ④ 확장할 열에서 오른쪽 키·건수·합계를 함께 선택합니다.

전체 외부 조인은 양쪽에만 존재하는 행도 결과에 남깁니다. Microsoft의 전체 외부 조인 예제에서 병합 창과 확장 결과를 확인할 수 있습니다. 정산 자료에 실제로 여러 행이 있는 상태로 곧바로 병합하면 결과 행이 늘어날 수 있으므로, 이 글에서는 키별 집계를 먼저 합니다.

담당자에게 넘길 결과에는 원본 위치가 필요하다

불일치 표에는 거래번호와 차액뿐 아니라 원본 파일명, 시트명, 원본 행 번호, 판단 사유, 담당자 확인 내용을 남깁니다. “200원 차이”만 전달하면 상대방이 다시 모든 파일을 뒤져야 합니다. 원본 행 번호는 정렬하기 전에 붙여 둡니다.

마지막 검산은 세 가지입니다. 양쪽 원본의 행 수가 보존됐는지, 비교 대상에서 빠진 행의 사유가 모두 기록됐는지, 정상·예외·대상 제외 금액을 더했을 때 각 원본 합계로 돌아오는지 확인합니다. 대사 결과 파일과 원본 파일은 함께 보관해야 다음 달 정정 내역이 들어왔을 때 같은 기준으로 다시 비교할 수 있습니다.

자주 생기는 오해: 두 파일을 정렬한 뒤 같은 행끼리 빼면 되지 않을까요? 한쪽에만 있는 거래가 하나 끼어들면 뒤의 행이 전부 어긋납니다. 정렬은 보기 좋게 만드는 과정이고, 거래를 연결하는 기준은 비교키입니다.

관련 절차: 엑셀 파일 합치기: Power Query 폴더 연결·새로 고침 방법 · SQL JOIN 중복으로 SUM 합계가 늘어날 때: 재현과 해결