Statistische functies in Excel: BINOMIALE

Samenvatting

Dit artikel beschrijft de functie BINOMIALE in Microsoft Office Excel 2003 en nieuwere versies van Excel, ziet u hoe u de functie en wordt vergeleken met de resultaten van eerdere versies van Excel de resultaten van de functie voor Excel 2003 en nieuwere versies van Excel.

Microsoft Excel 2004 voor Mac-gegevens

De statistische functies in Excel 2004 voor Mac zijn bijgewerkt met dezelfde algoritmen die zijn gebruikt voor het bijwerken van de statistische functies in Excel 2003 en nieuwere versies van Excel. Alle informatie in dit artikel die beschrijft hoe een functie werkt of hoe een functie is gewijzigd voor Excel 2003 en nieuwere versies van Excel ook van toepassing op Excel 2004 voor Mac.

Meer informatie

Wanneer cumulatief = waar, de binomiale (x, n,
p, cumulatief) functie geeft als resultaat de kans dat x of minder successen in
n onafhankelijke Bernoulli-experimenten. Elk van de proeven heeft een kans p succes (en kans 1 -p van de storing). Wanneer
cumulatief = ONWAAR, retourneert BINOMIALE de kans op exact x successen.

Syntaxis

BINOMDIST(x, n, p, cumulative)

Parameters

  • x is een niet-negatief geheel getal
  • n is een positief geheel getal
  • 0 < p < 1
  • Cumulatief is een logische variabele die geëvalueerd als waar wordt of ONWAAR

Voorbeeld van gebruik

Controleer de volgende veronderstellingen:
  • In honkbal ".300 hitter" hits kans 0,300 op succes elke keer dat hij wordt geleverd met bat (elke proef) (honkslag).
  • Opeenvolgende zijn slagbeurten onafhankelijke Bernoulli-experimenten.
U kunt de volgende tabel om de kans dat een slagman exact 0, 1, 2,..., of 10 hits produceert in 10 proeven en de kans dat de slagman 0, 1 of minder, 2 of minder,..., 9 of minder, of 10 of minder hits in 10 proeven.

Als de slagman 50 hits produceert in zijn eerste 200 proeven (.250 gemiddeld), moet hij 100 hits in de volgende 300 proeven om 150 honkslagen en een gemiddelde van meer dan 500 proeven.300 ophalen. De volgende tabel kunt u de kans dat de slagman voldoende honkslagen produceert om zijn gemiddelde onderhouden analyseren. Honkbalcommentators vaak daarnaar verwijst de 'wet der gemiddelden' wanneer ze zeggen dat ventilatoren geen zorgen te maken over de prestaties van deze slagman als hij slechts 50 hits produceert in zijn eerste 200 proeven omdat ' worden tegen het einde van het seizoen zijn gemiddelde. 300. " Als de proeven echt onafhankelijk zijn en de slagman echt een kans op succes 0,3 per willekeurige proef was, is deze redenering bedrieglijk omdat de resultaten van de eerste 200 proeven geen invloed op het succes of de mislukking van de laatste 300 proeven.

Ter illustratie van het gebruik van een binomiale Maak een leeg Excel-werkblad, Kopieer de volgende tabel, selecteer cel A1 in het lege Excel-werkblad en plak de posten die in de volgende tabel wordt gevuld A1:C22 cellen in het werkblad.
aantal proeven10
kans op succes0,3
successen, xP (exact x successen)P (x of minder successen)
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 proeven, kans op succes 0,3:
successen, xP (exact x successen)P (x of minder successen)
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)
Opmerking Nadat u deze tabel in het nieuwe Excel-werkblad plakt, klikt u op de knop Plakopties en klik vervolgens op Aanpassen aan opmaak van bestemming. Met het geplakte bereik nog steeds is geselecteerd, gebruikt u een van de volgende procedures, afhankelijk van de versie van Excel die u gebruikt:
  • In Microsoft Office Excel 2007, klikt u op het tabblad Start , klikt u in de groep cellen op Opmaak en klik vervolgens op Kolombreedte AutoAanpassen.
  • In Excel 2003 en eerdere versies van Excel, wijst u
    Kolom in het menu Opmaak en klik op
    AutoAanpassen aan selectie.
U kunt cellen B4: C22 voor een consistente leesbaarheid (bijvoorbeeld getallen opmaken met 5 decimalen).

B4: B14 toont de kansen op exact
x successen in 10 proeven. Het meest waarschijnlijke aantal gunstige uitkomsten is 3. De kans op 0, 6, 7, 8, 9 of 10 successen is minder dan 0,05 en opgeteld ongeveer 0,076. Dus de kans op 1, 2, 3, 4 of 5 successen is ongeveer 1 – 0,076 = 0,924. De waarschijnlijkheid van de cellen C4:C14 tonen
x of minder successen in 10 proeven. U kunt controleren of de gegevens in kolom C in de rij gelijk is aan de som van alle waarden in kolom B, naar beneden tot en met die rij zijn.

B18:B20 blijkt dat de meest waarschijnlijke aantal gunstige uitkomsten in 300 proeven 90. De kans op exact x successen neemt toe als
x wordt verhoogd naar 90 en neemt af als
x nog steeds hoger dan 90. De kans op 90 of minder successen is iets meer dan 50%, zoals C20. De kans op 99 of minder successen is ongeveer 0,884. Er is dus alleen een 11,6% kans (0,116 = 1 – 0,884) van 100 of meer successen.

Resultaten in oudere versies van Excel

Knusel beschreven (Zie Opmerking 1) gevallen waarbij BINOMIALE heeft geen numeriek antwoord teruggeeft en levert #NUM! in plaats daarvan vanwege een numerieke overloop. Wanneer BINOMIALE, zijn juist. BINOMIALE wordt #NUM! alleen wanneer het aantal proeven groter is dan of gelijk is aan 1030. Er zijn geen problemen met computergebruik als n < 1030. In de praktijk zijn dergelijke hoge waarden van n onwaarschijnlijk. Met deze een groot aantal onafhankelijke experimenten wil een gebruiker de binomiale verdeling benaderen met een normale verdeling als (
n*p and
n* (1 -p) voldoende hoog zijn, bijvoorbeeld elke groter is dan 30) of door een Poisson-verdeling.

Opmerking 1 Knusel, L. 'op de nauwkeurigheid van de statistische verdelingen in Microsoft Excel 97", rekenkundige statistieken en analyse van gegevens (1998), 26: 375-377.

Voor het geval niet-cumulatieve binomiale (x, n,
p, ONWAAR) luidt als volgt
COMBIN(n,x)*(p^x)*((1-p)^(n-x))
COMBINATIES is een Excel-functie geeft als het aantal combinaties van x items in een populatie van resultaat
n items. COMBINATIES (n,x) is soms nCxgeschreven, en met de naam 'gecombineerde coëfficiënt' of gewoon,"n kiezen
x". Als u met combinaties door te typen experimenteert
=Combinaties(1029;515) in één cel en
=Combinaties(1030;515) in een andere cel, de eerste cel retourneert een astronomische getal, 1.4298E + 308, en de tweede cel wordt #NUM! omdat het nog groter. De overloop van combinaties veroorzaakt een overloop van BINOMIALE in eerdere versies van Excel.

COMBINATIES is niet gewijzigd voor Excel 2003 en nieuwere versies van Excel.

Resultaten in Excel 2003 en nieuwere versies van Excel

Omdat Microsoft heeft onderzocht wanneer overloop veroorzaakt binomiale #NUM! en weet dat BINOMIALE correct functioneert wanneer er geen overloop optreedt, wordt Microsoft heeft een voorwaardelijk algoritme geïmplementeerd in Excel 2003 en nieuwere versies van Excel.

De binomiale uit eerdere versies van Excel (de rekenkundige formule die eerder in dit artikel is genoemd) wanneer n < 1030. Als n > = 1030, Excel 2003 en nieuwere versies van Excel gebruiken de alternatieve algoritme die verderop in dit artikel wordt beschreven.

Meestal combinaties veroorzaakt een overloop omdat het astronomische, maar
p^x en (1 -p) ^ (n-x) zijn beide oneindig klein. Als het mogelijk is om deze te vermenigvuldigen samen zou, zou het product een realistische kans tussen 0 en 1. Omdat de bestaande beperkte rekenkundig niet kan ze vermenigvuldigen, voorkomt een alternatief algoritme de evaluatie van combinaties.

De aanpak van Microsoft berekent een ongeschaalde som van alle kansen op exact x successen die later worden gebruikt voor schaling. Het berekent tevens een ongeschaalde waarde van de kans dat u wilt dat BINOMIALE. Tot slot wordt de schalingsfactor gebruikt om een juiste waarde voor de BINOMIALE.

Het algoritme benut het feit dat de verhouding van opeenvolgende termen van de vorm combinaties (n,k) * (p^k) * ((1 -p) ^ (n-k)) is een eenvoudig formulier. Het algoritme wordt verder beschreven in de pseudocode in de volgende stappen uit.

Stap 0: (Initialiseren). TotalUnscaledProbability en UnscaledResult eigenschappen naar 0 geïnitialiseerd. Initialiseren van de constante EssentiallyZero op een heel klein getal, bijvoorbeeld 10^(-12).

Stap 1: Zoeken n*p en afronden naar het dichtstbijzijnde gehele getal m. Is de meest waarschijnlijke aantal uitkomsten in een experiment n
m or m+1. COMBINATIES (n,k) * (p^k) * ((1 -p) ^ (n-k)) afneemt naarmate k van afneemt
m-1 m
m-2, enzovoort. Ook combinaties (n,k) * (p^k) * ((1 -p) ^ (n-k)) afneemt naarmate k van oploopt
m+ 2 m+ 1
m+ 3, enzovoort.
TotalUnscaledProbability = TotalUnscaledProbability + 1;If (m == x) then UnscaledResult = UnscaledResult + 1;
If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;

Stap 2: Bereken de ongeschaalde kansen voor
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;

Stap 3: Bereken de ongeschaalde kansen voor
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;

Stap 4: Combineer de ongeschaalde resultaten:
Return UnscaledResult/TotalUnscaledProbability;
Hoewel deze methode alleen voor n gebruikt wordt > = 1030, kunt u de volgende toevoegingen aan het Excel-werkblad kunt u dit algoritme berekent de binomiale (3, 10, 0.3, TRUE) met een handje uitvoeren (in het voorbeeld baseball, de kans op 3 of minder hits in 10 proeven voor een slagman.300).

Kopieer de volgende tabel, selecteer cel D4 in het Excel-werkblad dat u eerder hebt gemaakt ter illustratie en plak de posten die in de volgende tabel wordt gevuld D1:E15 cellen in het werkblad.
=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)
Kolom D bevat de ongeschaalde kansen. De 1 in cel D6 is het resultaat van stap 1 van het algoritme. Excel 2003 en nieuwere versies van Excel berekenen van de waarden in cellen D7, D8,..., D14 (in die volgorde) in stap 2. Excel berekent de waarden in cellen D5 en D4 (in die volgorde) in stap 3. De som van alle ongeschaalde kansen wordt weergegeven in D15.

Typ de volgende formule in een lege cel om de kans op 3 of minder successen te berekenen:
= SUM(D4:D7)/D15
In het vorige voorbeeld stopt EssentiallyZero niet stap 2 of 3. Echter, als u wilt evalueren, binomiale (550, 2000, 0,3, TRUE), stopt EssentiallyZero stap 2 en stap 3. Een willekeurige variabele is binomiale met
n = 2000 en p = 0,3 heeft een verdeling die wordt benaderd door de normaal met gemiddelde 600 en standaarddeviatie SQRT (2000 * 0,3 *(1 – 0.3)) = SQRT(420) = 20,5 is toegestaan. 805 is dus 10 standaarddeviaties hoger dan het gemiddelde en 395 is 10 standaarddeviaties lager dan het gemiddelde. Afhankelijk van de instelling van EssentiallyZerostoppen EssentiallyZero stap 2 voordat u 805 bereikt en stap 3 stoppen voordat u 395 bereikt.

Conclusies

Onnauwkeurigheden in versies van Excel die ouder dan Excel 2003 zijn treden alleen wanneer het aantal proeven groter dan of gelijk is aan 1030 is. In dergelijke gevallen wordt de binomiale #NUM! in eerdere versies van Excel omdat één term loopt over in een reeks van termen die bij elkaar worden vermenigvuldigd. U kunt dit probleem met Excel 2003 en nieuwere versies van Excel de alternatieve procedure die eerder in dit artikel wordt vermeld, wanneer een dergelijke overloop zou optreden.

De functie CRITBINOM, VERD, Neg. en POISSON vertonen hetzelfde gedrag in eerdere versies van Excel. Deze functies geven ook de correcte numerieke resultaten ofwel #NUM! of #DIV/0!. Nogmaals, problemen treden door overloop (of een negatieve).

Het is eenvoudig om te bepalen wanneer en hoe deze problemen optreden. Excel 2003 en nieuwere versies gebruiken een alternatief algoritme die vergelijkbaar is met die voor BINOMIALE correcte antwoorden in gevallen waar de eerdere versies van Excel #NUM terug!
Eigenschappen

Artikel-id: 827459 - Laatst bijgewerkt: 14 feb. 2017 - Revisie: 2

Microsoft Office Excel 2007, Microsoft Excel 2004 for Mac

Feedback