Resumo
Para filtrar e extrair dados com base em vários critérios complexos, você pode usar a função FILTER com uma cadeia de expressões que usam lógica booleana. No exemplo mostrado, a fórmula em G5 é:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4))
Esta fórmula retorna dados onde:
a conta começa com "x" E a região é "leste" e o mês NÃO é abril.
Explicação
Neste exemplo, precisamos construir uma lógica que filtre os dados para incluir:
a conta começa com "x" E a região é "leste" e o mês NÃO é abril.
A lógica de filtragem desta fórmula (o argumento de inclusão) é criada encadeando três expressões que usam lógica booleana em matrizes nos dados. A primeira expressão usa a função LEFT para testar se a conta começa com "x":
LEFT(B5:B16)="x" // account begins with "x"
O resultado é uma matriz de valores TRUE FALSE como este:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
A segunda expressão testa se Região é "leste" com o operador igual a (=):
C5:C16="east" // region is east
O resultado é outra matriz:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
A terceira expressão usa a função MONTH com a função NOT para testar se o mês não é abril:
NOT(MONTH(D5:D16)=4) // month is not april
que produz:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Observe que a função NOT inverte o resultado da expressão MONTH.
Todas as três matrizes são multiplicadas juntas. A operação matemática força os valores TRUE e FALSE para 1s e 0s, então, neste ponto, podemos visualizar o argumento include como este:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
A multiplicação booleana corresponde à função lógica AND, então o resultado final é uma única matriz como esta:
(0;0;0;1;1;0;0;0;0;1;0;1)
A função FILTER usa essa matriz para filtrar os dados e retorna as quatro linhas que correspondem aos 1s na matriz.
Critérios de extensão
As expressões usadas para criar o argumento de inclusão no filtro podem ser estendidas conforme necessário para lidar com filtros ainda mais complexos. Por exemplo, para filtrar ainda mais os dados para incluir apenas linhas em que o valor> 10.000, você pode usar uma fórmula como esta:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4)*(E5:E16>10000))
