Creación de una campana de Gauss con Excel

Descargar el fichero: campana.xlsm

Veamo cómo crear una campana de Gauss. Trataremos de ver gráficamente el Teorema Central del Límite. Partimos de una serie de números aleatorios que se distribuyen según una distribución uniforme entre 0 y 1. U[0,1]

Los aleatorios se obtienen con la función de Excel:

=ALEATORIO()

El teorema central del límite nos dice que necesitaríamos infinitos valores, pero lo haremos con únicamente 12 valores.

Calculamos la media de estos valores con la función:

=PROMEDIO(datos)

Esta media se encuentra en la celda C1, y cada vez que escribimos algo en una celda, o cada vez que pulsamos la tecla F9, se recalculan los valores, ya que se basan en números aleatorios.

Queremos que los valores de la celda C1 (el promedio) se copien secuencialmente en la columna E, y de forma automática. Para conseguir nuestro objetivo utilizaremos una macro.

La macro anota permite escribir los valores de los promedios en la columna E, poniendo tantos como necesitemos. En la imagen el bucle FOR...NEXT llega hasta 10.000 valores.


Histograma

Para crear el histograma de frecuencias definimos los intervalos. Elegimos 20 intervalos y para obtenerlos en la columna G hacemos una serie que comienza en 0 y finaliza en 1, con un intervalo de 0,05.

A su derecha dejamos preparada una zona donde mediante la función FRACUENCIA determinaremos cuantos datos, de entre las 10.000 medias generadas, se encuentran dentro de cada intervalo.

La función Frecuencia tiene la siguiente sintaxis:

=FRECUENCIA(datos;grupos)

• donde los datos son lo valores de la columna E, que es donde se encuentran las 10.000 anotaciones de los promedios generados
• donde grupos es el rango G1:G21 que es done se encuntran los intervalos que hemos definido

Es una función matricial que requiere tres pasos:

1. Seleccionar con el ratón (o con el teclado) la zona donde la función dejará sus resultados. En este caso abarca más de una celda, concretamente es la zona amarilla
2. Se escribe la función matricial propiamente dicha. En este caso, se escribe la función Frecuencia
3. No se valida pulsando INTRO. Se han de pulsar simultáneamente las tres teclas siguientes: CONTROL+MAYÚSCULAS+INTRO

Para saber más sobre funciones matriciales consulte el siguiente post:

Funciones matriciales en Excel

Si ponemos en horizontal los valores obtenidos con la función Frecuencia, y con un poco de imaginación se puede ver ya la campana de Gauss.

Realicemos el gráfico.

Media Móvil

Si pulsamos sobre el gráfico con el botón derecho del ratón, podemos elegir "Agregar línea de tendencia", y de todas ellas elegir la Media Móvil. Así obtendremos el siguiente gráfico.

Vídeo