엑셀 명단 비교 방법: 두 명단을 붙이면 신규와 이탈이 같이 나옵니다

지난달 명단과 이번 달 명단을 나란히 놓고 눈으로 봅니다.

300줄쯤 되면 끝까지 못 봅니다. 눈이 먼저 지칩니다. 중간에 순서가 어긋나면서 어디까지 봤는지 잃어버립니다. 다시 처음부터 봅니다.

두 명단을 비교하는 일은 눈이 아니라 수식이 할 일입니다. 그리고 비교만으로 끝나지 않고 세 숫자가 같이 나옵니다.

비교하기 전에 표기를 맞춥니다

엑셀 명단 비교 방법은 여기서 갈립니다. 이 단계를 건너뛰면 아래 수식이 전부 헛돕니다.

홍길동과 홍길동을 엑셀은 다른 값으로 봅니다. 전화번호가 한쪽은 010-1234-5678, 다른 쪽은 01012345678이면 안 잡힙니다.

비교할 열을 정제한 새 열로 하나 만들어 둡니다.

=TRIM(SUBSTITUTE(A2,"-",""))

이름으로 비교하면 동명이인이 섞여 들어옵니다. 이름은 식별자가 될 수 없습니다. 가능하면 회원번호나 사업자번호나 이메일로 비교합니다. 값이 같아 보이는데 안 잡히는 다른 원인은 공백과 형식 정제에 정리해 뒀습니다.

엑셀 명단 비교 방법은 COUNTIF 두 줄이면 됩니다

A명단 옆에 한 열, B명단 옆에 한 열을 붙입니다.

A명단 옆:  =IF(COUNTIF(B명단범위,$A2)>0,"양쪽","A만")
B명단 옆:  =IF(COUNTIF(A명단범위,$A2)>0,"양쪽","B만")

한쪽만 걸면 절반만 보입니다. 나머지 절반은 그대로 지나갑니다. A에는 있는데 B에 없는 것을 첫 줄이 잡고, B에만 있는 것을 두 번째 줄이 잡습니다. 양쪽을 다 걸어야 두 목록의 차이가 전부 드러납니다.

명단 비교를 한 번만 걸고 끝내는 경우가 많았습니다. 그러면 새로 들어온 쪽을 통째로 놓칩니다.

값까지 가져와야 하면 XLOOKUP을 씁니다

있는지 없는지에 더해 지난달 금액까지 옆에 붙이려면 조회 함수로 갑니다.

=XLOOKUP($A2,지난달!$A:$A,지난달!$D:$D,"없음")

365가 아니면 VLOOKUP에 IFERROR를 씌웁니다.

=IFERROR(VLOOKUP($A2,지난달!$A:$D,4,FALSE),"없음")

이렇게 두면 같은 고객의 이번 달 금액과 지난달 금액이 한 줄에 놓입니다. 증감까지 옆에 붙이면 어느 고객이 줄었는지 바로 보입니다.

신규·유지·이탈 세 건수를 셉니다

엑셀 명단 비교에서 실제로 쓰이는 결과가 이 세 숫자로 나옵니다.

신규 = COUNTIF(이번달구분범위,"B만")
유지 = COUNTIF(이번달구분범위,"양쪽")
이탈 = COUNTIF(지난달구분범위,"A만")

세 숫자를 매달 한 줄로 쌓아 두면 몇 달 만에 흐름이 보입니다. 신규가 늘어도 이탈이 더 크면 명단은 줄어듭니다. 전체 인원 숫자에는 이 움직임이 안 보입니다. 늘었는지 줄었는지만 남고 누가 빠졌는지는 없습니다.

이탈로 잡힌 목록을 따로 뽑아 두는 것이 실제 행동으로 이어집니다. 이번 달에 안 온 고객 명단이 그대로 연락할 대상이 됩니다.

조건부 서식은 확인용으로만 씁니다

두 범위를 선택하고 홈 · 조건부 서식 · 셀 강조 규칙 · 중복 값을 고르면 겹치는 값에 색이 칠해집니다.

빠르고 편한데 거기서 막힙니다. 색은 셀 수가 없습니다. 필터로 뽑아 다음 작업으로 넘기기도 번거롭습니다. 색으로 보고 수식으로 셉니다.

엑셀 명단 중복 제거를 먼저 걸어야 합니다

엑셀 명단 중복 제거를 건너뛰면 건수가 어긋납니다. 한쪽 명단 안에 같은 사람이 두 번 있으면 건수가 어긋납니다. 유지 100명으로 나왔는데 실제로는 95명인 상태가 됩니다.

데이터 · 중복된 항목 제거로 정제한 열 기준으로 먼저 정리합니다. 원본을 지우는 것이 걱정되면 원본 시트를 복사해 두고 사본에서 제거합니다.

중복이 몇 건이었는지도 세어 둡니다.

=COUNTA(범위)-SUMPRODUCT(1/COUNTIF(범위,범위))

중복 건수가 매달 비슷하게 나오면 입력 규칙을 손봐야 합니다. 더 자세한 순서는 중복값 제거에 적어 뒀습니다.

매달 같은 파일에 쌓으면 비교가 저절로 됩니다

명단을 매달 새 파일로 받으면 비교를 매달 새로 만듭니다.

기준월 열을 하나 두고 한 시트에 계속 쌓으면 어느 달 사이든 비교가 됩니다. 파일이 커지는 것보다 비교를 다시 만드는 시간이 더 아깝게 듭니다. 엑셀 자동화를 붙인다면 파일을 폴더에서 읽어 기준월을 붙여 쌓는 부분입니다.

여기서 나온 이탈 목록은 엑셀 매출 관리의 거래처별 매출과 붙여 보면 금액까지 나옵니다.

자주 받는 질문

Q. 이름만 있고 다른 식별자가 없습니다

A. 이름과 다른 열을 붙여 임시 키를 만듭니다. 이름과 연락처 끝 네 글자를 이어 붙이는 식입니다. 동명이인이 붙어 있으면 어떤 수식으로도 구분하지 못합니다.

Q. 명단이 만 줄이 넘어 COUNTIF가 느립니다

A. 범위를 열 전체에서 실제 데이터 범위로 줄이고 표 이름으로 참조합니다. 그래도 느리면 두 명단을 파워 쿼리에서 병합으로 붙입니다. 병합은 왼쪽 우선 조인을 지원해서 신규와 이탈을 한 번에 뽑아 줍니다.

Q. 세 개 이상의 명단을 비교하려면 어떻게 하나요

A. 명단을 한 시트에 세로로 쌓고 출처 열을 붙인 뒤 피벗으로 봅니다. 행에 식별자, 열에 출처를 놓으면 어느 명단에 있고 없는지가 표로 나옵니다.

Q. 개인정보가 든 명단을 밖에 넘겨야 합니다

A. 비교에 쓰는 열만 남기고 나머지를 지웁니다. 이름과 연락처가 필요 없는 비교면 식별자만 넘깁니다. 가려서 넘기는 방법은 마스킹에 정리해 뒀습니다.

엑셀 명단 비교 방법으로 얻는 것은 신규와 유지와 이탈 세 숫자입니다. 매달 한 줄로 쌓습니다. 그 세 숫자부터 남겨 두려 합니다.