Description d'amélioration numérique dans Analysis ToolPak ANOVA outil dans Excel

Traductions disponibles Traductions disponibles
Numéro d'article: 829215 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Cet article décrit des améliorations numériques chacun trois dans les outils Analysis ToolPak ANOVA. Cet article illustre également les résultats inexacts dans Microsoft Excel 2002 et des versions antérieures d'Excel dans situations extrêmes.

Plus d'informations

Plusieurs fonctions requièrent le calcul du somme d'écarts carrés sur une moyenne. Pour cela faire précisément et Excel 2003 et versions ultérieures d'Excel utilisent une procédure lors de la recherche de la moyenne au premier passage et une procédure calcule lors d'ensuite les écarts carrés sur la moyenne sur la seconde phase en deux phase.

Dans l'arithmétique précise, le même résultat se produit dans les versions antérieures d'Excel qui utilisent "la formule de Calculatrice" Cette formule est nommée donc parce qu'elle était en cours d'utilisation généralisée lorsque statisticiens ont utilisé des calculatrices au lieu d'ordinateurs. Avec la formule de Calculatrice versions antérieures d'Excel font le somme des carrés des observations et soustraient puis la quantité suivante de ce total :
((sum of observations)^2) / number of observations
Ce calcul se produit dans un seul passe dans les données.

La formule de Calculatrice est en précision arithmétique finie d'erreurs roundoff dans d'extrêmes cas. Excel 2002 et versions antérieures d'Excel utilisent la formule de Calculatrice pour plus plusieurs fonctions qui nécessitent un somme d'écarts carrés sur une moyenne (tel que VAR, ECARTYPE, PENTE et PEARSON). Toutefois ces versions d'Excel utilisent également la procédure plus numériquement robuste en deux phase pour les fonctions CORREL, COVARIANCE et DEVSQ.

Experts dans l'informatique statistique vous recommandent que vous n'utilisez pas la formule de Calculatrice. La formule de Calculatrice est présentée dans des textes sur l'informatique statistique "ne comme comment procéder pas". Tous trois des outils ANOVA Analysis ToolPak (ATP) utilise malheureusement la formule de Calculatrice ou une approche single-pass équivalente étendu dans Excel 2002 et des versions antérieures d'Excel.

Excel 2003 et versions ultérieures d'Excel utilisent la procédure en deux phase pendant les trois tous modèles ATP ANOVA. Cet article explique les améliorations de calcul suivantes dans les trois modèles ANOVA d'ATP :
  • Facteur seul
  • Two-factor de réplication
  • Two-factor sans réplication
Cet article explique ultérieurement ces modèles.

Parce qu'Excel a toujours utilisé la procédure avec DEVSQ en deux phase, cet article l'utilise fréquemment décrivant les procédures améliorées. Ces procédures révisées appellent DEVSQ ou utilisent efficacement le code de lequel le même de la fonctionnalité de DEVSQ est exactement fonctionnalité.

Pour chaque outil ANOVA sortie ATP contient une table de Résumé avec des valeurs de Count, Somme, Moyenne et Variation et une table ANOVA qui a différents sommes de carrés et sommes de valeurs de DD, df MS, F et P-value. Des résultats dans la table de résumé sont calculés en appelant les fonctions Excel COUNT, SUM, MOYENNE et VAR.. Le VAR seul de ces quatre fonctions, fait l'objet d'erreurs roundoff.

Excel 2002 et versions antérieures d'Excel implémentent VAR en utilisant la formule de Calculatrice. L'article suivant sur VAR décrit les améliorations qui se sont produites dans Excel 2003 et des versions ultérieures d'Excel. Cet article vous permet également de faire des essais avec des données numériques pour voir lorsqu' erreurs roundoff se produire dans des versions antérieures d'Excel est probable.

Pour plus d'informations sur VAR, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
826112 Vous Excel des fonctions statistiques : fonction VAR


Comme cet article explique trois les modèles ANOVA, il vous concentre sur les tables ANOVA de sortie. Dans chaque cas, les tables de Résumé sont well-behaved dans Excel 2003 et versions ultérieures d'Excel. Problèmes surviennent dans la colonne Variation quand données possèdent d'extrêmes valeurs dans Excel 2002 et des versions antérieures d'Excel.

Cependant cet article comprend les tables de Résumé dans les sections de modèle étant donné que ces tables sont utiles pour la comparaison lorsque vous révisez les exemples modifiés dans l'annexe.

Modèle 1 : facteur seul

Voici un exemple simple avec des données.
Réduire ce tableauAgrandir ce tableau
BASIC MODÈLE ANOVA 1 :
123
244
365
486
57
68
Variance : facteur seul
RÉSUMÉ
GroupesCountSommeMoyenneVariance
Colonne 1 6213.53.5
Colonne 2 42056.666667
Colonne 3 6335.53.5
ANOVA
Source de variation SSdfMSFP-value Crit F
Entre groupes 12.7526.3751.5068180.2578973.805567
Dans groupes 55134.230769
Total67.7515
Et Excel 2002 et versions antérieures d'Excel utilisent le pseudocode suivant pour calculer les sommes de carrés :
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;
Cette approche est essentiellement la formule de Calculatrice. Cette approche calcule les sommes de carrés d'observations et cette approche, puis soustrait une quantité d'ils comme VAR calcule le somme de carrés des observations et soustrait puis, simplement sum of observations^ 2 / exemple <VAR> dimensionne </VAR>. Le pseudocode similaire pour le modèle 2 et le modèle 3 été omis.

Pour le modèle 2 et le modèle 3, à nouveau sommes de carrés est calculé et une quantité est soustraite du somme de carrés comme la formule de Calculatrice. Fréquemment textes de statistique de base suggèrent malheureusement des approches pour ANOVA tel qu'une le affiché au début de cet article.

Et Excel 2003 et versions ultérieures d'Excel utilisent une approche différente pour calculer plusieurs les entrées dans la colonne SS du tableau ANOVA. Cet article d'illustration, suppose que les données numériques dans l'exemple précédent apparaissent avec des données manquantes dans les cellules B6 et B7 dans A2:C7 de cellules.
  • DD total est DEVSQ simplement appliqué à toutes les données tel que DEVSQ(A2:C7). DEVSQ fonctionne correctement même si donnée est manquante.
  • Total SS moins sommer DEVSQ appliqué à chaque colonne tel que DEVSQ(A2:A7) + DEVSQ(B2:B7) + DEVSQ(C2:C7) est entre Groups SS.
  • Dans des groupes, DD est DD totaux entre groupes DD.
Si des entrées dans la colonne SS du tableau ANOVA sont calculées correctement, suivre la précision de toutes les entrées de la table.

Model 2 : Two-factor de réplication

Voici un exemple simple avec des données.
Réduire ce tableauAgrandir ce tableau
BASIC MODÈLE ANOVA 2groupe 1 groupe 2 groupe 3
1 d'évaluation 123
244
365
2 d'évaluation 486
5107
6128
Variance : Two-factor de réplication
RÉSUMÉgroupe 1groupe 2 groupe 3 Total
1 d'évaluation
Count3339
Somme6121230
Moyenne2443.333333
Variance1412,5
2 d'évaluation
Count3339
Somme15302166
Moyenne51077.333333
Variance1416.25
Total
Count666
Somme214233
Moyenne3.575.5
Variance3.5143.5
ANOVA
Source de variation SSdfMSFP-value Crit F
Exemple72172366.22E - 054.747221
Colonnes37218.59.250.0037093.88529
Interaction924.52.250.1479733.88529
Dans24122
Total14217
À nouveau si des entrées dans la colonne SS sont calculées correctement, suit la précision des toutes toutes entrées dans la partie ANOVA de la sortie.

La procédure de calcul pour Excel 2003 et des versions ultérieures d'Excel existe. Cette procédure utilise DEVSQ pour calculer plusieurs les entrées dans la colonne SS du tableau ANOVA. Cet exemple d'illustration, suppose que les données numériques apparaissent dans B2:D7 de cellules.
  • DD total est DEVSQ simplement appliqué à toutes les données tel que DEVSQ(B2:D7).
  • Exemple de DD est Total SS moins sommer DEVSQ appliqué à chacun d'exemple tel que DEVSQ(B2:D4) + DEVSQ(B5:D7).
  • Colonnes DD sont Total SS moins sommer DEVSQ appliqué à chaque colonne tel que DEVSQ(B2:B7) + DEVSQ(C2:C7) + DEVSQ(D2:D7).
  • Sommer DEVSQ appliqué à chaque essai tel que DEVSQ(B2:B4) + DEVSQ(C2:C4) + DEVSQ(D2:D4) + DEVSQ(B5:B7) + DEVSQ(C5:C7) + DEVSQ(D5:D7) ou une paire de groupe est dans DD.
  • Interaction DD égale Total SS moins Sample SS moins Columns SS moins Within SS.

Modèle 3 : Two-factor sans réplication

Voici un exemple simple avec des données.
Réduire ce tableauAgrandir ce tableau
BASIC MODÈLE ANOVA 3 :FAIBLE MOYEN BONJOUR
MÉDIOCRE123
244
365
CLASSER MID 486
5107
6128
RICHE71410
8126
9102
Variance : Two-factor sans réplication
RÉSUMÉCountSommeMoyenneVariance
MÉDIOCRE3621
3103.3333331.333333
3144.6666672.333333
CLASSER MID 31864
3227.3333336.333333
3268.6666679.333333
RICHE33110.3333312.33333
3268.6666679.333333
321719
FAIBLE 94557.5
MOYEN 9788.66666716
BONJOUR9515.6666676.25
ANOVA
Source de variation SSdfMSFP-value Crit F
Lignes176.6667822.083335.760870.0014762.591094
Colonnes68.66667234.333338.9565220.0024553.633716
Erreu61.33333163.833333
Total306.666726
Si les valeurs de la colonne SS sont calculées correctement, la précision des toutes toutes valeurs de la table ANOVA suit.

Excel 2003 et versions ultérieures d'Excel procèdent comme suit de calcul. La procédure utilise DEVSQ pour calculer les valeurs dans la colonne SS du tableau ANOVA. Cet exemple d'illustration, suppose que la plage de cellules illustrée dans l'exemple précédent est cellules A1:D10. Par conséquent les données numériques apparaissent dans B2:D10 de cellules.
  • DD total est DEVSQ simplement appliqué à toutes les données tel que DEVSQ(B2:D10).
  • DD de lignes est Total SS moins sommer DEVSQ appliqué à chaque ligne tel que 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).
  • Colonnes DD sont Total SS moins sommer DEVSQ appliqué à chaque colonne tel que DEVSQ(B2:B10) + DEVSQ(C2:C10) + DEVSQ(D2:D10).
  • Erreur DD est Total SS moins Rows SS moins Columns SS.

Résultats dans Excel 2002 et des versions antérieures d'Excel

Dans cas extrêmes dans lequel plusieurs chiffres significatifs sont dans les données, mais une petite variance, la formule de Calculatrice entraîne également des résultats inexacts. Exemples de problèmes roundoff donne dans situations extrêmes telles l'annexe qui s'affiche dans la suite de cet article.

Résultats dans Excel 2003 et des versions ultérieures d'Excel

Excel 2003 et versions ultérieures d'Excel utilisent une procédure qui effectue deux passes dans les données. Excel 2003 et versions ultérieures d'Excel au premier passage, calculent le somme et le nombre des valeurs de données. Excel peut calculer exemple de la moyenne (moyenne) de cela.

Sur la seconde phase Excel calcule la différence carrée entre chaque point de données et exemple de la moyenne et fait puis le somme de ces différences carrées. Par conséquent les résultats dans Excel 2003 et des versions ultérieures d'Excel sont plus stables numériquement.

Conclusions

Une approche en deux phase améliore la performance numérique dans les trois tous outils ATP ANOVA dans Excel 2003 et des versions ultérieures d'Excel avec des versions antérieures d'Excel. Les résultats que vous obtenez en utilisant Excel 2003 et des versions ultérieures d'Excel ne sont jamais, moins précis que les résultats que vous obtenez en utilisant des versions antérieures d'Excel.

Cependant dans des cas plus pratiques, il n'existe aucune différence entre ces résultats. Il y a cela parce que données ne présentent pas généralement le type de comportement inhabituel que l'Annexe suivante illustre. S'instabilité numérique produire dans des versions antérieures d'Excel lorsque donnée contient un grand nombre d'avec peu de variation relativement entre des valeurs de données chiffres significatifs est plus probable.

Si vous utilisez une version antérieure d'Excel et souhaitez afficher si Excel 2003 ou une version ultérieure d'Excel vous donnent les résultats ANOVA différents, comparez les résultats que vous obtenez lorsque vous utilisez les outils ANOVA dans vos versions antérieures d'Excel avec les résultats que vous obtenez lorsque vous utilisez les procédures qui utilisent DEVSQ.

Remarque les procédures qui utilisent DEVSQ ont été décrites au début de cet article pour la table ANOVA associée aux chaque outils.

Pour vérifier que Variations sont correctes dans la table de Résumé pour chaque plage, utilisez DEVSQ ( plage) / (COMPTEZ ( plage) u 1).

Annexe : exemples numériques de la performance d'Excel 2002 et exemples de versions antérieures d'Excel

Cet article a pour chaque exemple de base de modèles 1, 2 et 3, précédemment présenté la sortie de l'outil ATP. Cela a inclus les tables Résumé et ANOVA. La donnée a été modifiée dans chaque exemple pour créer un exemple "insisté". 10 sont ajoutés pour cela^ 8 à chaque valeur de données. Ajoutez une constante tel que 10^ 8 à chaque valeur de données n'affecte pas Variation dans la table de Résumé ( mais affectera Moyenne et Sum en manières évidentes). Il ne doit également affecter aucune entrée de la table ANOVA.

Si vous comparez Variations dans les tables de Résumé et DD dans les tables ANOVA, vous noterez qu'all de cela ne sont pas calculés correctement dans trois tous des modèles stressed suivants sauf une entrée dans le modèle 3 qui est pointé "<B> <-</B> ".

Dans tous les cas insistés les résultats ANOVA que vous obtenez en utilisant Excel 2003 et des versions ultérieures d'Excel correspondent aux résultats précédents dans les cas de base (comme ils de devoir avoir accepter aux résultats).

ANOVA 1 a insisté le modèle avec des valeurs de données volumineuses

Réduire ce tableauAgrandir ce tableau
100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
100000004100000008100000006
100000005100000007
100000006100000008
Variance : facteur seul
RÉSUMÉ
GroupesCountSommeMoyenneVariance
Colonne 1 66000000211E + 084.8
Colonne 2 44000000201E + 088
Colonne 3 66000000331E + 081.6
ANOVA
Source de variation SSdfMSFP-value Crit F
Entre groupes 020013.805567
Dans groupes 64134.923077
Total6415

ANOVA 2 a insisté le modèle avec des valeurs de données volumineuses

Réduire ce tableauAgrandir ce tableau
groupe 1groupe 2 groupe 3
1 d'évaluation 100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
2 d'évaluation 100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
Variance : Two-factor de réplication
RÉSUMÉgroupe 1groupe 2 groupe 3 Total
1 d'évaluation
Count3339
Somme3000000063000000123000000129E + 08
Moyenne1000000021000000041000000041E + 08
Variance0404
2 d'évaluation
Count3339
Somme3000000153000000303000000219E + 08
Moyenne1000000051000000101000000071E + 08
Variance0406
Total
Count666
Somme600000021600000042600000033
Moyenne100000004100000007100000005.5
Variance4.814.41.6
ANOVA
Source de variation SSdfMSFP-value Crit F
Exemple64164240.0003674.747221
Colonnes3221660.0156253.88529
Interaction3221660.0156253.88529
Dans32122.666666667
Total12817

ANOVA 3 a insisté le modèle avec des valeurs de données volumineuses

Réduire ce tableauAgrandir ce tableau
FAIBLE MOYEN BONJOUR
MÉDIOCRE100000001100000002100000003
100000002100000004100000004
100000003100000006100000005
CLASSER MID 100000004100000008100000006
100000005100000010100000007
100000006100000012100000008
RICHE100000007100000014100000010
100000008100000012100000006
100000009100000010100000002
Variance : Two-factor sans réplication
RÉSUMÉCountSommeMoyenneVariance
Ligne 1 33000000061000000020
Ligne 2 33000000101000000032
Ligne 3 33000000141000000052
Ligne 4 33000000181000000064<---
Ligne 5 33000000221000000076
Ligne 6 330000002610000000910
Ligne 7 330000003110000001012
Ligne 8 330000002610000000910
Ligne 9 330000002110000000718
Colonne 1 99000000451000000058
Colonne 2 990000007810000000914
Colonne 3 99000000511000000064
ANOVA
Source de variation SSdfMSFP-value Crit F
Lignes12881620.1132812.591094
Colonnes3221620.1677723.633716
Erreu128168
Total28826

Propriétés

Numéro d'article: 829215 - Dernière mise à jour: mercredi 10 janvier 2007 - Version: 2.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
Mots-clés : 
kbexpertisebeginner kbfunctions kbprogramming kbfuncstat kbinfo KB829215 KbMtfr kbmt
Traduction automatique
TRADUCTION AUTOMATIQUE : Cet article technique a été traduit par un système automatique, c'est-à-dire sans aucune intervention humaine. Microsoft propose ce type d?articles en complément de ceux traduits par des traducteurs professionnels. Ces articles sont destinés principalement aux utilisateurs ne comprenant pas l'anglais en leur proposant, en complément, une version française de l?information technique fournie dans la version américaine et originale de l?article. Pour plus de détails, veuillez consulter la page http://support.microsoft.com/gp/mtdetails. Attention : Il est possible que certaines parties de l?article ne soient pas traduites par le système automatique et qu?elles soient restées rédigées en anglais. Microsoft ne garantit pas la qualité linguistique des traductions et ne peut être tenu responsable d?aucun problème, direct ou indirect, dû à une quelconque erreur de traduction du contenu ou de son utilisation par les utilisateurs.
La version anglaise de cet article est la suivante: 829215
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

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