엑셀로 데이터 활용하기 시리즈의 마지막 편입니다.
데이터를 정리하고 분석 및 시각화도 해보았으니 이제는 중요한 정보만 정리하여 나도 보고 다른 사람들에게도 공유하는 단계가 진행되어야 합니다.
데이터에서 전반적인 흐름을 파악하고 공유하는 데 가장 많이 쓰이는 수단으로는 대시보드가 있습니다. 특히 기업에서는 동일한 지표를 전사적으로 공유하고 관리해야 하기 때문에 함께 보는 대시보드 구성이 정말 중요합니다.
그래서 이번 글에서는 '대시보드형 보고서 만들기'를 주제로 다루어 보겠습니다!
앞의 두 개의 글과 동일한 데이터셋을 사용하겠습니다.
주로 사용할 기능은 '피벗 테이블'과 '차트'입니다.
1단계. 대시보드 구성 계획하기
먼저 대시보드를 어떻게 구성할지에 대해서 정리하면, 중간에 길을 잃게 되는 일이 적습니다.
대시보드로 알고 싶은 핵심 지표, KPI를 먼저 설정한 다음 어떤 시점에서 지표를 나타낼지에 대해서 생각해 볼까요?
저는 이익 변수를 KPI로 설정하고 다양한 요인별 이익의 변화를 살펴 보겠습니다.
데이터를 살펴 본 후에 상세한 항목은 아래와 같이 구성했습니다. 또 각각 어떻게 시각화할지도 동시에 고민해 봤어요.
- 이익과 매출액의 추이 (선형차트) - 첫 번째 줄에 위치
- 각 변수별 이익 비율 (파이차트) - 두 번째 줄에 위치
(: 고객분류별, 제품대분류별, 배송방식별, 지역별) - 할인율에 따른 이익 (산점도) - 세 번째 줄에 위치
- 이익 TOP10 제품명 (테이블) - 세 번째 줄에 위치
가능하다면 종이나 빈 슬라이드에 어떤식으로 구성할지 구역을 나누어 보는 것도 좋겠습니다 :) 저는 이 정도로 계획을 한 후에 대시보드 제작을 진행했습니다.
2단계. 항목별 피벗테이블 만들기
피벗테이블을 만드는 과정을 영상으로 간단하게 보여드렸습니다.
소스데이터를 선택한 후에 상단 [삽입] 탭에서 가장 왼쪽에 있는 '피벗테이블'을 선택합니다. 새 워크시트에 추가를 선택한 이유는 각 탭별로 '소스데이터 탭', '피벗테이블 탭', '대시보드 탭'으로 정리하려고 하기 때문입니다.
우선 '1. 이익과 매출액의 추이'를 나타내기 위한 테이블을 구성해 보겠습니다.
순서는 이렇습니다.
(1) 행과 값에 원하는 변수를 필드 이름 창에서 드래그해 넣습니다.
(2) 저는 '주문일'을 행 상자에 넣었는데 날짜 변수의 경우 연, 분기, 주문일이 자동으로 생성됩니다. 그 중에서 분기에 따른 구분은 필요없어서 제거한 상태입니다.
(3) 값 상자에는 이익과 매출의 평균 값을 넣었습니다. 기본적으로 합계 값이 들어가게 되는데 아래 오른쪽 이미지와 같이 값 목록에서 마우스 우클릭을 통해 필드 값을 설정할 수 있습니다.
*이때 평균으로 지정한 이유는 월별로 일수가 다르기 때문에 합계로는 월별 추이를 제대로 파악했다고 보기 어렵기 때문입니다.
다음으로는 '2. 각 변수별 이익 비율' 중 '고객분류별 이익 비율'을 위한 테이블을 만들어 볼게요.
(1) 앞에서 만든 테이블 전체를 선택합니다.
- 테이블의 아무 곳에나 마우스를 놓고 [control(command) + 'A']를 누르거나 테이블의 첫 셀인 A3을 클릭하여 테이블 전체를 선택합니다.
(2) 복사하여[control(command) + 'C'] 옆 공간에 붙여넣기[control(command) + 'V'] 합니다.
(3) 행 상자에 '고객 분류', 값 상자에 '이익'을 넣습니다.
*전체 이익에서 각 고객 분류가 차지하는 비율을 알기 위해 이번에는 합계값으로 지정했습니다.
이런 식으로 원하는 값을 각 영역 상자에 넣어서 최종적으로는 아래와 같이 총 여섯 개의 피벗테이블을 완성했습니다.
'4. 이익 TOP 5 제품명'은 테이블 형태로 표시할 거기 때문에 대시보드에서 즉시 피벗테이블 형태로 만들어 보려고 합니다.
3단계. 차트 만들기
두 가지 차트를 만드는 과정을 보면서 전반적인 맥락을 이해해 봅시다. 이 내용을 바탕으로 크게 어렵지 않게 다른 유형의 차트도 구성하실 수 있을 거에요.
그리고 이어서 피벗테이블에서 상위 항목만 뽑아서 보는 방법을 살펴 보겠습니다.
• 이익과 매출액의 추이 (선형차트)
피벗테이블의 아무 셀이나 클릭해 둔 상태로 상단 [삽입] 탭 가운데에 있는 차트를 선택합니다. 저는 1번 차트의 경우 선형차트로 하려고 했으니 꺾은 선형 차트를 선택해 보겠습니다.
그 결과 아래와 같은 차트가 구성됩니다.
차트 디자인을 좀 수정해 볼게요. 차트 이름을 넣어주고 범례의 위치도 변경하려고 합니다.참고로 팁을 드리자면, 각 구성 요소를 수정하고 싶으면 해당 요소를 더블클릭하면 '서식창'을 볼 수 있습니다.
하지만 현재 차트 제목이 없는 상태이기 때문에 제목은 다른 방식으로 넣어 줄게요.
(1) 차트를 클릭하면 몇 개의 상단 메뉴가 활성화 됩니다. 그 중 [디자인] 메뉴로 이동합니다.
(2) 오른쪽에 '차트 요소 추가' 메뉴가 있습니다. 해당 메뉴에서 '차트 제목 - 차트 위'를 선택합니다.
차트 제목을 원하는대로 작성한 후에 범례를 더블클릭해 범례 서식창을 활성화시켰습니다.
위치를 아래쪽으로 지정할게요 :)
데이터 레이블도 추가해 볼까요?
아래와 같이 라인을 클릭해 레이블을 추가한 후, 마찬가지로 더블클릭해 서식창을 엽니다.
위치나 표시형식 등 다양한 서식을 지정할 수 있어요.
최종적으로 아래와 같은 차트가 완성됐습니다.
완성된 차트를 잘라내어[control(command) + 'X'] 대시보드 탭에 붙여넣기 합니다.
• 각 변수별 이익 비율 (파이차트) - 고객분류, 제품대분류, 배송방식별
고객분류, 제품대분류, 배송방식, 지역별로 총 네 개의 파이차트를 만들기로 했으나 지역별 평균 이익이 음수인 곳이 있어 지역별 이익은 막대차트로 구성하겠습니다. 그 외의 항목들은 다 동일한 형식이기 때문에 대표로 고객분류별 이익 비율 차트를 만드는 과정을 살펴 봅시다.
마찬가지로 해당하는 피벗테이블을 클릭한 후 상단 [삽입] 탭에서 차트 유형을 선택합니다.
아래와 같은 파이 차트가 생성되었습니다.
디자인을 수정해서 더 알아보기 좋게 만들어 볼게요.
[디자인] 탭 오른쪽에 있는 빠른 모양의 '레이아웃 1'을 선택하겠습니다.
아래와 같이 좀 더 깔끔하게 디자인이 수정되었습니다.
파이의 색이 짙어서인지 레이블의 글자가 잘 안 보이는 것 같아 글자 색도 흰색으로 변경해 볼까요? 레이블을 클릭해 [홈] 탭에서 글자 색을 지정할 수 있습니다.
완성된 차트는 마찬가지로 잘라내어[control(command) + 'X'] 대시보드 탭에 붙여넣기 합니다.
• 이익 TOP10 제품명
이번에는 차트가 아니라 피벗테이블 그대로 활용하되, 이익의 상위 10개만 표시되도록 해 보겠습니다.
소스데이터를 다시 전체 선택[control(command) + 'A']해서 피벗 테이블을 만드는데 피벗 테이블을 배치할 위치를 'dashboard'탭의 원하는 셀으로 지정합니다.
피벗테이블의 행을 '이름'으로, 값을 '이익' 합계로 지정하면 아래와 같이 구성됩니다.
테이블의 '이름' 오른쪽에 아래로 향하는 삼각형(▼)이 보이시나요? TOP10개만 보이도록 필터링을 하기 위해서는 해당 기능이 필요합니다. 삼각형이 보이지 않는다면 해당 영역을 클릭한 후 상단 [피벗 테이블 분석]의 '필트 머리글'을 선택해 주세요.
삼각형을 클릭하면 아래와 같은 팝업 창이 뜹니다. 정렬 기준을 '합계: 이익'으로 지정한 후, 값 기준을 '상위 10개'로 하고 바로 아래 생겨나는 칸에 표시하고 싶은 행의 수를 넣습니다. 저는 TOP10을 표시할 거라서 10을 넣었습니다.
이렇게 설정을 완료하면 만들려고 했던 테이블이 완성됩니다!
(*정렬을 선택하지 않아 TOP 10에 속하는 목록이 뒤죽박죽 있네요.. 직접 하실 경우에는 정렬을 선택해서 더 깔끔한 대시보드를 만드실 수 있습니다.)
4단계. 대시보드 구성하기
차트를 만들어 대시보드 탭에 붙여 넣은 결과는 아래와 같습니다.
아직은 엑셀 느낌이 강해서 대시보드 느낌이 덜하죠? 몇 가지 설정을 해 봅시다.
먼저 저는 배경 색을 지정해 주고 싶어서 [홈] 탭에서 색깔을 설정했습니다. 아래 그림에 빨간 사각형으로 표시된 부분을 클릭해 전체를 선택한 후 색깔을 변경해 주세요.
피벗테이블도 같이 색깔이 변경됐으니, 그 부분만 드래그 해서 흰색으로 다시 바꾸어 줍니다.
다음으로는 엑셀에 표시된 눈금선, 머리글을 제거해 줍니다.
디자인적인 요소를 더 신경쓸 수도 있겠지만 가장 기초적인 내용만 다루어 봤습니다.
본 포스팅에서는 빠졌지만 슬라이서 기능을 이용해 전체 내용에 한 번에 동일한 필터를 적용할 수도 있으니, 참고해 주세요 :-)
5단계. 대시보드의 데이터를 더 자세히 분석해 보기
대시보드를 보다가 생긴 궁금증(ex. n월에 유난히 매출이 떨어진 이유가 뭐지?)에 대해서 분석하여 답을 찾고 싶으신가요? 다시 새롭게 다방면에서 데이터를 시각화하고 분석하면 시간도 오래 걸릴 것 같죠.. :(
이때 셀프 서비스 애널리틱스 툴 HEARTCOUNT를 활용할 수 있습니다.
먼저 아래는 HEARTCOUNT ABI를 통해서 만든 동일한 대시보드입니다.
첫 번째 차트에서 이익과 매출이 비슷한 추이를 보이는 경우도 있지만 완전히 반대인 경우(2020년 1월)도 있습니다.
그 차이에 가장 큰 영향을 준 변수가 무엇인지 찾아보겠습니다.
해당하는 그래프 우측 상단에 있는 HEARTCOUNT 로고를 클릭하여 분석 기능으로 이동합니다.
이익과 매출 라인 중 이익 라인이 그대로 시각화 되어 표시됩니다.
그 중에서도 1월에 판매된 데이터가 궁금하기 때문에 1월 데이터만 드래그해서 시각화되도록 해 보겠습니다.
이제 X축에 매출을 넣은 후, 매출이 양수(+)임에도 이익이 음수(-)인 포인트를 집중해서 살펴 보겠습니다.
아래 이미지의 드래그한 부분이 속합니다.
각 변수에 따라 포인트의 색상을 구분하여 비교해 봅시다.
(1) 제품대분류에 따라 구분
오른쪽에 보시면 색상의 범례를 확인하실 수 있습니다.
하늘색으로 표시된 '가구'가 유난히 앞서 살펴 보기로 한 구간에 속하는 포인트가 많습니다. 그 다음으로는 전자제품, 사무용품 순으로 포인트의 개수가 많아 보입니다.
(2) 지역에 따라 구분
지역별로 보니 분홍색 포인트(특별광역시)가 가장 많은 수를 보였습니다. 그 다음으로는 비슷하게 수도권과 경상의 포인트가 많아 보입니다. 그런데 수도권의 경우에는 양의 상관관계를 보이는 포인트도 많이 포함되어 있습니다.
(3) 할인율에 따라 구분
색상이 짙은 초록색일수록 낮은 할인율, 짙은 붉은색일수록 높은 할인율을 나타냅니다. 위의 두 변수보다 더 명확한 차이가 드러나는 것 같습니다. 할인율이 높아질수록 이익과 매출이 강한 음의 상관관계를 보이네요.
이런 식으로 시각적인 분석과 함께 각 범례를 선택해 범례별 상관계수를 확인하며 답을 찾을 수 있습니다.
마지막으로 지금까지 수동 분석을 통해 답을 찾는 과정이었다면 자동 분석을 통해 한 번에 답을 찾는 방법을 보여드리며 글을 마무리하겠습니다.
가장 먼저 비활성화 되어있는 자동 분석 메뉴를 활성화시키기 위해서 오른쪽 위에 있는 [캠페인 저장] 버튼을 눌러 캠페인을 저장해 줍니다.
그 후 활성화된 우선 왼쪽 메뉴 중 KPIs 관리 메뉴에 들어가 이익을 KPI로 지정합니다.
KPI 설정이 끝난 다음에는 요인분석 기능으로 이동합니다.
요인분석은 KPI의 변화를 가장 잘 설명하는 변수를 설명력이 강한 순서대로 제시해 주는 기능입니다.
이때 데이터셋에 포함된 모든 변수와의 상관관계가 자동으로 다 분석되기 때문에 주관이 개입되거나 특정 변수가 누락될 걱정이 없습니다.
요인분석 화면을 보니 할인율과 매출 변수가 요인의 차이를 가장 잘 나타내는 변수로 제시되었습니다.
아래 시각화 화면도 함께 제시되어 구체적인 수치를 확인할 수도 있습니다.
이번 시리즈의 마지막 포스팅이 마무리 되었습니다.
엑셀을 활용해서 대시보드를 만드는 과정과 함께 대시보드에서 생겨난 질문에 대해서 HEARTCOUNT를 통해 답을 찾는 과정까지 살펴 봤는데 다양한 내용을 담다 보니 글이 길어 졌네요.. :)
본문에서는 HEARTCOUNT ABI로 만든 대시보드에서 바로 분석 기능으로 넘어가는 시나리오를 보여 드렸습니다.
그러나 가지고 계시던 엑셀 파일을 업로드하는 식으로도, 이미 사용 중이신 대시보드에서 데이터를 뽑아서 업로드하는 방식으로도 이렇게 답을 빠르고 쉽게 찾을 수 있습니다.
HEARTCOUNT를 무료로 시작하고 싶으시다면, 저희 공식 홈페이지에서 [무료로 시작하기]를 클릭해 보세요.
긴 글을 읽어 주셔서 감사드립니다!
대시보드와 분석 기능의 결합 솔루션, HEARTCOUNT ABI 알아보러 가기 →
최재완 강사님이 기초 사용법, 엑셀을 통한 데이터 전처리, ChatGPT 활용법까지 차근 차근 더 자세히 알려 드리는 인프런 강의를 수강해 보세요.
> 인프런에서 더 자세히 보기
> 하트카운트 구독하고 평생 무료로 듣기
'엑셀' 관련 다른 콘텐츠
emma
heartcount
