엑셀 카테고리 묶는법: 태그 목록만 정하면 분류가 자동으로 붙습니다

분류 기준이 담당자마다 다릅니다.

같은 문의가 어제는 배송지연으로, 오늘은 단순문의로 들어갑니다. 월말에 유형별로 세어보면 숫자는 나오는데 그 숫자로 정할 수 있는 게 없습니다.

상담 데이터를 AI로 자동 분류하겠다는 이야기가 먼저 나옵니다. 자동 분류는 붙일 태그 목록이 있어야 돌아갑니다. 목록이 흔들린 채로 엑셀 자동화를 얹으면 흔들린 상태 그대로 빨라집니다. 이 글은 엑셀 데이터 정리 방법 여덟 단계 가운데 다섯 번째 단계를 다룹니다.

엑셀 카테고리 묶는법: 문의 유형표를 만드는 세 가지 작업

문의 유형표를 만드는 일은 목록 정하기, 입력 고정하기, 묶어서 보기 셋으로 나뉩니다.

① 어떤 이름을 쓸지 정합니다. 배송지연인지 배송문의인지 하나로 정합니다. ② 그 이름만 들어가게 입력을 막습니다. 손으로 치면 반드시 다른 말이 섞입니다. ③ 묶어서 봅니다. 여기서 목록이 잘못됐다는 게 드러납니다.

엑셀 카테고리 묶는법을 찾을 때는 대개 ②만 봅니다. 순서로는 ①이 먼저이고, ③이 뒤로 밀리면 안 됩니다. 데이터를 쌓아놓고 나서 분류를 고민하면 이미 쌓인 몇 천 행을 다시 만져야 합니다.

엑셀 카테고리 만드는법: 대분류와 중분류를 두 단으로

시트 하나를 목록 전용으로 씁니다. A열에 대분류, B열에 중분류를 적습니다. 배송 아래에 배송지연·오배송·미배송, 결제 아래에 결제실패·환불요청·부분취소를 둡니다.

입력 시트로 돌아와 유형 열을 잡고 데이터 탭의 데이터 유효성 검사에서 제한 대상을 목록으로 놓습니다. 원본에 대분류 범위를 지정하면 그 열에는 목록에 있는 값만 들어갑니다.

중분류를 대분류에 맞춰 바꾸려면 종속 드롭다운을 씁니다. 대분류별로 이름을 정의해두고 중분류 열의 유효성 검사 원본에 =INDIRECT(A2)를 넣습니다. 배송을 고르면 배송 아래 세 가지만 뜨고, 결제를 고르면 결제 아래만 뜹니다.

여기까지 하면 오타로 갈라지는 유형이 사라집니다. 배송지연과 배송 지연이 두 줄로 세어지는 일이 없어집니다.

카테고리를 몇 개로 할지 정하는 기준

20개로 쪼개면 어느 것도 안 보입니다. 목록을 만들다 보면 계속 늘어납니다. 실무자는 실제로 다른 일이라고 느끼니까 쪼개고 싶어집니다.

기준을 하나만 둡니다. 그 유형의 건수가 달라졌을 때 우리가 다르게 할 일이 있는가. 배송지연이 늘면 물류를 봅니다. 오배송이 늘면 피킹을 봅니다. 이 둘은 나눌 값어치가 있습니다. 그런데 배송지연을 하루지연과 이틀지연으로 나누면 둘 다 물류를 보게 되므로 나눌 이유가 없습니다.

월 문의가 500건이고 유형이 20개면 한 유형에 25건입니다. 25건은 두 달 연속 늘어도 우연과 구분이 안 됩니다. 대분류는 다섯에서 여덟 사이, 중분류는 대분류당 셋에서 다섯 정도에서 시작하고 데이터를 보고 늘립니다.

엑셀 카테고리별 보는법: 묶어놓고 나면 무엇이 보이나

데이터 유효성 검사로 유형 열이 고정되면 피벗테이블은 몇 번의 클릭으로 끝납니다. 행에 대분류, 열에 주차, 값에 건수를 놓습니다.

여기서 한 열을 더 놓습니다. 접수시각과 완료시각의 차이를 처리시간으로 계산해 평균으로 같이 봅니다. 건수가 가장 많은 유형과 처리시간이 가장 긴 유형은 대개 다릅니다. 건수가 많은 쪽은 상세페이지 문구로 줄이고, 처리시간이 긴 쪽은 응대 매뉴얼이나 권한으로 줄입니다. 두 가지 처방이 서로 다릅니다.

건수만 세면 뒤쪽 처방이 안 보입니다.

유형 목록을 고치면 엑셀 자동화도 못 따라옵니다

목록을 고치면 지난 데이터가 안 맞습니다. 세 달쯤 지나면 유형을 손보게 됩니다. 안 쓰는 유형을 지우고 새로 생긴 문의를 넣습니다.

이때 지난 데이터가 옛 이름으로 남아 있습니다. 유효성 검사는 앞으로 들어올 값만 막고 이미 들어간 값은 그대로 둡니다. 목록을 고친 달부터 집계가 끊기고, 분기 비교를 하려면 옛 이름과 새 이름을 잇는 표를 따로 만들게 됩니다.

이름을 언제 바꿨는지도 파일에 안 남습니다. 반년 뒤에 숫자가 튀는 달을 보면 그게 실제 변화인지 유형을 고친 흔적인지 구분이 안 됩니다.

엑셀 자동화를 여기에 얹어도 이 문제는 안 풀립니다. 매크로는 목록이 바뀐 시점을 모릅니다. 행이 몇 천 줄이고 보는 사람이 여럿이면 여기서 엑셀이 버겁습니다. 유형표에 적용 시작일을 붙이고 이름이 바뀐 이력을 남기는 일은 표 한 장으로는 잘 안 됩니다. 저희가 이 부분을 다루는 방식도 결국 이름과 적용일을 따로 관리하고 집계할 때 그 시점의 이름으로 되돌려 세는 것입니다.

계정과목표와 문의 유형표는 같은 물건입니다

둘 다 나중에 세려고 미리 정해두는 이름입니다. 회계에서 계정과목표를 먼저 정하는 이유와 같습니다. 전표를 다 끊어놓고 나서 과목을 다시 나누면 지난 기간과 비교가 안 됩니다. 그래서 과목을 바꿀 때는 언제부터 적용하는지를 같이 적습니다.

문의 유형표도 같은 취급을 받아야 합니다. 그리고 이 표가 정리되면 상담 데이터가 손익과 붙습니다. 반품 사유별 건수는 반품률의 설명이 되고, 불량 문의의 유형별 추이는 품질 비용이 어디서 생기는지 알려줍니다. 유형 이름이 흔들리면 이 연결이 통째로 끊깁니다.

자주 받는 질문

Q. 카테고리는 몇 개가 적당한가요

A. 엑셀 카테고리 만드는법 자체는 어렵지 않고, 어려운 것은 개수입니다. 대분류 다섯에서 여덟, 대분류당 중분류 셋에서 다섯으로 시작합니다. 판정 기준은 개수가 아니라 그 유형의 건수가 달라졌을 때 다르게 할 일이 있는지입니다. 처방이 같은 두 유형은 나누지 않습니다.

Q. 엑셀 대신 상담 관리 프로그램을 쓰면 되지 않나요

A. 프로그램을 써도 유형 목록은 사람이 정해야 합니다. 도구를 바꿔도 이 작업은 남습니다. 오히려 프로그램은 처음 세팅할 때 목록을 넣게 되어 있어서, 목록을 안 정한 채로 시작하면 기본값이 그대로 굳습니다.

Q. 이미 쌓인 데이터의 유형이 제각각인데 다시 붙여야 하나요

A. 전부 다시 붙이지 않습니다. 옛 이름과 새 이름을 잇는 표를 한 장 만들고, 엑셀 카테고리별 보는법대로 피벗을 돌릴 때 그 표를 거치게 합니다. 원본은 그대로 두고 세는 쪽에서만 맞추는 것이 되돌리기 쉽습니다.

Q. 자동 분류를 쓰면 목록이 필요 없지 않나요

A. 반대입니다. 자동 분류는 붙일 후보가 정해져 있어야 돌아가고, 목록이 애매하면 결과도 애매하게 나옵니다. 그리고 정확도를 재려면 사람이 붙인 태그와 대조해야 하는데 그 기준이 목록입니다.

목록을 먼저 정하는 일은 지루하고, 그래서 늘 뒤로 밀립니다. 그래도 이걸 먼저 하는 팀이 나중에 덜 되돌립니다.