Já aprendemos como contar células que contêm um texto específico usando a função CONT.SE. Neste artigo, aprenderemos como contar quantas vezes uma palavra aparece na faixa do Excel. Em outras palavras, contaremos quantas vezes uma palavra ocorreu em uma faixa do Excel.
Fórmula Genérica
= SUMPRODUCT (LEN (intervalo) -LEN (SUBSTITUTE (intervalo, palavra, ""))) / LEN (palavra)
Faixa: O intervalo no qual você está tentando contar a palavra específica.
Palavra: A palavra que você deseja contar.
Vamos dar um exemplo e entender como funciona.
Exemplo: conte a palavra “feliz” na faixa do Excel.
Aqui temos algumas frases em células diferentes. Precisamos contar as ocorrências da palavra “feliz” nessa faixa.
Escreva esta fórmula na célula D2.
= SUMPRODUCT (LEN (A2: A4) -LEN (SUBSTITUTE (A2: A4, D1, ""))) / LEN (D1)
Usando esta função, o Excel conta quantas vezes a palavra específica "feliz" aparece no intervalo A2: A4. Esta fórmula do Excel retorna a contagem como 4.
Como funciona?
A ideia é obter a soma da contagem de caracteres de determinada palavra no intervalo e, em seguida, dividir pelo comprimento da palavra. Por exemplo, se Happy está ocorrendo 4 vezes em um intervalo, seu comprimento total é 20 (4 * 5) no intervalo. Se dividirmos 20 por 5, obtemos 4. Que é a contagem de palavras no intervalo.
Vamos derrubar por dentro.
LEN (A2: A4): retorna a contagem de caracteres em cada célula como uma matriz {49; 27; 34}.
Próximo LEN (SUBSTITUIR (A2: A4, D1, "")): A função de substituição substitui a palavra em D1 com “” em cada célula do intervalo A2: A4. Em seguida, a função Len retorna a contagem de caracteres dessas sentenças substituídas em uma matriz {39; 22; 29}.
LEN (D1): retorna o comprimento da palavra em D1 que 5 (feliz).
Agora a fórmula é simplificada para SUMPRODUCT ({49; 27; 34} - {39; 22; 29}) / 5. Após a subtração de matrizes, SUMPRODUCT tem SUMPRODUTO ({10; 5; 5}) / 5. A função adiciona o array e obtemos 20/5. O que nos dá nosso resultado 4.
Contagem insensível a maiúsculas e minúsculas
Uma vez que SUBSTITUTO diferencia maiúsculas de minúsculas, a fórmula acima irá ignorar qualquer palavra que não tenha o mesmo caso, ou seja, “Feliz”. Para fazer a fórmula acima ignorar maiúsculas e minúsculas, devemos mudar a caixa de cada palavra para a caixa da palavra que estamos procurando. É assim que é feito.
= SUMPRODUTO (LEN (A2: A4) -LEN (SUBSTITUIR (SUPERIOR (A2: A4), SUPERIOR (D1), ""))) / LEN (D1)
Agora o caso não importa mais. Esta função contará cada palavra em D1 independentemente do caso.
Possíveis erros:
Uma parte da palavra de outra palavra será contada: neste exemplo, se tivéssemos a palavra "felicidade" (apenas por exemplo, sei que não há palavra como esta), ela também teria sido contada. Para evitar isso, você pode ter cercado as palavras com especificações, “” & D1 e “”. Mas quando a palavra aparece primeiro ou último na frase, isso também falhará.
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.