=DESREF(A1;3;2) devuelve la celda que está 3 filas más abajo y 2 columnas a la derecha de A1, es decir, C4. Dale además un alto y un ancho y devuelve un rango entero, que es para lo que más se usa DESREF: totales y promedios sobre un rango que se mueve o crece. DESREF se llama OFFSET en inglés, y la tabla muestra la fórmula en inglés; también acepta las fórmulas escritas en español, con punto y coma (o con comas, como en México).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=DESREF(A1;E2;F2)3 filas hacia abajo y 2 a la derecha de A1 caen en C4, el precio de Carrot, $0.80. Pon Cols en 0 para obtener el nombre Carrot, o Rows en 5 para la fila de Milk. Las filas y columnas pueden ser negativas para moverse hacia arriba o hacia atrás, y salirse por el borde superior o lateral de la hoja da #¡REF! (#REF! en inglés; la tabla muestra los nombres de error en inglés).
Sintaxis de DESREF
=OFFSET(reference, rows, cols, [height], [width])
reference(ref): la celda (o rango) inicial.rows,cols(filas, columnas): cuánto moverse. 0 significa quedarse.height,width(alto, ancho): el tamaño del rango que se devuelve, contado desde la celda a la que se llega. Si se omiten, son el tamaño dereference.
Sola en una celda, una DESREF que devuelve varias celdas se desborda en Excel 365; las versiones antiguas suelen mostrar #¡VALOR! (#VALUE! en inglés). Dentro de SUMA, PROMEDIO, CONTAR o MAX funciona como un rango.
Sumar las últimas N filas
El trabajo clásico de DESREF: un total que siempre cubre las filas más recientes, por muchas que se añadan. CONTAR (COUNT en inglés) averigua cuántos valores hay, DESREF baja hasta el primero de los últimos N y el alto toma N filas.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=SUMA(DESREF(B1;CONTAR(B2:B13)-E2+1;0;E2;1))Hay 7 valores, así que DESREF empieza 7-3+1, 5 filas por debajo de B1, en B6, y toma 3 filas: de May a Jul, 14,900. En Excel en español la fórmula de F2 es =SUMA(DESREF(B1;CONTAR(B2:B13)-E2+1;0;E2;1)). Escribe 4900 en B9 (agosto) y el total pasa a Jun, Jul y Aug, porque CONTAR encuentra ahora 8. El rango B2:B13 deja sitio para el resto del año. La columna no debe tener celdas vacías en medio, o CONTAR se queda corto y la ventana cae en el sitio equivocado.
Un promedio móvil
Copiada hacia abajo en una columna, DESREF con un desplazamiento de filas negativo da a cada fila una ventana de las filas de arriba: aquí el promedio del mes actual y de los dos anteriores.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=PROMEDIO(DESREF(B4;-2;0;3;1))C4 promedia B2:B4 (de Jan a Mar), 4,300. Cada fila de abajo mueve la ventana una posición. En Excel en español la fórmula de C4 es =PROMEDIO(DESREF(B4;-2;0;3;1)). Cambia el 3 por 6 y el -2 por -5 para un promedio de seis meses (entonces la fórmula empieza en la fila 7). Este caso concreto no necesita DESREF: =PROMEDIO(B2:B4) copiada hacia abajo desde C4 hace lo mismo, porque las referencias relativas ya se mueven. DESREF se gana su sitio cuando el tamaño de la ventana sale de una celda.
Por qué INDICE suele ser mejor opción
DESREF es volátil: Excel recalcula cada DESREF después de cualquier edición en cualquier parte del libro, porque no puede saber de antemano a qué celdas apuntará. Una hoja con miles de ellas se vuelve lenta. INDICE (INDEX en inglés) también devuelve una referencia, y un rango escrito como inicio:INDICE(...) crece igual sin ser volátil:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
En Excel en español: =SUMA(DESREF(B2;0;0;E2;1)) y =SUMA(B2:INDICE(B2:B13;E2)). Las dos leen las primeras E2 filas de la columna. DESREF además es más difícil de auditar: Rastrear precedentes y los contornos de colores que Excel dibuja mientras editas la fórmula muestran la celda inicial y los argumentos, no el rango que DESREF termina devolviendo. Usa DESREF para un modelo rápido o el rango de un gráfico; prefiere INDICE en libros grandes. INDICE explica más sobre devolver rangos, e INDIRECTO es la otra función de referencia volátil.
Práctica: total de los primeros N meses
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Tu turno: En F2, usa DESREF dentro de SUMA para sumar los primeros N meses, donde N está en E2.
Preguntas frecuentes
¿Qué hace DESREF en Excel?
Devuelve una referencia que está a un número dado de filas y columnas de una celda inicial, y opcionalmente con otro tamaño. =DESREF(A1;3;2) es la celda 3 filas más abajo y 2 columnas a la derecha de A1, es decir, C4.
¿Cómo sumo las últimas N filas en Excel?
Empieza en el encabezado y baja hasta el primero de los últimos N valores: =SUMA(DESREF(B1;CONTAR(B2:B100)-N+1;0;N;1)). CONTAR averigua cuántos valores hay, y el alto N toma esa cantidad de filas. Solo funciona cuando la columna no tiene huecos.
¿Por qué DESREF es volátil?
Excel recalcula cada DESREF después de cualquier cambio en el libro, porque las celdas a las que apunta solo se conocen después de ejecutarla. En libros grandes eso ralentiza todo. Un rango construido con INDICE, como B2:INDICE(B2:B100;N), hace el mismo trabajo sin ser volátil.
¿Cuáles son los argumentos de DESREF?
DESREF(ref; filas; columnas; [alto]; [ancho]): la celda inicial, cuántas filas hacia abajo (negativo es hacia arriba), cuántas columnas a la derecha (negativo es hacia atrás) y, opcionalmente, el tamaño del rango que se devuelve.
