Como usar a função FILTRO do Excel

O Office 365 traz alguns ótimos recursos como XLOOKUP, SORT e FILTER.

Ao filtrar dados no Excel, no mundo pré-Office 365, confiávamos principalmente nos filtros internos do Excel ou, no máximo, em filtros avançados ou fórmulas SUMPRODUCT complexas.Isso geralmente é uma solução complicada se você precisar filtrar parte do conjunto de dados.

Mas com o novo recurso FILTER, agora é muito fácil filtrar rapidamente partes de um conjunto de dados com base nas condições.

Neste tutorial, mostrarei como o novo recurso FILTER é incrível e algumas coisas úteis que você pode fazer com ele.

Mas antes de entrar no exemplo, vamos dar uma olhada rápida na sintaxe da função FILTER.

Se você deseja obter esses novos recursos no Excel, você podeAtualize para o Office 365(Junte-se ao programa interno para acessar todos os recursos/fórmulas)

Função de filtro do Excel – Sintaxe

A seguir está a sintaxe da função FILTER:

=FILTER(matriz,incluir,[if_vazio])

• ordem - este é o intervalo de células onde você tem os dados e deseja filtrar alguns dados dele
• incluir - Esta é a condição que informa à função quais registros filtrar
• [if_vazio] – Este é um parâmetro opcional onde você pode especificar o que retornar se a função FILTER não encontrar resultados.Por padrão (quando não especificado) retorna #CALC!Erro

Agora vamos ver alguns exemplos incríveis de funções de filtro e o que ele pode fazer que costumava ser muito complicado sem ele.

Exemplo 1: filtrar dados com base em uma condição (região)

Suponha que você tenha um conjunto de dados como o abaixo e queira filtrar apenas todos os registros nos Estados Unidos.

Aqui está a fórmula FILTER que faz isso:

=FILTRO($A$2:$C$11,$B$2:$B$11="EUA")

A fórmula acima usa o conjunto de dados como uma matriz e a condição é $B$2:$B$11=”US”

Essa condição fará com que a função FILTER examine todas as células da coluna B (células que tenham o intervalo) e filtre apenas os registros que correspondem a essa condição.

Além disso, neste exemplo, coloco os dados originais e filtrados na mesma planilha, mas você também pode colocá-los em planilhas separadas ou até mesmo em pastas de trabalho.

A função de filtro retorna uma matriz dinâmica de resultados (o que significa que, em vez de retornar um valor, ela retorna uma matriz que transborda para outras células).

Para isso, você precisa ter uma área onde o resultado esteja vazio.Já existe algo em qualquer célula do intervalo (E2:G5 neste exemplo) e a função lhe dará o erro #SPILL.

Além disso, como este é um array dinâmico, você não pode alterar parte do resultado.Você pode excluir todo o intervalo com o resultado ou a célula E2 (onde a fórmula é inserida).Ambos excluirão toda a matriz de resultados.Mas você não pode alterar nenhuma célula (ou excluí-la).

Na fórmula acima, codifiquei o valor do intervalo, mas você também pode colocá-lo em uma célula e fazer referência a essa célula com o valor do intervalo.

Por exemplo, no exemplo abaixo, tenho o valor do intervalo na célula I2 e depois faço referência a ele na fórmula:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Isso torna a fórmula ainda mais útil, agora você só precisa alterar o valor do intervalo na célula I2 e o filtro mudará automaticamente.

Você também pode ter um menu suspenso na célula I2, onde você pode simplesmente fazer uma seleção e atualizar instantaneamente os dados filtrados.

Exemplo 2: filtrar dados com base em um critério (maior ou menor que)

Você também pode usar operadores de comparação em funções de filtro e extrair todos os registros maiores ou menores que um determinado valor.

Por exemplo, suponha que você tenha o conjunto de dados mostrado abaixo e queira filtrar todos os registros com vendas acima de 10000.

A seguinte fórmula pode fazer isso:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

O argumento array refere-se a todo o conjunto de dados, neste caso a condição ($C$2:$C$11>10000).

A fórmula verifica cada registro quanto ao valor na coluna C.Se o valor for maior que 10000, ele é filtrado, caso contrário, é ignorado.

Se você deseja obter todos os registros inferiores a 10000, pode usar a seguinte fórmula:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

Você também pode ser mais criativo com a fórmula FILTER.Por exemplo, se você quiser filtrar os três principais registros com base nas vendas, poderá usar a seguinte fórmula:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

A fórmula acima usa a função LARGE para obter o terceiro maior valor no conjunto de dados.Em seguida, use esse valor em uma condição de função FILTER para obter todos os registros com vendas maiores ou iguais ao terceiro maior valor.

Exemplo 3: Filtrando dados usando várias condições (AND)

Suponha que você tenha o seguinte conjunto de dados e queira filtrar todos os registros nos Estados Unidos com um valor de vendas superior a 10000.

Esta é uma condição E, você precisa verificar duas coisas - a região precisa estar nos EUA e as vendas precisam ser superiores a 10000.Se apenas uma condição for atendida, os resultados não devem ser filtrados.

Aqui está a fórmula de filtro que filtrará os registros com os EUA como região e com mais de 10000 vendas:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Como estou usando duas condições e preciso que ambas sejam verdadeiras, usei o operador de multiplicação para combinar as duas condições.Isso retorna uma matriz de 0s e 1s, onde 1 só é retornado se ambas as condições forem atendidas.

Se não houver registros correspondentes, a função retornará #CALC!Erro.

Se você quiser retornar algo significativo (não um erro), você pode usar uma fórmula como esta:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Aqui, usei "não encontrado" como o terceiro parâmetro, que é usado quando nenhum registro correspondente é encontrado.

Exemplo 4: Filtrando dados usando vários critérios (OR)

Você também pode modificar o parâmetro "contém" na função FILTER para verificar as condições OR (onde qualquer condição pode ser verdadeira).

Por exemplo, digamos que você tenha o conjunto de dados mostrado abaixo e queira filtrar registros cujo país seja os Estados Unidos ou o Canadá.

Aqui está a fórmula para fazer isso:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Observe que na fórmula acima, estou apenas adicionando as duas condições usando o operador de adição.Como cada uma dessas condições retorna um array de TRUE e FALSE, posso adicionar um array combinado que será TRUE se uma das condições for atendida.

Outro exemplo pode ser quando você deseja filtrar todos os registros em que o país é os Estados Unidos ou o valor de vendas é superior a 10000.

A fórmula a seguir fará isso:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

NOTA: Ao usar a condição AND na função FILTER, use o operador de multiplicação (*) e, ao usar a condição OR, use o operador de adição (+).

Exemplo 5: Filtrar dados para registros acima/abaixo da média

Você pode usar fórmulas na função FILTER para filtrar e extrair registros com valores acima ou abaixo da média.

Por exemplo, digamos que você tenha o conjunto de dados mostrado abaixo e queira filtrar todos os registros com um valor de vendas acima da média.

Você pode fazer isso com a seguinte fórmula:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Novamente, para abaixo da média, você pode usar a seguinte fórmula:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Exemplo 6: Filtre apenas registros pares (ou registros ímpares)

Se você precisar filtrar e extrair rapidamente todos os registros em linhas pares ou ímpares, poderá usar a função FILTER para fazer isso.

Para fazer isso, você precisa verificar o número da linha na função FILTER e filtrar apenas os números de linha que correspondem à condição do número da linha.

Suponha que você tenha um conjunto de dados como abaixo e eu só queira extrair registros pares desse conjunto de dados.

Aqui está a fórmula para fazer isso:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

A fórmula acima usa a função MOD para verificar o número da linha (dado pela função ROW) de cada registro.

A fórmula MOD(ROW(A2:A11)-1,2)=0 retorna TRUE quando o número da linha é par e FALSE quando é ímpar.Observe que subtraí 2 da parte ROW(A11:A1) porque o primeiro registro está na segunda linha, o que ajusta os números das linhas para tratar a segunda linha como o primeiro registro.

Da mesma forma, você pode filtrar todos os registros ímpares com a seguinte fórmula:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Exemplo 7: classificar dados filtrados usando uma fórmula

Usar a função FILTER com outras funções nos permite fazer mais.

Por exemplo, se você usar a função FILTER para filtrar um conjunto de dados, poderá usar a função SORT para obter resultados classificados.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e queira filtrar todos os registros com vendas acima de 10000.Você pode usar a função SORT com esta função para garantir que os dados resultantes sejam classificados de acordo com as vendas.

A fórmula a seguir fará isso:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

A função acima usa a função FILTER para obter os dados na coluna C com vendas superiores a 10000.Em seguida, use a matriz retornada pela função FILTER na função SORT para classificar os dados de acordo com as vendas.

O segundo parâmetro na função SORT é 3, que é classificar de acordo com a terceira coluna.O quarto parâmetro é -1, que classifica os dados em ordem decrescente.

Então esses são 7 exemplos de uso da função FILTER no Excel.

Espero que você tenha achado este tutorial útil.