엑셀 데이터 활용하기(3) 대시보드형 보고서 만들기
안녕하세요. 하트카운트 팀입니다.
이번에는 '엑셀 데이터 활용하기' 시리즈의 마지막 세션입니다.
지난 세션은 아래의 링크에서 확인 가능합니다 :)
지난 세션을 통해서 데이터를 정리하고 시각화했으니, 다음으로는 보고나 공유, 정리를 위해 가장 잘 흔히 사용되는 '대시보드'를 만들어야 합니다.
엑셀을 이용해서 대시보드를 만드는 과정을 지금부터 다루어 보겠습니다.
엑셀의 '피벗 테이블'과 '차트' 기능을 주로 사용하여 대시보드를 만들겠습니다.
데이터셋은 지난 세션들과 동일합니다.
데이터셋은 아래와 같이 구성되어 있습니다.
1. 대시보드 구성 계획하기
대시보드를 어떻게 만들지 구성할지 미리 계획해 두면, 전체적인 구조를 고려하며 더 전달력있는 대시보드를 구성할 수 있습니다. 또 중간에 헤매는 일이 적어집니다.
그러면 KPI(핵심 지표)를 먼저 설정하고, 어떻게 지표의 추이와 현황을 표시할지 고민해 봅시다.
저는 '이익'을 KPI로 지정하고 다양한 관점(요인)으로 이익의 변화를 나타내고 싶습니다.
데이터를 구성하는 변수를 확인해 본 후, 아래와 같이 구성과 시각화 방법을 결정했습니다.
(1) 이익과 매출액의 추이: 첫 번째 줄에 배치하고 선형차트로 표현
(2) 각 변수별 이익의 비율: 두 번째 줄에 배치하고 파이차트로 표현 - 고객분류별, 제품대분류별, 배송방식별, 지역별
(3) 할인율에 따른 이익: 세 번째 줄에 배치하고 산점도로 표현
(4) 이익 상위 10위권 제품: 세 번째 줄에 배치하고 테이블로 표현
이렇게 글로 적었지만 종이가 있다면 직접 펜으로 구역을 나누며 생각해 보는 것도 좋습니다. :)
2. 항목별 '피벗테이블' 생성하기
위 gif는 피벗테이블을 생성하는 과정입니다.
데이터가 기입된 테이블 영역 전체를 선택(control/command + A)한 후 [삽입 - 피벗테이블]을 클릭합니다.
저는 '소스데이터', '피벗테이블', '대시보드'로 탭을 나누기 위해서 피벗 테이블의 생성 위치를 '새 워크시트'로 선택하였습니다.
먼저 '(1) 이익과 매출액의 추이'를 나타내기 위한 피벗테이블을 생성하겠습니다.
아래의 순서를 따라 진행합니다.
1단계 - '행'과 '값'에 원하는 변수를 '필드 이름' 창에서 드래그해 넣습니다.
2단계 - '주문일'을 행 상자에 넣습니다. 날짜 변수는 엑셀이 자동으로 연, 분기, 일을 자동으로 생성하기 때문에 필요하지 않은 분기는 제거합니다.
3단계 - 값 상자에는 이익과 매출의 평균을 넣습니다. (*기본적으로 합계가 들어가며 평균값을 표시하기 위해서는 해당 변수를 마우스 우클릭하여 필드 값 설정을 진행합니다.)
시각적으로도 확인해 보세요!
원하는대로 피벗테이블이 구성되었으면, 다음으로는 '(2) 각 변수별 이익의 비율'을 담고 있는 피벗 테이블을 그려 보겠습니다.
1단계 - 피벗차트(1)을 전체 선택(control/command + a)하여 복사(control/command + c), 붙여넣기(control/command + c)를 진행합니다.
2단계 - 행 상자에 '고객 분류', 값 상자에 '이익'을 넣습니다. (*전체 이익에서 각 고객 분류가 차지하는 비율을 알기 위해 합계값으로 지정)
각 영역 상자에 원하는 변수를 넣어서 최종적으로 아래와 같이 여섯 개의 피벗테이블을 완성했습니다.
'(4) 이익 상위 10위권 제품'은 테이블 형태로 표시하기 위해 대시보드에서 바로 생성하도록 하겠습니다.
3. '차트' 기능 활용
이제 각 피벗테이블을 시각화하여 대시보드를 구성할게요.
대표적으로 차트 두 개를 만드는 과정을 상세하게 살펴 보겠습니다. 나머지 차트도 해당 과정을 이해하여 쉽게 구성 가능합니다.
(1) 이익과 매출액 추이 - 선형 차트
해당하는 피벗테이블의 아무 셀이나 선택한 상태로 [삽입 - 차트]를 클릭합니다. 저는 계획했던대로 선형차트를 선택했습니다.
차트가 그려졌습니다.
디자인을 몇 가지 수정해 볼까요? 차트 이름은 넣고 범례를 오른쪽에서 아래쪽으로 변경해 보겠습니다. 웬만한 차트 디자인 수정은 차트 각 영역을 더블 클릭하면 보이는 '서식창'에서 가능합니다.
차트 제목은 없는 상태이기 때문에 다른 방식으로 먼저 추가하겠습니다.
차트를 클릭하면 활성화되는 [디자인 - 차트 요소 추가 - 차트 제목 - 차트 위]를 선택합니다.
다양한 요소가 보이시나요? 이런 방식으로 여러 요소의 표시 여부와 위치를 결정할 수 있습니다.
이번에는 범례의 위치를 바꿔 보겠습니다.
범례 영역을 더블클릭하면 오른쪽에 '범례 서식' 메뉴가 활성화됩니다.
이때 범례 옵션에서 위치를 지정 가능합니다.
데이터 레이블(값 표시)도 추가해 볼까요?
해당하는 영역을 우클릭하여 '데이터 레이블 추가'를 선택하겠습니다.
최종적으로 아래와 같은 차트가 완성되었습니다.
완성된 차트를 잘라내어(control/command + x) 대시보드 탭에 붙여넣기 합니다.
(2) 각 변수별 이익의 비율 - 파이차트
고객분류, 제품대분류, 배송방식, 지역별로 총 네 개의 파이 차트를 만들기로 했으나 평균 이익이 음수인 지역이 있는 관계로 지역별 이익은 막대차트로 대체하도록 하겠습니다.
앞에서 선쳥차트를 만들었던 것과 마찬가지로 해당하는 피벗테이블을 클릭하여 [삽입 - 차트]에서 파이차트를 선택합니다.
이번에도 간단하게 디자인을 수정해 볼까요?
위에서와 같이 하나 하나 직접 수정할 수도 있지만, 이번에는 [디자인] 탭 오른쪽에 있는 빠른 모양의 '레이아웃 1'을 선택해 보겠습니다.
차트가 깔끔하게 정리된 것이 보입니다.
각 파이의 색이 짙어서 레이블이 잘 보이지 않으니 글자 색도 변경해 볼까요?
레이블을 클릭한 후 [홈]에서 글자 색을 지정합니다.
차트가 완성되었으면 잘라내어 대시보드 탭에 붙여넣습니다.
(4) 이익 상위 10위권 제품 - 테이블
이번에는 차트 기능을 통해 시각화하는 것이 아니라 대시보드에 바로 피벗테이블을 만들어 넣겠습니다.
위치는 대시보드 탭에서 적당한 셀을 선택하여 넣어 줍니다.
피벗테이블 행은 '이름', 값은 '이익' 합계로 지정합니다.
테이블의 '이름' 오른쪽에 삼각형(▼)이 보이시나요? 상위 10위원만 표시할 수 있도록 필터링하기 위해 필요한 버튼입니다.
(*삼각형이 보이지 않는다면 해당 셀을 선택 후 상단 메뉴의 [피벗 테이블 분석 - 필드 머리글] 선택)
삼각형을 누르면 위와 같은 창이 활성화됩니다.
정렬 기준을 '합계: 이익', 값 기준을 '상위 10개'로 지정하고 그 아래 생겨난 입력창에 표시하고 싶은 행의 수를 넣습니다. 저는 상위 10위권을 표시하기 위해 '10'을 입력했습니다.
정렬도 선택하여 깔끔하게 구성해 보세요.
4. 대시보드 구성하기
만든 피벗테이블을 대시보드 탭에 모아 적절히 배치해 두었습니다.
엑셀 느낌이 강해 대시보드 느낌이 덜 나네요.
몇 가지 디자인을 수정해 보겠습니다.
배경색을 지정하고, 엑셀의 눈금선과 머리글을 없앨게요.
붉은 사각형으로 표시된 부분을 클릭해 전체를 선택한 후, 채우기 색을 선택합니다.
[보기 - 표시] 메뉴에서 표시할 사항을 선택할 수 있습니다.
이렇게 가장 기본적인 요소들로 대시보드를 만들어 보았습니다.
글이 길어져 포함시키지 못했지만 슬라이서 기능을 이용해 한 번에 동일한 필터를 적용할 수도 있답니다. :)
대시보드를 완성했는데 지표 변화에 대한 구체적인 이유가 궁금하지는 않으신가요?
데이터 분석 솔루션 HEARTCOUNT를 사용해 보세요.
아래부터는 동일한 대시보드를 HEARTCOUNT ABI로 만들어 특이한 지표 변화의 요인을 발견하는 과정을 담고 있습니다.
HEARTCOUNT ABI를 이용해 위와 동일한 구성으로 제작한 대시보드입니다.
가장 위의 차트를 보니 이익과 매출이 대체로 같은 방향으로 움직이지만 몇몇 시점에서는 서로 다른 방향으로 움직이기도 했습니다.
이런 변화에 가장 큰 영향을 준 변수는 무엇일까요?
특이 변화를 보인 포인트 중 하나인 2020년 1월 데이터를 분석해 보겠습니다. 해당 차트 오른쪽 위 로고를 눌러 분석 기능으로 이동합니다.
대시보드의 첫 번째 차트와 동일한 차트가 표시되었습니다.
궁금한 1월의 데이터만 필터링해 표시하였습니다.
이익과 매출 간의 관계를 보기 위해 X축에 매출을 넣겠습니다.
매출은 늘 양수인데 이익이 마이너스였던 위 드래그(회색 사각형) 영역에 표시된 포인트를 중심으로 살펴 보겠습니다.
먼저, 각 변수에 따라 색상을 구분하여 비교해 보겠습니다.
(1) 제품대분류에 따라 구분
(2) 지역에 따라 구분
(3) 할인율에 따라 구분
가장 눈에 띄게 관계가 있어 보이는 변수는 할인율이었습니다.
할인율이 높아질수록 이익과 매출이 강한 음의 상관관계에 있는 것이 확인됩니다.
이렇게 수동 분석을 진행할 수도 있지만 자동 분석을 통해서도 쉽게 지표 변화의 요인을 발견할 수 있습니다.
오른쪽 위의 [캠페인 저장]을 클릭합니다.
자동 분석의 기준이 되는 KPI를 먼저 지정합니다.
저는 '이익' 변수를 KPI로 지정하였습니다.
KPI의 변화를 잘 설명하는 변수를 설명력이 높은 순서대로 자동 제시해 주는 '요인 분석' 기능을 활용해 볼게요.
이때 데이터셋에 포함된 모든 변수와의 상관관계가 자동으로 다 분석되므로 주관의 개입, 특정 변수 누락의 걱정이 없습니다.
요인분석 화면을 보니, 할인율과 매출 변수가 요인 변수의 변화를 가장 잘 설명하는 조합인 것이 확인됩니다.
해당 항목을 클릭하여, 하단에서 시각적으로도 상세하게 살펴볼 수 있습니다. 할인율이 '34%'을 넘어서면서부터 계속해서 적자였다는 것이 확인됩니다.
이렇게 '엑셀 데이터 활용하기' 시리즈가 마무리되었습니다.
다양한 내용을 담으려고 하다보니 글이 매우 길어졌네요.
본문에서는 HEARTCOUNT ABI의 대시보드에서 분석기능으로 넘어가는 과정을 담았지만, HEARTCOUNT에서 바로 데이터를 업로드하는 식으로도 이용 가능합니다.
손쉽게 데이터를 파악하고 분석할 수 있는 솔루션, HEARTCOUNT를 무료로 시작하고 싶으신가요? 공식 홈페이지에서 [무료로 시작하기]를 클릭해 보세요.
