Tabela Dinâmica no Excel (Pivot Table): Guia Completo

Em resumo, utilizamos tabela dinâmica no excel para resumir relatórios, desenvolver análise de dados completas, entre muitos outros benefícios.

Sem dúvidas a dinâmica no excel é uma das maneiras mais completas que o Excel oferece de ferramentas. Com ela, podemos automatizar dados, interligar tabelas, desenvolver cálculos personalizados, entre muitas outras ferramentas.

Pensando nisso, a Studio Excel desenvolveu um material completo do básico ao avançado sobre tabelas dinâmicas, no decorrer desse artigo você estará apto para desenvolver lindas planilhas.

O que é Tabela Dinâmica?

Uma Tabela Dinâmica é uma ótima ferramenta que permite calcular, analisar dados, verificar gráficos visuais, resumir informações, realizar comparações de dados, desenvolver gráficos dinâmicos, entre outros.

No entanto, para poder aprender realizar todas essas ações, é necessário conhecer suas finalidades completas, funções e modelos de tabelas dinâmicas possíveis de serem criadas.

Por que usar?

Em resumo, as tabelas dinâmicas são compostas por colunas, linhas e diversas vantagens de cálculos para você inserir em suas planilhas.

Essa tabela te permite realizar Soma, Subtração, formatação, filtros. Tudo de maneira dinâmica.

Após isso, a tabela te oferece um visual completo: Resumo de dados, resumo de informações, dados precisos, cálculos complexos.

Pensando nisso, destacamos as principais vantagens de usar uma dinâmica.

Facilidade

Uma dinâmica simplifica uma grande base de dados em poucas linhas e colunas, conforme necessidade do usuário. Essas colunas recebem as facilidades de serem removidas, adicionadas, ocultas, calculadas e excluídas facilmente.

Através disso, é possível resumir uma tabela completa com mais de 5000˜10000 linhas (valor teórico) em poucas informações. Por exemplo, separado por categorias.

Por tanto, uma das principais vantagens de utilizar uma tabela dinâmica, é sua facilidade de ser utilizada.

Uma tabela dinâmica usa um campo de dados especificado pelo usuário e converte o cabeçalho de cada coluna em uma opção mais fácil de ser gerenciada. Colunas contendo dados podem ser removidas, adicionadas ou movidas facilmente.

Aumento de Desempenho Profissional

Em segundo lugar, quem aqui não gostaria de se destacar no mercado profissional. Com isso, saber usar tabela dinâmica de maneira avançada te coloca a frente de grande maioria dos empregados.

Destacamos os três principais motivos que te colocam a frente no mercado de trabalho.

Análise de Dados Simplificada

Embora, o excel ofereça diversas vantagens, nada melhor que ter uma análise completa de dados de maneira totalmente simples.

Em outras palavras, o Excel vai te demonstrar informações acumuladas em um pequeno resumo, completo.

É possível manipular tudo em poucos cliques.

Criação de Relatórios rapidamente

Em segundo lugar, você não perde tempo para desenvolver uma tabela dinâmica completa, isso por que, você consegue fazer através de pequenos passos.

Além disso, você pode criar tabelas a partir de informações externas do Excel. Em resumo, você pode exportar base de dados completas sem precisar deixar sua planilha muito pesada.

Tomada de Decisões Precisas

Aqui está a principal vantagem em utilizar tabela dinâmica.

Com uma base de dados criada rapidamente, facilitando então uma análise de dados completa e precisa. Sua tomada de decisão ficará ainda mais fácil.

Por outro lado, suas tomadas de decisões ficarão cada vez mais precisas, isso por que o Excel te resume dados sem grau de erro desde que sua base de dados esteja correta.

Quando Usar uma Tabela Dinâmica

Essa pergunta é muito comum quando estamos aprendendo a criar tabelas dinâmicas, pois muitas vezes não sabemos o momento certo de utilizá-la.

Em resumo, a melhor hora de se usar uma tabela dinâmica: É agora!

Com isso, destacamos os principais motivos de saber a hora certa de criar a sua tabela dinâmica.

Base de dados de grande porte

Essa é sem dúvidas a principal hora de criar uma tabela dinâmica, pois quando temos uma base de dados em excel muito grande, perdemos muito tempo para encontrar informações.

Com isso, sem dúvidas é fundamental a criação de uma dinâmica para facilitar a sua análise.

Otimizar informações visuais

Em resumo, partindo na mesma linha das base de dados grande porte, é essencial usar uma tabela dinâmica quando precisamos otimizar informações de maneira visual.

Facilitando assim, a tomada de decisão mais precisa, uma planilha organizada e sem valores impossíveis de serem encontrados.

Como fazer uma Tabela Dinâmica

A partir de agora, você vai aprender a criar uma tabela dinâmica passo a passo completas. Partindo desde a manipulação da base de dados, chegando ao final com gráficos dinâmicos interligados.

Portanto, recomendamos o acompanhamento das atividades aqui demonstrada em seus computadores pessoais. Dessa maneira, o aprendizado ficará ainda mais completo.

Realize o download gratuito da base de dados utilizada aqui.

1: Preparar a Base de Dados

Para dar início ao nosso passo a passo, vamos começar manipulando nossa base de dados, pensando nisso, separamos o que pode fazer, e o que não se deve fazer.

Não Fazer (Tabela não Funciona!)

Ao criar uma tabela dinâmica nunca podemos ter colunas em branco, como é possível perceber abaixo, o espaço em branco prejudica e impede a criação.

Como é possível verificar, deixamos a coluna B e D respectivamente em branco, ao inserir uma dinâmica, teremos um alerta conforme abaixo.

Alerta de Erro

Em resumo, com colunas em branco, ao tentar inserir uma nova tabela dinâmica você vai se deparar com uma mensagem semelhante a abaixo.

O nome do campo da tabela dinâmica não é válido. Para criar um relatório de tabela dinâmica, você deve usar dados organizados como uma lista com colunas rotuladas.

Ajustando a Base de Dados da maneira correta

Após você saber o jeito errado de criar uma tabela dinâmica, vamos aprender a criar da maneira correta.

Com isso, deixando todas as colunas preenchidas com cabeçalho e informações, selecionamos as colunas de maneira total.

Após isso, podemos dar andamento a criação da tabela.

2: Como criar Tabela Dinâmica no Excel

Em resumo, com a seleção completa da Coluna A até a Coluna L, vamos então na Guia Inserir -> Tabela Dinâmica.

Assim, você terá a opção de inserir em uma Nova Planilha ou em uma Planilha Existente. No entanto optamos por escolher uma Nova Planilha.

Com isso, você será levado para uma nova aba.

3: Campos da Tabela Dinâmica

Em primeiro lugar, você deve ter se deparado com uma guia na lateral direita de seu computador.

Embora, ali esteja um resumo completo de nossa tabela, você vai notar que todos os cabeçalhos de nossa base estarão disponíveis para inserir.

Conheça os campos disponíveis.

Em resumo, a primeira opção que nos aparece são os nomes do campo.

Ali estarão todas as colunas de sua base. Por outro lado, logo abaixo você irá perceber quatro campos disponíveis.

Filtros

Responsável para habilitar opções de filtros dentro da tabela. Nesse campo você pode mover qualquer cabeçalho da coluna (Nome do Campo) que desejar filtrar.

Colunas

Responsável para demonstrar as informações de sua tabela em Colunas. Nele você pode otimizar informações na horizontal.

Linhas

Nesse campo será o mais utilizado pelo usuário, você consegue resumir todas as informações de uma base de dados através das linhas disponíveis.

Valores

Por último, o local onde vão os valores de sua planilha, esse campo só intende valores numéricos, responsável para resumir todos os dados em um campo.

4: Preenchendo uma Tabela Dinâmica utilizando os campos

Após, conhecer os campos disponíveis para montar a tabela, vamos então a prática.

Com isso, vamos arrastar as Colunas Categoria para o campo de Linhas, e o Valor para o campo de Valores.

Após isso, nossa tabela dinâmica irá se movimentar e ser manipulada de acordo com a condição que estabelecemos.

Portanto, temos nossa primeira tabela dinâmica em excel criada. Como previsto, o Excel compilou todas as informações das categorias em um local só

Como Configurar sua Tabela Dinâmica

Após sua primeira tabela criada, vamos demonstrar como você pode configurar e deixar a sua estética mais avançada.

Em outras palavras, aqui estará o seu dedo mágico para demonstrar seu bom gosto ao criar lindas dinâmicas.

Layout

Em primeiro lugar, se pensa no Layout.

O Layout é responsável pelo tamanho de sua tabela, tanto horizontal, quanto vertical.

No entanto, o ideal é sempre desabilitar o ajuste automático de largura, pois isso pode prejudicar suas informações. Embora, para isso seja necessário seguir o passo a passo a seguir.

Botão direito do mouse encima da tabela: Opções da Tabela Dinâmica.

Como você pode observar, nesse campo de opções você terá todas as maneiras de configurar o Layout de sua tabela.

Design

Em segundo lugar, vem o design de sua dinâmica, esse será o carro chefe de sua apresentação visual.

Mas para isso, basta selecionar sua tabela e você vai notar nas guias do Excel que ficou habilitado o campo Design.

Como podemos observar, nosso design está formatado com o modelo padrão, podemos então alterar o visual estético de nossa dinâmica. Conheça as alternativas.

Portanto, aqui você vai embelezar sua tabela dinâmica. Por outro lado, se você não gostou de nenhum modelo disponível, você pode criar o seu próprio modelo.

Embora, seja mais difícil, você pode criar através da opção: Novo Estilo de Tabela Dinâmica.

Formatação

Por outro lado, e não menos importante, precisamos formatar nossa dinâmica, nessa formatação se inclui: Fontes, tamanho e caracteres.

Com isso, através da tela inicial, selecionamos nossa dinâmica e através da guia inicial, vamos formatar a fonte.

Note que para isso precisamos selecionar todas as células.

Após isso, vamos inserir a formatação monetária em nossa Coluna B onde se encontram a soma de valor.

Portanto, aprendemos configurar totalmente nossas tabelas dinâmicas.

Como Atualizar Tabela Dinâmica

Embora o excel nos facilite em muitas opções, no momento de atualizar as tabelas dinâmicas, ele acaba dificultando um pouco.

Mas, para ficar mais claro, vamos demonstrar o motivo: Toda vez que você modifica, acrescenta, altera sua base de dados, essas informações precisam ser replicadas para sua tabela dinâmica, correto?

Embora, esse seja o mundo ideal, para o Excel não é bem assim, toda vez que você modifica uma informação na base de dados é necessário que você atualize tudo.

Com isso, existem duas maneiras, atualizar manual, ou com alguma programação em VBA.

Atualizar Manualmente

Em resumo, essa se torna a maneira mais comum de se atualizar as tabelas dinâmicas de uma vez só.

Em outras palavras, para realizar essa ação, vamos selecionar nossa tabela.

Após isso, em nossa Guia inicial -> Dados -> Atualizar tudo.

Portanto, todas as modificações que foram realizadas na base de dados serão convertidas agora para sua dinâmica.

No entanto, além de poder atualizar a tabela dinâmica, pela guia inicial, também podemos através do botão direito do mouse.

Selecione com o botão direito sobre a tabela e verifique que vamos ter a opção: Atualizar.

Atualizar Automaticamente

Após isso, conhecemos as maneiras manual de atualizar dinâmicas.

Porém, se você já é um usuário avançado de excel, possivelmente vai gostar mais de utilizar a maneira automática de atualizar dinâmicas.

Mas, se você deseja se aprofundar um pouco mais sobre atualizar em VBA, recomendamos a que assista: Atualizar dinâmicas em VBA.

Por outro lado, vamos deixar disponível nesse artigo o código necessário para atualizar todas as tabelas dinâmicas de uma vez automaticamente.

Sub RefreshPivotTables()

  Dim pivotTable As pivotTable

  For Each plan In ActiveWorkbook.Sheets
    For Each pivotTable In plan.PivotTables
        pivotTable.RefreshTable
    Next
  Next

End Sub

Após isso, basta inserir o código em um botão de chamada.

Como Inserir Segmentação de Dados no Excel

Em primeiro lugar, após você já estar avançado no uso de tabelas dinâmicas, vamos demonstrar como inserir segmentação de dados em suas tabelas.

Em resumo, tal ação te permite interligar mais de uma tabela dinâmica com um botão de seleção.

Selecione sua Tabela. Após isso, através da guia inicial se direcione em Análise de Tabela Dinâmica -> Inserir Segmentação de Dados.

Em segundo lugar, selecione a categoria que você gostaria de facilitar o filtro dentro da tabela.

Portanto, você terá sua segmentação de dados criada. Se tudo deu certo, ficará assim:

Como você vai poder observar, a segmentação de dados serve como uma espécie de filtro no excel, facilitando então a análise completa de uma tabela.

Interligar duas Tabela Dinâmicas

Embora, a segmentação de dados criada, atenda apenas uma tabela dinâmica, você pode utilizar esse mesmo filtro para interligar com outra.

Desde que, sua base de dados seja igual.

Portanto, selecione sua segmentação de dados com o botão direito, se deslocando até: Conexões de Relatório.

Portanto, você precisa deixar todas as tabelas dinâmicas selecionadas.

Após isso, com apenas uma segmentação de dados você vai conseguir manipular ambas tabelas.

Campo Calculado em Tabela Dinâmica

Embora, você criou tabelas, configurou, e no momento de trazer um resultando no campo de valores percebeu que não existe a ferramenta de divisão por exemplo.

Com isso, existe a ferramenta de Criar Campo Calculado no Excel.

Em resumo, essa ferramenta te permite criar um cálculo personalizado.

Como Criar um Campo Calculado personalizado

Em primeiro lugar, precisamos encontrar a ferramenta, correto?

Com isso, selecionamos nossa dinâmica, e através da Guia inicial -> Análise da Tabela Dinâmica -> Campos, Itens e Conjuntos -> Campo Calculado.

Por outro lado, para ficar visual, ele está localizado aqui:

Após isso, vamos nos deparar com uma tela semelhante a essa.

Portanto, após estar nessa tela, vamos detalhar as informações:

1. Nome: Descrição que você dará para a fórmula criada manual.
2. Fórmula: Cálculo personalizado que deseja criar.

Embora, o campo de fórmula seja bastante limitado, ele nos facilita em cálculos matemáticos simples, como Soma, Divisão, etc.

Como um exemplo, vamos inserir nosso campo de Valor e realizar a divisão por dois.

Após isso, você vai perceber em sua tabela dinâmica, que o resultado final será diferente do cálculo anterior. Por outro lado, ele foi dividido por dois.

No entanto, você pode realizar a divisão por outro campo, para isso bastante selecionar outra categoria no lugar do número 2.

Aprendendo sobre Gráficos Dinâmicos

Como resultado, após aprender movimentar e mexer com qualquer modelo de tabela dinâmica, chegamos aqui no resultado mais importante de uma análise.

Os famosos gráficos dinâmicos no Excel são excelentes meio de demonstrar o seu trabalho estético dentro de uma tabela dinâmica.

Em outras palavras, ele é responsável por demonstrar a parte bruta em apenas gráficos.

Com isso, demonstramos quatro exemplos de gráficos dinâmicos no excel, são eles: Barras, Colunas, Linhas e Pizza.

Como Criar Gráficos Dinâmicos

Em primeiro lugar, com nossa tabela dinâmica selecionada, vamos então nos deslocar através da Guia inicial -> Inserir Gráfico Dinâmico.

Portanto, como você vai perceber, o gráfico foi criado utilizando as informações da tabela dinâmica selecionada.

Após isso, vamos então ter um gráfico inserido semelhante ao abaixo.

Como Alterar Tipo de Gráfico Dinâmico

Embora, em primeiro momento gráfico dinâmico criado acaba sendo um gráfico de colunas, conseguimos logo manipular e alterar o gráfico.

Mas, para isso, selecione o gráfico criado, através da Guia inicial -> Design -> Alterar Tipo de Gráfico.

Após isso, temos todos os gráficos comuns disponíveis para inserir e alterar, destacamos os 4 gráficos dinâmicos mais utilizados.

4 Gráficos Dinâmicos mais utilizados

A seguir, conheça os modelos mais utilizados, e mais conhecidos por quem utiliza o excel de maneira profissional ou recreativa.

Destacamos apenas exemplos visuais, com um breve relato.

Barras

Embora, seja semelhante ao gráfico de colunas, tem sua particularidade de ser representado na horizontal. Responsável por demonstrar valores separados por categoria.

Colunas

Em resumo, o mais utilizado do Excel. No entanto, tem sua demonstração de maneira vertical.

Linhas

Se torna um modelo bastante utilizado para comparações de dados, classificar valor por meta, realizado, objetivo, entre outros.

Pizza

Em resumo, quando tivermos menos informações e precisamos ter um percentual demonstrativo. Com isso, utilizamos o gráfico de pizza, que nos atende perfeitamente, quando se resume em tamanho de informações.

Conclusão

Portanto, se você chegou até aqui, possivelmente tenha tido um aprendizado bastante aprofundado sobre tabelas dinâmicas.

Pensando nisso, a Studio Excel recomenda que assista o vídeo completo sobre como criar um dashboard do zero através do excel.

Acreditamos que essa seja a maneira perfeita de colocar em prática tudo aquilo que aprendemos nesse passo a passo completo, de tabelas dinâmicas no excel.

Se você gostou desse artigo, não deixe de comentar e compartilhar com os seus amigos.