Como Fazer Tabela Dinâmica no Excel

Aqui você vai aprender criar uma tabela dinâmica! Além disso, mostrarei vários exemplos que demostram a aplicação delas no nosso dia a dia.

A tabela dinâmica é ideal para quem está trabalhando com grandes conjuntos de dados no Excel, porque ela se aplica como uma maneira rápida de fazer um resumo interativo de vários registros. Não bastando isso, ela pode classificar e filtrar automaticamente diferentes subconjuntos de dados, contar totais, calcular a média, e também criar tabulações cruzadas.

Outro benefício de usar tabelas dinâmicas é que você pode configurar e alterar a estrutura de sua tabela de resumo simplesmente arrastando e soltando as colunas da tabela de origem.

O que é uma tabela dinâmica no Excel?

Uma Tabela Dinâmica do Excel é uma ferramenta para explorar e resumir grandes quantidades de dados, analisar totais relacionados e apresentar relatórios de resumo projetados para:

Apresentar grandes quantidades de dados de uma forma amigável.
Resumir os dados por categorias e subcategorias.
Filtrar, agrupar, classificar e formatar condicionalmente diferentes subconjuntos de dados para que você possa se concentrar nas informações mais relevantes.
Mover linhas para colunas ou colunas para linhas (o que é chamado de “dinamização”) para visualizar diferentes resumos dos dados de origem.
Subtotalizar e agregar dados numéricos na planilha.
Expandir ou reduzir os níveis de dados e fazer uma busca detalhada para ver os detalhes por trás do total.
Apresentar de forma mais atraente seus dados.

Temos aqui nosso primeiro exemplo, onde temos centenas de entradas em uma planilha com os valores de vendas de revendedores locais:

Uma maneira possível de somar uma lista grande de números, é usando fórmulas como SOMASE e SOMASES. No entanto, se quiser comparar vários fatos sobre cada um dos tópicos, usar uma tabela dinâmica é uma maneira muito mais eficiente. E olha só, com apenas alguns cliques você consegue obter uma tabela de resumo resiliente e facilmente personalizável que totaliza os números para qualquer campo desejado.

Exemplo Tabela Dinâmica 1

Exemplo Tabela Dinâmica 2

Exemplo Tabela Dinâmica 3

As imagens acima demonstram apenas alguns dos muitos layouts possíveis de se fazer. E as etapas abaixo mostram como você pode criar rapidamente sua própria tabela dinâmica em todas as versões do Excel.

Como fazer uma tabela dinâmica no Excel: início rápido

Muitas pessoas pensam que criar uma tabela dinâmica é trabalhoso e demorado. Mas isso não é verdade! A Microsoft vem refinando a tecnologia há muitos anos e, nas versões modernas do Excel, os relatórios resumidos são fáceis de usar e incrivelmente rápidos. Na verdade, você pode construir sua própria tabela de resumo em apenas alguns minutos. Veja como:

1. Organize seus dados de origem

Antes de criar um relatório de resumo, organize seus dados em linhas e colunas e, a seguir, converta o intervalo de dados em uma tabela do Excel. Para fazer isso, selecione todos os dados, vá para a guia Inserir e clique em Tabela.

Usar uma tabela do Excel para os dados de origem oferece um benefício muito bom: seu intervalo de dados torna-se “dinâmico”. Nesse contexto, uma faixa dinâmica significa que sua tabela vai se expandir e encolher automaticamente conforme você adiciona ou remove entradas, portanto, não precisa se preocupar se sua tabela dinâmica está perdendo os dados mais recentes.

Dicas úteis:

Adicione cabeçalhos significativos às colunas;
Certifique-se de que sua tabela de origem não contenha linhas ou colunas em branco e nenhum subtotal.
Para facilitar a manutenção de sua tabela, nomeie a tabela de origem alternando para a guia Design e digitando o nome na caixa Nome da tabela no canto superior direito de sua planilha.

2. Crie uma tabela dinâmica

Selecione qualquer célula na tabela de dados de origem e vá para a guia Inserir > Tabela Dinâmica.

Em seguida, abrirá uma janela chamada Criar Tabela Dinâmica. Certifique-se de que a tabela ou intervalo correto de células esteja destacado no campo Tabela / Intervalo. Em seguida, escolha o local de destino para sua tabela dinâmica do Excel:

Se selecionar Nova Planilha, o excel colocará a tabela dinâmica em uma nova planilha, começando na célula A1.
Se selecionar Planilha Existente, o excel colocará sua tabela no local especificado em uma planilha existente. Na caixa Local, clique no botão Recolher caixa de diálogo para escolher a primeira célula onde deseja posicionar sua tabela.

Clique em OK para criar uma tabela dinâmica. O formato será semelhante a imagem abaixo:

Dicas:

Na maioria dos casos, faz sentido colocar uma tabela dinâmica em uma planilha separada. Eu recomendo para quem está iniciando agora, fica bem mais facil de trabalhar.
Se você quiser criar uma tabela dinâmica a partir de dados que se encontram em outra planilha ou pasta de trabalho, inclua a pasta de trabalho e os nomes da planilha usando a seguinte sintaxe [nome_do_arquivo] nome_da_planilha! Intervalo, por exemplo:

[Lista de exemplos.xlsx] Planilha1! $A$1: $E$20

Criar uma tabela dinâmica e um gráfico dinâmico ao mesmo tempo, pode ser bem util. Para fazer isso, no Excel 2019, 2016 e 2013, vá para a guia Inserir > grupo Gráficos, clique na seta abaixo do botão Gráfico Dinâmico e clique em Gráfico Dinâmico e Tabela Dinâmica. No Excel 2010 e 2007, clique na seta abaixo da Tabela Dinâmica e, em seguida, clique em Gráfico Dinâmico.

3. Como organizar o layout da tabela dinâmica

A área onde se trabalha com os campos do relatório de resumo é chamada de Lista de Campos da Tabela Dinâmica. Ele está localizado na parte direita da planilha e dividido nas seções de cabeçalho e corpo:

A seção de campos contém os nomes dos campos que pode ser adicionado à tabela. Os nomes dos campos correspondem aos nomes das colunas da tabela de origem.
A seção Layout contém as áreas: Filtros, Colunas, Linhas e Valores. Aqui você pode organizar e reorganizar os campos da sua tabela.

As alterações feitas na Lista de campos da tabela dinâmica são refletidas imediatamente na tabela.

Como adicionar um campo à tabela dinâmica

Para adicionar um campo à seção Layout, marque a caixa de seleção ao lado do nome do campo na seção Campo.

Por padrão, o Microsoft Excel adiciona os campos à seção Layout da seguinte maneira:

Os campos não numéricos são adicionados à área Linhas;
Os campos numéricos são adicionados à área Valores;
As hierarquias de data e hora do Online Analytical Processing (OLAP) são adicionadas à área Colunas.

Como remover um campo da tabela dinâmica

Para excluir um determinado campo, você pode: Desmarcar a caixa aninhada no nome do campo na seção Campo do painel Tabela Dinâmica. Para isso, clique com o botão direito do mouse no campo.

Como organizar os campos da tabela dinâmica

Os campos na seção Layout podem ser organizados de três maneiras:

Arraste e solte os campos entre as 4 áreas da seção Layout usando o mouse. Como alternativa, clique e segure o nome do campo na seção Campo e arraste-o para uma área na seção Layout – isso removerá o campo da área atual na seção Layout e o colocará na nova área.
Clique com o botão direito no nome do campo na seção Campo e selecione a área onde deseja adicioná-lo:

Clique no campo na seção Layout para selecioná-lo. Isso também exibirá as opções disponíveis para aquele campo específico.

4. Escolha a função para o campo Valores (opcional)

Por padrão, o Microsoft Excel usa a função Soma para campos de valor numérico que você coloca na área Valores da Lista de Campos. Quando você coloca dados não numéricos (texto, data ou booleano) ou valores em branco na área Valores, a função Contagem é aplicada.

Mas é claro, é possivel escolher uma função de resumo diferente, caso desejar. No Excel 2019, 2016 e 2013, clique com o botão direito do mouse no campo de valor que deseja alterar, clique em Resumir valores por e escolha a função de resumo desejada.

No Excel 2010 e nas versões mais antigas, a opção Resumir Valores por também está disponível na faixa de opções – na guia Opções, no grupo Cálculos.

Abaixo veja um exemplo da tabela dinâmica com a função Média:

Os nomes das funções são, em sua maioria, autoexplicativos:

Soma: calcula a soma dos valores.
Contagem: conta o número de valores não vazios (funciona como a função CONT.VALORES).
Média: calcula a média dos valores.
Máx: encontra o maior valor.
Mín: encontra o menor valor.
Produto: calcula o produto dos valores.

Para obter funções mais específicas, clique em Resumir valores por > Mais opções… Você vai encontrar a lista completa de funções de resumo disponíveis e as descrições detalhadas.

5. Mostrar cálculos diferentes nos campos de valor (opcional)

As tabelas dinâmicas do Excel fornecem mais um recurso que permite apresentar valores de maneiras diferentes, por exemplo, mostrar totais como porcentagem ou classificar valores do menor para o maior e vice-versa. A lista completa de opções de cálculo está disponível aqui.

Este recurso é denominado Mostrar Valores como e pode ser acessado clicando com o botão direito do mouse no campo da tabela do Excel 2019, 2016 e 2013. No Excel 2010 e para as versões mais antigas, também é possível encontrar essa opção na guia Opções, no grupo Cálculos.

Dica:

O recurso Mostrar Valores como pode ser especialmente útil se você adicionar o mesmo campo mais de uma vez e mostrar, por exemplo, vendas totais e vendas como uma porcentagem do total ao mesmo tempo.

Lista de campos da tabela dinâmica

O painel da tabela dinâmica, conhecido como Lista de Campos da Tabela Dinâmica, é a principal ferramenta para organizar a tabela de resumo exatamente como deseja. Para tornar o seu trabalho com os campos mais confortável, personalize o painel de acordo com sua preferência.

Alterando a visualização da Lista de Campos

Se você deseja alterar a forma como as seções são exibidas na Lista de Campos, clique no botão Ferramentas e escolha seu layout preferido.

Você também pode redimensionar o painel horizontalmente arrastando a barra (divisor) que separa o painel da planilha.

Fechando e abrindo o painel da Tabela Dinâmica

Fechar o painel da tabela dinâmica é tão fácil quanto clicar no botão Fechar (X) no canto superior direito do painel. Mas, fazer com que ele apareça novamente não é tão óbvio.

Para exibir a Lista de Campos novamente, clique com o botão direito em qualquer lugar da tabela e selecione Mostrar Lista de Campos no menu de contexto.

Também dá certo se você clicar no botão Lista de Campos na Faixa de Opções, que fica na guia Analisar, no grupo Mostrar.

Uso de tabelas dinâmicas recomendadas

Como você acabou de ver, criar uma tabela dinâmica no Excel é fácil. No entanto, as versões modernas do Excel vão ainda mais longe e tornam possível fazer automaticamente um relatório mais adequado para seus dados de origem. Tudo que você precisa fazer é dar 4 cliques do mouse:

Clique em qualquer célula do seu intervalo de células ou tabela de origem.
Na guia Inserir, clique em Tabelas Dinâmicas Recomendadas. O Microsoft Excel exibirá alguns layouts, com base em seus dados.
Na caixa de diálogo Tabelas Dinâmicas Recomendadas, clique em um layout para ver a visualização.
Se você estiver satisfeito com a visualização, clique no botão OK e adicione uma tabela dinâmica a uma nova planilha.

Como você pode ver acima, o Excel foi capaz de sugerir alguns layouts básicos para meus dados de origem. De modo geral, usar a Tabela Dinâmica Recomendada é uma maneira rápida de começar, especialmente quando se tem muitos dados e não sabe por onde começar.

Como usar a tabela dinâmica no Excel

Agora que te apresentei o básico, navegue até as guias Analisar e Design das Ferramentas de Tabela Dinâmica no Excel 2019, 2016 e 2013 (guias Opções e Design no Excel 2010 e 2007) para explorar os grupos e opções fornecidas ali. Essas guias ficam disponíveis assim que você clica em qualquer lugar da tabela.

É possivel também, acessar opções e recursos que estão disponíveis para um elemento específico clicando com o botão direito nele.

Como projetar e melhorar a tabela dinâmica

Depois de criar uma tabela dinâmica com base nos dados de origem, você pode querer refiná-la ainda mais para fazer uma análise de dados poderosa.

Para melhorar o design da tabela, vá até a guia Design, lá você vai encontrar alguns estilos predefinidos. Para criar seu próprio estilo, clique no botão Mais na galeria de Estilos de Tabela Dinâmica e, em seguida, clique em “Novo Estilo de Tabela Dinâmica …“.

Para personalizar o layout de um determinado campo, clique nesse campo e, em seguida, clique no botão Configurações do campo na guia Analisar no Excel 2019, 2016 e 2013 (guia Opções no Excel 2010 e 2007). Alternativamente, você pode clicar com o botão direito do mouse no campo e escolher Configurações do campo no menu de contexto.

A imagem abaixo demonstra um novo design e layout para a tabela dinâmica.

Como se livrar dos títulos “Rótulos de Linhas” e “Rótulos de Colunas”

Quando você está criando uma tabela dinâmica, o Excel aplica o layout Compacto por padrão. Este layout exibe “Rótulos de Linha” e “Rótulos de Coluna” como cabeçalhos de tabela.

Uma maneira fácil de se livrar desses títulos ridículos é mudar do layout Compacto para Esboço ou Tabular. Para fazer isso, vá para a guia Design da faixa de opções, clique no menu suspenso Layout do Relatório e escolha Mostrar em Formato de Estrutura de Tópicos .

Isso exibirá os nomes reais dos campos, como você vê na tabela à direita, o que faz muito mais sentido.

Como atualizar uma tabela dinâmica no Excel

Embora um relatório de tabela dinâmica esteja conectado aos dados de origem, você pode se surpreender ao saber que o Excel não o atualiza automaticamente. É possivel obter qualquer atualização de dados executando uma operação de atualização manualmente ou atualizando-a automaticamente ao abrir a pasta de trabalho.

Atualizar os dados da tabela dinâmica manualmente no Excel

Clique em qualquer lugar da sua tabela.
Na guia Analisar (guia Opções em versões anteriores), no grupo Dados, clique no botão Atualizar ou pressione ALT + F5.
Como alternativa, você pode clicar com o botão direito na tabela e escolher Atualizar no menu de contexto.

Para atualizar todas as tabelas dinâmicas em sua pasta de trabalho, clique na seta do botão Atualizar e, em seguida, clique em Atualizar Tudo.

Dica:

Se o formato de sua tabela dinâmica for alterado após a atualização, certifique-se de que as opções “Ajustar automaticamente a largura da coluna na atualização” e “Preservar a formatação da célula na atualização” estejam selecionadas. Para verificar isso, clique na guia Analisar (Opções)> grupo Tabela Dinâmica > botão Opções. Na caixa de diálogo Opções de tabela dinâmica, alterne para a guia Layout e formato e você encontrará essas caixas de seleção lá.

Depois de iniciar uma atualização, você pode revisar o status ou cancelá-lo se mudar de ideia. Basta clicar na seta do botão Atualizar e, em seguida, clicar em Atualizar status ou Cancelar atualização.

Atualizar uma tabela dinâmica automaticamente ao abrir a pasta de trabalho

Na guia Analisar / Opções, no grupo Tabela Dinâmica, clique em Opções > Opções.
Na caixa de diálogo Opções de Tabela Dinâmica, vá para a guia Dados e marque a caixa de seleção Atualizar dados ao abrir o arquivo.

Como mover uma tabela dinâmica para um novo local

Se você deseja mover a tabela para uma nova pasta de trabalho, vá para a guia Analisar (guia Opções no Excel 2010 e anterior) e clique no botão Mover Tabela Dinâmica no grupo Ações. Neste caso, selecione um novo destino e clique em OK.

Como excluir uma tabela dinâmica do Excel

Se não precisa mais de um determinado relatório de resumo, pode excluí-lo de várias maneiras.

Se a sua tabela esta em uma planilha separada, simplesmente exclua essa planilha.
Se sua tabela estiver localizada junto com alguns outros dados em uma planilha, selecione a tabela dinâmica inteira usando o mouse e pressione a tecla Delete.
Clique em qualquer lugar da tabela dinâmica que deseja excluir, vá para a guia Analisar (guia Opções no Excel 2010 e anterior) > grupo Ações, clique na pequena seta abaixo do botão Selecionar, escolha Tabela Dinâmica Inteira e pressione Excluir.

Importante:

Se algum gráfico de tabela dinâmica estiver associado à sua tabela, a exclusão da tabela dinâmica o tornará um gráfico padrão que não pode mais ser dinamizado ou atualizado.

E finalmente, chegamos ao fim deste tutorial. Espero ter ajudado e até a proxima!

Conclusão

Neste artigo vimos como trabalhar com Tabelas Dinâmicas no Excel. Recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.

O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.

Portanto, continue lendo mais sobre Excel Avançado aqui.