MS excel - atribuindo “categorias” com base em palavras-chave

Question

Eu tenho um arquivo excel com despesas (a quantidade de dinheiro gasto está em uma coluna), e na próxima coluna eu tenho uma descrição curta que é feita principalmente de várias palavras. Eu quero "simplificar" a descrição e atribuir uma ou duas palavras a cada descrição, que estaria em outra coluna próxima a ela. O problema é que a descrição não é "unificada", por exemplo, eu posso ter strings como "almoço de negócios", "jantar de negócios no restaurante XXX", "café com jornalistas" etc., e eu gostaria de atribuir essa descrição "comida " rótulo. Existem também diferentes categorias que seguem um padrão similar.

Minha ideia era criar outra tabela (em uma folha diferente) - em uma coluna eu tenho palavras-chave como "café", "almoço", "jantar" e na coluna ao lado as etiquetas que eu quero atribuir, é "comida". Eu usei a função vlookup com correspondência aproximada, mas ela me retorna 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, eu tenho "estacionamento no hotel xxx" e na tabela eu tenho 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ê deseja a função FIND() e / ou SEARCH() . Uso:

FIND(find_text, within_text)
                                                                returns the starting position of the first text string
                                                                within the second text string (starting at position 1)

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

FIND("lunch", "Lunch with customer") returns #VALUE!
but
SEARCH("lunch", "Lunch with customer") returns 1

Suponho que você desejará usar SEARCH() , o insensível a maiúsculas e minúsculas.

Você desejará configurar uma matriz assim:

Provavelmente,émelhorfazerissoemumafolhaseparada;vamoschamá-loKey-Sheet.Emseguida,nafolhadedados:SeadescriçãodeformalivreestivernacolunaA(iniciandonacélulaA1),insiraoseguintenacélulaB1:

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

epressioneCtrl+Deslocamento+Enter,paratorná-louma“fórmuladematriz”.(Eleseráexibidonabarradefórmulasentrechaves.)Explicação:

• SEARCH('Key-Sheet'!$A$1:$A$7,$A1)-paracadapalavra-chavedacolunaAdaplanilhadechaves(“café”,“almoço”,“jantar”,etc…),procureporelanadescriçãodalinhaatual,colunaA,deafolhadedados(porexemplo,“almoçodenegócios”).Issocriaráumamatrizcontendo{#VALUE!;%código%;%código%;…}(seteelementos(nesteexemplo),umporpalavra-chave;osegundomostraoresultadopara"almoço", que está em 10 ).
• #VALUE! - substitua 'Key-Sheet'!A2 valores por IFERROR(…,LEN($A1)+1) , que, sendo #VALUE! , possivelmente não pode ser um valor de retorno válido de 15 (e que, na verdade, é maior que qualquer valor de retorno válido possível de LEN("business lunch")+1 ), mas que é um número válido. Então agora nosso array é { SEARCH() ; %código%; %código%; …}.
• SEARCH() - extrai o valor mínimo da matriz: neste exemplo, 15 . Em geral, esse será o (primeiro) retorno bem-sucedido de 10 .
• 15 - note que o segundo parâmetro para MIN(…) é o mesmo que o primeiro marcador, acima. Então, estamos procurando 10 na matriz { SEARCH() ; %código%; %código%; …} Isso retorna a posição do =MATCH(…, …) , que é 2, correspondendo ao fato de que MATCH() na folha de dados (“almoço de negócios”) contém “almoço”, que está na segunda linha da folha de chaves.

Para obter a categoria de despesas, é uma simples questão de indexação na coluna 10 da folha de chave. Defina a célula #VALUE! para 10 . (Isso não precisa ser uma fórmula de matriz.)

Observe(comoprevistoacima)que,seumadescriçãodedespesacontiverváriaspalavras-chave,elaencontraráapenasaprimeira.

Sevocênãoquerseincomodarcomovalorintermediário,bastacalcular

=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)

Issofazprecisaserumafórmuladematriz.

P.S.asfunções#VALUE!e10têmumterceiroargumentoopcional:

SEARCH(find_text,within_text,[start_num])

Então

SEARCH("cigar", "Sometimes a cigar is just a cigar.") returns 13
but
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17) returns 29

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

    

Answer 2

Como Tyson disse, o "fechar / aprox." correspondência não é para 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ê procurar o valor "7" em "1,2,5,8,12", o valor retornado será "5", que é o valor mais próximo de 7 que não é maior que 7.

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

O que você deve fazer é treinar-se para inserir algum tipo de "código de categoria" ao inserir os dados e usar uma coluna de memorando para "detalhes adicionais", como "01-Food and Drink", "Tomou manda sair para jantar em seu aniversário ".

Se você já tem uma grande quantidade de dados onde isso pode ser difícil de fazer, você pode fazer alguns truques para acelerar as coisas (embora você ainda tenha que fazer um monte de coisas manuais ordenando as coisas). / p>

Comece adicionando uma coluna que verifique a descrição da palavra "park" e retorne 0 se não encontrada, 1 se encontrada ... algo como "= If (Search (" park ", A1) > 1,1 , 0) "(e depois copie automaticamente a fórmula para baixo 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 "estacionar" e outras sem. Adicione outra coluna para dizer, aqueles com "comida" neles. Então, entre "comida" e "estacionar", você pode classificar (usando ambas as colunas) em quatro grupos: Uns sem nenhuma palavra, com "comida", com "park" e outros com ambos.

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