¿Cómo crear un rastreador de gastos en Excel?

La gestión financiera juega un papel importante en una organización o en las finanzas personales de la vida de las personas. Para administrar las finanzas, las organizaciones tienen un administrador de finanzas, de la misma manera que una persona individual puede administrar sus propios gastos utilizando un administrador de gastos.

Expense Manager , también conocido como Expense Tracker , es una aplicación o software que se utiliza para mantener registros de la entrada y salida de dinero. Se utiliza para administrar sus gastos diarios y realizar un seguimiento de » Cuánto gasta, Más de lo que ha gastado» .

Por ejemplo , en este ejemplo crearemos un registro de gastos con Microsoft Excel, que se automatizará según lo que se ingrese en el registro, también mostrará cuánto dinero queda en cada categoría y cambiará según el mes seleccionado en el menú desplegable.

Pasos de implementación

Paso 1: Primero, abriremos la aplicación de Microsoft Excel, y definiremos las siguientes columnas Mes, Fecha, Descripción, Categoría, Ingresos, Débitos, Saldo. Puede definir sus propias columnas según sus requisitos.

Defining-columns

Fig. 1 – Columnas de seguimiento de gastos

Ahora, convertiremos estas columnas en tablas con todas las filas alternas. Para esto, seleccionaremos todas las columnas e iremos al estilo Formato como tabla dentro de la pestaña Inicio de Excel y elegiremos cualquiera de las vistas de formato de tabla que desee. A continuación se adjunta la captura de pantalla del mismo.

Inicio > Formatear como tabla

Format-as-table

Fig. 2 – Formato como tabla

Una vez que haya seleccionado su diseño de vista de tabla, aparecerá un mensaje allí, debe marcar la casilla de verificación «Mi tabla tiene encabezados», que creará las columnas de la tabla como encabezado el encabezado de la tabla. Haga clic en el botón Aceptar.

Defining-data-for-table

Fig. 3 – Casilla de verificación de encabezados de tabla

Después de hacer clic en el botón Aceptar, se crearán tablas con encabezados. Y obtendrá la tabla con el encabezado de sus columnas.

Tables-with-headers-created

Fig. 4 – Tabla de Gastos

Paso 2: en este paso, agregaremos las fórmulas necesarias y el formato de número a las columnas de la tabla para automatizar la mayor parte del trabajo por nosotros.

Primero, agregaremos la fecha de inicio y, con eso, agregaremos las fórmulas a nuestra tabla. Para esto primero, agregaremos una fecha aleatoria en la columna de fecha y presionaremos el botón Intro.

Date-column

Fig. 5 – Columna de fecha

Después de esto, cambiaremos el formato de fecha. Puede usar cualquier formato que desee, para hacerlo, seleccionaremos la columna de fecha (aquí, es la columna B) y haga clic derecho sobre ella y abra las celdas de formato, allí iremos a la categoría de fecha y elegiremos una formato de fecha.

Columna B > Clic derecho > Formato de celdas > Fecha > Tipo

Formatting-data-options

Fig. 6 – Opción de formato de fecha

Ahora, en la pestaña Formato de celdas, seleccionaremos la Categoría como Fecha y elegiremos un Tipo de fecha.

Data-format-cells

Fig. 7 – Celdas de formato de fecha

A continuación, configuraremos la fórmula en la columna Mes para extraer solo el número de mes de la columna Fecha. Para ello escribiremos = MES([@Date]) dentro de la columna del mes. La fórmula completará automáticamente el resto de las filas del mes con el número de mes de la columna de fecha. Cada vez que ingresemos una nueva fecha en la columna de fecha, se actualizará automáticamente el número de mes de acuerdo con la fecha.

=MES([@Fecha])

Auto-month-filled

Fig. 8 – Mes automático rellenado según la fecha

Paso 3: A continuación, agregaremos un menú desplegable sobre la columna de categoría para que podamos elegir una categoría de gasto. Para esto, seleccionaremos toda la columna de categoría y luego mantendremos presionada la tecla ctrl. y anule la selección del encabezado de la columna, luego, desde la pestaña Datos , seleccionaremos Validación de datos que está presente dentro de las Herramientas de datos.

Category-column

Fig. 9 – Columna de categoría

Una vez que haga clic en la opción Validación de datos, se abrirá una pestaña de Validación de datos, allí dentro de los criterios de Validación, en la sección Permitir, debe seleccionar Lista, que se usa para crear una lista desplegable y los menús de la lista deben ingresarse dentro de Fuente sección con comas separadas, Excel creará la lista para nosotros.

Data-validation-prompt

Fig. 10: solicitud de validación de datos

Puede agregar la lista del menú según sus propios gastos. Aquí, agregaremos Ingresos , Hipoteca , Servicios públicos , Comestibles , Escalofríos de combustible y lugares de reunión . Después de agregar, haga clic en el botón Aceptar, Excel creará la lista desplegable para todas las celdas seleccionadas y se creará el menú desplegable.

Menu-categories

Fig 11 – Menú Categorías

Después de esto, daremos formato a las columnas Ingresos , Débitos y Saldo como columnas de moneda. Para hacerlo, primero, seleccionaremos estas columnas, luego, dentro de la pestaña Inicio, iremos a la sección de números y seleccionaremos la moneda, la moneda que desee. En este caso, elegiremos la moneda Rupias indias.

Income-debit-balance-columns

Fig. 12 – Columnas de ingresos, débitos y saldos

Paso 4: En este paso, sumaremos la fórmula para obtener el saldo actual. Para esto, necesitamos agregar un saldo inicial a nuestra aplicación de seguimiento de gastos. Agregaremos 10000 rupias como Saldo y definiremos la Descripción para esto como nuestro saldo inicial y luego definiremos la fórmula para obtener el saldo actual.

Starting-balance

Figura 13 – Saldo inicial

Ahora, definiremos la fórmula en la siguiente celda debajo del saldo (celda G3) para automatizar el saldo actual. La fórmula que se utilizará es =SUM(G2+[@Income]-[@Debits]) .

=SUMA(G2+[@Ingresos]-[@Débitos])

Formulae-for-running-balance

Fig 14 – Fórmulas para Correr Equilibrio

Tenemos que agregar la fórmula en toda la columna, para esto, primero seleccionaremos la columna G3 y presionaremos la tecla Ctrl+C para copiar la fórmula, luego seleccionaremos toda la columna G y anularemos la selección del encabezado y las celdas de saldo inicial de la columna G (aquí, es la celda G1 y G2) mientras mantiene presionada la tecla Ctrl y luego pegue la fórmula usando la tecla Ctrl + V , Excel la pegará en toda la columna.

Una vez que lo peguemos, Excel completará automáticamente el saldo inicial de 10000 rupias en toda la columna G, igual que la columna del mes, pero a medida que actualice sus gastos e ingresos, calculará el saldo actual.

Auto-updating-balance

Fig. 15 – Saldo de actualización automática

Paso 5: En este paso, crearemos la barra de progreso que mostrará cuánto hemos gastado en cada categoría y cuánto ingreso necesitamos en cada categoría. Para hacerlo, debemos agregar todas las categorías en una columna separada que queremos rastrear.

Tracking-columns

Fig. 16 – Columnas de seguimiento

A continuación, debemos agregar una columna Total en la celda J1 y agregar el número de mes en el que queremos centrarnos en la columna I1 (aquí, es enero, por lo que agregaremos el número de mes a1).

Total-column-and-month-number

Fig. 17 – Columna de total y columna de número de mes en I1

Después de esto, debemos agregar la fórmula SUMAR.SI.CONJUNTO(rango_suma, rango_criterio1, criterio1, [rango_criterio2, criterio2], …) para todas las columnas de las que queremos realizar un seguimiento. Necesitamos escribir SUMAR.SI.CONJUNTO() por separado para la columna Ingresos y Gastos.

Primero, escribimos SUMAR.SI.CONJUNTO() para la columna Ingresos para mantener su seguimiento por separado con los gastos. Usaremos la siguiente fórmula. (Nota: tenga en cuenta que utilizará la fórmula de acuerdo con las columnas que debe definir)

=SUMAR.SI.CONJUNTO(Tabla1[[#Todas],[Ingresos]],Tabla1[[#Todas],[Mes]],I1,Tabla1[[#Todas],[Categoría]],I2)

SUMIFS()-for-balance

Fig. 18 – SUMAR.SI.CONJUNTO() para ingresos

Ahora, agregaremos la fórmula SUMIFS() para nuestras categorías de gastos. Para el seguimiento de gastos utilizaremos la siguiente fórmula.

=SUMAR.SI.CONJUNTO(Tabla1[[#Todas],[Débitos]],Tabla1[[#Todas],[Mes]],$I$1,Tabla1[[#Todas],[Categoría]],I3)

SUMIFS()-for-expenses-categories

Fig. 19 – SUMIFS() para categorías de gastos

Después de agregar esta fórmula, simplemente arrastraremos y soltaremos la fórmula en todas las columnas de categorías que queremos rastrear. es decir, hipoteca, servicios públicos, comestibles, combustible y escalofríos y lugares de reunión. Esto actualizará todos los gastos según los meses.

Tracking-expenses

Fig. 20 – Seguimiento de gastos

Podemos ver que las hipotecas, el combustible y los comestibles están en 0, ya que no hemos agregado ningún gasto en estas categorías. Ahora, para mejorarlo, cambiaremos el tipo de estas columnas de seguimiento a monedas (aquí, en rupias indias). Para ello, seleccionaremos todas estas columnas, y luego en la pestaña Inicio, iremos a la sección Número y cambiaremos el tipo a monedas.

Change-type-of-tracked-columns

Fig 21 – Cambiar el Tipo de columnas rastreadas

Paso 6: En este paso, diseñaremos nuestra barra de progreso la cual nos mostrará cuántos ingresos tendremos y cuánto nos queda en cada categoría para gastar. De nuevo, diseñaremos los ingresos por separado y las categorías de gastos por separado.

Barra de progreso para Ingresos

Primero seleccionaremos la columna que tiene el valor de los ingresos. Desde la pestaña Inicio, elegiremos Formato condicional en la sección Estilo. Y, seleccione Barras de datos, seleccionaremos barras verdes para los ingresos.

Progress-bars

Fig. 22 – Barras de progreso

Lo estiraremos un poco más para obtener buenas imágenes. Ahora, necesitamos definir las reglas para ello. Para esto, iremos nuevamente a Formato condicional y luego elegiremos Administrar reglas. 

Manager-rule-tab

Fig. 23 – Pestaña Regla de administrador

Necesitamos actualizar nuestra regla existente, que Excel crea de forma predeterminada. Simplemente hacemos doble clic para abrirlo y actualizar el valor mínimo y máximo y establecer su tipo y número. Esto hará que nuestra barra de progreso sea rastreable.

Data-bar

Fig. 24 – Reglas por defecto – BARRA DE DATOS

Estableceremos el valor mínimo en 0 y el valor máximo en 45000, luego de actualizar estos valores, presionaremos OK y aplicaremos nuestras reglas.

Managing-rules

Fig. 25 – Administrar reglas

Después de esto, habremos terminado con los ingresos y se verá así.

Tracking-income

Fig. 26 – Seguimiento de ingresos

Lo mismo tenemos que hacer para todos nuestros gastos por separado. Puede configurar los valores mínimo y máximo según sus necesidades. Aquí, haremos un seguimiento de los gastos en color rojo con un valor mínimo y máximo de la siguiente manera Hipoteca (0, 8000), Servicios públicos (0, 5000), Comestibles (0, 5000), Combustible (0, 2000), Chills & Hangout ( 0, 3000). Una vez que repitamos los mismos pasos para todos los gastos, obtendremos algo como esto.

Tracking-expenses

Fig. 27 – Seguimiento de gastos

Paso 7: En este paso, intentaremos mejorar nuestro diseño. Agregamos bordes a través de nuestra tabla de seguimiento. Para esto, seleccionaremos toda la tabla de gastos y la arrastraremos hacia abajo y crearemos una columna (aquí, es la columna H y la columna K) con una longitud equivalente. Puedes encontrarlo debajo de la imagen.

Tracking-table-border

Fig. 28 – Borde de la tabla de seguimiento

Avanzando, seleccionaremos toda la tabla y crearemos Todos los bordes. Para ello, vamos a la pestaña de Inicio , y luego en la sección Fuente , elegiremos la opción de bordes y seleccionamos Todos los bordes , hará bordes alrededor de nuestra mesa.

Tracking-table-all-borders

Fig. 29 – Tabla de seguimiento de todos los bordes

Una vez que hagamos esto, seleccionaremos las celdas alrededor de nuestro borde que dejamos en el paso anterior, para eso necesitamos seleccionar esas celdas manualmente presionando y manteniendo presionada la tecla ctrl y seleccionando las celdas.

Cells-selection

Fig. 30 – Selección de celdas

Una vez que seleccionamos todas las celdas alrededor de nuestra tabla, las rellenamos con un color sólido. Para eso, vamos a la pestaña Inicio , y luego en la sección Fuente , rellenamos el color.

Filling-cells

Fig. 31 – Células de llenado

Producción

Aquí, probaremos nuestra aplicación Expense Manager. Completamos algunos de los gastos en el mes de enero y luego cambiamos el número de mes en la Tabla de seguimiento de progreso y completamos el mes de febrero.

Publicación traducida automáticamente

Artículo escrito por ankur035 y traducido por Barcelona Geeks. The original can be accessed here. Licence: CCBY-SA

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *