엑셀 중복값 제거 방법: 기준 열만 정하면 재문의가 한 건으로 묶입니다

문의 건수가 실제보다 많습니다.

같은 사람이 사흘에 걸쳐 세 번 물어보면 대장에는 세 줄이 남습니다. 월말에 세면 문의가 늘어난 것처럼 보이고, 처리율은 낮아 보입니다.

중복을 지우는 일로 알고 있는데, 실제로 하는 일은 무엇을 같은 건으로 볼지 정하는 것입니다. 이 글은 엑셀 데이터 정리 여덟 단계 가운데 네 번째 단계를 다룹니다.

엑셀 중복값 제거 방법: 기준 열을 먼저 정합니다

중복된 항목 제거를 누르면 어느 열을 볼지 묻습니다. 여기서 무엇을 고르느냐가 결과를 전부 정합니다.

상담 대장에서 쓸 만한 기준은 셋입니다.

① 연락처와 접수일자. 같은 사람이 같은 날 두 번 남긴 것을 한 건으로 봅니다. 가장 안전하고 가장 적게 지웁니다. ② 연락처와 주문번호. 같은 건에 대한 문의는 며칠에 걸쳐도 한 건입니다. 재문의를 잡는 기준입니다. ③ 연락처만. 같은 사람의 모든 문의가 한 건이 됩니다. 고객 수를 셀 때만 씁니다.

②가 대개 맞습니다. 다만 주문번호가 안 적힌 문의가 섞여 있으면 그 줄들이 전부 다른 건으로 남으니, 빈칸이 몇 퍼센트인지 먼저 봅니다.

엑셀 중복값 찾기: 지우기 전에 무엇이 겹치는지 봅니다

지우는 것은 되돌리기 어렵습니다. 순서를 하나 넣습니다.

빈 열에 =COUNTIFS($B:$B,B2,$D:$D,D2)를 넣습니다. B가 연락처, D가 주문번호입니다. 값이 2 이상인 줄이 겹치는 줄입니다.

조건부서식의 중복 값 규칙으로 색을 입혀 눈으로 훑어도 됩니다. 다만 조건부서식은 열 하나만 보므로, 두 열을 함께 봐야 하면 위 수식이 맞습니다.

엑셀 중복값 찾기를 먼저 해두면 지운 뒤에 몇 줄이 사라졌는지 설명할 수 있습니다.

엑셀 중복값 갯수를 세면 재문의율이 나옵니다

여기서 숫자 하나가 나옵니다.

전체 줄 수에서 고유 건수를 빼면 재문의 건수입니다. 고유 건수는 =SUMPRODUCT(1/COUNTIFS($B:$B,$B2:$B500,$D:$D,$D2:$D500))으로도 되고, 365를 쓰면 =ROWS(UNIQUE(B2:D500))이 짧습니다.

재문의 건수를 전체로 나눈 값이 재문의율입니다. 이 숫자가 달마다 어떻게 움직이는지가 상세페이지나 안내 문구를 고쳤을 때 효과가 드러나는 숫자입니다.

엑셀 중복값 추출: 지우지 말고 따로 빼둡니다

원본에서 지우면 나중에 누가 왜 지웠는지 못 봅니다.

고급 필터를 열어 다른 장소에 복사를 고르고 동일한 레코드는 하나만을 켭니다. 원본은 그대로 두고 정리본이 따로 생깁니다. 365라면 =UNIQUE(A2:F500)을 새 시트에 한 번 넣는 것으로 끝납니다.

집계는 정리본에서 하고, 원본은 접수 이력으로 남깁니다. 재문의 몇 건이 어떤 유형에서 나왔는지 보려면 원본이 있어야 합니다.

완전히 같지 않은 중복은 수식이 못 잡습니다

연락처를 어떤 사람은 하이픈을 넣고 어떤 사람은 안 넣습니다. 같은 사람인데 두 줄로 남습니다.

=SUBSTITUTE(B2,"-","")로 표기를 맞추면 이 종류는 잡힙니다. 그런데 진짜 문제는 다른 쪽입니다. 어제 배송을 물어본 사람이 오늘 환불을 물어보면 연락처도 주문번호도 같은데 다른 문의입니다. 반대로 주문번호를 안 적고 다시 문의한 것은 같은 건인데 다른 줄로 남습니다.

이건 표기 문제가 아니라 내용을 읽어야 아는 문제라 수식이 닿지 않습니다. 규칙으로 잡히는 데까지 잡고, 남는 줄이 몇 퍼센트인지 세어 두는 편이 낫습니다. 그 비율이 크면 문의 내용을 함께 보고 판단하는 단계가 필요해집니다.

재문의율은 응대 원가로 환산됩니다

재문의율은 응대 시간으로 바로 환산됩니다.

한 건이 두 번 들어오면 상담사 시간도 두 번 듭니다. 월 500건에서 재문의가 20%면 100건이 다시 온 것이고, 건당 응대가 5분이면 매달 8시간이 같은 일에 들어갑니다. 이 8시간은 인력 충원 얘기가 나올 때 근거가 됩니다.

그리고 재문의가 몰리는 유형을 알려면 유형이 먼저 정리돼 있어야 합니다. 태그 목록을 정하는 순서를 같이 보면 이 숫자가 훨씬 쓸모 있어집니다.

자주 받는 질문

Q. 중복된 항목 제거를 쓰면 어느 줄이 남나요

A. 위에 있는 줄이 남고 아래가 지워집니다. 접수일자 오름차순으로 정렬해두면 첫 문의가 남고 재문의가 지워집니다. 반대로 최근 상태를 남기려면 내림차순으로 정렬한 뒤에 실행합니다.

Q. 엑셀 중복값 찾기와 제거 중 무엇을 먼저 하나요

A. 찾기가 먼저입니다. 몇 줄이 겹치는지 보고 그 비율이 예상과 다르면 기준 열이 잘못된 것입니다. 바로 제거하면 잘못된 기준으로 지운 뒤에야 알게 됩니다.

Q. 재문의를 지우면 문의 건수가 줄어드는데 실적이 나빠 보이지 않나요

A. 두 숫자를 같이 씁니다. 접수 건수는 원본에서, 실제 문의 건수는 정리본에서 냅니다. 둘의 차이가 재문의이고, 그 차이를 줄이는 것이 CS 팀의 성과입니다.

Q. 엑셀 중복값 갯수가 매달 크게 흔들리는데 정상인가요

A. 기준 열이 흔들리는 경우가 많습니다. 주문번호 빈칸 비율이 달마다 다르면 재문의율도 같이 움직입니다. 빈칸 비율을 같이 기록해두면 숫자가 튄 이유를 바로 구분할 수 있습니다.

기준 열을 정하는 데 십 분이 걸리고, 안 정하면 매달 같은 숫자를 의심하게 됩니다.