Extraiga los valores del presupuesto en un estado de resultados

En esta publicación, exploramos una forma de incorporar valores presupuestarios a un estado de resultados exportado desde QuickBooks y demostramos cómo manejar el hecho de que el extracto utiliza nuevas columnas para sangrar.

Descripción general

Cuando el presupuesto y los datos reales residen en la misma aplicación, crear un informe de variación es fácil. Sin embargo, cuando los datos reales se almacenan en un sistema de contabilidad y los datos del presupuesto se almacenan en Excel u otra aplicación, la creación de informes de variación es un poco más manual. Exploraremos cómo utilizar las funciones CONCATENAR y BUSCARV para realizar la tarea de incorporar datos presupuestarios al estado de resultados. A modo de ilustración, utilicé QuickBooks para generar el estado de resultados.

Informe de variación

Echemos un vistazo rápido a la exportación que surgió de QuickBooks:

El objetivo es colocar los datos presupuestarios en la columna I de este estado de resultados.

Echemos un vistazo rápido a los datos del presupuesto, que se almacenan en una tabla denominada tbl_bud :

Entonces, nuestro objetivo es escribir una fórmula en la columna I del estado de resultados que recupere los valores de la tabla de presupuesto.

Si examinamos detenidamente el formato del estado de resultados por un momento, notaremos que no todos los valores de búsqueda se almacenan en la misma columna. Por ejemplo, la etiqueta de la cuenta 40110 se almacena en la columna F , pero la etiqueta de la cuenta 40150 se almacena en la columna G. Esto hace que sea difícil escribir una fórmula que podamos completar y que funcione para todas las cuentas.

Para demostrar el problema, digamos que escribimos la siguiente fórmula en la celda I5 :

=BUSCARV(F5, tbl_bud, 2, 0)

Dado que la etiqueta del informe está en la celda F5 , la fórmula funciona muy bien. Sin embargo, cuando copiamos esta fórmula a la celda I7 para recuperar la siguiente cuenta, la fórmula no funciona. Excel actualiza automáticamente la fórmula a esto:

=BUSCARV(F7, tbl_bud, 2, 0)

Dado que la etiqueta de la cuenta no está en F7 , está en G7 , la fórmula se rompe cuando la copiamos, como se muestra a continuación:

Afortunadamente, esta situación es relativamente fácil de manejar con la función CONCATENAR.

La función CONCATENAR une cadenas de texto. Los argumentos de la función se combinan y la función devuelve la cadena resultante. Simplemente podemos modificar el primer argumento de la función BUSCARV haciendo que Excel primero combine las celdas de etiqueta.

Si todas las etiquetas estuvieran almacenadas en las columnas F y G , por ejemplo, podríamos modificar nuestra fórmula de la siguiente manera:

=BUSCARV(CONCATENAR(F5,G5), tbl_bud, 2, 0)

Esto le indicaría a Excel que combine los valores en F5 y G5 y luego busque la cadena combinada en la tabla de presupuesto.

Si inspeccionamos nuestro estado de resultados de arriba a abajo, notaremos que todas las etiquetas que necesitamos buscar están almacenadas en D , E , F y G. Entonces, usaremos la siguiente fórmula:

=BUSCARV(CONCATENAR(D5,E5,F5,G5), tbl_bud, 2, 0)

Cuando copiamos esta fórmula, funciona para todas las líneas, como se muestra a continuación:

Para finalizar el informe de variación, copiamos la fórmula en todo el estado de resultados, creamos una columna de variación, completamos las fórmulas de subtotal de la columna real en la columna de presupuesto y copiamos el formato de la columna real en las columnas de presupuesto y variación, como ilustrado a continuación:

No dude en descargar el archivo de muestra utilizado para crear las capturas de pantalla anteriores.

Como ocurre con todo en Excel, hay muchas formas de lograr un objetivo. Si tiene un enfoque que utiliza o prefiere, compártalo publicando un comentario a continuación.

Notas y recursos adicionales

• Archivo de muestra: ConcatenateLookup
• Probablemente, un mejor método para usar en libros de trabajo recurrentes es crear una tercera hoja de trabajo en el libro que almacene un informe de variación que utilice fórmulas para recuperar valores tanto de la hoja real como de la de presupuesto. Dado que puede resultar difícil utilizar funciones de búsqueda o funciones de suma condicional para recuperar datos cuando la exportación utiliza nuevas columnas para sangrar, es posible que desee explorar la posibilidad de cambiar el tipo de exportación a un formato plano, que a veces se puede crear con una exportación CSV.
• A veces, las exportaciones de sistemas contables pueden incluir espacios adicionales. Si las etiquetas de la cuenta tienen espacios iniciales o finales, ajuste una función TRIM alrededor del valor de búsqueda para eliminarlos.
• Si intenta escribir fórmulas en la hoja de trabajo exportada de QuickBooks y se muestra la fórmula en lugar de los resultados de la fórmula, probablemente primero necesitará formatear la columna como general o numérica y luego escribir la fórmula. Si ya ha escrito la fórmula, es posible que deba cambiar el formato y luego actualizar la fórmula presionando F2 y luego Enter.
• Usamos BUSCARV para realizar la recuperación básica, aunque hay muchas otras opciones que quizás prefiera, incluidas ÍNDICE/COINCIDIR o SUMIFS.