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 construí-la.

Antes de criar a Tabela Dinâmica, prepare a base: analise quais informações precisa resumir e formate os dados com o Formatar como Tabela, na guia Página Inicial. Assim cada novo lançamento entra no intervalo automaticamente e basta atualizar. Crie a tabela em uma nova planilha e desmarque o ajuste automático de largura das colunas.

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

O que você aprende neste vídeo

Resposta rápida: A aula ensina dois truques antes de montar a Tabela Dinâmica: formatar a base de dados como tabela e nomeá-la, para que toda linha nova entre automaticamente no intervalo da tabela dinâmica; e criar um botão com uma macro gravada que atualiza o resumo com um clique, sem passar pelo menu de atualizar.

Neste vídeo (24 min):

• 1:35 - Atalho Ctrl + * para selecionar toda a base de dados de uma vez, em vez de arrastar o mouse até o fim das 3.295 linhas
• 2:20 - Inserir > Tabela Dinâmica numa nova planilha, confirmando o intervalo selecionado
• 3:50 - Campo “marca” arrastado para Linhas e “faturado” arrastado para Valores gera o total por marca
• 5:05 - Campo “data” arrastado para Colunas agrupa o resultado por mês automaticamente
• 7:10 - Opções da Tabela Dinâmica > desmarcar “ajustar automaticamente a largura das colunas ao atualizar”, para a largura não mudar sozinha a cada atualização
• 10:05 - Uma linha nova na base de dados não aparece na tabela dinâmica depois de atualizar, porque o intervalo de origem continua sendo o que foi selecionado na criação
• 12:10 - Truque 1: aplicar “Formatar como Tabela” na base de dados antes de criar a tabela dinâmica, e dar um nome a ela (Base_Dados) no Gerenciador de Nomes
• 13:10 - A nova tabela dinâmica é criada apontando para o nome da tabela, em vez de um intervalo de células fixo
• 15:10 - Linha adicionada à base com Ctrl + Shift + seta entra sozinha no intervalo da tabela e aparece ao atualizar a tabela dinâmica
• 17:10 - Truque 2: um retângulo com o texto “Clique para atualizar o resumo” funciona como botão
• 19:10 - Gravação de uma macro que executa o comando Atualizar, e atribuição dela ao botão pelo clique direito
• 21:00 - Teste final: nova linha na base e um clique no botão atualizam a tabela dinâmica na hora, sem passar pelos menus

Trechos do vídeo:

• A tabela dinâmica não atualiza sozinha quando uma linha nova entra na base: o comando Atualizar reprocessa o mesmo intervalo de origem, que não muda de tamanho por conta própria
• Formatar a base de dados como tabela e nomeá-la faz o intervalo crescer sozinho a cada linha nova, e é esse nome que a tabela dinâmica passa a usar como fonte
• Desmarcar “ajustar automaticamente a largura das colunas ao atualizar” evita que o Excel redimensione as colunas toda vez que a tabela dinâmica é atualizada
• Uma macro gravada para o comando Atualizar, atribuída a um botão desenhado na planilha, tira a necessidade de abrir o menu de Tabela Dinâmica toda vez

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.

É 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.

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).

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.

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).

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

Perguntas frequentes

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?

---