변수끼리 얼마나 연관성이 있을까? - 엑셀로 상관관계분석하기

상관관계분석

업무를 하다보면 수많은 데이터를 축적하고 있는데, 그 데이터의 변수들끼리 관계가 있는지를 알아보고 싶을때 상관관계 분석을 사용합니다.

결과값으로 상관계수를 추출하게되는데 -1과 +1사이의 값이며 변수들이 얼마나 밀접하게 관련되어 있는지를 알수있는 척도가 됩니다.

 

상관 계수 +1은 완전한 양의 상관관계를 뜻하며, -1은 완전한 음의 상관관계를 뜻합니다.

 

상관관계 그래프

 

1. 엑셀로 상관관계 분석하기

(1) 엑셀 함수를 이용하여 2개의 변수를 분석하기

예제로 하기와 같이 X,Y 2개의 변수 데이터값이 있으며, Y=X+2로 임의의 값을 넣어보았습니다.

=CORREL("X값 범위","Y값 범위")

결과값 : 1

해석 : 두 변수의 값은 완전한 양의 상관관계를 갖는다. 즉, 두 변수는 서로에게 영향을 줄 수 있다.

 

 

(2) 엑셀 분석도구를 이용하여 3개의 변수를 분석하기

예제로 하기와 같이 X,Y,Z 3개의 변수에 각 6개의 데이터를 취출하여 상관관계를 알아보고자 합니다.

그래프를 보면 X,Z는 어느정도 상관관계가 있어보이나 Y는 어떠한 관계가 있는지 알수가 없습니다.

이를 알아보기 위해 상관계수를 추출해봅니다.

 

 

우선 엑셀에 데이터 분석 툴을 추가해야합니다.

a. 엑셀 옵션 -> 리본사용자 지정 -> 개발도구 앞에 체크표시를 한 후 확인

 

b. 개발도구 탭 -> Excel 추가 기능 -> 분석 도구 체크

 

c. 데이터 탭 -> 데이터 분석 -> 상관 분석

 

d. 입력범위(데이터범위) 설정 -> 출력 범위(값 출력 위치) 설정

 

e. 결과 해석하기

X-Y : 양의 방향으로 0.19

X-Z : 양의 방향으로 0.90

Y-Z : 양의 방향으로 0.10

X와 Z는 데이터의 연관성이 있으나, Y는 X,Z와 연관성이 없다.