Fórmula FILTRO sem ter a Versão Mais Nova do Excel
Se você não tem a versão mais nova do Excel já deve ter percebido que existem algumas funções que não estão disponíveis para você, como é o caso da fórmula FILTRO.
Se esse é o seu caso, vou te mostrar nessa publicação como você pode chegar ao mesmo resultado da fórmula FILTRO sem ter essa fórmula!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso Canal do YouTube para mais vídeos!
O que você aprende neste vídeo
Resposta rápida: Sem a função FILTRO, o mesmo resultado sai em cinco etapas com colunas auxiliares: um SE que devolve o nome quando o cargo bate, um índice sequencial montado com SE + MÁXIMO, a lista de posições, e então ÍNDICE com CORRESP para trazer os nomes em sequência, tudo envolvido em SEERRO para não exibir #N/D.
Neste vídeo (20 min):
- 2:30 - Passo 1, um
SEcompara o cargo da linha com o cargo escolhido: se bate, devolve o nome; se não, devolve vazio (“”). - 5:00 - A célula do cargo escolhido precisa de F4: ela é a mesma para todas as linhas e não pode “cair” ao arrastar.
- 6:30 - Passo 2, numerar os que sobraram: a lista tem buracos, então é preciso um índice sequencial só para os preenchidos.
- 8:00 - A lógica: se a célula ao lado estiver vazia, fica vazio; senão, pega o
MÁXIMOdos índices anteriores e soma 1. - 11:00 - O intervalo do MÁXIMO é o truque: trava-se só o início com F4, deixando o fim livre, assim ele cresce a cada linha.
- 13:00 - Passo 3, montar a lista de saída com os números 1, 2, 3… na quantidade de linhas desejada.
- 14:00 - Passo 4,
CORRESPencontra em qual posição da coluna auxiliar está cada número, usando 0 para correspondência exata. - 16:00 -
ÍNDICEpega essa posição e devolve o nome correspondente, é o par que substitui o FILTRO. - 17:00 - Passo 5, envolver tudo em
SEERRO, devolvendo “” quando acabam os resultados, para não exibir #N/D. - 18:00 - No fim, as colunas auxiliares podem ser ocultadas, a saída fica idêntica à da função FILTRO.
Clique aqui para baixar a planilha utilizada nessa publicação!
A fórmula FILTRO (FILTER) do Excel retorna uma lista de dados que atendem a um critério, usando apenas uma função. Ela está disponível no Microsoft 365 e no Excel 2021/2024. Quem tem uma versão mais antiga consegue o mesmo resultado combinando as funções SE, ÍNDICE e SEERRO com colunas auxiliares.
O que é a fórmula FILTRO?
É uma fórmula do Microsoft 365 ou Excel 2021 que permite com que o usuário obtenha informações a partir de um filtro, ou seja, poderá obter até uma lista utilizando uma única fórmula.
Quando utilizar essa função?
Vamos utilizar essa fórmula sempre que quisermos obter informações seja uma ou mais, pois essa fórmula na versão mais recente do Excel permite com que o usuário consiga obter uma lista de informações filtradas com apenas uma única fórmula.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Como usar a fórmula FILTRO sem ter a versão mais recente do Excel?
Nesta aula vamos aprender como o usuário poderá utilizar uma versão alternativa da fórmula FILTRO quando não possui a versão mais recente do Excel que possui de fato a fórmula.
Essa é a tabela inicial que vamos utilizar. É possível observa que na célula F2 temos a fórmula filtro para filtrar os vendedores pelo cargo que se encontra na célula D2. Logo ao lado vamos construir a fórmula com outras fórmulas do Excel e assim vamos obter o mesmo resultado.
Lembrando que como vamos “construir” essa fórmula utilizando outras, vamos utilizar também algumas colunas auxiliares para esse procedimento.
O primeiro passo é começar com a coluna auxiliar utilizando a fórmula SE, para verificar se o cargo das pessoas da lista é igual ao que temos na seleção.
Com essa fórmula vamos verificar cada cargo de cada vendedor se corresponde ao cargo escolhido, em caso positivo vamos retornar o nome daquele vendedor, caso contrário vamos retornar vazio.
Como estamos utilizando a fórmula SE, vamos ter que arrastar essa fórmula até a extensão dos dados que temos na tabela, ou seja, essa coluna auxiliar terá a mesma quantidade de informações da tabela de vendedores.
É possível observar que neste caso já temos todas os vendedores do cargo de analista conforme a fórmula FILTRO, no entanto vamos precisar organizar isso para fique melhor representado e não separado.
O próximo passo será para numerar esses vendedores para que possamos ter a quantidade deles e posteriormente organizá-los.
Com essa fórmula vamos conseguir atribuir um valor de máximo entre os valores sempre que tivermos um nome na coluna H.
Com isso temos cada vendedor com um número atribuído a ele. O próximo passo é criar outra coluna auxiliar com os números de 1 até a quantidade de vendedores total que temos na tabela.
Com essas 3 colunas auxiliares seremos capazes de organizar os vendedores um embaixo do outro de forma que não fique espaço entre eles.
Come essa fórmula vamos descobrir a posição de cada um desses números em relação aos vendedores que temos na coluna I.
Veja que com isso já conseguimos juntar as informações e temos as posições de cada um dos vendedores, agora basta obter o vendedor que se encontra em cada uma dessas posições para termos o nosso filtro completo.
Para inserir o nome de cada vendedor de acordo com as posições que temos na coluna K vamos utilizar a fórmula ÍNDICE. Com isso já teremos a lista completa do filtro, no entanto, como temos #N/D na coluna K que são dos valores que não foram encontrados isso irá gerar o mesmo erro na fórmula de índice.
Por esse motivo vamos acrescentar a fórmula SEERRO para toda vez que essa fórmula retornar um erro nós vamos inserir vazio dentro dessa célula.
Com isso teremos exatamente o mesmo resultado da fórmula FILTRO, lembrando que o usuário terá sempre que arrastar as fórmulas até a quantidade de informações da tabela para que isso funcione corretamente.
Agora antes de comparar as duas fórmulas, podemos ocultar as 4 colunas auxiliares que utilizamos. Para isso basta selecionar as colunas, clicar com o botão direito nelas e ir até a opção Ocultar.
Portanto ao modificar o cargo escolhido o resultado será exatamente o mesmo, a diferença é que estamos utilizando uma forma mais elaborada e é necessário utilizar todas as linhas de funcionários.
Essa é uma forma de criar a fórmula FILTRO sem possuir a versão mais atual do Excel. É um pouco mais difícil, mas como pode ser visto o resultado é o mesmo.
Perguntas frequentes
1. O que faz a fórmula FILTRO no Excel?
A função FILTRO retorna automaticamente todas as linhas de um intervalo que atendem a um critério que você define, tudo com uma única fórmula. Por exemplo, ela pode listar apenas os vendedores de um determinado cargo. O resultado é dinâmico: se os dados mudarem, a lista filtrada se atualiza sozinha.
2. Quais versões do Excel têm a função FILTRO?
A função FILTRO faz parte das funções de matriz dinâmica, disponíveis no Microsoft 365 e no Excel 2021 e 2024. Versões anteriores, como o Excel 2019 e 2016, não possuem essa fórmula, mas é possível reproduzir o mesmo comportamento combinando outras funções do próprio Excel.
3. Como filtrar dados no Excel sem a fórmula FILTRO?
Você monta o filtro com colunas auxiliares e a combinação das funções SE, ÍNDICE e SEERRO. A função SE marca os itens que atendem ao critério, você numera esses itens e usa ÍNDICE para trazê-los em sequência. O SEERRO limpa os resultados vazios, deixando a lista organizada.
4. Qual a diferença entre a função FILTRO e o filtro comum do Excel?
O filtro comum (AutoFiltro) apenas oculta as linhas que não atendem ao critério dentro da própria tabela. Já a função FILTRO gera uma nova lista, em outro local da planilha, com os resultados filtrados. Isso permite usar esses dados em relatórios, gráficos e outras fórmulas sem alterar a tabela original.
Conclusão
Nessa aula eu te mostrei como você pode criar a fórmula FILTRO no Excel sem de fato ter a versão do Microsoft 365, que é a versão mais recente do Excel e que possui essa fórmula!
É um processo um pouco mais trabalhoso, mas você consegue fazer essa combinação de fórmulas para criar o que precisa. São vários passos, mas se você realmente precisa dessa fórmula pode seguir esse passo a passo que vai conseguir criar a sua!
Uma outra opção que você tem é utilizar o VBA para criar essa fórmula de forma personalizada! Caso queira saber como fazer isso com o VBA basta clicar aqui que temos uma publicação com o passo a passo e o código dessa fórmula!
Hashtag Treinamentos
Para acessar outras publicações de VBA, clique aqui!
