MS Excel - atribuindo "categorias" com base em palavras-chave

Question

Tenho um arquivo Excel com despesas (a quantidade de dinheiro gasto está em uma coluna) e na próxima coluna tenho uma breve descrição que é composta principalmente de várias palavras. Quero "simplificar" a descrição e atribuir uma ou duas palavras a cada descrição, que ficaria em outra coluna ao lado dela. O problema é que a descrição não é "unificada", por exemplo posso ter strings como "almoço de negócios", "jantar de negócios no restaurante XXX", "café com jornalistas" etc., e gostaria de atribuir a essas descrições "comida " rótulo. Existem também diferentes categorias que seguem um padrão semelhante.

Minha ideia era criar outra tabela (em uma planilha diferente) - em uma coluna eu tenho palavras-chave como "café", "almoço", "jantar" e na coluna ao lado delas eu coloco os rótulos que quero atribuir, que é "comida ". Usei a função vlookup com correspondência aproximada, mas ela me retornou resultados incorretos. Por alguma razão, a ordem das palavras na lista parece afetar os resultados e, mesmo que haja uma correspondência parcial (exata em uma palavra da string), o vlookup a ignora e retorna outra coisa. Por exemplo, tenho "estacionamento no hotel xxx" e na tabela tenho o par "estacionamento" - "despesas de viagem", o vlookup retorna o rótulo "comida".

Você pode me ajudar a resolver esse problema? (existe uma abordagem diferente que você sugeriria?)

Answer 1

Você quer a função FIND()e/ou SEARCH(). Uso:

FIND(find_text, within_text)
                                                                retorna a posição inicial da primeira sequência de texto
                                                                dentro da segunda sequência de texto (começando na posição 1)

Portanto, FIND("lunch", "lunch with customer")retorna 1 e FIND("lunch", "business lunch")retorna 10. Se a primeira string não for encontrada na segunda, isso retornará um #VALUE!valor de erro.  SEARCH()é semelhante, FIND()exceto pelo fato de que FIND()diferencia maiúsculas de minúsculas e SEARCH()não é. Então

FIND("lunch", "Lunch with customer")retorna #VALUE!
mas
SEARCH("lunch", "Lunch with customer")retorna 1

Presumo que você desejará usar SEARCH()o que não diferencia maiúsculas de minúsculas.

Você vai querer configurar um array como este:

                                                       

Provavelmente é melhor fazer isso em uma folha separada; vamos chamá-lo Key-Sheet. Em seguida, na sua planilha de dados: Se a sua descrição de formato livre estiver na coluna A (começando em cell A1), insira o seguinte na cell B1:

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

e pressione Ctrl+ Shift+ Enter, para torná-la uma “fórmula de matriz”. (Ele será exibido na barra de fórmulas entre colchetes.) Explicação:

• SEARCH('Key-Sheet'!$A$1:$A$7,$A1)– para cada palavra-chave da coluna Ada planilha chave (“café”, “almoço”, “jantar”, etc…), pesquise-a na descrição na linha, coluna atual A, da planilha de dados (por exemplo, “almoço de negócios ”). Isto criará umvariedadecontendo { #VALUE!; 10; #VALUE!; … } (sete elementos (neste exemplo), um por palavra-chave; o segundo mostra o resultado de “almoço”, que está em 'Key-Sheet'!A2).
• IFERROR(…,LEN($A1)+1)– substitua #VALUE!valores por 15, que, sendo LEN("business lunch")+1, não pode ser um valor de retorno válido de SEARCH()(e que, de fato, é maior que qualquer valor de retorno válido possível de SEARCH()), mas que é um número válido. Então agora nosso array é { 15; 10; 15; … }.
• MIN(…)– extraia o valor mínimo da matriz: neste exemplo, 10. Em geral, este será o (primeiro) retorno bem-sucedido de SEARCH().
• =MATCH(…, …)– observe que o segundo parâmetro MATCH()é igual ao primeiro marcador acima. Então estamos procurando 10no array { #VALUE!; 10; #VALUE!; … }. Isso retorna a posição do 10, que é 2, correspondente ao fato de que A1na ficha técnica (“almoço de negócios”) contém “almoço”, que está na 2ª linha do Key-Sheet.

Para obter a categoria de despesa, basta indexar na coluna Bda Folha-Chave. Defina a célula C1como =OFFSET('Key-Sheet'!$B$1,B1-1,0). (Isso não precisa ser uma fórmula de matriz.)

                               

Observe (conforme prenunciado acima) que, se uma descrição de despesa contiver várias palavras-chave, apenas a primeira será encontrada.

Se você não quiser se preocupar com o valor intermediário, basta 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)

Essefazprecisa ser uma fórmula de matriz.

---

PS as funções FIND()e SEARCH()têm um terceiro argumento opcional:

SEARCH(find_text, within_text, [start_num])

Então

SEARCH("cigar", "Sometimes a cigar is just a cigar.")retorna 13
, mas
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)retorna 29

Não vejo razão para você usá-lo.

Answer 2

Como disse Tyson, o "próximo/aprox." correspondência não se destina a palavras. Para citar o arquivo de ajuda:

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.

O que significa que se você pesquisar o valor “7” em “1,2,5,8,12”, o valor retornado seria “5”, que é o valor mais próximo de 7 que não é maior que 7.

Não há maneira fácil de fazer o que você deseja sem algum tipo de programação e avaliação extensa de palavras individuais e análise gramatical.

O que você deve fazer é treinar para inserir algum tipo de "código de categoria" ao inserir os dados originalmente e, em seguida, usar uma coluna de memorando para "detalhes adicionais".. como "01-Comida e Bebida","Levou o chefe para jantar de aniversário".

Se você já possui uma grande quantidade de dados onde isso pode ser difícil de fazer, você pode fazer alguns truques para acelerar as coisas (embora ainda precise fazer muita classificação manual).

Comece adicionando uma coluna que verifica a descrição da palavra "parque" e retorna 0 se não for encontrado, 1 se for encontrado.. algo como "=If(Search("park",A1)>1,1,0)" (e copie automaticamente a fórmula em todas as linhas dos seus dados). Em seguida, você pode classificar a tabela inteira por essa coluna, para que seus dados sejam divididos em dois grupos: descrições com "estacionamento" e outras sem. Adicione outra coluna para, digamos, aquelas com “comida”. Então, entre “comida” e “parque”, você pode classificar (usando ambas as colunas) em quatro grupos: aqueles sem nenhuma palavra, aqueles com “comida”, aqueles com “parque” e aqueles com ambos.

Fazendo isso repetidamente, você pode classificar rapidamente grupos que são claramente de uma categoria ou de outra, marcá-los com um código de categoria e ignorá-los a partir de então enquanto faz pesquisas adicionais por palavras, até que tudo tenha sido categorizado.