Categorizar texto en Hojas de cálculo de Google según el texto parcial coincidente en el índice de categorías

Nov 25 2020

Estoy buscando hacer coincidir, con VLOOKUP, parte de las palabras en una columna en la hoja principal con un índice de palabras clave / categorías donde las palabras parciales se almacenan para facilitar en otra hoja.

Aquí hay una hoja de trabajo de ejemplo: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

  1. La hoja de Gastos tiene los datos tal como los recibimos, en las columnas A a C.
  2. En la columna D, nos gustaría asignar automáticamente una categoría a estos gastos. Si hacemos esto con texto exacto en la columna B, por ejemplo BANK VISA PREAUTH PYMT, entonces es fácil. La fórmula BUSCARV en la columna D funciona al obtener esta referencia precisa de la pestaña 'Categorías' (la celda A1 está redactada exactamente de esta manera). Esta fórmula funciona:=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
  3. El desafío es que nuestros gastos de un tipo similar no siempre seguirán el texto exacto. Por ejemplo: SEND E-TFR US***ke5y SEND E-TFR US***xja. Como tal, nos gustaría hacer coincidir solo el texto SEND E-TFR.

Esto significa que BUSCARV debe coincidir con los parciales en la primera columna de la pestaña 'Categorías'. ¿Cómo se ajusta la fórmula VLOOKUP para lograr esto?

PD. Mi pregunta es bastante similar a esta con la distinción vital de querer hacer coincidir subcadenas.
Categorizar celdas en hojas de cálculo de Google

EDITAR: Respondido.

Respuestas

2 Rubén Nov 25 2020 at 11:31

En este caso VLOOKUPno se puede usar porque algunas de las categorías kyes están contenidas dentro de los datos a ser comparados, por otro lado, el caso es más complejo que la descripción incluida en el cuerpo de la pregunta ya que hay algunas categorías que son una coincidencia parcial entre ellos (es decir, BANK VISA PREAUTH PYMTy Bank Visa) y porque las coincidencias deben hacerse sin distinción entre mayúsculas y minúsculas ( BANK MUTUAL FUNDSdebe categorizarse como Mutual Funds)

Aquí está la fórmula propuesta

=ArrayFormula(ARRAY_CONSTRAIN(
SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE({
IF(
TRIM(B2:B9)=TRIM(TRANSPOSE(Categories!A1:A3)),Categories!B1:B3&"|",),
TRANSPOSE(IF(IFERROR(SEARCH(TRIM(Categories!A4:A8),TRANSPOSE(TRIM(B2:B9))),),Categories!B4:B8&"|",))
}),,1000000))),"|"),
COUNTA(B2:B9),1))
  • B2:B9 son las celdas que tienen los valores que deben compararse con las claves de categorías.

    NOTA: Se supone que no habrá celdas en blanco en este rango.

  • Categories!A1:A3son las celdas que contienen las claves que deberían hacer una coincidencia completa y Categories!B1:B3son las categorías correspondientes

  • Categories!A4:A8son las celdas que contienen las claves que deben hacer una coincidencia parcial y Categories!B4:B8son las categorías correspondientes

2 PKHunter Nov 26 2020 at 01:35

Gracias a @Ruben por la respuesta. Finalmente, decidimos adoptar un enfoque más simple. Solo para verificar la expresión regular de la cadena de búsqueda en BUSCARV y tener esas subcadenas en los índices clave en la pestaña Categorías.

Entonces, si un gasto tiene este texto en la celda B1:

SEND E-TFR US***ke5
SEND E-TFR US***abc
SEND E-TFR US***xyz
...

Lo que estaba causando problemas con las coincidencias exactas, simplemente truncamos la cadena a la subcadena que ocurre antes del segundo espacio:

REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")

Para darnos solo esto:

SEND E-TFR

Y nuestra función VLOOKUP renovada:

=IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "")

El índice de claves de categorías se refiere a ese texto truncado para la búsqueda:

Por lo tanto, coincide y completa la categoría correcta:

Un archivo de prueba para ver que esto funciona es: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

¡Gracias!