안녕하세요! gbgg입니다. 능력 있는 직장인이 되는 법! 열한 번째 시간입니다!
그동안 기초 함수들에 대한 설명과 폼 사용하는 법, 그리고 vba를 통한 자동화까지 진행했는데요!
이번엔 이 모든 것들을 활용하여 가계부를 만들어볼 까 합니다.
자료도 첨부해두었으니 보면서 따라 하시면 쉽게 이해하실 수 있을 겁니다!
가계부에 들어갈 내용
- 지출/수입 입력
- 지출 내역
- 수입 내역
- 일, 월별 내역 통계
- 신용카드 / 체크카드 / 현금
- 자산현황 그래프 / 차트
가계부에 들어갈 내용은 위와 같습니다.
입력할 수 있는 시트와 지출/수입 내역을 확인할 수 있는 시트, 기본설정 시트 등으로 구분될 것이고
한눈에 알아보기 쉽게 디자인도 고려하여 진행될 것입니다.
앞선 강의에서 배웠던 요소들 대부분이 사용되므로 참조해주시고 모르는 것은
댓글 달아주시면 답변 달아드리겠습니다!
기본 디자인
가계부의 큰 디자인 틀은 두 가지가 있습니다. '달력 형태'와 '내역 형태'인데 제가 사용해 보았을 때는 상대적으로
달력 형태로 이루어진 디자인이 더 깔끔하고 한눈에 알아보기 편했습니다.
이번 포스팅에서는 이러한 달력 형태를 기준으로 vba와 함수를 함께 사용하여 만들어보겠습니다.
- 35개의 달력 칸에 날짜 정보와 입/출금 내역이 표시된다.
- 월 단위로 통계를 낸 뒤 달력 하단에 표시한다.
달력처럼 꾸미기 위해 위 그림과 같은 형식으로 구분해보겠습니다.
날짜와 수입, 지출, 그리고 수입과 지출을 가지고 통계가 필요하겠죠?
달력처럼 디자인 해 보겠습니다. 수입은 파란색, 지출은 빨간색, 통계는 검은색으로 표시합니다.
날짜는 일반 달력처럼 최대 5주간 내용이 기록되도록 하겠습니다.
이제 월별로 달력을 확인할 수 있도록 연월을 표시하고 연동해보겠습니다.
위와 같이 연도가 들어갈 셀과 월이 들어갈 셀을 만들고 버튼 두개를 생성합니다.
버튼을 생성한 후 버튼에 기능을 부여해봅시다. (지난 포스팅 참조)
| Sheets("Sheet1").Range("D2") = Sheets("Sheet1").Range("D2").Value + 1 |
버튼을 누르면 숫자가 올라가도록 구현해 보았습니다.
왼쪽 버튼 '◀'은 -1이 되어야 하고 오른쪽 버튼 '▶'은 +1이 되어야겠죠?
그리고 날짜가 12월이 넘어가면 연도도 증가해야 합니다.
이 두 조건을 정리해봅시다.
- 값이 12인 상태에서 +1을 하면 값을 1로 바꾸고 연도 부분에 +1을 해준다.
- 값이 1인 상태에서 -1을 하면 값을 12로 바꾸고 연도 부분에 -1을 해준다.
이 두 개를 한번 구현해보겠습니다.
단추 1 소스코드(<-)
| Sub 단추1_Click() If Sheets("Sheet1").Range("D2").Value = 1 Then Sheets("Sheet1").Range("D2") = 12 Sheets("Sheet1").Range("B2") = Sheets("Sheet1").Range("B2") - 1 Else Sheets("Sheet1").Range("D2") = Sheets("Sheet1").Range("D2").Value - 1 End If End Sub |
단추 2 소스코드(->)
|
Sub 단추2_Click() |
결과 화면
자 이제 연도와 월을 셀에 표시했으니 이 값들을 가지고 달력에 날짜를 표시해보겠습니다.
함수 코드
| =DATE(B2,D2,1) - WEEKDAY(DATE(B2,D2,1)) + 1 |
매 월마다 28일, 30일, 31로 불규칙적이기 때문에 함수로 계산합니다.
DATE(B2, D2,0) B2에는 연도 값이, D2에는 월 값이 들어가 있으므로
2018년 1월 29일 데이터를 입력하면 2018년 1월 데이터를 출력합니다.
'WEEKDAY(DATE(B2, D2,1))' WEEKDAY 함수는 해당 연월의 가장 '첫' 요일을 반환해줍니다.
해당 연월의 1일에서 이 첫 요일 값을 빼주고 +1을 해주면 됩니다.
자 이제 달력의 첫 일요일 값을 얻었으니 나머지는 더해주기만 하면 됩니다.
- 첫 주는 일요일 값에서 + 1, +2, +3...
- 둘째 주부터는 전 주 값에서 +7
첫 번째 주는 위와 같이 바로 이전 셀 값에서 +1을 해줍니다.
두 번째 주부터는 위와 같이 바로 전 주의 값에서 +7을 해줍니다.
위 그림과 같이 모두 적용해줍니다. 이제 할 일이 두 가지 남았습니다.
- 날짜만 보이도록 셀 서식을 변경한다.
- 해당 연월이 아닌 날은 조건부 서식을 통해 색을 바꿔준다.
셀 서식은 위와 같이 사용자 서식에서 'd'를 입력해주면 날짜 값만 받을 수 있습니다.
해당 연월이 아닌 부분의 날짜는 흐리게 표시해보겠습니다.
'조건부 서식' 기능을 클릭해줍니다.
위와 같이 서식 값에 =MONTH(B6)<>$D$2를 넣고 글씨 색은 회색으로 변경하겠습니다.
월 값이 같을 경우에만 서식 변경이 없도록 하였습니다.
셀 범위는 위와 같이 날짜가 들어있는 부분만 선택해주시면 됩니다.
결과
위와 같이 달력이 잘 동작되는 것을 확인할 수 있습니다.
수입과 지출 데이터 값은 다음 포스팅에서 데이터 시트를 만들어 INDEX 함수와 MATCH 함수를 사용하여 추출해보겠습니다. 이 부분은 재고관리 강의에서 진행했던 부분이니 쉽게 이해가 가실 것이라 생각됩니다.
마지막으로 눈에 잘 들어오도록 셀 색깔 등을 수정해주시면 됩니다.
엑셀 자동화
지난 포스팅에서 설명했던 vba와 함수 등을 사용하여 가계 부용 달력을 만들어보았는데요!
어려워 보이지만 첨부된 예제를 보시면서 천천히 따라 해 보면 금방 이해할 수 있을 거라고 생각합니다.
궁금한 점이나 추가 요청사항은 댓글로 남겨주시면 답변 달아드리겠습니다!
능력 있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때까지 한번 열심히 달려봅시다!
다음 포스팅에서 뵙겠습니다!
최근댓글