[엑셀 데이터 분석] T-test, 회귀분석, 시계열 데이터 분석

1) T-test (T검정)

 

각 패키지로 10일씩 실험 판매 진행.

제품 패키지 외 모든 조건은 동일하다고 가정.

패키지 변경이 매출액에 영향을 미치는지 가설검정.

패키지 변경 前		패키지 변경 後
일차	매출액	일차	매출액
1	3,854,000	1	2,506,500
2	3,242,250	2	3,094,500
3	6,482,250	3	3,497,250
4	5,718,500	4	3,622,500
5	6,527,500	5	4,014,750
6	4,806,500	6	3,570,250
7	3,791,500	7	3,933,250
8	4,119,250	8	3,755,000
9	4,962,250	9	4,490,250
10	4,692,750	10	3,169,750
합	4,819,675	합	3,565,400

 

귀무가설 : 패키지 변경 전 매출액 평균 = 패키지 변경 후 매출액 평균

대립가설: 패키지 변경 전 매출액 평균 > 패키지 변경 후 매출액 평균 (단측검정)

 

① F 검정 진행 (모분산 차이 검정)

두 집단의 평균을 비교하기 전에 두 집단의 분산 유의미한 차이 보이는지 먼저 검정

귀무가설: 패키지 변경 전 매출액의 분산 = 패키지 변경 후 매출액의 분산

대립가설: 패키지 변경 전 매출액의 분산 ≠ 패키지 변경 후 매출액의 분산

 

[데이터] - [데이터 분석] - [F-검정: 분산에 대한 두 집단]  클릭

 

P(F <=f) 단측검정 (p값) < 0.05 (유의확률)

p 값이 유의확률 0.05보다 작으므로 귀무가설 기각. 대립가설 채택.

패키지 변경 전 매출액의 분산 ≠ 패키지 변경 후 매출액의 분산

=> 이분산 가정의 두 집단에 대한 T 검정 실행해야 한다.

 

② 이분산 T 검정 진행 (모집단 평균 비교)

T검정은 이분산인 경우, 등분산인 경우 다르게 계산함. 엑셀 도구도 분류되어 있음.

 

[데이터] - [데이터 도구] - [t-검정: 이분산 가정 두집단]

 

P(T <=t) 는 T값이 t값 이하일 가능성을 의미함. 즉, 유의확률 p-value. 

P값 < 0.05로 귀무가설을 기각하고 대립가설을 채택함.

즉, 패키지 변경 전 매출액 평균과 패키지 변경 후 매출액 평균에는 유의미한 차이가 있으며 패키지 변경은 매출액에 영향을 미침. 패키지 변경으로 매출액이 감소하였다고 결론 내릴 수 있음.

 

2) 회귀분석

회귀분석은 독립변수와 종속변수 간 관계를 수학적으로 설명하여 독립변수의 값으로 종속변수의 값을 예측할 수 있도록 하는 분석임.

아래에서는 주요지표 현황 자료를 활용하여 매출 (종속변수)에 높은 영향을 미치는 독립변수를 도출하고 회귀분석 진행.

 

 

① 상관관계 분석 및 공분산 분석 진행

매출액(종속변수)에 높은 영향 미치는 변수 도출하기.

위 문제에서는 지금 나온 변수가 모두 영향을 미치는 변수라고 간주하고 모든 변수로 회귀분석 진행함

 

② 회귀 분석

[데이터] - [데이터 도구] - [회귀분석]

 

 

• 결정계수 (R^2) 는 우리가 만든 회귀모델의 설명력을 의미. 높으면 높을수록 설명력이 높은 것. 일반적으로 결정계수는 자연과학, 공학 분야의 연구에서는 70% 이상을, 인문사회과확에서는 30% 이상의 설명력을 요구한다. 
• 유의한 F값은 회귀모델이 잘못되었을 확률을 의미한다. 0.05보다 더 높아야 한다. 0.05보다 낮으면 유의미한 회귀모델이 아니다. (여기서는 0.05보다 작지만 연습하는 거니까 그냥 넘어가기)
• P값 중 0.05보다 작은 값은 무의미한 값. b값에 0값을 사용을 체크해야 함. 

 

③그래프와 수식 완성하기

 

 

3) 시계열 데이터 분석

시간의 흐름에 따라 변화하는 데이터에서 규칙을 찾아 다음 데이터 변화를 예측하고자 하는 분석

 

① FORECAST.ETS 함수 사용하여 예측

= FORECAST.ETS ( 예측하려는 날의 날짜, 과거 데이터 범위, 그 데이터 범위의 날짜, Seasonality, data_completion, aggregation)

 

• 시즈널리티: 계절성 구분하는 단위. 지금은 달마다 나누어져 있으니까 12로 하기. 안적으면 알아서 컴퓨터가 분석함. 그래도 틀릴 가능성 있으니까 알고있다면 입력하기
• 데이터 컴플리션: 빈 값(0값)이 갑자기 나왔을 때 정말 0값으로 계산 할 건지 예측치로 채워 계산할 것인지. 0은 0으로 계산하는 것 1은 예측치 채우는 것. 0값이 하나라도 있으면 가격이 크게 달라져서 1넣기
• 어그리게이션: 한 달에 두개 값이 있다면 어떻게 할 건지 합으로? 평균으로? 

② 그래프 만들기

[삽입]-[2차원 꺽은선형 그래프]