엑셀 상관분석 기능

엑셀의 데이터 분석기능을 활용하여 상관분석하는 방법에 대해 알아본다.

[예제] 프로농구 선수들의 신장, 체중, 윙스펜 및 게임당 평균 리바운드 개수가 아래와 같을 때 각 변수들 간의 상관계수를 구하여라. 또한 리바운드와 가장 상관성이 큰 신체조건은 무엇인가?

<풀이>

위와 같이 위크 시트 셀에 데이터를 입력한 후 데이터 탭>데이터 분석을 클릭한다. 메뉴가 안보이면 엑셀 추가기능 설정 방법 링크를 참조하여 옵션을 추가한다.

통계 테이터 분석창이 팝업되면 상관 분석을 선택한 후 확인을 누른다.

아래와 같이 상관 분석 팝업창이 뜨면 해당 항목을 입력한다. 제1행에 각 변수의 이름이 있으므로 '첫째 행 이름표 사용' 체크박스를 클릭하고 '입력 범위'에 해당되는 변수 셀들을 모두 선택한다. '데이터 방향'은 서로 다른 조건의 변수가 각 열에 있으므로 '열' 버튼을 클릭한다. 마지막으로 '출력 옵션' 항목 중 원하는 출력 위치를 선택한 후 확인을 누른다.

다음과 같이 각 변수별 상관계수(correlation coefficient)가 자동으로 계산되어 출력 위치에 표시된다. 동일 변수 이름이 교차하는 셀은 값이 같으므로 당연히 '1'이다. 각 변수 이름의 행과 열이 교차하는 셀은 값이 그 변수들 간의 상관계수 값이다. 신체조건 변수들 간에는 체중과 위스펜의 상관성(0.90)이 가장 높다. 리바운드는 예상과는 달리 신장(0.79)보다 윙스펜(0.88)의 상관성이 가장 높다.

추가적으로 분산형 차트로 산포도(산점도)를 추가하면 도식적으로 쉽게 파악할 수 있다.

아래 도표는 각 상관계수의 대각선 방향 대칭셀에 해당 변수를 x, y축으로 하여 산포도를 추가한 것이다. 모든 변수는 양(positive)의 선형 관계가 있음을 한 눈에 알 수 있다.

상관관계의 유의성은 상관계수의 크기로 판단하는 것이 아니라, 검정결과의 유의 확률에 의해 판정한다. 가설설정은 다음과 같다. (r: 상관계수)

- 귀무가설(\(H_0\)) : 두 변수는 상관관계가 없다. (\(r=0\))

- 대립가설(\(H_1\)) : 두 변수는 상관관계가 있다. (\(r\ne 0\))

각 변수 간의 피어슨 상관계수의 검정통계량을 아래 식으로 구한다. (n: 표본수)

\(t=\dfrac{r\sqrt{n-2}}{\sqrt{1-r^2}}\ \backsim t(n-2)\)

각 변수 간의 검정통계량을 상관계수와 같이 표에 기입하면 다음과 같다.

한편 유의수준 α의 t-검정통계량을 엑셀 함수로 구하면

t(0.05)=tinv(0.05, 18-2)=2.12

따라서 각 변수 간 모든 검정통계량이 유의수준의 검정통계량 보다 크므로 귀무가설을 기각한다. 즉, 신장, 체중, 윙스팬 및 리바운드수는 5% 유의수준에서 상호 간에 상관관계가 있다.