많은 양의 엑셀 자료에서 중복되는 값을 찾거나 제거할 수 있는 방법은 여러 가지가 있습니다.
예전에 고객의 이름과 상호 및 주소가 담긴 주소록 데이터나 부품종류 및 상태 등을 정리한 부품 데이터를 관리한 적이 있습니다.
이러한 가공되지 않은 데이터들에는 중복 값이 존재하며 이를 수작업으로 찾아내기 위해서는 정확성도 떨어질 뿐만 아니라
시간도 많이 소요됩니다.
주소록 같은 불특정 다수에게서 수집된 데이터를 사용하려면 반드시 데이터를 가공해야 하며 이때 중복 값 제거는 필수로 거쳐야 할 과정입니다. 중복 값 제거를 위한 여러 가지 방법을 소개해보겠습니다.
- Ctrl + F를 사용한 검색
- 중복된 항목 제거
- 조건부 서식 - 중복 값
- 조건부 서식 + 텍스트 합치기 함수
- Countif, Countifs 함수
Ctrl + F를 사용한 검색
엑셀 워크시트에서 내가 원하는 데이터를 찾거나 바꾸는 찾기 및 바꾸기 기능을 사용합니다.
찾기 및 바꾸기 기능의 단축키 (Ctrl + F)를 사용하여 중복 여부를 검색하고 싶은 값을 입력합니다.
찾기 및 바꾸기 창에서 찾을 내용에 중복 여부를 확인하고 싶은 값을 입력한 뒤 모두 찾기를 클릭합니다.
위와 같이 해당 값이 입력되어있는 모든 셀에 대한 위치와 값이 표시됩니다.
중복된 항목 제거
중복된 항목 제거 기능을 사용하여 선택한 열에 있는 중복된 데이터들을 삭제할 수 있습니다.
위와 같이 중복된 항목을 제거할 열을 선택한 뒤 데이터 - 중복된 항목 제거를 선택합니다.
선택한 영역 옆에 데이터가 있을 경우 위와 같은 경고창이 나타납니다.
선택 영역 확장 : 선택 영역을 옆의 데이터까지 확장
B열과 C열에 있는 데이터를 기준으로 제거할 것이므로 NO열의 체크박스를 해제합니다.
위와 같이 B열과 C열의 데이터가 모두 같은 13번 데이터(A-707, 폐기)가 제거됩니다.
현재 선택 영역으로 정렬 : 현재 선택한 데이터에서만 중복 값 제거
위와 같이 선택한 영역에 있는 데이터에서만 중복 값이 제거됩니다.
조건부 서식
조건부 서식 기능을 사용하여 중복 값이 들어있는 셀을 찾아낼 수 있습니다.
데이터 범위를 지정한 뒤 조건부 서식 - 중복 값을 선택합니다.
위와 같이 중복되는 값이 있는 셀의 위치에 서식이 적용되어 찾아낼 수 있습니다.
그렇다면 선택영역을 B열이 아닌 B~C열로 변경하면 어떻게 될까요?
B열과 C열에서 각각의 중복 값만 찾기 때문에 위와 같은 결과가 나옵니다.
이렇게 다수의 열에 있는 데이터의 중복 값을 확인할 땐 텍스트 합치기 함수를 사용하면 됩니다.
조건부 서식 + 텍스트 합치기(CONCATENATE 함수)
다수의 열에 데이터가 존재할 때 텍스트를 합쳐주는 CONCATENATE 함수를 활용하여 중복 값을 찾아낼 수 있습니다.
B열과 C열의 데이터를 합칠 열을 추가로 생성합니다.
CONCATENATE 함수를 사용하여 B열과 C열의 데이터를 합칩니다.
=CONCATENATE(합칠 텍스트 1, 합칠 텍스트 2....)
합친 데이터의 범위를 지정하고 조건부 서식 - 중복 값을 선택하면 위와 같이
B열과 C열의 값이 모두 같은 경우의 중복 값이 표시됩니다.
COUNFIF, COUNTIFS 함수
엑셀의 COUNTIF함수는 COUNT함수와 IF함수를 합친 함수로 특정 조건을 만족하는 경우 COUNT 되는 함수입니다.
COUNTIF 함수를 사용하여 중복 값을 찾아낼 수 있습니다.
COUNTIF(range, criteria)
– range : 조건을 적용할 범위
– criteria : 개수를 세는 조건
COUNTIF 함수의 range에는 중복 값을 검출할 셀 범위를 입력하고
criteria에는 조건이 들어가야 하므로 검색하고자 하는 셀의 위치를 입력합니다.
위와 같이 결괏값이 1이 아닌 경우 범위 내에 중복 값이 존재하는 것임을 알 수 있습니다.
그렇다면 다수의 데이터가 일치하는 경우에 대한 중복 체크를 하려면 어떻게 해야 할까요?
COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2,...)
– criteria_range : 조건을 적용할 범위
– criteria : 개수를 세는 조건
COUNTIF 함수는 하나의 조건만 가지고 COUNT 할 수 있지만
COUNTIFS 함수는 특정 다중 조건이 일치할 때 COUNT 됩니다.
B열과 C열의 조건을 모두 확인할 것이므로 range1에는 B열, range2에는 C열의 범위와 셀의 위치를 입력합니다.
위와 같이 다수의 조건을 만족하는 셀의 개수(중복 값)를 확인할 수 있습니다.
알아두면 유용한 엑셀 지식iN
2020/10/27 - [엑셀 지식iN] 엑셀의 셀에 콤보박스 만드는 방법은?
최근댓글