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

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

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

작성자 :
오빠두엑셀
최종 수정일 : 2022. 08. 23. 23:58
URL 복사
메모 남기기 : (85)

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

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

큰 화면으로 보기

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [대시보드강의] 대시보드 만들기 라이브
    예제파일
  • [관련자료] 엑셀 매출현황 대시보드
    무료서식

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

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

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

이번 강의에서는 온라인 쇼핑몰의 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. 슬라이서 스타일에 사용자 지정 스타일이 추가되었습니다. 해당 스타일을 선택하면 슬라이서 디자인이 변경됩니다.

    엑셀 대시보드 슬라이서 완성
    새롭게 추가된 사용자지정 슬라이서 스타일을 선택하면 슬라이서 디자인이 변경됩니다.
4.9 69 투표
게시글평점
85 댓글
Inline Feedbacks
모든 댓글 보기
85
0
여러분의 생각을 댓글로 남겨주세요.x