구글 시트로 가계부 자동화하기

가계부를 꾸준히 쓰는 건 쉽지 않습니다. 영수증을 모았다가 주말에 몰아서 입력하고, 합계를 내고, 어디에 돈을 많이 썼는지 따져보는 과정에서 지치기 마련이죠. 그래서 중요한 건 “의지”보다 “시스템”입니다. 구글 시트는 설치 없이 브라우저에서 열리고, 자동 저장과 모바일 입력, 함수·폼·Apps Script 같은 자동화 기능을 모두 품고 있어 개인 가계부를 “덜 귀찮게” 만들어 줍니다. 핵심은 구조를 잘 설계해 한 번 입력하면 여러 화면에서 자동으로 쓰이게 하는 것. 예를 들어 ‘원천 입력’ 탭에서 날짜·카테고리·금액만 적으면, 월별 지출 요약과 카테고리별 비중 차트, 고정비/변동비 분해, 이번 달 잔액 예측까지 자동으로 갱신되는 흐름을 만들 수 있습니다.

이 글은 가계부를 처음 자동화하려는 분을 위해 ① 설계(표 구조·카테고리·검증·서식) → ② 자동화(함수·폼·스크립트) → ③ 분석/대시보드(피벗·차트·목표·알림) 순서로 안내합니다. 중간중간 실전 예시와 복붙 가능한 수식/스크립트를 넣어, 오늘 저녁에 바로 시트를 만들어 돌릴 수 있게 했습니다. 목표는 단순합니다. “나중에 정리”라는 미루기를 없애고, 그날그날의 한 줄 입력만으로 돈의 흐름을 손바닥 보듯 보게 만드는 것. 천천히 따라오세요. 오늘의 한 줄이 한 달 뒤 지출을 줄이는 근거가 됩니다.

1) 설계: 표 구조·카테고리·유효성·조건부 서식으로 ‘입력 실수’부터 줄이기

자동화의 80%는 설계에서 결정됩니다. 무작정 시트를 열어 값을 적기보다, 역할이 분리된 탭 구조와 표준화된 항목을 먼저 잡으세요. 추천 구조는 네 가지입니다: ① 원천입력(Transactions), ② 코드표(Categories/계좌/결제수단), ③ 가공(월/주 파생열·정제표), ④ 대시보드(요약/차트). 이렇게 나누면 입력은 단순해지고, 나중에 수식을 고쳐도 화면 전체가 안정적으로 따라옵니다.

① 원천입력 탭(Transactions) 필수 열

날짜 (YYYY-MM-DD, 형식 > 숫자 > 날짜)
유형 (지출/수입/이체)
카테고리 (식비·교통·주거·통신·교육·의료·문화·경조사·저축·투자 등)
세부메모 (상호·메모: “편의점 / 간식”)
금액 (지출은 음수, 수입은 양수로 통일하거나, 별도 열로 + 통화서식)
결제수단 (체크카드·신용카드·현금·계좌이체·간편 결제)
계좌 (국민-생활비, 토스-저축 등)
고정/변동 (고정비/변동비 라벨)

② 코드표 탭으로 드롭다운(유효성 검사) 만들기

코드표 시트에 각 목록을 한 열씩 작성: 카테고리 목록, 결제수단, 계좌, 고정/변동
원천입력 탭 → 열 선택 → 데이터 > 데이터 유효성 → 항목 목록(범위) → 코드표 범위 지정
드롭다운 칩 스타일을 선택하면 가독성이 좋아지고 오타가 사라집니다.

예: 코드표! A2:A에 “식비/카페/교통/주거/통신/구독/의료/교육/문화/경조사/저축/투자/기타” 입력 → 원천입력! C열(카테고리)에 유효성 검사

③ 입력 보조 파생열(가공 탭) — 자동으로 월·주·요일 뽑기

=ARRAYFORMULA(IF(ROW(Transactions!A:A)=1,"월", IF(Transactions!A:A="",,TEXT(Transactions!A:A,"yyyy-mm"))))

=ARRAYFORMULA(IF(ROW(Transactions!A:A)=1,"주", IF(Transactions!A:A="",,WEEKNUM(Transactions!A:A))))

=ARRAYFORMULA(IF(ROW(Transactions!A:A)=1,"요일", IF(Transactions!A:A="",,TEXT(Transactions!A:A,"ddd"))))

이 파생열 덕분에 피벗/차트에서 월·주·요일 단위로 바로 그룹화할 수 있습니다.

④ 조건부 서식으로 ‘지출 위험’ 시각 신호

지출 금액(음수) → 텍스트 빨강/굵게
카테고리=“구독/정기결제” → 연한 보라 배경(눈에 띄게)
월 지출 합계가 예산을 초과하면 대시보드 카드 배경을 주황으로

팁: 형식 > 조건부 서식에서 수식 사용: =AND($B2="지출",$E2 <0) 같은 식으로 세밀하게 제어.

⑤ 상단 요약 카드(대시보드) — 오늘 열면 바로 보이는 5개

이번 달 지출: =SUMIFS(Transactions! E:E, MONTH(Transactions! A:A), MONTH(TODAY()), YEAR(Transactions! A:A), YEAR(TODAY()), Transactions! B:B, "지출")
이번 달 수입: 조건만 “수입”으로 변경
순현금흐름: 수입 − 지출
고정비 합계: =SUMIFS(Transactions! E:E, Transactions! H:H, "고정비", MONTH(...),...)
카테고리 TOP3: 피벗이나 QUERY로 추출

⑥ 예산표(월 예산 vs 실적)

예산 시트: 카테고리 / 월예산
대시보드에서 =SUMIFS(... 카테고리...)로 실적 합산, 게이지 또는 도넛으로 진행률 시각화

⑦ 흔한 오류와 예방

날짜가 텍스트 → 월 그룹/정렬이 깨짐 → 날짜 형식으로 통일
카테고리 표기 불일치 → 분석 불가 → 드롭다운 유효성 필수
금액에 “원” 붙임 → 합계 오류 → 숫자만 입력 + 통화 서식 적용

한 줄 요약: 원천입력은 단순, 코드표로 표준, 가공에서 자동, 대시보드로 한눈.

2) 자동화: 함수·폼·Apps Script로 ‘입력→집계→알림’이 굴러가게

설계가 끝났다면, 반복을 줄이는 자동화를 얹을 차례입니다. 구글 시트의 강점은 함수 기반 자동화(ARRAYFORMULA/FILTER/QUERY)와 폼 연동(모바일 입력), Apps Script(자바스크립트 기반 스케줄/이벤트 자동화)를 같은 파일에서 다룰 수 있다는 점입니다. 아래 레시피를 그대로 붙여서 시작해 보세요.

① 함수 자동화 — 손대지 않아도 확장되는 계산

월별 집계 동적 표 (가공 탭):

=QUERY(Transactions!A:H,
"select TEXT(A,'yyyy-mm'), sum(E)
 where B = '지출'
 group by TEXT(A,'yyyy-mm')
 order by TEXT(A,'yyyy-mm')", 0)

→ 날짜에서 월만 뽑아 지출 합계. 새 행이 생겨도 자동 반영.

카테고리별 월 집계:

=QUERY(Transactions!A:H,
"select C, sum(E)
 where B='지출' and TEXT(A,'yyyy-mm') = '"&TEXT(TODAY(),"yyyy-mm")&"'
 group by C
 order by sum(E) desc", 0)

고정비 자동 추출 (이월 확인용):

=FILTER(Transactions!A:H, Transactions!H:H="고정비", MONTH(Transactions!A:A)=MONTH(TODAY()))

② 구글 폼 연동 — 현장에서 10초 입력

메뉴 삽입 > 양식 → “지출 등록” 폼 생성: 날짜, 금액, 카테고리(드롭다운), 결제수단, 메모
모바일 홈 화면에 폼 바로가기 추가 → 밖에서 결제 직후 10초 입력 → 응답이 응답 시트로 자동 누적
응답 시트 → 원천입력 탭으로 ARRAYFORMULA로 미러링 하거나, Apps Script로 정리하여 이동

팁: 폼의 카테고리 목록은 코드표 범위를 연결해 유지보수 부담을 줄이세요(Google Forms의 질문에 목록 붙여 넣기).

③ Apps Script — 요약 메일·이체 분리·월말 스냅숏

① 매일 아침 지출 요약 메일 보내기:

function sendDailySummary() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Transactions');
  const last = sh.getLastRow();
  const data = sh.getRange(2,1,last-1,8).getValues(); // A:H
  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
  let spend = 0, income = 0;
  data.forEach(r => {
    const d = Utilities.formatDate(new Date(r[0]), Session.getScriptTimeZone(), 'yyyy-MM-dd');
    if (d === today) {
      if (r[1]==='지출') spend += Number(r[4]) || 0;
      if (r[1]==='수입') income += Number(r[4]) || 0;
    }
  });
  const body = `📊 ${today} 요약
- 지출: ${spend.toLocaleString()}원
- 수입: ${income.toLocaleString()}원
- 순현금흐름: ${(income+spend).toLocaleString()}원`;
  MailApp.sendEmail(Session.getActiveUser().getEmail(), `[가계부] ${today} 일일 요약`, body);
}

트리거: 확장 프로그램 > Apps Script → 왼쪽 시계 아이콘(트리거) → 시간 기반 매일 08:00.

② 이체 거래 자동 분리 (지출/수입 집계에 제외):

function markTransfers() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Transactions');
  const rng = sh.getRange(2,1,sh.getLastRow()-1,8).getValues();
  for (let i=0; i<rng.length; i++) {
    const memo = (rng[i][3]||'').toString().toLowerCase();
    if (rng[i][1]==='이체' || memo.includes('이체') || memo.includes('자체이체')) {
      // 카테고리를 '이체'로 표준화
      sh.getRange(i+2, 3).setValue('이체');
    }
  }
}

③ 월말 스냅숏(대시보드 값 백업):

function snapshotMonth() {
  const ss = SpreadsheetApp.getActive();
  const dash = ss.getSheetByName('대시보드');
  const snap = ss.getSheetByName('스냅샷') || ss.insertSheet('스냅샷');
  const month = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM');
  const spend = dash.getRange('B2').getValue(); // 예: 이번달 지출 카드 위치
  const income = dash.getRange('B3').getValue();
  snap.appendRow([month, spend, income, income+spend, new Date()]);
}

④ 자동화 체크리스트

입력: 폼 바로가기(모바일) / 시트 직접 입력
정리: ARRAYFORMULA로 파생열 / QUERY로 월별·카테고리별 집계
알림: Apps Script로 일일 요약 메일 / 월말 스냅숏
품질: 유효성·조건부 서식으로 오타/과다 지출 신호

현장 예시: 버스를 내리며 폼에 “교통 / 1,450원” 입력 → 10초 → 가공 탭 월합계 자동 반영 → 대시보드 도넛 차트의 교통 비중이 즉시 업데이트 → 다음 날 아침 메일에서 어제 순현금흐름 확인.

3) 분석·대시보드: 피벗·차트·목표·알림으로 ‘보이는 돈 관리’

입력과 자동화가 준비되면, 이제 중요한 건 보는 방식입니다. “이번 달 왜 많이 썼지?”라는 질문에 5초 안에 답이 나오게 만드는 게 대시보드의 역할입니다. 아래 구성을 그대로 배치해 보세요.

① 상단 KPI 카드(3~5개)

이번 달 지출, 이번 달 수입, 순현금흐름, 고정비 합계, 예산 대비%
숫자 포맷: 통화(₩), 음수는 빨강. 예산 대비가 90%↑면 주황, 110%↑면 빨강 배경.

② 월별 추세(선/영역 차트)

행=월, 값=지출/수입 합계 → 선형으로 두 지표 동시 표시
목표선(기준선)을 함께 넣어 “괜찮다/위험하다”를 즉시 인지

③ 카테고리 비중(도넛/누적 막대)

이번 달 카테고리별 지출 합계 → 도넛 차트
시간 변화까지 보고 싶다면 월 ×카테고리 누적 막대로 전환

④ 고정비/변동비 분해(막대)

고정비 항목(주거·통신·구독·보험 등)만 따로 막대 → 줄일 수 없는 베이스 파악
변동비(식비·카페·문화·쇼핑 등)에서 절감 후보 찾기

⑤ 현금흐름 캘린더(일자별 막대)

날짜별 순현금흐름(수입+지출)을 막대로 표시 → 특정 요일 패턴/말일 지출 몰림 등 확인

⑥ 피벗 테이블 3종

월 ×카테고리: 어디가 늘었는지 추세 파악
결제수단 ×카테고리: 카드/현금 비중, 수수료 영향 파악
계좌 ×유형: 계좌별 잔액 흐름 추적(이체 제외)

⑦ QUERY로 ‘질문형’ 리포트

=QUERY(Transactions!A:H,
"select C, sum(E)
 where B='지출' and TEXT(A,'yyyy-mm')='"&TEXT(TODAY(),"yyyy-mm")&"'
 group by C
 order by sum(E) desc
 limit 5",0)

→ “이번 달 지출 TOP5 카테고리” 표. 아래에 “지난달 TOP5”도 나란히 배치해 비교.

⑧ 목표·경보(알림)

예산 시트에 카테고리별 월 예산을 두고, 대시보드에서 진행률(실적/예산)을 계산
조건부 서식으로 90%/100% 경계선 색
알림 규칙(도구 > 알림 규칙)으로 대시보드 범위 변경 시 이메일 알림

⑨ 레이아웃 팁(가독성)

좌측 상단: KPI 카드 / 우측 상단: 도넛(비중) / 중앙: 월 추세(선) / 하단: 카테고리 TOP5 표
색상은 브랜드 4~5색으로 고정, 강조 1색만 사용
폰트 크기: 제목 16px, 축/범례 12~13px, KPI는 크게

⑩ 시뮬레이션 박스(“일주일 커피 끊으면?”)

절감액(원) = (하루 커피값 × 남은 일수) → 실시간으로 월 지출 예상치에서 차감 표시

시트 우측에 입력칸 2개(하루 커피값, 남은 일수)를 두고, 이번 달 지출 예상에서 자동 반영해 ‘보이는 절감 동기’를 만들어 주세요.

대시보드 한 줄 규칙: 보여주지 말고, 결론을 보이게. “이번 달 식비 과다(예산 125%, +12만)”처럼 카드 텍스트에 결론을 써 주세요.

결론: ‘오늘의 한 줄’이 내일의 선택을 바꾼다

가계부 자동화의 목적은 예쁘게 정리된 표가 아닙니다. 더 빠르고, 덜 아프게, 정확히 판단하기입니다. 오늘 한 줄을 입력하면, 내일의 대시보드가 말해 줍니다. “이번 달은 구독·식비가 문제야”, “카페 지출이 주중에 몰려”, “고정비는 손대기 어렵고 변동비에서 10%만 줄이면 돼.” 자동화는 이 판단을 매일 가능하게 만드는 최소한의 장치입니다. 폼으로 입력을 가볍게, 유효성으로 오타를 없애고, ARRAYFORMULA/QUERY로 보조표를 자동 확장, 피벗·차트로 시각화, Apps Script로 요약·스냅숏·알림까지 연결하면, 가계부는 더 이상 주말의 숙제가 아닙니다.

마지막으로 3단계 실행 플랜을 남깁니다. 1단계(오늘): 탭 구조(원천/코드표/가공/대시보드) 만들고 드롭다운·조건부 서식 적용, 폼 만들기. 2단계(이번 주): 월/카테고리 집계 QUERY, KPI 카드·도넛·선 차트 구성, 일일 요약 메일 트리거 설정. 3단계(이번 달): 예산 시트 연동, 목표/경보 색상, 월말 스냅숏과 “TOP5 변동 리포트” 자동화. 어려운 기술보다 중요한 건 꾸준한 입력입니다. 오늘의 한 줄이 쌓여 한 달 뒤, “왜 돈이 남지 않았나?”의 막연함 대신 명확한 답을 보여줄 것입니다. 그리고 그 답은 다음 달의 더 현명한 소비로 이어질 겁니다.

richjin7285

구글 시트로 가계부 자동화하기

1) 설계: 표 구조·카테고리·유효성·조건부 서식으로 ‘입력 실수’부터 줄이기

2) 자동화: 함수·폼·Apps Script로 ‘입력→집계→알림’이 굴러가게

3) 분석·대시보드: 피벗·차트·목표·알림으로 ‘보이는 돈 관리’

결론: ‘오늘의 한 줄’이 내일의 선택을 바꾼다

티스토리툴바