miércoles, 16 de marzo de 2011

Listar elementos según criterio en una tabla.

Un usuario preguntaba la manera de listar los elementos coincidentes a un criterio dado de un origen de datos.
En concreto planteaba:

...Dispongo de un archivo en el cual tengo 2 columnas. En la columna A el nombre de un producto y en la B su condición. La condición puede estar repetida.
Lo que necesito es que por mediante una formula una vez elegida la condición es obtener todos los productos que cumplan dicha condición.
Aclaración ya que archivo va hacer utilizado por distintas personas las cuales no tienen el mismo nivel de conocimiento en Excel es que no utilizo filtros avanzados ni macros....


Siendo un ejemplo de origen de datos:


Para conseguir el resultado deseado deberemos añadir un par de columnas auxiliares a nuestro origen de datos.
La primera de ellas aplicaremos a cada elemento la fórmula:
=(B2=$G$2)*1
que nos dirá cuáles de los elementos del campo 'producto' tiene la misma condición buscada (en celda G2).


Observemos como la fórmula evalua si es VERDADERO o FALSO la coincidencia, y al multiplicarlo por 1, obtenemos los valores 0 ó 1, necesarios para calcular nuestra siguiente columna auxiliar.
Aplicamos en la columna D, para cada registro de la tabla origen, la siguiente fórmula:
=(JERARQUIA($C2;$C$2:$C$9;0)+CONTAR.SI($C$2:$C2;C2)-1)*C2
que combina una ordenación estándar con la función JERARQUIA (ver) y CONTAR.SI, lo que nos devolverá una ordenación de nuestros elementos, de acuerdo a la coincidencia o no del criterio buscado.
Acabamos multiplicándolo por el valor de la columna 'Aux1' para trabajar final y únicamente con los elementos del campo 'producto' elegido:


Estamos listos para alcanzar nuestro listado final. Aplicando la fórmula(en Excel 2007 o 2010):
=SI.ERROR(INDICE(producto;COINCIDIR(FILA()-3;$D$2:$D$9;0);1);"")
logramos nuestros listado de productos cuya condición coincide con la elegida.
Para ello, previamente hemos asignado un nombre al rango del campo 'producto':
producto=Hoja1!$A$2:$A$9


haz click en la imagen


Con el segundo argumento, clave para nuestro ejemplo, de la función INDICE:
COINCIDIR(FILA()-3;$D$2:$D$9;0);1)
obtenemos el número de fila correspondiente dentro del rango o matriz del campo 'producto'; necesario para destacar exclusivamente y ordenados los productos seleccionados.

El último apunte viene dado por la función SI.ERROR(valor; valor_si_es_error)
que sirve para eliminar el error devuelto por la fórmula anterior (INDICE(producto;COINCIDIR(FILA()-3;$D$2:$D$9;0);1), que será en aquellos casos que en nuestra columna 'Aux1' teníamos un valor 0, esto es, para aquellos 'producto' no coincidentes con el criterio seleccionado.

16 comentarios:

  1. Excel es muy bueno para la organziación de datos, cuando son poca cantidad, en caso contrario lo mejor es acudir a un ERP o CRM.

    Saludos,


    Juan Torsion

    ResponderEliminar
  2. No me funciona =SI.ERROR(INDICE(producto;COINCIDIR(FILA()-3;$D$2:$D$9;0);1);"")

    ResponderEliminar
    Respuestas
    1. Hola como estás?, un placer saludar igualmente.
      REvisa que has creado el nombre definido que se indica:
      producto
      y que la fórmula final se encuentra en la situación del ejemplo (comienza en fila 4)!!!

      Un cordial saludo

      Eliminar
    2. Hola una consulta, podrías poner una imagen de la fórmula tal cual sería en tu ejemplo? Quiero entender la parte de la fórmula: (FILA()-3 Muchas Gracias!

      Eliminar
    3. Estoy haciendo algo parecido a tu ejemplo pero la diferencia es que en vez de un solo cuadro tengo muchos (con la misma condición) y quiero hacer un listado y agruparlos por una condición, pero como te comento este listado tendría que agrupar productos de diferentes cuadros....podrías ayudarme con la fórmula, no sé como insertarle más rangos. Gracias

      Eliminar
    4. Hola,
      las imágenes están en el post...
      El sentido del FILA()-3 es ajustar el inicio del rango destino, de tal forma que la celda G4 (fila 4) quede ajustada a 1, como primer valor...

      Saludos

      Eliminar
    5. Hola RF
      como estás?, un gusto saludarte
      No sería posible trabajar sobre rangos discontinuos.
      Tendrás que unificar en un único lugar todos los 'cuadros'.
      Saludos

      Eliminar
    6. Hola, me refería a que la fórmula quede expresada tal cual está en el ejemplo para poder analizarla y entenderla. En el listado sólo me sale el producto A y se repite, no aparecen los productos D, F y G. Saludos, gracias por tu tiempo

      Eliminar
    7. la fórmula es la que se indica en le texto y se ve en la imagen
      ;-)

      =SI.ERROR(INDICE(producto;COINCIDIR(FILA()-3;$D$2:$D$9;0);1);"")

      Slds

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

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

      Eliminar
  4. Hola! He utilizado esta formula y me ha servido para optimizar muchas planillas en mi trabajo, pero ahora quisiera hacer lo mismo pero listando segun dos condiciones. Cómo tendría que estructurarla?¡

    Saludos!

    ResponderEliminar
    Respuestas
    1. Hola,
      el asunto obviamente se complicaría... aunque la técnica sería la misma.
      En ese caso usaríamos la función Y para verificar las otras condiciones, en lugar de
      B2=G2 podría ser
      =Y(B2=g2;A2=h2)
      Espero te oriente
      slds

      Eliminar
    2. Gracias! Estoy en eso!!

      Eliminar
  5. Saludos, como puedo cargar una lista con datos de cabecera de cada culumna en un combobox.

    ResponderEliminar
    Respuestas
    1. Hola John,
      un combobox en realidad sirve para seleccionar entre elementos de una lista, quizá si quieres un encabezado necesites un ListBox.
      Echa un vistazo a
      http://excelforo.blogspot.com.es/2011/12/vba-listbox-con-varias-columnas-y.html

      Saludos

      Eliminar