엑셀 중복값 찾기 회사 자료에서 중복 데이터 쉽게 확인하는 방법

회사에서 엑셀로 자료를 정리하다 보면 생각보다 자주 발견되는 것이 중복 데이터입니다.

직원 명단을 정리하거나 거래처 자료를 확인할 때, 여러 사람이 작성한 파일을 하나로 합칠 때도 같은 데이터가 두 번 들어가는 경우가 있습니다. 예를 들어 직원번호가 같은 자료가 두 번 입력되거나, 거래처 목록에 동일한 거래처가 반복해서 들어가는 경우입니다.

이런 자료를 그대로 사용하면 인원 수나 거래처 수를 잘못 계산하거나, 이후 다른 자료를 비교할 때 문제가 생길 수도 있습니다.

그래서 저는 엑셀 자료를 정리할 때 중복 데이터가 있는지 먼저 확인한 후 필요한 경우 수정하는 과정이 중요하다고 생각합니다.

이번 글에서는 회사 자료를 정리할 때 활용할 수 있는 엑셀 중복값 찾는 방법을 조건부 서식과 COUNTIF 함수를 중심으로 정리해보겠습니다.

 

※ 아래 예시는 실제 회사 자료가 아닌 설명을 위한 가상의 데이터입니다.

 

---

1. 엑셀에서 중복값을 확인해야 하는 경우

엑셀에서 중복값을 찾는 작업은 생각보다 여러 상황에서 필요합니다.

예를 들어 회사 자료라면 다음과 같은 경우가 있습니다.

• 직원번호가 중복으로 입력된 경우
• 거래처 코드가 중복된 경우
• 거래처명이 반복되는 경우
• 상품코드가 중복된 경우
• 같은 자료를 여러 사람이 입력한 경우
• 여러 개의 엑셀 파일을 하나로 합친 경우

특히 여러 사람이 각각 작성한 자료를 하나로 합칠 때는 같은 데이터가 들어갈 가능성이 있습니다.

이때 전체 데이터를 하나씩 눈으로 확인하는 것보다 엑셀 기능을 이용해서 중복값을 먼저 찾아보는 것이 훨씬 편합니다.

---

2. 가장 간단한 방법은 조건부 서식

엑셀에서 중복값을 빠르게 확인하고 싶다면 조건부 서식을 활용할 수 있습니다.

조건부 서식은 특정 조건에 해당하는 셀의 서식을 자동으로 표시해주는 기능입니다.

중복값도 조건부 서식을 이용해서 색으로 구분할 수 있습니다. Microsoft에서도 중복값을 확인할 때 조건부 서식을 이용해 중복 데이터를 강조 표시하는 방법을 안내하고 있습니다.

예를 들어 다음과 같은 직원번호 자료가 있다고 해보겠습니다.

직원번호	이름	부서
1001	김민수	인사
1002	이지은	회계
1003	박지훈	총무
1002	최서연	영업
1005	김현우	구매

여기에서 직원번호 1002가 두 번 들어가 있습니다.

이런 경우 중복값을 색으로 표시하면 쉽게 확인할 수 있습니다.

 

중복값을 확인할 열을 선택합니다.

예를 들어 직원번호가 A열에 있다면 A2:A6을 선택합니다.

그다음 엑셀 메뉴에서 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값 순서로 선택합니다.

엑셀이 선택한 범위에서 중복되는 값을 찾아 지정한 서식으로 표시해줍니다.

엑셀에서 중복된 데이터 값을 찾을 때 조건부서식에서 셀강조 규칙을 보면 중복값을 표시하는 방법이 있다.

---

 

 

3. COUNTIF 함수로 중복 여부 확인하기

중복값을 단순히 색으로 표시하는 것이 아니라 중복 여부를 별도의 열에서 확인하고 싶다면 COUNTIF 함수를 사용할 수 있습니다.

COUNTIF는 지정한 범위에서 특정 조건에 해당하는 셀의 개수를 계산하는 함수입니다.

예를 들어 A열에 직원번호가 있다고 해보겠습니다.

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

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

 

그러면 A2의 직원번호가 A2:A6 범위에서 몇 번 나타나는지 계산합니다.

결과가

• 1 → 한 번만 존재
• 2 → 두 번 존재
• 3 → 세 번 존재

하는 방식입니다.

엑셀에서 중복값을 조건부서식이 아닌 COUNTIF로 찾는 방법

 

---

중복 여부를 '중복' 또는 '정상'으로 표시하기

숫자로 표시하는 것보다 중복 여부를 바로 확인하고 싶다면 IF 함수와 함께 사용할 수 있습니다.

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

 

위 수식처럼 표시할 수 있습니다.

 

엑셀에서 중복값을 찾을때 COUNTIF와 IF를 같이 사용하여 중복을 표시하고 싶을때 사용.

 

이 방식은 중복 여부를 별도의 열에서 확인하고 싶을 때 유용합니다.

---

4. 중복값을 찾았다고 바로 삭제하면 안 되는 이유

여기서 중요한 부분이 있습니다.

중복값이라고 표시됐다고 해서 무조건 삭제하면 안 됩니다.

예를 들어 직원 이름이 두 번 나온다고 해보겠습니다.

이름	부서
김민수	인사
김민수	영업

이 경우 이름만 보면 중복이지만 실제로는 동명이인일 수 있습니다.

거래처명도 마찬가지입니다.

같은 회사 이름이 두 번 나왔다고 해서 반드시 중복 거래처라고 볼 수는 없습니다.

사업장이나 거래처 코드가 다를 수도 있기 때문입니다.

따라서 중복값을 찾았다면 먼저 무엇을 기준으로 중복인지 정하는 것이 중요합니다.

---

5. 중복을 판단하는 기준을 먼저 정하기

예를 들어 직원자료라면 단순히 이름만 비교하는 것보다 직원번호를 기준으로 중복을 확인하는 것이 더 적절할 수 있습니다.

거래처 자료라면

• 거래처 코드
• 사업자등록번호
• 거래처명

등 어떤 항목을 기준으로 볼 것인지 먼저 정해야 합니다.

예를 들어 '거래처명은 같지만 사업자등록번호가 다르다.' 면 단순 중복으로 판단해서는 안 될 수 있습니다.

반대로 '거래처 코드와 사업자등록번호가 모두 동일하다.' 면 중복 자료인지 다시 확인할 필요가 있습니다.

중복값 찾기의 핵심은 중복을 찾는 것보다 무엇을 중복으로 볼 것인지 기준을 정하는 것입니다.

---

6. 엑셀의 '중복 제거' 기능 사용하기

중복값을 확인한 뒤 실제로 불필요한 데이터를 삭제해야 하는 경우에는 엑셀의 중복 제거 기능을 사용할 수 있습니다.

방법은 간단합니다.

먼저 중복을 제거할 자료 범위를 선택합니다.

그다음

데이터 → 중복 제거

를 선택합니다.

 

중복된 항목제거 선택하면 나오는 창

 

엑셀에서 중복된 데이터를 지우고 난 뒤 화면

 

중복 제거를 실행하면 어떤 열을 기준으로 중복 여부를 판단할 것인지 선택할 수 있습니다.

예를 들어 직원번호만 선택하면 직원번호를 기준으로 중복 여부를 판단합니다.

여러 열을 선택하면 선택한 열의 조합을 기준으로 중복 여부를 판단합니다. 따라서 어떤 열을 선택하는지가 중요합니다.

 

중복 제거 전에 꼭 확인하세요

중복 제거 기능은 실제 데이터를 삭제하는 작업이기 때문에 실행하기 전에 원본 데이터를 복사해두는 것이 좋습니다.

Microsoft에서도 중복 제거를 실행하면 데이터가 영구적으로 삭제될 수 있으므로, 작업 전에 원본 범위를 다른 워크시트나 통합 문서에 복사해두는 것을 권장하고 있습니다.

저라면 원본 파일을 바로 수정하기보다는 원본 파일 보관 → 복사본에서 중복 확인 → 삭제 여부 확인 순서로 작업할 것 같습니다.

---

7. 두 개의 자료를 비교해서 중복값 찾기

회사에서 엑셀을 사용하다 보면 하나의 목록 안에서 중복값을 찾는 것보다 두 개의 자료를 비교해야 하는 경우도 있습니다.

예를 들어 A열 이번 달 직원번호 C열 지난달 직원번호가 있다고 해보겠습니다.

두 자료에 같은 직원번호가 있는지 확인하고 싶다면 MATCH 등을 활용해서 비교할 수 있습니다.

Microsoft에서도 두 열의 데이터를 비교해 중복 항목을 찾는 방법으로 MATCH와 IF를 조합한 방법을 안내하고 있습니다.

다만 자료가 단순하다면 먼저 조건부 서식이나 COUNTIF로 해결할 수 있는지 확인한 후, 필요한 경우 다른 함수를 사용하는 것이 편합니다.

---

8. 중복값을 찾을 때 생기는 의외의 문제

중복값을 찾았는데 생각보다 많은 값이 중복으로 표시되는 경우가 있습니다.

이때는 단순히 같은 데이터가 입력된 것인지 확인하기 전에 데이터 자체가 동일한 형태로 입력되어 있는지도 확인해야 합니다.

예를 들어 이름이나 거래처명을 입력할 때 앞뒤에 불필요한 공백이 들어가거나 데이터 형식이 서로 다르면 예상과 다른 결과가 나올 수 있습니다.

COUNTIF 같은 함수를 사용할 때도 데이터에 앞뒤 공백이나 잘못된 문자가 들어가 있으면 결과를 확인하기 어려울 수 있다고 Microsoft가 안내하고 있습니다.

그래서 중복값을 찾기 전에 자료의 입력 상태를 한 번 확인하는 것도 좋습니다.

---

9. 회사 자료에서 중복값을 확인할 때 체크리스트

중복값을 찾는 작업은 단순해 보이지만 실제 자료에서는 무엇을 중복으로 볼 것인지가 가장 중요합니다.

엑셀 중복값 확인 체크리스트

□ 원본 파일을 먼저 보관하기

□ 중복을 판단할 기준 정하기

□ 중복값을 조건부 서식으로 확인하기

□ 필요한 경우 COUNTIF로 중복 횟수 확인하기

□ 이름만 같다고 바로 중복으로 판단하지 않기

□ 거래처명과 코드 등 관련 항목 함께 확인하기

□ 중복 제거 전에 삭제 대상 다시 확인하기

□ 중복 제거 후 데이터 건수 다시 확인하기

 

엑셀에서 중복값을 찾는 방법은 생각보다 어렵지 않습니다.

가장 간단하게 확인하려면 조건부 서식 → 중복 값 기능을 이용하면 되고,

중복 횟수를 확인하거나 별도의 결과를 만들고 싶다면 COUNTIF 함수를 활용할 수 있습니다.

 

실제로 중복 데이터를 삭제해야 한다면 데이터 → 중복 제거 기능을 사용할 수 있습니다. 다만 중복 제거는 데이터를 실제로 삭제하는 작업이기 때문에 원본을 먼저 보관하고, 어떤 열을 기준으로 중복을 판단할 것인지 확인한 뒤 실행하는 것이 좋습니다.

특히 회사 자료에서는 같은 이름이나 같은 거래처명이 있다는 이유만으로 바로 삭제하지 않는 것이 중요합니다.

 

중복값을 찾는 것보다 먼저 "이 자료에서 무엇을 중복이라고 판단할 것인가?"를 정해두면 실수도 줄일 수 있습니다.

엑셀 자료를 정리할 때 중복값 때문에 고민된다면 먼저 조건부 서식으로 전체 자료를 확인하고, 필요한 경우 COUNTIF로 중복 횟수를 확인한 다음 실제 삭제 여부를 판단하는 방법을 활용해보세요.