엑셀 키워드 매칭: 사전 한 장만 만들면 태그가 저절로 붙습니다

태그를 손으로 답니다.

문의 내용을 읽고 배송인지 환불인지 판단해 유형 열에 적습니다. 월 백 건이면 할 만하고, 오백 건이면 하루가 갑니다.

그런데 그 판단의 상당수는 단어 하나로 끝납니다. "배송"이 들어 있으면 배송입니다. 이 부분은 규칙으로 붙일 수 있습니다.

이 글은 엑셀 데이터 정리 여덟 단계의 여섯 번째 단계 앞부분이고, 유형 목록이 먼저 정해져 있어야 합니다.

엑셀 search 함수 쓰는법부터 봅니다

SEARCH는 찾는 글자가 몇 번째에 있는지 돌려주고, 없으면 오류를 냅니다. 있는지 없는지만 알면 되므로 ISNUMBER로 감쌉니다.

=ISNUMBER(SEARCH("배송",D2))     D2 에 배송이 들어 있으면 TRUE

SEARCH는 대소문자를 구분하지 않고 와일드카드도 받습니다. 정확히 구분해야 하면 FIND를 쓰는데, 한글 상담 데이터에서는 거의 필요 없습니다.

여기까지가 한 단어입니다. 유형이 여섯이고 단어가 유형마다 여러 개면 수식이 길어집니다.

규칙을 수식에 넣지 말고 표에 적습니다

IFS로 중첩하면 이렇게 됩니다.

=IFS(ISNUMBER(SEARCH("배송",D2)),"배송",
     ISNUMBER(SEARCH("환불",D2)),"환불",
     ISNUMBER(SEARCH("교환",D2)),"교환","미분류")

돌아가긴 합니다. 문제는 단어를 하나 더할 때마다 이 수식을 열어 고쳐야 한다는 것입니다. 실무자는 수식을 못 고치므로 결국 담당자 한 명에게 몰립니다.

규칙을 수식 안에 적으면 규칙이 늘 때마다 수식을 고치게 됩니다. 규칙은 표에 적고 수식은 표를 읽게 둡니다.

키워드사전 시트를 만들어 두 열만 둡니다. A열에 키워드, B열에 유형입니다.

배송     배송
배송지연 배송
안 왔    배송
환불     환불
반품     환불

한 유형에 여러 단어를 적고 줄을 계속 늘립니다. 실무자가 새 표현을 발견하면 한 줄 추가하면 끝입니다.

엑셀 키워드 매칭 수식은 한 줄입니다

사전을 읽는 수식은 이렇게 됩니다.

=IFERROR(INDEX(키워드사전!B:B,
   MATCH(TRUE,ISNUMBER(SEARCH(키워드사전!A2:A100,D2)),0)),"미분류")

SEARCH에 범위를 넣으면 사전의 각 키워드를 D2에서 찾아 결과 배열을 만듭니다. MATCH(TRUE,...)가 처음 걸린 줄 번호를 주고, INDEX가 그 줄의 유형을 가져옵니다. 하나도 안 걸리면 미분류입니다.

365가 아니면 입력할 때 Ctrl+Shift+Enter로 배열 수식으로 넣습니다.

사전에서 위에 있는 줄이 먼저 걸립니다. 그래서 구체적인 단어를 위에, 넓은 단어를 아래에 둡니다. 배송지연을 배송보다 위에 두면 배송지연 건이 더 좁은 유형으로 갑니다.

규칙이 잡는 비율을 셉니다

이게 이 작업에서 제일 중요한 숫자입니다.

=COUNTIF(E:E,"미분류")/COUNTA(E2:E500)

미분류 비율입니다. 사전을 늘릴 때마다 이 숫자가 내려갑니다. 어느 선에서 더 안 내려가는 지점이 오는데, 거기가 규칙으로 갈 수 있는 끝입니다.

이 숫자를 안 세면 사전을 계속 늘려야 할지 다른 방법으로 가야 할지 판단이 안 됩니다. 매주 기록해 두면 몇 주 만에 곡선이 보입니다.

엑셀 키워드 빈도분석으로 사전을 늘립니다

미분류로 남은 건들을 그냥 보지 말고 단어를 세어 봅니다.

미분류 건의 내용 열만 새 시트에 복사하고, 텍스트 나누기로 공백 기준으로 자른 뒤 한 열로 세로로 쌓습니다. 그다음 피벗으로 단어별 건수를 셉니다.

상위에 올라오는 단어가 사전에 없는 표현입니다. "아직도", "언제쯤", "취소요"처럼 유형을 짐작할 수 있는 말이 보이면 사전에 추가합니다.

엑셀 키워드 분석이라고 하면 대단해 보이는데 실제로는 자르고 세는 것입니다. 이걸 한 달에 한 번 하면 사전이 계속 좋아집니다.

사전이 안 듣는 문장이 남습니다

"아직도 안 왔어요"에는 배송이라는 단어가 없습니다. "이거 왜 이래요"는 어느 유형인지 문장만으로도 애매합니다.

사전에 안 왔을 넣으면 이 건은 잡힙니다. 그런데 이런 표현을 계속 넣다 보면 사전이 수백 줄이 되고, 넓은 표현이 엉뚱한 건까지 잡기 시작합니다. 미분류 비율은 내려가는데 정확도가 같이 내려갑니다.

그래서 두 숫자를 같이 봅니다. 미분류 비율과, 사람이 확인했을 때 맞게 붙은 비율입니다. 뒤 숫자가 떨어지기 시작하면 사전을 더 늘리는 대신 남은 건을 다른 방법으로 넘길 시점입니다.

적요를 보고 계정과목을 다는 일과 문의 내용을 보고 유형을 다는 일은 같은 작업입니다. 둘 다 사전을 만들어 두면 대부분이 자동으로 붙고, 남는 몇 퍼센트가 사람 몫으로 남습니다.

자주 받는 질문

Q. 사전에 없는 표기 흔들림은 어떻게 하나요

A. 사전을 늘리기 전에 표기를 먼저 통일합니다. 공백과 줄바꿈이 섞여 있으면 SEARCH가 못 찾는 경우가 있습니다. 정제 순서를 먼저 걸고 나서 사전을 붙입니다.

Q. 한 건에 여러 유형이 걸리면 어떻게 되나요

A. 사전에서 위에 있는 줄이 이깁니다. 배송과 환불이 같이 언급된 건은 사전 순서에 따라 하나로 갑니다. 둘 다 남겨야 하면 유형 열을 두 개 두고 각각 다른 사전으로 붙입니다.

Q. 엑셀 키워드 빈도분석에서 조사가 붙어 안 세어집니다

A. 한국어는 조사가 붙어 같은 단어가 여러 형태로 나옵니다. 공백으로만 자르면 "배송이"와 "배송을"이 다른 단어가 됩니다. 세기 전에 자주 나오는 조사를 SUBSTITUTE로 지우거나, 세는 목적이 표현 발견이므로 그대로 두고 눈으로 묶어도 됩니다.

Q. 사전이 몇 줄쯤 되면 적당한가요

A. 줄 수보다 미분류 비율로 판단합니다. 서른 줄로 충분한 곳도 있고 이백 줄이 필요한 곳도 있습니다. 줄을 늘려도 미분류가 안 줄면 거기서 멈춥니다.

규칙이 잡는 비율을 세어 두면 어디까지 왔는지 보입니다. 그 숫자부터 만들어 두려 합니다.