엑셀 대시보드 만들기 | 초보자도 할 수 있는 초간단 대시보드

함수를 사용하지 않고 피벗테이블과 슬라이서만 사용하여 제작하는 고급스러운 디자인의 엑셀 대시보드 제작 방법을 알아봅니다.

홈페이지 » 엑셀 대시보드 만들기 | 초보자도 할 수 있는 초간단 대시보드

엑셀 대시보드 만들기 | 피벗테이블만 사용해서 만드는 초간단 대시보드

엑셀 대시보드 만들기 라이브 목차 바로가기
영상강의

예제파일 다운로드

본 강의에서 제작한 쇼핑몰 매출현황 1페이지 대시보드 완성파일은 아래 링크에서 다운로드 가능합니다.

강의를 시작하기 전에...

본 게시물에서는 대시보드 제작에 필요한 핵심 요소만 간추려서 그림과 함께 간략한 설명을 적어드렸습니다. 대시보드 만들기에 대한 자세한 설명은 아래 적어드린 영상강의의 시간대별 목차를 참고해주세요.

00:34 대시보드 상황 설명
02:06 이전 가계부 대시보드 대비 차이점
03:04 대시보드 레이아웃 꾸미기
09:06 레이아웃 만들기의 중요성
11:14 아이콘 삽입 방법
12:22 월별 매출현황 데이터
14:45 판매 채널별 매출현황 데이터
15:28 중요 분석자료 데이터
20:22 차트를 잘 만드는 방법
21:23 월별 매출현황 차트 만들기
22:58 차트의 보조축을 숨기는 방법
23:49 대시보드 위에 차트 추가하기
29:40 채널별 매출현황 차트 만들기
36:54 중요 분석지표 차트 만들기
45:43 실시간으로 변하는 텍스트상자 만들기
49:39 채널별 유입경로 데이터
51:44 TOP 5 구매지역 데이터
53:50 TOP 20 판매제품 데이터
54:50 채널별 유입경로 차트 만들기
56:31 TOP 5 구매지역 차트 만들기
59:12 TOP 20 판매제품 차트 만들기
61:17 조건별 필터 (슬라이서) 추가하기
65:59 슬라이서 보고서 연결 시 주의사항
69:22 슬라이서 환경 설정하기
73:30 슬라이서 스타일 꾸미기
75:24 대시보드 배포 전 마지막 설정사항

이번 강의에서 다룬 대시보드에 더해, 이전 가계부 만들기 대시보드에서 다룬 버튼을 클릭하면 해당 항목이 강조되는 차트 버튼을 클릭하여 시트를 이동하는 기능 등을 추가 할 수 있습니다. 더 자세한 내용은 아래 엑셀 대시보드 만들기 무료 강의 전체목록을 참고하세요.

엑셀 대시보드 만들기, 첫단계 : 데이터 이해하기

이번 강의에서는 온라인 쇼핑몰의 PG사 매출 데이터를 예제로 대시보드를 제작할 예정입니다. 원본데이터는 예제파일의 '매출' 시트에서 확인할 수 있으며, 데이터에 사용된 각 항목은 아래와 같습니다.

엑셀 대시보드 데이터
이번 강의에서 제작할 대시보드의 원본 데이터입니다.
항목설명
날짜매출이 일어난 날짜입니다.
지역제품이 배송 된 지역입니다.
구분, 대분류, 중분류, 소분류제품을 각 구분별로 나눈 범주입니다.
수량, 단가최종 구매가 확정되기 전 전 고객이 선택한 제품의 수량과 단가입니다.
확정수량, 확정단가, 확정매출최종 구매후 (할인 이후 등..) 확정 된 제품의 수량과 단가 그리고 최종 매출액 입니다.
판매채널, 유입경로, 재구매, 주문최소, 만족도매출을 분석하기 위한 분석 지표입니다.

엑셀 대시보드 레이아웃 만들기

이번 강의에서 만들 대시보드는 총 6개 그룹으로 나눠 작성합니다.
엑셀 대시보드 레이아웃

레이아웃 구분설명
월별 매출현황제품의 월별 판매수량 및 총 매출을 시간의 흐름에 따라 출력합니다.
주요 분석지표매출 분석에 사용되는 주요 지표 3가지, 재방문율, 주문취소율, 고객만족도를 표시합니다.
채널 유입경로각 판매채널 별 고객의 유입경로를 6개로 나눠 (검색유입, 보조광고, 메인광고, 다이렉트, 홈페이지, SNS) 표시합니다.
채널별 매출 현황각 판매채널별 매출 현황을 표시합니다.
상위 5개 구매지역선택한 제품을 구매한 상위 5개 구매지역을 표시합니다.
상위 20개 판매제품선택한 제품군의 상위 20개 판메제품을 표시합니다.

차트 제작에 사용 될 피벗테이블 만들기

매출 시트의 표 범위를 전체 선택 한 후, '삽입' - '피벗테이블'을 클릭하여 차트자료 시트 'A2'셀에 피벗테이블을 생성합니다. (또는 단축키 Alt + N + V)

엑셀 피벗테이블 추가
표 범위를 전체 선택한 뒤, 피벗테이블을 추가합니다.

이후 총 8개의 피벗테이블을 추가하는데요. 첫번째 피벗테이블을 생성한 후, 첫번째 피벗테이블을 하나씩 복사/붙여넣기 하여 피벗테이블 만들기 작업을 반복하면 편리합니다. (보다 쉬운 작업을 위해 예제파일에는 각 피벗테이블 영역을 미리 나눠드렸습니다.)

월별 매출현황
엑셀 대시보드 월별 매출현황
월별 매출현황을 나타내는 피벗테이블을 생성합니다.
피벗테이블 영역필드
연, 날짜
' 피벗테이블에 추가된 날짜 필드를 우클릭한 뒤, 그룹으로 이동하여 '연, 월' 만 활성화합니다.
합계 : 확정수량
합계 : 확정매출
' 값 필드설정 - 표시형식에서 사용자 지정서식을 백만단위 표시로 변경합니다. (#,##0,,)
판매채널별 매출현황
엑셀 대시보드 판매채널별 매출
판매채널별 매출을 나타내는 피벗테이블을 생성합니다.
피벗테이블 영역필드
판매채널
합계 : 확정매출
' 값 필드설정 - '값 표시형식' 탭으로 이동한 뒤, 값 표시형식을 총 합계 비율로 변경합니다.
재구매 현황
엑셀 대시보드 재구매 현황
고객의 재구매 현황을 보여주는 피벗테이블을 생성합니다.
피벗테이블 영역필드
재구매
개수 : 수량
' 값 필드설정 - '값 필드 요약기준' 을 '개수'로 선택합니다. 이후 '값 표시형식' 탭으로 이동한 뒤, 값 표시형식을 총 합계 비율로 변경합니다.
주문취소 현황
엑셀 대시보드 주문취소 현황
구매 도중 주문취소가 발생한 비율을 보여주는 피벗테이블을 생성합니다.
피벗테이블 영역필드
주문취소
개수 : 수량
' 값 필드설정 - '값 필드 요약기준' 을 '개수'로 선택합니다. 이후 '값 표시형식' 탭으로 이동한 뒤, 값 표시형식을 총 합계 비율로 변경합니다.
고객만족도 평균
대시보드 고객 만족도
고객 만족도 평균을 나타내는 피벗테이블을 추가합니다.
피벗테이블 영역필드
평균 : 수량
' 값 필드설정 - '값 필드 요약기준' 을 '평균'으로 선택합니다. 이후 '값 표시형식' 탭으로 이동한 뒤, 값 표시형식을 '계산 없음'으로 변경합니다.

고객만족도의 경우 총 5점 만점 중의 평균 만족도를 출력해야 하므로 별도의 표를 추가로 생성합니다. 잔여의 경우 [ =5-평균 ] 으로 계산합니다.

판매 채널별 유입경로
대시보드 유입경로
판매 채널별 유입경로를 나타내는 피벗테이블을 추가합니다.
피벗테이블 영역필드
유입경로
개수 : 수량
' 값 필드설정 - '값 필드 요약기준' 을 '개수'로 선택합니다. 이후 '값 표시형식' 탭으로 이동한 뒤, 값 표시형식을 '계산 없음'으로 변경합니다.
상위 5개 구매지역
엑셀 대시보드 상위 5개 구매지역
상위 5개 구매지역을 나타내는 피벗테이블을 추가합니다.
피벗테이블 영역필드
지역
' 필터 화살표 버튼을 클릭한 뒤, '기타 정렬옵션' - 내림차순기준 - '개수: 확정수량'으로 변경합니다.
' 값 필터 - '상위 10' 으로 이동한 뒤, 값을 5로 변경합니다.
개수 : 확정수량
' 값 필드설정 - '값 필드 요약기준' 을 '개수'로 선택합니다. 이후 '값 표시형식' 탭으로 이동한 뒤, 값 표시형식을 '계산 없음'으로 변경합니다.
상위 20개 판매제품
엑셀 대시보드 상위 20개 판매제품
상위 20개 판매제품을 출력하는 피벗테이블을 생성합니다.
피벗테이블 영역필드
소분류
' 필터 화살표 버튼을 클릭한 뒤, '기타 정렬옵션' - 내림차순기준 - '합계: 확정매출'로 변경합니다.
' 값 필터 - '상위 10' 으로 이동한 뒤, 값을 20으로 변경합니다.
합계 : 확정매출
' 값 필드설정 - '값 필드 요약기준' 을 '합계'로 선택합니다. 이후 '값 표시형식' 탭으로 이동한 뒤, 값 표시형식을 '계산 없음'으로 변경합니다.
' 값 필드설정 - '표시형식'에서 사용자 지정서식을 천단위 표시로 변경합니다. [ #,##0, ]

대시보드 각 영역에 차트 추가하기

이전 단계에서 만들어준 피벗테이블을 하나씩 선택하여 차트를 각각 생성합니다. 차트 별 레이아웃이나 디자인은 상황에 따라 적절히 수정할 수 있으나, 모든 차트에 공통으로 적용되는 규칙은 아래와 같습니다.

  1. 차트를 선택한 뒤, '서식'으로 이동하여 도형 채우기와 윤곽선을 없음으로 설정합니다.

    대시보드 차트 서식 변경
    차트의 채우기 및 윤곽선의 색상을 없으므로 변경합니다.
  2. 차트의 제목과 범례, 눈금선은 제거하여 차트를 간소화 합니다.

    대시보드 차트 간소화
    차트의 제목, 눈금선, 범례를 제거하여 간소화합니다.
  3. 요에 따라 데이터 레이블을 추가합니다.

    차트 데이터레이블 추가
    필요시 차트에 데이터레이블을 추가합니다.
  4. 가로막대형 차트의 경우 기본 값으로 항목이 역순 출력 되므로, '축 서식'에서 '항목을 거꾸로'를 활성화합니다.

    가로막대 그래프 항목을 거꾸로
    가로막대 차트의 경우 축 서식에서 '항목을 거꾸로'를 활성화합니다.

아이콘 및 텍스트 추가하여 대시보드 꾸미기

예제파일의 왼쪽, 오른쪽 상단을 보시면, 이번 대시보드에서 사용 될 아이콘과 텍스트박스를 확인할 수 있습니다. 각 아이콘과 텍스트박스를 차트와 레이아웃 크기에 맞춰 이동한 뒤 대시보드를 꾸며줍니다.

대시보드 아이콘 및 텍스트
예제파일 '대시보드시트' 왼쪽/오른쪽 위에 아이콘과 텍스트상자를 미리 추가해두었습니다.

주요 매출 분석지표에 사용할 텍스트 박스는 차트가 바뀔 때마다 동시에 업데이트 되어야 합니다. 따라서 텍스트 박스의 값을 다른 셀 주소를 참조하여 실시간으로 연동 할 수 있는데요. 방법은 아래와 같습니다.

  1. 텍스트박스를 선택한 뒤, 수식입력줄에 등호(=)를 입력합니다.

    텍스트박스 실시간 업데이트
    텍스트박스를 선택한 뒤, 수식입력줄에 등호(=)를 입력합니다.
  2. 이후 텍스트박스 위에 표시 할 값이 입력될 셀을 참조하면, 텍스트박스의 값이 실시간으로 업데이트 됩니다.

    텍스트박스 값 연동
    텍스트박스에 출력할 값이 입력된 다른 셀을 참조합니다.

슬라이서 생성 후 보고서 연결하기

  1. 차트자료 시트로 이동한 뒤, 피벗테이블을 선택합니다. 이후 피벗테이블 분석탭을 클릭하면 좌측에 '피벗테이블 이름'을 확인할 수 있습니다.

    피벗테이블 이름 확인
    피벗테이블을 선택한 후, 피벗테이블 분석으로 이동합니다.
  2. 각각의 피벗테이블 이름을 알아보기 쉽게 변경합니다. (예: 피벗_월별매출현황 등..)

    피벗테이블 이름 변경
    피벗테이블의 이름을 알아보기 쉽게 변경합니다.
  3. 이후 '삽입' - '슬라이서'로 이동하여 5개의 슬라이서를 추가합니다. (필요시 다른 항목의 슬라이서를 추가해도 무방합니다.)
    날짜, 구분, 대분류, 중분류, 연도

    슬라이서 삽입
    피벗테이블을 선택한 채로, 삽입 - 슬라이서를 클릭하여 슬라이서를 추가합니다.
  4. 슬라이서를 우클릭 한 뒤, 보고서 연결로 이동합니다.

    슬라이서 보고서 연결
    슬라이서를 우클릭한 뒤, 보고서 연결로 이동합니다.
  5. 날짜/연도 슬라이서는 '월별 매출현황 피벗테이블'을 제외한 나머지 모든 피벗테이블을, 기타 슬라이서는 모든 피벗테이블을 연결합니다.

    피벗테이블 보고서 연결 선택
    슬라이서와 연결 될 보고서 목록을 체크하여 활성화합니다.
  6. 슬라이서를 잘라내기 한 뒤, 대시보드 레이아웃 위 적절한 위치에 붙여넣기 합니다.

    대시보드 슬라이서 복사 붙여넣기
    보고서 연결이 끝났으면, 슬라이서를 잘라내기 하여 대시보드 위에 붙여넣기 합니다.

슬라이서 스타일 꾸미기

  1. 키보드 Shift 키를 누른채로 스타일을 변경할 여러개의 슬라이서를 동시에 선택합니다.

    엑셀 대시보드 슬라이서 선택
    디자인을 변경할 슬라이서를 선택합니다.
  2. 상단의 '슬라이서' 탭의 슬라이서 스타일에서 원하는 기본 스타일을 우클릭 한 뒤, '중복'을 선택합니다.

    대시보드 슬라이서 스타일 복사
    슬라이서 탭으로 이동한 뒤, 원하는 슬라이서 스타일을 우클릭하여 '중복'으로 이동합니다.
  3. '전체 슬라이서' - 서식 으로 이동한 뒤, 채우기는 검은색, 테두리는 없음으로 설정합니다.

    전체 슬라이서 서식
    전체슬라이서 - 서식에서 채우기 색상과 테두리를 변경합니다.
  4. '머릿글' - 서식으로 이동한 뒤, 글꼴 색상을 흰색으로 변경합니다.

    머릿글 글꼴 색상 변경
    머릿글의 글꼴 색상을 흰색으로 변경합니다.
  5. 슬라이서 스타일에 사용자 지정 스타일이 추가되었습니다. 해당 스타일을 선택하면 슬라이서 디자인이 변경됩니다.

    엑셀 대시보드 슬라이서 완성
    새롭게 추가된 사용자지정 슬라이서 스타일을 선택하면 슬라이서 디자인이 변경됩니다.
5 21 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
25 Comments
Inline Feedbacks
View all comments
Myung
Myung
2020년 6월 25일 8:03 오전
게시글평점 :
     

우...우왕

감사합니다
감사합니다
2020년 6월 25일 8:15 오전
게시글평점 :
     

잘보고 있습니다. 감사합니다 🙂

손몽군
손몽군
2020년 6월 25일 10:32 오전
게시글평점 :
     

오빠두님의 강의를 열심히 시청하고 있습니다.
이번 강의는 특히, 정말 도움이 되는 유용한 내용입니다. 공유해 주셔서 고맙습니다!
업무에 많은 도움이 되리라 믿어 의심하지 않습니다.
감사합니다!

8동 29호 여자
8동 29호 여자
2020년 6월 25일 4:47 오후
게시글평점 :
     

예제 파일이 폰으로 로긴하면 다운 버튼만 보이고, 링크는 찾을 수 없다고 나오고, 컴으로 보면 예제파일 자체가 보이지 않는 건 저 뿐일까요ㅜ

Jae Young Park
Jae Young Park
2020년 6월 26일 9:35 오전
게시글평점 :
     

기다렸는데... 올라왔군요. 감사합니다.

평생다요트
평생다요트
2020년 6월 26일 6:54 오후
게시글평점 :
     

방송보고 버벅거리다 올리신강의보고 천천히 끝까지 다했어요 ㅜㅜ 감동 감사합니다

나성필
나성필
2020년 6월 27일 11:13 오전
게시글평점 :
     

강의내용처럼 하나의 원시데이터를 여러개의 피벗이 아닌 다른 원시데이터로 만든 다른 피벗인데 공통된 값을 가진 슬라이서를 활용하여 슬라이서 내용 변경시 서로 다른 피벗의 값이 바뀌도록 할 수는 없을까요?ㅠ

Vves
Vves
2020년 6월 27일 10:33 오후
게시글평점 :
     

울고싶을정도로 감사합니다!!

집중고고
집중고고
2020년 6월 29일 5:50 오전
게시글평점 :
     

어제 유튜브 보고와서 가입합니다.

Steve Shin
Steve Shin
2020년 6월 29일 9:32 오후
게시글평점 :
     

정말 최고십니다....감사합니다.

jhj
jhj
2020년 6월 30일 9:31 오전
게시글평점 :
     

유튜브 보고 들어왔는데 시선을 강탈하는 멋진 대시보드입니다.
다음 보고 올릴 때 능숙하게 할 수 있도록 강의 영상 계속 돌려보면서 연습해야겠어요.

kim jong gwan
kim jong gwan
2020년 7월 1일 9:33 오전
게시글평점 :
     

열심히 만들어 볼게요
좋은 정보 감사합니다.^^

이혜
이혜
2020년 7월 8일 7:24 오후
게시글평점 :
     

우와

홍비맘
홍비맘
2020년 7월 21일 1:30 오후
게시글평점 :
     

우연히 유튜브에서 발견하구 바로 회원가입 했네요

이주흠
이주흠
2020년 8월 5일 1:12 오전
게시글평점 :
     

와진짜 미쳤다 감사합니다

유승주
유승주
2020년 8월 5일 8:30 오후
게시글평점 :
     

정말많이공부 감사합니다

Ryan
Ryan
2020년 8월 13일 5:50 오후
게시글평점 :
     

정말 고맙습니다ㅜㅜ

혬짱짱
2020년 8월 17일 10:33 오후
게시글평점 :
     

항상 강의보면서 감탄합니당! 정말 대단하세요! 열심히 배워보겠습니다!

공나단
공나단
2020년 8월 31일 4:14 오후
게시글평점 :
     

혹시 완성본은 공유 안되겠죠...?

Oyoshi
Oyoshi
2020년 9월 23일 4:48 오후
게시글평점 :
     

안녕하세요
혹시 슬라이더에서 주간으로 검색할 수 있는 방법도 있을까요?

쿠니미
쿠니미
2020년 10월 11일 9:53 오후
게시글평점 :
     

정말 최고에요.

25
0
여러분의 생각을 댓글로 남겨주세요.x