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

1. 계절성 분석 (월별 매출 흐름)

이번 강의에서는 매출현황 원본 데이터를 바탕으로 계절성·요일별·트렌드 등 매출 분석의 핵심 지표를 실시간으로 살펴보는 대시보드를 단계별로 만들어 봅니다.

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

   

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

월별 데이터 비교시 주의사항

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

이러한 오차를 줄이려면 월별 데이터를 비교할 때 계산 방식을 '합계'에서 '평균'으로 변경해 주면 됩니다.

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

2. 요일별 매출 분석

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

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

사용자 목록 추가하기

엑셀을 기본 한국어 설정 그대로 사용한다면 요일의 기본 정렬 순서는 일요일부터 시작되어 '일-월-화-수-목-금-토' 순으로 정렬됩니다. 정렬 순서를 월요일부터 시작하는 '월-화-수-목-금-토-일'로 바꾸려면 사용자 지정 목록에 원하는 정렬 순서를 추가하면 됩니다.

1. 사용자 지정 목록 추가하기 : 엑셀 좌측 상단의 [파일] - [옵션] - [고급]으로 이동한 뒤, 화면을 아래로 내리면 [사용자 지정 목록 편집] 버튼이 보입니다. 해당 버튼을 클릭합니다.
2. 목록 항목으로 '월,화,수,목,금,토,일'을 아래 그림과 같이 입력한 뒤 [추가] 버튼을 누르면 사용자 지정 목록에 등록됩니다. [확인] 버튼을 눌러 마무리합니다.
3. 사용자 지정 목록으로 정렬하기 : 요일별로 데이터가 정리된 피벗테이블에서 요일을 오름차순으로 정렬하면 '월-화-수-목-금-토-일' 순서로 정렬되는 것을 확인할 수 있습니다.

3. 평일 vs 휴일 매출 분석하기

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

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

4. 판매 트렌드 분석하기

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

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

   

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

5. 슬라이서로 대시보드 완성하기

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

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