lunes, septiembre 01, 2008

Calcular frecuencias y crear histogramas con Excel

En una nota anterior hemos mostrado como crear un histograma usando el Data Analyisis del Analysis ToolPak.
En esta nota veremos como hacerlo manualmente usando la función FRECUENCIA. Además veremos una fórmula para crear grupos de distribución uniformes a partir de los datos de la muestra.
Y como dijo Jack el Destripador, vamos por partes.
Supongamos esta tabla de notas de los alumnos de un colegio imaginario.



Digamos ahora que el director del colegio nos ha pedido analizar la distribución de las notas, dividiéndolas en cinco grupos de igual amplitud.
Vemos que la nota mínima obtenida es 50 lo que calculamos fácilmente con la fórmula

=MIN(A2:D18)

Lo mismo para la nota máxima, con =MAX(A2:D18)

Como nuestro director quiere cinco grupos de notas, estos serán:

de 0 a 60
de 61 a 70
de 71 a 80
de 81 a 90
de 91 a 100

Para nuestro uso en Excel tomamos sólo el extremo superior de intervalo, es decir



Para crear la columna con las frecuencias de cada grupo usamos la función FRECUENCIA. Esta función es matricial, es decir, la introducimos en el rango de celdas apretando simultáneamente Ctrl+Mayúsculas+Enter



Esta función tiene dos argumentos: Datos, en nuestro caso la tabla con las notas y Grupos, los grupos de distribución. Nótese que si bien el grupo es un intervalo, por ejemplo 0 - 60, sólo usamos la cota superior.
Para calcular el peso relativo de cada grupo usamos la fórmula

=G2/SUMA(G$2:G$8)



Ahora podemos representar la distribución fácilmente en un gráfico, que hará las delicias del director del colegio



También podemos calcular la distribución de las notas para cada asignatura por separado y mostrarlas en un mismo gráfico, lo que permitiría hacer una comparación



En lugar de crear dos columnas para cada asignatura, podemos calcular directamente la distribución relativa de los grupos de notas usando esta formula matricial

=FRECUENCIA(B2:B18,$F$2:$F$6)/CONTAR(B2:B18)



Sencillamente dividimos el resultado de FRECUENCIA para cada grupo por el total de elementos (notas) de la asignatura.

¿Cómo haríamos para calcular el intervalo de cada grupo en función de los datos de la tabla?
Lo haremos, como ya sospechan, con una fórmula matricial. Nuestras variables son la nota mínima, la máxima y la cantidad de grupos requeridos. Nuestra fórmula deberá calcular la cata superior de cada grupo.
Siguiendo con nuestro ejemplo, la nota mínima es 50, la máxima 100 (de hecho 99, pero el sentido común nos dice que debemos tomar el cuenta la máxima posible) y queremos 5 grupos divididos uniformemente.
Para facilitar la lectura de la fórmula he definido el rango de la tabla (A2:D18) en un nombre: "notas". La fórmula matricial que nos permitirá calcular los grupos es

={MIN(notas)+FILA(INDIRECTO("1:5"))*(MAX(notas)-MIN(notas)+1)/5}



Un detalle importante: seleccionamos todo el rango F2:F6 antes de introducir la fórmula. Luego escribimos la fórmula y pulsamos Ctrl+Mayúsculas+Enter simultáneamente.

¿Cómo funciona esta fórmula?

La expresión MIN(notas) genera una matriz de cinco líneas, todas con el valor 50 (la menor nota de la tabla);

FILA(INDIRECTO("1:5") genera una matriz de cinco valores del 1 al 5, siendo 5 el número de grupos que queremos exponer

MAX(notas)-MIN(notas)+1 genera una matriz de cinco líneas todas con el valor 50 (99-50+1)

La fórmula genera, entonces, una matriz con los valores 60, 70, 80, 90, 100 como resultado de:

50 + (1*50/5)
50 + (2*50/5)
50 + (3*50/5)
50 + (4*50/5)
50 + (5*50/5)

Si queremos generar 10 grupos, por ejemplo, usamos como variable 10 en lugar de 5

={MIN(notas)+FILA(INDIRECTO("1:10"))*(MAX(notas)-MIN(notas)+1)/10}



Technorati Tags:

22 comentarios:

  1. Estimado Sr Dulkelman, yo se que el comentario que a continuacion le expongo no tiene nada que ver con el tema, pero no he encontrado una respuesta satisfactoria por ninguna parte no me queda mas que atreverme a dirigirme a usted, la cosa es aparentemente sencilla, cuando escribimos una fecha poniendole formato dd-mmmm-aaaa o sus variantes, siempre que se vea el mes con letra y no como numero, el mismo aparece en minusculas, yo quisiera saber como se puede lograr que aparesca en mayusculas y que aun asi se puedan realizar operaciones o filtros... y ya abusando de su confianza, existe algun metodo con el que se puede lograr que las celdas de excel al momento de capturar informacion las muestre en mayusculas solamente o en minusculas solamente segun se indique, esto independientemente de si se encuentra activo el boton de Bloq. mayusculas... Por cierto que ya estoy enterado de que existen formulas que arrojan como resultado texto en mayusculas o minusculas segun se indique (=MAYUSC() y =MINUSC()), pero al trabajar con tanta informacion la cosa se pone tediosa, lo ideal en mi caso seria que, cuando me llega la hoja, seleccionarla toda y con algun boton efectuar el cambio (se escucha tan facil), siendo una especie de formato.

    Agradezco de antemano la lectura de la presente y el apoyo que nos pueda prestar.

    ResponderBorrar
  2. Hola
    Excel no tiene un formato para mostrar los meses en la fechas en mayúsculas. Las soluciones posibles son usando las funciones MAYUSC o MINUSC, por ejemplo

    =MAYUSC(TEXTO(A1,"dd-mmmm-aaaa"))

    o usando una macro. El problema en ambos casos es que estamos convirtiendo la fecha, que es un número, en texto. En este caso no podrías realizar operaciones o filtros como lo harías con fechas.
    Por supuesto que también podemos encontrar soluciones para este problema.
    Te sugiero que te pongas en contacto conmigo por mail ya que este tema realmente no está relacionado con la nota.

    ResponderBorrar
  3. Quiza no sea el lugar adecuado para cultar esto, pero necesito ayuda. mil disculpas. Tengo esta serie INA00001 (8 CARACTERES, pero no todos los compos tienen los 8 caracteres, hay varios que solo tienen 7 (INA0001.) Como hago para agregarle un "0" despues de INA (TRES PRIMEROS CARACTERES) a los campos que no lo tienen?

    ResponderBorrar
  4. Así es, no es el lugar. Pero puedes mandarme tu consulta al mail que aparece arriba (a la izquierda).
    Jorge

    ResponderBorrar
  5. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  6. Hola Jorge:

    ¿Por qué usamos FILA(INDIRECTO("1:5")) en lugar de FILA(1:5) en una parte de la fórmula?

    Lo probé sin el INDIRECTO y creo que funciona bien, salvo que no me esté dando cuenta de algo. Todo con el ánimo de simplificar la fórmula, que está genial.

    Gracias,
    Leonardo

    ResponderBorrar
  7. Hola Jorge:

    ¿Por qué usamos FILA(INDIRECTO("1:5")) en lugar de FILA(1:5) en una parte de la fórmula?

    Lo probé sin el INDIRECTO y creo que funciona bien, salvo que no me esté dando cuenta de algo. Todo con el ánimo de simplificar la fórmula, que está genial.

    Gracias,
    Leonardo

    ResponderBorrar
  8. Hola Leonardo,
    tenés razón. No me acuerdo por qué usé INDIRECTO.
    Gracias por la observación.

    ResponderBorrar
  9. Hola Jorge,

    He intentado aplicar está formula para algo que creo muy sencillo pero no consigo que funcione. Obviamente debido a mi inexperiencia.
    Te comento y quizás me puedas ayudar, también estoy buscando en todo tu blog pero no lo vi o no lo reconocí, si lo has comentado ya, te agradecería que me lo enlazaras.

    De una columna de datos he obtenido el máximo y mínimo, y lo que quería era hacer una columna de 20 filas en la que se mostrara ese rango (max-min) en esas 20 filas (sería algo así como hacer automático un "rellenar serie"). De modo que si mi máximo es 20 y el mínimo es 10, mostrase la columna del siguiente modo:
    10,5
    11
    11,5
    12
    12,5
    .
    .
    .
    19,5
    20
    Y si los máximos/mínimos cambian se ajuste y actualice de modo automático.
    Apliqué la función matricial que hay en este post tuyo, en la siguiente manera: ={MÍN(mae_datos)+FILAS(INDIRECTO("1:20"))*(MÁX(mae_datos)-MÍN(mae_datos)+1)/20)}
    Pero o bien tengo un error, o suprimiendo el "+1" del final obtengo 20 veces el número del máximo multiplicado por 20(???). "mae_datos" es el nombre que apliqué en mi hoja a la columna de datos de donde obtener los mínimos y máximos.

    Lo dicho, agradecería cualquier ayuda, muchas gracias, un saludo desde España.

    ResponderBorrar
  10. Hola,
    la fórmula tendría que ser

    =MIN(mae_datos)+ROW(INDIRECT("1:20"))*(MAX(mae_datos)-MIN(mae_datos))/20

    seleccionando previamente un rango de 20 celdas e introducirla apretando simultáneamente Ctrl+Mayúsc.+Enter. Los corchetes aparecen automáticamente (fórmula matricial).

    ResponderBorrar
  11. Perdón, puse las fórmulas en inglés. Tiene que ser

    =MIN(mae_datos)+FILA(INDIRECTO("1:20"))*(MAX(mae_datos)-MIN(mae_datos))/20

    ResponderBorrar
  12. Muchísimas gracias Jorge, ya lo solventé, tanto paréntesis y corchete me liaron y andaban intercambiados en el final de la fórmula y ni lo veía.
    Disculpas por tanto comentario y mi sincero agradecimiento por tus respuestas y paciencia.

    Un saludo.

    ResponderBorrar
  13. ESTIMADO:
    No he logrado agregar la función frecuencia... =frecuencia(datos;grupos)... no tengo idea que es lo que hago mal y llevo 3 horas intentando... tengo excel 2007.
    Espero me pueda orientar paso a paso.
    Muchas gracias.

    POLYLLA

    ResponderBorrar
  14. FRECUENCIA debe usarse como fórmula matricial, tal como está indicado en la nota.

    ResponderBorrar
  15. Media 500 celdas A1 Y B1
    Desvio std 85 celdas A2 y B2
    Valor 600 celdas A3 y B3
    Probabilidad 88,03% celdas A4 y B4
    El 88,03% se obtiene con la formula DISTR.NORM(B3;B1;B2";VERDADERO)
    COMO hago en excel 2007 para graficar la campana de Gaus??? con esos datos
    Desde ya mil gracias...............

    ResponderBorrar
  16. Buenas tardes,

    Ante todo, le agradezco este blog. Está genial!

    Le planteo mi pregunta. Partiendo del ejemplo que usted pone, ¿cómo podría incluir ahí, por ejemplo, las notas de varios años y de varias personas y hacer que el gráfico me fuera mostrando las variaciones de frecuencia dependiendo de esas variables? Se trataría de poder hacer que el gráfico mostrara las frecuencias del mes/año que yo quiera y las frecuencias de una sola persona durante ese mes/año o del total.

    Imagino que será posible hacerlo, pero llevo un rato intentándolo y no hay manera...

    ¡MUCHAS GRACIAS!

    ResponderBorrar
  17. Como casi todo en Excel la respuesta es si, se puede. La cuestión es cómo organizar los datos.
    ¿Podrías enviarme un ejemplo de los datos?

    ResponderBorrar
  18. Buenos días,

    Ya conseguí hacerlo. No sé si es la mejor manera, ni la más ortodoxa, pero ahí está conseguido...

    Por resumir, he utilizado una replica de la misma tabla de datos, pero formulada con =si(), de tal forma que en ella sólo me aparezcan los datos que estoy buscando en cada momento. En la tabla original he incluido una columna, formulada con =si(concatenar()), que apunta a una tercera hoja, donde está el histograma, en la que se concatenan también las diferentes variables, dependiendo de lo que elija. Si el contenido de esa celda coincide con lo incluido en la columna de la tabla original, en la tabla réplica aparecen los datos correspondiente, si no, no aparecen. Así puedo hacer los diferentes cálculos de frecuencia sobre la tabla réplica.

    Imagino que mi explicación no está demasiado clara y que mi método no es el más "profesional", pero lo he conseguido!

    De nuevo, muchas gracias por el blog. Si no fuera por él, todo sería mucho más dificil en el trabajo.

    Un saludo.

    ResponderBorrar
  19. Buenas noches, necesito saber como hacer la frecuencia de un test de lectura en el que los alumnos leen 95, 100, 150,175,225 palabras por minuto. llevo mirando paginas y no logro encontrar nada. La idea es poner un valor comprendido por ejemplo entre 0 y 50 palabras, 50 y 100 palabras....pero no encuentro la formula. Si alguien puede ayudarme se lo agredezco de antemano. Un saludo a todos.

    ResponderBorrar
  20. Podrías utilizar la técnica que muestro en esta nota, y también en la nota del enlace que aparece al principio.

    ResponderBorrar
  21. Sólo escribo para agradecer y felicitar al autor por este blog. Gracias de corazón JLD

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.