[Excel] 13. 엑셀 자산 관리용 가계부 만들기 2편 (입금 출금 통계 표시하기, 조건부서식)

안녕하세요! @gbgg입니다. 엑셀 자산관리용 가계부만들기 2편입니다!

지난시간에는 월 별로 바뀌는 달력을 만들고 간단한 버튼을 만들어 연 단위도 이동이 되도록 만들어 보았는데요~

이제 기본 틀을 만들었으니 달력에 해당 요일에 입출금 상황이 어떤지 표시하고 다음 작업을 위한 대략적인 UI와 디자인까지 끝내보겠습니다!

---

강의자료

1편 강의자료 다운로드 (블로그 링크)
2편 강의자료 다운로드 (블로그 링크)

---

기본 셋팅

가장 먼저 데이터를 추가할 시트를 생성하고 이름을 'DATA'로 지정하겠습니다.

달력이 들어가있는 시트이름도 변경해야하는데 코드에 'sheet1'로 설정되어있으니 변경하겠습니다.

빨간줄 쳐져 있는 부분을 시트이름 'MAIN'으로 변경하시면 됩니다.

DATA 시트에는 총 6가지 항목이 들어갑니다.
위와 같이 셋팅해주시면 됩니다.

• NO (데이터 기록 순서)
• 날짜 (연도, 월, 일)
• 구분 (입금/출금)
• 구분2 (사용자가 설정한 계좌 이름)
• 금액 (입출금 금액)
• 메모 (입/출금 내역 메모)

---

SUMIFS함수, TEXT함수

MAIN시트와 DATA시트까지 모두 셋팅하셨다면 잠시 함수에 대해 설명하겠습니다.

SUMIFS 함수 사용법 참조

'DATA'시트에 기록된 값들 중 필요한 값만 빼와야겠죠? SUMIFS 함수를 사용하겠습니다.
함수는 위 링크(현황판만들기 편)를 들어가시면 예제를 보실 수 있습니다.

두번째로 설명드릴 함수는 'TEXT'함수입니다.

이 수식의 결과값인 '1'을 SUMIFS 함수에 적용해야하는데 안타깝게도 그냥 사용하면 적용되지 않습니다.

이 수식의 결과값을 사용하기 위해 TEXT 함수를 사용해보겠습니다.

• TEXT(텍스트, 서식)

=TEXT(C6,"d") 이렇게 되면 함수 결과값인 1이 출력되고 수식에서도 사용할 수 있습니다.

예를들면 다음과 같은 식에서 =SUMIFS(DATA!G2:G1000,DATA!D2:D1000,C6)

C6을 검색 값으로 넣으면 결과값인 1로 검색되는것이 아니고 수식값인 =B6+1으로 검색되므로 올바른 결과가 나오지 않을 수 있습니다.

자 이제 SUMIFS 함수를 사용하여 수입 값을 계산해보겠습니다.

2018년 1월 1일이라 가정할 때,

• '입금' 값만 검색
• 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(해당 일의 월 값이 = 선택한 월 값, SUMIFS 수행, 0)
  =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 배열함수 활용)

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