Função de filtro do Excel - explicada com exemplos + vídeo

Assistir ao vídeo - Exemplos de funções de FILTRO do Excel

O Office 365 traz algumas funções incríveis - como XLOOKUP, SORT e FILTER.

Quando se trata de filtrar dados no Excel, no mundo pré-Office 365, dependíamos principalmente do filtro embutido do Excel ou, no máximo, do filtro Avançado ou de fórmulas complexas de SUMPRODUCT. No caso de você ter que filtrar uma parte de um conjunto de dados, geralmente era uma solução alternativa complexa (algo que abordei aqui).

Mas com a nova função FILTER, agora é muito fácil filtrar rapidamente parte do conjunto de dados com base em uma condição.

E neste tutorial, vou mostrar como é incrível a nova função FILTER e algumas coisas úteis que você pode fazer com ela.

Mas antes de entrar nos exemplos, vamos aprender rapidamente sobre a sintaxe da função FILTER.

Caso queira obter esses novos recursos no Excel, você pode atualizar para o Office 365 (junte-se ao programa insider para obter acesso a todos os recursos / fórmulas)

Função de filtro do Excel - sintaxe

Abaixo está a sintaxe da função FILTER:

= FILTRO (matriz, incluir, [se_empty])

• variedade - este é o intervalo de células onde você tem os dados e deseja filtrar alguns dados deles
• incluir - esta é a condição que informa à função quais registros filtrar
• [if_empty] - este é um argumento opcional onde você pode especificar o que retornar caso nenhum resultado seja encontrado pela função FILTER. Por padrão (quando não especificado), ele retorna o #CALC! erro

Agora vamos dar uma olhada em alguns exemplos incríveis de função de filtro e outras coisas que ela pode fazer que costumavam ser bastante complexas em sua ausência.

Clique aqui para baixar o arquivo de exemplo e seguir adiante

Exemplo 1: Filtrando dados com base em um critério (região)

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja filtrar todos os registros apenas para os EUA.

Abaixo está a fórmula de FILTRO que fará isso:

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

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

Esta condição faria com que a função FILTER checasse todas as células da coluna B (aquela que contém a região) e somente aqueles registros que correspondessem a este critério seriam filtrados.

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

Função de filtro retorna um resultado que é uma matriz dinâmica (o que significa que, em vez de retornar um valor, ela retorna uma matriz que se espalha para outras células).

Para que isso funcione, você precisa ter uma área onde o resultado venha a ser vazio. Em qualquer uma das células nesta área (E2: G5 neste exemplo) já tem algo nela, a função irá dar a você o erro #SPILL.

Além disso, como este é um array dinâmico, você não pode alterar uma parte do resultado. Você pode excluir todo o intervalo que contém o resultado ou a célula E2 (onde a fórmula foi inserida). Ambos excluiriam todo o array resultante. Mas você não pode alterar nenhuma célula individual (ou excluí-la).

Na fórmula acima, codifiquei o valor da região, mas você também pode colocá-lo em uma célula e fazer referência à célula que tem o valor da região.

Por exemplo, no exemplo abaixo, tenho o valor da região na célula I2 e isso é referenciado na fórmula:

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

Isso torna a fórmula ainda mais útil e agora você pode simplesmente alterar o valor da região na célula I2 e o filtro mudará automaticamente.

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

Exemplo 2: Filtrando dados com base em um critério (mais que ou menos que)

Você também pode usar operadores comparativos dentro da função de filtro e extrair todos os registros que são mais ou menos que um valor específico.

Por exemplo, suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja filtrar todos os registros em que o valor das vendas é superior a 10.000.

A fórmula abaixo pode fazer isso:

= FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10.000))

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

A fórmula verifica cada registro para o valor na Coluna C. Se o valor for maior que 10.000, ele é filtrado, caso contrário, será ignorado.

Caso queira obter todos os registros inferiores a 10.000, você pode usar a fórmula abaixo:

= FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10.000))

Você também pode ser mais criativo com a fórmula FILTER. Por exemplo, se você deseja filtrar os três primeiros registros com base no valor das vendas, pode usar a fórmula abaixo:

= FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = GRANDE (C2: C11,3)))

A fórmula acima usa a função LARGE para obter o terceiro maior valor no conjunto de dados. Esse valor é então usado nos critérios da função FILTER para obter todos os registros em que o valor de vendas é maior ou igual ao terceiro maior valor.

Clique aqui para baixar o arquivo de exemplo e seguir adiante

Exemplo 3: Filtrando dados com vários critérios (AND)

Suponha que você tenha o conjunto de dados abaixo e deseja filtrar todos os registros para os EUA, onde o valor de venda é superior a 10.000.

Esta é uma condição AND em que você precisa verificar duas coisas - a região precisa para os EUA e as vendas precisam ser superiores a 10.000. Se apenas uma condição for atendida, os resultados não devem ser filtrados.

Abaixo está a fórmula de FILTRO que filtrará os registros com os EUA como a região e vendas de mais de 10.000:

= FILTRO ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US") * ($ C $ 2: $ C $ 11> 10.000))

Observe que o critério (chamado de argumento de inclusão) é ($ B $ 2: $ B $ 11 = ”US”) * ($ C $ 2: $ C $ 11> 10.000)

Como estou usando duas condições e preciso que ambas sejam verdadeiras, usei o operador de multiplicação para combinar esses dois critérios. Isso retorna uma matriz de 0 e 1, em que 1 é retornado apenas quando ambas as condições são atendidas.

Caso não existam registros que atendam aos critérios, a função retornaria o erro #CALC! erro.

E caso você queira retornar algo significativo (ao invés do erro), você pode usar uma fórmula conforme mostrado abaixo:

= FILTRO ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "EUA") * ($ C $ 2: $ C $ 11> 10.000), "Nada encontrado")

Aqui, usei “Não encontrado” como o terceiro argumento, que é usado quando nenhum registro é encontrado que corresponda aos critérios.

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

Você também pode modificar o argumento ‘incluir’ na função FILTER para verificar se há um critério OR (onde qualquer uma das condições fornecidas pode ser verdadeira).

Por exemplo, suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja filtrar os registros em que o país é os EUA ou Canadá.

Abaixo está a fórmula que fará isso:

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

Observe que, na fórmula acima, simplesmente adicionei as duas condições usando o operador de adição. Uma vez que cada uma dessas condições retorna uma matriz de TRUEs e FALSEs, posso adicionar para obter uma matriz combinada onde é TRUE se qualquer uma das condições for atendida.

Outro exemplo pode ser quando você deseja filtrar todos os registros em que o país é os EUA ou o valor de venda é superior a 10.000.

A fórmula abaixo fará isso:

= FILTRO ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "EUA") + (C2: C11> 10.000))

Observação: ao usar critérios AND em uma função FILTER, use o operador de multiplicação (*) e, ao usar os critérios OR, use o operador de adição (+).

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

Você pode usar fórmulas dentro da função FILTER para filtrar e extrair registros onde o valor está acima ou abaixo da média.

Por exemplo, suponha que você tenha o conjunto de dados conforme mostrado abaixo e deseja filtrar todos os registros onde o valor de venda está acima da média.

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

= FILTRO ($ A $ 2: $ C $ 11, C2: C11> MÉDIA (C2: C11))

Da mesma forma, para abaixo da média, você pode usar a fórmula abaixo:

= FILTRO ($ A $ 2: $ C $ 11, C2: C11<>

Clique aqui para baixar o arquivo de exemplo e seguir adiante

Exemplo 6: Filtrando apenas os registros de número EVEN (ou registros de número ímpar)

No caso de você precisar filtrar e extrair rapidamente todos os registros de linhas de números pares ou linhas de números ímpares, você pode fazer isso com a função FILTER.

Para fazer isso, você precisa verificar o número da linha na função FILTER e filtrar apenas os números das linhas que atendem aos critérios de número da linha.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e eu só queira extrair registros pares deste conjunto de dados.

Abaixo está a fórmula que fará isso:

= FILTRO ($ 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 de cada registro (que é fornecido pela função ROW).

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í 1 da parte da LINHA (A2: A11) porque o primeiro registro está na segunda linha e isso ajusta o número da linha para considerar a segunda linha como o primeiro registro.

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

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

Exemplo 7: classificar os dados filtrados com fórmula

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

Por exemplo, se você filtrar um conjunto de dados usando a função FILTER, poderá usar a função SORT com ele para obter o resultado que já está classificado.

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja filtrar todos os registros em que o valor de vendas é superior a 10.000. Você pode usar a função CLASSIFICAR com a função para garantir que os dados resultantes sejam classificados com base no valor de vendas.

A fórmula abaixo fará isso:

= SORT (FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10.000)), 3, -1)

A função acima usa a função FILTER para obter os dados em que o valor de venda na coluna C é maior que 10.000. Essa matriz retornada pela função FILTER é então usada dentro da função CLASSIFICAR para classificar esses dados com base no valor de vendas.

O segundo argumento na função SORT é 3, que deve ser classificado com base na terceira coluna. E o quarto argumento é -1, que deve classificar esses dados em ordem decrescente.

Clique aqui para baixar o arquivo de exemplo

Portanto, estes são 7 exemplos para usar a função FILTER no Excel.

Espero que você tenha achado este tutorial útil!

Você também pode gostar dos seguintes tutoriais do Excel:

1. Como filtrar células com formatação de fonte em negrito no Excel
2. Caixa de Pesquisa de Filtro Dinâmico do Excel
3. Como filtrar dados em uma tabela dinâmica no Excel