FILTRO no Excel (FILTER): vários critérios, E e OU

=FILTER(A2:C7,B2:B7="North") retorna todas as linhas de A2:C7 cuja região na coluna B é North. No Excel em português a função se chama FILTRO (FILTER em inglês), e você também pode digitar as fórmulas em português na tabela, com ponto e vírgula: =FILTRO(A2:C7;B2:B7="North"). Você digita a fórmula em uma célula e as linhas que batem são despejadas nas células abaixo e à direita. Mude uma região da coluna B para North, ou um North para South, e a lista se atualiza.

FórmulasRestaurar

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =FILTRO(A2:C7;B2:B7="North")

Só E2 tem fórmula. As outras células preenchidas em E:G são o resultado despejado dela: clique em F3 e você vê que ela pertence à fórmula de E2. Se alguma coisa for digitada nessa área, a FILTRO mostra #DESPEJAR! (#SPILL! em inglês; a tabela mostra os nomes de erro em inglês) em vez das linhas (veja erros #DESPEJAR!).

Sintaxe da FILTRO

=FILTER(array, include, [if_empty])

• array é o que você quer de volta: uma coluna, várias colunas ou a tabela inteira.
• include é uma condição com um VERDADEIRO ou FALSO por linha de array, como B2:B7="North". Ela precisa ter exatamente o mesmo número de linhas que array. (Para filtrar colunas, passe um valor por coluna.)
• if_empty é o que mostrar quando nenhuma linha bate. Sem ele, um resultado vazio é o erro #CALC!.

A FILTRO precisa do Excel 2021, do Excel 2024 ou do Microsoft 365. No Excel 2019 e anteriores ela mostra #NOME? (#NAME?), e o botão Filtro da guia Dados é o jeito de filtrar nessas versões. O Google Sheets também tem FILTER, e lá cada condição também pode ser passada como um argumento separado.

As comparações de texto não diferenciam maiúsculas: B2:B7="north" bate com North. A FILTRO mantém as linhas na ordem original; ordenar o resultado é um passo à parte, mostrado mais abaixo.

Filtrar pelo valor de uma célula

Deixar "North" fixo na fórmula significa editar a fórmula toda vez. Coloque o valor em uma célula e compare com a célula. Escolha outra região em F1 e o resultado acompanha:

FórmulasRestaurar

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =FILTRO(A2:C7;B2:B7=F1;"No sales")

West não tem linhas, então escolher West mostra o texto de if_empty, No sales.

Números funcionam do mesmo jeito. C2:C7>=F1 com 100 em F1 mantém todas as linhas com vendas de pelo menos 100, e C2:C7>F1 torna a comparação estritamente maior.

FILTRO com vários critérios (E)

Para manter uma linha só quando duas condições são verdadeiras, multiplique as condições. Isto retorna as linhas de North com vendas acima de 100:

FórmulasRestaurar

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =FILTRO(A2:C7;(B2:B7="North")*(C2:C7>100))

Ann (120) e Cara (200) passam. Finn é de North, mas os 60 dele não passam de 100, então ele fica de fora.

Por que multiplicar: cada condição é uma coluna de VERDADEIRO e FALSO, e na aritmética VERDADEIRO vale 1 e FALSO vale 0. Uma linha só dá 1 quando todos os fatores são 1, então o * funciona como E. Cada condição precisa dos próprios parênteses, e você pode encadear quantas quiser: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

A função E() (AND em inglês) não funciona aqui. E(B2:B7="North";C2:C7>100) reduz o intervalo inteiro a um único VERDADEIRO ou FALSO em vez de um por linha, então a FILTRO recebe o formato errado.

FILTRO com OU

Some as condições para manter uma linha quando pelo menos uma delas é verdadeira. Isto retorna as linhas de North e de East:

FórmulasRestaurar

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =FILTRO(A2:C7;(B2:B7="North")+(B2:B7="East"))

Uma linha que atende às duas condições soma 2, e a FILTRO mantém qualquer linha cujo resultado não seja 0, então a soma funciona como OU. Você pode misturar os dois: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) significa (North ou East) e acima de 100. Aqui isso retorna Ann, Cara e Dan.

A FILTRO retorna #CALC! quando nada bate

Quando nenhuma linha passa, a FILTRO não tem nada para retornar. Sem um terceiro argumento, o resultado é o erro #CALC!; com ele, você recebe o seu próprio texto:

FórmulasRestaurar

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! O cálculo não tem resultado, por exemplo um FILTRO que não encontra nada.No Excel em português: =FILTRO(A2:A7;B2:B7="West")

E2 mostra #CALC! e F2 mostra No match. Mude B3 de South para West e as duas fórmulas retornam Ben. Para não mostrar nada, use um texto vazio: =FILTRO(A2:A7;B2:B7="West";"").

Esta tabela também mostra como filtrar uma coluna só: array é A2:A7, então só os nomes voltam. Para pegar algumas colunas da tabela, mas não todas, envolva o resultado na ESCOLHERCOLS (CHOOSECOLS em inglês): =ESCOLHERCOLS(FILTRO(A2:C7;B2:B7="North");1;3) retorna nomes e vendas sem a região. A ESCOLHERCOLS precisa do Microsoft 365 ou do Excel 2024.

Ordenar o resultado da FILTRO

A FILTRO retorna as linhas na ordem em que aparecem na tabela. Envolva a fórmula na CLASSIFICAR (SORT em inglês) para ordenar o resultado: aqui as linhas de North ordenadas por vendas, da maior para a menor. O 3 é a coluna do resultado usada na ordenação, e -1 significa decrescente.

FórmulasRestaurar

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =CLASSIFICAR(FILTRO(A2:C7;B2:B7="North");3;-1)

Cara (200) vem primeiro, depois Ann (120) e Finn (60). Para retornar só as primeiras linhas, envolva mais uma vez na PEGAR (TAKE em inglês): =PEGAR(CLASSIFICAR(FILTRO(A2:C7;B2:B7="North");3;-1);2) mantém as duas primeiras (a PEGAR precisa do Microsoft 365 ou do Excel 2024). CLASSIFICAR e CLASSIFICARPOR cobre as outras opções de ordenação.

FILTRO de linhas que contêm um texto

A FILTRO não tem curingas, então B2:B7="*th*" procura o texto literal *th*. Para manter as linhas cujo nome contém um texto, teste cada célula com a LOCALIZAR, que retorna uma posição quando o texto é encontrado e um erro quando não é, e envolva na ÉNÚM:

FórmulasRestaurar

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =FILTRO(A2:C7;ÉNÚM(LOCALIZAR("an";A2:A7)))

Isto retorna Ann e Dan: a LOCALIZAR ignora maiúsculas, então "an" também bate com o An de Ann. Use a PROCURAR em vez da LOCALIZAR para uma correspondência que diferencia maiúsculas.

Prática: FILTRO com duas condições

FórmulasRestaurar

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Sua vez: Em E2, retorne as linhas (as três colunas) dos vendedores de South com vendas acima de 85.

VerificarVer resposta

Prática: FILTRO por uma célula, com alternativa

FórmulasRestaurar

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Sua vez: Em F3, liste os nomes (só a coluna A) dos vendedores da região digitada em F1. Se não houver nenhum, mostre None.

VerificarVer resposta

Erros comuns com a FILTRO

• Intervalos de alturas diferentes. =FILTRO(A2:C7;B2:B6="North") confere 5 linhas para uma tabela de 6, e o Excel retorna #VALOR! (#VALUE!). Faça o include começar e terminar nas mesmas linhas que o array.
• Zeros onde a origem está vazia. A FILTRO retorna 0 para uma célula vazia do array. Troque as células vazias por texto vazio antes de filtrar: =FILTRO(SE(A2:C7="";"";A2:C7);B2:B7="North").
• Colunas inteiras. =FILTRO(A:C;B:B="North") funciona, mas se a própria fórmula está nas colunas A a C, ela se refere a si mesma. Coloque o resultado ao lado da tabela, ou use um intervalo fixo como A2:C1000.
• Aspas em volta de números. C2:C7>"100" compara números com texto e não mantém nada. Escreva C2:C7>100.
• Esperar o botão Filtro. A FILTRO copia as linhas que batem para outro lugar e deixa a tabela em paz. Para esconder linhas na própria tabela, use Dados > Filtro.