안녕하세요! gbgg입니다. 엑셀 자산관리용 가계부 만들기 2편입니다!
지난 1편 포스팅에서는 월 별로 변화하는는 달력을 만들고 버튼을 만들어 연 단위로 변경이 되도록 만들어보았습니다.
이제 기본적인 달력 틀이 완성되었으니 데이터 시트를 만들고 그 데이터를 달력에 표시해보겠습니다.
아래 자료는 아래에서 진행할 자산관리용 가계부 만들기 2~3편의 예제입니다.
기본 셋팅
가장 먼저 데이터를 추가할 시트를 생성하고 이름을 'DATA'로 지정하였습니다.
추가로 이전 포스팅에서 달력이 들어가 있는 시트 이름은 기본 이름인 'sheet1'이었으니 코드를 수정해봅시다.
빨간 줄 쳐져 있는 부분인 이전 시트명을 현재 변경한 시트 이름 'MAIN'으로 변경하시면 됩니다.
DATA 시트에는 총 6가지 항목이 들어갑니다.
가계부를 기록할 때 필요한 필수 목록들입니다.
아래와 같이 세팅해봅시다.
- NO (데이터 기록 순서)
- 날짜 (연도, 월, 일)
- 구분 (입금/출금)
- 구분 2 (사용자가 설정한 계좌 이름)
- 금액 (입출금 금액)
- 메모 (입/출금 내역 메모)
SUMIFS함수, TEXT함수
MAIN시트와 DATA시트까지 모두 세팅하셨다면 잠시 함수에 대해 설명하겠습니다.
우선 'DATA'시트에 기록된 값들 중 조건에 맞는 값들을 가져와야 하므로 SUMIFS 함수를 사용해 보겠습니다.
SUMIFS 함수에 대한 설명은 지난 포스팅(위 링크)에 자세하게 설명되어있습니다.
두 번째로 설명드릴 함수는 'TEXT'함수입니다.
TEXT 함수는 사용자 지정서식에 맞춰 셀의 값을 원하는 형식으로 텍스트화 하여 표시해주는 함수입니다.
위 수식의 결괏값을 SUMIFS 함수의 조건문에 적용해야 하는데 그러려면 데이터 형식을 변경해주어야 합니다.
위와 같은 식을 SUMIFS 함수의 조건 검색 값으로 입력하면 결괏값으로 검색하지 않고 수식 값인 =B6+1으로 검색되므로 올바른 결과가 나오지 않을 수 있습니다.
- TEXT(텍스트, 서식)
자 이제 SUMIFS 함수를 사용하여 수입 값을 계산해보겠습니다.
2018년 1월 1일이라 가정할 때 아래와 같은 4가지 조건이 필요하겠죠?
- '입금' 값만 검색
- 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일로 인식되어 표시된다.
위 두 문제는 다음 포스팅에서 "조건부 서식"과 함수를 일부 수정하여 해결해보겠습니다.
엑셀 자동화
다음 포스팅에서는 은행 정보, 계좌정보, 입/출금 정보 등의 설정 메뉴를 만들고
한 달의 입/ 출금 내용을 한눈에 정리하여 볼 수 있는 상세내역도 만들어 보고자 합니다.
궁금한 점이나 추가 요청사항은 댓글로 남겨주시면 답변 달아드리겠습니다!
능력 있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때까지 한번 열심히 달려봅시다!
다음 포스팅에서 뵙겠습니다!
최근댓글