Imagine uma base onde em uma coluna você tem várias informações, possivelmente repetidas. Você quer uma nova relação onde apareçam apenas valores “únicos”, sem estarem duplicados. Quais são os procedimentos para que você obtenha a tal nova lista?
Existem três formas, duas delas já bem difundidas na internet:
- Utilizar a ferramenta “Remover duplicadas”, disponível na aba Dados > Ferramentas de Dados > Remover Duplicadas
- Utilizar a fórmula “ÚNICO”, disponível na versão 365 do Excel
- Utilizar um método de trabalho via CONT.SES
As duas primeiras formas são as bem difundidas na internet (encontramos este conteúdo em diversos locais). Utilizaremos aqui o terceiro método, válido para todas as versões do Excel.
Sua utilização é interessante pois podemos obter via fórmula uma lista de elementos únicos, sem necessitar de procedimento com a ferramenta “Remover duplicadas”. Além disso, sua utilização “inteligente” facilita o uso em qualquer versão do Excel.
Esse artigo tem vídeo disponível no nosso canal no YouTube. Caso queira, veja a versão em vídeo, logo abaixo. Não se esqueça de nos seguir por lá para sempre receber conteúdo!
O exemplo
Suponhamos que, em uma pesquisa, levantamos o time que 33 pessoas torcem. Adicionalmente, coletamos o estado onde essa pessoa mora. O produto final é uma tabela como a abaixo, onde encontramos três colunas: Nome, Estado e Time (nela, temos apenas alguns nomes, sabendo que a tabela completa conta com 33 deles).
Queremos saber:
- Quais são todos os times que têm torcedores, nessa pesquisa?
- Quais são todos os estados envolvidos, nessa pesquisa?
Neste artigo descobriremos como fazer isso, lançando mão de ferramentas já conhecidas no Excel, mas utilizadas de forma criativa e inteligente.
Etapa 1: encontrando a primeira ocorrência
Por fins de didática, tomaremos como exemplo a resposta para a pergunta que envolve os times que têm torcedores.
Como o título da etapa já diz, para que consigamos remover duplicadas de forma “manual”, é fundamental que encontremos a primeira ocorrência. Podemos entender como “primeira ocorrência” a primeira vez que um determinado nome (ou, no caso, “time”) aparece. Por exemplo, na tabela da figura 1, a linha da “Ana Beatriz” representa a primeira vez que aparece “Flamengo” como time, e a linha do Cauã, a segunda.
Para encontrar a primeira ocorrência, faremos uma nova coluna, posterior à coluna “Time” (no exemplo, chamada de Aux_1). Nessa coluna, colocaremos uma fórmula de CONT.SES, que deverá contar quantas vezes ele encontrou aquele time, desde a “primeira linha” da relação até a linha atual. A figura 2, abaixo, ilustra a fórmula.
Note: na célula D2 (a selecionada na figura 2) incluímos a fórmula: “=CONT.SES($C$2:C2;C2)”. Ela significa que, do intervalo que vai de C2 até C2, queremos quantas vezes aparece o conteúdo de C2. O fato de a célula estar travada no primeiro C2 significa que quando deslocarmos a fórmula de D2 para baixo, teremos uma variação de todas as outras células, exceto essa. Isso significa que, para a célula D3, por exemplo, teremos quantas vezes tivemos uma ocorrência como C3 no intervalo de C2 a C3.
Dessa forma, toda vez que estivermos tratando da “primeira ocorrência”, teremos um número 1 na coluna D. Isso porque, no caso de uma segunda ocorrência, o CONT.SES terá contado duas vezes o mesmo valor no intervalo, trazendo o número 2. Exemplo está na figura 3.
Cauã, na linha 8 da planilha, e Daniel, na linha 10, são as segunda ocorrências de, respectivamente, Flamengo e Corinthians (visto que as primeiras foram, também respectivamente, Ana Beatriz e Rebeca).
Etapa 2: destacando a primeira ocorrência
Como já sabemos que as primeiras ocorrências estão marcadas por um 1 na coluna D, fica simples diferenciá-las.
Para isso, transformaremos a coluna D em um “ok” quando o valor do CONT.SES for igual a 1. Caso não seja, traremos um “não ok”. Um “ok” transmite a primeira ocorrência, enquanto um “não ok”, uma ocorrência “repetida”. A figura 4 ilustra a fórmula.
Note na barra de fórmulas que o CONT.SES é o mesmo da etapa anterior. O que fizemos foi aproveitar a fórmula que lá já estava, incluir um “=SE(“ antes dela e um “=1;”ok”;”não ok”)” depois dela. Feito isso, temos a coluna D resultante, com as primeiras ocorrências sinalizadas por um “ok”.
Etapa 3: encontrando todas as primeiras ocorrências
O que precisamos agora é encontrar todas as primeiras ocorrências, ordenando-as.
Para isso, criaremos uma nova coluna auxiliar, alocada na coluna E. A fórmula que colocaremos está na figura 5.
Na coluna E faremos um CONT.SES parecido com o que fizemos na coluna D. A diferença é que dessa vez ele analisará os valores da coluna D e não da coluna C. A ideia deste CONT.SES é trazer quantas vezes, no intervalo que vai de D2 (fixo, marcado por $) até determinada linha, tivemos a ocorrência do item que está na célula de determinada linha (na coluna D). O & denota a junção do conteúdo da célula da coluna D com este CONT.SES (um “concatenar”, para os íntimos).
Exemplos:
- Na linha 6, vemos na coluna E um valor “ok5”, resultado da fórmula “=D6&CONT.SES($D$2:D6;D6)”. Isso significa que ele juntou o conteúdo da célula D6 (o “ok”) com quantas vezes o conteúdo da célula D6 apareceu no intervalo entre D2 e D6. Como tivemos 5 ocorrências de ok neste intervalo, o resultado é um ok5
- Na linha 10, vemos na coluna E um valor “não ok2”, resultado da fórmula “=D10&CONT.SES($D$2:D10;D10)”. Isso significa que ele juntou o conteúdo da célula D10 (o “não ok”) com quantas vezes o conteúdo da célula D10 apareceu no intervalo entre D2 e D10. Como tivemos 2 ocorrências de não ok neste intervalo, o resultado é um não ok2
Etapa 4: listando os valores encontrados
Nosso trabalho agora é encontrar todos os valores listados como ok1, ok2, ok3, …, pois eles representam os valores “únicos”, já removidos duplicadas. Dentre as várias possibilidades, fizemos o mostrado na figura 6.
Onde, tomando como referência a primeira linha da tabela após o cabeçalho (que está na linha 5 da nossa planilha):
- Coluna id: traz números sequenciais, de 1 até a quantidade de números de times (no caso) que temos para mostrar. Podemos conseguir estes números rapidamente fazendo G5 = LIN(A1), o que facilita para quando “puxarmos” a fórmula para baixo
- Coluna nome: concatena “ok” com o número da coluna id. A fórmula é H5 =”ok”&G5
- Coluna pos.: traz qual a posição do item “nome” dentro da coluna E. A fórmula é I5 = CORRESP(H5;E:E;0)
- Coluna nome: traz o nome do time que ocupa a posição demonstrada em pos., dentro da coluna C. A fórmula é J5 = ÍNDICE(C:C;I5;1)
A coluna “nome” traz todos os itens “únicos”, removendo duplicadas, resultado final do nosso exercício. As 4 colunas anteriormente citadas podem ser substituídas por uma única fórmula, que é dada por: =ÍNDICE(C:C;CORRESP(“ok”&LIN(A1);E:E;0);1). Neste exemplo, a fórmula traz a primeira ocorrência, ligada ao ok1. Ao puxar a fórmula para baixo, temos os outros casos. A fórmula nada mais é do que fazermos toda a tabela de uma vez só, substituindo de trás para frente (da coluna nome até a coluna id) as fórmulas de cada coluna.
Etapa 5: um ajuste final
Para fim de acabativa, podemos fazer um ajuste nas fórmulas, de modo a evitar erros de “#N/D”. Estes erros ocorrerão quando puxarmos a fórmula para baixo (chegando em um possível “ok578”, por exemplo) e não encontrarmos este elemento na tabela original. No nosso exemplo, isso começa a ocorrer a partir do ok21, conforme mostrado na figura 7 (visto que só temos 20 times no exemplo e seria natural termos conteúdo até o ok20).
Para evitar este problema, em uma célula devemos fazer um CONT.SES que contará quantos “ok” temos na coluna D (que nos traz as primeiras ocorrências). Feito isso, faremos a tabela da figura 7 (ou a fórmula resumo citada) ir apenas até esse valor. Este avanço está na figura 8.
Note que na célula H2 incluímos a fórmula =CONT.SES(D:D;”ok”), que traz o número de ocorrências de “ok” na coluna D: 20.
Com isso, para a primeira linha da tabela de G a J (linha 5 da planilha), incluímos a estrutura:
- =SE(LIN(A1)<=$H$2; – antes da fórmula que está na célula (tirando apenas o =)
- ;””) – depois da fórmula que está na célula
As fórmulas então ficariam (em negrito está destacado a fórmula anterior):
- id: =SE(LIN(A1)<=$H$2;LIN(A1);””)
- nome: =SE(LIN(A1)<=$H$2;“ok”&G5;””)
- pos.: =SE(LIN(A1)<=$H$2;CORRESP(H5;E:E;0);””)
- nome: =SE(LIN(A1)<=$H$2;ÍNDICE(C:C;I5;1);””)
Assim, os dados só seriam mostrados se a linha da tabela (representada por LIN(A1)) for menor que o número de elementos “ok” encontrados na coluna D (representados por H2). Quando passamos de 20, por exemplo, a célula é mostrada como vazia (“”).
Para a fórmula única que citamos, onde não é preciso construir a tabela, a fórmula seria: =SE(LIN(A1)<=CONT.SES(D:D;”ok”);ÍNDICE(C:C;CORRESP(“ok”&LIN(A1);E:E;0);1);””)
O nosso resultado final, junto com a base inicial, é mostrado na figura 9.
Portanto, é isso. Neste artigo vimos como remover duplicadas de uma base, lançando mão de ferramentas já conhecidas por nós usuários mas que quando utilizadas em conjunto nos trazem a solução citada.
Por fim, quem leu este artigo também se interessou pelos artigos abaixo! Clique para saber mais!
Transformar Linha em Coluna no Excel
Quer aprender mais?
Assim, encerramos o nosso artigo! Hoje você aprendeu sobre como remover duplicadas via fórmulas sem a função ÚNICO.
Ainda assim, você acredita que pode saber mais de Excel? Certamente você quer aprender Excel aprendendo o que realmente interessa! Dessa forma, montamos o Curso Excel Direto ao Ponto para te repassar de forma organizada o que realmente você utilizará! Conheça mais detalhes clicando aqui!