Como eu categorizo uma lista?

Question

Estou procurando categorizar / classificar uma lista combinando determinadas strings. Eu anteriormente não consegui encontrar uma solução, então espero que desta forma seja mais fácil.

Lista de exemplos:

[A]
The apple is the pomaceous fruit of the apple tree
Apples grow on deciduous trees which are large if grown from seed
Apples are an important ingredient in many desserts, such as apple pie
Puréed apples are generally known as apple sauce
A banana is an edible fruit produced by several kinds of large plants
Worldwide, there is no sharp distinction between "bananas" and "plantains"
The term "banana" is also used as the common name for the plants
Orange is the colour of saffron, pumpkins and apricots
The colour orange is named after the appearance of the ripe orange fruit
In ancient Egypt, artists used an orange mineral pigment called realgar
Apple, orange and banana smoothie
Eating an orange and banana exceed allowable sugar intake
Kale or borecole (Brassica oleracea Acephala Group) is a vegetable
Until the end of the Middle Ages, kale was one of the most common green vegetables

Strings sendo procuradas e como elas serão categorizadas (sem distinção entre maiúsculas e minúsculas):

Apple = Apple
Apple Pie = Dessert
Banana = Banana
Orange = Orange
(anything not categorized) = Vegetables
(multiple found strings) = Multiple --> if this isn't possible it's fine

Veja o que a coluna ao lado da lista pode dizer:

[B]
Apple
Apple
Pie
Apple
Banana
Banana
Banana
Orange
Orange
Orange
Multiple
Multiple
Vegetables
Vegetables

E então eu só usaria a classificação / filtro. Obrigado!

    

Answer 1

Eu resolveria isso com o complemento do Power Query. São necessários alguns passos para chegar lá, mas não são necessários códigos ou alterações na estrutura de dados de entrada.

Eu criei um protótipo que você pode visualizar ou fazer o download - é a "demonstração da Consulta ao usuário - pesquisando uma lista de palavras-chave e categorizando" no meu One Drive:

link

Basicamente, minha técnica era criar uma consulta preliminar para carregar a lista de categorias e atribuir uma chave de mesclagem simulada; em seguida, mesclar isso com o texto a ser pesquisado usando uma chave de mesclagem simulada. Isso produz uma linha para cada linha de entrada x cada categoria. Então, calculei a categoria usando a função Text.Contains e finalmente usei um Group By para retornar ao conjunto original de linhas.

Nesse ponto, você tem uma tabela normalizada que é ideal para exploração por filtragem ou uso de uma Tabela Dinâmica e / ou Gráfico Dinâmico.

    

Answer 2

O fato de você querer que ele seja dinâmico E hierárquico (a torta de maçã é mais preciosa que a maçã), torna isso um pouco difícil, mas se você estiver disposto a tê-lo estaticamente programado, poderá fazer algo assim:

Row 1 - Your search text
Row 2 - Your result text

B1=Apple
B2=Apple
B3=If(Len($A2)>LEN(SUBTITUTE(LOWER($A2),LOWER(B$1),"")),B$2,"")
C1=Apple Pie
C2=Deserts
D1=Orange
D2=Orange
Drag B3 across and down

O que você está fazendo é substituir instâncias de "apple" por nada e depois contar as letras para ver se há menos do que o original. Normalmente, essa é uma operação que diferencia maiúsculas de minúsculas, mas usei as letras minúsculas nos dois textos que estou comparando primeiro. Isso produzirá o texto do resultado em cada coluna se houver um hit para o texto de pesquisa de colunas.

Para encorperar o hiarchy você poderia mudar a coluna de B para IF (LEN (C2) > 0, "", NORMAL FORMULA), para que a coluna não diga APPLE se a coluna C já tiver um valor. FÓRMULA NORMAL apenas sendo a fórmula para B3 de cima.

Em seguida, você pode usar uma contagem para avaliar quantas ocorrências você tem

=IF(COUNTA(B2:D2)=0,"Vegitables",IF(COUNTA(B2:D2)>1,"Multiple",B2&C2&D2))

Se houver 0 correspondências, vegitables, se múltiplo e depois múltiplo, caso contrário, você terá apenas um campo preenchido para que possa obter sua resposta final concatenando os resultados.

Além disso, criei uma fórmula que apenas pega uma lista dinâmica e descobre quantos hits você tem. Esta é uma função de matriz, então você terá que digitá-la sem o {} e, em seguida, em vez de digitar, pressione ctrl + shift + enter

{=SUM(--(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($F$1:$F$6),""))>0))}

Isso falha, pois poderia 2 para qualquer coisa que tenha "torta de maçã", já que tem maçã e pizza, mas vence, pois pode funcionar em uma lista dinâmica fornecida na coluna F.