14 Jun Aplicar probabilidad arbitraria en valores predefinidos
Funciones utilizadas: SUMAR, INDICE, COINCIDIR, ALEATORIO, CONTAR.SI
En este artículo explicaremos como podemos aplicar probabilidades distintas a un grupo de valores, y apreciar una cantidad determinada de sucesos aleatorios con cierta configuración de probabilidad. En otras palabras, analizaremos el peso de probabilidad para distintos números aleatorios. También podremos observar la distribución real de una ejecución de N sucesos y cual es la desviación real respecto a la probabilidad esperada.
Lo primero que vamos a realizar es una tabla en el rango A1:C7 con 3 columnas representadas por los encabezados: VALOR, PROBABILIDAD y PROBABILIDAD ACUMULADA.
En el rango A2:A7 se colocarán los valores de RESULTADO que deseamos que devuelva cada suceso aplicado. Tal como se ejemplifica en el video, estaremos analizando como valores de resultado en el rango A2:A7 a distintos colores, donde A2=Azul, A3=Rojo, A4=Amarillo, A5=Verde, A6=Naranja y A7=Violeta. En el rango B2:B7 vamos a especificar en porcentaje cual es la probabilidad de que dicho valor de resultado ocurra o sea elegido, y en principio lo haremos con distribución uniforme de probabilidades, es decir 16,7% por valor (1 sobre 6). En el rango C2:C7 colocaremos una fórmula que sume la probabilidad acumulada de la celda inmediata superior y la probabilidad en porcentaje de la misma fila. Es decir, por ejemplo en la celda C2 vamos a colocar la función =SUMA(C1;B1) y arrastraremos hacia abajo hasta la celda C7. De esta forma, la celda C2 siempre va a comenzar con una probabilidad acumulada de cero (0) debido a que “sumaría” los textos de los encabezados que si bien parece extraño, tiene una explicación que veremos más adelante.
En la celda E1 colocaremos el encabezado SUCESOS y en el rango E2:E21 (20 sucesos en total) vamos a generar la ejecución de cada suceso en función de nuestra primer tabla con colores, peso de probabilidad por color y una probabilidad acumulada. Para ello en la celda E2 colocamos la siguiente fórmula:
|
Esta fórmula parte internamente de una función ALEATORIO que nos devolverá un valor entre 0 y 1, para luego ser el valor buscado de la función coincidir la cual apunta al rango de nuestra probabilidad acumulada y finalmente hallar el valor de resultado (color) correspondiente con la función indice. Debemos arrastrarla hasta la celda E21.
El tercer argumento de la función coincidir está omitido debido a que nos permite devolver la posición del valor más grande menor o igual al valor buscado, es decir que la fórmula “viaja” a través del rango de probabilidad acumulada de la columna C hasta encontrar un valor más grande para así volver a la posición anterior, y definimos arbitrariamente que el primer valor en C2 fuese cero (o) para asegurarnos de que todos los valores menores a 0.1 sean encontrados y permita devolver en estos casos la primer posición.
Lo tercero que haremos será otra tabla en el rango G1:I7 con 3 columnas representadas por los encabezados: VALOR, DISTRIBUCIÓN REAL y DESVIACIÓN REAL.
Esta tabla nos servirá de control para evaluar si nuestra fórmula que aplicaremos para cada suceso es correcta, y además nos permitirá evaluar dos cuestiones importantes, la distribución y la desviación. La distribución real se refiere a cuál fue, luego de nuestra ejecución de los 20 sucesos, la distribución ocurrida en porcentaje de cada valor de resultado del rango inicial A2:A7, mientras que la desviación real es la diferencia entre la distribución real (o sea lo que ocurrió) y la probabilidad esperada (o sea lo que definimos nosotros). La distribución real debería siempre tender al valor de probabilidad esperado, mientras que la desviación real debería tender siempre a cero dado que lo esperado a infinitas iteraciones debería tender a ser igual a lo ocurrido.
En el rango G2:G7 copiaremos los mismos valores que en el rango A2:A7, es decir los colores que podemos obtener en cada suceso. La fórmula que aplicaremos en H2 para la primer distribucíón y para evaluar que porcentaje del total arrojó el color Azul es la siguiente:
|
Arrastramos la fórmula hasta la celda H7 y modificamos en cada celda del rango el argumento “Azul” por el color correspondiente a cada fila.
Luego en el rango I2:I7 la fórmula que aplicaremos será una simple resta de distribución real menos probabilidad. En la celda I2 colocamos la siguiente fórmula y arrastramos la fórmula hasta la celda I7:
|
Finalmente tenemos nuestra hoja armada para probar distintas probabilidades, es decir “jugar” con los valores porcentuales del rango B2:B7, pero siempre sumando un total de 100% en el rango. A medida que vamos variando dicho rango y refrescamos las fórmulas del rango de sucesos E2:E21, vamos a tener diversos resultados que se acomodan mayormente a estas probabilidades de ocurrencia. Y lo podremos controlar mediante los valores de distribución real y desviación real.
Como dato adicional, en el video se aplica formato condicional al rango de sucesos E2:E21 para que de forma visual y rápidamente se puedan interpretar los resultados.
Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.
Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.