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 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 forma de campana curva, 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 paso- 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:

• La media (también conocida como la 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 mediciones. Esto define la extensión de sus datos en la distribución normal o, en términos sencillos, 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 más baja es la DE, más alta es la curva y menos sus datos se distribuirán 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 de tres SD.

Ahora que Conozca los conceptos básicos, pasemos de la teoría a la práctica.

Introducción

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: Encuentre la media.

Normalmente, se le da la media y SD desde el principio, pero si ese no es el caso, puede calcular estos valores fácilmente en unos pocos pasos simples. Primero abordemos 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 las puntuaciones del examen en el conjunto de datos:

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 ROUND de la siguiente manera:

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

Uno abajo, 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:

Nuevamente, la fórmula toma 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 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 una puntuación de examen en particular, mientras que los valores del eje y lo dirán Calcula 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úrate de elegir un rango que incorpore las tres desviaciones estándar.

Comencemos a contar desde uno (ya que no hay forma de que un estudiante pueda obtener una calificación negativa en el examen) y avancemos hasta 150 (no importa si es 150 o 1500) para configurar otra tabla 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. Navegue a la pestaña Inicio.
3. En el grupo Edición, elija «Completar».
4. En «Serie en, ”Seleccione» Columna «.
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 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 a la derecha (F4) de su primer intervalo (E4):

Aquí está la versión decodificada para ayudarlo 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 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 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 «Dispersión con líneas suaves».

Paso # 6: Configure la tabla de etiquetas.

Técnicamente, usted tiene su curva de campana. Pero sería difícil de leer ya que carece de datos que la 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 tabla de ayuda de la siguiente manera:

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

Luego, calcule los valores de la 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 usando la fórmula espejo:

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

Finalmente, llene los valores de la etiqueta del eje y (J2: J8) con ceros como desee marcadores de datos colocados 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 la gráfica del 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 la serie X «y J2 : J8 para «Valores de la 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 del gráfico y seleccione «Cambiar Tipo de gráfico ”.

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

1. Navegue hasta el Pestaña Combo.
2. Para la serie «Serie2», cambie «Tipo de gráfico» a «Dispersión».
   • Nota: Asegúrese de que «Serie1» permanezca como «Dispersión con líneas suaves». A veces, Excel lo cambiará cuando haga un Combo. También asegúrese de que «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: 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 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 para agregar las etiquetas de datos personalizadas. Primero, haga clic con el botón derecho en cualquier punto que represente la serie «Serie2» y seleccione «Agregar etiquetas de datos».

A continuación, reemplace las etiquetas predeterminadas con 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 «Serie2».
2. Seleccione «Formato 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 cuadrícula (haga clic derecho en ellas > Eliminar).

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

Por último, cambie el color de los puntos para que se ajusten al estilo de su 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 su color de la paleta que aparece.

Además, r Quite los bordes alrededor de los puntos:

1. Haga clic con el botón derecho en el mismo marcador de datos nuevamente y seleccione «Esquema».
2. Elija «Sin esquema».

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

Como ajuste final, puede agregue 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).
• Ir a la pestaña Insertar.
• Haga clic en el botón «Formas».
• Elija «Línea».

Mantenga presionada la tecla «MAYÚS» mientras arrastrando 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 está lista, mostrando sus valiosos datos de distribución.

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