정말 쉽고 효율적인 엑셀 매출분석 대시보드 만들기

엑셀 매출분석 대시보드 목차 바로가기

예제파일 다운로드
계절성 요인 분석하기
월별 데이터를 피벗테이블로 분석 할 경우 주의사항
요일별 매출 분석하기
사용자 지정 목록으로 정렬순서 변경
평일/휴일 매출 분석하기
판매 트렌드 분석하기
슬라이서 추가 및 보고서 연결하기

영상강의

동영상 재생이 안 되시나요? - 유튜브에서 시청 하기

엑셀 대시보드 강의시작
00:00
계절성 요인 분석하기
00:32
월별 데이터 분석 시 주의사항
02:35
계절성 요인 분석 차트 만들기
03:42
요일별 매출 분석하기
06:12
사용자 지정 목록으로 정렬순서 바꾸기
07:02
요일별 매출 분석차트 만들기
08:09
평일/휴일 매출분석
09:53
판매 트렌드 분석하기
12:17
부드러운 꺾은선 그래프 만들기
13:37
슬라이서 추가하기
15:51
슬라이서 보고서 연결시 주의사항
17:25
슬라이서 꾸미기
19:02
여러 도형을 쉽게 편집하는 방법
21:41

큰 화면으로 보기

예제파일 다운로드

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

[대시보드강의] 쉽고 효율적인 엑셀 매출분석 대시보드 만들기
예제파일
다운로드
✨ 엑셀 매출분석 대시보드 (요일별, 시계열, 항목별) - 무료 서식
회원자료
바로가기

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

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

계절성 요인 분석하기

이번 강의에서는 주어진 매출현황 원본 데이터를 바탕으로 매출분석 핵심지표인 "계절성", "요일별", "트렌드" 분석을 실시간으로 하는 대시보드를 만들어 보겠습니다.

계절성 분석 피벗테이블 만들기 : 예제파일의 [정규화 데이터] 시트로 이동합니다. 이후 원본 데이터인 표 범위를 모두 선택한 뒤, [삽입] - [피벗 테이블] 버튼을 클릭합니다.
표를 선택한 후, 삽입 - 피벗테이블을 클릭합니다.
[피벗테이블 만들기] 대화상자가 나오면 피벗테이블을 넣을 위치로 '기존 워크시트'를 선택 후 예제파일의 H6셀에 피벗테이블을 추가합니다.
기존 워크시트 H5 셀에 피벗테이블을 추가합니다.
피벗테이블을 선택하면 [피벗테이블 필드]가 나옵니다. 피벗테이블 필드가 나오지 않을 경우, 피벗테이블을 우클릭 - '필드목록 표시'를 클릭합니다. 이후 아래 그림과 같이 피벗테이블 필드를 추가합니다.
- 행 : 날짜
- 열 : 제품구분
- 값 : 값

피벗테이블의 필드를 위와 같이 설정합니다.
날짜를 월별로 묶기 : 피벗테이블의 날짜를 우클릭한 뒤 [그룹]으로 이동합니다. 그룹화 대화상자가 나오면 월과 연을 선택 후 [확인] 버튼을 클릭합니다. 이후 피벗테이블의 행 필드에는 '날짜'만 남기고 연은 필드목록에서 제거합니다.
날짜를 우클릭 - 그룹에서 연과 월을 선택합니다.
계절성 분석 차트 만들기 : 피벗테이블을 선택한 뒤 [삽입] - [추천차트]를 클릭합니다. 이후 [꺾은선형] 차트에서 꺾은선 차트를 생성합니다.
피벗테이블을 선택 - 삽입 - 추천차트에서 꺾은선 차트를 추가합니다.
차트의 버튼과 눈금선을 제거합니다. 이후 범례 서식에서 위치를 위쪽으로 변경 후, 오른쪽 구석으로 이동합니다. 이후 꺾은선 차트의 윤곽선을 원하는 색상으로 변경하면 계절성 분석 차트가 완성됩니다.
꺾은선 차트가 완성되었습니다.
차트를 대시보드 위로 옮기기 : 차트를 선택한 후 Ctrl + X 로 잘라내기 합니다. 이후 [매출현황분석] 시트에 붙여넣기 한 후, [제품구분별] 영역으로 이동합니다. 차트 서식에서 윤곽선을 없음으로 변경하면 대시보드 위에 차트가 알맞게 추가됩니다.
차트를 잘라내기 한 뒤, 대시보드 위로 붙여넣기 합니다.

월별 데이터를 피벗테이블로 분석 할 경우 주의사항

피벗테이블로 월별 데이터를 분석할 경우 한가지 주의사항이 있습니다. 바로 피벗테이블은 기본으로 값을 '합계'로 집계하기 때문에 한 달이 31일인 경우와 30일인 경우 합계 기준이 달라져 월별 데이터 비교 시 약간의 오차가 발생할 수 있다는 것인데요.

이러한 오차를 해결하려면 월별 데이터를 비교 할 시, 계산방식을 '합계' 에서 '평균'으로 변경할 수 있습니다.

피벗테이블 값 요약기준 변경하기 : 피벗테이블의 값을 우클릭한 뒤, '값 요약 기준' 에서 계산방식을 '평균'으로 변경합니다.
피벗테이블에서 값을 우클릭한 뒤, 값 요약기준을 평균으로 변경합니다.
값이 평균으로 계산되면서 값에 소수점이 추가되었습니다. 값을 좀 더 보기 좋게 표시하기 위해 표시형식도 변경하겠습니다. 값을 우클릭한 후, [필드 표시 형식]으로 이동합니다.
값을 우클릭 - 필드표시 형식으로 이동합니다.
셀 서식 대화상자가 나오면 '사용자 지정'으로 이동한 뒤, 셀 서식으로 #,##0 을 입력 후 [확인] 버튼을 클릭합니다. 표시형식이 변경되며 피벗테이블이 깔끔하게 정리되었습니다. 셀 서식에 대한 보다 자세한 설명은 아래 셀서식의 모든 것 기초 입문강의를 참고하세요.
엑셀 셀서식/사용자 지정서식의 모든 것 - 영상강의

사용자 지정 으로 이동한 뒤, 표시형식을 [#,##0] 으로 입력합니다.

요일별 매출 분석하기

이번에는 요일별 매출을 분석하는 피벗테이블과 차트를 추가하겠습니다.

요일별 매출 분석 피벗테이블 만들기 : 기존에 만든 피벗테이블을 복사한 뒤, 예제파일 P5셀에 붙여넣기 합니다.
기존 피벗테이블을 복사한 뒤, P5셀에 붙여넣기 합니다.
피벗테이블을 우클릭한 뒤, [필드 목록 표시]로 이동합니다. 이후 필드목록을 아래와 같이 변경합니다.
- 행 : 요일
- 값 : 값
요일별 매출 분석 차트 만들기 : 피벗테이블을 선택 후, [삽입] - [추천 차트]로 이동합니다. 이후 [세로 막대형] 차트에서 세로막대형 차트를 추가합니다.
피벗테이블을 선택 - 차트 - 추천차트에서 세로막대형 차트를 삽입합니다.
차트의 제목, 버튼, 눈금선, 세로축, 범례를 제거하여 차트를 간소화합니다. 이후 막대를 우클릭 - 데이터 레이블 추가를 클릭하여 데이터 레이블을 추가합니다.
막대를 우클릭한 뒤, 데이터 레이블을 추가합니다.
막대를 우클릭 한 후, [데이터 계열 서식]으로 이동합니다. 이후 간격 너비를 100% 로 변경하면 차트를 더욱 보기 좋게 시각화할 수 있습니다.
막대를 우클릭 - 데이터레이블 서식에서 간격너비를 100%로 변경합니다.
차트를 대시보드 위로 옮기기 : 차트를 잘라내기 한 후, 매출현황 분석 시트의 요일별 매출 영역에 붙여넣기 합니다. 이후 차트 서식에서 윤곽선을 없음으로 변경하면 차트가 대시보드 위에 알맞게 추가됩니다.
차트를 대시보드 위에 붙여넣기 합니다.

사용자 지정 목록으로 정렬순서 변경

엑셀을 기본 한국어 설정 그대로 사용 중이라면 요일의 기본 정렬 순서는 일요일부터 시작되어 '일-월-화-수-목-금-토'로 정렬됩니다. 만약 요일의 정렬 순서를 월요일부터 시작되는 '월-화-수-목-금-토-일'로 변경하려면 사용자 지정 목록에 원하는 정렬 순서를 추가하여 해결할 수 있습니다.

사용자 지정 목록 추가하기 : 엑셀의 좌측 상단 [파일] - [옵션] - [고급]으로 이동 후, 아래로 쭉 내려오면 [사용자 지정 목록 편집] 버튼이 있습니다. 버튼을 클릭합니다.
파일 - 고급 에서 사용자 지정 목록 편집으로 이동합니다.
목록 항목으로 월,화,수,목,금,토,일을 아래와 같이 입력 후 [추가] 버튼을 누르면 사용자 지정 목록이 추가됩니다. [확인] 버튼을 눌러 마무리합니다.
목록 항목으로 월,화,수,목,금,토,일을 추가합니다.
사용자 지정 목록으로 정렬하기 : 요일별로 데이터가 분석된 피벗테이블에서 요일을 오름차순으로 정렬하면 요일이 '월-화-수-목-금-토-일' 순서로 정렬됩니다.
피벗테이블을 정렬하면 요일순으로 정렬됩니다.

평일/휴일 매출 분석하기

이번에는 평일과 휴일 매출을 분석하는 피벗테이블과 차트를 추가하겠습니다.

평일/휴일 분석 피벗테이블 만들기 : 기존 피벗테이블을 복사한 뒤, 예제파일 W5셀에 붙여넣기 합니다.
이후 피벗테이블의 필드목록 표시에서 피벗테이블 필드를 아래와 같이 변경합니다.
- 행 : 평일/휴일
- 값 : 값
평일/휴일 분석 차트 만들기 : 피벗테이블을 선택 후, [삽입] - [추천차트]로 이동합니다. 이후 [세로막대형]차트를 선택하여 차트를 추가합니다.
피벗테이블을 선택 - 삽입 - 추천차트에서 세로막대차트를 추가합니다.
차트의 제목, 눈금선, 세로축, 버튼을 제거하여 간소화 한 후, 데이터레이블을 추가합니다. 이후 막대 색상을 원하는 색으로 변경하여 차트를 간소화 및 시각화합니다.
차트를 간소화하고 시각화합니다.
차트를 대시보드 위로 옮기기 : 차트를 잘라내기 한 뒤 매출현황 분석시트의 평일/휴일 영역 위로 붙여넣기 합니다. 이후 차트 서식에서 윤곽선을 없음으로 변경하면 차트가 대시보드 위에 알맞게 삽입됩니다.
차트를 대시보드 위에 붙여넣기 합니다.

판매 트렌드 분석하기

마지막으로 판매 트렌드를 분석하는 피벗테이블과 차트를 추가하겠습니다.

트렌드 분석 피벗테이블 만들기 : 기존 피벗테이블을 복사한 후, 예제파일 AD5셀에 붙여넣기 합니다.
이후 피벗테이블을 우클릭 - 필드 목록 표시로 이동한 뒤, 필드목록을 아래와 같이 변경합니다. 만약 필드목록에 '연'이 없을 경우, 날짜 필드를 우클릭 후 날짜가 '연'과 '월'로 그룹화 되었는지 확인합니다.
- 행 : 연
- 값 : 값

피벗테이블의 필드를 위와 같이 변경합니다.
트렌드 분석 차트 만들기 : 피벗테이블을 선택한 후, [삽입] - [추천차트]로 이동합니다. 이후 꺾은선형 차트를 추가합니다.
피벗테이블 선택 - 삽입 - 추천차트에서 꺾은선 차트를 삽입합니다.
차트 제목과 범례, 눈금선과 세로축을 제거하여 간소화 한 뒤, 데이터 레이블을 추가하여 차트를 꾸며줍니다. 차트의 [+] 버튼을 클릭한 뒤, 추세선을 추가합니다.
차트의 + 를 클릭 후, 추세선을 추가합니다.
차트의 윤곽선과 추세선의 색상을 변경합니다. 추세선을 우클릭하여 [추세선 서식]으로 이동한 후, 수식을 차트위에 표시하여 차트를 완성합니다.
꺾은선과 추세선의 색상을 변경합니다.
차트를 대시보드 위로 옮기기 : 차트를 잘라낸 뒤, 매출현황 분석 시트의 판매트렌드 영역위에 붙여넣기 합니다. 이후 차트 서식에서 윤곽선을 없음으로 변경하면 차트가 대시보드 위에 알맞게 추가됩니다.
차트를 대시보드 위에 붙여넣기 하여 마무리합니다.

슬라이서 추가 및 보고서 연결하기

이제 마지막으로 슬라이서를 추가한 뒤, 슬라이서를 여러 피벗테이블과 연동하여 실시간으로 데이터를 분석할 수 있는 대시보드를 만들겠습니다.

슬라이서 추가하기 : 피벗테이블을 선택 후, [삽입] - [슬라이서] 버튼을 클릭하면 슬라이서 삽입 대화상자가 나옵니다. 목록에서 요일, 제품구분, 연도 슬라이서를 추가합니다.
피벗테이블 - 삽입 - 슬라이서를 클릭합니다.
슬라이서를 여러 피벗테이블과 연동 : 슬라이서를 우클릭한 후, [보고서 연결]로 이동합니다. 이후 목록에 표시된 모든 피벗테이블의 체크박스를 선택하여 슬라이서와 보고서를 연동합니다. 나머지 다른 슬라이서도 동일하게 보고서를 연결합니다.
슬라이서를 우클릭 - 보고서 연결에서 모든 피벗테이블을 선택합니다.
슬라이서를 잘라내기 한 후, 매출현황분석 시트 위로 붙여넣기 합니다. 이후 슬라이서를 보기 좋게 꾸며주면 매출 현황을 분석하는 대시보드가 완성됩니다.

4.9 33 투표

게시글평점

정말 쉽고 효율적인 엑셀 매출분석 대시보드 만들기 | 강력 추천

피벗테이블과 슬라이서만 사용하여 매출 분석에 필요한 주요 지표를 동시에 분석하는 대시보드를 만드는 방법을 단계별로 살펴봅니다.