엑셀 중복값, 중복데이터 찾기

엑셀에서 중복값을 찾는 방법을 한방에 해결해 봅시다. 엑셀은 데이터를 입력하고 관리하는 대표적인 직장인들의 좋은 툴이죠.  하지만, 데이터를 입력하다 보면 중복 입력하여 중복값을 찾아야 하는 경우가 종종 있죠. 

 

이번에는 중복값을 찾는 방법을 한방에 해결해 보겠습니다. 

 

첫 번째, [Ctrl]+F 기능 활용

두 번째, 조건부 서식 이용하기

세 번째, COUNTIF함수와 EXACT함수 이용하기

 

 

---

엑셀 중복 값 찾기

 

엑셀에서 중복 값을 찾는 방법은 3가지가 있습니다. 

하나씩 살펴보겠습니다. 

 

1. [Ctrl]+F 기능 활용

 

① 찾고 싶은 영역(데이터)을 선택하고, 

② 키보드에서 [Ctrl]+F를 동시에 누릅니다. 

참고로, [Ctrl]+F는 "찾기 및 바꾸기"의 단축키입니다. 

③ 찾을 내용에 찾고 싶은 단어를 입력합니다. 

 

엑셀 중복값 찾기

 

④ "모두 찾기" 버튼을 클릭하면,

⑤ 내가 찾을 내용과 동일한 데이터가 있는 위치가 모두 나타나게 됩니다. 

 

엑셀 중복값 찾기

 

하나씩 클릭하면 해당 위치로 이동하니까, 수정하면 되겠죠^^

 

 

---

2. 조건부 서식 이용하기

 

2번째 방법으로는

조건부 서식을 이용하는 것입니다. 

참고로, 조건부 서식은 참 많은 기능이 있는데 자세한 것은 다음에 하고, 오늘은 중복 값 찾기에만 집중할게요^^

 

① 중복 값이 있는지 확인할 영역(데이터)을 선택합니다. 

② 메뉴에서 [조건부 서식]→[셀 강조 규칙]→[중복 값]을 선택합니다. 

 

엑셀 중복값 찾기

 

③ 중복 값이 있을 때 표시할 방법을 선택합니다. 

아래 사례는 빨강 텍스트와 채우기를 선택한 결과입니다. 

 

엑셀 중복값 찾기

 

이렇게 하면 중복 값이 한눈에 들어오겠죠^^

하지만, 이 방법은 데이터가 몇 개 없을 때 유용해요~

데이터 숫자가 많으면 다음 방법을 활용하길 추천드립니다. 

 

 

---

3. 함수 이용하기

 

중복 값을 찾아야 할 비교 데이터가 많으면 함수를 이용하는 것이 편합니다. 

대표적으로 COUNTIF함수와 EXACT함수가 있는데 두 함수의 용도가 조금 다릅니다. 

COUNTIF함수는 중복된 값의 개수를 나타내는 함수로 몇 번 중복되었는지를 확인할 때 사용합니다. 

 

반면, EXACT함수는 중복된 값을 찾을 수 있는데, 동일한 값이 2개 이상 있다면 2번째부터 중복으로 표시해 줍니다. 

(값이 하나면 중복이 아니니까요^^)

 

하나씩 살펴보겠습니다. 

 

COUNTIF함수로 중복 값 개수 찾기

 

준비작업

COUNTIF함수를 쓰기 위해서는 준비작업이 필요한데. 바로 기준 데이터를 만드는 것입니다. 

① 아래와 같이 데이터가 있다면 ② 데이터에서 언급된 항목 값 "치킨", "피자", "떡볶이", "곱창"을 하나의 표로 만들어 줍니다. 

비교할 대상을 만들어 주는 것입니다. 

 

엑셀 중복값 찾기

 

이제 중복 값이 몇 개인지 확인해 봅시다. 

 

① 중복 값 개수를 입력할 셀을 선택하고, 함수를 입력합니다. 

함수 마법사에서 COUNTIF함수를 선택하거나, 함수 입력창에 "=COUNTIF"를 입력하시면 됩니다. 

 

② 비교할 영역(Range)을 입력합니다. 

우리가 중복 값이 있는 데이터 영역(D3:D22)을 입력합니다. 

다음 비교할 값(Criteria) F3을 입력합니다. 

함수 식으로는 "=COUNTIF(D3:D22, F3)"입니다. 

엑셀 중복값 찾기

 

 

 

이런 식으로 하면 아래와 같은 결과물이 나올 겁니다. 

"곱창"은 3번 중복됐다는 말이겠죠~

그런데 중복수를 이야기하려면 최초 1개는 빼야겠죠?

그러니 이 숫자에서 1을 빼고 중복 데이터가 2번 있다라고 해석해야 합니다. 

(원래 COUNTIF함수라는 함수가 중복 데이터를 찾는 목적이 아니고, 같은 데이터의 개수를 찾는 함수라서 약간 불편함이 있어요 ㅠ.ㅠ)

엑셀 중복값 찾기

 

EXACT함수로 중복 값 찾기

 

EXACT함수는 중복 값을 찾아주는 함수입니다. 

하지만 EXACT함수는 기본적으로 위/아래의 셀만 비교를 해주기 때문에 약간의 준비작업이 필요한데요

준비작업은 아래 그림과 같이 순서를 나타내는 "ID"셀과 중복 값을 넣을 "중복 여부"셀을 만들어 줍니다. 

 

엑셀 중복값 찾기

 

① 그리고, 비교할 값이 있는 셀을 오름차순으로 정렬해줘야 합니다. 

[마우스]+우클릭 또는 [메뉴]→[정렬]→[오름차순 정렬] 하면 됩니다. 

엑셀 중복값 찾기

 

② 중복 여부 셀을 선택하고, EXACT 함수식을 입력합니다. 

비교할 항목이 있는 셀의 값을 가지고 함수식은 "=EXACT(D2, D3)"입니다. 

나머지 비교할 항목도 동일한 방식으로 입력합니다. 

엑셀 중복값 찾기

 

아래와 같은 결과가 나오게 되죠. 

원리는 바로 위에 데이터 값과 아래 데이터 값을 비교해서, 동일하면 TRUE로 나오고, 다르면 FALSE로 나오게 됩니다. 

TRUE는 중복 값이고, FALSE는 중복되지 않은 값이 되겠죠~

엑셀 중복값 찾기

 

③ 이제 중복 값인 "TRUE"만 뽑아내야겠지요~

우선, 중복 여부의 값을 복사(Ctrl+C) 하고, 마우스+우클릭한 후 "선택하여 붙여 넣기"를 통해 값만 붙여 넣습니다. 

그렇게 되면 함수 식이 아니라 "TRUE"나 "FALSE"라는 문자만 남게 됩니다. 

엑셀 중복값 찾기

 

④ 이제 데이터를 정리하겠습니다.

[Ctrl+F]를 누른 후, FALSE를 ""으로 바꿔 줍니다. 

중복되지 않은 데이터는 빈칸으로 만듭니다. 

 

⑤ [Ctrl+F]를 누른 후, TRUE를 "중복"으로 바꿔 줍니다. 

중복된 데이터는 표시하는 겁니다. 

엑셀 중복값 찾기

 

⑥ 끝으로 데이터의 순서를 원상복구 해야겠죠. 

처음 데이터와 같이 [ID] 셀을 [오름차순]으로 정렬합시다. 

그러면 중복 여부 셀에 중복데이터는 2번째부터 "중복"이라고 표시되는 것을 확인할 수 있죠?

1번째로 등장한 데이터는 "처음" 등장한 데이터이기 때문에 중복데이터라고 볼 수 없으니 "빈 공간"으로 나오게 됩니다. 

엑셀 중복값 찾기

 

텍스트를 제거하는 포스팅은 아래를 참조하세요

2022.03.24 - [보고서 작성기술/엑셀] - 엑셀 중복값 제거하기