1년에 4번 관리하는, "AI + 엑셀 초간단 가계부" 작성법
엑셀+AI 가계부 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 AI 실전 가계부 작성법 - 예제파일예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
쿠팡/네이버페이 구매내역 불러오기
팡과 네이버 쇼핑에서 구매한 내역을 엑셀로 한 번에 추출하는 템플릿의 자세한 사용법은 아래 서식 다운로드 페이지와 영상 강의를 참고하세요.
은행 통장내역 정리하기
- 먼저 은행마다 구조가 다른 통장 내역 엑셀 파일을 정리하는 방법을 알아보겠습니다. 첫번째 예제는 '카카오뱅크'와 '토스뱅크'에서 제공하는 통장내역 파일을 예시를 살펴보겠습니다. 예제파일에서 [가계부 실습] - [통장_카카오뱅크] 엑셀 파일을 실행합니다.
카카오뱅크 통장 내역 엑셀 파일을 실행합니다. - 먼저 통장 내역에서 '문자 형식'의 날짜를 올바른 날짜로 변경하겠습니다. 엑셀은 '점(.)'으로 구분된 날짜는 날짜로 인식하지 못합니다. 따라서 점(.)으로 구분된 날짜가 있다면 점을 빼기(-)로 변경해 날짜 형식으로 편리하게 변경할 수 있습니다.
점으로 구분된 날짜는 문자로 인식됩니다. - 점으로 구분된 날짜 범위를 선택한 후, Ctrl + H 를 눌러 찾기 및 바꾸기를 실행합니다. 이후 찾을 내용은 마침표(.), 바꿀 내용은 빼기(-)를 입력하고 [모두 바꾸기] 버튼을 클릭합니다.
찾기 및 바꾸기를 실행합니다. - 그러면 "194개 항목이 바뀌었습니다." 라는 안내창이 출력되며, 문자 형식의 날짜가 올바른 날짜 형식으로 변경됩니다.
올바른 날짜 형식으로 변경됩니다. - 이제 표를 선택하고 Ctrl + Shift + L 을 눌러 자동 필터를 적용한 후, 날짜 필드의 필터를 열면 년도/월별로 그룹화된 것을 확인할 수 있습니다.
필터를 열면, 올바른 날짜로 구분 된 것을 확인할 수 있습니다. - 엑셀은 1900년 1월 1일을 시작으로 하루마다 1씩 증가하는 숫자로 날짜를 관리합니다. 따라서 하루(=1), 12시간(=0.5), 6시간(=0.25)로 계산됩니다. 예를 들어, 비어있는 셀에 2026/1/1 을 입력한 후 [홈] 탭 - [표시형식] 을 [일반]으로 변경하면 46023이 나오는데, 이는 1900/1/1 부터 46023일이 지난 날짜로 해석할 수 있습니다.
엑셀에서 날짜는 숫자로 관리됩니다. [💡Tip] 엑셀의 문자/숫자/날짜 데이터 형식에 대한 자세한 설명은 아래 영상 강의를 참고하세요!
- 따라서 날짜/시간이 들어있는 열에서 '정수'만 추출하면 날짜, '소수'만 추출하면 시간으로 분리할 수 있습니다. 예제파일에서 표 오른쪽 비어있는 셀을 선택한 후, INT 함수를 입력합니다. =INT(날짜/시간) 을 입력하면 날짜+시간 데이터에서 정수만 추출됩니다.
INT 함수로 정수(=날짜)를 추출합니다. - 이후 오른쪽에는 "=날짜/시간 - 정수" 로 계산하면, 날짜/시간 데이터에서 시간 부분(소수)만 추출됩니다.
날짜+시간에서 정수를 빼면 시간만 추출됩니다. - 수식을 아래로 자동채우기 후, [홈] 탭 - [표시형식] 에서 날짜 부분은 '간단한 날짜', 시간 부분은 '시간' 형식으로 변경합니다.
데이터 표시 형식을 변경합니다. - 카카오뱅크처럼 '입금(+)'과 '출금(-)'이 한 열로 작성된 경우, IF 함수로 입금/출금 열을 분리할 수 있습니다.
· 출금 : =IF(금액셀<0, 금액셀*-1, 0)
· 입금 : =IF(금엑셀>0, 금액셀, 0) - 수식 입력 후, 아래로 자동채우기하면 입금과 출금이 2개 열로 분리됩니다. 이렇게 완성된 표는 값 붙여넣기로 가계부 정리표에 누적해서 관리합니다.
IF 함수로 입금과 출금을 분리합니다. [💡Tip] 신한,국민,우리 등 다른 은행의 통장 내역도 비슷한 과정으로 정리할 수 있습니다. 다른 은행의 통장을 정리하는 모든 과정은 영상에서 알기 쉽게 정리했으니 참고하세요!
소비 항목 자동 분류를 위한 키워드 표 만들기
- 이제 각 소득/지출을 각 카테고리별로 분류할 차례입니다. 정리된 통장 내역을 취합된 가계부 표를 선택한 후, [삽입] 탭 -[피벗테이블]을 클릭합니다. 피벗테이블 만들기 대화상자가 실행되면 '기존 워크시트'를 선택하고, 오른쪽 비어있는 셀에 피벗테이블을 생성합니다.
가계부 데이터를 선택한 후, 기존 워크시트에 피벗테이블을 생성합니다. - 피벗테이블의 각 필드를 다음과 같이 추가합니다.
· 행 : 내용
· 값 : 내용, 출금, 입금
피벗테이블 필드를 추가합니다. - 피벗테이블의 행 레이블 필터에서 [기타 정렬 옵션] 을 클릭합니다.
행 레이블 - 기타 정렬옵션으로 이동합니다. - 정렬 대화상자가 실행되면 '내림 차순 기준' 에서 "개수: 내용"을 선택합니다. 그러면 통장 내역에서 거래내역이 많은 순서대로 정렬됩니다.
내용의 갯수 기준 내림차순으로 정렬합니다. - 이제 각 항목을 하나씩 분류하는 대신, AI를 활용해 각 항목의 키워드별 카테고리를 분류합니다. 템플릿에 준비된 프롬프트 (또는 아래 프롬프트)를 복사해 Gemini (또는ChatGPT 등) 채팅창에 붙여넣습니다.
완성된 프롬프트를 붙여넣습니다. - "#소분류"에는 미리 T2:T26 범위에 미리 작성한 소분류 목록을 붙여넣고, "#통장 내역"에는 피벗테이블로 생성한 고유목록을 붙여넣습니다.
- 프롬프트를 실행하면 그림과 같이 각 항목별 '키워드'와 '소분류'가 한 번에 분류됩니다.
각 거래내역의 주요 키워드와 카테고리가 자동으로 분류됩니다. - 완성된 목록을 복사해 엑셀에 붙여넣고, 각 항목별 주요 키워드와 소분류가 올바르게 작성되었는지 검토합니다. 만약 잘못된 항목이 있다면 적절히 수정합니다.
완성된 데이터를 엑셀에 붙여넣고 잘못된 부분은 없는지 최종 검토합니다. - 표를 모두 수정한 후, 피벗테이블과 표의 항목 범위를 우클릭 - [삭제]를 선택해 범위를 제거합니다.
항목과 키워드를 제외한 나머지 데이터를 제거합니다. - 키워드와 소분류 범위를 선택한 후, [데이터] 탭 - [중복된 항목 제거]를 선택해 범위의 중복된 키워드를 정리합니다.
키워드표의 중복값을 제거합니다. - 범위를 선택한 후, Ctrl + T 를 눌러 범위를 표로 변환하면 키워드 표가 완성됩니다.
범위를 표로 변환합니다.
소비 항목 자동 분류하기
- 템플릿에 준비된 '자동 분류' 공식을 복사합니다.
=INDEX(소분류범위,MATCH(TRUE,ISNUMBER(SEARCH(키워드범위,내역)),0))
[💡Tip] 공식 앞에 작은따옴표(')는 제외하고 복사하는 것을 주의합니다.
- 복사한 공식을 가계부 취합 표에서 [소분류] 필드에 붙여넣고, 공식의 각 범위를 수정해 실행하면 각 항목별 소분류가 자동으로 분류됩니다.
키워드 자동 분류 공식을 복사/붙여넣습니다. - 대분류는 다음과 같이 VLOOKUP 함수 (또는 XLOOKUP 함수)를 작성해 매핑합니다.
=VLOOKUP([@소분류],표4,2,0)
VLOOKUP 함수로 대분류를 채웁니다.
피벗테이블로 월별 가계부 대시보드 만들기
- 이제 피벗테이블로 월별 분석 대시보드를 생성합니다. 자동 분류된 가계부 표를 선택한 후, [삽입] 탭 - [피벗테이블] - [새 워크시트]로 피벗테이블을 생성합니다.
가계부 월별 대시보드에 사용할 피벗테이블을 생성합니다. - 피벗테이블의 행 필드로 [거래일자]를 이동하면 날짜를 자동으로 인식해 '연/분기/월'로 분류됩니다. 날짜를 우클릭 - [그룹]으로 이동합니다.
행으로 날짜를 추가한 후, 우클릭 - 그룹으로 이동합니다. - 그룹화 창이 실행되면, '월'과 '연'만 선택하고 [확인] 버튼을 클릭합니다.
날짜를 월과 연으로 그룹화합니다 - 가계부에서 입금/출금을 따로 보지 않고, '순 현금흐름(입출금)'만 보기위해 계산필드를 추가합니다. 피벗테이블을 선택한 후, [피벗테이블 분석] 탭 - [필드 및 항목 집합] - [계산 필드]로 이동합니다.
피벗테이블에 계산필드를 추가합니다. - 계산필드 삽입 대화상자가 실행되면, 이름으로 "입출금"을 입력하고 수식에는 입금과 출금을 각각 더블클릭해 "=입금 - 출금"으로 입력합니다. 수식을 모두 입력한 후, [추가] 버튼을 클릭합니다.
입금에서 출금을 뺀 입출금 필드를 추가합니다. - 이후 [확인] 버튼을 클릭하면 피벗테이블에 입출금 필드가 추가됩니다. 이제 여기에서 '자산이동'이 아닌 실제 지출항목만 보이도록 필터를 추가해보겠습니다.
피벗테이블에 입출금 계산필드가 추가됩니다. - 피벗테이블의 열 필드로 "대분류"와 "소분류"를 추가합니다.
열 영역으로 대분류와 소분류를 추가합니다. - 열 레이블 필터를 연 후, 지출이 아닌 항목(예: 자산이동, 투자)을 표시 항목에서 제외합니다.
열 레이블에서 자산 항목을 제외합니다. - [확인] 버튼을 클릭해 필터를 적용하면, 그림과 같이 실제 소득과 지출 항목에 대해서만 정리된 피벗테이블이 완성됩니다.
소득과 지출에 대한 피벗테이블 보고서가 완성됩니다. [💡Tip] 여기에 조건부서식을 적용하면 더욱 가독성 높은 월별 분석 대시보드를 만들 수 있습니다. 조건부서식을 적용하는 전체 과정은 영상 강의를 참고하세요!
노트북LM 으로 소비 분석 리포트 만들기
- 마지막으로 NotebookLM을 활용해 월별 소득/지출 분석 리포트를 만들어보겠습니다. 아래 링크를 클릭해 노트북LM으로 이동한 후, [새로 만들기] 버튼을 클릭해 새로운 노트북을 생성합니다.
노트북LM으로 이동합니다. - 새 노트북이 생성되면 [소스 추가] 에서 [복사된 텍스트]를 선택한 후, 2개의 소스를 추가합니다. 먼저 피벗테이블 범위를 복사해 첫번째 소스를 추가합니다.
피벗테이블 데이터를 첫번째 소스로 추가합니다. - 이후 [복사된 텍스트]를 다시 클릭하고, 통장 내역의 모든 소득/지출 내역을 복사해 두번째 소스로 추가합니다.
가계부 원본 데이터를 두번째 소스로 추가합니다. - 소스를 모두 추가한 후, 오른쪽 목록에서 [보고서], [슬라이드 자료], [인포그래픽] 등 필요한 자료 형식을 클릭하면 가계부 분석 리포트 생성이 시작됩니다.
원하는 형식의 보고서를 생성합니다. - 만약 원하는 주제가 있다면, 각 항목 오른쪽의 연필모양 아이콘을 클릭한 후 만들려는 자료에 대한 설명으로 지시문을 직접 작성해 더 정확한 보고서나 슬라이드를 생성할 수 있습니다.
연필 버튼을 클릭해 맞춤형 지시문을 적을 수 있습니다. - 잠시만 기다리면 그림과 같이 보고서와 슬라이드가 완성됩니다.
노트북LM 보고서/슬라이드가 완성됩니다.
- 먼저 은행마다 구조가 다른 통장 내역 엑셀 파일을 정리하는 방법을 알아보겠습니다. 첫번째 예제는 '카카오뱅크'와 '토스뱅크'에서 제공하는 통장내역 파일을 예시를 살펴보겠습니다. 예제파일에서 [가계부 실습] - [통장_카카오뱅크] 엑셀 파일을 실행합니다.
