A tabela dinâmica existe em todas as versões do consolidado software e editor de planilhas Microsoft Excel.
O ponto chave para compreender a utilização da tabela dinâmica é saber que seu uso não se dá em simplesmente exibir dados, mas sim como um recurso de teor importante quando o assunto é resumir informações.
Tendo isso em mente, é importante compreender as quatro áreas que compõem as tabelas dinâmicas para poder dominar seu uso. São elas: filtros, linhas, colunas e valores.
Pensando nisso, vamos mostrar nesse post:
- O que é uma tabela dinâmica?
- Qual a estrutura básica de uma tabela dinâmica?
- Para que serve uma tabela dinâmica e quando usar?
- Quais as vantagens e desvantagens de usar uma tabela dinâmica?
- Vale a pena fazer uma tabela dinâmica? Por que usar?
- Como fazer uma tabela dinâmica no Excel? O passo a passo com exemplos!
- Como usar uma tabela dinâmica? 5 dicas!
Se você quer descomplicar e aprender na prática sobre tabela dinâmica, é só continuar lendo aqui!
O que é uma tabela dinâmica?
Uma tabela dinâmica é um recurso do Excel capaz de resumir informações processando dados a fim de fornecer uma análise de uma determinada planilha. Por meio da tabela dinâmica, é possível fazer análise de dados de tabelas, além de fornecer ferramentas que otimizam o processo para gerar resumos de forma intuitiva, de maneira rápida e eficiente.
Qual a estrutura básica de uma tabela dinâmica?
Antes de mais nada, é importante que aprendamos sobre os elementos de uma tabela dinâmica e como utilizá-los. A imagem abaixo é uma tabela dinâmica, ou seja, um resumo para análise de dados que foi gerado a partir de uma massa de dados prévia.
Destacamos cinco conceitos fundamentais para gerá-la: campos, filtros, linhas e colunas.
Campo
Campos, são os “cabeçalhos” do relatório que servem como fonte de dados para a criação da tabela.
Filtro de relatório
Como o nome sugere, a área de filtros serve para adicionar o comportamento de filtro por elemento escolhido a tabela dinâmica. O filtro de relatório passou a se chamar apenas Filtros nas versões mais recentes do Excel.
Rótulos de linha
A área de linhas, fará com que sua tabela dinâmica exiba os campos nas posições das linhas.
Rótulos de coluna
A área de colunas, fará com que sua tabela dinâmica exiba os campos nas posições das colunas.
Valores
A área de valores é utilizada para fazer operações matemáticas, ou seja: o único campo que faz contas da tabela dinâmica é o campo valores. Ao arrastar um campo numérico para a área de valores, ele exibirá uma soma total desses valores na tabela dinâmica.
Para que serve uma tabela dinâmica e quando usar?
A tabela dinâmica facilita a organização dos dados de uma planilha promovendo a transformação de dados em informações objetivas disponíveis em relatórios com conteúdos dinâmicos sem a necessidade de criar fórmulas complexas.
Quais as vantagens e desvantagens de usar uma tabela dinâmica?
A principal vantagem da tabela dinâmica está relacionada com a produtividade. Por meio da ferramenta tabela dinâmica, é possível gerar resumos gerenciais sem a necessidade de escrever algumas fórmulas como a soma das vendas mensais no crédito, débito ou dinheiro, por exemplo.
Vale a pena fazer uma tabela dinâmica? Por que usar?
O uso da tabela dinâmica está relacionado com a necessidade de obter-se resumos gerenciais de forma prática, sem a necessidade de construir fórmulas para consolidarmos esses dados.
Como fazer uma tabela dinâmica no Excel? O passo a passo com exemplos!
Antes de construirmos nossa tabela dinâmica, primeiro vamos preparar nossa base de dados. Você pode construir sua própria base de dados ou fazer o download da mesma base que utilizaremos aqui:
Nossa base de dados é uma planilha com os campos:
- Identificador: uma sequência numérica de 1 a 1000;
- Nome Vendedor: em nosso caso de uso possuímos cinco pessoas vendedoras;
- Forma Pagamento: Crédito, Débito e Dinheiro;
- Venda: valor da venda em reais;
- Mês: o mês em que ocorreu a venda;
Algumas observações: na coluna vendas foi utilizado o formato personalizado.
Em seguida foi escolhido a categoria Contábil com duas casas decimais e nenhum símbolo.
Para a coluna Mês, foi formatado Personalizado de Data e aplicado o tipo [$-pt-BR]mmmm;@ que exibe as datas apenas como meses em formato português Brasil.
Definimos nossa base de dados. Para facilitar, vamos selecionar qualquer célula de nossa planilha e transformar em tabela clicando na opção Formatar como Tabela. Isso facilitará nosso processo fazendo com que não fiquemos preocupados com intervalos de linhas e colunas.
No menu suspenso, escolhemos o tipo de estilo de tabela que desejamos.
O Excel reconhecerá o intervalo pertencente à tabela. Importante marcar a opção Minha tabela tem cabeçalhos. Os cabeçalhos da tabela serão os campos da tabela dinâmica.
Na guia Design da Tabela, alteramos o Nome da Tabela para VENDAS. Isso facilitará não termos que nos preocupar com intervalo de linhas e colunas da tabela.
Agora que temos nossa base de dados definida e formatada como tabela, construiremos nossa tabela dinâmica.
Na guia Inserir, escolhemos a opção Tabela Dinâmica > Da Tabela/Intervalo.
Na caixa de diálogo, selecionamos a tabela/intervalo utilizando apenas o rótulo VENDAS, que é o nome que atribuímos ao formatar como tabela. Deixaremos marcado a opção Nova Planilha fazendo com que o Excel crie uma nova aba de planilha para a tabela.
Se você estiver vendo algo como a imagem a seguir, significa que ela foi criada com sucesso. Então, parabéns! Agora podemos configurar seu comportamento de acordo com nossa necessidade. Para isso, vamos clicar e arrastar os campos do relatório para a área de Filtros, Colunas, Linhas e Valores.
Exemplo 1
Por exemplo, se quisermos que nossa tabela dinâmica mostre as vendas realizadas no crédito, débito e dinheiro para cada vendedor, por exemplo: podemos arrastar os campos Forma Pagamento para a área Colunas, Nome Vendedor para a área Linhas e Venda para a área Valores. O padrão da área de Valores é realizar uma soma sem a necessidade de utilizar fórmulas de soma.
Como resultado dessa configuração, teremos a tabela a seguir.
Ainda será necessário formatar os valores numéricos com o Formato Contábil com duas casas decimais e sem símbolo, assim como fizemos na etapa de formatação da nossa tabela.
Agora pensemos no cenário em que uma pessoa vendedora nova fez uma venda de 350 no crédito na data de 23/12/2021. Sendo assim, voltemos a nossa tabela da BASE DE DADOS, selecionamos a última célula com valor e pressionamos a tecla TAB.
O Excel criará uma nova linha onde adicionaremos os seguintes valores. Escrevemos a data no formato dd/mm/aaaa e o excel formatará exibindo o mês por escrito.
Voltando a nossa tabela dinâmica, clicamos em qualquer célula com o botão direito do mouse e em seguida escolhemos a opção Atualizar.
Agora nosso resumo mostrará as vendas da nova pessoa vendedora sem a necessidade de refazer todo o processo.
A critério de análise, é possível selecionar quais colunas ou linhas desejamos exibir.
Ao aplicar o Filtro de Rótulo Dinheiro, como resultado temos apenas as vendas realizadas com essa Forma de Pagamento.:
Exemplo 2
Agora, modificaremos a configuração da nossa tabela dinâmica. Clique e arraste o campo Forma de Pagamento para os Filtros.
Como resultado, temos:
Diferente dos Filtros de Rótulos que ocultam linhas ou colunas, o que fizemos foi adicionar o comportamento de filtrar as vendas pela Forma de Pagamento. Podemos filtrar por tudo ou por um critério mais específico, como as vendas feitas no crédito, por exemplo. Aplique e alterne entre os filtros e observe o comportamento da Tabela.
Feitas as observações, modificaremos a exibição da nossa tabela mais uma vez. Adicionamos os Meses a área das Colunas.
Como resultado, temos:
Mais uma vez, alterne entre os filtros e observe o comportamento da Tabela Dinâmica.
Como usar uma tabela dinâmica? 5 dicas!
Se você chegou até aqui, já deve estar familiarizado com o modo de alterar a exibição apenas clicando e arrastando os campos para as áreas desejadas. Vamos agora para o próximo nível com mais essas dicas.
Primeiramente vamos alterar nossa tabela dinâmica para que seja exibida nas Linhas o campo Nome Vendedor e nos Valores o campo Venda. Como resultado temos:
Dica 1: Duplicar uma tabela dinâmica
A qualquer momento, você pode selecionar sua tabela dinâmica, copiar (CTRL + C) e colar (CTRL + V).
Agora possuímos duas tabelas idênticas. Esse procedimento garante que tenhamos duas tabelas dinâmicas a partir da mesma base de dados. Quando falarmos tabela 1 estamos nos referindo a tabela da esquerda e tabela 2 a tabela da direita, combinado?
Dica 2: Resumir por Contagem
Para nossa tabela dois, a tabela da direita, arrastaremos o campo Venda mais duas vezes de modo que sua tabela fique com 3 colunas iguais Soma de Venda.
Clique com o lado direito do mouse ou um clique duplo sobre o primeiro campo Soma de Venda e em seguida em Configurações do Campo de Valor.
Na guia resumir Valores por primeiro escolha a opção Contagem e, somente após escolher, renomeie o rótulo no campo Nome Personalizado para Qt. Vendas; Por último clique em Formato do Número com zero casas decimais.
Nesse momento sua tabela deverá estar semelhante a essa.:
Agora você tem uma coluna que realiza a contagem da quantidade de vendas sem a necessidade de fazer uma fórmula =CONT.VALORES();
Dica 3: Resumir por Média
Agora vamos repetir o procedimento anterior para a coluna Soma de Venda2. Porém, na guia Resumir Valores por, escolhemos o cálculo por Média e personalizamos o nome para Tícket Médio. Em Formato do Número usaremos Contábil, com duas casas decimais e Símbolo Nenhum.
Seu resultado deve ser:
Observe que não precisamos utilizar a fórmula =MÉDIA().
Dica 3: Mostrar Valores como Porcentagem
Na coluna Soma de Venda3, faremos o mesmo procedimento clicando com o botão direito do mouse em seguida clicando em Configurações do Campo de Valor. Na guia Mostrar Valores como, escolhemos a opção % do Total Geral. Em seguida clicamos no campo base Forma Pagamento e por último personalizamos o nome para Porcentagem.
Sua tabela deverá ficar assim:
Dica 4: Segmentação de dados
Nesse momento, você deverá ter duas tabelas assim:
A segmentação de dados funcionará também como um filtro de dados com a capacidade de conectar mais de uma tabela que está vinculada a mesma fonte de dados. A segmentação é comumente utilizada na criação de Dashboards.
Para ilustrar melhor o funcionamento da segmentação de dados, vamos criar um gráfico para cada tabela.
Selecionamos a primeira tabela clicando em qualquer célula e na guia Inserir colocaremos um gráfico de colunas.
Ajustamos o tamanho e posicionaremos embaixo da sua respectiva tabela.
Para a segunda tabela, vamos inserir um gráfico de pizza.
Após dimensionar e posicionar embaixo da sua respectiva tabela, você deverá está com algo semelhante ao que temos a seguir:
Para criar a segmentação, selecionamos a primeira tabela dinâmica clicando em qualquer célula. A partir de então podemos realizar a segmentação por dois caminhos (dependendo da versão do excel).
- A primeira opção é por meio da guia Inserir > Segmentação de Dados;
- A Segunda opção é por meio da guia Análise de Tabela Dinâmica > Inserir Segmentação de Dados;
Após clicar em inserir Segmentação de Dados, marcamos o campo Meses.
Então, obteremos esse resultado:
Para ver a segmentação de dados funcionando, você pode clicar no mês e ver a planilha e o gráfico exibindo apenas as vendas do mês escolhido.
Observe porém que apenas a nossa tabela da esquerda, ou seja, nossa tabela 1 é quem está mudando a exibição assim como o gráfico derivado dela. Isso acontece porque a segmentação de dados foi criada a partir da tabela 1. Porém, o que queremos é que a tabela da direita, ou seja a tabela 2, também mude o comportamento quando clicarmos nos meses. Para resolver isso, vamos a próxima dica.
Dica 5: Conectando relatórios de tabelas dinâmicas
Após realizar a segmentação de dados, agora conectaremos nossa segmentação ao relatório da segunda tabela, a tabela da direita. Clicando na segmentação por Meses com o botão direito do mouse , vamos escolher a opção Conexões de Relatório.
No pop up selecionaremos os relatórios de Tabela Dinâmica que queremos que essa segmentação seja aplicada.
Agora, quando aplicamos um filtro de segmentação, ele aplicará o filtro nos dois relatórios e, consequentemente, nos seus gráficos.
Clique nos meses e observe o comportamento.
Você poderá limpar os filtros aplicados clicando no canto superior direito da segmentação ou pressionando as teclas Alt + C.
Dependendo da sua base de dados, você poderá criar mais de uma segmentação de dados e conectar a seus relatórios e usá-las combinadas.
A tabela dinâmica é uma poderosa ferramenta capaz de prover resumos regenciais. Vale salientar também que toda alteração de dados deve ser feita apenas na fonte de dados original. Sua construção ocorre de forma intuitiva com pouco esforço. Você pode obter o arquivo final clicando aqui.
Gostou do conteúdo? Você pode conferir também nosso post sobre Como fazer um dashboard no Excel? Passo a passo e dicas!
