실전 과제 3종: 지출관리·재고관리·프로젝트 일정표 만드는 방법과 템플릿 설계(엑셀·구글시트 활용)

지금까지 테이블 구조, 데이터 청소, 조회, 요약(SUMIFS), 피벗, 드롭다운, 조건부 서식까지 실무 핵심 도구에 대해 설명드렸습니다. 이제는 지금까지 배운 것들을 활용해 결과물을 만드는 방법에 대해 알아보겠습니다. 이번 편에서는 실무에서 바로 활용하기 좋은 3가지 템플릿 과제를 제공해 드립니다. 단순히 표만 제공하는 것이 아니라 원본 데이터 설계 → 입력 표준화 → 자동 경고 → 요약/대시보드 흐름으로 총정리 해드리겠습니다.

과제: 지출관리 / 재고관리 / 프로젝트 일정표

0. 공통 원칙: 템플릿은 “원본”과 “보고서”를 분리한다

• 원본(Data): 입력/누적되는 표(테이블 구조)
• 목록(List): 드롭다운 항목(카테고리, 담당자 등)
• 요약(Summary): SUMIFS/피벗/대시보드

이 3시트 구조만 지켜도 유지보수 난이도가 확 내려갑니다.

과제 1) 지출관리 템플릿(개인/팀 공용)

1-1) 원본 시트(Data) 컬럼 설계

아래처럼 “한 행 = 한 지출”로 쌓습니다.

• A: 날짜
• B: 구분(개인/팀/법인카드 등)
• C: 카테고리(식비/교통/소모품/교육/기타)
• D: 결제수단(현금/카드/이체)
• E: 금액(숫자만)
• F: 메모(선택)
• G: 증빙(파일 링크/번호 등, 선택)

1-2) 입력 표준화(드롭다운)

• 구분(B열), 카테고리(C열), 결제수단(D열)을 “목록(List) 시트” 범위와 연결
• 금액(E열)은 숫자만(통화 기호는 서식으로 표시)

1-3) 조건부 서식(실수 방지)

• 필수값 누락: 날짜/카테고리/금액 빈칸이면 빨강
• 비정상 금액: 0 또는 음수 입력 시 강조(환불 처리 규칙이 없다면)

1-4) 요약 시트(Summary): 월별/카테고리별 합계

요약 시트에서 “선택 월”을 K1(예: 2025-12-01)에 두고, 월별 합계는 다음처럼 계산합니다.

• 월 합계: =SUMIFS(Data!$E:$E, Data!$A:$A, ">="&$K$1, Data!$A:$A, "<"&EDATE($K$1,1))

카테고리별 합계는 카테고리 셀(예: A5)에 카테고리명이 있을 때:

• =SUMIFS(Data!$E:$E, Data!$C:$C, $A5, Data!$A:$A, ">="&$K$1, Data!$A:$A, "<"&EDATE($K$1,1))

1-5) 피벗 확장(선택)

• 행: 카테고리
• 열: 월(날짜 그룹)
• 값: 금액 합계
• 필터: 구분(개인/팀)

과제 2) 재고관리 템플릿(입출고 기반)

재고는 “현재 재고만 적는 방식”보다, 입출고 로그를 쌓고 요약으로 현재 재고를 계산하는 방식이 안정적입니다.

2-1) 원본 시트(Data) 컬럼 설계(입출고 로그)

• A: 날짜
• B: 품목코드
• C: 품목명
• D: 구분(입고/출고/조정)
• E: 수량(정수)
• F: 단가(선택)
• G: 창고/위치(선택)
• H: 비고(선택)

2-2) 입력 표준화(드롭다운 + 유효성)

• 구분(D열): 입고/출고/조정 드롭다운
• 수량(E열): 0보다 큰 정수만 허용(조정은 별 규칙 운영 가능)
• 품목코드/품목명은 마스터(품목 표)로 관리하면 조회 자동화 가능

2-3) 조회 자동화(선택): 품목코드로 품목명 자동 채우기

품목 마스터가 Sheet2에 있을 때(코드=A열, 품목명=B열):

• 엑셀: =IFERROR(XLOOKUP(B2,품목!$A:$A,품목!$B:$B,""),"")
• 구글시트: =IFERROR(INDEX(품목!B:B, MATCH(B2, 품목!A:A, 0)),"")

2-4) 현재 재고 계산(요약 시트)

핵심은 “입고는 +, 출고는 -”로 합산하는 것입니다. 가장 쉬운 방식은 원본에 “재고변동” 열을 하나 두는 겁니다.

원본 I열(재고변동) 예시:

• =IF(D2="입고",E2,IF(D2="출고",-E2,0))

그러면 요약에서 품목코드(예: A5)에 대해 현재 재고는:

• =SUMIFS(Data!$I:$I, Data!$B:$B, $A5)

2-5) 경고 자동화(조건부 서식)

• 안전재고 이하면 노랑/빨강
• 재고가 음수면 빨강(출고가 선반영되었거나 입력 오류 가능)

안전재고는 “품목 마스터”에 별도 열로 관리(예: 품목!C열=안전재고)하면 확장성이 좋습니다.

과제 3) 프로젝트 일정표 템플릿(마감/상태 자동화)

프로젝트 일정표는 보기 좋은 간트차트보다, 먼저 마감/지연을 자동으로 잡아주는 표가 실무에 더 잘 먹힙니다.

3-1) 원본 시트(Data) 컬럼 설계

• A: 프로젝트명
• B: 작업(Task)
• C: 담당자(드롭다운)
• D: 시작일
• E: 마감일
• F: 완료(구글시트 체크박스 또는 엑셀 상태 드롭다운)
• G: 우선순위(높음/보통/낮음)
• H: 상태(자동)
• I: 남은일수(자동)

3-2) 상태/남은일수 자동 수식(구글시트 기준)

예: E2=마감일, F2=체크박스(TRUE/FALSE)

• 남은일수(I2): =IF(E2="","",E2-TODAY())
• 상태(H2): =IF(F2=TRUE,"완료",IF(E2="","미정",IF(E2<TODAY(),"지연",IF(E2<=TODAY()+3,"임박","진행중"))))

엑셀에서도 동일 논리로 구현 가능하며, 체크박스 대신 “완료/진행중” 드롭다운을 써도 됩니다.

3-3) 조건부 서식(프로젝트 관리의 핵심)

• 지연이면 빨강: =AND($H2="지연",$F2<>TRUE)
• 임박이면 노랑: =AND($H2="임박",$F2<>TRUE)
• 완료면 회색(선택): =$H2="완료"

3-4) 요약 시트: 담당자별 미완료/지연 건수

담당자 이름이 A5에 있을 때:

• 미완료 건수: =COUNTIFS(Data!$C:$C,$A5, Data!$F:$F,FALSE)
• 지연 건수: =COUNTIFS(Data!$C:$C,$A5, Data!$H:$H,"지연")

3-5) (선택) 간트차트 느낌 내기(구글시트/엑셀 공통)

진짜 간트차트를 만들지 않아도, 조건부 서식으로 기간 막대를 만들 수 있습니다. 다만 초반에는 상태/지연 관리가 우선이므로, 팀이 익숙해진 다음 단계로 추천합니다.

마지막 점검: 템플릿 품질 체크리스트(필수 10개)

1. 원본 데이터는 테이블 구조인가(빈 행/병합/소계 없음)?
2. 입력 열은 드롭다운/유효성 검사로 표준화했나?
3. 숫자/날짜 타입이 깨끗한가(텍스트 혼입 없음)?
4. 필수값 누락을 조건부 서식으로 잡았나?
5. 원본과 요약이 시트로 분리되어 있나?
6. 요약표는 자동 업데이트되나(SUMIFS/피벗)?
7. 오류가 발생하면 ‘보이게’ 설계했나(숨김 남발 금지)?
8. 목록 시트는 누가 관리하는지 정했나?
9. 새 행이 늘어나도 규칙이 따라오나(범위 설정)?
10. 팀이 이해할 수 있는 용어/상태 정의를 문서화했나?

총정리

템플릿을 잘 만들어두면 이후 업무는 입력만 하면 되니 굉장히 편리해집니다. 오늘 말씀드린 과제 3종은 서로 다른 업무처럼 보이지만 공통 구조는 동일합니다. 원본을 깨끗하게 쌓고 → 입력을 표준화하고 → 자동 경고/요약으로 관리하시면 됩니다.