EXCEL

PROBABILIDADES

 

SIMULACIÓN DEL LANZAMIENTO DE DADOS.

FRECUENCIA Y PROBABILIDAD.

 

Vamos a elaborar una hoja de cálculo con Excel para simular el lanzamiento de 1,2 y 3 dados y analizar numérica y gráficamente la frecuencia con que aparecen los diferentes resultados para compararlos con la probabilidad o frecuencia teórica esperada.

Para ello utilizamos la función =ALEATORIO( ) que genera un número al azar entre 0 y 1. En realidad no es totalmente aleatorio pero sigue una secuencia que podemos considerar impredecible.

Observa las siguientes figuras para interpretar lo que se pretende:

 

 

 

 

CONSTRUCCIÓN DE LA HOJA

 

• Abre una nueva hoja de Excel e introduce en las primera celdas lo siguiente:

 

 

En la celda A3 introduce la expresión =ALEATORIO( ). Se incluyen los paréntesis porque es una función aunque en este caso no depende de ningún valor y por eso se dejan vacíos. Se generará un número entre 0 y 1. Si queremos simular el lanzamiento de un dado bastará multiplicar por 6 y se generará un número entre 0 y 6. Debemos añadir la función ENTERO para eliminar los decimales y añadir 1 para que el número generado esté entre 1 y 6. La expresión final a introducir en A3 será por tanto la siguiente:

 

Selecciona la columna de celdas desde A3 hasta A103 y pulsa CTRL+J para rellenar hacia abajo y copiar la expresión al resto de celdas o arrastra la esquina inferior derecha de la celda A3 hasta llegar a A103.

Pulsando F9 se “recalcula” toda la hoja. En nuestro caso se generará una nueva secuencia de 100 números aleatorios. Pulsa F9 varias veces y compruébalo.

 

• Para hallar las frecuencias ( absolutas) introducimos en la celda C6 la función

=CONTAR.SI(A3:A102 ; “=1”)

Esta función cuenta el número de celdas que cumplen la condición “=1” en el bloque de celdas A3:A103. Así obtendremos el número de veces que “ha salido el 1”.

De forma análoga hallamos la frecuencia del 2, 3, 4, 5 y 6.

En la columna contigua podemos hallar las frecuencias relativas dividiendo las frecuencias absolutas de la columna C entre 100 ( número de lanzamientos).

La hoja puede quedar finalmente de la siguiente forma:

	A	B	C	D
1	100 LANZAMIENTOS DE UN DADO
2	Tiradas
3	=ENTERO(ALEATORIO()*6)+1	total	=SUMA(A3:A102)
4	=ENTERO(ALEATORIO()*6)+1	media	=C3/100
5	=ENTERO(ALEATORIO()*6)+1	frecuencias	ABS	REL
6	=ENTERO(ALEATORIO()*6)+1	1	=CONTAR.SI(A3:A102;"=1")	=C6/100
7	=ENTERO(ALEATORIO()*6)+1	2	=CONTAR.SI(A3:A102;"=2")	=C7/100
8	=ENTERO(ALEATORIO()*6)+1	3	=CONTAR.SI(A3:A102;"=3")	=C8/100
9	=ENTERO(ALEATORIO()*6)+1	4	=CONTAR.SI(A3:A102;"=4")	=C9/100
10	=ENTERO(ALEATORIO()*6)+1	5	=CONTAR.SI(A3:A102;"=5")	=C10/100
11	=ENTERO(ALEATORIO()*6)+1	6	=CONTAR.SI(A3:A102;"=6")	=C11/100
12	=ENTERO(ALEATORIO()*6)+1	TOTAL	=SUMA(C6:C11)	=SUMA(D6:D11)

 

En la celda C3 podemos hallar la suma de todos los valores obtenidos y en C4 el valor medio de los lanzamientos.

Por último podemos añadir en las celdas C12 y D12 dos sumas de comprobación que deberán ser siempre 100 y 1 respectivamente.

 

• Para crear un gráfico de frecuencias sigue los siguientes pasos:

▫         Selecciona las celdas comprendidas entre C6 y C11.

▫          Elige la opción Insertar-Gráfico en el menú de la barra de herramientas.

▫         Acepta la primera opción marcada (Columnas) y pulsa el último botón “Finalizar”. Puedes arrastrar la ventana gráfica a la posición que desees y modificar su tamaño seleccionando la ventana del gráfico ( basta hacer clic) y arrastrando los bordes.

Pulsa repetidamente la tecla F9 y observa que las barras no suelen variar mucho de una secuencia a otra y sus alturas oscilan en torno a 100/6= 16,666....

Incluso puedes mantener pulsada la tecla F9 y observar la generación continuada de secuencias de lanzamientos.

Si seleccionas las celdas D6:D11 en vez de C6:C11 obtendrás un gráfico de las frecuencias relativas que será idéntico al de frecuencias absolutas salvo que las unidades del eje Y serán distintas ( de 0 a 1). Si sitúas el ratón sobre las unidades del eje Y y haces clic con el botón secundario ( normalmente el derecho) se abrirá una ventana con dos opciones. Selecciona “Formato de ejes...” y accederás a una nueva ventana. Pulsa la solapa “Escala” en la parte superior y marca un valor máximo para el eje Y ( por ejemplo 25 para frecuencias absolutas o 0,25 para relativas). De esta forma evitarás que las unidades del eje Y se adapten a cada secuencia de valores y cambien continuamente.

 

 

Puedes mejorar la visualización de la hoja añadiendo colores de fondo en algunas celdas para diferenciarlas.

También puedes considerar un número distinto de lanzamientos sin más que ampliar o disminuir el número de celdas de la columna A, aunque deberás sustituir 100 por el nuevo número de lanzamientos en las celdas que calculan las frecuencias relativas.

 

LANZAMIENTO DE DOS Y TRES DADOS.

 

De forma análoga podemos construir una hoja para simular el lanzamiento de dos dados. Los lanzamientos aleatorios se obtendrían en las columnas A y B. La suma de los puntos de los dos dados se obtendría en la columna C. Los valores a “contar” se deben ampliar hasta 12.

Abre una nueva hoja pulsando Hoja 2 en la parte inferior de la ventana de Excel e introduce lo siguiente en las celdas correspondientes.

 

 

 

Puedes introducir la expresión de E6 ( incluyendo los signos $) y rellenar hacia abajo hasta A17. Al incluir el signo $ las referencias no se incrementarán A continuación sitúate en cada celda , pulsa F2 para modificarla y cambia “=1” por el resultado correspondiente de la celda a su izquierda.

 

En las celdas A3 y B3 introduce la expresión =ENTERO(ALEATORIO()*6)+1. A continuación selecciona A3:B103 y pulsa CTRL.+J para “rellenar hacia abajo”.

Para crear un gráfico de las frecuencias relativas selecciona las celdas  F6:F17.

Pulsa F9 repetidamente y observa las frecuencias y el gráfico. Observa que no todos los resultados tienen la misma probabilidad de “salir”. Es más fácil “sacar  8” que “sacar 12”. Observa también que nunca sale 1 ( piensa por qué) y podíamos haberlo eliminado del gráfico y las frecuencias.

 

Para considerar 100 lanzamientos de tres dados habrá que añadir una columna para el tercer dado, modificar la columna de la suma y ampliar los posibles resultados hasta 18.

Abre una nueva hoja, introduce la expresión =ENTERO(ALEATORIO()*6)+1 en las celdas A3,B3 y C3, selecciona A3:C103 y “rellena hacia abajo” con CTRL.+J. A continuación completa la hoja con las siguientes expresiones:

 

 

En las celdas F6 a F23 debes incluir una expresión análoga a la de F6  para contemplar todos los resultados posibles desde “=1” hasta “=18” aunque los casos “=1” , “=2” sabemos que en realidad son imposibles y podemos eliminarlos. Puedes introducir la expresión de F6 ( incluyendo los signos $) y rellenar hacia abajo. A continuación sitúate en cada celda , pulsa F2 para modificarla y cambia “=1” por el resultado correspondiente de la celda a su izquierda.

Crea el correspondiente gráfico con las frecuencias relativas de la columna G.

 

 LANZAMIENTOS SUCESIVOS DE UN DADO

 

Para no limitar a 100 el número de lanzamientos podemos elaborar una nueva hoja que procese un solo lanzamiento y vaya acumulando los resultados tras cada pulsación de la tecla F9 que en Excel provoca un nuevo “cálculo” de toda la hoja y por tanto de las expresiones que contengan la función ALEATORIO( ).

El resultado puede ser como el de la siguiente figura:

 

 

Abre una nueva hoja ( puedes pulsar “hoja 4” en la parte inferior de la ventana de Excel) e introduce lo siguiente:

 

 

Al pulsar F9 se genera en B2 un nuevo valor entre 1 y 6. Para ir acumulando los resultados que van saliendo incluimos en B3 la expresión =B3+B2. Pero al incluir en la expresión una referencia a la propia celda B3 podemos recibir un mensaje de aviso sobre una “referencia circular”. Para evitarlo debemos indicar a Excel que habilite la opción de “iteración” o cálculo repetitivo. Para ello abre el menú “Herramientas” y la opción “Opciones”. Selecciona la ficha “Calcular” en la parte superior de la ventana  y marca la casilla “Iteración”. Conviene incluir 1 como Nº máximo de iteraciones para que los lanzamientos se procesen de uno en uno.

 

 

Ahora ya podemos incluir =B3+B2 en la celda B3 y la expresión =B4+1 en la celda B4 para ir contando el número de lanzamientos ( número de pulsaciones de F9).

En la celda D2 escribiremos =SI(B2=1 ; D2+1 ; D2). De esta forma cada vez que “salga 1” se incrementará el valor anterior de la celda D2, y si no sale se tomará de nuevo el valor anterior.

Antes de proceder de igual forma para el resto de resultados consideremos el siguiente inconveniente. Cada vez que pulsemos F9 se acumula el nuevo resultado a los anteriores pero no podemos volver al principio y comenzar de nuevo desde 0.

Para conseguirlo introducimos 0 en la celda B6 y modificamos las expresiones de las demás celdas. La expresión del número de tiradas efectuadas que se muestra en B4 será la siguiente =SI(B6=0 ; 0 ; B4+1). De esta forma si introducimos 0 en B6 se mostrará 0 como número de tiradas. Sólo al volver a introducir otro valor distinto de 0 ( por ejemplo 1) en la celda B6 se iniciará una nueva secuencia de tiradas. En el resto de expresiones habrá que incluir también =SI(B6=0 ; 0 ; .........)

Las expresiones finales pueden ser las siguientes:

 

 

La inclusión del signo $ en las columnas D y E permiten “rellenar hacia abajo” para después modificar las respectivas expresiones con F2.

Completa la hoja creando un gráfico con las celdas E2:E7 que contienen las frecuencias relativas.

Para probar la hoja introduce 0 en B6 y pulsa F9. Introduce ahora 1 en B6 y vuelve a pulsar repetidamente F9 ( puedes mantenerlo pulsado un tiempo razonable). Comprueba que al principio pueden observarse discrepancias entre las frecuencias relativas y la probabilidad teórica 1/6 pero a medida que va aumentando el número de pruebas se constata la Ley de los grandes números y las frecuencias tienden a estabilizarse en torno a la probabilidad.

 

Puedes mejorar el aspecto de la hoja poniendo colores de fondo para distinguir mejor las diferentes columnas y la zona de datos iniciales y resultados, centrando el contenido de algunas celdas, coloreando las celdas que contienen títulos o etiquetas, etc.

 

También puedes añadir un comentario de ayuda que aparezca al pasar el ratón por alguna celda. Por ejemplo sitúa el cursor en la celda B6 y elige la opción Insertar-Comentario en la barra de herramientas de la parte superior de la ventana y escribe:”Introduce 0 para iniciar nueva secuencia.”..

 

 

 

Es fácil modificar la hoja para considerar el lanzamiento de dos dados o más. Basta añadir dos celdas tirada1 y tirada2 y asignar a tirada la suma de ambas. También habrá que añadir más celdas para contener los resultados posibles ( de 2 a 12) y adaptar el gráfico a los nuevos datos ampliados. Aunque la suma 1 no puede aparecer conviene incluirla para facilitar la creación del gráfico si no quieres introducir expresamente la serie de valores para el eje X.

 

 

En el archivo probabilidad.xls se muestra una hoja ya elaborada con las indicaciones que se proponen.

 

 PROBABILIDADES  A POSTERIORI. FÓRMULA DE BAYES.

 Considera el siguiente problema que aparece en el libro:

 

Un gato persigue a un ratón. Éste puede entrar en uno de los callejones a1,a2 o a3.

En cada uno de ellos puede cazarlo, +, o no. Se sabe:

P(entre por a1)=P(a1)=0,3      ;           P(a2)=0,5        ;           P(a3)=0,2.

 

P(lo cace habiendo entrado en A)=P(b1/a1)=0,4        P(b1/a2)=0,6   P(b1/a3)=0,1

 

Calcular la probabilidad de el gato cace al ratón.

Supongamos que el gato ha cazado al ratón ¿Cuál es la probabilidad de que haya sido entrando por el callejón a1?

 Vamos a elaborar una hoja de cálculo con Excel para resolver este tipo de problemas. Consideramos dos experiencias aleatorias A y B. Para cada una de ellas consideramos tres posibilidades con sus correspondientes probabilidades. Si sólo hay dos sucesos posibles basta asignar probabilidad 0 al tercero. Si queremos considerar un mayor número de posibilidades habría que ampliar la hoja pero sería fácil hacerlo.

 

 

 

CONSTRUCCIÓN DE LA HOJA

 

• Abre una nueva hoja de Excel e introduce en las primera celdas lo siguiente:

 

 

En las celdas B3,B4 y B5 se introducen las probabilidades de los sucesos a1, a2 y a3 que conforman la primera prueba. Como elemento de comprobación podemos añadir en la celda B6 la expresión =SUMA(B3:B5) o =B3+B4+B5 para asegurarnos de que suman 1. Si introduces los datos de otro ejemplo que se te ocurra puedes ir comprobando con esta celda las probabilidades que puedes considerar.

En las celdas C3, C4 y C5 introducimos las probabilidades del primer suceso de la segunda prueba condicionado a cada uno de los sucesos de la primera prueba. De forma análoga introducimos las probabilidades de los demás sucesos condicionados de la segunda prueba en las celdas D3 a E5. También podemos incluir en E3 la expresión =C3+D3+E3 para comprobar que suman 1 y de forma análoga las expresiones =C4+D4+E4 y =C5+D5+E5 en las celdas E4 y E5 respectivamente.

En las celdas comprendidas entre C8 y E8 incluimos las probabilidades de los distintos “caminos posibles” o ramas del grafo correspondientes a la intersección de cada suceso de la primera prueba y cada suceso de la segunda ( callejón a1 y es cazado, etc). Estas probabilidades se hallan multiplicando las correspondientes probabilidades de los sucesos. Al incluir el signo $ podemos rellenar sólo la celda C8 y copiarla al resto de celdas.

 

Las probabilidades totales de los sucesos de la segunda prueba ( por ejemplo “ser cazado”) se calculan en las celdas C12, D12 y E12 sumando las correspondientes probabilidades de los sucesos condicionados situados en las celdas superiores.

 

Para hallar las probabilidades “a posteriori” introduce en las celdas G8 a I10 lo que se muestra en la siguiente tabla:

 

 

En la celda G8 se divide la probabilidad del suceso intersección P(a1 y b1) entre la probabilidad total del suceso b1. De esta forma obtenemos P(b1/a1).Al incluir el signo $ basta introducir la expresión de G8 y copiarla al resto de celdas.

 

También puedes incluir varias celdas para comprobar que las correspondientes  probabilidades introducidas como datos suman 1 y evitar posibles errores.

 

Prueba la hoja con diferentes ejemplos comprobando con las celdas de control que las probabilidades que propongas sumen 1.

 

En el archivo bayes.xls se muestra una hoja ya elaborada con las indicaciones que se proponen.

 

---