martes, 13 de abril de 2010

La función DIRECCION en Excel.

Revisaré una función poco conocida en general, pero que para casos determinados es más que útil.
Aprovecharé la solicitud de ayuda de un lector a este respecto:
...En una hoja del libro  tengo un resumen de todos  los costos  y los rescato de cada hoja enumeradas del mismo libro , por ejemplo de la hoja 000338 rescato el valor de la celda c4. con esta formula.  ="000338"!c$4. el problema se presenta cuando copio la formula hacia abajo en la hoja resumen, tengo que cambiar el valor de la formula manualmente cuando creo otra hoja, por ejemplo la nueva hoja 000339, tengo que repetir  ="000339"!c$4. y son cientos de hojas todos los dias, no hay forma que este valor aumento uno de forma automatica y se mantega la celda C$4...

El enfoque para la resolución de la duda planteada será combinar o anidar la función DIRECCION en la función INDIRECTO.
Supongamos que disponemos de dos hojas de cálculo, a las que hemos llamado '000339' y '000340', y en cada hoja tenemos un valor en la celda C4, por ejemplo valores numéricos 1.000 y 2.000; en una hoja 'resumen' deseamos tener el listado de todos esos valores:


La idea es aprovechar el listado de valores 339, 340 etc para obtener el valor de cada celda $C$4 de cada hoja; para esto necesitaremos convertir en primer lugar esos valores del rango A2:A3 en algo entendible para Excel; aplicamos la siguiente función
=DIRECCION(4;3;;;"000"&A2)
donde le estamos diciendo que queremos ver el valor de la fila 4, columna 3 de la hoja resultante de la combinación de "000" y el valor de A2; es decir obtendríamos
'000339'!$C$4
Perfecto, por que con esta dirección, anidándolo en la función INDIRECTO, resultará el valor de la celda C4 de la hoja '000339', es decir, 1.000:
=INDIRECTO(DIRECCION(4;3;;;"000"&A2)).



La sintaxis de la función DIRECCION es:
=DIRECCION(fila; columna; [abs]; [a1]; [nombre_hoja])
donde:
  • fila especifica el número de fila que se va a usar en la referencia de celda.

  • columna especifica el número de columna que se va a usar en la referencia de celda.

  • abs especifica el tipo de referencia que se devolverá. Podrá escogerse entre 1 (referencia absoluta), 2 (fila absoluta, columna relativa), 3 (fila relativa, columna absoluta) y 4 (relativa).

  • A1 valor lógico que especifica el estilo de referencia A1 o R1C1.

49 comentarios:

  1. Gracias por el artículo, estaba buscando exactamente que hacer después de tener la dirección en formato de texto para obtener el valor de la columna.

    Saludos!

    ResponderEliminar
  2. Hola Ante todo felicitarte por el Foro, es el mejor que he visto.
    Debo crear la previsión de comprar mensual de 2000 artículos, el problema es que debo analizar los movimientos de venta en los 12 meses anteriores para cada articulo y como comprenderás para 2000 artículos es bastante dispendioso y lento.Existe alguna forma en se calcule el valor a pedir de acuerdo a la estadística de venta? Muchas gracias

    ResponderEliminar
  3. Muchas gracias por el comentario.
    Respecto a la pregunta habría que ver como está estructurado el fichero, pero te diré que en alguna ocasión he tenido que realizar estimaciones para forecasts o budgets y empleé la fución ESTIMACION.LINEAL
    http://excelforo.blogspot.com/2009/06/lineas-de-tendencia-con-funciones.html
    aplicándola a los datos históricos. Se trata de obtener una línea de regresión que nos devuelva un dato estimado lo más correcto posible...
    En tu caso, tal cual lo planteas lo aplicaría a las estadísticas de venta de cada producto de que dispones... recuerda que esta función es matricial.
    si tienes algún problema con esto envíame un ejemplo de cómo tienes el fichero a
    excelforo@gmail.com
    Un saludo
    P.D.: si la correlación de cada producto en el tiempo es claramente lineal, mejor usa la función TENDENCIA.

    ResponderEliminar
  4. Excelente foro, tengo un problema no se como hacer lo siguiente:

    Necesito que la formula que hace referencia a A1 cuando la copie a A8 sea igual a A2 y no a A8, no se si me hice entender? Muchas gracias por su ayuda

    ResponderEliminar
  5. Hola, muchas gracias por tu apoyo...
    Respecto a tu pregunta me cuesta entenderla sin un contexto, pero podrías emplear lo siguiente:
    =INDIRECTO("A"&SI(FILA()<>8;FILA();2))
    con esto consigues que la referencia cuando copies a la celda A8 se refiera a la celda A2, y en el resto de casos a su fila respectiva.
    Espero te sirva.
    Slds

    ResponderEliminar
  6. Tengo un Promblema con la funcion INDIRECTO() y no se si es la funcion correcta pra mi problema.
    Tengo que hacer referencia a una celda pero de otro libro que se guarda según la fecha de creación, es decir que tengo un libro por cada día y siempre el resultado se encuentra en la misma posición de ese libro (\\PC1\Users\...\[Parte diario de produccion_ 2011-11-11.xls]Parte'!$D$38).
    Probé de muchas formas y no consigo hacerla dinámica, mi idea es poner la fecha en una celda y que me muestre el valor correspondiente a esa fecha de ese libro

    Saludos

    ResponderEliminar
  7. Hola Parche78,
    en principio parece la función adecuada...
    supongo por lo que dices que vincularás el archivo en función de algún desplegable o que directamente incluyes un día en una celda (por ejemplo A1:
    Tu función en A2 sería:
    =INDIRECTO("\\PC1\Users\...\[Parte diario de produccion_"&A1&".xls]Parte'!$D$38")
    y debería funcionar...
    Slds

    ResponderEliminar
  8. Muchas gracias por responder,
    Ya probé de esa forma y me da #¡REF! es como que no toma el argumento de A1 como txt. el problema me parece que es que no lo puedo agregar a la cadena de texto como texto para que tome el valor. Usando solo la funcion =CONCATENAR("\\PC1\Users\...\[Parte diario de produccion_ "&A1&".xls]Parte'!$D$38")lo copio y uso pegando especial como valor me direcciona al libro perfectamente.

    Slds

    ResponderEliminar
  9. Quería preguntarte si conoces alguna forma de hacer esto ultimo automáticamente (dinámico) que no tenga que que copiar y pegar valores

    gracias

    Slds

    ResponderEliminar
  10. Hola Parche78,
    como te escribía en un comentario anterior, teóricamente (a mi me funciona) podrías vincular a otras celdas, con por ejemplo valores de celdas validadas...
    Un cordial saludo!!

    ResponderEliminar
  11. Excelente trabajo y gracias me salvaste!!!

    ResponderEliminar
  12. Hola! como estas? hice una funcion que me devuelve la direccion de celda , la idea seria usar esta direccion para otra formula.
    En si lo que necesito hacer, es copiar valores de una hoja a otra, segun fechas en una columana y cuando cambia de dia seguir copiando los valores en otra columna. Para esto hice una formula que detecta cuando cambia la fecha y me devuelve la direccion de celda. El problema es que cuando quiero tomar el valor de celda para iniciar en otra columna no me lo toma... quizas tenga que usar la funcion indirecto? como puedo hacer??
    esta es la formula:
    =+SI(H4=2;CELDA("DIRECCION";DESREF($A$1;FILA()+COINCIDIR(C3;I4:I9;0)-2;COLUMNA(I:I)-1));0)

    Espero que se entienda
    Muchas gracias
    Te felicito por el foro!!

    ResponderEliminar
    Respuestas
    1. Hola Gaston,
      pues tu mismo te has contestado; efectivamente, la función INDIRECTO es lo que buscas. Anida el condicional dentro de INDIRECTO, de esta manera Excel transformará un 'texto' en una referencia (celda) de la hoja de cálculo.

      Slds cordiales

      Eliminar
  13. Muchas gracias! Cuando utilizo las referencias FC en distintas hojas, no entiendo como referenciarme a una posición de otra hoja. Osea en la misma hoja, referenciar una posición respecto a una celda, varia sumándole a f y/o C la posición respecto a ella. Pero para referenciar a otra hoja no entiendo como varia.

    ResponderEliminar
  14. Estoy teniendo problemas cuando pongo la funcion asi, me devuelve #¡REF! aparentemente no me toma las doble comillas en DIRECCION

    =INDIRECTO(CELDA("DIRECCION";DESREF($A$1;FILA()+COINCIDIR(C3;I4:I9;0)-2;COLUMNA(I:I)-1));0)

    muchas gracias
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Gaston,
      por que no usas la función DIRECCION en lugar de CELDA para obtener la referencia?, con DIRECCION puedes indicar la Hoja muy facilmente, como argumento de la función...
      Slds

      Eliminar
  15. Muchas gracias!! Tengo la ultima traba y logro hacer mi proposito. Ahora necesito que cuando arrastre la funcion para abajo ( en direccion que se van incrementando las filas) me vaya copiando el valor de las de las columnas en la misma fila. Por ejemplo que A1=B1, A2=C1, utilizando esta funcion no se como adpatar las referencias para que me haga esto.


    =INDIRECTO(DIRECCION("F1"&COLUMNA()-1;C2;4;0;"hoja6"))





    Muchas gracias

    Saludos

    ResponderEliminar
  16. me sirvió mucho

    Muchas gracias!!

    Saludos!

    ResponderEliminar
  17. HOLA ESTOY INTENTO ELABORAR UN CONCETRADO DE TRABJADORES
    TENGO UNA TABLA DE DATOS (LA MATRIZ) PERO EL CONCENTRADO LO ESTOY REALIZANDO EN OTRA PESTAÑA

    NUMERO DE TRABA 01 SUELDOS, 02 GRATIFICACION,03 ETC.
    1
    2
    3
    4
    ENTONCES NECESITO ALGUNAS FORMULAS QUE ME ARROJE LA CANTIDAD DEL CONCEPTO Y DEL TRABAJADOR EN ESPECIFICO. EXISTEEE JAJAJA
    LA MATRIZ VIENE ASI:

    TRABAJADOR 1
    CONCEPTO 1 SUELDO CANTIDAD
    CONCEPTO 2 GRATIFICACION CANTIDAD
    CONCEPTO 3 ETCETERA CANTIDAD

    TRABAJADOR 2
    CONCEPTO 1 SUELDO CANTIDAD
    CONCEPTO 2 GRATIFICACION CANTIDAD
    CONCEPTO 3 ETCETERA CANTIDAD

    Y NECESITO QUE EL CONCETRADO QUEDE COMO LO EXPLIQUE AL PRINCIPIO.
    SE PUEDEEE?

    ResponderEliminar
    Respuestas
    1. Hola,
      lo primero comentarte que, por favor, evites escribir en mayúsculas (esto es como si gritaras).
      Respecto a tu cuestión, habría que tratar previamente la información, transponiendo las cantidades de los diferentes conceptos, para que así, posteriormente sea más sencillo resumir la información que quieres.
      Deberías usar la función TRANSPONER, por ejemplo, supongamos que las cantidades del Trabajador1 estñan en C2:C4, entonces, a la derecha de la celda A1 (Trabajador1), esto es en B1:D1 escribes matricialmente =TRANSPONER(C2:C4)...
      Asi te quedaría para cada Trabajador en orden las cantidades de Sueldo, Gratificación, Etc.
      Luego ya podrás resumir con funciones del tipo SUMAR.SI o alguna equivalente en el informe total, trabajando sobre esas columnas, identificadas por filas por cada Trabajador.
      Slds

      Eliminar
  18. Hola.
    Como paso previo a usar la función DIRECCION, estoy probando esta fórmula en la hoja de nombre "20". En la celda B20 está el valor "4", pero la función me da siempre "no". ¿Qué hago mal?
    =SI(("B"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;31)))=4;"SI";"NO")

    ResponderEliminar
    Respuestas
    1. Hola,
      te faltaría indicar a Excel que tu composición 'B20' es una referencia válida, deberás anidar todo
      ("B"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;31)))
      en la función INDIRECTO.

      Quedaría:
      =SI(INDIRECTO(("B"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;31))))=4;"SI";"NO")

      Slds

      Eliminar
  19. y.. cómo le añado que esa celda B está en otra hoja?, cuál es la forma correcta de:
    =SI(INDIRECTO('EQUIPO'!("B"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;31))))=4;"SI";"NO")

    ResponderEliminar
    Respuestas
    1. Bueno...
      si la hoja se llama EQUIPO sería:
      =SI(INDIRECTO("'EQUIPO'!B"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;31)))=4;"SI";"NO")
      Slds

      Eliminar
  20. Sobre esta última consulta.. me funciona perfectamente, pero tengo que tener abiertos los 2 libros, y entrar en la función para pulsar enter para que actúe correctamente. ¿Se puede hacer sin que sea necesario ese enter, ni tener abierto a la vez el otro libro?
    muuchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      no hace falta que tengas los libros abiertos, con esa fórmula has creado un vínculo, y por tanto desde el Editor de vínculos podrás Actualizar datos, sin necesidad de abrir el otro Libro... aunque es cierto que esta herramienta en ocasiones falla :-(

      Slds

      Eliminar
  21. Buenos d�as,
    Tengo la f�rmula: EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo")))
    Esta f�rmula me da el nombre de la hoja activa, pero necesito que me de el nombre de la hoja donde est� la f�rmula, independientemente de si es la activa o no. �Se puede hacer?. Gracias

    ResponderEliminar
    Respuestas
    1. Hola Miguel,
      en primer lugar a la fórmula EXTRAE que muestras le faltaría un último argumento, deberá tener esta forma:
      =EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;LARGO(CELDA("nombrearchivo"))-ENCONTRAR("]";CELDA("nombrearchivo")))
      y no como indicas.
      Por otra parte, esta fórmula te va a mostrar siempre el nombre de la hoja donde esté la fórmula, y no depende de cual esté activa.

      Una cosa más, por favor, para evitar que me vuelva loco, evita realizar las preguntas por dos medios diferentes (mail y comentario), contesto igualmente por ambos.
      Muchas gracias ;-)

      Eliminar
  22. Buenas tardes, gracias por este foro, tengo el diguiente problema que lo he encontrado con algunas funciones, en especial de direccionamiento: uso VBA con funciones de Excel, lo que he encontrado es que fórmulas como Offset que en español la colocaron conçmo DESREF y Address que en español la colocaron como DIRECCION no se pueden usar en VBA en ninguno de los dos lenguajes.
    Gracias
    Juan Macedo (juan.mac2@gmail.com)

    ResponderEliminar
    Respuestas
    1. Hola Juan,
      si te refieres a las llamadas desde VBA a funciones de la hoja de cálculo (DIRECCION y DESREF), puedes utilizar las propiedades del objeto Range:
      Range.Offset
      y
      Range.Address
      obtendrías lo que necesitas..
      Slds cordiales

      Eliminar
  23. Hola, Ismael

    Ante todo, felicidades por el blog. Aporta información de lo más útil y ha conseguido que un negado en Excel como yo, empiece a poder hacer sus pequeños pinitos.

    Andaba buscando una función que me proporcionase la última celda con datos de una columna determinada. Teniendo en cuenta que los valores de dicha columna (la A) son cadenas de texto (nombres, para ser más exactos), había empleado con éxito la función:

    DIRECCION(COINCIDIR(REPETIR("z";250);A:A);1)

    El problema lo tengo ahora para poder utilizar el valor devuelto (pongamos por ejemplo, $A$10).

    La idea es utilizar esa posición para introducir un nuevo dato con la función Cells. He probado asignando el valor resultante a una variable, e incluso a anidar las funciones de este modo:

    Cells((DIRECCION(COINCIDIR(REPETIR("z";250);A:A);1)) + 1, 1) = nuevo_valor

    En ambos casos me da error. A ver si me pudieses echar un cable. Gracias por adelantado.

    ResponderEliminar
    Respuestas
    1. Hola Francisco,
      si lo estás intentando consguir con alguna macro, lo má sencillo es emplear el modo final, sería algo así para la columna A:
      Cells(Rows.count,1).end(xlup).offset(1,0).value = nuevo_valor

      Si lo pretendes hacer con funciones en la hoja de cálculo, como parece por la forma de localizar la celda en cuestión (con DIRECCION, COINCIDIR, etc) sería algo más complicado, ya que Cells es una propiedad dentro de VBA y no es utilizable directamente en la hoja....

      Espero te haya podido aclarar algo.
      Slds cordiales

      Eliminar
    2. De verdad: no es por hacer la pelota, pero lo hacéis tan fácil después de los quebraderos de cabeza que me he llevado que sólo queda inclinarse ante los maestros. Muchísimas gracias. Lo intentaba hacer con un macro pero los intentos que había hecho con unas y otras funciones no llegaba a buen puerto. Esa función me aporta justo lo que buscaba.

      Muy feliz año y a seguir creciendo. Fantástico blog...

      Eliminar
    3. Muchas gracias Francisco,
      Me alegro mi contribución te haya dado la luz al problema... es sólo experiencia
      ;-)

      Igualmente Feliz Año!!!

      Eliminar
  24. Ismael!

    hola, oye, necesito dar de alta unos materiales al almacén, pero la cuestión es que quiero que cada vez que haya una modificación al archivo (no consulta, sino escritura) se modifique en automático la fecha..como vez es posible? te agradezco la orientación y espero puedas responder en la brevedad posible.

    Saludos Cordiales.!

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que asociar un evento en la hoja de trabajo _Change, de tal forma que cada vez que cambies algo en esa hoja establezca la Fecha u hora del sistema donde determines...
      Saludos cordiales

      Eliminar
  25. Hola Ismael.
    Disculpa la molestia.
    Como puedo poner o enviar una valor de una celda conocida, que es parte de una lista ingresada por formulario, a una celda de referencia calculada que es parte de una matriz y en la cual no puedo poner una formula porque son miles de opciones
    Saludos cordiales y te anticipo mi agradecimiento

    ResponderEliminar
    Respuestas
    1. Hola Álvaro,
      habría que conocer la estructura de los datos...
      A priori me atrevería a decir que alguna función de búsqueda pueda servirte para lo que necesitas, tipo BUSCARV o INDICE+COINCIDIR
      Si como indicas no es posible poner una fórmula (??) quizá habría que optar por una macro.. para lo que tendríamos que conocer con precisión la distribución de datos ..
      Slds

      Eliminar
  26. Gracias por responder
    Por Ejemplo digamos que son diez jugadores en una competencia todos contra todos (no hay ida y vuelta). Por sorteo se define al empezar el partido cual es el jugador No1 y cual el No2. Un jugador puede participar una o varias veces en el día.
    Con un formulario ingreso los resultados (nombrejugador1, tantosalcanzados1, nombrejugador2, tantosalcanzados2). Con esos datos lógicamente se genera una lista. Estos resultados de la lista debo pasar a una matriz en la que en la columna1 (a partir de la fila 2) están los nombres de los jugadores. E la fila 1 en cada dos columnas están nuevamente los nombres de los jugadores. En la intersección entre los jugadores que han competido encuentro dos celdas en donde debo poner los resultados para realizar los cálculos de tantos anotados, puntos ganados por ganador o empate, porcentaje de producción y ubicación.
    De la lista calculo en base a los nombres la referencia a donde debo poner los resultados. Tengo entonces la referencia. No se como puedo mandar los resultados del partido a las celdas de las referencias calculadas.
    Si no puedo con formulas de excel, si tienes la bondad de indicarme con vb de excel.
    Como puedo poner el subindice de la matriz (en el vb) la referencia que fue calculada?
    Anticipo mi agradecimiento

    ResponderEliminar
  27. Hola Ismael
    Con esto espero te aclare mi duda
    tengo en A1 el valor ingresado (4)
    tengo en B3 el valor calculado ($C$3)
    Necesito mandar el valor 4 de A1 a la celda $C$3
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías:
      range("C3").value=range("A1").value
      Slds

      Eliminar
    2. Estimado Ismael
      Gracias tu gentileza

      Este código tengo

      Range("B1").Select
      Selection.End(xlDown).Select
      Selection.Copy
      Range("B15").Select
      ActiveSheet.Paste

      Pero "B15" no es valor fijo sino calculado por consiguiente variable. Calculo con la siguiente formula

      =DIRECCION(((COINCIDIR(E8;R$3:R$26;0))+2);((COINCIDIR(A8;S$2:EZ$2;0))+18))

      Como le pongo en lugar de B15 el resultado de la formula ???

      Eliminar
    3. Me estoy perdiendo...
      por un lado quieres obtener el resultado de la fórmula pero por otro quieres pegar el valor de otra celda...
      como te decía en los primeros comentarios no parece posible las dos cosas a la vez.
      Si copias lo que tengas en B1 sobre B15, perderás lo que tuvieras (en este caso parece una fórmula).

      Eliminar
  28. Estimado Ismael

    Ampliando la pregunta

    Range("B1").Select
    Selection.End(xlDown).Select
    Selection.Copy
    Range("i1").Select
    Selection.End(xlDown).Select
    ---Ojo en esta celda esta la direccion a donde debo pastear lo copiado ----
    Range("O30").Select
    ActiveSheet.Paste

    el O3 es el valor calculado, que como puedes ver en el anterior mensaje fue B15 y si sigo metiendo datos me dará otro valor y así sucesivamente

    Saludos

    ResponderEliminar
  29. Hola. Lo que quiero hacer es lo siguiente, tengo 2 hojas en un solo libro, en una hoja esta un monto en una celda X por decir M8, en mi segunda hoja quiero direccionar esa celda, pero de la celda AQ4 donde escribí M8. esto lo hago para poder cambiar solamente M8 u otra direccion. se puede hacer eso???

    ResponderEliminar
    Respuestas
    1. Hola,
      si tienes dos hojas (Hoja1 y Hoja2), en tu hoja2 en la celda AQ4 escribes
      =INDIRECTO("Hoja1!M8")
      y recuperarás el valor de la celda M8 de la Hoja1

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.