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:

Listar elementos según criterio en una tabla.


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).

Listar elementos según criterio en una tabla.


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:

Listar elementos según criterio en una tabla.


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

Listar elementos según criterio en una tabla.
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.

10 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
  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