엑셀 중복값 찾기, 조건부서식 규칙으로 중복데이터 표시

먼저 설명 드린 리본메뉴의 조건부서식-중복값 표시는 단순히 지정된 범위내에 행과 열에 상관없이 중복된 셀 값을 모두 표시해 줍니다. (관련 글 : 엑셀 조건부 서식으로 중복값 표시)

조건부서식의 규칙에 함수를 이용해서 좀 더 융통성 있게 중복값을 표시해 줄 수 있습니다.

1. 조건부 서식 단일조건 중복값 표시 (Countif 함수)

열을 기준으로 단일조건의 중복 데이터만 표시할려면 Countif 함수를 이용하면 됩니다. 여기서는 단일 조건인 ‘병원명’ 범위($B$3:$B$27)에서 중복값을 찾아내어 표시할려고 합니다.

즉 ‘병원명’이 중복된 경우 같은 행의 ‘업종’과 ‘위치’ 까지 표시되도록 서식을 같이 적용할 겁니다. 먼저 중복값을 표시할 데이터를 범위 지정한 후에 ‘조건부 서식’의 ‘새 규칙’으로 들어갑니다.

규칙 유형 선택에서 ‘수식을 사용하여 서식을 지정할 셀 결정’을 선택한 후에

아래와 같이 수식을 입력하고, 적용할 서식을 설정해주시고 확인을 클릭합니다.

조건부 서식에 쓰인 함수를 살펴보겠습니다.

=COUNTIF(‘범위’,‘값’)>1

※ 범위($B$3:$B$27)는 고정(?$:?$) 시켜주고, 값($B3)은 변동될 수 있게 열만 고정시켜 줍니다.

COUNTIF 함수로 ‘범위’내에서 ‘값’이 몇 개 있는지 숫자로 표시하고 이 값이 1 이상이라면(>1) 지정된 서식을 적용해줍니다. (1 이상이라는 것은 중복됐다는 뜻). 입력한 함수를 E열에 표시했으니 좀 더 이해가 빠를겁니다.

2. 조건부 서식 다중조건 중복값 표시 (SumProduct 함수)

여러 열의 다중조건을 적용해서 중복값을 찾고자 한다면, 예를 들어 아래의 데이터에서 병원명, 업종, 위치가 모두 중복되는 행에 표시를 해주려면 조건부서식의 규칙을 Sumproduct 함수를 이용하여 적용하면 됩니다.

1번과 동일한 과정으로 조건부서식의 규칙을 수식으로 정해줍니다.

조건부 서식의 규칙에 쓰인 수식은 아래와 같습니다.

=SUMPRODUCT(($B$31:$B$39=$B31)*($C$31:$C$39=$C31)*($D$31:$D$39=$D31))>1

간략히 풀어쓰자면

=SUMPRODUCT((병원명)*(업종)*(위치))>1

=SUMPRODUCT((조건1)*(조건2)*(조건3))>1

=SUMPRODUCT((범위1=값1)*(범위2=값2)*(범위3=값3))>1

=SUMPRODUCT(($B$31:$B$39=$B31)*($C$31:$C$39=$C31)*($D$31:$D$39=$D31))>1

위와 같은 방식으로 조건을 계속해서 추가할 수 있습니다.

입력한 결과는 아래와 같이 병원명(조건1)과 업종(조건2), 위치(조건3)가 모두 동일한 중복데이터를 표시해줍니다.

SomeProduct 함수는 조건이 모두 맞는 행들의 데이터를 합산해주는 함수로 글이 너무 길어지는 관계로 함수에 대한 자세한 설명은 생략하겠습니다. :)

엑셀 중복 찾기, 엑셀 중복데이터 찾기, 엑셀 중복 체크, 엑셀 중복이름 찾기, 엑셀 중복표시, 엑셀에서 같은 값 찾기, 엑셀 중복값 찾기, 엑셀 중복검사

My Post It