Cómo Crear una Curva de Campana – Distribución Normal en Excel

Este tutorial demostrará cómo crear una curva de campana de distribución normal en todas las versiones de Excel: 2007, 2010, 2013, 2016 y 2019.

 

En estadística, una curva de campana (también conocida como distribución normal estándar o curva de Gauss) es un gráfico simétrico que ilustra la tendencia de los datos a agruparse alrededor de un valor central, o media, en un conjunto de datos determinado.

El eje Y representa la probabilidad relativa de que un valor determinado se produzca en el conjunto de datos, mientras que el eje X traza los propios valores en el gráfico para crear una curva en forma de campana, de ahí su nombre.

El gráfico nos ayuda a analizar si un valor concreto forma parte de la variación esperada o es estadísticamente significativo y, por tanto, hay que examinarlo con más detenimiento.

Como Excel no tiene ninguna solución incorporada que ofrecer, tendrá que trazarlo usted mismo. Por eso hemos desarrollado el Complemento Creador de Gráficos, una herramienta que le permite construir gráficos avanzados en Excel con sólo unos pocos clics.

En este tutorial paso a paso, aprenderá a crear una curva de campana de distribución normal en Excel desde el principio:

Para trazar una curva gaussiana, necesitas saber dos cosas:

• La media (también conocida como medida estándar). Determina el centro de la curva que, a su vez, caracteriza la posición de la curva.
• La desviación estándar (DE ) de las medidas. Define la dispersión de los datos en la distribución normal o, en términos sencillos, la amplitud de la curva. Por ejemplo, en la curva de campana mostrada arriba, una desviación estándar de la media representa el rango entre las puntuaciones del examen de 53 y 85.

Cuanto menor sea la DE, más alta será la curva y menos se dispersarán los datos, y viceversa.

Vale la pena mencionar la regla 68-95-99,7 que puede aplicarse a cualquier curva de distribución normal, lo que significa que aproximadamente el 68% de los datos se situarán a una DE de la media, el 95% a dos DE y el 99,7% a tres DE.

 

Ahora que conoce lo esencial, pasemos de la teoría a la práctica.

Cómo Empezar

A modo de ilustración, supongamos que tiene las notas de los exámenes de 200 estudiantes y quiere calificarlos «en una curva», lo que significa que las notas de los estudiantes se basarán en su rendimiento relativo con respecto al resto de la clase:

Paso 1: Encontrar la Media.

Normalmente, se le dan los valores de la media y la DE desde el principio, pero si ese no es el caso, puede calcular fácilmente estos valores en unos pocos pasos sencillos. Abordemos primero la media.

Dado que la media indica el valor promedio de una muestra o población de datos, puede encontrar su medida estándar utilizando la función PROMEDIO.

Escribe la siguiente fórmula en cualquier celda vacía(F1 en este ejemplo) junto a tus datos reales (columnas A y B) para calcular la media de las puntuaciones del examen en el conjunto de datos:

=PROMEDIO(B2:B201)

Una nota rápida: en la mayoría de los casos, puede ser necesario redondear la salida de la fórmula. Para ello, simplemente envuélvela en la función REDONDEAR de la siguiente manera

=REDONDEAR(PROMEDIO(B2:B201);0)

 

Paso #2: Encontrar la Desviación Estándar.

Uno menos, uno más. Afortunadamente, Excel tiene una función especial para hacer todo el trabajo sucio de encontrar la desviación estándar para usted:

=DESVEST.P(B2:B201)

De nuevo, la fórmula recoge todos los valores del rango de celdas especificado(B2:B201) y calcula su desviación estándar, pero no te olvides de redondear el resultado.

=(REDONDEAR(DESVEST.P(B2:B201);0))

 

Paso #3: Configurar los Valores del Eje X para la Curva.

Básicamente, el gráfico constituye un número masivo de intervalos (piense en ellos como pasos) unidos con una línea para crear una curva suave.

En nuestro caso, los valores del eje x se utilizarán para ilustrar la puntuación de un examen en particular, mientras que los valores del eje y nos indicarán la probabilidad de que un estudiante obtenga esa puntuación en el examen.

Técnicamente, puedes incluir tantos intervalos como quieras; puedes borrar sin esfuerzo los datos redundantes más adelante modificando la escala del eje horizontal. Sólo asegúrese de elegir un intervalo que incorpore las tres desviaciones estándar.

Empecemos un recuento en uno (ya que no hay forma de que un estudiante obtenga una puntuación negativa en el examen) y vayamos hasta 150 -realmente no importa si es 150 o 1500- para establecer otra tabla de ayuda.

1. Elija cualquier celda vacía debajo de los datos del gráfico (como E4) y escriba «1», el valor que define el primer intervalo.
2. Vaya a la pestaña Inicio.
3. En el grupo Edición, elige «Rellenar».«
4. En «Serie en», seleccione «Columna».«
5. En «Valor de paso«, escriba «1» Este valor determina los incrementos que se sumarán automáticamente hasta que Excel llegue al último intervalo.
6. En «Valor de parada»,escriba «150 «, el valor que representa el último intervalo, y haga clic en «Aceptar»

Milagrosamente, 149 celdas de la columna E(E5:E153) se han llenado con los valores que van de 2 a 150.

NOTA: No oculte las celdas de datos originales como se muestra en las capturas de pantalla. De lo contrario, la técnica no funcionará.

Paso #4: Calcule los Valores de la Distribución Normal para Cada Valor del Eje X.

Ahora, encuentra los valores de la distribución normal -la probabilidad de que un estudiante obtenga una determinada puntuación en el examen representada por un valor particular del eje x- para cada uno de los intervalos. Afortunadamente para ti, Excel tiene el caballo de batalla para hacer todos estos cálculos por ti: la función NORM.DIST.

Escribe la siguiente fórmula en la celda a la derecha(F4) de tu primer intervalo(E4):

=DISTR.NORM.N(E4;$F$1;$F$2;FALSO)

Aquí está la versión decodificada para ayudarle a ajustar en consecuencia:

=DISTR.NORM-N([el primer intervalo];[la media(referencia absoluta)];[la desviación estándar(referencia absoluta); FALSO)

Bloquea los valores de la media y la desviación estándar para poder ejecutar sin esfuerzo la fórmula para los intervalos restantes(E5:E153).

 

Ahora, haz doble clic en el tirador de relleno para copiar la fórmula en el resto de las celdas (F5:F153).

Paso #5: Crear un Gráfico de Dispersión Con Líneas Suavizadas.

Finalmente, ha llegado el momento de construir la curva de campana:

1. Seleccione cualquier valor en la tabla de ayuda que contiene los valores de los ejes x e y(E4:F153).
2. Vaya a la pestaña «Insertar».
3. Haga clic en el botón «Insertar gráfico de dispersión (X, Y) o de burbujas«.
4. Elija «Dispersión con líneas suavizadas»

Paso #6: Configurar la Tabla de Etiquetas.

Técnicamente, ya tiene su curva de campana. Pero sería difícil de leer ya que carece de cualquier dato que la describa.

Hagamos la distribución normal más informativa añadiendo las etiquetas que ilustran todos los valores de la desviación estándar por debajo y por encima de la media (también puedes utilizarlas para mostrar las puntuaciones z en su lugar).

Para ello, configuremos otra tabla de ayuda como la siguiente:

En primer lugar, copie el valor medio(F1) junto a la celda correspondiente de la columna Valor X(I5).

A continuación, calcule los valores de la desviación estándar por debajo de la media introduciendo esta sencilla fórmula en la celda I4

=I5-$F$2

En pocas palabras, la fórmula resta la suma de los valores de desviación estándar anteriores de la media. Ahora, arrastra el asa de relleno hacia arriba para copiar la fórmula en las dos celdas restantes(I2:I3).

Repite el mismo proceso para las desviaciones estándar por encima de la media utilizando la fórmula espejo:

=I5+$F$2

De la misma manera, ejecuta la fórmula para las otras dos celdas(I7:I8).

Por último, rellena los valores de la etiqueta del eje Y(J2:J8) con ceros, ya que quieres que los marcadores de datos se sitúen en el eje horizontal.

 

Paso #7: Inserte los Datos de la Etiqueta en el Gráfico.

Ahora, añada todos los datos que ha preparado. Haga clic con el botón derecho del ratón en el gráfico y elija «Seleccionar datos».»

En el cuadro de diálogo que aparece, seleccione «Agregar».»

Resalte los rangos de celdas respectivos de la tabla auxiliar –I2:I8 para los «valores de la serie X» y J2:J8 para los «valores de la serie Y»– y haga clic en «Aceptar»

 

Paso #8: Cambiar el Tipo de Gráfico de la Serie de Etiquetas.

Nuestro siguiente paso es cambiar el tipo de gráfico de la serie recién añadida para que los marcadores de datos aparezcan como puntos. Para ello, haga clic con el botón derecho del ratón en el gráfico y seleccione «Cambiar tipo de gráfico».»

A continuación, diseñe un gráfico combinado:

1. Vaya a la pestaña Combo.
2. Para la serie «Serie2 «, cambie el «Tipo de gráfico» a «Dispersión».»
   • Nota: Asegúrese de que la «Serie1» permanezca como «Dispersión con líneas suavizadas» También asegúrese de que la «Serie1» no esté en el Eje Secundario – la casilla de verificación junto al tipo de gráfico no debe estar marcada.
3. Haga clic en «Aceptar«

 

Paso #9: Modificar la Escala del Eje Horizontal.

Centre el gráfico en la curva de campana ajustando la escala del eje horizontal. Haga clic con el botón derecho del ratón en el eje horizontal y seleccione «Dar formato al eje…» en el menú.

Una vez que aparezca el panel de tareas, haga lo siguiente:

• Vaya a la pestaña de Opciones del Eje.
• Establezca el valor de Límites mínimos en «15«
• Establezca el valor de Límites Máximos en «125«

Puede ajustar el rango de la escala del eje como crea conveniente, pero como conoce los rangos de la desviación estándar, establezca los valores de los Límites un poco lejos de cada una de sus terceras desviaciones estándar para mostrar la «cola» de la curva.

 

Paso #10: Inserte y Posicione las Etiquetas de Datos Personalizadas.

Al pulir su gráfico, asegúrese de agregar las etiquetas de datos personalizadas. Primero, haga clic con el botón derecho del ratón en cualquier punto que represente la Serie «Serie2 » y seleccione «Añadir etiquetas de datos.»

A continuación, sustituya las etiquetas por defecto por las que ha configurado previamente y colóquelas encima de los marcadores de datos.

1. Haga clic con el botón derecho del ratón en cualquier etiqueta de datos de la Serie «Serie2 «.
2. Seleccione «Formatear etiquetas de datos».«
3. En el panel de tareas, cambie a la pestaña Opciones de etiqueta.
4. Marque la casilla «Valor X«.
5. Desmarque la casilla «Valor Y«.
6. En «Posición de la etiqueta»,elija «Arriba«

Además, ahora puede eliminar las líneas de la cuadrícula (haga clic con el botón derecho sobre ellas > Eliminar).

Paso #11: Recolorear los Marcadores de Datos (opcional).

Finalmente, vuelva a colorear los puntos para ayudarlos a encajar en su estilo de gráfico.

1. Haga clic con el botón derecho en cualquier etiqueta de datos de la serie «Serie2 «.
2. Haga clic en el botón «Rellenar«.
3. Elija el color de la paleta que aparece.

Además, elimine los bordes alrededor de los puntos:

1. Vuelva a hacer clic con el botón derecho en el mismo marcador de datos y seleccione «Contorno«
2. Elija «Sin contorno«

Paso #12: Añadir Líneas Verticales (opcional).

Como ajuste final, puede añadir líneas verticales al gráfico para ayudar a enfatizar los valores SD.

• Seleccione el trazado del gráfico (de esta manera, las líneas se insertarán directamente en el gráfico).
• Vaya a la pestaña «Insertar».
• Haz clic en el botón «Formas«.
• Elija «Línea».«

Mantenga presionada la tecla «SHIFT » mientras arrastra el ratón para dibujar líneas perfectamente verticales desde cada punto hasta donde cada línea se encuentra con la curva de campana.

Cambie el título del gráfico y su curva de campana mejorada estará lista para mostrar sus valiosos datos de distribución.

Y así es como se hace. Ahora puede elegir cualquier conjunto de datos y crear una curva de campana de distribución normal siguiendo estos sencillos pasos