Neste artigo você aprenderá como retornar o texto que mais se repete no Excel com fórmulas, explicação passo-a-passo e download da planilha.
1. Introdução
No Excel podemos retornar o valor numérico mais frequente através das funções MODA e MODO.ÚNICO, por exemplo, em fórmulas simples. No entanto, para dados que são textos, estas funções não retornam aquele com a maior frequência.
Em virtude disto, este artigo propõe diferentes fórmulas para este fim, disponibilizando a exibição do valor mais frequente, sendo este um texto.
Para fins de estudo tomaremos como base os dados ilustrados na imagem a seguir, cujos dados analisados estão contidos entre B3 e B15.
Dentre todos os valores, AL é o mais recorrente, sendo, desta forma, a moda. No tópico a seguir serão citadas e demonstradas fórmulas para o retorno da moda com base nestes dados.
2. Aninhamentos de funções para exibir a moda para textos
Uma alternativa inicial para retornar o texto mais recorrente é contar as ocorrências de todos eles. Aquele com a máxima contagem de ocorrências é o nosso alvo desejado, visto que será o que mais ocorre. Desta forma, podemos usar a função CONT.SE para contar todos os textos. Adicionalmente podemos usar a função MÁXIMO para retornar a maior contagem. Por fim, usamos as funções ÍNDICE e CORRESP para retornar o texto mais recorrente a partir da maior contagem.
A fórmula inserida para retornar a moda foi =ÍNDICE(B3:B15;CORRESP(MÁXIMO(CONT.SE(B3:B15;B3:B15));CONT.SE(B3:B15;B3:B15);0)).
Uma fórmula mais curta e com menos funções utilizadas se baseia em encontrar as posições das primeiras ocorrências de cada texto com a função CORRESP. Como cada texto ao se repetir terá sempre a posição de sua primeira ocorrência retornada, podemos então calcular a moda destas posições, visto que são números.
Esta fórmula é bem mais inteligente e funciona como se nós estivéssemos representando cada texto por um código, para poder então calcular a moda destes.
A fórmula inserida para retornar a moda foi =ÍNDICE(B3:B15;MODO(CORRESP(B3:B15;B3:B15;0))).
A função MODO é antiga e existe em modo de compatibilidade com o Excel 2007. Foram lançadas 2 funções em substituição: MODO.ÚNICO e MODO.MULT. A função MODO.ÚNICO retorna a moda de valores, sendo que exibe o primeiro encontrado em caso de moda com múltiplos resultados. Esta é a diferença fundamental para MODO.MULT, que retorna todas as modas, caso em que muitos valores são os mais recorrentes (empates). Assim, a nossa fórmula supracitada, poderá ser reescrita com a substituição de MODO por MODO.ÚNICO e MODO.MULT. A moda de nossos dados, que é AL, é absoluta. Não há outro texto tão recorrente quanto AL. Por isso, para quaisquer das 3 funções citadas, o resultado será AL.
Logo, quando houver empate, apenas MODO.MULT será capaz de exibir todos os itens modais, como ilustra a imagem seguinte, em que AL e SE ocorrem igualmente 5 vezes nos dados.
A fórmula inserida foi =ÍNDICE(B3:B15;MODO.MULT(CORRESP(B3:B15;B3:B15;0))).
Podemos otimizar a nossa fórmula, que apresenta múltiplos retornados, exibindo os valores em apenas uma célula, através de concatenação. Abaixo temos uma aplicação em que foi usada a função UNIRTEXTO com a vírgula sendo usada como separador entre os valores da moda.
A fórmula inserida foi =UNIRTEXTO(“, “;;ÍNDICE(B3:B15;MODO.MULT(CORRESP(B3:B15;B3:B15;0)))).
Download da Planilha Retornar o Texto que Mais se Repete no Excel
Para realizar o download desta planilha de exemplo clique no botão abaixo
