Cómo utilizar la función INDIRECTO en Google Sheets

En INDIRECTO de Google Sheets toma la dirección de la celda en forma de texto y devuelve una referencia de celda. Funciona de forma opuesta a la función DIRECCIÓN que devuelve una dirección en formato de texto.

La diferencia entre la función INDIRECTA y una función directa típica es que una función típica hace referencia directa a una celda (o rango de celdas) dentro de la fórmula. En cambio, la función INDIRECTA utiliza la dirección de la celda (en forma de cadena de texto) para encontrar la celda pertinente.

Nota: Si quieres saber más sobre otras funciones de google sheet, tenemos un post con Más de 100 funciones de Google Sheet

Utilizar una cadena de texto para hacer referencia a una celda ofrece varias ventajas que se describen a continuación.

Sintaxis

INDIRECT(celda_referencia_como_cadena, [is_A1_notacion])

cell_reference_as_string - es la forma textual de la dirección de una celda. Observe que no es la propia referencia de la celda. Como tiene forma de cadena de texto, debe ir entre comillas dobles, a menos que haga referencia a una celda que contenga la cadena de texto (véanse los ejemplos siguientes).

is_a1_notation - es una entrada opcional que indica a la función en qué tipo de notación está la dirección de la celda. Existen dos tipos de representaciones para la dirección de una celda. La notación A1 especifica la celda por la letra de la columna (A, B, C, etc.) y el número de fila (1, 2, 3, etc.), identificando celdas de la forma A1, B3, F14, etc. Otra forma de representar una celda es R1C1, que da el número de fila (R1) y el número de columna (C1). En este formato, la celda A1 sería R1C1, y la celda B3 sería R3C2 (porque B3 es la tercera fila de la segunda columna). TRUE es el valor por defecto, y obtienes la notación A1 a cambio. De lo contrario, si necesita la notación R1C1, debe especificar FALSE.

Cómo utilizar la función INDIRECTO

La forma más fácil de entender este concepto es verlo en funcionamiento, por lo que a continuación te ofrezco algunos ejemplos, cada uno de ellos con una combinación diferente.

Como puede ver, el primer parámetro puede ser una cadena directa encerrada entre comillas dobles (filas 2 y 3), una referencia de celda que contiene la cadena de direcciones (fila # 7), o incluso una cadena concatenada (filas 4 y 5).

Para ayudarte a entender mejor la función, voy a repasar la lista de las distintas variaciones de la fórmula y los resultados que genera cada una.

Ejemplo 1

En la primera fórmula =INDIRECT(“B3”), simplemente especifico que quiero que el resultado de la fórmula sea lo que haya en la celda B3, que es Brian.

Ejemplo 2

En la segunda fórmula =INDIRECT(“B2”, TRUE), hago lo mismo, pero esta vez pido que el resultado sea de la celda B2, que es Alex.

También indico TRUE, lo que significa que quiero la notación A1.

Tenga en cuenta que no es necesario especificarlo. La función asume que está utilizando la notación A1 a menos que indique lo contrario (utilizando FALSE que indica la notación R1C1).

Ejemplo 3

En la tercera fórmula =INDIRECT(“Hoja1!”&A4,True) concateno una cadena de texto que indicará a la fórmula a qué celda quiero hacer referencia.

Para ello, se empieza diciendo “¡Hoja1!”, seguido de &A4, que indica a la fórmula que busque en la celda A4 de la Hoja 1.

El texto de la celda A4 es “B7”, lo que indica a la fórmula que debe dar la salida de la celda B7, que es Fabio.

En esta fórmula, también indico TRUE para la notación A1, que también es opcional.

Ejemplo 4

La cuarta fórmula =INDIRECT(A5&”!B8″) hace lo mismo pero de forma diferente.

Se escribe “Hoja1!B8” haciendo referencia a la celda A5 (que contiene “Hoja1”) y añadiendo el “!B8”.

Como resultado, la fórmula indirecta busca en la celda B8 y devuelve Greg.

Ejemplo 5

La quinta fórmula =INDIRECT(“R5C2”,FALSE) utiliza el formato R1C1 para identificar la celda B5 diciendo R5C2 (la quinta fila de la segunda columna).

En este caso, debo indicar a la fórmula que estoy utilizando la notación R1C1 añadiendo FALSO al final de la fórmula.

Ejemplo 6

La sexta fórmula =INDIRECT(A7,FALSE) hace referencia a otra celda, A7, que apunta la fórmula a la celda B6 en formato R1C1, así que de nuevo añado FALSE.

Observe que en este ejemplo A7 no está entre comillas porque no es una cadena de texto sino una referencia a la celda A7 que contiene la cadena de texto (R6C2).

La última fórmula =INDIRECT(SUBSTITUTE(SUBSTITUTE(A8,”[“,””),”]”,””),FALSE) utiliza la fórmula SUBSTITUTE para cambiar el formato de dirección de la celda R[1]C[1] a R1C1, ya que ésta es la notación que puede manejar la fórmula.

Actualización de un rango de celdas en una fórmula

Una de las ventajas de utilizar la función INDIRECTO es que, al especificar la(s) celda(s) a la(s) que quieres que haga referencia la fórmula, esas celdas se mantienen inalteradas aunque añadas nuevas filas o columnas a la hoja.

El siguiente ejemplo muestra la diferencia entre utilizar una fórmula SUMA normal y utilizar la fórmula SUMA combinada con una fórmula INDIRECTA, =SUMA(INDIRECTA(“B2:B6”))

Aquí se puede ver que para sumar los ingresos de lunes a viernes, se puede utilizar SUMA con o sin la función INDIRECTO.

La función Suma hace referencia a las celdas B2:B6 directamente mientras que utilizando la fórmula INDIRECTA hace referencia a las celdas como una cadena de texto “B2:B6”.

La diferencia entra en juego cuando se realizan cambios en la hoja como, por ejemplo, añadir otra fila.

En la captura de pantalla anterior, puede ver que se ha insertado una fila adicional encima del lunes para añadir los ingresos del domingo. Cuando esto ocurre, la función SUMA cambia automáticamente sus celdas a B3:B7.

La fórmula que utilizaba la función INDIRECTA, sin embargo, mantenía el rango original de B2:B6.

En algunas situaciones, el hecho de que SUMA (y otras fórmulas) pueda cambiar su rango basándose en alteraciones de las filas y columnas de una hoja puede ser útil. Por otro lado, esto puede resultar frustrante cuando se desea mantener el mismo rango.

En estos casos, la función INDIRECTO es una forma útil de conseguirlo.

Utilización de INDIRECT con rangos con nombre

Rangos con nombre para referirse a un grupo de celdas. La fórmula INDIRECTA puede utilizar un rango con nombre para diversos cálculos.

En el siguiente ejemplo, he creado un rango con nombre para las celdas B3:B7 llamado Lunes_a_Viernes.

Para ello, seleccione las celdas y haga clic en Datos > Rangos con nombre.

Una vez creado el rango con nombre, se puede hacer referencia a él utilizando la función INDIRECTO.

En el ejemplo anterior, utilizo =SUMA(INDIRECTO(“De_lunes_a_viernes”)) para llamar al intervalo De_lunes_a_viernes y, a continuación, mostrar la suma de este intervalo.

Cómo utilizar INDIRECT para hacer referencia a otras hojas

Otro uso de la función INDIRECTO es referenciar datos en otras hojas.

En el sencillo ejemplo siguiente, puede ver cómo se puede utilizar la función para extraer datos de otras hojas.

He creado una segunda pestaña llamada Hoja2, y en la celda B4 de esa hoja he introducido “Datos de la hoja 2”.

Entonces en la Hoja 1, puedo usar la fórmula INDIRECTA para referirme a esa celda y sacar los datos de esa celda diciendo =INDIRECTA(“Hoja2!”&”B4”)

Esto puede ser útil cuando se tiene una hoja con múltiples pestañas de la que se está extrayendo información. En lugar de incluir la Hoja2 en la fórmula, puede escribir “Hoja2” en una celda y hacer que la fórmula INDIRECTA haga referencia a esa celda.

Si luego quieres cambiarla para obtener datos de otra pestaña, en lugar de cambiar la fórmula puedes simplemente cambiar la celda para que diga “Hoja3” en lugar de Hoja2, por ejemplo.

Del mismo modo, puede hacer lo mismo con la celda específica a la que hace referencia en la hoja (B4 en este ejemplo).

La función INDIRECTO también puede utilizarse para utilizar el formato condicional con varias pestañas.

Cómo utilizar la función INDIRECTA con MATCH

La función INDIRECTO puede combinarse con la función MATCH para devolver un rango dinámico de celdas.

En el siguiente ejemplo, combino las funciones INDIRECT y MATCH para obtener los ingresos de los días que especifique, utilizando la fórmula =INDIRECT(“B” & MATCH(B10,A:A,FALSE) & “:B” & MATCH(B11,A:A,FALSE), TRUE)

Como puede ver en la captura de pantalla, he introducido el lunes y el jueves como los días de inicio y fin para los que quiero ver los ingresos.

La fórmula MATCH encuentra esos días en mi rango de datos e identifica las filas en las que están. Estas filas se ponen en la fórmula INDIRECTA como las filas entre las que quiero devolver los datos. El resultado es que la fórmula da los ingresos de lunes a jueves.

Al configurarlo de esta manera, puedo obtener datos de diferentes rangos de días simplemente cambiando las celdas de los días inicial y final.

También puedo combinar esta fórmula con otras funciones, como SUM, para obtener los ingresos totales del intervalo de días que especifique.

Lo he hecho en el siguiente ejemplo, escribiendo =SUMA(INDIRECTO(“R” & MATCH(B10,A:A,FALSE) & “C2” & “:R” & MATCH(B11,A:A,FALSE) & “C2”, false))

Como puedes ver, en este ejemplo he utilizado la notación R1C1 en lugar de la notación A1 del ejemplo anterior.

Sin embargo, la lógica es la misma: utilice la función MATCH para identificar los datos que desea obtener y utilice la función INDIRECT para llamar a esos datos.

Utilización de la fórmula INDIRECTA con tabulaciones

La fórmula INDIRECTA puede utilizarse para consolidar información de varias pestañas de una hoja de cálculo.

En el siguiente ejemplo, tengo una hoja de cálculo con los datos de ventas del año. Cada mes es una pestaña separada, que tiene datos de ventas semanales y un valor total de ventas para el mes, como se muestra en la siguiente captura de pantalla.

En el Resumen ficha, quiero traer todos los totales de ventas mensuales.

En lugar de copiar y pegar cada valor, puedo utilizar la fórmula INDIRECTA para acceder a los datos de cada pestaña mediante la siguiente fórmula: =INDIRECTA(A2&”!B6″)

Como puede ver, esta fórmula indica a la función indirecta que busque en cada ficha y devuelva el total de ventas de ese mes, que está en la celda B6 de cada pestaña.

De este modo, puedes consolidar rápidamente los datos en varias pestañas. Lo mejor de todo es que cuando las cifras cambian en cualquiera de las pestañas, ese cambio se refleja automáticamente en la pestaña de resumen.

Errores - Fórmula INDIRECTA

¿Se producirán errores con la fórmula INDIRECTA? Por supuesto que sí.

La siguiente captura de pantalla ilustra el resultado cuando se intenta introducir la notación de dirección estilo A1 para cell_reference_as_string y FALSE para el parámetro is_A1_notation.

Hacer esto obviamente dejará la fórmula INDIRECTA buscando notación estilo R1C1, y no encuentra ninguna. De ahí el error.

Lo contrario también es cierto, como se indica en la siguiente instantánea.

Ahora el peor de los casos.

Intentas introducir un texto que no es ni la representación A1 ni la representación R1C1, sino un texto ficticio. Y, por supuesto, debería dar error.

Gracias por leer esta guía detallada de Sheetgo sobre cómo utilizar la función INDIRECTO en Google Sheets. ¡Echa un vistazo a nuestros otros artículos para más consejos de hoja de cálculo!

¿Te ha gustado este post?

Compártalo con sus amigos y colegas a través de los botones de las redes sociales situados a la izquierda.