[엑셀팁] 중복값을 찾는 4가지 방법

직장인은 숙명적으로 데이터를 다루어야 하는 직업입니다. 100건 이하의 스몰 데이터에서부터 수천, 수만 건에 이르는 빅 데이터를 다루어야 하는 경우도 생기게 되는데요. 불규칙적으로 연속되는 숫자를 분석하며 희열을 느끼는 분들은 잘 모르시겠지만 숫자만 봐도 이마의 고열을 느끼는 일반 직장인들에게는 곤욕이 아닐 수 없습니다. 

한 번은 여러 번 확인 후 보고한 데이터에 문제가 있다는 지적을 받은 적이 있습니다. 모든 것이 완벽했지만 가장 근본적인 문제, 원자료(raw data) 가공에 문제가 있었던 것이었어요. 여기저기서 데이터를 끌어다 붙이면 생기는 문제, 바로 중복값 때문에 생긴 오류였습니다. 데이터 량이 많으면 많을수록 중복값을 골라내는 데에는 큰 어려움이 존재하거든요. 그래서 준비했습니다. 완벽하게 중복값을 발견하는 3가지 방법입니다. 

※ 주의: 중복값을 완벽히 제거했다는 업무상 자기만족은 느낄 수 있지만 칭찬을 기대하진 마세요. 당연한 일이거든요…추욱

예시를 위해서 효성의 크레오라 제품명으로 방대한 양의 데이터 파일을 만들었습니다. 무려 12가지나 됩니다. Data1과 data2의 숫자들은 난수로 생성한 데이터입니다. 아무 의미 없어요.

중복값을 찾아보세요. 총 3개의 중복 데이터가 포함되어 있습니다. 

 방법1: 한 번에 다 지워주는 ‘중복된 항목 제거’

많은 분들이 활용하고 있는 방법이죠? 메뉴>데이터>데이터 도구에서 쉽게 중복된 항목 제거 기능을 찾으실 수 있어요. 일단 기능을 실행하고 나면 데이터가 자연적으로 지워지기 때문에 실행을 완료하기 전엔 꼭 삭제해도 되는 데이터가 맞는지 확인해야 합니다. 

메뉴의 버튼이 잘 안보이시죠? 데이터도구 메뉴만 크게 볼까요? 어떤 아이콘인지 잘 보이시죠? 

실행하기 전에 꼭 중복값을 비교할 열의 셀을 선택한 후에 아이콘을 클릭해주세요. 그럼 실행을 해볼까요?

중복값을 확인할 열을 선택하세요. 하나 이상을 선택하면 됩니다. 저는 제품명에 대한 중복값만을 체크해보겠습니다. 

1개의 중복값이 제거 되었습니다. 원자료와 비교해보면 음..제가 지우고 싶었던 것과는 차이가 있습니다. 제거되지 않은 데이터도 보이네요. 이유가 뭘까요?

9번 데이터만 삭제되었군요. 9번은 정확히 지우고 싶은 데이터가 확실합니다. 그런데 혹시 찾으셨어요? 2가지 중복값이 더 있는데요. 11번과 12번이에요. 11번은 ‘creora® highclo™’와 ‘creora®highclo™’, 12번은 ‘creora® eco-soft‘와 ‘creora® eco-soft(공백)’,  즉 띄어쓰기 차이에 의해서 중복값으로 분류되지 않았네요.

여러 사람한테서 얻은 데이터는 제아무리 같은 제품명으로 표기했다 하더라도 조금씩 차이가 있을 수 있으니 바로 적용해서는 안 되겠군요. 그럼 다음 방법으로 이동합니다. 

 방법2: 눈에 보이는 ‘조건부 서식’

보통 조건부 서식은 특정 값보다 크거나 작은 경우, 또는 같은 경우를 표시하는 기능이죠. 이 중에서 같은 값을 표시하면 중복값을 눈으로 확인할 수 있어 유용합니다.

조건부 서식을 적용할 셀이나 열을 선택하여 기능을 사용하시면 되는데요, 아래 이미지처럼 ‘셀 강조 규칙>중복값’을 선택합니다. 

중복값을 표시하는 서식을 지정하는 팝업 창이 하나 뜨면 별다른 변경 없이 확인을 눌러주세요. 찾으면 바로 지울 데이터이니까 표시 서식은 중요하지 않거든요.

아쉽게도 1단계, 중복된 항목 제거 기능과 같은 결과입니다. 하지만 두 가지 기능은 수행 결과를 컨트롤 할 수 있다는 점에서 조금 다릅니다. 조건부 서식의 경우엔 중복되는 값을 모두 표시하기 때문에 위쪽의 데이터를 지울지, 아래쪽의 데이터를 지울지 사용자가 결정을 할 수 있어요. 어찌 되었건 모두 중복값을 모두 거르진 못했으니 다음 방법으로 가보겠습니다. 

 방법3: 하나하나 확인하리라! ‘필터-오름차순 정렬’

보고 싶은 항목의 데이터만 볼 때 사용하는 필터 기능 중에서 오름차순이나 내림차순 정렬을 활용해서 중복값을 찾을 수 있습니다. 여기까지만 들으셔도 어떻게 하는 줄 아시겠죠? 데이터 상단의 항목을 선택한 후 ‘메뉴-데이터-정렬 및 필터’에서 필터를 클릭하세요. 그리고 제품명으로 오름차순 정렬을 하시면 똑같은 데이터가 보이실 거예요.

지금은 12개뿐이지만 더 많은 데이터일 경우 눈으로만 찾기엔 힘드실 거예요. 이럴 때 위의 2단계 조건부 서식을 함께 사용하시면 더 빠르게 찾을 수 있습니다. 

결과를 살펴보면 중복값 3개 중 2가지를 찾은 것을 확인하실 수 있는데요. 그럼 나머지 하나는 어떻게 된 거죠? 1번과 11번 항목의 띄어쓰기 차이는 오름차순 정렬로도 찾아내기가 쉽지 않습니다. 그럼 이제 마지막 방법으로 넘어가 보겠습니다. 

 방법4: 마무리는 역시 ‘피벗’

드디어 마지막 단계! 확실히 중복값을 찾아보기 위해서 엑셀의 꽃, 피벗 기능을 활용할 예정입니다. 피벗은 각 항목별로 개수나 평균, 합계 등을 한눈에 확인할 수 있는 기능입니다. 가장 기본이 되는 기능만을 활용하여 중복값을 찾아보겠습니다. 

먼저 데이터가 있는 위치 아무 곳의 셀을 선택하고 메뉴>삽입에 있는 ‘피벗테이블’을 클릭하세요. 표 또는 범위가 올바르게 설정되었는지 확인하고 새 워크시트에서 열기를 클릭하면 새로운 시트에 피벗테이블이 생길 거예요. 왼쪽 필드에서 제품명을 마우스로 끌어 행에 넣어주시고, 나머지 필드 중 하나를 시그마 값에 끌어넣어주세요. 

행 레이블 중 creora®과 그 뒤의 단어 사이에 띄어쓰기가 되지 않은 것이 눈에 확 들어오는군요. 또 creora® eco-soft라는 항목이 두 개나 보이네요. 이상합니다. 우리는 답을 알고 있잖아요. Eco-soft 뒤에 공백이 있었다는 것을요. 원데이터에서 이 두 가지 부분을 찾아서 수정 후 새로고침 해보겠습니다. 

이제 좀 확실해진 것 같은데요. 중복값 3가지를 모두 찾으셨나요? 사업분야의 개수가 2로 표시되는 곳 세 곳입니다. 역시 피벗입니다. 비교하기도 쉽고 찾아내기도 쉽네요. 

지금까지 데이터에서 중복값을 찾아보았는데요. 역시 중간에 끼인 공백은 찾기가 쉽지 않습니다. 하지만 소개해 드린 4가지 방법을 단계별로 적용하면 세상에 못 찾을 중복값은 없을 거예요. ㅎㅎ 직장인 여러분의 즐거운 엑셀 생활에 도움이 되었으면 좋겠습니다. 다음엔 더 유용한 팁으로 찾아뵐게요~