Crear períodos de años fiscales con BUSCARV

Soy un gran admirador de la función BUSCARV y estoy sorprendido por su utilidad diaria para los contadores. En esta publicación, utilizamos la función BUSCARV para convertir o traducir fechas de transacciones del año calendario a períodos de años fiscales, como un trimestre fiscal. Para lograr esto, primero necesitaremos investigar en detalle el cuarto argumento de la función, el argumento de búsqueda de rango. Vamos a empezar.

Argumento de búsqueda de rango

La sintaxis de la función BUSCARV es la siguiente:

=BUSCARV(valor_búsqueda, matriz_tabla, núm_índice_columna, [rango_búsqueda])

Dónde:

• valor_buscado : el valor a encontrar
• table_array – el rango en el que estamos buscando
• col_index_num : la columna que tiene el valor a devolver
• [range_lookup] – verdadero o falso, ¿estamos haciendo una búsqueda de rango?

El cuarto argumento, el valor opcional range_lookup, es el argumento que nos permite traducir fácilmente fechas en grupos de trimestres fiscales o realizar cualquier otra búsqueda de rango. Entonces, ¿qué es una búsqueda de rango? Significa que estamos tratando de encontrar un valor entre dos puntos finales. La palabra rango aquí no se refiere a un rango de hoja de cálculo, como A1:B10. Se refiere a un rango entre dos valores, como un número entre 1 y 100, 101 y 200, o en el caso de fechas, entre el 1/1/14 y el 31/03/14. Estamos tratando de encontrar nuestro valor de búsqueda dentro de un rango de valores. Dado que este argumento es opcional, los usuarios de Excel no están obligados a expresar un valor… es decir… podemos dejarlo fuera de la función. El valor predeterminado, si se omite, es VERDADERO, como en verdadero, estamos haciendo una búsqueda de rango. El valor de FALSO significa que no estamos haciendo una búsqueda de rango, queremos un valor coincidente exacto. En nuestro caso, queremos hacer una búsqueda de rango y usaremos VERDADERO para nuestra fórmula.

Hagamos un intento.

Versión simple – Año específico

Antes de escribir nuestra fórmula, dejemos clara nuestra tarea. Tenemos una lista de transacciones. Cada registro de transacción incluye la fecha de la transacción. Necesitamos resumir las transacciones, pero nuestra empresa tiene un cierre de año fiscal 6/30. Dado que Excel solo admite grupos de fechas de años calendario, necesitaremos usar la función BUSCARV para traducir las fechas de las transacciones en grupos de períodos fiscales. Para preparar nuestra hoja de trabajo, configuramos una pequeña tabla de búsqueda que Excel puede usar para realizar la traducción. Mientras calentamos, podemos visualizar la pequeña tabla de búsqueda de la siguiente manera:

Tenemos una etiqueta desde la fecha, hasta la fecha y un cuarto. Buscaremos la fecha de la transacción en la tabla para determinar la etiqueta del trimestre.

Suponiendo que la fecha de nuestra primera transacción se almacenó en la celda C15 y que la pequeña tabla de búsqueda se llama Tabla1, entonces la siguiente fórmula funcionaría:

=BUSCARV(C15, Tabla1, 3, VERDADERO)

Dónde:

• C15 es el valor de búsqueda, la fecha de la transacción
• Tabla1 es el rango de búsqueda, la tabla de cuartos
• 3 es la columna que tiene el valor a devolver, la etiqueta del cuarto, que es la tercera columna de la tabla
• VERDADERO estamos haciendo una búsqueda de rango

Los resultados se muestran a continuación:

Básicamente, la fórmula encuentra la fecha en el rango de búsqueda y devuelve la etiqueta del trimestre.

Pero podemos hacerlo aún mejor. Cuando BUSCARV encuentra la fecha de la transacción, en realidad solo busca en la primera columna, la columna “de”, conocida como columna de búsqueda. Eso significa que ignora la segunda columna, la columna “a”. Aunque a los humanos les encanta ver ambos puntos finales de fecha, Excel no los necesita. Entonces, podemos simplificar nuestra tabla de búsqueda excluyendo la columna “a”, como se muestra a continuación:

Esto funciona porque la función BUSCARV solo busca su valor coincidente en la primera columna, la columna de búsqueda, e ignora otras columnas. Después de encontrar una coincidencia, la función se desliza hacia la derecha para devolver un resultado relacionado, en nuestro caso, la etiqueta del cuarto.

Pregunta rápida para usted: ¿es necesario ordenar la tabla de búsqueda de trimestres? Sí, si el cuarto argumento de BUSCARV es VERDADERO, entonces el rango de búsqueda debe ordenarse en orden ascendente según la columna de búsqueda para que devuelva un resultado confiable.

Versión avanzada: todos los años

Podemos hacerlo incluso mejor que la fórmula anterior. La tabla de búsqueda anterior funciona para un año fiscal específico, pero requiere que agreguemos nuevas filas cada año posterior. Dado que nuestro objetivo es eliminar los pasos manuales de los procesos recurrentes, necesitaremos encontrar una manera de crear una tabla de búsqueda que funcione para todos los años.

Afortunadamente, Excel proporciona la función MES que extrae el número del mes de una fecha. Al anidar la función MES en nuestra función BUSCARV, podemos simplificar nuestra tabla de búsqueda para incluir solo meses, como se muestra a continuación.

Podemos usar la función MES para sacar el mes de la fecha, como se muestra a continuación:

Recuerde, la tabla de búsqueda debe ordenarse en orden ascendente para que la función devuelva un resultado confiable. Dado que esta tabla incluye solo meses, se puede utilizar para transacciones que abarquen muchos años fiscales.

Y ese amigo mío es mi método preferido para traducir fechas de transacciones en períodos fiscales. Si tiene otros métodos, compártalos publicando un comentario a continuación… ¡gracias!

Recursos adicionales

• Archivo de muestra: Períodos Fiscales
• Tablas: esta publicación se basa en la función de tabla… si aún no has jugado con ella, consulta las publicaciones de blog relacionadas con tablas