Excel에서 Pearson 상관 관계를 계산하는 방법. 정규 분포의 가설을 테스트합니다. Excel의 쌍 상관 계수 행렬
실험실 작업
상관 분석뛰어나다
1.1 상관관계 분석 MS 엑셀에서
상관 분석은 두 확률 변수 X와 Y 사이의 연결 정도를 결정하는 것으로 구성됩니다. 상관 계수는 이러한 연결의 척도로 사용됩니다. 상관 계수는 X와 Y의 결합 모집단에서 n개의 관련 관찰 쌍(x i, y i) 샘플로부터 추정됩니다. 정량적 척도로 측정된 X와 Y 값 사이의 관계 정도를 평가하려면 사용 선형 상관 계수(피어슨 계수), 이는 샘플 X와 Y가 정규 분포를 따른다고 가정합니다.
상관 계수는 -1(엄격한 역선형 관계)부터 1(엄격한 직접 비례 관계)까지 다양합니다. 0으로 설정하면 두 샘플 사이에 선형 관계가 없습니다.
상관관계의 일반적인 분류(Ivanter E.V., Korosov A.V., 1992에 따름):
다양한 규모로 측정할 수 있는 변수 X와 Y에 따라 여러 유형의 상관 계수가 있습니다. 적절한 상관 계수의 선택을 결정하는 것은 바로 이 사실입니다(표 13 참조).
MS Excel에서는 쌍 선형 상관 계수를 계산하는 데 특수 함수가 사용됩니다. CORREL(배열1; 배열2),
|
№ 과목 | ||
예시 1: 10명의 학생들에게 시각적 비유와 언어적 사고에 대한 테스트를 실시했습니다. 테스트 작업을 해결하는 데 걸리는 평균 시간은 초 단위로 측정되었습니다. 연구자는 다음 질문에 관심이 있습니다. 이러한 문제를 해결하는 데 걸리는 시간 사이에 관계가 있습니까? 변수 X는 시각적 비유 테스트를 해결하는 데 소요되는 평균 시간을 나타내고, 변수 Y는 언어 테스트 과제를 해결하는 데 소요되는 평균 시간을 나타냅니다.
아르 자형 
해결책:관계 정도를 확인하려면 먼저 MS Excel 테이블에 데이터를 입력해야 합니다(그림 1 표 참조). 그런 다음 상관 계수의 값이 계산됩니다. 이렇게 하려면 C1 셀에 커서를 놓습니다. 도구 모음에서 함수 삽입(fx) 버튼을 클릭합니다.
표시되는 기능 마법사 대화 상자에서 카테고리를 선택합니다. 통계그리고 기능 코렐을 클릭한 다음 확인을 클릭합니다. 마우스 포인터를 사용하여 array1(A1:A10) 필드에 샘플 데이터 범위 X를 입력합니다. array2 필드에 샘플 데이터 범위 Y(B1:B10)를 입력합니다. 확인을 클릭하세요. 셀 C1에는 상관 계수 값(0.54119)이 나타납니다. 다음으로 상관계수의 절대값을 보고 연결 유형(닫힘, 약함, 중간 등)을 결정해야 합니다.
쌀. 1. 상관계수 계산 결과
따라서 시각적 비 유적 문제와 언어 테스트 문제를 해결하는 시간 사이의 연관성은 입증되지 않았습니다.
연습 1. 20개의 농업 자산에 대한 데이터가 제공됩니다. 찾다 상관 계수곡물 수확량과 토지의 질 사이에서 그 중요성을 평가합니다. 데이터가 표에 표시됩니다.
표 2. 토지 품질에 대한 곡물 수확량의 의존성
|
농장 번호 |
토지의 질, 점수 |
생산성, c/ha |
작업 2.스포츠 피트니스 장비의 작동 시간(천 시간)과 수리 비용(천 루블) 사이에 연관성이 있는지 확인하십시오.
|
시뮬레이터 가동시간(천시간) |
수리 비용 (천 루블) |
1.2 MS Excel의 다중 상관관계
~에 큰 숫자관찰 결과, 여러 표본에 대해 상관 계수를 순차적으로 계산해야 하는 경우 편의상 결과 계수를 다음 표에 요약합니다. 상관 행렬.
상관 행렬해당 행과 열의 교차점에 해당 매개변수 간의 상관 계수가 있는 정사각형 테이블입니다.
MS Excel에서는 상관행렬을 계산하는 절차가 사용됩니다. 상관관계패키지에서 데이터 분석.이 절차를 통해 다양한 매개변수 간의 상관 계수를 포함하는 상관 행렬을 얻을 수 있습니다.
필요한 절차를 구현하려면 다음이 필요합니다.
1. 명령을 실행 서비스 - 분석 데이터;
2. 나타나는 목록에서 분석 도구라인 선택 상관관계그리고 버튼을 누르세요 좋아요;
3. 나타나는 대화 상자에서 다음을 지정하십시오. 입력 간격즉, 분석된 데이터가 포함된 셀에 대한 링크를 입력합니다. 입력 간격에는 두 개 이상의 열이 포함되어야 합니다.
4. 섹션에서 그룹화입력된 데이터(열 또는 행 기준)에 따라 스위치를 설정합니다.
5. 표시하다 휴일 간격, 즉, 분석 결과가 표시될 셀에 대한 링크를 입력합니다. 출력 범위의 크기는 자동으로 결정되며 출력 범위가 원본 데이터와 겹칠 수 있는 경우 메시지가 표시됩니다. 버튼을 누르세요 좋아요.
상관 행렬은 출력 범위로 출력되며, 각 행과 열의 교차점에는 해당 매개변수 간의 상관 계수가 있습니다. 행과 열 좌표가 일치하는 출력 범위의 셀에는 값 1이 포함됩니다. 각 열은 다음과 같습니다. 입력 범위그 자체와 완전히 연관되어 있다
예시 2.기상 조건과 박물관 및 공원 출석에 대한 월간 관측 데이터가 있습니다(표 3 참조). 기상 조건과 박물관 및 공원 관람 사이에 관계가 있는지 확인하는 것이 필요합니다.
표 3. 관찰 결과
|
맑은 날 수 |
박물관 방문객 수 |
공원 방문객 수 |
해결책. 상관 분석을 수행하려면 원본 데이터를 A1:G3 범위에 입력합니다(그림 2). 그럼 메뉴에서 서비스물품을 고르시 오 분석 데이터그런 다음 줄을 입력하십시오 상관관계. 나타나는 대화 상자에서 다음을 지정하십시오. 입력 간격(A2:C7). 데이터가 열에 표시되도록 지정합니다. 출력 범위(E1)를 지정하고 버튼을 누릅니다. 좋아요.
그림에서. 33은 기상 조건과 박물관 출석 간의 상관 관계가 -0.92이고, 기상 조건과 공원 출석 간의 상관 관계가 0.97, 공원과 박물관 출석 간의 상관 관계가 0.92임을 보여줍니다.
따라서 분석 결과 종속성이 드러났습니다. 즉, 박물관 출석률과 화창한 날 수 사이에는 강한 역선형 관계가 있고, 공원 출석률과 기상 조건 사이에는 거의 선형(매우 강한 직접적) 관계가 있는 것으로 나타났습니다. 박물관과 공원 출석 사이에는 강한 반비례 관계가 있습니다.
쌀. 2. 실시예 2의 상관행렬을 계산한 결과
작업 3. 관리자 10명의 성격에 대한 심리적 특성에 대한 전문가 평가 방법을 사용하여 평가되었습니다. 15명의 전문가가 5점 체계를 사용하여 각 심리적 특성을 평가했습니다(표 4 참조). 심리학자는 리더의 이러한 특성 사이의 관계에 대한 질문에 관심이 있습니다.
표 4. 연구 결과
|
과목 |
재치 |
엄격함 |
임계성 |
오늘 기사에서는 변수가 서로 어떻게 관련될 수 있는지에 대해 설명합니다. 상관 관계를 사용하면 첫 번째 변수와 두 번째 변수 사이에 관계가 있는지 확인할 수 있습니다. 이번 활동도 이전 활동만큼 재미있기를 바랍니다!
상관관계는 x와 y 사이 관계의 강도와 방향을 측정합니다. 그림은 보여줍니다 다양한 방식순서쌍(x, y)의 산점도 형태의 상관관계. 전통적으로 x 변수는 가로 축에 배치되고 y 변수는 세로 축에 배치됩니다.
그래프 A는 양의 선형 상관 관계의 예입니다. x가 증가하면 y도 선형적으로 증가합니다. 그래프 B는 x가 증가함에 따라 y가 선형적으로 감소하는 음의 선형 상관 관계의 예를 보여줍니다. 그래프 C에서는 x와 y 사이에 상관관계가 없음을 알 수 있습니다. 이러한 변수는 어떤 식으로든 서로 영향을 미치지 않습니다.
마지막으로 그래프 D는 변수 간의 비선형 관계의 예입니다. x가 증가하면 y는 먼저 감소한 다음 방향을 바꾸고 증가합니다.
기사의 나머지 부분에서는 종속 변수와 독립 변수 간의 선형 관계에 중점을 둡니다.
상관 계수
상관 계수 r은 독립 변수와 종속 변수 간의 관계의 강도와 방향을 모두 제공합니다. r 값의 범위는 -1.0에서 +1.0 사이입니다. r이 양수이면 x와 y의 관계는 양수 관계이고(그림의 그래프 A), r이 음수이면 음수 관계도 됩니다(그래프 B). 0에 가까운 상관 계수는 x와 y 사이에 관계가 없음을 나타냅니다(그래프 C).
x와 y 사이의 관계 강도는 상관 계수가 - 1.0에 가까운지 +- 1.0에 가까운지에 따라 결정됩니다. 다음 그림을 연구하세요.
그래프 A는 r = + 1.0에서 x와 y 사이의 완벽한 양의 상관 관계를 보여줍니다. 그래프 B - r = - 1.0에서 x와 y 사이의 이상적인 음의 상관관계. 그래프 C와 D - 추가 예 약한 유대종속변수와 독립변수 사이.
상관 계수 r은 종속 변수와 독립 변수 간의 관계의 강도와 방향을 모두 결정합니다. r 값의 범위는 - 1.0(강함) 부정적인 연결) ~ + 1.0(강한 양의 관계). r = 0이면 변수 x와 y 사이에 연결이 없습니다.
다음 방정식을 사용하여 실제 상관 계수를 계산할 수 있습니다.
글쎄요! 나도 이 방정식이 정말 엉망진창처럼 보인다는 걸 알아 이상한 캐릭터하지만 당황하기 전에 시험 등급 예시를 적용해 보겠습니다. 학생이 통계 공부에 쏟는 시간과 최종 시험 점수 사이에 관계가 있는지 확인하고 싶다고 가정해 보겠습니다. 아래 표는 이 방정식을 몇 가지 간단한 계산으로 나누고 관리하기 쉽게 만드는 데 도움이 됩니다.
보시다시피, 과목 공부에 투자한 시간과 시험 성적 사이에는 매우 강한 양의 상관관계가 있습니다. 교사들은 이 사실을 알게 되어 매우 기뻐할 것입니다.
유사한 변수 간의 관계를 설정하면 어떤 이점이 있나요? 좋은 질문입니다. 관계가 존재하는 것으로 확인되면 다음을 기반으로 시험 결과를 예측할 수 있습니다. 일정 금액해당 주제를 공부하는 데 시간을 할애했습니다. 간단히 말해서 연결이 강할수록 예측이 더 정확해집니다.
Excel을 사용하여 상관 계수 계산
나는 여러분이 이 끔찍한 상관계수 계산을 볼 때 다음과 같은 사실을 알게 되어 정말 기뻐할 것이라고 확신합니다. 엑셀 프로그램다음 특성을 가진 CORREL 함수를 사용하여 이 모든 작업을 수행할 수 있습니다.
CORREL(배열 1, 배열 2),
배열 1 = 첫 번째 변수의 데이터 범위,
배열 2 = 두 번째 변수의 데이터 범위.
예를 들어, 그림은 시험 등급 예에 대한 상관 계수를 계산하는 데 사용되는 CORREL 함수를 보여줍니다.
1.엑셀을 연다
2. 데이터 열을 생성합니다. 이 예에서는 1학년 학생들의 공격성과 자기 의심 사이의 관계 또는 상관관계를 고려해 보겠습니다. 30명의 어린이가 실험에 참여했으며 데이터는 Excel 표에 표시됩니다.
1열 - 주제 번호
2열 - 공격포인트로
3열 - 자신감포인트로
3.그런 다음 테이블 옆에 있는 빈 셀을 선택하고 아이콘을 클릭해야 합니다. 에프엑스(f(x))엑셀 패널에서
4. 기능 메뉴가 열리면 카테고리 중에서 선택해야 합니다. 통계 , 함수 목록 중에서 알파벳 순으로 찾기 코렐그리고 확인을 클릭하세요
5. 그런 다음 함수 인수 메뉴가 열리면 필요한 데이터 열을 선택할 수 있습니다. 첫 번째 열을 선택하려면 공격선 옆에 있는 파란색 버튼을 클릭해야 합니다. 어레이1
6.데이터 선택 어레이1칼럼에서 공격대화 상자에서 파란색 버튼을 클릭합니다.
7. 그런 다음 배열 1과 유사하게 라인 옆에 있는 파란색 버튼을 클릭합니다. 어레이2
8.데이터 선택 어레이2- 열 자신감파란색 버튼을 다시 누른 다음 OK
9. 여기에서 r-Pearson 상관 계수가 계산되어 선택된 셀에 기록되었습니다. 우리의 경우 이는 양수이며 대략 다음과 같습니다. 0,225 . 이것은 다음과 같이 말합니다. 중간 정도의 긍정적 1학년 학생들의 공격성과 자기 의심 사이의 연관성
따라서, 통계적 추론실험은 다음과 같습니다: r = 0.225, 변수 사이에 중간 정도의 양의 관계가 드러났습니다. 공격그리고 자신감.
일부 연구에서는 상관 계수의 p-수준 유의성을 지정해야 하지만 Excel은 SPSS와 달리 이 옵션을 제공하지 않습니다. 괜찮아, (A.D. Nasledov)가 있습니다.
연구 결과에 첨부할 수도 있습니다.
작업 1.
Pearson 테스트를 사용하여 유의 수준에서 ㅏ= 0.05 모집단의 정규 분포에 대한 가설이 일치하는지 확인 엑스경험적 표본 크기 분포 N = 200.
해결책.
1. 계산해보자 
및 표본 표준편차 
.
2. 다음을 고려하여 이론적인 주파수를 계산해 봅시다. N = 200, 시간= 2, = 4.695, 공식에 따르면 
.
작곡하자 계산표(함수 값 제이(엑스)은 부록 1)에 나와 있습니다.
나 |
||||
3. 경험적 빈도와 이론적 빈도를 비교해보자. 기준의 관찰된 값을 찾을 수 있는 계산 테이블을 만들어 보겠습니다. 
:
나 |
|||||
| 합집합 |
중요 분포 지점 표(부록 6)에 따르면 유의 수준별 ㅏ= 0.05 및 자유도 수 케이 = 에스– 3 = 9 – 3 = 6 오른쪽 임계 영역(0.05; 6) = 12.6의 임계점을 찾습니다.
=22.2 > = 12.6이므로 모집단의 정규 분포에 대한 가설을 기각합니다. 즉, 경험적 빈도와 이론적 빈도가 크게 다릅니다.
문제 2
통계 데이터가 제공됩니다.
직경 측정 결과 N= 분쇄 후 200롤이 표에 요약되어 있습니다. (mm):
테이블롤 직경의 주파수 변화 계열
| 나 | ||||||||
xi, mm |
||||||||
xi, mm |
||||||||
필수의:
1) 별도의 변형 시리즈를 편집하고 필요한 경우 주문합니다.
2) 시리즈의 주요 수치 특성을 결정합니다.
3) 주다 그래픽 표현분포의 다각형(히스토그램) 형태의 계열;
4) 이론적 정규분포 곡선을 작성하고 Pearson 기준을 사용하여 경험적 분포와 이론적 분포의 일치성을 확인합니다. 분포 유형에 대한 통계적 가설을 검정할 때 유의 수준 a = 0.05를 받아들입니다.
해결책:
우리는 정의에 따라 주어진 변형 시리즈의 주요 수치 특성을 찾을 것입니다. 롤의 평균 직경은 (mm)입니다.
엑스평균 = = 6.753;
보정 분산(mm2):
디 = 
= 0,0009166;
수정된 평균 제곱(표준) 편차(mm):
에스 = = 0,03028.
쌀.롤 직경의 빈도 분포
변형 계열의 원래("원시") 빈도 분포, 즉 일치 니(xi), 값의 분포가 상당히 큰 것으로 구별됩니다. 니일부 가상의 "평균화" 곡선과 관련이 있습니다(그림). 이 경우, 해당 구간에 속하는 직경에 대한 빈도를 조합하여 구간 변동 계열을 구성하고 분석하는 것이 바람직하다.
간격 그룹 수 케이 Sturgess 공식을 사용하여 정의해 보겠습니다.
케이= 1 + 로그2 N= 1 + 3.322lg N,
어디 N= 200 – 표본 크기. 우리의 경우
케이= 1 + 3.322×lg200 = 1 + 3.322×2.301 = 8.644 » 8.
간격의 너비는 (6.83 – 6.68)/8 = 0.01875 » 0.02mm입니다.
간격 변동 계열이 표에 나와 있습니다.
표 롤 직경의 주파수 간격 변화 시리즈.
| 케이 | ||||||||
xk, mm |
||||||||
간격 계열은 빈도 분포의 히스토그램 형태로 시각적으로 표시될 수 있습니다. 
쌀. 롤 직경의 빈도 분포. 실선– 정상 곡선을 평활화합니다.
히스토그램의 출현을 통해 롤 직경의 분포가 정규 법칙을 따른다는 가정을 할 수 있으며, 이에 따라 이론적 빈도는 다음과 같이 구할 수 있습니다.
NK, 이론 = N× N(ㅏ; 에스; xk)×디 xk,
여기서 정규 분포의 평활화 가우스 곡선은 다음 식으로 결정됩니다.
N(ㅏ; 에스; xk) = 
.
이 표현들에는 xk– 주파수 간격 변화 계열의 간격 중심.
예를 들어, 엑스 1 = (6.68 + 6.70)/2 = 6.69. 센터 평가로 ㅏ가우스 곡선의 매개변수 s를 사용할 수 있습니다.
ㅏ = 엑스수요일
그림에서. 가우스 정규 분포 곡선은 일반적으로 경험적 간격 분포에 해당함을 알 수 있습니다. 그러나 반드시 확인해야 할 사항은 다음과 같습니다. 통계 학적으로 유의이 서신. 경험적 분포와 경험적 분포의 일치성을 확인하기 위해 Pearson 적합도 기준 c2를 사용합니다. 이를 위해 기준의 경험적 값을 합계로 계산합니다.
= 
,
어디 NK그리고 NK,theor – 각각 경험적 및 이론적(정규) 빈도입니다. 계산 결과를 다음과 같이 제시하는 것이 편리합니다. 표 형식:
테이블피어슨 테스트 계산
[xk, xk+ 1), mm |
xk, mm |
NK,이론 |
||
결정적인 가치유의 수준 a = 0.05와 자유도에 대한 Pearson 테이블을 사용하여 기준을 찾습니다. 디.에프. = 케이 – 1 – 아르 자형, 어디 케이= 8 – 간격 변동 계열의 간격 수; 아르 자형= 2 – 표본 데이터를 기반으로 추정된 이론적 분포 매개변수의 수(단위: 이 경우, - 옵션 ㅏ그리고 들). 따라서, 디.에프. = 5. 피어슨 기준의 임계값은 crit(a; 디.에프.) = 11.1. c2emp 이후< c2крит, заключаем, что согласие между эмпирическим и теоретическим нормальным распределением является статистическим значимым. Иными словами, теоретическое нормальное распределение удовлетворительно описывает эмпирические данные.
문제 3
초콜릿 상자가 자동으로 포장됩니다. 무작위 비반복 샘플링 계획에 따라 배치에 포함된 2000개의 패키지 중 130개를 채취하여 무게에 대한 다음 데이터를 얻었습니다.
다음 가설을 검정하려면 a=0.05의 유의 수준에서 Pearson 검정을 사용해야 합니다. 임의의 값 X(패키지의 무게)는 일반 법칙에 따라 분배됩니다. 하나의 그래프에 경험적 분포와 이에 상응하는 정규 곡선의 히스토그램을 구성합니다.
해결책
1012,5
= 615,3846
메모:
원칙적으로 수정된 표본 분산은 정규 분포 법칙의 분산으로 간주되어야 합니다. 하지만 왜냐하면 관측치 수 - 130이면 충분히 크므로 "보통" 관측치가 적합합니다.
따라서 이론적 정규 분포는 다음과 같습니다.
[xi ; xi+1]
경험적 주파수
니확률
파이
이론적인 주파수
npi
(니-npi)2
PEARSON 함수(영어로 PEARSON 입력)는 Pearson 상관 계수 r을 계산하도록 설계되었습니다. 이 기능업무상 학위 반영이 필요한 경우에 사용 선형 의존성두 데이터 세트 사이. Excel에는 동일한 결과를 얻는 데 사용할 수 있는 여러 기능이 있지만 Pearson 기능의 다양성과 단순성으로 인해 선택이 가능해졌습니다.
PEARSON 함수는 Excel에서 어떻게 작동하나요?
MS EXCEL에서 PEARSON 함수를 사용하여 두 데이터 세트 간의 Pearson 상관관계를 계산하는 예를 살펴보겠습니다. 첫 번째 배열은 온도 값을 나타내고, 두 번째 배열은 특정 여름 기간의 압력을 나타냅니다. 완성된 테이블의 예가 그림에 나와 있습니다.
작업은 다음과 같습니다. 6월의 온도와 압력 사이의 관계를 결정하는 것이 필요합니다.
Excel에서 분석할 때 PEARSON 함수를 사용한 솔루션의 예
함수에 의해 반환된 Pearson에 따르면 이 지표 -0.14는 하루 중 이른 시간의 온도와 압력 사이의 불리한 관계를 나타냅니다.
PEARSON 기능 단계별 지침
상관계수는 정량적 특성의 우발성을 나타내는 가장 편리한 지표입니다.
과제: 선형 피어슨 상관 계수를 결정합니다.
솔루션 예시:
따라서 계산 결과에 따르면 실험의 통계적 결론은 연령과 하루 흡연량 사이에 음의 관계가 있음을 나타냅니다.
Excel의 Pearson 상관 분석
과제: 학생들은 시각적 및 언어적 사고에 대한 테스트를 받았습니다. 테스트 작업을 해결하는 데 걸리는 평균 시간은 초 단위로 측정되었습니다. 심리학자는 다음 질문에 관심이 있습니다. 이러한 문제를 해결하는 데 걸리는 시간 사이에 관계가 있습니까?
솔루션 예시: 초기 데이터를 테이블 형식으로 제시해 보겠습니다.
Pearson에 따른 계산 결과 해석
Pearson 선형 상관 계수의 값은 +1을 초과할 수 없고 -1보다 작을 수 없습니다. 이 두 숫자 +1과 -1은 상관 계수의 경계입니다. 따라서 계산 결과 값이 +1보다 크거나 -1보다 작으면 계산에 오류가 발생한 것입니다.
모듈러스의 상관 계수가 1에 가까운 것으로 판명되면 이는 다음과 같습니다. 높은 레벨변수 사이의 연결.
마이너스 기호가 수신되면 한 특성의 더 큰 값이 다른 특성의 더 작은 값에 해당합니다. 즉, 마이너스 기호가 있으면 한 변수(부호, 값)가 증가하면 다른 변수는 감소하는 것입니다. 이러한 종류의 의존성을 역 의존성이라고 합니다. 비례 의존. 결과적인 상관 의존성을 올바르게 해석하려면 이러한 조항을 명확하게 이해하는 것이 매우 중요합니다.