엑셀 대시보드 만들기, 90분 총정리 가이드 | 카카오 스타일 가계부 대시보드

엑셀의 기본 함수, 표, 피벗테이블, 슬라이서 및 차트를 종합적으로 활용하여 카카오 스타일의 가계부 대시보드를 완성합니다.

홈페이지 » 엑셀 대시보드 만들기, 90분 총정리 가이드 | 카카오 스타일 가계부 대시보드

작성자 :
오빠두엑셀
최종 수정일 : 2022. 09. 21. 18:46
URL 복사
메모 남기기 : (7)

엑셀 대시보드 만들기, 90분 총정리 | 카카오 스타일 대시보드

엑셀 카카오 스타일 대시보드 만들기 목차 바로가기
영상 강의

  1. ----- 1교시 -----
  2. xls 파일형식, 주의하세요!
    01:16
  3. 거래내역을 표로 변환하기
    02:21
  4. 표 사용시, 스타일 문제 해결방법
    03:40
  5. 대시보드에 필요한 항목 추가하기
    04:51
  6. 엑셀 가계부 관리가 어려운 이유?
    05:31
  7. 표 기능을 200% 활용하는 방법
    07:06
  8. 표 동적범위, 이렇게 사용하세요!
    10:07
  9. 자동으로 확장되는 목록상자 만들기
    11:27
  10. 목록상자를 편리하게 사용하는 단축키
    12:29
  11. 각 지출항목을 자동으로 분류하는 공식
    13:11
  12. 표 수식이 자동완성 안될 경우, 해결방법
    16:57
  13. 기타 지출의 계정과목 분류 채워넣기
    17:43
  14. 대분류, 시간대 외 나머지 항목 추가하기
    22:16
  15. ----- 2교시 -----
  16. 대시보드에 사용할 피벗테이블 만들기
    24:02
  17. 실무에서 유용한 패널 위치 옮기기 Tip
    25:19
  18. ① 대분류별 지출 피벗테이블
    25:44
  19. ② 상위 10개 거래내역 피벗테이블
    28:07
  20. 슬라이서+필터 사용시 주의사항
    28:57
  21. ③-1 월별 지출/수입현황 피벗테이블
    30:25
  22. 문자로 된 날짜를 한 번에 바꾸는 방법
    31:21
  23. ③-2 월별 지출/수입현황 피벗테이블 완성
    32:36
  24. 실무에서 유용한 피벗테이블 레이아웃
    33:34
  25. ④ 시간대별 지출현황 피벗테이블
    36:33
  26. ⑤-1 최근 30개 거래내역 피벗테이블
    37:06
  27. 피벗테이블 날짜, 올바른 집계방법
    37:52
  28. 계산필드로 입/출금 차액 집계하기
    41:10
  29. ⑤-2 최근 30개 거래내역 피벗테이블 완성
    42:01
  30. 셀 서식으로 보고서 시각화하기
    43:28
  31. ----- 3교시 -----
  32. 엑셀 대시보드 핵심 기능, 슬라이서!
    45:46
  33. 여러 피벗테이블을 동시에 필터링하기
    47:21
  34. 보고서 연결 사용시 주의사항
    48:17
  35. 슬라이서에서 불필요한 버튼 숨기기
    49:19
  36. 슬라이서 디자인, 쉽게 하는 방법!
    50:40
  37. 슬라이서 버튼 개수 설정하기
    53:49
  38. 슬라이서 머릿글 변경하기
    54:42
  39. 대시보드 항목별 소제목 만들기
    56:04
  40. ①-1 대분류별 지출상세 차트 만들기
    56:51
  41. 대분류별 지출상세 차트 시각화
    59:51
  42. 도형을 편리하게 옮기는 단축키 Tip
    1:02:20
  43. ② 상위 10개 지출항목 차트 만들기
    1:04:08
  44. ③ 월별 지출/수입현황 차트 만들기
    1:06:09
  45. 세로막대 차트, 레이블 겹침 문제 해결
    1:10:00
  46. ④ 시간대별 지출현황 차트 만들기
    1:12:22
  47. 실무에서 유용한 꺾은선 차트 Tip
    1:14:40
  48. 피벗테이블, 빈 항목 항상 표시하기
    1:15:50
  49. 차트에서 없는 항목을 연결하는 방법
    1:18:25
  50. ----- 4교시 -----
  51. ⑤ 상위 30개 거래내역 표 만들기
    1:20:30
  52. 사용자 편의성 높이기 - 슬라이서 연결 설정
    1:22:51
  53. 실시간 연동되는 텍스트 상자 만들기
    1:25:42
  54. 최종 갱신일, 잔액 구하는 공식 원리
    1:28:54
  55. 함수 사용시, 셀 병합 문제 해결 Tip
    1:30:06
  56. 최종 갱신일 구하기
    1:31:30
  57. 통장 잔액 구하기
    1:33:37
  58. 가계부 대시보드 완성 및 최종 테스트
    1:36:07
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [대시보드강의] 엑셀 카카오 스타일 대시보드 만들기
    예제파일
  • [관련자료] 엑셀 카카오 스타일 대시보드
    무료서식

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


표를 활용한 자동 확장 목록상자 만들기

엑셀의 범위를 표로 변환하면, 새로운 데이터가 추가되었을 때 범위가 자동으로 확장되는 동적범위를 매우 편리하게 사용할 수 있습니다.

  1. 범위를 표로 변환하기 : 예제파일을 실행한 후, [통장내역] 시트로 이동합니다. 이후 거래내역에서 임의의 셀을 선택한 후, Ctrl + A 를 누르면 연속된 데이터가 한 번에 선택됩니다. 그 상태에서 [삽입] 탭 - [표]를 클릭하거나 단축키 Ctrl + T 를 눌러 범위를 표로 변환합니다.

    엑셀 범위를 표로 변환
    거래내역 범위를 선택한 후, 삽입 탭 - 표에서 범위를 표로 변환합니다.
  2. 표를 선택한 후, [테이블 디자인] 탭에서 [표 스타일]을 없음으로 변경하면 범위의 기존 서식을 그대로 유지할 수 있습니다.
    엑셀 표 스타일 꾸미기
    표를 선택한 후, [테이블 디자인] - [표 스타일] 을 없음으로 변경합니다.
  3. 동일한 방법으로 오른쪽에 있는 계정과목 분류표와 계정과목 분류 단어표 범위를 표로 변환한 후, 각 표의 이름을 '분류표'와 '단어표'로 변경합니다.

    엑셀 대시보드 표 이름 변경
    오른쪽에 미리 정리한, 단어표의 분류표도 범위를 표로 변경한 후, 표 이름을 변경하빈다.
  4. 이제 거래내역 표의 오른쪽으로 대시보드에 사용할 새로운 항목을 추가합니다. 이번 강의에서는 "계정과목, 대분류, 거래일, 시간대" 를 아래 그림과 같이 추가합니다.
    엑셀 대시보드 항목 추가
    거래내역 표 오른쪽으로 대시보드에 사용할 항목을 추가합니다.
    오빠두Tip : 표의 오른쪽으로 새로운 항목을 추가하면, 표가 자동으로 확장되면서 오른쪽 아래에 표시되는 꺾쇠모양도 오른쪽으로 이동합니다.
    엑셀 표 자동 확장
  5. 자동으로 확장되는 목록상자 만들기 : 이제 계정과목을 선택해서 입력할 수 있도록 목록상자를 추가합니다. 계정과목을 입력할 H열을 전체 선택한 후, [데이터] 탭 - [데이터 유효성검사]로 이동합니다. 데이터유효성 대화상자가 실행되면, 제한대상으로 '목록'을 선택한 후, 원본 수식으로 아래 수식을 입력합니다. 수식을 입력한 후, [확인] 버튼을 누르면 분류표에 따라 자동으로 확장/축소되는 목록상자가 적용됩니다.
    =INDIRECT("분류표[분류]")
    엑샐 자동 확장 목록상자
    계정과목을 입력할 H열을 선택 후, 데이터 유효성 목록상자를 적용합니다.
    오빠두Tip : 데이터 유효성검사를 활용한 목록상자 기초 사용법은 아래 5분 기초영상 강의를 참고하세요!

각 지출 항목을 자동으로 분류하기

  1. 계정과목 자동 분류하기 : 이전에 키워드 자동 분류강의에서 소개해드린 함수 공식을 사용하면, 각 거래처별 계정과목을 편리하게 분류할 수 있습니다.
    =IFERROR(INDEX($출력범위,MIN(IF(ISNUMBER(FIND($단어범위,대상셀)),ROW($단어범위)-ROW($단어범위시작셀)+1,ROWS($단어범위)+1))),"")
    오빠두Tip : 공식의 동작원리 및 응용 예제는 아래 키워드 분류 자동화 공식 영상 강의를 참고해주세요!
  2. 계정과목의 첫번째 셀인 H8셀을 선택한 후, 아래 수식을 복사/붙여넣기로 입력합니다. 배열 수식이므로 엑셀 2019 이전버전 사용자는 Ctrl + Shift + Enter 로 입력하고, 엑셀2021이후/M365 사용자는 Enter 로 입력해도 괜찮습니다.
    =IFERROR(INDEX(단어표[분류],MIN(IF(ISNUMBER(FIND(단어표[포함단어],[@내용])),ROW(단어표[포함단어])-ROW($N$6)+1,ROWS(단어표[포함단어])+1))),"")

    엑셀 계정과목 자동 분류 공식
    키워드 분류 공식을 복사/붙여넣기 하면 각 거래별 계정과목이 자동으로 분류됩니다.
  3. 수식을 입력하면 아래쪽 나머지 행에 수식이 자동으로 완성됩니다. 만약 수식이 자동으로 입력되지 않을 경우, 영상강의 16:57을 참고하세요. 자동으로 채워진 계정과목을 살펴보면, 중간중간 비어있는 항목이 보입니다. 비어있는 항목을 확인하기 위해 [내용]을 오름차순 정렬 후 [계정과목]에서 비어있는 항목을 필터링합니다.

    엑셀 가계부 빈 항목 필터링
    필터를 걸어서 자동으로 분류되지 않은 계정과목을 확인합니다.
  4. 미분류 계정과목 확인 후 채워넣기 : 내용을 필터링하면 "가스파스, 예금, 오빠두엑셀, 인터넷상거래, 주식배당금, 쿠팡" 등 몇몇 자주 사용하는 거래처를 확인할 수 있습니다. 이러한 거래 목록을 단어표에 추가하면 이후 계정과목을 자동으로 완성할 수 있습니다. 다시 Ctrl + Shift + L 을 눌러 필터를 해제한 후, 오른쪽 단어표에 아래와 같이 포함단어와 분류를 추가합니다.
    거래내역 분류표 작성
    오른쪽 단어표에 자동으로 분류할 거래처명과 계정과목을 추가합니다.
    포함단어 분류
    11번가 기타생활비
    가스파스 주식
    츄러스 간식
    기타문화비
    수수료 업무관련
    아웃백 주식
    적금 예적금
    예금 예적금
    오빠두엑셀 급여
    인터넷상거래 기타생활비
    적금만기 금융이익
    배당금 금융이익
    중화 주식
    탕수육 주식
    쿠팡 기타생활비
    효성 기타생활비
    오빠두Tip : Ctrl + Shift + L(컨쉬엘) 단축키는 실무자가 꼭 알아야 할, 필터 적용/해제 단축키입니다. 컨쉬엘 단축키에 대한 자세한 설명은 아래 5분 영상강의를 참고해주세요!
  5. 단어표에 자주 사용하는 거래처 목록을 추가하면, 계정과목이 자동으로 채워집니다. 또는 영상강의 17:43처럼, 일회성으로 발생하는 비용은 직접 작성 후 값 붙여넣기로 입력합니다.

    통장 내역 계정과목 자동 분류
    단어표에 항목을 추가하면, 거래내역의 계정과목이 자동으로 채워집니다.
  6. 대분류, 거래일, 시간대 채워넣기 : 이제 나머지 대분류와 거래일, 시간대도 하나씩 작성합니다. 대분류가 시작되는 I8셀에 아래 수식을 입력하면 각 계정과목의 대분류가 자동으로 분류됩니다.
    =VLOOKUP([@계정과목],분류표,2,0)

    엑셀 가계부 대분류
    VLOOKUP 함수를 사용해 각 계정과목의 대분류를 입력합니다.
  7. 거래일과 시간대에도 아래 수식을 각각 입력하면 거래일과 시간대가 자동으로 채워집니다.
    항목 함수
    거래일 (J8셀) =[@거래일자]
    시간대 (K8셀) =HOUR([@거래시간])

    통장내역 거래일자 시간대 분류
    이후 나머지 거래일과 시간대도 하나씩 작성합니다.
  8. 시간대의 경우 일반서식을 사용하면 0시가 빈칸으로 표시됩니다. 따라서 시간대가 입력된 K열을 전체 선택 후, [홈] 탭 - [표시형식]을 숫자로 변경하거나 단축키 Ctrl + Shift + 1 을 눌러 숫자서식으로 변경합니다.
    엑셀 숫자 서식 변경
    시간대에서 숫자 0이 표시되지 않을 경우, 표시형식을 숫자형식으로 변경합니다.
    오빠두Tip : 셀 서식을 변경하는 단축키인 Ctrl + Shift + 1~5는 알아두면 실무에서 매우 편리합니다. 단축키에 대한 자세한 설명은 아래 실무자 필수 단축키 20개 영상강의를 참고하세요!

대시보드에 사용할 피벗테이블 만들기

이번 강의에서는 총 5가지를 주제로 대시보드를 제작합니다. 본 게시글에서는 각 피벗테이블의 필드 구성을 정리하였으며, 피벗테이블을 만드는 방법에 대한 자세한 설명은 영상 강의 24:02 이후를 참고해주세요!

  1. 대분류별 지출 상세 피벗테이블
    엑셀 가계부 대분류별 지출
    대분류별 지출 상세 피벗테이블
    영역 필드
    대분류
    출금(합계)
    오빠두Tip : 실무에서는 피벗테이블의 레이아웃을 '테이블' 형식으로 사용하면 데이터를 더욱 보기좋게 집계할 수 있습니다. 피벗테이블의 주요 설정방법에 대한 자세한 설명은 아래 5분 영상강의를 참고하세요!
  2. 상위 10개 거래내역 피벗테이블
    가계부 피벗테이블 항목별 지출
    상위 10개 거래내역 피벗테이블
    영역 필드
    내용
    출금(합계)
  3. 월별 지출/수입현황 피벗테이블
    엑셀 월별 입출금 내역
    월별 지출/수입현황 피벗테이블
    영역 필드
    거래일자(년도), 거래일자(월)
    출금(합계), 입금(합계)
    오빠두Tip : 피벗테이블의 날짜를 년도와 월로 그룹화하려면 원본데이터가 반드시 '날짜 형식'으로 작성되어야 합니다. 문자 형식의 날짜를 날짜데이터로 일괄 변환하는 방법은 아래 1분 영상강의를 참고하세요!
  4. 시간대별 지출현황 피벗테이블
    엑셀 통장내역 시간대별 지출
    시간대별 지출현황 피벗테이블
    영역 필드
    시간대
    출금(합계)
  5. 최근 30개 거래내역 피벗테이블
    엑셀 가계부 최근 지출
    최근 30개 거래내역 피벗테이블
    영역 필드
    거래일, 내용
    계산필드로 '입금'-'출금' 계산된 필드 추가
    오빠두Tip :
    주의사항 ① : 피벗테이블의 날짜 필드는 한 개의 기준으로만 그룹화할 수 있습니다. '거래일자' 필드는 월별 지출/수입현황 피벗테이블에서 이미 '년도'와 '월'로 그룹화했으므로, 이번에는 '거래일' 필드를 추가해서 '일'단위로 사용합니다.
    주의사항 ② : 계산필드를 사용해 '입출금'을 한 번에 표시하는 방법은 영상강의 41:10 을 참고하세요!

대시보드 핵심 기능, 슬라이서 추가하기

대시보드 분석에 사용할 피벗테이블을 모두 추가하였으면, 이제 피벗테이블을 실시간으로 필터링하기 위한 대시보드의 핵심 기능인 "슬라이서"를 추가합니다.

  1. 슬라이서 추가하기 : 추가한 피벗테이블 중에서 임의의 피벗테이블을 하나 선택합니다. 이후 [피벗테이블 분석] 탭 - [슬라이서 삽입] 버튼을 클릭하면 '슬라이서 삽입' 창이 실행되고, 목록에서 '년(거래일자), 개월(거래일자), 계정과목, 대분류'를 선택한 후 [확인] 버튼을 클릭합니다.
    피벗테이블 슬라이서 추가
    피벗테이블을 선택 - 피벗테이블 분석 - 슬라이서 삽입에서 슬라이서를 추가합니다.
    오빠두Tip : 년(거래일자)와 개월(거래일자)는 사용하고 계신 엑셀버전에 따라 "년도, 개월"과 같이 조금씩 다르게 표시될 수 있습니다.
  2. 이제 슬라이서를 적절한 위치로 이동한 후, 버튼을 클릭하면 선택했던 피벗테이블이 실시간으로 필터링되는 것을 확인할 수 있습니다. 이제 슬라이서 버튼을 클릭하면, 나머지 피벗테이블도 동시에 필터링되도록 보고서를 연결하겠습니다. 임의의 슬라이서를 우클릭 한 후, [보고서 연결]로 이동합니다.

    엑셀 슬라이서 보고서 연결
    피벗테이블을 동시에 필터링하기 위해 슬라이서를 우클릭 - 보고서 연결로 이동합니다.
  3. 보고서 연결 및 슬라이서 설정 : 그러면 새로운 창이 나오면서, 방금 전 추가했던 피벗테이블 목록을 확인할 수 있습니다. 목록에 있는 피벗테이블을 모두 선택한 후, [확인] 버튼을 클릭해서 보고서를 연결합니다. 나머지 슬라이서도 동일한 방법으로 보고서를 모두 연결합니다.
    엑셀 피벗테이블 연결
    보고서 연결의 피벗테이블 목록에서 연결할 피벗테이블을 모두 선택합니다.
    오빠두Tip : 보고서 연결은 피벗테이블의 원본데이터가 동일한 경우에만 가능하며, 원본데이터가 서로 다른 피벗테이블을 연결하려면 파워피벗을 사용하면 됩니다. 파워피벗에 대한 자세한 설명은 아래 관련 영상강의를 참고하세요!
  4. 슬라이서로 날짜 필드를 추가하면, 아래 그림과 같이 항상 비어있는 버튼이 표시됩니다. 그럴 경우, 슬라이서를 우클릭한 후, [슬라이서 설정]으로 이동해서 '데이터가 없는 항목 숨기기'를 체크하면 실제로 있는 날짜 버튼만 깔끔하게 표시할 수 있습니다.

    슬라이서 날짜 항목 숨기기
    날짜 데이터가 있는 슬라이서의 경우, 슬라이서 설정에서 데이터가 없는 항목 숨기기를 체크합니다.
  5. 슬라이서 꾸미기 : 슬라이서 설정을 모두 완료하였으면, 슬라이서를 대시보드 시트로 이동합니다. 키보드 Shift키를 누른 상태로, 슬라이서를 클릭하면 여러 슬라이서를 동시에 선택할 수 있습니다. 슬라이서를 잘라내기 후, 대시보드시트에 붙여넣기 합니다.
  6. 슬라이서를 선택한 상태로, 위쪽의 [슬라이서] 탭을 클릭하면, 슬라이서 스타일에서 '카카오톡' 형식의 디자인을 확인할 수 있습니다. 해당 스타일을 선택하면, 슬라이서가 깔끔하게 꾸며집니다.
    엑셀 슬라이서 디자인 꾸미기
    슬라이서를 대시보드 시트로 옮긴 후, 슬라이서 스타일을 변경합니다.
    오빠두Tip : 이전 강의에서 소개해드린 '스타일 시트'를 한번 사용해보세요! 슬라이서와 피벗테이블 을 더욱 편리하게 꾸밀 수 있습니다.
  7. 이후 영상강의 50:40 이후에 소개해드린 맞춤 등 다양한 기능을 활용해서 슬라이서 위치와 버튼 개수 등을 적절히 변경하면 아래 그림과 같이 슬라이서 디자인이 깔끔하게 완료됩니다.

    엑셀 슬라이서 버튼 완성
    이후 슬라이서의 위치와 크기, 버튼 설정을 적절히 변경하면 슬라이서 필터가 완성됩니다.

각 항목별 시각화 차트 만들기

각 항목별 차트를 만들고 시각화하는 방법은 영상강의 56:51 이후 설명으로 대체합니다!

엑셀 대시보드 차트 완성
영상강의 56:51 이후를 참고하여 대시보드의 각 항목별 시각화 차트를 완성합니다.

엑셀로 시각화 차트를 만들 때에는, '간소화'와 '색감' 2가지 핵심 규칙만 기억하면 충분합니다! 엑셀 시각화 차트를 만들기 위한 핵심 규칙과 기초 예제는 아래 5분 영상강의를 참고해주세요!

최종 갱신일 및 통장 잔액 구하기

자, 이제 시각화 차트까지 만들었다면 대시보드의 90%가 완성되었습니다. (정말 고생많으셨어요!🙌)

엑셀 카카오 가계부 대시보드 gif
대시보드에 필요한 세부 항목을 추가하면, 가계부 대시보드가 완성됩니다!

이제 마지막단계로 ① 사용자의 편의성을 개선하고, ② 대시보드의 세부항목을 추가하면 대시보드가 모두 완성됩니다. 아래 정리해드린 각 세부 항목 계산에 사용된 공식을 참고해서 카카오 스타일 가계부 대시보드를 완성해보세요!

최종 업데이트 날짜 구하는 공식 (1:28:54)

="마지막 업데이트 : "&TEXT(MAX(A:A),"yyyy년 mm월 dd일")

최종 잔액 구하는 공식 (1:33:37)

=INDEX($범위,MAX(IFERROR(MATCH("*",$범위,-1),0),IFERROR(MATCH(9E+307,$범위,1),0)))
오빠두Tip : 범위의 마지막 셀을 자동으로 찾는 동적범위의 기본 동작원리와 응용 예제는 아래 두 영상강의에서 자세히 정리했습니다.
5 6 투표
게시글평점
7 댓글
Inline Feedbacks
모든 댓글 보기
7
0
여러분의 생각을 댓글로 남겨주세요.x