Categorize o texto no Planilhas Google com base na correspondência de texto parcial no índice da categoria

Nov 25 2020

Estou procurando combinar, com VLOOKUP, parte das palavras em uma coluna na planilha principal com um índice de palavras-chave / categorias onde palavras parciais são armazenadas para facilitar em outra planilha.

Aqui está um exemplo de planilha: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

  1. A planilha de despesas contém os dados conforme os recebemos, nas colunas A a C.
  2. Na coluna D, gostaríamos de atribuir automaticamente uma categoria a essas despesas. Se fizermos isso com o texto exato na coluna B, por exemplo BANK VISA PREAUTH PYMT, então é fácil. A fórmula VLOOKUP na coluna D funciona obtendo essa referência precisa na guia 'Categorias' (a célula A1 é redigida exatamente dessa maneira). Esta fórmula funciona:=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
  3. O desafio é que nossas despesas do mesmo tipo nem sempre seguem o texto exato. Por exemplo: SEND E-TFR US***ke5e SEND E-TFR US***xja. Como tal, gostaríamos de fazer a correspondência apenas com o texto SEND E-TFR.

Isso significa que VLOOKUP precisa corresponder a parciais na primeira coluna da guia 'Categorias'. Como ajusta a fórmula VLOOKUP para conseguir isso?

PS. Minha pergunta é bastante semelhante a esta com a distinção vital de querer combinar substrings.
Categorizar células nas planilhas do Google

EDIT: respondido.

Respostas

2 Rubén Nov 25 2020 at 11:31

Neste caso VLOOKUPnão pode ser usado porque algumas das categorias kyes estão contidas nos dados a serem correspondidos, por outro lado, o caso é mais complexo do que a descrição incluída no corpo da pergunta, pois existem algumas categorias que são uma correspondência parcial entre eles (ou seja, BANK VISA PREAUTH PYMTe Bank Visa) e porque as correspondências devem ser feitas sem distinção entre maiúsculas e minúsculas ( BANK MUTUAL FUNDSdevem ser categorizadas como Mutual Funds)

Aqui está a fórmula proposta

=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 são as células com os valores que devem ser comparados com as chaves das categorias.

    NOTA: Presume-se que não haverá células em branco neste intervalo.

  • Categories!A1:A3são as células que contêm as chaves que devem fazer uma correspondência completa e Categories!B1:B3são as categorias correspondentes

  • Categories!A4:A8são as células que contêm as chaves que devem fazer uma correspondência parcial e Categories!B4:B8são as categorias correspondentes

2 PKHunter Nov 26 2020 at 01:35

Obrigado a @Ruben pela resposta. Por fim, decidimos adotar uma abordagem mais simples. Apenas para verificar o regexp da string de pesquisa em VLOOKUP, e ter essas substrings nos índices de chave na guia Categorias.

Portanto, se uma despesa tiver este texto na célula B1:

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

O que estava causando problemas com correspondências exatas, apenas truncamos a string para a substring que ocorre antes do segundo espaço:

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

Para nos dar apenas isso:

SEND E-TFR

E nossa função VLOOKUP renovada:

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

O índice da chave das categorias então se refere a esse texto truncado para a pesquisa:

O que, portanto, corresponde e preenche a categoria certa:

Um arquivo de teste para ver isso funcionando é: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

Obrigado!