Filtro avançado do Excel - um guia completo com exemplos

Assistir ao vídeo - Filtro avançado do Excel

O Filtro Avançado do Excel é um dos recursos mais subestimados e subutilizados que encontrei.

Se você trabalha com Excel, tenho certeza que já usou (ou pelo menos ouviu falar do filtro regular do Excel). Ele filtra rapidamente um conjunto de dados com base na seleção, texto especificado, número ou outros critérios.

Neste guia, vou mostrar algumas coisas legais que você pode fazer usando o filtro avançado do Excel.

Mas primeiro … O que é Filtro Avançado do Excel?

Filtro avançado do Excel - como o nome sugere - é a versão avançada do filtro regular. Você pode usar isso quando precisar usar critérios mais complexos para filtrar seu conjunto de dados.

Aqui estão algumas diferenças entre o filtro regular e o filtro avançado:

• Embora o filtro de dados regular filtre o conjunto de dados existente, você pode usar o filtro avançado do Excel para extrair o conjunto de dados para algum outro local também.
• O Filtro Avançado do Excel permite que você use critérios complexos. Por exemplo, se você tiver dados de vendas, poderá filtrar os dados em um critério em que o representante de vendas é Bob e a região é Norte ou Sul (veremos como fazer isso nos exemplos). O suporte do Office tem uma boa explicação sobre isso.
• Você pode usar o Filtro Avançado do Excel para extrair registros exclusivos de seus dados (mais sobre isso em um segundo).

FILTRO EXCEL AVANÇADO (exemplos)

Agora vamos dar uma olhada em alguns exemplos de como usar o Filtro Avançado no Excel.

Exemplo 1 - Extraindo uma lista única

Você pode usar o Filtro Avançado do Excel para extrair rapidamente registros exclusivos de um conjunto de dados (ou em outras palavras, remover duplicatas).

No Excel 2007 e em versões posteriores, há uma opção para remover duplicatas de um conjunto de dados. Mas isso altera seu conjunto de dados existente. Para manter os dados originais intactos, você precisa criar uma cópia dos dados e usar a opção Remover duplicados. O filtro avançado do Excel permite que você selecione um local para obter uma lista exclusiva.

Vamos ver como usar filtros avançados para obter uma lista exclusiva.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo:

Como você pode ver, existem registros duplicados neste conjunto de dados (destacados em laranja). Isso pode ser devido a um erro na entrada de dados ou resultado da compilação de dados.

Nesse caso, você pode usar a ferramenta Filtro Avançado do Excel para obter rapidamente uma lista de todos os registros exclusivos em um local diferente (para que seus dados originais permaneçam intactos).

Aqui estão as etapas para obter todos os registros exclusivos:

• Selecione todo o conjunto de dados (incluindo os cabeçalhos).
• Vá para a guia Dados -> Classificar e Filtrar -> Avançado. (Você também pode usar o atalho de teclado - Alt + A + Q) Isso abrirá a caixa de diálogo Filtro avançado.
• Na caixa de diálogo Filtro avançado, use os seguintes detalhes:
  • Açao: Selecione a opção ‘Copiar para outro local’. Isso permitirá que você especifique o local onde pode obter a lista de registros exclusivos.
  • Intervalo da lista: Certifique-se de que se refere ao conjunto de dados a partir do qual você deseja encontrar registros exclusivos. Além disso, certifique-se de que os cabeçalhos no conjunto de dados estejam incluídos.
  • Intervalo de critérios: Deixe em branco.
  • Copiar para: Especifique o endereço da célula onde deseja obter a lista de registros exclusivos.
  • Copiar somente registros exclusivos: Marque esta opção.
• Clique OK.

Isso lhe dará instantaneamente uma lista de todos os registros exclusivos.

Cuidado: Ao usar o Filtro avançado para obter a lista exclusiva, certifique-se de também selecionar o cabeçalho. Do contrário, consideraria a primeira célula como o cabeçalho.

Exemplo 2 - Usando critérios no filtro avançado do Excel

Obter registros exclusivos é uma das muitas coisas que você pode fazer com o filtro avançado do Excel.

Sua principal utilidade reside em sua capacidade de permitir o uso de critérios complexos para filtrar dados.

Aqui está o que quero dizer com critérios complexos. Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja obter rapidamente todos os registros em que as vendas são superiores a 5.000 e a região são os EUA.

Veja como você pode usar o Filtro Avançado do Excel para filtrar os registros com base nos critérios especificados:

• A primeira etapa ao usar o Filtro Avançado do Excel com critérios complexos é especificar os critérios. Para fazer isso, copie os cabeçalhos e cole-os em algum lugar da planilha.
• Especifique os critérios pelos quais deseja filtrar os dados. Neste exemplo, como queremos obter todos os registros para os EUA com vendas acima de 5.000, insira 'US' na célula abaixo de Região e> 5000 na célula abaixo de Vendas. Isso agora seria usado como uma entrada no Filtro avançado para obter os dados filtrados (conforme mostrado nas próximas etapas).
• Selecione todo o conjunto de dados (incluindo os cabeçalhos).
• Vá para a guia Dados -> Classificar e Filtrar -> Avançado. Isso abrirá a caixa de diálogo Filtro avançado.
• Na caixa de diálogo Filtro avançado, use os seguintes detalhes:
  • Açao: Selecione a opção ‘Copiar para outro local’. Isso permitirá que você especifique o local onde pode obter a lista de registros exclusivos.
  • Intervalo da lista: Certifique-se de que se refere ao conjunto de dados a partir do qual você deseja encontrar registros exclusivos. Além disso, certifique-se de que os cabeçalhos no conjunto de dados estejam incluídos.
  • Intervalo de critérios: Especifique os critérios que construímos nas etapas acima. Neste exemplo, seria F1: I3.
  • Copiar para: Especifique o endereço da célula onde deseja obter a lista de registros exclusivos.
  • Copiar somente registros exclusivos: Marque esta opção.
• Clique OK.

Isso forneceria instantaneamente todos os registros em que a região são os EUA e as vendas são superiores a 5.000.

O exemplo acima é um caso em que a filtragem é feita com base em dois critérios (EUA e vendas superiores a 5000).

O filtro avançado do Excel permite que você crie muitas combinações diferentes de critérios.

Aqui estão alguns exemplos de como você pode construir esses filtros.

Usando os critérios AND

Quando você deseja usar os critérios AND, é necessário especificá-los abaixo do cabeçalho.

Por exemplo:

• Para filtrar registros quando a região for os EUA E o representante de vendas for Joe.
• Para filtrar registros quando a região for os EUA E o valor de vendas for maior que 5000.
• Quando a região for os EUA E as vendas serão registradas após 31-03-2017.

Usando os critérios OR

Quando você deseja usar critérios OR, você precisa especificar os critérios na mesma coluna.

Por exemplo:

• Para filtrar registros quando a região é os EUA OU a região é a Ásia.
• Para filtrar registros quando o representante de vendas é Bob OU Martha.

Agora, você deve ter percebido que quando temos os critérios na mesma linha, é um E critérios, e quando o temos em linhas diferentes, é um OU critério.

Exemplo 3 - Usando caracteres WILDCARD no filtro avançado do Excel

O Filtro Avançado do Excel também permite o uso de caracteres curinga durante a construção dos critérios.

Existem três caracteres curinga no Excel:

1. * (asterisco) - Representa qualquer número de caracteres. Por exemplo, ex * pode significar excel, excels, example, expert, etc.
2. ? (ponto de interrogação) - Representa um único personagem. Por exemplo, Tr? Mp pode significar Trump ou Tramp.
3. ~ (til) - É usado para identificar um caractere curinga (~, *,?) No texto.

Agora vamos ver como podemos usar esses caracteres curinga para fazer alguma filtragem avançada no Excel.

• Para filtrar registros onde o nome do representante de vendas começa com J.

Observe que * representa qualquer número de caracteres. Portanto, qualquer representante com o nome começando com J seria filtrado com esses critérios.

Da mesma forma, você também pode usar os outros dois caracteres curinga.

Observação: caso esteja usando o Office 365, você deve verificar a função FILTRO. Ele pode fazer muitas coisas que um filtro avançado pode fazer com uma fórmula simples.

NOTA:

1. Lembre-se de que os cabeçalhos nos critérios devem ser exatamente iguais aos do conjunto de dados.
2. A filtragem avançada não pode ser desfeita quando copiada para outros locais.