??????? ????????? ?? Excel: CRITBINOM

?????? ????????? ?????? ?????????
???? ???????: 828117 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

????? ??? ??????? ?????? ??? ??????? ?????? CRITBINOM ?? Microsoft Office Excel 2003 ??? ????????? ??????? ?? Excel ? ???? ??? ??? ??????? ?????? ? ????? ??? ????? ?????? ?? Excel 2003 ?????????? ??????? ?? Excel ???? ????? CRITBINOM ?? ??????? Excel ???????.

Microsoft Excel 2004 ?????? ??? ??????? ???????

?? ????? ??????? ????????? ?? Excel 2004 ?? Mac ???????? ??? ??????????? ???? ?? ????????? ?????? ??????? ????????? ?? Excel 2003 ?????????? ??????? ?? Excel. ????? ??? ??????? ?? ??? ??????? ?????? ????? ??? ???? ?? ????? ????? ???? ??????? Excel 2003 ?????????? ??????? ?? Excel ??????? ' ????? ??? Excel 2004 ?? Mac.

??????? ????

???? ?????? CRITBINOM (n p, ????) ?? ?????? x ???????? ???????? ?? x ???? ?????? ?? ????? ???????? ?????? n. ????? ?? ????? ?????? ????? p ??????. CRITBINOM ????? ???? ???? x ??? ???????? ???????? ???? ???? ???? ?? ?? ????? ????. ???????? ???????? ??? ???? ?? ?? ????? ????.

???? ??????

CRITBINOM(n, p, alpha)
??????? n ??? ???? ????, 0 < p < 1 ? 0 < = ???? < = 1 (??? ????? ?? ??? ??????? ??? ???? = 0 ? ???? = 1 ?? ???? ?????? ??????).

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

?? baseball ? ????? ??? supposition simplifying ?? ????? ".300 hitter" ?????? (??????) ?? ?????? 0.300 ???? ??? ????? ???? (?? ?????) ? ?? ????????? ????? ?? bat ?? Bernoulli ?????? ???????. ?? ??????? 100 ???????? 30 ????? ????? ?????? ???????? ?? ????? ?????.

???? ??????? CRITBINOM ??? ?? ??????? ???????:
  • x ???? ??? ???? ?????? ??? ????? ?????? ????? 0.25 ???? x ????? ?????? ??? ?? ??? ?? ???? ?????? (????? x ??? ?? 30)
  • (??????? ????) ???? ??? ???? ?????? x ' ??? ????? ?????? ????? 0.25 ???? ?? ?????? ??? ????? x ' ???? ?????? (x ' ????? ????? ??? ?? 30)
  • ??? y ???? ?????? ????? ?????? ???? ???? ?????? ??? ? ?? ???? ?? ???? ?????? ??? ?? 0.10 (y ????? ???? ?? 30)
?????? ?????? CRITBINOM ????? ???? Excel ???? ??? ???? ?????? ??????. ?? ??? ?????? A1 ?? ????? ?? ????? ???? ??? Excel ?????? ????????? ???? ???? ?????? A1:B21 ??????? ?? ???? ?????.
?? ??? ??????????? ??? ??????
??? ????? n100
?????? ?????? p0.3
????
0.05=critbinom($b$1,$b$2,a4)
0.1=critbinom($b$1,$b$2,a5)
0.2=critbinom($b$1,$b$2,a6)
32618.20377=critbinom($b$1,$b$2,a7)
0.3=critbinom($b$1,$b$2,a8)
0.4=critbinom($b$1,$b$2,a9)
0.5=critbinom($b$1,$b$2,a10)
0.6=critbinom($b$1,$b$2,a11)
0.7=critbinom($b$1,$b$2,a12)
0.75=critbinom($b$1,$b$2,a13)
0.8=critbinom($b$1,$b$2,a14)
??????=critbinom($b$1,$b$2,a15)
0.95=critbinom($b$1,$b$2,a16)
critbinom?????? ???????
=critbinom(1030,0.5,0.16704)499
=critbinom(1030,0.5,0.1831)500
=critbinom(1030,0.5,0.51242)515
??? ??? ??? ?????? ?? ???? ??? Excel ???? ??? ???? ?????? ????? ? ?? ???? ??? ????? ????? ??????. ?? ??????? ????? ?????? ???? ?? ????? ?????? ??? ????????? ??????? ??? ?????? ?? ????? Excel ???? ???? ???????:
  • ?? Microsoft Office Excel 2007? ???? ??? ????? ??????? ?????? ???????? ???? ??? ????? ?? ???????? ????? ?? ???? ??? ?????? ?????? ???? ??????.
  • ?? Excel 2003? ??? ??? ???? ?? ??????? ????? ?? ???? ??? ?????? ?????? ???????.
????? ??????? B4:B16 ??? CRITBINOM ????? alpha ??????? ?? A4:A16. ?????? 1 ?? CRITBINOM(100, 0.3, 0.25) ?? ?????? B7 27 ???? ???? ?????? ?????? ??? ?? ?? ??? ?? ???? ?????? ???? ?? ?? ????? 32618.20377 ????? ???????? ?????? ??? ???? ?????? 26 ?? ??? ??? ?? 32618.20377. ???? 27 ?? ???? ??? ???? ?????? x ??? ?? ?????? ????? 0.25 ???? x ????? ?????? ??? ?? ??? ?? ???? ??????.

?????? 2 ????? ???? ??? ???? 1 ???. ??? ????? 0.25 ???? ???? ?????? ?? ?? ??? ???? ??? ?????? ???? 0.75 ?????? ??? 28 ?? ???? ?? ???? ?????? ?? ???????? ????? 0.25 ???? ?? ???? ???????? 28 ?? ????.

?????? 3 CRITBINOM(100, 0.3, 0.9) ?? 36. ???? ???? ????? 0.9 ???? ?????? ??? ???? ?????? 36 ?? ???. ????? ???? ??? ?????? ???? 0.1 ?????? ??? ???? ?????? 37 ?? ????. ??? ????? 37 ???? ??? ???? ??? ??? ?????.

???? ??????? 2 ? 3 ??????? ????? ?????? ?? ??????? ??? ??????? ???????? ?? ?????? ?? ????? ?? ???? CRITBINOM. ???? CRITBINOM ????? ??? "x ???? ???? ?????? ?????? ????? ?? x ???? ?? ?? ????? ???? x = 27." ??? ??????? ?? ?? ??? ??????? ???? ??? ????? ??????? ??? ??????. ??? ???? ??????? ??? P(number of successes <= 27) > = ???? ?? P(number of successes >= 28) < 1-????.

???? A18:B21 ??????? ???? CRITBINOM. ?? ?????? ??? ????? ??????? ???????? ???????? ????? Knusel ELV. ??????? Knusel ??? ???????? ????? ?????? ?? ????? ????? ?? ???????? ?????. ??? ??? ???? ????? Excel ???? ?? Excel 2003 ???? ????????? ?? A20 ? A21 # NUM!. ??? ??? ???? Excel 2003 ?? ????? ???? ?? Excel, ??? ???????? ??? ????????? ?? A19:A21 ? B19:B21.

??????? ?? ??????? Excel ???????

????? CRITBINOM ??? BINOMDIST. (???? ?????? 1) ????? Knusel ???????? ??? BINOMDIST ?? ???? ?????? ?????? ????? ???? # NUM! ????? ?? ??? ???? ?? ????? ????? ??? ?????. ????? ??? ??????? ?????? ??????? ?????? BINOMDIST ? ???? ?????. # NUM! ??? ????? ??? ????? ???? ??? ????? ???? ?? ?? ????? ?????. ?? ???? ??? ????? ???????? ??? n < ?????. ?????? ??? ??????? ??????? ??? BINOMDIST ??????? ??? ???? ??????.

?????? 1: Knusel ? L. ??? ????? ??????? ????????? ?? Microsoft Excel 97, ???????? ???????? ?????? ???????? 26 ? 375-377 ? 1998.

????? ?? ????????? ??? BINOMDIST ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
827459??????? ????????? ?? Excel: BINOMDIST
?? ??? ???????? Knusel ??? CRITBINOM ?? ????? ????? ??. ??? ????? ?? ???? ??? ?? ?????, ???? CRITBINOM ???? ??? ?????? ??? ??? ???? ??? n > = ?????.

??????? ?? Excel 2003 ?????????? ??????? ?? Excel

????? ??????? ??? ????????? ?? Excel 2003 ?????????? ??????? ?? Excel ?? ?????? ??? ??? ?? ????? ?? BINOMDIST: ??????? ????????? ???????? pre-Excel 2003 ???????? ??? ??? n < ????? ?????? ??????? ???? ????? ??? ??? n > = ?????. ???? ???????? ?? ??? ?????? ?????? ?? ?????? ??? ??? n > = ?????. ????? ??? ???? ???????? ????? ??? ??????? BINOMDIST: ????? ?? ???? ????? m (m = n ??????? * p) ?? ????? ?????? ??? ????? ?? 1 ??? m ?? ????? ?? ?????????? ??? ????? m + 1, m + 2 ? m + 3... ????? ??? infinitesimal, ??? ?????????? ?????? ??? ???????? ??? ????? m-1 ?? m 2 ?? m-3... ????? ??? infinitesimal ??? ??????????. ???????? ????? ?????????? ????????.

????? CRITBINOM ????? ?? ?????? ????????? ?????? ??? ????? ?????? ?????? NORMSINV ??????? ??????? ??????? ??????? ?????. ??????? NORMSINV ???? ????? ??? ???? ????? ?????? NORMSDIST ??? homes ???????. ??? ????? ??? ??? ??????? ??????? ??????? ????? ???????? BINOMDIST ???? ???? ???? ????? ?????? ?? CRITBINOM ??????? BINOMDIST ???? ?????. ??????? ?????? guess ????, ?????? ????????? ???????? ????? (??? ??????? ???? BINOMDIST) ? ??? ?? ??? guess ??? ??? ????? ??????.

?????? ???? ?? guess ???????? ????? ???? ??? ??????? ?? ??????. ????? ????? ?????? ?? ??? ????? ?????? ???????? ???????? ?? ?? ?????? n ????? ????, * p ? ???????? ???????? SQRT(n*p*(1-p)). ??? ?? ???? ??? ????? ????? ???????? ????? n*p*(1-p) > 30. ????? ??? ?????? ?? n > = ????? ?? ?? ??? p ???? ?? ???? ?????? ?? 1 ??? 0. ??? ???? ??? ???? ??????? ??????? ????? ???? ??? NORMSINV ????? ?? ??????? NORMSINV ????. ????? ???? ?? 26.2.23 ?? Abramowitz ?????? ? I. Stegun ?,handbook ????? ?????? ? Dover ? 1972 ? ???? p..

??? ??? alpha < = 0.5 ????? t = SQRT(LN(1/(alpha^2))) ?? ????? NORMSINV(alpha) ?????? NApprox = -t + (2.515517 + 0.802853 * t + 0.010328 * t * t) /(1 + 1.432788*t + 0.189269*t*t + 0.001308*t*t*t)

??? ???? ???? > 0.5 ????? t = SQRT (LN (1/((1 ? alpha) ^ 2))) ?? ????? NORMSINV(alpha) ?????? NApprox = t-(2.515517 + 0.802853 * t + 0.010328 * t * t) /(1 + 1.432788*t + 0.189269*t*t + 0.001308*t*t*t)

?????? ??????? guessed CRITBINOM ??? ???.
Guess = FLOOR(n*p + NApprox*SQRT(n*p*(1-p)))
???? ?????? ?? ?? ??? ??? ???? ?????? [0, n]. ????? ????? ????????? ???????? ???????:
If (Guess < 0) then Guess = 0;
If (Guess > n) then Guess = n;
??????? ???? pseudocode ???? ??? ?? ?????? ?? pseudocode BINOMDIST (?? ??????? ??? BINOMDIST) ????? ?? ??? ?? ?????????? ??? ????????? ? ???????? ????. ????? ???? ??? ????? ??? pseudocode ???? ??? ???? ???? ???? BINOMDIST(Guess, n, p, TRUE) ? BINOMDIST(Guess, n, p, FALSE).

?????? 0: (?????). ????? TotalUnscaledProbability ? UnscaledPGuess (?? ??? ???????? ??? ?????????) UnscaledCumPGuess (?? ??? ???????? ????????) ???? ??? 0. ????? EssentiallyZero ????? ??? ??? ???? ???? ??? 10^(-12).

???? 1: ????? n * p ? ???????? ????? ??? ???? ??? ???? m. ?????? ?????? ??? ???? ?????? ?? ??????? n ?? ??? m ?? m + 1.
TotalUnscaledProbability = TotalUnscaledProbability + 1;
If (m == Guess) then UnscaledPGuess = UnscaledPGuess + 1;
If (m <= Guess) then UnscaledCumPGuess = UnscaledCumPGuess + 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 == Guess) then UnscaledPGuess = UnscaledPGuess + CurrentValue;
	If (k <= Guess) then UnscaledCumPGuess = UnscaledCumPGuess + 
		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 == Guess) then UnscaledPGuess = UnscaledPGuess + CurrentValue;
	If (k <= Guess) then UnscaledCumPGuess = UnscaledCumPGuess + 
		CurrentValue;
	If (CurrentValue <= EssentiallyZero) then Done = TRUE;
	PreviousValue = CurrentValue;
	k = k-1;
  }
end While;
?????? 4: (??? ????? ??? ?????):
PGuess = UnscaledPGuess/TotalUnscaledProbability;
CumPGuess = UnscaledCumPGuess/TotalUnscaledProbability;
???? 5: ??? ???????. ??? ??? ?????? ???? ????? ??? ??? ?? ???? PGuess CumPGuess. ???? ?? CumPGuessMinus1 ???? ?????? ???????? ???? ? 1. ?? ???? ?? ??????? ??????? ??? CumPGuessMinus1 < ???? ???? CumPGuess > = ????. ??? ??? ?? ?? ??? ?? < ???? ? ????? ???? ?????? 1. ??? ?? ?? ??? > = ???? ? ????? ???? ?????? 1. ??? ????? ?????. ?????
CumPGuessMinus1 = CumPGuess ? PGuess;
??? ??? ???? ?????? ???? ??
PGuessPlus1 = PGuess * (n ? Guess) * p / Guess / (1 ? p);
CumPGuessMinus1 = CumPGuess;
CumPGuess = CumPGuess + PGuessPlus1;
PGuess = PGuessPlus1;
Guess = Guess + 1;
??? ??? ???? ??? ????? ???? ??
PGuessMinus1 = PGuess * Guess * (1 ? p) / (n ? Guess + 1) / p;
CumPGuess = CumPGuessMinus1;
CumPGuessMinus1 = CumPGuessMinus1 ? PGuess;
PGuess = PGuessMinus1;
Guess = Guess ? 1;
?? ????? ??? ????? ???? ???? ?? ??? ????? ?? ?? ????? ??? ????? ???? ???? ?? ??? ????? ???? ????? ???? ????? ???? ??? ????? ??? ????? ?? ????? ???? ??? ????? ??? ?????.

?? ?????? CRITBINOM ?????? ?? ??? ?????. ??? ???? ?????? anecdotal casual ??? ?? ?????? ?????? ????? ????? ???? ?????? ?????? ???????? "?" ??? ?????? ?? ???? ?? ?????? ?? ??????. ????? ?????? ????? ???? ?????? ?? ????; ?? ?????? ??? ?????????? casual ?????? ???? ?????? ????? ?????? ?? ????? ???? ?????? ?????? ???? ?? 2.

??????

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

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

??? ????? ?????? ???? ??? ???? ??? ???????? ?????? ??? ???????? ??? ???? ???? ?? ??? ???? ??? ??? ????? ?????????? BINOMDIST (?? CRITBINOM) ?? Excel 2003 ?????????? ??????? ?? Excel. ??? ????? ??? ?????????? ?????? ???????? ??????? ?? ??????? ??? ?? ????????? ??????? ?? Excel ?????? # NUM! ?? # DIV/0!.

???????

???? ???????: 828117 - ????? ??? ??????: 28/?? ?????/1427 - ??????: 3.2
????? ???
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
????? ??????: 
kbmt kbformula kbexpertisebeginner kbinfo KB828117 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????828117

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

 

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