[Excel] 엑셀 다중회귀분석

풀이과정) 매출액(종속변수)과 개인소득, 공급자의 임금, 합계 가격, 예산 투자액, 광고비 , 판매비용, 전체사업 광고비의 인과관계를 선형회귀식을 통하여 알아보자. 그리고 몇가지 상황에 대하여 다음 기간의 판매액을 예측해 보자.
 
자료를 엑셀시트에 작성하고 엑셀메뉴표시줄에서 <도구>- <데이터분석>-<회귀분석>을 선택하고 [확인]을 누른다.
 
[확인]을 누르면 [회귀분석]대화상자가 나타난다. 회귀분석대화상자는 입력, 출력, 잔차, 정규확률로 크게 4가지로 나누어져 있으며 입력란에는 주어진 X,Y의 범위를 입력할 수 있다. 먼저[Y축입력범위]에 <셀B1:B39>까지 입력한다. 이때 워크시트상에서 마우스를 사용해서 범위를 입력하거나 아니면[=$B$1:$B$9]라고 직접 입력해도 된다. [X축 입력범위]에는 <셀 C1:J39>까지 입력한다.

이름표를 선택하면 회귀분석 결과를 엑셀시트에 나타낼때 첫번째 행을 선택한다. 예를 들면 이 예제의 경우 이름표를 지정하면 <셀 B1:J1>까지의 이름을 선택하게 된다. 그리고 회귀분석 결과가 제시될때 독립변수의 이름이 나타난다. 신뢰수준을 결정하는 부분에서는 일반적으로 받아들여지는 신뢰수준인 95%를 지정한다.
출력옵션을 지정하는 부분에서는 새로운 워크시트를 지정한다.
잔차부분에서는 잔차를 분석하기 위해서 잔차,잔차도, 표준잔차, 선적합도를 지원한다. 잔차, 잔차도,선적합도의 결과를 알기 위해서는 이것을 모두 선택하면 된다. 그리고 마지막으로 정규확률도를 지정한다.
 
[확인]단추를 누르면 새로운 시트가 만들어 지면서 결과가 제시된다.
 
 
결과분석) 결과분석할 때에는 " t-값"을 이용하는 방법과 "P-값"을 이용하는 방법이 있다. 일반적으로는 계산의 간편성 때문에 "P-값"을 믾이 이용하나 정확한 분석을 위해서는 t-값을 이용하는 것이 올바른 방법이다. 일반적으로 t-검정을 할때에는 95% 신뢰도를 가정한다. 즉, 유의수준을 5%로 본다.
이와같은 가설을 검정하는 것은 곧 양측검정을 의미하기 때문에 대개 t-값의 절대치가 2와 비슷하거나 크면 이때의 변수는 종속변수에 영향을 미치게 된다.
예제의 결과를 살펴보면 독립변수의 t-값이 <셀D17:D25>까지 입력되어 있다. 여기서 X2, X4, X7, X8변수의 t-값이 모두 2의 절대값보다 작은 값을 가진다. 따라서 X2, X4, X7, X8변수는 판매액에 별다른 영향을 주지 못한다고 분석할 수 있다.
매출액에 별다른 영향을 주지 않는 변수들을 파악하였으므로 이번에는 이 변수들을 제외한 나머지 변수들을 고려하여 Y와 X1, X3, X5, X6의 변수와의 선형관계를 조사해야 한다.
 
 
먼저, 새로운 워크시트에 Y와 X1, X3, X5, X6의 자료를 작성한다. 앞에서와 같은 방법으로 <도구>-<데이터분석>-<회귀분석>을 선택하고 회귀분석 대화상자에 자료의 범위를 아래의 화면의 결과처럼 입력한다. 이번에는 잔차의 결과를 보기 위해 모두를 선택한다.
 
자료의 입력이 끝나고 [확인]단추를 누르면 새로운 회귀분석의 결과가 제시된다. 도한 잔차의 분석결과 제시된다.
 
 
결과를 보면 95% 신뢰도에서 X1, X3, X5, X6의 변수들의 t-값이 2의 절대값보다 큰 값을 가지고 있으므로 유의한 결과를 나타낸다고 설명할 수 있다. 즉, X1, X3, X5, X6의 변수들이 매출액에 영향을 미치고 있다는 뜻이다. 잔차를 분석하기 위해서는 열과 행의 폭을 조정하면 잔차, 잔차도, 선적합도 등의 결과를 자세히 볼 수 있다.
 
 
 
▶ 이번에는 회귀식을 추정해보자.
회귀식은 매출액(Y)과 매출액에 영향을 준다고 분석된 독립변수(X1, X3, X5, X6)들로 표현된다. 이때 추정회귀선을 나타내기 위해 사용되는 회귀계수는 <셀B17:B21>까지 입력되어 있다.
따라서 추정회귀식은 "매출액(Y)=3276.207+(5.695*개인소득(X1))+(-15.17*가격(X3))+(1.55*투자(X5))+(7.57*광고비(X6))이다.
 
 
▶ 다음으로 위의 추정회귀식을 가지고 앞으로의 판매액을 예측할 수 있는지 살펴보자.
먼저 1999년도의 변수들의 조건이 아래 표에 제시되어 있다.
 
 
엑셀 시트에 <셀 D3:F6>까지 위의 표를 작성하고 마우스를 이용하여 <셀D4.를 지정한다. 1999년도 상반기의 매출액을 계산하기 위해서는 Y절편 X1, X3, X5, X6의 회귀계수가 필요하다.
 
 
 
<셀D4>에 다음의 식을 입력한다.

"$B$17 + $B#18*E4 + $B$19*F4 + $B$20*G4 + $B$21*H4"
위의 식을 살펴보면 회귀계수를 나타내는 <셀 B17, 셀 B18, 셀 B19, 셀 B20, 셀 B21>에 모두 "$"표시가 되어있다. 이표시는 셀을 지정한다는 의미로서 식의 다른 셀들의 깂이 변해도 "$"표시로 지정되어 있는 셀은 변하지 않는다는 뜻이다. 이것을 "절대참조"라고 한다. 이처럼 절대참조를 하는 이유는 수식을 복사할 때 해당셀을 변하지 않게 하기 위해서이다. 값이 계산되면 <셀 D5>가지 자동채우기를 실시하면, 쉽게 계산이 된다.
 
 
결과적으로 도출된 추정회귀식"Y=3276.207+5.695X1-15.17X3+1.55X5_7.57X6"에서 1999년도 매출액을 추정하면 상반기에는 5459.324, 하반기에는 5555.523인 것을 알 수 있다.