Função FILTRO Excel: Filtros Dinâmicos no Excel

fevereiro 7, 2023fevereiro 2, 2023 admin 0 comentários

Procurando por Função FILTRO Excel? Então você está no lugar certo!

Como você costuma filtrar dados no Excel? Na maioria das vezes, utilizamos o Filtro do Excel e em cenários mais complexos, a ferramenta de Filtro Avançado. De fato, essas duas ferramentas, são bastante práticas e poderosas, no entanto, esses métodos têm uma desvantagem significativa – eles não são atualizados automaticamente quando seus dados mudam, o que significa que você teria que limpar e filtrar novamente.

Por outro lado, quando se usa a função FILTRO Excel 365, você pode criar filtros dinâmicos. Isso significa que ao contrário das tradicionais ferramentas de filtro, a função FILTRO recalcula automaticamente a cada alteração na planilha, portanto, você precisará configurar seu filtro apenas uma vez!

Neste tutorial, você aprenderá como utilizar a função FILTRO através de exemplos prático. Se interessou? Então confira os detalhes a seguir.

Navegue pelo tópico de seu interesse:

Função FILTRO Excel 365
- Função FILTRO não aparece no Excel: O que fazer?
Uso Básico da função FILTRO no Excel
Função FILTRO: Dicas importantes
Como filtrar no Excel: exemplos práticos da função FILTRO
O que fazer quando função FILTRO do Excel não está funcionando

Função FILTRO Excel 365

A função FILTRO no Excel é usada para filtrar um intervalo de dados com base nos critérios que você especificar.

A função pertence à categoria de funções de matriz dinâmica. Isto significa que o resultado é uma matriz de valores que se espalha automaticamente em um intervalo de células, começando pela célula onde você insere uma fórmula.

A sintaxe da função FILTRO é a seguinte:

FILTRO(matriz; incluir; [se_vazia)

Onde:

Matriz: (Argumento obrigatório). Representa o intervalo ou matriz de valores que você deseja filtrar.
Incluir: (Argumento obrigatório). Representa os critérios fornecidos como uma matriz booleana (valores VERDADEIRO e FALSO).
Sua altura (quando os dados estão em colunas) ou largura (quando os dados estão em linhas) deve ser igual à do argumento da matriz.
Se_vazia: (Argumento opcional). Representa o valor a ser retornado quando nenhuma entrada atender aos critérios.

Função FILTRO não aparece no Excel: O que fazer?

A função FILTRO só está disponível no Excel para Microsoft 365 e Excel 2021. No Excel 2019, Excel 2016 e versões anteriores, ela não é suportada. Portanto, se você está se perguntando porque a função FILTRO não está aparecendo no seu Excel, então é provável que você esteja utilizando uma versão mais antiga do Microsoft Excel e, neste caso, recomendamos que atualize seu Office caso queira utilizar essa função em suas planilhas.

Uso Básico da função FILTRO no Excel

Para começar, vamos conferir alguns exemplos simples apenas para compreender melhor como funciona uma fórmula do Excel para filtrar dados.

Na imagem abaixo, suponha que você queira extrair um filtro com os alunos que obtiveram uma determinada nota (especificada na célula E1). Para isso, fornecemos a expressão B2:B12=E1 ao argumento incluir, que irá produzir uma matriz booleana necessária, com VERDEIRO correspondendo ao valor inserido em E1.

Deste modo, a fórmula ficará da seguinte maneira:

=FILTRO(A2:B12;B2:B12=E1;”Sem resultados”)

Você também poderá utilizar a seguinte fórmula: =FILTRO(A2:B12;B2:B12=”A”;”Sem resultados”)

No entanto, na prática, é mais conveniente inserir os critérios em uma célula separada, por exemplo, E1, e usar uma referência de célula em vez de codificar o valor diretamente na fórmula.

Se nenhum registro corresponder aos critérios especificados, a fórmula retornará o valor que você colocou no argumento se_vazia, “Sem resultados” neste exemplo:

Se preferir não retornar nada neste caso, forneça uma string vazia (“”) para o último argumento:

=FILTRO(A2:B12;B2:B12=E1;””)

Se os seus dados estiverem organizados horizontalmente da esquerda para a direita, como mostrado na imagem abaixo, a função FILTRO também funcionará bem. Neste caso, apenas certifique-se de definir intervalos apropriados para a matriz e incluir argumentos, para que a matriz de origem e a matriz booleana tenham a mesma largura:

=FILTRO(B1:L2;B2:L2=B5;”Sem resultados”)

Função FILTRO: Dicas importantes

Para filtrar efetivamente no Excel com fórmulas, é importante que você se atente aos seguintes pontos:

A função FILTRO espalha automaticamente os resultados vertical ou horizontalmente na planilha, dependendo de como seus dados originais estão organizados. Portanto, certifique-se de sempre ter células vazias suficientes para baixo e para a direita, caso contrário, você receberá um erro #DESPEJAR!.
Os resultados da função FILTRO do Excel são dinâmicos, o que significa que são atualizados automaticamente quando os valores no conjunto de dados original mudam. No entanto, o intervalo fornecido para o argumento de matriz não é atualizado quando novas entradas são adicionadas aos dados de origem. Se você deseja que a matriz seja redimensionada automaticamente, converta-a em uma tabela do Excel e crie fórmulas com referências estruturadas ou crie um intervalo nomeado dinâmico.

Como filtrar no Excel: exemplos práticos da função FILTRO

Agora que você já sabe como funciona uma fórmula básica de filtro do Excel, vamos conferir alguns exemplos práticos para que você consiga resolver tarefas mais complexas.

Filtro com vários critérios (Lógica E)

Para filtrar dados com vários critérios, basta fornecer duas ou mais expressões lógicas para o argumento incluir:

FILTRO(matriz;( intervalo1 = critérios1 ) * ( intervalo2 = critérios2 ); “Nenhum resultado”)

A operação de multiplicação processa as matrizes com a lógica E, garantindo que sejam retornados apenas os registros que atendem a todos os critérios. Tecnicamente, funciona assim:

O resultado de cada expressão lógica é uma matriz de valores booleanos, onde VERDADEIRO equivale a 1 e FALSO a 0. Em seguida, multiplicam-se os elementos de todas as matrizes nas mesmas posições. Como a multiplicação por zero sempre dá zero, apenas os itens para os quais todos os critérios são VERDADEIRO entram na matriz resultante e, consequentemente, apenas esses itens são extraídos no filtro.

Exemplo 1: Filtrando duas (ou mais) colunas

Para melhor compreensão, observe o exemplo abaixo onde estamos filtrando os dados por duas colunas (Nome do Produto e Nome do Vendedor).

Para isso, estabelecemos os seguintes critérios: digite o nome do produto em F1 ( critérios1 ) e o nome do vendedor em F2 ( critérios2 ).

Considerando que nossos dados de origem estão em A2:C10 ( matriz ), os produtos estão em A2:A10 ( intervalo1 ) e as vendedores estão em B2:B10 ( intervalo2 ), a fórmula ficará da seguinte maneira:

=FILTRO(A2:C10;(A2:A10=F1)*(B2:B10=F2);”Sem resultados”)

Como resultado, obtemos todas as vendas de um determinado vendedor para um determinado produto:

Exemplo 2: Filtrando entre duas datas

Antes de mais nada, vale ressaltar que não é possível fazer uma fórmula genérica para filtrar por data no Excel. Em diferentes situações, você precisará criar critérios de maneira diferente, dependendo se deseja filtrar por uma data específica, por mês ou por ano. O objetivo deste exemplo é demonstrar a abordagem geral.

Neste segundo exemplo, adicionamos mais uma coluna contendo as datas em que as vendas foram realizadas. Deste modo, podemos usar a função FILTRO para extrair as vendas que ocorreram em um período específico.

Observe que neste caso, ambos os critérios se aplicam ao mesmo intervalo:

=FILTRO(A2:D10;(D2:D10>=G1)*(D2:D10<=G2);”Sem resultados”)

Onde G1 e G2 são as datas para filtrar entre elas.

Filtro com vários critérios (Lógica OU)

Para extrair dados com base em múltiplas condições OU, você também deve usar as expressões lógicas conforme mostrado nos exemplos anteriores, mas em vez de multiplicar, você as soma. Isso significa que quando as matrizes booleanas retornadas pelas expressões são somadas, a matriz resultante terá 0 (zero) para entradas que não atendem a nenhum critério (ou seja, todos os critérios são FALSO) e essas entradas serão filtradas. As entradas para as quais pelo menos um critério for VERDADEIRO serão extraídas.

Abaixo está a fórmula genérica para filtrar colunas com a lógica OU:

FILTRO(matriz;( intervalo1 = critérios1 ) + ( intervalo2 = critérios2 ); “Nenhum resultado”)

Como exemplo, vamos extrair uma lista de vendedores que venderam este ou aquele produto. Neste caso, a fórmula ficará da seguinte maneira:

=FILTRO(A2:C10;(A2:A10=F1)+(A2:A10=F2);”Sem resultados”)

Como resultado, saberemos quais vendedores venderam Laranjas ou Pêssegos.

Filtro com vários critérios (Lógica E e OU)

Se você precisar aplicar os dois tipos de critérios, basta juntar os critérios E com asterisco (*) e os critérios OU com o sinal de mais (+).

Por exemplo, se você precisa retornar a lista de vendedores que venderam Maçã ou Pêssego a partir da data de de 07/01/2023, então use a fórmula conforme demonstrado abaixo:

=FILTRO(A2:D10;(A2:A10=F2)+(A2:A10=F3)*(D2:D10>=G2);”Sem resultados”)

Como filtrar duplicatas no Excel

Se seu objetivo é filtrar duplicatas, ou seja, extrair entradas que ocorrem mais de uma vez, então basta usar a função FILTRO junto com a função CONT.SES.

A ideia é obter as contagens de ocorrências para todos os registros e extrair aquelas maiores que 1. A fórmula genérica ficará da seguinte maneira:

=FILTRO(matriz;CONT.SES(coluna1; coluna1;coluna2;coluna2)>1;”Sem resultados”)

Por exemplo, para filtrar linhas duplicadas dos dados em A2:C20 com base nos valores de todas as 3 colunas, esta é a fórmula a ser usada:

Dica extra: Para filtrar duplicatas com base nos valores de determinada coluna, inclua apenas as colunas específicas na função CONT.SES.

Filtrar espaços em branco no Excel

Se você precisa excluir espaços em branco em seu filtro, então você também poderá fazer isso utilizando a função FILTRO Excel. Uma fórmula para filtrar células em branco é, na verdade, uma variação da função FILTRO com vários critérios E.

Nesse caso, verificamos se todas (ou determinadas) colunas contêm algum dado e excluímos as linhas em que pelo menos uma célula está vazia. Para identificar células não vazias, basta usar o operador “diferente de” (<>) junto com uma string vazia (“”). Veja o exemplo abaixo:

=FILTRO(matriz; (coluna1<>””)*(coluna2<>””);”Sem resultados”)

Função FILTRO para texto específico

A função FILTRO também possibilita extrair células que contenham determinado texto. Neste caso é preciso combiná-la com as função ÉNÚM e PROCURAR.

A função PROCURAR pesquisa uma sequência de texto especificada em um determinado intervalo e retorna um número (a posição do primeiro caractere) ou o erro #VALOR! (texto não encontrado).
A função ÉNÚM converte todos os números em VERDADEIRO e os erros em FALSO e passa a matriz booleana resultante para o argumento include da função FILTRO.

Para este exemplo, queremos obter a lista de vendedores cujos nomes contenham “Ma”. Neste caso, a fórmula ficará da seguinte maneira:

Como filtrar e calcular ao mesmo tempo

Uma característica interessante da função FILTRO do Excel é que ela pode não apenas extrair valores com condições, mas também calcular os dados filtrados. Neste caso, basta combinar a função FILTRO com funções como SOMA, MÉDIA, CONT.SE, MÁXIMO ou MÍNIMO.

Por exemplo, para somar o total de vendas de cada vendedor, use a seguinte fórmula:

=SOMA(FILTRO(A2:C10;B2:B10=E2;0))

Outros exemplos são:

Média de Vendas de cada vendedor

=MÉDIA(FILTRO(A2:C10;B2:B10=E2;0))

Valor Máximo de Vendas por vendedor

=MÁXIMO(FILTRO(A2:C10;B2:B10=E2;0))

Valor Mínimo de Vendas por vendedor

=MÍNIMO(FILTRO(A2:C10;B2:B10=E2;0))

Importante: Note que, em todas as fórmulas foi usado zero para o argumento se_vazia, portanto, as fórmulas retornariam 0 se nenhum valor que atendesse aos critérios fosse encontrado. Fornecer qualquer texto como “Sem resultados” resultaria em um erro #VALOR.

Função FILTRO para diferenciar maiúsculas de minúsculas

Por padrão, a função FILTRO Excel não diferencia maiúsculas de minúsculas, o que significa que não faz distinção entre caracteres maiúsculos e minúsculos. Se você precisa distinguir entre maiúsculas e minúsculas, então basta aninhar a função EXATO no argumento incluir da função FILTRO. Isso forçará a função a fazer um teste lógico com distinção entre maiúsculas e minúsculas:

FILTRO(matriz; EXATO( intervalo; critérios); “Sem resultados”)

No exemplo abaixo, suponha que queremos filtrar apenas os registros de vendas do produto “laranja”. Neste caso, queremos diferenciar os registros “Laranja” de “laranja”. Veja como ficará a fórmula:

Filtrando colunas específicas (adjacentes)

Se você deseja que algumas colunas vizinhas apareçam no resultado da sua função FILTRO, então basta incluir apenas essas colunas na matriz, pois é esse argumento que determina quais colunas retornar. Veja o exemplo abaixo abaixo onde queremos retornar apenas as duas primeiras colunas da tabela (Produto e Vendedor). Portanto, fornecemos apenas o intervalo para o argumento matriz.

Filtrando colunas específicas (Não adjacentes)

Por outro lado, para usar a função FILTRO para filtrar colunas não adjacentes, use a seguinte dica:

1. Insira a função FILTRO com a(s) condição(ões) desejada(s) usando toda a tabela para matriz.

2. Em seguida, aninhe a fórmula acima dentro de outra função FILTRO. Neste caso, use uma constante de valores de matriz VERDADEIRO e FALSO ou 1’s e 0’s para o argumento incluir, onde VERDADEIRO (1) representa as colunas a serem exibidas e FALSO (0) representa as colunas a serem excluídas.

Por exemplo, para retornar apena as colunas Produto (1ª coluna) e Valor ( 3ª coluna), basta usar {1,0,1} ou {VERADEIRO\FALSO\VERDADEIRO} para o argumento incluir da função FILTRO externa. Veja o exemplo abaixo

O que fazer quando função FILTRO do Excel não está funcionando

Existem alguns casos em que sua fórmula FILTRO do Excel poderá resultar em um erro. Veja quais são:

Erro #CALC!
Ocorre se o argumento opcional se_vazia for omitido e nenhum resultado que atenda aos critérios for encontrado. A razão é que atualmente o Excel não suporta matrizes vazias. Para evitar tais erros, certifique-se de sempre definir o valor se_vazia em suas fórmulas.

Erro #VALOR!
Ocorre quando a matriz e o argumento de inclusão têm dimensões incompatíveis.

#N/D, #VALOR!, etc.
Diferentes erros podem ocorrer se algum valor no argumento incluir for um erro ou não puder ser convertido em um valor booleano.

Erro #NOME
Ocorre ao tentar usar a função FILTRO em uma versão mais antiga do Excel. Lembre-se de que é uma nova função, disponível apenas no Office 365 e no Excel 2021.

No novo Excel, um erro #NOME ocorre se você acidentalmente digitar incorretamente o nome da função.

Erro #DESPEJAR!
Na maioria das vezes, esse erro ocorre se uma ou mais células no intervalo de despejo não estiverem completamente em branco. Para corrigi-lo, basta limpar ou excluir células não vazias.

Erro #REF!
Ocorre quando uma fórmula FILTRO é usada entre pastas de trabalho diferentes e a pasta de trabalho de origem é fechada.

Baixe aqui a planilha com os exemplos apresentados neste tutorial

Confira também as seguintes dicas de Excel:

E então, o que achou do uso da função FILTRO no Excel? Deixe seu comentário abaixo e continue conferindo mais dicas de Excel em nosso site!