1년에 4번 관리하는, "AI + 엑셀 초간단 가계부" 작성법

쿠팡/네이버페이 구매내역 불러오기

쿠팡과 네이버 쇼핑에서 구매한 내역을 엑셀로 한 번에 추출하는 템플릿의 자세한 사용법은 아래 서식 다운로드 페이지와 영상 강의를 참고하세요.

은행 통장내역 정리하기

1. 먼저 은행마다 구조가 다른 통장 내역 엑셀 파일을 정리하는 방법을 알아보겠습니다. 첫 번째 예제로 '카카오뱅크'와 '토스뱅크'에서 제공하는 통장내역 파일을 살펴보겠습니다. 예제파일에서 [가계부 실습] - [통장_카카오뱅크] 엑셀 파일을 실행합니다.
2. 먼저 통장 내역에서 '문자 형식'의 날짜를 올바른 날짜로 변경합니다. 엑셀은 '점(.)'으로 구분된 날짜를 날짜로 인식하지 못합니다. 따라서 점(.)으로 구분된 날짜가 있다면 점을 빼기(-)로 변경하면 날짜 형식으로 간편하게 변환할 수 있습니다.
3. 점으로 구분된 날짜 범위를 선택한 후, Ctrl + H 를 눌러 찾기 및 바꾸기를 실행합니다. 이후 찾을 내용에 마침표(.), 바꿀 내용에 빼기(-)를 입력하고 [모두 바꾸기] 버튼을 클릭합니다.
4. "194개 항목이 바뀌었습니다." 안내창이 출력되며, 문자 형식의 날짜가 올바른 날짜 형식으로 변경됩니다.
5. 이제 표를 선택하고 Ctrl + Shift + L 을 눌러 자동 필터를 적용한 후, 날짜 필드의 필터를 열면 연도/월별로 그룹화된 것을 확인할 수 있습니다.
6. 엑셀은 1900년 1월 1일을 시작으로 하루마다 1씩 증가하는 숫자로 날짜를 관리합니다. 따라서 하루(=1), 12시간(=0.5), 6시간(=0.25)으로 계산됩니다. 예를 들어 비어있는 셀에 2026/1/1 을 입력한 후 [홈] 탭 - [표시형식] 을 [일반]으로 변경하면 46023이 표시되는데, 이는 1900/1/1 부터 46,023일이 지난 날짜를 의미합니다.[💡Tip] 엑셀의 문자/숫자/날짜 데이터 형식에 대한 자세한 설명은 아래 영상 강의를 참고하세요!
   
7. 따라서 날짜/시간이 들어있는 열에서 '정수'만 추출하면 날짜, '소수'만 추출하면 시간으로 분리할 수 있습니다. 예제파일에서 표 오른쪽 비어있는 셀을 선택한 후, INT 함수를 입력합니다. =INT(날짜/시간) 을 입력하면 날짜+시간 데이터에서 정수만 추출됩니다.
8. 이후 오른쪽 셀에 "=날짜/시간 - 정수" 수식을 입력하면, 날짜/시간 데이터에서 시간 부분(소수)만 추출됩니다.
9. 수식을 아래로 자동채우기한 후, [홈] 탭 - [표시형식] 에서 날짜 부분은 '간단한 날짜', 시간 부분은 '시간' 형식으로 변경합니다.
10. 카카오뱅크처럼 '입금(+)'과 '출금(-)'이 한 열에 작성된 경우, IF 함수로 입금/출금 열을 분리할 수 있습니다.
    · 출금 : =IF(금액셀<0, 금액셀*-1, 0)
    · 입금 : =IF(금엑셀>0, 금액셀, 0)
11. 수식을 입력하고 아래로 자동채우기하면 입금과 출금이 2개 열로 분리됩니다. 이렇게 완성된 표는 값 붙여넣기로 가계부 정리표에 누적하여 관리합니다.[💡Tip] 신한,국민,우리 등 다른 은행의 통장 내역도 비슷한 과정으로 정리할 수 있습니다. 다른 은행의 통장을 정리하는 전체 과정은 영상에서 알기 쉽게 정리했으니 참고하세요!

소비 항목 자동 분류를 위한 키워드 표 만들기

1. 이제 각 소득/지출 항목을 카테고리별로 분류할 차례입니다. 정리된 통장 내역이 취합된 가계부 표를 선택한 후, [삽입] 탭 - [피벗테이블]을 클릭합니다. 피벗테이블 만들기 대화상자가 실행되면 '기존 워크시트'를 선택하고, 오른쪽 비어있는 셀에 피벗테이블을 생성합니다.
2. 피벗테이블의 각 필드를 다음과 같이 추가합니다.
   · 행 : 내용
   · 값 : 내용, 출금, 입금

   

3. 피벗테이블의 행 레이블 필터에서 [기타 정렬 옵션] 을 클릭합니다.
4. 정렬 대화상자가 실행되면 '내림차순 기준'에서 "개수: 내용"을 선택합니다. 그러면 통장 내역에서 거래 빈도가 높은 순서대로 정렬됩니다.
5. 이제 각 항목을 하나씩 분류하는 대신, AI를 활용해 각 항목의 키워드별 카테고리를 자동 분류합니다. 템플릿에 준비된 프롬프트(또는 아래 프롬프트)를 복사해 Gemini (또는 ChatGPT 등) 채팅창에 붙여넣습니다.
6. "#소분류"에는 T2:T26 범위에 미리 작성한 소분류 목록을 붙여넣고, "#통장 내역"에는 피벗테이블로 생성한 고유목록을 붙여넣습니다.
7. 프롬프트를 실행하면 그림과 같이 각 항목별 '키워드'와 '소분류'가 한 번에 자동 분류됩니다.
8. 완성된 목록을 복사해 엑셀에 붙여넣고, 각 항목별 주요 키워드와 소분류가 올바르게 작성되었는지 검토합니다. 잘못 분류된 항목이 있다면 적절히 수정합니다.
9. 표를 모두 수정한 후, 피벗테이블과 표의 항목 범위를 우클릭 - [삭제]를 선택해 불필요한 범위를 제거합니다.
10. 키워드와 소분류 범위를 선택한 후, [데이터] 탭 - [중복된 항목 제거]를 선택하여 중복 키워드를 정리합니다.
11. 범위를 선택한 후 Ctrl + T 를 눌러 범위를 표로 변환하면 키워드 표가 완성됩니다.

소비 항목 자동 분류하기

1. 템플릿에 준비된 '자동 분류' 공식을 복사합니다.
   =INDEX(소분류범위,MATCH(TRUE,ISNUMBER(SEARCH(키워드범위,내역)),0))

   [💡Tip] 공식 앞에 작은따옴표(')는 제외하고 복사하는 것에 주의합니다.

2. 복사한 공식을 가계부 취합 표의 [소분류] 필드에 붙여넣고, 공식의 각 범위를 수정하여 실행하면 각 항목별 소분류가 자동으로 분류됩니다.
3. 대분류는 다음과 같이 VLOOKUP 함수(또는 XLOOKUP 함수)를 작성하여 매핑합니다.
   =VLOOKUP([@소분류],표4,2,0)

   

피벗테이블로 월별 가계부 대시보드 만들기

1. 이제 피벗테이블로 월별 분석 대시보드를 생성합니다. 자동 분류가 완료된 가계부 표를 선택한 후, [삽입] 탭 - [피벗테이블] - [새 워크시트]로 피벗테이블을 생성합니다.
2. 피벗테이블의 행 필드로 [거래일자]를 이동하면 날짜가 자동으로 인식되어 '연/분기/월'로 분류됩니다. 날짜를 우클릭 - [그룹]으로 이동합니다.
3. 그룹화 창이 실행되면, '월'과 '연'만 선택하고 [확인] 버튼을 클릭합니다.
4. 가계부에서 입금/출금을 따로 보지 않고 '순 현금흐름(입출금)'만 확인하기 위해 계산필드를 추가합니다. 피벗테이블을 선택한 후, [피벗테이블 분석] 탭 - [필드 및 항목 집합] - [계산 필드]로 이동합니다.
5. 계산필드 삽입 대화상자가 실행되면, 이름에 "입출금"을 입력하고 수식에는 입금과 출금을 각각 더블클릭하여 "=입금 - 출금"으로 작성합니다. 수식을 모두 입력한 후 [추가] 버튼을 클릭합니다.
6. 이후 [확인] 버튼을 클릭하면 피벗테이블에 입출금 필드가 추가됩니다. 이제 '자산이동'이 아닌 실제 지출 항목만 표시되도록 필터를 추가하겠습니다.
7. 피벗테이블의 열 필드로 "대분류"와 "소분류"를 추가합니다.
8. 열 레이블 필터를 열고, 지출이 아닌 항목(예: 자산이동, 투자)을 표시 항목에서 제외합니다.
9. [확인] 버튼을 클릭하여 필터를 적용하면, 그림과 같이 실제 소득과 지출 항목만 정리된 피벗테이블이 완성됩니다.[💡Tip] 여기에 조건부서식을 적용하면 더욱 가독성 높은 월별 분석 대시보드를 만들 수 있습니다. 조건부서식을 적용하는 전체 과정은 영상 강의를 참고하세요!

노트북LM으로 소비 분석 리포트 만들기

1. 마지막으로 NotebookLM을 활용해 월별 소득/지출 분석 리포트를 만들어보겠습니다. 아래 링크를 클릭해 노트북LM으로 이동한 후, [새로 만들기] 버튼을 클릭하여 새 노트북을 생성합니다.
   https://notebooklm.google.com/

   

2. 새 노트북이 생성되면 [소스 추가]에서 [복사된 텍스트]를 선택한 후, 2개의 소스를 추가합니다. 먼저 피벗테이블 범위를 복사하여 첫 번째 소스를 추가합니다.
3. 이후 [복사된 텍스트]를 다시 클릭하고, 통장 내역의 모든 소득/지출 내역을 복사하여 두 번째 소스로 추가합니다.
4. 소스를 모두 추가한 후, 오른쪽 목록에서 [보고서], [슬라이드 자료], [인포그래픽] 등 필요한 자료 형식을 클릭하면 가계부 분석 리포트 생성이 시작됩니다.
5. 원하는 주제가 있다면, 각 항목 오른쪽의 연필 아이콘을 클릭한 후 만들려는 자료에 대한 설명을 지시문으로 직접 작성하여 더 정확한 보고서나 슬라이드를 생성할 수 있습니다.
6. 잠시 기다리면 그림과 같이 보고서와 슬라이드가 완성됩니다.