안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
엑셀(excel) 작업이 대부분 회사 또는 직장에서 많이 사용되는것이 엑셀을 데이터 작업을 하는데 아주 유용하기 때문일것입니다.
이번 기록에서는 개인 용무로 엑셀(excel) 데이터를 사용할 수 있는 엑셀 가계부 간단 서식과 가계부에 입력된 데이터를 기초로 월간, 연간의 수익과 지출의 요약 분석 데이터를 자동으로 계산하고 완성해주는 수식을 작성해 보고자 합니다.
샘플 예제 서식으로 간단하게 작성한 서식이기 때문에 사용자의 환경에 모두 만족할수는 없겠지만 수식이 작성된 내용을 확인해 보면 사용자의 환경에 맞게 사용할 수 있도록 작성되어 있는 수식을 수정하는것으로 사용자의 환경에 맞는 가계부로 수정하여 사용할 수 있을것 같습니다.
엑셀 가계부 서식과 자동 계산 수식을 함께 살펴 보기로 합니다.
1. 엑셀 가계부 서식.
1-1. 참조 시트.
엑셀 가계부 서식 참조 시트에 신용카드 종류와 신용카드 결제일별로 이용일을 입력하고, 가계부 내역 건별로 분류할 항목들과 하위 항목들을 기본적으로 작성하여 연간, 월별, 엑셀 가계부 내역서에 참조 영역으로 활용될 수 있습니다.
1-2. 연간수익지출요약 시트.
엑셀 가계부 서식 연간수익지출요약 시트에서는 연간 수익과 지출의 분류에 따른 금액이 수익 지출 내역을 입력한 내용을 각 분류별로 합산액이 월별로 모두 표기 되어 1년간 분류별 수익과 지출 금액을 요약본으로 한눈에 확인할 수 있습니다.
1-3. 월간수익지출요약 시트.
엑셀 가계부 서식 월간수익지출요약 시트에서는 A1셀에는 년도를 B2셀에는 연간 초기 잔고 또는 시작 잔고, A3셀에는 가계부 요약본을 확인하고자 하는 월을 직접 입력해 주고, 월간 지출과 수익, 그리고 카드 종류별 결제 금액, 수익과 지출 분류에 따른 금액을 수익 지출 내역에 입력한 내용을 기초로 자동으로 계산되어 한눈에 볼 수 있는 요약본이 생성됩니다.
1-4. 저축성지출 시트.
엑셀 가계부 서식 저축성지출 시트에서는 수익과 지출 내역서에 입력한 내용중 지출 금액중 설명 내용이 적금1 ~ 적금6으로 작성된 금액의 요약과 합계금액을 자동 계산 완성하여 확인할 수 있습니다.
1-5. 수익지출내역 시트.
엑셀 가계부 서식 수익지출내역 시트에서는 수익과 지출에 대한 내역을 대부분 직접 입력해 주어야 하는 시트로 C열에는 날짜, D열에는 수익 금액, E열은 지출 금액, F열은 사용 카드 종류, 설명은 수익과 지출에 대한 내용, H열에는 수익과 지출을 분류 하는 카테고리, I열에는 H열에서 분류한 수익과 지출의 하위 카테고리, J열은 초기 잔액부터 수익과 지출을 차감한 차액을 계산 하는 열로 분류 할 수 있습니다.
D1셀의 초기 잔액은 월간수익지출요약 시트에서 입력한 해당년도 초기 잔액을 불러오는 수식을 입력한 값, A열과 B열은 각각 C열에 입력한 날짜를 참조 하여 해당 날짜의 월과 일자를 수식에 의해서 결과값이 반영되고 J열은 초기 잔액 + 수익 - 지출로 잔액을 계산 하는 수식이 작성되어 결과값을 자동으로 반열합니다.
그리고 F열에는 앞서 작성한 참조 시트의 신용카드 종류를 데이터유효성 드롭다운 형식으로 적용되어 카드 종류를 선택할 수 있고, H열과 I열 역시 데이터 유효성 검사 드롭다운 형식이 적용되어 해당 수익과 지출에 대한 분류를 참조시트에 작성한 분류를 선택할 수 있도록 합니다.
나머지 날짜, 수익, 지출, 설명은 직접 입력해 주어야 하는 데이터 입력값이어야 합니다.
2. 엑셀 가계부 서식 자동 계산 완성 수식 작성.
엑셀 가계부 서식을 자동 완성 할 수 있는 수식은 다음과 같이 작성 할 수 있습니다.
| 1. 연간 수익 지출 요약 시트. 1-1. B3셀 =SUMIFS(수익지출내역!$D$4:$D$1000,수익지출내역!$A$4:$A$1000,연간수익지출요약!B$2) 1-2. B4셀 =SUMIFS(수익지출내역!$E$4:$E$1000,수익지출내역!$A$4:$A$1000,연간수익지출요약!B$2) 1-3. B5셀 =B3-B4 1-4. B9셀 =SUMIFS(수익지출내역!$D$4:$D$1000, 수익지출내역!$A$4:$A$1000,연간수익지출요약!B$8,수익지출내역!$H$4:$H$1000,연간수익지출요약!$A9) 1-5. B10셀 =SUMIFS(수익지출내역!$D$4:$D$1000, 수익지출내역!$A$4:$A$1000,연간수익지출요약!B$8,수익지출내역!$H$4:$H$1000,연간수익지출요약!$A10) 1-6. B11셀 =SUM(B9:B10) 1-7. B15셀 =SUM(B16:B29) 1-8. B16셀 =SUMIFS(수익지출내역!$E$4:$E$1000, 수익지출내역!$A$4:$A$1000,연간수익지출요약!B$14,수익지출내역!$H$4:$H$1000,연간수익지출요약!$A16) |
| 2. 월간 수익 지출 요약 시트. 2-1. B6셀 =SUM(B16:B29) 2-2. B7셀 =SUM(C16:C29) 2-3. B10셀 =SUM(G16:G29) 2-4. B11셀 =SUM(H16:H29) 2-5. G7셀 =VLOOKUP($F7,참조!$F$2:$K$3,6,FALSE) 2-6. G8셀 =VLOOKUP($F8,참조!$F$2:$K$3,6,FALSE) 2-7. 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)) 2-8. 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)) 2-9. C16셀 =SUMIFS(수익지출내역!$E$4:$E$1000, 수익지출내역!$H$4:$H$1000,월간수익지출요약!$A16,수익지출내역!$A$4:$A$1000,월간수익지출요약!$A$3) 2-10. H16셀 =SUMIFS(수익지출내역!$D$4:$D$1000, 수익지출내역!$H$4:$H$1000,월간수익지출요약!$F16,수익지출내역!$A$4:$A$1000,월간수익지출요약!$A$3) |
신용카드 결제 금액을 합산하는 수식이 H7셀 신한카드와 H8셀 우리카드의 계산 수식이 SUMPRODUCT함수로 작성되는것은 동일하지만 서로 약간의 차이가 있는데 이유는 각 카드의 이용일이 결제 대금으로 계산되는것이 틀리다는것으로 신한카드는 참조 시트에서 확인해 보면 당월 12일부터 익월 11일까지의 이용대금을 매월 25일에 결제 해야 하고 우리 카드는 당월 1일부터 매월 말일까지 사용한 이용대금을 매월 10일에 결제 해야 하는것으로 보통 신용카드는 해당월에 지출이 마무리 되는것이 아니고 자신이 설정한 방법에 따라 이용일이 달라지기 때문에 이용일이 당월부터 익월까지인 경우에는 H7셀 수식과 같이 작성하고 이용일이 당월부터 당월까지인 경우에는 H8셀과 같은 수식으로 작성하여 이용대금 합산액을 계산 하는 수식을 작성 할 수 있음을 참고해 주시길 바랍니다.
| 3. 저축성 지출 시트. 3-1. B2셀 =SUM(B3:B14) 3-2. B3셀 =SUMIFS(수익지출내역!$E$4:$E$1000, 수익지출내역!$G$4:$G$1000,저축성지출!B$1,수익지출내역!$A$4:$A$1000,저축성지출!$A3) |
| 4. 수익 지출 내역서 시트. 4-1. A4셀 =IF(C4="","",MONTH(C4)) 4-2. B4셀 =IF(C4="","",DAY(C4)) 4-3. J4셀 =IF(C4="",0,SUM(+D1+D4-E4)) 4-4. J5셀 이하 =IF(C5="","",SUM(J4+D5-E5)) |
2-1. 데이터 유효성 검사.
수익 지출 내역 시트의 F열, H열, I열은 데이터 유효성 검사를 적용시킨것으로 참조시트에 작성한 분류는 수익과 지출의 중복값이 적용되어 입력했기 때문에 그대로 데이터 유효성 검사의 참조 범위 영역으로 지정할 경우 불필요한 내역이 계속해서 드롭다운 목록으로 나오기 때문에 불편함이 발생 할 수 있습니다.
데이터 유효성 검사의 활용은 이전에 작성한 다음의 글을 함께 참고 해 보시면 좋습니다.
엑셀(Excel) 정해진 데이터 값 이외 데이터 값 입력 방지 드롭다운 목록 만들기 엑셀 데이터 유효성 검사 기능을 사용해 보자.
따라서 분류에 따른 하위 분류로 나눠지는 항목에 따라 각각 분리 하여 작성한 후 각 분류와 하위 분류의 표를 이름정의하여 데이터 유효성 검사를 적용 시키거나 또는 동일한 높이와 너비의 참조 영역으로 지정할 경우에는 드롭다운 목록에 공란 또는 공백이 발생 할 수 있기 때문에 이것또한 불편함이 발생 할 수 있죠.
따라서 보통 분류와 하위 분류에 따라 표를 분리 하고 각각의 표 이름을 정의하여 INDIRECT함수를 사용하여 각 분류표의 이름을 참조 할 수 있도록 할 수 있겠으나 각각 분리된 표를 모두 이름으로 정리하지 않는다면 INDIRECT함수로 데이터 유효성 검사 드롭다운 목록이 생성되지 않을것입니다.
따라서 현재 샘플 예제 서식에 작성되어 있는 C열과 B열 그대로를 참조 하면서 드롭다운 목록에 공란과 공백이 없고 별도의 이름을 정의하지 않고도 드롭다운 목록을 만들 수 있는 방법은 C열과 B열에 작성된 분류와 하위분류의 참조 범위를 동적 범위로 활용하여 데이터 유효성 검사 참조 범위로 작성하여 해결 할 수 있습니다.
참조 영역 범위를 동적 범위로 활용하고 적용하는 수식은 이전에 작성한 다음의 글을 함께 참고 해 보시길 바랍니다.
엑셀(Excel) offset 함수로 참조 범위 영역을 동적 범위로 수식 작성을 해보자.
따라서 F열과 H열은 각각 중복되는 데이터 값 없이 드롭다운 목록이 생성 될 수 있도록 참조 시트의 각 참조 버무이 영역을 범위 지정해 주고, I열만 H열의 분류에 따라 하위 분류값만을 드롭다운 목록 형식으로 생성 될 수 있는 동적 범위를 참조 할 수 있는 수식을 다음과 같이 작성 할 수 있습니다.
| =OFFSET(참조!$D$1,MATCH($H4,참조!$C$2:$C$101,0),0,COUNTIFS(참조!$C:$C,$H4),1) |
이후 데이터 유효성 검사가 필요한 셀까지 해당 데이터 유효성 검사를 적용시켜 줄 수 있습니다.
※ 기본적으로 엑셀 가계부 샘플 예제 서식의 합산 범위는 1,000행까지를 참조 범위로 하고 있기 때문에 만약 수익 지출 내역의 입력되는 내용이 1,000행을 넘어갈 경우 작성된 대부분의 참조 범위 영역을 1,000행 이상으로 수정하여 사용해야 합니다.
추가로 가계부 작성하는데 필요한 부분이나 수정이 필요한 경우 댓글로 남겨 주시면 더욱 정교한 샘플 서식을 완성할 수 있는데 많은 도움이 될 수 있으니 샘플 서식을 다운 받으시면서 공감과 댓글 함께 부탁 드리겠습니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"엑셀 가계부 서식과 자동 계산 완성" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
※ 엑셀 가계부 서식과 자동 계산 완성 샘플 예제 서식 다운로드.
'엑셀(Excel)' 카테고리의 다른 글
| 엑셀(Excel) 속도가 느려질때 확인하고 해결해줘야 하는것들. (1) | 2022.12.07 |
|---|---|
| 엑셀(Excel) 급여 계산 자동 서식과 수식 작성 그리고 2023년 4대보험 요율 최저시급 변경을 확인해 보자. (0) | 2022.12.06 |
| 엑셀(Excel) 네트 급여 세후 급여로 세전 급여 추정 계산 수식 작성을 해보자. (0) | 2022.11.28 |
| 엑셀(Excel) 숫자로 보이는 문자 데이터 숫자로 변환하는 수식을 작성해보자. (1) | 2022.11.25 |
| 엑셀(Excel) 퇴직금 계산 방법 및 서식과 퇴직소득세 계산 서식 및 퇴직금 지급 금액 계산 수식 작성. (1) | 2022.11.25 |
댓글