[챕터3]보고서 퀄리티 UP! 엑셀 자동화 대시보드 만들기
오빠두 엑셀
https://www.oppadu.com/challenges/excel-basic-1day/
[1교시] 실무에서 필요한 엑셀 데이터 시각화 기법
직장인에게 데이터 시각화란,
"내가 보여주고자 하는 (또는 상대방이 보고 싶어하는) 자료를
알기 쉽게 전달하는 것" 입니다.
데이터 시각화의 핵심은 '전달할 메시지'를 정하는 것 > 무엇을(What), 어떻게(How) 전달할지 고민하는 게 중요!
1. 꺾은선 차트
꺾은선 차트는 '시간의 흐름에 따른 데이터'를 시각화할 때 사용한다.
차트에서 중요한 부분은 색감과 간소화이다.
1)항목 간소화
- 차트 제목: Delete
- 세로 축: Delete > 데이터 레이블 추가(차트 + 버튼 클릭 - 데이터 레이블 체크)
- 눈금선: 옅은 색으로 변경
2)색감
전달할 메시지에 따라 강조할 부분은 눈에 띄게 변경하고 나머지 부분은 연하게 바꿔주면 메시지가 분명하게 전달된다.
영업 2팀의 실적이 가장 좋기 때문에 영업 1팀과 3팀의 꺾은선과 데이터레이블을 연한 색으로 변경하고
영업 2팀의 꺾은선과 데이터레이블을 빨간색으로 변경하여 영업2팀의 실적이 강조되게 한다.
2. 세로막대
기본 세로막대 차트가 가나다 순으로 정렬되어 막대의 높낮이가 들쑥날쑥하다.
세로막대차트는 내림차순으로 정렬되어야 보기 편하므로
원본데이터를 내림차순 정렬해주면 차트 내 막대 순서도 변경된다.
1)항목 간소화
- 차트 제목: Delete
- 세로 축: Delete > 데이터 레이블 추가 (차트 + 버튼 클릭 - 데이터 레이블 체크)
- 눈금선: 옅은 색으로 변경
2)색감
서울특별시의 합격자 수가 가장 많기때문에 나머지 지역의 꺾은선과 데이터레이블을 연한 색으로 변경하고
서울특별시의 꺾은선과 데이터레이블을 진한 남색으로 변경하여 서울특별시의 합격자 수를 강조한다.
[TIP]막대너비
보통 막대 간 간격이 막대 너비보다 넓어서 차트가 불안정하게 보이는데 막대너비를 확대하면 안정적으로 바꿔줄 수 있다.
막대 우클릭-데이터 계열 서식 클릭 후 간격너비를 100%로 변경하면 막대너비가 넓어져서 보기 편해진다.
[TIP] 말풍선 상자 편집
빨간색 화살표를 추가하고 말풍선 상자에 서울지역 학생 50% 이상을 입력하여 서울특별시 합격자 수를 강조해준다.
말풍선 상자는 기본 여백 때문에 편집 후에 글자가 가려지는 현상이 있는데 이때 도형서식에서 기본여백을 0으로 바꾸면
편집할 때처럼 동일하게 모든 글자가 보이게 된다.
3. 대시보드
3-1. 피벗테이블 만들기
1)월별 매출
원본데이터를 Ctrl+T로 표로 만들어준 후 테이블 이름을 '매출데이터'로 변경한다.
그리고 피벗테이블을 삽입한 후 행에 '주문일', 값에 '구매액'을 넣어 월별 매출 피벗테이블을 만든다.
월별 매출이기 때문에 우측 마우스-그룹화를 클릭하여 월, 연을 단위로 선택해 월별 구매액이 보이게 한다.
항목이 한 열에 몰려 있어서 보기 불편하기 때문에 디자인 탭-보고서 레이아웃에서
'테이블 형식으로 표시'를 클릭하여 연도와 월을 구분해준다.
2)지역별 매출
월별 매출 피벗테이블을 복사한 후 행에 지역을 넣어 지역별 매출 테이블을 완성한다.
월별매출은 가나다순이 맞지만, 지역별 매출은 구매액이 높은 순으로 정렬해야 하므로
지역 필터를 클릭하고 '기타정렬옵션'을 클릭한 뒤 내림차순 기준을 선택하고 합계:구매액 기준을 선택한다.
3-2. 슬라이서
피벗테이블에 커서를 둔 상태에서 피벗 테이블 분석 탭-슬라이서 삽입을 선택한 후 원하는 항목을 체크한다.
슬라이서는 커서를 둔 피벗테이블에만 적용되는데 모든 피벗테이블이 변경되게 하려면
보고서 연결을 해야 한다. 슬라이서 우클릭-보고서 연결을 클릭한 후 전체 테이블을 체크하면 연결이 된다.
슬라이스가 여러 개인 경우 각각 보고서 연결을 해줘야 한다.
3-3. 대시보트 차트
월별 매출 피벗테이블의 꺾은선차트를 삽입한다. 피벗테이블에서 차트를 만들면 필드 단추가 생성되는데
이를 숨기려면 우측 마우스-차트에서 모든 필드 단추 숨기기를 클릭하면 된다.
차트 제목, 범례 등을 지워 간소화해준다. 이제 슬라이서에서 필터링할 때마다 차트가 자동으로 변경된다.
3-4. 대시보드 아웃라인 만들기
- 슬라이서 열 설정
슬라이서 항목의 길이가 짧아 열을 늘리고 싶은 경우 슬라이서를 선택하고 슬라이서 탭에서
열 개수를 1개에서 원하는 개수로 입력하면 된다.
- 슬라이서 항목 숨기기
데이터가 없는 항목을 숨기고 싶은 경우 슬라이서 우클릭-슬라이서 설정에서
데이터가 없는 항목 숨기기를 체크하면 된다.
- 세로 막대 차트 축 서식
- 원본데이터와 차트의 항목순서가 항상 반대로 정렬되므로 세로축 서식에서 '항목을 거꾸로'를 선택해
원본데이터와 동일하게 정렬한다.
- 꺾은선 차트 곡선 부드럽게 변경
꺾은선 차트는 기본적으로 뾰족하게 설정되는데, 데이터 계열 서식에서 완만한 선을 체크하면 부드럽게 변화된다.
- 대시보드 꾸미기
색상표, 도형 등 마스터 대시보드 엑셀 시트를 참고하여 대시보드를 완성한다.
[TIP]
- 아이콘: flaticon 사이트 또는 삽입-아이콘 활용
- 이미지: 구글 이미지 검색 후 엑셀로 드래그(별도 다운로드 X)
- 도형 드래그: 홈-찾기 및 선택-개체 선택
