Contenido
¡Felicidades! Llegaste al último día del maratón. 30 funciones de Excel en 30 días. Ha sido un viaje largo e interesante durante el cual has aprendido muchas cosas útiles sobre las funciones de Excel.
El día 30 de la maratón, dedicaremos el estudio de la función INDIRECT (INDIRECTO), que devuelve el enlace especificado por la cadena de texto. Con esta función, puede crear listas desplegables dependientes. Por ejemplo, al seleccionar un país de una lista desplegable determina qué opciones aparecerán en la lista desplegable de ciudades.
Entonces, echemos un vistazo más de cerca a la parte teórica de la función. INDIRECT (INDIRECTO) y explorar ejemplos prácticos de su aplicación. Si tiene información adicional o ejemplos, por favor compártalos en los comentarios.
Función 30: INDIRECTO
Función INDIRECT (INDIRECTO) devuelve el enlace especificado por la cadena de texto.
¿Cómo se puede utilizar la función INDIRECTO?
Desde la función INDIRECT (INDIRECTO) devuelve un enlace dado por una cadena de texto, puede usarlo para:
- Cree un vínculo inicial que no se desplace.
- Cree una referencia a un rango con nombre estático.
- Cree un enlace usando información de hoja, fila y columna.
- Cree una matriz de números que no cambie.
Sintaxis INDIRECTO (INDIRECTO)
Función INDIRECT (INDIRECTO) tiene la siguiente sintaxis:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) es el texto del enlace.
- a1 – si es igual a VERDADERO (TRUE) o no se especifica, entonces se utilizará el estilo del enlace A1; y si es FALSO (FALSO), entonces el estilo R1C1.
Trampas INDIRECTAS (INDIRECTA)
- Función INDIRECT (INDIRECTO) se vuelve a calcular cada vez que cambian los valores en la hoja de cálculo de Excel. Esto puede ralentizar en gran medida su libro de trabajo si la función se usa en muchas fórmulas.
- Si la funcion INDIRECT (INDIRECTO) crea un enlace a otro libro de Excel, ese libro debe estar abierto o la fórmula informará un error #¡ÁRBITRO! (#¡ENLACE!).
- Si la funcion INDIRECT (INDIRECTO) hace referencia a un rango que excede el límite de fila y columna, la fórmula informará un error #¡ÁRBITRO! (#¡ENLACE!).
- Función INDIRECT (INDIRECTO) no puede hacer referencia a un rango dinámico con nombre.
Ejemplo 1: crear un enlace inicial que no se desplace
En el primer ejemplo, las columnas C y E contienen los mismos números, sus sumas calculadas usando la función SUM (SUMA) también son iguales. Sin embargo, las fórmulas son ligeramente diferentes. En la celda C8, la fórmula es:
=SUM(C2:C7)
=СУММ(C2:C7)
En la celda E8, la función INDIRECT (INDIRECTO) crea un enlace a la celda inicial E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Si inserta una fila en la parte superior de la hoja y agrega el valor de enero (ene), la cantidad en la columna C no cambiará. La fórmula cambiará, reaccionando a la adición de una línea:
=SUM(C3:C8)
=СУММ(C3:C8)
Sin embargo, la función INDIRECT (INDIRECTO) fija E2 como la celda de inicio, por lo que enero se incluye automáticamente en el cálculo de los totales de la columna E. La celda final ha cambiado, pero la celda inicial no se ha visto afectada.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Ejemplo 2: enlace a un rango con nombre estático
Función INDIRECT (INDIRECTO) puede crear una referencia a un rango con nombre. En este ejemplo, las celdas azules forman el rango NúmLista. Además, también se crea un rango dinámico a partir de los valores de la columna B NumListDyn, dependiendo del número de números en esta columna.
La suma de ambos rangos se puede calcular simplemente dando su nombre como argumento a la función SUM (SUMA), como se puede ver en las celdas E3 y E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
En lugar de escribir un nombre de rango en una función SUM (SUMA), puede hacer referencia al nombre escrito en una de las celdas de la hoja de trabajo. Por ejemplo, si el nombre NúmLista está escrito en la celda D7, entonces la fórmula en la celda E7 será así:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Desafortunadamente la función INDIRECT (INDIRECTO) no puede crear una referencia de rango dinámico, por lo que cuando copie esta fórmula en la celda E8, obtendrá un error #¡ÁRBITRO! (#¡ENLACE!).
Ejemplo 3: Crear un enlace utilizando información de hoja, fila y columna
Puede crear fácilmente un enlace basado en los números de fila y columna, así como usar el valor FALSO (FALSO) para el segundo argumento de función INDIRECT (INDIRECTO). Así se crea el enlace de estilo R1C1. En este ejemplo, agregamos adicionalmente el nombre de la hoja al enlace: 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Ejemplo 4: Cree una matriz de números que no se desplace
A veces, necesita usar una matriz de números en las fórmulas de Excel. En el siguiente ejemplo, queremos promediar los 3 números más grandes en la columna B. Los números se pueden ingresar en una fórmula, como se hace en la celda D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Si necesita una matriz más grande, es poco probable que desee ingresar todos los números en la fórmula. La segunda opción es usar la función FILA (ROW), como se hizo en la fórmula matricial ingresada en la celda D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
La tercera opción es usar la función FILA (Acompañar INDIRECT (INDIRECTO), como se hizo con la fórmula matricial en la celda D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
El resultado para las 3 fórmulas será el mismo:
Sin embargo, si se insertan filas en la parte superior de la hoja, la segunda fórmula devolverá un resultado incorrecto debido a que las referencias en la fórmula cambiarán junto con el cambio de fila. Ahora, en lugar del promedio de los tres números más grandes, la fórmula devuelve el promedio de los números más grandes tercero, cuarto y quinto.
Uso de funciones INDIRECT (INDIRECTO), la tercera fórmula mantiene las referencias de fila correctas y continúa mostrando el resultado correcto.