lunes, febrero 27, 2006

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

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: ,

18 comentarios:

  1. 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

    ResponderBorrar
  2. 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í

    ResponderBorrar
  3. 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

    ResponderBorrar
  4. 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

    ResponderBorrar
  5. 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.

    ResponderBorrar
  6. 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.

    ResponderBorrar
  7. 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

    ResponderBorrar
  8. 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,

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

    ResponderBorrar
  10. 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

    ResponderBorrar
  11. 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

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

    ResponderBorrar
  13. 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

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

    ResponderBorrar
  15. 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

    ResponderBorrar
  16. Hola, 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

    ResponderBorrar
  17. Carolina, se puede hacer con la función DESREF. Fijate en esta nota.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.