Saldo bancario diario con una tabla dinámica

Cuando descarga la actividad bancaria, normalmente obtiene las transacciones, pero no el saldo bancario diario. Para calcular el saldo bancario diario, debe resumir las transacciones por día y mostrar un total acumulado para todos los días, incluso aquellos sin actividad. Esta publicación demuestra cómo crear un informe de este tipo utilizando una tabla dinámica.

Descripción general

Antes de profundizar en los detalles técnicos, dejemos claro nuestro objetivo. Hemos descargado parte de la actividad bancaria e ingresamos manualmente el saldo bancario inicial a partir del 1/1/2017. Esto se ilustra a continuación.

Nos gustaría crear un informe que muestre una fila para cada día del mes, incluso si no hay transacciones para ese día. Además, el informe debe mostrar un total acumulado que represente el saldo bancario diario. Básicamente, nos gustaría crear un informe similar a este.

Hagámoslo.

Detalles

La creación de un informe de este tipo se puede lograr con una tabla dinámica. Explicaré cada paso a continuación. Pero, si ya está familiarizado con las tablas dinámicas, la respuesta corta es esta: agrupar por mes y día, mostrar elementos sin datos y mostrar valores como un total acumulado.

Construiremos este informe siguiendo los siguientes pasos.

• Tabla dinámica básica
• Mostrar todos los días
• Total acumulado
• Productos cosméticos

Tabla dinámica básica

Configuremos la tabla dinámica básica. Seleccione cualquier celda en el rango de la fuente de datos y Insertar Tabla dinámica. Colocamos el informe en una nueva hoja de trabajo y hacemos clic en Aceptar.

En el panel Campos de tabla dinámica, marcamos las casillas de verificación Fecha e Importe. En este punto, tenemos una tabla dinámica básica, como se muestra a continuación.

Nota: Excel 2016 para Windows agrupa automáticamente los campos de fecha y, si está utilizando una versión anterior a Excel 2016 para Windows, su informe puede verse así:

Si es así, no te preocupes, los pasos a continuación funcionarán bien

El mayor problema con nuestro informe es que solo muestra días con actividad. Sin embargo, nuestro informe debe incluir una fila para cada día del mes, incluso aquellos sin transacciones. Entonces, la siguiente tarea es mostrar todos los días.

Mostrar todos los días

Queremos mostrar todos los días, incluso aquellos sin transacciones de datos. Para hacer esto, es necesario agrupar el campo de fecha. Queremos que nuestro informe esté agrupado por mes y día. Entonces, hacemos clic derecho en cualquier celda de fecha del informe y seleccionamos Grupo.

Esto muestra el cuadro de diálogo Agrupación, donde elegimos Meses y Días, como se muestra a continuación.

Hacemos clic en Aceptar y nuestro informe ahora se ve así:

Ahora, abrimos el cuadro de diálogo Configuración de campo para el campo Fecha (no el campo Meses). Una forma sencilla de hacerlo es hacer clic derecho en cualquier día del informe y seleccionar Configuración de campo. En el cuadro de diálogo Configuración de campo resultante, nos dirigimos a la pestaña Diseño e impresión. Allí encontramos una casilla de verificación llamada Mostrar elementos sin datos . Lo comprobamos, como se muestra a continuación.

Le damos a Aceptar y ¡guau! Ahora vemos una fila de informe para cada día, como se muestra a continuación.

Nota: si agrupa el campo de fecha por mes, podrá mostrar meses sin datos. Si agrupa por día, puede mostrar días sin datos. Es decir, la casilla Mostrar elementos sin datos se puede aplicar al campo del grupo de fechas deseado.

¡Nos estamos acercando! En este punto, necesitamos agregar una columna de total acumulado.

Total acumulado

Agregar una columna de total acumulado no es tan malo. Comenzamos insertando nuevamente la columna Monto en el área de diseño Valores. Una forma sencilla de hacerlo es hacer clic y arrastrar el campo Importe al área Valores.

Ahora tenemos dos columnas de Importe en el informe. Dejaremos el primero como está y convertiremos la segunda columna Monto en el total acumulado. Para hacer esto, hacemos clic derecho en cualquier celda de valor en la segunda columna Monto y seleccionamos Mostrar valores como Total acumulado en. En el cuadro de diálogo resultante Mostrar valores como, aceptamos el campo base predeterminado (Fecha) y hacemos clic en Aceptar.

Bam, nuestro informe ahora incluye una columna de total acumulado, como se muestra a continuación.

Nuestro informe básico está listo, ahora sólo tenemos que limpiarlo y hacerlo bonito.

Productos cosméticos

Continuaré y resumiré los pasos que utilicé para formatear el informe, pero el formato es una preferencia personal, así que siéntase libre de omitirlos o realizar cualquier otro tipo de formato que prefiera.

• Primero, filtramos las filas del informe no deseadas. Para hacer esto, seleccionamos el control de filtro en el encabezado Etiquetas de fila y desmarcamos todos menos Jan.
• A continuación, cambiamos el estilo del informe a Tabular usando el icono de comando Herramientas de tabla dinámica Diseño de informe Mostrar en forma tabular.
• Luego, actualizamos el estilo a uno que nos guste usando la galería de estilos de herramientas de tabla dinámica (yo elegí Pivot Style Medium 13).
• Actualice el formato del número haciendo clic derecho en cualquier celda de valor y seleccionando Formato de número. Haga eso una vez para cada columna de datos.
• Actualice los encabezados del informe de Suma de monto a Total diario y de Suma de monto2 a Total acumulado seleccionando cada celda de etiqueta y escribiendo la nueva etiqueta.

Y el informe resultante se muestra a continuación.

Lo logramos… ¡nuestro total acumulado refleja el saldo bancario diario!

Si tiene otras ideas divertidas sobre tablas dinámicas, compártalas publicando un comentario a continuación.

Recursos adicionales

• Archivo de muestra: saldo bancario diario
• Mostrar meses sin datos: Grupos de meses de tabla dinámica