엑셀(EXCEL), 데이터들의 추세선을 만들어서 선형성과 상관계수를 보기(추세선 보기와 구하는 함수 : SLOPE, INTERCEPT, CORRE)

목차

1. 그래프에서 추세선 보기

2. 선형예측 방정식

3. 선형 예측 함수(SLOPE, INTERCEPT, CORREL)

 
인자 두개의 관계를 구하는 함수로는 대표적으로 LINEST가 있습니다.
(엑셀(EXCEL) Linest 함수와 추세선 그래프 상관성(R2) 데이터 분석하기)
배열함수가 다루기 어려울 수도 있어 선형성만 따로 보는 방법이 있습니다.
 

1. 그래프에서 추세선 보기

 
선형예측 추세선을 그리는 방법을 알아보겠습니다.
 
① 분산형 그래프를 그립니다.
(X,Y 데이터가 따로 들어가는 분산형 그래프에서 추세선을 그려야 정확합니다.)
② 추세선을 그리는 것은 "표선택" - [상단 매뉴] - [챠트 디자인] - [차트 요소 추가] - [추세선] - [선형 예측] 에 있습니다.
③ 데이터를 따라가는 추세선을 그려집니다.
 

2. 선형예측 방정식

 
선형예측 모델의 방정식이 표에 표시됩니다.

이 때 표시되는 값는 y = ax + b 형식의 식과 결정 계수 혹은 RMS 값이라고 불리는 R2가 있습니다.
추세선 서식에서 이 값을 차트에 표시할 것인지 설정할 수 있습니다.
추세선을 "선형 회귀선"이라고 부를 수 있는데, 이 선의 기울기인 "a"와 절편인 "b"가 있으면 분석이나 예측이 가능합니다.
그리고 결정계수 R2는 X와 Y가 얼마나 관련이 있는지 이 "a,b"값을 얼마나 믿을 수 있는지를 나타냅니다.
1이면 같은 그래프 수준의 신뢰도를 가지고 0이면 전혀 믿을 수 없습니다.
기대하고 있는 정밀도 수준에 따라 다르겠지만, 0.95이상이면 믿을만 하고 0.7이하면 데이터를 예측할 수 없습니다.

3. 선형 예측 함수

 
예측 방정식을 표시하는 함수가 있습니다.
기울기를 나타내는 SLOPE, 절편을 나타내는 INTERCEPT, 상관계수를 나타내는 CORREL입니다.
추세선에선에서는 결정계수 R2로 나타납니다. 이건 Linest 함수로 구할 수 있습니다.

Y = a X + b
Y = "SLOPE" X + "INTERCEPT"

 
SLOPE(known_y's, known_x's) : 선형 회귀선의 방정식상의 기울기를 구합니다. 주어진 값의 그래프 범위에서 Y변화 / X변화로 추가적인 DATA가 주어질 경우 변할 수 있습니다.

• known_y's : 종속 데이터 요소의 셀 배열 또는 범위입니다.
• known_x's : 독립 데이터 요소의 집합입니다.

 
INTERCEPT(known_y's, known_x's) : 선형 회귀선의 방정식상의 절편을 구합니다. 이는 y=ax+b 형식의 선형 회귀방정식에서 x=0일 때의 값이 됩니다.

• known_y's : 관측값이나 데이터의 종속 변수 집합입니다.
• known_x's : 관측값이나 데이터의 독립 변수 집합입니다.

 
CORREL(array1,array2) : 두개의 데이터 집단의 상관계수를 반영합니다. 양수관계의 경우에는 1, 음수관계의 경우에는 -1에 가까울수록 관련이 깊은 DATA입니다. 0에 가까울 수록 두 데이터 집단은 상관이 없습니다.

• array1 : 셀 값의 범위입니다.
• array2 : 셀 값의 두 번째 범위입니다.
• 1에 가까울수록 상관성이 올라가기는 하지만 추세선이나 LINEST와 다른 방법으로 계산하여 값이 다릅니다.
• 범위를 A(인자 a, 평균 Ā), B의 상관인자를 구하는 방법은 아래와 같습니다.