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

1. 거래내역을 표로 변환하고 목록상자 만들기

엑셀의 일반 범위를 표로 변환하면 새로운 데이터가 추가될 때마다 범위가 자동으로 확장되는 동적범위를 매우 편리하게 사용할 수 있습니다. 가계부 대시보드의 원본이 되는 통장 거래내역과 분류표를 모두 표로 변환한 후, 데이터 유효성검사를 활용해 자동으로 확장되는 목록상자까지 추가하겠습니다.

1. 범위를 표로 변환하기 : 예제파일을 실행한 후 [통장내역] 시트로 이동합니다. 거래내역에서 임의의 셀을 선택한 상태에서 Ctrl + A 를 누르면 연속된 데이터가 한 번에 선택됩니다. 그 상태에서 [삽입] 탭 - [표]를 클릭하거나 단축키 Ctrl + T 를 눌러 선택된 범위를 표로 변환합니다.
2. 변환된 표를 선택한 후, [테이블 디자인] 탭에서 [표 스타일]을 '없음'으로 변경하면 기존에 적용해둔 셀 서식을 그대로 유지하면서 표 기능만 사용할 수 있습니다.
   
3. 동일한 방법으로 오른쪽에 있는 계정과목 분류표와 계정과목 분류 단어표 범위도 표로 변환한 후, 각 표의 이름을 '분류표'와 '단어표'로 변경합니다. 표 이름은 [테이블 디자인] 탭의 왼쪽 [표 이름] 입력란에서 수정할 수 있습니다.
4. 이제 거래내역 표의 오른쪽에 대시보드 분석에 사용할 새로운 항목을 추가합니다. 이번 강의에서는 "계정과목, 대분류, 거래일, 시간대" 4개 항목을 아래 그림과 같이 차례로 추가합니다.
   오빠두Tip : 표의 오른쪽에 새 항목을 추가하면 표가 자동으로 확장되며, 오른쪽 아래에 표시되던 꺾쇠 모양도 새 열의 끝으로 이동합니다.
   
5. 자동으로 확장되는 목록상자 만들기 : 이제 계정과목을 목록에서 선택해 입력할 수 있도록 목록상자를 추가합니다. 계정과목을 입력할 H열을 전체 선택한 후 [데이터] 탭 - [데이터 유효성검사]로 이동합니다. 데이터 유효성 대화상자가 실행되면 제한 대상으로 '목록'을 선택하고, 원본 수식으로 아래 수식을 입력합니다. [확인] 버튼을 누르면 분류표의 항목이 늘거나 줄어들 때 자동으로 확장·축소되는 목록상자가 적용됩니다.
   =INDIRECT("분류표[분류]")

   

   오빠두Tip : 데이터 유효성검사를 활용한 목록상자 기초 사용법은 아래 5분 기초영상 강의를 참고하세요!

   
   

2. 계정과목과 대분류·시간대 자동 분류하기

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)

   

7. 거래일과 시간대 항목에도 아래 수식을 각각 입력하면 거래일과 시간대가 자동으로 채워집니다.
   

   항목	함수
   거래일 (J8셀)	=[@거래일자]
   시간대 (K8셀)	=HOUR([@거래시간])

   

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

3. 대시보드용 피벗테이블 5개 추가하기

이번 강의에서는 총 5가지 주제의 피벗테이블로 대시보드를 구성합니다. 본 게시글에서는 각 피벗테이블의 필드 구성만 정리해두었으며, 피벗테이블을 만드는 자세한 절차는 영상 강의 24:02 이후 부분을 참고해주세요.

1. 대분류별 지출 상세 피벗테이블
   

   영역	필드
   행	대분류
   값	출금(합계)

   오빠두Tip : 실무에서는 피벗테이블 레이아웃을 '테이블' 형식으로 사용하면 데이터를 더욱 보기 좋게 집계할 수 있습니다. 피벗테이블의 주요 설정 방법에 대한 자세한 내용은 아래 5분 영상강의를 참고하세요!
   
2. 상위 10개 거래내역 피벗테이블
   

   영역	필드
   행	내용
   값	출금(합계)

3. 월별 지출/수입현황 피벗테이블
   

   영역	필드
   행	거래일자(년도), 거래일자(월)
   값	출금(합계), 입금(합계)

   오빠두Tip : 피벗테이블의 날짜를 년도와 월로 그룹화하려면 원본 데이터가 반드시 '날짜 형식'으로 작성되어 있어야 합니다. 문자 형식의 날짜를 날짜 데이터로 일괄 변환하는 방법은 아래 1분 영상강의를 참고하세요!
   
4. 시간대별 지출현황 피벗테이블
   

   영역	필드
   행	시간대
   값	출금(합계)

5. 최근 30개 거래내역 피벗테이블
   

   영역	필드
   행	거래일, 내용
   값	계산필드로 '입금'-'출금' 계산된 필드 추가

   오빠두Tip :
   주의사항 ① : 피벗테이블의 날짜 필드는 한 가지 기준으로만 그룹화할 수 있습니다. '거래일자' 필드는 월별 지출/수입현황 피벗테이블에서 이미 '년도'와 '월'로 그룹화했으므로, 이번에는 '거래일' 필드를 추가해 '일' 단위로 사용합니다.
   주의사항 ② : 계산필드를 사용해 '입출금'을 한 번에 표시하는 방법은 영상강의 41:10 부분을 참고하세요!

4. 슬라이서 추가하고 보고서 연결하기

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

1. 슬라이서 추가하기 : 추가한 피벗테이블 중 임의의 피벗테이블을 하나 선택합니다. 이후 [피벗테이블 분석] 탭 - [슬라이서 삽입] 버튼을 클릭하면 '슬라이서 삽입' 창이 실행됩니다. 목록에서 '년(거래일자), 개월(거래일자), 계정과목, 대분류'를 선택한 후 [확인] 버튼을 클릭합니다.
   오빠두Tip : 년(거래일자)와 개월(거래일자)는 사용 중인 엑셀 버전에 따라 "년도, 개월" 등으로 조금씩 다르게 표시될 수 있습니다.
2. 이제 슬라이서를 적절한 위치로 이동한 후 버튼을 클릭하면, 선택했던 피벗테이블이 실시간으로 필터링되는 것을 확인할 수 있습니다. 다음으로 슬라이서 버튼을 클릭할 때 나머지 피벗테이블도 동시에 필터링되도록 보고서를 연결하겠습니다. 임의의 슬라이서를 우클릭한 후 [보고서 연결]로 이동합니다.
3. 보고서 연결 및 슬라이서 설정 : 새 창이 실행되면서 방금 추가했던 피벗테이블 목록이 표시됩니다. 목록에 있는 피벗테이블을 모두 선택한 후 [확인] 버튼을 클릭해 보고서를 연결합니다. 나머지 슬라이서도 동일한 방법으로 보고서를 모두 연결합니다.
   오빠두Tip : 보고서 연결은 피벗테이블의 원본 데이터가 동일한 경우에만 가능하며, 원본 데이터가 서로 다른 피벗테이블을 연결하려면 파워피벗을 사용해야 합니다. 파워피벗에 대한 자세한 설명은 아래 관련 영상강의를 참고하세요!
   
4. 슬라이서로 날짜 필드를 추가하면 아래 그림처럼 항상 비어 있는 버튼이 함께 표시됩니다. 그럴 경우 슬라이서를 우클릭한 후 [슬라이서 설정]으로 이동해 '데이터가 없는 항목 숨기기'를 체크하면, 실제로 거래가 있는 날짜 버튼만 깔끔하게 표시할 수 있습니다.
5. 슬라이서 꾸미기 : 슬라이서 설정을 모두 완료하였으면 슬라이서를 대시보드 시트로 이동합니다. 키보드 Shift키를 누른 상태로 슬라이서를 클릭하면 여러 슬라이서를 동시에 선택할 수 있습니다. 슬라이서를 잘라내기 한 후 대시보드 시트에 붙여넣기 합니다.
6. 슬라이서를 선택한 상태에서 위쪽의 [슬라이서] 탭을 클릭하면 슬라이서 스타일에서 '카카오톡' 형식의 디자인을 확인할 수 있습니다. 해당 스타일을 선택하면 슬라이서가 깔끔하게 꾸며집니다.
   오빠두Tip : 이전 강의에서 소개해드린 '스타일 시트'를 활용하면 슬라이서와 피벗테이블을 더욱 편리하게 꾸밀 수 있습니다.
   
7. 이후 영상강의 50:40 이후에 소개한 맞춤 정렬 등 다양한 기능을 활용해 슬라이서의 위치와 버튼 개수를 적절히 변경하면, 아래 그림과 같이 슬라이서 디자인을 깔끔하게 마무리할 수 있습니다.

5. 시각화 차트로 대시보드 꾸미기

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

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

6. 사용자 편의성과 세부 항목으로 대시보드 마무리

시각화 차트까지 완성했다면 대시보드의 90%가 마무리된 상태입니다.

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

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

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