función INDIRECTO Excel [uso avanzado]

funcion INDIRECTO

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:

  1. Uso de nombres predeterminados
  2. 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

funcion INDIRECTO

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.

validacion de datos

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.

nombre de rangos

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.

validacion de datos

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.

crear desde la seleccion

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.

funcion SUMA e INDIRECTO

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

funcion INDIRECTO y BUSCARV

 

Autor

MOS Expert CPC Martín Cruz

Compartir

Comments (7)

  • Ainhoa Reply

    Super interesante el post!!!

    diciembre 22, 2019 at 5:17 am
  • ANIBAL CHAMORRO Reply

    Gracias por el aporte en manejo de funcion INDIRECTO.
    Planteo la siguiente expresion para poder comprenderla:
    si.error(indice(indirecto(indirecto(t2));coincidir(b1;indirecto(indirecto(u2));0);coincidir(Hhoja 4!$r2;indirecto(indirecto($u2));0));0)
    Quiero comprender las funciones anidadas de INDIRECTO.
    Las otras funciones coincidir , indice si las comprendo .
    Anticipo agradecimientos por resolver la consulta.

    enero 31, 2020 at 11:10 am
    • Benjamin Reply

      no muestras los valores en las celdas a que hace referencia indirecto…. pero puedo inferir que se refiere a un nombre de rango que esta escrito directamente en la celda de tal forma que lo puedes cambiar sin necesidad de editar la formula

      noviembre 9, 2020 at 2:00 am
  • Iñigo Reply

    muy interesante

    julio 29, 2020 at 6:04 am
    • Martín Cruz Reply

      Muchas gracias!!!

      julio 29, 2020 at 11:01 am
  • Yrbo Sangronis Reply

    Excelente explicación Profesor.

    agosto 23, 2020 at 1:15 pm
    • Martín Cruz Reply

      Muchas gracias Yrbo!!!

      agosto 23, 2020 at 1:47 pm

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *