엑셀의 범위를 표로 변환하면, 새로운 데이터가 추가되었을 때 범위가 자동으로 확장되는 동적범위를 매우 편리하게 사용할 수 있습니다.
범위를 표로 변환하기: 예제파일을 실행한 후, [통장내역] 시트로 이동합니다. 이후 거래내역에서 임의의 셀을 선택한 후, Ctrl + A 를 누르면 연속된 데이터가 한 번에 선택됩니다. 그 상태에서 [삽입] 탭 - [표]를 클릭하거나 단축키 Ctrl + T 를 눌러 범위를 표로 변환합니다.
거래내역 범위를 선택한 후, 삽입 탭 - 표에서 범위를 표로 변환합니다.
표를 선택한 후, [테이블 디자인] 탭에서 [표 스타일]을 없음으로 변경하면 범위의 기존 서식을 그대로 유지할 수 있습니다. 표를 선택한 후, [테이블 디자인] - [표 스타일] 을 없음으로 변경합니다.
동일한 방법으로 오른쪽에 있는 계정과목 분류표와 계정과목 분류 단어표 범위를 표로 변환한 후, 각 표의 이름을 '분류표'와 '단어표'로 변경합니다.
오른쪽에 미리 정리한, 단어표의 분류표도 범위를 표로 변경한 후, 표 이름을 변경하빈다.
이제 거래내역 표의 오른쪽으로 대시보드에 사용할 새로운 항목을 추가합니다. 이번 강의에서는 "계정과목, 대분류, 거래일, 시간대" 를 아래 그림과 같이 추가합니다.
거래내역 표 오른쪽으로 대시보드에 사용할 항목을 추가합니다.
오빠두Tip : 표의 오른쪽으로 새로운 항목을 추가하면, 표가 자동으로 확장되면서 오른쪽 아래에 표시되는 꺾쇠모양도 오른쪽으로 이동합니다.
자동으로 확장되는 목록상자 만들기: 이제 계정과목을 선택해서 입력할 수 있도록 목록상자를 추가합니다. 계정과목을 입력할 H열을 전체 선택한 후, [데이터] 탭 - [데이터 유효성검사]로 이동합니다. 데이터유효성 대화상자가 실행되면, 제한대상으로 '목록'을 선택한 후, 원본 수식으로 아래 수식을 입력합니다. 수식을 입력한 후, [확인] 버튼을 누르면 분류표에 따라 자동으로 확장/축소되는 목록상자가 적용됩니다.
=INDIRECT("분류표[분류]")
계정과목을 입력할 H열을 선택 후, 데이터 유효성 목록상자를 적용합니다.
오빠두Tip : 데이터 유효성검사를 활용한 목록상자 기초 사용법은 아래 5분 기초영상 강의를 참고하세요!
수식을 입력하면 아래쪽 나머지 행에 수식이 자동으로 완성됩니다. 만약 수식이 자동으로 입력되지 않을 경우, 영상강의 16:57을 참고하세요. 자동으로 채워진 계정과목을 살펴보면, 중간중간 비어있는 항목이 보입니다. 비어있는 항목을 확인하기 위해 [내용]을 오름차순 정렬 후 [계정과목]에서 비어있는 항목을 필터링합니다.
필터를 걸어서 자동으로 분류되지 않은 계정과목을 확인합니다.
미분류 계정과목 확인 후 채워넣기: 내용을 필터링하면 "가스파스, 예금, 오빠두엑셀, 인터넷상거래, 주식배당금, 쿠팡" 등 몇몇 자주 사용하는 거래처를 확인할 수 있습니다. 이러한 거래 목록을 단어표에 추가하면 이후 계정과목을 자동으로 완성할 수 있습니다. 다시 Ctrl + Shift + L 을 눌러 필터를 해제한 후, 오른쪽 단어표에 아래와 같이 포함단어와 분류를 추가합니다.
오른쪽 단어표에 자동으로 분류할 거래처명과 계정과목을 추가합니다.
포함단어
분류
11번가
기타생활비
가스파스
주식
츄러스
간식
펍
기타문화비
수수료
업무관련
아웃백
주식
적금
예적금
예금
예적금
오빠두엑셀
급여
인터넷상거래
기타생활비
적금만기
금융이익
배당금
금융이익
중화
주식
탕수육
주식
쿠팡
기타생활비
효성
기타생활비
오빠두Tip :Ctrl + Shift + L(컨쉬엘) 단축키는 실무자가 꼭 알아야 할, 필터 적용/해제 단축키입니다. 컨쉬엘 단축키에 대한 자세한 설명은 아래 5분 영상강의를 참고해주세요!
단어표에 자주 사용하는 거래처 목록을 추가하면, 계정과목이 자동으로 채워집니다. 또는 영상강의 17:43처럼, 일회성으로 발생하는 비용은 직접 작성 후 값 붙여넣기로 입력합니다.
단어표에 항목을 추가하면, 거래내역의 계정과목이 자동으로 채워집니다.
대분류, 거래일, 시간대 채워넣기: 이제 나머지 대분류와 거래일, 시간대도 하나씩 작성합니다. 대분류가 시작되는 I8셀에 아래 수식을 입력하면 각 계정과목의 대분류가 자동으로 분류됩니다.
=VLOOKUP([@계정과목],분류표,2,0)
VLOOKUP 함수를 사용해 각 계정과목의 대분류를 입력합니다.
거래일과 시간대에도 아래 수식을 각각 입력하면 거래일과 시간대가 자동으로 채워집니다.
항목
함수
거래일 (J8셀)
=[@거래일자]
시간대 (K8셀)
=HOUR([@거래시간])
이후 나머지 거래일과 시간대도 하나씩 작성합니다.
시간대의 경우 일반서식을 사용하면 0시가 빈칸으로 표시됩니다. 따라서 시간대가 입력된 K열을 전체 선택 후, [홈] 탭 - [표시형식]을 숫자로 변경하거나 단축키 Ctrl + Shift + 1 을 눌러 숫자서식으로 변경합니다.
시간대에서 숫자 0이 표시되지 않을 경우, 표시형식을 숫자형식으로 변경합니다.
오빠두Tip : 셀 서식을 변경하는 단축키인 Ctrl + Shift + 1~5는 알아두면 실무에서 매우 편리합니다. 단축키에 대한 자세한 설명은 아래 실무자 필수 단축키 20개 영상강의를 참고하세요!
이번 강의에서는 총 5가지를 주제로 대시보드를 제작합니다. 본 게시글에서는 각 피벗테이블의 필드 구성을 정리하였으며, 피벗테이블을 만드는 방법에 대한 자세한 설명은 영상 강의 24:02 이후를 참고해주세요!
대분류별 지출 상세 피벗테이블대분류별 지출 상세 피벗테이블
영역
필드
행
대분류
값
출금(합계)
오빠두Tip : 실무에서는 피벗테이블의 레이아웃을 '테이블' 형식으로 사용하면 데이터를 더욱 보기좋게 집계할 수 있습니다. 피벗테이블의 주요 설정방법에 대한 자세한 설명은 아래 5분 영상강의를 참고하세요!
상위 10개 거래내역 피벗테이블상위 10개 거래내역 피벗테이블
영역
필드
행
내용
값
출금(합계)
월별 지출/수입현황 피벗테이블월별 지출/수입현황 피벗테이블
영역
필드
행
거래일자(년도), 거래일자(월)
값
출금(합계), 입금(합계)
오빠두Tip : 피벗테이블의 날짜를 년도와 월로 그룹화하려면 원본데이터가 반드시 '날짜 형식'으로 작성되어야 합니다. 문자 형식의 날짜를 날짜데이터로 일괄 변환하는 방법은 아래 1분 영상강의를 참고하세요!
시간대별 지출현황 피벗테이블시간대별 지출현황 피벗테이블
영역
필드
행
시간대
값
출금(합계)
최근 30개 거래내역 피벗테이블최근 30개 거래내역 피벗테이블
영역
필드
행
거래일, 내용
값
계산필드로 '입금'-'출금' 계산된 필드 추가
오빠두Tip : 주의사항 ① : 피벗테이블의 날짜 필드는 한 개의 기준으로만 그룹화할 수 있습니다. '거래일자' 필드는 월별 지출/수입현황 피벗테이블에서 이미 '년도'와 '월'로 그룹화했으므로, 이번에는 '거래일' 필드를 추가해서 '일'단위로 사용합니다. 주의사항 ② : 계산필드를 사용해 '입출금'을 한 번에 표시하는 방법은 영상강의 41:10 을 참고하세요!
대시보드 분석에 사용할 피벗테이블을 모두 추가하였으면, 이제 피벗테이블을 실시간으로 필터링하기 위한 대시보드의 핵심 기능인 "슬라이서"를 추가합니다.
슬라이서 추가하기: 추가한 피벗테이블 중에서 임의의 피벗테이블을 하나 선택합니다. 이후 [피벗테이블 분석] 탭 - [슬라이서 삽입] 버튼을 클릭하면 '슬라이서 삽입' 창이 실행되고, 목록에서 '년(거래일자), 개월(거래일자), 계정과목, 대분류'를 선택한 후 [확인] 버튼을 클릭합니다.
피벗테이블을 선택 - 피벗테이블 분석 - 슬라이서 삽입에서 슬라이서를 추가합니다.
오빠두Tip : 년(거래일자)와 개월(거래일자)는 사용하고 계신 엑셀버전에 따라 "년도, 개월"과 같이 조금씩 다르게 표시될 수 있습니다.
이제 슬라이서를 적절한 위치로 이동한 후, 버튼을 클릭하면 선택했던 피벗테이블이 실시간으로 필터링되는 것을 확인할 수 있습니다. 이제 슬라이서 버튼을 클릭하면, 나머지 피벗테이블도 동시에 필터링되도록 보고서를 연결하겠습니다. 임의의 슬라이서를 우클릭 한 후, [보고서 연결]로 이동합니다.
피벗테이블을 동시에 필터링하기 위해 슬라이서를 우클릭 - 보고서 연결로 이동합니다.
보고서 연결 및 슬라이서 설정:그러면 새로운 창이 나오면서, 방금 전 추가했던 피벗테이블 목록을 확인할 수 있습니다. 목록에 있는 피벗테이블을 모두 선택한 후, [확인] 버튼을 클릭해서 보고서를 연결합니다. 나머지 슬라이서도 동일한 방법으로 보고서를 모두 연결합니다.
보고서 연결의 피벗테이블 목록에서 연결할 피벗테이블을 모두 선택합니다.
오빠두Tip : 보고서 연결은 피벗테이블의 원본데이터가 동일한 경우에만 가능하며, 원본데이터가 서로 다른 피벗테이블을 연결하려면 파워피벗을 사용하면 됩니다. 파워피벗에 대한 자세한 설명은 아래 관련 영상강의를 참고하세요!
슬라이서로 날짜 필드를 추가하면, 아래 그림과 같이 항상 비어있는 버튼이 표시됩니다. 그럴 경우, 슬라이서를 우클릭한 후, [슬라이서 설정]으로 이동해서 '데이터가 없는 항목 숨기기'를 체크하면 실제로 있는 날짜 버튼만 깔끔하게 표시할 수 있습니다.
날짜 데이터가 있는 슬라이서의 경우, 슬라이서 설정에서 데이터가 없는 항목 숨기기를 체크합니다.
슬라이서 꾸미기: 슬라이서 설정을 모두 완료하였으면, 슬라이서를 대시보드 시트로 이동합니다. 키보드 Shift키를 누른 상태로, 슬라이서를 클릭하면 여러 슬라이서를 동시에 선택할 수 있습니다. 슬라이서를 잘라내기 후, 대시보드시트에 붙여넣기 합니다.
슬라이서를 선택한 상태로, 위쪽의 [슬라이서] 탭을 클릭하면, 슬라이서 스타일에서 '카카오톡' 형식의 디자인을 확인할 수 있습니다. 해당 스타일을 선택하면, 슬라이서가 깔끔하게 꾸며집니다.
슬라이서를 대시보드 시트로 옮긴 후, 슬라이서 스타일을 변경합니다.
오빠두Tip : 이전 강의에서 소개해드린 '스타일 시트'를 한번 사용해보세요! 슬라이서와 피벗테이블 을 더욱 편리하게 꾸밀 수 있습니다.
이후 영상강의 50:40 이후에 소개해드린 맞춤 등 다양한 기능을 활용해서 슬라이서 위치와 버튼 개수 등을 적절히 변경하면 아래 그림과 같이 슬라이서 디자인이 깔끔하게 완료됩니다.
