안녕하세요! 
지난시간에는 월 별로 바뀌는 달력을 만들고 간단한 버튼을 만들어 연 단위도 이동이 되도록 만들어 보았는데요~
이제 기본 틀을 만들었으니 달력에 해당 요일에 입출금 상황이 어떤지 표시하고 다음 작업을 위한 대략적인 UI와 디자인까지 끝내보겠습니다!
1편 강의자료 다운로드 (블로그 링크)
2편 강의자료 다운로드 (블로그 링크)
가장 먼저 데이터를 추가할 시트를 생성하고 이름을 'DATA'로 지정하겠습니다.
달력이 들어가있는 시트이름도 변경해야하는데 코드에 'sheet1'로 설정되어있으니 변경하겠습니다.
빨간줄 쳐져 있는 부분을 시트이름 'MAIN'으로 변경하시면 됩니다.
DATA 시트에는 총 6가지 항목이 들어갑니다.
위와 같이 셋팅해주시면 됩니다.
MAIN시트와 DATA시트까지 모두 셋팅하셨다면 잠시 함수에 대해 설명하겠습니다.
'DATA'시트에 기록된 값들 중 필요한 값만 빼와야겠죠? SUMIFS 함수를 사용하겠습니다.
함수는 위 링크(현황판만들기 편)를 들어가시면 예제를 보실 수 있습니다.
두번째로 설명드릴 함수는 'TEXT'함수입니다.
이 수식의 결과값인 '1'을 SUMIFS 함수에 적용해야하는데 안타깝게도 그냥 사용하면 적용되지 않습니다.
이 수식의 결과값을 사용하기 위해 TEXT 함수를 사용해보겠습니다.
=TEXT(C6,"d") 이렇게 되면 함수 결과값인 1이 출력되고 수식에서도 사용할 수 있습니다.
예를들면 다음과 같은 식에서 =SUMIFS(DATA!G2:G1000,DATA!D2:D1000,C6)
C6을 검색 값으로 넣으면 결과값인 1로 검색되는것이 아니고 수식값인 =B6+1으로 검색되므로 올바른 결과가 나오지 않을 수 있습니다.
자 이제 SUMIFS 함수를 사용하여 수입 값을 계산해보겠습니다.
2018년 1월 1일이라 가정할 때,
=SUMIFS(DATA!$G$2:$G$1000,DATA!$E$2:$E$1000,"입금",DATA!$B$2:$B$1000,$B$2,DATA!$C$2:$C$1000,$D$2,DATA!$D$2:$D$1000,TEXT(C6,"d"))
다음과 같이 조건 4개를 입력하면 됩니다. 범위나 연, 월 값은 절대값(f4)을 취해줍니다.
이제 출금값도 표시해야겠죠? "입금" 부분을 "출금" 부분으로 바꾸기만 하면 됩니다.
=SUMIFS(DATA!$G$2:$G$1000,DATA!$E$2:$E$1000,"출금",DATA!$B$2:$B$1000,$B$2,DATA!$C$2:$C$1000,$D$2,DATA!$D$2:$D$1000,TEXT(C6,"d"))
이제 출금부분이 잘 나오는지 확인하기 위해 'DATA' 시트에 출금 값을 임의로 넣어보겠습니다.
같은 날짜에 출금값 40,000원을 넣은 뒤 확인해봅시다.
이제 입금값에서 출금값을 뺀 뒤 아래 셀에 넣어보겠습니다. =C7-C8
입금 값과 출금 값이 제대로 나오는 것을 확인할 수 있습니다. 이제 모든 날짜에 적용해보겠습니다.
모두 적용해보았습니다. 여기서 두 가지 문제점을 확인해 볼 수 있습니다.
1)입금, 출금 데이터가 없는 날짜에도 0이 표시되어 지저분하다.
2)다음 월로 넘어간 부분(2월 1일)이 1월 1일로 인식되어 표시된다.
1번 문제점은 조건부 서식으로 간단하게 처리해보겠습니다.
위 그림과 같이 범위를 설정해 준 뒤 '다음을 포함하는 셀만 서식 지정' 에서 셀 값이 0인 것만 연한 회색으로 글씨색을 바꾸겠습니다.
2번문제인 다음 월로 넘어간 부분(2월 1일)이 1월 1일로 인식되어 표시된다. 는 IF문을 사용하여 해결해 보겠습니다.
=IF(MONTH(C6)=D2,SUMIFS수행,0)MONTH 함수를 사용하여 월 값을 비교한 뒤 일치할 경우에만 SUMIFS 함수를 통해 계산값을 출력하도록 설정하면 됩니다.
자 그럼 한번 아까 작성한 SUMIFS문을 수정해보겠습니다.
=IF(MONTH(C6)=$D$2,SUMIFS(DATA!$G$2:$G$1000,DATA!$E$2:$E$1000,"입금",DATA!$B$2:$B$1000,$B$2,DATA!$C$2:$C$1000,$D$2,DATA!$D$2:$D$1000,TEXT(C6,"d")),0)
아까와 같은 방법으로 출금 부분도 수정한 뒤 모두 적용시키겠습니다.
자! 다음과 같이 선택한 월의 값만 표시되고, 수입과 지출이 없는 부분은 회색으로 보이도록 수정하였습니다.
정확한 테스트를 위해 임의로 값을 여러 개 넣어보겠습니다.
이제 디자인을 살짝 수정한 후 결과를 확인해 보겠습니다.
이제 달력이 완성되었으니 다른 메뉴를 만들기 전에 디자인을 살짝 해보겠습니다.
달력 주변 셀을 깔끔하게 회색으로 처리해주고 메뉴는 진한 회색과 선택한 메뉴만 파란색으로 설정해주었습니다.
이제 조금 더 눈에 잘 들어오도록 '보기' 메뉴를 설정해보겠습니다.
작업할 땐 수식 표시줄과 눈금선, 머리글이 있어야 편하지만 최종적으로 사용할 때는 체크를 풀어두는 것이 더 좋습니다.
수식표시줄, 눈금선, 머리글의 체크를 푼 상태입니다. 이렇게 해 두면 사용할 때 더 편하겠죠?
디자인을 변경하면서 셀을 추가하고 변경하였으니 '코드보기'에 들어가 바뀐 부분을 변경해주는 작업도 잊지 말고 해줍시다.
다음과 같이 월 별로 입/출금 값이 정상적으로 표시되는 것을 확인할 수 있습니다.
그 동안 포스팅한 내용들을 사용하여 가계부를 만들어 보았습니다.
다음 포스팅에서는 은행정보, 계좌정보, 입/출금 정보 등의 설정 메뉴를 만들고
한달의 입/ 출금 내용을 한 눈에 정리하여 볼 수 있는 상세내역도 만들어 보겠습니다.
혹시 강의에 추가되었으면 하는 기능이 있다거나
조언해주실 부분이 있으시다면 댓글로 거침없이 피드백 주세요! 쓴소리 좋아합니다!
다음 포스팅에서 뵙겠습니다!
지난 포스팅
[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 배열함수 활용)