Calcular Frecuencias Y Crear Histogramas Con Excel

Calcular frecuencias y crear histogramas con Excel
LUNES, SETIEMBRE 01, 2008
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 elDestripador, 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 nuestrodirector 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áneamenteCtrl+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 deldirector 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 catasuperior 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 unamatriz 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 comovariable 10 en lugar de 5

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

Cómo hacer un histograma en Excel

Un histograma es una representación gráfica de una variable en forma de barras donde el tamaño de cada barra es proporcional a la frecuencia del valor que está representando. El eje horizontal del histograma tiene los valores de las variables y el eje vertical las...