La función DESREF es una de las funciones de búsqueda y referencia más potentes y flexibles de Excel. Devuelve una referencia a un rango que se encuentra a un número especificado de filas y columnas de una celda o rango de celdas de referencia. La referencia devuelta puede ser una sola celda o un rango de varias celdas. Es especialmente útil para crear rangos dinámicos que se ajustan automáticamente a medida que se añaden o eliminan datos.

Una de sus características más importantes es que es una función volátil, lo que significa que se recalcula cada vez que se produce un cambio en la hoja de cálculo. Esto puede afectar al rendimiento en libros de trabajo grandes y complejos.

Sintaxis

=DESREF(ref, filas, columnas, [alto], [ancho])

La función DESREF tiene los siguientes argumentos:

• ref: Es la celda o rango de celdas adyacentes que sirve como punto de partida para el desplazamiento. Obligatorio.
• filas: El número de filas, hacia arriba o hacia abajo, a las que se desea hacer referencia en la celda superior izquierda. Un número positivo indica un desplazamiento hacia abajo, mientras que un número negativo indica un desplazamiento hacia arriba. Obligatorio.
• columnas: El número de columnas, hacia la izquierda o la derecha, a las que se desea hacer referencia en la celda superior izquierda. Un número positivo indica un desplazamiento hacia la derecha, y un número negativo indica un desplazamiento hacia la izquierda. Obligatorio.
• alto: El alto, en número de filas, que se desea que tenga la referencia devuelta. Debe ser un número positivo. Opcional. Si se omite, la referencia devuelta tendrá el mismo alto que ref.
• ancho: El ancho, en número de columnas, que se desea que tenga la referencia devuelta. Debe ser un número positivo. Opcional. Si se omite, la referencia devuelta tendrá el mismo ancho que ref.

Ejemplos

Para los siguientes ejemplos, usaremos la tabla de datos de ventas trimestrales:

Ejemplo 1: Devolver el valor de una celda específica

Para obtener las ventas del Q3 de la región Este, partimos de la celda A1, nos desplazamos 3 filas hacia abajo y 3 columnas a la derecha.

=DESREF(A1; 3; 3)

Resultado: 2300. La fórmula devuelve el valor de la celda D4.

Ejemplo 2: Sumar un rango dinámico de celdas

Se pueden sumar todas las ventas del Q1 (columna B). Usaremos CONTARA para determinar dinámicamente cuántas filas de datos hay.

=SUMA(DESREF(B1; 1; 0; CONTARA(A2:A100)))

Desglose de la fórmula:

• DESREF(B1; 1; 0; CONTARA(A2:A100)):
  • Parte de B1.
  • Se desplaza 1 fila hacia abajo (a B2).
  • Se desplaza 0 columnas.
  • El alto del rango será el resultado de CONTARA(A2:A100), que en este caso es 4 (Norte, Sur, Este, Oeste).
  • Esto crea una referencia al rango B2:B5.
• SUMA(B2:B5): Suma los valores de dicho rango.

Resultado: 6700 (la suma de 1500 + 1200 + 2200 + 1800).

Ejemplo 3: Devolver un rango completo a otra función

Podemos obtener la media de ventas de la región Sur. Para ello, creamos una referencia a todo el rango de sus ventas (Q1 a Q4).

=PROMEDIO(DESREF(A3; 0; 1; 1; 4))

Desglose de la fórmula:

• DESREF(A3; 0; 1; 1; 4):
  • Parte de A3 («Sur»).
  • Se desplaza 0 filas.
  • Se desplaza 1 columna a la derecha (a B3).
  • Define un alto de 1 fila.
  • Define un ancho de 4 columnas.
  • Esto crea una referencia al rango B3:E3.
• PROMEDIO(B3:E3): Calcula el promedio de dicho rango.

Resultado: 1387.5 (el promedio de 1200, 1300, 1450 y 1600).

Observaciones

La principal consideración al usar DESREF es su naturaleza volátil. Esto significa que Excel recalculará cualquier fórmula que contenga DESREF cada vez que se realice un cambio en cualquier celda del libro, lo que puede ralentizar significativamente el rendimiento en hojas de cálculo grandes. Siempre que sea posible, es recomendable usar alternativas no volátiles como la combinación de INDICE y COINCIDIR, o la función BUSCARX en versiones más recientes de Excel.

Errores comunes

• #¡REF!: Ocurre si la referencia resultante del desplazamiento se encuentra fuera de los límites de la hoja de cálculo. Por ejemplo, =DESREF(A1; -1; 0) intentaría hacer referencia a una fila por encima de la fila 1, lo cual es imposible.
• #¡VALOR!: Ocurre si alguno de los argumentos no numéricos (como alto o ancho) es un valor no válido (por ejemplo, un número negativo, cero o texto).

Disponibilidad por versión de Excel

DESREF es una función fundamental y ha estado presente en Excel desde sus primeras versiones. Está disponible en todas las versiones, incluyendo Excel 2003, 2007, 2010, 2013, 2016, 2019, 2021 y Microsoft 365.

Compatibilidad

Funciones Relacionadas

• INDICE: Una alternativa no volátil muy potente que devuelve el valor o la referencia de una celda en la intersección de una fila y columna particular en un rango dado.
• INDIRECTO: Devuelve una referencia especificada por un valor de texto. Al igual que DESREF, también es una función volátil.
• BUSCARX: Disponible en Microsoft 365 y versiones recientes, es una función de búsqueda moderna que puede devolver rangos y es generalmente más eficiente y fácil de usar.