Categorizar string com base em algumas palavras foi uma das minhas tarefas básicas na análise de dados. Por exemplo, em uma pesquisa, se você perguntar às pessoas o que elas gostam em um determinado smartphone, as mesmas respostas terão uma variedade de palavras. Para câmera, eles podem usar palavras como fotos, vídeos, selfies, etc. Todas implicam em câmera. Portanto, é muito importante categorizar as frases antes, para obter algumas informações significativas.
Neste artigo, aprenderemos como categorizar no Excel usando palavras-chave.
Vejamos o exemplo da pesquisa sobre a qual falamos.
Exemplo: categorizar dados coletados de uma pesquisa no Excel
Então, fizemos uma pesquisa sobre nosso novo smartphone xyz. Perguntamos aos nossos clientes o que eles gostam no telefone xyz e capturamos suas respostas no Excel. Agora precisamos saber quem gostou de nossa tela de LED, alto-falante e câmera.
Para isso preparamos uma lista de palavras-chave que podem se referir a uma categoria, como você pode ver na imagem abaixo. Para entender, mantive-o pequeno.
Os comentários estão no intervalo A2: A9, as palavras-chave estão em E2: E10 e a categoria está em F2: F10.
A fórmula genérica para criar categorias será:
{= INDEX (Categoria, CORRESPONDÊNCIA (VERDADEIRO, ISNUMBER (PESQUISAR (palavras, frase)), 0))}
Observe as chaves, é uma fórmula de matriz. Precisa ser inserido usando CTRL + SHIFT + ENTER.
Categoria: É o intervalo que contém a lista de categorias. Frases ou feedbacks serão categorizados por esses valores. No nosso caso, é F2: F10.
Palavras: é o intervalo que contém uma lista de palavras-chave ou frases. Estes serão pesquisados no frases. Aqui está E2: E10.
Frase: é a frase que será categorizada. É uma única célula relativa.
Como agora conhecemos cada variável e função usada para categorização no Excel, vamos implementá-la em nosso exemplo.
Na célula B2, escreva esta fórmula e pressione CTRL + SHIFT + ENTER.
{= INDEX ($ F $ 2: $ F $ 10, CORRESPONDÊNCIA (VERDADEIRO, ISNUMBER (PESQUISAR ($ E $ 2: $ E $ 10, A2)), 0))}
Copie a fórmula para ver a categoria de cada feedback.
Precisamos de uma lista de palavras e categorias fixas, elas não devem mudar à medida que copiamos a fórmula, portanto, dei uma referência absoluta de palavras-chave e categorias. Embora desejemos que as sentenças mudem à medida que copiamos a fórmula, é por isso que usei a referência relativa de como A2. Você pode ler informações sobre como fazer referência no Excel aqui.
Agora você pode preparar um relatório de quantos usuários gostaram de tela de LED, alto-falante e câmera.
Como funciona?
O núcleo da fórmula é ISNUMBER (SEARCH($ E $ 2: $ E $ 10, A2)): Eu expliquei em detalhes aqui. A função SEARCH pesquisa cada valor de palavras-chave ($ E $ 2: $ E $ 10) na frase de A2. Ele retorna uma matriz de localização encontrada da palavra ou #VALOR (para a palavra não encontrada). Finalmente, teremos um array de 9 elementos para este exemplo. {#VALOR!; 5; #VALOR!; # VALOR!; # VALOR!; # VALOR!; # VALOR!; # VALOR!; # VALOR!}. Em seguida, usamos a função ISNUMBER para converter esse array em dados úteis. Ele o converte em uma matriz de VERDADEIRO e FALSO. {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Agora em diante, tudo é uma correspondência de índice simples. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): a função MATCH procura TRUE, no array resultante e retorna o índice do primeiro encontrado TRUE. que é 2 para este caso.
ÍNDICE($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Em seguida, a função INDEX examina a 2ª posição na categoria ($ F $ 2: $ F $ 10) que é a tela de LED. Finalmente, esta fórmula categoriza este texto ou feedback como tela de LED.
Fazendo com que seja sensível a maiúsculas e minúsculas:
Para tornar esta função sensível a maiúsculas e minúsculas, use a função FIND em vez da função SEARCH. A função FIND diferencia maiúsculas de minúsculas por padrão.
{= INDEX (Categoria, MATCH (TRUE, ISNUMBER (FIND (palavras,frase)),0))}
Os pontos fracos:
1.Se duas palavras-chave forem encontradas na mesma frase, a frase será categorizada de acordo com a primeira palavra-chave da lista.
Capturando o texto dentro de outra palavra. Suponha que estamos procurando LAD em um intervalo. Em seguida, palavras contendo LAD serão contadas. Por exemplo, Ladders serão contados para LAD, pois contém LAD nele. Portanto, tome cuidado com isso. A prática recomendada é normalizar seus dados o máximo possível.
Portanto, este foi um tutorial rápido sobre como categorizar dados no Excel. Tentei explicar da maneira mais simples possível. Por favor, deixe-me saber se você tiver qualquer dúvida sobre este artigo ou qualquer artigo relacionado ao Excel.
⇬ Fazer download do arquivo:
Categorizar string com base em palavras-chaveComo verificar se a célula contém texto específico no Excel
Como verificar uma lista de textos em sequência no Excel
Obtenha a correspondência de dois critérios COUNTIFS no Excel
Obtenha o COUNTIFS com OR para vários critérios no Excel
Artigos populares :
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.