Στατιστικές συναρτήσεις του Excel: η συνάρτηση BINOMDIST

Σύνοψη

Αυτό το άρθρο περιγράφει τη λειτουργία BINOMDIST στο Microsoft Office Excel 2003 και σε νεότερες εκδόσεις του Excel, παρουσιάζει τον τρόπο χρήσης της συνάρτησης και συγκρίνει τα αποτελέσματα της συνάρτησης για το Excel 2003 και για νεότερες εκδόσεις του Excel με τα αποτελέσματά του για παλαιότερες εκδόσεις του Excel.

Microsoft Excel 2004 για Mac πληροφορίες

Τις στατιστικές συναρτήσεις στο Excel 2004 για Mac ενημερώθηκαν χρησιμοποιώντας το ίδιο αλγορίθμων που χρησιμοποιούνται για να ενημερώσετε τις στατιστικές συναρτήσεις στο Excel 2003 και σε νεότερες εκδόσεις του Excel. Οι πληροφορίες σε αυτό το άρθρο που περιγράφει πώς λειτουργεί μια συνάρτηση ή τον τρόπο τροποποίησης μιας συνάρτησης για το Excel 2003 και για νεότερες εκδόσεις του Excel εφαρμόζεται επίσης σε Excel 2004 για Mac.

Περισσότερες πληροφορίες

Κατά την αθροιστική = TRUE, η συνάρτηση BINOMDIST (x, n,
p, αθροιστική) αποδίδει την πιθανότητα x ή λιγότερα επιτυχιών στο
n ανεξάρτητων δοκιμών Bernoulli. Κάθε μία από τις δοκιμές έχει μια σχετική πιθανότητα p επιτυχίας (και πιθανότητα 1 -p αποτυχίας). Όταν
αθροιστική = FALSE, η συνάρτηση BINOMDIST αποδίδει την πιθανότητα ακριβώς x επιτυχίες.

Σύνταξη

BINOMDIST(x, n, p, cumulative)

Παράμετροι

  • x είναι μη αρνητικός ακέραιος
  • n είναι ένας θετικός ακέραιος
  • 0 < p < 1
  • Cumulative είναι μια λογική μεταβλητή που παίρνει τις τιμές TRUE ή FALSE

Παράδειγμα χρήσης

Κάντε τα ακόλουθα δεδομένα:
  • Στο μπέιζμπολ, ".300 hitter" επισκέψεων (επιτυγχάνει) με πιθανότητα 0.300 κάθε φορά που μπαίνει σε bat (κάθε δοκιμής).
  • Διαδοχικές φορές σε bat είναι ανεξάρτητων δοκιμών Bernoulli.
Μπορείτε να χρησιμοποιήσετε τον παρακάτω πίνακα για να βρείτε την πιθανότητα ότι η ζύμη λαμβάνει ακριβώς 0, 1, 2,..., ή 10 χτυπήσει σε 10 δοκιμές και την πιθανότητα ότι η ζύμη λαμβάνει 0, 1 ή λιγότερα, 2 ή λιγότεροι,..., 9 ή λιγότερα, ή 10 ή λιγότερες επισκέψεων σε 10 δοκιμές.

Εάν η ζύμη λαμβάνει 50 επισκέψεων σε του πρώτα 200 δοκιμών (.250 average), αυτός πρέπει να λάβετε 100 επισκέψεων σε του επόμενων 300 δοκιμές για να έχετε 150 επιτυχίες και ένα.300 μέσος όρος πάνω από 500 δοκιμών. Μπορείτε να χρησιμοποιήσετε τον παρακάτω πίνακα για να αναλύσετε την πιθανότητα ότι η ζύμη λαμβάνει επαρκή επισκέψεων για τη διατήρηση του μέσου όρου. Commentators μπέιζμπολ συχνά παραπέμπει σε το "δίκαιο των μέσων όρων" όταν λένε ότι οι ανεμιστήρες δεν χρειάζεται να ανησυχείτε σχετικά με τις επιδόσεις του παρόντος ζύμη με μόνο 50 επισκέψεων σε του πρώτα 200 δοκιμές, επειδή "από το τέλος της εποχής θα είναι ο μέσος όρος. 300." Εάν τις δοκιμές ήταν πραγματικά ανεξάρτητη και η ζύμη είχε όντως ένα 0,3 πιθανότητα επιτυχίας σε κάθε μία δοκιμαστική έκδοση, αυτό λογικής είναι fallacious επειδή τα αποτελέσματα των δοκιμών πρώτα 200 δεν επηρεάζουν την επιτυχία ή την αποτυχία πάνω από 300 τελευταία δοκιμών.

Για να απεικονίζουν τη χρήση των BINOMDIST, δημιουργήστε ένα κενό φύλλο εργασίας του Excel, αντιγράψτε τον παρακάτω πίνακα, επιλέξτε το κελί A1 στο κενό φύλλο εργασίας του Excel και στη συνέχεια επικολλήστε τις εγγραφές έτσι ώστε να γεμίζει ο ακόλουθος πίνακας A1:C22 κελιά στο φύλλο εργασίας σας.
αριθμός των δοκιμών10
πιθανότητα επιτυχίας0,3
επιτυχίες, xP (ακριβώς x επιτυχίες)P (x ή λιγότερες επιτυχίες)
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 δοκιμών, πιθανότητα επιτυχίας 0,3:
επιτυχίες, xP (ακριβώς x επιτυχίες)P (x ή λιγότερες επιτυχίες)
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)
Σημείωση Μετά την επικόλληση αυτού του πίνακα στο νέο φύλλο εργασίας του Excel, κάντε κλικ στο κουμπί Επιλογές επικόλλησης , και στη συνέχεια κάντε κλικ στην επιλογή Συμφωνία μορφοποίησης προορισμού. Με την επιλεγμένη περιοχής επικόλλησης, χρησιμοποιήστε μία από τις ακόλουθες διαδικασίες, ανάλογα με την έκδοση του Excel που εκτελείτε:
  • Στο Microsoft Office Excel 2007, κάντε κλικ στην καρτέλα " κεντρική σελίδα ", κάντε κλικ στο κουμπί " μορφή " στην ομάδα " κελιά " και στη συνέχεια κάντε κλικ στο κουμπί Αυτόματη Προσαρμογή πλάτους στήλης.
  • Στο Excel 2003 και σε παλαιότερες εκδόσεις του Excel, τοποθετήστε το δείκτη σε
    Στήλη στο μενού " μορφή " και, στη συνέχεια, κάντε κλικ στο κουμπί
    Αυτόματη προσαρμογή επιλογής.
Εάν θέλετε, μπορείτε να μορφοποιήσετε κελιά B4:C22 για συνεπής αναγνωσιμότητας (για παράδειγμα, μορφή αριθμών σε πέντε δεκαδικά ψηφία).

B4:B14 κελιά, εμφάνιση ακριβώς τις πιθανότητες
x επιτυχιών στις 10 δοκιμές. Το πιο πιθανό αριθμός επιτυχιών είναι 3. Τις πιθανότητες 0, 6, 7, 8, 9 ή 10 επιτυχίες είναι λιγότερο κάθε από 0.05 και να προσθέσετε πληροφορίες σχετικά με 0.076. Έτσι τις πιθανότητες 1, 2, 3, 4 ή 5 επιτυχίες είναι περίπου 1 – 0.076 = 0.924. Κελιά εμφάνιση C4:C14 τις πιθανότητες
x ή λιγότερα επιτυχιών στις δοκιμές 10. Μπορείτε να επαληθεύσετε ότι οι καταχωρήσεις στη στήλη C σε οποιαδήποτε γραμμή είναι κάθε ίση με το άθροισμα όλων των εγγραφών στη στήλη B, προς τα κάτω και αυτή τη γραμμή.

B18:B20 δείχνουν ότι το πιο πιθανό αριθμός επιτυχιών στις δοκιμές 300 είναι 90. Αυξάνει την πιθανότητα ακριβώς x επιτυχίες ως
αυξάνεται σε 90 x , και στη συνέχεια μειώνεται καθώς
x εξακολουθεί να αυξάνεται σε υψηλότερη θέση από 90. Η πιθανότητα 90 ή λιγότερες επιτυχιών είναι λίγο περισσότερο από 50%, όπως δείχνει C20. Η πιθανότητα 99 ή λιγότερες επιτυχίες είναι περίπου 0.884. Επομένως, υπάρχει μόνο μια πιθανότητα 11.6% (0.116 = 1 – 0.884) των 100 ή περισσότερες επιτυχίες.

Αποτελέσματα σε παλαιότερες εκδόσεις του Excel

Knusel (βλέπε σημείωση 1) τεκμηριωμένες περιπτώσεις όπου BINOMDIST επιστρέφει μια αριθμητική απαντήσεων και αποδόσεις #NUM! Αντίθετα λόγω αριθμητικής υπερχείλισης. Όταν η συνάρτηση BINOMDIST αποδίδει αριθμητική απαντήσεις, είναι σωστή. Η συνάρτηση BINOMDIST αποδίδει #NUM! μόνο όταν ο αριθμός των δοκιμών είναι μεγαλύτερη ή ίση με 1030. Υπάρχουν υπολογιστικής προβλήματα εάν n < 1030. Στην πράξη, οι υψηλές τιμές του n είναι απίθανη. Με έναν υψηλό αριθμό των ανεξάρτητων δοκιμών, ένας χρήστης μπορεί να χρειαστεί η προσέγγιση της διωνυμικής κατανομής από την κανονική κατανομή (Εάν
n*p and
n* (1 -p) είναι αρκετά υψηλά, για παράδειγμα, κάθε είναι μεγαλύτερη του 30) ή από μια κατανομή Poisson διαφορετικά.

Σημείωση 1 Knusel, L. "για την ακρίβεια στατιστική κατανομή μεταξύ στο Microsoft Excel 97", Στατιστικά υπολογιστικής και ανάλυση δεδομένων (1998), 26: 375 377.

Για την περίπτωση μη σωρευτική, η συνάρτηση BINOMDIST (x, n,
p, ψευδές) χρησιμοποιεί τον παρακάτω τύπο
COMBIN(n,x)*(p^x)*((1-p)^(n-x))
COMBIN είναι μια συνάρτηση του Excel που αποδίδει τον αριθμό των συνδυασμών x στοιχείων σε έναν πληθυσμό
n στοιχεία. COMBIN (n,x) είναι ορισμένες φορές εγγραφή nCx, και με το όνομα "combinatorial συντελεστή" ή απλά,"n επιλέξτε
x". Εάν μπορείτε να πειραματιστείτε με COMBIN πληκτρολογώντας
=COMBIN(1029,515) σε ένα κελί και
=COMBIN(1030,515) σε ένα διαφορετικό κελί, στο πρώτο κελί επιστρέφει έναν αριθμό astronomical, 1.4298E + 308, και το δεύτερο κελί επιστρέφει τιμή σφάλματος #NUM!. επειδή είναι ακόμα μεγαλύτερη. Η υπερχείλιση COMBIN προκαλεί υπερχείλιση του συνάρτηση BINOMDIST σε παλαιότερες εκδόσεις του Excel.

Δεν έχει τροποποιηθεί COMBIN για το Excel 2003 και για νεότερες εκδόσεις του Excel.

Αποτελέσματα στο Excel 2003 και σε νεότερες εκδόσεις του Excel

Επειδή η Microsoft έχει διαγνωστεί όταν υπερχείλιση προκαλεί BINOMDIST για να επιστρέψει #NUM! και να γνωρίζει ότι η συνάρτηση BINOMDIST είναι καλά behaved όταν παρουσιαστεί υπερχείλιση, Microsoft έχει εφαρμόσει έναν αλγόριθμο υπό όρους στο Excel 2003 και σε νεότερες εκδόσεις του Excel.

Ο αλγόριθμος χρησιμοποιεί κωδικό BINOMDIST από παλαιότερες εκδόσεις του Excel (υπολογιστικής τύπου που αναφέρεται παραπάνω σε αυτό το άρθρο) όταν n < 1030. Όταν n > = 1030, Excel 2003 και σε νεότερες εκδόσεις του Excel, χρησιμοποιήστε τον εναλλακτικό αλγόριθμο που περιγράφεται παρακάτω σε αυτό το άρθρο.

Συνήθως, η COMBIN υπερχειλίσεις επειδή είναι astronomical, αλλά
p^x και (1 -p) ^ (n-x) είναι κάθε infinitesimal. Εάν ήταν δυνατόν να πολλαπλασιαστούν μαζί, το προϊόν θα ήταν ρεαλιστική πιθανότητα μεταξύ 0 και 1. Ωστόσο, επειδή το υπάρχον αριθμητικός πεπερασμένη δεν είναι δυνατό να πολλαπλασιαστούν, εναλλακτικού αλγόριθμου αποφεύγει την αξιολόγηση COMBIN.

Η προσέγγιση της Microsoft υπολογίζει ένα κείμενο χωρίς κλιμάκωση άθροισμα όλες τις πιθανότητες ακριβώς τις επιτυχίες x που χρησιμοποιούνται αργότερα για λόγους κλιμάκωσης. Υπολογίζει επίσης μια τιμή χωρίς κλιμάκωση της πιθανότητας ότι θέλετε η συνάρτηση BINOMDIST για να επιστρέψετε. Τέλος, χρησιμοποιεί τον παράγοντα κλίμακας για να επιστρέψει τη σωστή τιμή BINOMDIST.

Ο αλγόριθμος αξιοποιεί το γεγονός που ο λόγος των διαδοχικών όρων της φόρμας COMBIN (n,k) * (p^k) * ((1 -p) ^ (n-k)) έχει μια απλή φόρμα. Ο αλγόριθμος συνεχίζεται όπως περιγράφεται στο pseudocode το στα παρακάτω βήματα.

Βήμα 0: (Προετοιμασία). Η προετοιμασία του TotalUnscaledProbability και τις UnscaledResult ιδιότητες στην τιμή 0. Η προετοιμασία της σταθεράς EssentiallyZero σε πολύ μικρό αριθμό, για παράδειγμα, 10^(-12).

Βήμα 1: Εύρεση n*p και στρογγυλοποίηση προς τα κάτω, στον πλησιέστερο ακέραιο αριθμό, m. Το πιο πιθανό αριθμός επιτυχιών στις δοκιμές n είναι είτε
m or m+1. COMBIN (n,k) * (p^k) * ((1 -p) ^ (n-k)) μειώνεται καθώς μειώνεται k από
m-1 m
m-2, και ούτω καθεξής. Επίσης, COMBIN (n,k) * (p^k) * ((1 -p) ^ (n-k)) μειώνεται καθώς αυξάνεται k από
m+ 1 έως m+ 2 για να
+ 3 m, και ούτω καθεξής.
TotalUnscaledProbability = TotalUnscaledProbability + 1;If (m == x) then UnscaledResult = UnscaledResult + 1;
If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;

Βήμα 2: Να υπολογίσετε τις πιθανότητες χωρίς κλιμάκωση για
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;

Βήμα 3: Να υπολογίσετε τις πιθανότητες χωρίς κλιμάκωση για
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;

Βήμα 4: Συνδυάζει τα αποτελέσματα χωρίς κλιμάκωση:
Return UnscaledResult/TotalUnscaledProbability;
Αν και η μέθοδος αυτή χρησιμοποιείται μόνο για n > = 1030, μπορείτε να χρησιμοποιήσετε τις παρακάτω προσθήκες στο φύλλο εργασίας του Excel, για να σας βοηθήσει να εκτελέσετε με το χέρι του αυτόν τον αλγόριθμο για να υπολογίσει το BINOMDIST (3, 10, 0.3, TRUE) (στο παράδειγμα μπέιζμπολ, την ευκαιρία των επισκέψεων 3 ή λιγότερα από 10 δοκιμές για μια.300 ζύμη).

Να παρουσιάζουν αυτό το παράδειγμα, αντιγράψτε τον παρακάτω πίνακα, επιλέξτε το κελί D4 του φύλλου εργασίας του Excel που δημιουργήσατε προηγουμένως και, στη συνέχεια, επικολλήστε τις εγγραφές έτσι ώστε να γεμίζει ο ακόλουθος πίνακας D1:E15 κελιά στο φύλλο εργασίας σας.
=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)
Στήλη D περιέχει τις πιθανότητες χωρίς κλιμάκωση. Το 1 στο κελί D6 είναι το αποτέλεσμα βήμα 1 από τον αλγόριθμο. Excel 2003 και σε νεότερες εκδόσεις του Excel υπολογίζει τις εγγραφές στα κελιά D7, D8,..., D14 (με αυτή τη σειρά) στο βήμα 2. Το Excel υπολογίζει τις εγγραφές στα κελιά D5 και D4 (με αυτήν τη σειρά) στο βήμα 3. Το άθροισμα των όλα χωρίς κλιμάκωση πιθανότητες εμφανίζεται στο D15.

Για να υπολογίσετε την πιθανότητα 3 ή λιγότερες επιτυχίες, πληκτρολογήστε τον ακόλουθο τύπο σε οποιοδήποτε κενό κελί:
= SUM(D4:D7)/D15
Στο προηγούμενο παράδειγμα, EssentiallyZero δεν σταματά βήματα 2 ή 3. Ωστόσο, εάν θέλετε να αξιολογήσετε συνάρτηση BINOMDIST (550, 2000 0,3, TRUE), EssentiallyZero ενδέχεται να σταματήσει το βήμα 2 ή το βήμα 3. Διωνυμική τυχαία μεταβλητή με
n = 2000 και p = 0,3 έχει μια κατανομή που υπολογίζονται κατά προσέγγιση από την κανονική με μέση 600 και τυπική απόκλιση SQRT (2000 * 0,3 *(1 – 0.3)) = SQRT(420) = 20,5. Στη συνέχεια 805 είναι 10 τυπικές αποκλίσεις υψηλότερη από τη μέση τιμή και 395 είναι 10 τυπικές αποκλίσεις κάτω από τον μέσο όρο. Ανάλογα με τη ρύθμιση του EssentiallyZero, EssentiallyZero ενδέχεται να σταματήσει το βήμα 2 πριν φτάσετε 805 και ενδέχεται να σταματήσει το βήμα 3 πριν φτάσει 395.

Συμπεράσματα

Ανακρίβειες σε εκδόσεις του Excel που είναι παλαιότερες από το Excel 2003 παρουσιάζεται μόνο όταν ο αριθμός των δοκιμών είναι μεγαλύτερη ή ίση με 1030. Στην περίπτωση αυτή, το BINOMDIST επιστρέφει #NUM! σε παλαιότερες εκδόσεις του Excel επειδή ένας όρος υπερχειλίσεις σε μια ακολουθία των όρων που πολλαπλασιάζονται μαζί. Για να διορθώσετε αυτήν τη συμπεριφορά, Excel 2003 και σε νεότερες εκδόσεις του Excel, χρησιμοποιήστε την εναλλακτική διαδικασία που αναφέρεται παραπάνω σε αυτό το άρθρο όταν η υπερχείλιση διαφορετικά θα προκύψει.

Η συνάρτηση CRITBINOM, η συνάρτηση HYPGEOMDIST, η συνάρτηση NEGBINOMDIST και POISSON παρουσιάζουν την ίδια συμπεριφορά σε παλαιότερες εκδόσεις του Excel. Οι συναρτήσεις αυτές επιστρέφουν επίσης είτε σωστή αριθμητικά αποτελέσματα ή #NUM! ή #DIV/0!. Και πάλι, προβλήματα παρουσιάζονται εξαιτίας (ή θετική υπερχείλιση).

Είναι εύκολο να προσδιορίσετε πότε και πώς αυτά τα ζητήματα προκύπτουν. Excel 2003 και σε νεότερες εκδόσεις του Excel, χρησιμοποιήστε μια εναλλακτική αλγόριθμος που είναι παρόμοια με αυτήν για τη συνάρτηση BINOMDIST για να επιστρέψετε τις σωστές απαντήσεις στις περιπτώσεις όπου οι παλαιότερες εκδόσεις του Excel επιστρέψει #NUM!.
Ιδιότητες

Αναγνωριστικό άρθρου: 827459 - Τελευταία αναθεώρηση: 17 Ιαν 2017 - Αναθεώρηση: 1

Σχόλια