엑셀 중복 값 찾기: 조건부 서식·COUNTIF·두 열 비교

엑셀 중복 값 찾기는 조건부 서식으로 색을 표시하거나 COUNTIF 수식으로 반복 횟수를 세면 됩니다. 먼저 한 열의 값만 비교할지, 여러 열의 조합이나 행 전체를 중복으로 판단할지 정하세요. 아래 방법은 별도 VBA 없이 사용할 수 있습니다.

엑셀 중복 값 찾기 전에 확인할 것

한 열을 기준으로 찾으면 그 열에서 같은 값이 두 번 이상 나타나는지 확인합니다. 행 전체가 중복인지 확인하려면 비교할 열의 조합이 같아야 합니다. 조건부 서식과 아래 COUNTIF 예제는 한 열의 값을 기준으로 합니다. 여러 열을 기준으로 중복 항목을 제거할 때는 제거 창에서 기준 열을 직접 확인해야 합니다.

조건부 서식으로 중복 값을 색깔로 표시하기

1. 중복 여부를 확인할 데이터 범위를 선택합니다. 예를 들어 A2:A6을 선택합니다.
2. 홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값을 선택합니다.
3. 표시할 서식을 고르고 확인을 누릅니다.

범위 안에서 두 번 이상 나타나는 값이 강조됩니다. 처음 나온 셀도 같은 중복 그룹에 포함되어 표시됩니다. 원본 값을 바꾸지 않고 빠르게 확인할 때 적합합니다.

COUNTIF 수식으로 중복 여부와 개수 확인하기

다음 표처럼 A열에 값이 있다고 가정합니다.

셀	값	중복 개수
A2	사과	2
A3	바나나	2
A4	사과	2
A5	포도	1
A6	바나나	2

C2에 다음 수식을 입력하고 C6까지 채우면 각 값의 중복 개수를 확인할 수 있습니다.

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

결과가 1보다 크면 중복입니다. 이 수식은 대소문자를 구분하지 않습니다. *, ?, ~ 같은 문자가 값에 들어 있으면 COUNTIF가 이를 패턴으로 해석합니다. 값이 *인 셀이 하나뿐이어도 다른 텍스트를 함께 세므로, 결과가 1보다 크다는 이유만으로 같은 값이 중복됐다고 단정할 수 없습니다. 이런 문자를 글자 그대로 비교하려면 기준 인수를 아래처럼 바꿉니다.

=COUNTIF($A$2:$A$6,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?"))

~를 먼저 두 번으로 바꾸고 *·? 앞에 ~를 붙이는 순서가 중요합니다. 조건에 와일드카드를 일부러 쓰는 경우에는 이 처리를 하지 않습니다. 문자 대소문자를 구분해야 하는 경우에는 이 수식만으로 판별할 수 없습니다. 범위의 $ 기호는 수식을 아래로 복사해도 참조 범위가 바뀌지 않도록 합니다. 셀 오른쪽 아래의 채우기 핸들을 아래로 끌어 수식을 복사할 수 있습니다.

두 열을 비교해 공통 값 찾기

A열의 각 값이 B열 어디엔가 있는지 확인하려면 C2에 아래 수식을 입력하고 필요한 행까지 채웁니다.

=IF(COUNTIF($B$2:$B$4,A2)>0,"공통","없음")

A열 값	B열 값	C열 결과
사과	포도	공통
바나나	사과	없음
포도	배	공통

이 수식은 같은 행의 A열과 B열을 비교하는 것이 아니라, A열의 값을 B2:B4 전체에서 찾습니다. 여기에도 와일드카드 제한이 그대로 적용되므로, *·?가 든 값을 비교할 때는 위와 같은 SUBSTITUTE 처리가 필요합니다. 반대 방향도 확인하려면 B열의 값을 A열 범위와 비교하는 수식을 적용합니다.

정렬·필터와 피벗 테이블로 중복 확인하기

정렬하면 같은 값이 가까이 모여 눈으로 확인하기 쉽습니다. 여러 열로 된 표라면 표 전체를 선택하고 데이터 > 정렬에서 중복을 확인할 열을 기준으로 오름차순 정렬합니다. 한 열만 선택해 정렬 경고가 나타나면 선택 영역 확장을 선택해야 같은 행의 데이터가 함께 이동합니다. 필터는 필요한 항목을 추려 보는 기능이며, 그 자체로 중복을 판정하지는 않습니다.

항목별 반복 횟수는 피벗 테이블로 집계할 수 있습니다.

1. 머리글을 포함한 데이터 범위를 선택합니다.
2. 삽입 > 피벗 테이블을 선택하고 위치를 정한 뒤 확인합니다.
3. 중복 기준으로 삼을 필드를 행과 값 영역에 각각 넣습니다.
4. 값 영역의 필드 메뉴에서 값 필드 설정을 열고, 값 요약 기준을 개수로 지정합니다.

이 집계는 앞의 COUNTIF 예제에서 쓴 A2:A6(사과·바나나·사과·포도·바나나) 을 원본으로 합니다. A1에 품목 머리글을 넣고 A1:A6 을 선택해 피벗 테이블을 만들고, 품목 필드를 행과 값 영역에 넣으면 사과 2, 바나나 2, 포도 1이 됩니다. 바로 위 두 열 비교 표를 선택하면 각 항목이 1개씩으로 집계되어 같은 결과가 나오지 않습니다. 숫자 항목은 합계로 집계될 수도 있으므로 반복 횟수를 보려면 값 요약 기준이 개수인지 확인합니다.

중복 항목 제거 전 확인할 점

중복 항목 제거는 선택한 데이터에서 행을 삭제할 수 있습니다. 먼저 복사본을 만들고, 어떤 열을 기준으로 중복을 판단할지 확인하세요.

1. 머리글을 포함한 데이터 범위를 선택합니다.
2. 데이터 > 중복된 항목 제거를 선택합니다.
3. 중복 판단에 사용할 열을 확인하고 실행합니다.
4. 제거된 행과 남은 결과를 검토합니다.

선택한 열의 값이 같으면 다른 열의 값이 달라도 중복으로 처리될 수 있습니다. 필요하지 않은 행이 제거되지 않았는지 확인하세요.

결과가 예상과 다를 때

COUNTIF 결과가 맞지 않으면 선택한 데이터 범위와 수식의 기준 셀을 확인합니다. COUNTIF의 조건 지정 방법은 COUNTIF 함수 사용법에서, $A$2:$A$6처럼 범위를 고정하는 방법은 엑셀 $ 의미와 사용법에서 더 자세히 다룹니다. 셀 앞뒤에 공백이 있거나 숫자와 숫자처럼 보이는 텍스트가 섞이면 같은 값처럼 보여도 결과가 달라질 수 있습니다. 조건부 서식이나 피벗 테이블도 먼저 올바른 데이터 범위를 선택했는지 확인하세요.