Análisis de la función INDIRECTA por ejemplos

A primera vista (especialmente al leer la ayuda), la función INDIRECT (INDIRECTO) parece simple e incluso innecesario. Su esencia es convertir el texto que parece un enlace en un enlace completo. Aquellos. si necesitamos referirnos a la celda A1, entonces podemos hacer habitualmente un enlace directo (ingrese un signo igual en D1, haga clic en A1 y presione Entrar), o podemos usar INDIRECT con el mismo propósito:

Tenga en cuenta que el argumento de la función, una referencia a A1, se ingresa entre comillas, por lo que, de hecho, es texto aquí.

"Bueno, está bien", dices. "¿Y cuál es el beneficio?" 

Pero no juzgues por la primera impresión, es engañosa. Esta función puede ayudarte en muchas situaciones.

Ejemplo 1. Transponer

Un clásico del género: necesitas girar el dia vertical

groove a horizontal (transposición). Por supuesto, puede usar una inserción o función especial TRANSPORTE (TRANSPONER) en una fórmula matricial, pero puede arreglárselas con nuestro INDIRECT:

La lógica es simple: para obtener la dirección de la siguiente celda, pegamos la letra “A” con el carácter especial “&” y el número de columna de la celda actual, que nos da la función COLUMNA (COLUMNA).

Es mejor hacer el procedimiento inverso de manera un poco diferente. Dado que esta vez necesitamos formar un enlace a las celdas B2, C2, D2, etc., es más conveniente usar el modo de enlace R1C1 en lugar de la clásica "batalla naval". En este modo, nuestras celdas se diferenciarán solo en el número de columna: B2=R1C2, C2=R1C3, D2=R1C4 etc.

Aquí es donde entra el segundo argumento de función opcional. INDIRECT. si es igual MENTIRA (FALSO), luego puede configurar la dirección del enlace en el modo R1C1. Entonces podemos transponer fácilmente el rango horizontal de nuevo a vertical:

Ejemplo 2. Suma por intervalo

Ya hemos analizado una forma de sumar sobre una ventana (rango) de un tamaño dado en una hoja usando la función ELIMINACIÓN (COMPENSAR). Un problema similar también se puede resolver usando INDIRECT. Si necesitamos resumir datos solo de un cierto período de rango, entonces podemos pegarlos en partes y luego convertirlos en un enlace completo, que podemos insertar dentro de la función. SUM (SUMA):

Ejemplo 3. Lista desplegable de tablas inteligentes

A veces, Microsoft Excel no trata los nombres y las columnas de las tablas inteligentes como enlaces completos. Entonces, por ejemplo, al intentar crear una lista desplegable (pestaña Datos – Validación de datos) basado en la columna Empleados de mesa inteligente Personas obtendremos un error:

Si “envolvemos” el enlace con nuestra función INDIRECT, entonces Excel lo aceptará fácilmente y nuestra lista desplegable se actualizará dinámicamente al agregar nuevos empleados al final de la tabla inteligente:

Ejemplo 4. Enlaces irrompibles

Como sabe, Excel corrige automáticamente las direcciones de referencia en las fórmulas al insertar o eliminar filas-columnas en una hoja. En la mayoría de los casos, esto es correcto y conveniente, pero no siempre. Digamos que necesitamos transferir los nombres del directorio de empleados al informe:

Si coloca enlaces normales (ingrese =B2 en la primera celda verde y cópielo), luego, cuando elimine, por ejemplo, Dasha, ¡obtendremos el #ENLACE! error en la celda verde correspondiente a ella. (#¡ÁRBITRO!). En el caso de utilizar la función para crear enlaces INDIRECT no habrá tal problema.

Ejemplo 5: Recopilación de datos de varias hojas

Supongamos que tenemos 5 hojas con informes del mismo tipo de diferentes empleados (Mikhail, Elena, Ivan, Sergey, Dmitry):

Supongamos que la forma, el tamaño, la posición y la secuencia de los productos y los meses en todas las tablas son iguales; solo difieren los números.

Puede recopilar datos de todas las hojas (no los resuma, pero póngalos uno debajo del otro en una "pila") con solo una fórmula:

Como puede ver, la idea es la misma: pegamos el enlace a la celda deseada de la hoja dada, y INDIRECT lo convierte en un “en vivo”. Para mayor comodidad, encima de la tabla, agregué las letras de las columnas (B,C,D) y, a la derecha, los números de línea que deben tomarse de cada hoja.

Trampas

Si está utilizando INDIRECT (INDIRECTO) necesitas recordar acerca de sus debilidades:

• Si vincula a otro archivo (pegando el nombre del archivo entre corchetes, el nombre de la hoja y la dirección de la celda), solo funciona mientras el archivo original está abierto. Si lo cerramos, obtenemos el error #¡ENLACE!
• INDIRECTO no puede hacer referencia a un rango dinámico con nombre. En estático, no hay problema.
• INDIRECTO es una función volátil o “volátil”, es decir, se recalcula ante cualquier cambio en cualquier celda de la hoja, y no sólo las celdas de influencia, como en las funciones normales. Esto tiene un efecto negativo en el rendimiento y es mejor no dejarse llevar por tablas INDIRECTAS grandes.

• Cómo crear un rango dinámico con tamaño automático
• Sumar sobre una ventana de rango en una hoja con la función OFFSET