Excel nos provee de muchas herramientas para construir estas referencias dinámicas. Las más conocidas tal vez sean BUSCARV (VLOOKUP en su versión inglesa), su variante BUSCARH (HLOOKUP), INDICE (INDEX) y COINCIDIR (MATCH).
En esta nota voy a hablar sobre una función poco utilizada por lo general: INDIRECTO.
La ayuda de Excel describe esta función de la siguiente manera:
Devuelve la referencia especificada por una cadena de texto. Las referencias se
evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee
cambiar la referencia a una celda en una fórmula sin cambiar la propia formula.
Lo que en castellano quiere decir: dada una referencia a una celda en forma textual, use INDIRECTO para recibir el valor que contiene esa celda.
Veamos un ejemplo (El archivo del ejemplo se puede descargar
aqui). Supongamos un cuaderno Excel con una hoja para mes de ventas. Cada hoja tiene el nombre del mes.
Los datos en cada hoja están organizados de la siguiente manera: productos en la columna A, ventas en la columna B
Por cada mes agregamos una hoja al cuaderno. En la primer hoja tenemos una fórmula que nos muestra el total de ventas de acuerdo al mes que elijamos
Cómo hacemos esto? Como pueden ver hemos usado Validación de datos para crear una lista de meses.
En la celda B2 escribimos la fórmula =SUMA(INDIRECTO(A2&"!B:B"))
Al elegir un mes en la celda A2, la expresión (A2&"!B:B") se transforma en el texto enero!B:B. Al utilizar este texto como argumento para la función INDIRECTO, Excel lo convierte en una referencia al rango B:B de la hoja "enero", que a su vez se transforma en el argumento de la función SUMA.
Ahora cada vez que elijamos un mes en la celda A2, veremos el total de ventas del mes elegido en la celda B2.
Categorías: Funciones&Formulas_, LOOKUPS_
Technorati Tags: INDIRECTO, Funciones Excel
Tengo un libro de Excel con mas de doce hojas (una por cada mes, llamadas Real (1), Real (2)… … Real (12)) y en otra hoja del mismo libro, la cual es un informe (llamada balance) tengo dos celdas en la que se seleccionan, con una lista desplegable, el número del mes que se desee y, con otra lista, el nombre del banco.
ResponderBorrarNecesito extraer cierta (mucha) información de cada hoja llamada “Real (#)” dependiendo el número del mes que se seleccione en la hoja "Balance" y del nombre del Banco; sin embargo‚ al anidar fórmulas solo se permiten hasta siete niveles de funciones y necesito anidar mas de doce, la fórmula que me serviría es esta pero no funciona:
=SI(E2=1;BUSCARH(D9;'D.REAL (1)'!$D$188:$O$192;3);(si(E2=2;BUSCARH(D9;'D.REAL (2)'!$D$188:$O$192;3);si(E2=3;BUSCARH(D9;'D.REAL (3)'!$D$188:$O$192;3);si(E2=4;BUSCARH(D9;'D.REAL (4)'!$D$188:$O$192;3);si(E2=5;BUSCARH(D9;'D.REAL (5)'!$D$188:$O$192;3);si(E2=6;BUSCARH(D9;'D.REAL (6)'!$D$188:$O$192;3);si(E2=7;BUSCARH(D9;'D.REAL (7)'!$D$188:$O$192;3);si(E2=8;BUSCARH(D9;'D.REAL (8)'!$D$188:$O$192;3);si(E2=9;BUSCARH(D9;'D.REAL (9)'!$D$188:$O$192;3);si(E2=10;BUSCARH(D9;'D.REAL (10)'!$D$188:$O$192;3);si(E2=11;BUSCARH(D9;'D.REAL (11)'!$D$188:$O$192;3);si(E2=12;BUSCARH(D9;'D.REAL (12)'!$D$188:$O$192;3);0)))))))))))))
Me encontré en este blog la posibilidad de utilizar la función Indirecto, pero tengo problemas al utilizarla con mas de dos variables, ya que en tu ejemplo solo se seleccionaba el mes, y yo necesitaría utilizar el mes y el banco. Tu sabes algún truco adicional para poder hacer esto? o si en lugar de anidar funciones o utilizar la función Indirecto existe otra forma?·
Bueno‚ agradezco tu amable ayuda y quedo atento a tus comentarios.
Un saludo
Óscar W
Sevilla - España
Hola,
ResponderBorrarsería bueno que me enviaras el archivo para ver como están organizados los datos.
Pero suponiendo que en las hojas de los meses hay dos columnas, una con el nombre de los bancos y la otra con el saldo, una solución posible sería la siguiente:
en la hoja Balance en la celda A2, por ejemplo, pondría el mes (con una lista desplegable);
en la celda B2 el mes (otra lista desplegable);
en la celda C2 pondría esta fórmula
=BUSCARV(B2,INDIRECTO(A2&"!A:B"),2,0)
Esta fórmula combina ambas variables, el mes y el banco.
Puedes descargar un ejemplo aquí
Hola Jorge, encontraste la manera de utilizar la función Indirecto con libros externos? Excel no lo permite, estoy buscando algún rodeo, función sustituta, macro o lo q sea...
ResponderBorrarmuy bueno el blog
Miguel Lederkremer
Director Editorial
Revista Users
Hola Miguel
ResponderBorrargracias por los conceptos. Te invito a leer mi entrada sobre el tema sobre còmo usar INDIRECTO con referencia a cuadernos Excel cerrados
Hola,
ResponderBorrarLo que no me queda claro es como hacer parar crae la lista de los meses (de donde sale esa lista), para todo lo demas no tengo problemas), la lisa de los meses, la tengo que hacer en otra hoja?
Saludos,
Agustin.
Hola Agustín
ResponderBorrarla lista de meses está generado con Validación de Datos, como está señalado en la nota. Si cliqueas el enlace podrás leer laq nota donde lo explico.
También podés descargar el archivo con el ejemplo, acabo de reparar el enlace.
Gracias Jorge... la verdad que di muchas vueltas por la red... y vos en dos oraciones pudiste ayudarme de 10... Muchisimas Gracias por tu ayuda desinteresada... Gracias
ResponderBorrarHola Jorge,
ResponderBorrarFelicidades por tu blog. La froma de exponer y explicar los temas es muy buena y clara.
Quiero hacer una suma de varias columnas pero el número de las columnas no es fijo. Si encuentro la celda_inicial y la celda_final, soy capaz de construir la fórmula textual "suma(celda_inicial,celda_final)" pero luego quiero que en otra celda aparezca tal cual la fórmula real=suma(celda_inicial,celda_final). ¿hay alguna función que realice esto (formula.text al revés) o debo usar vba?
Muchas gracias,
Podrías usar la función XLM EVALUAR, como explico en esta nota.
ResponderBorrarEstimado Jorge: Muchas gracias por tus aportes. Necesito yu ayuda en algo que tal vez es muy simple. En las celdas A1 tengo Enero, en la B1 Febrero, y asu sucesivamente....luego en la celda A2 tengo $ 100, en la B2 $ 120 y asi distintos valores...lo que quiero en el total (columna M..Total periodo) es sumar por ejemplo de Feb a Ago (cree una lista desplegable donde indico desde ..y hasta...) y que sea un rango dinamico, luego poder poner desde Ene-Sep, y asi sucesivamente de forma que seleccionando los meses de DESDE y HASTA la formula de SUMA de vaya modificando
ResponderBorrarLalo
ResponderBorrarhay varias formas de hacerlo. Personalmente me inclinaría por usar una tabla dinámica, para lo cual pondría los meses (como fechas) en la columna A y los valores en la coumna B. Pero siguiendo tu ejemplo, supongamos que en la celda B4 tenemos la lista deplegable que nos da el primer mes del rango y en la celda B5 el mes de cierre, una fórmula posible sería ésta:
=SUMA(INDIRECTO(DIRECCION(2,COINCIDIR(B4,meses,0))&":"&DIRECCION(2,COINCIDIR(B5,meses,0))))
"meses" es una rango nominado (nombre) que incluye el rango A1:L1 que contiene los nombres de los meses
Muchas gracias, excelente función, no la conocía y sí que aplica en muchísimos casos. Muy muy util, gracias nuevamente.
ResponderBorrarhola!queria hacer una consulta. hace poco cree una historia clinica. ademas grabe una macro para que agregue hojas. el tema es que quiero que los datos de esas hojas a agregar (que aun no estaN)se vuelquen automaticamente en una hoja del mismo libro. ya probe con funcion indirecto y es casi imposible. este libro esta pensado para tener varias hohas
ResponderBorrarSi bien se puede hacer con funciones, y no necesariamente INDIRECTO, creo que una solución más cabal sería usando macros.
ResponderBorrarBuenas:
ResponderBorrarEstoy teniendo un problema a la hora de usar referencias a libros externos. Quiero que me coja un dato de un libro, y la hora del cual me lo debe coger también debe ser variable. Al poner por ejemplo '[Libro externo.xlsx]I3&"'!A1 me da error al dar dinamismo a la hoja a la que me refiero. ¿es posible solventar esto? Lo he probado con indirecto, desref, buscarv pero ninguno me funciona
Jaume, fijate en esta nota.
ResponderBorrarHola, tengo una fila con notas y rangos f2;j2 (de 1 estudiante) y necesito copiarlas en otra hoja pero en columnas, he leido que con indirecto se puede hacer pero no se como pues necesito despues arrastrar la fórmula pues son muchos estudiantes para copiar y transponer uno por uno. Gracias
ResponderBorrarCarolina, se puede hacer con la función DESREF. Fijate en esta nota.
ResponderBorrar