??????? ????????? ?? Excel: BINOMDIST

?????? ????????? ?????? ?????????
???? ???????: 827459 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

????? ??? ??????? ?????? ??? ??????? ?????? 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.

??????? ????

??? cumulative = TRUE ? ????? ?????? BINOMDIST (x, n, p, cumulative) ?????? x ?? ??? ?? ???? ?????? ?? ????? ???????? ?????? n. ??? ?? ??????? ?????? ????? p ????? (?? ?????? 1-p ?????). ??? cumulative = FALSE ?? BINOMDIST ????? ???????? x ?? ???????? ??????.

???? ??????

BINOMDIST(x, n, p, cumulative)

??????

  • x??? ???? ??? ????
  • n??? ???? ????
  • 0 <p< 1
  • Cumulative????? ?????? ???? ????? TRUE ?? FALSE

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

???????? ???????:
  • ?? baseball ".300 hitter" ??? ???? ?????? (????) ?? ?????? 0.300 ???? ??? ????? ???? (?? ?????).
  • ???? ??????? ?? bat ??? Bernoulli ?????? ???????.
?????? ?????? ?????? ????? ?? ?????? ?? ???? ??? ??? ??? ?????? ?????? 0, 1, 2,... ?? hits 10 ?? ??????? 10 ? ?????? ?? ?????? ???? 0 ?? 1 ?? ???, 2 ?? ???,..., ???? ?????? 9 ?? ???, ?? 10 ?? ??? ?? ??????? 10.

??? ??? ?????? ??? ??? ???? ?????? 50 ?? ?? ??????? 200 ????? (?????.250) ? ??? ??? ?????? ??? ??? ???? ?????? 100 ?? ??????? 300 ?????? ????? ?? ??? 150 ??? ???? ?????? ????.300 ????? ??????? 500 ????. ????? ??????? ?????? ?????? ?????? ???? ?????? ???? ??? ????? ????? ????? ???????? ?? ?????. allude baseball 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 ???????

(???? ?????? 1) ????? Knusel ???????? ??? BINOMDIST ?? ???? ?????? ?????? ????? ???? # NUM! ????? ?? ??? ???? ?? ????? ????? ??? ?????. ????? ???? BINOMDIST ?????? ?????? ????? ? ???? ?????. ???? BINOMDIST ?????? # NUM! ??? ????? ??? ????? ???? ???? ?? ?? ????? ?????. ?? ???? ??? ????? ???????? ??? n < ?????. ?? ??????? ?????? ??? ????? ??????? ??n ???????. ?? ??? ??? ???? ?? ???????, ?? ????? ?????? ?????? ????? ?? ?????? ??? ????? ?????? (??? ??? n * ??? ????? ???? ??? *(1-p) p ? n ? ??? ???? ??????? ?? ???? ?? 30) ?? ?? ???? ????? Poisson ???? ???.

?????? 1 Knusel L. "?????? ????? ??????? ????????? ?? Microsoft Excel 97" ? ???????? ???????? ?????? ???????? (???) 26: 375 - 377.

????? ??? ????????? BINOMDIST(x, n, p, false) ?????? ?????? ???????
COMBIN(n,x)*(p^x)*((1-p)^(n-x))
COMBIN ?? ???? Excel ???? ??? ??????? ?? ??????? x ????? ????? n. COMBIN(n,x) ??????? ????? n C x ???? ?????? "????? 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 well-behaved ??? ??? ???? ????? ????? ? Microsoft ?? ????? ???????? ???? ?? Excel 2003 ?????????? ??????? ?? Excel.

?????????? ?????? ????????? ???????? BINOMDIST ?? ????????? ??????? ?? Excel (?????? ???????? ???????? ?????? ?? ??? ???????) ??? n < ?????. ?????n > = ????? 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. ?? ?????? ?????? ??? ???? ?????? ?? ??????? nm ?? m + 1. COMBIN(n,k)*(p^k)*((1-p)^(n-k)) decreases as k decreases from m to m-1 to m-2, and so on. Also, COMBIN(n,k)*(p^k)*((1-p)^(n-k)) decreases as k increases from m+1 to m+2 to m+3, and so on.
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 > = ????? ? ????? ??????? ?????? ??????? ??????? ??? ???? ??? Excel ???????? ?? ????? ?????? - ????? ??? ?????????? ????? BINOMDIST (3 ?? 10 ?? 0.3 TRUE) (?? ?????? baseball ???? ???? ?????? 3 ?? ??? ?? ??????? 10 ?????? ??? ??????.300).

?????? ??? ??? ?????? ?????? ??? ?????? D4 ?? ???? ??? Excel ???? ??? ???????? ?????? ??? ?? ???? ????????? ???? ???? ?????? ?????? D1:E15 ??????? ?? ???? ?????.
?? ??? ??????????? ??? ??????
=d5*(1-$b$2)*(a4+1)/($b$2*($b$1-a4))= ?4 / $ 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)= ?8 / $ 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 ??? ????? ???? ??? ????? ???? ?? ?? ????? ?????. ?? ??? ??? ???????? ???? BINOMDIST ?????? # NUM! ?? ????????? ??????? ?? Excel ???? ????? ????? ???? ?? ????? ???? ?????? ????. ?????? ??? ?????? Excel 2003 ?????????? ??????? ?? Excel ?????? ??????? ??????? ???????? ?????? ?? ??? ??????? ??? ???? ?? ???? ????? ???? ?????? ???.

???? ?????? CRITBINOM ? HYPGEOMDIST ??? NEGBINOMDIST ? POISSON ??? ???? ????? ???? ?? ????????? ??????? ?? Excel. ????? ??? ??????? ?????? ????? ????? ?????? ?? # NUM! ?? # DIV/0!. ??? ????? ???? ????? ???? ????? ????? (?? ????? ???? ??????).

?? ????? ????? ????? ?????? ???? ??? ????????. ??????? Excel 2003 ?????????? ??????? ?? Excel ???????? ???? ???? ???? BINOMDIST ????? ???????? ??????? ?? ??????? ??? ?? ????????? ??????? ?? Excel ?????? # NUM!.

???????

???? ???????: 827459 - ????? ??? ??????: 27/?? ?????/1427 - ??????: 4.2
????? ???
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
????? ??????: 
kbmt kbexpertisebeginner kbinfo KB827459 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????827459

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

 

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