Excel

Categorizar texto con palabras clave

Categorize Text With Keywords

Fórmula de Excel: categorizar texto con palabras claveFórmula genérica
{= INDEX (categories, MATCH (TRUE, ISNUMBER ( SEARCH (keywords,text)),0))}
Resumen

Para categorizar texto usando palabras clave con una coincidencia 'contiene', puede usar la función BUSCAR, con la ayuda de INDICE y COINCIDIR. En el ejemplo que se muestra, la fórmula en C5 es:

cómo encontrar una celda en Excel
|_+_|

dónde palabras clave es el rango con nombre E5: E14 y categorias es el rango con nombre F5: F14.



Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.



Explicación

En el fondo, esto es un yo Función NDEX y MATCH .

Dentro de la función COINCIDIR, usamos la Buscando función para buscar celdas en la columna B para cada palabra clave enumerada en el rango con nombre palabras clave (E5: E14):



|_+_|

Porque estamos buscando varios elementos (en el rango nombrado palabras clave ), obtendremos varios resultados como este:

|_+_|

¡El valor! Se produce un error cuando SEARCH no puede encontrar el texto. Cuando SEARCH encuentra una coincidencia, devuelve un número que corresponde a la posición del texto dentro de la celda.

Para cambiar estos resultados a un formato más utilizable, usamos el Función ISNUMBER , que convierte todos los valores a VERDADERO / FALSO así:



|_+_|

Esta matriz entra en la función COINCIDIR como el lookup_array , con el valor de búsqueda establecido como VERDADERO. COINCIDIR devuelve la posición del primer VERDADERO que encuentra en la matriz (7 en este caso) que se proporciona al Función INDICE como el fila_num :

|_+_|

ÍNDICE devuelve el séptimo elemento en categorias , 'Auto', como resultado final.

Con XLOOKUP

Con el Función XLOOKUP , esta fórmula se puede simplificar un poco. XLOOKUP puede utilizar la misma lógica utilizada dentro de la función COINCIDIR anterior, por lo que la fórmula equivalente es:

cuántos días laborables entre dos fechas
|_+_|

XLOOKUP ubica el primer VERDADERO en la matriz y devuelve el valor correspondiente de categorias .

Fórmulas de matriz dinámica están disponibles en Oficina 365 solamente.

Evitar coincidencias falsas

Un problema con este enfoque es que puede obtener coincidencias falsas de subcadenas que aparecen dentro de palabras más largas. Por ejemplo, si intenta hacer coincidir 'dr', también puede encontrar 'Andrea', 'beber', 'seco', etc. ya que 'dr' aparece dentro de estas palabras. Esto sucede porque SEARCH automáticamente hace una coincidencia 'contiene'.

Para un truco rápido, puede agregar espacio alrededor de las palabras de búsqueda (es decir, 'dr' o 'dr') para evitar atrapar 'dr' en otra palabra. Pero esto fallará si 'dr' aparece primero o último en una celda, o aparece con puntuación, etc.

Si necesita una solución más precisa, una opción es normalizar el texto primero en un columna de ayuda , teniendo cuidado de agregar también un espacio inicial y final. Luego, puede buscar palabras completas rodeadas de espacios.

Autor Dave Bruns


^