추세선의 기울기와 절편, R^2 구하는 방법

현재 가지고 있는 데이터를 바탕으로 내삽(interpolation)과 외삽(extrapolation)을 통해서 데이터들 사이에 있는 값이나 데이터의 범위를 벗어난 영역의 값을 추정할 수 있습니다.

 

연구실 근무 시절에는 시그마 플롯이나 다른 통계 프로그램들을 많이 사용했지만, 이를 간단하게 엑셀로도 할 수 있는데, 매우 간단하지만 의외로 모르는 분들도 있기에 공유해 봅니다.

 

예를 들어서 우선 아래와 같이 어떠한 실험을 하였는데 시간에 따른 온도 데이터가 있다고 해봅시다. 엑셀에 정리된 데이터입니다.

 

추세선을 그릴 실험 데이터

 

데이터를 그래프로 표시하면 아래와 같이 됩니다. 여기서 데이터 부분을 클릭한 다음에 우클릭을 해보면 추세선 추가라는 메뉴를 볼 수 있습니다. 여기를 클릭합니다.

 

그래프에 데이터 선택 후 우클릭 - 추세선 추가

 

그러면 추세선 서식이 나옵니다. 이 경우에 누가 봐도 선형의 상관관계를 가지는 것이 타당하므로 선형을 선택하면 됩니다. 만약 2차나 3차 다항식이 더욱 적합하거나, 지수, 로그 등 기타 데이터에 대해 이미 알려진 정보가 있으면 거기에 맞게 옵션을 선택해 주면 됩니다.

 

추세선 옵션

 

여기서 수식을 차트에 표시에 체크, R-제곱 값을 차트에 표시에 체크해 주면 그래프에 해당 수식과 R^2값이 표기가 됩니다. 만약에 x축의 값이 0일 때 가지는 값을 이미 알고 있거나 원점을 지나야 한다면 절편에 체크를 하고 필요한 값을 넣어주면 됩니다.

 

아래와 같이 선형 추세선의 수식과 R^2값이 그래프에 나타납니다.

 

선형 추세선이 표현된 그래프

 

선형 추세선의 기울기는 0.1536, 절편은 1.0242이고 R^2값은 0.9979입니다. 이제 시간이든 온도든 예측값은 수식을 통해서 쉽게 구할 수가 있겠죠. 참고로 이 값들은 엑셀에서 추세선을 그리지 않고 함수로도 구할 수가 있습니다. 아래 함수를 이용해서 값을 구해보면 추세선 서식에서 구한 값과 동일한 값이 구해지는 것을 쉽게 확인이 가능합니다.

 

• 기울기 : Slope 함수 (Slope(Known_y's, Known_x's)
• 절편 : Intercept 함수 (Intercept(Known_y's, Known_x's)
• R^2 : RSQ 함수 (RSQ(Known_y's, Known_x's)

 

그리고 여기서 Forecast.linear(x, Known_y's, Known_x's) 함수를 이용하면 선형 추세선에서 별도로 값을 넣어서 계산하지 않아도 수식으로 예측값을 손쉽게 찾을 수가 있습니다.