Creo que no he encontrado nunca mejor explicación para hablar de este tema que el siguiente ejemplo, ¡descárgalo aquí!
Si has visto el archivo, veras que la formula DESREF funciona como una referencia móvil con la que, en lugar de seleccionar una celda, le daremos a Excel un conjunto de coordenadas para indicar el valor que buscamos.
Hablemos en español. Supongamos que queremos obtener el promedio de los últimos 7 días de ventas según avancen los días en una tabla como la de abajo.
Con la formula normal de promedio deberíamos estar cambiando diario el rango para tomar el nuevo día, lo cual puede llegar a ocasionar bastantes errores si es que se nos olvida realizar esa acción. Para resolverlo, podemos usar DESREF con su modalidad de rango y hacer algo así:
=PROMEDIO(DESREF(C3,CONTARA($C$3:$C$1000)-7,0,7,1))
Si lo explicamos en mortal va:
- DESREF posiciona la referencia en la celda C3.
- CONTARA va a ir a decirnos cuantas celdas con valores tenemos en el rango de C3:C1000 y le restamos 7 para que, de la última celda con valor mueva nuestro cursor 7 días atrás.
- Al definirle un alto de rango de 7 por un ancho de rango de 1 a la formula, le estamos diciendo que: se posicione en C3, de ahí avance hasta la última celda con datos y retroceda 7 días y, a partir de ahí, tome un rango de 7 filas hacia abajo.
- Por lo tanto, la formula PROMEDIO tomara el rango (para nuestro ejemplo) de C13:C19 (que es lo que devuelve DESREF).
Ahora, cada vez que un día nuevo sea agregado con sus respectivos valores podremos observar como el promedio cambia de manera automática gracias a que hemos creado un rango dinámico.
¡La fórmula DESREF puede ser una maravilla usada sabiamente! Solo toma en cuenta que es muy sensible en cuanto a su utilización, ya que agregar o quitar celdas, filas y columnas puede afectar seriamente su funcionamiento, adicional de que es una fórmula que se calcula con cada nueva acción que hagas, por lo que es muy pesada y habrá que evitar usar muchas al mismo tiempo.
Un buen sustituto para un libro con muchas referencias móviles puede ser INDICE+COINCIDIR o posiblemente DIRECCION, así que ¡prueba con todas hasta que una te convenza! Cuando tengas tu favorita ven y compártela con todos.
