엑셀 데이터 정리 방법: 상담 기록 집계는 여덟 단계만 따라 하면 됩니다

상담 기록은 텍스트로 쌓입니다.

이름과 연락처, 문의 내용, 처리 메모가 한 줄에 섞여 들어옵니다. 채널이 여럿이면 그 줄의 생김새도 여럿입니다.

이걸 AI에 넣으면 분류가 된다고들 합니다. 결과가 흔들리는 이유는 대개 모델이 아니라 그 앞의 다섯 단계입니다. 엑셀 데이터 정리 방법을 찾아보면 함수 목록부터 나오는데, 순서를 먼저 보는 편이 빠릅니다.

엑셀 데이터 정리 방법: 상담 기록이 지나는 여덟 단계

여덟 단계 가운데 일곱은 엑셀이 끝까지 합니다. 여섯 번째 하나만 다릅니다. 고객관리 엑셀만드는법을 찾을 때 나오는 순서도 대체로 이것과 같습니다.

앞의 다섯 단계는 엑셀이 끝까지 합니다

1단계 모으기. 카톡과 전화로 들어온 상담을 저녁에 표로 옮기는 일입니다. 법무법인 사무국이 매일 하는 작업을 상담 접수 정리 편에 따로 적었습니다.

2단계 합치기. 채널톡과 카카오와 전화 이력은 컬럼 이름도 날짜 형식도 다릅니다. 폴더를 통째로 읽는 순서를 따로 적었고, 그러면 매주 같은 작업이 새로 고침 한 번이 됩니다.

3단계 표기 통일. 배송지연과 배송 지연과 늦음이 다른 값으로 쌓입니다. 치환을 한 건씩 시키면 손보다 느려지므로 치환 목록을 표로 한 번에 넘깁니다.

4단계 중복 정리. 같은 사람이 사흘에 걸쳐 세 번 물어본 것을 세 건으로 세면 문의 건수가 부풀고 처리율이 실제보다 나빠 보입니다. 기준 열을 정하는 순서를 따로 적었습니다.

5단계 카테고리 정하기. 태그를 붙이기 전에 붙일 목록을 정합니다. 엑셀 카테고리 묶는법에 대분류와 중분류를 두 단으로 만드는 순서를 적었습니다.

엑셀 데이터 정리 함수로 되는 일

위 다섯 단계에서 실제로 쓰는 것은 열 개가 안 됩니다.

텍스트 나누기와 TEXTSPLIT로 한 칸에 붙어 온 값을 가릅니다. UNIQUE와 중복된 항목 제거로 같은 줄을 걷어냅니다. COUNTIFS로 몇 번 들어왔는지 셉니다. SUBSTITUTE와 XLOOKUP으로 매핑표를 태웁니다. 데이터 유효성 검사로 앞으로 들어올 값을 막습니다. SEARCH와 IFS로 키워드가 들어간 건에 유형을 답니다.

엑셀 데이터 정리 함수를 더 찾아 헤맬 필요는 없습니다. 함수가 모자라서 막히는 것이 아닙니다. 조회가 안 붙는 경우는 엑셀 vlookup #N/A 편에, 합계가 비는 경우는 엑셀 sumif 함수 편에 따로 적었습니다.

엑셀이 멈추는 단계는 여섯 번째 하나입니다

"아직도 안 왔어요"라는 문의에는 배송이라는 단어가 없습니다.

키워드 사전으로 붙일 수 있는 데까지 붙이고 나면 규칙은 여기서 멈춥니다. 규칙을 더 넣으면 이번에는 엉뚱한 건까지 배송으로 잡힙니다. 규칙이 잡는 비율은 실제로 세보면 대개 절반을 조금 넘는 선에서 더 안 오릅니다.

남은 건을 문장의 뜻으로 판단하는 일이 여섯 번째 단계이고, 여기가 엑셀 바깥입니다. 이 단계를 AI에 맡기면 정확도를 재는 절차가 같이 필요합니다. 샘플 100건을 사람이 붙인 태그와 대조해 일치율을 세고, 틀린 건이 어느 유형에 몰리는지 보고 5단계의 목록을 고칩니다.

순서를 지키면 되돌릴 일이 줄어듭니다

엑셀 데이터 정리 방법에서 순서는 여기서 가장 자주 어긋납니다. 5단계를 6단계 뒤로 미루는 경우가 많습니다. 일단 데이터를 쌓고 나중에 분류를 고민하는 것입니다.

그러면 이미 붙은 태그를 다시 붙이게 됩니다. 계정과목을 정하지 않고 전표를 끊으면 결산에서 되돌아오듯, 유형을 정하지 않고 태그를 붙이면 집계에서 되돌아옵니다.

3단계와 4단계의 순서도 바뀌면 안 됩니다. 표기를 통일하기 전에 중복을 지우면 배송지연과 배송 지연이 서로 다른 값이라 둘 다 남습니다.

엑셀 자동화는 여덟 단계를 다 지난 뒤에 붙입니다

매크로를 먼저 짜는 경우가 있습니다. 손이 제일 많이 가는 작업부터 자동화하고 싶어지기 때문입니다.

순서가 아직 안 정해졌을 때 엑셀 자동화를 붙이면 잘못된 순서가 그대로 굳습니다. 그리고 매크로는 목록이 언제 바뀌었는지 모르기 때문에, 유형을 손본 달부터 집계가 조용히 어긋납니다.

여덟 단계가 한 번 손으로 돌아간 다음에 엑셀 자동화를 붙이는 편이 빠릅니다. 그때는 무엇을 자동화할지가 이미 표로 적혀 있습니다. 무엇부터 자동화할지 고르는 기준은 엑셀 자동화 매크로 편에 정리했습니다.

유형이 정리되면 상담 기록이 숫자와 붙습니다

여기까지 하면 상담 데이터가 숫자와 붙습니다.

반품 사유별 건수는 반품률의 설명이 됩니다. 불량 문의의 유형별 추이는 품질 비용이 어디서 생기는지 알려줍니다. 배송 문의가 늘어난 주간과 배송비가 오른 주간이 겹치면 그건 협력사와 이야기할 자료가 됩니다.

매주 같은 표를 다시 만들지 않으려면 7단계에서 대시보드를 한 번 만들어 두는 편이 낫습니다. 표를 옮길 때 모양이 깨진다면 엑셀 서식 붙여넣기 편을, 파일을 밖으로 넘겨야 한다면 엑셀 마스킹 함수 편을 같이 보시면 됩니다. 유형 이름이 흔들리면 이 연결이 통째로 끊깁니다. 그래서 다섯 번째 단계가 지루해도 먼저입니다. 운영 데이터가 재무로 넘어가는 구조는 AI ERP 현황 정리에 따로 적어두었습니다.

자주 받는 질문

Q. 고객관리 엑셀만드는법을 찾다가 왔는데 상담 데이터와 같은 순서인가요

A. 같습니다. 고객관리 엑셀만드는법을 검색하는 분과 상담 기록을 정리하는 분이 겪는 작업은 거의 같습니다. 여러 곳에서 모아 합치고, 표기를 맞추고, 중복을 걷고, 분류를 정한 다음에 셉니다. 다루는 항목만 다르고 단계와 순서는 그대로입니다.

Q. 여덟 단계를 다 해야 하나요

A. 세는 것이 목적이면 1번부터 7번까지가 최소입니다. 8번은 이 작업이 매달 돌아올 때만 필요합니다. 한 번 쓰고 끝나는 자료는 자동화하지 않는 편이 쌉니다.

Q. 엑셀 대신 처음부터 데이터베이스를 쓰면 안 되나요

A. 행이 만 줄을 넘거나 동시에 고치는 사람이 셋을 넘으면 그때가 맞습니다. 다만 옮기기 전에 유형 목록은 정해야 하고, 그 작업은 도구를 바꿔도 사라지지 않습니다.

Q. 여섯 번째 단계를 AI에 맡기면 앞의 다섯 단계도 줄어드나요

A. 줄지 않습니다. 오히려 앞이 정리돼 있을수록 여섯 번째의 정확도가 올라갑니다. 표기가 흔들리고 중복이 남은 표를 넣으면 같은 건에 다른 태그가 붙습니다.

여덟 단계 가운데 일곱은 엑셀이 합니다. 그 일곱을 제대로 해두는 일부터 같이 정리해 나가려 합니다.