función INDIRECTO Excel [uso avanzado]
La función INDIRECTO de Excel es una de las funciones búsqueda más potentes que tenemos disponible en Excel.
Su combinación con otras funciones no tiene límite y siempre que encontremos un argumento matriz en otra función, podemos pensar en usar la función INDIRECTO.
Función INDIRECTO Excel ¿para qué sirve?
La función INDIRECTO sirve para hacer referencia a una celda, rango o matriz, mediante dos opciones principales:
- Uso de nombres predeterminados
- Uso de nombres personalizados
En nombres predeterminados tenemos a A1, F5, B6:B20, B6:F6, B6:B20.
En nombres personalizados tenemos: Zona, Vendedor, Meses o cualquier otro nombre que se asigne a una celda o rango.
¿Cómo usar la función INDIRECTO?
Cómo usar o cómo utilizar la función INDIRECTO pasa por revisar sus 2 argumentos:
Argumento ref. Normalmente aquí hacemos referencia a una celda que contiene el nombre de una referencia, ya sea predeterminada o personalizada
Argumento [a1]. Normalmente este argumento se omite, pero no lo emitimos cuando el argumento ref es de tipo F1C1.
Veamos un ejemplo
Función INDIRECTO Excel Validación de datos
La combinación de la función con la herramienta validación de datos crea dinamismo en Excel, de forma tal, que el rango que utilizamos en validación de datos se dinamiza con la función INDIRECTO, creando una lista desplegable dinámica sin necesidad de seleccionar un rango cada vez.
Veamos un ejemplo
Queremos tener dos opciones, una, la de seleccionar un mes, la otra, de seleccionar una provincia, en ambos casos queremos obtener un total.
Validación de datos incrustada
Sobre la celda D8 usaremos la herramienta validación de datos para incorporar dos nombres: Provincia y mes, que permita seleccionar uno de estos elementos.
Creación de nombres personalizados
Aplicaremos, a los rangos con marco azul (ver imagen superior), nombres personalizados, para el rango I7:M7 (Mes) y el rango H8:H11 (Provincia).
Son los mismos nombres que usamos en la validación de datos de la celda D8.
Validación de datos dependiente
Ahora usaremos la función INDIRECTO como fórmula dentro de validación de datos, para establecer un vinculo con lo que seleccionemos en la celda D8.
Ahora asignaremos nombres personalizados a cada columna y fila del cuadro, usaremos los nombres de los meses y de las provincias como nombres de rangos.
Crear desde la selección
Usaremos la herramienta “Crear desde la selección” como herramienta para crear nombres masivos.
Formulación con la función INDIRECTO
Ahora trabajaremos una fórmula sobre la celda D14 para que, cuando se seleccione un mes o una provincia, se genere el resultado totalizado.
Anidaremos la función INDIRECTO con la función SUMA.
Función INDIRECTO Excel Ejemplos
Búsqueda dinámica con INDIRECTO
Veamos este caso. Tenemos 4 tablas pertenecientes a la zona Norte, Sur, Este y Oeste. Cada zona tiene los mismos 4 productos.
Tenemos una base de datos donde queremos traer las cantidades en stock.
Al utilizar la función BUSCARV, el argumento valor_buscado sería la celda K12, pero tendríamos un problema con la matriz, ya que para el primer registro la matriz_tabla sería el rango D12:E15 pero para el segundo registro la matriz_tabla tendría que ser el rango G24:H27.
Como ya sabemos, la función INDIRECTO nos sirve para hacer referencia a una celda que contiene un nombre predeterminado o personalizado.
En este caso, tenemos que nombrar los rangos indicados en la imagen, para poder incorporar la función INDIRECTO y lea el contenido de la celda J12 como un nombre de rango y lograr la búsqueda dinámica que necesitamos.
Combinar la función INDIRECTO con la función BUSCARV es viable ya que esta última solicita un matriz, que INDIRECTO puedo capturar.
Descarga archivo de trabajo Búsqueda dinámica con funcion INDIRECTO
Autor