2. 엑셀 - 데이터 분석 Tool

 데이터 전처리에 사용되는 엑셀 Tool 

1. IF 함수

= IF(조건식, 조건을 만족할 경우 표시할 값, 조건을 만족하지 않을 경우 표시할 값)

 

ex)  IF(C5>=5000, “Platinum”, IF(C5>=2000, “Gold”, “Silver”))

IF 함수 중첩: 거짓일 때의 표시값에 또다른 함수식을 넣음으로써 변수를 늘려갈 수 있다. ∴ IF N번 중첩 → N+1가지 구분

 

2-1. COUNTIF 함수

= COUNTIF(특정 범위, 조건식)

 → 셀 범위 내에서 조건식에 부합하는 데이터의 개수 파악

* 조건식: "문자", "불완전한 조건식(>=5)" 등에는 쌍따옴표를 써줘야 함.

 

2-2. COUNTIFS 함수

= COUNTIFS(특정 범위 1, 조건 1, 특정 범위 2, 조건 2)

 → 셀 범위 내에서 (1개 이상) 여러 조건식에 모두 부합하는 데이터의 개수 파악

여성 중 나이가 40살 이상인 고객의 수

2-3. COUNTBLANK 함수

= COUNTBLANK( 특정 범위 )

 → 셀 범위 내에서 결측치의 개수 파악

 

3. SUMIFS 함수

=SUMIFS( 더할 값들의 범위, 더할 조건들의 범위 1, 조건 1, 더할 조건들의 범위 2, 조건 2)

 → 셀 범위 내에서 조건식에 부합하는 데이터의 값 더해줌

2030년 매출액들의 합

4. VLOOKUP 함수

= VLOOKUP( 기준 데이터값, 불러올 데이터의 범위, 불러올 데이터의 열번호, 0)

                     VLOOKUP($D5,    직원정보LIST!$D:$J,    3,    0)

VLOOKUP 함수는 공통 기준열[사번]을 1열로 가정하며 이를 기준으로 n번째 열의 데이터를 불러오는 함수식이다.

예를 들어 팀은 3열, 직급은 4열, 담당업무는 7열이므로, 상기 함수식의 굵은 글씨에 3,4,7을 입력하여 팀, 직급, 담당업무 데이터를 추출할 수 있다.

 

5. MATCH 함수

=MATCH( 찾으려는 행 값, 찾고 싶은 값이 포함된 행 범위)

VLOOKUP( 기준 데이터값, 불러올 데이터의 범위, MATCH 함수, 0) 를 써서 자동으로 열번호를 찾을 수도 있다.

 

6. 이상치 탐지

• 사분위수 (IQR) 활용 : Q1 -1.5 * IQR 미만, Q3 + 1.5 * IQR 초과의 값은 이상치
• Z-score 활용 : -3 미만, 3 초과인 (x-m)/s의 값은 이상치
• Box-Plot 활용 : 상자수염 그래프 생성 후 데이터 레이블 활성화하여 IQR 파악 → IQR 초과의 값은 이상치

IQR 활용

z-score

Box-plot

---

 데이터 분석에 사용되는 엑셀 Tool 1. 통계적 분석 

1. 상관분석

• 데이터 분석 도구에서 "공분산 분석, 상관 분석 Tool" 및 "조건부 서식" 활용 → 계수 큰 값이 강한 상관관계

 

2. 회귀분석

1) 단순 선형 회귀 분석 (x 한 개)

▶결과 해석법

1. 결정 계수 (0~1): 1에 가까울수록 회귀 모형이 실제 값을 잘 설명함 (0.96이면 96% 설명한다는 뜻)
2. F값: 0.05 미만이면 회귀 모형이 유의미하므로 사용 가능함
3. Y 절편, X 1 (기울기) → 함수식

ex/ y축: 매출값 열 / x축: 직원수 열 지정 

→ 결정계수: 0.644 / F: 0.05미만

 

 

2) 다중 선형 회귀 분석 (x 여러 개)

ex/ y축: 매출값 열 / x축: 기간~직원수 열 지정 

-> 결정계수: 0.96, F: 0.05미만

조정된 결정계수 사용 ( ↔ 단순: 결정계수)

쓸모없는 변수여도 x 개수가 많아지면 결정계수가 높아지는 오류 발생 → 이러한 오류분을 조정

 

p값의 경우 0.05 이하인 변수들이 종속 변수 y (매출)을 가장 잘 설명함.

0.05 이하의 p값을 갖는 변수들 (광고비, 판매수량, 직원수) 로 다중 회귀식 도출

 

3. 분산 분석

* 가정 1 : 코로나 이전엔 모든 해의 상반기 매출액과 하반기 매출액에 큰 차이가 없음 (상반기 하반기 구분 없음)

* 가정 2 : 매출액에 영향을 주는 모든 변수는 통제됨 (코로나 19 제외)

 

01 가설. 19년 상반기와 19년 하반기 매출의 평균에 유의미한 차이가 있음

02 가설. 19년 상반기와 20년 상반기 매출의 평균에 유의미한 차이가 있음

 

1) F 검정

결과 해석: p값이 1의 경우 0.05이상, 2의 경우 0.05이하 → 각각 등분산, 이분산 t-test 진행

 

2) t-test

결과 해석: p값이 1의 경우 0.05이상, 2의 경우 0.05이하 → 1은 유의미한 차이 없음, 2는 유의미한 차이 있음

 

∴ 2번 가설을 채택한다 !

---

 데이터 분석에 사용되는 엑셀 Tool 2. 시계열 데이터 분석 

◎ 시계열 데이터 분석

시계열 데이터: 시간의 흐름에 따라 발생한 데이터 ( 날씨, 주가 등 )

 

지수 평활법

FORECAST.ETS → 예측치 산출

매출액 예측

---

 데이터 시각화에 사용되는 엑셀 Tool  

여러 차트 존재 → 데이터 나타내기 가장 적합한 차트 선정

 

효과적으로 의미 전달하는 디자인 방법

1. 차트의 데이터 모두 단색 (회색)으로 변경
2. 차트에서 가장 중요한, 강조해야 하는 데이터 선택
3. 차트의 메인 컬러 1개 선택
4. 2번의 중요 데이터에만 메인 컬러 적용

 

조건부 서식

최댓값을 5000으로 두면 작은 값끼리 비교 불가능 -> 0~1000

4개월 중 1월의 확진자 수 가장 높을 때