엑셀(EXCEL) 선형관계에서 결정계수와 상관계수의 뜻, 상관성의 의미(R2의 사용방법, Correl 함수)

관측되는 2개의 데이터가 있다면, 이 둘의 관계를 파악합니다.

두개의 데이터의 선형성의 예시가 있습니다.

 

 

두 개의 집단이 선형 관계를 가질 때 "기울기"와 "절편"을 가지는 직선으로 그릴 수 있습니다.

이 두 개를 x, y를 이용해서 나타내겠습니다.

 

기울기(a)와 절편(b)의 정의

 

그리고 이 선형관계의 정도를 나타내는 수가 있습니다.

두 개의 수의 관계에 대해서 엑셀에서는 2가지 방법을 제공합니다.

그래프를 그려서 R2 값을 추출하거나, Correl 함수로 관계를 구하는 것입니다.

R2를 결정계수, CORREL 함수를 상관계수라고 부릅니다.

결론적인 값은 둘 다 비슷합니다. 하나씩 알아보겠습니다.

 

320x100

 

 

1. 결정계수 R2

 

R2는 추세선을 그려서 뽑아내거나, LINEST 함수를 이용해서 구할 수 있습니다. 

(구하는 법 : 엑셀(EXCEL) Linest 함수와 추세선 그래프 상관성(R2) 데이터 분석하기)

구해진 기울기 a와 b에 y를 다시 적용하면 "예측된 값"인 ys를 적용할 수 있습니다.

 

 

예측된 값과 실제 y값의 차이의 제곱을 구합니다.

그리고 이 합을 ssresid라고 합니다.

 

 

그리고 관측된 y와 y의 평균의 차이의 제곱을 합한 sstotal을 구합니다.

 

 

이제 R2를 구할 수 있습니다.

 

 

r2는 제곱값들의 연산임으로 항상 양수입니다.

y와 y 평균들을 차이와 y와 y예측값을 비교하는 함수입니다.

1에 가깝다면 예측값이 실제 y에 거의 같아 짐으로 둘  사이이의 관계가 높은 것으로 볼 수 있습니다.

데이터가 얼마나 선에 가까운지 보여주는 함수입니다.

 

 

 

2. CORREL 함수

 

CORREL(array1,array2) : 두 개의 범위의 상관계수를 구하는 함수입니다.

 

이 함수의 공식은 아래와 같습니다.

 

 

X의 편차들과 Y의 편차들의 차이의 곱을 X, Y의 RMS의 곱으로 나눈 값입니다.

이 값은 각각의 변화량의 추세가 비슷하면 1에 가까워지고 아니면 점점 작아집니다.

불규칙도가 높아지면 0에 가까워 집니다.

 

분모는 제곱항이고, 분자는 그대로 임으로 음의 상관관계일 때는 음수가 출력됩니다.

1이나 -1에 가깝다면 상관성이 매우 높은 것입니다.

선형성과 데이터들의 차이를 동시에 보여주는 함수입니다.

 

---

 

구하는 방식에 따라 디테일한 값은 다를 수 있습니다.

상관계수와 결정계수는 둘다 두 데이터간의 관계가 있는지 없는지를 수치화 합니다.

그래서 사용용법이 거의 같습니다.

그래서 유사한 값이 나오고 1에 가까울수록 높은 상관성을 보여줍니다.