Como fazer Tabela Dinâmica Excel

Em síntese, a Tabela Dinâmica Excel é um recurso que possibilita a visualização de dados da planilha de forma resumida. Facilitando assim, a análise e tomada de decisões.

Nas versões mais antigas do Excel a tabela dinâmica era vista como difícil de se compreender. E dessa forma, se utilizava por poucos. Além disso, sua forma de criação era difícil, nada intuitiva e nem didática. Mas o mito Tabelas Dinâmicas e Dashboard no Excel caiu por terra, e hoje é muito simples criar esse recurso que traz muitos benefícios na hora de filtrar e resumir os dados de uma planilha. E mais, com o uso de tabelas dinâmicas é possível criar Dashboards incríveis utilizando os recursos de filtros disponíveis.

Em contrapartida, caso prefira ver uma explicação prévia em vídeo pode acessar abaixo:

Em suma, a Tabela Dinâmica Excel criamos a partir de uma planilha ou tabela e deve ter as seguintes características:

• Não deve conter linhas e/ou colunas vazias.
• Deve conter apenas uma linha de título para as colunas.

No nosso exemplo, a planilha que consideramos para a construção da tabela dinâmica é a seguinte:

Veja que a planilha possui uma linha de títulos e não possui linhas ou colunas vazias.

Criando a tabela dinâmica

Para criar a tabela dinâmica você deve seguir os seguintes passos:

1. Selecionar as células a partir das quais a tabela dinâmica será criada ou clicar em uma célula qualquer da planilha ou tabela que deve ser considerada. Você também pode clicar sobre qualquer célula da planilha que o Excel considera toda a área que envolve essa célula como região selecionada.

• Na guia Inserir, grupo Tabelas selecione a opção  Tabela Dinâmica. Observe que temos uma outra ferramenta, que é Tabelas Dinâmicas Recomendadas. Essa opção permite a criação rápida de uma tabela dinâmica, escolhendo uma entre as recomendadas pelo Excel para a região selecionada.

• A seguinte janela será mostrada:

O primeiro campo mostra o intervalo selecionado. Se você quiser modificar esse intervalo, basta deletar a informação do intervalo selecionado e selecionar uma outra região.

Em seguida, você deve escolher se deseja criar a tabela dinâmica em uma nova planilha ou na planilha existente. Dessa forma, deixe selecionada a opção Nova Planilha e clique em OK.

A partir daí vamos então construir a Tabela Dinâmica Excel em uma nova planilha da pasta de trabalho.

Construindo a Tabela Dinâmica

É adicionada uma nova planilha na pasta de trabalho para a criação da Tabelas Dinâmicas e Dashboard no Excel.

Além das guias padrão, duas guias são habilitadas para que possamos trabalhar com a tabela dinâmica: Análise de Tabela Dinâmica e Design. Mais em seguida veremos algumas ferramentas disponíveis nessas guias.

Do lado direito da janela temos o painel referente aos Campos da Tabela Dinâmica.

Nesse painel visualizamos as seguintes áreas:

• Campos (colunas) da planilha ou região selecionada e que podem ser escolhidos para fazer parte do relatório de tabela dinâmica.
• Filtros: campos que irão compor os filtros principais do relatório.
• Colunas: campos que serão rótulos de colunas do relatório.
• Linhas: campos que serão os rótulos de linhas do relatório.
• Valores: campos cujos dados serão somados, contados ou resumidos.

Para inserir um campo em qualquer área do relatório, basta então, clicar sobre ele a arrastá-lo até a área desejada.

Tabela Dinâmica Filtrada por Fornecedor

Vamos criar uma tabela dinâmica com filtro em fornecedor e que mostre a quantidade vendida de cada produto.

Você deve arrastar os campos para que fiquem da seguinte forma no painel:

Note que, enquanto os campos são colocados nas áreas específicas do painel, a tabela dinâmica vai sendo formada na planilha. Por fim, você deve ter o seguinte resultado:

Na célula B1 você tem a opção para filtro por fornecedor, ou seja, os dados do relatório podem ser mostrados para todos os fornecedores ou aqueles escolhidos no filtro. Para filtrar, basta clicar na setinha e você terá um menu onde poderá escolher um ou mais fornecedores para filtro.

Você também pode filtrar os produtos clicando, da mesma forma, na setinha ao lado de Rótulos de Linha.

Também é possível classificar os rótulos de linha por meio de opções desse menu.

Para facilitar o nosso trabalho mais à frente, vamos dar um nome para essa tabela dinâmica. Nesse sentido, na guia Análise de Tabela Dinâmica, clique na ferramenta Tabela Dinâmica. E então digite QUANTIDADES no campo Nome da Tabela Dinâmica.

Tabela Dinâmica Resumindo Valores de Compras

Vamos criar uma nova tabela dinâmica contendo os produtos por fornecedor como rótulos de linhas, as quantidades, preços unitários e o valor total dos produtos.

1. Criar a tabela dinâmica.
2. Arrastar para a área Linhas os campos Fornecedor e, em seguida, Produto.

3 – Arrastar para a área ε Valores o campo Total.

Modificando o visual do Relatório

Vamos modificar o nome dessa tabela dinâmica para TOTAL COMPRAS, seguindo os mesmos procedimentos que fizemos para o relatório criado anteriormente.

Modificando os rótulos de coluna

Para modificar os rótulos de colunas basta clicar sobre a célula e digitar um novo conteúdo. Contudo, o novo rótulo não pode ser o mesmo nome que um campo da tabela.

Clicando na célula A3, vamos digitar: Fornecedores

Clicando na célula B3, vamos digitar: Valor Total

Formatando os dados do relatório

Vamos agora formatar os valores monetários para que sejam mostrados com o símbolo da moeda. Para isso clique duas vezes sobre o rótulo de coluna Valor Total e a seguinte janela será aberta:

Clique no botão Formato do Número e escolha Moeda.

Clique em OK e, outra vez em OK para fechar a primeira janela.

A janela Configurações do Campo de Valor poderia ter sido no entanto, acessada por meio de duas outras opções:

• Clicando na setinha ao lado do campo Valor Total na área Valores.

• Selecionando o campo Valor Total e, em seguida, selecionando a ferramenta Configurações do Campo na guia Análise de Tabela Dinâmica.

A guia Design oferece mais opções de formatação da tabela dinâmica, tais como:

Layout: permite mostrar ou ocultar subtotais e totais gerais, bem como modificar o layout do relatório por meio de várias opções e também inserir linhas em branco após cada item.

Estilos: oferece uma variedade de estilos para a tabela dinâmica.

Atualizando Dados na Tabela Dinâmica

As modificações efetuadas na planilha que gerou a tabela dinâmica não são refletidas automaticamente nas tabelas dinâmicas criadas. Para que sejam atualizadas é necessário clicar na ferramenta Atualizar na guia Análise de Tabela Dinâmica.

Dessa forma, se forem inseridas novas linhas no final da planilha fonte, será necessário atualizar a região que deve ser considerada para a tabela dinâmica. Então nesse caso será necessário clicar na ferramenta Alterar Fonte de Dados na guia Análise de Tabela Dinâmica.

Gráficos Dinâmicos

Em resumo, os gráficos dinâmicos exibem séries de dados, permitindo uma rápida visualização dos dados de uma tabela dinâmica. Assim, os recursos disponíveis para esses tipos de gráficos são exatamente os mesmos que encontramos para gráficos criados a partir de qualquer planilha. A diferença no entanto, é que encontramos nos gráficos dinâmicos caixas de combinação que permitem a aplicação direta de filtros.

Gráfico de Pizza para a tabela dinâmica QUANTIDADES

Selecione a tabela dinâmica QUANTIDADES. Na guia Inserir, selecione Gráfico Dinâmico e, a seguir, Pizza 3D.

O gráfico pode ser modificado como qualquer outro gráfico.

Gráfico de colunas para a tabela dinâmica TOTAL COMPRAS

Em seguida, selecione a tabela dinâmica TOTAL COMPRAS. Na guia Inserir, selecione Gráfico Dinâmico e, a seguir, Coluna Agrupada.

Segmentação de Dados

O recurso Segmentação de Dados propicia a aplicação de filtros rápidos tornando mais fácil a leitura e interpretação de informações, principalmente quando precisamos trabalhar com uma grande quantidade de dados.

Selecione a tabela dinâmica QUANTIDADES e siga os passos a seguir para criar uma segmentação de dados.

1. Clique em qualquer célula da tabela dinâmica.
2. Na guia Análise de Tabela Dinâmica, grupo Filtrar, selecione Inserir Segmentação de Dados.

• Selecione o(s) campo(s) que deseja considerar para que seja(m) o(s) filtro(s). Se você selecionar mais de um campo, será criada uma segmentação de dados para cada um. Clique em Produto e Fornecedor e, a seguir, OK .

Veja então, que foram criadas duas segmentações de dados. Apesar do campo Fornecedor não estar presente nessa tabela dinâmica, a segmentação de dados por fornecedor afeta o relatório.

Desse modo, para ativar o filtro, basta clicar sobre o botão desejado e você terá os dados relativos àquele filtro escolhido. Observe que todas as informações constantes da tabela dinâmica são afetadas pela segmentação de dados, inclusive o gráfico.

Na segmentação de dados temos os seguintes elementos:

• Limpar Filtro: esse botão permite desativar o filtro ativo.

• Seleção Múltipla: esse botão permite selecionar vários itens para o filtro.

Por fim, você pode formatar e configurar a Segmentação de Dados utilizando as ferramentas da guia Segmentação, que se ativa quando a segmentação de dados estiver selecionada.

Linha do Tempo

Em suma, o recurso Linha do Tempo permite filtrar os dados por períodos, de forma interativa, sem que haja a necessidade de que o campo que contém a data esteja presente na tabela dinâmica.

Selecione a tabela dinâmica TOTAL COMPRAS e clique em qualquer uma de suas células. Siga os seguintes passos para criar uma Linha do Tempo.

1. Na guia Análise de Tabela Dinâmica, grupo Filtrar, selecione Inserir Segmentação de Dados.

• Em seguida, selecione o campo Data e, a seguir, OK.

Veja que foi criada uma Linha do Tempo para o campo Data.

Temos os seguintes elementos:

• Limpar Filtro: permite desativar o filtro ativo.

• Todos os períodos: permite selecionar se o filtro será por anos, trimestres, meses ou dias.

Você pode formatar e configurar a Linha do Tempo utilizando as ferramentas da guia Linha do tempo, que é ativada quando a linha do tempo estiver selecionada.

Conectando as Ferramentas de Filtro a todas as Tabelas Dinâmicas da Pasta

Criamos segmentações de dados para a tabela dinâmica QUANTIDADES e uma linha do tempo para a tabela dinâmica TOTAL COMPRAS. As segmentações de dados só afetam a tabela dinâmica QUANTIDADES, assim como a linha do tempo só afeta a tabela dinâmica TOTAL COMPRAS.

No entanto você pode conectar as segmentações de dados e linhas do tempo criadas em tabelas dinâmicas específicas a outras tabelas dinâmicas criadas a partir da mesma fonte, permitindo que esses filtros ajam em todas elas. Isso é muito importante na criação de Dashboards, que são excelentes recursos para visualização de situações criadas a partir de filtros e que permitem uma rápida análise dos dados.

1. Selecione a tabela dinâmica QUANTIDADES.
2. Selecione a segmentação de dados Produto.
3. Na guia Segmentação, clique em Conexões de Relatório.

Veja que a segmentação de dados somente está conectada à tabela QUANTIDADES. Para conectá-la também à tabela TOTAL COMPRAS selecione a opção. A seguir, clique em OK.

Agora você tem a segmentação de dados conectada às duas tabelas dinâmicas criadas. Faça o mesmo procedimento para conectar a segmentação de dados Fornecedor à tabela TOTAL COMPRAS.

A linha do tempo criada na tabela TOTAL COMPRAS também podemos conectar à tabela QUANTIDADES da mesma forma.

Tabela dinâmica com Dashboard no Excel

Dashboard é um painel de controle que contém representações visuais consolidadas para uma determinada situação. Nesse painel colocamos gráficos, indicadores e outros elementos que representam situações de acordo com filtros selecionados.

Abaixo também temos um vídeo explicando o uso de tabela dinâmica com gráfico no Excel:

Os dashboards são excelentes ferramentas para tomadas de decisão, pois consolidam dados de formas diferentes e com muita agilidade.

No Excel os dashboards criamos a partir dos recursos das tabelas dinâmicas.

Antes de criar o dashboard, vamos fazer algumas modificações nas tabelas e gráficos dinâmicos criados.

Na tabela QUANTIDADES vamos tirar a linha de Total Geral. Para isso, na guia Análise de Tabela Dinâmica, ferramenta Tabela Dinâmica, Opções, clique na guia Totais e Filtros. Em seguida, desative Mostrar totais gerais das linhas e Mostrar totais gerais das colunas.

Ainda na tabela QUANTIDADES, selecione o gráfico. E, com o mouse apontado para Soma de Quantidade, clique com o botão direito e escolha Ocultar Todos os Botões de Campo do Gráfico.

Na tabela TOTAL COMPRAS, selecione o gráfico e então, com o mouse apontado para Valor Total, clique com o botão direito e escolha Ocultar Todos os Botões de Campo do Gráfico.

Para criar o dashboard, abra uma nova planilha na pasta de trabalho. E assim, formate para que os elementos fiquem didáticos e de uma forma atraente. Por exemplo:

Criando o Dahsboard

É nessa planilha que vamos então, criar o dashboard. Você pode utilizar um plano de fundo já criado, colocar elementos para receber os indicadores e gráficos, ou seja, pode formatar da maneira que achar melhor.

Vamos colocar os filtros (segmentações de dados e linha do tempo) na faixa cinza à esquerda.

Para isso, copie das tabelas dinâmicas onde estão inseridas e cole nessa região e formate para que fiquem parecidos com a figura:

Agora vamos trazer os gráficos para a área verde. Da mesma forma, você pode formatá-los como desejar.

Em seguida, vamos colocar indicadores para mostrar as quantidades vendidas de cada produto. Essa informação virá da tabela QUANTIDADES.

Vamos inserir caixas de texto para trazer as informações. Na guia Inserir, escolha Caixa de Texto e desenhe abaixo do gráfico de pizza. Na barra de fórmulas digite o sinal de =. E selecione a tabela QUANTIDADES e clique na célula que contém o nome do primeiro produto. No meu caso está na Planilha7, célula A4.

Formate a caixa de texto para não ter preenchimento e nem contorno. Copie para baixo e modifique a fórmula para trazer os dois outros produtos.

E então, vamos trazer as quantidades. Da mesma forma, utilize as caixas de texto. Mas cuidado! Não clique na célula da planilha onde está o conteúdo desejado. Acesse a planilha e digite o endereço da célula, caso contrário você terá um erro.

E assim nós temos um exemplo de como criar um Dashboard no Excel. Por fim, agora que você já conheceu nosso post de “Tabelas Dinâmicas e Dashboard no Excel”. Em conclusão, aproveite para conhecer nossas planilhas profissionais. Abaixo temos uma demonstração de um Dashboard profissional em Excel.

Controle de Notas Fiscais em Excel