작성일: 2023. 3. 31.
🔥 와..! 😱 3주만에 이게 가능..?! - 나만의 멋진 엑셀 대시보드 만들기! (No 함수) 🔥
✍ 사전학습자료 (총 20분)
[예습] 피벗테이블 최강 콤비, 슬라이서 필터 사용방법 (06:43)
🚀 보충학습자료 (총 20분)
[보충학습] 피벗테이블 슬라이서 디자인, 3가지 요소만 기억하세요 (07:27)
[보충학습] 엑셀 차트 시각화, 이 영상에 모두 담았습니다! (07:09)
[보충학습] 엑셀 백분율 차트, 알고보면 간단합니다! (06:14)
🎯 미션 목표
- 피벗테이블과 차트 시각화 기법을 활용 실무에 바로 적용할 수 있는 자동화 대시보드를 제작하고, 대시보드 마스터 시트를 활용하여 대시보드를 편리하게 디자인하는 방법을 알아봅니다..
✍ 스터디 노트 (요약)
• 빈칸을 채워 문장을 완성한 후, 아래 A 보고서와 B 보고서를 예제로 실무 데이터 시각화의 핵심 요소를 설명합니다.
| 직장인에게 데이터 시각화란, "내가 보여주고자 하는 (또는 상대방이 보고 싶어하는) 자료는 알기 쉽게 전달하는 것" 입니다. |
① 직장인에게 데이터 시각화란 말그대로 "Data (데이터) + 視覺化 (시각화)"를 뜻하며, 무조건 예쁘게 꾸미는 것이 아니라 전달하고자 하는 메시지를 "알기 쉽게" 정리하는 것입니다. 전달하고자 하는 메시지가 무엇인지를 먼저 정하고, 그 메시지를 알기 쉽게 전하기 위해서는 어떤 시각화 기법을 활용할 것인지를 고민하는 것이 실무 데이터 시각화의 핵심입니다.
② 데이터 시각화에서 핵심적으로 기억해야 할 것은 '색감'과 '간소화' 입니다. A보고서만 본다면 어떤 내용을 전하고자 한 것인지 분명히 알 수가 없고, 단순한 집계 데이터를 나열해 둔 것으로밖에는 보이지 않습니다. 한편 B보고서는 2023년 상반기 월별 판매량 추이를 분석한 것이며 이중에서 하향 트렌드를 보이는 일부 제품을 따로 색상으로 표시하여 '판매량이 저조한 냉정수기, 정수기필터, 슬로우쿠커, 김치냉장고'의 판매량 추이를 강조하고, 전체 제품별 판매량의 변화를 눈에 잘 들어오도록 스파크라인을 통해 데이터를 시각화한 것을 확인할 수 있습니다. 상반기 중 판매량이 가장 높았던 월의 스파크라인을 주황색으로 표시해 색감을 더했습니다.
• 차트 시각화에서 가장 중요한 2가지 요소는 ① 색감과 ② 차트 간소화입니다. 아래 그림을 예제로 2가지 요소에 대해 간략히 설명합니다.
| ① 차트 색상은 최대 3개를 넘기지 않습니다. ② 차트 색상은 '강조색'과 '보조색'을 정한 후 사용하면 편리합니다. ③ 차트에 불필요한 요소 (예: 차트제목, 세로축, 눈금선) 등을 숨기면더욱 보기 좋은 차트를 만들 수 있습니다. |
① 색감: 차트의 색상은 최대 3개를 넘기지 않아야 하며, 엑셀 기본 색상보다 조금만 더 신경쓰면 눈에 띄는 고퀄리티의 데이터 시각화 자료를 만들 수 있습니다. 위 보고서에서 전달하고자 하는 메시지는 '영업2팀이 1~3분기 꾸준히 성장하고 있으며 4분기에도 실적이 좋을 것으로 예상된다'라는 것입니다. 강조하고자 하는 영업2팀 데이터의 색상을 돋보이게 하고, 나머지 영업1팀, 영업3팀의 색상은 상대적으로 눈에 덜 들어오는 옅은 회색으로 시각화했습니다.
② 간소화: 색감 정리가 끝났다면 이제 불필요한 항목을 최소화해야 합니다. 우선 차트 제목은 삭제하고, 눈금선의 윤곽선 색상은 가장 옅게 설정하여 꺾은선 그래프가 더 잘보이도록 합니다. 언제나 기억해야 할 것은 강조하고자 하는 데이터를 눈에 띄게 만들어야 한다는 것입니다. 영업2팀의 성장 추이를 더 강조하기 위해 차트의 세로 길이를 늘려 그래프의 기울기를 높이고, 세로축의 최소값/최대값을 영업2팀의 최소/최대 실적치에 맞추어 실적이 더욱 가파르게 성장한 것처럼 시각화합니다. 세로축 데이터를 삭제하고 각 분기별 데이터 레이블에 해당 실적을 표기하면 그래프가 더 보기 좋아집니다. 그리고 가장 중요하게 전달해야 하는 영업2팀의 현 실적 및 4분기 예상실적에 대한 긍정적인 전망을 눈금선 박스 등을 통해 따로 구별해 둔다면 더할 나위 없이 좋은 그래프를 완성할 수 있습니다.
• 챌린지를 진행하며 새롭게 배운 내용, 업무에 적용하여 작업시간을 단축한 이야기 등을 자유롭게 작성합니다.
(업무에 도움이 된 내용, 앞으로 취업 후 꼭 써보고 싶은 기능, 등 무엇이든 좋습니다. 편하게 남겨주세요!😆)
3주간 배운 것을 정리해보니, 이렇게나 많은 것을 얻어간 소중한 시간이었다는 것을 새삼 느꼈습니다.
✏️ 챌린지 1주차
✔️ 빠른 채우기 Ctrl + e (치즈크러스트 레귤러)
✔️ 자동채우기 - 날짜 범위 더블클릭 땅땅
✔️ 표 테두리는 순서대로 하나씩 흐바흐바 Alt → H → B → A
✔️ 표 너비 복사는 위쓰 Ctrl + w
✔️ 백분율 단축키 Ctrl + Shift + 5
✔️ 천 단위 구분 기호 Ctrl + Shift + 1
✔️ 숫자는 오른쪽 정렬, 문자는 왼쪽 정렬
✔️ 셀 서식 (샵 콤마 샵 샵 영 #,##0)
✔️ 조건부 서식 - 데이터 막대, 아이콘 강조, 스파크라인
✏️ 챌린지 2주차
✔️ 머릿글 한 줄, 셀 병합 금지, 집계 데이터 제외
✔️ 원하는 값(유효한 데이터)만 넣게 하고 싶을 때: 데이터 유효성 검사
✔️ 피벗테이블 = 십자가
✔️ 다중조건 VLOOKUP 함수
✔️ 실전 SUMIF 함수 (고유값 뽑기)
✔️ 세로방향 블록쌓기
✔️ 피벗테이블, 슬라이서 활용한 나만의 자동화 보고서 만들기
✏️ 챌린지 3주차
✔️ 피벗테이블 계산필드
✔️ 데이터 시각화 = 메시지를 알기 쉽게 전달하는 것
✔️ 차트 색감 & 간소화 꿀팁 기억하기
✔️ 차트 제목 삭제, 눈금선 옅게, 세로축 삭제, 데이터 레이블 추가, 막대 폭 조정, 서식 강조
✔️ 가로막대 차트는 항목을 거꾸로
✔️ 대시보드 만들기 - 원본은 표로 만들기, 피벗 테이블 넣기, 피벗 디자인은 테이블 형식으로
✔️ 슬라이서로 실시간 필터링 하기 (슬라이서 우클릭 - 보고서 연결)
✔️ 피벗 차트 버튼 숨기기
✔️ 마스터시트 꾸미기 (색상, 도형, 윤곽선, 완만한 선, 아이콘 등)
✔️ 엑셀 범위를 PPT처럼 선택 - [홈] 탭 - 찾기 및 선택 - 개체선택 클릭 후 드래그
✔️ 도형/차트 값을 셀과 실시간 연동하기 (수식 필요)
마침 회사에서 작년 카드전표 금액을 급히 맞출 일이 있었는데, 그동안 관리되어 오던 데이터들이 가로블럭으로 쌓여있다는 것이 보이기 시작했습니다. 시간이 조금 걸리더라도 야근을 하며 기존 데이터를 세로블럭으로 정리하고 피벗테이블, 슬라이서로 월별/분기별/주간 결제금액을 한 워크시트에서 실시간으로 필터링할 수 있도록 만들어 부장님께 가져다 드렸더니 '세상에 이렇게까지 잘 정리해주다니?'라는 말을 들었습니다 ㅎㅎ 저도 이제 오대뤼처럼 혼나지 않고 상사에게 칭찬을 듣는 대뤼가 될 수 있을 것 같습니다. 귀한 자료인 마스터시트는 업무 보고에 활용하기 보다는 제 스스로 자료 관리 차원에서만 대시보드를 만들 생각입니다. (상사 분들은 예쁘게 꾸미는 것 까지는 바라지 않고 정확하고 빠르게 알잘딱깔센한 워크시트를 원하시더라고요 ㅎㅎ) 재직 6년차에 엑셀을 웬만큼 많이 다룰 줄 안다고 생각했는데, 컴활 학원에서 여기저기 주워 들은 함수만 화려하게 쓸 줄 알았지 이렇게 함수 하나 안 쓰고 쉽고 빠른 방법으로 업무의 효율성을 높이는 방법이 있다는건 이번에 오빠두엑셀을 통해 처음 알았습니다. 앞으로도 라이브 강의나 챌린지 강의 기회가 있으면 꾸준히 참여하며 엑셀을 마스터 하고 싶습니다. 감사합니다 오빠두님!!
✍ 스터디 노트
✏️ 지난 주 내용 복습
✔️ 올바른 데이터 구조 규칙 3가지 (함수 몰라도 이것만 기억하자)
1. 머릿글은 반드시 '한 줄'로 관리하기
2. 원본데이터에는 셀 병합 절대 사용 금지
3. 집계 데이터는 원본에서 제외하기 (뻥튀기 x)
✔️ 피벗테이블은 + 십자가 + 만 기억하기
✔️ 데이터는 무조건 세로방향
✏️ 피벗테이블 계산필드 기본 사용법
✔️ 계산필드를 쓰면 원본을 유지한 상태로 피벗테이블을 만들 수 있다
- 피벗테이블은 원본 데이터를 집계해주는 "보고서"
- 아래 피벗테이블에서 [배송비+결제액] 합계 금액으로 데이터를 보고 싶을 때? "계산필드"
- [피벗 테이블 분석] 탭 - 필드, 항목 및 집합 - 계산 필드
- 원하는 항목 더블클릭해서 수식 작성
⚠️ 주의사항: 곱셈, 나눗셈을 계산필드에 적용하면 총합계/부분합계에서 잘못된 계산 결과가 나온다.
- 하지만 오히려 실무에서 '가중평균'으로 계산할 때 유용하게 쓸 수 있다
✏️ 피벗테이블 계산필드 활용 (단가평균 vs. 가중평균)
✔️ [피벗 테이블 분석] 탭 - 필드, 항목 및 집합 - 계산 필드 - 레이블 요약 기준을 평균으로 바꾸기
- 전체 금액 / 전체 수량 = 가중치를 고려한 평균 단가 산출 가능!!
- 오빠두 엑셀 가중평균 구하기 페이지 참고
✏️ 직장인에게 '데이터 시각화'란?
✔️ Data (데이터) + 視覺化 (시각화)
- 예쁘게 꾸미는 것이 아니라 "알기 쉽게" 정리하는 것
- 데이터 시각화의 핵심은 전달할 메시지를 전하는 것. 무엇을(What)?, 어떻게(How)?
- 전달할 메시지를 먼저 정해라
- 피벗테이블 정렬 원칙: 가나다순 오름차순 정렬
✏️ 차트 시각화, 2가지만 기억하세요 - "색감" & "간소화"
✔️실무에서 필요한 차트 5가지
1️⃣ 꺾은선: 시간의 흐름에 따른 값의 변화
2️⃣ 세로막대: 항목별 값의 크기 대/소 비교
3️⃣ 가로막대: 크기비교할 항목 종류가 많을 경우
4️⃣ 누적막대: 여러 카테고리의 항목별 비율 및 시간에 따른 변화
5️⃣ 원형: 단일 카테고리 항목별 비율
✏️ 꺾은선 차트
✔️ 시간의 흐름에 따른 데이터값의 시각화
- 범위 설정 - [삽입] 탭 - 추천 차트 - 꺾은선형
✏️ 차트 간소화 꿀팁
✔️ 불필요한 항목을 최소화하기
1️⃣ 차트제목 삭제
2️⃣ 눈금선 클릭 - 윤곽선 색상은 가장 옅게
3️⃣ 강조할 데이터를 눈에 띄게 만들어라 (차트 가로세로 모양, 최소값/최대값 조정 등)
4️⃣ 세로축 삭제, 차트 요소 추가 - 데이터 레이블
5️⃣ 이 차트를 통해 내가 전달할 메시지를 떠올려라 (영업2팀의 성장세)
✏️ 세로막대 차트
✔️ 세로막대를 내림차순으로 정리하면 데이터 비교가 용이하다
- 전달할 메시지: "서울특별시의 합격자 수가 전체 지역의 합계 이상이다"
✔️ 세로막대 간소화 꿀팁: 막대 폭 조정 - 막대 우클릭 - 데이터 계열 서식 - 간격 너비 100%
- 특이사항은 말풍선 효과 - 텍스트가 잘리면 말풍선 텍스트상자 여백 조정하기
✏️ 가로막대 차트
✔️ 가로막대 차트는 데이터를 내림차순으로 보여준다 (기본값)
- 그래서 축 서식 - '항목을 거꾸로'에 체크해야 함
- 세로막대로 표시하기에 항목이 너무 많을 경우 가로막대 차트를 쓴다
✏️ 데이터를 실시간으로 필터링하는 대시보드 만들기
✔️ 대시보드에 사용할 피벗테이블 만들기
- 데이터를 분석할 기준 만들기
- 원본 데이터는 표로 만들어 두기 (Ctrl + t, 머리글 포함) / 표 디자인 없음 / 표 이름 바꾸기
- [삽입] 탭 - 피벗 테이블 - 십자가 규칙!
- 금액은 천 단위 구분기호 넣기 (Ctrl + Shift + 1)
- 엑셀 상위 버전은 날짜도 자동으로 그룹화 해준다 (엑셀 2016 이상)
- 디자인은 테이블 형식으로 바꾸기 (피벗테이블 디자인 - 보고서 레이아웃 - 테이블 형식으로 표시)
- 지역 이름 가나다순 데이터를 '구매액 기준'으로 재정렬 하고 싶을 때 - 내림차순 기준: 합계: 구매액
✏️ 슬라이서로 여러 보고서 필터링하기
✔️ 슬라이서를 추가하면 실시간 필터링이 가능하다
- [피벗테이블 분석] - 슬라이서 삽입
- 슬라이서가 두 개 이상일 때 모든 필터에 데이터를 연결하기: 슬라이서 우클릭 - 보고서 연결
✏️ 대시보드 기본 차트 만들기
✔️ 차트의 기준이 될 피벗테이블 클릭 - 차트 삽입
- 피벗테이블 차트(피벗차트)의 경우: 피벗 버튼 숨기기
- 피벗 버튼 우클릭 - 차트에서 모든 필드 단추 숨기기
- 피벗 버튼 우클릭이 안 될 때: [피벗 차트 분석] 탭 - 필드 단추 - 모두 숨기기
- 기본 세팅 끝나면, 차트를 보기좋기 꾸며주기만 하면 된다!
✏️ 마스터 시트로 대시보드 틀 만들기
✔️ 새 워크시트에 적절한 공간을 분리해 원하는 구간별로 도형과 색상을 추가하기
- 주요 기업 시그니처 색상표 조합을 활용하면 예쁘게 꾸밀 수 있다
✔️ [슬라이서] 탭 - 단추 - 열, 높이, 너비 수정
✏️ 대시보드에 필요한 차트 추가하기
✔️ 차트 삽입 - 위에서 배운대로 차트 간소화, 디자인 꿀팁 그대로 활용하기!
✏️ 대시보드를 보기 좋게 꾸미는 꿀팁
✔️ 차트 주변 윤곽선 지우기 (차트 클릭 - [서식] - 도형 윤곽선 없음)
✔️ 뾰족한 꺾은선: 윤곽선 우클릭 - 데이터 계열 서식 - '완만한 선' 체크
✔️ 제목 추가: [삽입] - 텍스트 상자 추가 (도형 윤곽선, 채우기 없음)
✔️ 글씨 색깔: 기본 검정 보다는 살짝 흐린 검정색으로 (텍스트 1, 25% 더 밝게)
✔️ 아이콘(엑셀 2016부터): [삽입] 탭 - 아이콘 (색상 변경: 그래픽 채우기)
✏️ 대시보드 구성요소 추가 및 완성
✔️ 구글 이미지 검색 - 엑셀로 드래그하면 바로 삽입 가능 - 자르기 - 도형에 맞춰 자르기
✔️ 플래티콘 (flaticon.com) 아이콘 검색
✏️ 엑셀 범위를 PPT처럼 선택하는 방법
✔️ [홈] 탭 - 찾기 및 선택 - 개체선택 클릭 후 드래그
✏️ 도형/차트 값을 셀과 실시간 연동하기
✔️ 빈 셀에 텍스트 입력 후 원하는 수식 입력
= "[" & TEXT(TODAY(), "yy년 mm월") & 기준 월별 트렌드" & "]"
- 텍스트를 연동시킬 도형이나 텍스트상자 클릭 후, 해당 셀 수식 입력줄에 연동할 셀을 절대참조
= $E$9
- 원본 셀은 숨기기 ㅋㅋ
💪 3주 챌린지 요약 정리
✏️ 1주차
✔️ 빠른 채우기 Ctrl + e (치즈크러스트 레귤러)
✔️ 자동채우기 - 날짜 범위 더블클릭 땅땅 / 격일 설정도 가능함
✔️ 피벗테이블, VLOOKUP 함수 등 모든 기능들은 다 세로방향으로 동작한다
✔️ 표 테두리는 순서대로 하나씩 흐바흐바 Alt → H → B → A
✔️ 인터넷 검색 이미지는 아트(Alt)
✔️ 표 너비 복사는 위쓰 Ctrl + w
✔️ 백분율 단축키 Ctrl + Shift + 5
✔️ 천 단위 구분 기호 Ctrl + Shift + 1
✔️ 숫자는 오른쪽 정렬, 문자는 왼쪽 정렬
✔️ 셀 서식 (샵 콤마 샵 샵 영 #,##0 / 세미콜론;)
✔️ 조건부 서식 - 데이터 막대, 아이콘 강조, 스파크라인
✏️ 2주차
✔️ 데이터 구조 규칙 3가지 (머릿글 한 줄, 셀 병합 금지, 집계 데이터 제외)
✔️ 원하는 값(유효한 데이터)만 넣게 하고 싶을 때: 데이터 유효성 검사
✔️ 피벗테이블 = 십자가
✔️ 다중조건 VLOOKUP 함수
✔️ 실전 SUMIF 함수 (고유값 뽑기)
✔️ 세로방향 블록쌓기
✔️ 피벗테이블, 슬라이서 활용한 나만의 자동화 보고서 만들기
✏️ 3주차
✔️ 피벗테이블 계산필드
✔️ 데이터 시각화 = 메시지를 알기 쉽게 전달하는 것
✔️ 차트 색감 & 간소화 꿀팁 기억하기
✔️ 차트 제목 삭제, 눈금선 옅게, 세로축 삭제, 데이터 레이블 추가, 막대 폭 조정, 서식 강조
✔️ 가로막대 차트는 항목을 거꾸로
✔️ 대시보드 만들기 - 원본은 표로 만들기, 피벗 테이블 넣기, 피벗 디자인은 테이블 형식으로
✔️ 슬라이서로 실시간 필터링 하기 (슬라이서 우클릭 - 보고서 연결)
✔️ 피벗 차트 버튼 숨기기
✔️ 마스터시트 꾸미기 (색상, 도형, 윤곽선, 완만한 선, 아이콘 등)
✔️ 엑셀 범위를 PPT처럼 선택 - [홈] 탭 - 찾기 및 선택 - 개체선택 클릭 후 드래그
✔️ 도형/차트 값을 셀과 실시간 연동하기 (수식 필요)
✏️ 3주 동안 배운 내용을 가지고 나만의 대시보드를 만들어 보자!
✔️ 오빠두엑셀 유튜브 '엑셀 대시보드, 초보자를 위한 90분 총정리 가이드 - 보고서의 품격이 달라집니다!' 강의를 보고, 오빠두님이 무료서식으로 제공해 주신 [통장내역] 시트를 raw data로 가져와 이번 챌린지 때 배운 내용을 가지고 저만의 인스타그램 컨셉 대시보드를 만들어 보았습니다! 통장내역 시트에서 항목별 적요 입력시 데이터 유효성 검사 기능을 써 보았고(카드결제, 계좌이체, 모바일결제, 보험, 통신, 현금IC, 인터넷), 금액에 천 단위 구분 기호 삽입 단축키도 사용해 보았습니다(Ctrl + Shift + 1). 피벗테이블을 만들 때 연도/월 그룹화를 하고, 입/출금 계산필드, 피벗 테이블 레이아웃은 테이블 형식으로 정리, 슬라이서 보고서 연결, 데이터 시각화를 위한 차트 색감과 디자인 간소화, 도형+차트값 셀 연동 등등.. 3주간 배운 내용을 최대한 많이 반영해보려고 노력했습니다~~ (뿌듯)
✏️ 1주차
✔️ 빠른 채우기 Ctrl + e (치즈크러스트 레귤러)
✔️ 자동채우기 - 날짜 범위 더블클릭 땅땅
✔️ 표 테두리는 순서대로 하나씩 흐바흐바 Alt → H → B → A
✔️ 표 너비 복사는 위쓰 Ctrl + w
✔️ 백분율 단축키 Ctrl + Shift + 5
✔️ 천 단위 구분 기호 Ctrl + Shift + 1
✔️ 숫자는 오른쪽 정렬, 문자는 왼쪽 정렬
✔️ 셀 서식 (샵 콤마 샵 샵 영 #,##0)
✔️ 조건부 서식 - 데이터 막대, 아이콘 강조, 스파크라인
✏️ 2주차
✔️ 머릿글 한 줄, 셀 병합 금지, 집계 데이터 제외
✔️ 원하는 값(유효한 데이터)만 넣게 하고 싶을 때: 데이터 유효성 검사
✔️ 피벗테이블 = 십자가
✔️ 다중조건 VLOOKUP 함수
✔️ 실전 SUMIF 함수 (고유값 뽑기)
✔️ 세로방향 블록쌓기
✔️ 피벗테이블, 슬라이서 활용한 나만의 자동화 보고서 만들기
✏️ 3주차
✔️ 피벗테이블 계산필드
✔️ 데이터 시각화 = 메시지를 알기 쉽게 전달하는 것
✔️ 차트 색감 & 간소화 꿀팁 기억하기
✔️ 차트 제목 삭제, 눈금선 옅게, 세로축 삭제, 데이터 레이블 추가, 막대 폭 조정, 서식 강조
✔️ 가로막대 차트는 항목을 거꾸로
✔️ 대시보드 만들기 - 원본은 표로 만들기, 피벗 테이블 넣기, 피벗 디자인은 테이블 형식으로
✔️ 슬라이서로 실시간 필터링 하기 (슬라이서 우클릭 - 보고서 연결)
✔️ 피벗 차트 버튼 숨기기
✔️ 마스터시트 꾸미기 (색상, 도형, 윤곽선, 완만한 선, 아이콘 등)
✔️ 엑셀 범위를 PPT처럼 선택 - [홈] 탭 - 찾기 및 선택 - 개체선택 클릭 후 드래그
✔️ 도형/차트 값을 셀과 실시간 연동하기 (수식 필요)
'오피스' 카테고리의 다른 글
| 파워쿼리 기초 완성, 2주 특별 챌린지 - 1주차 (0) | 2024.01.25 |
|---|---|
| 종이접기 효과는 이제 그만! 매력적인 PPT 슬라이드 디자인하기 - 이미지, 인포그래픽, 애니메이션 (PPT 보고서 디자인 2주 챌린지) (0) | 2023.05.03 |
| 엉망진창 내 프레젠테이션, 손쉽게 고쳐보기 (PPT 보고서 디자인 2주 챌린지) (0) | 2023.04.28 |
| [오빠두엑셀] 2023년 엑셀 끝내기, 기초 완성! 3주 챌린지 (2주차) (0) | 2023.03.24 |
| [오빠두엑셀] 2023년 엑셀 끝내기, 기초 완성! 3주 챌린지 (1주차) (0) | 2023.03.17 |
댓글