Excel에서 분석 도구 ANOVA 도구의 숫자 향상에 대 한

요약

숫자 개선을 각 세 분석 도구 ANOVA 도구에 설명 합니다. 이 문서에는 Microsoft Excel 2002에서 및 극단적인 경우에 이전 버전의 Excel에서 정확 하지 않은 결과가 보여 줍니다.

자세한 내용

많은 함수가 평균에 대 한 제곱 편차의 합을 계산을 해야합니다. 이것을 정확 하 게 Microsoft Office Excel 2003과 이후 Excel 버전 첫 번째 패스에서 평균을 찾고 두 번째 패스에서 평균에 대 한 편차 제곱된을 계산 합니다.를 두 단계 절차를 사용 합니다.

정확한 산술 연산에서 "계산기 수식은".를 사용 하 여 이전 버전의 Excel에서 동일한 결과 발생 Statisticians 컴퓨터 대신 계산기를 사용 하면 널리 사용에서 되었기 때문에이 수식은 때문 이라고 합니다. 계산기 수식을 사용 하 여 이전 버전의 Excel의 관찰, 제곱의 합계를 계산 하 고 뺍니다에서이 전체 다음 수량:
((sum of observations)^2) / number of observations
이 계산은 한 통과 데이터에서에서 발생합니다.

계산기 수식은 유한 정밀도 산술, 극단적인 경우의 반올림 오류로 될. Excel 2002와 이전 버전의 Excel (VAR, STDEV, 기울기, 피어슨 등)는 평균에 대 한 제곱 편차의 합이 필요 하는 대부분의 함수에 대 한 계산기 수식을 사용 하 여. 그러나 이러한 버전의 Excel에서는 보다 강력해 두 단계 절차 DEVSQ CORREL 및 COVAR 함수에 대 한.

통계 컴퓨팅 전문가 계산기 수식을 사용 하지 않는 것을 권장 합니다. 계산기 수식은 텍스트 통계 계산에 대 한 "방법" 하지 않는 것으로 표시 됩니다. 불행 하 게도 세 가지 ATP (분석 도구) ANOVA 도구 Excel 2002 및 이전 버전의 Excel에서 계산기 수식을 또는 동등한 단일 패스 접근 방식을 널리 사용을 확인합니다.

Excel 2003과 이후 Excel 버전 두 단계 절차를 사용 하 여 모든 세 가지 ATP ANOVA 모델에 대 한. 이 문서에서는 ATP의 세 가지 ANOVA 모델로 계산 다음과 같은 개선.
  • 단일 요소
  • 복제를 사용 하 여 2 단계
  • 복제 없이 두 요소
이 문서는 나중에 이러한 모델을 설명합니다.

Excel에서 항상 두 단계 절차 DEVSQ을 사용 하기 때문에이 여기서 자주 사용 하는 향상 된 절차를 설명 하는. 이러한 수정 된 절차를 효과적으로 DEVSQ 호출 또는 해당 기능을 정확히 DEVSQ의 기능 같은 코드를 사용 하 여.

각 ANOVA 도구에 대 한 출력 ATP 개수, 합계, 평균 및 차이, 값 사용 하 여 요약 테이블 및 다양 한 제곱합과 SS, df 값 ANOVA 테이블을 포함 MS, F 및 P-값입니다. 결과 요약 테이블의 개수, 합계, 평균 및 차이 Excel 함수를 호출 하 여 계산 합니다. 이러한 네 가지 함수 VAR만 반올림 오류는.

Excel 2002와 이전 버전의 Excel에 VAR 계산기 수식을 사용 하 여 구현 합니다. VAR에 대 한 다음 문서를 Excel 2003과 이후 Excel 버전의 향상에 설명 합니다. 이 문서에서는 반올림 오류는 이전 버전의 Excel에서 발생 하는 경우 보려면 숫자 데이터 시험해 볼 수도 있습니다.

VAR에 대 한 자세한 내용은 Microsoft 기술 자료의 다음 문서 번호를 클릭 합니다.

826112 excel 통계 함수: VAR



세 가지 ANOVA 모델로 설명 합니다 같이 ANOVA 출력 테이블에 설명 합니다. 각각의 경우에서 요약 테이블은 Excel 2003과 이후 Excel 버전에서 올바르게 동작 합니다. Excel 2002 및 이전 버전의 Excel에서 데이터 극단적인 값을 가질 때 차이 열에서 문제가 발생 합니다.

그러나이 문서의 부록에서 수정 된 예제 검토할 때 이러한 테이블이 비교에 유용한 때문에 요약 테이블 모델 섹션에 포함 합니다.

모델 1: 단일 요소

데이터가 있는 간단한 예는 다음과 같습니다.
기본 모델 1 분산 분석:
123
244
365
486
57
68
: 일원 배치법
요약
그룹개수합계평균분산
열 16213.53.5
2 열42056.666667
3 열6335.53.5
분산 분석
원본 변형SSdfMSFP-값F 긴급 상황
그룹 간에12.7526.3751.5068180.2578973.805567
그룹 내에서55134.230769
합계67.7515
Excel 2002와 이전 버전의 Excel 제곱의 합계를 계산 하려면 다음의 의사 코드를 사용 합니다.
GrandSum = 0;GrandSumOfSqs = 0; 
GrandSampleMeanSqrd = 0;
GrandMeanSqrd = 0;
GrandSampleSize = 0;

For s = 1 to Number_of_Samples do
GrandSum = GrandSum + sum of observations in s-th sample;
GrandSumOfSqs = GrandSumOfSqs + sum of squared observations in s-th sample;
GrandSampleMeanSqrd = GrandSampleMeanSqrd +
(sum of observations in s-th sample^2)/size of s-th sample;
GrandSampleSize = GrandSampleSize + size of s-th sample
Endfor;

GrandMeanSqrd = (GrandSum^2) / GrandSampleSize;

TotalSS = GrandSumOfSqs – GrandMeanSqrd;
BetweenGroupsSS = GrandSampleMeanSqrd – GrandMeanSqrd;
WithinGroupsSS = GrandSumOfSqs – GrandSampleMeanSqrd;

계산기 수식은 기본적으로이 방법은 것입니다. 이 이렇게 관찰, 제곱의 합계를 계산 하 고 VAR 관찰, 제곱의 합을 계산 하 고 뺀 후 다음의 수량을 뺍니다.
데이터의 합^2 /크기 예제. 모델 2 및 3 모델에 대 한 비슷한 의사 코드는 생략 되었습니다.

다시 모델 2 및 3 모델에 대 한 제곱의 합을 계산 하 고 수량 계산기 수식은와 제곱의 합에서 뺍니다. 아쉽게도 기본 통계의 텍스트 자주 제안 방법 분산 분석에 대 한이 문서의 앞부분에서 표시 된 것과 같은.

Excel 2003과 이후 Excel 버전 SS 열의 ANOVA 테이블의 다양 한 항목을 계산 하는 다른 방법을 사용 합니다. 설명을 위해이 문서에서는 B6 및 B7 셀에 누락 된 데이터를 사용 하 여 이전 예제에서는 숫자 데이터 셀 A2:C7에 나타납니다.
  • SS 총 DEVSQ(A2:C7) 등의 모든 데이터에 적용 된 DEVSQ 뿐입니다. DEVSQ는 데이터가 없어진 경우에 올바르게 작동 합니다.
  • SS 그룹 사이 DEVSQ DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7) 등의 각 열에 적용 된 총을 뺀 전체 SS입니다.
  • 그룹 SS 내에서 그룹 SS 간 뺀 전체 SS입니다.
SS 열의 ANOVA 테이블의 항목을 올바르게 계산 하는 경우 테이블의 다른 항목의 정확성이 따릅니다.

모델 2: 두 요소 복제

데이터가 있는 간단한 예는 다음과 같습니다.
분산 분석 2 기본 모델그룹 1그룹 2그룹 3
시험 1123
244
365
시험 2486
5107
6128
분산 분석: 두 요소 복제
요약그룹 1그룹 2그룹 3합계
시험 1
개수3339
합계6121230
평균2443.333333
분산1412.5
시험 2
개수3339
합계15302166
평균51077.333333
분산1416.25
합계
개수666
합계214233
평균3.575.5
분산3.5143.5
분산 분석
원본 변형SSdfMSFP-값F 긴급 상황
샘플72172366.22E-054.747221
37218.59.250.0037093.88529
상호 작용924.52.250.1479733.88529
내에서24122
합계14217
다시 SS 열의 항목을 올바르게 계산 하는 경우 출력 ANOVA 일부가 다른 모든 항목의 정확성을 따릅니다.

Excel 2003과 이후 Excel 버전에 대 한 계산 절차는 다음과 같습니다. 이 절차는 DEVSQ SS 열의 ANOVA 테이블의 다양 한 항목을 계산 하는. 그림에 대해 숫자 데이터 셀 B2:D7에 나타나는지 가정이 합니다.
  • SS 총 DEVSQ(B2:D7) 등의 모든 데이터에 적용 된 DEVSQ 뿐입니다.
  • SS 샘플 DEVSQ DEVSQ(B2:D4) + DEVSQ(B5:D7) 같은 각 샘플에 적용의 합을 뺀 전체 SS입니다.
  • SS 열 DEVSQ DEVSQ(B2:B7) + DEVSQ(C2:C7) + DEVSQ(D2:D7) 등의 각 열에 적용 된 총을 뺀 전체 SS입니다.
  • SS 내에서 DEVSQ 각 평가판 또는 DEVSQ(B2:B4) + DEVSQ(C2:C4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7) 같은 그룹 쌍에 적용 된의 합계가입니다.
  • SS 상호 작용 전체 SS 샘플 SS 열 SS SS 내에서 뺀 뺀 뺀 것과 같습니다.

모델 3: 2 요소 없이 복제

데이터가 있는 간단한 예는 다음과 같습니다.
분산 분석 3 기본 모델:낮음규모
안녕
불량123
244
365
중간 클래스486
5107
6128
풍부한71410
8126
9102
분산 분석: 두 요소 없이 복제
요약개수합계평균분산
불량3621
3103.3333331.333333
3144.6666672.333333
중간 클래스31864
3227.3333336.333333
3268.6666679.333333
풍부한33110.3333312.33333
3268.6666679.333333
321719
낮음
94557.5
규모
9788.66666716
안녕9515.6666676.25
분산 분석
원본 변형SSdfMSFP-값F 긴급 상황
176.6667822.083335.760870.0014762.591094
68.66667234.333338.9565220.0024553.633716
오류61.33333163.833333
합계306.666726
SS 열의 값을 올바르게 계산 ANOVA 테이블에 있는 모든 값의 정확도 다음과 같습니다.

Excel 2003과 이후 Excel 버전 계산 다음 절차를 사용합니다. SS 열의 ANOVA 테이블의 값을 계산 하 DEVSQ 사용 하는 프로시저. 설명을 위해이 예제는 이전 예제에 표시 된 셀 범위 a1: d10 셀 이라고 가정 합니다. 따라서, 숫자 데이터 셀 b2: d 10에 나타납니다.
  • SS 총 DEVSQ(B2:D10) 등의 모든 데이터에 적용 된 DEVSQ 뿐입니다.
  • SS 행 DEVSQ DEVSQ(B2:D2) + DEVSQ(B3:D3) + DEVSQ(B4:D4) + DEVSQ(B5:D5) + DEVSQ(B6:D6) + DEVSQ(B7:D7) + DEVSQ(B8:D8) + DEVSQ(B9:D9) + DEVSQ(B10:D10) 등의 각 행에 적용의 합을 뺀 전체 SS입니다.
  • SS 열 DEVSQ DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10) 등의 각 열에 적용 된 총을 뺀 전체 SS입니다.
  • SS 오류 행 SS 열 SS 뺀 뺀 전체 SS입니다.

Excel 2002와 이전 버전 Excel의 결과

극단적인 경우는 데이터에 많은 유효 자릿수가 가지 있지만 작은 차이, 계산기 수식을 정확 하지 않은 결과를 안내 하는. 이 문서의 뒷부분에 나오는 나타나는 부록 반올림 문제의 예로 그러한 극한 상황 제공 합니다.

Excel 2003과 이후 Excel 버전의 결과

Excel 2003과 이후 Excel 버전 두 데이터를 통과 하는 절차를 사용 합니다. 첫 번째 패스에서 Excel 2003과 이후 Excel 버전 계산의 합계와 데이터 값의 수입니다. 이러한 경우에서 Excel (평균) 표본 평균을 계산할 수 있습니다.

두 번째 패스에서 각 데이터 요소 및 표본 평균이 제곱된의 차이점을 계산 제곱이 더. 따라서 Excel 2003과 이후 Excel 버전에서 결과가 더 안정적입니다.

결론

2 패스 접근에는 모두 세 가지 ATP ANOVA 도구 Excel 2003과 이전 Excel 버전을 비교 하 여 이후 버전의 Excel에서에서 숫자 성능이 향상 됩니다. Excel 2003과 이후 Excel 버전을 사용 하 여 얻은 결과 이전 버전의 Excel 사용 하 여 얻은 결과 보다 덜 정확한 수 없습니다.

그러나 대부분의 실제 경우에 차이가 없는이 결과입니다. 즉, 데이터 다음 부록 보여 주는 비정상적인 동작 종류를 일반적으로 발생 하지 않습니다. 숫자의 불안정성은 데이터가 비교적 작은 변형 사이의 데이터 값 함께 유효 자릿수가 많은 수를 포함 하는 경우 이전 버전의 Excel에서는 발생할 가능성이 가장 높습니다.

이전 버전의 Excel 사용 하 고 여부를 Excel 2003 또는 Excel의 이후 버전 사용 하면 다른 ANOVA 결과 확인 하려면 ANOVA 도구 DEVSQ 사용 하는 절차를 사용 하 여 얻은 결과 사용 하 여 이전 버전의 Excel에서는 얻은 결과 비교 합니다.

참고: DEVSQ 사용 하는 절차는 각 도구와 연결 된 ANOVA 테이블에 대 한이 문서의 앞부분에서 설명 했습니다.

DEVSQ (범위)를 사용 하 여 차이 요약 표에 각 범위에 대해 정확한 지를 확인 하려면 / (수 (범위)-1).

Excel 2002의 성능과 이전 버전의 Excel의 부록: 숫자 예

1, 2 및 3 모델에 있는 각 기본 등이 여기서 이전에 ATP 도구 출력을 제공합니다. 이것의 요약 및 ANOVA 테이블을 포함합니다. 각 예제 "스트레스" 예제를 만들려면 데이터 수정 되었습니다. 10 추가 하 여 이렇게 ^ 각 데이터 값에는 8. 상수 10과 같은 추가 ^ 차이 요약 테이블에서 영향을 주지 않습니다 (있지만 평균 및 합계 확실 한 방법으로 적용 됩니다) 각 데이터 값에는 8. 것도 영향을 미치지 않습니다 ANOVA 테이블의 항목.

ANOVA 테이블의 요약 테이블 및 SS의 차이 비교 하는 경우는 이러한 모든 잘못 계산 된다 세 항목을 가리키는 3 모델에서 제외한 다음 스트레스 모델에 모두 알 수 있습니다 "<---".

스트레스는 모든 경우에 Excel 2003 및 이후 Excel 버전을 사용 하 여 얻은 분산 분석 결과 (원래 대로) 기본 경우에서 이전 결과 함께 동의 합니다.

ANOVA 1 과도 큰 데이터 값 사용 하 여 모델

100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
: 일원 배치법
요약
그룹개수합계평균분산
열 166000000211E + 084.8
2 열44000000201E + 088
3 열66000000331E + 081.6
분산 분석
원본 변형SSdfMSFP-값F 긴급 상황
그룹 간에020013.805567
그룹 내에서64134.923077
합계6415

ANOVA 2 과도 큰 데이터 값 사용 하 여 모델

그룹 1그룹 2그룹 3
시험 1100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
시험 2100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
분산 분석: 두 요소 복제
요약그룹 1그룹 2그룹 3합계
시험 1
개수3339
합계3000000063000000123000000129E + 08
평균1000000021000000041000000041E + 08
분산0404
시험 2
개수3339
합계3000000153000000303000000219E + 08
평균1000000051000000101000000071E + 08
분산0406
합계
개수666
합계600000021600000042600000033
평균100000004100000007100000005.5
분산4.814.41.6
분산 분석
원본 변형SSdfMSFP-값F 긴급 상황
샘플64164240.0003674.747221
3221660.0156253.88529
상호 작용3221660.0156253.88529
내에서32122.666666667
합계12817

ANOVA 3 과도 큰 데이터 값 사용 하 여 모델

낮음규모
안녕
불량100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
중간 클래스100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
풍부한100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
분산 분석: 두 요소 없이 복제
요약개수합계평균분산
행 133000000061000000020
2 행33000000101000000032
3 행33000000141000000052
4 행33000000181000000064<---
5 행33000000221000000076
6 행330000002610000000910
7 행330000003110000001012
8 행330000002610000000910
9 행330000002110000000718
열 199000000451000000058
2 열990000007810000000914
3 열99000000511000000064
분산 분석
원본 변형SSdfMSFP-값F 긴급 상황
12881620.1132812.591094
3221620.1677723.633716
오류128168
합계28826
속성

문서 ID: 829215 - 마지막 검토: 2017. 2. 7. - 수정: 1

피드백