Cómo crear una curva de campana de 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 2022.

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 determinado.

El eje y representa la probabilidad relativa de que ocurra un valor dado 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 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 para ofrecer, tendrá que trazarlo 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, necesita saber dos cosas:

• El significado (también conocida como medida 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 (SD) de las medidas. Esto define la extensión de sus datos en la distribución normal, o en un lenguaje sencillo, qué tan ancha debe ser la curva. Por ejemplo, en la curva de campana que se muestra arriba, una desviación estándar de la media representa el rango entre los puntajes del examen de 53 y 85.

Cuanto menor sea la SD, más alta será la curva y menos se distribuirán sus 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 de la media, 95% dentro de dos SD y 99.7% dentro tres SD.

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

Empezando

Con fines ilustrativos, supongamos que tiene los puntajes de las pruebas de 200 estudiantes y desea calificarlos "en una curva", lo que significa que las calificaciones de los estudiantes se basarán en su desempeño relativo al resto de la clase:

Paso # 1: Encuentra la media.

Por lo general, se le proporcionan los valores de la media y la DE desde el principio, pero si ese no es el caso, puede calcular estos valores fácilmente 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 usando 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 los puntajes del examen en el conjunto de datos:

1	= PROMEDIO (B2: B201)

Una nota rápida: la mayoría de las veces, es posible que deba redondear el resultado de la fórmula. Para hacer eso, simplemente envuélvalo en la función REDONDEAR de la siguiente manera:

1	= REDONDO (PROMEDIO (B2: B201), 0)

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

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

1	= DESVEST.P (B2: B201)

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

1	= REDONDEO (DESVEST.P (B2: B201), 0)

Paso # 3: Configure los valores del eje x para la curva.

Básicamente, el gráfico constituye una gran cantidad 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 dirá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 a contar en uno (ya que no hay forma de que un estudiante pueda obtener una calificación negativa en el examen) y vayamos hasta 150 (realmente no importa si es 150 o 1500) para configurar otra mesa de ayuda.

1. Elija cualquier celda vacía debajo de los datos del gráfico (como E4) y tipo “1,” el valor que define el primer intervalo.
2. Navega al Hogar pestaña.
3. En el Edición grupo, elija "Llenar.”
4. Debajo "Serie en," Seleccione "Columna.”
5. Para "Valor de paso," escribe “1.” Este valor determina los incrementos que se sumarán automáticamente hasta que Excel alcance el último intervalo.
6. Para "Valor de parada," escribe "150,” el valor que representa el último intervalo y haga clic en "está bien.”

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 normal para cada valor del eje x.

Ahora, encuentre los valores de 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 usted, Excel tiene el caballo de batalla para hacer todos estos cálculos por usted: la función NORM.DIST.

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

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

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

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

Bloquea los valores de media y desviación estándar 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: Cree un diagrama 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 xey (E4: F153).
2. Ve a la Insertar pestaña.
3. Haga clic en el "Insertar gráfico de dispersión (X, Y) o de burbujas" botón.
4. Escoger "Dispersión 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 usarlos para mostrar las puntuaciones z).

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

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

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

1	= 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, 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 usando la fórmula espejo:

1	= I5 + $ F $ 2

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

Finalmente, complete los valores de la 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 del gráfico y seleccione "Seleccione Datos.”

En el cuadro de diálogo que aparece, seleccione "Agregar.”

Resalte los rangos de celdas respectivos de la tabla de ayuda-I2: I8 por "Valores de la serie X" y J2: J8 por "Valores de la serie Y "-y haga clic en "está bien.”

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 hacerlo, haga clic con el botón derecho en el gráfico del gráfico y seleccione "Cambiar el tipo de gráfico.”

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

1. Navega al Combo pestaña.
2. Para Serie "Serie2", cambio "Tipo de gráfico" para "Dispersión.”
   • Nota: asegúrese de que "Serie1"Permanece como"Dispersión con líneas suaves. " A veces, Excel lo cambiará cuando haga un Combo También asegúrese de "Serie1”No se coloca en el eje secundario; la casilla de verificación junto al tipo de gráfico no debe estar marcada.
3. Haga clic en "OK.”

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 "Eje de formato”Del menú.

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

• Ve a la Opciones de eje pestaña.
• Selecciona el Límites mínimos valor a "15.”
• Selecciona el Límites máximos valor a "125.”

Puede modificar 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 alejados 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 perfecciona su gráfico, asegúrese de agregar las etiquetas de datos personalizadas. Primero, haga clic derecho en cualquier punto que represente Serie "Serie2" 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 derecho en cualquier Serie "Serie2" etiqueta de datos.
2. Seleccione "Dar formato a las etiquetas de datos.”
3. En el panel de tareas, cambie al Opciones de etiqueta pestaña.
4. Comprobar el "Valor X" caja.
5. Desmarca la casilla "Valor Y" caja.
6. Debajo "Posición de la etiqueta," escoger "Encima.”

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

Paso # 11: Vuelva a colorear los marcadores de datos (opcional).

Finalmente, cambie el color de los puntos para ayudarlos a encajar en el estilo de su gráfico.

1. Haga clic derecho en cualquier Serie "Serie2" etiqueta de datos.
2. Haga clic en el "Llenar" botón.
3. Elija su 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 "Esquema.”
2. Escoger "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 del gráfico (de esa manera, las líneas se insertarán directamente en el gráfico).
• Ve a la Insertar pestaña.
• Haga clic en el "Formas" botón.
• Escoger "Línea.”

Mantenga presionado el "CAMBIO" mientras arrastra el mouse 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!