엑셀 거래처 관리: 이름이 흔들리면 같은 거래처가 세 줄로 남습니다

피벗을 돌렸는데 한국상사가 세 줄로 나옵니다.

(주)한국상사 · 주식회사 한국상사 · 한국상사가 각각 다른 거래처로 잡혔습니다. 총합은 맞는데 거래처별 숫자가 전부 틀립니다. 상위 열 곳을 뽑으면 순위가 뒤집힙니다.

엑셀 거래처 관리를 이름 기준으로 두면 매달 같은 일을 반복하게 됩니다.

이름을 고치지 말고 코드를 붙입니다

원본에 있는 이름을 통일하려고 하면 끝이 안 납니다. 다음 달 파일에 또 새로운 표기가 들어오고, 이미 고친 과거 파일과 안 고친 파일이 섞입니다.

방향을 바꿔서 거래처마다 코드를 하나씩 붙입니다. 이름은 그대로 두고 코드로 집계합니다.

거래처마스터 시트에 네 열만 둡니다.

코드    정식명칭          사업자번호      담당
C001   주식회사 한국상사   123-45-67890   김
C002   대한유통(주)        234-56-78901   박

정식명칭은 세금계산서에 찍히는 이름으로 씁니다. 사업자번호가 있으면 나중에 회계 쪽 자료와 붙일 때 편합니다.

별칭표가 원본 표기를 코드로 옮깁니다

마스터만으로는 원본이 안 붙습니다. 원본에 나오는 모든 표기를 코드에 연결한 표가 하나 더 필요합니다.

별칭표 시트에 두 열을 둡니다.

(주)한국상사        C001
주식회사 한국상사    C001
한국상사            C001
한국상사 본점       C001
대한유통            C002

한 코드에 표기가 몇 개든 상관없습니다. 줄을 계속 늘리면 됩니다. 실무자가 새 표기를 발견하면 한 줄 추가하고 끝냅니다.

원본에 코드를 붙이는 수식은 한 줄입니다.

=IFERROR(VLOOKUP(TRIM(C2),별칭표!A:B,2,FALSE),"미매칭")

TRIM을 씌우는 이유는 앞뒤 공백 때문에 안 붙는 경우가 흔해서입니다. 값이 같아 보이는데 안 잡히는 다른 원인들은 공백과 형식 정제에 진단 수식으로 정리해 뒀습니다.

미매칭 줄만 매달 봅니다

엑셀 거래처 관리에서 사람이 계속 해야 하는 일은 이것 하나로 줄어듭니다.

=COUNTIF(코드범위,"미매칭")

이번 달에 처음 나타난 표기가 미매칭으로 남습니다. 그 줄만 필터로 뽑아 별칭표에 추가하면 다음 달부터 자동으로 붙습니다.

첫 달에는 미매칭이 수십 건 나옵니다. 두 달째에 열 건 안쪽으로 떨어지고, 석 달째부터는 새 거래처가 생겼을 때만 나옵니다. 이 숫자가 줄어드는 것이 눈에 보여서 계속 하게 됩니다.

엑셀 거래처 정리방법에서 자주 막히는 세 경우

① 같은 이름의 다른 회사. 사업자번호가 다르면 다른 코드를 줍니다. 이름만으로 붙이면 두 회사 매출이 한 줄로 합쳐집니다

② 본점과 지점. 지점별로 관리해야 하면 코드를 나누고, 합쳐 봐야 하면 상위 코드 열을 하나 더 둡니다. 나중에 나누는 것보다 처음에 나눠 두고 합치는 편이 쉽습니다

③ 개인 고객. 이름이 같은 사람이 여럿입니다. 연락처 끝 네 글자를 붙여 코드를 만들거나 채널 회원번호를 그대로 코드로 씁니다

엑셀 거래처 관리를 코드로 하면 집계가 한 줄로 끝납니다

여기까지 오면 엑셀 거래처별 매출 합계는 조건 하나짜리 수식입니다.

=SUMIF(코드범위,$A2,금액범위)

이름 대신 코드로 걸기 때문에 표기가 몇 가지든 한 줄로 모입니다. 보고서에 찍을 이름은 마스터에서 정식명칭을 가져옵니다.

=VLOOKUP($A2,거래처마스터!A:B,2,FALSE)

집계가 맞는지 확인하는 방법과 부분합이 총합과 안 맞는 원인은 거래처별 매출 합계에 따로 적었습니다.

마스터를 만들어 두면 다른 표에도 씁니다

거래처 코드는 매출에만 쓰이지 않습니다. 단가표에서 거래처별 단가를 걸 때, 견적서에서 거래처를 고를 때, 정산에서 입금을 대사할 때 같은 코드를 씁니다.

엑셀 자동화를 붙이기 전에 이 연결부터 만듭니다. 표를 만들 때마다 이름을 다시 치는 방식은 표가 늘어날수록 안 맞기 시작합니다. 코드를 한 번 만들어 두면 표들이 서로 붙습니다. 엑셀 자동화라고 부르는 것들이 대부분 이 연결 위에서 돌아갑니다.

전체 순서는 엑셀 매출 관리 일곱 단계에 있습니다. 이 글은 세 번째 단계입니다.

자주 받는 질문

Q. 거래처가 200곳인데 코드를 다 만들어야 하나요

A. 한 번에 다 만들지 않습니다. 최근 1년 매출 상위부터 붙이면 대개 상위 20%가 금액의 대부분을 덮습니다. 나머지는 미매칭으로 두고 나올 때마다 추가합니다.

Q. 별칭표가 몇백 줄이 되면 느려지지 않나요

A. VLOOKUP 한 열이면 수천 줄까지 체감이 없습니다. 느려지면 별칭표를 표(Ctrl+T)로 만들고 참조 범위를 열 전체에서 표 이름으로 바꿉니다.

Q. 거래처명을 사업자번호로 붙이면 안 되나요

A. 주문 데이터에 사업자번호가 있으면 그게 가장 정확합니다. 다만 온라인 채널 주문에는 거의 없습니다. 있는 자료에는 사업자번호로 붙이고, 없는 자료에만 별칭표를 쓰는 방식이 현실적입니다.

Q. 이미 몇 년치 데이터가 이름으로 쌓여 있습니다

A. 과거 데이터를 고치지 않고 코드 열만 새로 붙입니다. 별칭표가 완성되면 과거분도 같은 수식으로 한 번에 코드가 붙습니다. 원본을 고치는 방식은 되돌릴 수 없어서 권하지 않습니다. 엑셀 거래처 정리방법을 검색하면 이름을 바꾸는 방법이 먼저 나오는데, 그 길로 가면 매달 다시 바꾸게 됩니다.

미매칭 건수가 열 건 아래로 내려온 달부터 거래처별 숫자를 믿습니다. 그 숫자부터 세어 두려 합니다.