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 gaussiana) 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 dado.

El eje y representa la probabilidad relativa de que un valor dado ocurra en el conjunto de datos, mientras que el eje x traza los valores en el gráfico para crear una curva en forma de campana, de ahí el nombre.

El gráfico nos ayuda a analizar si un valor en particular es parte de la variación esperada o es estadísticamente significativo y, por lo tanto, debe examinarse más de cerca.

Dado que Excel no tiene ninguna solución incorporada que ofrecer, tendrá que trazarla usted mismo. Es por eso que desarrollamos el complemento Chart Creator, una herramienta que le permite crear gráficos avanzados de Excel con solo unos pocos clics.

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

Para trazar una curva gaussiana, debe saber dos cosas:

• La media (también conocida como medición estándar). Esto 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 mediciones. Esto define la extensión de sus datos en la distribución normal—o en inglés sencillo, qué tan ancha debe ser 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 de los exámenes de 53 a 85.

Cuanto más baja sea la SD, más alta será la curva y menos se extenderán los datos, y viceversa.

Vale la pena mencionar la regla 68-95-99.7 que se puede aplicar a cualquier curva de distribución normal, lo que significa que aproximadamente el 68% de sus datos se colocarán dentro de una SD lejos de la media, el 95% dentro de dos SD y el 99,7% dentro de tres SD.

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

Primeros pasos

Para fines ilustrativos, supongamos que tiene los puntajes de los exámenes de 200 estudiantes y desea calificarlos «en una curva», lo que significa que las calificaciones de los estudiantes se basarán en su rendimiento relativo al resto de la clase:

Paso # 1: Encuentra la media.

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

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

Escriba la siguiente fórmula en cualquier celda vacía (F1 en este ejemplo) junto a sus datos reales (columnas A y B) para calcular el promedio de las puntuaciones de los exámenes en el conjunto de datos:

Una nota rápida: más a menudo que no, usted puede necesitar para redondear la fórmula de salida. Para hacer eso, simplemente envuélvalo en la función REDONDA de la siguiente manera:

Paso # 2: Encuentre la desviación estándar.

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

Una vez más, la fórmula recoge todos los valores del rango de celdas especificado (B2:B201) y calcula su desviación estándar, pero no olvide redondear la salida también.

Paso # 3: Configure 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 una puntuación de 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, puede incluir tantos intervalos como desee; puede borrar sin esfuerzo los datos redundantes más adelante modificando la escala del eje horizontal. Solo asegúrese de elegir un rango que incorpore las tres desviaciones estándar.

Comencemos un conteo en uno (ya que no hay manera de que un estudiante pueda obtener una puntuación negativa en el examen) e ir hasta 150, realmente no importa si es 150 o 1500, para configurar otra mesa auxiliar.

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 de edición, seleccione » Rellenar.»
4. En la columna» Serie en, «seleccionar».»
5. Para «Valor de paso, «escriba» 1.»Este valor determina los incrementos que se sumarán automáticamente hasta que Excel alcance el último intervalo.
6. Para «Valor de parada», escriba» 150″, el valor que representa el último intervalo, y haga clic en «Aceptar».»

Milagrosamente, 149 celdas en 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 distribución normales para cada valor del eje x.

Ahora, encuentre los valores de distribución normales, la probabilidad de que un estudiante obtenga una determinada puntuación de examen representada por un valor particular del eje x, para cada uno de los intervalos. Afortunadamente para usted, Excel tiene el caballo de batalla para hacer todos estos cálculos por usted: la NORMA.Función DIST.

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

Aquí está el decodificado de la versión para ayudarle a ajustar en consecuencia:

Bloquea los valores de media y SD para que pueda ejecutar sin esfuerzo la fórmula para los intervalos restantes (E5:E153).

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

Paso # 5: Crea un gráfico de dispersión con líneas suaves.

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

1. Seleccione cualquier valor en la tabla auxiliar que contenga 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 dispersión (X, Y) o Gráfico de burbujas».
4. Elija » Dispersar con Líneas Suaves.»

Paso # 6: Configure la tabla de etiquetas.

Técnicamente, tienes tu curva de campana. Pero sería difícil de leer, ya que carece de datos que lo describan.

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

Para eso, configure otra tabla auxiliar de la siguiente manera:

Primero, copie el valor medio (F1) junto a la celda correspondiente en la columna Valor X (I5).

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

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

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

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

Por último, rellene los valores de etiqueta del eje y (J2:J8) con ceros como desee que los marcadores de datos se coloquen en el eje horizontal.

Paso # 7: Inserte los datos de la etiqueta en el gráfico.

Ahora, agregue todos los datos que ha preparado. Haga clic con el botón derecho 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 «Valores de serie X» y J2: J8 para «valores de serie Y»—y haga clic en «ACEPTAR.»

Paso # 8: Cambie 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 agregada para que los marcadores de datos aparezcan como puntos. Para ello, haga clic con el botón derecho 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 Series «Series2″, cambie» Tipo de gráfico «a» Dispersión.»
   • Nota: Asegúrese de que» Series1 «permanezca como» Dispersión con Líneas Suaves».»A veces, Excel lo cambiará cuando haga un Combo, también asegúrese de que «Series1» no se empuje al Eje Secundario; la casilla de verificación junto al tipo de gráfico no debe marcarse.
3. Haz clic en «Aceptar».»

Paso # 9: Modifique 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 en el eje horizontal y seleccione «Formato de eje» en el menú.

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

• Vaya a la pestaña Opciones de 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 escala del eje como mejor le parezca, pero como conoce los rangos de desviación estándar, establezca los valores de 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 coloque las etiquetas de datos personalizadas.

A medida que pule su gráfico, asegúrese de agregar las etiquetas de datos personalizadas. Primero, haga clic con el botón derecho en cualquier punto que represente la serie «Series2» y seleccione «Agregar etiquetas de datos».»

A continuación, reemplace las etiquetas predeterminadas por las que configuró anteriormente y colóquelas encima de los marcadores de datos.

1. Haga clic con el botón derecho en cualquier etiqueta de datos de la serie «Series2».
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, «elegir» Arriba.»

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

Paso # 11: Cambia el color de los marcadores de datos (opcional).

Finalmente, cambie el color de los puntos para que se ajusten a su estilo de gráfico.

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

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

1. Haga clic con el botón derecho en el mismo marcador de datos de nuevo y seleccione «Contorno».»
2. Elija » Sin contorno.»

Paso # 12: Agregue líneas verticales (opcional).

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

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

Mantenga pulsada la tecla» MAYÚS » 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, mostrando sus valiosos datos de distribución.

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