엑셀 중복 값 처리의 모든 것: 찾기부터 삭제까지

엑셀을 사용하다보면 데이터 처리에 있어서 중복값이 문제되는 경우가 종종 있습니다. 예를들면 고객이나 환자의 방문일을 조사한 표가 다음과 같이 있다고 하겠습니다. 

 

위와 같이 고객 데이터에서 한 사람이 두 번 입력되어 있다면, 실제보다 고객 수가 많아 보이게 됩니다. 또한, 중복 데이터는 불필요한 저장 공간을 차지하여 파일 크기를 늘리고 처리 속도를 저하시킵니다. 따라서 정확한 분석과 효율적인 데이터 관리를 위해 중복 값 처리는 필수적인 과정입니다. 이번 포스팅에서는 중복값 찾기와 제거에 대해서 알아보도록 하겠습니다.

중복값 찾기 - 조건부 서식 활용

조건부 서식은 중복 값을 시각적으로 빠르게 확인할 수 있는 유용한 도구입니다. 사용 방법은 다음과 같습니다.

중복 확인 기준 및 범위 선택 → '홈' 탭 → '조건부 서식' → '셀 강조 규칙' → '중복 값' → 색상 선택

상세한 설명은 아래와 같습니다.

예시 고객 데이터

먼저 중복을 확인할 기준 데이터를 선정하고 (등록번호나 이름이 좋겠죠), 그 범위를 선택합니다. 데이터가 많다면 A열을 전체선택하는 것이 좋습니다. 여기서는 데이터가 많지 않기 때문에 A2:A8까지 수동선택하였습니다.

홈' 탭

홈' 탭 → '조건부 서식' → '셀 강조 규칙' → '중복 값'

다음으로 상단의 홈탭으로 가면 우측에 조건부 서식이 보이는데, 여기서 [ '셀 강조 규칙' → '중복 값']을 선택합니다. 

색상선택

그럼 위와같은 박스가 나오면서 벌써 중복값이 빨간색으로 표기가 되었습니다. 색깔을 바꾸려면 빨간 박스를 눌러서 다른 색상으로 바꿀 수 있습니다. 

중복값 제거 - 데이터 중복제거기능사용

엑셀에는 중복 값을 쉽게 제거할 수 있는 내장 기능이 있습니다. 이 기능을 사용하는 방법은 다음과 같습니다.

임의 데이터 혹은 범위 선택 → '데이터' 탭 → '중복 된 항목 제거' 클릭 → 중복 제거 기준 열 선택 → '확인'

임의 데이터 혹은 범위 선택 → '데이터' 탭 → '중복 된 항목 제거' 클릭 → 중복 제거 기준 열 선택 → '확인'

- 그림과 같이 중복을 제거하고 싶은 데이터가 있는 임의 셀 또는 범위를 선택합니다.
- '데이터' 탭으로 이동한 후, '중복된 항목 제거' 버튼을 클릭합니다.
- 4번과 같이 중복 제거의 기준이 될 열을 선택할 수 있는 창이 나타납니다. 원하는 열을 선택한 후 '확인'을 클릭합니다.
이 과정을 거치면 선택한 기준에 따라 중복된 행이 제거되고, 고유한 값만 남게 됩니다. 

여러 열에 걸친 중복 처리

실제 업무에서는 한 열이 아닌 여러 열의 조합으로 중복을 판단해야 하는 경우가 많습니다. 예를 들어, 고객 데이터에서 이름과 전화번호가 모두 같은 경우에만 중복으로 처리하고 싶을 수 있습니다. 이런 경우 열을 여러개 선택하면 두 기준열이 모두 중복일 때만 중복처리가 됩니다. 

예를들면 그림에서 박병이 고객은 3행과 8행에 동시에 존재하지만 방문일은 각각 다른날짜입니다. 반면에 김갑일 고객은 방문일도 같아서 완전한 중복데이터입니다. 이런경우에 중복 값 제거에서 등록번호와 방문일을 동시에 선택하고 확인을 누르면 김갑일 고객의 중복값만 제거됩니다. 

제거된 결과

Countif 함수 응용

더 세밀한 제어가 필요한 경우 COUNTIF 함수를 사용할 수 있습니다. 이 함수는 특정 조건을 만족하는 셀의 개수를 세는 기능을 합니다. 중복 값 찾기에 활용하는 방법은 다음과 같습니다.

=COUNTIF($A$1:$A$100, A2)>1

이 공식은 A1 셀의 값이 A1부터 A100 범위에서 1번을 초과하여 나타나는지 확인합니다. 범위는 각자의 데이터에 맞게 조절하면 됩니다. 결과가 TRUE라면 해당 값은 중복입니다. 등록번호를 기준으로 하기 위해 위 그림에서는 A2를 조건으로 사용하였습니다. 

이 방법의 장점은 중복 여부를 새로운 열에 표시할 수 있다는 것입니다. 새로운 열에 표시하면 뭐가 좋을까요? 나중에 필터링이나 추가 분석이 가능해집니다. 예를 들어보겠습니다.

Countif로 변수 두개에 각각 중복을 파악하고 필터링하기

D2와 E2의 함수식은 위에서 설명한대로 Conuntif를 사용하여 아래와 같이 작성하였습니다. 

D2: =COUNTIF($A$1:$A$100, A2)>1 
E2: =COUNTIF($C$1:$C$100, C2)>1

그리고 등록번호와 방문일이 동시에 중복일 때만 완전중복으로 인식하기 위해 And를 이용한 함수식은 위 그림에 빨간 밑줄로 표시되어 있습니다. 이렇게 함으로써 김갑일 고객만이 등록번호와 방문날짜가 동시에 중복된 케이스임을 알 수 있습니다.

Countifs로 여러 기준을 동시에 필터링하기

예를 들어, A열에 이름, B열에 전화번호, C열에 이메일 주소가 있다고 가정해보겠습니다. 이 세 가지 정보가 모두 동일한 경우에만 중복으로 처리하고 싶다면 다음과 같은 함수를 사용할 수 있습니다.

=IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2,$C$2:$C$1000,C2)>1,"중복","고유")

Countifs로 여러 기준 동시 필터링하기

 

함수의 작동 방식은 다음과 같습니다.
-COUNTIFS 함수는 A, B, C 열의 각 값이 모두 일치하는 행의 개수를 셉니다.
-그 개수가 1보다 크면 (즉, 2개 이상이면) "중복"이라고 표시합니다.
-1이면 (즉, 유일하면) "고유"라고 표시합니다.

이 방법을 사용하면 여러 열의 정보를 모두 고려하여 중복을 정확하게 식별할 수 있습니다. 특히 대량의 데이터에서 복잡한 중복 조건을 처리할 때 유용합니다.

중복 값 처리는 데이터 정리의 기본이면서도 매우 중요한 기술입니다. 이 글에서 소개한 다양한 방법들을 활용하면 데이터의 정확성을 높이고, 분석의 질을 향상시킬 수 있습니다. 처음에는 어렵게 느껴질 수 있지만, 조금씩 연습하다 보면 곧 엑셀 데이터 정리의 달인이 될 수 있을 것입니다. 실제 업무에서 이러한 기술을 적용해보면서, 여러분만의 노하우를 쌓아가시기 바랍니다