가계부 카테고리 자동 분류하기, 키워드 규칙표 만드는 법

가계부가 무너지는 지점은 늘 같습니다. 기록이 아니라 분류입니다. 카드 내역 200건을 받아서 한 줄씩 "이건 식비, 이건 교통비" 하고 있으면 두 달을 못 갑니다.

그런데 이 작업은 사실 규칙이 정해져 있습니다. 스타벅스 강남2호점은 언제나 카페고, GS25 역삼점은 언제나 편의점입니다. 규칙표를 한 번 만들어두면 다음 달부터는 붙여넣기만 하면 됩니다.

1단계. 시트 구조 잡기

시트는 세 장이면 충분합니다.

거래내역 — 카드사에서 받은 내역을 그대로 붙여넣는 곳
분류규칙 — 키워드와 카테고리를 짝지어 놓은 표
월별집계 — 피벗테이블

거래내역 시트에는 최소한 날짜 | 가맹점명 | 금액 세 열이 있으면 됩니다. 카드사 파일에 열이 더 많아도 지우지 말고 두세요. 나중에 확인할 일이 생깁니다.

2단계. 분류규칙표 만들기

분류규칙 시트를 이렇게 구성합니다.

A열: 키워드 | B열: 대분류 | C열: 소분류

키워드는 가맹점명 전체가 아니라 변하지 않는 부분만 적습니다.

스타벅스 → 식비 / 카페 ('스타벅스 강남2호점'도 잡힙니다)
GS25 → 식비 / 편의점
카카오T → 교통 / 택시
쿠팡 → 쇼핑 / 온라인

처음부터 완벽할 필요 없습니다. 가장 자주 나오는 20개만 적고 시작하세요. 나머지는 4단계에서 채웁니다.

3단계. 자동 분류 수식

가맹점명이 B열에 있다고 가정합니다. 버전에 따라 두 가지 방법이 있습니다.

모든 버전에서 되는 방법

=IFERROR(LOOKUP(9^9, SEARCH(분류규칙!$A$2:$A$200, $B2), 분류규칙!$B$2:$B$200), "미분류")

동작을 뜯어보면 이렇습니다.

SEARCH(키워드목록, B2) → 각 키워드가 가맹점명 안에서 몇 번째 글자에 있는지 숫자로 반환합니다. 없으면 오류값이 됩니다
LOOKUP(9^9, ...) → 9^9는 아주 큰 수입니다. 이보다 작은 값 중 마지막 것을 찾아 대응하는 분류를 가져옵니다
IFERROR(..., "미분류") → 하나도 안 걸리면 미분류로 표시합니다

소분류는 마지막 인수만 분류규칙!$C$2:$C$200으로 바꾸면 됩니다.

Microsoft 365를 쓴다면

=IFERROR(XLOOKUP(TRUE, ISNUMBER(SEARCH(분류규칙!$A$2:$A$200, $B2)), 분류규칙!$B$2:$B$200), "미분류")

이쪽은 처음 걸린 규칙을 가져옵니다. LOOKUP 방식과 우선순위가 반대라는 점을 기억하세요.

4단계. 미분류를 줄여나가기 (가장 중요)

이 방식의 핵심은 수식이 아니라 운영 방법입니다.

분류 열에 필터를 걸고 미분류만 표시합니다
금액이 큰 것부터 봅니다
규칙표에 한 줄씩 추가합니다
수식은 자동으로 다시 계산됩니다

첫 달에는 미분류가 절반쯤 나옵니다. 둘째 달에는 20%, 셋째 달에는 5% 아래로 떨어집니다. 규칙표는 쓸수록 정확해지는 자산이라 초반의 수고가 계속 남습니다.

미분류 건수를 한눈에 보려면 맨 위 셀에 이렇게 넣어두세요.

=COUNTIF(D:D, "미분류")

5단계. 월별 집계

거래내역을 표로 만든 뒤 피벗테이블을 삽입합니다.

행 : 대분류 → 소분류
열 : 날짜 (우클릭 → 그룹 → 월)
값 : 금액 합계

이러면 카테고리별 월간 추이가 한 장에 나옵니다. 다음 달 데이터를 붙여넣고 새로고침만 누르면 갱신됩니다.

6단계. 예산 대비 표시

카테고리별 예산을 적은 열을 만들고, 초과분을 색으로 표시합니다.

범위 선택 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정.

=AND($C2<>"", $C2>$D2)

C열이 지출, D열이 예산일 때입니다. AND($C2<>"", ...)로 빈 행을 걸러내지 않으면 아래쪽 빈 칸이 전부 칠해집니다.

자주 막히는 지점 세 가지

① 키워드가 겹쳐서 엉뚱하게 분류됨

커피와 스타벅스가 둘 다 규칙표에 있으면 스타벅스 커피 강남점은 양쪽에 다 걸립니다.

LOOKUP 방식은 마지막에 걸린 규칙을 채택하므로, 구체적인 키워드를 표 아래쪽에 두세요. 일반적인 키워드(커피, 마트)는 위, 구체적인 키워드(스타벅스, 이마트에브리데이)는 아래입니다.

XLOOKUP 방식은 반대로 처음 걸린 규칙을 쓰므로 구체적인 키워드를 위에 둬야 합니다. 어느 쪽을 쓰든 일부러 겹치는 사례를 하나 만들어 테스트해보세요. 순서를 잘못 알고 몇 달치를 잘못 분류하는 일이 흔합니다.

② 카드사마다 열 구성이 달라서 매번 손봐야 함

신용카드와 체크카드, 은행 계좌 내역을 함께 관리하면 파일마다 형식이 다릅니다.

파워쿼리로 가져오면 열 이름 맞추기를 한 번만 해두고 이후에는 새로고침으로 끝납니다. 데이터 → 데이터 가져오기 → 파일에서 → 폴더에서를 쓰면 폴더에 파일을 넣기만 해도 자동으로 포함됩니다.

③ 금액이 텍스트로 저장돼서 합계가 0

카드사 파일에서 매우 흔합니다. 1,234원처럼 쉼표나 단위가 문자로 붙어 있으면 숫자로 인식되지 않습니다.

=VALUE(SUBSTITUTE(SUBSTITUTE(C2,",",""),"원",""))

웹에서 복사한 내역이라면 눈에 안 보이는 공백까지 섞여 있을 수 있습니다.

자주 묻는 질문

Q. 같은 가맹점인데 상황에 따라 분류가 달라야 하면요?

편의점에서 산 담배와 도시락을 나눠야 하는 경우입니다. 이건 가맹점명만으로는 판단할 수 없으므로 자동화 대상이 아닙니다. 규칙으로는 '편의점'까지만 분류하고, 필요한 건만 수동으로 수정하는 열을 하나 더 두세요. 수정 열이 비어 있으면 자동 분류를, 값이 있으면 그것을 쓰는 방식입니다.

Q. 규칙표가 200줄이 넘어가면 느려지나요?

SEARCH는 배열 전체를 훑기 때문에 거래 건수 × 규칙 수만큼 계산합니다. 거래 500건에 규칙 300개면 15만 번입니다. 체감될 정도로 느려지면 계산을 수동으로 바꾸고(수식 → 계산 옵션) 필요할 때 F9로 갱신하세요.

Q. 대소문자나 띄어쓰기가 달라도 잡히나요?

SEARCH는 대소문자를 구분하지 않으므로 gs25와 GS25는 같게 처리됩니다. 다만 띄어쓰기는 구분하므로 카카오 T와 카카오T는 다릅니다. 키워드를 짧게 잡는 편이 안전합니다.

Q. 구글 시트에서도 되나요?

됩니다. LOOKUP과 SEARCH 조합이 동일하게 작동합니다. 구글 시트에서는 =REGEXMATCH()로 더 유연한 규칙을 만들 수도 있지만, 정규식을 모른다면 이 방식이 관리하기 쉽습니다.

정리

가맹점명 전체가 아니라 변하지 않는 부분만 키워드로
LOOKUP(9^9, SEARCH(...)) 조합이 모든 버전에서 작동
안 걸리는 건 '미분류'로 남기고 매달 규칙표에 추가
키워드가 겹칠 때의 우선순위를 반드시 테스트할 것

관련 글

거래처별 매출 집계, SUMIF 말고 피벗테이블로 하는 법
조건부 서식으로 마감일·재고 알림 만들기
엑셀 숫자가 텍스트로 저장됐을 때, 원인과 해결법 4가지
여러 시트 한 장으로 합치기, 복사 붙여넣기 말고 3가지 방법