????? ???? ????? ??????? ?? ANOVA Analysis ToolPak ?? Excel

?????? ????????? ?????? ?????????
???? ???????: 829215 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

??? ??? ??????? ????????? ??????? ?? ?? ?? ????? ????? Analysis ToolPak ANOVA. ??? ??????? ??? ???? ????? ???? ?? Microsoft Excel 2002 ? ?? ??????? ????? ?? Excel ?? ??????? ??????.

??????? ????

????? ?????? ?? ??????? ???? ????? ?????????? ????????? ??? ???. ?????? ??? ???? ?? Microsoft Office Excel 2003 ?????????? ?? Excel? ?????? ????? ???????? ???? ???? ?? ????? ??? ??????? ?????, ?? ?? ???? ?????????? ????????? ??? ????? ??? ??????? ??????.

?? ??????? ??????, ???? ??? ??????? ?? ??????? ????? ?? Excel ???? ?????? ' ?????? ??????? ". ??? ???? ??? ?????? ????? ??? ????????? ?????? ??? ??????? statisticians calculators ????? ?? ????? ?????????. ?? ??????? ??????? ????????? ??????? ?? Excel ??? ?????? ????????? ?? ??? ?? ??? ?? ??? ???????? ?????? ???????:
((sum of observations)^2) / number of observations
???? ??? ?????? ?? ???? ????? ??? ????????.

?? ??? ???? ?????? ?? ?????? ??????? ?? ????? ????? roundoff ?? ??????? ??????. Excel 2002 ? ????????? ??????? ?? Excel ??????? ?????? ??????? ?? ???? ??????? ???? ????? ????? ?????????? ????????? ??? ??? (??? STDEV VAR, ?? SLOPE, ? PEARSON). ??? ????????? ?? Excel ???? ????? ?????? ??????? ???????? ???? ???? ?????? ?? ??????? CORREL COVAR, ? DEVSQ.

??????? ?? ???? ?????????? ???????? ???? ?? ???????? ?????? ???????. ?????? ??????? ??? ????? ?? " ??? ????? ?????? ??? " ?? ???? ??? ???? ??????????. ?????? ?? ????? ANOVA Analysis ToolPak (ATP) ???? ????? ??? ??????? ???? ?? ?????? ??????? ?? ??? ??????? ??????? ?????? ????? ?? Excel 2002 ? ?? ????????? ??????? ?? Excel.

Excel 2003 ?????????? ?? Excel? ?????? ??????? ???????? ??????? ????? ?????? ANOVA ATP ???????. ????? ??? ??????? ????????? computational ??????? ?? ????????? ANOVA ????? ATP ?????:
  • ???? ????
  • ????? Factor ?? ????? ????????
  • ????? Factor ???? ????? ????????
??? ??????? ????? ??? ???????? ???? ???.

??? ??????? ???? Excel ?????? ??? ??????? ??????? ???????? ?? DEVSQ, ????? ????????? ??????? ?? ?? ??? ????????? ???????. ??? ????????? ??????? ??????? ??????? DEVSQ ?? ?????? ????????? ???????? ?????? ??????? ?? ?? ?????? ??? ????? DEVSQ 's.

ATP ??????? ?? ??? ?? ????? ANOVA ????? ??? ???? ???? ???????? ??? ?? ????? Count ? Average, ? ?????, ? ???? ANOVA ?? ????????? ?????? ?? ???????? ?????? ?? SS, df, MS-DOS ? F ? ???? P-. ??????? ?? ?????? ??????? ??? ?????? ?????? ??????? ????? Excel SUM COUNT ? AVERAGE, ? VAR. ?? ??? ???????? ????? ??? ????? ????? roundoff VAR ???.

Excel 2002 ? ????????? ??????? ?? Excel ?????? VAR ???????? ?????? ???????. ??? ?????? ?????? ??? VAR ????????? ???? ???? ?? Excel 2003 ?? ?? ??????? ???? ?? Excel. ??? ??????? ??? ???? ?? ??????? ?????? ????? ??????? ????? ???? ????? roundoff ?? ??????? ?? ???? ?? ????????? ??????? ?? Excel.

?????? ??? ???? ?? ????????? ??? VAR, ???? ??? ??? ?????? ?????? ???? ??????? ?? ????? ?????Microsoft:
826112 ?? Excel ????? ???????: VAR


??? ????? ??? ??????? ???????? ANOVA ???? ?? ???? ???? ??? ??????? ??????? ANOVA. ?? ?? ??????? ???? ??????? ???? well-behaved ?? Excel 2003 ?? ?? ??????? ???? ?? Excel. ?? Excel 2002 ? ?? ??????? ????? ?? Excel, ???? ????? ??? ???? ?????? ????? ?????? ?? ?????? ?????.

??? ??????? ??? ???? ????? ??????? ???? ?? ??????? ???? ??? ??? ??????? ???? ????? ?? ??? ?????? ??? ?????? ??????? ?? ?????? ?? ??????.

???? ???? ???? 1:

???? ???? ?? ???????? ?? ??? ???.
?? ??? ??????????? ??? ??????
BASIC MODEL ANOVA 1:
123
244
365
486
57
68
???? ???? Anova:
SUMMARY
???????????????????????????
?????? 1 6213.53.5
?????? 2 42056.666667
?????? 3 6335.53.5
ANOVA
???? ?? ??????? SSdfMS-DOSF?????? P- crit F
??? ????????? 12.7526.3751.5068180.2578973.805567
??? ??????? 55134.230769
??????67.7515
Excel 2002 ? ????????? ??????? ?? Excel ????????? ?? pseudocode ??????? ????? ?? ????????? ????????:
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 / sample. ?? ?? ??? pseudocode ????? ?? ???? 2 ? 3 ????.

??? ????? ???????? ???? 2 ??????? 3, ????? ???? ?? ??????? ?? ????? ???????? ??? ?? ?????? ??????? ??? ?????? ????????? ?? ????????. ???? ???????? ???????? ???? ????? ????? ?????? ????? ?? ANOVA ??? ??? ???? ??? ????? ?????? ?? ??? ???????.

Excel 2003 ?????????? ?? Excel? ?????? ????? ?????? ????? ????????? ???????? ?? ?????? ?? ?????? ANOVA SS. ??????? ???????, ????? ??? ??????? ???? ???????? ??????? ?? ?????? ?????? ?? ??????? A2:C7 ?? ?????? ?????? ?? ??????? B7 ? B6.
  • SS ????????? ?? ????? ??? ???? ???????? ??? DEVSQ(A2:C7) DEVSQ ???. DEVSQ ???? ???? ???? ??? ??? ???????? ??????.
  • ??? ??????? ?SS ?? ???? ????? DEVSQ ?????? ??? ?? ????, ??? DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7) SS ???????.
  • ??? ??????? ?SS ?? SS ??????? ???? SS ??? ???????.
??? ?? ??? ?????? ????????? ?? ?????? ?? ?????? ANOVA SS ???? ????? ???? ??? ????????? ?????? ?? ??????.

???? 2: ??????-Factor ?? ????? ????????

???? ???? ?? ???????? ?? ??? ???.
?? ??? ??????????? ??? ??????
BASIC ANOVA 2 MODEL???????? 1 ???????? 2 ?????? 3
????????? 1 123
244
365
????????? 2 486
5107
6128
Anova: ??????-Factor ?? ????? ????????
SUMMARY???????? 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
ANOVA
???? ?? ??????? SSdfMS-DOSF?????? P- crit F
????72172366.22E-054.747221
?????37218.59.250.0037093.88529
?????924.52.250.1479733.88529
???24122
??????14217
??? ????? ????? ?? ???? ????????? ?????? ?? ????? ANOVA ?? ??????? ???? ??? ??? ??? ???? ????????? ?? ???? SS ?????.

??? ?? ????? computational ?? Excel 2003 ??? ??? ????????? ?????? ?? Excel. ?????? ??? ??????? DEVSQ ????? ????????? ???????? ?? ?????? ?? ?????? ANOVA SS. ??????? ???????, ????? ??? ?????? ???? ???????? ??????? ?? 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 ???????.
  • ?? ????? DEVSQ ??????? ??? ?? ????? ?? ??? ????????, ??? + DEVSQ(C2:C4) DEVSQ(B2:B4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7) ???? SS.
  • ????? ????? SS SS ??????? ???? SS Sample ???? SS ??????? ???? SS ????.

???? 3: ??????-Factor ???? ????? ????????

???? ???? ?? ???????? ?? ??? ???.
?? ??? ??????????? ??? ??????
BASIC ANOVA 3 MODEL:LOW MED HI
POOR123
244
365
??? CLASS 486
5107
6128
RICH71410
8126
9102
Anova: ??????-Factor ???? ????? ????????
SUMMARY????????????????????
POOR3621
3103.3333331.333333
3144.6666672.333333
??? CLASS 31864
3227.3333336.333333
3268.6666679.333333
RICH33110.3333312.33333
3268.6666679.333333
321719
LOW 94557.5
MED 9788.66666716
HI9515.6666676.25
ANOVA
???? ?? ??????? SSdfMS-DOSF?????? P- crit F
??????176.6667822.083335.760870.0014762.591094
?????68.66667234.333338.9565220.0024553.633716
???61.33333163.833333
??????306.666726
??? ??? ??? ???? ????? ?? ???? SS ????? ???? ????? ?? ???? ??? ???? ?? ?????? ANOVA.

Excel 2003 ?????????? ?? Excel? ?????? ??????? ?????? computational. ?????? ??????? DEVSQ ????? ????? ?? ?????? ?? ?????? ANOVA SS. ??????? ???????, ????? ??? ?????? ?? ?????? ?? ??????? ?? ?????? ?? ?????? ?????? ?? A1:D10 ???????. ????, ???? ???????? ??????? ?? B2:D10 ???????.
  • SS ????????? ?? ????? ??? ???? ???????? ??? DEVSQ(B2:D10) DEVSQ ???.
  • SS ?????? ??? SS ??????? ???? ????? DEVSQ ?????? ??? ?? ??? ??? DEVSQ(B9:D9) + DEVSQ(B8:D8) + DEVSQ(B7:D7) + DEVSQ(B6:D6) + DEVSQ(B5:D5) + DEVSQ(B4:D4) + DEVSQ(B3:D3) DEVSQ(B2:D2) + DEVSQ(B10:D10) +.
  • ??????? ?SS ?? ???? ????? DEVSQ ?????? ??? ?? ????, ??? DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10) SS ???????.
  • ??? SS ?? SS ??????? ???? SS ???? ???? SS ???????.

??????? ?? Excel 2002 ? ?? ????????? ??????? ?? Excel

?? ??????? ?????? ??? ???? ?? ??????? ???????? ????? ?? ???????? ???? ????? ????? ????? ?? ?????? ??????? ???? ??? ????? ??? ????. ?????? ???? ???? ???? ??? ?? ??? ??????? ???? ????? roundoff ????? ?? ??? ??? ??????? ??????.

??????? ?? Excel 2003 ? ?? ????????? ?????? ?? Excel

Excel 2003 ?????????? ?? Excel? ?????? ????? ???? ???? ?????? ??? ????????. ??? ??????? ?????, Excel 2003 ?????????? ?? Microsoft Excel ???? ????? ? ??? ??? ????????. Excel ?? ???, ???? ???? ????? ???? (?????).

?? ??????? ??????, Excel ????? ????????? ????? ??? ??? ???? ?????? ?? ????? ?????? ?? ??? ?? ??? ?????? ????????? ???. ?????? ????? ??????? ?? Excel 2003 ?? ?? ??????? ???? ?? Excel ?? ???? ???????? ??????.

conclusions

????? ???????? ?????? ?????? ?? ??????? ?? ???? ????? ANOVA ATP ??????? ?? Excel 2003 ?? ?? ??????? ???? ?? Excel compared ??? ??????? ????? ?? Excel. ??????? ???? ???? ???????? Excel 2003 ?? ????????? ?????? ?? Excel ?? ??? ??? ??? ?? ??????? ???? ???? ???????? ??????? ????? ?? Excel.

?? ?????? ???? ???????? ??? ???? ???? ?? ???? ??? ??? ??? ???????. ???? ???? ???? ??? ???? ????????? ???? ?????? ?????? ???? ??? ????? ????????. ??????? ??????? ?? ?????? ???????? ?? ???? ?? ????????? ??????? ?? Excel ????? ????? ?????? ??? ????? ?? ??????? ???????? ??? ????? ????? ?????? ??? ??? ????????.

??? ??? ?????? ????? ???? ?? Excel, ???? ??? ??? ???? ?????? Excel 2003 ?? ????? ???? ?? Excel ????? ???? ANOVA ????? ?????? ?? ?????? ??????? ???? ???? ??? ??????? ??????? ANOVA ?? ??????? ?????? ?? Excel ?? ??????? ???? ???? ??? ??????? ????????? ???? ?????? DEVSQ.

?????? ?? ??? ????????? ???? ?????? DEVSQ ?????? ?? ??? ??????? ANOVA ?????? ???? ????? ?? ?? ???????.

?? ?????? ?? ??? Variances ?? ?????? ???? ?? ?? ???? ?? ?????? DEVSQ ( ???? ) / (COUNT û ( ???? ) 1).

????? Numeric ??????: ?????? ?? Excel 2002 ?????????? ??????? ?? Excel

??? ??????? ??? ???? ??????? ???????? ??? ?? ?????? 1 ? 2? ? 3, ?????? ????? ???? ATP ???????. ????? ??? ??????? ???? ? ANOVA. ?? ????? ???????? ?? ?? ?????? ?????? ???? stressed " ". ??? ??? ?? ???? ????? 10 ^ 8 ??? ?? ???? ??????. ????? ????? ?? ???? ??? 10 ^ ?? ???? ????? ?? ?????? ???? 8 ??? ?? ???? ?????? (???? ????? Average ? Sum ???? ???????). ?? ??? ???? ???? ????? ??? ?? ????? ?? ???? ANOVA.

??? ????? Variances ?? ??????? ???? ? SS ?? ??????? ANOVA, ?????? ??? ???? ?? ?? ?? ??? ??? ?????? ?? ?????? ?? ????????? stressed ??????? ???????? ????? ???? ?? ???? 3 ???? ???? ??? " <--- ".

ANOVA ??????? ???? ???? ???????? Excel 2003 ?? ????????? ?????? ?? Excel ?? ???? ???????, stressed ???? ?? ??????? ??????? ?? ??????? ???????? (??? ???).

????? ?? ??? ?????? ????? stressed ANOVA 1

?? ??? ??????????? ??? ??????
100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
???? ???? Anova:
SUMMARY
???????????????????????????
?????? 1 66000000211E + 084.8
?????? 2 44000000201E + 088
?????? 3 66000000331E + 081-6
ANOVA
???? ?? ??????? SSdfMS-DOSF?????? P- crit F
??? ????????? 020013.805567
??? ??????? 64134.923077
??????6415

????? ?? ??? ?????? ????? stressed ANOVA 2

?? ??? ??????????? ??? ??????
???????? 1???????? 2 ?????? 3
????????? 1 100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
????????? 2 100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
Anova: ??????-Factor ?? ????? ????????
SUMMARY???????? 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
ANOVA
???? ?? ??????? SSdfMS-DOSF?????? P- crit F
????64164240.0003674.747221
?????3221660.0156253.88529
?????3221660.0156253.88529
???32122.666666667
??????12817

3 ANOVA stressed ???? ?? ??? ?????? ?????

?? ??? ??????????? ??? ??????
LOW MED HI
POOR100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
??? CLASS 100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
RICH100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
Anova: ??????-Factor ???? ????? ????????
SUMMARY????????????????????
???? 1 33000000061000000020
???? 2 33000000101000000032
???? 3 33000000141000000052
?? 4 33000000181000000064<---
???? 5 33000000221000000076
???? 6 330000002610000000910
???? 7 330000003110000001012
???? 8 330000002610000000910
?? 9 330000002110000000718
?????? 1 99000000451000000058
?????? 2 990000007810000000914
?????? 3 99000000511000000064
ANOVA
???? ?? ??????? SSdfMS-DOSF?????? P- crit F
??????12881620.1132812.591094
?????3221620.1677723.633716
???128168
??????28826

???????

???? ???????: 829215 - ????? ??? ??????: 20/?? ?????/1427 - ??????: 2.0
????? ???
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
????? ??????: 
kbexpertisebeginner kbfunctions kbprogramming kbfuncstat kbinfo KB829215 KbMtar kbmt
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????. ??? ?? ???? ??? ??????? ????? ?? ???????? ?? ??? ???????? ?????? ????? ????????? ??????? ???? ??? ???????
???? ??? ????? ??????? ?????? ??????????829215

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com