오늘은 엑셀 중복값 제거에 대해서 알아보려고 합니다.
좀 더 정확히 말하면 중복된 데이터가 없도록 만드는 것입니다.
예를들어 침대, 침대, 의자, 세탁기, TV, 침대 이렇게 데이터들이 나열되어 있을 때 침대가 세번 언급되어 중복되어 있습니다.
중복을 제거하면 침대, 의자, 세탁기, TV 이렇게 되는 것입니다.
엑셀에서는 보통 데이터들을 표에 넣어 다루기 때문에 중복을 검사할 부분이 표의 특정 열 하나만 일 수도 있고 연관된 여러 열들을 합쳐서 검사할 수도 있습니다. 쉽게 말해서 핵심은 표의 열을 기준으로 중복을 검사한다는 것입니다.
그럼 구체적인 예를 들어 설명하겠습니다.
엑셀 중복값 제거
다음과 같이 3 개의 열을 갖는 표가 있을 때 중복을 제거하려고 합니다. 이 때 검사 기준을 명확히 해야하는데 우선 NO(일련번호)는 당연히 제외되야 하겠죠.
그럼 들여온 물품과 구체적인 품명 중 어떤 열에서 중복을 검사할지 정해야 합니다. 우선 들여올 물품에 해당하는 열에 대한 중복값 제거를 해보겠습니다.
- 들여올 물품에 대한 중복값 제거
데이터 -> (데이터도구)의 중복된 항목 제거 리본메뉴를 선택하면 다음과 같은 메뉴가 나옵니다.
너무 간단한가요? 3개의 열에서 중복값을 제거할 열에 해당하는 것에만 체크를 하고 나머지는 체크를 해제해줍니다. 그럼 확인을 눌러 확인해보겠습니다.
가구, 전자제품, 과일이 하나씩만 보입니다. 의도대로 중복값을 제거했지만 몇가지 문제가 있습니다.
첫 째로 일련번호까지 그대로 제거되어 일련번호가 제역할을 못하는 군요.
둘 째로 들여올 물품에 해당하는 열이 대분류라면 구체적인 품명이 소분류가 됩니다. 따라서 중복값을 제대로 제거하려면 소분류에 따라서 제거하는게 이치적으로 맞습니다.
우선 Ctrl + Z를 눌러 중복값 제거를 되돌린 뒤 앞에서 했던 작업을 약간 변경해서 다시 해보겠습니다.
문제점 해결
보통 일련번호를 마우스로 자동채우기로 하실 겁니다. 이 방법으로 일련번호를 만들면 중간에 행이 삭제될 경우 자동으로 일련번호가 다시 매겨지지 않아서 이가 빠진것 처럼 보이겠죠.(위 결과처럼 말이죠.)
따라서 일련번호를 숫자의 증가패턴으로 자동채우기를 하지말고 다음과 같은 수식을 만들어 자동채우기를 해보겠습니다.
=ROW(C3)-2
ROW 함수는 현재 셀의 행번호를 반환해 줍니다. 표에서 일련번호의 시작행이 3행이므로 -2를 해주면 1이됩니다. 자동채우기를 하면 나머지 행들도 일련번호가 매겨집니다.
이제 앞에서 했었던 작업, 중복된 항목 제거 리본을 선택해서 중복값을 제거할 열을 선택해보겠습니다.
일련번호에 대한 열만 체크 해제를 한 후 결과를 확인해 보겠습니다.
들여올 물품과 구체적인 품명 두 열이 동시에 일치하는 값으로 중복값을 검사해서 2개의 행이 지워졌습니다.(가구/의자, 가구/세탁기에 해당하네요.)
그리고 앞에서와 달리 일련번호가 자동으로 수정되었습니다.
사실 일련번호를 매기는 수식으로 COUNTA 함수를 사용하는 방법이 더 많이 쓰입니다.
=COUNTA($C$3:C3)
왜냐면 ROW 함수를 사용하는 방법은 수식의 특성상 표를 다른 위치로 옮기게 되면 수식을 수정해야 할 수도 있기 때문입니다. 그런데 안타깝게도 COUNTA 함수는 중복값 제거시에 문제가 생깁니다. 궁금하시면 어떤 문제가 생기는지 직접 실험해 보시길 바랍니다. 지금까지 엑셀 중복값 제거에 대해서 알아보았습니다.