Blog

Postado em em 12 de novembro de 2019

Tabela Dinâmica no Excel – 10 Dicas que você precisa conhecer

Nessa publicação vou te mostrar 10 dicas absurdas sobre a ferramenta de tabela dinâmica no Excel! Tenho certeza que você vai aprender muita coisa maneira!

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 é a Tabela Dinâmica no Excel?

Tabela dinâmica como o próprio nome sugere é uma tabela em que podemos fazer algumas alterações de forma dinâmica, ou seja, podemos alterá-la de forma fácil e rápida para analisar somente os dados que queremos naquele determinado momento. É importante ressaltar que para criar uma tabela dinâmica será necessária uma tabela base, pois todas as informações que poderemos modificar serão baseados nessa tabela base.

 

Quando utilizar a Tabela Dinâmica no Excel?

A tabela dinâmica sempre será utilizada quando o usuário precisa de uma análise mais detalhada e mais focado em certos dados, pois o usuário tem a possibilidade de alterar de forma fácil os dados que serão mostrados para se adequar a análise desejada no momento. Essa tabela possui diversas ferramentas que nem todos que tem conhecimento de Excel sabem e nessa aula vamos aprender 10 dicas sobre a tabela dinâmica.

 

Quais são as 10 dicas sobre essa ferramenta?

Nesta aula vamos ver algumas dicas que poucas pessoas sabem em relação a tabela dinâmica, lembrando que essas dicas são para as pessoas que já tem conhecimento em tabela dinâmica.

No arquivo que foi baixado temos 3 tabelas dinâmicas que serão utilizadas para mostrar as essas dicas, como já sabemos que cada tabela dinâmica precisa de uma tabela base para ser criada, elas também se encontram dentro do arquivo.

 

Dica 1 – Subtotais

É possível verificar que na primeira tabela dinâmica temos os dados de uma empresa e logo ao lado de cada um dos setores temos o total das vendas de todos os 3 anos analisados.

 

Tabela Dinâmica no excel 1 - Tabela inicial

Tabela Dinâmica no excel 1 – Tabela inicial

 

O que pouca gente sabe é que temos a possibilidade de alterar como esse subtotal aparece dentro da tabela dinâmica. Para fazer essa alteração vamos clicar em qualquer célula dentro da tabela dinâmica para habilitar duas novas guias, vamos acessar a guia Design e em seguida a opção Subtotais.

 

Opções para alterar os Subtotais

Opções para alterar os Subtotais

 

Aqui temos 3 opções, uma para não mostrar esses subtotais e as outras duas para alterar entre o subtotal no início ou no final do agrupamento. O padrão do Excel como já vimos é mostrando esse subtotal no início do grupo.

 

Dica 2 – Total Geral

Da mesma forma que podemos fazer alterações com os subtotais, podemos fazer também com os totais gerais, pois como vimos na imagem da tabela temos um total geral para os valores em colunas e um total geral para os valores nas linhas. Então na coluna F temos o total por ano e por setor enquanto na linha 23 temos o total por trimestre (os números na coluna 2 indicam os 4 trimestres do ano).

 

Tabela Dinâmica no excel 1 - Tabela inicial

Tabela Dinâmica no excel 1 – Tabela inicial

 

Então para fazer as alterações nesses totais gerais vamos até a guia Design e acessar a opção Totais Gerais, que fica logo ao lado da opção Subtotais.

 

Opções para alterar os Totais Gerais

Opções para alterar os Totais Gerais

 

Aqui temos 4 opções, uma é para desabilitar tudo, uma para habilitar somente para colunas, uma para habilitar somente para linhas e uma que habilita para linhas e colunas que é o padrão do Excel. Essas modificações são úteis para que o usuário deixe apenas a visualização que seja mais importante para diminuir a quantidade de dados e facilitar a análise deles.

 

Dica 3 – Campo Calculado

Vamos utilizar agora a Tabela 2 para as próximas dicas. Note que nesta tabela temos as datas, o faturamento e o custo de uma determinada empresa, no entanto está faltando uma informação importante que é o lucro da empresa.

 

Tabela 2 - Segunda tabela dinâmica no Excel

Tabela 2 – Segunda tabela dinâmica no Excel

 

Como sabemos para inserir uma coluna a mais dentro da tabela dinâmica temos que ter essa coluna dentro da nossa tabela base, ou seja, teríamos que ir até a tabela base, fazer os cálculos e atualizar a tabela dinâmica, pois essa coluna não existe na tabela.

 

Tabela base para a segunda tabela dinâmica no excel

Tabela base para a segunda tabela dinâmica no excel

 

No entanto podemos fazer esse procedimento sem que tenhamos que mexer na nossa base de dados, e faremos isso utilizando a ferramenta de Campo Calculado. Essa ferramenta se encontra na guia Analisar dentro da opção Campos, Itens e Conjuntos.

 

Acessando a opção de Campo Calculado

Acessando a opção de Campo Calculado

 

Ao selecionar essa opção teremos uma nova janela dentro do ambiente Excel.

 

Janela do campo calculado já com os dados

Janela do campo calculado já com os dados

 

Dentro dessa janela podemos altera o nome do nosso campo, quer será Lucro e logo abaixo podemos colocar a fórmula, lembrando que só poderemos utilizar as operações básicas com essa ferramenta. Para a fórmula podemos utilizar a caixa de Campos logo abaixo e inseri-lo para compor a fórmula, neste caso será o Faturamento menos o Custo.

Ao pressionar OK o Excel irá criar a coluna Soma de Lucro, com o somatório do lucro para todas as datas que temos na planilha.

 

Resultado do campo calculado na tabela dinâmica no excel

Resultado do campo calculado na tabela dinâmica no excel

 

Dica 4 – Agrupamento

Utilizando ainda a mesma tabela caso o usuário queira agrupar as datas, ou separá-las com o objetivo de ter uma melhor visualização. Isso será feito porque normalmente não temos uma análise de dados diária, a melhor forma de visualizar pode ser em meses, trimestres e até anos.

Então para agrupar esses dados de datas que temos para melhorar a análise desses dados vamos clicar em uma das datas e em seguida ir até a guia Dados. Nesta guia vamos selecionar a opção Agrupar.

 

Opção para agrupar dados

Opção para agrupar dados

 

Ao clicar nessa opção o Excel irá abrir uma nova janela que nos permite escolher o tipo de agrupamento que será feito.

 

Opções para o agrupamento de dados

Opções para o agrupamento de dados

 

Neste caso vamos selecionar as opções de Meses, Trimestres e Anos. Ao pressionar OK o Excel irá dividir todas as datas que temos dentro das opções que selecionamos.

 

Resultado do agrupamento de dados na tabela dinâmica no excel

Resultado do agrupamento de dados na tabela dinâmica no excel

 

OBS: Veja que ao agruparmos as datas o Excel retirou os subtotais da tabela, caso o usuário queira colocá-los de volta basta fazer o procedimento que já vimos.

 

Desta forma temos uma análise mais detalhada por períodos dentro da tabela, o que facilita ao invés de olharmos data por data. Esse tipo de análise pode variar de acordo com a necessidade do usuário e dos dados que estão sendo analisados, nada impede que o usuário faça uma análise diária caso esteja trabalhando com dados que variem diariamente como é o caso das ações.

 

Dica 5 – Análise detalhada de dados da tabela dinâmica

Na última tabela que fizemos o agrupamento de dados foi possível notar que temos os dados no último grupo, que é o de meses, contém o somatório de todas as datas daquele mês, mas olhando somente para a tabela não é possível verificar de quais dias ou os outros detalhes que compõem esse valor analisado.

Para isso temos uma forma muito útil que poucas pessoas conhecem para fazer essa análise. Vamos supor que queremos verificar as informações de janeiro de 2018, que se encontra na célula B4, para isso basta dar um duplo clique nessa célula que o Excel irá gerar uma nova aba com os dados detalhados.

 

Resultado após o duplo clique em um dado da tabela dinâmica no excel

Resultado após o duplo clique em um dado da tabela dinâmica no excel

 

É possível observar que o Excel cria uma nova aba com todos os dados que compõem os dados da célula selecionada. O Excel puxa o mesmo formato que temos na tabela base que foi utilizada para criar a tabela dinâmica. Então essa é uma opção muito útil para mostrar de onde estão vindo aqueles valores da célula selecionada de forma isolada e sem alterar a tabela dinâmica.

 

Dica 6 – Classificação

Assim como em uma tabela comum dentro da tabela dinâmica podemos fazer a classificação dos dados, para isso basta clicar com o botão direito no dado que será classificado e ir até a opção Classificar.

 

Opções de classificação de dados

Opções de classificação de dados

 

IMPORTANTE: O usuário poderá fazer a classificação para qualquer tipo de dado, como nessa tabela temos valores e datas o Excel permite a classificação dos valores e também das datas, basta selecionar o dado que deseja classificar.

 

Dica 7 – Lista Personalizada

Para as próximas dicas vamos utilizar a Tabela 3.

 

Tabela 3 - Terceira tabela dinâmica no excel

Tabela 3 – Terceira tabela dinâmica no excel

 

É possível observar que temos uma tabela de regiões e do faturamento de cada cliente para cada uma dessas regiões. No entanto as vezes o usuário pode querer modificar essa ordem em que as regiões aparecem, e talvez a classificação em ordem alfabética não é a mais adequada para o caso. Então vamos supor que o usuário queira uma ordem personalizada da seguinte maneira:

 

Sudeste

Sul

Centro-Oeste

Nordeste

Norte

 

Então para que seja possível inserir essa ordem que o usuário deseja teremos que construir uma lista personalizada para atribuir a nossa tabela dinâmica. Para a criação dessa lista personalizada vamos até a guia Arquivo e em seguida em Opções. Feito isso será aberta uma janela de opções do Excel. Nesta janela vamos até a opção Avançado e dentro desse ambiente vamos descer a barra de rolagem até encontrar a opção Editar Listas Personalizadas.

 

Acessando as opções do Excel para criar uma lista personalizada

Acessando as opções do Excel para criar uma lista personalizada

 

Ao selecionar essa opção o Excel irá abrir a janela de listas personalizadas já na opção NOVA LISTA, então basta colocar a sequência desejada pelo usuário nesse espaço e clicar em Adicionar para colocarmos essa lista juntamente com as outras.

OBS: Veja que o Excel já possui algumas listas personalizadas como padrão, então se o usuário escrever janeiro em uma célula é arrastar o Excel irá completar automaticamente até a célula desejada com os meses do ano. O mesmo vale para qualquer lista personalizada.

 

Inserindo os dados da nova lista personalizada

Inserindo os dados da nova lista personalizada

 

Feito isso podemos clicar em OK e em OK novamente na janela de opções do Excel. Para inserir essa lista dentro da tabela dinâmica vamos clicar com o botão direito em qualquer uma das regiões e acessar a opção Mais Opções de Classificação dentro de Classificar.

 

Mais opções de classificação dos dados

Mais opções de classificação dos dados

 

Ao selecionar essa opção o Excel irá abrir uma nova janela.

 

Janela para mais opções de classificação

Janela para mais opções de classificação

 

Aqui vamos marcar a opção Crescente para que nossos dados fiquem na ordem que colocamos, caso queira a ordem inversa basta selecionar a opção Decrescente, mas como não inserimos nossa lista ainda vamos clicar em Mais Opções.

 

Selecionando a lista personalizada que foi criada

Selecionando a lista personalizada que foi criada

 

Nesta janela vamos desmarcar a caixa que por padrão está marcada e logo abaixo vamos selecionar a nossa lista que acabamos de criar, em seguida basta pressionar OK e OK novamente para a janela anterior. Feito isso teremos nossa tabela dinâmica com a ordem da lista personalizada.

 

Resultado da classificação com lista personalizada

Resultado da classificação com lista personalizada

 

Dica 8 – Segmentação de Dados

Dentro da tabela dinâmica podemos inserir um “filtro inteligente” que é muito utilizado na criação de Dashboards dentro do Excel, isso serve para facilitar na hora de filtrar e possibilitar que o usuário faça isso de maneira fácil.

Para acessar essa opção vamos clicar em qualquer célula da tabela dinâmica (para habilitar as duas guias), vamos até a guia Analisar e em seguida em Inserir Segmentação de Dados.

 

Opção de segmentação de dados

Opção de segmentação de dados

 

Ao selecionar essa opção o Excel irá abrir uma janela com os dados que temos na tabela, desta forma podemos escolher qual deles irá ser utilizado como filtro.

 

Selecionando a opção de Clientes para a segmentação de dados

Selecionando a opção de Clientes para a segmentação de dados

 

Neste caso vamos escolher a segmentação de dados para os Clientes, desta forma o Excel vai gerar uma janela com cada um dos clientes em forma de botões.

 

Resultado da segmentação de dados

Resultado da segmentação de dados

 

Ao clicar em qualquer um deles o filtro já será aplicado, ou seja, se clicarmos no Cliente B a tabela dinâmica só irá mostrar esse cliente, pois a tabela foi filtrada apenas para ele.

 

Selecionando apenas um cliente dentro da segmentação

Selecionando apenas um cliente dentro da segmentação

 

Desta forma o usuário poderá filtrar de forma mais fácil seus dados e facilitar sua análise apenas com os dados que deseja. Caso queira filtra mais de um cliente por vez podemos segurar a tecla CTRL e selecionar outro cliente, ou marcar a opção logo no cabeçalho da segmentação de dados que é a Seleção Múltipla.

 

Seleção múltipla de cliente na segmentação de dados

Seleção múltipla de cliente na segmentação de dados

 

Caso o usuário queira fazer algumas modificações nessa segmentação de dados ao selecionar essa caixa poderá ir até a guia Opções e fazer as alterações conforme a necessidade. Poderá alterar a quantidade de colunas em que os dados serão mostrados, o tamanho da caixa e até mesmo os estilos dessa segmentação.

 

Opções para formatar a segmentação de dados

Opções para formatar a segmentação de dados

 

Dica 9 – Expandir e Recolher Campos

Caso o usuário queira fazer uma análise mais detalhada desses dados inserindo as datas basta selecionar algum dado da tabela e nos campos da tabela dinâmica vamos colocar a Data de Operação na parte de linhas logo abaixo da Região do Brasil.

 

Inserindo o campo de Data de Operação em linhas

Inserindo o campo de Data de Operação em linhas

 

Feito isso é possível observar que o Excel expande essa data em trimestres e anos.

 

Resultado da inserção de datas no campo de linhas

Resultado da inserção de datas no campo de linhas

 

Dentro da tabela dinâmica temos a opção de expandir as informações clicando no botão de +, desta forma a tabela irá mostrar mais detalhes daquele item.

 

Expandindo as datas para maior detalhamento de dados

Expandindo as datas para maior detalhamento de dados

 

Então é possível “navegar” dentro da tabela dinâmica para ter mais detalhes desses dados. Caso o usuário não queira ficar abrindo item por item para verificar todas as informações temos duas opções que são: Expandir Campo e Recolher Campo.

 

Opção de expandir e recolher campos

Opção de expandir e recolher campos

 

Essas duas opções facilitam, pois, o usuário pode expandir todos os anos (tendo uma célula de ano selecionada) ou todos os trimestres (selecionando a opção de expandir novamente). Isso facilita principalmente se a tabela é muito grande e possui muitos campos para expandir, então evita o trabalho de ter que fazer de um a um.

Caso o usuário queira inserir um filtro de Ano por exemplo basta puxar o campo de ano que está dentro da parte de linhas e remover os campos de Data da Operação e Trimestres.

 

Inserindo o filtro de Anos

Inserindo o filtro de Anos

 

Feito isso a tabela será modificada e ficará com uma opção logo acima para que o usuário possa filtrar de acordo com o ano desejado.

 

Resultado da inserção de filtros

Resultado da inserção de filtros

 

Dica 10 – Formatação de dados

Caso seja necessário formatar os dados dessa tabela para que possamos inserir o formato contábil para indicar que os dados se tratam de dinheiro a opção que muitos iriam fazer seria selecionar os dados, ir até a guia Página Inicial e em seguida formatar como desejado. No entanto essa formatação será limitada as células que foram selecionadas, então se a quantidade de dados aumentar ou diminuir somente aquelas células serão formatadas.

Para formatar de forma correta vamos pressionar com o botão direito em uma das células da tabela dinâmica e selecionar a opção Formato de Número.

 

Opção de formato de número da tabela dinâmica no excel

Opção de formato de número da tabela dinâmica no excel

 

Na janela que será aberta vamos selecionar a opção Contábil e pressionar OK.

 

Escolhendo o formato Contábil

Escolhendo o formato Contábil

 

Feito isso todos os dados serão formatados de forma correta e agora temos a tabela com os dados em formato contábil que mostra que os dados são referentes a dinheiro.

 

Resultado da formação de número com a opção Contábil

Resultado da formação de número com a opção Contábil

 

Dica 11 – Formatação Condicional

Assim como em tabelas comuns dentro da tabela dinâmica é possível inserir uma formatação condicional nos dados para formatar da forma que o usuário precisar para determinado problema. Para isso vamos selecionar qualquer uma das células da tabela, em seguida iremos acessar a guia Página Inicial. Aqui vamos fazer o procedimento normal da formatação, ir até a opção Formatação Condicional, Regras de Primeiros/Últimos e então a opção Acima da Média.

 

Opção de formatação condicional para valores acima da média da tabela dinâmica no excel

Opção de formatação condicional para valores acima da média da tabela dinâmica no excel

 

Feito isso podemos modificar como será o preenchimento da formatação, neste caso vamos escolher a cor verde. Ao pressionar OK teremos o seguinte resultado.

 

Resultado para a célula selecionada

Resultado para a célula selecionada

 

Temos apenas a célula selecionada formatada, mas temos uma opção logo ao lado que nos permite expandir essa formatação para toda a tabela.

 

Opção para expandir a formatação condicional na tabela dinâmica no excel

Opção para expandir a formatação condicional na tabela dinâmica no excel

 

Ao selecionar a última opção a formatação condicional será expandida para todos os dados da tabela.

 

Resultado da formatação condicional para toda a tabela dinâmica no excel

Resultado da formatação condicional para toda a tabela dinâmica no excel

 

Finalizamos então todas as dicas sobre tabela dinâmica que boa parte delas poucas pessoas sabem, mesmo as que tem Excel avançado, portanto são dicas muito úteis que podem ser utilizadas em diversas situações.

 

Para acessar outra publicação sobre tabela dinâmica, clique no link: https://www.hashtagtreinamentos.com/5-passos-para-aprender-como-fazer-tabela-dinamica-no-excel/

Você conhece as modalidades de curso que a Hashtag Treinamentos oferece? Possuímos uma ampla variedade de cursos, tanto online quanto presenciais! Clique para saber mais!


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


Quer ter acesso a um Minicurso de Finanças no Excel de forma 100% gratuita? Preencha seu e-mail abaixo!