엑셀의 상관관계표. 상관 관계 계산, 선형 회귀 구축 및 서비스에서 두 RV의 종속성에 대한 가설 테스트의 예

회귀 및 상관 분석– 통계 연구 방법. 이것은 하나 이상의 독립 변수에 대한 매개변수의 종속성을 표시하는 가장 일반적인 방법입니다.

아래에서 구체적인 실제 사례경제학자들 사이에서 매우 인기 있는 이 두 가지 분석을 살펴보겠습니다. 우리는 또한 그것들을 결합했을 때 결과를 얻는 예를 제공 할 것입니다.

Excel의 회귀 분석

종속 변수에 대한 일부 값(독립, 독립)의 영향을 보여줍니다. 예를 들어, 경제 활동 인구의 수가 기업 수, 임금 및 기타 매개변수에 따라 어떻게 달라지는지. 또는: 외국인 투자, 에너지 가격 등이 GDP 수준에 어떤 영향을 미치는지.

분석 결과를 통해 우선 순위를 지정할 수 있습니다. 그리고 주요 요인을 기반으로 예측, 개발 계획 우선 영역관리 결정을 내리기 위해.

회귀 발생:

선형(y = a + bx);
포물선(y = a + bx + cx 2);
지수(y = a * exp(bx));
거듭제곱(y = a*x^b);
쌍곡선(y = b/x + a);
대수(y = b * 1n(x) + a);
지수(y = a * b^x).

Excel에서 회귀 모델을 작성하고 결과를 해석하는 예를 고려하십시오. 선형 유형의 회귀를 살펴보겠습니다.

작업. 6개 기업에서 월평균 값그리고 퇴직한 직원의 수. 평균 급여에 대한 퇴직 직원 수의 의존성을 결정할 필요가 있습니다.

모델 선형 회귀다음과 같은 형식이 있습니다.

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

여기서 는 회귀 계수, x는 영향을 미치는 변수, k는 요인 수입니다.

이 예에서 Y는 퇴직 근로자의 지표입니다. 영향을 미치는 요소는 임금(x)입니다.

Excel에는 선형 회귀 모델의 매개변수를 계산하는 데 사용할 수 있는 기본 제공 함수가 있습니다. 그러나 Analysis ToolPak 추가 기능을 사용하면 더 빠르게 작업을 수행할 수 있습니다.

강력한 분석 도구 활성화:

활성화되면 데이터 탭에서 추가 기능을 사용할 수 있습니다.

이제 우리는 회귀 분석을 직접 다룰 것입니다.

우선, 우리는 R-제곱과 계수에 주목합니다.

R-제곱은 결정 계수입니다. 이 예에서는 0.755 또는 75.5%입니다. 이는 모델의 계산된 매개변수가 연구된 매개변수 간의 관계를 75.5% 설명한다는 것을 의미합니다. 결정 계수가 높을수록 더 좋은 모델입니다. 양호 - 0.8 이상. 나쁨 - 0.5 미만(이러한 분석은 합리적인 것으로 간주될 수 없음). 우리의 예에서 - "나쁘지 않음".

계수 64.1428은 고려 중인 모델의 모든 변수가 0인 경우 Y가 무엇인지 보여줍니다. 즉, 모델에 설명되지 않은 다른 요인도 분석된 매개변수의 값에 영향을 미칩니다.

계수 -0.16285는 Y에 대한 변수 X의 가중치를 보여줍니다. 즉, 이 모델 내의 평균 월 급여는 가중치가 -0.16285(작은 영향 정도)로 퇴직자 수에 영향을 미칩니다. "-"기호는 부정적인 영향을 나타냅니다. 급여가 높을수록 퇴직이 적습니다. 공정합니다.

Excel의 상관관계 분석

상관 분석은 하나 또는 두 개의 샘플에서 지표 사이에 관계가 있는지 여부를 확인하는 데 도움이 됩니다. 예를 들어, 기계의 작동 시간과 수리 비용 사이, 장비 가격과 작동 기간, 어린이의 키와 체중 등.

관계가 있는 경우 한 매개변수의 증가가 다른 매개변수의 증가(양의 상관관계) 또는 감소(음의)로 이어지는지 여부. 상관 분석은 분석가가 한 지표의 값이 다른 지표의 가능한 값을 예측할 수 있는지 여부를 결정하는 데 도움이 됩니다.

상관 계수는 r로 표시됩니다. +1에서 -1까지 다양합니다. 상관관계 분류 다른 지역다를 것입니다. 계수 값이 0이면 샘플 간에 선형 관계가 없습니다.

Excel을 사용하여 상관 계수를 찾는 방법을 고려하십시오.

CORREL 함수는 쌍을 이루는 계수를 찾는 데 사용됩니다.

작업: 작업 시간 사이에 관계가 있는지 확인 선반및 유지 보수 비용.

커서를 아무 셀에 놓고 fx 버튼을 누릅니다.

"통계" 범주에서 CORREL 기능을 선택합니다.
인수 "배열 1" - 값의 첫 번째 범위 - 기계 시간: A2: A14.
인수 "Array 2" - 값의 두 번째 범위 - 수리 비용: B2:B14. 확인을 클릭합니다.

연결 유형을 결정하려면 계수의 절대 수를 확인해야 합니다(각 활동 필드에는 자체 척도가 있습니다).

여러 매개변수(2개 이상)의 상관관계 분석을 위해서는 "데이터 분석"("분석 패키지" 추가 기능)을 사용하는 것이 더 편리합니다. 목록에서 상관관계를 선택하고 배열을 지정해야 합니다. 모두.

결과 계수는 상관 행렬에 표시됩니다. 이 같은:

상관-회귀 분석

실제로 이 두 기술은 종종 함께 사용됩니다.

예시:

이제 회귀 분석 데이터가 표시됩니다.

"상관관계"는 라틴어로 "상관관계", "관계"를 의미합니다. 상관 계수를 계산하여 관계의 양적 특성을 얻을 수 있습니다. 이 인기있는 통계 분석계수는 매개변수가 서로 관련되어 있는지 여부를 보여줍니다(예: 키와 체중, 지능 수준 및 학업 성취도, 부상 횟수 및 작업 시간).

상관 관계 사용

상관 관계 계산은 특히 경제학, 사회학 연구, 의학 및 생체 인식 분야에서 널리 사용됩니다.

간단한 산술 연산을 수행하여 상관 관계를 수동으로 계산할 수 있습니다. 그러나 데이터 집합이 큰 경우 계산 프로세스에 매우 많은 시간이 소요됩니다. 이 방법의 특징은 수집이 필요하다는 것입니다. 큰 수피처 간의 관계가 있는지 여부를 가장 정확하게 표시하기 위한 소스 데이터. 따라서 컴퓨터 기술을 사용하지 않고 상관 분석을 본격적으로 사용하는 것은 불가능합니다. 이 문제를 해결하기 위한 가장 인기 있고 저렴한 프로그램 중 하나는 다음과 같습니다.

Excel에서 상관 관계를 수행하는 방법은 무엇입니까?

상관 관계를 결정하는 데 가장 시간이 많이 걸리는 단계는 데이터 세트입니다. 비교할 데이터는 일반적으로 두 개의 열 또는 행으로 정렬됩니다. 표는 셀에 틈이 없도록 만들어야 합니다. 최신 버전 Excel(2007 이하)에서는 통계 계산을 위한 추가 설정이 필요하지 않습니다. 필요한 조작을 수행할 수 있습니다.

계산 결과가 표시될 빈 셀을 선택합니다.
Excel 주 메뉴에서 "수식" 항목을 클릭합니다.
"Function Library"에 그룹화된 버튼 중 "Other Functions"를 선택합니다.
드롭다운 목록에서 상관 관계 계산 기능(통계 - CORREL)을 선택합니다.
Excel에서 함수 인수 패널이 열립니다. "배열 1" 및 "배열 2"는 비교할 데이터의 범위입니다. 이 필드를 자동으로 채우려면 원하는 표 셀을 선택하기만 하면 됩니다.
확인을 클릭하여 함수 인수 창을 닫습니다. 계산된 상관 계수가 셀에 나타납니다.

상관 관계는 직접(계수가 0보다 큰 경우) 및 역(-1에서 0까지)일 수 있습니다.

첫 번째는 한 매개변수가 증가하면 다른 매개변수도 증가함을 의미합니다. 역(음) 상관은 한 변수가 증가하면 다른 변수는 감소한다는 사실을 반영합니다.

상관 관계는 0에 가까울 수 있습니다. 이것은 일반적으로 연구된 매개변수가 서로 관련이 없음을 나타냅니다. 그러나 관계를 반영하지 않는 실패한 샘플이 만들어지거나 관계에 복잡한 비선형 특성이 있는 경우 상관 관계가 0인 경우가 있습니다.

계수가 중간 또는 강한 관계(±0.5 ~ ±0.99)를 나타내는 경우 이는 통계적 관계일 뿐이며 한 매개변수가 다른 매개변수에 미치는 영향을 전혀 보장하지 않는다는 점을 기억해야 합니다. 두 매개변수가 서로 독립적이지만 설명되지 않은 제3의 요인에 의해 영향을 받는 상황도 배제할 수 없습니다. Excel은 상관 계수를 즉시 계산하는 데 도움이 되지만 일반적으로 정량적 방법비교 가능한 샘플에서 인과 관계를 설정하기에는 충분하지 않습니다.

상관 계수(또는 선형 상관 계수)는 "r"로 표시됩니다( 드문 경우"ρ"로) 두 개 이상의 변수의 선형 상관 관계(즉, 어떤 값과 방향에 의해 주어지는 관계)를 특성화합니다. 계수 값은 -1과 +1 사이에 있습니다. 즉, 상관 관계는 양수와 음수 모두일 수 있습니다. 상관 계수가 -1이면 완전한 음의 상관 관계가 있습니다. 상관 계수가 +1이면 완전한 양의 상관 관계가 있습니다. 다른 경우에는 두 변수 사이에 양의 상관관계, 음의 상관관계가 있거나 상관관계가 없습니다. 상관 계수는 무료 온라인 계산기 또는 좋은 그래프 계산기를 사용하여 수동으로 계산할 수 있습니다.

단계

상관 계수 수동 계산

데이터를 수집합니다.상관 계수 계산을 시작하기 전에 주어진 숫자 쌍을 조사하십시오. 세로 또는 가로로 배열할 수 있는 표에 적어 두는 것이 좋습니다. 각 행 또는 열에 "x" 및 "y"로 레이블을 지정합니다.

예를 들어, 변수 "x"와 "y"의 네 쌍의 값(숫자)이 주어집니다. 다음 테이블을 생성할 수 있습니다.
- x || 와이
- 1 || 1
- 2 || 3
- 4 || 5
- 5 || 7

산술 평균 "x"를 계산합니다.이렇게하려면 "x"의 모든 값을 더한 다음 결과를 값 수로 나눕니다.
- 이 예에서는 변수 "x"에 대해 4개의 값이 제공됩니다. 산술 평균 "x"를 계산하려면 이 값을 더한 다음 합계를 4로 나눕니다. 계산은 다음과 같이 작성됩니다.
- μ x = (1 + 2 + 4 + 5) / 4 (\displaystyle \mu _(x)=(1+2+4+5)/4)
- μ x = 12 / 4 (\displaystyle \mu _(x)=12/4)
- μ x = 3 (\displaystyle \mu _(x)=3)
산술 평균 "y"를 찾으십시오.이렇게하려면 동일한 단계, 즉 "y"의 모든 값을 더한 다음 합계를 값 수로 나눕니다.
- 이 예에서는 변수 "y"에 대해 4개의 값이 제공됩니다. 이 값을 더한 다음 합계를 4로 나눕니다. 계산은 다음과 같이 작성됩니다.
- μ y = (1 + 3 + 5 + 7) / 4 (\displaystyle \mu _(y)=(1+3+5+7)/4)
- μ y = 16 / 4 (\displaystyle \mu _(y)=16/4)
- μ y = 4 (\displaystyle \mu _(y)=4)
"x"의 표준 편차를 계산합니다. x와 y의 평균을 계산했으면 이러한 변수의 표준 편차를 찾으십시오. 표준 편차는 다음 공식을 사용하여 계산됩니다.
- σ x = 1 n − 1 Σ (x − μ x) 2 (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(n-1)))\Sigma (x-\mu _( x))^(2))))
- σ x = 1 4 − 1 * ((1 − 3) 2 + (2 − 3) 2 + (4 − 3) 2 + (5 − 3) 2) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(4-1))*((1-3)^(2)+(2-3)^(2)+(4-3)^(2)+(5-3) ^(2))))
- σ x = 1 3 * (4 + 1 + 1 + 4) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3)))*(4+1+1+4)) ))
- σ x = 1 3 * (10) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(10))))
- σ x = 10 3 (\displaystyle \sigma _(x)=(\sqrt (\frac (10)(3))))
- σ x = 1 , 83 (\displaystyle \sigma _(x)=1.83)
표준 편차 "y"를 계산합니다.이전 단계의 단계를 따릅니다. 동일한 수식을 사용하되 "y" 값을 대입합니다.
- 이 예에서 계산은 다음과 같이 작성됩니다.
- σ y = 1 4 − 1 * ((1 − 4) 2 + (3 − 4) 2 + (5 − 4) 2 + (7 − 4) 2) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(4-1))*((1-4)^(2)+(3-4)^(2)+(5-4)^(2)+(7-4) ^(2))))
- σ y = 1 3 * (9 + 1 + 1 + 9) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(9+1+1+9)) ))
- σ y = 1 3 * (20) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(20))))
- σ y = 20 3 (\displaystyle \sigma _(y)=(\sqrt (\frac (20)(3))))
- σ y = 2 , 58 (\displaystyle \sigma _(y)=2.58)
상관 계수를 계산하는 기본 공식을 작성하십시오.이 공식에는 평균, 표준 편차 및 두 변수의 숫자 쌍의 수(n)가 포함됩니다. 상관 계수는 "r"(드문 경우 "ρ")로 표시됩니다. 이 기사에서는 공식을 사용하여 Pearson 상관 계수를 계산합니다.
- 여기와 다른 출처에서 수량은 다른 방식으로 표시될 수 있습니다. 예를 들어, 일부 공식에는 "ρ" 및 "σ"가 있고 다른 공식에는 "r" 및 "s"가 있습니다. 일부 교과서는 다른 공식을 제공하지만 위 공식과 수학적으로 동일합니다.
두 변수의 평균과 표준 편차를 계산했으므로 공식을 사용하여 상관 계수를 계산할 수 있습니다. "n"은 두 변수의 값 쌍의 수입니다. 다른 수량의 값은 이전에 계산되었습니다.
- 이 예에서 계산은 다음과 같이 작성됩니다.
- ρ = (1 n − 1) Σ (x − μ x σ x) ∗ (y − μ y σ y) (\displaystyle \rho =\left((\frac (1)(n-1))\right) \시그마 \left((\frac (x-\mu _(x))(\sigma _(x)))\right)*\left((\frac (y-\mu _(y))(\sigma _(y)))\오른쪽))
- ρ = (1 3) * (\displaystyle \rho =\left((\frac (1)(3))\right)*)[ (1 − 3 1 , 83) ∗ (1 − 4 2 , 58) + (2 − 3 1 , 83) ∗ (3 − 4 2 , 58) (\displaystyle \left((\frac (1-3))( 1.83))\right)*\left((\frac (1-4)(2.58))\right)+\left((\frac (2-3)(1.83))\right) *\left((\ frac (3-4)(2,58))\오른쪽))
  + (4 − 3 1 , 83) ∗ (5 − 4 2 , 58) + (5 − 3 1 , 83) ∗ (7 − 4 2 , 58) (\displaystyle +\left((\frac (4-3) )(1.83))\right)*\left((\frac (5-4)(2.58))\right)+\left((\frac (5-3)(1.83))\ right)*\left( (\frac (7-4)(2,58))\right))]
- ρ = (1 3) * (6 + 1 + 1 + 6 4 , 721) (\displaystyle \rho =\left((\frac (1)(3))\right)*\left((\frac (6) +1+1+6)(4,721))\오른쪽))
- ρ = (1 3) * 2 , 965 (\displaystyle \rho =\left((\frac (1)(3))\right)*2.965)
- ρ = (2, 965 3) (\디스플레이 스타일 \rho =\left((\frac (2,965)(3))\right))
- ρ = 0 . 988 (\디스플레이 스타일 \rho =0.988)
결과를 분석합니다.이 예에서 상관 계수는 0.988입니다. 이 값은 어떤 식으로든 주어진 숫자 쌍의 특성을 나타냅니다. 값의 부호와 크기에 주의하십시오.
- 상관 계수의 값이 양수이므로 변수 "x"와 "y" 사이에는 양의 상관 관계가 있습니다. 즉, "x"의 값이 증가하면 "y"의 값도 증가한다.
- 상관 계수의 값이 +1에 매우 가깝기 때문에 x 및 y 변수의 값은 높은 상관 관계가 있습니다. 점을 찍으면 좌표 평면, 그들은 어떤 직선에 가깝게 위치할 것입니다.
온라인 계산기를 사용하여 상관 계수 계산
1. 상관 계수를 계산하기 위해 인터넷에서 계산기를 찾으십시오.이 계수는 종종 통계에서 계산됩니다. 숫자 쌍이 많으면 상관 계수를 수동으로 계산하는 것이 사실상 불가능합니다. 따라서 상관 계수를 계산하기 위한 온라인 계산기가 있습니다. 검색 엔진에서 "상관 계수 계산기"(따옴표 제외)를 입력하십시오.
2. 데이터를 입력합니다.데이터(숫자 쌍)를 올바르게 입력하려면 사이트의 지침을 읽으십시오. 적절한 숫자 쌍을 입력하는 것이 매우 중요합니다. 그렇지 않으면 잘못된 결과를 얻게 됩니다. 웹사이트마다 데이터 입력 형식이 다릅니다.
  - 예를 들어 http://ncalculators.com/statistics/correlation-coefficient-calculator.htm 사이트에서 변수 "x"와 "y"의 값은 두 개의 수평선에 입력됩니다. 값은 쉼표로 구분됩니다. 즉, 이 예에서 "x"의 값은 1,2,4,5와 같이 입력되고 "y"의 값은 1,3,5,7과 같습니다.
  - 다른 사이트 http://www.alcula.com/calculators/statistics/correlation-coefficient/ 에서는 데이터가 세로로 입력됩니다. 이 경우 해당 숫자 쌍을 혼동하지 마십시오.
3. 상관 계수를 계산합니다.데이터를 입력한 후 "계산", "계산" 또는 이와 유사한 버튼을 클릭하여 결과를 얻으십시오.
  
  그래프 계산기 사용하기
  1. 데이터를 입력합니다.그래프 계산기를 잡고 통계 계산 모드로 전환한 다음 편집 명령을 선택합니다.
    - 다른 계산기에서는 다른 키를 눌러야 합니다. 이 기사에서는 Texas Instruments TI-86 계산기에 중점을 둡니다.
    - 통계 계산 모드로 전환하려면 - Stat("+" 키 위)을 누릅니다. 그런 다음 F2 - 편집(편집)을 누릅니다.
  2. 이전에 저장된 데이터를 삭제합니다.대부분의 계산기는 입력한 통계를 지울 때까지 유지합니다. 이전 데이터와 새 데이터를 혼동하지 않으려면 먼저 저장된 정보를 삭제하십시오.
    - 화살표 키를 사용하여 커서를 이동하고 "xStat" 제목을 강조 표시합니다. 그런 다음 Clear 및 Enter를 눌러 xStat 열에 입력된 모든 값을 지웁니다.
    - 화살표 키를 사용하여 "yStat" 제목을 강조 표시합니다. 그런 다음 지우기 및 Enter 키를 눌러 yStat 열에 입력된 모든 값을 지웁니다.
  3. 초기 데이터를 입력합니다.화살표 키를 사용하여 "xStat" 제목 아래의 첫 번째 셀로 커서를 이동합니다. 첫 번째 값을 입력하고 Enter 키를 누릅니다. 화면 하단에 "xStat (1) = __"이 표시되고 공백 대신 입력된 값이 표시됩니다. Enter 키를 누르면 입력한 값이 테이블에 나타나고 커서가 다음 줄로 이동합니다. 그러면 화면 하단에 "xStat(2) = __"가 표시됩니다.
    - 변수 "x"의 모든 값을 입력하십시오.
    - x 변수의 모든 값을 입력했으면 화살표 키를 사용하여 yStat 열로 이동하고 y 변수의 값을 입력합니다.
    - 모든 숫자 쌍을 입력한 후 Exit를 눌러 화면을 지우고 집계 모드를 종료합니다.
  4. 상관 계수를 계산합니다.데이터가 직선에 얼마나 가까운지를 특성화합니다. 그래프 계산기는 적절한 직선을 빠르게 결정하고 상관 계수를 계산할 수 있습니다.
    - 통계(통계) - 계산(계산)을 클릭합니다. TI-86에서 --를 누릅니다.
    - "선형 회귀" 기능을 선택합니다. TI-86에서 "LinR"이라고 표시된 를 누릅니다. "LinR _" 라인이 깜박이는 커서와 함께 화면에 표시됩니다.
    - 이제 xStat 및 yStat의 두 변수 이름을 입력합니다.
      - TI-86에서 이름 목록을 엽니다. 이 작업을 수행하려면 – – 를 누르십시오.
      - 사용 가능한 변수는 화면 하단에 표시됩니다. 선택(대부분 F1 또는 F2를 눌러)하고 쉼표를 입력한 다음 를 선택합니다.
      - Enter 키를 눌러 입력된 데이터를 처리합니다.
  5. 결과를 분석합니다. Enter 키를 누르면 다음 정보가 화면에 표시됩니다.
    - y = a + b x (\displaystyle y=a+bx): 직선을 나타내는 함수입니다. 함수는 표준 형식(y = kx + b)으로 작성되지 않았습니다.
    - a = (\displaystyle a=). 이것은 선이 y축과 교차하는 점의 y좌표입니다.
    - b = (\디스플레이 스타일 b=). 그것 경사똑바로.
    - corr = (\displaystyle (\text(corr))=). 이것은 상관 계수입니다.
    - n = (\디스플레이 스타일 n=). 이것은 계산에 사용된 숫자 쌍의 수입니다.

많은 회사와 기업에서 널리 사용되는 유틸리티입니다. 이 프로그램은 매우 광범위한 작업을 해결하는 데 사용되기 때문에 거의 모든 직원이 어느 정도 Excel에 능숙해야 하는 것이 현실입니다. 테이블로 작업할 때 특정 변수가 서로 관련되어 있는지 여부를 확인해야 하는 경우가 많습니다. 이를 위해 소위 상관 관계가 사용됩니다. 이 기사에서는 Excel에서 상관 계수를 계산하는 방법에 대해 자세히 살펴보겠습니다. 알아봅시다. 가다!

일반적으로 상관 계수가 무엇인지부터 시작하겠습니다. 두 요소 사이의 관계 정도를 나타내며 항상 -1(강력한 역관계)에서 1(강력한 정방향 관계) 범위입니다. 계수가 0이면 값 사이에 관계가 없음을 나타냅니다.

이제 이론을 다루었으니 실습으로 넘어가 보겠습니다. 변수와 y 사이의 관계를 찾으려면 Microsoft Excel "CORREL"의 내장 기능을 사용하십시오. 이렇게 하려면 함수 마법사 버튼(수식 필드 옆에 있음)을 클릭합니다. 열리는 창의 기능 목록에서 "CORREL"을 선택합니다. 그런 다음 "Array1" 및 "Array2" 필드에 범위를 설정합니다. 예를 들어 "Array1"의 경우 y 값을 선택하고 "Array2"의 경우 x 값을 선택합니다. 결과적으로 프로그램에서 계산한 상관 계수를 얻게 됩니다.

다음 방법은 주어진 공식을 사용하여 종속성을 찾아야 하는 학생에게 적합합니다. 우선 변수 x와 y의 평균값을 알아야 합니다. 이렇게하려면 변수 값을 선택하고 "AVERAGE"기능을 사용하십시오. 다음으로 각 x와 x avg, y avg의 차이를 계산해야 합니다. 선택한 셀에 쓰기 공식 x-x, y-. 평균 값으로 셀을 고정하는 것을 잊지 마십시오. 그런 다음 나머지 숫자에 적용되도록 공식을 아래로 끕니다.

이제 필요한 모든 데이터가 있으므로 상관 관계를 계산할 수 있습니다. 결과 차이를 다음과 같이 곱합니다. (x-x avg) * (y-y avg). 각 변수에 대한 결과를 얻은 후 autosum 함수를 사용하여 결과 숫자의 합계를 구합니다. 이것이 분자가 계산되는 방식입니다.

이제 분모로 넘어 갑시다. 계산된 차이는 제곱해야 합니다. 이렇게 하려면 별도의 열에 (x-x avg) 2 및 (y-y avg) 2 수식을 입력합니다. 그런 다음 수식을 전체 범위로 늘립니다. 그런 다음 "AutoSum" 버튼을 사용하여 모든 열(x 및 y)의 합계를 찾습니다. 발견 된 합계를 곱하고 추출해야합니다. 제곱근. 마지막 단계는 분자를 분모로 나누는 것입니다. 얻은 결과는 원하는 상관 계수가 됩니다.

보시다시피 Microsoft Excel 기능을 올바르게 사용하는 방법을 알면 복잡한 수학 표현식을 계산하는 작업을 크게 단순화할 수 있습니다. 프로그램에 구현된 도구 덕분에 Excel에서 몇 분 만에 쉽게 상관 관계 분석을 수행하여 시간과 노력을 절약할 수 있습니다. 기사가 문제를 이해하는 데 도움이 되었다면 의견을 작성하고 논의된 주제에 대해 관심 있는 모든 것에 대해 질문하십시오.

알아채다!특정 문제에 대한 솔루션은 비슷해 보일 것입니다. 이 예, 아래의 모든 표와 설명 텍스트를 포함하지만 초기 데이터를 고려하면 ...

작업:
26개의 값 쌍(x k ,y k )의 관련 샘플이 있습니다.

케이	1	2	3	4	5	6	7	8	9	10
*x k*	25.20000	26.40000	26.00000	25.80000	24.90000	25.70000	25.70000	25.70000	26.10000	25.80000
*y k*	30.80000	29.40000	30.20000	30.50000	31.40000	30.30000	30.40000	30.50000	29.90000	30.40000

케이	11	12	13	14	15	16	17	18	19	20
*x k*	25.90000	26.20000	25.60000	25.40000	26.60000	26.20000	26.00000	22.10000	25.90000	25.80000
*y k*	30.30000	30.50000	30.60000	31.00000	29.60000	30.40000	30.70000	31.60000	30.50000	30.60000

케이	21	22	23	24	25	26
*x k*	25.90000	26.30000	26.10000	26.00000	26.40000	25.80000
*y k*	30.70000	30.10000	30.60000	30.50000	30.70000	30.80000

다음을 계산/구축하는 데 필요합니다.
- 상관 계수;
- 유의 수준 α = 0.05에서 확률 변수 X와 Y의 종속성에 대한 가설을 테스트합니다.
- 선형 회귀 방정식의 계수;
- 산포도(상관 필드) 및 회귀선 그래프;

해결책:

1. 상관 계수를 계산합니다.

상관 계수는 두 확률 변수의 상호 확률적 영향을 나타내는 지표입니다. 상관 계수 아르 자형에서 값을 가져올 수 있습니다. -1 ~ 전에 +1 . 절대값이 가까울 경우 1 , 그렇다면 이것이 증거다. 강한 연결값 사이, 그리고 가까울 경우 0 -그러면 연결이 약하거나 부재임을 나타냅니다. 절대값이라면 아르 자형 1과 같으면 수량 간의 기능적 관계에 대해 이야기할 수 있습니다. 즉, 한 수량은 수학 함수를 사용하여 다른 수량으로 표현될 수 있습니다.

다음 공식을 사용하여 상관 계수를 계산할 수 있습니다.

k = 1

(x k -M x) 2 , 2 =

M x

k = 1

x k ,

나의

또는 공식에 따라

수신, y

M xy - M x M y

SxSy

(1.4), 여기서:

M x

k = 1

x k ,

나의

k = 1

y k ,

Mxy

k = 1

x k y k (1.5)

에스 x 2

k = 1

x k 2 - M x 2,

시 2

k = 1

y k 2 - m y 2 (1.6)

실제로 공식 (1.4)는 상관 계수를 계산하는 데 더 자주 사용됩니다. 더 적은 계산이 필요합니다. 그러나 공분산이 이전에 계산된 경우 cov(X,Y)인 경우 식 (1.1)을 사용하는 것이 더 유리합니다. 공분산의 실제 값 외에도 중간 계산 결과를 사용할 수도 있습니다.

1.1 공식 (1.4)를 사용하여 상관 계수 계산, 이를 위해 x k 2 , y k 2 및 x k y k 값을 계산하고 표 1에 입력합니다.

1 번 테이블

케이	*x k*	*y k*	x k 2	y k 2	*x ky k*
1	2	3	4	5	6
1	25.2	30.8	635.04000	948.64000	776.16000
2	26.4	29.4	696.96000	864.36000	776.16000
3	26.0	30.2	676.00000	912.04000	785.20000
4	25.8	30.5	665.64000	930.25000	786.90000
5	24.9	31.4	620.01000	985.96000	781.86000
6	25.7	30.3	660.49000	918.09000	778.71000
7	25.7	30.4	660.49000	924.16000	781.28000
8	25.7	30.5	660.49000	930.25000	783.85000
9	26.1	29.9	681.21000	894.01000	780.39000
10	25.8	30.4	665.64000	924.16000	784.32000
11	25.9	30.3	670.81000	918.09000	784.77000
12	26.2	30.5	686.44000	930.25000	799.10000
13	25.6	30.6	655.36000	936.36000	783.36000
14	25.4	31	645.16000	961.00000	787.40000
15	26.6	29.6	707.56000	876.16000	787.36000
16	26.2	30.4	686.44000	924.16000	796.48000
17	26	30.7	676.00000	942.49000	798.20000
18	22.1	31.6	488.41000	998.56000	698.36000
19	25.9	30.5	670.81000	930.25000	789.95000
20	25.8	30.6	665.64000	936.36000	789.48000
21	25.9	30.7	670.81000	942.49000	795.13000
22	26.3	30.1	691.69000	906.01000	791.63000
23	26.1	30.6	681.21000	936.36000	798.66000
24	26	30.5	676.00000	930.25000	793.00000
25	26.4	30.7	696.96000	942.49000	810.48000
26	25.8	30.8	665.64000	948.64000	794.64000

1.2. 우리는 공식 (1.5)에 의해 M x를 계산합니다.

1.2.1. x k

x 1 + x 2 + ... + x 26 = 25.20000 + 26.40000 + ... + 25.80000 = 669.500000

1.2.2.

669.50000 / 26 = 25.75000

M x = 25.750000

1.3. 유사하게, 우리는 M y를 계산합니다..

1.3.1. 모든 요소를 순서대로 추가합시다 y k

y 1 + y 2 + … + y 26 = 30.80000 + 29.40000 + ... + 30.80000 = 793.000000

1.3.2. 결과 합계를 샘플 요소 수로 나눕니다.

793.00000 / 26 = 30.50000

마이 = 30.500000

1.4. 유사하게, 우리는 M xy를 계산합니다..

1.4.1. 표 1의 6번째 열의 모든 요소를 순차적으로 추가합니다.

776.16000 + 776.16000 + ... + 794.64000 = 20412.830000

1.4.2. 결과 합계를 요소 수로 나눕니다.

20412.83000 / 26 = 785.10885

M xy = 785.108846

1.5. 공식 (1.6.)을 사용하여 S x 2의 값을 계산합니다..

1.5.1. 표 1의 4번째 열의 모든 요소를 순차적으로 추가합니다.

635.04000 + 696.96000 + ... + 665.64000 = 17256.910000

1.5.2. 결과 합계를 요소 수로 나눕니다.

17256.91000 / 26 = 663.72731

1.5.3. 마지막 숫자에서 값 M x의 제곱을 빼면 S x 2에 대한 값을 얻습니다.

에스 x 2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481

1.6. 공식 (1.6.)에 의해 S y 2의 값을 계산합니다..

1.6.1. 표 1의 5번째 열의 모든 요소를 순차적으로 추가합니다.

948.64000 + 864.36000 + ... + 948.64000 = 24191.840000

1.6.2. 결과 합계를 요소 수로 나눕니다.

24191.84000 / 26 = 930.45538

1.6.3. M y 의 마지막 숫자 제곱에서 빼면 S y 2 값을 얻습니다.

시 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538

1.7. S x 2와 S y 2의 곱을 계산해 보겠습니다..

S x 2 S Y 2 = 0.66481 0.20538 = 0.136541

1.8. 마지막 숫자의 제곱근을 추출하고 값 S x S y를 얻습니다..

SxSy = 0.36951

1.9. 공식 (1.4.)에 따라 상관 계수 값을 계산합니다..

R = (785.10885 - 25.75000 30.50000) / 0.36951 = (785.10885 - 785.37500) / 0.36951 = -0.72028

답: Rx,y = -0.720279

2. 상관 계수의 유의성을 확인합니다(의존 가설 확인).

상관계수 추정치는 유한한 표본을 대상으로 계산되어 일반적인 값과 다를 수 있으므로 상관계수의 유의성을 확인해야 한다. 확인은 t-기준을 사용하여 수행됩니다.

티 =

수신, y


√	n - 2


√	1 - R 2 x,y

(2.1)

임의 값 티스튜던트 t-분포를 따르고 t-분포 표에 따라 주어진 유의 수준 α에서 기준(t cr.α)의 임계값을 찾아야 합니다. 공식 (2.1)에 의해 계산된 모듈로 t가 t cr.α보다 작은 것으로 판명되면 다음 사이의 종속성은 랜덤 변수 X와 Y는 그렇지 않습니다. 그렇지 않으면 실험 데이터는 확률 변수의 종속성에 대한 가설과 모순되지 않습니다.

2.1. 우리가 얻는 공식 (2.1)에 따라 t-기준의 값을 계산하십시오:

티 =

-0.72028


√	26 - 2


√	1 - (-0.72028) 2

= -5.08680

2.2. t-분포 표에서 매개변수 t cr.α의 임계값을 결정합시다.

원하는 값 t kr.α는 자유도에 해당하는 행과 주어진 유의 수준 α에 해당하는 열의 교차점에 위치합니다.
우리의 경우 자유도 수는 n - 2 = 26 - 2 = 24 및 α = 0.05 , 이는 기준 t cr.α =의 임계값에 해당합니다. 2.064 (표 2 참조)

표 2 t-분포

자유도 수 (n - 2)	α = 0.1	α = 0.05	α = 0.02	α = 0.01	α = 0.002	α = 0.001
1	6.314	12.706	31.821	63.657	318.31	636.62
2	2.920	4.303	6.965	9.925	22.327	31.598
3	2.353	3.182	4.541	5.841	10.214	12.924
4	2.132	2.776	3.747	4.604	7.173	8.610
5	2.015	2.571	3.365	4.032	5.893	6.869
6	1.943	2.447	3.143	3.707	5.208	5.959
7	1.895	2.365	2.998	3.499	4.785	5.408
8	1.860	2.306	2.896	3.355	4.501	5.041
9	1.833	2.262	2.821	3.250	4.297	4.781
10	1.812	2.228	2.764	3.169	4.144	4.587
11	1.796	2.201	2.718	3.106	4.025	4.437
12	1.782	2.179	2.681	3.055	3.930	4.318
13	1.771	2.160	2.650	3.012	3.852	4.221
14	1.761	2.145	2.624	2.977	3.787	4.140
15	1.753	2.131	2.602	2.947	3.733	4.073
16	1.746	2.120	2.583	2.921	3.686	4.015
17	1.740	2.110	2.567	2.898	3.646	3.965
18	1.734	2.101	2.552	2.878	3.610	3.922
19	1.729	2.093	2.539	2.861	3.579	3.883
20	1.725	2.086	2.528	2.845	3.552	3.850
21	1.721	2.080	2.518	2.831	3.527	3.819
22	1.717	2.074	2.508	2.819	3.505	3.792
23	1.714	2.069	2.500	2.807	3.485	3.767
24	1.711	2.064	2.492	2.797	3.467	3.745
25	1.708	2.060	2.485	2.787	3.450	3.725
26	1.706	2.056	2.479	2.779	3.435	3.707
27	1.703	2.052	2.473	2.771	3.421	3.690
28	1.701	2.048	2.467	2.763	3.408	3.674
29	1.699	2.045	2.462	2.756	3.396	3.659
30	1.697	2.042	2.457	2.750	3.385	3.646
40	1.684	2.021	2.423	2.704	3.307	3.551
60	1.671	2.000	2.390	2.660	3.232	3.460
120	1.658	1.980	2.358	2.617	3.160	3.373
∞	1.645	1.960	2.326	2.576	3.090	3.291

2.2. t-기준과 t cr.α의 절대값을 비교합시다.

t-기준의 절대값은 임계값 t = 5.08680, tcr.α = 2.064보다 작지 않으므로 0.95의 확률로 실험 데이터(1 - α), 가설에 모순되지 않는다확률 변수 X와 Y의 의존성.

3. 선형 회귀 방정식의 계수를 계산합니다.

선형 회귀 방정식은 확률 변수 X와 Y 사이의 관계를 근사화(대략적으로 설명)하는 직선 방정식입니다. X가 자유롭고 Y가 X에 종속된다고 가정하면 회귀 방정식은 다음과 같이 작성됩니다.

Y = a + b X(3.1), 여기서:

수신, y

와이

σ x

수신, y

싸이

에스 엑스

(3.2),

a = M y - b M x (3.3)

식 (3.2)에 의해 계산된 계수 비선형 회귀 계수라고 합니다. 일부 출처에서 ㅏ~라고 불리는 상수 계수회귀 및 비변수에 따라.

주어진 값 X에 대한 예측 오차 Y는 다음 공식으로 계산됩니다.

값 σ y/x(공식 3.4)는 잔차 표준편차, 그것은 X의 고정된(주어진) 값에서 방정식(3.1)에 의해 설명된 회귀선으로부터 Y의 이탈을 특징으로 합니다.

Sy 2 / S x 2 = 0.20538 / 0.66481 = 0.30894. 마지막 숫자에서 제곱근을 추출합니다.
Sy/Sx = 0.55582

3.3 계수 b 계산공식 (3.2)에 의해

비 = -0.72028 0.55582 = -0.40035

3.4 계수 계산 a공식 (3.3)에 의해

ㅏ = 30.50000 - (-0.40035 25.75000) = 40.80894

3.5 회귀 방정식의 오류 추정.

3.5.1 S y 2 에서 제곱근을 추출하고 다음을 얻습니다.

= 0.31437
3.5.4 공식 (3.5)로 상대 오차를 계산해 보겠습니다.

δy/x = (0.31437 / 30.50000)100% = 1.03073%

4. 산점도(상관 필드)와 회귀선 그래프를 작성합니다.

산점도는 대응하는 쌍(x k , y k )을 평면의 점으로 그래픽 표현한 것입니다. 직교 좌표상관 필드는 연결된(쌍을 이루는) 샘플의 그래픽 표현 중 하나입니다. 동일한 좌표계에서 회귀선의 그래프도 그려집니다. 다이어그램이 가능한 한 명확하도록 축의 눈금과 시작점을 신중하게 선택해야 합니다.

4.1. 샘플 X의 최소 및 최대 요소는 각각 18번째 및 15번째 요소이며 x min = 22.10000 및 x max = 26.60000입니다.

4.2. 샘플 Y의 최소 및 최대 요소는 각각 2번째 및 18번째 요소이며, y min = 29.40000 및 y max = 31.60000입니다.

4.3. 가로축에서 점 x 18 = 22.10000의 바로 왼쪽에 있는 시작점을 선택하고 점 x 15 = 26.60000이 축에 맞고 다른 점들이 명확하게 구별되도록 축척을 선택합니다.

4.4. y축에서 y 2 = 29.40000 지점 바로 왼쪽에 있는 시작점을 선택하고, y 18 = 31.60000 지점이 축에 맞도록 축척과 다른 지점을 명확하게 구분합니다.

4.5. 가로축에 값 x k 를 배치하고 세로축에 값 y k 를 배치합니다.

4.6. 좌표 평면에 점 (x 1, y 1), (x 2, y 2), ..., (x 26, y 26)을 배치합니다. 아래 그림과 같이 산점도(상관 필드)를 얻습니다.

4.7. 회귀선을 그려봅시다.

이를 위해 좌표가 (x r1 , y r1) 및 (x r2 , y r2)인 두 개의 다른 점을 방정식 (3.6)을 만족하여 좌표 평면에 놓고 이를 통해 선을 그립니다. 첫 번째 점의 가로 좌표로 x min = 22.10000을 취합시다. 방정식 (3.6)에서 x min 값을 대입하면 첫 번째 점의 세로 좌표를 얻습니다. 따라서 좌표가 있는 점이 있습니다(22.10000, 31.96127). 유사하게, 우리는 값 x max = 26.60000을 가로 좌표로 설정하여 두 번째 점의 좌표를 얻습니다. 두 번째 점은 (26.60000, 30.15970)입니다.

회귀선은 아래 그림에서 빨간색으로 표시됩니다.

회귀선은 항상 X와 Y의 평균값, 즉 좌표 (M x , M y).