Blog

Postado em em 10 de novembro de 2020

O que fazer antes da Tabela Dinâmica? Dois Truques Simples

Você já se perguntou o que fazer antes da Tabela Dinâmica? Vou te mostrar quais são os cuidados que você deve tomar antes de construi-la.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

O que é uma Tabela Dinâmica no Excel?

A Tabela Dinâmica nada mais é do que uma tabela resumo, ou seja, é um resumo gerencial dos dados de uma planilha maior. Com esse resumo o usuário tem uma facilidade maior para analisar as informações que tem, verificar totais, tem uma melhor representação dos dados e com tudo isso pode fazer uma análise mais detalhada e tomar decisões mais assertivas do que se estivesse analisando a tabela base como um todo.

Quando utilizar a Tabela Dinâmica?

Vamos utilizar essa tabela sempre que quisermos um resumo de forma fácil e rápida, ao invés de utilizar fórmulas para chegar nesse resultado. Será muito utilizada para facilitar a análise de dados, principalmente quando temos muitas informações. E como o próprio nome já diz ela é dinâmica, então o usuário consegue alterar as visualizações dos dados de forma rápida e fácil.

Excel Tabela Dinâmica, como construir?

O primeiro passo antes da construção de qualquer gráfico ou tabela dentro do Excel é fazer uma análise das informações que temos para verificar como podemos representar essas informações de forma eficiente.

Base de dados
Base de dados

É possível observar que temos as informações de data, marca, produto e quantidade faturada. Isso quer dizer que temos um registro de vendas de alguns produtos e muita das vezes é importante ter um resumo de vendas para verificar o faturamento total por produto, por marca e até mesmo por períodos.

Por esse motivo é que vamos criar uma tabela dinâmica. O primeiro passo é selecionar todos os dados, como temos muitos não é viável fazer essa seleção com o mouse, portanto podemos utilizar o atalho CTRL + T (após selecionar uma célula qualquer das informações que temos).

Feito isso basta ir até a guia Inserir e selecionar a opção Tabela Dinâmica, após essa seleção será mostrada uma nova janela para que o usuário faça algumas configurações da tabela dinâmica.

Configuração da tabela dinâmica
Configuração da tabela dinâmica

A primeira delas é a seleção de dados, que pode ser do próprio arquivo ou de uma base externa e se a tabela dinâmica será criada na mesma planilha ou em uma nova. É recomendado que essa tabela seja sempre criada em uma Nova Planilha.

Ao clicar em OK a tabela dinâmica será criada em outra planilha, no entanto ela não terá nenhum formato, pois o usuário é que vai escolher as informações que serão apresentadas na tabela (e poderá mudar sempre que quiser).

Criação da tabela dinâmica
Criação da tabela dinâmica

A tabela dinâmica está criada e selecionada, por isso temos os Campos da Tabela Dinâmica a direita do programa para que o usuário possa pegar as informações que tem na tabela base e separar entre as áreas que deseja visualizar.

Então o que pode ser feito é arrastar as informações de cada coluna para os campos que temos da tabela dinâmica, dessa forma a tabela irá ser construída e terá um resumo automático sem que o usuário precise fazer qualquer cálculo.

Campos da tabela preenchidos
Campos da tabela preenchidos

Neste caso vamos colocar essas duas informações nesses dois campos, assim a tabela será construída dessa maneira já fazendo a soma de todos os valores de faturamento para cada uma das marcas (já vamos também formatar como moeda).

Tabela dinâmica criada
Tabela dinâmica criada

Então de uma forma muito simples já temos um resumo do faturamento total de cada uma das marcas sem esforço e sem precisar fazer qualquer tipo de cálculo.

Como é possível sempre fazer essas alterações, podemos por exemplo colocar as informações de Data no campo de Colunas, assim teremos o faturamento de cada marca separado por meses.

Inserindo a informação de data no campo de colunas
Inserindo a informação de data no campo de colunas

Um ponto importante é que sempre que fazemos esse tipo de mudança o Excel ajusta a largura das colunas de forma automática o que pode deixar a formatação de cada coluna diferente e não muito visual.

Então imagine toda vez que faz uma atualização de dados as colunas mudam a largura para se adequar aquele novo dado, isso pode ser um problema porque elas podem ficar de tamanhos diferentes e o visual da tabela não ficar tão agradável.

Então uma forma de resolver isso é clicando com o botão direito na tabela dinâmica e selecionando Opções da Tabela Dinâmica.

Opções da tabela dinâmica
Opções da tabela dinâmica

Dentro dessa janela vamos desmarcar a caixa de Ajustar automaticamente a largura das colunas ao atualizar. Desta forma sempre que fizermos uma atualização ela vai manter a largura que o usuário já estipulou para que não tenha cada coluna com uma largura deixando a tabela sem um padrão.

Desmarcando o ajuste automático de colunas
Desmarcando o ajuste automático de colunas

Lembrando que o usuário poderá fazer algumas alterações na tabela na guia Design para alterar estilo, criar seu próprio, remover ou adicionar totais e ou subtotais.

Nesse exemplo vamos alterar algumas formatações e vamos ocultar a primeira e terceira linha da tabela dinâmica, pois não possuem informações que precisamos visualizar.

Tabela dinâmica após formatação
Tabela dinâmica após formatação

Veja que desta forma e retirando as linhas de grade dentro de guia Exibir, temos uma tabela mais limpa e mais fácil de visualizar as informações.

Feito isso vamos supor que o usuário precise inserir mais informações na sua base de dados, então vamos inserir uma informação qualquer após a última linha.

Inserindo nova informação na base de dados
Inserindo nova informação na base de dados

Neste caso fizemos a inserção de um novo tênis, uma nova marca e o faturamento dessa venda. Feito isso se o usuário voltar a tabela dinâmica ela não será atualizada com a nova informação. Isso acontece porque o Excel não faz a atualização automática da tabela dinâmica.

Uma solução seria selecionar a tabela dinâmica, ir até a guia Análise de Tabela Dinâmica e clicar em Atualizar.

Opções de atualização da tabela dinâmica
Opções de atualização da tabela dinâmica

Só que teríamos outro problema, pois ao atualizar não iria acontecer nada. Isso acontece porque o nosso intervalo de dados ainda continua o mesmo, ou seja, a nova linha não está inclusa nesse intervalo, então teríamos que clicar em Alterar Fonte de Dados e selecionar todos os dados incluindo a nova informação.

Tabela dinâmica atualizada
Tabela dinâmica atualizada

Feito isso temos a nova marca e a nova venda atualizada dentro da tabela dinâmica, no entanto temos uma forma para que o usuário não tenha sempre que alterar a fonte de dados sempre que coloca um novo dado, então ao invés de alterar a fonte de dados bastaria atualizar.

Para corrigir esse “problema” basta formatar toda a nossa base de dados como tabela, utilizando a ferramenta Formatar como Tabela que fica dentro da guia Página Inicial.

Formatando a base de dados como tabela
Formatando a base de dados como tabela

Feito isso sempre que inserirmos novos dados o Excel automaticamente já vai considerar que aquilo faz parte da nossa base de dados, então essa seleção ficaria automática. Desta forma se adicionarmos outra venda dentro da tabela ela já pega a formatação e já considera que aquele dado faz parte da tabela.

Inserindo novas informações
Inserindo novas informações

Fizemos a adição de duas novas vendas, agora se formos até a tabela dinâmica e atualizar essas duas informações serão inseridas de forma automática sem que seja necessário alterar o intervalo de seleção.

Atualizando a tabela dinâmica
Atualizando a tabela dinâmica

Agora temos as vendas de outubro e novembro adicionadas dentro da marca Hashtag automaticamente. Então temos uma maneira mais fácil para atualizar essas informações.

Agora outra maneira que temos também ao invés de toda vez selecionar a tabela e ir em atualizar é gravar uma macro com essa ação e atribuir ela a uma forma do Excel, assim sempre que clicarmos nessa forma o Excel vai reproduzir essa mesma ação.

Para fazer isso é simples vamos até a guia Exibir, em seguida em Macros e por fim em Gravar Macro.

Iniciando a gravação da macro
Iniciando a gravação da macro

A partir desse ponto todas as ações que forem feitas dentro do Excel serão gravadas, então vamos clicar na tabela dinâmica, em seguida ir até a guia Análise de Tabela Dinâmica e clicar em Atualizar.

OBS: Lembrando que ao clicar em Gravar Macro o Excel vai solicitar um nome para esse macro e um atalho (mas esse é opcional).

Feito isso basta clicar no botão de pause que fica no canto inferior esquerdo do programa ou ir até a guia Exibir, depois em Macros e por fim em Parar Gravação.

Parando a gravação da macro
Parando a gravação da macro

Com a macro criada podemos ir até a guia Inserir e inserir uma forma qualquer e colocar um texto para informar que ao clicar teremos uma atualização.

Criando um "botão" com uma forma do Excel
Criando um “botão” com uma forma do Excel

Feito isso agora basta clicar com o botão esquerdo do mouse e selecionar a opção Atribuir macro.

Atribuindo a macro ao botão criado
Atribuindo a macro ao botão criado

Em seguida basta escolher a macro que acabamos de criar e pressionar OK. Agora sempre que o usuário fizer uma alteração na base de dados e clicar nesse “botão” os dados serão atualizados.

Inserindo nova informação na base de dados
Inserindo nova informação na base de dados

Ao clicar no botão a atualização será feita sem que o usuário tenha que repetir sempre os mesmos passos para atualizar, então dá menos trabalho e qualquer um pode atualizar o resumo mesmo que não tenha conhecimentos em Excel.

Atualizando a tabela com o botão
Atualizando a tabela com o botão

IMPORTANTE: Como utilizamos uma macro nesse arquivo é muito IMPORTANTE que na hora de salvar o arquivo o usuário altere o tipo dele para Pasta de Trabalho Habilitada para Macro do Excel, caso contrário o botão não irá funcionar quando abrir a planilha novamente com o salvamento normal.

Nessa aula foi possível aprender a como criar uma tabela dinâmica de forma fácil e rápida, como evitar as mudanças de largura sempre que o usuário atualiza sua tabela dinâmica. Outros dois pontos muito importantes, que são como não precisar fazer atualização do intervalo formatando a base de dados como tabela e como atribuir a atualização da tabela dinâmica a um botão facilitando o processo mesmo para pessoas que não saibam mexer no Excel.

Então todas as mudanças, sejam inserindo, modificando ou removendo informações da base de dados serão atualizadas ao clicar no botão, mesmo que o usuário exclua uma marca ou informações de um mês inteiro a tabela dinâmica será atualizada.

Agora basta praticar e melhorar suas análises em planilhas pessoas e planilhas do trabalho para impressionar os colegas e o seu chefe.

Hashtag Treinamentos

Para acessar outras publicações de Excel Intermediário, clique aqui!


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


Quer sair do zero em Power BI e impressionar no mercado de trabalho? Se inscreva agora no Power BI Impressionador!