Control de Gastos en Excel

El manejo de formatos contables en excel no siempre es sencillo, controlar el registro de gastos en excel puede llegar a ser un dolor de cabeza, sobre todo cuando empiezas a registrar cosas por todos lados sin ninguna estructura definida. En realidad es algo muy sencillo de manejar, en este post te enseñare a llevar Control de Gastos con Formulas Excel y a generar un reporte de manera rápida pero muy potente y profesional.

Para comenzar es importante definir las cuentas de tus gastos, de tal forma que luego en los registros diarios puedas especificar la cuenta en cada uno de los registros, esto servirá para luego poder referenciar esos datos al momento de generar reportes.

Descargar Control de Gastos con Formulas Excel

Tablas en Excel

Para este caso particular trabajare con Tablas de Excel, este es un concepto diferente a los rangos normales de excel, las tablas tienen una serie de propiedades que te ayudaran de manera muy particular a gestionar los datos. Pero lo principal es que tanto tus registros como reportes serán procesados de manera rápida y sin mucho trabajo.

Anteriormente ya he tocado el tema de las Tablas de Excel, algunas de las grandes ventajas de las tablas es que al ir ingresando nuevos datos no es necesario estar re-dimensionando algún nombre de rango que hace referencia a los datos. Cada vez que ingresas datos en la siguiente fila o columna esta se re-dimensiona automáticamente.

Por otro lado, es sumamente sencillo el poder hacer referencia a las tablas desde formulas, lo cual facilita enormemente la generación de cálculos y reportes. Sobre todo porque no solo puedes hacer referencia explicita a la tabla en si, sino también a sus columnas en base a los encabezados de la tabla.

Insertar una nueva tabla

1. Selecciona una de las celdas en el rango de datos que convertirás a tabla, de preferencia uno de los encabezados.
2. Click en el menú “Insertar”.
3. Click en Tabla.
4. Activar la casilla “La tabla tiene encabezados”
5. Aceptar.

Con esto se convierte el rango en una tabla de excel, podras ver un nuevo menú de opciones que corresponde a la tabla, en esta puedes cambiar el nombre, sugiero siempre comenzar los nombres de tabla con “tbl” al inicio. Esto te sera de gran ayuda al momento de referenciar las tablas desde formulas

Diseñando la tabla para el control de gastos con formulas excel

En este caso realmente usaremos dos tablas, una donde estará la lista de cuentas y otra donde se ingresaran los registros de cada gasto, así que simplemente debes poner los encabezados en cada hoja. Para el registro de gastos sugiero estos datos ( Folio Fecha Cuenta Concepto Importe ). Para la lista de cuentas puedes poner una sola columna con el encabezado ( Cuenta ), recuerda que luego puedes agregar mas columnas y automáticamente se agregan a la tabla.

Una vez que tengas los encabezados solo debes proceder con los pasos para insertar tabla en ambas hojas, de tal modo que tendrás listas las dos tablas para proceder con los registros y el diseño del reporte. En este caso puedes usar los siguientes nombres de tabla ( tblGastos, tblCuentas ).

En la tabla de cuentas solo deberás ingresar los diferentes nombres de cuenta que manejaras.

Listas desplegable de cuentas en la hoja de registro de gastos.

Para facilitar el registro, se puedehacer una validacion de datos en la columna de cuenta en la hoja de registro de gastos, de tal modo que puedas seleccionar la cuenta desde una lista, para ello hacer lo siguiente.

1. Selecciona la primera fila donde se ingresaran datos en la columna de Cuenta en la hoja de registro de gastos, en este caso puede ser la celda B2.
2. Click en menú Datos/ Validación de Datos.
3. En permitir seleccionar la opción “lista”.
4. En origen poner esta formula =INDIRECTO("tblCuentas[Cuenta]")
5. Aceptar

Con eso podrás ver una lista desplegable en la columna de cuenta.

Preparar hoja de reporte de gastos con formulas excel

Esto lo podemos hacer en la misma hoja donde esta la lista de cuentas, recuerda que insertamos una tabla en esa hoja “tblCuentas”

Como vamos a generar el reporte en base a la cuenta y mes, puedes poner los nombres de los meses a la derecha en la tabla.

La tabla se vera de esta manera.

Formula para obtener la suma de cada cuenta y por mes.

Lo que tenemos que hacer aquí es comparar los datos de la columna de cuenta de la tabla “tblGastos” con cada una de las cuentas en la tabla “tblCuentas”. También tenemos que comprar la columna de fecha con el mes que corresponda en “tblCuentas”, cada vez que ambas comparaciones sean verdaderas, se deberá sumar la cantidad de la columna “Importe” de “tblGastos”.

Para lo anterior usaremos la función SUMAPRODUCTO de la siguiente manera.

=SUMAPRODUCTO((tblGastos[Cuenta]=[@Cuenta])*(TEXTO(tblGastos[Fecha],"mmmm")=tblCuentas[[#Encabezados],[enero]])*(tblGastos[Importe]))

Comparación de la columna de “Cuenta” (tblGastos[Cuenta]=[@Cuenta])

Comparación de la columna de “Fecha” (TEXTO(tblGastos[Fecha],"mmmm")=tblCuentas[[#Encabezados],[enero]])

Cuando en ambas comparaciones el resultado es verdadero se suma (tblGastos[Importe])

La misma formula se coloca en la columna de cada mes, solo tenemos que cambiar el mes en la formula.

Columna para totalizar cada cuenta.

A la derecha de la tabla “tblCuentas” teclea “Total” y en la celda de abajo pones esta formula.

=SUMA(tblCuentas[@[enero]:[marzo]])

En ese caso yo puse solo tres meses en la tabla, si pones todos lo meses solo cambia “marzo” por “diciembre” en la formula.

Totalizar columnas

Esto se puede hacer de dos maneras, una es poniendo una formula en alguna fila abajo de la tabla =SUMA(tblCuentas[enero]) , la otra es en el menú diseño de herramientas para tabla activar la casilla “Fila de Totales”

El resultado sera como en la siguiente imagen, recuerda que como la tabla “tblGastos” se re-dimensiona automáticamente cada vez que se agregan datos en la ultima fila, la tabla “tblCuentas” que es nuestro reporte también, se estará actualizando automáticamente.

Tutorial Control de Gastos con formulas excel

Espero que este ejemplo te sea de gran ayuda y puedas adaptarlo en tus proyectos.