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).
Este comentario ha sido eliminado por el autor.
ResponderEliminarBuenas tardes Ismael, te hago una consulta similar a este caso... tiene una vuelta mas de rosca creo...
ResponderEliminartengo en una sola columna el total de cupones de tarjeta de creditos cobrados y en la misma columna pero con sigo contrario, los importes de los saldos que deposito el banco... el saldo se compone de varios cupones...
me gustaría poder saber que cupones de la lista no se cobraron y por ende conforman mi saldo al final de mes... un ejemplo pequeño seria el siguiente:
cupones cobrados 1,22
cupones cobrados 2,33
cupones cobrados 3,55
cupones cobrados 4,2
cupones cobrados 5,89
cupones cobrados 6,77
cupones cobrados 7,25
cupones cobrados 8,99
cupones cobrados 9,3
cupones cobrados 10
depositados -1,22
depositados -8,99
depositados -10,09
depositados -20,32
total a fin de mes: 18,88 (que se componen de 2.33 + 7.25 + 9.3)
Hola,
Eliminaresto es más un problema de conciliación que lo que se expone en el post
Echa un vistazo a
http://excelforo.blogspot.com.es/2011/12/conciliar-en-excel-por-aproximacion-con.html
Espero puedas sacar algo de ahí...
El tema es complicado
Saludos
buenisimo! re mil agradecido siempre! saludos cordiales!
Eliminar