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:
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:
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.
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.
ResponderEliminarSaludos!
Hola Ante todo felicitarte por el Foro, es el mejor que he visto.
ResponderEliminarDebo 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
Muchas gracias por el comentario.
ResponderEliminarRespecto 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.
Excelente foro, tengo un problema no se como hacer lo siguiente:
ResponderEliminarNecesito 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
Hola, muchas gracias por tu apoyo...
ResponderEliminarRespecto 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
Tengo un Promblema con la funcion INDIRECTO() y no se si es la funcion correcta pra mi problema.
ResponderEliminarTengo 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
Hola Parche78,
ResponderEliminaren 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
Muchas gracias por responder,
ResponderEliminarYa 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
Quería preguntarte si conoces alguna forma de hacer esto ultimo automáticamente (dinámico) que no tenga que que copiar y pegar valores
ResponderEliminargracias
Slds
Hola Parche78,
ResponderEliminarcomo 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!!
Excelente trabajo y gracias me salvaste!!!
ResponderEliminarHola! como estas? hice una funcion que me devuelve la direccion de celda , la idea seria usar esta direccion para otra formula.
ResponderEliminarEn 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!!
Hola Gaston,
Eliminarpues 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
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.
ResponderEliminarEstoy teniendo problemas cuando pongo la funcion asi, me devuelve #¡REF! aparentemente no me toma las doble comillas en DIRECCION
ResponderEliminar=INDIRECTO(CELDA("DIRECCION";DESREF($A$1;FILA()+COINCIDIR(C3;I4:I9;0)-2;COLUMNA(I:I)-1));0)
muchas gracias
Saludos
Hola Gaston,
Eliminarpor 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
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.
ResponderEliminar=INDIRECTO(DIRECCION("F1"&COLUMNA()-1;C2;4;0;"hoja6"))
Muchas gracias
Saludos
Hola Gaston,
Eliminarexpliqué algo muy parecido, seguro te vale, en
http://excelforo.blogspot.com.es/2010/06/celdas-con-referencia-de-estilo-f1c1.html
Slds
me sirvió mucho
ResponderEliminarMuchas gracias!!
Saludos!
;-)
EliminarSlds
HOLA ESTOY INTENTO ELABORAR UN CONCETRADO DE TRABJADORES
ResponderEliminarTENGO 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?
Hola,
Eliminarlo 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
Hola.
ResponderEliminarComo 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")
Hola,
Eliminarte 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
y.. cómo le añado que esa celda B está en otra hoja?, cuál es la forma correcta de:
ResponderEliminar=SI(INDIRECTO('EQUIPO'!("B"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;31))))=4;"SI";"NO")
Bueno...
Eliminarsi la hoja se llama EQUIPO sería:
=SI(INDIRECTO("'EQUIPO'!B"&(EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;31)))=4;"SI";"NO")
Slds
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?
ResponderEliminarmuuchas gracias
Hola,
Eliminarno 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
Buenos d�as,
ResponderEliminarTengo 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
Hola Miguel,
Eliminaren 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 ;-)
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.
ResponderEliminarGracias
Juan Macedo (juan.mac2@gmail.com)
Hola Juan,
Eliminarsi 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
Hola, Ismael
ResponderEliminarAnte 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.
Hola Francisco,
Eliminarsi 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
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.
EliminarMuy feliz año y a seguir creciendo. Fantástico blog...
Muchas gracias Francisco,
EliminarMe alegro mi contribución te haya dado la luz al problema... es sólo experiencia
;-)
Igualmente Feliz Año!!!
Ismael!
ResponderEliminarhola, 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.!
Hola,
Eliminartendrí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
Hola Ismael.
ResponderEliminarDisculpa 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
Hola Álvaro,
Eliminarhabrí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
Gracias por responder
ResponderEliminarPor 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
¿Puedes enviarme el fichero a
Eliminarexcelforo@gmail.com
???
Slds
Hola Ismael
ResponderEliminarCon 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
Hola,
Eliminarpodrías:
range("C3").value=range("A1").value
Slds
Estimado Ismael
EliminarGracias 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 ???
Me estoy perdiendo...
Eliminarpor 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).
Estimado Ismael
ResponderEliminarAmpliando 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
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???
ResponderEliminarHola,
Eliminarsi 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