나만의 가계부 만들기

나만의 가계부 만들기

요즘은 모바일용 가계부 앱들이 많은데 힘들여서 엑셀에 가계부를 정리해서 사용하는 분들이 없습니다.

엑셀을 다뤄보고 싶은 마음과 모바일용 가계부앱들에게서 모자라는 부분을 보완해서 사용해보고자 저만의 가계부를 정리할 수 있도록 만들어보도록 하겠습니다.

처음 엑셀을 사용해 만들어보는 것이다보니 많은 부분에서 미흡하며 차츰 엑셀에 대한 공부를 하면서 보완을 해나가도록 하겠습니다.

1. 가계부 디자인

사용내역, 카테고리, 카테고리별 합계, 카테고리별 내역 4가지의 구분으로 만들어서 사용할 예정입니다.

4가지 구분은 엑셀의 탭으로 구분합니다.

[가계부 디자인]

- 사용내역

  돈의 수입과 지출의 내역을 모두 기록하는 영역

  각 내역별 카테고리를 설정하여 표시

- 카테고리

  사용내역의 카테고리로 사용할 내용을 정리하여 표시하는 영역

- 카테고리별 합계

  카테고리에 입력된 내용을 바탕으로 각 항목들의 합계를 계산하여 표시하는 영역

- 카테고리별 내역

  각 카테고리의 대분류를 기준으로 사용내역에서 내역을 찾아서 표시하는 영역

2. 사용할 엑셀 기능들

- 이름정의

- 데이터유효성검사 참조 : 2017/10/16 - [엑셀 다루기] - 데이터 유효성 검사로 대, 중 분류하기

- SUMIF() 참조 : 2017/10/22 - [엑셀 다루기] - [엑셀 함수] SUMIF 다루기

- INDIRECT()

- 고급필터 참조 : 2017/11/06 - [엑셀 다루기] - [엑셀 함수] 고급필터 사용법

3. 기능 만들기

1. 카테고리

  1) 사용내역의 결제종류, 결제방법, 구분, 카테고리에 사용할 콤보값들을 정의합니다.

[결제종류, 결제방법 설정]

결제종류를 표시하고 각 결제종류에 따라서 서브 값으로 표시가 될 항목들을 정의하였습니다.

ex) 결제종류 : 신용카드 선택 시 결제방법 : 신한카드,국민카드 선택 가능

     결제종류 : 체크카드 선택 시 결제방법 : 신한체크카드, 국민체크카드, 카카오체크카드 선택 가능

[구분, 카테고리 설정]

  사용내역의 구분을 표시하고 각 구분에 따라서 서브 값으로 표시가 될 항목들을 정의하였습니다.

  ex) 구분 : 고정수입 선택 시 결제방법 : 급여, 기타 선택 가능

       구분 : 데이트 선택 시 결제방법 : 외식, 쇼핑, 교통, 숙박, 기타 선택 가능

  2) 각각의 항목들에 이름정의 기능을 사용하여 항목들에 이름을 지정합니다.

    데이터 유효성 검사 기능 사용하여 리스트의 대분류, 중분류를 표시하기 위한 이름 정의를 합니다.

    ex) 수식 - 정의된 이름 - 이름정의 메뉴 참조

이름 : 결제종류

범위 : 통합문서

참조대상 : 카테고리!$A$2:$A$5

해당 내용을 입력하여 각각의 카테고리 항목에 이름 정의

2. 사용내역

  1) 일시(속성 : 날짜), 금액(속성 : 회계), 내역(속성 : 텍스트), 결제종류(속성 : 텍스트), 결제방법(속성 : 텍스트), 구분(속성 : 텍스트), 카테고리(속성 : 텍스트), 메모(속성 : 텍스트) 컬럼을 생성

[사용내역의 컬럼 생성]

  2) 카테고리에서 정의한 이름을 기반으로 결제종류, 결제방법, 구분, 카테고리에 데이터 유효성 검사를 설정합니다.

    결제종류에 따라서 결제방법을 선택할 수 있도록, 구분에 따라서 카테고리를 선택할 수 있도록 설정

    ex) 데이터 - 데이터 도구 - 데이터 유효성 검사 메뉴 참조

대분류인 결제종류 설정

제한 대상 : 목록

원본 : 결제종류

제한 대상 : 목록중분류인 결제방법 설정

원본 : INDIRECT($D2) (대분류의 항목 위치)

3. 카테고리별 합계

  1) 카테고리 Tab에 작성한 내용을 직접 입력하여 다음 표처럼 작성한다.

[카테고리별 합계 표 작성]

  2) 각각의 카테고리별 합계를 계산하기 위해서 SUMIF() 함수를 사용하여 카테고리별 합계를 구합니다.

    ex) 수식 - 함수 라이브러리 - 함수 삽입 메뉴 참조

각 카테고리의 금액에

RANGE : 내용돈내역!$G:$G (합계 카테고리 영역)

Criteria : "*"&C2&"*" (비교할 조건:카테고리가 포함 표시)

Sum_range : 내용돈내역!$B:$B (합계를 구할 영역)

-> SUMIF(내용돈내역!$G:$G,"*"&C2&"*",내용돈내역!$B:$B)

* 함수에 적용된 다른 참조된 Tab는 그 다음번에서 확인가능합니다.

4. 카테고리별 내역

  1) 고급필터를 사용하여 사용내역에 정리된 내용 각 구분별 내역으로 정리합니다.

    ex) 데이터 - 정렬 및 필터 - 고급 메뉴 참조

목록 범위 : 사용내역!$A:$H (사용내역의 전체영역 선택)결과 : 다른 장소에 복사 선택

조건 범위 : 카테고리 합계 Tab의 내용돈 카테고리 선택

복사 위치 : 내용돈내역 빈 Tab 생성하여 임의 위치 선택

  2) 고급 필터 설정 시 아래 그림처럼 구분이 "내용돈"으로 설정된 영역만 따로 추출해서 내역으로 정리할 수 있다.

[카테고리 내역 고급 필터 설정 완료]

내용돈 내역외에 나머지 카테고리에 있는 고정수입 내역, 고정지출 내역, 데이터 내역을 정리할 수 있습니다.

초기 엑셀로 가계부를 만드는 것이다보니 조금은 불편하게 만들고 있다고 생각 할 수 있지만 엑셀을 공부하면서 가계부를 좀더 다듬으면서 엑셀 공부를 할 수 있도록 해봐야겠습니다.

현재 제가 만든 가계부의 단점은 매크로를 사용하지 않아서 일일이 수작업으로 해야하며, 좀더 세분하게 내역을 정리하는것이 조금 불편한것 같습니다. 그러한 점들은 추후 보완하여 정리하도록 하겠습니다.