Como Extrair Valores sem Repetição no Excel

Neste artigo você aprenderá como extrair valores sem repetição no Excel com valores únicos.

1. Utilidade das Listas com Valores Sem Repetição

Por diversos motivos precisamos de listas de dados sem repetição. Podemos citar a aplicação de validação de dados com lista suspensa em que é deselegante e inconveniente a existência de repetição. Outra aplicação a citar são as caixas de combinação, controles de formulários presentes na guia Desenvolvedor e que possuem o mesmo princípio de funcionamento da validação de dados citada, sendo povoadas por dados que ficam mais otimizados quando não há repetição.

A lista com dados sem repetição é algo obrigatório, por exemplo, na montagem de relatórios que resumem dados e cuja duplicidades pode gerar totalizadores incorretos. Imagine, por exemplo, o resumo de vendas por cidades com um totalizador ao final. Caso haja repetição de uma ou mais cidades, o total de vendas estará errado e exibirá um valor acima do esperado. Por isso, é crucial evitar repetições para garantir que não haja erros nos resumos de dados.

Dado o exposto, este artigo objetiva expor alguns métodos de “enxugar” listas para exibir apenas uma ocorrência de cada elemento, evitando repetições.

2. Alguns Métodos para Exibir Valores sem Repetição

Elencamos 4 maneiras de obter valores sem repetição a partir de uma lista.

2.1. Usando fórmulas

Vamos propor fórmulas para obter valores sem repetição a partir de uma lista, sendo a primeira baseada na função ÚNICO, que apresenta a vantagem do despejo automático dos dados.

a) Com a função ÚNICO

Esta função, presente do Excel 2021 em diante, incluindo a versão 365 (por assinatura), torna demasiadamente trivial a nossa tarefa. Tudo que precisamos fazer é inserir como argumento para esta função a lista que contém as duplicidades. Os dados serão retornados sem repetições.

Considere os dados a seguir.

Desejamos obter as cidades sem repetição, que são 4: Criciúma, Florianópolis, Joinville e Blumenau. Como comentado, usamos a função ÚNICO, informando a lista de dados, que vai de B3 até B17. A imagem seguinte exibe o resultado.

A fórmula inserida foi: =ÚNICO(B3:B17). Caso o nosso objetivo seja dispor os dados em ordem alfabética, poderemos usar a função CLASSIFICAR. A fórmula adaptada seria: =CLASSIFICAR(ÚNICO(B3:B17)).

Se desejarmos os dados dispostos em linha, em vez de coluna, devemos usar a função TRANSPOR para obter o resultado, conforme ilustra a imagem seguinte.

A fórmula inserida foi: =TRANSPOR(ÚNICO(B3:B17)). Caso necessário, poderemos também classificar alfabeticamente os dados com a função CLASSIFICAR. A fórmula adaptada resultante seria =TRANSPOR(CLASSIFICAR(ÚNICO(B3:B17))) ou ainda =TRANSPOR(ÚNICO(CLASSIFICAR(B3:B17))). Note que estas duas últimas fórmulas propostas possuem apenas uma diferença: a inversão de posições entre CLASSIFICAR e ÚNICO, ou seja: o mais importante é que a primeira função da fórmula (a mais externa) seja a função TRANSPOR.

b) Com as funções ÍNDICE, CORRESP e CONT.SE

Principalmente para viabilizar o uso de versões mais antigas do Excel, podemos aninhar funções de uso mais tradicional no Excel.

A fórmula inserida foi: =SEERRO(ÍNDICE($B$3:$B$17;CORRESP(0;CONT.SE($D$2:D2;$B$3:$B$17);0));””). Esta fórmula usa SEERRO para fazer tratamento sempre que estiver copiada para células excedentes, uma vez que os dados únicos já estiverem retornados. Por sua vez, as funções CORRESP e CONT.SE identificam e retornam a posição da primeira ocorrência de cada cidade. A função ÍNDICE usa essas posições para retornar cada cidade em sua primeira ocorrência.

Apenas para efeito de ilustração, a imagem a seguir propõe mais uma fórmula: =FILTRO(B3:B17;CORRESP(B3:B17;B3:B17;0)=SEQUÊNCIA(LINS(B3:B17))).

3.2. Usando tabelas dinâmicas para valores sem repetição

Curiosamente podemos também usar tabelas dinâmicas para obter listas sem repetição. O processo é bem simples e consiste apenas em arrastar o campo em questão (contendo dados duplicados) para o quadrante de linhas ou colunas.

É óbvio que faz mais sentido partir de uma base de dados com múltiplas colunas (e se tiver, o faça), mas para efeitos didáticos, partiremos do mesmo conjunto de dados explorado no item anterior, com fórmulas.

Ao montar a nossa tabela dinâmica, já obteremos a lista com cidades sem repetição ao incluir a coluna das cidades no quadrante de linhas (apontado pela seta em vermelho na imagem seguinte).

Para “enxugar” esta lista podemos remover o total geral, clicando com o botão direito na sua célula e indicando a opção Remover Total Geral.

Por fim, podemos remover também o cabeçalho do campo, acessando a guia contextual Análise de Tabela Dinâmica, desmarcando a opção Cabeçalhos de Campos, no grupo Mostrar.

Pronto. Teremos então o seguinte resultado (que pode nem parecer ser uma tabela dinâmica, dada a sua apresentação visual “nua”):

Se quisermos os dados dispostos em linhas, em vez de colunas, basta colocar o campo das cidades no quadrante de colunas, removendo também o total geral.

3.3. Usando o recurso de remover duplicadas

Devemos iniciar pela seleção dos dados, que não precisa ser de toda a região (pelo menos uma célula já será suficiente). Feito isso, acessamos a opção Remover Duplicadas. Este recurso está acessível a partir da guia Dados, conforme imagem a seguir, tendo o seu comando no grupo Ferramentas de Dados.

Será exibida então a caixa de diálogo para executarmos o procedimento. Devemos selecionar as colunas desejadas (em nosso caso temos apenas uma coluna, que é exibida já pré-selecionada – destacada em azul). Se nossos dados tiverem cabeçalho, a primeira célula deverá ser ignorada e marcamos a opção Meus dados contêm cabeçalhos. Caso todas as células, incluindo a primeira, sejam dados passíveis da remoção de duplicidades, ou seja, quando a primeira célula não for rótulo da coluna, esta opção deverá ficar desmarcada. Por fim, clicamos em Ok para finalizar.

O Excel então remove as duplicidades e exibe uma mensagem indicando o número de valores duplicados removidos e valores exclusivos remanescentes.

3.4. Usando o Power Query

Usar o Power Query é a melhor alternativa quando o volume de dados for expressivo e quando o trabalho for repetitivo por conta da existência de dados que se atualizam com frequência.

Partindo do pressuposto de que os dados estão em planilha local, devemos tê-los formatados como tabela, que em nosso caso se chama tabCidades.

Para iniciar, vamos carregar estes dados no Power Query. Com a tabela selecionada (pelo menos uma de suas células já será suficiente), na guia Dados, clicamos em De Tabela/Intervalo, comando presente no grupo Obter e Transformar Dados.

O editor do Power Query será exibido com os dados carregados.

Para removermos as duplicidades, clicamos com o botão direito sobre o nome da coluna e apontamos para a opção Remover Duplicadas.

O processo é então executado em uma etapa, com a invocação da função Table.Distinct. Note que restaram apenas as 4 cidades existentes, sem repetição.

Daí em diante, devemos escolher o que fazer com os dados resultantes. Entre as opções, estão a criação de uma conexão com os dados de origem, despejo em tabela ou ainda criação de tabelas dinâmicas ou gráficos dinâmicos. Para isso, acessamos a guia Página Inicial e clicamos em Fechar e Carregar (item destacado com retângulo em vermelho na imagem seguinte – não clicar no ícone com disquete), finalizando com Fechar e Carregar Para.

Será apresentada uma caixa de diálogo final para escolhermos a opção desejada. Em nosso caso, escolhemos inserir e exibir os dados em tabela, a partir da célula D2, confirmando em Ok.