짤막한 강좌

이번 강좌에서는 시간 표시 막대와 슬라이서를 삽입해 피벗 테이블과 연결한 후 필터링 한 결과에 맞게 차트로 구성된 보고서가 동적으로 변하도록 하는 대시보드를 작성해 보겠습니다.

 

 

대시보드란? 데이터를 시각적으로 표현하고 분석할 수 있도록 차트, 표, 피벗 테이블 등을 통합하여
중요한 정보를 쉽게 파악할 수 있게 해주는 도구

 

 

 

https://youtu.be/v432qsPW-KU

 

 

실습 파일 다운로드

실습파일.xlsx

0.08MB

 

실습 파일에는 '피벗 테이블'과 '대시보드' 시트가 있습니다.

'피벗 테이블' 시트에는 월별 판매금액, 제품별 수량, 매장별 판매금액 그리고 매장별 판매금액과 구성비, 분류별 판매금액을 나타내는 다섯 개 피벗 테이블이 작성되어 있고, '대시보드' 시트에는 피벗 테이블을 원본으로 해서 보고서에 적합한 차트를 작성해 두었습니다.

 

 

 

먼저 각 피벗 테이블 이름을 변경해두어야 합니다. 그러면 대시보드를 작성할 때 헷갈리지 않습니다.

첫 번째 피벗 테이블을 선택하고 [피벗 테이블 분석]에서 피벗 테이블 이름을 '월별_판매'로 변경합니다.

이름은 이름만으로 어떤 피벗 테이블인지 알기 쉽도록 정하면 됩니다.

 

 

두 번째 피벗 테이블도 같은 방법으로 '제품별_수량', 세 번째 피벗 테이블은 '매장별_판매', 네 번째 피벗 테이블은 '매장별_판매구성비', 다섯 번째는 '분류별_판매'로 이름을 변경합니다.

 

이번에는 [페이지 레이아웃] 탭 - [테마] 그룹 - [색]을 선택합니다. 기본값인 오피스 색상 테마가 적용되어 있는데 이왜에도 다양한 색상 테마가 제공됩니다. 마우스를 가져다 대면 워크시트에 있는 피벗 테이블과 차트의 색상이 번번에 변경되는데 '녹색'을 선택합니다.

 

차트 크기 변경과 배치, 서식 수정
'대시보드' 시트에서 작업

 

월별 판매금액을 나타내는 차트의 꺾은선을 선택한 후 마우스 오른쪽 버튼을 눌러 [데이터 계열 서식]을 선택합니다.

 

그리고 '완만한 선'을 체크 표시합니다. 꺾은선이 완만해지니까 차트가 부드럽게 표현됩니다.

 

차트의 원본이 되는 피벗 테이블의 판매금액 단위가 커서 천 단위를 생략해 두었습니다.

연결된 차트에도 [단위:천원]'을 표시해 두었는데 이 표시는 차트에서 [차트 요소]를 선택해 '축 제목' - '기본 가로'에 체크 표시를 해 내용을 입력해 둔 것입니다.

 

차트 편집이 끝나면 [22] 행 위치에 배치하고 [서식] 탭 - 높이 '8.14cm', 너비 '17.12cm'로 크기를 변경합니다.

 

 

실제 작업할 때 여러 번 배치해 가면서 보기 좋게 설정하면 됩니다.

 

이번에는 제품별 수량의 합계를 나타내는 차트를 선택하고 높이 '8.14cm', 너비 '17.12cm'로 크기를 변경하고 [22] 행 위치에 배치해 월별_판매금액 차트와 나란하게 배치합니다.

 

더 정확하게 배치하려면 먼저 첫 번째 차트를 선택하고 <Shift>를 눌러 두 번째 차트를 선택한 후 [도형 서식] 탭 - [맞춤[ 그룹 - [중간 맞춤]을 선택합니다. 그러면 두 차트의 수평이 정확하게 맞춰집니다.

 

 

이제 도넛 차트와 콤보 차트의 크기를 변경하고 배치해 보겠습니다.

먼저 매장별_구성비를 나타내는 차트와 분류별_구성비를 나타내는 차트를 선택합니다.

 

[도형 서식] 탭 - [크기] 그룹에서 높이 '7.54cm', 너비 '9.5cm'로 변경합니다.

그리고 [8] 행 위치에 대충 배치합니다.

 

매장별 판매금액의 구성비를 나타내는 차트에서 도넛 구멍 크기를 '60%'로 변경해 도넛이 두꺼워지도록 설정합니다.

 

같은 방법으로 두 번째 도넛 차트도 선택하고 도넛 구멍을 '60%'로 변경합니다.

 

그리고 레이블은 직접 드래그해서 조각 바깥으로 배치합니다.

 

※ 원형 차트와 달리 도넛 차트는 레이블 위치를 자동으로 배치하는 옵션이 없습니다. 그래서 직접 드래그해서 위치를 변경해주면 됩니다.

 

'그림 영역'을 선택하고 차트를 아래쪽으로 위치를 변경할 수 있습니다.

 

이번에는 콤보 차트를 선택하고 높이 '7.51cm', 너비 '14.66cm'로 변경합니다

 

아래쪽에 있는 차트와 오른쪽 끝을 맞추겠습니다.

 

그런 다음 도넛 차트와 콤보 차트를 선택하고 [도형 서식] - [맞춤] - [위쪽 맞춤]을 선택합니다. 3개 차트 중에서 맨 위에 있는 차트를 기준으로 정렬합니다.

 

그리고 가로 간격을 통일하기를 선택하면 보기 좋게 간격이 설정됩니다.

 

콤보 차트를 보면 콤보 차트의 구성비는 꺾은선형으로 보조축을 기준으로 나타내고, 레이블까지 표시되어 있습니다. 레이블을 표시해 두었기 때문에 보조축의 값은 표시되지 않아도 됩니다. 그래서 보조축의 값을 보이지 않도록 설정 하겠습니다.

 

보조축을 선택하고 [축 서식] 창에서 [레이블 옵션]을 확장한 다음 레이블 위치를 '없음'을 선택합니다.

 

그럼 제거되는 것이 아니고 감춰집니다. 감춰지기 때문에 꺾은선은 보조축에 영향을 그대로 받아 변화 없이 그대로 표시됩니다.

 

그리고 [차트 스타일]을 선택해서 '색'을 선택한 다음 '다양한 색상표 3'을 선택합니다.

 

그리고 [차트 요소]를 눌러 '범례'를 '아래쪽'으로 배치하겠습니다. 범례가 있어야지 막대가 판매금액인지 꺾은선이 구성비인지 쉽게 확인이 가능합니다.

 

 

대시 보드 작성 - 시간 표시 막대 삽입
시간 표시 막대를 삽입해 동적으로 데이터를 분석해 볼 수 있는 대시보드를 완성해 보겠습니다.

 

'피벗 테이블' 시트를 선택하고 피벗 테이블 하나를 선택합니다. 어떤 것을 선택하든 상관없습니다. 그리고 [피벗 테이블 분석] - [시간 표시 막대 삽입]을 선택합니다.

 

'판매일'에 체크 표시하고 [확인]을 누릅니다. 시간 표시 막대가 삽입 됩니다.

 

타임라인은 사용하면 슬라이더 콘트롤을 원하는 기간으로 필터링할 수 있습니다.

 

 

삽입된 시간 표시 막대를 '대시보드' 시트로 옮겨 1행부터 표시되도록 배치하고 크기를 변경하겠습니다.

<Ctrl + X>를 눌러 잘라내기 한 후 '대시보드' 시트에서 <Ctrl + V>를 눌러 붙이기 합니다.

 

시각 표시 막대에서는 월 단위 뿐만 아니라 년, 분기, 일을 필터링할 수 있도록 설정을 변경할 수 있습니다.

 

이 대시보드에서는 월 단위로만 필터링할 것이므로 [타임라인] 탭 - [표시] 그룹에서 '시간 수준'에 체크 표시를 해제해 변경할 수 없도록 설정합니다.

 

그리고 '선택 레이블'도 해제해 현재 선택하고 있는 개월을 표시하지 않도록 하겠습니다. 표시하지 않아도 현재 선택하고 있는 기간을 바로 알 수가 있죠.

 

앞에서 필터 한 결과는 필터 해제합니다. 단축키 <Alt + C>를 눌러도 필터 한 결과를 해제할 수 있습니다.

 

12월까지 보이도록 시간 표시 막대의 크기를 변경합니다.

 

이번에는 시간 표시 막대에서 마우스 오른쪽 버튼을 눌러 [보고서 연결]을 선택합니다.

 

처음 시간 표시 막대를 삽입할 때 선택했던 피벗 테이블만 연결되어 있는데 보고서 연결은 기간을 필터링했을 때 변화되는 피벗 테이블을 연결해주는 기능입니다. 그래서 피벗 테이블이 필터링되면 연결된 차트는 자동으로 필터링되어 변합니다.

 

그럼 월별 판매금액을 나타내는 피벗 차트는 필터 했을 때 영향을 받지 않고 나머지 차트들은 해당 기간에 맞게 필터링된 결과로 변하도록 '월별_판매'만 제외하고 모두 체크 표시를 합니다. 그리고 [확인]을 누릅니다.

 

시간 표시 막대에서 3월을 선택합니다. 그랬더니 월별 판매 금액을 나타내는 차트를 제외하고 모든 차트가 3월의 데이터로 필터링됩니다.

 

이번에는 5월까지 기간을 확장해 보겠습니다. 필터링 한 결과에 맞게 연결된 차트가 변합니다.

 

다시 필터 해제 또는 단축키 <Alt + C>를 눌러 필터 결과를 해제합니다.

 

 

대시 보드 작성 - 슬라이서 삽입

 

'피벗 테이블' 시트를 다시 선택하고 피벗 테이블 하나를 선택한 다음 [피벗 테이블 분석] 탭에서 [슬라이서 삽입]을 선택합니다.

 

슬라이서를 사용하면 테이블 또는 피벗 테이블의 데이터를 쉽게 필터링 할 수 있습니다. 매장명과 제품명은 체크 표시하고 [확인]을 누릅니다. 삽입한 슬라이서를 <Shift>를 눌러 선택하고 잘라냅니다. 그런 다음 대시보드 시트에 붙여넣기해서 위치를 이동시켜 줍니다.

 

먼저 '매장명' 슬라이서를 선택한 다음 [슬라이서] 탭 - [단추] 그룹에서 열을 2로 변경합니다.

 

그랬더니 슬라이서 속의 매장명이 2열로 배치되었죠. 크기를 적당하게 변경합니다.

 

같은 방법으로 제품명 슬라이스서도 선택하고 [단추] 그룹에 있는 열을 5로 변경합니다. 그리고 제품이 모두 보이도록 변경해 줍니다.

 

가급적이면 시간 표시 막대와 슬라이서의 높이가 같은 것이 좋습니다. 먼저 시간 표시 막대의 높이를 확인합니다. 3.63cm입니다.

 

슬라이서를 선택해 높이를 '3.63cm' 그리고 그 옆에 있는 슬라이스서도 '3.63cm'로 높이를 변경합니다.

 

시간 표시 막대와 차트의 위치를 [왼쪽 맞춤]해서 나란하게 배치하고 슬라이서도 두 개 차트와 함께 선택해서 [오른쪽 맞춤]을 선택합니다.

 

 

그런 다음 시간 표시 막대와 슬라이서 두 개를 선택하고 [위쪽 맞춤]을 선택합니다.

 

그리고[가로 간격을 동일하게]를 선택합니다.

 

슬라이서 크기를 조금 더 크게 설정합니다. 가급적이면 시간 표시 막대와 슬라이서의 간격이 비슷하면 완성도 있는 대시보드를 완성할 수 있습니다.

 

차트를 선택하고 방향키를 눌러 섬세하게 위치를 변경합니다.

 

매장명 슬라이서를 선택하고 마우스 오른쪽 버튼을 눌러 [보고서 연결]을 선택합니다. 매장별_판매와 매장별_판매구성비를 제외하고 모두 체크 표시하고 [확인]을 누릅니다.

 

매장명 슬라이서에서 '광명점'을 선택하면 분류별 판매금액의 구성비를 나타내는 차트와 월별 판매금액을 나타내는 차트, 제품별 수량의 합계를 나타내는 차트가 필터링되어 변하는 것을 알 수 있습니다.

 

'다중 선택' 옵션을 선택하면 광명점 뿐만 아니라 광명점, 부산점, 세종점 여러 매장의 데이터도 확인할 수 있습니다.

 

 

이번에는 제품명 슬라이서를 선택하고 [보고서 연결]을 실행합니다.

여기서 한 가지! 알아두어야 할 것이 있습니다. 모든 보고서가 슬라이서에 연결되어 있으면 어떤 문제점이 있는지 보겠습니다.

 

예를 들어 김치냉장고를 필터링했을 때 분류별 판매금액의 구성비를 나타내는 차트와 제품별 수량의 합을 나타내는 차트는 계열이 하나만 표시되기 때문에 의미 없는 차트가 됩니다.

 

 

이번에는 제품명 슬라이서를 선택하고 [보고서 연결]을 실행합니다.

여기서 한 가지! 알아두어야 할 것이 있습니다. 모든 보고서가 슬라이서에 연결되어 있으면 어떤 문제점이 있는지 보겠습니다.

 

예를 들어 김치냉장고를 필터링했을 때 분류별 판매금액의 구성비를 나타내는 차트와 제품별 수량의 합을 나타내는 차트는 계열이 하나만 표시되기 때문에 의미 없는 차트가 됩니다.

 

이제 제품명을 필터링해보면 보고서에 연결되지 않은 차트를 제외하고 나머지 차트가 필터링 결과에 맞게 변하는 것을 알 수 있습니다.

 

 

타임라인과 슬라이서 제거

 

 

삽입한 타임라인과 슬라이서를 제거하는 방법은 선택하고 <Delete> 키를 누르면 쉽게 제거할 수 있습니다.

 

마지막으로 [보기] 탭을 선택하고 [표시] 그룹에서 '눈금선'에 체크 표시를 해제합니다. 그러면 워크 시트의 눈금선이 보이지 않아 워드 문서와 같아 보이도록 설정할 수 있습니다.

 

그리고 모든 셀을 선택하고 [홈] 탭 - [채우기 색]을 '연한 회색'을 선택합니다.

 

이번 시간에는 시간 표시 막대와 슬라이서를 삽입해 보고서를 동적으로 분석하는 대시보드를 작성해 봤습니다. 꼭 활용해 보세요, 데이터를 한 눈에 파악하는 편리한 보고서를 작성할 수 있습니다.

 

강의가 마음에 드셨다면 구독해보세요~ 매주 새로운 강의를 학습할 수 있습니다.

즐거운 주말 보내세요~