한 6년 전 부터 구글 드라이브의 스프레드시트로 가계부를 만들어 쓰고 있습니다. 드미트리 가계부 라는 이름으로 템플릿을 오픈 해 놓았는데 다른 분들도 많이 받아서 쓰셨던 것 같습니다. 게을러서 바쁜 일정 때문에 2012년인가를 마지막으로 업데이트를 안 하고 같은 템플릿을 계속 쓰고 있었는데, 아이들도 생기고 하다 보니 소비 패턴에도 많은 변화가 생기기도 하고 해서 간만에 템플릿 업데이트를 했습니다.
새로운 가계부 템플릿 주소는 아래와 같습니다.
https://docs.google.com/spreadsheets/d/1y626Oq27d7TxB28KNRMmAxQs7AS56lHc-PJ1JsCrWyI
가계부 템플릿 저장
사용하려면 당연히 구글 계정이 필요하고요, 공개 읽기 권한으로 되어 있으니 편집 하려면 자신의 계정으로 사본을 저장해서 사용하면 됩니다.
가끔 템플릿 문서를 편집 권한 달라고 저한테 메일 보내는 분들 계신데 사본 저장해서 사용하세요!!
개요 탭
먼저 첫번째로 개요 탭이 있습니다. 개요 탭에서는 수입/지출 항목과 형태를 정의하고 전체 합계를 보는 화면입니다.
항목값 - [B3:B22]
먼저 B열의 B3 ~ B22 범위는 수입/지출 항목값을 관리하는 셀 들입니다. 우선은 제가 주로 사용하는 항목대로 했는데, 이 범위의 셀의 값들을 변경하시면 다른 통계 탭과 1월 ~ 12월 탭의 항목 선택 옵션값 들도 모두 자동으로 적용됩니다. B13 ~ B19 셀에는 색깔이 입혀져 있는데 1~12월 탭에서 항목 선택시 이 색들과 동일하게 적용되도록 조건부 서식이 적용되어 있으니 색깔은 변경하면 안됩니다.
B3 ~ B22 셀에는 내가 사용할 항목들을 넣고 C3 ~ C22 셀에는 그 항목에 대한 설명들을 적으면 됩니다.
월별 예산계획 - 수입 [D3:D22], 지출 [E3:E22]
D3 ~ E22 셀에는 각 항목에 대한 월별 예산 계획값을 입력합니다. D 열은 수입, E 열은 지출 입니다. 이번 가계부 작성하면서 가장 신경쓰고 싶었던 것이 미리 계획한 예산과 내가 쓴 경비들을 계속 비교할 수 있는 기능을 넣는 것이었습니다. 특히 3행의 고정비 값은 1월~12월 탭에 매월 고정비로 자동 반영이 되기 때문에 3행의 고정는 항목은 수정하지 말고 수입/지출 값만 수정해서 사용하도록 합니다.
23행은 각 수입/지출 예산 계획값의 합계 가, 24행은 전체 수입에서 지출을 뺀 잔액 이 표시됩니다. 템플릿의 경우 수입 350만원, 지출 338만원으로 매월 12만원의 잔액이 남는 것으로 되어 있습니다.
년간 예산계획 - 수입 [F3:F22], 지출 [G3:G22]
F3 ~ G22 셀에는 각 항목에 대한 년간 예산 계획값이 표시됩니다. 월별과 마찬가지로 F 열은 수입, G 열은 지출 입니다. 기본적으로는 [월별 수입/지출 값 x 12]가 되어 있습니다. 하지만 몇가지 항목은 월 고정 지출 외에 년간 이벤트로 발생하는 수입/지출이 있어 약간의 수정이 되어 있습니다.
F4셀 값은 350만 x 12 = 4200만원 이 기본 값인데, 별도 상여금 100만원이 있다고 가정하여 4,300만원 으로 되어 있습니다. 셀 서식은=(D4*12)+1000000입니다.G6셀 값은 40만 x 12 = 480만원 이 기본 값인데, 자동차세 등 년 단위 세금 30만원이 있다고 가정하여 510만원 으로 입력되어 있습니다. 셀 서식은=(E6*12)+300000입니다.G11셀 값은 10만 x 12 = 120만원 이 기본 값인데 휴가철 해외여행 경비 200만원을 잡아놓았다고 가정하여 320만원 으로 입력되어 있습니다. 셀 서식값은=(E11*12)+2000000입니다.
F3 ~ G22 셀 값들은 D3 ~ E22 셀들의 값 x 12 가 아닌 경우 약간 더 진한 노란색으로 표시되도록 조건부 서식이 적용되어 있습니다. 특별한 경우가 아니면 월별 예산 x 12 를 입력하고, 별도 특별 수입/지출이 있는 경우 셀 값을 수정하면 됩니다.
년간 지출 현황 - 합계 [H3:H22], 계획대비 잔액 [I3:I22], 잔액 비율 [J3:J22]
H,I,J 셀에는 년간 전체 지출 현황이 나타나 있습니다. 이 셀의 값들은 모두 자동 계산됨으로 수정하지 않도록 합니다. 참고로 월별 현황은 통계 탭에 있습니다. 현재 템플릿에는 1월달 가계부에 대한 값들만 입력이 되어 있어서 실제 H3~H22 열의 값들은 1월 통계의 값과 동일합니다.
23행은 각 통계에 대한 합계가 나타나 있습니다. H23 셀은 지금까지의 전체 수입에서 지출은 뺀 값 인 808,112 원을 나타내고, I23 셀은 예산 대비 잔액인 (1년 전체 예산은 14 만원을 남기도록 되어 있습니다) 808,112 에서 140,000 을 뺀 668,112 원을 나타내고 있습니다. J23 셀은 1년 전체 예산 4,300만원에서 현재까지 지출 한 금액 대비 잔액 비율 인 93.59% 를 나타내고 있습니다. 이는 1년치 예산 계획 중 현재 100 - 93.59 인 6.41% 만큼 사용했다는 뜻입니다.
형태값 [B27:B31]
개요 탭의 더 아래쪽을 보면 B27 ~ B31 셀에 수입/지출 방법에 대한 형태값 들이 있습니다. 항목값과 마찬가지로 이 범위의 셀의 값들을 변경하시면 다른 통계 탭과 1월 ~ 12월 탭의 형태 선택 옵션값 들도 모두 자동으로 적용됩니다. 또한 이 역시도 조건부 서식이 적용되어 있으니 글씨 색은 변경하면 안됩니다.
D:27 ~ D31, E:27 ~ E31 셀에는 형태에 대한 수입/지출 형태에 대한 합계가 있어 내가 지금까지 카드를 얼마나 많이 긁었나, 현금은 얼마를 썼나 볼 수 있습니다.
통계 탭
두번째 통계 탭 입니다. 보게 되면 개요 탭에서 정의 한 항목과 형태 값 들이 반영되어 있습니다.B열의 데이터 값들과 C열,D열의 월별 예산계획 값은 개요 탭에서 입력한 값들이 자동 반영됩니다. E열 부터는 1월 ~ 12월 탭에서 입력한 실제 가계부 값이 반영된 통계값들이 나타나게 됩니다.
23행은 개요 탭의 연간 합계와 마찬가지로 월별 항목별 통계가 반영되어 있습니다. 템플릿의 1월의 경우에는 E23 셀은 1월간 전체 수입에서 지출을 뺀 808,112 원이 표시되고 있고, F23 셀은 원래 예산은 월마다 12만원을 남기도록 짰기 때문에 예산대비 잔액인 808,112 에서 120,000 을 뺀 668,112 원이 표시됩니다. G23 셀은 전체 예산 수입 대비 잔액인 23.09% 가 표시되고 있습니다. 이 셀에서 계산하는 잔액은 예산대비 잔액이 아닌 실제 잔액이며 이 셀의 서식은 =E$23/$C$23 입니다.
26행 부터는 항목별이 아닌 월별 형태별 통계가 반영되어 있습니다.
기본적으로 통계 탭은 모든 셀 값들이 자동 반영 되기 때문에 수정할 일은 없습니다.
참고로 셀 색이
개요 탭의 D,E열같이에메랄드 색인 경우는 입력한 값이 전체 스프레드시트에 영향을 미치는 값이고,통계 탭의 대부분 셀처럼엷은 노란색인 경우는 값들이 자동으로 계산되는 셀 들입니다. 따라서 엷은 노란색 셀은 절대로 변경하면 안됩니다.
1월 ~ 12월 탭
이제 실제로 가계부를 입력하는 1월 ~ 12월 탭에 대해 알아보겠습니다. 템플릿에는 1월 탭의 내용에 예시 값들을 미리 입력 해 두었습니다. 실제 사용 하실 때는 A ~ F열의 5번째 행 아래 값 부터 지우고 사용하시면 됩니다. 앞에서 언급했듯이 3, 4행의 엷은 노란색 셀들은 변경하면 안됩니다.
수입/지출 항목 입력
기본적으로 A열에는 날짜를, B열에 내용을 입력하고 C열,D열의 항목, 형태는 드롭다운 메뉴로 선택하도록 합니다. 개요 탭에서 입력한 항목, 형태의 데이터 값들이 드롭다운 목록 선택 옵션으로 나타납니다.
E열, F열 에는 각각 수입, 지출 값을 입력합니다. 각 열의 2행에 전체 수입, 지출에 대한 합계가 나타납니다. H열 에는 위에서부터 아래로 전체 수입/지출 에 대한 누계가 표시되며 지금 입력한 행 까지의 수입-소비 합을 볼 수 있습니다.
예산계획 대비 잔액
사실 이번 2017년 가계부에서 가장 신경 쓴 부분이 G열의 예산계획 대비 잔액 기능입니다. C열의 항목을 선택하게 되면 G열에 해당 항목에 대한 잔액이 바로 표시되어 내가 이번 달에 해당 항목에 얼마나 더 소비가 가능한지 볼 수 있습니다.
예를 들어 아래와 같이 외식 항목을 선택하면 예산대비 잔액이 2만원 이 남았다고 G열 에 바로 표시를 해 줍니다. 이걸 보면서 아, 이번달은 이제 외식에 얼마를 더 쓸 수 있겠구나 하고 미리 짐작을 할 수 있습니다.
고정 지출비와 전월 이월금
각 월별 탭에 상단의 3, 4행 에는 각각 전월 이월금과 고정 지출비 항목이 미리 입력 되어 있습니다. 앞에서 언급 했듯이 엷은 노란색 셀임으로 절대로 변경하면 안됩니다. 고정 지출비는 개요 탭에서 입력 해 놓은 고정 지출비 가 자동 입력되고, 전월 이월금은 이전 달 이월 누계 금액 (예를 들어 2월 탭에서는 1월 전체 누계 금액) 이 반영됩니다.
그런데 통계 탭에서는 고정 지출비를 40만원으로 입력했는데, 위 화면에는 고정비 지출인 F4 셀의 값이 0 으로 되어 있습니다. 통계 값에서 제대로 반영하기 위해 고정 지출비의 값은 A4셀에 있는 날짜값에 영향을 받도록 되어 있습니다. 실제로 오늘 날짜가 A4 셀의 날짜와 같거나 이후가 되어야 고정 지출비가 나타나게 됩니다. A4 셀의 값을 오늘(제가 지금 블로그 포스트를 작성중인 날짜) 로 바꿔보도록 하겠습니다.
위와 같이 A4 셀의 날짜를 2016년 12월로 바꾸니 F4 셀 값이 고정 지출비로 반영이 되었습니다. 1월 ~ 12월 탭의 A3, A4 셀의 값은 각 월의 1일 로 입력이 되어 있습니다. 만약 고정 지출비용이 나가는 날짜가 따로 있다면 그 날짜로 변경 해 놓아도 됩니다. F4 셀 서식은 =IF(TODAY()>=$A$4,'개요'!E$3,0) 입니다.
A3 셀은 H3의 이월누계 값에 영향을 받습니다. 아래는 2월 탭의 내용입니다. A3 셀 값을 보면 2017년 2월 1일 로 되어 있습니다. H3 셀의 이월 누계 값은 A3 셀의 날짜 값에서 월을 가져와 그 전월 탭의 이월 누계값 을 가져오도록 되어 있습니다.
3월 탭의 경우 A3 셀 값이 2017년 3월 1일 로 되어 있고, H3 셀의 값은 2월 탭의 이월 누계값 을 가져오도록 되어 있습니다. 만약 3월 탭의 A3 셀 값을 2017년 2월 1일로 바꾸게 되면 2월 대신 1월 탭의 이월 누계값 을 가져오게 됩니다. 1월 탭의 H3 셀 값은 0 이고, 2월 ~ 12월 탭의 H3 셀 서식은 =INDIRECT(CHAR(39)&CONCAT(MONTH($A$3)-1,"월")&CHAR(39)&"!$I$2") 입니다. (앞에 설명한 A3셀 날짜 값에서 월만 추출해서 1을 뺀 월 탭의 I2 셀 값을 가져오는 수식입니다. 좀 복잡하죠?)
탭을 다른 기준으로 변경하기
현재는 1월 ~ 12월의 월별 기준으로 되어 있는데, 만약에 나는 이 가계부를 월별이 아니라 주 단위, 아니면 분기별 같이 다른 기준으로 사용하고 싶다고 하는 경우가 있을 것입니다. 문제가 되는 것은 통계 탭인데 통계 탭에 있는 수많은 셀들의 수식은 1행의 타이틀 값에 영향을 받도록 되어 있습니다.
위와 같이 E,F,G 열에 속한 셀들은 E,F,G:1 셀 값인 1월 이라는 값을 참조하여 실제 1월 탭의 값들을 가져오도록 수식화 되어 있습니다. 탭의 이름과 타이틀의 이름을 동일하게 맞춰주면 그 열에 속한 셀들은 타이틀 이름과 같은 셀의 값들을 바라보게 됩니다. 아래는 1월을 1분기로 탭 이름과 타이틀명을 바꾼 예 입니다.
참고로 셀들은 타이틀을 바꿨을 때 반영됩니다. 타이틀을 먼저 바꾸면 셀들이 그 즉시 잘못된 값들을 참조하게 되어 수많은 셀들에서 수식 오류가 납니다. 항상 탭 이름을 먼저 바꾸고 타이틀명을 변경해야 합니다.
정리
기본적인 가계부 사용법 설명은 다 끝났습니다. 실제로 사용 하다 보면 수식 버그가 나올 수도 있을텐데 발견할 때 마다 고쳐놓도록 하겠습니다. 옛날 버전에는 통화 (KRW, USD) 를 변환하는 기능도 있었는데 이번 가계부에는 그 기능은 뺐습니다. 환율 변동도 있고, 실제로 별로 도움이 되지 않아서 입력할 때 마다 환율 계산 해서 넣는게 편하더군요.
원래는 이 가계부를 구글 드라이브 API를 사용하는 웹앱 이나 모바일 앱으로 만드는게 목표였는데, 올해도 이런 저런 일로 미루다 보니 하지 못했습니다. 내년에는 할 수 있도록 다시 노력 해 보겠습니다. 사용하면서 궁금한 점 있으면 아래 답글 달아 주시거나 메일, 페북 메신져 등으로 연락 주세요. 😘
