회귀분석을 돌리면 필히 결정계수를 확인해야 하는데 엑셀에서는 크게 두 가지 방식으로 결정계수를 구할 수 있다. 막강한 LinEst 함수와 단순회귀분석용 RSQ 함수를 이용하면 된다.
아래 자료에 대해 몇 가지 모형을 가정해 추정치를 구해 보았다.
Trend 함수를 활용하면 아래와 같은 방식으로 손쉽게 추정치를 구할 수 있다.
E3 셀(Y=X³+X²+X):
=TREND($A$3:$A$9, $B$3:$B$9 ^ {3, 2, 1}, $B3 ^ {3, 2, 1})
=TREND($A$3:$A$9, $B$3:$B$9 ^ {3, 2, 1}, $B3 ^ {3, 2, 1})
그러면 결정계수는?
LinEst 함수를 이용해 구하는 방법은 여러 차례 소개했다.
E13 셀(Y=X³+X²+X):
=INDEX(LINEST($A$3:$A$9, $B$3:$B$9 ^ {3, 2, 1}, TRUE, TRUE), 3, 1)
=INDEX(LINEST($A$3:$A$9, $B$3:$B$9 ^ {3, 2, 1}, TRUE, TRUE), 3, 1)
그리고 RSQ 함수는 단순회귀모형에서 간편히 쓰는 함수이나 중회귀모형에서는 앞서 구한 추정치를 가지고 결정계수를 구한다. 물론 결과는 앞에서 구한 것과 같다.
E14 셀(Y=X³+X²+X):
=RSQ($A$3:$A$9, E$3:E$9)
=RSQ($A$3:$A$9, E$3:E$9)
단, 조건이 있다. 위의 방법은 가법모형에 사용하는 방식이고, 승법모형에서는 변수 변환을 해줘야 한다. 대표적 승법모형인 지수 회귀모형을 가정해 구하면,
추정치는 변수 변환 후 Trend나 Growth 함수로 구한다.
H3 셀(Trend):
=EXP(TREND(LN($A$3:$A$9), $B$3:$B$9, $B3))
=EXP(TREND(LN($A$3:$A$9), $B$3:$B$9, $B3))
I3 셀(Growth):
=GROWTH($A$3:$A$9, $B$3:$B$9,$B3)
=GROWTH($A$3:$A$9, $B$3:$B$9,$B3)
그리고 결정계수는 변수 변환 후 LinEst나 LogEst 함수로 구한다.
H13 셀(LinEst):
=INDEX(LINEST(LN($A$3:$A$9), $B$3:$B$9, TRUE, TRUE), 3, 1)
=INDEX(LINEST(LN($A$3:$A$9), $B$3:$B$9, TRUE, TRUE), 3, 1)
I13 셀(LogEst):
=INDEX(LOGEST($A$3:$A$9, $B$3:$B$9, TRUE, TRUE), 3, 1)
=INDEX(LOGEST($A$3:$A$9, $B$3:$B$9, TRUE, TRUE), 3, 1)
승법모형에서 RSQ 함수를 이용해 결정계수를 구할 때에는 종속변수뿐만 아니라 추정치에도 변수 변환이 필요하다.
H14 셀:
=RSQ(LN($A$3:$A$9), LN(H$3:H$9))
=RSQ(LN($A$3:$A$9), LN(H$3:H$9))
분산형 차트에서 추세선으로도 결정계수를 구할 수 있다. 그러나 이렇게 구한 결정계수는 정확도가 떨어진다는 단점이 있다.
