Funciones estadísticas de Excel: DISTR.BINOM

Seleccione idioma Seleccione idioma
Id. de artículo: 827459 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

En este artículo se describe la función DISTR.BINOM de Microsoft Office Excel 2003 y de versiones posteriores de Excel, se ilustra cómo utilizarla y se comparan los resultados de la función para Excel 2003 y versiones posteriores de Excel con los resultados cuando se utiliza en versiones anteriores de Excel.

Información de Microsoft Excel 2004 para Mac

Las funciones estadísticas de Excel 2004 para Mac se actualizaron utilizando los mismos algoritmos empleados para actualizar las funciones estadísticas en Excel 2003 y en versiones posteriores de Excel. Toda la información de este artículo que describe cómo actúa una función o cómo se ha modificado una función para Excel 2003 y para las versiones posteriores de Excel también se aplica a Excel 2004 para Mac.

Más información

Cuando acumulado = TRUE, la función DISTR.BINOM(x, n, p, acumulado) devuelve la probabilidad de que se produzcan x o menos resultados satisfactorios en n ensayos de Bernoulli independientes. Cada uno de los ensayos tiene una probabilidad asociada p de resultado satisfactorio (y una probabilidad 1-p de error). Cuando acumulado = FALSE, DISTR.BINOM devuelve la probabilidad de que se produzcan exactamente x resultados satisfactorios.

Sintaxis

BINOMDIST(x, n, p, cumulative)

Parámetros

  • x es un entero no negativo
  • n es un entero positivo
  • 0 < p < 1
  • Acumulado es una variable lógica que toma los valores TRUE o FALSE

Ejemplo de uso

Imagine lo siguiente:
  • En béisbol, un "bateador .300" acierta (tiene resultados satisfactorios) con una probabilidad de 0,300 cada vez que batea (cada ensayo).
  • Los bateos sucesivos son ensayos de Bernoulli independientes.
Puede utilizar la tabla siguiente para encontrar la probabilidad de que este bateador obtenga exactamente 0, 1, 2, ..., o 10 aciertos en 10 ensayos y la probabilidad de que obtenga 0, 1 o menos, 2 o menos, ... 9 o menos, o 10 o menos aciertos en 10 ensayos.

Si el bateador obtiene 50 aciertos en sus 200 primeros ensayos (un promedio de ,250), debe obtener 100 aciertos en sus 300 ensayos siguientes para tener 150 aciertos y un promedio de ,300 en 500 ensayos. Puede utilizar la tabla siguiente para analizar la posibilidad de que el bateador obtenga aciertos suficientes para mantener su promedio. Los comentaristas de béisbol aluden con frecuencia a la "ley de promedios" cuando dicen que los aficionados no tienen que preocuparse por el rendimiento de este bateador con solamente 50 aciertos en sus primeros 200 ensayos porque "a finales de temporada su promedio será de ,300". Si los ensayos fueran realmente independientes y el bateador tuviera realmente una probabilidad de éxito de 0,3 en cualquier ensayo, este razonamiento sería engañoso porque los resultados de los primeros 200 ensayos no afectan al éxito o al error de los últimos 300 ensayos.

Para ilustrar el uso de DISTR.BINOM, cree una hoja de cálculo de Excel en blanco, copie la tabla siguiente, seleccione la celda A1 en la hoja de cálculo de Excel en blanco y, a continuación, pegue las entradas de manera que la tabla siguiente rellene las celdas A1:C22 de la hoja de cálculo.
Contraer esta tablaAmpliar esta tabla
número de ensayos10
probabilidad de éxito0.3
resultados satisfactorios, xP(exactamente x resultados satisfactorios)P(x o menos resultados satisfactorios)
0=DISTR.BINOM(A4,$B$1,$B$2,FALSE)=DISTR.BINOM(A4,$B$1,$B$2,TRUE)
1=DISTR.BINOM(A5,$B$1,$B$2,FALSE)=DISTR.BINOM(A5,$B$1,$B$2,TRUE)
2=DISTR.BINOM(A6,$B$1,$B$2,FALSE)=DISTR.BINOM(A6,$B$1,$B$2,TRUE)
3=DISTR.BINOM(A7,$B$1,$B$2,FALSE)=DISTR.BINOM(A7,$B$1,$B$2,TRUE)
4=DISTR.BINOM(A8,$B$1,$B$2,FALSE)=DISTR.BINOM(A8,$B$1,$B$2,TRUE)
5=DISTR.BINOM(A9,$B$1,$B$2,FALSE)=DISTR.BINOM(A9,$B$1,$B$2,TRUE)
6=DISTR.BINOM(A10,$B$1,$B$2,FALSE)=DISTR.BINOM(A10,$B$1,$B$2,TRUE)
7=DISTR.BINOM(A11,$B$1,$B$2,FALSE)=DISTR.BINOM(A11,$B$1,$B$2,TRUE)
8=DISTR.BINOM(A12,$B$1,$B$2,FALSE)=DISTR.BINOM(A12,$B$1,$B$2,TRUE)
9=DISTR.BINOM(A13,$B$1,$B$2,FALSE)=DISTR.BINOM(A13,$B$1,$B$2,TRUE)
10=DISTR.BINOM(A14,$B$1,$B$2,FALSE)=DISTR.BINOM(A14,$B$1,$B$2,TRUE)
300 ensayos, probabilidad de resultados satisfactorios de 0,3:
resultados satisfactorios, xP(exactamente x resultados satisfactorios)P(x o menos resultados satisfactorios)
89=DISTR.BINOM(A18,300,0.3,FALSE)=DISTR.BINOM(A18,300,0.3,TRUE)
90=DISTR.BINOM(A19,300,0.3,FALSE)=DISTR.BINOM(A19,300,0.3,TRUE)
99=DISTR.BINOM(A20,300,0.3,FALSE)=DISTR.BINOM(A20,300,0.3,TRUE)
100=DISTR.BINOM(A21,300,0.3,FALSE)=DISTR.BINOM(A21,300,0.3,TRUE)
101=DISTR.BINOM(A22,300,0.3,FALSE)=DISTR.BINOM(A22,300,0.3,TRUE)
Nota: después de pegar esta tabla en la nueva hoja de cálculo de Excel, haga clic en el botón Opciones de pegado y, a continuación, haga clic en Coincidir con formato de destino. Con el rango pegado todavía seleccionado, utilice uno de los procedimientos siguientes según la versión de Excel que esté ejecutando:
  • En Microsoft Office Excel 2007, haga clic en la ficha Inicio, haga clic en Formato en el grupo Celdas y, a continuación, haga clic en Autoajustar ancho de columna.
  • En Excel 2003 y en versiones anteriores de Excel, seleccione Columna en el menú Formato y haga clic en Autoajustar a la selección.
Puede dar formato a las celdas B4:C22 para que sean coherentes (por ejemplo, números con cinco posiciones decimales).

Las celdas B4:B14 muestran las probabilidades de que haya exactamente x éxitos en 10 ensayos. El número más probable de resultados satisfactorios es 3. Las posibilidades de que haya 0, 6, 7, 8, 9 ó 10 resultados satisfactorios son menores de 0,05 y llegan hasta 0,076. Por tanto, las posibilidades de 1, 2, 3, 4 ó 5 resultados satisfactorios es de 1 ? 0,076 = 0,924. Las celdas C4:C14 muestran las probabilidades de que haya x o menos resultados satisfactorios en 10 ensayos. Puede comprobar que cada una de las entradas de la columna C de cualquier fila es igual a la suma de todas las entradas de la columna B, hasta esa fila incluida.

Las celdas B18:B20 muestran que el número más probable de resultados satisfactorios en 300 ensayos es 90. La probabilidad de que haya exactamente x resultados satisfactorios aumenta a medida que x aumenta a 90 y después disminuye a medida que x sigue aumentando por encima de 90. La posibilidad de que haya 90 o menos resultados satisfactorios es justo superior al 50%, como muestra la celda C20. La posibilidad de que haya 99 o menos resultados satisfactorios es del 0,884. Por tanto, sólo hay una probabilidad del 11,6% (0,116 = 1 ? 0,884) de 100 o más resultados satisfactorios.

Resultados en versiones anteriores de Excel

Knusel (vea la nota 1) documentó casos en los que DISTR.BINOM no devuelve una respuesta numérica y produce #¡NUM! en su lugar debido a un desbordamiento numérico. Cuando DISTR.BINOM devuelve respuestas numéricas, son correctas. DISTR.BINOM solamente devuelve #¡NUM! cuando el número de ensayos es mayor o igual a 1030. No hay ningún problema de cálculo si n < 1030. En la práctica, esos valores tan elevados de n son improbables. Con un número tan alto de ensayos independientes, puede que un usuario desee aproximar la distribución binomial por una distribución normal (si n*p y n*(1-p) son suficientemente altos, por ejemplo, cada uno es mayor que 30) o, de lo contrario, por una distribución de Poisson.

Nota 1: Knusel, L. "On the Accuracy of Statistical Distributions in Microsoft Excel 97", Computational Statistics and Data Analysis (1998), 26: 375-377.

Para el caso no acumulativo, DISTR.BINOM(x, n, p, false) utiliza la fórmula siguiente:
COMBIN(n,x)*(p^x)*((1-p)^(n-x))
COMBINAT es una función de Excel que proporciona el número de combinaciones de elementos x en una población de n elementos. COMBINAT(n,x) se escribe a veces nCx y se denomina un "coeficiente combinatorio" o simplemente "n choose x". Si prueba con COMBINAT escribiendo =COMBINAT(1029,515) en una celda y =COMBINAT(1030,515) en otra celda diferente, la primera celda devuelve un número astronómico, 1,4298E+308, y la segunda celda devuelve #¡NUM! porque es aún mayor. El desbordamiento de COMBINAT produce un desbordamiento de DISTR.BINOM en las versiones anteriores de Excel.

COMBINAT no se ha modificado para Excel 2003 y para las versiones posteriores de Excel.

Resultados en Excel 2003 y en versiones posteriores de Excel

Puesto que Microsoft ha diagnosticado cuándo un desbordamiento hace que DISTR.BINOM devuelva #¡NUM! y sabe que DISTR.BINOM se comporta bien cuando no se produce el desbordamiento, Microsoft ha implementado un algoritmo condicional en Excel 2003 y en las versiones posteriores de Excel.

El algoritmo utiliza código de DISTR.BINOM de versiones anteriores de Excel (la fórmula de cálculo mencionada anteriormente en este artículo) cuando n < 1030. Cuando n >= 1030, Excel 2003 y las versiones posteriores de Excel utilizan el algoritmo alternativo que se describe más adelante en este artículo.

Normalmente, COMBINAT se desborda porque es astronómico, pero p^x y (1-p)^(n-x) son cada uno infinitesimales. Si fuera posible multiplicar juntos estos valores, el producto sería una probabilidad realista de entre 0 y 1. Sin embargo, como la aritmética finita existente no puede multiplicarlos, un algoritmo alternativo evita la evaluación de COMBINAT.

El enfoque de Microsoft calcula una suma sin escala de todas las probabilidades de exactamente x resultados satisfactorios que se utilizan después para los propósitos de escala. También calcula un valor sin escala de la probabilidad que desea que DISTR.BINOM devuelva. Por último, utiliza el factor de escala para devolver un valor de DISTR.BINOM correcto.

El algoritmo aprovecha el hecho de que la proporción de términos sucesivos del formato COMBINAT(n,k)*(p^k)*((1-p)^(n-k)) tiene un formato sencillo. El algoritmo funciona como se describe en el pseudocódigo incluido en los pasos siguientes.

Paso 0: (Inicialización). Inicialice las propiedades TotalUnscaledProbability y UnscaledResult a 0. Inicialice la constante EssentiallyZero a un número muy pequeño, por ejemplo 10^(-12).

Paso 1: busque n*p y redondee hacia abajo hasta el número entero más cercano, m. El número más probable de resultados satisfactorios en n ensayos es m o m+1. COMBINAT(n,k)*(p^k)*((1-p)^(n-k)) disminuye a medida que k disminuye de m a m-1 a m-2, y así sucesivamente. Además, COMBINAT(n,k)*(p^k)*((1-p)^(n-k)) disminuye a medida que k aumenta de m+1 a m+2 a m+3, y así sucesivamente.
TotalUnscaledProbability = TotalUnscaledProbability + 1;
If (m == x) then UnscaledResult = UnscaledResult + 1;
If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;
Paso 2: calcular las probabilidades sin escala para 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;
Paso 3: calcular las probabilidades sin escala para 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;
Paso 4: combinar los resultados sin escala:
Return UnscaledResult/TotalUnscaledProbability;
Aunque este método sólo se utiliza para n >= 1030, puede utilizar las sumas siguientes en la hoja de cálculo de Excel para ayudarle a ejecutar manualmente este algoritmo para calcular DISTR.BINOM(3, 10, 0.3, TRUE) (en el ejemplo del béisbol, la posibilidad de que haya 3 o menos aciertos en 10 ensayos para un bateador .300).

Para ilustrarlo, copie la tabla siguiente, seleccione la celda D4 de la hoja de cálculo de Excel que creó anteriormente y pegue las entradas de forma que la tabla siguiente rellene las celdas D1:E15 de la hoja de cálculo.
Contraer esta tablaAmpliar esta tabla
=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
=SUMA(D4:D14)
La columna D contiene las probabilidades sin escala. El 1 de la celda D6 es el resultado del paso 1 del algoritmo. Excel 2003 y las versiones posteriores de Excel calculan las entradas de las celdas D7, D8, ..., D14 (en ese orden) en el paso 2 y las entradas de las celdas D5 y D4 (en ese orden) en el paso 3. La suma de todas las probabilidades sin escala aparece en D15.

Para calcular la probabilidad de 3 o menos resultados satisfactorios, escriba la fórmula siguiente en cualquier celda en blanco:
= SUMA(D4:D7)/D15
En el ejemplo anterior, EssentiallyZero no detiene los pasos 2 ó 3. Sin embargo, si desea evaluar DISTR.BINOM(550, 2000, 0.3, TRUE), EssentiallyZero puede detener el paso 2 o el paso 3. Una variable binomial aleatoria con n = 2000 y p = 0,3 tienen una distribución que está aproximada por la normal con una media de 600 y una desviación estándar RCUAD(2000*0.3*(1 ? 0.3)) = RCUAD(420) = 20.5. Entonces, 805 es 10 desviaciones estándar superior a la media y 395 es 10 desviaciones estándar inferior a la media. Dependiendo del valor de EssentiallyZero, EssentiallyZero puede detener el paso 2 antes de llegar a 805 y puede detener el paso 3 antes de llegar a 395.

Conclusiones

Las imprecisiones en las versiones de Excel anteriores a Excel 2003 solamente se producen cuando el número de ensayos es mayor o igual a 1030. En esos casos, DISTR.BINOM devuelve #¡NUM! en las versiones anteriores de Excel porque un término se desborda en una secuencia de términos que se multiplican juntos. Para corregir este comportamiento en Excel 2003 y en versiones posteriores de Excel, utilice el procedimiento mencionado anteriormente en este artículo cuando de lo contrario se produciría un desbordamiento.

En las versiones anteriores de Excel, las funciones BINOM.CRIT, DISTR.HIPERGEOM, NEGBINOMDIST y POISSON se comportan de manera similar. Estas funciones también devuelven resultados numéricos correctos, o #¡NUM! o #¡DIV/0! De nuevo, los problemas se deben a un desbordamiento (o a un subdesbordamiento).

Es fácil determinar cuándo y cómo se producen estos problemas. Excel 2003 y las versiones posteriores de Excel utilizan un algoritmo alternativo similar al de DISTR.BINOM para devolver respuestas correctas en aquellos casos donde las versiones anteriores de Excel devuelven #¡NUM!

Propiedades

Id. de artículo: 827459 - Última revisión: viernes, 25 de enero de 2008 - Versión: 4.0
La información de este artículo se refiere a:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
Palabras clave: 
kbinfo kbexpertisebeginner KB827459

Enviar comentarios

 

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