Periodificar Fechas con Fórmulas

Descargar el Archivo

Hoy, como no podría ser de otra manera, comienzo este post felicitando el cumpleaños a mi querido hermano Carlos (que cumple los "tres patitos": 2 x 22...) ¡FELICIDADES HERMANO!

"Tengo una tabla con fechas y necesitaría agruparlas por trimestres en un nuevo campo para poder aplicar la herramienta de Subtotales"

Para solucionar este problema sin hacer uso de Tablas Dinámicas utilizaremos las funciones MES, TRUNCAR y BUSCARV.

Como siempre, partamos de un ejemplo:

Lo que pretendemos conseguir es que en el rango C13:C36 nos aparezca el número de trimestre que corresponde a cada una de las fechas. Para ello escribimos en C13 la siguiente fórmula:

=TRUNCAR((MES($A13)+2)/3)

La función TRUNCAR nos devuelve la parte entera de un número. Tal y como explica la ayuda de excel, TRUNCAR y ENTERO son similares, ya que ambos devuelven enteros. TRUNCAR suprime la parte fraccionaria del número. ENTERO redondea los números al entero menor más próximo, según el valor de la porción fraccionaria del número. ENTERO y TRUNCAR son diferentes solamente cuando se usan números negativos: TRUNCAR(-4,3) devuelve -4, pero ENTERO(-4,3) devuelve -5, ya que -5 es el número entero menor más cercano.

El motivo de utilizar esta función es porque si observamos los resultados de obtener el número de mes (función MES), sumarle dos y dividirlo por tres comprobamos que no todos son exactos:

Para evitar los decimales usamos la función TRUNCAR.

Una vez generado este nuevo campo ya podríamos aplicar la herramienta SUBTOTALES y agrupar por trimestres. Pero si lo que queremos es poder agrupar en base a otras periodificaciones entonces tendremos que modificar la fórmula.

Hemos utilizado la fórmula =(MES($A13)+2)/3) Fíjese que al tratarse de trimestres hemos utilizado como denominador el 3 y como sumando el 2 (3-1=2). Cuando se trate de bimestres utilizaremos como denominador el 2 y como sumando el 1 (2-1=1); con los cuatrimestres utilizaremos como denominador el 4 y como sumando el 3 (4-1=3); y con semestres el 6 y como sumando el 5 (6-1=5). Como puede ver el patrón de la fórmula es muy sencillo.

Para hacer el modelo "flexible" preparamos la hoja de la siguiente forma:

1. Borramos la solución anterior. Vamos a poner la periodificación a continuación de la fecha. Para ello insertamos una columna entre la A y la columna B (en la columna C aparecerán ahora las dietas).

2. Generamos la siguiente entrada de datos:

3. Le damos el nombre, por ejemplo, Agrupacion al rango A3:B6 (lo podemos hacer seleccionando dicho rango; haciendo clic en el cuadro de nombres - a la izquierda de la barra de fórmulas- y escribiendo dicho nombre y pulsando después Enter).

4. Nos situamos en la celda B8 y vamos a Datos/Validación de datos/Permitir/Lista. Como lista seleccionamos el rango A3:A6.

5. Nos situamos en la celda B13 y escribimos la siguiente fórmula:

=TRUNCAR((MES($A13)+BUSCARV($B$8;agrupacion;2;FALSO)-1)/BUSCARV($B$8;agrupacion;2;FALSO))

Si se fija lo único que estamos haciendo es dejar como variable el tipo de agrupación que deseamos realizar y aplicar, por medio de la función BUSCARV, el patrón comentado anteriormente.

6. En la celda B12 escribimos la fórmula =B8

De nuevo podemos aplicar la herramienta SUBTOTALES sin problema: