Descarga el libro de trabajo de ejemplo
Este tutorial demuestra cómo utilizar el Función INDIRECTA de Excel en Excel para crear una referencia de celda a partir del texto.
Descripción general de la función INDIRECTA
La función INDIRECTA Crea una referencia de celda a partir de una cadena de texto.
(Observe cómo aparecen las entradas de la fórmula)
Función INDIRECTA Sintaxis y entradas:
| 1 | = INDIRECTO (ref_text, C1) |
ref_text : Una cadena que representa una referencia de celda o una referencia de rango. La cadena puede estar en formato R1C1 o A1, o puede ser un rango con nombre.
a1 - OPCIONAL: Indica si la referencia está en formato R1C1 o A1. FALSO para R1C1 o VERDADERO / Omitido para A1.
¿Qué es la función INDIRECTA?
La función INDIRECTO le permite dar una cadena de texto y hacer que la computadora interprete esa cadena como una referencia real. Esto se puede usar para hacer referencia a un rango en la misma hoja, una hoja diferente o incluso un libro de trabajo diferente.
PRECAUCIÓN: La función INDIRECTA es una de las funciones volátiles. La mayoría de las veces, cuando está trabajando en su hoja de cálculo, la computadora solo volverá a calcular una fórmula si las entradas han cambiado sus valores. Sin embargo, una función volátil recalcula cada vez que haga un cambio en cualquier celda. Se debe tener precaución para asegurarse de no causar un gran tiempo de recálculo debido al uso excesivo de la función volátil o al tener muchas celdas dependientes del resultado de una función volátil.
Crea una referencia de celda
Digamos que desea obtener el valor de A2, pero desea asegurarse de que su fórmula corsé en A2 independientemente de que se inserten / eliminen nuevas filas. Podrías escribir una fórmula de
| 1 | = INDIRECTO ("A2") |
Tenga en cuenta que el argumento dentro de nuestra función es la cadena de texto "A2" y no una referencia de celda. Además, dado que se trata de una cadena de texto, no es necesario indicar una referencia absoluta como $ A $ 2. El texto nunca cambiará y, por lo tanto, esta fórmula siempre apuntará a A2, sin importar a dónde se mueva.
Número de fila INDIRECTO
Puede concatenar cadenas de texto y valores de celdas. En lugar de escribir "A2" como lo hicimos anteriormente, podemos tomar un valor numérico de la celda B2 y usarlo en nuestra fórmula. Escribíamos una fórmula como
| 1 | = INDIRECTO ("A" y B2) |
El símbolo "&" se utiliza aquí para concatenar la cadena de texto "A" con el valor de la celda B2. Entonces, si el valor de B2 era actualmente 10, entonces nuestra fórmula leería esto como
| 123 | = INDIRECTO ("A" y 10)= INDIRECTO ("A10")= A10 |
Valor de columna INDIRECTO
También puede concatenar en la referencia de columna. Esta vez, digamos que sabemos que queremos tomar un valor de la fila 10, pero queremos poder cambiar de qué columna extraer. Pondremos la letra de la columna que queremos en la celda B2. Nuestra fórmula podría verse como
| 1 | = INDIRECTO (B2 y "10") |
Si el valor de B2 es "G", entonces nuestra fórmula se evalúa así
| 123 | = INDIRECTO ("G" y 10)= INDIRECTO ("G10")= G10 |
INDIRECTO estilo r1c1
En nuestro ejemplo anterior, tuvimos que usar una letra para indicar la referencia de la columna. Esto se debe a que estábamos usando lo que se conoce como referencia de estilo A1. En el estilo A1, las columnas se dan con una letra y las filas con números. Las referencias absolutas se indican usando el “$” antes del elemento que queremos que permanezca absoluto.
En r1c1, tanto las filas como las columnas se inician con number. La referencia absoluta a a1 se escribiría como
| 1 | = R1C1 |
Puede leer esto como "Fila 1, Columna 1". Las referencias relativas se dan usando corchetes, pero el número indica la posición relativo a la celda con fórmula. Entonces, si estuviéramos escribiendo una fórmula en la celda A10 y necesitamos referirnos a A1, escribiríamos la fórmula
| 1 | = R [-9] C |
Puede leer esto como “La celda 9 filas hacia arriba, pero en la misma columna.
La razón por la que esto podría ser útil es que INDIRECT puede admitir el uso de la notación r1c1. Considere el ejemplo anterior en el que estábamos obteniendo un valor de la fila 10 pero queríamos poder cambiar la columna. En lugar de dar una letra, digamos que ponemos un número en la celda B2. Nuestra fórmula entonces podría verse como
| 1 | = INDIRECTO ("R10C" & B2, FALSO) |
Hemos omitido el 2Dakota del Norte argumento hasta ahora. Si este argumento se omite o es Verdadero, la función se evaluará usando el estilo A1. Dado que es Falso, se evaluará en r1c1. Supongamos que el valor de B2 es 5. Nuestra fórmula evaluará esto así
| 12 | = INDIRECTO ("R10C5", FALSO)= $ E $ 10 |
Diferencias INDIRECTAS con A1 vs r1c1
¿Recuerda que mostramos anteriormente que, dado que el contenido de esta fórmula era una cadena de texto, nunca cambió?
| 1 | = INDIRECTO ("A2") |
Esta fórmula siempre buscará en la celda A2, sin importar dónde mueva la fórmula. En r1c1, dado que puede indicar la posición relativa mediante corchetes, esta regla no es coherente. Si coloca esta fórmula en la celda B2
| 1 | = INDIRECTO ("RC [-1]") |
Estará mirando la celda A2 (ya que la columna A está a la izquierda de la columna B). Si copia esta fórmula en la celda B3, el texto del interior seguirá siendo el mismo, pero el INDIRECTO ahora verá la celda A3.
INDIRECTO con nombre de hoja
También puede combinar un nombre de hoja en sus referencias INDIRECTAS. Una regla importante que debe recordar es que debe colocar comillas simples alrededor de los nombres y debe separar el nombre de la hoja de la referencia de celda con un signo de exclamación.
Supongamos que tenemos esta configuración, en la que indicamos el nombre, la fila y la columna de nuestra hoja.
Nuestra fórmula para combinar todos estos en una referencia se vería así:
| 1 | = INDIRECTO ("'" & A2 & "'!" & B2 & C2) |
Nuestra fórmula luego será evaluada así:
| 123 | = INDIRECTO ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECTO ("'" Hoja2'! B5 ")= 'Hoja2'! B5 |
Técnicamente, dado que la palabra "Hoja2" no tiene espacios, no necesitar las comillas simples. Es perfectamente válido escribir algo como
| 1 | = Hoja2! A2 |
Sin embargo, no está de más poner las comillas cuando no las necesita. Es una buena práctica incluirlos para que su fórmula pueda manejar los casos en los que podrían ser necesarios.
INDIRECTO a otro libro
También mencionaremos que INDIRECT puede crear una referencia a un libro de trabajo diferente. La limitación es que INDIRECT no obtendrá valores de un libro de trabajo cerrado, por lo que este uso en particular tiene una practicidad limitada. Si el libro de trabajo al que apunta INDIRECTO no está abierto, la función arrojará un "#REF!" error.
La sintaxis al escribir el nombre del libro de trabajo es que debe estar entre corchetes. Usemos esta configuración e intentemos obtener un valor de la celda C7.
Nuestra formula seria
| 1 | = INDIRECTO ("'[" & A2 & "]" & B2 & "'! C7") |
Nuevamente, preste atención a la ubicación de las comillas simples, los corchetes y el signo de exclamación. Nuestra fórmula luego será evaluada así:
| 123 | = INDIRECTO ("'[" & "Sample.xlsx" & "]" & "Resumen" & "'! C7")= INDIRECTO ("'[Sample.xslx] Resumen'! C7")= '[Sample.xlsx] Resumen'! C7 |
INDIRECTO para construir rango dinámico
Cuando tiene un gran conjunto de datos, es importante intentar optimizar las fórmulas para que no hagan más trabajo del necesario. Por ejemplo, en lugar de hacer referencia a toda la columna A, es posible que queramos hacer referencia al número exacto de celdas en nuestra lista. Considere el siguiente diseño:
En la celda B2, colocamos la fórmula
| 1 | = CONTAR (A: A) |
La función CONTAR es muy fácil de calcular para la computadora, ya que simplemente verifica cuántas celdas en la columna A tienen algún valor, en lugar de tener que hacer comprobaciones lógicas u operaciones matemáticas.
Ahora, construyamos nuestra fórmula que sume los valores en la columna A, pero queremos asegurarnos de que solo mire el rango exacto con valores (A2: A5). Escribiremos nuestra fórmula como
| 1 | = SUMA (INDIRECTO ("A2: A" y B2)) |
Nuestro INDIRECTO tomará el número 5 de la celda B2 y creará una referencia al rango A2: A5. La SUM puede entonces utilizar este rango para su cálculo. Si agregamos otro valor en la celda A6, entonces el número en B2 se actualizará y nuestra fórmula SUM también se actualizará automáticamente para incluir este nuevo valor.
PRECAUCIÓN: Con la introducción de Tablas en Office 2007, es mucho más eficiente almacenar sus datos en una tabla y usar una referencia estructural en lugar de crear la fórmula que usamos en este ejemplo debido a la naturaleza volátil de INDIRECT. Sin embargo, pueden ser casos en los que necesite crear una lista de elementos y no pueda usar una tabla.
Gráficos dinámicos con INDIRECT
Tomemos el ejemplo anterior y demos un paso más. En lugar de escribir una fórmula para darnos la suma de los valores, crearemos un rango con nombre. Podríamos llamar a este rango "MyData" y hacer que se refiera a
| 1 | = INDIRECTO ("A2: A" & COUNTA ($ A: $ A)) |
Tenga en cuenta que, dado que colocamos esto en un rango con nombre, cambiamos la referencia a B2 y, en su lugar, colocamos la función CONTAR allí directamente.
Ahora que tenemos este rango con nombre, podríamos usarlo en un gráfico. Crearemos un gráfico de líneas en blanco y luego agregaremos una serie de datos. Para los valores de la serie, podría escribir algo como
| 1 | = Hoja1! MyData |
El gráfico ahora utilizará esta referencia para trazar valores. A medida que se agregan más valores a la columna A, el INDIRECTO se referirá a un rango cada vez más grande, y nuestro gráfico continuará actualizado con todos los valores recién agregados.
Validación dinámica de datos con INDIRECTO
Al recopilar información de los usuarios, a veces es necesario hacer que las opciones de una elección dependan de una elección previa. Considere este diseño, donde nuestra primera columna permite al usuario elegir entre frutas, verduras y carnes.
En el 2Dakota del Norte columna, no queremos tener una lista grande que muestre todas las opciones posibles, ya que ya hemos reducido un poco las cosas. Entonces, creamos otras 3 listas que se ven así:
A continuación, asignaremos a cada uno de estas listas a un rango con nombre. Es decir, todas las frutas estarán en un rango denominado “Frutas” y las verduras en “Verduras”, etc.
De vuelta en nuestra tabla, estamos listos para configurar la validación de datos en el 2Dakota del Norte columna. Crearemos una validación de tipo Lista, con una entrada de:
| 1 | = INDIRECTO (A2) |
El INDIRECTO leerá la elección realizada en la columna A y verá el nombre de una categoría. Hemos definido rangos con estos nombres, por lo que el INDIRECTO tomará ese nombre y creará una referencia al rango deseado.
Notas adicionales
Utilice la función INDIRECTA para crear una referencia de celda a partir del texto.
Primero cree la cadena de texto que representa una referencia de celda. La cadena debe estar en la letra de columna y el número de fila habituales del estilo A1 (M37) o en el estilo R1C1 (R37C13). Puede escribir la referencia directamente, pero normalmente hará referencia a las celdas que definen las filas y columnas. Por último, ingrese el formato de referencia de celda que elija. VERDADERO u Omitido para la referencia de estilo A1 o FALSO para el estilo R1C1.
Mientras trabaja con fórmulas INDIRECTAS, es posible que desee utilizar la Función FILA para obtener el número de fila de una referencia o el Función COLUMNA para obtener el número de columna (no letra) de una referencia.
Regrese a la lista de todas las funciones en Excel
INDIRECTO en Hojas de cálculo de Google
La función INDIRECTA funciona exactamente igual en Google Sheets que en Excel:
