production

Função Filtro Excel

Função Filtro Excel

Neste artigo você aprenderá passo-a-passo a utilizar a função Filtro do Excel e detalhadamente.

A função Filtro permite que você realize filtros dinamicamente retornando estes dados ou usando em uma fórmula.

Esta é uma grande melhoria que veio com o Microsoft 365 e permite que você resolva problemas altamente complexos, que anteriormente só eram resolvidos com fórmulas matriciais como esta: Fórmula Matricial com Duas Condições no Excel.

A função Filtro do Excel é um grande avanço, mas antes de mais nada, saiba que ela só está disponível no Excel 365 Desktop, Web, Mobile e para MAC.

Como usar a função Filtro

A função filtro tem poucos parâmetros, como verá abaixo, entendendo o conceito você conseguirá criar fórmulas incríveis com esta função:

SINTAXE:

=FILTRO(matriz; incluir; [se vazia])

EXEMPLO:

=FILTRO($B$10:$D$35;($D$10:$D$35=$I$4);“Não encontrada”)

  • matriz: A matriz é o local aonde estão os dados que você deseja filtrar, basta selecionar o intervalo, que pode ser uma coluna, duas, três, quantas forem. Estes são os dados que serão retornados. Por exemplo $B$10:$D$35. Veja também este artigo aonde eu falo de matrizes dinâmicas:  Matrizes Dinâmicas Excel – O que muda?
  • incluir: Esta é a parte aonde serão inclusos os filtros. Eles seguem sempre a mesma sintaxe. Colocando entre parênteses o filtro e usando operadores como =, <, > ou fórmulas que retornam VERDADEIRO ou FALSO. Por exemplo: ($D$10:$D$35=$I$4), aonde está se verificando se as células do intervalo são iguais ao parâmetro em I4.
  • [se vazia]: Este parâmetro é OPCIONAL, e semelhante a função SEERRO, ela retorna o que for passado se não encontrar dados, por exemplo “Não encontrada”.

Aplicação da função FILTRO:

Na opção passamos uma lista de notas dos alunos e desejamos selecionar a matéria e filtrar somente as notas dos alunos daquela matéria.

Função Filtro Excel 1

Resultado da aplicação da função:

Ao aplicar a fórmula e mudar a matéria automaticamente é retornada uma lista com os alunos e as notas dos alunos de Matemática.

Função Filtro Excel 5

Filtro Excel com E. Filtro com várias condições

A função filtro permite que você tenha várias condições de filtragem, por exemplo, filtrar somente o aluno João Paulo da matéria de Matemática.

Função Filtro Excel 2

Vamos entender a fórmula do exemplo acima:

=FILTRO($B$10:$D$35;($D$10:$D$35=$I$4)*($B$10:$B$35=$I$5);“”)

A parte em azul se refere ao intervalo que desejamos retornar, no caso as três colunas de dados.

Em vermelho, ($D$10:$D$35=$I$4), temos a primeira parte do filtro aonde estamos filtrando os alunos da matéria Matemática e na segunda parte usamos o *(asterisco) para unir com a condição ($B$10:$B$35=$I$5) na qual é realizado o filtro se o aluno é João Paulo.

Então para verificar mais de uma condição, como E no Excel, basta usar o asterisco, pode unir quantos filtros couberem na expressão, sempre coloque entre parênteses cada expressão antes de unir com o *(asterisco).

Resultado da fórmula FILTRO com E:

Função Filtro Excel 4

Filtro OU Excel

Para usar o filtro OU no Excel usamos o operador +(Mais), bastando as expressões entre parênteses do filtro, quantas forem, pode inclusive usar mais parênteses e unir com a expressão E como vimos anteriormente.

Vamos entender em uma aplicação prática.

No exemplo abaixo queremos retornar de uma lista todos os alunos que são da matéria de Matemática OU que chamam Marcinho.

Para isso usamos a seguinte fórmula:

=FILTRO($B$10:$D$35;($D$10:$D$35=$I$4)+($B$10:$B$35=$I$5);“”)

Na primeira parte em azul temos os dados que desejamos filtrar: $B$10:$D$35

Temos então a condição se a matéria é Matemática em vermelho: ($D$10:$D$35=$I$4)

E em laranja temos concatenado com +(OU) se o estudante é Marcinho: ($B$10:$B$35=$I$5)

Temos como resultado da fórmula FILTRO OU então:

Função Filtro Excel 4

Como pode notar foram retornados todos os estudantes da matéria de Matemática ou que se chamam Marcinho, independente da matéria, se trocarmos pelo operador *(E), iríamos retornar apenas o estudante Marcinho da matéria de Matemática.

Função Filtro Números

O Microsoft 365 tem a função Filtro como um grande coringa, pode ser usado em inúmeras situações.

Ela permite além de filtrar textos, também números e datas.

Veja um exemplo de filtro com números.

Queremos retornar da lista de alunos apenas os alunos que tiraram entre 7 e 10

Função Filtro Excel 6

No caso acima, como trabalhamos com intervalo, usamos o operador >= e <=, como já usamos no Excel. Veja a fórmula:

=FILTRO(B10:D35;($C$10:$C$35>=I4)*($C$10:$C$35<=I5);””)

Em azul temos o intervalo a ser filtrado: B10:D35

Em vermelho, temos a nota inicial, então no caso estamos filtrando se na coluna nota temos um valor maior ou igual a 7: ($C$10:$C$35>=I4)

E em laranja temos a segunda condição, concatenada com *(E), pois queremos as notas >=7 E <=10: ($C$10:$C$35<=I5)

Como resultado temos os seguintes dados:

Função Filtro Excel 7

Então, independente da matéria a fórmula trouxe somente os alunos com nota entre 7 E 10 inclusive.

Função Filtro com Texto Contém

Além dos operadores o Excel também permite usar VERDADEIRO e FALSO na função Filtro.

Para isso é usado muitas vezes fórmulas como no caso que iremos demonstrar.

Para este exemplo temos a situação em que desejamos filtrar todos os alunos que tenham C em seu nome.

Função Filtro Excel 8

Para isso usamos a seguinte fórmula:

=FILTRO($B$10:$D$35;(ÉNÚM(LOCALIZAR($I$4;$B$10:$B$35;1)));””)

Entendendo a fórmula:

Na primeira parte temos o que desejamos retornar: $B$10:$D$35

Na segunda parte temos as condições da função filtro: (ÉNÚM(LOCALIZAR($I$4;$B$10:$B$35;1)))

Nesta condição estamos usando a função LOCALIZAR para verificar se encontra a letra C($I$4) no intervalo de nomes dos alunos($B$10:$B$35), procurando a partir da primeira posição (1).

Então se a função LOCALIZAR retornar ela irá retornar a posição em que encontrou a cadeia de caracteres procurada, no caso a letra C, e caso não encontre, irá retornar um erro.

Então usamos a função ÉNÚM, para verificar se o retorno da função LOCALIZAR é um número, e se for, ela irá retornar VERDADEIRO e caso contrário FALSO.

O resultado é uma lista de (VERDAEIRO;FALSO;VERDADEIRO, VERDADEIRO….) que irá ser aplicada na função FILTRO  e retornar somente os VERDADEIROS.

Função Filtro Excel 9

Filtro de Data com a função Filtro

A função Filtro também permite que sejam realizados filtros por data.

Por exemplo, gostaríamos de retornar as categorias de produtos atualizadas em de um determinado intervalo de tempo.

Função Filtro Excel 10

Para isso utilizamos a seguinte fórmula no Excel:

=FILTRO(B10:D35;($C$10:$C$35>=I4)*($C$10:$C$35<=I5);””)

Veja que o nosso exemplo é muito parecido com a situação que tivemos com as notas dos alunos, com o filtro de números.

Então para filtrar intervalos de datas usamos também os operadores de >= e <= e o operador *(E)

Em B10:D35 temos o intervalo que desejamos retornar.

Na primeira parte do filtro, temos a o período e a data inicial que queremos filtrar, no caso maior ou igual a … ($C$10:$C$35>=I4)

Na segunda parte do filtro que unimos com o *(E) temos o período final a filtrar ($C$10:$C$35<=I5).

Como resultado temos então somente os dados atualizados no período solicitado.

Função Filtro Excel 11

Função Filtro Transpor Excel

Caso queiramos também podemos transpor os dados filtrados.

Função Filtro Excel 12

Na imagem acima vemos os dados de um intervalo e gostaríamos de dispor eles na horizontal.

Para isso usamos a função FILTRO e antes delausamos a função TRANSPOR.

=TRANSPOR(FILTRO($B$10:$D$35;($D$10:$D$35=$I$4);””))

A função Transpor do Excel então retorna os dados que estão na vertical na horizontal e vice-versa.

Desta forma o resultado da fórmula, aonde estamos filtrando apenas os alunos de uma determinada matéria é esta:

Função Filtro Excel 13

Filtro Excel TOP N, maiores valores

Também é possível realizar o filtro retornando apenas os maiores valores ou menores valores.

Veja o exemplo.

Função Filtro Excel 14

No filtro acima queremos retornar apenas os 5 alunos com as maiores notas.

Para isso usamos no filtro a função MAIOR, verificando se os valores da coluna de Nota são maiores ou iguais ao 5.º maior valor.

=FILTRO($B$10:$D$35;($C$10:$C$35>=MAIOR($C$10:$C$35;$I$4));””)

Em azul temos o intervalo de retorno: $B$10:$D$35

Em vermelho temos a coluna que queremos comparar o valor $C$10:$C$35

E em laranja temos a condição para realizar o filtro: MAIOR($C$10:$C$35;$I$4)

para a condição verificamos se o valor dos dados em vermelho é maior ou igual ao valor em laranja que retorna a quinta maior nota, conforme passado no parâmetro.

Função Filtro Excel 15

Função Filtro e Classificar Excel

Estas  duas funções são muitas vezes usadas em conjunto, por que a função Classificar permite que um intervalo de dados seja classificado automaticamente.

Veja por exemplo a lista do exemplo anterior:

Função Filtro Excel 15

Perceba que está fora de ordem de nota, e gostaríamos que ficasse da maior para a menor.

Para isso usamos então a função classificar do seguinte modo:

=CLASSIFICAR(FILTRO($B$10:$D$35;($C$10:$C$35>=MAIOR($C$10:$C$35;$I$4));””);2;-1)

De uma forma simples usamos a função Classificar do Excel antes da função Filtro na fórmula.

E nela passamos então o número da coluna que desejamos usar para classificar (2) e passamos também a ordem que desejamos, no caso Decrescente (-1).

O resultado que temos então é uma lista classificada da maior para a menor nota.

Função Filtro Excel 17

Função Filtro Filtrar Repetidos no Excel

O uso de funções em fórmulas que envolvem a função Filtro permitem a realização de tarefas altamente complexas de forma simples.

Neste exemplo veja como filtrar os dados repetidos no Excel usando a função FILTRO e a função CONT.SE.

Queremos consultar somente as células com repetição na lista abaixo.

Função Filtro Excel 18

Para isso usamos a seguinte função:

=CLASSIFICAR(ÚNICO(FILTRO($B$10:$B$35;(CONT.SE($B$10:$B$35;$B$10:$B$35)>=2);””)))

Gostaríamos então de retornar os dados da lista em $B$10:$B$35 que tenham repetidos.

Usamos então a seguinte condição de filtro: (CONT.SE($B$10:$B$35;$B$10:$B$35)>=2)

Esta condição verifique se os dados da lista em vermelho contém mais de um valor, passando como parâmetro a própria lista.

Desta forma para cada item da lista é verificado se há mais de um registro, retornando uma lista de VERDADEIRO e FALSO, que é aplicada nos dados que desejamos retornar.

Além disso usamos a função ÚNICO, que irá retornar somente os valores únicos da lista, dado que a função FILTRO retornaria os dados repetidos e a CLASSIFICAR para retornar os dados de forma classificada no Excel.

Então temos o seguinte retorno:

Função Filtro Excel 22

Comparar listas com a função Filtro do Excel

Com a função filtro você consegue também comparar listas e retornar somente os itens em comum entre as duas.

Veja o seguinte exemplo.

Função Filtro Excel 19

Temos a Lista de alunos 1 e a Lista de alunos 2 e gostaríamos de retornar somente os dados em comum entre as listas.

Para isso usamos a seguinte fórmula:

=FILTRO($B$10:$B$35;(CONT.SE($D$10:$D$21;$B$10:$B$35));””)

Em azul, temos o intervalo que desejamos retornar: $B$10:$B$35

Em vermelho $D$10:$D$21, temos o intervalo de dados que estamos comparando, sempre deve ser o intervalo menor como condição da função CONT.SE que será o intervalo, caso contrário ocorrerá um erro.

E em laranja $B$10:$B$35, temos os critérios do CONT.SE, contar se encontrar este valor, neste caso. O retorno será uma lista de 0 e 1 {0;1;0;0;0;0;0;1;0;0;0;1;0;1;0;1;1;1;1;0;0;0;1;0;0;0}.

Como para o Excel 0 é FALSO e  1 é VERDADEIRO, temos então o retorno dos dados apenas quando encontrar o valor da lista em vermelho na lista em laranja, retornando a lista azul, que é igual ao intervalo da laranja.

O resultado é então:

Função Filtro Excel 20

Função Filtro em Validação de dados no Excel

A função filtro pode ser usada em validação de dados no Excel, mas não diretamente, para isso podemos usar um Nome Definido ou ainda podemos aplicar # para indicar o intervalo.

Para isso criamos o seguinte exemplo.

Temos uma lista de jogadores que podemos escalar, e na medida em que são escolhidos os jogadores escolhidos são retirados da lista de jogadores que podem ser escolhidos.

Função Filtro Excel 21

Para isso usamos a seguinte fórmula:

=FILTRO($G$8:$G$36;(CONT.SE($B$8:$B$18;G8:G36)=0))

No exemplo acima estamos usando a função CONT.SE para verificar a quantidade de itens do intervalo em vermelho estão contidos no intervalo de jogadores, se houver algum ele retorna 1 (VERDADEIRO), senão retorna 0 (FALSO).

Com base nisso criamos uma validação de dados na lista aonde selecionamos os jogadores, o intervalo em vermelho de B8:B18.

Para isso vá em Dados->Validação de dados e inclua a célula aonde está a fórmula e aplique # ao final. No exemplo =$E$8#

Função Filtro Excel 25

Este # indica que o intervalo será entendido como um intervalo de matriz dinâmica, e desta forma a validação de dados entenderá que as informações resultantes da fórmula deverão ser todas listadas na validação. Isso se aplica também ao gerenciador de nomes.

Por fim temos então uma seleção aonde os jogadores selecionados são automaticamente retirados da lista aonde podemos selecioná-los.

Download

Realize o download do da planilha de exemplo de Filtro Excel e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Baixe a planilha