[엑셀_중급] 중복 값이 있는 셀 또는 전체 행 표시하기 (조건부 서식)

※ Office 365를 기준으로 작성되었습니다.

 

조건부 서식을 이용하여 중복된 값을 표시하는 방법에 대해 알아보겠습니다.

먼저 1개의 행/열에서 중복 값을 찾아 표시하는 방법을 살펴보고,

그 다음 중복 값이 있는 셀의 전체 행을 찾아 표시하는 방법을 설명드리겠습니다. 

 

중복 값 표시 (조건부 서식).xlsx

0.01MB

 

 

---

 

 

 

Ⅰ. 1개의 행 또는 열에 중복 값 표시 방법

 

1. 검색 영역 지정

먼저, 중복 값이 있는지 검색하려는 영역을 선택합니다.

Tip! 영역의 상단에서 하단으로 선택해야 조건부 서식에 오류가 발생하지 않습니다.

 

 

2. 조건부 서식 실행

① [홈 > 스타일 > 조건부 서식 > 셀 강조 규칙 > 중복 값]을 클릭합니다.

 

② '적용할 서식'에서 중복 값 표시형태를 선택하거나, '사용자 지정 서식'으로 원하는 표시형태를 설정하고 '확인'을 클릭합니다.

 

 

 

---

 

 

 

ㅊ

 

---

 

 

※ 수식 해석

여기서부터는 집중해서 봐주셔야 합니다.

 

들어가기에 앞서, 조건부 서식의 수식에 적용되는 법칙 두 가지를 기억해주세요.

 

① 조건부 서식의 수식은 선택한 범위의 첫 번째 셀(밝은 색으로 선택된 셀)부터 적용된다.

② 조건부 서식의 수식도 일반 수식처럼 앵커($)의 영향을 받는다.

 

---

 

1. 수식의 결과값은 TRUE or FALSE

조건부 서식에서 사용하는 수식의 결과값은 '참(TRUE)' 또는 '거짓(FALSE)'이어야 합니다.

결과값이 '참'이면 해당 셀에 지정한 서식을 적용하게 되는 것입니다.

(예시: =5>3 → TRUE / =4<3 → FALSE)

 

그렇다면 위의 예시에서 접수번호를 기준으로 중복 값을 찾는 수식은 어떻게 구성해야 할까요?

 

문장으로 표현하자면 아래와 같습니다.

접수번호 영역(range)에 각 접수번호(criteria)가 2개 이상(>=2)인가?

 

 

위 문장을 수식으로 풀어봅시다.

=COUNTIF(range, criteria)>=2
▼
=COUNTIF($E$3:$E$8,$E3)>=2

 

 

수식을 보면, COUNTIF 함수의 결과값이 2 이상(중복O)이면 TRUE를, 2 미만(중복X)이면 FALSE를 반환하게 됩니다.

(▼COUNIFS 함수에 대한 설명은 링크 참조)

[엑셀] 수식 입력 가이드 해석(VLOOKUP, COUNTIFS, SUMIFS)

 

2. 앵커($)를 박는 이유

(▼앵커($)에 대한 설명은 링크 참조)

[엑셀_초급] 수식 범위에 앵커($) 걸기(범위 고정)

조건부 서식의 수식에서 셀 주소에 앵커($)를 박는 이유는 무엇일까요?

조건부 서식은 지정한 범위의 셀 하나하나마다 조건을 계산하기 때문입니다.

 

조건부 서식의 수식은 선택한 범위의 첫 번째 셀(밝게 표시된 색)부터 적용됩니다.

따라서 앵커를 박지 않으면 수식에서 참조하는 셀의 주소도 이동하게 됩니다.

 

만약 위의 수식에서 찾는 값(E3)에 앵커($)를 박지 않았다면, D3셀의 조건은 =COUNTIF($E$3:$E$8, E3)이 됩니다.

E3셀의 찾는 값은 F3, F3셀은 G3, G3셀은 H3이 되겠죠.

찾는 값(criteria)에 앵커($)를 박지 않았을 경우의 셀별 조건

 

위 예시에서, 범위가 이동할 때 찾는 값의 행(3~8열)이 함께 변경되는 것까지는 맞습니다.

그러나 찾는 값(접수번호)의 열은 E행으로 고정되어 있어야 합니다.

따라서 올바른 수식은 =COUNTIF($E$3:$E$8, $E3)이 되는 것입니다.

찾는 값(criteria)의 열에 앵커를 박았을 때

위 이미지처럼 올바른 수식으로 입력했을 때, G6셀의 조건을 문장과 수식으로 각각 표현하면 다음과 같습니다.

접수번호 열에서 E6셀의 값이 2개 이상인가?
=COUNTIF($E$3:$E$8,$E6)>=2

 

 

---

 

조건부 서식의 수식은 첫 번째 셀에 적용되는 수식(직접 입력하는 수식)을 제외하고는 화면에 드러나지 않기 때문에 추상적 사고가 필요합니다.

그러나 어려운 만큼 활용 가능성도 높으니 포기하지 마시고 잘 따라와주시기 바랍니다.

조건부 서식 활용 방법은 기회가 되는 대로 종종 올리도록 하겠습니다.