sábado, 25 de abril de 2009

Búsquedas basadas en varias columnas a la vez

En el artículo de hoy vamos a ver cómo podemos realizar búsquedas dentro de una tabla basadas en elementos de varias columnas a la vez. Supongamos que tenemos la siguiente tabla:
Queremos introducir en la ENTRADA DE DATOS la Referencia y el Tipo para, en la SALIDA DE DATOS, obtener información sobre el Coste, el PVP y el Descuento asociado (Dcto), información con la que, además, realizaremos algunos cálculos:
Como parece evidente necesitamos realizar una búsqueda basada en dos elementos de distintas columnas (Referencia y Tipo). Para solucionar este problema (con el menor esfuerzo posible) debemos seguir los siguientes pasos:
1. Seleccionamos el rango D2:H11 y vamos al menú Insertar/Nombres/Crear. Por defecto, en el menú que aparecerá, vienen marcadas las opciones nombres en columna superior y nombres en columna izquierda. Dejamos seleccionada solamente nombres en columna superior y aceptamos. De esta manera habremos creado los nombres de cada columna de nuestra tabla (puede comprobarlo abriendo el Cuadro de Nombres que se encuentra a la izquierda de la barra de fórmulas) que se corresponderán precisamente con los rótulos de cada columna de dicha tabla.
2. El siguiente paso será preparar la entrada de datos con sendas listas desplegables para que seleccionemos de éstas la Referencia y Tipo que nos interese. Para ello nos situamos en la celda B2 y vamos al menú Datos/Validación de datos y seleccionamos Permitir/Lista. En el cuadro Origen escribimos =Referencia  ,que es el nombre del rango que contiene los elementos de nuestra lista.
3. Nos situamos en la celda B3 y vamos al menú Datos/Validación de datos y seleccionamos Permitir/Lista. En el cuadro Origen seleccionamos el rango E3:E5 (fíjese que en este rango están los tres tipos que se repiten para las distintas referencias y por ello no hace falta seleccionar el rango E3:E11 o utilizar el Nombre de Rango Tipo). Aceptamos.

Ya tenemos la entrada de datos preparada para seleccionar, por medio de listas desplegables, la referencia y tipo con las que nos interese trabajar. Ya sólo nos queda formular... 

Para resolver "el problema" de buscar en varias columnas (en nuestro caso dos) vamos a utilizar las funciones INDIRECTO, COINCIDIR, CONCATENAR e INDICE, con entrada matricial (las funciones INDIRECTO y COINCIDIR han sido utilizadas en numerosos ejemplos de este blog que puede consultar si lo necesita). La función CONCATENAR nos permite lo que su nombre indica, es decir, unir varios argumentos en uno solo. El operador "&" hace las mismas funciones que CONCATENAR. Puede probar esta función situándose, por ejemplo, en la celda D14 de nuestro modelo y escribiendo la siguiente fórmula: ="El Coste de "&D3&E3&" es de "&F3    , y el resultado que obtendrá será: El Coste de MontañaGTX es de 452
La función INDICE, por otro lado, nos proporciona el dato que se encuentra en una fila y columna determinada de una matriz.

4. Nos situamos en B6 y escribimos la siguiente fórmula:
=INDICE(INDIRECTO(A6);COINCIDIR($B$2&$B$3;Referencia&Tipo;0))
PERO NO PULSAMOS ENTER. Como estamos trabajando con matrices dentro de la función COINCIDIR, tenemos que realizar una entrada matricial y para ello debemos pulsar las teclas Ctrl+Shift+Enter. El resultado será la misma fórmula pero con llaves:
{=INDICE(INDIRECTO(A6);COINCIDIR($B$2&$B$3;Referencia&Tipo;0))}

Fíjese que hemos utilizado "&" (que es lo mismo que CONCATENAR) dentro de la función COINCIDIR. De esta manera le estamos pidiendo que una B2 y B3 y que los busque en el rango llamado Referencias y Tipo, que también hemos unido con "&". El resultado de esta parte de la fórmula será el número de fila de nuestra tabla que ocupa la unión de B2 y B3. Conseguido esto sólo nos queda indicarle de qué columna queremos la información y asunto resuelto. Fíjese que en B6 queremos obtener el Coste. Para ello sería suficiente con poner el nombre de este rango (que es precisamente Coste) como primer argumento de la función INDICE. Pero en B7 y en B8 necesitaremos el PVP y el Descuento respectivamente. Para resolverlo con una sola fórmula he utilizado INDIRECTO(A6) en vez del nombre de rango correspondiente (y, por supuesto, referencias absolutas en B2 y B3). 

5. Copiamos la fórmula escrita en B6 en las celdas B7 y B8. Problema resuelto.
6. Las fórmulas de B10 y B11 no tienen ningún misterio, a saber:
    en B10:   =B7*(1-B8)        para calcular el precio menos el descuento.
    en B11:   =(B10-B6)/B6   para calcula el porcentaje de margen tras el descuento.


16 comentarios:

  1. Gracias, me ha ayudado mucho

    ResponderEliminar
  2. Hola Excelente ayuda, pero tengo una duda, cuando aplico la operacion para matricial (Ctrl+Shift+Enter) no realiza dicha funcion de poner la misma formula entre llaves, que puedo hacer al respecto, gracias y saludos.

    ResponderEliminar
  3. Debes pulsar la tecla Ctlr y la tecla Shift y manteniendo ambas pulsadas entonces pulsa Enter.

    ResponderEliminar
  4. APRECIABLE KIKO: MUY INTERESANTE ESTE TIP, PERO SI QUISIERA BUSCAR UN VALOR EN DOS O MAS COLUMNAS CON COINCIDIR NO PODRIA, LO HE INTENTADO CON SI(ESERROR Y SI.ERROR. TAMBIEN HE PROBADO CON LA FUNCION DIRECCION Y COINCIDIR(FUNCIONA SI SOLO BUSCO EN UNA COLUMNA), ¿EXISTE ALGUNA FORMA DE LOGRARLO? DE ANTEMANO MUCHAS GRACIAS.
    UNA PAISANA DE LUGO. JUANA GARCIA




    ResponderEliminar
    Respuestas
    1. Buenos días paisana. Necesito que me digas qué es lo que quieres conseguir ¿Buscar un valor y que te diga la dirección en la que se encuentra? ¿Que marque el valor con otro color?...

      Eliminar
    2. !HOLA KIKO! ESTOY MUY AGRADECIDA POR LA RAPIDEZ CON QUE RESPONDES, VOY A INTENTAR EXPLICARLO MEJOR:
      SI BUSCO CON COINCIDIR INDICANDO UN RANGO FORMADO POR 2 O 3 COLUMNAS EL RESULTADO ES #N/A.
      QUIERO REALIZAR LA BUSQUEDA A TRAVES DE VARIAS COLUMNAS COMBINANDO COINCIDIR CON LAS FUNCIONES SI ES ERROR O SI.ERROR. PERO UNO DE LOS INCONVENIENTES ES QUE NO ME DICE LA COLUMNA DONDE SE ENCUENTRA EL VALOR A BUSCAR Y ADEMAS QUIERO SABER LA FILA, ENTONCES QUIERO COMBINARLA CON LA FUNCION DIRECCION (Que aprendí en tu blog) PERO NO LO CONSIGO. POR EJEMPLO CON 2 COLUMNAS HAGO LO SIGUIENTE:
      SI.ERROR(DIRECCION(COINCIDIR(B2;D1:D10;0);SI.ERROR(DIRECCION(COINCIDIR(B2;E1:E10;0)) SIENDO B2 EL VALOR A BUSCAR, POR CADA COLUMNA AÑADO UN SI.ERROR
      ¿ES POSIBLE ALGO ASI? NUEVAMENTE TE EXPRESO MI ADMIRACION POR TU BLOG DEL
      QUE SIGO APRENDIENDO CADA DIA.
      UNA PAISANA DE LUGO. JUANA GARCIA

      Eliminar
    3. Buenos días. Si lo que quieres es obtener la dirección de la celda buscada dentro de una matriz entonces te recomiendo la lectura del siguiente post: http://jldexcelsp.blogspot.com.es/2009/10/encontrar-la-direccion-de-una-celda-en.html

      Un saludiño

      Eliminar
    4. BOAS NOITES KIKO. LA PAGINA A LA QUE ME REMITES YA NO EXISTE, DE TODAS MANERAS YA DI CON LA SOLUCION, AUNQUE SOLO FUNCIONA EN EXCEL 2010, PUESTO QUE LA SINTAXIS ES LA MISMA PARA EXCEL 2003-2010 NO ENTIENDO PORQUE NO FUNCIONA EN 2003. MIL GRACIAS.
      JUANA GARCIA

      Eliminar
    5. Bos días. Estoy entrando en la página que te recomendé y no me da ningún problema ¿? En cualquier caso, si me mandas la solución que has aplicado intento ver por qué no te funciona con 2003.

      Eliminar
  5. BOAS NOITES KIKO! ACABO DE VER TU RESPUESTA Y TE ENVIO A TU CORREO EL ARCHIVO EFECTUADO CON EXCEL 2010.
    NO PUEDO ENVIARTELO CON EXCEL 2003 PORQUE ME DICE QUE HAY DEMASIADOS ARGUMENTOS Y SI NO BORRO LA FORMULA NO ME DEJA SALIR DE EXCEL, AL MENOS YO NO SE HACERLO.
    MUY AGRADECIDA POR TODO. ACABO DE APRENDER (MODA) .
    JUANA GARCIA

    ResponderEliminar
  6. Buenas tardes.
    Recurro a sus conocimientos los cuales nos sirven mucho a personas con poco conocimiento de Excel.
    Tengo un rango con varias columnas y varias filas; en la columna A va un concepto y en la B su valor, en la C un concepto y en la D su valor, y así sucesivamente.
    Necesito busca el valor de un concepto para después descontarlo del valor contenido en otra celda, la cual está fuera del citado rango.
    No he logrado hacer esto que parece muy sencillo pero para mí no lo es.
    Agradezco si me puede brindar su ayuda.
    Henry S

    ResponderEliminar
    Respuestas
    1. Este comentario ha sido eliminado por el autor.

      Eliminar
    2. Hola Henry. Dependiendo de la dimensión de tus datos se puede resolver de distintas maneras. Por favor dime cuántas columnas y filas manejas. En función de la dimensión, se puede solucionar con la función INDIRECTO, creando los nombres de los conceptos previamente, o anidando BUSCARV. Por favor, mándame un mail con tu tabla a kiko.rial@gmail.com y te ayudo. Un saludo.

      Eliminar
    3. Muy amable por su respuesta. Sería interesante conocer varias alternativas para aplicar la que más se adecue a mi necesidad. De inmediato le envío un ejemplo de mi libro. Muchas gracias por su interés.

      Eliminar
  7. Hola Kiko. Me he mirado el articulo de listas desplegables dependientes de abril 2009, y ese me ha traido hasta aqui. Pero aun no consigo resolver mi misterio y estaria muy agradecido si me pusieras ayudar.
    Trabajo con ingenieria y tengo un documento de calculo en el que tengo en cuenta, por ejemplo, las diferentes capas que constituyen la fachada del edificio: hormigon, aislamiento, mortero, lamina impermeable, ladrillo... El unico material que no es estandar es el aislamiento, y es ahi donde querria centrame.
    Tengo distintos tipos de aislamiento dependiendo de su klasse (es un valor numerico, el cual seria uno de los factores a tener en cuenta en la operacion final): por ejemplo: 0,37 0,34 y 0,33. Una vez elegido eso, me gustaria utilizar la funcion indirecto sobre esa lista, para elegir el modelo de aislamiento, dependiendo de una empresa u otra. Pero mi problema es que la lista debe tener el mimo nombre que anteriormente, y no me deja nombrar mi lista con un numero tal como 0,37, en la que podria elegir los diferentes modelos de aislamiento. finalmente, y una vez conseguido eso, mi idea seria utilizar otra funcion indirecto sobre el modelo de aislamiento elegido, para poder elegir el grosor comercial con el que el material es distribuido (seria otroa dato numerico que se tendria en cuenta en mi trabla de operaciones).
    Espero que sea suficientemente entendible pese a la complicidad de explicar las cosas solo mediante texto. Si puedes ayudarme y necesitas ver el archivo, te lo puedo enviar por correo.
    Muchas gracias por tu dedicacion y atencion a nuestras dudas, todos aprendemos mucho de tus conocimientos.

    ResponderEliminar
  8. Hola Luis. La solución pasa por generar un nombre de texto para cada número de klasse. Cuando introduzcas una klasse utilizamos la función BUSCARH para buscar el número en cuestión y qué nombre tiene asociado. Hecho esto ya podemos aplicar la función INDIRECTO contra dicho nombre. Si me mandas tu mail te hago llegar la solución. Un saludo

    ResponderEliminar