Empecemos por citar a Wikipedia:
…la llamada distribución normal…[se utiliza] como modelo en multitud de variables (peso, altura, calificaciones...), en cuya distribución los valores más usuales se agrupan en torno a uno central y los valores extremos son escasos.
La representación gráfica de la distribución normal se ve así:
Lo que nos dice este gráfico es que en la distribución normal los miembros de la muestra se concentran alrededor del promedio y son más escasos a medida que nos vamos a los extremos. En la distribución normal cerca del 68% de los miembros de la muestra se ubican a en el área definida por un desvío estándar en cada sentido
En esta nota veremos cómo crear gráficos de distribuciones normales con las herramientas que nos provee Excel.
Para definir una distribución normal necesitamos una colección de datos de los cuales calcularemos el promedio y el desvío estándar. Por ejemplo, para crear este gráfico
hemos definido el desvío estándar en la celda B1 (1) y el promedio en la celda B2 (0), en el rango A5:A29 hemos puesto una serie de datos que va de -3 a +3, es decir de menos tres desvíos estándar a más tres desvíos estándar
Los valores en el rango B5:B29 están calculados con la fórmula
=DISTR.NORM(A5;Media;DesvEst;FALSO)
donde
También podemos representar la distribución normal con un gráfico de área
En resumen, para crear un gráfico de distribución normal necesitamos la serie que de datos que queremos analizar, obviamente, la función PROMEDIO, la función DESVEST para calcular el desvío estándar y la función DISTR.NORM para calcular la distribución normal para la media y desviación estándar de cada dato de la serie.
Para ampliar un poco más la explicación pongamos por ejemplo una serie de 500 datos.
Los datos los hemos puesto en el rango Datos (A2:A501); en E1 hemos calculado el promedio de los datos y en E2 el desvío estándar.
En el rango C5:C25 creamos una serie de grupos, es decir, intervalos de frecuencia de los datos. Para calcular cuántos datos caen en cada grupo usamos la función FRECUENCIA
={FRECUENCIA(datos;grupos)}
Esta es una función matricial y la introducimos pulsando Ctrl+Mayúsculas +Enter luego de haber seleccionado todo el rango.
Finalmente calculamos al distribución normal con
=DISTR.NORM(C5;$E$1;$E$2;FALSO)
en el rango E5:E25
Con estos datos podemos crear un histograma (también pueden consultar esta nota) y en el mismo gráfico agregar la distribución normal de los datos con un gráfico de línea (usando un eje secundario)
Este gráfico nos permite comparar el histograma creado a partir de los datos con la distribución normal teórica.
El archivo de este ejemplo se puede descargar aquí.
Technorati Tags: MS Excel
Muchas gracias, es excelente.
ResponderBorrarSr Dunkelman lo felicito por su blog.
ResponderBorrarTengo la siguiente inquietud, como capturar coordenas del grafico que esta como imagen (abaco escaneado). Extraer dichas coordenas para despues realizar otros calculos.
Saludos
Roberto N
Roberto
ResponderBorrarno puedes poner imágenes en un comentario. Puedes mandarme el archivo por mail con la consulta.
Excelente Blog, demasiado util. Felicidades señor Dunkelman.
ResponderBorrarSaludos,
Héctor
Gracias, pero...¿qué significa "demasiado útil"?
ResponderBorrarExcelente, muy bien explicado, a mis marcadores de Google!!!
ResponderBorrarGRacias por el material expuesto Sr.Dunkelman a sido de mucho valor para mi. Lo utilizare en clases.
ResponderBorrarMuchs gracias por las explicaciones, me fueron de mucha ayuda
ResponderBorrarDe donde se obtienen los grupos, como hago para originarlos?
ResponderBorrarTwngo todos mis datos, pero son positivos, son de mediciones, quiero saber como hacer en ese calo los grupos? muchas gracias..
ResponderBorrarKaty, está todo explicado en la nota. También se puede descargar el ejemplo.
ResponderBorrarNecesito hacer una grafica para una distribución continua, en base a intervalos de clase,,, sabes como hacerlo en excell?
ResponderBorrarRoberto
El último curso de estadística lo tome en el año 1990. Sería muy útil se me dieras una descripción de las series de datos que necesitas representar.
ResponderBorrarExcelente información. Ahora, una pregunta: si la distribución de los datos responde a una Normal Truncada (por ejemplo, truncada inferiormente en 0), cómo debería proceder?
ResponderBorrarAxel,
ResponderBorrarcomo le respondo al lector Roberto, han pasado muchos años desde la última vez que tomé un curso de estadística. No tengo una respuesta clara para tí. Te sugiero que investigues en alguno de los muchos foros sobre Excel que hay en la Web.
Muchas gracias|
ResponderBorrarhola... felicitaciones por tan excelente explicación. la duda que tengo es el cómo se leen los resultados.
ResponderBorrarhola.
ResponderBorrartengo una campana que no se como leer. me puedes ayudar o dónde puedo buscar información?
gracias
Un buen libro de estadística o alguno de los muchos recursos que hay en Internet, como Wikipedia.
ResponderBorrarbuenas.... si sobre la grafica yo quiero tambien representar con lineas verticales los requisitos de diseño la media todo eso como hago para incorporar a la grafica esas rectas verticales
ResponderBorrar¿Qué la ha pasado a los signos de puntuación? Me resulta difícil entender tu consulta. Por favor, trata de explicarlo nuevamente.
ResponderBorrarMuchas gracias por la nota solo me queda la duda de como se calcula la columna de grupos cual seria el valor ininial y final y que rangos debo tomar
ResponderBorrarFijate en las técnicas que muestro en esta nota.
ResponderBorrarEstimado.... entendí la mayoria de tu explicacion, salvo como obtener la distribucion normal, al menos para el valor -400 a mi no me da 0.
ResponderBorrarde todas maneras muy buena tu iniciativa y espero algua recomendacion.
Saludos
Alvaro,
ResponderBorrarte sugiero descargar el archivo del ejemplo y analizarlo o aplicarlo a tus datos
Estimado....si baje el archivo, el problema que tenia es que a ti te da la desviacion 0 para el valor -400 por que le restringiste los decimales.
ResponderBorrarYo trabaje con mas decimales y me dio un valor muy pequeño, pero de todas maneras esta correcto.
Gracias por responder y te comento que voy a estar por este blog un buen tiempo, ya que estoy trabajando de cabeza en excel.
Saludos
Hola que tal. Probablemente la pregunta que voy a hacer sea un poco repetitiva, yo estoy trabajando con datos positivos, traté de guiarme con la nota y con el ejemplo y todavía no lo tengo muy claro. Yo misma establecí los rangos en mi mcaso ya que son calificaciones y las mismas oscilan entre 50 y 100, pero igual creo que la curva de la distribución normal no está correcta ya que las barras de frecuencia me salen muy pequeñas y la curva muy arriba lo que me deja un área muy grande. Por favor, agradecería cualquier recomendación.
ResponderBorrarTendrías que mandarme el archivo para que pueda hacerme una idea más cabal del problema.
ResponderBorrarFelicidades por esta explicacion, excelente
ResponderBorrarMuy buenas noches.
ResponderBorrarEs muy interesante su blog, y buscaba una plantilla de excel para poder modificar y establecer la comparación entre las calificaciones de los alumnos con la curva normal, pero una vez introducidos los datos (son mas de 500) la curva normal no aparece dibujada, ya que todas las notas son datos positivos. Agradecería me indicara como poder hacer para que el valor inicial de la curva fuera el 0 y el valor final el 10, ya que lo he establecido en intervalos de 0,5, pero aún así sigue sin aparecer, ya que la matriz no puede ser modificada sin borrar todo el formato.
Me ha sido muy útil toda su labor, espero tener pronto noticias suyas.
Por cierto mi correo es granadinofelix@hotmail.com.
Saludos cordiales.
No hay ninguna necesidad que los datos vayan de negativo a positivo para representar la curva normal. Puedes mandarme el archivo por mail para que le de un vistazo.
ResponderBorrarGenial el blog, la explicación y todo, Gracias
ResponderBorrarcomo puedo hacer solo la grafica???
ResponderBorrarsin datos nii nada??
Si no hay datos no se puede crear un gráfico. ¿Qué estarías representando? Si necesitás una imagen podés copiar alguna de las que aparecen en la nota o en cualquier otro sitio.
ResponderBorrartengo una serie de datos como lo son diferencias de inventario, y quiere saber que numero de referencias se me ajustan al 95% de los datos, y en excel tengo dos columnas una con referencia y otra con la diferencia de unidades, como hago para montarla
ResponderBorrarmuchas gracias
Al igual que Katy no capto como obtuviste los grupos
ResponderBorrarCon la función FRECUENCIA tal como está explicado en la nota. Además hay un enlace a la nota que explica la función FRECUENCIA en detalle.
ResponderBorrarEstos comentarios parecen una conversaci,on, ¿no creen?. :D
ResponderBorrarhola, necesito saber como poner en esa misma grafica otra linea que indique la media y otras dos que indiquen la desviacion estandar
ResponderBorrarTodas tus explicaciones sobre excel son buenísimas, yo quisiera saber si tienes algún ejemplo sobre normales pero con datos relacionados al tráfico de personas por hora?
ResponderBorrarNo, no tengo ningún ejemplo específico.
ResponderBorrarPara agregar líneas que indiquen la media y la desviación estandar se puede usar la técnica que muestro en esta nota. El único detalle es que las líneas tendrán que se verticales por lo que tendrás que definir tres series de valores.
ResponderBorrarQue verraquera , me salvaste de una ...mil gracias! Evelyn
ResponderBorrarMuchisimas gracias, su blog es fabuloso.
ResponderBorrarLo felicito
SOfia
Buenos días,
ResponderBorrarMuy interesante su página. Lo que no llego a entender es porqué selecciona la función DistNorm con el ejemplo de 500 datos cuando luego esos resultados no los utliza posteriormente, ya que en el gráfico final incluye la curva de la distribución normal (construida con la función DistNorm) y en un eje secundario la distribución de frecuencias para graficar la serie de 500 datos. Me gustaría saber la finalidad de emplear la función DistNorm con la serie de 500 datos.
Gracias y un saludo.
La muestra que estamos analizando tiene 500 datos (el rango en la columna A) y los usamos en la fórmula FRECUENCIA en el rango D5:D25. El rango esta contenido en nombre "datos". Este rango contiene los valores de la serie que ves como barras del histograma en el gráfico.
ResponderBorrarBuenos días,
ResponderBorrarSu respuesta es muy clara, pero no me estaba refieriendo a ello exactamente. Sino a que en el ejemplo utiliza la función DISTR.NORM...FALSO sobre GRUPOS y, posteriormente, realiza el grafico con la curva normal y la distribución de frecuencias de la serie. Estaría muy interesado en saber porqué se utiliza la anterior función sobre GRUPOS ya que en el ejemplo no parece que vayan a emplearse sus resultados.
Muchísimas gracias por su tiempo y enhorabuena por compartir sus conocimientos.
Te sugiero que descargues el ejemplo, si no lo has hecho. Allí podrás ver donde se emplean cada uno de los elementos.
ResponderBorrarEn el ejemplo de Excel no hay duda alguna. Gracias y un saludo.
ResponderBorrarFalta pulsar F2 antes de Crt+Mayusculas+Enter para introducir la función frecuencia. Sino no sale
ResponderBorrarEstimada Paciente,
ResponderBorrarF2 es necesario sólo si estamos editando el contenido de la celda. Si introducimos la fórmula desde el principio pulsando Ctrl+Mayus+Enter no hace falta volver a editar la celda.
Muchas gracias por su informacion, me fue muy util
ResponderBorrarGracias por la informacion, pero necesito hacer una curva de gauss con unas calificaciones de desempeño. si alguien me puede ayudar se lo agradeceria mi correo es eacancinoAgmail.com
ResponderBorrarEmanuel,
ResponderBorrarte sugiero que descargues el ejemplo y lo adaptes a tus necesidades.
como agrega un eje secundario para visualizar en a misma gráfica la campana de gauss?
ResponderBorrarEl eje secundario se agrega seleccionando la serie deseada y marcando la opción "eje secundario" en el menú de formato de series.
ResponderBorrarNo conocía este blog, debo decir que me parece increíble y utilísimo, sobretodo para alguien tan verde en estos temas como yo.
ResponderBorrarEscribo en esta entrada porque es justo lo que andaba buscando. Tras cientos de búsquedas en google, con la cabeza como un bombo intentado descifrar el lenguaje empleado en todas ellas, me encuentro con este blog, explicaciones claras, y además con un fichero de ejemplo disponible para descargar.
Sencillamente impresionante, muchísimas gracias, un saludo desde España.
Hola doctor Jorge, estoy tratando de sacar esta gràfica sobre multiples notas de una facultad; las notas asumo que son los datos; sin embargo, la frecuencia no me da por ningun lado. Los grupos de datos son inexactos, podría ser por eso??
ResponderBorrarMIl gracias por su valiosa ayuda. Cristina
Cristina,
ResponderBorrarte suigiero que me mandes un ejemplo de tu archivo con una explicación por mail privado (fijate en el enalce Ayuda).
Que tal, Jorge
ResponderBorrarestoy tratando de hacer un ejercicio similar al de tu ejemplo pero con inventarios, como podría empezar?
gracias!
¿Podrías describir lo que quieres hacer con los inventarios?
ResponderBorrarHola Jorge
ResponderBorrarEstoy tratando de hacer un flujo de caja donde tengo montos de dinero que debo distribuir en el tiempo y que se debe distribuir en forma normal (Gauss). Los datos que tengo son: el monto y el número de meses. Entonces, lo que quiero obtener es, el monto parcial para cada mes con una distribución normal. Es decir, el mes 1 y el mes final tendrán montos bajos y el mes del medio tendrá el mayor valor. La suma total debe los montos parciales debe ser igual al monto total.
Gracias. Andrea
Hola Andrea
ResponderBorrarla explicación es un poco extensa para ponerla en el marco de un comentario. Puedo enviarte un ejemplo si me mandas tu dirección de mail (la mía figura en el enlace Ayuda, en la parte superior de la plantilla).
Quizás me equivoque, pero esta frase tiene un pequeño error:
ResponderBorrar"donde Media es el nombre de la celda B1 y Media el de la celda B2"
No debería ser:
"donde DesvEst es el nombre de la celda B1 y Media el de la celda B2
Saludos
Así es. Hace más de tres años que públiqué esta nota y hasta ahora nadie había visto el error. Gracias. Será corregido.
ResponderBorrarcómo interpretar mis datos, cuando no se comportan de manera normal, debo buscar una nueva distribución???
ResponderBorrarPosiblemente, pero te sugiero que consultes con alguien que entienda más que yo sobre estadística.
ResponderBorrarHola amigo Dunkelman, ayúdeme por favor con un problema que tengo.
ResponderBorrarHe descargado su archivo para modificar los datos y trazar la curva con otros datos, modificando la desviación estándar, pero la media que yo necesito es un número diferente a "0" como en el ejercicio, y al modificar la media todos los datos cambian a "0000000" y la curva desaparece.
La media para mi ejercicio es 10,08 y la desviación estándar 0,12.
Gracias
Tendría que ver tu archivo.
ResponderBorrarGracias men, me ahorraste trabajo...;)
ResponderBorrarMi estimado, un millon de gracias, me permitió hacer una presentacin rapida y efectiva de la capacidad de proceso de unos equipos donde trabajo. Chapeau.
ResponderBorrarGracias, muy util las instrucciones
ResponderBorrarHola, me podrian sacar de la duda........en que casos se utiliza el falso o verdadero al usarlo en la desviacion??
ResponderBorrarSi te refieres al argumento ACUM dn la función DISTR.NORM, te sugiero que veas la ayuda en línea de la función (Acum: Obligatorio. Un valor lógico que determina la forma de la función. Si el argumento acumulado es VERDADERO, la función DISTR.NORM devuelve la función de distribución acumulada; si es FALSO, devuelve la función de masa de probabilidad.)
ResponderBorrarEstimado, me podría ayudar con información referente a graficar una línea recta horizontal que representa el valor promedio de una serie de datos, pues no se como hacerlo
ResponderBorrarHola,
ResponderBorrarhay varias formas de hacerlo. Te sugiero que veas la técnica que muestro en esta nota.