거래처명이 제각각일 때, Power Query ‘퍼지 병합’으로 매칭표 만드는 법

거래처 정산표를 합치다 보면 가장 답답한 순간이 있습니다. 같은 업체인데 원본마다 이름이 조금씩 다르게 들어오는 경우입니다. 예를 들어 주식회사 한빛유통, (주)한빛유통, 한빛 유통이 모두 같은 거래처인데, 일반 병합이나 VLOOKUP으로는 서로 다른 값으로 처리됩니다. 이럴 때 손으로 하나씩 고치기 시작하면 다음 달에도 똑같은 일을 반복하게 됩니다.

이번 글은 실무자가 자주 묻는 질문 형태로 정리했습니다. 핵심은 Power Query의 퍼지 병합을 이용해 비슷한 거래처명을 후보로 찾고, 사람이 확인할 부분만 줄이는 방식입니다. 완전 자동으로 믿고 끝내는 도구라기보다, 매칭 후보를 빠르게 좁혀주는 실무 정리법에 가깝습니다.

Q. 이름이 조금 다른 거래처도 자동으로 붙일 수 있나요?

가능합니다. 다만 “무조건 정확하게 붙는다”가 아니라, 비슷한 이름끼리 후보를 만들어 준다고 이해하는 편이 안전합니다.

예를 들어 원본 매출자료에는 아래처럼 들어와 있고, 기준 거래처표에는 표준명이 따로 있다고 가정해 보겠습니다.

이런 데이터는 일반 병합으로는 대부분 실패합니다. Power Query의 퍼지 병합은 띄어쓰기, 일부 단어 차이, 약어 차이를 어느 정도 감안해 유사한 행을 찾아줍니다.

Q. 먼저 준비해야 할 표는 어떻게 만들면 되나요?

최소한 표는 두 개가 필요합니다.

첫 번째는 정리할 원본표입니다. 매출자료, 주문자료, 입금자료처럼 거래처명이 제각각 들어온 표입니다. 여기에는 거래처명 외에도 매출일, 금액, 담당자, 품목 등이 있을 수 있습니다.

두 번째는 기준 거래처표입니다. 회사에서 공식적으로 쓰는 표준 거래처명, 거래처코드, 사업자번호 등을 담아둔 표입니다. 퍼지 병합의 목적은 결국 원본표에 기준표의 거래처코드나 표준명을 붙이는 것입니다.

여기서 중요한 점은 기준표가 지저분하면 결과도 지저분해진다는 것입니다. 기준표 안에 같은 거래처가 두 번 들어 있거나, 폐업 거래처가 섞여 있거나, 표준명이 흔들리면 퍼지 병합 결과도 헷갈리게 나옵니다.

Q. Power Query에서는 어디에서 설정하나요?

두 표를 모두 Excel 표로 만든 뒤, 각각 Power Query로 불러옵니다. 표 안 아무 셀이나 클릭한 다음 데이터 > 테이블/범위에서를 누르면 됩니다.

쿼리 편집기에서 원본 매출자료 쿼리를 선택하고, 홈 > 쿼리 병합을 누릅니다. 아래쪽에 기준 거래처표를 선택한 뒤, 원본의 거래처명 열과 기준표의 표준거래처명 열을 각각 클릭합니다.

여기서 일반 병합으로 끝내지 말고, 병합 창 아래쪽의 퍼지 일치를 사용하여 병합 수행 옵션을 켭니다. 그러면 유사도 임계값, 대소문자 무시, 공백 무시, 최대 일치 항목 수 같은 설정을 조정할 수 있습니다.

Q. 유사도 임계값은 몇으로 두는 게 좋나요?

처음부터 너무 낮게 잡으면 엉뚱한 거래처가 붙습니다. 반대로 너무 높게 잡으면 거의 매칭이 안 됩니다. 실무에서는 보통 0.8 전후에서 시작해 결과를 보고 조정하는 편이 무난합니다.

정산표에 바로 반영할 데이터라면 0.8이나 0.85 정도로 시작하는 것을 권합니다. 누락이 조금 생기더라도, 틀린 거래처가 붙는 것보다는 안전합니다.

Q. ‘최대 일치 항목 수’는 1로 두면 되나요?

처음 검토할 때는 꼭 1로만 두지 않는 것이 좋습니다. 최대 일치 항목 수를 2 또는 3으로 두면, 비슷한 후보가 여러 개 나옵니다. 이게 귀찮아 보이지만 실제로는 오매칭을 잡는 데 도움이 됩니다.

예를 들어 청운상사 강남점이라는 원본명이 있을 때 기준표에 청운상사, 청운상사 강남지점, 청운상사 본점이 모두 있다면 하나만 자동 선택되는 것보다 후보를 보고 확인하는 편이 안전합니다.

최종 자동 반영용 쿼리는 최대 일치 항목 수 1로 운영하더라도, 처음 기준을 잡을 때는 후보를 여러 개 펼쳐서 확인해 보세요. 한 번 기준을 잡아두면 다음 달부터는 같은 규칙으로 새 자료를 훨씬 빨리 정리할 수 있습니다.

Q. 회사명 앞뒤의 ‘주식회사’, ‘(주)’는 미리 지워야 하나요?

가능하면 정리용 열을 하나 만들어 두는 편이 좋습니다. 퍼지 병합이 어느 정도 차이를 감안하긴 하지만, 불필요한 단어를 줄여두면 결과가 더 안정적입니다.

Power Query에서 사용자 지정 열을 만들어 거래처명을 정규화할 수 있습니다. 예를 들어 괄호, 공백, ‘주식회사’, ‘(주)’ 같은 표현을 제거하는 식입니다.

Text.Replace(Text.Replace(Text.Replace([거래처명], "주식회사", ""), "(주)", ""), " ", "")

기준 거래처표에도 같은 방식의 정리용 열을 만들어 두고, 실제 병합은 원본 거래처명이 아니라 이 정리용 열끼리 수행합니다. 표시할 때는 표준거래처명을 가져오면 됩니다.

다만 모든 단어를 무작정 지우면 안 됩니다. 예를 들어 서울물류와 서울물류센터가 별도 거래처라면 ‘센터’를 제거하는 순간 둘이 섞일 수 있습니다. 정리 규칙은 회사 데이터의 특성을 보고 정해야 합니다.

Q. 매칭 결과가 맞는지 빠르게 확인하는 방법은요?

병합 후에는 기준표의 표준거래처명, 거래처코드뿐 아니라 가능하면 유사도 점수도 같이 확인하는 흐름을 추천합니다. Power Query 화면에서 퍼지 병합 결과를 확장할 때 관련 점수 열이 제공되는 경우가 있습니다. 환경에 따라 표시 방식이 다를 수 있으니 병합 결과 확장 열을 확인해 보세요.

엑셀 시트로 불러온 뒤에는 아래 기준으로 검토 열을 하나 추가해도 좋습니다.

특히 지점명, 물류센터명, 브랜드명과 법인명이 섞여 있는 데이터는 거래처명만으로 확정하기 어렵습니다. 이럴 때는 사업자번호나 전화번호 같은 보조 키를 기준표에 함께 관리하는 것이 좋습니다.

Q. 흔한 실수는 어떤 게 있나요?

가장 흔한 실수는 퍼지 병합 결과를 그대로 믿고 정산을 끝내는 것입니다. 퍼지 병합은 후보를 잘 찾아주지만, 회사 내부 기준까지 이해해 주지는 않습니다. 예를 들어 ‘동아상사’와 ‘동아상사물류’가 실제로 다른 거래처라면 이름이 비슷하다는 이유로 잘못 붙을 수 있습니다.

두 번째는 기준표를 업데이트하지 않는 것입니다. 원본에서 매칭 안 된 거래처가 나왔는데, 그때그때 수동으로만 고치면 다음 달에도 또 비어 나옵니다. 매칭 안 된 항목은 기준 거래처표에 신규 등록하거나, 별도의 예외 매칭표에 반영해야 반복 작업이 줄어듭니다.

세 번째는 원본 데이터를 직접 덮어쓰는 것입니다. Power Query를 쓸 때는 원본 거래처명은 그대로 두고, 옆에 표준거래처명과 거래처코드를 붙이는 구조가 안전합니다. 나중에 문제가 생겼을 때 원본값과 매칭값을 비교할 수 있어야 합니다.

Q. 예외 매칭표를 따로 두면 뭐가 좋나요?

예외 매칭표는 “이 이름이 들어오면 이 거래처로 본다”는 사전입니다. 퍼지 병합 전에 예외표로 먼저 정확히 붙이고, 남은 것만 퍼지 병합에 넘기면 결과가 훨씬 안정됩니다.

반복되는 예외가 20개만 쌓여도 다음 달 정리 시간이 눈에 띄게 줄어듭니다. 예외표는 숨겨두지 말고 담당자가 확인할 수 있는 별도 시트로 관리하는 편이 좋습니다.

Q. 함수로 처리하는 것과 무엇이 다른가요?

정확히 같은 키가 있다면 함수가 더 간단합니다. 거래처코드가 이미 원본에 있거나, 거래처명이 기준표와 완전히 일치한다면 XLOOKUP이나 INDEX/MATCH로 충분합니다.

하지만 거래처명이 매번 조금씩 달라진다면 함수만으로는 조건이 복잡해집니다. SUBSTITUTE로 단어를 지우고, SEARCH로 포함 여부를 보고, 여러 보조열을 만들다 보면 파일이 무거워지고 유지보수도 어려워집니다. 이런 경우에는 Power Query에서 정리 규칙과 병합 과정을 저장해 두는 방식이 더 편합니다.

물론 한 번 정리된 결과를 보고서에서 조회할 때는 함수가 유용합니다. 즉, 데이터 정리는 Power Query, 보고서 조회와 계산은 함수로 역할을 나누면 깔끔합니다.

이럴 때 이렇게 쓰면 된다

거래처명이 기준표와 완전히 같다면 일반 병합이나 조회 함수로 처리하면 됩니다. 띄어쓰기, 괄호, ‘주식회사’ 정도만 다른 수준이라면 정리용 열을 만든 뒤 병합하면 됩니다. 표기가 제각각이고 축약명이 자주 나온다면 예외 매칭표를 먼저 적용하고, 남은 항목에 퍼지 병합을 사용하세요.

정산 금액이 크거나 거래처 구분이 민감한 자료라면 임계값을 높게 두고, 매칭 안 된 항목을 사람이 확인하는 방식이 안전합니다. 반대로 후보 목록을 넓게 뽑아 기준표를 정비하는 목적이라면 임계값을 조금 낮춰도 됩니다. 핵심은 퍼지 병합을 최종 판단자가 아니라 검토 대상을 줄여주는 도구로 쓰는 것입니다. 이 기준만 잡아두면 매달 들어오는 제각각 거래처명도 훨씬 덜 흔들리게 정리할 수 있습니다.