martes, 17 de noviembre de 2020

Acumulado hasta la fecha (YTD) condicionado

Si trabajas en entornos contables, financieros o simplemente desarrollas informes de algún tipo, seguro que en muchas ocasiones habrás necesitado obtener un importe acumulado hasta una fecha dada (en inglés YTD - year to date).
Hoy iremos un paso más allá mostrando alternativas para recuperar ese acumulado basado en un criterio!
Acumulado hasta la fecha (YTD) condicionado

Se trata en definitiva de calcular la suma de todos los ingresos (costes variables o fijos-según celda P2) de nuestro grupo de empresas desde enero hasta el mes informado en la celda Q2.

Comezaremos con la 'superfunción' SUMAPRODUCTO, así en P4 añadimos:
=SUMAPRODUCTO(
(DESREF(C3;;;CONTARA(C3:C23);COINCIDIR(Q2;C2:N2;0)))
*(B3:B23=P2))

Acumulado hasta la fecha (YTD) condicionado

Esta fórmula devuelve un rango de 21 filas 8tantas como tiene nuestra plantilla, y un número de columnas igual al equivalente al mes buscado (4 para nuestro ejemplo donde buscamos 'abr': (DESREF(C3;;;CONTARA(C3:C23);COINCIDIR(Q2;C2:N2;0)))
Acabamos multiplicando columna a columna, cada una de las cuatro, por el vector de VERDADEROS y FALSOS que nos devuelve la comparativa con el concepto buscado en P2.
SUMAPRODUCTO termina acumulando el resultado de esos productos..., tal como necesitábamos.

Otra posibilidad sería emplear la forma matricial siguiente. En P8:
=SUMA(SI(B3:B23=P2;DESREF(C3;;;CONTARA(C3:C23);COINCIDIR(Q2;C2:N2;0))))
Acumulado hasta la fecha (YTD) condicionado

De forma similar a la anterior DESREF devuelve una matriz de 21 filas por 4 columnas... a la cual aplicamos un SI condicional para discrimina solo aquellas filas que coincidan con la celda P2 de 'ingreso'
Si evaluamos la matriz veríamos que sumaríamos..
Acumulado hasta la fecha (YTD) condicionado

Sumando por tanto solo los importes buscados...

Otra opción es insertar en la celda P12 y basándonos en una suma por columnas, del rango C25:N25, la siguiente fórmula:
=SUMA(C25:INDICE(C25:N25;1;COINCIDIR(Q2;C2:N2;0)))
donde nos aprovechamos del uso 'especial' de INDICE para recuperar una celda como referencia.

Y finalmente (obviamente existen más alternativas, pero en algún momento hay que parar) en la celda P16 insertamos la fórmula desbordada:
=SUMA(FILTRAR(
FILTRAR($C$3:$N$23;$B$3:$B$23=P2);
SECUENCIA(1;12)<=COINCIDIR($Q$2;$C$2:$N$2;0)))

donde el uso de FILTRAR se encarga de recuperar los registros de los meses y conceptos necesarios.
Acumulado hasta la fecha (YTD) condicionado

Hacemos uso del truco del doble FILTRAR explicado aquí.

Estan son algunas de las mejores maneras de recuperar nuestra ansiado YTD condicionado.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.