Blog

Postado em em 21 de maio de 2019

Filtro Avançado – Como filtrar tabela no Excel junto com Validação de Dados e Remover Duplicatas

Caso prefira esse conteúdo no formato de vídeo-aula clique nesse link!

Para baixar a planilha utilizada nessa aula clique aqui!

O que são as ferramentas Remover Duplicatas, Validação de Dados e Filtro Avançado?

São ferramentas encontradas na guia Dados do Excel, são muito úteis e bastante utilizadas. Abaixo vamos ver um pouco do que são cada uma dessas ferramentas e para que servem.

Remover Duplicatas – Como o próprio nome já diz é uma ferramenta muito útil para que o usuário posso remover dados duplicados que possam vir a atrapalhar uma certa ação ou resultado de sua planilha, portanto é fundamental para verificar se existem dados repetidos (pode ser mais de 1) ou até mesmo para verificar se algum dado já foi inserido.

Validação de Dados – A validação de dados é uma forma de permitir certos tipos de dados em uma célula ou conjunto de células. Isso é muito utilizado para que o usuário de uma planilha evite erros ou evite que outras pessoas coloquem dados que não são adequados àquela situação. Como por exemplo, vamos supor que temos um campo de Nome e o usuário coloca números ao invés de letras. É possível utilizar essa ferramenta para dizer àquela célula que ela só irá aceitar texto. É utilizada para restringir o que será inserido na célula.

Filtro Avançado – É um tipo de filtro como os que são vistos nas tabelas, no entanto é possível fazer uma filtragem com mais de um filtro de uma sem vez ter todo o trabalho de ter que selecionar de coluna em coluna o que deseja. O usuário pode colocar todas as condições em uma única linha e fazer toda a filtragem de uma vez, ou em mais linhas se quiser mais de uma filtragem em conjunto. Além disso, ele pode gerar uma nova tabela, não alterando a original.

Quando utilizar as ferramentas Remover Duplicatas, Validação de Dados e Filtro Avançado?

Remover Duplicatas – Será utilizada para remover dados repetidos para evitar erros de cadastro ou análise de dados. No caso que será mostrado iremos utilizar essa ferramenta para analisar quantas unidades temos no total de uma lista em que elas se repetem, ou seja, se tivermos 2 unidades e elas se repetem 20 vezes cada, ao utilizarmos essa ferramenta teremos apenas 2 linhas de resultados, o que vai nos dizer quantas unidades únicas nós temos.

Validação de Dados – Será utilizada para restringir o tipo de dado que poderá ser inserido dentro de uma célula. No exemplo temos as áreas dos funcionários divididas em Administrativo, Compras, Financeiro, Logística, Marketing e RH. Neste caso podemos utilizar a validação de dados para permitir que o usuário escolha apenas uma dessas seis áreas utilizando um menu suspenso. Desta forma o usuário não poderá colocar nenhum outro tipo de dado que não sejam essas opções mostradas.

Filtro Avançado – Será utilizado para fazer uma filtragem mais detalhada sem que o usuário tenha que utilizar filtro por filtro de cada uma das colunas e depois tenha que voltar tudo ao normal.

Como utilizar as ferramentas Remover Duplicatas, Validação de Dados e Filtro Avançado?

Remover Duplicatas

Para testarmos essa ferramenta que fica na guia Dados, vamos primeiramente selecionar a célula C3 e em seguida iremos pressionar CTRL + SHIFT + SETA PARA BAIXO. Desta forma conseguiremos selecionar toda a coluna das unidades dos funcionários.

Repare que todas as unidades serão selecionadas, da primeira até a última célula preenchida. Com as células selecionadas vamos pressionar as teclas CTRL + C para copiar o conteúdo dessas células.

Após copiar os dados vamos selecionar a célula K2 e colar todos os dados copiados utilizando as teclas CTRL + V. Desta forma todos os dados da coluna C serão colados na coluna K.

Ainda com os dados da colagem selecionados vamos na guia Dados e vamos selecionar a opção Remover Duplicatas.

Ao selecionar essa opção será aberta uma nova janela em que devemos marcar a segunda opção que é Continuar com a seleção atual. Depois basta pressionar Remover Duplicatas.

O resultado desta ação é que somente os valores únicos serão apresentados, enquanto os valore repetidos foram todos deletados. Note que o Excel mostra quantos valores duplicados foram removidos e quantos valores exclusivos foram mantidos.

Então é possível notar que em toda a tabela temos apenas 3 unidades de funcionários: Minas Gerais, São Paulo e Rio de Janeiro.

Validação de Dados

Para começarmos com a validação de dados vamos primeiramente mover os dados da coluna K para a coluna R. Para isso basta selecionar as 3 células de unidade e pressionar as teclas CTRL + X (que é a opção para recortar, funciona como a opção de copiar, no entanto na hora de colar ela remove as informações de onde foi copiada).

Após o recorte das três células basta selecionar a célula R2 e colar com as teclas CTRL + V.

Agora que essas informações estão mais isoladas, vamos selecionar a célula H3 para que possamos efetuar a validação de dados.

Para começar com a validação de dados temos que ir até a guia Dados e em seguida selecionar a opção Validação de Dados.

Ao selecionar essa opção será aberta uma nova janela com as opções de validação. Na parte de permitir vamos selecionar a opção lista e em fonte temos que selecionar o intervalo das nossas unidades, que vai da célula R2 até a célula R4.

Após pressionar OK a validação de dados estará feita. Agora basta clicar na célula H3 que uma seta irá aparecer ao lado da célula. Ao clicar nessa seta podemos ver as opções de unidade que temos, que são exatamente as 3 unidades que acabamos obter.

Agora o usuário estará limitado a escolher uma dessas 3 opções que são exatamente as únicas dentro de toda a planilha analisada.

Filtro Avançado

Vamos utilizar essa ferramenta, neste caso, para fazer uma filtragem das pessoas das unidades de São Paulo que são da área Administrativa. Primeiramente temos que deixar essas opções selecionadas.

Feito isso vamos até a guia Dados e vamos selecionar a opção Avançado.

Ao selecionar essa opção será aberta uma nova janela muito importante, que é a janela onde os dados serão selecionados para que possamos fazer a filtragem avançada.

Intervalo da Lista – É o intervalo da nossa tabela que contém todos os dados, neste caso ela vai da célula B2 até a célula F151.

Intervalo de Critérios – É o intervalo que vamos utilizar para fazer a filtragem, ou seja, serão nossos critérios para filtrar a tabela com todos os dados. Neste caso é o intervalo que vai da célula H2 até a célula J3.

Copiar Para – Essa opção só será habilitada quando o usuário selecionar a opção Copiar para outro local. Isso quer dizer que vamos copiar essa filtragem que estamos fazendo em uma nova célula, ou seja, não vamos querer filtrar em cima dos nossos dados para que não haja problema com os dados originais.

Somente registros exclusivos – Essa opção é para que o Excel evite de colocar valores duplicados enquanto faz a filtragem utilizando os critérios selecionados.

Tendo tudo preenchido basta pressionar OK.

É possível notar que uma nova tabela foi gerada baseada nos critérios que selecionamos que foram: unidade de São Paulo e da área Administrativa. Desta forma temos todos os funcionários que se encaixam nesses dois critérios ao mesmo tempo.

Com essa ferramenta é possível fazer filtragens de forma mais eficiente e rápida apenas modificando os critérios.

IMPORTANTE: Apenas modificar os critérios não vai atualizar automaticamente a nova tabela feita, o usuário terá que abrir novamente a opção de filtro avançado marcar novamente as caixas de Copiar para outro local e Somente registros Exclusivos. Depois disso, basta pressionar OK. As outras opções ficam salvas, não há necessidade de modificá-las.

Vamos testar colocando outros critérios.

Feito isso podemos analisar os novos resultados baseado nos novos critérios selecionados.

Portanto podemos fazer diversas filtragens de planilhas grandes de uma forma mais fácil e eficiente, apenas alterando os critérios de filtragem. Lembrando que o usuário poderá escolher diferentes locais para copiar a nova lista de filtragem, ou seja, poderá fazer diferentes filtragens em diferentes locais sem que um sobreponha o outro.

Para continuar recebendo conteúdo sobre esse e outros assuntos de Excel e VBA, acompanhe os nossos posts.