Segmentação de Dados com Fórmulas no Excel | Passo a passo

Aprenda como usar Segmentação de Dados com fórmulas no Excel, criando uma estrutura em que a seleção realizada pelo usuário altera automaticamente os resultados apresentados no relatório. Neste passo a passo, você verá como combinar Tabela Dinâmica, Segmentação de Dados e funções de matriz dinâmica para criar um filtro interativo usando fórmulas.

A Segmentação de Dados é um recurso visual e muito prático para filtrar informações em tabelas e tabelas dinâmicas. O problema é que, por padrão, ela não funciona diretamente como um filtro para uma fórmula comum. Ou seja, não basta criar uma segmentação e esperar que uma função como FILTRO reconheça automaticamente aquilo que foi selecionado.

Mas existe uma forma de contornar essa limitação. A estratégia consiste em utilizar uma Tabela Dinâmica como estrutura intermediária para que a Segmentação de Dados determine quais informações estarão disponíveis. Depois, usamos fórmulas para capturar esses resultados e aplicá-los em outra fórmula.

No nosso exemplo, temos uma base de dados organizada no formato de tabela. O objetivo é utilizar essa base como origem, criar as estruturas necessárias para as segmentações e, a partir delas, aplicar uma fórmula que filtre os dados automaticamente.

O resultado é uma solução muito interessante para relatórios e dashboards, porque o usuário poderá clicar nos botões da Segmentação de Dados e visualizar imediatamente os registros correspondentes no relatório criado com fórmulas.

Criar Tabela Dinâmica para Segmentação de Dados

Para criarmos a estrutura que permitirá utilizar a Segmentação de Dados com fórmulas, precisaremos criar algumas tabelas dinâmicas.

Primeiro, clique na guia Inserir -> Tabela Dinâmica e selecione a tabela Despesas, incluindo o cabeçalho.

A Tabela Dinâmica será utilizada como uma espécie de estrutura auxiliar. Ela não será necessariamente o relatório final que o usuário visualizará. Sua função principal será fornecer os elementos que serão controlados pelas Segmentações de Dados.

Esse detalhe é importante para entender a lógica da solução. A Segmentação de Dados precisa estar conectada a uma tabela ou tabela dinâmica. Como queremos utilizar essa seleção posteriormente em uma fórmula, vamos aproveitar os resultados apresentados pela Tabela Dinâmica como uma ponte entre o filtro visual e a fórmula.

Depois de criar a Tabela Dinâmica, precisamos obter somente os valores que realmente estão sendo apresentados por ela. Para isso, podemos utilizar a função APARARINTERVALO ou a referência de intervalo com o operador de ponto.

A partir da tabela dinâmica criada, fazemos o uso de uma função para retornar os dados do intervalo.

Pode usar a função APARARINTERVALO ou, então, selecionar o intervalo e colocar ponto entre os intervalos. Fazemos assim: =B9.:.B1000

Essa etapa é fundamental porque a Tabela Dinâmica pode ocupar uma área maior do que a quantidade de registros efetivamente exibidos. Ao utilizar uma referência que considere somente a parte preenchida do intervalo, conseguimos criar uma lista dinâmica para ser utilizada posteriormente pelas fórmulas.

Em outras palavras, em vez de simplesmente apontar para um intervalo fixo e trabalhar com várias células vazias, podemos criar uma referência que acompanhe os dados disponíveis na Tabela Dinâmica.

Aparar Intervalo no Excel

A função APARARINTERVALO permite realizar a remoção de células em branco no início e no final dos intervalos.

Essa função é especialmente útil neste tipo de estrutura porque precisamos transformar o resultado apresentado pela Tabela Dinâmica em uma lista que possa ser utilizada pelas fórmulas.

No exemplo abaixo, temos o intervalo aplicado dentro de uma Tabela Dinâmica que filtra os dados no intervalo do começo ao final das células da Tabela Dinâmica.

Observe que a ideia não é simplesmente eliminar células vazias por uma questão estética. O objetivo é criar um intervalo dinâmico que represente exatamente os itens disponíveis depois que o filtro for aplicado.

Isso permite que uma fórmula localizada em outra área da planilha utilize esse resultado como uma lista de critérios.

Após isso, temos os dados limpos, como podemos ver na imagem abaixo.

Essa lista será importante na próxima etapa. Quando o usuário selecionar uma opção na Segmentação de Dados, a Tabela Dinâmica será atualizada. Consequentemente, o intervalo utilizado como referência também será atualizado.

É justamente essa atualização que permitirá que a fórmula identifique quais registros devem aparecer no relatório.

Como funciona a ligação entre a Segmentação e a fórmula?

Até aqui, temos três elementos trabalhando juntos: a base de dados, a Tabela Dinâmica e o intervalo auxiliar. O próximo passo é adicionar a Segmentação de Dados.

A Segmentação será conectada à Tabela Dinâmica. Quando o usuário selecionar uma opção, a Tabela Dinâmica será filtrada e apresentará somente os dados correspondentes à seleção.

Como o intervalo auxiliar acompanha o resultado da Tabela Dinâmica, ele também passa a conter somente os valores selecionados.

Esse é o ponto-chave da técnica: a fórmula não precisa acessar diretamente a Segmentação de Dados. Ela utiliza os resultados que foram produzidos pela estrutura controlada pela segmentação.

Essa abordagem é bastante útil porque permite combinar a interface visual das Segmentações de Dados com as funções modernas do Excel.

Segmentação de Dados com Função FILTRO

A Segmentação de Dados é então aplicada a partir da Tabela Dinâmica selecionada e, além disso, podemos aplicar filtros de dados com fórmulas.

Para isso, aplicamos o filtro de dados no relatório conforme vemos nas imagens.

Agora temos a parte mais importante da solução: utilizar a função FILTRO para retornar os registros da tabela de despesas que correspondem às seleções realizadas nas Segmentações de Dados.

A função FILTRO trabalha com uma matriz de dados e uma matriz lógica que determina quais registros serão retornados. No nosso caso, essa matriz lógica será construída utilizando a combinação das funções ÉNÚM e CORRESP.

A função FILTRO é aplicada filtrando os dados usando ÉNÚM e CORRESP.

A função utilizada foi:

=FILTRO(tDespesas;(ÉNÚM(CORRESP(tDespesas[Ano];Cálculos!D9#;0)))
                   *(ÉNÚM(CORRESP(tDespesas[Mês];Cálculos!H9#;0)))
                   *(ÉNÚM(CORRESP(tDespesas[Categoria];Cálculos!L9#;0))))

Explicando a fórmula

Para entender essa fórmula, é importante observar que temos três critérios diferentes sendo avaliados: Ano, Mês e Categoria.

Para cada uma dessas informações existe uma lista auxiliar que é atualizada de acordo com a Segmentação de Dados. Essas listas estão nas referências Cálculos!D9#, Cálculos!H9# e Cálculos!L9#.

O símbolo # utilizado nessas referências indica o intervalo derramado de uma fórmula de matriz dinâmica. Dessa maneira, a referência não fica limitada a uma única célula: ela representa todo o resultado que começa naquela célula.

A função CORRESP realiza uma busca dos dados da tabela e tenta localizar os itens na lista na qual utilizamos o APARARINTERVALO. Assim, somente os dados filtrados são considerados para o resultado.

A função CORRESP retorna a posição do item encontrado na lista, como, por exemplo, 1, 2 ou 3. Quando não encontra o item, ela retorna #N/D, indicando que o valor não foi localizado.

Porém, não queremos trabalhar diretamente com os números retornados pelo CORRESP. Precisamos apenas saber se o item foi encontrado ou não. É nesse ponto que entra a função ÉNÚM.

ÉNÚM é uma função do Excel que identifica se o valor é um número ou não, retornando VERDADEIRO ou FALSO.

Quando o CORRESP encontra o item, retorna um número. Consequentemente, ÉNÚM retorna VERDADEIRO. Quando o CORRESP não encontra o item e retorna #N/D, ÉNÚM retorna FALSO.

Assim, construímos uma matriz lógica que indica quais linhas da tabela correspondem aos filtros selecionados.

O próximo detalhe importante está no uso do operador de multiplicação * entre os critérios.

No Excel, quando trabalhamos com valores lógicos convertidos em números, VERDADEIRO pode ser tratado como 1 e FALSO como 0. Portanto, quando multiplicamos os três critérios, somente as linhas que atenderem simultaneamente aos três filtros terão resultado igual a 1.

Por exemplo, imagine que uma determinada linha corresponda ao ano selecionado, ao mês selecionado e à categoria selecionada. Nesse caso, teremos:

1 × 1 × 1 = 1

Essa linha será retornada pela função FILTRO.

Agora imagine que a linha corresponda ao ano e ao mês, mas não corresponda à categoria:

1 × 1 × 0 = 0

Nesse caso, o registro não será retornado.

Essa é uma técnica muito utilizada para combinar vários critérios em funções de filtragem. Ela permite transformar diferentes condições em uma única matriz lógica que pode ser utilizada pela função FILTRO.

Sendo assim, aplicamos novos filtros para cada nova Segmentação de Dados e, ao selecionar uma opção na segmentação, filtramos os dados automaticamente.

Por que utilizar FILTRO em vez de filtros tradicionais?

A função FILTRO apresenta uma vantagem importante neste cenário: ela consegue retornar automaticamente todos os registros que atendem aos critérios definidos.

Não precisamos criar uma fórmula individual para cada linha do relatório. A função cria um resultado de matriz dinâmica e derrama os registros automaticamente pelas células abaixo e ao lado, conforme a quantidade de dados encontrada.

Isso deixa a estrutura mais simples e reduz a necessidade de copiar fórmulas manualmente.

Além disso, quando os critérios mudam, o resultado é atualizado automaticamente. Portanto, a interação realizada pelo usuário na Segmentação de Dados provoca uma nova avaliação da fórmula e, consequentemente, uma nova lista de resultados.

Relatório de Dados com Função FILTRO e Segmentação de Dados

Por fim, temos um relatório automático de dados que é filtrado com a função FILTRO, podendo ainda receber diversas melhorias.

Esse é o resultado mais interessante da estrutura: o usuário não precisa acessar a fórmula nem modificar critérios manualmente. Basta utilizar as Segmentações de Dados para escolher as informações desejadas.

Nas melhorias, podemos colocar classificação de dados usando a função CLASSIFICAR ou ainda ESCOLHERCOLS para selecionar as colunas no relatório.

A função CLASSIFICAR pode ser utilizada quando queremos controlar a ordem dos registros apresentados. Dessa forma, o resultado pode ser organizado por uma determinada coluna, facilitando a leitura do relatório.

Já a função ESCOLHERCOLS pode ser utilizada quando a tabela original possui muitas colunas, mas o relatório precisa apresentar somente algumas delas.

Essa combinação permite separar a base de dados da apresentação final. A tabela pode conter todas as informações necessárias para o controle interno, enquanto o relatório apresenta apenas os campos relevantes para quem está realizando a análise.

Como utilizar essa técnica em dashboards

Essa estrutura também pode ser utilizada na criação de dashboards no Excel. Em vez de apresentar apenas uma tabela filtrada, podemos utilizar o resultado da função FILTRO como parte de um painel de análise.

Por exemplo, podemos criar Segmentações de Dados para Ano, Mês e Categoria. Ao selecionar uma combinação de filtros, a tabela de resultados é atualizada automaticamente.

A partir dos mesmos critérios, podemos criar indicadores complementares, gráficos ou outras áreas de análise.

Isso cria uma experiência muito mais interativa para o usuário. Em vez de procurar informações manualmente dentro de uma base extensa, ele pode utilizar os filtros visuais para chegar rapidamente ao conjunto de dados desejado.

O mais importante é manter uma separação clara entre a base, as estruturas auxiliares e a área de apresentação. Isso facilita a manutenção da planilha e reduz o risco de o usuário alterar acidentalmente alguma parte da estrutura.

Cuidados ao criar a estrutura

Embora a solução seja relativamente simples depois que entendemos a lógica, alguns cuidados são importantes.

Primeiro, a base precisa estar organizada corretamente. Utilize cabeçalhos claros, evite linhas e colunas vazias no meio dos dados e mantenha cada informação em sua própria coluna.

Também é recomendável utilizar uma Tabela do Excel como origem. Dessa maneira, quando novos registros forem adicionados, a estrutura da tabela poderá acompanhá-los sem que seja necessário alterar manualmente todas as referências.

Outro cuidado é verificar os tipos de dados. Se a coluna Ano estiver armazenada como número em alguns registros e como texto em outros, por exemplo, as comparações poderão apresentar resultados inesperados.

O mesmo cuidado vale para meses, categorias e demais campos utilizados pelas Segmentações de Dados.

Também é importante testar a planilha com diferentes combinações de filtros. Faça testes com uma única seleção, várias seleções e, quando aplicável, sem uma seleção específica.

O que acontece quando adicionamos novos registros?

Uma das grandes vantagens de estruturar a base como uma Tabela é facilitar a expansão dos dados.

Imagine que a base possua inicialmente 500 registros e posteriormente receba mais 100 despesas. Se a estrutura estiver corretamente configurada, a nova informação poderá ser incorporada à Tabela e utilizada nas estruturas relacionadas.

Mesmo assim, é importante testar o comportamento da Tabela Dinâmica e das fórmulas depois da inclusão de novos dados. Dependendo da configuração utilizada, a Tabela Dinâmica pode precisar ser atualizada para refletir imediatamente as novas informações.

Esse é um ponto importante em qualquer projeto de Excel: automatizar não significa deixar de verificar as dependências da solução. Quanto mais elementos estiverem conectados, mais importante será entender o fluxo das informações.

Uma alternativa poderosa para relatórios no Excel

Essa técnica demonstra como podemos combinar recursos diferentes do Excel para criar soluções que parecem mais complexas do que realmente são.

A Segmentação de Dados fornece uma interface visual para o usuário. A Tabela Dinâmica funciona como estrutura intermediária. O intervalo auxiliar transforma o resultado da seleção em uma lista que pode ser utilizada pelas fórmulas. Finalmente, a função FILTRO utiliza essas listas para gerar o relatório.

O grande benefício está justamente nessa combinação. Não precisamos desenvolver uma solução inteira em VBA para criar uma interface de filtragem. Em muitos casos, as funções modernas do Excel conseguem resolver grande parte da necessidade.

Isso não significa que o VBA deixou de ser útil. Pelo contrário. Em projetos maiores, podemos combinar essa técnica com macros para atualizar estruturas, controlar navegação, gerar relatórios ou automatizar tarefas adicionais.

Porém, sempre que uma solução puder ser construída de forma simples utilizando os recursos nativos do Excel, vale a pena considerar essa alternativa antes de adicionar código VBA ao projeto.

Conclusão

A Segmentação de Dados é uma excelente ferramenta para tornar filtros mais visuais e fáceis de utilizar. Embora seu uso tradicional esteja associado a Tabelas e Tabelas Dinâmicas, podemos criar uma estrutura que permita aproveitar suas seleções em fórmulas.

Neste exemplo, utilizamos uma Tabela Dinâmica como intermediária, criamos intervalos auxiliares e usamos as funções APARARINTERVALO, CORRESP, ÉNÚM e FILTRO para construir um relatório dinâmico.

A lógica é simples de resumir: a Segmentação de Dados altera os resultados da Tabela Dinâmica; os intervalos auxiliares capturam essas alterações; e a função FILTRO utiliza essas informações para retornar os registros correspondentes.

Depois de entender essa estrutura, você pode adaptá-la para diferentes necessidades. É possível trabalhar com outros campos, criar mais Segmentações de Dados, alterar as colunas apresentadas no relatório e utilizar funções como CLASSIFICAR e ESCOLHERCOLS para melhorar a apresentação.

O mais importante é perceber que o Excel permite combinar seus recursos de diferentes maneiras. Uma Segmentação de Dados não precisa ficar limitada ao filtro visual de uma tabela. Com uma estrutura adequada, ela pode participar indiretamente de uma solução baseada em fórmulas e tornar relatórios muito mais interativos.

Esse tipo de técnica é especialmente interessante para quem trabalha com dashboards, relatórios gerenciais e planilhas profissionais, pois permite oferecer uma experiência de filtragem simples para o usuário sem exigir alterações manuais nas fórmulas.

Download Planilha de Segmentação de Dados com Fórmulas

Faça o download da planilha de exemplo gratuitamente abaixo e acompanhe o funcionamento da estrutura apresentada neste artigo.

Depois de abrir a planilha, observe principalmente a relação entre a Tabela Dinâmica, as Segmentações de Dados, os intervalos auxiliares e a fórmula FILTRO. Entender essa conexão é mais importante do que simplesmente copiar a fórmula, pois é essa lógica que permitirá adaptar a solução para outras planilhas e projetos.