Excel에서 중복 찾기: 강조, 개수 세기, 목록 만들기

By Mirko Peters · 2026-11-12

조건부 서식, COUNTIF 플래그, UNIQUE, Power Query, 시트 간 검사를 사용해 Excel에서 중복을 찾는 방법과 삭제하기 전에 유사 일치 항목을 확인하는 방법을 설명합니다.

Automatically translated with AI from the original post. Read the English original

Excel에서 아무것도 삭제하지 않고 중복을 찾으려면 데이터를 선택한 다음 홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값을 사용합니다. Excel은 두 번 이상 나타나는 모든 값에 색을 입힙니다. 색 대신 숫자로 확인하려면 COUNTIF 수식이 들어간 도우미 열을 추가합니다. 이 글은 정리되지 않은 목록을 물려받아 문제를 고치기 전에 먼저 파악해야 하는 Excel 사용자를 위한 것입니다. 강조 표시 방법, 개수 세기와 플래그 지정 수식, 반복되는 값만 나열하는 방법, Power Query 옵션, 시트 간 검사, 유사 일치 항목을 처리하는 계획을 다룹니다.

핵심 포인트

• Excel에서 중복을 가장 빠르게 찾는 방법은 홈, 조건부 서식, 셀 강조 규칙, 중복 값입니다. 반복되는 값에 색을 지정하며 아무것도 삭제하지 않습니다.
• COUNTIF 도우미 열은 각 값이 몇 번 나타나는지 알려 주며, COUNTIFS를 사용하면 여러 열을 함께 검사할 수 있습니다.
• COUNTIF는 영문 대소문자를 구분하지 않으며 ?와 *를 와일드카드로 취급하므로, 개수가 예상과 다를 수 있습니다.
• 두 시트를 비교하려면 COUNTIF로 각 값을 다른 목록과 대조해 개수를 세고, 두 번째 목록이 다른 파일에 있다면 두 통합 문서를 모두 여십시오.
• Jon과 John 같은 유사 일치 항목은 중복으로 표시되지 않습니다. 먼저 공백과 대소문자를 정리한 다음, 나머지는 직접 검토하거나 Fuzzy Lookup 추가 기능을 사용하십시오.

삭제보다 찾기가 먼저입니다. 삭제할 행을 이미 정했다면 관련 가이드인 Excel에서 중복을 제거하는 방법에서 삭제 단계를 다룹니다. 여기서의 목표는 Excel에서 먼저 살펴보고, 무엇을 중복으로 볼지, 무엇을 남길지 직접 결정하는 것입니다.

조건부 서식으로 중복 강조하기

가장 빠른 확인 방법이며, Microsoft도 무언가를 제거하기 전 검토 단계로 이를 권장합니다. Microsoft의 고유 값 필터링 또는 중복 값 제거 문서를 따릅니다.

1. 확인하려는 셀, 열 또는 범위를 선택합니다. 대개 한 개의 열이면 충분합니다.
2. 홈 탭에서 조건부 서식을 엽니다.
3. 셀 강조 규칙을 선택한 다음 중복 값을 선택합니다.
4. 연한 빨강 채우기 같은 서식을 고르고 확인을 선택합니다.

두 번 이상 나타나는 값을 가진 모든 셀에 색이 입혀집니다. 시트를 훑어보거나 열을 색 기준으로 필터링하면 강조된 셀만 볼 수 있습니다. 되돌리려면 조건부 서식에서 해당 범위의 규칙을 지우십시오.

더 세밀하게 제어할 수 있는 두 번째 방법도 있습니다. 조건부 서식 > 규칙 관리를 열고 새 규칙을 만든 다음 고유 또는 중복 값만 서식 지정을 선택합니다. 다음에 해당하는 모든 값 목록에서 고유 또는 중복을 고르고, 표시 형식, 글꼴, 테두리 또는 채우기를 설정할 수 있습니다. 한 번만 나타나는 값에만 색을 입히고 반복되는 값은 그대로 두고 싶을 때 유용합니다.

알아두어야 할 제한 사항이 두 가지 있습니다. Microsoft에 따르면 Excel은 피벗 테이블의 값 영역에 있는 중복 항목을 강조 표시할 수 없습니다. 또한 열 하나를 선택하면 규칙은 한 번에 한 열만 검사합니다. 여러 열을 함께 봐야만 중복되는 행은 강조되지 않습니다. 이런 경우에는 수식이 필요합니다.

COUNTIF로 중복 세기 및 표시

도우미 열을 사용하면 정렬하거나 필터링할 수 있는 숫자를 얻을 수 있습니다. 두 행이 두 개 또는 세 개의 열 값이 모두 일치할 때만 중복되는 경우에도 적합한 방법입니다.

반복되는 모든 값 표시

값이 A2:A100에 있다고 가정해 보겠습니다. B2에 다음을 입력합니다.

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

수식을 아래로 채웁니다. 결과가 1이면 고유한 값이라는 뜻입니다. 2이면 두 번 나타난다는 뜻이며, 이런 식으로 계속됩니다. 반복되는 값의 모든 항목은 첫 번째 항목을 포함해 같은 숫자를 받습니다. 개수를 TRUE 또는 FALSE로 바꾸려면 비교 연산을 추가합니다: =COUNTIF($A$2:$A$100,A2)>1.

두 번째 이후에 나타나는 항목만 표시

행을 삭제하려는 경우에는 보통 첫 번째 행을 남기고 싶을 것입니다. 이때는 확장되는 범위를 사용합니다.

=COUNTIF(A$2:A2,A2)>1

수식을 아래로 복사하면 A$2:A2 범위가 점점 커집니다. 값이 처음 나타날 때는 FALSE가 표시되고, 그 이후에 나타나는 모든 항목에는 TRUE가 표시됩니다. Excel의 중복 항목 제거 명령은 첫 번째 항목을 남기므로 이 방식과 동일한 결과를 얻습니다.

COUNTIFS로 여러 열 검사

예를 들어 이름과 성이 모두 일치할 때만 중복으로 보는 레코드라면, 열마다 범위와 조건 쌍을 하나씩 지정하는 COUNTIFS를 사용합니다.

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

결과가 1보다 크면 해당 조합이 두 번 이상 나타난다는 뜻입니다. 열이 더 많다면 쌍을 더 추가하면 됩니다. COUNTIF 안에서 범위를 결합하는 방식으로 같은 검사를 만들려고 하지 마십시오. 예를 들어 COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2)처럼 작성하는 방식입니다. COUNTIF는 첫 번째 인수로 실제 범위를 요구하므로, 이 수식은 보이는 것과 다르게 동작합니다. 이 경우에는 COUNTIFS가 가장 명확한 해법입니다.

중복 검색 시 사람들을 놀라게 하는 COUNTIF의 동작

Microsoft의 COUNTIF 페이지에서는 중복 항목을 찾을 때 중요한 몇 가지 동작을 설명합니다.

• 텍스트 일치는 영문 대소문자를 구분하지 않습니다. "apples"와 "APPLES"는 같은 값으로 셉니다. 따라서 Excel은 이 둘을 중복으로 취급합니다.
• ?와 * 문자는 와일드카드로 동작합니다. 예를 들어 제품 코드처럼 데이터에 이런 문자가 포함되어 있으면 COUNTIF가 예상보다 많이 일치할 수 있습니다. 문자를 그대로 검색하려면 해당 문자 앞에 물결표(~)를 붙이십시오.
• COUNTIF는 255자를 넘는 문자열에 대해 잘못된 결과를 반환합니다.
• 앞뒤 공백, 직선 따옴표와 곡선 따옴표가 섞인 경우, 출력되지 않는 문자 등도 개수 계산을 잘못되게 만들 수 있습니다. Microsoft는 데이터를 먼저 정리하기 위해 TRIM 및 CLEAN 함수를 사용할 것을 권장합니다.

"Excel이 중복을 찾지 못한다"는 불만의 대부분은 바로 이 마지막 사항에서 비롯됩니다. 겉보기에 똑같아 보이는 두 셀도 뒤에 공백이 하나 더 있을 수 있습니다. 먼저 정리한 다음 개수를 세십시오.

필터링 및 검토

도우미 열을 만들었다면 TRUE 또는 1보다 큰 값으로 필터링한 후 행을 확인하십시오. 도우미 열을 기준으로 정렬하면 반복되는 행을 모두 한곳에 모을 수 있습니다. 무언가를 삭제하기 전에 샘플을 먼저 확인하십시오. 많은 경우 "중복"이라고 생각했던 항목이 이름만 같은 별개의 레코드인 경우가 많습니다.

중복 값만 목록으로 표시

때로는 각 행 옆에 표시를 붙이는 대신 반복되는 항목만 간단한 목록으로 보고 싶을 수 있습니다.

Microsoft 365용 Excel, 2024 및 2021에서는 UNIQUE 함수로 이 작업을 할 수 있습니다. Microsoft의 UNIQUE 페이지에 따르면 =UNIQUE(array,[by_col],[exactly_once])는 범위에서 고유한 값을 반환하며, exactly_once를 TRUE로 설정하면 한 번만 나타나는 값만 반환합니다. 따라서 =UNIQUE(A2:A100)은 각 값을 한 번씩 보여 주고, =UNIQUE(A2:A100,,TRUE)는 중복되지 않은 값만 보여 줍니다. 반복되는 값만 얻으려면 UNIQUE를 FILTER 및 COUNTIF와 결합하십시오.

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

이 수식은 개수가 1보다 큰 행만 남기고, 각 값을 한 번씩 표시합니다. 동적 배열을 지원하는 Excel 버전이 필요합니다. 큰 목록에 적용하기 전에 몇 개의 행으로 먼저 테스트해 보십시오.

이전 버전에서는 도우미 열을 사용하십시오. =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"")를 입력하고 아래로 채웁니다. 이제 열에는 반복되는 각 값이 표시되고 나머지 행은 빈 칸으로 남습니다. 빈 칸을 필터로 제외하십시오. 그러면 반복되는 각 값이 나타난 횟수만큼 표시되는데, 검토용으로는 이것으로 충분합니다.

고급 필터를 사용하면 고유 레코드만 새 위치에 복사할 수 있습니다. 데이터 > 고급으로 이동하여 다른 위치에 복사를 선택하고, 복사 위치 상자에 셀을 입력한 다음 고유 레코드만을 선택합니다. 이 목록을 원본과 비교하면 어떤 항목이 반복되었는지 확인할 수 있습니다.

Power Query로 중복 항목만 남기기

데이터가 많거나 매주 새로 들어온다면 Power Query로 중복 항목을 찾는 것이 좋습니다. Microsoft의 Power Query 문서에는 중복 항목 제거와 반대되는 중복 항목 유지 명령이 설명되어 있습니다. 이 명령은 선택한 열의 값이 두 번 이상 나타나는 행만 남기며, 중복 항목 제거와 달리 값당 하나만 남기지 않고 일치하는 모든 행을 반환합니다.

중복 행을 유지하거나 제거하는 방법에 대한 Microsoft 문서 중복 행 유지 또는 제거에 제거 방법의 단계가 나와 있습니다. 두 방법의 시작 단계는 동일합니다.

1. 데이터 안의 셀을 선택하고 쿼리 > 편집을 선택하거나, 데이터 > 테이블/범위에서를 선택하여 데이터를 불러옵니다.
2. 열 머리글을 클릭하여 해당 열을 선택합니다. 여러 열을 선택하려면 Shift 키 또는 Ctrl 키를 누른 상태에서 선택합니다.
3. 명령을 적용합니다. Microsoft의 제거 단계는 홈 > 행 제거 > 중복 항목 제거를 사용합니다. 홈 탭의 다른 행 명령 중에서 짝이 되는 중복 유지 명령을 찾아보고 미리 보기를 확인하세요.

Microsoft는 또한 Power Query가 대소문자를 구분한다고 설명합니다. 따라서 COUNTIF와 달리 "Apple"과 "apple"은 서로 다른 값으로 처리됩니다. 이는 두 방법 사이의 실제 차이입니다. 매주 반복하는 점검이 목표라면 동작 방식을 하나로 정해 일관되게 사용해야 합니다. 예를 들어 먼저 열을 대문자로 변환하는 방법이 있습니다.

두 시트 또는 목록 사이의 중복 찾기

흔한 작업 중 하나는 한 시트의 목록이 다른 시트에도 나타나는지 확인하는 것입니다. 예를 들어 새 고객 목록을 기존 목록과 비교하거나, 이번 달 주문을 지난달 주문과 비교하는 경우입니다.

1. 두 목록을 Sheet1과 Sheet2처럼 별도의 시트에 두고, 값은 A열에 입력합니다.
2. Sheet1의 B2에 =COUNTIF(Sheet2!A:A,A2)>0을 입력합니다.
3. 수식을 아래로 채웁니다. TRUE는 해당 값이 Sheet2에도 있다는 뜻입니다.
4. TRUE로 필터링하면 겹치는 항목을, FALSE로 필터링하면 새로운 항목을 확인할 수 있습니다.

두 열의 값이 함께 일치해야 한다면 COUNTIFS를 사용하고, 각 열에 대한 조건 쌍과 다른 시트의 범위를 지정합니다.

두 번째 목록이 다른 통합 문서에 있다면, Microsoft는 닫힌 통합 문서를 참조하는 수식이 #VALUE! 오류를 반환할 수 있다고 경고합니다. 작업하는 동안 두 통합 문서를 모두 열어 두거나, Power Query를 사용해 두 번째 목록을 첫 번째 통합 문서로 가져오세요. 점검이 끝난 후에도 결과는 그 시점의 데이터에 따라 달라진다는 점을 기억하세요. 어느 목록이든 변경되면 표시도 달라집니다.

유사한 항목: Jon Smith와 John Smith

위에서 설명한 방법은 대소문자 차이를 제외하면 완전히 일치하는 항목만 찾습니다. 실제 목록에는 오타나 표기가 다른 경우도 많습니다. Excel에는 두 텍스트 값이 얼마나 비슷한지 점수로 계산하는 기본 제공 함수가 없으므로, 다음 두 단계로 처리합니다.

1단계: 불필요한 차이 제거. TRIM으로 불필요한 공백을 제거하고 CLEAN으로 인쇄되지 않는 문자를 제거한 뒤, 정리된 값을 기준으로 점검합니다. 도우미 열에 =TRIM(A2)을 입력하면 정리된 텍스트를 확인할 수 있습니다. 원본이 아니라 이 열을 기준으로 중복을 셉니다. COUNTIF는 대소문자를 구분하지 않으므로 여기서는 대소문자를 바꿀 필요가 없습니다. 하지만 Power Query에서는 대소문자가 중요하므로 열을 대문자 또는 소문자로 변환합니다.

2단계: 남은 항목을 확인합니다. 정리된 목록을 알파벳순으로 정렬한 뒤 인접한 항목을 살펴봅니다. "Jon Smith"와 "John Smith"처럼 유사한 이름이 서로 붙어 있게 되므로 사람이 눈으로 확인할 수 있습니다. 목록이 큰 경우에는 Microsoft Research가 배포한 Excel용 Fuzzy Lookup 추가 기능을 활용할 수 있습니다. 이 기능은 한 표 안에서 유사 중복 행을 찾거나, 두 표에서 비슷한 행을 짝지어 찾을 수 있으며, 각 일치 항목에 유사도 점수를 매겨 줍니다. 다만 계획에 포함하기 전에 요구 사항을 확인하십시오. 다운로드 페이지에는 Windows와 Excel 2007, 2010, 2013, 2016이 명시되어 있고 2020년에 배포된 버전입니다. 최신 Microsoft 365 환경에는 설치되지 않을 수 있으며, Mac에서는 사용할 수 없습니다.

어떤 도구를 쓰든 유사 일치 결과는 직접 검토하십시오. 이름이 비슷하다고 해서 반드시 같은 사람은 아닙니다.

흔한 실수

• 중복을 두 개의 열로 정의해야 하는데 한 개의 열만 확인하는 실수입니다. 성이 같다는 것만으로는 중복 레코드가 되지 않습니다.
• 공백이나 특수 문자가 섞인 데이터에서 COUNTIF 결과를 그대로 믿는 실수입니다. 먼저 TRIM과 CLEAN 함수로 데이터를 정리하십시오.
• COUNTIF는 대소문자를 구분하지 않지만 Power Query는 구분한다는 점을 잊고, 두 방식의 결과가 다르다고 당황하는 경우입니다.
• COUNTIF($A$2:$A$100,A2)>1을 사용해 TRUE로 표시된 행을 모두 삭제하는 실수입니다. 이 방식은 첫 번째 항목까지 중복으로 표시하므로 모든 사본이 삭제됩니다. 확장 범위를 사용해 두 번째 이후 항목만 표시하십시오.
• 전체 열에서 255자를 넘는 문자열을 검색하는 실수입니다. 이 경우 COUNTIF가 잘못된 결과를 반환합니다.
• 강조 표시 직후 바로 삭제하는 실수입니다. Microsoft에 따르면 제거된 중복 항목은 영구적으로 삭제되므로, 먼저 시트를 복사해 두십시오.

함께 살펴보면 좋은 관련 주제

• 찾은 중복 항목을 제거합니다. 데이터 탭의 중복 항목 제거 명령은 첫 번째 일치 항목은 남기고 나머지를 삭제합니다. 어떤 행을 남길지 통제하려면 먼저 정렬하십시오.
• 첫 번째가 아니라 가장 좋은 레코드를 남기십시오. 날짜나 값으로 정렬해 원하는 행이 맨 위에 오도록 한 다음, 확장 COUNTIF로 나중에 나온 사본을 표시하십시오.
• 여러 파일의 목록을 비교합니다. Power Query를 사용하면 여러 통합 문서를 하나의 표로 결합해 함께 확인할 수 있습니다.
• 새 중복이 생기지 않도록 예방합니다. 사용자 지정 수식을 사용하는 데이터 유효성 검사로 이미 존재하는 값의 입력을 막을 수 있습니다. 테스트 시트에서 먼저 시험해 보십시오.
• 감사 기록을 남깁니다. 시트 사본에 도우미 열을 남겨 두면 나중에 무엇이 왜 표시되었는지 설명할 수 있습니다.

다음 단계

현재 파일에서 열 하나를 골라 조건부 서식으로 중복 항목을 강조 표시하고, 강조된 셀 개수를 세어 보십시오. 그다음 COUNTIF 도우미 열을 추가해 두 숫자를 비교합니다. 숫자가 다르다면 숨겨진 공백이나 대소문자 차이가 있다는 뜻입니다. 이렇게 하면 무언가를 삭제하기 전에 Excel에서 중복을 찾는 방법을 확실히 익힐 수 있습니다.