Cómo hacer rangos dinámicos con la función DESREF de Excel

La función DESREF de Excel es una de las menos conocidas por los usuarios habituales de Excel, pero se trata de una de las funciones a la que más partido podemos sacar para, por ejemplo, hacer rangos dinámicos, entre otros. La función DESREF devuelve una referencia a un rango desplazado respecto a una referencia inicial, según un número de filas y columnas especificado en la propia función.

La sintaxis de la función es la siguiente:

=DESREF(Referencia; Filas; Columnas; [Alto]; [Ancho])

Donde los argumentos indican:

• Referencia: Es la celda o rango de celdas inicial desde el que aplicaremos el desplazamiento.
• Filas: Es el número de filas que el rango resultante se desplazará hacia arriba (positivo) o hacia abajo (negativo), respecto a la referencia inicial.
• Columnas: Es el número de columnas que el rango resultante se desplazará hacia la derecha (positivo) o hacia la izquierda (negativo), respecto a la referencia inicial.
• [Alto] (Opcional): Este argumento opcional nos permite asignar el número de filas que tendrá el rango resultante.
• [Ancho] (Opcional): Este argumento opcional nos permite asignar el número de columnas que tendrá el rango resultante.

Una de las cosas que podemos hacer con la función DESREF es hacer rangos dinámicos, por ejemplo, sumar los N últimos datos de una lista que actualizamos con frecuencia. Imaginemos que registramos en Excel las ventas diarias de un ecommerce y nos interesa saber lo facturado en los últimos 5 días, utilizaríamos la función DESREF combinada con la función SUMA.

Para registrar las ventas usaremos la siguiente estructura de hoja: en el rango B2:B15 tenemos las ventas acompañadas de la fecha en el rango A2:A15. En la celda E5 calcularemos las ventas de los últimos 5 días:

La sintaxis de la función es:

=SUMA(DESREF(B2;CONTAR($B$2:$B$20000)-5;0;5;1))

La función SUMA utilizará el rango obtenido por DESREF para hacer la suma, por tanto, analicemos los argumentos de DESREF:

• Referencia: B2, por es donde comienza la lista de ventas.
• Filas: CONTAR($B$2:$B$20000)-5. Aquí está la clave, Excel contará cuantos datos hay en el rango B2:B20000 y le restará 5, de forma que desplazará la referencia hasta los últimos 5 datos.
• Columnas: el valor es 0 porque no queremos desplazamiento lateral.
• [Alto] (Opcional): el valor es 5 porque el rango resultante tendrá 5 filas, es decir, las últimas 5 ventas diarias.
• [Ancho] (Opcional): el valor es 1 porque el ancho del rango resultante es de una sola columna, la de las ventas.

El resultado de la fórmula es 78.382,13€, que corresponde a las ventas entre el 5 y el 9 de Enero de 2025. Si añadimos un dato más a la lista, por ejemplo, 15.000€ de ventas el día 10 de Enero, el resultado pasará a ser de 79.958,72€ porque la fórmula elimina el dato del día 5 e incorpora el del día 10: