............ Foro Excel
Si no lo has hecho aún: REGISTRATE!!!

BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Ver el tema anterior Ver el tema siguiente Ir abajo

BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por GalileoGali el Mar Nov 02, 2010 11:26 pm

Lleva el Merito Peo Sjoblom, resultado una matricial excepcional

Hoja1

 A
1305,8300255
2798,7392224
3 
4 
5Lisboa
6Hoja2
7Hoja3
Spreadsheet Formulas
CellFormula
A1{=BUSCARV(A2;INDIRECTO("'"&INDICE(RangoHojas;COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'"&RangoHojas&"'!A2:A200");A2)>0);0))&"'!A2:C200");3;0)}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!


Excel tables to the web >> [Tienes que estar registrado y conectado para ver este vínculo]






_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por sailepaty el Miér Nov 03, 2010 12:46 am

Con una pequeña modificacion para poder definir los rangos de las hojas en forma independiente.

Hoja1
ABC
1RANGOS
2HojasCuenta.SIBuscarv
3LisboaA2:A200A2:C200
4Hoja2D2:D200D2:F200
5Hoja3G2:G200G2:I200
6
7Valor a buscar:798.7392224
8
9305.8300255
Excel 2007

Array Formulas
CellFormula
B9=BUSCARV(B7;INDIRECTO(INDICE("'"&A3:A5&"'!"&C3:C5;COINCIDIR(VERDADERO;CONTAR.SI(INDIRECTO("'"&A3:A5&"'!"&B3:B5);B7)>0;0)));3;FALSO)
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


Saludos

sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 53
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por GalileoGali el Miér Nov 03, 2010 7:31 am

SAILE: excelente ampliacion y....con un truquito similar podrían revisarse varios libros de estructura uniforme, eso sí: SIEMPRE QUE ESTEN ABIERTOS, por las limitaciones de INDIRECTO.....
y ya que estamos en tren de aplicar: ¿qué pasaría si la columna de salida no fuera la misma en cada tabla?, podra este argumento también "pasarselo" a Buscarv como otra variable?

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por sailepaty el Miér Nov 03, 2010 11:06 am

Haber tratemos de hacer esta fórmula lo más dinámica posible a pesar del uso de INDIRECTO.

1.- El nombre de las hojas y los rangos de conteo y búsqueda se modificaran cuando cambien en las hojas respectivas.
2.- El numero de columna a devolver en cada hoja es el valor que el usuario digite en la celda correspondiente.
3.- Se separa la fórmula en dos partes para evitar la duplicidad de cálculo y evitar el mensaje de error sin tener que repetir el conteo.

Hoja1
ABCDE
1
2Nombre de archivo:
3[BUSCARV EN MULTIPLES HOJAS.xls]
4=ESPACIOS(EXTRAE(SUSTITUIR(CELDA("address";Lisboa!A1);"]";"]"&REPETIR(" ";500));2;500))
5
6Rangos:
7CONTAR.SIBUSCARVCOLUMNA
8'Lisboa'!$A$2:$A$200'Lisboa'!$A$2:$C$2003
9'Hoja2'!$D$2:$D$200'Hoja2'!$D$2:$F$2002
10'Hoja3'!$G$2:$G$200'Hoja3'!$G$2:$I$2001
11Rango CONTAR.SI
12=SUSTITUIR(CELDA("address";Lisboa!$A$2);$C$2;"")&":"&DIRECCION(FILA(Lisboa!$A$200);COLUMNA(Lisboa!$A$200))
13Rango BUSCARV
14=SUSTITUIR(CELDA("address";Lisboa!$A$2);$C$2;"")&":"&DIRECCION(FILA(Lisboa!$A$200);COLUMNA(Lisboa!$C$200))
15Rango COLUMNA
16=VALORES DIGITADOS MANUALMENTE
17
18Valor a buscar:
19234.0048204
20
21Valor localizado en hoja #:
221
23=COINCIDIR(VERDADERO;INDICE(CONTAR.SI(INDIRECTO(B8:B10);B19)>0;0);0)
24
25Valor encontrado:
26625.9119248
27=SI(ESNOD(B22);"NO ENCONTRADO";BUSCARV(B19;INDIRECTO(INDICE(C8:C10;B22));INDICE(D8:D10;B22);FALSO))
28
Excel 2007




SALUDOS

sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 53
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por moises melgar el Miér Nov 03, 2010 12:28 pm

bravo felicidades
impresionante
no tengo palabras para describir lo que he probado.
muchas muchas gracias, nunca se me habia ocurrido algo asi, y sin macros

pero en el administrador de nombres se encuentra "RangoHojas" y tiene como referencia =Hoja1!#¡REF! y no veo que se use en el libro ¿puede eliminarse?

moises melgar
Jr_Moderator

Masculino Cantidad de envíos : 512
Edad : 28
Ciudad - Pais : Coatzacoalcos - Mexico
Version de Excel : 2007 - 2010
Fecha de inscripción : 25/05/2010

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por sailepaty el Miér Nov 03, 2010 9:28 pm

Puedes eliminar el nombre sin ningun problema.

Saludos

_________________
I didn't ask to be Mexican I just got lucky!!!

sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 53
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por GalileoGali el Miér Nov 03, 2010 10:29 pm

SAILE: estoy viendo tus Formulas y la verdad es que no entiendo el por quÉ del Manejo de CELDA.
LO QUE OBTIENES EN CADA CASO CON LA COMBINACION CELDA DIRECCION esta claro, pero no termino de entender el propósito....

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por sailepaty el Jue Nov 04, 2010 12:21 am

No sé si interpreto bien tu pregunta pero si es así, la razón de las formulas es la siguiente:

Prefiero crear las referencias a los rangos de conteo y búsqueda con formulas, para en caso de que se inserten filas y/o columnas o cambie el nombre de la hoja* no se tengan que modificar manualmente.

*Con el inconveniente que hay que salvar el archivo para que cambie el nombre de la hoja en la formula.

Saludos

sailepaty
Moderador
Moderador

Masculino Cantidad de envíos : 135
Edad : 53
Ciudad - Pais : Dallas, TX
Version de Excel : 2007 y 2010
Fecha de inscripción : 04/03/2008

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por GalileoGali el Jue Nov 04, 2010 7:14 am

Gracias SAILE, ahora me quedó bien claro....
¡Eine Delikatessen!!

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por kurupc el Jue Nov 04, 2010 10:14 pm

Muchas gracias a todos por sus aportes, pero una sugerencia para los que no navegamos tan profundo en las aguas deliciosa del Excel, si en estos casos que parece genial lo que descubren y yo (tal vez otros también) no se bien de lo que hablan, si tal vez el que trae la novedad en este caso GalileoGali, si puede hacer una breve descripción de lo que se esta intentando mostrar, acuerdense que no todos tienen el nivel de conocimiento de ustedes, pero muchos podemos aplicar estas novedades. Es una sugerencia nomás.

kurupc

Masculino Cantidad de envíos : 20
Edad : 46
Ciudad - Pais : Argentina
Version de Excel : 2003-2007
Fecha de inscripción : 12/04/2010

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por GalileoGali el Vie Nov 05, 2010 7:40 am

Kurupc: tus comentarios son sumamente valiosos. Voy a tratar de explicar cómo funciona la estructura básica y después el funcionamiento de "los rangos, tal como los diseñó SAILE.
Espero encontrar un ratito en el fin de semana.

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por kurupc el Vie Nov 05, 2010 10:26 am

GalileoGali muchas gracias por la molestia y esperamos ansiosos de que se trata esta novedad. muchas gracias nuevamente.

kurupc

Masculino Cantidad de envíos : 20
Edad : 46
Ciudad - Pais : Argentina
Version de Excel : 2003-2007
Fecha de inscripción : 12/04/2010

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por GalileoGali el Vie Nov 05, 2010 10:25 pm

Bueno en el adjunto hay una primera explicacion de la Fórmula. Resta aún explicar las variantes de rangos propuestas por SAILE


_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por kurupc el Sáb Nov 06, 2010 1:31 am

Muchas Gracias GalileoGali por la explicación la estoy estudiando, no era para entenderla de una, hay que releerla más de una vez, pero muchisimas gracias nuevamente por aclarar el panorama y por la buena predisposición para enseñar (es una característica de los Grandes). Esperemos las otras variantes más complicadas. Saludos a todos los Excel-ENTES.

kurupc

Masculino Cantidad de envíos : 20
Edad : 46
Ciudad - Pais : Argentina
Version de Excel : 2003-2007
Fecha de inscripción : 12/04/2010

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por wenner el Lun Mar 28, 2011 9:56 pm

Iba a dar una puntuación positiva pero sin querer le he dado a menos y ahora veo no se puede modificar, por eso quiero rectificar contestando al mensaje y con una valoración (por escrito) de MUY POSITIVA.

wenner
Moderador
Moderador

Masculino Cantidad de envíos : 13
Edad : 105
Ciudad - Pais : España
Version de Excel : 2003-2007
Fecha de inscripción : 07/10/2008

Volver arriba Ir abajo

INDICAR.LIBRO(1)

Mensaje por GalileoGali el Mar Mar 29, 2011 12:24 pm

Gracias a que Héctor Miguel (en el foro de TodoExcel), trajo a la palestra el uso de INDICAR.LIBRO(1), para obtener un listado de todas las hojas del libro, precedidas del Nombre del Libro
En la práctica, por tratarse de una funcion XLMacro no la podemos llamar directamente desde una celda, sino que en esa especie de "UnderGround" que es el "espacio" de nombres, ahi lo vestimos para que resulte "Excel presentable"

Insertar >> Nombre >>> Definir para depurar lo que corresponde al Nombre del Archivo podemos definir ArrayHojas :: =REEMPLAZAR(INDICAR.LIBRO(1);1;HALLAR("]";INDICAR.LIBRO(1));"")

Entonces con este auxiliar, podemos prescindir de Rangos con el Nombre de las hojas, ya que INDICAR.LIBRO(1) se encarga de suministrar la informacion:

pues entonces podemos formular la busqueda en Multiples Hojas como

=BUSCARV(A2;INDIRECTO("'"&INDICE(ArrayHojas;COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'"&ArrayHojas&"'!J2:J200");A2)>0);0))&"'!J2:L200");3;0) con entrada MATRICIAL (ctrl + Mayusc + Enter)

Warning: la informacion de la primera hoja puede perturbar la busqueda, para evitarlo
1) pueden poner el elementoa buscar fuera del Rango especificado en todas las hojas para la Busqueda o bien crear otro NombreAuxiliar, así

2) ArrayHojasDep :: =si(ArrayHojas = "Hoja1";"Hoja2";ArrayHojas), y entonces utilizaríamos este nuevo depurado que descarta la primera hoja.

.
_________________
GalileoGali

_________________
GalileoGali
M.A.P. 2010-2013
Microsoft Active Professional

GalileoGali
Admin

Masculino Cantidad de envíos : 1962
Edad : 61
Ciudad - Pais : QUIROGA, Argentina
Version de Excel : 2000-2003-2007-2010
Fecha de inscripción : 24/01/2008

http://excelgali.mejorforo.net

Volver arriba Ir abajo

Re: BUSCARV EN MULTIPLES HOJAS (Peo Sjoblom)

Mensaje por Contenido patrocinado Hoy a las 3:14 pm


Contenido patrocinado


Volver arriba Ir abajo

Ver el tema anterior Ver el tema siguiente Volver arriba


 
Permisos de este foro:
No puedes responder a temas en este foro.