역시 LinEst 함수가 왕입니다요 - 두번째

지난 서울 아파트 관리비 총계 자료를 갖고 집단 간 차이 검정을 하련다.

25개 구를 모두 포함하긴 거시기 하니...
내 사는 노원구,
노원구와 비슷한 인구를 자랑하는 송파구.
(그러나 송파구는 재정자립도 상위에 속하는 반면 노원구는 재정자립도 하위에 속한다.)
그리고 평균이 가장 낮았던 성동구.
언제나 내 맘대로 선정 끝.

이제 각 구별로 무작위로 단지를 몇개 추출해 관리비 총계를 정리하면...

음...
무작위로 추출한 관리비 총계가 노원이 높게 나왔군. -_-
그러나 무작위 추출에 조작은 결코 없시요~

아무튼...
인자가 하나고 인자의 수준이 세 개인 일원배치법을 적용해
분산분석표를 작성하면...

위 결과를 얻기 위한 수식 중 주요한 것 몇개만 보면...

b22 셀 (처리 제곱합)
=SUMPRODUCT((B10:D10)*(B11:D11-E11)^2)

b23 셀 (잔차 제곱합)
=SUMSQ(IF(ISNUMBER(B3:D9),B3:D9-B11:D11))

b24 셀 (계 제곱합)
=SUMSQ(IF(ISNUMBER(B3:D9),B3:D9-E11))

e22 셀 (F비)
=D22/D23

f22 셀 (P값)
=FDIST(E22,C22,C23)

자유도 빼고 다 나열했군... -_-
b22, b23 셀은 배열수식.

어쨌든 결과는 P값이 유의수준 0.05 보다 작으므로 귀무가설을 기각.
따라서 구별 단지의 관리비 총계에는 차이가 있단 얘기 되겠다.

이쯤에서 "LinEst 함수는 언제 나오는겨???" 할 것이다.

계산의 편리를 위해 위에 자료 배치를 조금 조정한다.

이 경우 인자는 하나이며 인자의 수준이 세 개이기에 가변수를 세 개 만든다.
그리고 기존 자료를 적절히 구분해 나열하면 우선 준비 끝.
(예전 블로그 부터 꾸준히 봐왔다면 언젠가 본 듯한 양식일 것이다.)

이제 회귀분석을 돌려 분산분석표를 작성한다.
즉 각 구는 독립변수, 관리비 총계는 종속변수가 된다.

설마 앞서 구한 분산분석표 이름만 바꿔, 갖다 붙인거 아니여???
조크가 죽인다. ^^

위 분산분석표에서 LinEst 함수가 포함 된 셀만 보면...

i22 셀 (회귀 제곱합)
=INDEX(LINEST($K$2:$K$17,$H$2:$J$17,TRUE,TRUE),5,1)

i23 셀 (잔차 제곱합)
=INDEX(LINEST($K$2:$K$17,$H$2:$J$17,TRUE,TRUE),5,2)

j23 셀 (잔차 자유도)
=INDEX(LINEST($K$2:$K$17,$H$2:$J$17,TRUE,TRUE),4,2)

k23 셀 (잔차 평균제곱)
=INDEX(LINEST($K$2:$K$17,$H$2:$J$17,TRUE,TRUE),3,2)^2

l22 셀 (F비)
=INDEX(LINEST($K$2:$K$17,$H$2:$J$17,TRUE,TRUE),4,1)

왠만한 자리는 모두 LinEst 함수로 채웠다.
이제 됐나!!!

물론 이론이 있었기에 만들어 낸 함수지만,
LinEst 함수가 괜스레 왕인게 아니다.
(사실은 회귀분석이 왕이란 얘기.)

그래서 결론은 유의수준 0.05에서 귀무가설 기각.
적어도 한 넘은 회귀계수가 0이 아니란 얘기고 따라서 차이가 있다는 얘기.
설마 이때 귀무가설이 앞서 일원배치법에 적용된 귀무가설과 같다고 생각한다면... -_-;;

어쨌든 LinEst 함수를 사용하려면 엑셀 도움말에 나오는 아래 표를 숙지하고 있어야 한다.

위 처리에 사용된 분석모형을 통계에서는
일반선형모형(GLM, General Linear Model)이라 한다.
실험계획법을 회귀분석으로 처리한다는 발상, 열광에 열광이다. b(^_^)d

그러나 통계 프로그램이 없이 수작업으로 하려면
인자의 수, 인자 수준의 증가에 따라 가변수를 작성하는게 결코 만만치 않다는거~~~

그리고 이미 알고 있듯,
이 블로그에서 다루는 수준은 통개학 개론 겉핥기 수준이다.
따라서 일반선형모형에 대한 자세한 내용은 별도 확인이 필요하다는거~~~