Como criar uma tabela dinâmica no Excel: um tutorial passo a passo

A tabela dinâmica é uma das funções mais poderosas – e intimidantes – do Microsoft Excel. Poderoso porque pode ajudá-lo a resumir e entender grandes conjuntos de dados. Intimidante porque você não é exatamente um especialista em Excel, e as tabelas dinâmicas sempre tiveram a reputação de serem complicadas.

A boa notícia: aprender a criar uma tabela dinâmica no Excel é muito mais fácil do que você pode acreditar.

Mas antes de orientá-lo no processo de criação de uma, vamos dar um passo atrás e garantir que você entenda exatamente o que é uma tabela dinâmica e por que você pode precisar usar uma.

Em outras palavras, as tabelas dinâmicas extraem significado dessa confusão aparentemente interminável de números na tela. E, mais especificamente, permite agrupar seus dados de maneiras diferentes para que você possa tirar conclusões úteis com mais facilidade.

A parte “pivot” de uma tabela dinâmica decorre do fato de que você pode girar (ou girar) os dados na tabela para visualizá-los de uma perspectiva diferente. Para ser claro, você não está adicionando, subtraindo ou alterando seus dados ao fazer um pivô. Em vez disso, você está simplesmente reorganizando os dados para que possa revelar informações úteis a partir deles.

Para que servem as tabelas dinâmicas?

Se você ainda está um pouco confuso sobre o que as tabelas dinâmicas realmente fazem, não se preocupe. Esta é uma daquelas tecnologias que são muito mais fáceis de entender quando você a vê em ação.

O objetivo das tabelas dinâmicas é oferecer maneiras fáceis de resumir rapidamente grandes quantidades de dados. Eles podem ser usados ​​para melhor entender, exibir e analisar dados numéricos em detalhes - e podem ajudar a identificar e responder a perguntas imprevistas em torno deles.

Aqui estão sete cenários hipotéticos em que uma tabela dinâmica pode ser uma solução:

1. Comparação dos totais de vendas de diferentes produtos.

Digamos que você tenha uma planilha que contém dados de vendas mensais para três produtos diferentes – produto 1, produto 2 e produto 3 – e deseja descobrir qual dos três está trazendo mais dinheiro. Você pode, é claro, examinar a planilha e adicionar manualmente o número de vendas correspondente a um total corrente toda vez que o produto 1 aparecer. Você pode fazer o mesmo para o produto 2 e o produto 3 até ter totais para todos eles. Pedaço de bolo, certo?

Agora, imagine que sua planilha mensal de vendas tenha milhares e milhares de linhas. Classificar manualmente todos eles pode levar uma vida inteira. Usando uma tabela dinâmica, você pode agregar automaticamente todos os números de vendas do produto 1, produto 2 e produto 3 — e calcular suas respectivas somas — em menos de um minuto.

2. Mostrando as vendas de produtos como porcentagens das vendas totais.

As tabelas dinâmicas mostram naturalmente os totais de cada linha ou coluna quando você as cria. Mas essa não é a única figura que você pode produzir automaticamente.

Digamos que você inseriu os números de vendas trimestrais de três produtos separados em uma planilha do Excel e transformou esses dados em uma tabela dinâmica. A tabela forneceria automaticamente três totais na parte inferior de cada coluna — somando as vendas trimestrais de cada produto. Mas e se você quisesse descobrir a porcentagem que essas vendas de produtos contribuíram para todas as vendas da empresa, em vez de apenas o total de vendas desses produtos?

Com uma tabela dinâmica, você pode configurar cada coluna para fornecer a porcentagem da coluna de todos os totais das três colunas, em vez de apenas o total da coluna. Se três vendas de produtos totalizaram $ 200.000 em vendas, por exemplo, e o primeiro produto faturou $ 45.000, você pode editar uma tabela dinâmica para dizer que esse produto contribuiu com 22,5% de todas as vendas da empresa.

Para mostrar as vendas de produtos como porcentagens do total de vendas em uma tabela dinâmica, basta clicar com o botão direito do mouse na célula que contém um total de vendas e selecionar Mostrar valores como > % do total geral .

3. Combinando dados duplicados.

Nesse cenário, você acabou de concluir uma reformulação do blog e teve que atualizar vários URLs. Infelizmente, o software de relatórios do seu blog não lidou muito bem com isso e acabou dividindo as métricas de “visualização” para posts únicos entre dois URLs diferentes. Portanto, em sua planilha, você tem duas instâncias separadas de cada postagem de blog individual. Para obter dados precisos, você precisa combinar os totais de visualização para cada uma dessas duplicatas.

É aí que a tabela dinâmica entra em jogo. Em vez de ter que procurar e combinar manualmente todas as métricas das duplicatas, você pode resumir seus dados (via tabela dinâmica) pelo título da postagem do blog e pronto: as métricas de visualização dessas postagens duplicadas serão agregadas automaticamente.

4. Obter um número de funcionários para departamentos separados.

As tabelas dinâmicas são úteis para calcular automaticamente coisas que você não consegue encontrar facilmente em uma tabela básica do Excel. Uma dessas coisas é contar as linhas que todas têm algo em comum.

Se você tiver uma lista de funcionários em uma planilha do Excel, por exemplo, e ao lado dos nomes dos funcionários estão os respectivos departamentos aos quais eles pertencem, você pode criar uma tabela dinâmica a partir desses dados que mostra o nome de cada departamento e o número de funcionários que pertencem a esses departamentos. A tabela dinâmica elimina efetivamente sua tarefa de classificar a planilha do Excel por nome de departamento e contar cada linha manualmente.

5. Adicionando valores padrão a células vazias.

Nem todo conjunto de dados inserido no Excel preencherá todas as células. Se você estiver aguardando a chegada de novos dados antes de inseri-los no Excel, poderá ter muitas células vazias que parecem confusas ou precisam de mais explicações ao mostrar esses dados ao seu gerente. É aí que entram as tabelas dinâmicas.

Você pode personalizar facilmente uma tabela dinâmica para preencher células vazias com um valor padrão, como $0 ou TBD (para “a ser determinado”). Para grandes tabelas de dados, poder marcar essas células rapidamente é um recurso útil quando muitas pessoas estão revisando a mesma planilha.

Para formatar automaticamente as células vazias da tabela dinâmica, clique com o botão direito do mouse na tabela e clique em Opções de tabela dinâmica. Na janela que aparece, marque a caixa Esvaziar Células Como e digite o que você gostaria que fosse exibido quando uma célula não tiver outro valor.

Como criar uma tabela dinâmica

1. Insira seus dados em um intervalo de linhas e colunas.
2. Classifique seus dados por um atributo específico.
3. Realce suas células para criar sua tabela dinâmica.
4. Arraste e solte um campo na área “Row Labels”.
5. Arraste e solte um campo na área “Valores”.
6. Ajuste seus cálculos.

Agora que você tem uma noção melhor de como as tabelas dinâmicas podem ser usadas, vamos entrar no âmago da questão de como realmente criar uma.

Etapa 1. Insira seus dados em um intervalo de linhas e colunas.

Cada tabela dinâmica no Excel começa com uma tabela básica do Excel, onde todos os seus dados estão alojados. Para criar esta tabela, basta inserir seus valores em um conjunto específico de linhas e colunas. Use a linha superior ou a coluna superior para categorizar seus valores pelo que eles representam.

Por exemplo, para criar uma tabela Excel de dados de desempenho de postagem de blog, você pode ter uma coluna listando cada “Páginas principais”, uma coluna listando os “Cliques” de cada URL, uma coluna listando as “Impressões” de cada postagem e assim por diante. (Vamos usar esse exemplo nas etapas a seguir.)

Etapa 2. Classifique seus dados por um atributo específico.

Quando você tiver todos os dados que deseja inseridos em sua planilha do Excel, você desejará classificar esses dados de alguma forma para facilitar o gerenciamento depois de transformá-los em uma tabela dinâmica.

Para classificar seus dados, clique na guia Dados na barra de navegação superior e selecione o ícone Classificar abaixo dele. Na janela que aparece, você pode optar por classificar seus dados por qualquer coluna que desejar e em qualquer ordem.

Por exemplo, para classificar sua planilha do Excel por "Visualizações até a data", selecione o título da coluna em Coluna e selecione se deseja ordenar suas postagens do menor para o maior ou do maior para o menor.

Selecione OK no canto inferior direito da janela Classificar e você reordenará com êxito cada linha de sua planilha do Excel pelo número de visualizações que cada postagem de blog recebeu.

Etapa 3. Realce suas células para criar sua tabela dinâmica.

Depois de inserir os dados em sua planilha do Excel e classificá-los ao seu gosto, destaque as células que deseja resumir em uma tabela dinâmica. Clique em Inserir na navegação superior e selecione o ícone de tabela dinâmica . Você também pode clicar em qualquer lugar da planilha, selecionar “Tabela Dinâmica” e inserir manualmente o intervalo de células que deseja incluir na Tabela Dinâmica.

Isso abrirá uma caixa de opções onde, além de definir seu intervalo de células, você poderá selecionar se deseja ou não iniciar essa tabela dinâmica em uma nova planilha ou mantê-la na planilha existente. Se você abrir uma nova planilha, poderá navegar até ela e sair dela na parte inferior da pasta de trabalho do Excel. Depois de escolher, clique em OK.

Como alternativa , você pode destacar suas células, selecionar Tabelas Dinâmicas Recomendadas à direita do ícone Tabela Dinâmica e abrir uma tabela dinâmica com sugestões predefinidas sobre como organizar cada linha e coluna.

Observação: se você estiver usando uma versão anterior do Excel, "Tabelas Dinâmicas" podem estar em Tabelas ou Dados na navegação superior, em vez de "Inserir". No Planilhas Google, você pode criar tabelas dinâmicas na lista suspensa Dados na navegação superior.

Etapa 4. Arraste e solte um campo na área “Row Labels”.

Depois de concluir a Etapa 3, o Excel criará uma tabela dinâmica em branco para você. Sua próxima etapa é arrastar e soltar um campo — rotulado de acordo com os nomes das colunas em sua planilha — na área Rótulos de linha . Isso determinará qual identificador exclusivo - título da postagem do blog, nome do produto e assim por diante - a tabela dinâmica organizará seus dados.

Por exemplo, digamos que você queira organizar vários dados de blogs por título de postagem. Para fazer isso, basta clicar e arrastar o campo “Páginas principais” para a área “Rótulos de linha”.

Observação: sua tabela dinâmica pode parecer diferente dependendo da versão do Excel com a qual você está trabalhando. No entanto, os princípios gerais permanecem os mesmos.

Etapa 5. Arraste e solte um campo na área “Valores”.

Depois de estabelecer o que você vai organizar seus dados, sua próxima etapa é adicionar alguns valores arrastando um campo para a área Valores .

Continuando com o exemplo de dados de blog, digamos que você queira resumir as visualizações de postagem do blog por título. Para fazer isso, basta arrastar o campo “Visualizações” para a área Valores.

Etapa 6. Ajuste seus cálculos.

A soma de um valor específico será calculada por padrão, mas você pode facilmente alterar isso para algo como média, máximo ou mínimo, dependendo do que deseja calcular.

Em um Mac, você pode fazer isso clicando no pequeno i ao lado de um valor na área “Valores”, selecionando a opção desejada e clicando em “OK”. Depois de fazer sua seleção, sua tabela dinâmica será atualizada de acordo.

Se você estiver usando um PC, precisará clicar no pequeno triângulo de cabeça para baixo ao lado do seu valor e selecionar Configurações do campo de valor para acessar o menu.

Quando você categorizar seus dados ao seu gosto, salve seu trabalho e use-o como quiser.

Indo mais fundo com tabelas dinâmicas

Agora você aprendeu os fundamentos da criação de tabelas dinâmicas no Excel. Com esse entendimento, você pode descobrir o que precisa em sua tabela dinâmica e encontrar as soluções que procura.

Por exemplo, você pode notar que os dados em sua tabela dinâmica não estão classificados da maneira que você gostaria. Se este for o caso, a função Classificar do Excel pode ajudá-lo. Alternativamente, você pode precisar incorporar dados de outra fonte em seus relatórios, caso em que a função PROCV pode ser útil.

Nota do editor: Este post foi publicado originalmente em dezembro de 2018 e foi atualizado para maior abrangência.