martes, 5 de septiembre de 2017

Encontrar palabras de una lista en una celda

En el post de hoy trataremos un caso frecuente: cómo localizar elementos de una lista dentro de una celda.

Todo nace a partir de la duda planteada por un usuario:
[...]Tengo que trabajar sobre una base de datos extensa, y en una columna de DESCRIPCIÓN tengo información que no siempre viene escrita de la misma manera. Lo que necesito es identificar una palabra dentro de un texto, y ser capaz de extraer esa palabra, para luego poder hacer categorías mayores.
Lo que necesito es que si aparece en cualquier parte del texto la palabra Azul, la extraiga a la columna GRUPO.
[...]


Inicialmente pensé que no se podría realizar con funciones estándar, y que habría que pasar por una función personalizada... pero después de algunas pruebas conseguí el objetivo mediante el empleo de funciones de Excel: INDICE, ESNUMERO, SUMAPRODUCTO, HALLAR o ENCONTRAR y FILA.

Veamos el planteamiento de partida:

Encontrar palabras de una lista en una celda



La idea a lograr es que en cada una de las celdas de la columna de DESCRIPCIÓN localizar una de las palabras de la 'lista de colores'.

Comenzaremos, para facilitar nuestro trabajo y darle algo de dinamismo, asignándole un nombre definido al campo de la tabla:
Lista =Tabla2[lista colores]

La función que incorporamos a nuestro rango, en la celda B2 es:
=INDICE(Lista;SUMAPRODUCTO(ESNUMERO(HALLAR(Lista;A2))*(FILA(Lista)-1)))

Analizamos la función por partes.
En lo más 'profundo' encontramos
HALLAR(Lista;A2)
(si necesitamos precisión en cuanto a mayúsculas y minúsculas emplearemos la función ENCONTRAR).
Esta función bajo un entorno matricial devuelve un conjunto de números y errores para cada una de las palabras/colores buscados. Por ejemplo en la celda B2 de nuestro ejemplo obtendríamos:
{8;#¡VALOR!;#¡VALOR!}
Al anidarlo dentro de la función ESNUMERO convertimos esa matriz de datos en una matriz lógica de VERDADEROS y FALSOS:
{VERDADERO;FALSO;FALSO}


Por otro lado tenemos una segunda matriz de constantes que corresponderán a las filas en las que se encuentran los colores buscados... llegamos a estos números con lo siguiente:
(FILA(Lista)-1)
que devuelve:
{1;2;3}
Notemos el ajuste de -1 para salvar la posición del encabezado de la Tabla.


La función SUMAPRODUCTO hace el resto, multiplicando ambas matrices elemento a elemento y sumando el resultado de cada multiplicación individual.. retornando un solo valor:
{VERDADERO;FALSO;FALSO} * {1;2;3}
=1+0+0 =1


Ese número retornado, equivale al número de fila del dato buscado dentro del campo de nuestra tabla de colores, se emplea de manera directa con la función de búsqueda INDICE.
Con lo que finalmente conseguimos nuestro objetivo, recuperamos el color que existe en la celda de la 'descripción':

Encontrar palabras de una lista en una celda


Obviamente esto solo sirve para localizar una sola palabra...

11 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. Buenas tardes, Necesito una formula condicional para: "Dar $100.000 por cada año de vida". Los años de vida de las personas no son terminados en cero, sino al contrario terminan en otro dígito como por ejemplo 41, Me podrían ayudar por favor.

    ResponderEliminar
    Respuestas
    1. Hola Edwin,
      creo que este post te podrá servir para calcular la edad y que luego puedas multiplicarla por $100.000:
      http://excelforo.blogspot.com.es/2010/05/calcular-la-edad-con-excel.html
      Espero te oriente
      Saludos

      Eliminar
  3. Hola buen día
    llevo ya varios días probando diversas formulas
    Tengo una base de datos de aprox 5000 contactos.
    en la columna A esta la empresa y en la B el estatus si ya se contacto o esta pendiente por contactar. Ademas que diariamente se van agregando mas empresas a la base.
    Lo que necesito es una funcion que me ayude a buscar algun contacto y si este esta en la base me de su ubicacion, y en caso de que no se encuentre agregarlo, esto para no repetir los contactos.

    ResponderEliminar
    Respuestas
    1. Hola Diana,
      la búsqueda podría realizarse con la función BUSCARV.. pero no hay ninguna fórmula que permita agregarlo automáticamente.
      Habría que realizar un macro que gestionara una cosa y otra.

      Si bien te recomendaría emplearas este formulario estándar de Excel:
      http://excelforo.blogspot.com.es/2010/04/un-formulario-de-datos-en-excel.html
      Te permitirá realizar la búsqueda y desde el mismo interfaz la inserción de nuevos datos

      Espero te sirva
      Saludos

      Eliminar
  4. Muchas gracias Ismael. Me funcionó a la perfección! Es importante destacar que en la Lista no deben haber palabras que tengan una base igual, ejemplo "roja" y "rojas". Teniendo esa precaución, funciona.

    ResponderEliminar
    Respuestas
    1. Gracias por la puntualización Federico,
      sí, claro... al emplear la función HALLAR como base del cálculo, el comportamiento es el que comentas.
      Saludos cordiales

      Eliminar
  5. Hola Ismael, tengo inconvenientes.
    1)tengo que aplicar la combinación de teclas Ctr+shift +enter en la función.
    2)la tabla 1 a cual le asignaste?
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Ricardo,
      1-al emplear SUMAPRODUCTO no es necesario ejecutarla matricialmente
      2-¿a qué tabla 1 te refieres?. solo hay una Tabla (Tabla 2) a la que se le asigna e nombre definido descrito...

      Saludos

      Eliminar
  6. Hola Ismael,
    La formula me funciona perfecto, excepto que al principio me repite los valores de la lista y luego funciona. En tu ejemplo, los primeros valores que arroja son "azul, rojo, verde", igual que la lista de al lado. Pense que era una casualidad, pero luego aplicandola a mi caso (una lista de 73 marcas) los primeros 73 valores son las marcas (tal cual en el orden que aparecen en la lista), aunque en el campo buscado no aparezcan y luego empieza a funcionar. Sabes porque se podria dar?

    ResponderEliminar
    Respuestas
    1. Hola!
      en principio la ubicación de las listas no influye.. de hecho los siguientes datos de la columna A, que no 'coinciden' en posición con nada funcionan bien
      ??
      No sabría decirte.. mándame si quieres el fichero a
      excelforo@gmail.com

      y lo reviso
      Slds

      Eliminar

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