Cuando hablamos de referencias dinámicas nos referimos a situaciones en las cuales una hoja de Excel refleja resultados en base a otra hoja (en el mismo o en otro cuaderno).
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
9 comentarios:
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.
Necesito 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,
serí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...
muy bueno el blog
Miguel Lederkremer
Director Editorial
Revista Users
Hola Miguel
gracias por los conceptos. Te invito a leer mi entrada sobre el tema sobre còmo usar INDIRECTO con referencia a cuadernos Excel cerrados
Hola,
Lo 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
la 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
Hola Jorge,
Felicidades 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.
Publicar un comentario en la entrada