03. Excel - 240321(목)

엑셀을 활용한 데이터 분석 👨🏻‍🏫허주용

엑셀 고급함수

1. 상대참조와 절대참조
   1. 상대참조 - ctrl + c/v
   2. 절대참조 =$A$1 (F4키 이용)
   3. 열고정($A1) / 행고정(A$1)
2. vlookup/hlookup
   1. VLOOKUP(조회 값, 조회 값이 포함된 범위, 반환 값이 포함된 범위의 열 번호, 대략적인 일치(TRUE) 또는 정확히 일치(FALSE))
   2. HLOOKUP(조회 값, 조회 값이 포함된 범위, 반환 값이 포함된 범위의 행 번호, 대략적인 일치(TRUE) 또는 정확히 일치(FALSE))
   • False 대신 0/ True 대신 1로도 표기 가능
   • vlookup은 해당값이 여러개일 때 하나의 값 밖에 가져오지 못함.
   • 참조영역은 절대참조하는 습관 들이기!
3. error 표시
   1. if(iserror(수식), 수식이 오류로 평가되는 경우 반환할 값, 수식)
   2. iferror(수식, 수식이 오류로 평가되는 경우 반환할 값)
4. sumif/countif/sumproduct
   1. sumif(조건을 적용할 셀 범위, 추가할 셀을 정의하는 숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건)
   2. → 숫자가 아닌 텍스트 조건이나 논리 기호 또는 수학 기호가 포함된 조건은 큰따옴표(“”)로 묶어야 함
   3. countif(찾으려는 위치, 찾으려는 항목)
   4. 기준을 충족하는 셀의 개수를 계산
   5. sumproduct(계산하려는 배열의 첫번째 인수, 계산하려는 배열의 두번째 이후 인수)
      • 배열 인수의 차원은 모두 같아야 함
      • 기본 연산은 곱하기지만 더하기, 빼기 및 나누기도 가능
   6. 해당 범위 또는 배열의 제품 합계를 반환
5. 기타 함수
   1. if(검사할 조건, 조건이 TRUE일 경우 반환할 값, 조건이 FALSE일 경우 반환할 값)
   2. concatenate(text1, text2)
   3. 텍스트 합치기, =A2&B2로도 표기 가능
   • 수식보기 : ctrl + `
   • 함수 자동완성: tab 키

---

📌 매출 데이터 이용한 프로젝트시 Tip)

• 이익이 0이하인 경우는 제외(숫자필터 이용)
• 프로젝트 목표에 따라 매출, 이익, 판매량, 주문당매출, 주문당이익 등을 적절하게 선정

---

 

EDA

1. EDA 개요
2. 기초통계량
   1. 평균(average)/중위값(median)
   2. 분산(var.s)/표준편차(stdev.s)
   3. 최소값(min)/최대값(max)
   4. 사분위수IQR=Q3-Q1
   5. Q1=Quantile.inc(범위,1~3)
   6. n(count)
3. 왜도, 첨도
   1. 왜도(skew)
   2. 첨도(kurt)
4. 박스플롯, 이상치
   1. 이상치(상한) = Q3 + 1.5*IQR
   2. (하한) = Q1 - 1.5*IQR
   3. 수염(상한) =IF(최대값>이상치(상한), 이상치(상한), 최대값)
   4. (하한) = IF(최소값<이상치(하한), 이상치(하한), 최소값)
5. 상관계수
   1. 분석도구 > 상관분석으로 상관계수 계산
   2. 분산형 차트로 두 변수간의 관계 확인

---

📌 결측치가 있는 데이터 처리시 Tip)

• 보통 파이썬에서는 오류가 나기 때문에 어떻게든 처리 해야함, 그렇다면 엑셀에서는?
• 삭제(데이터가 많은 경우), 평균값 대치, 이전값 대치(시계열 데이터의 경우) 등 상황에 따라 적절하게 처리

---

엑셀로 이해하는 인공지능 👨🏻‍🏫허주용

지도학습

1. 회귀분석
   1. 회귀분석의 기본 아이디어는 잔차제곱의 합을 최소화하는 기울기와 절편을 찾는 것에서 시작
   2. SST = SSR + SSE
   3. 결정계수(R^2) = 1 - SSE/SST
   4. 손실함수와 평가지표
      1. 손실함수(기계를 위한 수치) : RMSE, MSE, MAE
      2. 평가지표(인간을 위한 수치) : 결정계수(R^2)
      → 손실함수와 평가지표가 충돌할 때 손실함수 오류가 적은 알고리즘 모델을 선택하는 것이 합리적

📚 과제

• 파워쿼리 실습
• 히스토그램 작성
• 지도학습/비지도학습 중 관심있는 분야 실습해보기
• sumproduct 함수 익숙해지기