- 최초 작성일: 2002-07-05
- 최종 수정일: 2026-09-29
- 조회수: 24 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 중복되는 값에 표시하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
오래간만에 조건부 서식을 활용한 예제 한 가지를 살펴보도록 합니다. 아래와 같은 데이터에서 중복되는 값이 있는 셀에 별도의 표시를 해야 할 일이 있다면 어떻게 할까요? 데이터 수가 그다지 많지 않는데도 쳐다보고 있으려니 눈이 팽팽 돌아갑니다.
- 최신 글을 불러오는 중...
중복되는 값에 표시하기
핵심 요약: COUNTIF와 조건부 서식으로 중복값 표시하기
영역 안에서 같은 값이 여러 번 나오는 셀을 눈으로 일일이 찾아내기는 쉽지 않습니다. 조건부 서식의 수식 조건에 COUNTIF 함수를 활용하면 중복값에 자동으로 서식을 입힐 수 있습니다.
- COUNTIF 조건식: =COUNTIF($B$40:$H$58,B14)>1 로 중복 여부를 판단합니다.
- 절대·상대참조 구분: 비교 범위는 절대참조, 비교 기준 셀은 상대참조로 지정합니다.
- 응용: 부등호를 바꾸면 반대로 중복되지 않는 값만 표시할 수도 있습니다.
[표 1] 원본 데이터입니다.
=INT(RAND()*1000) | =INT(RAND()*1000) | =INT(RAND()*1000) | =INT(RAND()*1000) | =INT(RAND()*1000) | =INT(RAND()*1000) | =INT(RAND()*1000) |
| (이하 동일한 패턴으로 총 16행의 임의 데이터가 이어집니다) | ||||||
특정한 영역 내에서 중복되는 값을 파악하고자 할 경우 조건부 서식을 사용하면 간단하게 해결이 됩니다. 아래의 표와 같이 말입니다.
[표 2] 중복되는 값에 표시를 한 데이터입니다. 원본 데이터를 그대로 참조하며, 같은 값이 2개 이상 존재하는 셀에만 서식이 적용된 모습입니다.
=B14 | =C14 | =D14 | =E14 | =F14 | =G14 | =H14 |
| (이하 동일한 패턴으로 총 19행이 원본 데이터를 그대로 참조합니다) | ||||||
(1) 조건부 서식을 지정할 영역을 지정하고 '서식-조건부 서식' 메뉴를 선택합니다.
(2) '조건부 서식' 대화 상자에서 아래와 같이 수식을 입력합니다. 이 수식은 원래 아래와 같이 IF 구문을 사용할 것을 축약해서 쓴 것입니다.
=IF(COUNTIF($B$40:$H$58,B14)>1,TRUE,FALSE)B40:H58 영역 내에서 같은 값을 가진 셀이 2개 이상인 경우에는 TRUE, 그렇지 않은 경우에는 FALSE로 처리가 되는 것입니다. 조건부 서식의 경우, TRUE인 경우에만 지정한 서식을 적용합니다.
(3) '서식' 버튼을 누른 다음 적당한 서식을 지정하고 '확인'을 누릅니다.
(4) 다시 '확인' 버튼을 눌러 마무리합니다. 그러면 위의 [표 2]와 같이 같은 값이 있는 셀에만 지정한 서식이 입혀집니다.
숙제를 하나 내어 드릴까요? 위에서는 '중복되는 값이 있는' 셀에 서식을 지정하는 방법에 대해 살펴보았는데, 만약 그 반대의 경우, 즉 '중복되지 않는 셀'에만 표시를 하려면 어떻게 해야 할까요? 너무 쉽지요?
정리 — 조건부 서식으로 중복값 표시하기
| 단계 | 내용 |
|---|---|
| 1 | 서식을 지정할 영역을 선택하고 [서식]-[조건부 서식] 메뉴를 엽니다. |
| 2 | 조건 형식을 '수식이'로 바꾸고 =COUNTIF($B$40:$H$58,B14)>1 을 입력합니다. |
| 3 | [서식] 버튼으로 강조 서식을 지정하고 확인합니다. |
| 4 | 중복되지 않는 값만 표시하려면 부등호를 =1 로 바꿉니다. |
자주 묻는 질문 (FAQ)
Q1. 조건부 서식으로 중복값을 표시하는 수식은 어떻게 만드나요?
=COUNTIF($B$40:$H$58,B14)>1 형태의 수식을 조건부 서식에 입력합니다. 지정한 영역에서 현재 셀과 같은 값을 가진 셀이 2개 이상이면 TRUE가 되어 지정한 서식이 적용됩니다.
Q2. 중복되지 않는 값에만 서식을 지정하려면 어떻게 하나요?
COUNTIF 조건의 부등호를 바꾸면 됩니다. =COUNTIF($B$40:$H$58,B14)=1 로 조건을 지정하면, 영역 내에서 값이 단 한 번만 나타나는(중복되지 않는) 셀에만 서식이 적용됩니다.
Q3. 조건부 서식 수식에서 절대참조를 쓰는 이유는 무엇인가요?
COUNTIF의 비교 대상 범위($B$40:$H$58)는 모든 셀에 대해 항상 같은 영역을 참조해야 하므로 절대참조로 고정합니다. 반면 비교 기준이 되는 셀(B14)은 조건부 서식이 적용되는 각 셀마다 달라져야 하므로 상대참조로 둡니다.
마치며
COUNTIF와 조건부 서식의 조합은 데이터 검증, 중복 체크 등 실무에서 매우 자주 쓰이는 패턴입니다. 오늘 배운 수식을 조금만 응용하면 반대로 '유일한 값'만 찾아내는 것도 어렵지 않으니 꼭 직접 연습해 보시기 바랍니다.
