Como aplicar vários critérios de filtragem combinando operações AND e OR com a função FILTER() no Excel

Aplicar vários critérios em diferentes colunas para filtrar o conjunto de dados no Microsoft Excel parece difícil, mas na verdade não é tão difícil quanto parece. A parte mais importante é acertar a lógica dessas colunas. Por exemplo, você deseja ver todos os registros em que uma coluna é igual a x outra é igual a y? Ou você pode querer ver todos os registros onde uma coluna = x outra coluna é igual a y. Os resultados serão muito diferentes. Neste artigo, mostrarei como incluir operações AND e OR na função FILTER() do Excel.

Em vários pontos, você lerá “AND e OR”, o que é gramaticalmente estranho. Estou me referindo às operações AND e OR genericamente. Não usaremos as funções AND() e OR(). Usarei maiúsculas apenas para melhorar a legibilidade.

VEJA: 83 dicas do Excel que todo usuário deve dominar (TechRepublic)

Estou usando o Microsoft 365 em um sistema Windows 10 de 64 bits. A função FILTER() está disponível no Microsoft 365, Excel Online, Excel 2021, Excel para iPad e iPhone e Excel para tablets e telefones Android. Eu recomendo que você espere para atualizar para o Windows 11 até que todos os problemas sejam resolvidos. Para sua conveniência, você pode baixar o arquivo .xlsx de demonstração.

As operações

Os operadores lógicos * e + especificam uma relação entre os operandos em uma expressão (AND e OR, respectivamente). Especificamente, * (AND) requer que os operandos sejam true para retornar true. Por outro lado, + (OR) requer que apenas um operando seja verdadeiro para retornar verdadeiro. No nosso caso, é uma expressão simples. Por exemplo

1 + 3 = 4 E 2 + 1 = 3

retorna verdadeiro enquanto

1 + 3 = 2 E 2 + 1 = 3

retorna falso. Se aplicarmos a operação OR às mesmas expressões

1 + 3 = 4 OU 2 + 1 = 3

retorna verdadeiro porque pelo menos uma expressão é verdadeira e

1 + 3 = 2 E 2 + 1 = 3

retorna true pelo mesmo motivo.

Agora vamos trabalhar com alguns exemplos.

Como usar o filtro interno no Excel

Vamos supor que você acompanhe as comissões usando o conjunto de dados simples mostrado em Figura A. Além disso, você quer saber se alguém está abaixo de uma referência específica – digamos, US$ 200. Felizmente, para usuários que sabem como usar os filtros integrados, você nem precisa da função FILTER(). No entanto, o uso do recurso interno funciona nos dados de origem e é difícil fazer referência em outras expressões; portanto, embora seja fácil, pode não ser a solução certa para todas as situações.

Figura A

Temos dois critérios, pessoal e comissão. Vamos usar um recurso interno para ver o conjunto filtrado para James com qualquer comissão menor ou igual a $200, lembrando que você deve estar trabalhando com um objeto Table:

1. Clique no menu suspenso Pessoal, desmarque (Selecionar tudo), marque James e clique em OK. Fazê-lo vai exibir quatro registros.
2. Na lista suspensa Comissão, escolha Filtros de número e, em seguida, escolha Menor que ou igual a, conforme mostrado em Figura B.
3. Digite 200 no controle à direita do operador de comparação (Figura C) e clique em OK.

Figura B

Figura C

Como você pode ver em Figura DJames tem apenas uma comissão abaixo do valor de referência de US$ 200: US$ 160.

Figura D

É fácil, mas requer um pouco de conhecimento sobre como o recurso funciona. Por outro lado, o recurso funciona com a fonte de dados, e isso pode não ser o que você deseja.

Como usar * no Excel

Você deve ter notado (Figura C) as opções AND e OR na caixa de diálogo em que você inseriu o valor de referência. Esta opção permite que você insira outros critérios para a mesma coluna—Comissão. Para realizar um AND , usaremos o símbolo *, que é semelhante à função AND(), mas AND() não funciona como você poderia esperar quando combinado com FILTER().

Figura E mostra os resultados da entrada da seguinte função

=FILTRO(Tabela1, (Tabela1[Personnel]=J2) * (Tabela1[Commission]<=K2), “Sem resultados”)

em H5. A princípio, nada acontece. Isso porque J2 e K2 estão vazios. Digite James em J2 e 200 em K2. Aplique os formatos apropriados ao conjunto filtrado resultante abaixo, se necessário. É a única coisa com bugs que eu não me importo – as funções de matriz dinâmica ignoram a formatação. Mesmo depois de aplicá-lo ao conjunto de resultados (colunas H a K), ele geralmente desaparece quando FILTER() recalcula.

Figura E

Se você selecionar as diferentes referências, sua função ficará um pouco diferente devido à referência estruturada. Não se preocupe com isso; ainda vai funcionar.

O primeiro argumento, Table1, identifica os dados de origem. O segundo argumento

(Tabela 1[Personnel]=J2) * (Tabela1[Commission]<=K2)

é o que nos interessa. Simplificando, a primeira expressão procura uma correspondência com o valor em J2 na coluna Personnel. Com James como critério para esta coluna, esta expressão retorna True. A segunda expressão procura correspondência com o valor em K2 na coluna Comissão e também encontra um valor menor ou igual a 200, portanto, a expressão retorna True. Por fim, a função retorna os dados das linhas que retornam True. Há apenas um, como antes.

Para verificar outros funcionários, basta alterar o nome em J2. Para alterar a referência de comissão, insira um novo valor em K2. Tenha em mente que o benchmark é sempre menor ou igual à avaliação porque <= está na função. Figura F mostra o resultado da verificação das vendas de Rosa.

Figura F

O caractere * faz um ótimo trabalho ao nos permitir aplicar critérios em várias linhas. Mas e o OU?

Como usar + no Excel

Às vezes, você desejará verificar a existência de um valor em outro. Quando for esse o caso, use o símbolo +. Podemos ver a diferença rapidamente modificando a função em H5: Substitua o símbolo * pelo símbolo +. Como você pode ver em Figura G, há três registros que correspondem aos critérios. O valor pessoal é James, o valor da comissão é menor ou igual a 200.

Figura G

E mais

Nesses exemplos simples, trabalhei com apenas duas colunas, mas não há problema em adicionar mais. Quando você faz isso, os parênteses se tornam muito importantes. Por exemplo, vamos supor que você queira adicionar outra pessoa aos critérios de pessoal, como Rosa—você quer corresponder a qualquer registro em que James Rosa tenha um valor de comissão menor que 200. Novamente, é uma edição simples na função original:

1. Selecione H5.
2. Digite (Tabela 1[Personnel]=J3) * entre a primeira e a segunda expressões para o segundo argumento (Figura H). Envolva as duas primeiras expressões entre parênteses: ((Tabela1[Personnel]=J2) + (Tabela1[Personnel]=J3)) * (Tabela1[Commission]<=K2). Esse conjunto extra de parênteses calcula a operação OR primeiro.
3. Digite Rosa no J3 para ver os resultados em Figura I

Figura H

Figura I

Há dois registros para James Rosa onde a comissão é menor ou igual a 200 (). Para incluir ainda mais pessoal, insira mais linhas e modifique a função adicionando uma nova expressão e lembre-se de que o OR inteiro precisa ser colocado entre parênteses. O mesmo seria verdade se você estivesse referenciando várias expressões do outro lado. Envolva várias expressões AND e envolva várias expressões OR juntas.