트렌드 직선의 비밀(선형회귀) 엑셀실습 #2

이 글은

2022.05.13 - [엑셀] - 트렌드 직선의 비밀(선형회귀) 엑셀실습 #1

트렌드 직선의 비밀(선형회귀) 엑셀실습 #1

에서 이어집니다.  굳이 차트를 그리지 않고 추세선을 산출해 내는 방법을 알아보겠습니다.

$n$개의 데이터 $(X,Y) = (x_1,y_1) ,\cdots , (x_n,y_n)$ 이 있다고 합시다. 그리고 이들 데이터를 가장 잘 설명하는 추세선을 $y=ax+b$ 라 합시다. 

 

• 방법1. 아주 기초적인 접근

그러면, 여기에서 다룬 내용에 따라

$$\begin{align} a &= \frac{\mathbb{E}(XY)-\mathbb{E}(X)\mathbb{E}(Y)}{\mathbb{E}(X^2)-\mathbb{E}(X)^2} \tag{1} \\ b & = \mathbb{E}(Y)- a\mathbb{E}(X) \tag{2} \end{align}$$

 

입니다. $\mathbb{E}$는 평균이니, 엑셀의 AVERAGE() 함수를 쓰면 될 것 같습니다. 식을 보니, 필요한것들은

$$\mathbb{E}(X), \mathbb{E}(Y), \mathbb{E}(X^2), \mathbb{E}(XY)$$

의 값이네요.

데이터 $X^2$과 $XY$를 얻기 위해 위와 같이 새로운 column (D,E)를 만들어서 수식대로 만들어 줍니다. 그러면 결과는

위와 같이 됩니다. 

위와 같이 AVERAGE() 수식을 걸어서 B~E열의 평균값 셀에 복사해 주면

 

위와 같이 되고, 차례대로 $\mathbb{E}(X), \mathbb{E}(Y), \mathbb{E}(X^2), \mathbb{E}(XY)$의 값이 됩니다.

기울기 $a$를 수식에 맞게 구하고(수식(1)),

$y$절편인 $b$도 구합니다.(수식 (2)). 그러면 최종적으로 다음의 결과를 얻습니다.

---

• 방법2. 공분산, 분산을 이용한 방법

수식(1)은 다음과 같이 공분산과 분산을 이용하여 쓸 수 있다고 하였습니다.

$$a=\frac{\rm{COV}(X,Y)}{\mathbb{V}(X)}$$

$b$ 구하는 식은 동일하므로 생략하겠습니다.

엑셀에는 공분산과 분산을 구하는 내장함수가 있습니다. 바로 COVARIANCE.P 와 VAR.P입니다. 참고로, COVARIANCE.S와 VAR.S 도 있는데 이는 표본집단에 대한 공분산과 분산을 의미합니다. (여기를 참고하세요). 그런데 지금 추세 분석은 주어진 $n$개의 데이터가 전부인 상황이므로 모집단으로 간주하여 분석을 해야 합니다. 따라서 .P가 붙은 함수를 쓰는게 맞습니다.

우선 공분산을 구하고,

분산을 구합니다. 그리고 최종적으로 기울기 $a$를 구하면

과 같이 됩니다. 방법1과 비교해 보기 바랄게요.

---

• 방법3. 극강의 FANCY함: SLOPE함수와 INTERCEPT 함수

주어진 데이터를 설명하는 추세직선을 구하는 문제는 굉장히 널리 쓰이고 유명한 방법입니다. 엑셀이 내장함수로 제공할 가능성이 높겠죠. 엑셀 함수는 다음과 같습니다.

기울기(slope)	=SLOPE(known_y's, known_x's)
$y$절편(intercept)	=INTERCEPT(known_y's, known_x's)

직접 구해보겠습니다.

이렇게 간단하게 기울기를 구하고($y$배열부터 input으로 입력해야 합니다.)

$y$절편(intercept)를 구합나니다. 방법1,2와 결과를 비교해 보기 바랍니다.

 

다음 글에서는 $R^2$값을 직접 엑셀에서 구해보도록 하겠습니다.