Blog

Postado em em 1 de outubro de 2019

Filtro no Excel – Como usar e fazer operações utilizando SUBTOTAL

Nessa publicação vou te mostrar como utilizar o filtro no Excel e a fórmula SUBTOTAL para realizar operações em células filtradas!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para baixar a planilha utilizada nessa aula clique aqui!

O que é Filtro e Subtotal?

Filtro é uma ferramenta muito importante dentro do Excel, pois permite ao usuário filtrar dados para que possa analisar de uma forma mais detalhada dos itens que deseja, então quando temos uma tabela com uma quantidade grande de dados é muito útil utilizar o filtro. Neste caso o filtro serve para que o usuário visualize apenas os dados que deseja analisar evitando assim que fazer alguma análise errada ou até mesmo de obter um dado incorreto da tabela.

A fórmula SUBTOTAL é geralmente utilizada quanto temos o filtro dentro de uma tabela. Isso porque ao utilizar essa fórmula podemos obter SOMA, MÉDIA, MULT, entre outras fórmulas para somar apenas o que foi filtrado. Isso quer dizer que ao utilizar a fórmula SOMA por exemplo em uma tabela e utilizar um filtro a fórmula não será modificada, pois o intervalo é do início até o final da tabela. Já com a fórmula SUBTOTAL essas operações são feitas apenas nos dados que estão visíveis, ou seja, apenas no que foi filtrado pelo usuário, e desta forma é possível obter um dado atualizado sempre que utilizar o filtro dentro da tabela.

 

Quando utilizar o Filtro e Subtotal?

O Filtro será utilizando sempre que o usuário precisar filtrar algum dado na tabela para facilitar sua visualização e melhorar sua análise. O filtro é bastante utilizado para diminuir a “poluição” de outros dados que não serão necessários no momento.

A fórmula SUBTOTAL será utilizada juntamente com o filtro para que o usuário tenha os dados atualizados somente dos dados que foram filtrados, desta forma não tem interferência dos outros dados que não deseja no momento, portanto é uma fórmula importante para acompanhar uma análise automática a medida em que utiliza os filtros.

 

Como utilizar o Filtro no Excel?

Primeiramente vamos analisar os dados que temos na tabela. É possível observar que temos 4 colunas: Data, Plataforma, Empresa e Volume. Desta forma já temos uma noção do que se trata a tabela e assim facilita a análise e filtragem de acordo com o que queremos observar.

 

Tabela inicial

Tabela inicial

 

Nesta tabela em específico temos 251 linhas. Ao começar a descer para analisar os dados mais abaixo é possível perceber que a primeira linha some, e ela que contém o cabeçalho, desta forma fica mais difícil associar do que se trata cada um dos dados. Então vamos até a guia Exibir, selecionar a opção Congelar Painéis e selecionar a opção Congelar Linha Superior.

 

Opção para congelar os cabeçalhos da tabela

Opção para congelar os cabeçalhos da tabela

 

Desta forma será possível congelar a primeira linha que contém os cabeçalhos e ao descer na tabela os cabeçalhos continuarão fixos, assim o usuário poderá associar os dados as colunas e verificar do que se trata cada um deles.

 

Descendo na tabela para verificar que o cabeçalho se mantém

Descendo na tabela para verificar que o cabeçalho se mantém

 

Agora será possível inserir os filtros na tabela. É possível fazer isso com o mouse, selecionando os cabeçalhos, indo até a guia Dados e selecionando a opção Filtro no Excel.

 

Opção filtro para inserir nos cabeçalhos da tabela

Opção filtro para inserir nos cabeçalhos da tabela

 

No entanto é possível fazer todas essas ações utilizando apenas o teclado. O primeiro passo é selecionar a célula A1 que é a primeira célula do cabeçalho, em seguida basta utilizar o atalho CTRL + SHIFT + SETA PARA DIREITA, desta forma todo o cabeçalho será selecionado independentemente da quantidade de colunas. Tendo os cabeçalhos selecionados basta utilizar outro atalho que é CTRL + SHIFT + L para que o filtro seja aplicado.

 

Tabela após aplicar o filtro

Tabela após aplicar o filtro

 

É possível observar que todos os cabeçalhos agora possuem uma seta para baixo, essa seta representa o filtro. É possível acessar o menu de filtros utilizando o mouse, mas vamos utilizar o teclado novamente para essa ação. Basta ir até o cabeçalho desejado e pressionar ALT + SETA PARA BAIXO, feito isso o menu de filtro será aberto com algumas opções para filtrar.

 

Acessando a opção de filtro no cabeçalho

Acessando a opção de filtro no cabeçalho

 

Temos algumas opções de classificação, filtros de texto e também quais são os dados que temos nesta coluna. É possível observar que nas mais de 200 linhas da tabela temos apenas 4 empresas. É possível navegar neste menu com as setas do teclado e para selecionar basta pressionar BARRA DE ESPAÇO na opção desejada.

Na parte de pesquisa é possível escrever o que está sendo procurado. Neste caso se escrevermos 2, só teremos como resultado a Empresa 2, e ao pressionar ENTER vamos ter o resultado do filtro apenas para essa empresa dentro da tabela.

 

Tabela após a utilização do filtro

Tabela após a utilização do filtro

 

Desta forma o Excel nos mostra apenas os dados da tabela que são referentes a Empresa 2, veja que as linhas a esquerda estão em azul e temos duplas divisórias para indicar que temos dados entre essas linhas, mas neste caso estamos mostrando só parte deles.

Caso o usuário deseje desfazer o filtro no Excel, ele deverá ir até a guia Dados e selecionar a opção Limpar ao lado do Filtro.

 

Opção para limpar os filtros que foram selecionados voltando a tabela ao normal

Opção para limpar os filtros que foram selecionados voltando a tabela ao normal

 

Tendo os dados filtrados podemos selecionar os dados de volume da Empresa 2 utilizando o atalho CTRL + SHIFT + SETA PARA BAIXO após selecionar a primeira célula.

 

Seleção de todos os dados na coluna de Volume

Seleção de todos os dados na coluna de Volume

 

Ao efetuar essa seleção temos alguns dados importantes sendo mostrados no canto inferior direito do Excel.

 

Verificando a soma do volume através da parte inferior do Excel

Verificando a soma do volume através da parte inferior do Excel

 

É possível observar que temos a média, contagem e a soma desses valores selecionados. Caso o usuário deseje acrescentar ou remover algum desses dados basta clicar com o botão direito em cima desses dados.

 

Menu para adicionar ou remover informações à parte inferior do Excel

Menu para adicionar ou remover informações à parte inferior do Excel

 

Neste menu é possível alterar algumas configurações para ficar de acordo com o que o usuário necessita.

Vamos agora calcular o volume da plataforma CSP e Empresa 4, ou seja, será necessário fazer um filtro na coluna da plataforma e na coluna de empresa para obter o resultado desejado.

 

Verificando os filtros que serão utilizados para o cálculo de volume

Verificando os filtros que serão utilizados para o cálculo de volume

 

Como foi dito no início da aula se utilizarmos a função SOMA normal do Excel para somar esses dados que foram filtrados o Excel vai somar também as células que estão ocultas, ou seja, o resultado não será apenas dos dados que foram filtrados, mas de tudo que está entre esses dados também. Por esse motivo é que vamos utilizar a fórmula SUBTOTAL.

 

Fórmulas do Excel dentro da fórmula SUBTOTAL para utilizar juntamente com tabelas que tenham filtro

Fórmulas do Excel dentro da fórmula SUBTOTAL para utilizar juntamente com tabelas que tenham filtro

 

Veja que o digitar essa fórmula temos várias fórmulas comuns do Excel dentro dela, no entanto vão funcionar apenas para os dados que estão visíveis e não para os ocultos. Como queremos a soma vamos selecionar o número 9 e em seguida vamos colocar ponto e vírgula para separar os argumentos e por fim vamos selecionar a coluna D como um todo.

 

Fórmula final de SUBTOTAL utilizando a opção de SOMA

Fórmula final de SUBTOTAL utilizando a opção de SOMA

 

Essa seleção da coluna toda é para pegar todos os dados, então sempre que o usuário fizer algumas filtragens a fórmula de SUBTOTAL com SOMA irá retornar apenas a soma dos valores visíveis naquele momento, fazendo com que o usuário sempre tenha os dados atualizados a medida em que filtra os dados. O resultado da fórmula deve bater com o que foi visto no canto inferior direito to Excel.

Para acessar outras publicações do nosso blog, acesse: https://www.hashtagtreinamentos.com/formatar-como-tabela/

Você já viu o que os nossos alunos dizem sobre os nossos cursos de Excel, VBA e Power BI? Clique aqui para ir até lá!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?