구 분 | 내 용 | 시간 (분) |
Excel 세팅 | •Excel을 활용하기 위한 기본 설정 및 함수 이해
- 바로가기 모음 탭
- vlookup, if 등 활용 | 15 |
데이터분석
실습 1 | •가상의 매출데이터 활용, Insight 도출 실습
- 분석을 바라보는 관점 수립, 이에 따른 데이터 산출 실습
- 피벗, sumif 등 활용 | 25 |
Review | •데이터 분석 미션 부여 (값 도출 및 인사이트 발표)
- 매출데이터 활용, 운영전략 제안 | 10 |
•
엑셀 활용
◦
개요, 실습 및 강의 시트 1
◦
데이터 시트 1
Sheet 1. 제목 : 엑셀을 이용한 데이터 분석 기초
•
자기소개 포함
•
목차
Sheet 2: 엑셀 난이도 점검
•
엑셀 얼마나 알고 있나요? - 교육생 난이도 점검
◦
vlookup / 피벗 활용 기준
◦
퀴즈 : 다음 빈칸에 어떤 함수를 쓰면 좋을까요?
Sheet 3 : [기본 설정] Excel 인터페이스 이해
•
기본 글꼴 설정하기
◦
파일-옵션-일반 -다음을 기본 글꼴로 사용
•
자동저장 옵션
◦
10분에서 5분으로
•
빠른 실행도구 모음
•
행과 열의 구분
◦
가로줄 행(行, Row)
◦
세로줄 열(列, column)
•
인터페이스
◦
리본, 통합 문서, 시트, 셀, 수식 입력줄, 상태 표시줄에 대해 설명
◦
개발자 도구 추가하기
◦
틀 고정 / 열 너비 & 행 높이 조정
Sheet 4 : [기본 설정] 셀 서식 지정
•
숫자 및 텍스트 서식, 조건부 서식, 셀 스타일에 대한 예시
•
숫자 / 텍스트 데이터 구분 (value / text 함수)
•
텍스트 나누기
◦
초록색 숫자표시 > 텍스트 나누기로 숫자 만들기
•
선택하여 붙여넣기
◦
C+A+V
•
한번에 붙여넣기
◦
C+enter
•
사용자 지정으로 숫자 형식 표기하기
◦
따라서 쉼표 표시 대신, ① 선택한 열에서 'Ctrl+1'을 눌러 ② '표시 형식' 탭의 ③ '사용자 지정'을 클릭하고 ④ '#,##0'을 선택해줍니다.
◦
팁! 핸드폰번호 같은 010으로 시작하는 숫자는 텍스트 나누기시 텍스트로 선택
•
빠른채우기 실행
◦
입력 후 Ctrl + E (단, 인접한 열)
•
셀 병합 해제 후 빈칸 채우기
◦
홈-편집-찾기 및 선택-이동옵션- 빈셀 - 확인
◦
=바로 위, ctrl+enter
Sheet 5 : [데이터 분석] Raw data 가공 하기
•
첫 행에 컬럼명으로 채워주기
•
불필요한 컬럼 삭제
◦
오른쪽 버튼 눌러 '삭제'를 선택합니다. 단축키인 'Ctrl+ (-)'키
•
데이터 선택 하기
◦
'Ctrl+Shift+ →' / 'Ctrl+Shift+ ↓’
◦
전체 지정시 열 지정 세팅
•
데이터 가늠하기
◦
'Ctrl+ ↓’ / 행 하단 이동
◦
컬럼명 'Alt+ ↓’ 누르기 (고유의 값만 간략히)
Sheet 6: [데이터 분석] 기본 수식 및 함수
•
산술 연산 및 기본 함수(Sum, Average, Min, Max, Count, CountA, IF)에 대한 소개
•
산술 연산 비교 연산자
◦
>,<, >=, <=,<>
•
기본함수
◦
Sum,Average, Min, Max, Mode,Count, large, small
◦
IF, Sumif, Sumifs, Countif, Countifs, Averageif, Averageifs
◦
isodd (홀수)
•
이름관리자 활용
◦
수식-정의된 이름-선택영역에서 만들기
•
날짜 가공하기
◦
left, right, mid
◦
Find, Search
▪
이메일 주소에서 영문명 추출 left, find
▪
이메일에서 도메인 주소 추출 len,find, right
◦
today, date, year,month,date
◦
datedif
start_date | end_date | 수식 | 설명(결과) |
2001-01-01 | 2003-01-01 | =DATEDIF(Start_date,End_date,"Y") | 해당 기간에 포함된 전체 연도 수인 2년 (2) |
2001-06-01 | 2002-08-15 | =DATEDIF(Start_date,End_date,"D") | 2001년 6월 1일부터 2002년 8월 15일까지의 날짜 수인 440일 (440) |
2001-06-01 | 2002-08-15 | =DATEDIF(Start_date,End_date,"YD") | 연도는 무시하고 6월 1일부터 8월 15일까지의 날짜 수인 75일 (75) |
A ~ Z까지 랜덤하게 생성하고 싶으면 셀 안에 =CHAR(RANDBETWEEN(65,90)) 이케 쓰면 된다. 이러면 1개의 알파벳이 랜덤하게 생성된다. 이 때 몇 개 더 붙여 쓰고 싶다면..
=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))
만약 대문자 아니고 소문자를 원한다면.. 숫자를 97~122 로 바꾸면 된다.
Sheet 7 : [데이터 분석] vlookup 과 피벗 테이블
•
필터 걸기 / 데이터 정렬
◦
'Ctrl+Shift+L
◦
자동채우기 속성 활용
▪
연속 데이터 채우기
•
고급 필터
◦
데이터 - 정렬 및 필터 - 고급
▪
목로검위 - 조건범위
•
vlookup
◦
[1단계] =VLOOKUP
영업팀명이 시작되는 D2 셀에 '=VL'까지만 치더라도 'VL'로 시작하는 함수가 VLOOKUP밖에 없기 때문에 'Tab'을 누르면 함수가 자동 완성됩니다.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
▪
lookup_value: 두 테이블에 공통되는 키 값인 [영업팀코드]를,
▪
table_array: 찾을 범위인 '영업팀정보' 시트의 표(반드시 가장 왼쪽 열이 [영업팀코드]로 시작하도록 범위 지정)에서 찾아서,
▪
col_index_num: 찾는 값이 그 값으로부터 오른쪽으로 몇 번째 열에 있는 값인지를 설정해주는 함수입니다.
▪
[range_lookup]: (이 값을 FALSE로 설정해야, 키 값이 정확하게 일치할 때 원하는 값을 불러옵니다. 비워두거나 TRUE로 설정하면 원하는 결과가 나오지 않을 수 있습니다.)
◦
[2단계] = iferror
▪
N/A 값을 다른 값으로 (왜? 계산이 안되서)
직장인은 각종 시스템과 수많은 파일로 흩어져 있는 데이터를 일일이 찾아다 써야 하고, 어딘가에 없으면 사람들에게 취합해 만들어서라도 써야 합니다.
그런데 중요한 건, 여러 데이터를 하나의 파일에 모아 놓더라도 각각의 시트로 존재하면 제대로 써먹을 수가 없다는 것입니다. 하나의 시트에 하나의 표로 들어 있어야지만 어떻게든 분석이 가능하죠. 바로 이때 쓰는 기능이 브이룩업입니다.
•
피벗 테이블
◦
현황을 보는 일보/대시보드를 만들때 쉽게
◦
raw data를 활용
▪
테이블 형식 / 일보로 활용 방법
▪
[1단계] 원본 데이터를 열 단위로 선택한 상태에서,
▪
[2단계] '삽입' 탭에 '피벗 테이블'을 누릅니다.
▪
[3단계] '피벗 테이블 필드'에 나오는 컬럼명을 드래그해, '필터'와 '값', '행'과 '열' 영역에 놓아주세요. 텍스트는 주로 행이나 열 영역에 놓아 고유값을 펼쳐주고, 숫자 데이터는 값 영역에 배치해 합계 또는 개수를 집계해줍니다.(값 데이터가 바뀌면 Ctrl+Alt+F5로 '새로고침' 해줍니다. 새 컬럼은 웬만하면 중간에 삽입하되, 부득이 참조하는 raw data 영역이 바뀌었다면 '분석 탭'에서 '데이터 원본 변경'을 해줍니다.)
◦
계산필드 활용
▪
피벗 테이블 분석 - 필드, 항목 및 집합
◦
슬라이서 활용
▪
피벗 테이블 분석 - 필터 - 슬라이서삽입
•
신규함수 : Filter
◦
범위,조건, 결과없음 반환값
▪
FILTER($A$2:$M$586,$C$2:$C$586=Q5,"결과없음")
▪
FILTER($A$2:$M$586,($C$2:$C$586=Q5)*($L$2:$L$586>=300000),"결과없음")
▪
=FILTER(FILTER($A$2:$M$586,($C$2:$C$586=Q5)*($L$2:$L$586>=300000),"결과없음"),{1,1,1,1,1,0,0,0,0,0,0,0,0})
•
고급 : index / match
◦
원하는 값을 바꿨을 때 자동 세팅되도록
◦
피벗과 유사, 같이 활용하면 좋음
◦
=INDEX(EP6_data!$S$21:$W$45,MATCH($C$2,EP6_data!$R$21:$R$45,0),MATCH(EP6_인덱스매치!C$4,EP6_data!$S$20:$W$20,0))
◦
Sheet 8 [데이터 분석] 데이터 시각화
•
조건부 서식 활용하기
•
데이터를 활용한 차트 제작 방법
◦
차트 꾸미기
Sheet 9 [Review]
•
표 제작 팁
◦
숫자는 오른쪽 정렬
◦
천단위 구분 기호 활용
◦
단위 명시
◦
표에서 세로선 지양
◦
요약 첫페이지
▪
시트 눈금선, 머리글 제거
•
구글 스프레드 시트 활용
◦
arrayformula
◦
importrange
◦
스파크라인
◦
•
시트 보호하기
•
꿀 단축키
◦
작업 재실행: Ctrl + Y
◦
찾기: Ctrl + F
◦
찾기 및 바꾸기: Ctrl + H
◦
현재 셀 편집: F2
◦
셀 서식: Ctrl + 1
◦
행 전체 선택: Shift + SpaceBar
◦
열 전체 선택: Ctrl + SpaceBar
◦
날짜 삽입: Ctrl + ;
◦
시간 삽입: Ctrl + :
◦
차트 만들기: F11 또는 Alt + F1
◦
셀 서식 - 글꼴: Ctrl + Shift + P 또는 Ctrl + Shift + F
•
웹크롤링 쉽게
◦
listly
