엑셀 중복된 고객 표시하고 중복 빼고 인원 세기

상담 신청 명단을 받다 보면 같은 분이 며칠 사이에 두세 번 신청한 줄이 섞여 있습니다. 그대로 연락하면 한 분께 같은 전화를 여러 번 드리게 되고, "이번 달 신청 인원"을 줄 수로 세면 실제보다 많게 나옵니다. 연락 전에 중복을 표시해 두고, 중복을 뺀 인원을 따로 세어 두면 이런 일이 줄어듭니다.

생활에서도 비슷합니다. 모임 참석 설문을 두 번 낸 사람, 주소록에 두 번 들어간 친구를 찾을 때 똑같이 씁니다. 이 글은 엑셀 "목적별 함수 조합" 시리즈의 한 편입니다. COUNTIF와 SUMPRODUCT는 컴활 공부에서도 자주 만나는 함수라, 두 함수를 겹쳐 쓰는 연습으로도 좋습니다.

예제 표 — 신청 명단 8줄

맨 위 줄은 열 문자, 맨 왼쪽 칸은 행 번호입니다. 이름과 번호는 모두 가상입니다. C열과 D열은 아래에서 수식으로 채웁니다.

3행과 7행을 보십시오. 둘 다 "이도윤"이지만 번호가 다릅니다. 이름만 보면 중복처럼 보여도 다른 사람일 수 있습니다. 그래서 이 글은 연락처(B열)를 기준으로 중복을 찾습니다.

완성 수식

① 중복된 줄 전부 표시 — C2에 넣고 C9까지 채웁니다(C2 오른쪽 아래 작은 네모를 끌거나 두 번 누릅니다).

=IF(COUNTIF($B$2:$B$9,B2)>1,"중복","")

② 첫 번째는 두고 두 번째부터만 표시 — D2에 넣고 D9까지 채웁니다. 연락할 명단을 고를 때는 이쪽이 편합니다.

=IF(COUNTIF($B$2:B2,B2)>1,"중복","")

③ 중복 빼고 몇 명인지 — 빈 칸 하나에 넣습니다.

=SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))

결과는 이렇습니다.

③의 결과는 5입니다. 8줄 중 D열에 "중복"이 붙은 3줄을 빼면 5줄이 남는 것과 같습니다. 모든 결과는 이 표를 엑셀 파일로 만들어 파이썬 formulas 라이브러리로 계산해 확인했습니다.

수식 풀어 보기 — 안쪽부터

① =IF(COUNTIF($B$2:$B$9,B2)>1,"중복","")

COUNTIF는 범위에서 조건에 맞는 칸이 몇 개인지 세는 함수입니다. 한 번만 나온 번호는 1이라 빈칸으로 남고, 두 번 이상 나온 번호는 모든 줄에 "중복"이 붙습니다. 범위 $B$2:$B$9에 $를 붙인 이유는 아래로 채워도 범위가 밀리지 않게 하기 위해서입니다.

② =IF(COUNTIF($B$2:B2,B2)>1,"중복","")

①과 다른 곳은 범위 하나뿐입니다. 시작점 $B$2는 고정하고 끝점 B2는 고정하지 않았습니다. 그래서 아래로 채우면 범위가 한 줄씩 길어집니다.

"지금 줄까지 위에서 몇 번째로 나왔나"를 세는 셈이라, 처음 나온 줄은 1이 되어 표시가 붙지 않습니다.

③ =SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))

1. 가장 안쪽 COUNTIF(B2:B9,B2:B9) — 조건 자리에 범위 전체를 넣으면 줄마다 "내 번호가 몇 번 나오나"를 한꺼번에 셉니다. 결과: 3, 1, 2, 3, 1, 1, 2, 3
2. 나누기 1/… — 각각 1을 그 수로 나눕니다. 결과: 1/3, 1, 1/2, 1/3, 1, 1, 1/2, 1/3
3. 바깥 SUMPRODUCT(…) — 모두 더합니다. 세 번 나온 번호는 1/3이 세 번 더해져 1, 두 번 나온 번호는 1/2이 두 번 더해져 1이 됩니다. 결국 번호마다 1씩 더해져 5가 됩니다.

SUMPRODUCT를 쓰는 이유는, 이 함수가 배열(여러 값의 묶음)을 받아서 계산해 주기 때문입니다. 이 덕분에 옛 버전 엑셀에서도 Ctrl + Shift + Enter 없이 그냥 Enter로 입력할 수 있습니다.

자주 막히는 곳

=SUMPRODUCT((B2:B100<>"")/COUNTIF(B2:B100,B2:B100&""))

• 이름으로 중복을 세면 틀릴 수 있습니다. 예제에서 이름(A열)으로 ③을 계산하면 4가 나옵니다. 번호가 다른 두 "이도윤"을 한 사람으로 센 결과입니다. 이름과 번호가 둘 다 같을 때만 중복으로 보려면 ①의 COUNTIF 자리를 COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2)로 바꿉니다.
• 번호 모양이 다르면 다른 값입니다. "010-0000-1001"과 "010 0000 1001", "01000001001"은 사람 눈에는 같지만 엑셀에는 다른 글자입니다. 중복을 찾기 전에 번호 형식부터 맞춰야 합니다.
• 앞뒤 공백도 다른 값으로 봅니다. Microsoft 문서도 COUNTIF로 텍스트를 셀 때 앞뒤 공백이나 따옴표 모양이 섞이지 않게 주의하라고 안내합니다. 영문 대소문자는 구분하지 않습니다.
• 결과가 3.9999…처럼 보일 때 — 1/3 같은 끝없는 소수를 더하다 보니 아주 미세한 차이가 생길 수 있습니다. 계산으로 확인해 보니 이름 기준 결과가 정확히 4가 아니라 3.9999999999999996으로 나왔습니다. 이 값을 다른 수식에서 비교에 쓸 거라면 =ROUND(SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)),0)처럼 반올림해 둡니다.

다른 방법

엑셀 2021·365 — UNIQUE 함수

UNIQUE는 Microsoft 문서 기준으로 Microsoft 365, Excel 2021, Excel 2024 등에서 쓸 수 있는 함수입니다. 엑셀 2019 이하에는 없으니 그때는 위의 ③을 씁니다.

=UNIQUE(B2:B9)
=COUNTA(UNIQUE(B2:B9))

첫 줄은 겹치지 않는 번호 5개를 아래 칸들로 쏟아 놓고(문서 표현으로 "분산"), 둘째 줄은 그 개수 5를 돌려줍니다. 결과가 흘러내릴 칸이 비어 있어야 합니다.

수식 없이 — 메뉴로 표시하거나 지우기

• 색으로 표시: 번호 열을 선택하고 [홈] 탭 → [스타일] 묶음 → [조건부 서식] → [셀 강조 규칙] → [중복 값]을 누릅니다. 중복된 칸에 색이 칠해집니다.
• 아예 지우기: [데이터] 탭 → [데이터 도구] 묶음 → [중복 제거]. Microsoft 문서에 따르면 첫 번째 항목은 남기고 나머지 같은 값을 삭제합니다. 영구 삭제라서, 문서도 먼저 조건부 서식 등으로 결과를 확인해 보라고 권합니다. 원본 시트를 복사해 두고 하는 편이 안전합니다.

수식 방식은 원본을 건드리지 않고, 명단이 바뀌면 표시도 따라 바뀐다는 점이 장점입니다.

자주 묻는 질문

함께 보면 좋은 글

• 엑셀 COUNTIF·COUNTIFS 조건 맞는 개수 세기 — COUNTIF 문법 자세히
• 엑셀 SUMPRODUCT 함수 활용 — 배열을 곱하고 더하는 원리
• 엑셀 조건부 서식 총정리 — 중복 값에 색 칠하기
• 엑셀 글 전체 목차 — 전화번호 형식 정리 등 목적별 함수 조합 시리즈 모아 보기

참고: COUNTIF 함수 (Microsoft 지원) · UNIQUE 함수 (Microsoft 지원) · 고유 값 필터링 또는 중복 값 제거 (Microsoft 지원)