엑셀 가계부 자동 계산 완성 서식 만드는 방법.

안녕하세요. 행아아빠입니다.

오늘 여러분들께 설명드릴 엑셀 내용으로는 엑셀 가계부 서식을 작성하고 가계부를 작성한 데이터를 기초로 하여 연간, 월간 요약본을 확인할 수 있는 계산 수식을 작성하여 자금의 흐름을 한눈에 파악할 수 있는 가계부 서식과 수식을 작성해 보려고 합니다.

 

이를 잘 활용한다면 가계부 뿐만 아니라 자금의 흐름을 파악할 수 있는 간단한 회계 장부도 만들 수 있을것으로 기대해 볼 수 있을것 같습니다.

 

많은 분들에게 오늘 내용 도움이 될 수 있기를 바라면서 오늘 내용에서는 간단한 수식과 데이터 유효성 검사를 활용하여 드롭다운 목록 만들기, 데이터 유효성 검사 참조 영역 범위를 동적 범위로 활용하는 방법을 함께 익힐 수 있는 가이드 역활이 될 수 있기를 바라겠습니다.

 

▶ 목차.

1. 엑셀 가계부 서식 작성하기.

 1-1. "참고" 시트 작성 내용.

 1-2. "연간수익지출요약" 시트 작성 내용과 수식 작성.

 1-3. "월간수익지출요약" 시트 작성 내용과 수식 작성.

 1-4. "저축성지출" 시트 작성 내용과 수식 작성.

 1-5. "수익지출내역" 시트 작성 내용과 수식 작성.

 

▶ 오늘 내용과 함께 볼만한 이전 내용 글.

1. 엑셀 데이터 유효성 검사 - 드롭다운 목록 형식 만들기.

2. 엑셀 offset 함수 동적 범위 수식으로 활용.

 

 

엑셀 가계부 자동 계산 완성 서식 만드는 방법 썸네일 이미지.

1. 엑셀 가계부 서식 만들기.

엑셀 가계부 서식을 간단하게 작성해 보기 위해서 필요한것이 용돈기입장과 같이 일기장처럼 수익과 지출 내역을 발생한 내용 그대로 작성 할 수 있는 수익지출내역서와 수익지출 내역서에 입력된 데이터를 기초로 연간, 월간 자금 흐름 요약본, 그리고 적금과 같은 비유동성 자금의 흐름을 확인할 수 있어야 하고 특히 신용카드 대금의 경우에는 보통 매월초 ~ 매월 말일까지 신용카드 사용액을 결제 대금으로 결제 하는 경우 보다는 당월 특정일 ~ 익월 특정일까지의 이용일을 카드 대금으로 결제 하는 경우가 발생하는등의 요소를 고려하여 서식을 작성해 봐야겠습니다.

 

따라서 엑셀 가계부 서식은 참고 시트, 연간수익지출요약 시트, 월간수익지출요약 시트, 저축정지출 시트, 수익지출내역 시트로 나누어 서식을 작성해 보고자 합니다.

 

각각의 시트에 작성되는 내용을 살펴보고 각각 작성된 자동 계산 수식을 다음으로 확인해 보기로 합니다.

 

 

1-1. "참고" 시트 작성 내용.

엑셀 가계부 참고 시트 작성 내용 이미지.

"참고" 시트에서는 수익과 지출의 내역을 분류 할 수 있는 항목과 신용카드 종류 그리고 신용카드별 이용일자와 대금 결제 일자를 작성하여 수익지출내역 시트에서 활용할 수 있는 참고 자료들을 작성해 주도록 합니다.

 

저는 항목 분류를 수익항목으로 기본수익과 기타수익 그리고 하위 분류 항목으로 기본수익에는 급여, 보너스, 수수료, 기타.. 기타 수익 하위 분류 항목으로는 이자 소득, 배당금등으로 분류하고 지출 항목은 공공요금, 여행, 일상생활등으로 분류 하고 하위 분류 항목으로 공공요금에는 전화, TV, 인터넷등과 같이 기본 분류 항목과 하위 분류 항목으로 분류 하여 작성했습니다.

 

따라서 사용자가 필요로 하는 항목으로 다시 분류 하여 사용하는것이 행아아빠가 제공하는 샘플 서식을 유용하게 활용할 수 있는 방법이 될 수 있을것 같습니다.

 

1-2. "연간수익지출요약" 시트 작성 내용과 수식 작성.

엑셀 가계부 연간수익지출요약 시트 작성 내용 이미지.

 

 

"연간수익지출요약" 시트에서는 직접 입력해야 하는 데이터는 없고, 수익지출내역 시트에서 작성한 내용을 참고 하여 월별 수익과 지출 그리고 분류 항목에 따른 금액 합계를 수식에 의해서 자동 계산하여 보여줌으로써 1년간 분류 항목별 금액을 한눈에 확인해 볼 수 있는 요약 시트를 완성 하여 해당 내용을 토대로 다음년도 예산을 계획하는 자료로 활용할 수 있을것 같습니다.

 

수식은 각각의 기준셀을 다음과 같이 작성하고 수식 계산으로 결과값을 표기 해야 하는 셀까지 수식 복사 합니다.

B3셀 수식 =SUMIFS(수익지출내역!$D$4:$D$1000,수익지출내역!$A$4:$A$1000,연간수익지출요약!B$2)

B4셀 수식 =SUMIFS(수익지출내역!$E$4:$E$1000,수익지출내역!$A$4:$A$1000,연간수익지출요약!B$2)

 

B9셀 수식 =SUMIFS(수익지출내역!$D$4:$D$1000,수익지출내역!$A$4:$A$1000,연간수익지출요약!B$8,수익지출내역!$H$4:$H$1000,연간수익지출요약!$A9)

B10셀 수식 =SUMIFS(수익지출내역!$D$4:$D$1000,수익지출내역!$A$4:$A$1000,연간수익지출요약!B$8,수익지출내역!$H$4:$H$1000,연간수익지출요약!$A10)

 

B16셀 수식 =SUMIFS(수익지출내역!$E$4:$E$1000,수익지출내역!$A$4:$A$1000,연간수익지출요약!B$14,수익지출내역!$H$4:$H$1000,연간수익지출요약!$A16)

 

그리고 B5셀은 수익 -지출로 =SUM(B3-B4)와 같이 수식을 작성하고 B11셀은 =SUM(B9:B10), B15셀은 =SUM(B9:B10)의 수식으로 간단하게 sum함수로 합계 계산 수식을 작성하여 계산 수식을 완성할 수 있습니다.

 

1-3. "월간수익지출요약" 시트 작성 내용과 수식 작성.

엑셀 가계부 월간수익지출요약 시트 작성 내용 이미지.

"월간수익지출요약" 시트에서는 연간수익지출요약 시트와 마찬가지로 월간 수익 지출 내역을 분류 항목에 따라 사용내역을 확인해 볼 수 있고 신용카드 종류별로 사용 금액을 신용카드 이용일에 따른 결제 대금을 확인해 볼 수 있습니다.

 

 

그리고 중요한것은 A1, B2, A3셀은 직접 입력해줘야 하는 셀로 A1셀은 연도, B2셀은 해당 년도에서 시작하는 초기 잔고 금액, A3셀은 "월간수익지출요약"시트를 확인할 해당년도 월을 직접 입력해 주어야 한다는것입니다.

 

수식은 각각의 기준셀을 다음과 같이 작성하고 수식 계산으로 결과값을 표기 해야 하는 셀까지 수식 복사 합니다.

B6, 7, 10, 11셀은 각각 지출과 수익의 분류 항목별 계획과 실제 금액의 셀 범위를 sum함수로 간단하게 합계 계산 수식을 작성해 주도록 합니다.

 

G7셀은 카드 종류별로 대금 결제일자를 결과값으로 가져오기 위해서 =VLOOKUP($F7,참조!$F$2:$K$3,6,FALSE)수식을 작성하고 G8셀은 수식 복사 하여 결과값을 표기 할 수 있습니다.

 

카드 대금 결제 수식은 신용카드 이용일자별로 수익지출내역 시트에서 지출 내역의 합계를 계산해야 하는데 유의할점으로 신용카드 이용일자가 전월 ~ 익월 특정일까지 이거나 당월 ~ 당월까지인경우로 나누어 수식을 작성해야 합니다.

 

샘플 예제 서식에서는 신한카드는 카드이용일자가 신한카드는 전월 ~ 익월 특정일까지이고, 우리카드의 경우에는 당월 ~ 당월까지인경우로 각각 나누어 수식을 다음과 같이 작성 할 수 있습니다.

H7셀 수식 =SUMPRODUCT(수익지출내역!$E$4:$E$1000,(수익지출내역!$A$4:$A$1000=월간수익지출요약!$A$3)*(수익지출내역!$F$4:$F$1000=월간수익지출요약!$F7)*(수익지출내역!$B$4:$B$1000<=참조!$J$2))+
SUMPRODUCT(수익지출내역!$E$4:$E$1000,(수익지출내역!$F$4:$F$1000=월간수익지출요약!$F7)*(수익지출내역!$A$4:$A$1000=SUM(월간수익지출요약!$A$3-1))*(수익지출내역!$B$4:$B$1000>=참조!$H$2))

 

H8셀 수식 =SUMPRODUCT(수익지출내역!$E$4:$E$1000,(수익지출내역!$F$4:$F$1000=월간수익지출요약!$F8)*(수익지출내역!$A$4:$A$1000=SUM(월간수익지출요약!$A$3-1))*(수익지출내역!$B$4:$B$1000>=참조!$H$3)*(수익지출내역!B4:B1000<=참조!$J$3))

 

C16셀의 실제 지출 내역을 각 분류 항목별 합계 금액을 계산 하기 위한 수식은 다음과 같이 작성합니다.

=SUMIFS(수익지출내역!$E$4:$E$1000,수익지출내역!$H$4:$H$1000,월간수익지출요약!$A16,수익지출내역!$A$4:$A$1000,월간수익지출요약!$A$3)

 

H16셀의 수익 항목별 거래 내역을 계산 하는 수식은 다음과 같이 작성합니다.

=SUMIFS(수익지출내역!$E$4:$E$1000,수익지출내역!$H$4:$H$1000,월간수익지출요약!$A16,수익지출내역!$A$4:$A$1000,월간수익지출요약!$A$3)

 

나머지 항목들은 간단 합계 수식이기 때문에 어렵지 않게 작성 할 수 있고, 범위를 참조 하는 수식은 수익지출내역 시트의 1,000행까지만을 참조 하여 계산하기 때문에 수익지출내역에 작성된 행이 1,000행을 넘어갈 경우에는 참조 범위 영역을 1,000행 이상으로 변경해 주어 사용합니다.

 

 

1-4. "저축성지출" 시트 작성 내용과 수식 작성.

엑셀 가계부 저축성지출 시트 작성 내용 이미지.

"저축성지출"시트에서는 수익지출내역에서 지출 내역중 적금1 ~ 적금6의 내용으로 작성된 금액 각각 내용에 해당하는 금액 합계를 월별로 보여주는 시트로 비유동성 자산이지만 적금 만기가 될 경우 유동 자산이 될 금액으로 유동 자산 뿐만 아니라 비유동자산 현황을 확인할 수 있는 시트입니다.

 

 

수식은 각각의 기준셀을 다음과 같이 작성하고 수식 계산으로 결과값을 표기 해야 하는 셀까지 수식 복사 합니다.

B3셀 기준으로 다중 조건의 합계 함수 sumifs함수를 사용해서 다음과 같이 작성 합니다.

=SUMIFS(수익지출내역!$E$4:$E$1000,수익지출내역!$G$4:$G$1000,저축성지출!B$1,수익지출내역!$A$4:$A$1000,저축성지출!$A3)

 

그리고 2행의 수식은 간단 sum 합계 함수를 사용해서 계산 수식을 완성시키도록 합니다.

 

1-5. "수익지출내역" 시트 작성 내용과 수식 작성, 그리고 데이터 유효성 검사 동적 범위 수식 작성.

엑셀 가계부 수익지출내역 시트 작성 내용 이미지.

"수익지출내역" 시트에서는 직접 입력해야 하는 데이터가 가장 많은 시트로 용돈기입장, 일기장처럼 수익과 지출이 발생한 일자별로 거래 내역을 직접 입력하는 시트입니다.

 

따라서 수익지출내역 시트에 작성된 수익과 지출 거래 내역의 내용을 토대로 연간수익지출요약, 월간수익지출요약, 저축성지출의 요약 내용을 계산하여 표시 할 수 있습니다.

 

 

"수익지출내역" 시트에 직접 입력해야할 내용으로는 C열에 날짜, D열에는 수익 거래 금액, E열에는 지출 거래 금액, G열에 수익과 지출 거래 내역에 대한 설명을 직접 작성해 주고, F, H, I열에는 데이터 유효성 검사 드롭다운 목록 형식을 적용하여 F열에는 신용카드 종류, H열에는 수익 또는 거래 내역에 대한 기본 분류 항목, I열에는 H열에 분류된 항목의 세부 하위 항목을 드롭 다운 목록 형식에서 선택할 수 있도록 하고 드롭다운 목록 데이터 범위는 참조 시트에 작성한 분류항목과 하위 분류 항목을 참조 합니다.

 

그리고 A열과 B열은 C열에 입력된 날짜의 월과 일자를 수식으로 작성하여 자동 완성 되도록 하고, J열은 해당 년도 초기 잔액으로 시작하여 수익과 지출의 거래내역이 진행되면서 자금의 잔고를 확인할 수 있도록 "수익지출내역" 시트에 처음 작성하는 행에는 초기잔액 + 수익 + 지출로 수식을 작성 하고 이후 셀부터는 바로 윗셀 잔고 + 수익 - 잔액으로 수식을 작성하여 수익과 지출 거래내역을 작성할때마다 잔고를 확인할 수 있도록 합니다.

 

수식은 각각의 기준셀을 다음과 같이 작성하고 수식 계산으로 결과값을 표기 해야 하는 셀까지 수식 복사 합니다.

우선 셀에 직접 수식을 작성하는 셀은 다음과 같이 수식을 작성하고 필요한 행까지 수식 복사 하여 사용할 수 있습니다.

A4셀 수식 =IF(C4="","",MONTH(C4)), B열 수식 =IF(C4="","",DAY(C4))으로 각각 C열에 작성된 날짜의 월과 일자를 각각 추출한 결과값을 표기 할 수 있도록 수식을 작성 합니다.

 

J4셀은 수익지출내역 시트에서 처음으로 수익과 지출 거래 내역을 작성하여 잔고를 계산 할 수 있도록 다음과 같이 수식을 작성 합니다.

=IF(C4="",0,SUM(+D1+D4-E4))

 

그리고 J5셀 이하부터는 J4셀 잔고를 기준으로 5행부터 입력되는 수익 지출 거래 내역에 의한 잔고를 계산 하기 위해서 다음과 같이 수식을 작성하도록 합니다.

=IF(C5="","",SUM(J4+D5-E5))

 

F열, H열, I열은 데이터 유효성 검사 드롭다운 목록 형식으로 적용을 해야 하는데 I열을 제외하고 F열과 H열은 참조 시트의 원본 데이터 범위를 참조 범위 영역으로 하여 적용할 수 있습니다.

F열의 참조 범위 영역은 참조 시트의 =참조!$F$2:$F$3 범위 영역으로 설정해 주도록 하고, H열의 참조 범위 영역은 참조 시트의 =참조!$A$2:$A$17 범위 영역으로 설정하여 드롭다운 목록 형식을 적용시켜 주도록 합니다.

 

I열의 데이터 유효성 검사 드롭다운 목록 형식은 참조 시트의 C열과 D열을 참조 범위 영역으로 하는데 참조 시트의 C열과 D열에는 각각 중복되는 데이터 값으로 작성되어 있고 하위 분류 항목 역시 일부 중복되는 데이터 값이 작성되어 있습니다.

 

따라서 H열의 분류에 따른 하위 분류 항목에 대해서만 범위를 참조 할 수 있도록 동적 범위를 참조 할 수 있도록 하여 드롭다운 목록 형식을 작성하기 위해서 offset함수로 참조 영역 범위 수식을 작성해야 하기 때문에 다음과 같이 참조 범위 영역 수식을 작성해 주도록 합니다.

=OFFSET(참조!$D$1,MATCH($H4,참조!$C$2:$C$101,0),0,COUNTIFS(참조!$C:$C,$H4),1)

 

만약 원본 참조 범위 영역 C열과 D열의 범위 영역을 참조 범위로 설정할 경우 중복되는 데이터 값이 드롭다운 목록에 모두 포함되서 나타나기 때문에 드롭다운 목록으로 작성한 의미가 없다고 볼 수 있고, 만약 공백이 포함된 셀이 포함되는 참조 범위 영역으로 설정된다면 드롭다운 목록에 공백이 포함되어 있어 편리하게 목록에서 데이터 값을 선택하기 위해 작성한 드롭다운 목록 형식이 번거로운 형식으로 변질되기 때문에 공백과 공란을 목록에서 포함하지 않고 중복되는 값을 포함하지 않도록 참조 범위 영역이 H열에서 선택된 데이터 값에 따라 동적 범위로 움직일 수 있도록 offset함수로 동적 범위 수식을 작성해 주는것으로 볼 수 있습니다.

 

이상으로 간단한 엑셀 가계부 자동 계산 완성 서식과 수식, 그리고 추가로 데이터 유효성 검사 드롭다운 목록 형식 적용과 동적 범위를 작성 하는 수식을 데이터 유효성 검사 드롭다운 목록 참조 범위 영역에 적용하는 수식까지 작성해 보았습니다.

 

오늘 내용도 많은 분들에게 도움이 될 수 있는 내용이 될 수 있기를 바라겠습니다.