4.5Funciones avanzadas: DESREF, INDIRECTO, AGREGAR
Lo esencial en 30 segundos
- DESREF = rango que se mueve: referencia desplazada y dimensionable (ej. 12 últimos meses móviles) — pero volátil, a limitar en libros grandes.
- INDIRECTO = texto que se convierte en referencia: =INDIRECTO("Ventas!B"&L1) apunta a una dirección calculada — potente pero volátil y frágil al renombrar.
- Reflejo de Wassim: para totalizar a pesar de errores y filtros, AGREGAR (no volátil, ignora errores/ocultos) — y buscar primero una alternativa robusta (ÍNDICE, tabla estructurada).
Mini-caso hilado — Wassim totaliza una tabla filtrada llena de errores
Wassim, contable en Argel, debe totalizar una columna de importes que contiene #N/A, con una tabla filtrada por Argel: =SUMA devuelve #N/A. Usa =AGREGAR(9;6;B2:B100) para ignorar los errores y luego la opción filas visibles para el filtrado. Para su síntesis móvil de los 12 últimos meses, construye un rango DESREF — y luego lo sustituye por una tabla estructurada, menos costosa. Último hito del hilo conductor: robusto donde SUMA fallaba, sin ralentizar el libro.
Último caso del hilo conductor 4.1-4.5 — Wassim cierra su nivel: importar, modelar, dinamizar, automatizar, fiabilizar.
1. DESREF: un rango que se mueve
DESREF construye una referencia desplazada respecto a un punto de partida (n filas, n columnas), y de tamaño parametrizable. Útil para rangos dinámicos (los 12 últimos meses móviles de Wassim). Pero es una función volátil: recalcula sin cesar y puede ralentizar un libro grande — a menudo sustituible por una tabla estructurada o ÍNDICE.
Forma tipo: =DESREF(punto_de_partida;filas;columnas;alto;ancho). Se restringe a los casos en que el tamaño del rango cambia realmente, y se mide el impacto en los archivos grandes.
2. INDIRECTO: referencia por texto
INDIRECTO transforma un texto en referencia: =INDIRECTO("Ventas!B"&L1) apunta a una celda cuya dirección se calcula — práctico para referencias construidas (por hoja, por nombre). Pero también es volátil y frágil (renombrar una hoja rompe el texto, sin actualización automática).
Reservarlo a los casos en que nada más encaja: cuando una referencia debe construirse dinámicamente y ni ÍNDICE ni las referencias estructuradas bastan.
3. AGREGAR: agregar ignorando
AGREGAR hace agregaciones (suma, promedio, max…) pudiendo ignorar los errores y/o las filas ocultas por un filtro. Ej. sumar una columna que contiene #N/A sin fallar, o totalizar solo lo visible tras filtrar. Una alternativa robusta y no volátil a apaños improvisados.
Lectura: AGREGAR(nº_función;opción;rango) — 9 = SUMA, 6 = ignorar errores, 5 = ignorar ocultos, 7 = ambos. Wassim se aprende 9;6 y 9;5 de memoria.
🔎 Ejemplo concreto — 🔎 Ejemplo concreto — Totalizar a pesar de errores y un filtro
Columna B de Wassim: números, #N/A, y la tabla está filtrada:
=SUMA(B2:B100) → devuelve #N/A (se bloquea) ❌
=AGREGAR(9;6; B2:B100) → suma ignorando los errores ✅
# 9 = SUMA ; 6 = ignorar los errores
=AGREGAR(9;5; B2:B100) → suma de las solas filas visibles (5)
// Rango de los N últimos meses (N en D1):
=DESREF($A$1; CONTARA($A:$A)-$D$1; 0; $D$1; 1)Lectura: AGREGAR combina una operación (9=suma) y una opción (ignorar errores/ocultos).
Lección: una función robusta evita los apaños frágiles alrededor de errores y filtros.
Vocabulario de la sección
- DESREF
- Construye una referencia desplazada y dimensionable (rangos dinámicos). Volátil.
Ejemplo sencillo: Wassim encuadra sus 12 últimos meses móviles con DESREF.
Lo que no es: un rango fijo que sigue las inserciones por sí solo.
Reflejo: rango de tamaño variable: DESREF, y luego plantearse una tabla estructurada.
- INDIRECTO
- Transforma un texto en referencia de celda o de rango. Volátil.
Ejemplo sencillo: Wassim apunta =INDIRECTO("Ventas!B"&L1) a una fila calculada.
Lo que no es: una referencia directa actualizada al renombrar.
Reflejo: dirección calculada: INDIRECTO como último recurso.
- AGREGAR
- Agrega (suma, promedio…) ignorando errores y/o filas ocultas.
Ejemplo sencillo: Wassim suma su columna con #N/A con AGREGAR(9;6;...).
Lo que no es: una SUMA que se bloquea en el primer error.
Reflejo: datos sucios o filtrados: pensar en AGREGAR.
- Función volátil
- Se recalcula con cada modificación del libro, aunque no tenga relación — puede ralentizar.
Ejemplo sencillo: Demasiados DESREF/INDIRECTO ralentizan el libro de cierre de Wassim.
Lo que no es: una función que solo recalcula si cambian sus entradas.
Reflejo: libro grande: contar las volátiles, preferir ÍNDICE.
En la práctica — En práctica — Robusto en lugar de frágil
- Suma un rango que contiene un #N/A con AGREGAR (opción ignorar errores).
- Filtra la tabla y totaliza solo lo visible con AGREGAR (opción ocultos).
- Construye un rango de los N últimos meses (DESREF) y luego valora una alternativa ÍNDICE.
- Anota por qué limitar INDIRECTO/DESREF en un libro grande.
Guion listo para decir — justificar AGREGAR en el cierre (30 s)
«Soy Wassim: la columna contiene #N/A y la tabla está filtrada, así que SUMA falla. Puse AGREGAR con la opción ignorar errores, y una versión filas visibles para el filtrado. Para el rango móvil, limité DESREF a lo estrictamente necesario — son funciones volátiles.»
Decirlo en la revisión de cierre: muestra el antes/después SUMA vs AGREGAR.
Plantilla para copiar y pegar — El trío robusto
=AGREGAR(9;6; B2:B100)
=AGREGAR(9;5; B2:B100)
=AGREGAR(9;7; B2:B100)
=DESREF($A$1; CONTARA($A:$A)-$D$1; 0; $D$1; 1)
=INDIRECTO("Ventas!B" & L1)Columna con errores
Como Wassim: =AGREGAR(9;6;rango) suma ignorando los #N/A, sin limpiar el origen.
Tabla filtrada
=AGREGAR(9;5;rango) totaliza únicamente las filas visibles tras filtrar.
Síntesis móvil
DESREF dimensionado por CONTARA para los N últimos meses — y luego tabla estructurada si hay lentitud.
Referencia construida
INDIRECTO hacia una hoja o una fila calculada, como último recurso y documentado.
✓ Buenas prácticas
- Preferir AGREGAR para agregar a pesar de errores/filtros.
- Limitar DESREF/INDIRECTO (volátiles) en libros grandes.
- Buscar primero una alternativa (ÍNDICE, tabla estructurada).
✗ Errores frecuentes
- Saturar un libro grande de funciones volátiles → lentitud.
- INDIRECTO hacia un nombre de hoja renombrado → #¡REF!.
- Usar DESREF donde bastaría una tabla estructurada.
Puntos clave
- DESREF: rango móvil/dimensionable (volátil).
- INDIRECTO: texto → referencia (volátil, frágil).
- AGREGAR: agrega ignorando errores y/o filas ocultas.
- Volátil = recálculo permanente; preferir alternativas robustas.
Reto 48h — aplícalo mañana
Sobre una columna real con errores: sustituye SUMA por AGREGAR(9;6;...), pruébalo en filtrado (opción 5), y luego audita tus DESREF/INDIRECTO y propón una alternativa.
- Sé agregar a pesar de errores y filas ocultas (AGREGAR).
- Sé construir un rango móvil (DESREF) y una referencia por texto (INDIRECTO).
- Sé limitar las volátiles y proponer alternativas robustas.
Preguntas frecuentes
¿Por qué evitar las funciones volátiles?
Se recalculan con cada cambio del libro (aunque no tenga relación), lo que sobrecarga los archivos grandes. DESREF e INDIRECTO son volátiles; cuando existe una alternativa no volátil (ÍNDICE, tabla estructurada, SUBTOTALES/AGREGAR), se prefiere.
¿AGREGAR o SUBTOTALES?
SUBTOTALES ignora las filas ocultas por un filtro; AGREGAR va más allá al ignorar también los errores y más opciones. Para datos «sucios» (errores) y filtrados, AGREGAR es más robusto.
¿Qué función suma una columna que contiene #N/A sin fallar?