Cómo crear una curva de campana en Excel

Una curva de campana, también conocida como distribución normal, es el tipo de distribución más común para una variable. Se acepta como una distribución que ocurre naturalmente en muchas situaciones. En una curva de campana, el punto más alto del arco define la media, que es el mayor número de ocurrencias de un elemento. La probabilidad de ocurrencias disminuye hacia ambos lados de la curva. Puede usar una curva de campana para comparar el rendimiento excluyendo los extremos, o definir sus expectativas por la posibilidad de que un resultado se encuentre dentro de un rango a la izquierda o a la derecha del centro. En esta guía, le mostraremos cómo crear una curva de campana en Excel con un escenario de caso de uso del mundo real como ejemplo.

Descargar Libro de trabajo

Lo básico

Todo lo que necesita es la media (promedio) y los valores de desviación estándar de su conjunto de datos. Ambas métricas se pueden calcular en Excel utilizando las fórmulas a continuación.

Para calcular la media (promedio) =PROMEDIO(datos)
Para calcular la desviación estándar =DESVEST.P (datos)

Teniendo en cuenta estos dos valores, los valores normalmente distribuidos siguen estas reglas:

  1. El área total bajo la curva es igual a 1 (100%)
  2. El centro de la curva de campana es la media del punto de datos
  3. (1-σ) Aproximadamente el 68,2% del área bajo la curva cae dentro de una desviación estándar (Media ± Desviación estándar)
  4. (2-σ) Alrededor de 95.el 5% del área bajo la curva se encuentra dentro de dos desviaciones estándar (Media ± 2 * Desviación estándar)
  5. (3-σ) Aproximadamente el 99,7% del área bajo la curva cae dentro de tres desviaciones estándar (Media ± 3 * Desviación estándar)

Imagen de University of Virginia

Crear una curva de campana en Excel

Tomemos un ejemplo común y digamos que estamos analizando los resultados de un examen para una clase de estudiantes. Usaremos una curva de campana para medir los resultados de los exámenes para una mejor comparación.

Comenzamos calculando las métricas para generar datos distribuidos normales que generarán nuestra curva. Tenemos que calcular:

  • Media (media) de los valores.
  • desviación Estándar de los valores.
  • 3-límites de desviación estándar para la media antes y después.
  • Valor de intervalo para puntos de datos distribuidos normalmente. Esto también requiere una determinación de los puntos de intervalo. Puede seleccionar cualquier número, pero tenga esto en cuenta, más intervalos significan más precisión.
Una nota rápida antes de sumergirse en las fórmulas. Utilizamos rangos con nombre en lugar de referencias de celda para facilitar la lectura de las fórmulas. Puede encontrar más detalles sobre los rangos con nombre aquí: Rangos con nombre de Excel

Métricas

Comience calculando la media y la desviación estándar de los datos. Puede usar el PROMEDIO y el STDEV.Funciones P para calcular los valores de media y desviación estándar, respectivamente.

 curva de campana en excel

El siguiente paso es calcular los 3 valores de desviación estándar para establecer los valores mínimo y máximo para el 99,7% de los datos.

Los 3 valores de desviación estándar pueden exceder el conjunto de datos real. Este es un comportamiento normal que puede ocurrir con frecuencia con conjuntos de datos más pequeños.

la curva de la campana en excel

Mínimo = 83.23 – 3 * 5.54 = 66.61

Máximo = 83.23 – 3 * 5.54 = 99.86

Después de establecer los valores mínimos y máximos para nuestra curva, necesitamos generar los intervalos. Los valores de intervalo serán la base para los valores normalmente distribuidos. Para calcular los intervalos, todo lo que necesita hacer es dividir el área entre los valores mínimo y máximo por recuento de intervalos. En este ejemplo, establecemos esto en 20, pero puede usar un número mayor para aumentar el número de puntos de datos.

Valor de intervalo= (99.85 – 66.60) / 20 = 1.66

Una vez calculado el valor del intervalo, puede generar los puntos de datos. Para hacer esto, ingrese el valor mínimo en una celda. Luego, justo debajo del valor mínimo, ingrese la fórmula para agregar el valor de intervalo al mínimo. Aquí, utilizamos referencias de celda (como J4) que ayudan a rellenar los puntos de datos fácilmente hasta el valor máximo.

El siguiente paso es calcular los valores distribuidos normalmente a partir de los puntos de datos generados. Puede utilizar la NORMA de Excel.Función DIST para generar estos valores.

Utilice los puntos de datos poblados como primer argumento de la función. Los valores de media y desviación estándar son los siguientes argumentos. Termine la fórmula con un valor booleano FALSO para usar el tipo no acumulativo de esta función.

Gráfico

ya casi Hemos terminado! Seleccione los puntos de datos y los valores de distribución normales y, a continuación, inserte un gráfico de dispersión X-Y. Utilice la versión Dispersos con líneas suaves para crear una curva de campana en Excel.

El gráfico puede parecer un poco fuera de primera. Veamos cómo puedes hacer que se vea mejor.

Para cambiar el título del gráfico, haga doble clic en el título y actualice el nombre.

A continuación, haga doble clic en el eje X y defina valores mínimos y máximos desde el panel de opciones de eje para eliminar el espacio en blanco en ambos lados. Esto le dará a su gráfico una mejor forma de campana. Establecemos valores que están un poco fuera de nuestro conjunto de datos. Por ejemplo, 66 – 100 para valores 66.30-99.86.

Puede mejorar aún más su gráfico agregando los valores de desviación estándar.

Para obtener más información sobre los gráficos de dispersión, consulte Gráfico de dispersión en Excel

curva de campana en excel

Leave a Reply

Deja una respuesta

Tu dirección de correo electrónico no será publicada.