Histogramas en Excel: crea gráficos para datos agrupados

Tienes 50 edades anotadas en una columna y necesitas ver cómo se reparten. Con 50 valores sueltos un gráfico no se lee: pintas 50 puntos y no ves nada. La solución es agruparlos en intervalos y dibujar las tres gráficas pensadas para eso: el histograma (las barras pegadas), el polígono (la línea que las une) y la ojiva (la línea de la frecuencia acumulada). Al terminar el artículo habrás armado una tabla de frecuencias con fórmulas, un histograma limpio, un polígono y una ojiva con la mediana marcada.

El tutorial funciona en Excel 2016, 2019, 2021, 2024 y Microsoft 365. El botón Histograma aparece desde Excel 2016; en 2013 y 2010 se hace con Análisis de datos > Histograma, como se explica al final.

Qué es un dato agrupado y por qué necesita su propio gráfico

Cuando los datos vienen pocos (8, 12, 20 valores) un gráfico de barras o de dispersión los muestra uno por uno y se entiende. Cuando son muchos —50, 200, 1.000—, el gráfico se vuelve ilegible: las barras se apelotonan, los colores se repiten y no se ve la forma de la distribución. La forma solo aparece cuando los datos se resumen.

Agrupar significa meter cada valor en un intervalo: [8, 17), [17, 26), [26, 35)… Una vez repartidos, ya no tienes 50 barras: tienes 7 (una por intervalo). Cada barra resume cuántos cayeron dentro. Con 7 barras sí se ve si la distribución es simétrica, si tiene un pico, si hay valores extremos.

El libro de ejemplo va a ser una hoja con las edades de 50 asistentes a un taller de fotografía: el valor mínimo es 10 y el máximo 73. La amplitud del intervalo se elige para que salgan entre 6 y 8 grupos; con 9 años por intervalo salen 7. En la columna A irá la lista cruda de las 50 edades, y a la derecha, en otra zona, la tabla con los intervalos, la marca de clase, las frecuencias y las frecuencias acumuladas que alimentarán los gráficos.

Los tres gráficos que le saca partido a una tabla de frecuencias

Una tabla de frecuencias tiene cinco columnas: el intervalo, la marca de clase (el punto medio), la frecuencia absoluta fi (cuántos cayeron), la frecuencia acumulada Fi (cuántos cayeron hasta ese intervalo incluido) y las dos frecuencias relativas fr y Fr (los porcentajes). Cada gráfico aprovecha una de esas columnas.

El histograma es el gráfico básico; el polígono y la ojiva son refinamientos que se construyen sobre el mismo histograma o sobre la misma tabla.

Preparar la tabla de frecuencias que va a alimentar el gráfico

La tabla se arma en el mismo libro, a la derecha de la columna A. Las celdas D2:H8 son la tabla: los límites de cada intervalo, la marca de clase, fi, Fi, fr y Fr. Los datos crudos siguen en A2:A51.

Los pasos para tener la tabla montada son:

1. En la celda D2 escribe 8 y en E2 escribe 17 (los límites del primer intervalo; se elige una amplitud de 9).
2. En la celda E3 escribe =E2+9 y arrastra el controlador de relleno hasta E8 para que se calculen los límites superiores.
3. En D3 escribe =E2 y arrastra hasta D8 para encadenar los límites inferiores (cada intervalo empieza donde acaba el anterior).
4. En la columna F (marca de clase) escribe la fórmula del primer intervalo y arrástrala.
5. En la columna G (fi) escribe CONTAR.SI.CONJUNTO sobre la columna A y arrastra.
6. En H2 escribe el primer acumulado y a partir de H3 la suma del fi de la fila más la Fi de arriba.

El intervalo, la marca de clase y la frecuencia con fórmulas

Las fórmulas de los límites, la marca y la frecuencia se hacen así. Cada una trabaja sobre los datos crudos de A2:A51.

Para los límites superiores, en E2 se teclea 8 y en E3 se arrastra:

=B1+9*(FILA()-1)

Si prefieres partir del primer límite, en E2 escribes =D2+9 y arrastras. La amplitud (9) sale de dividir el rango total (73 − 10 = 63) entre los 7 intervalos que quieres.

La marca de clase es el punto medio del intervalo. En F2:

=(B2+A2)/2

Se arrastra hasta F8. Para el primer intervalo da (17 + 8) / 2 = 12,5.

La frecuencia absoluta se cuenta con CONTAR.SI.CONJUNTO (en inglés COUNTIFS). En G2:

=CONTAR.SI.CONJUNTO($A$2:$A$51;">="&A2;$A$2:$A$51;"<"&B2)

Se arrastra hasta G8. El primer criterio es "mayor o igual que el límite inferior" y el segundo "estrictamente menor que el límite superior": así la edad que coincide con un límite superior no se cuenta dos veces.

Frecuencia acumulada, relativa y relativa acumulada

La frecuencia acumulada suma el fi del intervalo más todos los anteriores. En H2 se escribe el primer fi tal cual:

=G2

En H3 se suma el fi de la fila con la Fi de arriba:

=H2+G3

Se arrastra hasta H8. La última Fi vale 50.

Para las relativas se añade una columna más. En I2:

=G2/50

Se arrastra hasta I8. La suma de I2:I8 vale 1. Para la relativa acumulada (Fr) en J2:

=I2

Y en J3:

=J2+I3

Se arrastra hasta J8. La última Fr es 1 (100%).

Dibujar el histograma con Insertar > Gráficos

Con la tabla lista, el histograma se hace en tres clics. Excel lo crea desde la columna fi y los intervalos.

1. Selecciona A1:B8 — espera, en este ejemplo el rango correcto es D1:E8 con la columna fi incluida. Selecciona D1:G8 (los límites inferiores, los superiores, la marca y fi). Mejor aún, selecciona solo D2:D8 y G2:G8 con Ctrl para que la X del gráfico sean los intervalos.
2. Ve a Insertar > Gráficos > Histograma.
3. Aparece un gráfico con barras pegadas y la frecuencia en el eje vertical.

Si Excel no encuentra el botón Histograma directamente, usa Gráficos recomendados y acepta la primera opción de histograma.

Quitar el espacio entre barras y dar nombre a los ejes

Por defecto el histograma de Excel trae un pequeño hueco entre barras. Para que sea un histograma de verdad, ese hueco se cierra a 0.

1. Clic derecho sobre una barra > Dar formato a la serie de datos.
2. En Opciones de serie, Ancho del intervalo: cambia el valor a 0%.

Para los títulos del eje:

Se eligen Eje horizontal principal y se escribe "Edad (años)"; para Eje vertical principal "Frecuencia (nº de asistentes)". El título del gráfico va en Agregar elemento de grafico > Titulo del grafico.

Por qué Excel te ofrece «gráfico de Pareto» al lado

Al pedir un histograma, Excel propone a veces también un gráfico de Pareto: barras ordenadas de mayor a menor con una línea de la frecuencia acumulada encima. Es muy útil para controlar calidad (el 80% de los defectos viene del 20% de las causas), pero no es lo que buscas aquí: en un histograma los intervalos tienen un orden natural (de menor a mayor edad) y no se reordenan. Elige el histograma básico, no el Pareto.

El polígono de frecuencias sobre el mismo histograma

El polígono es una línea que pasa por la parte superior de cada barra del histograma. Para dibujarlo en Excel hace falta un gráfico de dispersión con líneas, porque el de columnas introduce espacios que no quieres.

1. Copia la columna F (marca de clase) y la columna G (fi) a una zona libre, por ejemplo K2:L8.
2. Selecciona K2:L8.
3. Insertar > Graficos > Dispersión > Dispersión con líneas y marcadores.

La línea une los puntos centrales de cada barra. Para que se parezca al polígono clásico se añaden dos puntos extra en los extremos.

Añadir los puntos de cierre en los extremos

Para que el polígono baje al eje en los dos extremos hace falta un punto antes del primer intervalo y otro después del último. Se calcula restando la amplitud (9) al primer límite y sumándola al último.

En K2 escribe la fórmula:

=F2-9

En L2:

=0

En K9:

=F8+9

En L9:

=0

Se vuelve a seleccionar K2:L10 y se reemplaza la serie por la nueva. Ahora la línea cierra en los dos extremos.

La ojiva con la frecuencia acumulada

La ojiva representa la frecuencia acumulada. El eje X lleva los límites superiores de cada intervalo y el eje Y la Fi correspondiente. El gráfico que mejor le encaja es uno de líneas.

1. Selecciona E1:E8 (límites superiores) y, manteniendo Ctrl, H1:H8 (Fi).
2. Insertar > Graficos > Lineas > Linea con marcadores.
3. Cambia el título del eje Y a "Frecuencia acumulada" y del X a "Edad (años)".

La línea sube de izquierda a derecha y termina en 50 (el total). La pendiente cambia según la fi del intervalo: cuanto más alta es la barra del histograma, más sube la ojiva en ese tramo.

Marcar la mediana, los cuartiles y los percentiles sobre la ojiva

La mediana es el dato que deja el 50% de los casos por debajo. En la ojiva se localiza donde la línea cruza 25 (la mitad de 50). El primer cuartil está en 12,5, el tercero en 37,5.

Para marcar la mediana como un punto visible, añade una segunda serie con un solo valor:

Celda M2: =K.ESIMO.MAYOR(F2:F8;4)+AJUSTE. Para hacerlo sin cálculo extra, usa el valor 38,8 que sale de interpolar dentro del intervalo [35, 44): la Fi pasa de 26 a 33 entre 35 y 44 años; para llegar a 25 hay que retroceder hasta 38,8.

=35+9*(25-26)/(33-26)

Para Q1 (12,5) y Q3 (37,5) se hace igual:

=26+9*(12,5-6)/(17-6)

da 30,5 para Q1; y:

=35+9*(37,5-26)/(33-26)

da 44,0 para Q3.

Errores típicos al graficar datos agrupados

Los cuatro tropiezos que se repiten al hacer histogramas, polígonos y ojivas en Excel:

• Dejar el hueco entre barras por defecto: ya no es un histograma, es un gráfico de columnas. Cierra el ancho del intervalo a 0%.
• Usar Insertar > Gráficos > Columnas en vez de Histograma. Las columnas pueden tener separaciones; el histograma de Excel aplica por defecto el ancho 0%.
• Meter los 50 datos crudos en el gráfico. Sale un barullo de 50 barras finas inútil. Pasa siempre por la tabla de frecuencias.
• Contar la edad que coincide con un límite superior dos veces. Si el criterio es ">=" en ambos lados, el valor 17 se cuenta en [8, 17] y en [17, 26]. El segundo criterio debe ser "<" (estrictamente menor).

«En mi Excel no sale Histograma»

El botón Histograma aparece desde Excel 2016. Si tienes Excel 2013 o 2010, no lo verás en Insertar > Gráficos. Tienes dos opciones: instalar el complemento Análisis de datos (Archivo > Opciones > Complementos > Complementos de Excel > Ir > marcar Herramientas para análisis > Aceptar) y usar Análisis de datos > Histograma, que crea el gráfico automáticamente. La otra opción es hacerlo a mano: gráfico de columnas con los intervalos en X y fi en Y, ancho del intervalo 0%, sin más. El resultado es el mismo.

El gráfico se queda con los datos de antes al añadir filas

El problema clásico: tienes la tabla D2:H8 y el histograma hecho; mañana metes una edad nueva en A52 (un asistente más, por ejemplo) y el gráfico no la refleja. La razón es que el gráfico apunta a un rango fijo (A1:B8), no a la columna entera.

El arreglo:

1. Selecciona A1:A52 (toda la columna de edades, incluidas las nuevas).
2. Insertar > Tablas > Tabla.
3. Marca "La tabla tiene encabezados" y pulsa Aceptar.
4. Ve al gráfico, clic en Seleccionar datos y cambia el origen para que use la columna de la tabla.

Ahora, cuando añadas una fila nueva a la tabla, Excel la incluye en el gráfico automáticamente. La fórmula de CONTAR.SI.CONJUNTO que ya tienes en G2:G8 también se actualiza si la copias a una columna dentro de la tabla.

Lo que ya sabes hacer con datos agrupados en Excel

• Armar la tabla de frecuencias con los intervalos, la marca de clase, fi, Fi, fr y Fr usando CONTAR.SI.CONJUNTO.
• Dibujar un histograma limpio con Insertar > Gráficos > Histograma, sin hueco entre barras y con los ejes titulados.
• Construir el polígono de frecuencias sobre el mismo histograma con un gráfico de dispersión con líneas, cerrado a 0 en los extremos.
• Leer la mediana y los cuartiles sobre la ojiva, marcándolos con puntos extra.
• Convertir el origen del gráfico en una Tabla de Excel para que crezca solo cuando entren datos nuevos.

El siguiente paso lógico es aplicar formato condicional con barras de datos sobre la columna fr para ver la proporción de cada intervalo de un vistazo, sin necesidad de mirar el histograma.