INDIRECTO
Devuelve la referencia especificada por una cadena de texto, esta referencia se evalúa de inmediato para presentar su contenido.
¿Qué quiere decir?
Imaginemos que en la celda C1 tengo el valor A1, como texto.
En la celda A1 tengo el valor de 25, por ejemplo.
Si en la celda D1 escribo =INDIRECTO(C1), va a tomar el valor de la celda C1 y la va a convertir en referencia, por lo que me va a dar el valor de A1, es decir, 25.
Igual pasaría si en vez de una celda, pongo un nombre de rango, pues, me cargara dicho nombre de rango, generalmente, esto se usa para rellenar una validación de datos, por lo menos, es para lo que yo lo uso.
Sintaxis
INDIRECTO(referencia; [a1])
· Referencia. Obligatorio. Referencia a una celda que contiene una referencia de estilo A1, como el ejemplo que hemos visto anteriormente, o una referencia de estilo R1C1, o, un nombre de rango. Si referencia no es una referencia de celda válida, INDIRECTO nos va a devolver #REF! #¡VALOR!
Si referencia hace referencia a otro libro, lo que se conoce como una referencia externa, el otro libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devuelve la #REF! #¡VALOR!, este error, se recupera una vez se haya abierto el libro.
· A1. Opcional. Un valor lógico que especifica el tipo de referencia que contiene la celda referencia.
o Si a1 es VERDADERO o se omite, referencia se interpreta como una referencia estilo A1.
o Si a1 es FALSO o se omite, referencia se interpreta como una referencia estilo F1C1.
Veamos un ejemplo sencillo.
Si escribo lo siguiente:
=INDIRECTO("B"&A1)
Estoy concatenando la letra B con el valor que hay en la celda A1, que es el valor 5, o sea, seria B5, y ese B5 lo convierte en referencia, por lo que me da el valor de B5.
Veamos otro ejemplo.
Tengo una hoja donde tengo las ventas de tres tiendas, es decir, tienda1, tienda2, y, tienda3.
En una hoja nueva, quiero calcular el total de la tienda que elija.
Lo primero que voy a hacer es crear tres nombres de rangos, uno será tienda1, que contendrá las cantidades de la tienda1, y, así con las dos tiendas restantes.
Par ello, selecciono las cantidades de la tienda1, voy al cuadro de nombres, y, lo llamo, por ejemplo, tienda1, y, pulso aceptar, importante, pulsar aceptar, en caso contrario, el nombre de rango no quedara guardado.
Sigo los pasos anteriores para crear tienda2, y, tienda3.
En la celda, J1, voy a crear una validación de datos, con las tres tiendas, pero en vez de seleccionar los valores desde la hoja de cálculo, lo escribirnos en la ventana de origen de validación de datos.
Debemos de escribir los nombres de las tiendas dentro de la validación de datos igual que lo hemos escrito al crear los nombres de rangos.
En la celda C1, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
Vamos a hacer uso solo del argumento referencia, donde seleccionamos la celda B1, que es donde se encuentra la tienda seleccionada.
=INDIRECTO(B1)
Aceptamos, y, tenemos una matriz desbordada, con las cantidades de la tienda seleccionada.
Pero queremos el ver total, pues, después del signo igual, ponemos la función SUMA.
=SUMA(INDIRECTO(B1))
Si cambiamos de tienda, veremos el total para dicha tienda.
Lo siguiente que vamos a hacer es crear un desplegable con la función INDIRECTO.
Tengo los siguientes nombres en formato de tabla, como no le he cambiado el nombre a la tabla, y, es la primera tabla de la hoja de cálculo, por defecto, se llama Tabla1, y, la única columna que tengo se llama [Nombre].
En la celda D1, voy a crear la validación de datos.
Dentro de lista de validación de datos, en origen, voy a poner:
Vemos que el argumento de INDIRECTO esta entre comillas, un texto que hace referencia a la columna Nombre, de la tabla llamada: Tabla1.
El nombre de la columna debe de ir entre corchetes, es la forma de hacer referencia a una columna de una tabla.
Hay que recordar que el nombre de una tabla de Excel lo podemos ver en la ficha Diseño, y a la izquierda, dentro del grupo propiedades, en este campo.
El objetivo que perseguimos construyendo una lista desplegable, mediante Indirecto, y mediante la construcción de una tabla de Excel, es que, al agregar nuevos registros, comprobaremos que en la lista desplegable, aparece esa nueva fila.
Si aceptamos y probamos veremos que es correcto.
Veamos otro ejemplo.
Tenemos una serie de provincias, queremos en una validación de datos, poder elegir una provincia, y, en otra validación de datos, que se rellene con las localidades de esa provincia.
Para ello, debemos, primero, crear los nombres de rangos.
Crearemos un primer nombre de rango, que lo vamos a llamar provincia, y, que va a contener las provincias, para ello, vamos a la pestaña datos, hacemos clic en validación de datos, desplegamos permitir y elegimos lista, y, en origen seleccionamos las provincias.
Lo siguiente va a ser crear los nombres de rangos de cada provincia, para ello, seleccionamos todo el rango, incluido los encabezados, vamos a la pestaña formula, y, hacemos clic en crear desde la selección, si aparece marcada columna de la izquierda, la desmarcamos, porque en este caso, hay datos.
Ya tenemos creado los nombres de rangos.
Teniendo seleccionada la celda B7, hacemos clic en la pestaña datos, y, clic en validación de datos, en permitir elegimos lista, y, en origen, escribimos =provincia, con esto, ya tenemos el primer cuadro de lista.
Bien, ahora, para la segunda lista, haremos uso de la función INDIRECTO, volvemos a validación de datos, y, en permitir, escribimos: =INDIRECTO(B7), que quiere decir que vaya a la celda B7 y cargue el nombre de rango que haya.
Si probamos veremos que al elegir una provincia del primer desplegable, y, vamos al segundo desplegable, salen las localidades de dicha provincia.
Ya que estamos vamos a ver la función DIRECCION, que suele usarse junto con INDIRECTO.
DIRECCIÓN la podemos usar para obtener la dirección de una celda en una hoja de cálculo, dados los números de fila y columna especificados.
Sintaxis
DIRECCION(fila; columna; [abs]; [a1]; [hoja])
Fila. Obligatorio. Especifica el número de fila que se va a usar.
Columna. Obligatorio. Especifica el número de columna que se va a usar.
Abs. Opcional. Especifica el tipo de referencia que se devolverá.
A1. Opcional. Valor lógico que especifica el estilo de referencia A1 o R1C1. En el estilo A1, las columnas se rotulan en orden alfabético y las filas se rotulan en orden numérico. En el estilo de referencia R1C1, las columnas y filas se rotulan numéricamente. Si el argumento A1 es VERDADERO o se omite, la función DIRECCION devuelve una referencia de estilo A1; si es FALSO, la función DIRECCION devuelve una referencia de estilo R1C1.
Nota: Para cambiar el estilo de referencia que usa Excel, hacemos clic en la pestaña Archivo, en Opciones y después en Fórmulas. En Trabajo con fórmulas, habilitamos o deshabilitamos la casilla Estilo de referencia R1C1.
sheet_text. Opcional. Es un valor de texto que especifica el nombre de la hoja de cálculo que se usará como referencia externa. Si se omite el argumento sheet_text, la dirección devuelta por la función hace referencia a una celda de la hoja actual.
Por ejemplo, si escribo: =DIRECCION(2;3), me devuelve $C$2 como referencia absoluta.
Si escribo =DIRECCION(2;3;2), me devuelve C$2, es decir, una referencia relativa, donde se fija la fila.
Si escribo =DIRECCION(2;3;2;FALSO), me devuelve C$2, pero en estilo de referencia R1C1.
Si escribo =DIRECCION(2;3;1;FALSO;"[Libro1]Hoja1"), me devuelve una referencia absoluta a otro libro en estilo de referencia R1C1.
Si a todo esto, delante ponemos la función INDIRECTO, pues, nos dará el valor de la celda C2 de la hoja1 del libro1.
=INDIRECTO(DIRECCION(2;3;1;FALSO;"[Libro1]Hoja1"))
Veamos otro ejemplo, partiendo de los siguientes datos:
Quiero saber el ID de la persona llamada Iker.
Quiero buscar el nombre Iker, pero que me devuelva la fila y columna que ocupa, con la función DIRECCION lo voy a convertir en referencia, y, con la función INDIRECTO, voy a tomar el valor.
Lo primero es preguntar si dentro del rango, se encuentra el valor Iker, para ello, lo haremos con el condicional SI de la siguiente manera, lo vamos a hacer de forma matricial, quiere decir, que una formula, o, función va a devolver mas de un valor.
Como argumento prueba lógica del condicional SI, voy a señalar el rango de nombres y lo igualo al nombre de Iker.
=SI(B2:B11="Iker"
Bien, si lo encuentra, quiero que me devuelva la fila y la columna, pero con la función DIRECCION, lo voy a convertir en referencia de la siguiente manera:
Vamos a hacer uso de la función FILA para obtener la fila, y, de la función COLUMNA, para obtener la columna.
La función FILA nos va a devolver el numero de fila de una referencia, y, la función COLUMNA, la columna de una referencia.
En caso, de haber coincidencia, la columna a devolver es la columna B, que es donde se encuentran los nombres.
=SI(B2:B11="Iker";DIRECCION(FILA(B2:B11);COLUMNA(A1));"")
Es decir, que si encuentra el nombre Iker, la función DIRECCION, va a convertir la fila y la columna que me devuelve en referencia, en caso contrario, me va a devolver un texto en blanco.
Como resultado es una matriz desbordada, y, donde haya coincidencia aparecerá la referencia, pero claro, no me aparece en la primera celda, es que donde quiero que aparezca.
¿Cómo lo hago?
Puedo usar la función CONCAT, que me va a unir las cadenas, pero ignorando las celdas que estén vacías, de la siguiente manera:
=CONCAT(SI(A1:C11="Iker";DIRECCION(FILA(B1:B11);COLUMNA(A1));""))
Por último, para obtener el id de empleado, pues delante, uso la función INDIRECTO.
=INDIRECTO(CONCAT(SI(B2:B11="Iker";DIRECCION(FILA(B2:B11);COLUMNA(A1));"")))
Commentaires