[엑셀] 중복 데이터 검사하는 2가지 방법 (COUNTIF 함수, 조건부서식)

엑셀에서 데이터를 취급할 때, "중복 데이터" 가 발생했는지 점검해야할 때가 있다.

이번 글에서는 "중복 데이터" 를 검사하는 2가지 방법을 소개하려고 한다. 개인적으로 경험으로는 데이터 활용/응용 측면에서는 COUNTIF 함수를 사용하는 방법이 좋고, 시각적으로 강조하고 표현한다는 측면에서는 조건부 서식을 이용하는 방법이 더 좋은 것 같다.

(1) COUNTIF 함수 이용하기
(2) 조건부 서식 이용하기

---

(0) 상황 설명

설명을 위해, 가상의 상황을 만들어봤다. 어떤 테스트 결과가 있다. 한번 테스트 할 때 10가지 항목을 검사한다. 그런 테스트를 10번 했다. 각 테스트 결과는 100점 만점으로 표시된다.

결과를 정리하기 위해서, 차시별 결과를 아래와 같이 표에 하나씩 붙여넣었다. 정신없이 일하면서 붙여넣다보니... 실수로 같은 데이터를 중복으로 붙여넣은 것 같다.

중복 데이터를 검사하여, 어느 부분에 실수를 했는지 찾아보자.

(1) COUNTIF 함수 이용하기

일단, 크기가 동일한 빈 표를 하나 더 만든다.

빈 표의 첫번째 빈칸(B14셀)에 아래와 같이 COUNTIF 함수를 적용해준다. COUNTIF 함수의 첫번째 인자에는 B2~K2를 선택하고 [F4]를 3번 눌러서 B열 및 K열에 절대참조를 걸어준다. 두번째 인자에는 B2를 선택해준다. 마지막에 -1 을 넣어준다.

=COUNTIF($B2:$K2,B2)-1

※ 참고.1 : B2~K2 범위에서 B2셀과 같은 값을가진 셀의 갯수를 반환해준다. 범위안에 B2셀 자신도 들어있기 때문에 결과값은 최소한 1 이상이 된다. B2셀 자신 외 중복값이 없다면 결과값은 딱 1이 될 것이다. 그러므로 COUNTIF($B2:$K2,B2) 함수 끝에 "-1" 을 붙여주어, B2셀 자신 외 중복값이 발생할 때부터 1이 되도록 만들어준다.

※ 참고.2 : 항목1 ~ 항목10 까지 해당 함수를 모두 복사할 것이므로, "범위"는 B열~K열로 고정되어야 한다. 그래서 B열과 K열에 절대참조($)를 걸어 $B2:$K2 로 설정하였다.

B14 셀을 복사하여 나머지 모든 빈간에 붙여넣어준다. 그러자, '8차시' 와 '9차시' 시험결과가 중복된 것으로 나타났다.

COUNTIF 함수와 IF 함수를 조합하여 아래와 같이 적용하면, 조금 더 나은 결과를 얻을 수 있다. 아래와 같이 함수를 적용해보자.

=IF(COUNTIF($B2:$K2,B2)-1>0,"중복","")

그러면, 중복된 셀만 "중복" 이라고 뜨고, 중복이 없는 셀은 빈칸으로 남는다.

(2) 조건부 서식 이용하기

항목1의 1차시~10차시 데이터를 선택해준다.

상단 [홈 메뉴] → [조건부 서식] → [셀 강조 규칙] → [중복 값] 순으로 클릭한다.

"중복" 값에 "진한빨강 텍스트가 있는 연한빨강 채우기" 서식이 적용되도록 기본 설정되어 있다. [확인]을 눌러 적용하자.

8차시와 9차시 데이터가 서로 중복되었다는 것을 시각적으로 보여준다. 게다가 COUNTIF 함수 때와 다르게, 중복을 검사하기 위한 별도의 표가 없어도 된다. 이런점은 편한 것 같다.

이제, 항목1에 적용한 조건부 서식을 항목2~항목10 에도 적용해줘야 한다. 애석하게도, 조건부 서식을 "한줄 씩" 적용해줘야 한다.

내가 개인적으로 쓰는 방법을 소개한다. (지극히 개인적인 방법이므로, 이 글을 보시는 분들은 각자 알아서 응용하시기 바람.)

항목1의 전체 데이터를 드래그하여 선택해준다. 그리고, [홈] 탭의 [서식복사]를 클릭해준다.

아래 방향키 (↓) 를 눌러서, 아래 행에도 서식을 적용해준다. 이때부터 [서식복사] + [아래방향키(↓)] 를 반복해서 눌러준다. 그렇게 항목10 까지 서식을 적용해준다.

그러면, 전체적으로 8차시 와 9차시에 중복된 값이 있음을 시각적으로 알 수 있게된다.

---

엑셀 책이나 강의를 보면 중복데이터를 체크하는 여러가지 방법들이 소개되는데, 내 생각에 “다량의 데이터”를 취급할 때 유용한 방법은 "COUNTIF 함수" 와 "조건부 서식" 을 사용한 2가지 방법으로 수렴하는 것 같다. (EXACT 함수도 중복데이터 비교하는 함수이지만, 개인적으로는 COUNTIF 가 더 편했던것같다.)

모쪼록, 데이터에 중복값이 있는지 의심이 드는 분들께, 이 글이 도움이 되길 바란다.



끝.