Gráficos de distribución normal (Gauss) con Excel.

viernes, enero 23, 2009

Hace unos días me preguntaba un lector cómo se puede crear con Excel un gráfico tipo campana de Gauss para representar un informe de desviaciones estándar.
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í:

grafico distribucion normal Gauss

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

grafico distribucion normal Gauss

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

grafico distribucion normal Gauss

Los valores en el rango B5:B29 están calculados con la fórmula

=DISTR.NORM(A5;Media;DesvEst;FALSO)

donde Media  DesvEst es el nombre de la celda B1 y Media el de la celda B2. Los valores del rango A5:A29 son el resultado de tomar 3 desvíos estándar de “izquierda a derecha” (-3 x DesvEst = -3).
También podemos representar la distribución normal con un gráfico de área

grafico distribucion normal Gauss

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.

grafico distribucion normal Gauss

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)

grafico distribucion normal Gauss


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:

74 comments:

bonhamled 24 enero, 2009 07:29  

Muchas gracias, es excelente.

Anónimo,  27 enero, 2009 19:38  

Sr Dunkelman lo felicito por su blog.
Tengo 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

Jorge L. Dunkelman 27 enero, 2009 21:22  

Roberto

no puedes poner imágenes en un comentario. Puedes mandarme el archivo por mail con la consulta.

Anónimo,  20 febrero, 2009 01:24  

Excelente Blog, demasiado util. Felicidades señor Dunkelman.
Saludos,
Héctor

Jorge L. Dunkelman 22 febrero, 2009 19:28  

Gracias, pero...¿qué significa "demasiado útil"?

Miguel Monroy 10 setiembre, 2009 05:48  

Excelente, muy bien explicado, a mis marcadores de Google!!!

Anónimo,  23 setiembre, 2009 21:51  

GRacias por el material expuesto Sr.Dunkelman a sido de mucho valor para mi. Lo utilizare en clases.

Anónimo,  07 octubre, 2009 18:20  

Muchs gracias por las explicaciones, me fueron de mucha ayuda

katy,  15 octubre, 2009 23:47  

De donde se obtienen los grupos, como hago para originarlos?

Katy,  15 octubre, 2009 23:52  

Twngo todos mis datos, pero son positivos, son de mediciones, quiero saber como hacer en ese calo los grupos? muchas gracias..

Jorge L. Dunkelman 16 octubre, 2009 07:39  

Katy, está todo explicado en la nota. También se puede descargar el ejemplo.

Anónimo,  23 octubre, 2009 18:30  

Necesito hacer una grafica para una distribución continua, en base a intervalos de clase,,, sabes como hacerlo en excell?
Roberto

Jorge L. Dunkelman 24 octubre, 2009 19:37  

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.

Axel,  28 diciembre, 2009 21:46  

Excelente 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?

Jorge L. Dunkelman 29 diciembre, 2009 07:38  

Axel,
como 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.

Vicente,  08 enero, 2010 19:44  

Muchas gracias|

Anónimo,  02 febrero, 2010 00:50  

hola... felicitaciones por tan excelente explicación. la duda que tengo es el cómo se leen los resultados.

Anónimo,  02 febrero, 2010 01:06  

hola.
tengo una campana que no se como leer. me puedes ayudar o dónde puedo buscar información?
gracias

Jorge L. Dunkelman 02 febrero, 2010 02:22  

Un buen libro de estadística o alguno de los muchos recursos que hay en Internet, como Wikipedia.

Anónimo,  11 febrero, 2010 00:38  

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

Jorge L. Dunkelman 11 febrero, 2010 07:15  

¿Qué la ha pasado a los signos de puntuación? Me resulta difícil entender tu consulta. Por favor, trata de explicarlo nuevamente.

Anónimo,  05 marzo, 2010 21:08  

Muchas 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

Alvaro,  13 marzo, 2010 20:49  

Estimado.... entendí la mayoria de tu explicacion, salvo como obtener la distribucion normal, al menos para el valor -400 a mi no me da 0.

de todas maneras muy buena tu iniciativa y espero algua recomendacion.
Saludos

Jorge L. Dunkelman 13 marzo, 2010 21:06  

Alvaro,
te sugiero descargar el archivo del ejemplo y analizarlo o aplicarlo a tus datos

Alvaro,  13 marzo, 2010 22:43  

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

Anónimo,  05 abril, 2010 06:18  

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.

Jorge L. Dunkelman 05 abril, 2010 08:37  

Tendrías que mandarme el archivo para que pueda hacerme una idea más cabal del problema.

Anónimo,  29 abril, 2010 02:01  

Felicidades por esta explicacion, excelente

Anónimo,  28 mayo, 2010 22:37  

Muy buenas noches.
Es 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.

Jorge L. Dunkelman 29 mayo, 2010 09:20  

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.

CAMILO 01 junio, 2010 23:55  

Genial el blog, la explicación y todo, Gracias

Anónimo,  03 junio, 2010 23:26  

como puedo hacer solo la grafica???
sin datos nii nada??

Jorge L. Dunkelman 04 junio, 2010 07:22  

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.

Anónimo,  09 junio, 2010 00:08  

tengo 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

muchas gracias

Anónimo,  19 junio, 2010 05:32  

Al igual que Katy no capto como obtuviste los grupos

Jorge L. Dunkelman 19 junio, 2010 18:30  

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

Anónimo,  02 octubre, 2010 00:48  

Estos comentarios parecen una conversaci,on, ¿no creen?. :D

Anónimo,  08 noviembre, 2010 07:45  

hola, necesito saber como poner en esa misma grafica otra linea que indique la media y otras dos que indiquen la desviacion estandar

Anónimo,  11 noviembre, 2010 17:39  

Todas 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?

Jorge L. Dunkelman 11 noviembre, 2010 18:45  

No, no tengo ningún ejemplo específico.

Jorge L. Dunkelman 12 noviembre, 2010 17:48  

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

evelynnn_u@yahoo.es,  16 diciembre, 2010 20:21  

Que verraquera , me salvaste de una ...mil gracias! Evelyn

Anónimo,  21 enero, 2011 17:03  

Muchisimas gracias, su blog es fabuloso.
Lo felicito

SOfia

Anónimo,  03 febrero, 2011 09:40  

Buenos días,

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

Jorge L. Dunkelman 04 febrero, 2011 18:45  

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.

Anónimo,  05 febrero, 2011 10:45  

Buenos días,
Su 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.

Jorge L. Dunkelman 07 febrero, 2011 18:56  

Te sugiero que descargues el ejemplo, si no lo has hecho. Allí podrás ver donde se emplean cada uno de los elementos.

Anónimo,  09 febrero, 2011 14:08  

En el ejemplo de Excel no hay duda alguna. Gracias y un saludo.

La paciente de la celda 3 10 marzo, 2011 10:20  

Falta pulsar F2 antes de Crt+Mayusculas+Enter para introducir la función frecuencia. Sino no sale

Jorge L. Dunkelman 10 marzo, 2011 11:53  

Estimada Paciente,
F2 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.

Anónimo,  18 marzo, 2011 04:31  

Muchas gracias por su informacion, me fue muy util

Emanuel Cancino 30 marzo, 2011 22:30  

Gracias 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

Jorge L. Dunkelman 30 marzo, 2011 23:10  

Emanuel,

te sugiero que descargues el ejemplo y lo adaptes a tus necesidades.

Anónimo,  23 abril, 2011 05:02  

como agrega un eje secundario para visualizar en a misma gráfica la campana de gauss?

Jorge L. Dunkelman 23 abril, 2011 07:26  

El eje secundario se agrega seleccionando la serie deseada y marcando la opción "eje secundario" en el menú de formato de series.

salakazam 18 mayo, 2011 12:52  

No conocía este blog, debo decir que me parece increíble y utilísimo, sobretodo para alguien tan verde en estos temas como yo.
Escribo 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.

Anónimo,  21 mayo, 2011 08:55  

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??

MIl gracias por su valiosa ayuda. Cristina

Jorge L. Dunkelman 21 mayo, 2011 10:09  

Cristina,
te suigiero que me mandes un ejemplo de tu archivo con una explicación por mail privado (fijate en el enalce Ayuda).

Anónimo,  18 enero, 2012 08:19  

Que tal, Jorge
estoy tratando de hacer un ejercicio similar al de tu ejemplo pero con inventarios, como podría empezar?
gracias!

Jorge L. Dunkelman 18 enero, 2012 21:52  

¿Podrías describir lo que quieres hacer con los inventarios?

Anónimo,  23 febrero, 2012 23:45  

Hola Jorge
Estoy 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

Jorge L. Dunkelman 25 febrero, 2012 17:53  

Hola Andrea

la 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).

Anónimo,  15 marzo, 2012 13:21  

Quizás me equivoque, pero esta frase tiene un pequeño error:

"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

Jorge L. Dunkelman 15 marzo, 2012 16:29  

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.

Anónimo,  07 junio, 2012 00:40  

cómo interpretar mis datos, cuando no se comportan de manera normal, debo buscar una nueva distribución???

Jorge L. Dunkelman 08 junio, 2012 18:29  

Posiblemente, pero te sugiero que consultes con alguien que entienda más que yo sobre estadística.

Anónimo,  17 junio, 2012 05:47  

Hola amigo Dunkelman, ayúdeme por favor con un problema que tengo.
He 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

Jorge L. Dunkelman 17 junio, 2012 06:53  

Tendría que ver tu archivo.

Anónimo,  12 setiembre, 2012 06:54  

Gracias men, me ahorraste trabajo...;)

Anónimo,  30 marzo, 2013 04:02  

Mi estimado, un millon de gracias, me permitió hacer una presentacin rapida y efectiva de la capacidad de proceso de unos equipos donde trabajo. Chapeau.

Anónimo,  12 mayo, 2013 19:17  

Gracias, muy util las instrucciones

Unknown 11 abril, 2014 19:35  

Hola, me podrian sacar de la duda........en que casos se utiliza el falso o verdadero al usarlo en la desviacion??

Jorge Dunkelman 12 abril, 2014 18:30  

Si 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.)

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP