[엑셀 통계] 42. 엑셀에서 피어슨 상관분석을 하는 세 가지 방법

도구 엑셀로 푸는 통계

42. 엑셀에서 피어슨 상관분석을 하는 세 가지 방법

---

상관분석은 두 변수 사이의 선형관계의 정도를 분석하는 방법입니다. 상관계수 r과, 유의확률 p가 계산됩니다.

상관분석은 크게 둘로 나뉩니다. 정규분포 가정 가능 여부에 따라 모수적 방법과 미모수적 방법입니다.

모수적방법에는 피어슨상관분석이 있고, 미모수적 방법에는 스피어만상관분석, 캔달의 타우 상관분석이 있습니다.

모수적 방법 : 피어슨
비모수 : 스피어만, 캔달의 타우

엑셀에서는 피어슨 상관분석만을 제공합니다. 스피어만이나 캔달의 타우는 이후 강의에서 직접 수식을 정의하여 사용하겠습니다.

엑셀에서 피어슨 상관분석을 하는 방법은 세가지가 있습니다. 산점도의 추세선을 이용하는 방법, CORREL 이라는 함수를 이용하는 방법, 데이터 분석도구를 이용하는 방법입니다. 하나씩 알아봅시다.


1) 산점도의 추세선 이용

오늘 사용할 데이터는 아래와 같습니다. 42 상관분석.xlsx

A와 B데이터를 선택하고 [삽입]탭의 분산형그래프를 클릭합니다.

 

아래와 같이 그래프가 그려집니다.

 

 

데이터에 우클릭 후 추세선추가로 들어갑니다.

선형,수식을차트에표시,R제곱값을 차트에 표시에 체크합니다.

 

 

여기서 구해진 R제곱 값이 상관계수 R을 제곱한 값입니다. 이 값에 루트를 씌우면 상관계수를 얻을 수 있습니다.

0.1327이 구해졌으므로, 루트를 씌우면 0.3642 입니다.

 

2) CORREL 함수 이용

CORREL함수에 두 데이터를 입력합니다.

 

 

아래 결과를 얻습니다.

3) 데이터분석 도구 이용

[데이터] 탭의 [테이터 분석]을 클릭합니다.

상관분석을 선택하고 입력범위에 A와 B데이터를 입력합니다. 첫째 행 이름표 사용을 체크합니다.

출력범위에 원하는 위치를 선택합니다.

결과는 아래와 같습니다. 

데이터분석 도구를 이용할 때의 장점은 상관계수 행렬을 출력해준다는 것입니다. 상관계수 행렬은 변수가 3개 이상일 때, 모든 변수 조합에서 가능한 상관계수를 모두 출력해줍니다. 확인을 위해 데이터 A,B,C 모두를 선택하고 한번 더 상관분석을 해봅시다.

위와 같이 모든 조합의 상관계수를 출력해줍니다.

엑셀 상관분석에는 비모수적 방법이 없는것 이외에도 한가지 한계가 더 있습니다. p값을 출력해주지 않는다는 것입니다. 이어지는 강의에서 p값을 직접 계산해보겠습니다.

영상이 더 편하신 분