viernes, 24 de agosto de 2012

Ejercicio de un BUSCARV anidado dentro de otro BUSCARV en Excel.

Explicaré hoy cómo completar el valor en una celda combinando varios instrumentos que nos ofrece Excel. En concreto veremos como combinamos la Validación de datos con un Nombre definido, trabajando sobre unos datos en forma de Tablas, y todo vinculado con una función BUSCARV aniadada dentro de otra.

El ejemplo sobre el que trabajaremos para desarrollar estas funcionalidades consiste en completar, en función a dos variables una tercera, partiendo de varias tablas de datos con registros de dos campos (un código y una descripción de producto) asociadas cada una a un cliente diferente:


En esta hoja de cálculo, llamada 'Clientes', vemos cuatro Tablas (Tabla1, Tabla2, Tabla3 y Tabla4), aunque podríamos trabajar con muchas mas; y también vemos la Tabla1 (la más importante) que nos relaciona cada Cliente con su Tabla. Esta es la base del ejercicio.

Toda esa información sirve para completar en la Hoja 'Buscador' el siguiente informe:

Ejercicio de un BUSCARV anidado dentro de otro BUSCARV en Excel.


Se trata, en definitiva, de seleccionar un cliente, y vinculado a este cliente alguno de sus códigos de producto, para una vez seleccionados Cliente y Código, aparezca mediante fórmula el nombre del producto correspondiente.

Comenzaremos el trabajo definiendo un Nombre definido que he llamado codigo, con la siguiente fórmula:
codigo =BUSCARV(Buscador!$B$3;Tabla1;2;0)&"["&Buscador!$B$3&"]"

Este Nombre construye un texto con una estructura compuesta por la Tabla y el Cliente, en función a la celda B3. Por ejemplo, Tabla2[Cliente1]. Este texto posteriormente lo convertiremos en un valor con la función INDIRECTO.

Otro paso es asignar a la celda B3 de la hoja 'Buscador' el nombre del Cliente; lo que conseguiremos con una Validación de datos tipo lista, empleando los valores del campo Cliente de la Tabla1:

Ejercicio de un BUSCARV anidado dentro de otro BUSCARV en Excel.


Una ventaja y novedad de trabajar con Excel 2010 es que permite relacionar y validar celdas entre diferentes hojas. En otras versiones deberíamos nombrar previamente ese rango.

Con el siguiente paso 'rellenaremos' la celda C3 con el código del producto, para lo que usaremos nuevamente una Validación de datos tipo lista, pero vinculada al valor desplegado en B3. Para esta tarea emplearé el Nombre definido codigo y la función INDIRECTO; ya que en cada tabla de la hoja 'Clientes' el campo de la primera columna (la que registra los códigos de los productos) se llama de esa manera (Cliente1, Cliente2, Cliente3 y Cliente4); por lo que al emplear INDIRECTO sobre ese texto, estamos llamando precisamente a dichos campos de las tablas, y los elementos que los componen:

Ejercicio de un BUSCARV anidado dentro de otro BUSCARV en Excel.



Recapitulemos, antes de llegar al último paso. Tenemos un nombre definido (codigo) que empleamos en la Validación de la celda C3, con la que obtenemos, en base al valor desplegado en B3, el código de producto asociado al cliente seleccionado en B3. Todo ello basado en las diferentes Tablas de la hoja 'Clientes'.
El último paso consiste en completar la celda D3 con la fórmula:
=BUSCARV($C3;INDIRECTO(BUSCARV($B3;Tabla1;2;0));2;0)

Ejercicio de un BUSCARV anidado dentro de otro BUSCARV en Excel.


Fijémosnos que tenemos un BUSCARV anidado dentro de otro. Y es que con el primero de ellos, el que es argumento de la función INDIRECTO, conseguimos la matriz de búsqueda del BUSCARV principal.
El resultado es claro, una vez seleccionado el Cliente en B3 y el código de producto en C3, nuestra fórmula devuelve el nombre del producto correspondiente asociado.

27 comentarios:

  1. Concatenar dos primeros caracteres del paterno, dos caracteres del materno, con una categoria, ejem A,B,C, y si esta afiliado en AFP

    ResponderEliminar
    Respuestas
    1. Hola que tal estás?.. me alegra igualmente saludarte.
      No entiendo tu pregunta, duda o sentencia...
      si pudieras explicarlo algo más...
      Un cordial saludo

      Eliminar
  2. Hola Ismael: Yo quisiera saber como podría hacer para tomar los tres primeros valores de una tabla mediante un BUSCARV y luego expresar la suma en otra tabla en otra hoja.

    ResponderEliminar
    Respuestas
    1. Hola Laura,
      para sumar los tres valores más altos de un rango podrías aplicar la siguiente fórmula matricial:
      =SUMA(K.ESIMO.MAYOR(C4:C10;{1;2;3}))
      recuerda validarla presionando Ctrl+Mayus+Enter

      Saludos

      Eliminar
  3. Hola, quiero hacer lo siguiente.
    SI(M30="B","NADA",BUSCARV(E30,$E$84:$Q$115,13,0)) cuando la celda M30 es igual a "A" me busca un valor (una nota ejem. 3.0, 4.1, 3.7, 2.5) en una tabla y me lo trae a la celda P30, ahora bien en la misma celda P30 quiero agregar varios SI, ya que dependiendo de la nota se deben cumplir algunas condiciones. Por ejemplo si es menor que 3, debe colocar "NADA", si es menor que 4, debe traerme un valor que esta en otra celda.
    Como podria hacerlo.

    ResponderEliminar
  4. buena tarde, necesito una formula que este comparando los destinos de la columna A con los nombres de la columna B y si coinciden entonces multiplique columna C y D

    ResponderEliminar
    Respuestas
    1. Hola Francisco,
      valdría un condicional:
      =SI(A1=B1;C1*D1)

      Saludos

      Eliminar
    2. Hola tengo un problema sin resolver y no se como lograr el anidamiento de buscarv para interceptar el dato que busco.
      Como datos tengo aulas, dias, y (horas de ingreso y fin del uso del aula) Y poner un numero de registro si esta ocupada.

      AULA DIA INICIA FIN Reg
      204 lunes 08:00 10:00 1
      204 lunes 10:00 12:00 2
      204 lunes 12:00 14:00 3
      203 lunes 08:00 10:00 4
      203 lunes 10:00 12:00 5
      203 lunes 12:00 14:00 6
      204 martes 08:00 10:00 7
      204 martes 10:00 12:00 8
      204 martes 12:00 14:00 9
      203 martes 08:00 10:00 10
      203 martes 10:00 12:00 11
      203 martes 12:00 14:00 12
      205 lunes 08:00 10:00 13
      205 lunes 10:00 12:00 14
      205 lunes 12:00 14:00 15
      .
      Tabla de doble entrada para poner el Nro de Registo si esta ocupada.
      DIA INICIO FIN 203 204 205....
      lunes 08:00 09:00 =BUSCARV(...
      lunes 09:00 10:00
      lunes 10:00 11:00
      lunes 11:00 12:00
      lunes 12:00 13:00
      lunes 13:00 14:00
      lunes 14:00 15:00
      lunes 15:00 16:00
      martes 08:00 09:00
      martes 09:00 10:00
      martes 10:00 11:00
      martes 11:00 12:00
      martes 12:00 13:00
      martes 13:00 14:00
      martes 14:00 15:00
      martes 15:00 16:00

      Gracias

      Eliminar
    3. Hola Pablo,
      yo en este caso, si como parece el número de REgistro es único, optaría por una función SUMAR.SI.CONJUNTO....ya que entiendo una misma sala no puede estar ocupada simultáneamente a la misma hora.
      La función tendría esta forma, a escribir donde pones el BUSCARV ahora:
      =SUMAR.SI.CONJUNTO(REG;AULA;D$1;DIA;$A2;INICIA;$B2;FIN;$C2)
      siendo REG, AULA, DIA, INICIA y FIN los rangos completos de la primera tabla, y D!, A2, B2 y C2 las celdas de la segunda tabla de doble entrada
      Saludos

      Eliminar
    4. Este comentario ha sido eliminado por el autor.

      Eliminar
  5. Gracias Ismael la probaré esa formula.

    ResponderEliminar
  6. Hola Ismael, tengo esta formula =CONCATENAR("#ESTRATO ",SI(BUSCARV($A$32,Hoja1!F:HZ,2,0)>=3,SI(BUSCARV($A$32,Hoja1!F:HZ,2,0)<=4,BUSCARV($A$32,Hoja1!F:HZ,2,0),SI(BUSCARV($A$32,Hoja1!F:HZ,2,0)>=5,BUSCARV($A$32,Hoja1!F:HZ,2,0))),"|") pero mi gran duda es cuando llego al final de la condición A32>=5 por que no estoy seguro si las posiciones de mi condición se respeten , lo digo por que si selecciono mi rango de datos tengo muchos valores que son iguales o mayores de 5, osea que si vuelvo a utilizar mi buscarv estoy dándole de nuevo una matiz de búsqueda y por eso del ordenado"FALSO" no obtenga el resultado correcto, no se si lo este haciendo de la manera mas larga o se te ocurra alguna mas fácil, muchas gracias por la ayuda y saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      no me queda claro qué pretendes lograr.. pero debes tener en cuenta que BUSCARV localizará la primera coincidencia de A32 en la primera columna de F:HZ (siempre).
      Por otro lado la construcción que has realizado del condicional no parece (a priori) tenga mucho sentido, ya que en todos los posibles caminos acabas con un BUSCARV de A32 en F:HZ.
      Diría tienes que replantear tu objetivo y aclarar qué necesitas obtener de ese BUSCARV según las condiciones >3, <4 o >5
      Saludos

      Eliminar
  7. hola ismael
    tengo en la columna A de una hoja los nits de las personas y en la columna B codigos de cuentas que se repiten cuando cambia el codigo de la columna A; en la coluna C tengo los nombres y en la columna D tengo los valores de los codigos de cuentas. en otra hoja tengo un formulasrio que en el encabezado lleva el nombre que ya ubique con un buscarv. en una coluna de ese formulario debo colocar los valores que estan en la columna D de la hoja anterior para cada persona ubicada en el encabezado. intento anidar varios buscarv y no me sale el resultado que quiero. por favor me puede aydar gracias

    ResponderEliminar
    Respuestas
    1. Hola!
      podrías enviarme el fichero a
      excelforo@gmail.com
      lo reviso y comento
      Saludos

      Eliminar
  8. Buenas tardes Ismael, estoy plantando un BuscarV perteneciente al libro llamado "Stock" pero a la vez, me gustaría anidar otro BuscarV (si es posible o con otra formula) de otro libro Llamado "Stock Hey" ¿Como se podría realizar? Ya que de las formas que he intentado, no me agarra la formula, gracias por tu atención y por la ayuda.

    ResponderEliminar
    Respuestas
    1. Hola Naireth,
      habría que saber qué buscas en cada libro y las tablas donde buscas..
      Para emplear un BUSCARV en un lugar u otro se pueden emplear diferentes formas siempre en función de cómo se tengan los datos.
      Por ejemplo:
      =SI.ERROR(BUSCARV(primer lugar);BUSCRAV(segundo lugar))

      Espero te oriente
      Slds

      Eliminar
  9. buen día, tengo la siguiente condicional de SI anidado con BUSCARV, me sigue dando #N/A en algunos caso no me toma la respuesta final al ser negativo en los dos casos, sabrás si algo esta mal en mi formula o si excede los caracteres que admite Excel? =SI(C75=BUSCARV(C75,'[Validacion AVPAR y EXPFIN.xlsx]AVAPAR'!$A$1:$C$317333,1,FALSO),BUSCARV(C75,'[Validacion AVPAR y EXPFIN.xlsx]AVAPAR'!$A$1:$C$317333,3,FALSO),SI(C75=BUSCARV(C75,'[Validacion AVPAR y EXPFIN.xlsx]EXPFIN'!$A$1:$C$54483,1,FALSO),BUSCARV(C75,'[Validacion AVPAR y EXPFIN.xlsx]EXPFIN'!$A$1:$C$54483,3,FALSO),"No se encontró en las bases de Avance Procesal ni Expediente Final de RE. Salvo que se encuentre en algún convenio de los mencionados por RE"))

    ResponderEliminar
    Respuestas
    1. Hola Manuel,
      si alguno de esos BUSCARV que empleas en las condiciones devuelve N/A todo te devolverá N/A...
      Prueba con SI.ERROR para controlar la no coincidencia
      Saludos

      Eliminar
    2. muchas gracias Ismael ya quedo

      Eliminar
  10. Amigo como hago para unir estas dos formulas..=+BUSCARV([OrdenID],Ordenes!A:C,3,FALSO)
    =+BUSCARV([Vendedor],Empleados!A:D,4,FALSO)

    ResponderEliminar
    Respuestas
    1. Hola Emerson,
      en qué forma pretendes 'unirlas' y con qué sentido??
      son dos búsquedas sobre dos hojas distintas, con dos valores buscados diferentes..
      no veo el nexo..

      Saludos

      Eliminar
  11. Hola Ismael
    tengo un problema con una base de datos que tiene 15 columnas con datos de alumnos de 1 año hasta 5 año con sus notas y cada año tiene 5 secciones y necesito una macro que busque los alumnos existentes;que se pueda agregar un alumno nuevo y lo coloque en la sección que corresponde Ejep. 3 A o 5 E etc. Que se grabe, también poder editar en caso que tenga un dato errado. he tratado de hacer y me sale error.gracias

    ResponderEliminar
    Respuestas
    1. Hola
      tendría que ver el fichero que ya tienes para localizar el fallo y poder comentarte
      Si quieres envíamelo, tras leer las Normas de uso del blog, a:
      excelforo@gmail.com

      Saludos

      Eliminar
  12. Hola Ismael, tengo un problema parecido, solo que son más variables y todas esas variables juntas dan resultados diferentes, ahí mas o menos como se haría, se siguen las mismas instrucciones? Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola David,
      habría que ver la distribución de los datos.. pero a priori el modelo explicado serviría para más de tres tablas
      Saludos

      Eliminar

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