엑셀 중복 데이터 찾기와 제거 방법 | COUNTIF·UNIQUE 함수 실무 예제

COUNTIF·UNIQUE 함수 실무 예제

엑셀로 고객 명단이나 거래처 목록을 관리하다 보면 같은 이름이 여러 번 입력되는 일이 발생합니다.

특히 고객 수가 많아지면 중복된 데이터를 일일이 찾아내기 어렵습니다. 중복 고객에게 안내 문자를 여러 번 보내거나 실제 고객 수를 잘못 계산하는 문제도 생길 수 있습니다.

오늘은 COUNTIF와 UNIQUE 함수를 활용해 중복 데이터를 찾고 정리하는 방법을 실무 예제로 알아보겠습니다.

1. 고객 명단에 중복이 생기는 이유

고객 정보를 여러 직원이 입력하거나 다른 파일에서 복사해 가져오면 중복이 발생하기 쉽습니다.

예를 들어 다음과 같은 고객 명단이 있다고 가정하겠습니다.

행	A열 고객명	B열 구매금액
2	김민수	50,000
3	이영희	70,000
4	박철수	40,000
5	김민수	30,000
6	최지은	60,000
7	이영희	20,000
8	정수진	80,000

고객명은 총 7건 입력되어 있지만 실제로 서로 다른 이름은 5개입니다.

이처럼 입력된 행의 수와 고객 수가 반드시 일치하는 것은 아닙니다.

2. COUNTIF 함수로 중복 고객 찾기

COUNTIF 함수는 지정한 범위에서 특정 값이 몇 번 나타나는지 계산합니다.

C2 셀에 다음 수식을 입력합니다.

=COUNTIF($A$2:$A$8,A2)

수식을 C8까지 복사하면 각 고객명이 전체 명단에서 몇 번 나타나는지 확인할 수 있습니다.

고객명	중복 횟수
김민수	2
이영희	2
박철수	1
김민수	2
최지은	1
이영희	2
정수진	1

결과가 2 이상이면 해당 고객명은 중복된 것입니다.

여기서 $A$2:$A$8은 수식을 아래로 복사해도 검색 범위가 바뀌지 않도록 고정한 부분입니다.

3. 중복 여부를 문자로 표시하기

숫자보다 '중복'이라는 글자로 표시하면 데이터를 확인하기 더 쉽습니다.

D2 셀에 다음 수식을 입력합니다.

=IF(COUNTIF($A$2:$A$8,A2)>1,"중복","정상")

이 수식은 동일한 고객명이 두 번 이상 나타나면 '중복', 한 번만 나타나면 '정상'을 표시합니다.

중복 항목만 확인하려면 데이터 메뉴에서 필터를 적용하고 '중복'을 선택하면 됩니다.

4. UNIQUE 함수로 중복 없는 고객 명단 만들기

중복 고객을 찾았다면 이번에는 중복이 제거된 별도의 고객 명단을 만들어 보겠습니다.

E2 셀에 다음 수식을 입력합니다.

=UNIQUE(A2:A8)

결과는 다음과 같습니다.

중복 제거 고객명
김민수
이영희
박철수
최지은
정수진

UNIQUE 함수는 중복된 값을 제외하고 고유한 항목만 추출합니다.

원본 고객 명단은 그대로 유지하면서 새로운 명단을 만들 수 있다는 장점이 있습니다.

단, UNIQUE 함수는 Microsoft 365와 Excel 2021 이상 버전에서 사용할 수 있습니다.

5. 실제 고객 수 자동 계산하기

중복되지 않은 고객명이 몇 개인지 바로 계산하려면 COUNTA 함수와 UNIQUE 함수를 함께 사용합니다.

=COUNTA(UNIQUE(A2:A8))

계산 결과는 5입니다.

고객명 범위에 빈칸이 포함될 수 있다면 다음과 같이 작성하는 것이 더 정확합니다.

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)

이 수식은 빈칸을 제외하고 고유 고객명 개수를 계산합니다.

6. 중복인데도 서로 다르게 인식되는 이유

실무에서는 눈으로 보기에는 같은 고객명인데 중복으로 인식되지 않는 경우도 있습니다.

대표적인 원인은 불필요한 공백입니다.

예를 들어 '김민수'와 '김민수 '는 화면에서 비슷하게 보이지만 뒤쪽 공백 때문에 다른 데이터로 처리될 수 있습니다.

이럴 때는 TRIM 함수를 사용합니다.

=TRIM(A2)

TRIM 함수는 앞뒤 공백을 제거하고 단어 사이의 반복된 일반 공백을 정리합니다.

다만 웹페이지에서 복사한 특수 공백은 TRIM만으로 제거되지 않을 수 있습니다.

또한 같은 이름을 가진 서로 다른 고객도 있기 때문에 실제 고객 중복 여부는 전화번호나 고객번호 등 식별 정보로 확인하는 것이 안전합니다.

7. 엑셀 중복 데이터 관리 시 주의사항

COUNTIF와 UNIQUE를 사용할 때는 다음 사항을 확인하는 것이 좋습니다.

• 고객명을 직접 삭제하기 전에 원본 데이터를 백업합니다.
• 중복된 고객명이라도 구매 내역은 서로 다를 수 있습니다.
• UNIQUE로 추출한 결과를 원본에서 삭제했다고 생각하면 안 됩니다.
• 동명이인이 있을 수 있으므로 고객번호를 기준으로 확인합니다.

특히 매출 자료에서는 같은 고객의 구매 내역이 여러 건 존재하는 것이 정상일 수 있습니다. 중복 고객명과 잘못 입력된 중복 거래는 구분해야 합니다.

엑셀_중복고객_COUNTIF_UNIQUE_실습.xlsx

0.01MB

 

마무리

엑셀에서 중복 고객을 관리할 때는 무조건 중복 행을 삭제하기보다 먼저 중복이 발생한 원인을 확인하는 것이 중요합니다.

COUNTIF는 중복 여부 확인, UNIQUE는 중복 없는 명단 생성에 적합합니다.

두 함수를 함께 활용하면 고객 관리뿐 아니라 거래처 목록, 상품코드, 직원 명단 등 다양한 실무 데이터도 효율적으로 정리할 수 있습니다.

작은 함수 하나라도 실제 업무에 적용해 보면 반복 작업을 줄이는 데 큰 도움이 됩니다.