엑셀로 가계부를 만들어서 사용하고 계신가요? 여기, 기존의 엑셀 가계부 보다 범용성과 사용성이 좋은 엑셀 가계부 대시보드를 소개해 드립니다. 이 강좌를 끝까지 완주 하신다면 자신의 돈의 흐름을 한눈에 볼 수 있는 엑셀 가계부 대시보드를 완성하게 되실겁니다.
글의 순서
- 엑셀 가계부 대시보드란?
- 1. 엑셀 가계부 대시보드 만들기 – 기본설정
- 2. 엑셀 가계부 대시보드 만들기 – 기본 데이터 입력하기
- 3. 엑셀 가계부 대시보드 만들기 – 기본 데이터를 표로 변환하기
- 4. 엑셀 가계부 대시보드 만들기 – 기본 데이터 가공 및 분류하기
엑셀 가계부 대시보드란?
엑셀 대시보드는 데이터를 시각적으로 나타내는 테이터 시각화의 한가지 방법입니다. 데이터 기반 분석의 다양한 측면을 분석, 모니터링 및 추적등을 쉽게 하기 위해 사용합니다. 일반적으로 중요하고 방대한 자료를 간결하고 신속하게 파악할 수 있도록 차트, 그래프, 테이블 및 기타 시각적 요소로 구성됩니다.
이를 기반으로 엑셀 가계부 대시보드를 만들게 된다면 월별 입금 출금현황, 일별 입금 출금현황, 고정비 지출 현황, 지출별 상세내역, 최근거래 내역등을 한눈에 알 수 있게 될것입니다.
엑셀 대시보드를 만들면서 주요 사용하게될 기능은 피벗테이블과 슬라이서 기능이며, 튜토리얼대로 진행하신다면 위의 그림과 같은 엑셀 가계부 대시보드를 완성하실 수 있습니다.
1. 엑셀 가계부 대시보드 만들기 – 기본설정
시작하기전 기본설정을 아래와 같이 작업 후 진행하시길 바랍니다. 그리고 사용하시는 엑셀은 어떤 버전을 사용하셔도 괜찮습니다.
저는 오피스 365 버전으로 진행하도록 하겠습니다.
- 엑셀을 실행 후 본인이 원하시는 파일 이름으로 저장 합니다. 저는 ‘2023년 가계부 대시보드’ 로 입력하겠습니다.
- 4개의 시트를 생성합니다.
- 각각의 시트의 이름은 대시보드, 사용내역, 피벗데이터, 자동달력으로 저장합니다.
대시보드 시트 – 최종결과물 시트
사용내역 시트 – 기본데이터인 입/출금 내역을 작성할 시트이며, 계속 누적 작성해야 하는 시트
피벗데이터 시트 – 사용내역을 기반으로 피벗데이터를 만들 시트
자동달력 시트 – 대시보드의 일별 사용내역을 표시하기 위해 달력을 만들 시트
[함께 참고하면 좋은 글 추천]
- MS 오피스 365 다운로드 방법 및 설치 가이드
- 엑셀 함수 모음 가이드 5편 – 정보 함수와 찾기 / 참조 함수
- 엑셀 함수 모음 가이드 6편 – 텍스트 함수와 큐브 함수
- 엑셀 함수 모음 가이드７편 – 통계 함수
2. 엑셀 가계부 대시보드 만들기 – 기본 데이터 입력하기
대시보드로 데이터 시각화 작업을 하려면 먼저, 기본 데이터가 필요합니다.
기본 데이터는 간단히 말해 그동안 사용하신 입금 출금 내역을 말합니다. 기존에 정리해 두셨던 자료가 있으시다면 그 자료를 사용해도 되고, 정리해둔 자료가 없다면, 주거래 은행에서 입금 출금 내역을 엑셀로 다운로드 받으셔서 사용해도 됩니다.
이번 가이드 에서는 날짜, 출금내역, 입금내역, 내용을 기반으로 진행하도록 하겠습니다.
사용내역 시트에 아래 그림과 같이 내용을 입력해 주십시오.
- ‘마지막 업데이트 : 2023-05-23’ 은 현재의 날짜가 아닌 최종거래날짜를 표기하기 위한 데이터이며, 함수를 사용하겠습니다.
=”마지막 업데이트 : “&TEXT(MAX(A:A),”yyyy-mm-dd”)
함수풀이 : 마지막 업데이트 + A열의 가장 큰 숫자를 반환하는데 반환할때, 날짜 형식으로 반환
TEXT 함수 알아보기
MAX 함수 알아보기 - 총 건수 란에 ’18건’ 은 총 사용 건수를 표기한 데이터이며, 함수를 사용하겠습니다.
=COUNTA(A8:A1048576)&”건”
함수풀이 : A8셀부터 A104876셀까지 데이터가 존재하는 셀의 개수를 반환 + ‘건’
COUNTA 함수 알아보기 - ‘기초잔액 : 1,000,000’ 은 기본데이터를 입력하기 시작할때 확인한 잔액을 의미하며, 자신의 통장잔고에 맞게 변경 하시면 되겠습니다.
- 위의 기초잔액을 불러오는 항목으로 =B5 를 입력하시면 됩니다.
- 최초 기초잔액으로 부터 출금과 입금의 데이터를 합산하여 계속 누적되면서 계산되는 잔액 항목으로 수식을 사용하겠습니다.
=E9-B10+C10
수식풀이 : 전 거래시 잔액 – 해당 출금금액 + 해당 입금금액 - 내용을 입력하는 항목으로 내용을 입력할때 정해져있는 법칙은 없으니 자유롭게 본인이 원하는대로 작성하면 되겠습니다.
단, 대시보드란에 항목을 구분하기 위하여 ‘사용내용 / 분류명’ 의 형식으로 작성하시면 되겠습니다. - 입력시 주의사항 : 해당 대시보드를 완성 후 사용과정에서 가장 많이 나타나는 오류가 바로 ‘날짜 오류’ 입니다.
바로 거래일자에 날짜를 입력하거나 혹은 붙여넣기를 할때 날짜 형식으로 지정이 되어 있지 않아서 오류가 뜨는 경우가 빈번합니다.
때문에 처음부터 입력하실때 날짜형식으로 지정이 되어 있는지 확인하시기를 권장드립니다.
3. 엑셀 가계부 대시보드 만들기 – 기본 데이터를 표로 변환하기
기본 데이터를 표로 변환하는 작업을 하겠습니다. 앞으로 계속해서 해당시트에 거래내용을 누적하여 작성하게 될 것이며, 이 데이터를 바탕으로 대시보드에 자료를 시각화 해서 표기할 예정입니다. 그런데 데이터를 새롭게 입력할 때마다 범위지정을 계속해서 해주어야 한다면 매번 업데이트 하는 일이 매우 번거러울 것입니다.
그렇기 때문에 우리는 기본 데이터를 표로 변환하여 새로운 데이터를 입력할 때마다 자동으로 범위가 업데이트가 되게 할 것입니다.
1. 거래내용을 입력했던 아무셀이나 클릭을 한 후 ‘Ctrl + A’ 를 눌러서 범위를 지정한 후 ‘Ctrl + T’ 를 눌러서 해당 범위를 표로 변환해 줍니다. 이때, ‘머리글 포함’을 체크하여 표로 변환해 줍니다. 그럼 아래 그림과 같이 표로 변환이 되는데 기본 표의 디자인이 마음에 들지 않으므로, 상단의 테이블 디자인 탭에서 ‘자세히’ 버튼을 눌러 ‘디자인 없음’을 클릭 합니다.
2. 디자인이 없어진 표의 머리글을 드래그 하여 배경색을 그림과 같이 지정해 줍니다.
3. 표의 내용을 직관적으로 이해할 수 있도록 표의 이름을 아래 그림처럼 수식탭의 이름관리자를 이용하여 변경해 줍니다.
4. 엑셀 가계부 대시보드 만들기 – 기본 데이터 가공 및 분류하기
표로 변환된 기본 데이터에 몇가지 항목을 추가 하도록 하겠습니다.
1. 입력한 입/출금의 내용을 체계적으로 구분하고 대시보드에 표기되는 항목을 지정하기 위해 대분류와 소분류를 추가하겠습니다.
2. 대시보드에 최근사용내역을 시각화 하기 위해 거래일을 추가하겠습니다. 거래일에 =[@거래일자] 를 입력합니다. =A8셀을 클릭하면 자동으로 [@거래일자]가 나옵니다. 이는 처음에 우리가 기본데이터를 표로 변환한 영향입니다. 이처럼 엑셀의 표기능은 자동화 서식을 만들때 꼭 활용해야 하는 기능입니다.
3. 이제 소분류와 대분류로 구분할 단어를 정하여 정리하도록 하겠습니다. 간단히 말해 자신이 사용하고 작성했던 사용내용을 바탕으로 키워드가 되는 단어를 미리 소분류와 대분류에 지정해 놓는 작업이라 할 수 있겠습니다. 분류작업은 어렵게 생각하지 마시고 자신이 보기 편하게 작성하면 되겠습니다.
예를 들면, 넷플릭스는 소분류로는 구독서비스 대분류로는 고정비 이렇게 작성 하시면 되겠습니다.
4. 작성을 완료했다면 소분류 키워드와 대분류 키워드 역시 표로 변환하여 새로운 키워드가 포함되도 자동으로 범위가 지정되도록 만들겠습니다. 방법은 위에서 설명드린 기본 데이터를 표로 변환하기와 동일합니다. 표로 변환 후 표의 이름을 수식탭의 이름관리자를 이용하여 각각 ‘소분류키워드’ 와 ‘대분류키워드’ 로 변경해 줍니다. 이름관리자에서 이름을 정할때는 공백은 허용되지 않기때문에 띄어쓰기가 없도록 주의해 주십시오!
5. 이제 사용내용을 추가할 때마다 분류했던 키워드가 각각 소분류열과 대분류열에 삽입되도록 수식을 만들어 주겠습니다.
소분류열에는 아래 수식을 작성해 주십시오.
=IFERROR(INDEX(소분류키워드[소분류],MIN(IF(ISNUMBER(FIND(소분류키워드[포함단어],[@내용])),ROW(소분류키워드[포함단어])-ROW($P$8)+1,ROWS(소분류키워드[포함단어])+1))),””)
※이 수식에서 ROW($P$8)는 소분류 키워드 머리글 아래행으로 만일 자신이 만든 소분류 키워드 표가 9행부터 시작된다면 ROW($P$9)로 바꿔주면 되겠습니다.
ISNUMBER 함수 알아보기
대분류열에는 아래 수식을 작성해 주십시오.
=VLOOKUP([@소분류],대분류키워드,2,FALSE)
VLOOKUP 함수 알아보기
수식을 모두 작성했다면 소분류열과 대분류열에 자신이 정했던 키워드가 삽입됩니다.
이것으로 따라하며 엑셀 가계부 대시보드 만들기 1편 – 기본 데이터 가공하기 편을 마치겠습니다. 다음편은 따라하며 엑셀 가계부 대시보드 만들기 2편 – 피벗테이블 가공하기 가이드를 포스팅 하겠습니다. 감사합니다.
