이 포스팅은 자동 달력을 만드는 편으로 2편에서 만들었던 피벗테이블과 연동하여 거래내역이 변할때 마다 같이 연동되어 자동으로 변하는 달력을 만드는 강좌 입니다.
이번 포스팅까지 진행 하시면 대부분의 데이터 가공과 수식 사용은 완료가 됩니다.
글의 순서
함께 참고하면 좋은 글 추천
- 따라하며 엑셀 가계부 대시보드 만들기 1편 – 기본 데이터 가공하기
- 따라하며 엑셀 가계부 대시보드 만들기 2편 – 피벗테이블 가공하기
- 엑셀 함수 모음 가이드 1편 – 날짜 함수와 시간 함수
- 엑셀 함수 모음 가이드 2편 – 논리 함수와 데이터베이스 함수
- 엑셀 함수 모음 가이드 5편 – 정보 함수와 찾기 / 참조 함수
1. 자동 달력 기본 디자인 만들기
- 자동 달력 시트의 보기 탭에서 눈금선을 체크해제 합니다.
- A1셀 부터 I40셀 까지 범위를 지정한 다음 배경색을 연 회색으로 지정합니다.
- B7셀부터 H39셀까지 범위를 지정한 다음 배경색을 흰색으로 지정합니다.
- ‘월’ 을 표기하기 위해 B1셀에 =피벗데이터!AB3 수식을 입력한 후 B1:H3 범위를 병합하고 글자의 크기를 72로 지정합니다.
- 숫자를 월로 변경하기 위해 Ctrl+1로 셀서식의 표시형식탭 으로 들어가서 사용자 지정 서식에 #,##0″월” 를 입력합니다.
- 월과 연도를 표기하기 위해 K7:L18 범위에 1월~12월을 영어로 작성해 줍니다.
- E4셀에 =VLOOKUP(B1,$K$7:$L$18,2,FALSE)&”,”&피벗데이터!AA3 수식을 작성해 줍니다.
- B8:H8셀에 요일을 그림과 같이 작성해 주고 밑줄도 만들어 줍니다.
- 색상은 일요일과 토요일은 빨간색, 나머지 요일은 검정색으로 지정합니다.
- 날짜를 표기하기 위해 수식을 만들어 사용하겠습니다.
- 일요일 아래 셀에 =DATE(피벗데이터!AA3,피벗데이터!AB3,1)-WEEKDAY(DATE(피벗데이터!AA3,피벗데이터!AB3,1)) + 1 를 입력합니다.
- 셀 서식에서 표시형식 탭의 사용자 지정에가서 d를 적어 날짜에서 day만 표기되게 만들어 줍니다.
- 월요일 아래셀(C9셀)에 =B9+1 을 입력해고 H9셀 까지 수식을 드래그 합니다.
- B14셀에 =B9+7 을 입력합니다.
- 수식을 복사하여 그림과 같이 간격을 유지하며 H34셀 까지 수식을 붙여 넣습니다.
- 토요일과 일요일은 빨간색으로 글자색을 변경해 줍니다.
- 해당월이 아닌 날짜는 옅은 회색으로 색이 되도록 조건부 서식을 만들어 주겠습니다.
2. 자동 달력에 수식 작성하여 입/출금 내역 표기하기
- 조건부 서식까지 지정하였다면 날짜는 왼쪽정렬로 맞추어 주십시오.
- 이제 달력에 입금과 출금내역이 자동으로 표기 되도록 수식을 작성해 주겠습니다.
- IF함수와 SUMIFS함수를 활용하여 만들겠습니다.
- =IF(SUMIFS(피벗데이터!$AE$5:$AE$10000,피벗데이터!$AA$5:$AA$10000,피벗데이터!$AA$3,피벗데이터!$AB$5:$AB$10000,피벗데이터!$AB$3,피벗데이터!$AC$5:$AC$10000,자동달력!B9)=0,””,SUMIFS(피벗데이터!$AE$5:$AE$10000,피벗데이터!$AA$5:$AA$10000,피벗데이터!$AA$3,피벗데이터!$AB$5:$AB$10000,피벗데이터!$AB$3,피벗데이터!$AC$5:$AC$10000,자동달력!B9))
- 위의 함수는 입금내역을 표기하는 함수이며 B10셀에 작성해 줍니다.
- =IF(SUMIFS(피벗데이터!$AD$5:$AD$10000,피벗데이터!$AA$5:$AA$10000,피벗데이터!$AA$3,피벗데이터!$AB$5:$AB$10000,피벗데이터!$AB$3,피벗데이터!$AC$5:$AC$10000,자동달력!B9)-1=0,””,SUMIFS(피벗데이터!$AD$5:$AD$10000,피벗데이터!$AA$5:$AA$10000,피벗데이터!$AA$3,피벗데이터!$AB$5:$AB$10000,피벗데이터!$AB$3,피벗데이터!$AC$5:$AC$10000,자동달력!B9)-1)
- 위의 함수는 출금내역을 표기하는 함수이며 B11셀에 작성해 줍니다.
- 위의 입금내역 함수와 출금내역 함수를 드래그하여 모든 범위에 채워줍니다.
- 입금내역과 출금내역 수식을 모두 작성 했다면 각각에 조건부 서식을 지정하여 가독성이 좋게 서식을 지정해 주겠습니다.
- 먼저, 아래 그림과 같이 입금수식을 작성했던 범위를 모두 지정하고 홈 탭의 조건부 서식에서 ‘셀 강조 규칙’의 ‘보다 큼’을 선택합니다.
- 값에 0 을 입력하고 사용자 지정 서식을 클릭합니다.
- 사용자 지정 서식에서 굵은 기울임꼴을 선택하고 색상은 파란색을 선택합니다.
- 그 다음 Ctrl + 1을 눌러 셀 서식으로 들어가서 표시형식의 숫자를 1000단위 구분 기호 사용을 체크해 줍니다.
- 출금수식의 서식을 지정하겠습니다. 출금수식을 작성했던 범위를 모두 지정하고 홈 탭의 조건부 서식에서 ‘셀 강조 규칙’의 ‘보다 작음’을 선택합니다.
- 값에 0 을 입력하고 사용자 지정 서식을 클릭합니다.
- 사용자 지정 서식에서 굵은 기울임꼴을 선택하고 색상은 붉은색을 선택합니다.
- 다음 Ctrl + 1 을 눌러 셀 서식으로 들어가서 표식형식의 숫자를 1000단위 구분 기호 사용을 체크하고 음수 선택란에 맨 아래 서식을 선택합니다.
여기까지 진행하셨다면 현재 달력은 아래와 같이 만들어져 있을 것입니다.
이제 자동달력 만들기 마지막 단계로 아래 그림 처럼 달력에 수입금액과 지출금액 그리고 잔액을 표기하여 주겠습니다.
각각의 셀에 아래와 같이 작성해 줍니다.
- G4셀에 수입
- G5셀에 지출
- G6셀에 잔액
H4셀에 수입의 수식을 작성하고 글꼴의 색상은 파란색으로 지정하겠습니다.
=IF(SUM(B10:H10,B15:H15,B20:H20,B25:H25,B30:H30,B35:H35)=0,””,SUM(B10:H10,B15:H15,B20:H20,B25:H25,B30:H30,B35:H35))
H5셀에 지출의 수식을 작성하고 글꼴의 색상은 붉은색으로 지정하겠습니다.
=IF(SUM(B11:H11,B16:H16,B21:H21,B26:H26,B31:H31,B36:H36)=0,””,SUM(B11:H11,B16:H16,B21:H21,B26:H26,B31:H31,B36:H36))
H6셀에 잔액의 수식을 작성하고 양수와 음수일때의 2가지 조건부 서식을 지정해 주겠습니다.
=IF(SUM(H4,H5)=0,””,SUM(H4,H5))
잔액이 0보다 클 경우
- 홈 탭의 조건부 서식에서 ‘셀 강조 규칙’의 ‘보다 큼’을 선택합니다.
- 값에 0 을 입력하고 사용자 지정 서식을 클릭합니다.
- 채우기 탭에서 색 없음란에 다른색을 선택합니다.
- 사용자 지정을 선택하고 육각란에 #53C6C8 을 입력합니다.
- 사용자 지정 서식에서 굵은 기울임꼴을 선택합니다.
- 다음 Ctrl + 1 을 눌러 셀 서식으로 들어가서 표식형식의 숫자를 1000단위 구분 기호 사용을 체크합니다.
잔액이 0보다 작을 경우
- 홈 탭의 조건부 서식에서 ‘셀 강조 규칙’의 ‘보다 작음’을 선택합니다.
- 값에 0 을 입력하고 사용자 지정 서식을 클릭합니다.
- 채우기 탭에서 색 없음란에 다른색을 선택합니다.
- 사용자 지정을 선택하고 육각란에 #E56E7D 를 입력합니다.
- 사용자 지정 서식에서 굵은 기울임꼴을 선택하고 색상은 흰색으로 지정합니다.
- 다음 Ctrl + 1 을 눌러 셀 서식으로 들어가서 표식형식의 숫자를 1000단위 구분 기호 사용을 체크합니다.
여기까지 진행하시느라 고생하셨습니다. 이제 기본 데이터 가공과 자동 달력 까지 완성 하셨을 겁니다. 다음 강좌에서는 지금까지 작업했던 작업물로 본격적인 시각화 작업을 진행 하도록 하겠습니다. 감사합니다.
