ASAC 빅데이터 분석가 과정 2일차 (24.12.05)

ASAC 빅데이터 분석가 7기/ASAC 일일 기록

ASAC 빅데이터 분석가 과정 2일차 (24.12.05)

junslee 2024. 12. 5. 10:50

엑셀을 활용한 데이터분석

허주용 강사님

코딩없이 시작하는 엑셀 크롤링. WEB부터 ChatGPT API까지 강의 | 다비 - 인프런

다비 | 코딩을 모르는 분들도 쉽게, 엑셀 파워쿼리 기능을 활용하여 데이터를 수집하고 가공하는 방법을 배우실 수 있어요. 아래 프로젝트를 같이 실습하면서 엑셀 크롤링 기술을 배웁니다., 엑

www.inflearn.com

[Python 초보] Flutter로 만드는 ChatGPT 음성번역앱 강의 | 다비 - 인프런

다비 | Python 배우긴 했는데, 어떻게 활용할지 막막한 분을 위한 강의입니다. 프로그램 기초지식을 99.9% 활용!!! 어려운 코드를 최소화하여 기본 예제코드를 가지고 만드는 Flutter 음성번역앱에 도

www.inflearn.com

이거 하고 싶으면 메일 보내기

1] 엑셀 고급함수

tip.수식 보는 법 : ctrl+`

1. 상대참조, 절대참조

- 수식을 "복사/붙히기"를 마음 것 잘 쓰기 위해 필요

상대 참조 : 수식을 복사해서 다른 셀에 붙여넣기를 할 때 셀의 위치가 같이 변경

절대 참조 : 수식을 복사해서 다른 셀에 붙여넣기를 할 때 셀의 위치를 강제로 고정시켜주는 것 (셀 주소에 "$" 표시)

- 절대참조/상대참조를 변경하는 단축키 : "F4"단축키

혼합 참조 (행 또는 열 고정), 절대 참조 (행/열 모두 고정)

2. vlookup, hlookup, iferror

- VLOOKUP : 세로로 작성된 표에서 원하는 값을 찾은 뒤 옆에 위치한 다른 값을 불러오는 함수

= VLOOKUP(검색할 값,표 범위, 열 인덱스 번호, [범위 검색]) //인덱스 번호를 행렬값으로 고정하기도 함

- 인덱스번호 : 몇 번째 열에서 가져올지(1부터 시작)

표 범위의 조건이 늘어나면 에러가 생김

해결 방법 : 조건을 하나로 해서 새로 만들기 but, 조건이 많아지면 비효율적임

- IFERROR : 수식에 에러가 발생하였을 때 대체할 문자, 숫자를 지정해주는 함수

= IFERROR(기존함수,"오류 변환 값")

- 오류 변환 값이 0이 되면, 에러가 난 셀이 0으로 되어 에러 없이 숫자 합산을 할 수 있다.

- 문자일 경우 큰따옴표"" 안에 변환할 값을 입력한다.

- HLOOKUP : 가로로 작성된 표에서 원하는 값을 찾은 뒤 옆에 위치한 다른 값을 불러오는 함수

= HLOOKUP(검색할 값,표 범위, 행 인덱스 번호, [범위 검색])

// VLOOKUP과 HLOOKUP의 차이 이해하기 (직접 해보기)

- ISERROR : 값이 오류이면 TRUE, 참이면 FALSE를 반환하는 함수

= ISERROR(Value)

- ISERROR함수는 일반적으로 IF함수와 함께 사용

= IF(logical_test,value_if_true,value_if_false) //IF문은 결측치를 채울 때 사용할 수 있다.

= IF(ISERROR(Value),value_if_true,value_if_false) //Value값의 참,거짓에 따라 원하는 값을 각각 넣을 수 있다.

3. sumif, countif, sumproduct

- SUMIF : 많은 자료 중에서 조건에 맞는 데이터만 찾아서 합계를 구하는 함수

= SUMIF(조건이 적용될 범위, 조건, 합계를 구할 범위)

- SUMIFS : 합계를 구하는 조건이 2개 이상일 때

= SUMIFS(합계를 구할 범위, 첫번째 조건범위, 조건, 두번째 조건범위, 조건, ...)

tip. "A"로 끝나는 단어를 찾는 방법 : A*

- COUNTIF : 조건에 맞는 데이터의 개수만 구하는 함수

= COUNTIF(범위, 조건)

- COUNTIFS : 조건 2개 이상의 개수를 구하기 위한 함수

= COUNTIFS(첫번째 조건 범위, 조건, 두번째 조건 범위, 조건, ...)

- SUMPRODUCT : 여러 범위의 각 항목을 곱한 후, 그 곱의 합계를 계산하는 함수

= SUMPRODUCT(범위1, [범위2], [범위3], ...)

논리식을 포함한 복잡한 조건을 처리할 수 있다.

파생변수 만드는 것이 매우 중요하다.

변수는 많이 만들어 데이터 양을 늘리는 게 머신 러닝에서는 매우 중요하다.

tip.'--' : 논리값을 숫자로 강제 변환하는 기호

4. 실습

<E-Commerce Data>

조건을 여러개 만들어 필터와 내림차순으로 정렬하여 원하는 값을 찾는다.

함수를 이용해 인사이트를 다 한 후에

tip. 중복된 항목 제거 : '데이터' -> '데이터 도구' -> '중복된 항목 제거'

tip. 대소 비교 시각화 : '조건부 서식' -> '색조'

2] EDA

1. EDA 개요

EDA(Exploratory Data Analysis, 탐색적 데이터 분석)

- 데이터 분석 분야에 대한 도메인 지식 강화

- 데이터셋에 있는 오류를 알 수 있다

- 전처리 해야 할 부분 또는 변수선택에 대한 판단 가능

- EDA 종류

- 엑셀에서 분석도구 사용하는 법

엑셀 홈 -> 옵션 -> 추가기능 -> 분석도구 (이동) -> 분석도구 추가

2. 기초통계량

- 분석에 사용할 데이터를 준비 : 인사이트한 데이터를 값만 따로 떼는 과정

전체를 선택한 후 복사 > 새로운 시트 만들기 > 이름을 ‘복사본’으로 입력 > A1 셀을 오른쪽마우스로 클릭 > 값만 붙여넣기 선택

- 열에서 원하는 값만 남기고 삭제하기

- 열머리글을 포함한 전체 데이터 선택 CTRL+SHFT+↓

평균 =AVERAGE

중위값 =MEDIAN

분산 =VAR.S

표준편차 =STDEV.S

최소값 =MIN

Q1 =QUARTILE.INC(표 범위,1)

Q2 =QUARTILE.INC(표 범위,2)

Q3 =QUARTILE.INC(표 범위,3)

최대값 =MAX

IQR =Q3-Q1

이상치(하한) =Q1-1.5*IQR

이상치(상한) =Q3+1.5*IQR

수염(하한) =IF(최소값<이상치하한,이상치하한,최소값)

수염(상한) =IF(최대값>이상치상한,이상치상한,최대값)

왜도 =SKEW

첨도 =KURT

수염과 이상치 수식이 바뀐거 같다
수염(상한) = Q3 + 1.5*IQR
수염(하한) = Q1 - 1.5*IQR
이상치(하한) = IF(최소값<수염하한,수염하한,최소값)
이상치(상한) = IF(최대값>수염상한, 수염상한, 최대값)

- 히스토그램 그리기 #데이터의 시각화

삽입 > 차트에 있는 히스토그램 선택

3. 왜도, 첨도

왜도(SKEW,비대칭도) : 데이터의 좌우 쏠림을 수치로 표현

- 데이터가 좌우로 쏠려 있으면 분석 결과의 정확도가 떨어지거나 오류가 발생할 수 있다.

- skewed to the right (=positive skew) : 왼쪽에 데이터가 몰려서 오른쪽에 꼬리가 늘어짐, 양수(+)

- skewed to the left (=negative skew) : 오른쪽에 데이터가 몰려서 왼쪽에 꼬리가 늘어짐, 음수(-)

첨도(KURT) : 분포의 꼬리가 얼마나 많이 늘어지는가를 수치로 표현

FISHER의 공식

4. 박스플롯, 이상치

박스플롯: 데이터의 분포를 한 눈에 볼 수 있게 시각화하여 이상치(Outlier)등을 탐지할 수 있는 시각화 도구

IQR : 데이터의 범위가 25% ~ 75%에 위치한 범위 부분

수염(Whisker) : IQR에서 (1.5*IQR) 만큼 벗어난 값들의 범위

//이상점 : 수염 밖에 표시한 데이터

이상치(Outlier) : 수염 범위에 벗어난 데이터

5. 상관계수

- 산점도 그리기 : '삽입' -> '차트' -> '분산형'

산점도 그래프로 데이터의 분포 현황을 확인하다.

- 상관계수 그래프 그리기 : '데이터' -> '데이터분석' -> '상관분석' -> '확인'

#입력 범위는 보고자 하는 변수 데이터 전체 (라벨 포함)

#데이터를 보기 좋게 정리한 후 색조로 대소 비교

#CORREL함수: 상관계수 구하는 함수

<여담>

CRM 고객관계관리

//키워드(도메인)으로 조사해보기

- 업무 관련성이 있고, 큰 툴을 가지고 있다.

- 뽑는 회사가 많다.

3] EDA 예제 실습

1. Iris

// 박스플롯의 각 값들을 구하고 상관분석을 진행한다.

2. Titanic

//캐글에서 데이터 가져옴

//결측치는 정답이 없다. - 행제거를 하기도 하고 행을 너무 많이 제거하면 열을 제거하기도 한다.

//결측치 : 값이 없는 셀

#성의 구분(male,female) 같은 경우 0,1로 데이터를 바꿔서 분석 진행

# 결측치를 채우기 위해 평균값을 집어 넣는 코드

=IF('2. Titanic'!D8="",'2. Titanic'!$L$2,'2. Titanic'!D8)

3. Wine Quality

//표준화/정규화랑 제곱근/로그/제곱 비교

// 값이 크지만 양수이면 양의 상관관계

// 값이 크지만 음수이면 음의 상관관계

# 함수 수식 바꿀 때 'Ctrl + F'로 '모두 바꾸기' 기능 사용

# 시트 복사 : Ctrl + 드래그

4. Diabetes(당뇨) //스킵

5. 서울시 미세먼지 //스킵

6. 서울시 지하철 2호선 승하차인원

#인사이트 - 데이터시트를 보고 원하는 표본을 뽑아내는 것

#출퇴근 분류

- 7~10시 출근, 17~20시 퇴근, 기타

=IF(OR(E3="06시",E3="07시",E3="08시",E3="09시"),"출근",IF(OR(E3="17시",E3="18시",E3="19시",E3="20시"),"퇴근","기타"))

#피벗테이블

- 우리가 표로 정리해서 보고자 하는 관점의 축을 잡고, 표로 만들어 주는 기능 (피벗 뜻 : 회전축)

- 다양한 관점에서 데이터를 확인해 볼 수 있다.

'삽입' -> '피벗테이블'

#틀고정 : 선택되는 셀의 상단과 좌측을 기준으로 설정