[Excel] 12. 엑셀로 능력있는 직장인이 되는 법. 자산 관리용 가계부 만들기 1편 (달력만들기)

안녕하세요! @gbgg입니다. 능력있는 직장인이 되는법! 열 두번째 시간입니다!

지난시간에 자주쓰이는 기초 함수들에 대해 설명드리고 강의를 마무리했었는데요!

이번 편부터는 한 3~4부작에 걸쳐서 자산관리용 가계부를 만들어볼까 합니다.

그동안 기초 함수들에 대한 강의를 진행했고 폼 사용하는 법과 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()

If Sheets("Sheet1").Range("D2").Value = 12 Then
    
    Sheets("Sheet1").Range("D2") = 1
    Sheets("Sheet1").Range("B2") = Sheets("Sheet1").Range("B2") + 1
    Else
        Sheets("Sheet1").Range("D2") = Sheets("Sheet1").Range("D2").Value + 1     
    End If
End Sub

결과 화면

자 이제 연도와 월을 셀에 표시했으니 이 값들을 가지고 달력에 날짜를 표시해보겠습니다.

함수 코드

=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의 기본이나 꼭 필요한 코딩지식 등등이 주 된 포스팅 내용이었다면

이번 강의를 기점으로는 실제로 사용할 수 있는 프로그램을 만들어나가면서 진행하겠습니다.

혹시 강의에 추가되었으면 하는 기능이 있다거나

조언해주실 부분이 있으시다면 댓글로 거침없이 피드백 주세요! 쓴소리 좋아합니다!

능력있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때 까지 한번 열심히 달려봅시다!

다음 포스팅에서 뵙겠습니다!

---

지난 포스팅

[Excel] 1. 능력있는 직장인이 되는 법. 엑셀 자동화란?

[Excel] 2. 능력있는 직장인이 되는 법. 다른 파일 불러오기!

[Excel] 3. 능력있는 직장인이 되는 법. 물품 입고시키기!

[Excel] 4. 엑셀로 능력있는 직장인이 되는 법. 간편한 UI 만들기!

[Excel] 5. 엑셀로 능력있는 직장인이 되는 법. 로그인 + 접속기록 만들기!

[Excel] 6. 엑셀로 능력있는 직장인이 되는 법. 재고(자산) 관리용 현황판 만들기!

[Excel] 7. 엑셀로 능력있는 직장인이 되는 법. 초급편 (기초함수 소개)

[Excel] 8. 엑셀로 능력있는 직장인이 되는 법. 초급편 (VLOOKUP, INDEX, MATCH 등)

[Excel] 9. 엑셀로 능력있는 직장인이 되는 법. 그림판 만들기 ! (매크로 기록 활용)

[Excel] 10. 엑셀로 능력있는 직장인이 되는 법. 엑셀 그림판에 애니메이션 기능 추가하기!

[Excel] 11. 엑셀로 능력있는 직장인이 되는 법. 초급편 (SUMPRODUCT, SUM 배열함수 활용)