Excel fonctions statistiques : loi

Résumé

Cet article décrit la fonction BINOMDIST dans Microsoft Office Excel 2003 et dans les versions ultérieures d’Excel, illustre l’utilisation de la fonction et compare les résultats de la fonction pour Excel 2003 et les versions ultérieures d’Excel avec ses résultats pour les versions antérieures d’Excel.

Microsoft Excel 2004 pour les informations Mac

Les fonctions statistiques dans Excel 2004 pour Mac ont été mises à jour en utilisant les mêmes algorithmes qu’ont utilisés pour mettre à jour les fonctions statistiques dans Excel 2003 et dans les versions ultérieures d’Excel. Les informations de cet article qui décrit le fonctionne d’une fonction ou comment une fonction a été modifiée pour Excel 2003 et les versions ultérieures d’Excel s’applique également à Excel 2004 pour Mac.

Plus d'informations

Lorsque cumulative = vrai, la fonction (x, n,
p, cumulé) fonction renvoie la probabilité de x ou moins de succès dans
essais de Bernoulli indépendants de n . Chacun des essais a une probabilité associée p de réussite (et probabilité 1 -p de défaillance). Lorsque
cumulative = FALSE, BINOMDIST renvoie la probabilité de succès de x exactement.

Syntaxe

BINOMDIST(x, n, p, cumulative)

Paramètres

  • x est un entier non négatif
  • n est un entier positif
  • 0 < p < 1
  • Cumulative représente une variable logique qui accepte les valeurs TRUE ou FALSE

Exemple d’utilisation

Vérifiez les hypothèses suivantes :
  • Au baseball, un tir d'un "batteur à 300" est réussi avec une probabilité de 0,300 à chaque fois qu'il frappe (à chaque essai).
  • Fois successives sont au bat d’essais de Bernoulli indépendants.
Vous pouvez utiliser le tableau suivant pour rechercher la probabilité qu’une PATE a FRIRE obtient exactement 0, 1, 2,..., ou 10 atteint dans les essais sur 10 et la probabilité que le BATTEUR obtient..., 0, 2 1 ou moins ou moins, des correspondances 9 ou moins, ou de 10 ou moins dans les essais sur 10.

Si le parfum obtient 50 accès dans ses 200 premiers essais (une moyenne de.250), il doit obtenir 100 réponses dans ses 300 ensuite essais d’avoir accès à 150 et une.300 moyenne de plus de 500 essais. Vous pouvez utiliser le tableau suivant pour analyser les risques que le BATTEUR Obtient un accès suffisant à maintenir sa moyenne. Commentateurs de baseball réfèrent fréquemment à la « loi de moyennes » lorsqu’ils disent qui ventilateurs ne pas à vous soucier de la performance de cette pâte à frire avec seulement 50 accès dans ses 200 premiers essais car » à la fin de la saison sa moyenne sera. 300. » Si les essais étaient réellement indépendants et le batteur avait réellement une chance de succès de 0,3 sur tout un essai, ce raisonnement est faux, car les résultats des 200 premiers essais n'affectent pas le succès ou l'échec sur les 300 derniers essais .

Pour illustrer l’utilisation de la fonction, créer une feuille de calcul Excel vierge, copier le tableau suivant, sélectionnez la cellule A1 dans votre feuille de calcul Excel vierge, puis collez les entrées de sorte que le tableau suivant remplisse A1:C22 de cellules dans votre feuille de calcul.
nombre d’essais10
probabilité de succès0,3
succès, xP (exactement x succès)P (x ou moins de succès)
0=BINOMDIST(A4,$B$1,$B$2,FALSE)=BINOMDIST(A4,$B$1,$B$2,TRUE)
1=BINOMDIST(A5,$B$1,$B$2,FALSE)=BINOMDIST(A5,$B$1,$B$2,TRUE)
2=BINOMDIST(A6,$B$1,$B$2,FALSE)=BINOMDIST(A6,$B$1,$B$2,TRUE)
3=BINOMDIST(A7,$B$1,$B$2,FALSE)=BINOMDIST(A7,$B$1,$B$2,TRUE)
4=BINOMDIST(A8,$B$1,$B$2,FALSE)=BINOMDIST(A8,$B$1,$B$2,TRUE)
5=BINOMDIST(A9,$B$1,$B$2,FALSE)=BINOMDIST(A9,$B$1,$B$2,TRUE)
6=BINOMDIST(A10,$B$1,$B$2,FALSE)=BINOMDIST(A10,$B$1,$B$2,TRUE)
7=BINOMDIST(A11,$B$1,$B$2,FALSE)=BINOMDIST(A11,$B$1,$B$2,TRUE)
8=BINOMDIST(A12,$B$1,$B$2,FALSE)=BINOMDIST(A12,$B$1,$B$2,TRUE)
9=BINOMDIST(A13,$B$1,$B$2,FALSE)=BINOMDIST(A13,$B$1,$B$2,TRUE)
10=BINOMDIST(A14,$B$1,$B$2,FALSE)=BINOMDIST(A14,$B$1,$B$2,TRUE)
300 essais, probabilité de succès 0,3 :
succès, xP (exactement x succès)P (x ou moins de succès)
89=BINOMDIST(A18,300,0.3,FALSE)=BINOMDIST(A18,300,0.3,TRUE)
90=BINOMDIST(A19,300,0.3,FALSE)=BINOMDIST(A19,300,0.3,TRUE)
99=BINOMDIST(A20,300,0.3,FALSE)=BINOMDIST(A20,300,0.3,TRUE)
100=BINOMDIST(A21,300,0.3,FALSE)=BINOMDIST(A21,300,0.3,TRUE)
101=BINOMDIST(A22,300,0.3,FALSE)=BINOMDIST(A22,300,0.3,TRUE)
Remarque Après avoir collé cette table dans votre nouvelle feuille de calcul Excel, cliquez sur le bouton Options de collage , puis cliquez sur Respecter la mise en forme de Destination. Avec la plage collée étant toujours sélectionnée, utilisez une des procédures suivantes, en fonction de la version d’Excel que vous exécutez :
  • Dans Microsoft Office Excel 2007, cliquez sur l’onglet accueil , cliquez sur Format dans le groupe cellules , puis cliquez sur Ajuster la largeur de colonne.
  • Dans Excel 2003 et dans les versions antérieures d’Excel, pointez sur
    Colonne dans le menu Format , puis cliquez sur
    Ajustement automatique.
Voulez-vous mettre en forme des B4:C22 de cellules pour une meilleure lisibilité cohérente (par exemple, les numéros de format à cinq chiffres après la virgule).

Cellules B4:B14 afficher les probabilités d’exactement
x 10 essais réussis. Le nombre probable de succès est 3. Les chances de 0, 6, 7, 8, 9 ou 10 succès sont chacun moins de 0,05 et ajouter à sur 0.076. Les chances de 1, 2, 3, 4 ou 5 succès est donc environ 1 – 0.076 = 0.924. Cellules de C4:C14 afficher les probabilités de
x ou moins de succès dans les essais sur 10. Vous pouvez vérifier que les entrées dans la colonne C dans n’importe quelle ligne sont chacune égale à la somme de toutes les entrées dans la colonne B, vers le bas, y compris de cette ligne.

B18:B20 afficher que le nombre probable de 300 essais réussis est de 90. Augmente à mesure que la probabilité de succès de x exactement
x s’élève à 90 et revient ensuite en tant que
x continue à augmenter de plus de 90. Les chances de succès de moins de 90 soient peu plus 50 %, comme le montre la C20. Les chances de succès de moins de 99 soient sur 0.884. Il est donc uniquement une chance 11.6 % (0.116 = 1 – 0.884) de 100 ou plus de succès.

Résultats dans les versions antérieures d’Excel

Knusel (voir la Remarque 1) documenté les instances dans lesquelles la loi ne renvoie pas une réponse numérique et génère des #NUM ! à la place en raison d’un dépassement de capacité numérique. Lorsque BINOMDIST renvoie des réponses numériques, ils ne sont pas corrects. BINOMDIST renvoie #NUM ! uniquement lorsque le nombre d’essais est supérieure ou égale à 1030. Il n’existe aucun problème calcul si n < 1030. Dans la pratique, ces valeurs élevées de n sont peu probable. Tel un grand nombre de tirages indépendants, un utilisateur peut souhaiter procéder au rapprochement de la distribution binomiale par une distribution normale si (
n*p and
n* (1 -p) sont suffisamment élevée, par exemple, chacun est supérieur à 30) ou par une loi de Poisson dans le cas contraire.

Remarque 1 Knusel, L. « sur l’exactitude des Distributions statistiques dans Microsoft Excel 97 », les statistiques de calcul et d’analyse de données (1998), 26 : 375-377.

Dans les cas non cumulative, BINOMDIST (x, n,
p, false) utilise la formule suivante
COMBIN(n,x)*(p^x)*((1-p)^(n-x))
COMBIN est une fonction Excel qui donne le nombre de combinaisons d’éléments de x dans une population
n éléments. COMBIN (n,x) est parfois écrit nCxet nommée « coefficient COMBINATOIRE » ou simplement, «n choisir
x". Si vous faites des essais avec COMBIN en tapant
=COMBIN(1029,515) d’une cellule et
=COMBIN(1030,515) dans une autre cellule, la première cellule renvoie un nombre astronomique, 1.4298E + 308, et la deuxième cellule #NUM ! dans la mesure où il est encore plus importante. Le dépassement de capacité de COMBIN provoque un dépassement de capacité de la loi dans les versions antérieures d’Excel.

COMBIN n’a pas été modifié pour Excel 2003 et les versions ultérieures d’Excel.

Résultats dans Excel 2003 et dans les versions ultérieures d’Excel

Étant donné que Microsoft a diagnostiqué lorsqu’un dépassement de capacité provoque la loi retourner #NUM ! et sait que la loi est valide lorsque le dépassement de capacité ne se produit pas, Microsoft a implémenté un algorithme conditionnel dans Excel 2003 et dans les versions ultérieures d’Excel.

L’algorithme utilise le code de fonction à partir de versions antérieures d’Excel (la formule de calcul mentionnée plus haut dans cet article) lorsque n < 1030. Lorsque n > = 1030, Excel 2003 et les versions ultérieures d’Excel utilisent l’algorithme de remplacement est décrite plus loin dans cet article.

En règle générale, COMBIN déborde car il est astronomique, mais
p^x et (1 -p) ^ (n-x) sont chaque infinitesimal. S’il était possible de les multiplier, le produit serait une probabilité réaliste entre 0 et 1. Cependant, parce que les arithmétique finie existant ne peut pas multiplier les, un autre algorithme évite l’évaluation de COMBIN.

L’approche de Microsoft calcule une somme non ajustée de toutes les probabilités de x exactement les réussites qui seront utilisées ultérieurement à des fins de mise à l’échelle. Il calcule également une valeur sans échelle de la probabilité que vous souhaitez BINOMDIST à retourner. Enfin, il utilise le facteur de mise à l’échelle pour renvoyer une valeur correcte de la loi.

L’algorithme tire parti du fait que le rapport entre les conditions successives du formulaire COMBIN (n,k) * (p^k) * ((1 -p) ^ (n-k)) est un formulaire simple. L’algorithme se poursuit comme indiqué dans le pseudo-code dans les étapes suivantes.

Etape 0 : (Initialisation). Initialiser le TotalUnscaledProbability et les propriétés de UnscaledResult à 0. Initialiser constante EssentiallyZero à un très petit nombre, par exemple, 10^(-12).

Étape 1 : Rechercher n*p et arrondi au nombre entier plus proche, m. Est le nombre probable de n essais réussis
m or m+1. COMBIN (n,k) * (p^k) * ((1 -p) ^ (n-k)) diminue lorsque k diminue à partir de
m m-1 pour
m-2 et ainsi de suite. En outre, COMBIN (n,k) * (p^k) * ((1 -p) ^ (n-k)) diminue lorsque k augmente à partir de
+ de 1 mpour m+ 2
m+ 3 et ainsi de suite.
TotalUnscaledProbability = TotalUnscaledProbability + 1;If (m == x) then UnscaledResult = UnscaledResult + 1;
If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;

Étape 2 : Calculer les probabilités non ajustées pour
k > m:
PreviousValue = 1;Done = FALSE;
k = m + 1;
While (not Done && k <= n)
{
CurrentValue = PreviousValue * (n – k + 1) * p / (k * (1 – p));
TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;
If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;
If (cumulative && k < x) then UnscaledResult = UnscaledResult +
CurrentValue;
If (CurrentValue <= EssentiallyZero) then Done = TRUE;
PreviousValue = CurrentValue;
k = k+1;
}
end While;

Étape 3 : Calculer les probabilités non ajustées pour
k < m:
PreviousValue = 1;Done = FALSE;
k = m - 1;
While (not Done && k >= 0)
{
CurrentValue = PreviousValue * k+1 * (1-p) / ((n – k) * p);
TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;
If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;
If (cumulative && k < x) then UnscaledResult = UnscaledResult +
CurrentValue;
If (CurrentValue <= EssentiallyZero) then Done = TRUE;
PreviousValue = CurrentValue;
k = k-1;
}
end While;

Étape 4 : Combiner les résultats sans échelle :
Return UnscaledResult/TotalUnscaledProbability;
Bien que cette méthode est utilisée uniquement pour n > = 1030, vous pouvez utiliser les ajouts suivants dans la feuille de calcul Excel pour vous aider à exécuter cet algorithme pour calculer la loi (3, 10, 0.3, TRUE)-main (dans l’exemple de base-ball, le risque d’accès de moins de 3 10 essais pour un parfum de.300).

Pour illustrer cela, copiez le tableau suivant, sélectionnez la cellule D4 de la feuille de calcul Excel que vous avez créé précédemment et puis collez les entrées de sorte que le tableau suivant remplisse D1:E15 de cellules dans votre feuille de calcul.
=D5*(1-$B$2)*(A4+1)/($B$2*($B$1-A4))=D4/$D$15
=D6*(1-$B$2)*(A5+1)/($B$2*($B$1-A5))=D5/$D$15
1=D6/$D$15
=D6*$B$2*($B$1-A7+1)/((1-$B$2)*A7)=D7/$D$15
=D7*$B$2*($B$1-A8+1)/((1-$B$2)*A8)=D8/$D$15
=D8*$B$2*($B$1-A9+1)/((1-$B$2)*A9)=D9/$D$15
=D9*$B$2*($B$1-A10+1)/((1-$B$2)*A10)=D10/$D$15
=D10*$B$2*($B$1-A11+1)/((1-$B$2)*A11)=D11/$D$15
=D11*$B$2*($B$1-A12+1)/((1-$B$2)*A12)=D12/$D$15
=D12*$B$2*($B$1-A13+1)/((1-$B$2)*A13)=D13/$D$15
=D13*$B$2*($B$1-A14+1)/((1-$B$2)*A14)=D14/$D$15
=SUM(D4:D14)
Colonne D contient les probabilités non ajustées. Le 1 dans la cellule D6 est le résultat de l’étape 1 de l’algorithme. Excel 2003 et les versions ultérieures d’Excel calculent les entrées dans les cellules D7, D8,..., D14 (dans cet ordre) à l’étape 2. Excel calcule les entrées dans les cellules D5 et D4 (dans cet ordre) à l’étape 3. La somme de toutes les probabilités sans échelle s’affiche dans D15.

Pour calculer la probabilité de succès de moins de 3, tapez la formule suivante dans une cellule vide :
= SUM(D4:D7)/D15
Dans l’exemple précédent, EssentiallyZero n’arrête pas l’étape 2 ou 3. Toutefois, si vous souhaitez évaluer BINOMDIST (550, 2000, 0,3, TRUE), EssentiallyZero peut arrêter étape 2 ou l’étape 3. Une variable aléatoire binomiale avec
n = 2000 et p = 0,3 a une distribution qui est à rapprocher de la normale moyenne de 600 et écart SQRT (2000 * 0,3 *(1 – 0.3)) = SQRT(420) = 20.5. Puis 805 est 10 écarts-types supérieurs à la moyenne et 395 10 écarts inférieurs à la moyenne. En fonction des paramètres de EssentiallyZero, EssentiallyZero peut cesser d’étape 2 avant d’atteindre 805 et peut arrêter étape 3 avant d’atteindre 395.

Conclusions

Imprécisions dans les versions d’Excel antérieures à Excel 2003 se produisent uniquement lorsque le nombre d’essais est supérieure ou égale à 1030. Dans ce cas, BINOMDIST renvoie #NUM ! dans les versions antérieures d’Excel, car un terme déborde dans une séquence de termes qui sont multipliés ensemble. Pour résoudre ce problème, Excel 2003 et les versions ultérieures d’Excel utilisent la procédure mentionnée plus haut dans cet article lorsque ce un dépassement de capacité se produirait dans le cas contraire.

La fonction POISSON, NEGBINOMDIST, CRITBINOM et loi présentent un comportement similaire dans les versions antérieures d’Excel. Ces fonctions renvoient également des résultats numériques corrects ou #NUM ! ou #DIV/0 !. Là encore, les problèmes sont dus à des débordement (ou dépassement de capacité négatif).

Il est facile de déterminer quand et comment ces problèmes se produisent. Excel 2003 et les versions ultérieures d’Excel utilisent un autre algorithme est similaire à celui de la fonction renvoyer des réponses correctes dans les cas où les versions antérieures d’Excel retournent #NUM !.
Propriétés

ID d'article : 827459 - Dernière mise à jour : 27 janv. 2017 - Révision : 1

Microsoft Office Excel 2007, Microsoft Excel 2004 for Mac

Commentaires