Control de ingresos y egresos en Excel

El objetivo de este artículo es la de crear un mini aplicación para poder llevar el control de los ingresos y egresos de dinero de nuestra cuenta. Dentro de los ingresos puede estar incluido el salario y otros conceptos, dentro de los egresos están los gastos. 

Aquí vamos a ver una de las formas, la que se nos ocurrió, para anotar los ingresos y egresos. Puede haber muchas, buscamos algo diferente.

Este artículo plantea el concepto principal sin utilización de macros, luego se agrega una como un toque extra que no agrega mayor valor, se puede utilizar sin macros.

Básicamente  se trata de utilizar una hoja por cada mes del año, en donde tenemos tres campos principales: Ingresos, gastos e inversiones. Pero pueden aplicar el concepto como Uds. quieran. Además tenemos una hoja de resumen, en dónde podemos visualizar un gráfico  y los totales de cada mes.

La intensión principal es ir guardando un registro y mediante el gráfico/resumen ir comparando o viendo desvíos. Es decir, analizar la información. Además tenemos la información ordenada y es un método sistemático.

En nuestra experiencia se ha usado de manera personal, para controlar ingresos/egresos personales en el hogar. Sin embargo nada impide que lo puedan usar para la oficina o para algún proyecto simple ya que es muy básico.

Click en la imagen para ver el video o en este link.

Elementos básicos:

El elemento básico es la plantilla dónde se vuelvan los datos, que tiene esta forma:

Esta dividida en tres componentes, la parte de arriba dónde van los ingresos, lo más común es el salario. La parte del medio dónde van los gastos. Y la parte inferior dónde van las inversiones. También hay un resumen del subtotal por rubro.

Lo pueden usar según les convenga, sin las inversiones o sin el salario ya que los que nos interesan más  a menudo es lo que gastamos.

En el caso anterior se trata del mes de agosto, tenemos una hoja por cada mes y la hoja esta nombrada de esta forma también, para este caso la hoja se llama “ago-13”.

Como ven hay 4 columnas, la primera es un número de orden que puede o no tenerlo. La segunda la descripción breve que le ponemos al ítem. La tercera es el tipo de gasto, esto lo vamos a poder elegir desde una lista mediante validación de datos para que nos quedé más cómodo  y la última dónde va el monto.

Vamos a ver que contiene la hoja que llamamos “Notas”

En esta hoja tenemos la lista de rubros  de acuerdo a nuestro interés, en nuestro caso utilizamos los que se ven. Además utilizamos una columna auxiliar con un código de identificación del rubro para poder hacer las operaciones para mostrar el resumen.

Por último tenemos la hoja de informes:

En esta hoja tenemos un gráfico dónde nos resume la progresión de gastos. Para eso también necesitamos una planilla con los montos por tipo. En realidad el gráfico y la tabla pueden ir separados pero a fines de la explicación están juntos. Para poder tomar los datos de cada hoja es necesario utilizar una formula, un tanto compleja que ya veremos.   Una vez que se realizan las formulas par una hoja, nos queda muy fácil poder tomar los datos de las demás hojas.

Funcionamiento:

El funcionamiento es muy simple, tenemos la hoja del mes desde donde empezamos a tomar los datos, para nuestro caso agosto. Por lo cual debemos nombrar a la hoja “ago-13”.

Luego de ello podemos introducir los datos de acuerdo a si es un ingreso, un gasto o una inversión. Eligiendo el tipo de la lista de validación.

En la hoja de resumen apareceran los datos resumidos sin necesidad de hacer otra operación.

Si necesitamos empezar en otro mes copiamos la hoja, la pegamos inmediantamente a la deracha, actualizamos el mes, por ejemplo “sep-13”, borramos los datos viejos y empezamos a introducir los nuevos. Este proceso, de copiar una hoja, pegarla y borrar los datos se puede hacer con una macro ya que son pasos simples que se pueden grabar.

  

Proceso:

Como se explico más arriba el proceso, en teoría es simple ya que básicamente consiste en crear la plantilla pero utilizamos algunas formulas para poder tratar los datos.

La primera formula es la que nos muestra el mes y el año en la parte inferior de la plantilla. Cuando nombramos la hoja , automáticamente se traduce el nombre de la hoja a ese lugar, ya que es lo que estamos buscando.

Como ven en la barra de formulas se utiliza una propiedad de la hoja que se llama celda, dentro de la propiedad el tipo “filename” que nos muestra la ubicación y el nombre del archivo, es decir el “path” pero como elegimos esta forma de representar la fecha, siempre vamos a tener la fecha representada por 6 caracteres, recortamos a la derecha 6 caracteres y tenemos lo que queremos.

“=DERECHA(CELDA("filename");6)”

Cada vez que creamos una nueva hoja, copiando la primera y cambiando el nombre, ese dato se nos ajusta automáticamente.

Ahora vamos a ver las formulas utilizadas en la hoja resumen:

Esta formula es más compleja, una vez que usamos una podemos usar la misma para los demás datos.

Tenemos que armar una tabla como indica la figura, con los meses en la parte superior y los rubros o tipos de gasto al costado izquierdo.

“=SI(ESERROR(INDIRECTO(DIRECCION(30;5;1;;TEXTO(D$27;"mmm-yy"))));0;INDIRECTO(DIRECCION(30;5;1;;TEXTO(D$27;"mmm-yy"))))”

·         El condicional con el SI/eserror se hace para no mostrar error con datos vacíos o no encontrados.

·         Salvado eso queda la formula “INDIRECTO(DIRECCION(30;5;1;;TEXTO(D$27;"mmm-yy")))”

·         Utilizamos la formula indirecto con la cuál podemos obtener el dato de una celda utilizando una referencia a texto, por ejemplo si tenemos “Indirecto(“A1”), podemos obtener el dato que se encuentra en la celda A1.

·         En este caso, debemos obtener los datos de cada mes, es decir de cada hoja nombrada como el mes de referencia.

·         Para eso, usamos la formula dirección, con esto logramos obtener una referencia de texto de cada hoja que nos interesa y junto con la formula indirecto obtenemos el dato.

·         La formula nos queda : “DIRECCION(30;5;1;;TEXTO(D$27;"mmm-yy"))”

·         Esta formula busca el subtotal por tipo/rubro que se encuentra en la parte inferior de cada hoja mensual. En este caso 30 es la fila, 5 la columna, 1 es el tipo de referencia (absoluta), el dato vacío es el estilo de referencia (A1), y el último dato “TEXTO(D$27;"mmm-yy”) es lo que sería el nombre de la hoja. Como ven busca el dato del mes en la hoja correspondiente.

·         El resultado de la formula es un monto, que es el subtotal del mes correspondiente para el rubro (ingreso, gasto o inversión). Para conseguir los datos a la derecha se debe copiar y pegar la formula hacia la derecha. Para conseguir los datos hacía abajo se debe cambiar la fila, para los gastos sería 31 y para las inversiones 32 en nuestro ejemplo.

Una vez que tenemos estas formulas, podemos preparar la tabla y el gráfico para varios meses y cada vez que se agregue un mes, los datos se transferirán de manera automática al resumen.

Código:

Como dijimos antes para no tener que ingresar en forma manual un nuevo mes podemos utilizar una macro, no es necesario,  lo pueden hacer manual. Sin embargo aquí les dejos la macro.

Es una macro modificada como verán en el video.  Básicamente lo que hacemos es copiar la hoja, pegarla a la derecha, renombrarla de acuerdo al mes y borrarle los datos. Dejamos el código como cortesía pero le recomendamos que primero graben la macro base y luego intentar las modificaciones sobre ese código, si intentan comenzar con este código  y no lo entienden no van a aprender cómo se hace y no les va a funcionar si hay algún cambio en la hoja, o puede generarles algún error desconocido.

Sub Macro1()

‘Macro mes Nuevo

    Sheets(ActiveSheet.Name).Select  ‘selecciona la hoja actual

    Sheets(ActiveSheet.Name).Copy After:=Sheets(ActiveSheet.Index) ‘copia la hoja hacia la derecha

    Sheets(ActiveSheet.Name).Select ‘ selecciona la hoja nueva creada

    Sheets(ActiveSheet.Name).Name = Format(Date, "mmm-yy") ‘renombra la hoja de acuerdo al mes en curso

    Range("B9:E28").Select ‘selecciona el rango de entrada de datos

    Selection.ClearContents ‘borra los datos seleccionados

     End Sub

-------------------------------------- FIN CÓDIGOS

Recuerden que los artículos algunas veces requieren un mayor o menor nivel de conocimiento en ciertos temas de Excel, por ejemplo formulas avanzadas, macros o VBA, etc. Sin embargo en los artículos se nombran todos los elementos y con esta información podemos hacer nuestras pequeñas investigaciones usando internet, así desarrollamos nuestro conocimiento. Por ejemplo si  utilizamos la función “Y”, que la suponemos conocida, y no sabemos ¿qué es? podemos buscar en internet para ver como se utiliza y cuales son los componentes. Las mini aplicaciones no son más que la recopilación de muchos elementos para dar forma a una nueva idea.

Con todos los elementos vistos podemos construir una aplicación interesante. Esperamos que les sirvan las ideas.

Pueden descargar algunos ejemplos (plantillas/templates) de la zona de Descargas. Si tienen alguna duda o consulta pueden hacerlo a través del formulario de contacto.

Les recomendamos que sigan el blog por alguno de los medios, mail o por redes sociales, para recibir las actualizaciones y novedades.

¡Éxitos!

Keys:

·         Control de gastos, ingresos, inversiones en Excel.

·         Plantilla de control de gastos en Excel.

·         Plantilla de control de gastos e ingresos en Excel.

·         Planilla para control de gastos en Excel.

·         Planilla para el control de gastos mensuales/domésticos/hogar gratis en Excel.