Hace algunas semanas un lector planteaba una duda sobre cómo localizar la suma acumulada creciente de un dato sobre un rango de celdas...
La idea es a partir de un rango de celdas C3:C14, y dado un valor acumulado en F3, recuperar el mes al que corresponde dicho importe acumulado (en F3).. directamente sin necesidad de cálculos auxiliares.
En la imagen anterior aparece el cálculo acumulado en D3:D14 para facilitar la comprobación.
haremos uso de una combinación bastante potente y poco conocida, que en este blog ya hemos empleado algunas veces (ver ejemplo1 y ejemplo2).
Hablo del uso matricial de las funciones SUBTOTALES y DESREF.
Para resolver nuestro problema escribimos en G3:
=INDICE(B3:B14;COINCIDIR(F3;SUBTOTALES(109;DESREF(C$3;0;0;FILA(INDIRECTO("1:12"));1));0))
y ejecutamos matricialemente (validando con Ctrl+Mayus+Enter).
La clave del asunto es cómo se obtiene el rango de acumulados creciente por mes con la fórmula:
SUBTOTALES(109;DESREF(C$3;0;0;FILA(INDIRECTO("1:12"));1))
con la que indicamos que vaya sumando/acumulando desde C3 los importes dados, hasta una, dos, tres, ...doce filas por debajo.
Esta sería la matriz devuelta por la fórmula anterior:
{500;886;1695;2386;2666;2848;3074;3843;4132;4448;4565;5428}
que coincide con la calculada en las celdas anexas (D3:D14)...
A partir de aquí el cálculo es simple, con COINCIDIR obtenemos la fila correspondiente, la cual trasladamos como argumento de INDICE sobre el rango de los conceptos o meses B3:B14.
Una vez obtenido la matriz con los importes acumulados, otro cálculo quizá con algo más de sentido (y más simple) sería recuperar el acumulado a una fecha dada, por ejemplo al mes 3, si F7 escribimos 3 :
=INDICE(SUBTOTALES(109;DESREF(C$3;0;0;FILA(INDIRECTO("1:12"));1));F7)
y ejecutamos matricialemente (validando con Ctrl+Mayus+Enter).
La idea es a partir de un rango de celdas C3:C14, y dado un valor acumulado en F3, recuperar el mes al que corresponde dicho importe acumulado (en F3).. directamente sin necesidad de cálculos auxiliares.
En la imagen anterior aparece el cálculo acumulado en D3:D14 para facilitar la comprobación.
haremos uso de una combinación bastante potente y poco conocida, que en este blog ya hemos empleado algunas veces (ver ejemplo1 y ejemplo2).
Hablo del uso matricial de las funciones SUBTOTALES y DESREF.
Para resolver nuestro problema escribimos en G3:
=INDICE(B3:B14;COINCIDIR(F3;SUBTOTALES(109;DESREF(C$3;0;0;FILA(INDIRECTO("1:12"));1));0))
y ejecutamos matricialemente (validando con Ctrl+Mayus+Enter).
La clave del asunto es cómo se obtiene el rango de acumulados creciente por mes con la fórmula:
SUBTOTALES(109;DESREF(C$3;0;0;FILA(INDIRECTO("1:12"));1))
con la que indicamos que vaya sumando/acumulando desde C3 los importes dados, hasta una, dos, tres, ...doce filas por debajo.
Esta sería la matriz devuelta por la fórmula anterior:
{500;886;1695;2386;2666;2848;3074;3843;4132;4448;4565;5428}
que coincide con la calculada en las celdas anexas (D3:D14)...
A partir de aquí el cálculo es simple, con COINCIDIR obtenemos la fila correspondiente, la cual trasladamos como argumento de INDICE sobre el rango de los conceptos o meses B3:B14.
Una vez obtenido la matriz con los importes acumulados, otro cálculo quizá con algo más de sentido (y más simple) sería recuperar el acumulado a una fecha dada, por ejemplo al mes 3, si F7 escribimos 3 :
=INDICE(SUBTOTALES(109;DESREF(C$3;0;0;FILA(INDIRECTO("1:12"));1));F7)
y ejecutamos matricialemente (validando con Ctrl+Mayus+Enter).
Excel
via [EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS] [http://ift.tt/2ioIbtX]
December 5, 2017 at 05:01AM
No hay comentarios.:
Publicar un comentario