☰

MS Excel: asignación de "categorías" basadas en palabras clave

tag-icon • tag-icon microsoft-excel
MS Excel: asignación de "categorías" basadas en palabras clave

Tengo un archivo de Excel con gastos (la cantidad de dinero gastado está en una columna) y en la siguiente columna tengo una breve descripción que en su mayoría está compuesta por varias palabras. Quiero "simplificar" la descripción y asignar una o dos palabras a cada descripción, que estarían en otra columna al lado. El problema es que la descripción no está "unificada", por ejemplo puedo tener cadenas como "almuerzo de negocios", "cena de negocios en el restaurante XXX", "café con periodistas", etc., y me gustaría asignarles a estas descripciones "comida". " etiqueta. También hay diferentes categorías que siguen un patrón similar.

Mi idea era crear otra tabla (en una hoja diferente): en una columna tengo palabras clave como "café", "almuerzo", "cena" y en la columna al lado tengo etiquetas que quiero que se asignen, que es "comida". ". Utilicé la función vlookup con coincidencia aproximada, pero me devuelve resultados incorrectos. Por alguna razón, el orden de las palabras en la lista parece afectar los resultados, y aunque hay una coincidencia parcial (exacta en una palabra de la cadena), vlookup la ignora y devuelve algo más. Por ejemplo, tengo "estacionamiento en el hotel xxx" y en la tabla tengo el par "estacionamiento" - "gastos de viaje", vlookup devuelve la etiqueta "comida".

¿Puedes ayudarme a resolver este problema? (¿Hay algún enfoque diferente que sugerirías?)

Respuesta1

Quieres la función FIND()y/o SEARCH(). Uso:

FIND(find_text, within_text)
                                                                devuelve la posición inicial de la primera cadena de texto
                                                                dentro de la segunda cadena de texto (comenzando en la posición 1)

Entonces FIND("lunch", "lunch with customer")devuelve 1 y FIND("lunch", "business lunch")devuelve 10. Si la primera cadena no se encuentra en la segunda, esto devuelve un #VALUE!valor de error.  SEARCH()es como FIND()excepto por el hecho de que FIND()distingue entre mayúsculas y minúsculas y SEARCH()no lo es. Entonces

FIND("lunch", "Lunch with customer")regresa #VALUE!
pero
SEARCH("lunch", "Lunch with customer")regresa 1

Asumiré que querrás usar SEARCH(), el que no distingue entre mayúsculas y minúsculas.

Querrás configurar una matriz como esta:

                                                        lista de palabras clave

Probablemente sea mejor hacer esto en una hoja aparte; llamémoslo Key-Sheet. Luego, en su hoja de datos: si su descripción de formato libre está en una columna A (comenzando en la celda A1), ingrese lo siguiente en la celda B1:

=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))

y presione Ctrl+ Shift+ Enter, para convertirla en una “fórmula matricial”. (Se mostrará en la barra de fórmulas entre llaves). Explicación:

  • SEARCH('Key-Sheet'!$A$1:$A$7,$A1)– para cada palabra clave de la columna Ade la hoja clave (“café”, “almuerzo”, “cena”, etc.), búsquela en la descripción en la fila, columna actual A, de la hoja de datos (por ejemplo, “almuerzo de negocios ”). Esto creará unaformaciónque contiene { #VALUE!; 10; #VALUE!; … } (siete elementos (en este ejemplo), uno por palabra clave; el segundo muestra el resultado de “almuerzo”, que está en 'Key-Sheet'!A2).
  • IFERROR(…,LEN($A1)+1)– reemplazar #VALUE!valores con 15, que, siendo LEN("business lunch")+1, no puede ser un valor de retorno válido SEARCH()(y que, de hecho, es mayor que cualquier posible valor de retorno válido de SEARCH()), pero que es un número válido. Entonces ahora nuestra matriz es { 15; 10; 15; … }.
  • MIN(…)– extraer el valor mínimo de la matriz: en este ejemplo, 10. En general, este será el (primer) regreso exitoso de SEARCH().
  • =MATCH(…, …)– tenga en cuenta que el segundo parámetro MATCH()es el mismo que el primer punto anterior. Entonces estamos buscando 10en la matriz { #VALUE!; 10; #VALUE!; … }. Esto devuelve la posición del 10, que es 2, correspondiente a que A1en la hoja de datos (“almuerzo de negocios”) contiene “almuerzo”, que está en la 2da fila de la Hoja de Claves.

Para obtener la categoría de gastos, es una simple cuestión de indexar en la columna Bde la Hoja Clave. Establecer celda C1en =OFFSET('Key-Sheet'!$B$1,B1-1,0). (No es necesario que sea una fórmula matricial).

                                datos de gastos

Tenga en cuenta (como se indicó anteriormente) que, si una descripción de gasto contiene varias palabras clave, solo encontrará la primera.

Si no quiere molestarse con el valor intermedio, puede simplemente calcular

=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)

Estehacedebe ser una fórmula matricial.

PD: las funciones FIND()y SEARCH()tienen un tercer argumento opcional:

SEARCH(find_text, within_text, [start_num])

Entonces

SEARCH("cigar", "Sometimes a cigar is just a cigar.")devuelve 13
pero
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)devuelve 29

No veo ninguna razón para que lo uses.

Respuesta2

Como dijo Tyson, el "cerca/aprox." El partido no está hecho para palabras. Para citar el archivo de ayuda:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Lo que significa que si busca el valor "7" en "1,2,5,8,12", el valor devuelto sería "5", que es el valor más cercano a 7 que no sea mayor que 7.

No existe una manera fácil de hacer lo que desea sin algún tipo de programación y evaluación exhaustiva de palabras individuales y análisis gramatical.

Lo que debe hacer es entrenarse para ingresar algún tipo de "código de categoría" cuando ingresa originalmente los datos, y luego usar una columna de notas para "detalles adicionales"... como "01-Comida y bebida", "Llevó al jefe a cena por su cumpleaños".

Si ya tiene una gran cantidad de datos y esto puede ser difícil de hacer, puede hacer algunos trucos para acelerar las cosas (aunque aún tendrá que ordenar muchas cosas manualmente).

Comience agregando una columna que verifique la descripción de la palabra "parque" y devuelva 0 si no se encuentra, 1 si se encuentra... algo como "=If(Search("parque",A1)>1,1,0)" (y luego copie automáticamente la fórmula en todas las filas de sus datos). Luego, puede ordenar toda la tabla por esa columna, de modo que sus datos se dividan en dos grupos: descripciones que contienen "parque" y aquellas que no lo tienen. Agregue otra columna para, por ejemplo, aquellas que contengan "comida". Luego, entre "comida" y "parque", puede ordenar (usando ambas columnas) en cuatro grupos: los que no tienen ninguna palabra, los que tienen "comida", los que tienen "parque" y los que tienen ambas.

Al hacer esto repetidamente, puede ordenar rápidamente grupos que claramente son de una categoría u otra, marcarlos con un código de categoría e ignorarlos a partir de ese momento mientras realiza búsquedas de palabras adicionales, hasta que todo haya sido categorizado.

información relacionada