엑셀 공부 챕터 5, 6(完)(230216, 230217)

*오빠두엑셀의 '실무엑셀 8시간 로드맵' 내용의 개인적 정리글. 편의에 따라 누락, 결합된 부분 있음

 

챕터5: 데이터 분석하기

0. 데이터 분석의 기본, 필터와 피벗 테이블

-필터: 즉각적 현황 파악에 유리

-피벗 테이블: 지속적 파악 및 관리에 유리

 

1. 필터

-적용: 데이터 -> 정렬 및 필터 - 필터 or Ctrl Shift L

*데이터 내부의 셀 하나를 클릭한 상태로 적용해도 좋으나 빈 행이 있으면 직접 영역 드래그 후 적용

-정렬 지우기, 텍스트 필터, 숫자 필터, 색 기준 필터도 가능

-정렬과 함께 쓰고 싶다면 선 정렬 후 필터를 사용할 것

 

2. 숨겨진 범위 vs 필터링 범위

-복붙 시 숨겨진 범위는 전체 데이터 복사, 필터링 범위는 보이는 데이터만 복사

-alt ; , ctrl C: 숨겨진 범위에서 보이는 데이터만 복사하기

 

3. 와일드카드

-김*: 김으로 시작하는 모든 항목 ex) 김영, 김미영, 김베드로

-김*미: 김으로 시작하고 미로 끝나는 모든 항목 ex) 김영미, 김윤현미

-김?: 김으로 시작하는 두 글자 항목 ex) 김영, 김현, 김순

-김?미: 김으로 시작하고 미로 끝나는 두 글자 항목 ex) 김영미, 김숙미, 김정미

-함수에서의 ~ : 와일드카드 해제. 함수에서 ~로 범위를 표시하고 싶으면 ~~을 사용해야

 

4. 데이터 관리 핵심규칙

1) 셀 내부에 줄바꿈을 사용하거나 셀을 비우지 말 것

*셀을 비우면 0으로 자동입력됨. 싫다면 =셀 번호&""을 사용할 것

2) 집계 데이터와 원본 데이터를 분리하기

3) 원본 데이터의 머릿글은 열 당 하나만 배정(셀 병합 금지)

4) 원본 데이터는 무조건 세로 방향 블록쌓기(가로는 취합, 정렬, 누락대처 어려움)

 

5. 피벗 테이블 기초와 시각화

-적용: ctrl A -> 삽입 - 피벗테이블 -> 행, 열, 값 설정

-필드 표시형식으로 셀 서식, 숫자 서식을 보기 좋도록 수정

-디자인: 피벗테이블 선택 -> 디자인 탭 -> 보고서 레이아웃 -> 테이블 형식

-너비 고정: 우클릭 -> 피벗 테이블 옵션 -> 업데이트시 열 자동맞춤 해제

-마찬가지로 피벗 테이블 옵션에서 확장/축소 표시 해제(shift scroll로 펼치기 가능)

 

6. 피벗 테이블의 집계 방식

-숫자(ex: 금액, 할인율, 등) ->합계 집계

-문자(ex: 영수증 번호, 일련번호, 등) -> 개수 집계

-평균으로 비교하기: 우클릭 -> 값 요약 기준 -> 평균(백분율 비교, 평일-주말 비교 등이 필요할 때)

*ctrl shift 5: 백분율 전환

 

7. 슬라이서 필터

-피벗 테이블 분석 -> 필터 - 슬라이서 삽입

-열 너비가 바뀌므로 옵션에서 수정

-불필요한 버튼 감추기: 슬라이서 우클릭 -> 슬라이서 설정 -> 데이터에 없는 항목 숨기기

-시간표시막대: 연속된 숫자에만 피벗테이블 분석에서 적용 가능. 불연속할 경우 아래처 슬라이서 사용

1) 날짜 인덱싱: 피벗 테이블 우클릭 -> 필드 목록 표시 -> 일자 항목을 행으로

-> 표에 있는 날짜 우클릭 -> 그룹 -> 일, 월, 연도 등 원하는 항목 선택

2) 피벗 테이블 반영하기: 필드 목록에서 일자 제거 -> 슬라이서 삽입

 

8. 대쉬보드 만들기

-대쉬보드? 서로 연결된 피벗 테이블을 여러 개 배치하는 것

-적용: 피벗 테이블 여러 개 만들기 -> 슬라이서 우클릭 -> 보고서 연결

-디자인: 피벗 테이블에 있는 셀 선택 -> 삽입 - 추천차트 -> 차트 디자인

-주의: 슬라이서의 연결은 피벗 테이블이 동일한 원본 데이터를 가질 때만 가능

*같은 데이터여도 범위를 직접 참조한 것과 이름을 붙여 참조한 것은 원본이 다른 것으로 간주함

 

 

챕터6: 데이터 시각화 하기

1. 비즈니스 엑셀 매너

1) 최종 저장은 핵심시트의 A1셀에서

2) 눈금선, 머릿글 숨기고 저장: 보기 -> 표시

3) 틀 고정 적용: alt WFF

4) 레이아웃 관리: 서식의 경우 보기 - 페이지 나누기 미리보기 or 페이지 레이아웃

5) 편집 가능 셀 표시하기: 데이터 영역 전체 선택 -> ctrl G -> 옵션 - 상수 -> 파란 글씨

 

2. 꺾은 선 그래프의 활용

-적용: 표 선택 -> 삽입 -> 추천 차트

-최댓값/최솟값 조정: 가로축 or 세로축 우클릭 -> 축 서식 -> 최댓값/최솟값

-깔끔한 정리: 제목, 축 삭제. 눈금선과 윤곽선은 가장 연하게

-데이터 레이블 추가하기: 차트 클릭 -> +메뉴 -> 데이터 레이블

-색깔 정리: 강조 데이터는 빨강, 나머지는 회색으로

-범례 위치 조정: 우클릭 -> 범례서식 -> 그래프 위쪽으로 설정 -> 드래그로 우측 코너로 옮기기

-예상 그래프 구분: 꺾은 선을 두 번 클릭하여 특정 부분만 선택 -> 우클릭 -> 윤곽 -> 점선 선택

 

3. 그림판을 이용한 색 수집

-색조합 검색: 구글 - PPT Chart Template 이미지 검색 -> win shift S(캡쳐)

-RGB값 추출: 그림판에 붙여넣기 -> 스포이드 -> 색 편집 -> RGB값 메모장에 기록

-엑셀 그래프에 적용: 채우기 - 다른 채우기 색 -> 사용자 지정

 

4. 혼합 차트 기초

-혼합 차트 사용 이유: 여러 정보를 동시에 표현하고 싶으나 단위가 달라 잘 표현되지 않는 경우

-보조축의 용도: 다른 단위의 축 추가

-축 숨기기: 축 우클릭 -> 축 서식 -> 레이블 -> 위치 없음

-데이터 레이블 조정: 위치, 색

-버블라인: 그래프 각 지점에 표시되는 둥근 포인트

-버블라인 만들기: 꺾은선 우클릭 -> 데이터계열 서식 -> 채우기 -> 표식 -> 기본제공: 원 -> 채우기: 흰색 -> 테두리 너비: 2

 

5. 누적막대그래프에 합계 레이블 표시하기

-누적막대그래프 만들기: 합계 제외 범위 선택 -> 추천 차트 -> 누적 세로 막대 -> 색 변경 -> 단색형

-합계 레이블 추가하기: 드래그 범위를 합계까지 -> 차트 우클릭 -> 차트 종류 변경 -> 모든 차트 -> 혼합

-> 합계 항목만 꺾은선으로 -> 꺾은 선 윤곽 없음 -> 그래프 우클릭 -> 데이터레이블 추가

-> 그래프 우클릭 -> 데이터 레이블 서식 -> 레이블 위치 위쪽으로 -> 범례에서 합계 삭제