엑셀 특정 글자 포함하면 분류 자동 입력: IF와 COUNTIF로 담당부서 채우기

엑셀에서 특정 글자가 포함되면 자동으로 분류하고 싶은 경우가 정말 많습니다. 예를 들어 문의 내용에 “환불”이 들어가면 정산팀, “배송”이 들어가면 물류팀처럼 담당부서를 자동으로 넣고 싶은데, 막상 해보면 IF 함수만으로는 어디에 와일드카드를 넣어야 할지 헷갈립니다.

이번 글은 초보자도 바로 따라 할 수 있게 IF와 COUNTIF만 사용해서 설명하겠습니다. 실제 고객 문의 목록에서 담당부서를 수기로 입력하던 방식과, 수식으로 자동 분류하는 방식을 Before/After 느낌으로 비교해 보겠습니다.

Before: 필터 걸고 눈으로 보며 담당부서 입력하던 방식

아래처럼 고객 문의 목록이 있다고 가정해 보겠습니다. A열은 접수일, B열은 고객명, C열은 문의내용, D열은 담당부서를 입력하는 칸입니다. 데이터는 2행부터 8행까지 들어 있고, 결과는 D2:D8에 표시할 예정입니다.

초보자일 때는 보통 C열 문의내용을 하나씩 읽고 D열에 “물류팀”, “정산팀”, “회계팀”을 직접 입력합니다. 데이터가 10건이면 괜찮지만, 300건이 넘어가면 오타가 생기고 기준도 흔들립니다.

After: 문의내용에 단어가 포함되면 담당부서가 자동 입력

이번 예제의 분류 기준은 단순하게 잡겠습니다. 실제 회사 파일에서는 기준이 더 많을 수 있지만, 처음에는 이렇게 4개 정도로 시작하는 것이 좋습니다.

여기서 핵심은 COUNTIF 함수가 “셀 안에 특정 글자가 들어 있는지” 확인할 수 있다는 점입니다. COUNTIF는 원래 개수를 세는 함수지만, 범위를 셀 하나로 지정하면 그 셀에 조건이 맞는지 확인하는 용도로도 쓸 수 있습니다.

D2 셀을 클릭한 뒤 아래 수식을 입력해 보세요. 그리고 D2의 채우기 핸들을 잡고 D8까지 아래로 복사하면 됩니다.

=IF(C2="","",IF(COUNTIF(C2,"*환불*")>0,"정산팀",IF(COUNTIF(C2,"*교환*")>0,"CS팀",IF(COUNTIF(C2,"*배송*")>0,"물류팀",IF(COUNTIF(C2,"*세금계산서*")>0,"회계팀","확인필요")))))

수식이 길어 보이지만 구조는 어렵지 않습니다. 먼저 C2가 빈칸이면 결과도 빈칸으로 둡니다. 그다음 C2 안에 “환불”이 있으면 정산팀, 없으면 “교환”을 확인하고, 또 없으면 “배송”을 확인하는 방식입니다.

여기서 꼭 알아야 할 별표(*)의 뜻

수식 안에 들어간 *환불*에서 별표는 “앞뒤에 어떤 글자가 와도 괜찮다”는 뜻입니다. 그래서 “환불”, “환불 처리”, “배송비 환불 문의드립니다”가 모두 조건에 걸립니다.

실무에서는 대부분 단어가 문장 중간에 들어갑니다. 그래서 “특정 글자 포함”을 찾을 때는 보통 *단어* 형태를 가장 많이 씁니다.

결과가 어떻게 바뀌는지 비교해 보기

위 수식을 D2:D8에 복사하면 아래처럼 담당부서가 자동으로 들어갑니다. 특히 6행의 “배송비 환불 문의드립니다”는 배송이라는 글자도 있지만, 수식에서 환불을 먼저 검사했기 때문에 정산팀으로 분류됩니다.

이 방식의 장점은 기준이 수식 안에 고정된다는 점입니다. 누가 작업해도 같은 문의내용이면 같은 담당부서가 나오기 때문에, 나중에 집계할 때 훨씬 안정적입니다.

초보자가 가장 많이 헷갈리는 부분

첫 번째는 COUNTIF(C2,"*환불*")에서 C2를 범위처럼 써도 되는지입니다. COUNTIF의 첫 번째 칸은 원래 범위 자리지만, 셀 하나만 넣어도 괜찮습니다. 여기서는 C2 한 칸만 검사하겠다는 뜻입니다.

두 번째는 큰따옴표입니다. 엑셀 수식에서 글자는 반드시 큰따옴표로 감싸야 합니다. 그래서 환불이라고만 쓰면 안 되고, "*환불*"처럼 별표까지 포함해서 큰따옴표 안에 넣어야 합니다.

세 번째는 키워드 순서입니다. 한 문장에 여러 단어가 들어갈 수 있기 때문입니다. 예를 들어 “교환 접수 후 배송 일정 확인”은 교환도 있고 배송도 있습니다. 이때 수식에서 교환을 먼저 검사하면 CS팀, 배송을 먼저 검사하면 물류팀이 됩니다.

분류 기준을 내 파일에 맞게 바꾸는 방법

내 파일에서 C열이 문의내용이 아니라면 수식의 C2만 바꾸면 됩니다. 예를 들어 문의내용이 E열에 있다면 D2 수식 안의 C2를 모두 E2로 바꿔야 합니다.

담당부서 이름도 자유롭게 바꿀 수 있습니다. “정산팀” 대신 “환불담당”, “CS팀” 대신 “고객지원”처럼 실제 회사에서 쓰는 이름으로 바꾸면 됩니다.

=IF(E2="","",IF(COUNTIF(E2,"*환불*")>0,"환불담당",IF(COUNTIF(E2,"*교환*")>0,"고객지원",IF(COUNTIF(E2,"*배송*")>0,"물류담당","확인필요"))))

위 예시는 문의내용이 E2에 있을 때 사용하는 형태입니다. 복사해서 쓸 때는 결과를 표시할 셀에 넣고, 문의내용 셀 주소만 현재 파일에 맞게 조정하면 됩니다.

담당부서별 건수까지 바로 세기

담당부서가 D열에 자동으로 들어갔다면, 이제 부서별 문의 건수도 쉽게 셀 수 있습니다. 예를 들어 F열과 G열에 작은 집계표를 만들어 보겠습니다.

G2 셀에는 아래 수식을 입력합니다. 그런 다음 G6까지 아래로 복사하면 부서별 건수가 자동으로 계산됩니다.

=COUNTIF($D$2:$D$8,F2)

여기서 $D$2:$D$8처럼 달러 표시가 붙은 이유는 범위를 고정하기 위해서입니다. G2 수식을 아래로 복사해도 항상 D2부터 D8까지의 담당부서 결과를 보게 됩니다.

결과가 이상할 때 확인할 순서

먼저 수식이 들어간 셀을 클릭하고, 문의내용 셀 주소가 맞는지 확인합니다. 예제에서는 C2를 보고 있는데, 실제 파일에서 문의내용이 C열이 아니라면 결과가 엉뚱하게 나올 수 있습니다.

다음으로 별표와 큰따옴표를 확인합니다. COUNTIF(C2,*환불*)처럼 큰따옴표가 빠지면 수식 오류가 납니다. 반드시 COUNTIF(C2,"*환불*") 형태여야 합니다.

마지막으로 키워드 순서를 확인합니다. “배송비 환불”을 물류팀으로 볼지 정산팀으로 볼지는 회사 기준에 따라 다릅니다. 더 우선해야 하는 단어를 수식의 앞쪽에 배치하면 됩니다.

실무에서 조금 더 편하게 쓰는 응용 팁

처음부터 완벽하게 모든 문의를 자동 분류하려고 하면 수식이 너무 길어집니다. 먼저 자주 나오는 단어 3~5개만 넣고, 나머지는 “확인필요”로 두는 편이 좋습니다.

그다음 “확인필요”만 필터로 걸어 보면 새로 추가해야 할 키워드가 보입니다. 예를 들어 “취소 요청”이 자주 보인다면 수식에 취소 조건을 하나 더 추가하면 됩니다.

=IF(C2="","",IF(COUNTIF(C2,"*환불*")>0,"정산팀",IF(COUNTIF(C2,"*취소*")>0,"정산팀",IF(COUNTIF(C2,"*교환*")>0,"CS팀",IF(COUNTIF(C2,"*배송*")>0,"물류팀",IF(COUNTIF(C2,"*세금계산서*")>0,"회계팀","확인필요"))))))

이렇게 관리하면 처음에는 간단한 수식으로 시작하면서도, 실제 업무 데이터에 맞게 점점 정확도를 높일 수 있습니다. 중요한 것은 담당부서를 손으로 계속 입력하는 것이 아니라, 반복되는 판단 기준을 수식으로 옮기는 것입니다.

문의내용, 요청사항, 비고, 메모처럼 문장형 데이터가 들어 있는 열이 있다면 오늘 방식이 꽤 자주 쓰입니다. “특정 글자 포함” 문제는 어렵게 생각하지 말고, COUNTIF에 별표(*)를 붙이고 IF로 결과를 나눈다고 기억해 두면 실무 파일에서 바로 써먹기 좋습니다.