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: MS Excel