Tablas dinámicas para analizar tu control de gastos

Antes de empezar: cómo deben estar los datos

La tabla dinámica funciona sobre una lista con una fila por movimiento y una columna por dato: fecha, concepto, categoría, tipo e importe. Es justo el formato de la pestaña Movimientos de nuestra plantilla de ingresos y egresos, que usamos como ejemplo. Comprueba tres cosas:

• Cada columna tiene un título en la primera fila y no hay columnas sin título.
• No hay filas de totales ni filas vacías en medio de los datos.
• Las fechas son fechas de verdad y los importes, números (si alinean a la izquierda, probablemente son texto).

Paso a paso en Excel

1. Convierte los datos en tabla. Haz clic en cualquier celda de los movimientos y pulsa Ctrl + T. Así, cuando añadas filas nuevas, la tabla dinámica las incluirá al actualizar.
2. Crea la tabla dinámica. Con una celda de la tabla seleccionada, ve a Insertar › Tabla dinámica, deja marcada la opción Nueva hoja de cálculo y pulsa Aceptar.
3. Categoría a Filas. En el panel Campos de tabla dinámica, arrastra Categoría al área Filas.
4. Importe a Valores. Arrastra Importe a Valores. Debe aparecer «Suma de Importe»; si pone «Cuenta de Importe», haz clic en él, elige Configuración de campo de valor y cambia a Suma.
5. Fecha a Columnas. Arrastra Fecha a Columnas. Las versiones recientes de Excel agrupan las fechas por meses solas; si no, haz clic derecho sobre una fecha de la tabla dinámica, elige Agrupar y marca Meses y Años.
6. Solo los gastos. Arrastra Tipo a Filtros y, en la celda de filtro que aparece encima, elige «Egreso» (o «Gasto»).
7. Formato de número. Clic derecho sobre un importe › Formato de número › Número con dos decimales y separador de miles.

Con los movimientos de enero a marzo de la plantilla, el resultado es este:

De un vistazo ves que febrero se dispara por el transporte: es el seguro anual del coche (486,00). Los totales coinciden con el resumen de la plantilla, calculado con SUMAR.SI.CONJUNTO: 1.315,85 en enero, 2.112,80 en febrero y 1.222,50 en marzo.

Tres cambios que dan mucha información

• Porcentaje de cada categoría: clic derecho sobre un importe › Mostrar valores como › % del total de columnas. Verás qué parte de cada mes se lleva cada categoría: la vivienda es el 57 % de enero.
• Ordenar de mayor a menor: clic derecho sobre el total de una categoría › Ordenar › De mayor a menor. Las categorías que más pesan quedan arriba.
• Ver el detalle: doble clic sobre cualquier importe y Excel abre una hoja nueva con los movimientos que lo forman. Ideal para saber qué hay detrás de una cifra rara.

Si quieres filtrar con botones, selecciona la tabla dinámica y ve a Analizar tabla dinámica › Insertar segmentación de datos: elige Categoría y tendrás un panel para pulsar una o varias categorías.

Actualizar cuando añades gastos

La tabla dinámica de Excel no se actualiza sola. Después de apuntar movimientos nuevos, haz clic derecho sobre ella y elige Actualizar, o ve a Datos › Actualizar todo. Si los datos no están en una tabla (Ctrl + T), comprueba en Analizar tabla dinámica › Cambiar origen de datos que el rango incluye las filas nuevas.

Paso a paso en Google Sheets

1. Selecciona las columnas de los movimientos, por ejemplo A:E. Con columnas completas, las filas nuevas entran solas.
2. Ve a Insertar › Tabla dinámica, elige Hoja nueva y pulsa Crear.
3. En el editor de tablas dinámicas de la derecha, junto a Filas, pulsa Añadir y elige Categoría.
4. Junto a Valores, añade Importe con «Resumir por: SUM».
5. Junto a Columnas, añade Fecha. Después, haz clic derecho sobre una de las fechas de la tabla dinámica y elige Crear grupo de fechas de tabla dinámica › Año-Mes.
6. Junto a Filtros, añade Tipo, deja marcado solo «Egreso» y pulsa Aceptar.

A diferencia de Excel, en Google Sheets la tabla dinámica se actualiza sola al cambiar los datos. Para mostrar porcentajes, en Valores cambia «Mostrar como» a % del total de la columna. Las filas con celdas vacías aparecen como «(vacío)»: si ves esa categoría, te falta rellenar alguna.

Tabla dinámica o SUMAR.SI.CONJUNTO

La tabla dinámica es ideal para explorar: cambias filas y columnas en segundos y respondes preguntas nuevas. Las fórmulas son mejores para un resumen fijo que se actualiza solo y que puedes combinar con presupuestos y formato condicional, como el de la plantilla de control de gastos mensuales. Lo ideal es usar las dos: fórmulas para el resumen de cada mes y una tabla dinámica cuando quieras investigar. Con los datos de un negocio funciona igual: prueba con la pestaña Gastos de la plantilla de gastos de empresa, con el proveedor en filas.

Errores comunes

• Sale «Cuenta de Importe» en lugar de la suma: hay alguna celda de texto o vacía en la columna Importe. Corrígela y cambia el resumen a Suma.
• No aparecen los gastos nuevos: en Excel, actualiza la tabla dinámica y comprueba que el origen de datos incluye las filas nuevas.
• Las fechas no se agrupan por meses: alguna fecha es texto. Búscala ordenando la columna: las fechas de texto se quedan al final.

Preguntas frecuentes