Excel에서 상관 관계를 찾는 방법

Spearman의 상관 계수 rS는 순위를 기반으로 한 최초의 비모수 테스트였습니다. Excel의 두 변수에 대해 각각 측정된 개인 샘플의 경우 아이디어는 자체 변수 내에서 각 점수의 순위를 지정하는 것입니다. 그런 다음 각 개인에 대해 다른 순위에서 한 순위를 뺍니다. 상관 관계가 완벽하면(양의 방향으로) 모든 차이가 0입니다.

아래 이미지는 예를 보여줍니다. 산업 심리학자는 FarDrate Timepiece Corporation 직원 20명의 사교성을 평가했습니다. 척도의 범위는 1(가장 사교적임)에서 100(가장 사교적임)까지입니다. 또한 각 FarDrate 직원은 자신의 직무 만족도를 1(가장 낮은 만족도)에서 80(가장 높은 만족도)의 척도로 평가했습니다. 귀무가설은 사교성이 직업 만족도와 상관관계가 없다는 것입니다. 대립 가설은 이 두 변수가 상관 관계가 있다는 것입니다.

데이터는 열 B와 C에 있고 순위는 열 E와 F에 있습니다. 각 순위 쌍 간의 차이는 열 G에 있습니다.

스피어맨의 rs.

공식은

여기서 d 는 쌍 간의 차이입니다. 정규 상관 계수의 경우와 마찬가지로 귀무 가설이 참이면 rS 값은 약 0이어야 합니다.

E 열의 순위를 계산하려면 다음을 입력하십시오.

=RANK.AVG(B2,$B$2:$B$21,1)

E2로 자동 완성됩니다. E 열의 순위에 대해 다음을 입력합니다.

=RANK.AVG(C2,$C$2:$C$21,1)

F2로 입력하고 자동 완성됩니다.

상관 계수를 계산하기 위해 J4 셀에 복잡한 Excel 공식을 입력할 필요가 없습니다. 왜요? Excel과 수학적 통계가 팀을 이루어 놀라움을 선사하기 때문에: 입력하기만 하면 됩니다.

=코렐(E2:E21,F2:F21)

J4로. 그게 전부입니다. 순위에서 CORREL을 사용하면 위의 공식과 같은 답이 나옵니다. (따라서 열 G의 쌍 간 순위 차이를 계산할 필요가 없습니다.)

계산하다

N 은 쌍의 수이고 검정 의 자유도 는 N-2 입니다.

Number_of_pairs를 I2 셀의 값 이름으로 정의할 수 있습니다. 그래서 유형

=J4*SQRT(Number_of_Pairs-2)/SQRT(1-J4^2)

J6 및

=T.DIST.2T(J6,Number_of_Pairs-2)

J7으로. 상관관계의 방향을 미리 알지 못하는 경우 양측 t 분포 함수를 사용할 수 있습니다. 그리고 다시 한 번 낮은 p-값은 귀무 가설을 기각하라고 알려줍니다.