Más sobre referencias dinámicas en Excel - uso de INDIRECTO

lunes, febrero 27, 2006

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 descargarindirecto2aqui). 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: ,

16 comments:

Oscar,  27 noviembre, 2006 12:02  

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

Jorge L. Dunkelman 27 noviembre, 2006 21:27  

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í

Anónimo,  05 diciembre, 2006 19:44  

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

Jorge L. Dunkelman 05 diciembre, 2006 20:03  

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

Anónimo,  15 julio, 2007 03:15  

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.

Jorge L. Dunkelman 15 julio, 2007 20:48  

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.

emanuex,  13 octubre, 2007 19:13  

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

Anónimo,  14 marzo, 2008 13:53  

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,

Jorge L. Dunkelman 15 marzo, 2008 08:43  

Podrías usar la función XLM EVALUAR, como explico en esta nota.

Lalo 13 agosto, 2009 17:49  

Estimado 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

Jorge L. Dunkelman 13 agosto, 2009 19:06  

Lalo
hay 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

Anónimo,  19 abril, 2011 22:29  

Muchas gracias, excelente función, no la conocía y sí que aplica en muchísimos casos. Muy muy util, gracias nuevamente.

familia chiganer 22 mayo, 2012 08:04  

hola!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

Jorge L. Dunkelman 26 mayo, 2012 11:15  

Si bien se puede hacer con funciones, y no necesariamente INDIRECTO, creo que una solución más cabal sería usando macros.

Jaume,  11 noviembre, 2013 14:20  

Buenas:

Estoy 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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP