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.

13 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