Histograma con un gráfico dinámico

Un histograma muestra gráficamente la cantidad de elementos que se encuentran dentro de intervalos iguales o contenedores. Por ejemplo, la cantidad de días con una temperatura alta entre 71 y 80 grados, 81-90 y 91-100, la cantidad de estudiantes con puntajes en exámenes entre 60-69, 70-79, 80-89, o la cantidad de facturas que vencen en 31-60, 61-90 o 91-120 días. En esta publicación, crearemos un histograma usando un gráfico dinámico.

Objetivo

Hemos exportado una lista de facturas abiertas desde nuestro sistema de contabilidad. Nos gustaría crear un gráfico que muestre las facturas según su estado de antigüedad, agrupadas en intervalos iguales, 1-30, 31-60, 61-90 días, etc. Esto se ilustra a continuación.

Normalmente, la parte que consume más tiempo al crear un histograma es resumir los datos y calcular la cantidad de elementos dentro de cada intervalo. Una vez resumidos los datos, es bastante fácil trazarlos.

Como ocurre con todo en Excel, hay varias formas de resumir los datos y, con un pequeño truco, podemos usar una tabla dinámica.

Antes de entrar de lleno, hay una nota de precaución. Las tablas dinámicas solo muestran valores que existen en la fuente de datos. Es decir, si no hay facturas entre 31 y 60 días, ese intervalo se excluirá de la tabla dinámica y del gráfico dinámico. Hay algunas maneras de abordar esto. Dependiendo de lo que esté representando, puede agregar celdas de valores en blanco a la tabla para las etiquetas de intervalo que faltan o usar una solución diferente que incluya CONTAR.SI, FRECUENCIA o la herramienta Histograma.

Suponiendo que nuestros datos contienen valores para todos los intervalos necesarios, es bastante rápido y fácil crear una tabla dinámica y un gráfico dinámico relacionado.

Tabla dinámica/gráfico dinámico

Comencemos mirando nuestros datos. Exportamos las facturas abiertas desde nuestro sistema de contabilidad y almacenamos los datos en una tabla ( Insertar Tabla ). Hemos definido una fecha de vencimiento “A partir de” y luego calculamos el número de días entre la fecha de la factura y la fecha A partir de con una fórmula de resta simple. La tabla de datos resultante se muestra a continuación.

Para resumir la tabla de datos y hacer que Excel coloque automáticamente los datos en intervalos, creamos una tabla dinámica. Insertamos Tabla dinámica y luego insertamos el campo Días en el área FILAS y el campo Monto en el área VALORES. Esto se muestra a continuación.

De forma predeterminada, Excel sumará el campo Importe ya que es numérico. Para nuestro histograma queremos cambiar las matemáticas para contar, por lo que hacemos clic con el botón derecho en cualquier celda de valor de la tabla dinámica y seleccionamos Resumir valores por Contar . La tabla dinámica actualizada se muestra a continuación.

El último paso es crear los intervalos o contenedores. ¡Esto es bastante fácil con una tabla dinámica una vez que conocemos el truco grupal!

Todo lo que tenemos que hacer es hacer clic derecho en cualquier celda de día en la tabla dinámica y seleccionar Grupo . En el cuadro de diálogo Agrupación resultante, podemos especificar los valores inicial y final, así como la duración del intervalo. En nuestro caso, queremos comenzar el primer intervalo con el día 1, queremos terminar en el día 120 y queremos que los intervalos abarquen 30 días.

Cuando hacemos clic en Aceptar… bam… ¡la tabla dinámica muestra los intervalos deseados!

Ahora podemos insertar un gráfico dinámico seleccionando el icono Herramientas de tabla dinámica Analizar Gráfico dinámico . En el cuadro de diálogo Insertar gráfico resultante, aceptamos el gráfico de columnas predeterminado y hacemos clic en Aceptar. Excel inserta un gráfico dinámico en la hoja de trabajo como se muestra a continuación.

Todo lo que tenemos que hacer ahora es limpiarlo con algunos toques cosméticos. Eliminamos la leyenda seleccionándola y pulsando la tecla suprimir de nuestro teclado. Eliminamos los botones del gráfico haciendo clic en el icono Herramientas de gráfico dinámico Analizar Botones de campo . Seleccionamos el estilo deseado con las opciones Herramientas de gráfico dinámico Diseño Estilos de gráfico . Eliminamos el espacio vacío entre columnas cambiando el Ancho del espacio a 0% como se muestra a continuación.

El histograma resultante se muestra a continuación.

Si bien un histograma muestra la distribución de frecuencia, es decir, la cantidad de elementos en cada intervalo, podemos modificar nuestra tabla dinámica para mostrar la cantidad de dólares dentro de cada intervalo cambiando las matemáticas de recuento a suma. Hacemos esto haciendo clic derecho en cualquier celda de valor en el informe y seleccionando Resumir valores por Suma . El gráfico de columnas actualizado se muestra a continuación.

Ahora nuestro gráfico muestra la cantidad de dólares que se encuentran dentro de cada rango. Esto también es muy fácil de actualizar en períodos futuros. Todo lo que tenemos que hacer es pegar transacciones en la tabla de datos y actualizar la tabla dinámica. Al hacerlo, se actualizarán la tabla dinámica y el gráfico dinámico.

Si tiene otros enfoques, preferencias o trucos… compártalos publicando un comentario a continuación.

Recursos adicionales

• Archivo de Excel de muestra
• Otras publicaciones de la mesa
• Otras publicaciones de tabla dinámica