La función INDIRECTO le permitirá crear referencias dinámicas y configurables en tus fórmulas
- Referencia en forma de texto. La función sólo necesita un argumento, la referencia a una celda pero en formato Texto.
- Transformar la escritura de la referencia. La parte fija se escribirá entre comillas y la parte variable fuera de las comillas.
=INDIRECTO("Referencia de celda")
Descripción general de la función INDIRECTA
De la ayuda en línea el sitio web de microsoft, las explicaciones sobre la función INDIRECTA no son muy esclarecedoras. De hecho, la función INDIRECTA le permite interpretar una cadena de caracteres como una referencia a una celda
Para decirlo de manera diferente y más completa, INDIRECT es capaz de leer una referencia escrita en una cadena de tipo Texto. Aplica en el siguiente ejemplo, si escribo la referencia A1 entre comillas, la función INDIRECTA devuelve el contenido de la celda A1
=INDIRECTO("A1")
Como ves, cuando la referencia está entre comillas, la función "interpreta" correctamente la celda y por tanto el resultado a mostrar. Si la referencia no tiene comillas, hay un error.
Construye tus propias referencias
Pero la ventaja de utilizar la función INDIRECTA es, por supuesto, construir sus propias referencias utilizando valores de celda o incluso fórmulas (ejemplos justo después).
Para ello, debes dividir la referencia en una parte fija y una parte variable.
- La parte fija de la referencia siempre irá entre comillas.
- La parte variable nunca irá entre comillas (de lo contrario, no será interpretada)
Por ejemplo, en el ejemplo anterior, es completamente posible reemplazar el valor 4 de la línea a devolver con una fórmula. Pero esta fórmula debe ser fuera de comillas. Y sobre todo, debes utilizar el símbolo & para vincular la parte fija y variable.
=INDIRECTO("B"&8/2)
Cada cálculo es igual a 4. Entonces la función INDIRECTA interpretará el resultado como parte de la referencia.
Caso concreto: extraer una muestra aleatoria
para formar un muestra de nuestros clientes, Usamos un número aleatorio para recuperar una fila. de nuestra base de clientes.
Crear una hoja de resumen
Otro uso muy interesante de la función INDIRECTA es construir una tabla de resumen. En el siguiente ejemplo, construimos una referencia usando
- El nombre de la hoja contenido en las celdas de la línea 2.
- El número de línea con la función LINE
Artículos relacionados
Encontrará ejemplos de uso con la función INDIRECTA en los siguientes artículos.
08/06/2023 a las 19:06
Hola,
Gracias por este tutorial tan claro, solo una nota rápida sobre el uso en O365 mediante la aplicación web Excel.
Para que funcione, la referencia de la hoja debe estar entre dos apóstrofes, por ejemplo: =INDIRECT("'"&$C3&"'!D9")
Siendo C3 la celda que contiene la referencia de la hoja y D9 la celda de esta hoja.
20/09/2023 a las 08:57
Gracias al autor de este sitio y muchas gracias a Patrcik Noël, autor de este valioso comentario que indica cómo hacer referencia a una hoja en la función INDIRECTA (también válida para la función DIRECCIÓN).
Sin esta referencia particular (con &), si cambia el nombre de la hoja, la fórmula no se actualiza y la referencia se pierde.
Para ello sólo hace falta una casilla en la que indicamos el nombre de la hoja mediante la fórmula: =RIGHT(CELL("filename";A1);NBCAR(CELL("filename";A1))-FIND("]";CELL ( "nombre de archivo";A1)))
Se te ha quitado una buena espina clavada, gracias.
No entiendo por qué necesitaba un comentario para encontrar esta información... El sitio de Microsft que enumera las funciones y sus argumentos no dice nada de eso...
El manual es quizás la referencia correcta en lugar de Internet...
20/09/2023 a las 09:02
Hola,
La fórmula se puede simplificar con TEXTO.DESPUÉS
=TEXTO.DESPUÉS(CELDA("nombre de archivo");"]")
26/04/2023 a las 11:59
La función INDIRECTA con la función DIRECCIÓN
Associated Together son realmente muy poderosos.
para extraer datos de varias hojas
a otra hoja por ejemplo
Gracias por todos estos tutoriales que nos ayudan mucho.
cordialmente
26/04/2023 a las 12:13
Gracias por los ánimos. Puedes enviarme un ejemplo de asociación de INDIRECTO con DIRECCIÓN a contacto@excel-ejercicio.com.
21/03/2023 a las 15:13
Hola Federico,
Muchas gracias por el ejemplo, está muy bien explicado y muy claro! Me funciona muy bien.
Tengo una pregunta para ti. Utilizo la función indirecta para referirme a un archivo externo (archivo que está abierto, por lo que se muestra un resultado) y diferentes pestañas.
Me gustaría poder utilizar mi fórmula indirecta repetidamente en otra fórmula (por ejemplo, sum.if.ens). Por lo tanto, esto crea una fórmula sum.si.ens que es un poco larga de escribir porque tengo que insertar el contenido de mi fórmula indirecta varias veces en mi fórmula sum.si.ens. También lleva mucho tiempo evaluar si tengo algún problema con los resultados.
Entonces, ¿es posible poner la función indirecta en mi celda A1 y simplemente hacer referencia a ella en mi fórmula sum.if.ens? Pongo el ejemplo con una fórmula de Suma para simplificar el ejemplo:
Por lo tanto
=SUM(INDIRECT(CHAR(39)&Folder_Project&"["&File_Name&"]"&Tab&CHAR(39)&"!$C$30:$T$136"))
se convertiría
=SUM(INDIRECT(A1&"!$C$30:$T$136")) donde A1 contendría "CAR(39)&Folder_Project&"["&File_Name&"]"&Tab&CAR(39)"
Mi problema actualmente cuando intento esto es que solo recibo mensajes de error...
¡Gracias de antemano y que tengas un buen día!
21/03/2023 a las 16:24
Hola,
Es difícil responder sin ver el contexto y la arquitectura de los libros, pero lo que es seguro es que Excel no fue creado para recuperar datos de libros cerrados. Por ejemplo, SUMIFENS y COUNTIFENS no pueden leer un libro cerrado. Te recomendaría repensar la arquitectura de tus datos, para que la información de un libro de trabajo se transfiera a una base de datos por ejemplo.
13/03/2023 a las 09:30
Hola,
=Indirect() te permite dirigirte a una celda, si no me equivoco.
Mi necesidad es ligeramente diferente. Necesito abordar una columna en una pestaña. El nombre de la pestaña cumple con un criterio concreto, y la columna a abordar será siempre la misma sea cual sea la pestaña (estructura idéntica para todas las pestañas excepto la primera que es un resumen de las demás).
Por ejemplo, tengo una fórmula como esta =IF(ISNA(EQUIV($B12&D$10;XLH28!$I:$I;0));"";"X"). Mi objetivo es configurar XLH28!$I:$I donde XLH28 será reemplazado por el nombre de la pestaña (XLH28, XLH29,...) que se encuentra en la columna B (aquí en B12 para XLH28). El uso de INDIRECT(CONCATENATE($B12;"!$I:$I")) no funciona y devuelve un error de propagación.
¿Cómo puedo lograr este direccionamiento sin utilizar macros?
Merci d'avance.
10/03/2023 a las 15:14
Hola,
Mi libro de Excel constará de una pestaña "Resumen" y varias pestañas (número desconocido) pero de estructura idéntica.
El nombre de las pestañas será del tipo "XLHn". En la pestaña "Resumen", queremos poder buscar información en la columna I. Por ejemplo, para la pestaña "XLH28", tendríamos una fórmula como =SI(ESTNA(EQUIV($B12&C$10;XLH28! $ I:$I;0));"";"X")
¿Cómo puedo reemplazar la parte "XLH28!$I$I" de la fórmula? No creo que INDIRECTO pueda permitirme abordar una columna completa.
Merci d'avance pour votre aide.
19/12/2022 a las 12:06
Hola,
Estoy intentando crear una fórmula indirecta() que tenga para uno de sus elementos una referencia a una celda que contiene una fórmula de filtro().
Entonces llamo a la celda con "#" para reproducir mi fórmula indirecta() en todos los resultados de la fórmula filter().
La fórmula devuelve "¡#VALOR!" en todas las líneas pero cuando consulto los resultados con F9 en la celda, encuentra los valores que quiero.
¿Tiene usted una solución?
Merci d'avance,
19/12/2022 a las 12:24
Entonces, nunca he probado el símbolo # (referencia extendida) con INDIRECT pero no estoy seguro de que funcione. Et j'irais plus loin dans ma réponse, je pense que c'est plus la construction de votre classeur qui est à revoir car sa construction actuelle doit être bien complexe si vous avez besoin de mixer les références étendues avec INDIRECT (c'est mon punto de vista)
24/06/2021 a las 16:52
Hola,
Gracias por tu tutorial muy claro. Sin embargo, encuentro un problema cuando quiero sumar el mismo bloque de celdas de varias hojas.
En la celda A20 el nombre de una persona, por ejemplo DUPONT, en la A22 el nombre de otra persona, por ejemplo Martin
Tengo una hoja Dupont y una hoja Martin
Si lo hago: =SUM(Dupont!A6:A10;'Martin'!A6:A10)
Lo que indirectamente da =SUM(INDIRECT("'"&A20&"'"&"!A6:A10");INDIRECT("'"&A22&"'"&"!A6:A10"))
Por otro lado, si hago =SUM('Dupont:Martin'!A6:A10)
Lo que traduje indirectamente por =sum(indirect("'"&A21&":"&A23&"'"&"!A6:A10"))
¡Recibo un mensaje de error #REF!
Si solo hay dos hojas, el primer método funciona, pero si hay varias hojas, esta fórmula se vuelve mucho más larga y compleja, mientras que la otra fórmula suma desde la primera hoja hasta la última hoja y, por lo tanto, no se alarga si agregas hojas.
¿Puedes decirme qué error cometí en mi fórmula? No puedo detectarlo.
Gracias por leer
jean-noël
24/06/2021 a las 17:01
No creo que la función INDIRECTA se haya desarrollado para comprender referencias en varias hojas de trabajo (referencias 3D). Lo más sencillo sería combinar todas tus hojas en 1 y ahí haces todos tus cálculos a partir de esta hoja. Es el mejor
18/06/2021 a las 17:14
Hola,
Estoy trabajando en una hoja de Excel de alrededor de 60 líneas. Las filas se completan uniformemente excepto las filas con contenido en las columnas G y H, por lo que para estas filas las otras celdas están vacías excepto en G y H. Entonces, después de un cierto número de filas (no siempre el mismo número), se completan completamente hay una línea con solo las celdas G y H completadas y nuevas líneas completas completadas, y así sucesivamente. Entonces tengo que reproducir el contenido de G y H en dos celdas delante de todas las líneas siguientes hasta las siguientes líneas donde el contenido de G y H cambia y así sucesivamente.
¿Existe alguna forma de realizar esta tarea automáticamente para ahorrar tiempo y reducir el riesgo de error?
05/05/2021 a las 08:20
Hola,
Estamos en el proceso de elaboración de un informe de autoevaluación de nuestro establecimiento.
La primera pestaña permite introducir comentarios (criterios / servicios / Puntos positivos / puntos de mejora)
El segundo nos permite registrar los resultados por criterios y servicios.
Las pestañas correspondientes a los servicios permiten editar un informe individualizado.
=> Resultados por criterios y servicios = OK (formato gráfico radar)
=> Resumen de comentarios por criterios = aún no implementado...
Algo que no puedo hacer es reportar los datos de la pestaña "entrada" por criterios y por servicios.
1) Número de criterio del informe (ejemplo: ServicioA = 9 criterios / ServicioB = 11 criterios)
2) Informar comentario en el criterio de número de enlace, servicio para puntos positivos
3) Reportar comentario en enlace nº criterio, servicio para puntos a mejorar
¿Podrías ayudarme a consolidar mi mesa?
Gracias de antemano por su ayuda
05/05/2021 a las 08:34
Haz tu formulario con Formularios https://www.youtube.com/watch?v=wYWNhiJJmTs eso sera mas simple
05/05/2021 a las 09:18
Gracias por tu pronta respuesta. Este punto se utilizará para implementar otras herramientas internamente.
Sin embargo, esto no satisface mis necesidades. Ya hemos recopilado datos de 9 servicios.
Lo que me pide mi jerarquía es que los elementos de respuesta de la pestaña "Entrada" se transfieran automáticamente a las pestañas "Servicio" y por criterios.
En las dos primeras entradas, solo hice =celdas de la hoja "Entrada"
Para facilitar las cosas, la pestaña "Entrada" debería incrementar las pestañas de servicios gradualmente.
La función indirecta me pareció adecuada, puedo estar equivocado... a menos que sea otra fórmula o peor, imposible de hacer.
05/05/2021 a las 10:29
Pues este es un proyecto real a realizar. Si quieres que yo me ocupe de ello, necesito tener acceso a los datos para valorar el tiempo que dedicarle y el precio que me costará. ¿Quieres que me haga cargo de este proyecto?
05/05/2021 a las 11:21
Por mucho que los Formularios simplificaran la gestión de datos y la explotación de los resultados, partimos de una hoja de cálculo de Excel con nuestros respectivos conocimientos.
Ante la urgencia de la necesidad (ultimátum de resultado para el 06/05/2021) y la expresión del solicitante, el costo de un estudio y desarrollo no cabe en ningún presupuesto de nuestro establecimiento de salud.
Su sitio y sus acciones son una mina de oro para novatos como yo. Voy a trabajar en ello en el back office para encontrar la fórmula que funcione bien y que nos sirva para los próximos años. También es un desafío para mí crear nuevas herramientas.
En cualquier caso, les agradezco su capacidad de respuesta y apoyo.
24/03/2021 a las 14:59
Hola,
¿Cómo adaptar la fórmula para recuperar información de varias filas simplemente estirando la fórmula?
Tomando su archivo como ejemplo, me gustaría crear la siguiente tabla en la pestaña Informe sin tener que modificar la columna y fila de las celdas de las que quiero recuperar la información en la fórmula.
AB
FR DE
1 País =INDIRECTO(B1&"!B2") =INDIRECTO(C1&"!B2")
2 MAYÚSCULAS =INDIRECTA(B1&"!B3") =INDIRECTA(C1&"!B3")
3 Área de población =INDIRECTA(B1&"!B4") =INDIRECTA(C1&"!B4")
gracias de antemano por su ayuda
20/03/2021 a las 14:34
Hola,
¿Cómo podemos encontrar la suma de los dígitos de una fecha de nacimiento? ¿O 25/06/1965 = 2+5+0+6+1+9+6+5=34 por lo tanto 3+4 =7 7 es el número final? Gracias por su ayuda
20/03/2021 a las 14:48
¿Pero cuál es el punto de hacer algo así?
13/08/2021 a las 08:46
En una celda estándar
=SOMME(1*GAUCHE(D13;2)+1*DROITE(D13;4)+1*STXT(D13;4;2))
Sin embargo, la celda de entrada de fecha (D13) debe estar en formato Txt.
07/02/2021 a las 11:31
bien merci
09/06/2020 a las 10:38
gracias fue muy claro y estoy feliz de tener dos ejemplos de uso de la función indirecta
05/07/2019 a las 23:09
Hola,
Gracias de todo corazón, después de horas en Internet tratando de entender cómo hacer esta fórmula, al final tan sencilla gracias a ti. ¡Este es exactamente el ejemplo que estábamos buscando!
18/01/2019 a las 14:11
Tengo una carpeta con 4 hojas correspondientes a 4 productos A, B, C y D, la estructura de cada uno de los cuales es idéntica. Finalmente tengo un quinto balance. Puse una lista desplegable en una celda del balance que enumera los 5 productos. Mi problema es encontrar una fórmula que me dé una suma de la columna C de una de estas hojas cada vez que se selecciona en la lista desplegable....ayúdenme a encontrar una solución por favor.
06/06/2018 a las 18:08
¡Hola
¿Podemos poner un INDIRECTO en una fórmula?
me explico
Tengo una hoja de resumen en la que recupero automáticamente los nombres de mis pestañas (usando una macro)
Me gustaría saber para cada pestaña cuantas columnas hay
Los nombres de mis pestañas no tienen un espacio en su nombre.
Los nombres de las pestañas están en la columna A.
y escribí =NBVAL(INDIRECT(A5)&"!1:1") luego =NBVAL(INDIRECT(A4)&"!1:1)")
lo que me devuelve 1 y ningún número de columnas en las pestañas
Cuando evalúo la fórmula el INDIRECTO se resuelve primero y devuelve el nombre de la pestaña
Luego en el siguiente paso pone un #ref y termina con un resultado de 1
31/01/2018 a las 14:57
Hola, necesito ayuda con un tablero. ¿Es posible obtener ayuda?
gracias
14/10/2015 a las 09:27
Bonjour.
Muy claras tus explicaciones. Sin embargo, una pequeña nota.
Escribe: "Tenga en cuenta que solo es posible asignar una fuente distinta a su hoja activa desde Excel 2010".
Aquí hay un método que funciona independientemente de la versión de Excel:
Ejemplo en Excel 2007.
En "Validación de Datos", si la Fuente está en otra hoja, debe ser identificada por un Nombre que se determina usando el Menú de Datos y luego el Administrador de Nombres.
En el Administrador de nombres, creamos un Nombre que hará referencia a una celda (o un rango de celdas) ubicada en una hoja distinta a la hoja activa.
Nombre opuesto:, escribe el nombre elegido
Frente a Zona: elija el libro de trabajo
Opuesto Se refiere a: haga clic en la pequeña cuadrícula de la derecha y seleccione la hoja luego la celda o rango de celdas que luego servirá como Fuente en Datos, Validación de datos.
Para que el Nombre elegido sea válido no utilice espacios ni tildes.
Para evitar conflictos, no utilice un Nombre que ya exista en Excel.
El uso de letras mayúsculas permite identificarlo como el nombre creado.
Este nombre se puede utilizar en todas las fórmulas de todas las hojas del libro.
cordialmente
16/02/2015 a las 21:59
¡Hola
bajo MO 2010 Me gustaría aplicar la fórmula INDIRECTA al segundo mes de esta fórmula (aquí marzo) =SUM(enero:marzo!AB2) ¡Pero no puedo hacerlo! =SUM(INDIRECT("Enero:Marzo!AB35")) ¡Excel me da el error #REF!
Gracias por iluminarme
22/01/2015 a las 11:35
Hola, lo siento, estoy un poco perdido: quiero poner un campo múltiple (de hecho, dos opciones "sí" o "no)" y solo en el caso de que se elija "sí" me gustaría que esto se refiera a un caso. para completar y que esto se bloquea si no se completa este cuadro.. No sé si quedo claro pero gracias de antemano por su ayuda.
22/12/2014 a las 11:08
Hola,
Me gustaría no bloquear el cuadro de referencia para poder ampliar mi fórmula sin dejar de hacer referencia a una página. Es eso posible ? ¿Cómo debo escribir esta fórmula? ¡Gracias de antemano por tu ayuda!
03/03/2015 a las 14:37
¡Hola
$A bloqueará la columna A
$1 bloqueará la línea 1
Entonces si quieres permanecer siempre en la misma celda: $A$1
06/11/2014 a las 22:55
Hola,
Creé un archivo de Excel en el que codificamos datos periódicamente.
En mi primera hoja, todos los datos están codificados, pero en mi segunda hoja, me gustaría que en esta solo ciertos datos se tomen automáticamente en comparación con mi primera hoja. (la tabla cruzada no me conviene porque en esta 2da hoja se agregarán otras columnas para codificar otros datos.
Podria usted ayudarme ?
¡Gracias!
18/08/2014 a las 07:34
Buenos días a usted
Vi tu profesionalismo...
y quisiera saber si me pueden ayudar a crear una cotización muy específica en VBA o algo más....
porque no sé nada al respecto y soy una pequeña empresa y me gustaría que mis cotizaciones fueran automáticas y personales para mi negocio...
Probé un software ya preparado pero no me convenía
entonces te pido ayuda por favor
gracias
a+
25/06/2014 a las 08:41
Hola,
Seguí tus explicaciones muy claras para esta función indirecta. Estoy en Excel 2007, hice el ejercicio y tengo un problema. Mis celdas están en formato estándar, en mi hoja Global, el IVA vuelve al formato 0,196 para 19.60%. Cambié el formato a %, el PB es que el PIB sube en %
Merci de votre aide
04/06/2014 a las 13:48
Hola,
gracias por estos valiosos tutoriales.
Un pequeño detalle que no funciona con Excel 2010, la fórmula FormulaText normalmente permite mostrar la definición de una fórmula en una celda.
Sin embargo, esta función se puede redefinir fácilmente utilizando un módulo y la siguiente función personalizada:
Función FT (MyCell como rango)
FT = MiCélula.Fórmula
Función finales
Para obtener detalles de implementación de este método, consulte este tutorial: https://www.youtube.com/watch?v=wpRGTvhp1cY
01/09/2014 a las 20:24
En Excel 2013, existe la función FORMULETEXT(referencia) que le permite escribir en una celda la fórmula contenida en otra celda.
11/05/2014 a las 11:41
Hola,
Muchas gracias por tus explicaciones. Ahora me parece todo claro. Sólo una pregunta, ¿es posible cambiar el No_index de la columna automáticamente para fórmulas ya establecidas?
Gracias de antemano
30/01/2014 a las 21:08
hola,
Estoy buscando una solución para que no cambien los enlaces en una subcarpeta que contiene mi hoja de excel.
Déjame explicarte: básicamente tengo una carpeta "modelo" que contiene 6 subcarpetas. Esta carpeta de plantilla se copiará y su nombre cambiará según el cliente.
por otro lado no hay cambios en las subcarpetas ¿Es posible mantener los enlaces en las subcarpetas?
gracias de antemano por su ayuda
atoch
18/11/2013 a las 21:06
Hola,
Estoy intentando utilizar la fórmula indirecta con el formato de celda "fecha" (dd-mmm), pero no puedo hacerlo... Debo admitir que la fórmula con espacios me ayudó un poco, pero me gustaría saber si es posible con fechas (es para un archivo de parte de horas semanales con una pestaña que contiene el resumen anual).
Merci d'avance!
30/10/2013 a las 18:37
BJR,
es realmente interesante gracias por estas explicaciones; Sin embargo, quiero crear un archivo con varias pestañas y en la pestaña de resumen quiero colocar dos menús desplegables que hagan dos opciones condicionales para recuperar los datos de las otras pestañas, ¿pueden ayudarme, por favor? 🙂
Gracias
24/09/2013 a las 19:04
Hola,
¿Puedes decirme en qué versión de Excel estás trabajando porque parece realmente muy bonita?
25/09/2013 a las 01:15
Excel 2013
Descargable en la parte superior derecha de esta página.
24/08/2013 a las 21:00
Hola,
Todo funciona perfectamente, ¡gracias! Pero me gustaría saber cómo recuperar automáticamente la lista de pestañas. El que pusiste en la columna A... ¡Y este, sin macro por favor!
24/08/2013 a las 23:39
Bonsoir,
Lamentablemente, no es posible extraer los nombres de sus hojas sin utilizar una macro. Pero no es tan difícil hacer macros 😉 Para tus necesidades, solo 4 líneas de código son suficientes y usé este ejemplo en el libro que escribí (ver referencia en la parte superior derecha de las páginas)
08/07/2015 a las 09:30
Tuve este problema y encontré una solución en la web.
(es un poco descabellado pero funciona (al menos en Excel 2010)):
1-Defina un nuevo nombre/rango con nombre (pestaña Fórmulas) (normalmente nombre_hoja) en el cuadro "Se refiere a", ingrese la fórmula
=LEER.ARCHIVO(1)
2- En todas las celdas del libro, la siguiente fórmula aplicada en las líneas del 3 al n de cualquier pestaña le permite recuperar el nombre de la hoja
=SI(LINEA()-1 <= COLUMNAS(nombre_hoja); DERECHA(INDEX(nombre_hoja;LINEA()-1);NBCAR(INDEX(nombre_hoja;LINEA()-1))-FIND("]";INDEX( nombre_hoja;LINE()-1))); "")