두 파일 차이 찾기, 눈검토 대신 ‘변경·신규·삭제’로 뽑는 법

월말마다 단가표, 거래처 목록, 품목 마스터처럼 ‘지난 파일’과 ‘이번 파일’을 비교해야 하는 일이 있습니다. 행 수가 200줄 정도면 필터 걸고 눈으로 봐도 어떻게든 버티지만, 2천 줄을 넘기면 이야기가 달라집니다. 특히 품목코드는 그대로인데 단가만 바뀌었거나, 거래처명이 살짝 수정됐거나, 아예 이번 달에 빠진 항목이 있으면 수작업 검토는 거의 사고 예약에 가깝습니다.

오늘은 예전 방식처럼 두 파일을 나란히 열어두고 색칠하는 대신, Power Query를 이용해 변경 / 신규 / 삭제 / 동일 상태를 자동으로 붙이는 방식으로 정리해 보겠습니다. 함수로도 만들 수 있지만, 매달 원본 파일을 바꿔 끼우는 업무라면 쿼리 방식이 훨씬 안정적입니다.

Before: 눈으로 비교하면 어디서부터 틀어질까

기존 방식은 보통 이렇습니다. 지난달 파일을 왼쪽에 두고, 이번 달 파일을 오른쪽에 둡니다. 품목코드 기준으로 정렬한 뒤 단가 열을 서로 비교합니다. 다른 셀은 노란색, 새로 들어온 품목은 초록색, 빠진 품목은 빨간색으로 칠합니다.

문제는 정렬이 한 번만 어긋나도 비교 기준이 무너진다는 점입니다. 중간에 신규 품목이 끼어 있으면 그 아래 행들이 전부 한 칸씩 밀려 보입니다. 코드 앞자리 0이 사라진 경우, 공백이 섞인 경우, 텍스트와 숫자가 섞인 경우에는 같은 코드인데도 다른 값처럼 취급됩니다.

After: 비교표는 ‘행 위치’가 아니라 ‘키’로 붙인다

핵심은 두 파일을 직접 옆에 붙여서 보는 것이 아니라, 먼저 비교 기준이 되는 고유키를 정하는 것입니다. 품목 마스터라면 품목코드, 거래처 목록이라면 거래처코드, 직원 명부라면 사번이 좋습니다. 이름이나 품명처럼 사람이 수정할 수 있는 값은 키로 쓰지 않는 편이 안전합니다.

예를 들어 지난달 단가표와 이번 달 단가표가 아래 구조라고 해보겠습니다.

이때 최종 결과는 단순히 값이 다른 셀을 찾는 표가 아니라, 업무 담당자가 바로 판단할 수 있는 비교표여야 합니다.

쿼리에서 먼저 정리해 둘 것

Power Query에서 비교를 시작하기 전에 지난 파일과 이번 파일을 각각 가져옵니다. 엑셀 표로 만들어 둔 뒤 데이터 > 테이블/범위에서로 불러오면 관리가 편합니다. 표 이름은 예를 들어 tbl_Old, tbl_New처럼 명확히 붙여 두면 나중에 헷갈리지 않습니다.

가져온 뒤에는 바로 병합하지 말고, 키 열부터 정리합니다. 실무에서 비교가 안 맞는 원인은 대부분 병합 단계가 아니라 그 전처리에서 나옵니다.

특히 중복 키는 반드시 먼저 봐야 합니다. 품목코드 하나가 지난 파일에 2번, 이번 파일에 3번 들어 있으면 병합 결과가 6행으로 늘어납니다. 이 상태에서 변경 건수가 갑자기 많아졌다고 판단하면 실제보다 훨씬 큰 문제처럼 보일 수 있습니다.

병합은 ‘전체 외부 조인’으로 한 번에 본다

신규와 삭제를 동시에 보려면 두 표 중 한쪽에만 있는 행도 결과에 남겨야 합니다. 그래서 Power Query의 쿼리 병합에서 조인 종류를 전체 외부로 선택합니다. 왼쪽은 지난 파일, 오른쪽은 이번 파일로 두고, 품목코드 열을 각각 선택합니다.

병합 후에는 이번 파일 쪽 열을 확장합니다. 이때 열 이름이 겹치므로 지난 파일 열에는 Old_, 이번 파일 열에는 New_ 같은 접두어를 붙여 두면 비교식 작성이 쉬워집니다.

Old_품목코드 | Old_품목명 | Old_단가 | New_품목코드 | New_품목명 | New_단가

그 다음 사용자 지정 열을 추가해 판정 값을 만듭니다. 개념은 간단합니다. 지난 키가 비어 있고 이번 키가 있으면 신규, 지난 키가 있고 이번 키가 비어 있으면 삭제입니다. 둘 다 있는데 비교 대상 열이 다르면 변경, 모두 같으면 동일입니다.

if [Old_품목코드] = null then "신규"
else if [New_품목코드] = null then "삭제"
else if [Old_단가] <> [New_단가] or [Old_사용여부] <> [New_사용여부] then "변경"
else "동일"

위 식에서 비교 열은 업무에 맞게 조정하면 됩니다. 단가만 중요하면 단가만 비교하고, 공급처나 사용여부까지 봐야 한다면 조건에 추가합니다. 품목명은 띄어쓰기나 표기 수정이 잦으므로, 변경 판정에 포함할지 여부를 먼저 합의해 두는 것이 좋습니다.

흔한 실수: null과 빈칸은 다르게 보일 수 있다

Power Query에서는 값이 없는 상태를 null로 다룹니다. 그런데 원본 엑셀에서 빈칸처럼 보이는 값이 실제로는 빈 문자열이거나 공백 한 칸인 경우가 있습니다. 이런 상태에서는 삭제나 신규 판정이 예상과 다르게 나올 수 있습니다.

그래서 텍스트 열은 병합 전에 공백 제거를 적용하고, 필요하면 빈 문자열을 null로 바꾸는 작업을 넣어 주는 편이 좋습니다. 단가 열도 마찬가지입니다. 빈칸을 0으로 바꿀지, null로 둘지는 업무 의미에 따라 달라집니다. 단가가 0인 상품과 단가가 미등록된 상품은 전혀 다른 의미일 수 있으니 무조건 0 처리하는 습관은 피해야 합니다.

변경 사유까지 보여주면 검토 시간이 줄어든다

판정이 ‘변경’이라고만 나오면 담당자는 다시 어떤 열이 바뀌었는지 확인해야 합니다. 한 단계 더 들어가서 변경 사유 열을 만들면 검토가 훨씬 빨라집니다.

if [Old_품목코드] = null then "이번 파일에만 있음"
else if [New_품목코드] = null then "지난 파일에만 있음"
else Text.Combine(
    List.RemoveNulls({
        if [Old_단가] <> [New_단가] then "단가" else null,
        if [Old_공급처] <> [New_공급처] then "공급처" else null,
        if [Old_사용여부] <> [New_사용여부] then "사용여부" else null
    }), ", "
)

이렇게 만들면 결과 표에서 변경 사유가 단가, 공급처, 사용여부처럼 표시됩니다. 단가 변경만 따로 필터링해서 구매팀에 전달하고, 사용여부 변경은 운영팀에서 확인하는 식으로 후속 작업을 나누기 좋습니다.

결과표에서 꼭 확인할 순서

비교표가 만들어졌다고 바로 배포하기보다는 몇 가지를 짧게 확인하는 습관이 필요합니다. 먼저 전체 행 수를 봅니다. 지난 파일 건수와 이번 파일 건수를 알고 있으면, 신규·삭제·동일·변경 건수의 합이 자연스러운지 감이 옵니다.

다음으로 신규와 삭제 건수를 봅니다. 평소에는 신규가 10건 안팎인데 갑자기 800건이 나오면 대부분 키 형식 문제입니다. 품목코드가 숫자로 바뀌었거나, 앞자리 0이 사라졌거나, 공백이 섞였을 가능성이 큽니다.

마지막으로 변경 건수 중 금액 차이가 큰 항목을 먼저 봅니다. 단가가 100원 오른 것과 10,000원 오른 것은 검토 우선순위가 다릅니다. 쿼리에서 차액 열을 추가해 두면 변경 건이 많을 때도 중요한 것부터 볼 수 있습니다.

[New_단가] - [Old_단가]

보고서로 넘길 때는 원본 열을 다 보여주지 않아도 된다

작업용 쿼리에는 모든 열이 있어도 괜찮지만, 보고용 결과표에는 필요한 열만 남기는 편이 좋습니다. 품목코드, 품목명, 지난 단가, 이번 단가, 차액, 판정, 변경 사유 정도면 대부분의 단가 검토 회의에서 충분합니다.

엑셀 시트로 로드한 뒤에는 표 필터로 판정만 골라 볼 수 있습니다. 조건부 서식을 얹어 신규는 연한 초록, 삭제는 연한 빨강, 변경은 연한 노랑으로 표시하면 검토자가 훨씬 빨리 읽습니다. 다만 색상만 믿고 판단하지 않도록 반드시 판정 열의 텍스트를 함께 남겨 두세요.

응용하면 이런 비교에도 그대로 쓴다

이 방식은 단가표에만 쓰는 요령이 아닙니다. 거래처 마스터 변경 이력, 부서별 인원 변동, 상품 노출 상태 변경, 월별 수수료율 변경, 시스템 코드표 검수에도 거의 그대로 적용됩니다. 키만 제대로 잡히면 비교 대상 열은 얼마든지 바꿀 수 있습니다.

키가 하나로 부족한 경우에는 두 열을 합쳐 복합키를 만들면 됩니다. 예를 들어 채널코드와 상품군을 함께 봐야 한다면 채널코드 & "|" & 상품군 형태의 비교키를 만들어 병합 기준으로 사용합니다. 구분자 없이 그냥 붙이면 AB+12와 A+B12처럼 우연히 같은 키가 될 수 있으니, 중간에 잘 쓰지 않는 구분자를 넣는 것이 안전합니다.

한 번 만들어 두면 다음 달은 새로 고침만

이 방법의 가장 큰 장점은 반복 작업을 줄여준다는 점입니다. 지난 파일과 이번 파일의 경로 또는 표만 교체한 뒤 모두 새로 고침을 누르면 같은 규칙으로 비교표가 다시 만들어집니다. 매달 색칠하던 시간을 줄이는 것도 좋지만, 더 중요한 건 누락과 착시를 줄인다는 점입니다.

두 표 비교 업무는 겉으로는 단순해 보여도 실수했을 때 영향이 큽니다. 단가 하나를 놓치면 정산이 틀어지고, 삭제된 품목을 못 보면 발주 사고로 이어질 수 있습니다. 다음번에 비슷한 파일 두 개를 받았다면 나란히 열어놓고 눈으로 찾기 전에, 먼저 키를 정하고 비교표 구조부터 만들어 보세요. 결과가 ‘예쁘게’ 나오는 것보다, 누가 봐도 같은 기준으로 다시 검증할 수 있는 표가 실무에서는 훨씬 강합니다.