Como Retornar Texto que Mais se Repete no Excel

Neste artigo você aprenderá como retornar o texto que mais se repete no Excel, usando fórmulas e com download gratuito do exemplo.

Material gratuito

Planilha de Controle Financeiro no Excel

Tenha um controle bonito e completo do seu financeiro.

Categorias e subcategorias já configuradas
Planilhas separadas por tabela (configuração, lançamentos, resumo)
Personalize com suas próprias categorias e ícones

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.