엑셀에서 이차회귀분석(Quadratic Regression) 하기

회귀분석(regression)은 서로 다른 두 변수 간에 관계를 알아보기 위해서 사용한다. 가장 흔한 분석 방법은 선형 회귀(linear regression)로 원인 변수(explanatory variable)와 반응 변수(response variable)에 관계가 선형에 있을 때 사용한다.

 

선형에 관계에 원인 변수(explanatory variable)값이 증가하면, 반응 변수(response variable) 값도 따라서 증가하고, 또 감소하면 따라서 감사하게 된다. 

 

하지만, 때때로 원인 변수(explanatory variable)와 반응 변수(response variable)에 관계가 비선형(non-linear) 관계에 있을 수 있다. 비선형 관계 중 가장 대표적인 예가 이차 관계(quadratic relationship)이다. 이차 관계(quadratic relationship)는 U자에 커브를 그리거나, 뒤집어진 U자 커브를 그린다. 

 

이차 관계(quadratic relationship)에서 처음에는 원인 변수(explanatory variable)값이 증가하면, 반응 변수(response variable)도 증가하다가. 어떤 지점을 지나는 시점에서 원인 변수(explanatory variable)값이 증가하면, 반응 변수(response variable)는 감소하게 된다. 또는 원인 변수(explanatory variable)값이 증가하면, 반응 변수(response variable)는 감소하다가, 어떤 지점을 지나는 시점에서 증가하게 된다.

 

예를들어 직장에서 근무 시간과 직장에 만족도 사이에 관계를 구하고자 할 때 이차 회귀분석(Quadratic Regression)을 할 사용할 수 있다. 아마 일정 구간까지는 일을 하면서 만족도가 올라갈 것이다. 하지만 일정 시간을 지나게 되면, 피로감이 몰려오고 그때부터는 시간이 지날수록 만족도가 감소할 것이다. 이러한 경우 선형 회귀(linear regression)보다 이차 회귀분석(Quadratic Regression)이 더 유효한 분석 모델이다.

 

이차회귀분석(Quadratic Regression)을 시행해본다.

엑셀에서 이차회귀분석(Quadratic Regression) 하기

근무 시간과 직장에 만족도에 대한 16명에 직원에 대한 설문 데이터가 아래와 같이 나와있다. (직장 내 만족도는 0-100)

1단계: 분산형 그래프 그려보기

삽입 > 차트 > 분산형(X,Y) 또는 거품형 차트 삽입에서 분산형을 선택한다.

 

이와 같은 '근무시간'과 '만족도'는 비선형(non-linear)이면서, 뒤집어진 U 자형 관계임을 확인할 수 있다. 그러므로 이차 회귀분석(Quadratic Regression) 모델로 분석하는가 적절한 예시다.

 

2단계: 원인 변수(explanatory variable)에 대한 제곱 값을 입력한다.

하나에 열(column)을 더 만들어서 원인 변수(explanatory variable)에 대한 제곱 값을 입력한다.

 

 

C열을 선택하고 ctl++ 키를 눌러서 하나열을 왼쪽에 추가한다. 그리고 제곱 값을 각 칸에 입력한다.

데이터 분석 툴에 들어간다. 

데이터 분석 툴이 없다면 여기서 다운 받는다.

 

데이터 분석 툴에 들어가서 회귀 분석을 클릭한다.

그리고 아래와 같이 Y와 X 값을 넣고, 이름표에 체크표시한 후, 출력 셀을 지정해준다.

확인을 누르면 아래와 같은 분석 결과가 출력된다.

3단계: 결과 해석하기

이제 주요 결과물들을 하나하나 해석해본다.

 

• 결정계수(R²): 0.9092, R²라고 쓰고, 결정계수(coefficient of determination)이다. 이 값은 원인 변수(explanatory variable)로 설명 가능한 반응 변수(response variable)의 변동성을 가리킨다. 예를 들어 지금 예시에서, 90.92%의 '만족도(y)'에 변동성이 '근무 시간(x)'과 '근무 시간^2(x^2)'로 설명 가능하다. 1로 갈수록 더 정확한 예측이 가능해진다.

 

• 표준오차(standard error): 5.366, 이 값은 관측값과 예측 모델 값 사이에 오차들에 평균이다. 예를 들어, 지금에 이차 회귀분석 모델에서 관측된 만족도와 생성된 이차회귀분석 모델의 값 사이에 오차는 평균 9.159 만큼 떨어져 있다. 

 

• F 비: 65.09, 이차회귀분석 모델의 F 통계량(F statistics)이다. 이 값은 회귀 모델이 값을 예측할만한지 아닌지 나타낸다. 여기서 F 통계량은 65.09 그리고 상응하는 p-값(p-value)은 < 0.0001보다 작다. 설정했던, 유의 수준에 알파 값 0.05 보다 매우 작기 때문에 회귀 모델은 유의미하며 사용 가능하다.  회귀 제곱 평균(MSR) / 잔차 제곱 평균(MSE), 5898.85/90.62 = 65.0

 

• 계수(Coefficients): 계수는 회귀 모델 방정식에 필요한 계수를 제공한다. 지금에 예제에서 선형 모델에 값은

y = b0 + b1*x1 + b2*x1 ²

지금에 예에서 회귀 모델 방정식은 

 

만족도 = -30.252 + 7.173*(근무 시간) - 0.106*(근무 시간) ²

 

위 방정식으로 근무시간에 대해, 얼마만큼 만족도가 나오는지 예측할 수 있다. 예를 들어 30시간 근무했을지 만족도는 88.649가 나온다.

 

만족도 = -30.252 + 7.173(30) -0.106(30) ²² = 88.649