엑셀을 이용해 간단하게 회귀분석을 할 수 있다. LINEST 함수는 선형 회귀 분석을 할 때 사용하는 함수이다. 이 함수를 이용하면 엑셀 추세선 기능에 있는 모델뿐만 아니라 내가 직접 만든 괴상한 함수도 쓸 수 있다.
1. LINEST 함수 사용법
가지고 있는 데이터에서 출력(known y's)과 입력(known x's)를 넣어 주면 아래의 선형 결합 모델을 가정해 각 입력에 해당하는 계수를 돌려준다.
1.1. 가정하는 모델
$$y = m_1x_1 + m_2x_2 + m_3x_3 + \cdots + m_{n-1}x_{n-1} + m_nx_n + b$$
1.2. LINEST 함수 사용법
=LINEST(known_y's, [known_x's], [const], [stats])
여기에서 known_y's는 데이터에서 출력 또는 반응(responses), known_x's는 입력 또는 변수(variables)에 해당한다. [const]는 상수를 직접 계산할 것인지 말 것인지를 알려주는 것으로 [True, False] 또는 [1, 0]으로 입력한다. [stats]는 추가 회귀 통계 값을 줄 것인지 말 것인지 입력하는 것으로 마찬가지로 [True, False] 또는 [1, 0]으로 입력한다.
1.3. LINEST 결과
1. 영역 Drag (가로 변수갯수+1, 세로 5줄)
2. =LINEST() 입력
3. CTRL + SHIFT + ENTER 입력
그냥 셀에 LINEST 결과를 받으면 1개 값만 주게 되는데 전체 LINEST가 돌려주는 배열 형식의 결과에서 {1,1} 위치에 해당하는 값만을 돌려준다. 따라서 셀 영역을 먼저 드래그하고 등호를 누른 뒤 LINEST 함수를 입력한 후 <CTRL+SHIFT+ENTER>를 치면 배열 결과를 보여준다.
결과는 셀은 그림 2와 같은 같은 의미가 있다. 주의할 점은 known_x's의 순서는 각 열의 계수가 $ b, m_1, m_2 ... m_{n-1}, m_{n} $에 해당되어 뱉어주는 결과와 순서가 반대이므로 나중에 사용할 때 쪼끔 주의할 필요가 있다.
결과 배열에서 각 셀은 다음 의미를 갖는다.
표 1. LINEST 결과 배열의 의미
| 값 | 의미 |
| $b$ | 상수항 |
| $m_n$ | $x_n$의 계수 |
| $se_n$ | $m_n$에 대한 오차 |
| $R^2$ | 결정 계수 |
| $se_y$ | $y$의 오차 |
| $F$ | F-통계량 |
| $df$ | 자유도 |
| $SS_{reg}$ | 회귀 제곱 합 |
| $SS_{residual}$$ | 잔차 제곱 합 |
2. 실험 데이터의 예
아래 데이터는 시뮬레이션을 이용해 수집한 데이터이다. 변수는 직경(diameter)과 높이(height)이고 측정 결과는 부피(volume)와 면적(area)이다. 각 변수는 3 수준으로 실험했고 총 경우의 수는 18가지이다. 직경과 높이가 달라짐에 따라 부피와 면적이 어떻게 변하는지를 여러 가지 경우에 대해 측정했으니 이제 이 데이터를 이용해 회귀분석을 하면 변수에 따라 부피와 면적이 얼마나 민감하게 반응하는지 알 수 있고 잘 맞는 모델을 만들면 임의의 직경과 높이에서 부피와 면적이 어떻게 결정될지 예측할 수 있을 것이다.
3. LINEST를 이용한 회귀 분석
3.1. 그래프 추세선 기능 이용
그래프를 이용해 추세선을 그려보면 아래처럼 추세를 확인할 수 있다. 이를 통해 부피와 면적이 직경과 높이에 대해 어느 정도 민감도를 갖는지 알 수 있다. 그러나 그래프에 있는 기능으로는 각각의 변수에 대한 분석만 가능할 뿐 다변수 함수의 회귀 분석을 하지 못하기 때문에 이렇게 변수가 2개 이상일 때는 LINEST 함수를 직접 이용해서 계수를 구하는 것이 좋다.
3.2. LINEST 이용
위 예제를 LINEST를 이용해서 선형 추세선을 구해보기로 한다. 아래 붉은색 영역이 known_y's 영역이고 파란색 영역이 known_x's 영역이다. Area에 대해 한 번, Volume에 대해 한 번, 각각 선형 회귀 분석을 해야 한다.
3.3. 회귀 분석 결과
아래와 같이 회귀 분석 결과를 얻었다. 변수의 순서와 LINEST 결과의 순서가 반대라는 점을 주의하자. 각 변수의 계수가 그래프에서 구한 추세선의 기울기와 같다는 것을 알 수 있다. 이 정도로도 결정계수가 0.97이상으로 아주 잘 맞는 모델임을 알 수 있다. 그렇지만 시뮬레이션으로 3 수준 변수를 가지고 얻은 데이터이므로 더 좋은 결과를 얻을 수 있을 것만 같은 느낌적인 느낌이 드는 것이다.
회귀 분석을 통해 얻은 결과는 다음과 같다.
\begin{align} V(d, h) &= 40.58 h + 137.44 d -962.11 \\\\ A(d, h) &= 21.99 h + 50.27 d -311.28 \end{align}
3.4. 복잡한 함수의 회귀 분석
이번에는 2차 항까지 해보자. 2차항 까지 하려면 아래처럼 이차항을 계산해 놓고 $X = x^2$으로 두고 새로운 변수처럼 사용하면 된다. 결과에서 얻는 새로운 변수 $X$의 계수가 곧 2차항 $x^2$의 계수가 된다.
회귀 분석 결과는 그림 8과 같다. 부피에 대해 $h^2$ 항과 $d$항의 민감도는 거의 0에 가깝다. 면적에 대해서는 $h^2$ 항의 민감도가 거의 없다고 볼 수 있다. 따라서 각 항은 모델에서 없애 버리고 부피는 $V(d^2, h)$로, 면적은 $A(d^2, h, d)$의 함수로 생각할 수 있다.
회귀 분석을 통해 얻은 결과는 다음과 같다.
\begin{align} V(d, h) &= 9.82 d^2 + 40.58 h -507.24 \\\\ A(d, h) &= 0.79 d^2 + 21.99 h + 39.27 d -274.89 \end{align}
3.5. 진짜 한 번만 더
이제 각 함수에 변수가 미치는 민감도를 알았으니 한번 더 이 변수들의 조합으로 이루어진 회귀분석을 해본다. $d^2h$ 항과 $dh$항을 추가해 결과를 확인해보자. 아래처럼 결정계수가 1이 되어 아주 만족스러운 결과를 얻을 수 있다. F 통계량도 아주 커져서 우연히 맞은 결과도 아닐 것이라고 추측해볼 수 있다.
회귀 분석을 통해 얻은 결과는 다음과 같다. 계수를 보니 뭔가 느낌이 온다.
\begin{align} V(d, h) &= 0.7854 d^2h \\\\ A(d, h) &= 0.7854 d^2 + 3.1416dh \end{align}
4. 그래서 어쩌라고..
사실 이 데이터는 이미 알고 있는 함수를 이용해 계산한 것이다. 회귀분석을 통해서 역으로 모델을 찾아가는 방법을 알아보았던 것이다. 실제로는 알려지지 않은 블랙박스를 우리가 가정한 모델과 잘 맞는지 확인하는 경우가 많을 것이다. 위의 예에서도 완벽하게 정확한 모델이 아닌 1차 근사나 2차 근사한 모델을 사용하더라도 0.99에 가까운 결정계수를 갖는다는 것을 알 수 있었다. 이 모델을 우리가 근사한 구간 안에서만 사용한다면 아주 적은 오차로 쓸만한 결과를 얻을 수 있을 것이다. 가능하다면 실험점 외의 다른 점으로 실험을 해보고 모델이 잘 예측하는지 교차 검증을 하는 것이 좋다.
\begin{align} V(d, h) &= \frac{\pi}{4} d^2h \\\\ A(d, h) &= \frac{\pi}{4} d^2 + \pi dh \end{align}
최근댓글