Un usuario me consultaba por la posibilidad de generar con DESREF una rango variable de acuerdo a un criterio particular... que siempre devolviera los datos de las últimos cinco periodos.
El comentario del usuario:
Para resolver este tema nos apoyaremos, entre otros post, en este donde se explicaba como localizar la última posición con valor de un rango... (ver post).
El modelo sobre el que trabajar será:
En nuestra celda O3 tenemos la fórmula buscada:
=SUMA(DESREF(B3;0;SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5);1;5))
comprobando en la celda contigua, mediante un suma directa que el resultado es correcto.
Pero, ¿cómo hemos montado esta fórmula?.
Lo primero ha sido localizar, dentro de nuestro rango de estudio B3:M3, cuál es la última celda ocupada, i.e., a qué mes corresponde.
Por ejemplo, sobre las ventas previstas de nuestro ejercicio, la función
COINCIDIR(9,9E+307;B3:M3;1)
devuelve un 9; que efectivamente corresponde con el último mes con dato.
A continuación restamos 5, ya que es precisamente el punto de partida requerido (cinco últimos periodos).
Para que la fórmula sea válida en cualquier caso, incluso cuando no hay cinco periodos completos,
montamos el condicional:
SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5)
que básicamente nos condiciona a que si hay menos de cinco datos, retorna un valor cero (a continuación veremos su significado) o de lo contrario el resultado de la diferencia anterior comentada (COINCIDIR(9,9E+307;B3:M3;1)-5)
El uso de este cero o de la diferencia, según el caso, aplicará como argumento 'columnas' de la función DESREF.
Recordemos la sintáxis:
DESREF(ancla, filas, columnas, [alto], [ancho])
Es decir, este valor condicionado, nos pautará el inicio del rango que estamos construyendo con DESREF.
El resto de la función DESREF está claro:
DESREF(B3;0;SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5);1;5)
El primer argumento, la celda B3, indica desde donde empieza a contar...el ancla.
Desde B3 me desplazo 0 filas hacia arriba o hacia abajo (esto es, me quedo en la fila 3).
Y desde B3 me desplazo el resultado del condicional (el cero o el resultado de la resta) hacia la derecha (esto es, columnas B, C, D, E, ..., M).
Los dos últimos argumentos empleados nos determinan el alto y ancho del rango variable que estamos generando.
El cuarto argumento, valor 1, nos dice que el rango tendrá una fila de alto; mientras que el quinto argumento nos marca el ancho del rango, en el ejercicio, cinco columnas (las requeridas en el planteamiento).
El último paso es aplicar el cálculo que necesitemos sobre dicho rango variable generado con DESREF; en el ejemplo la función SUMA.
El comentario del usuario:
| [...]He visto en la red que los ejemplos para rangos dinámicos son para series en columnas, es muy claro y se entiende, pero lo que no veo en la red y me canse de buscar y peor aún he intentado hacerlo es tener un rango dinámico pero para series que están en fila Cómo lo haría claro esta usando la función DESREF, u otra misma ya sea para calcular promedio y sumas de las 5 últimos días ya que esto va ir creciendo y gráficos igual[...] |
Para resolver este tema nos apoyaremos, entre otros post, en este donde se explicaba como localizar la última posición con valor de un rango... (ver post).
El modelo sobre el que trabajar será:
En nuestra celda O3 tenemos la fórmula buscada:
=SUMA(DESREF(B3;0;SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5);1;5))
comprobando en la celda contigua, mediante un suma directa que el resultado es correcto.
Pero, ¿cómo hemos montado esta fórmula?.
Lo primero ha sido localizar, dentro de nuestro rango de estudio B3:M3, cuál es la última celda ocupada, i.e., a qué mes corresponde.
Por ejemplo, sobre las ventas previstas de nuestro ejercicio, la función
COINCIDIR(9,9E+307;B3:M3;1)
devuelve un 9; que efectivamente corresponde con el último mes con dato.
A continuación restamos 5, ya que es precisamente el punto de partida requerido (cinco últimos periodos).
Para que la fórmula sea válida en cualquier caso, incluso cuando no hay cinco periodos completos,
montamos el condicional:
SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5)
que básicamente nos condiciona a que si hay menos de cinco datos, retorna un valor cero (a continuación veremos su significado) o de lo contrario el resultado de la diferencia anterior comentada (COINCIDIR(9,9E+307;B3:M3;1)-5)
El uso de este cero o de la diferencia, según el caso, aplicará como argumento 'columnas' de la función DESREF.
Recordemos la sintáxis:
DESREF(ancla, filas, columnas, [alto], [ancho])
Es decir, este valor condicionado, nos pautará el inicio del rango que estamos construyendo con DESREF.
El resto de la función DESREF está claro:
DESREF(B3;0;SI(CONTARA(B3:M3)<5;0;COINCIDIR(9,9E+307;B3:M3;1)-5);1;5)
El primer argumento, la celda B3, indica desde donde empieza a contar...el ancla.
Desde B3 me desplazo 0 filas hacia arriba o hacia abajo (esto es, me quedo en la fila 3).
Y desde B3 me desplazo el resultado del condicional (el cero o el resultado de la resta) hacia la derecha (esto es, columnas B, C, D, E, ..., M).
Los dos últimos argumentos empleados nos determinan el alto y ancho del rango variable que estamos generando.
El cuarto argumento, valor 1, nos dice que el rango tendrá una fila de alto; mientras que el quinto argumento nos marca el ancho del rango, en el ejercicio, cinco columnas (las requeridas en el planteamiento).
El último paso es aplicar el cálculo que necesitemos sobre dicho rango variable generado con DESREF; en el ejemplo la función SUMA.
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.