Como calcular a variação percentual com tabelas dinâmicas no Excel

As tabelas dinâmicas são uma incrível ferramenta de relatório embutida no Excel. Embora normalmente usado para resumir dados com totais, você também pode usá-los para calcular a porcentagem de mudança entre os valores. Melhor ainda: é simples de fazer.

Você poderia usar essa técnica para fazer todos os tipos de coisas – praticamente em qualquer lugar que você gostaria de ver como um valor se compara a outro. Neste artigo, usaremos o exemplo simples de cálculo e exibição da porcentagem pela qual o valor total das vendas muda mês a mês.

Aqui está a folha que vamos usar.

É um exemplo bastante típico de uma planilha de vendas que mostra a data do pedido, o nome do cliente, o representante de vendas, o valor total das vendas e algumas outras coisas.

Para fazer tudo isso, primeiro formataremos nosso intervalo de valores como uma tabela no Excel e, em seguida, criaremos uma Tabela Dinâmica para fazer e exibir nossos cálculos de alteração percentual.

Formatando o intervalo como uma tabela

Se o seu intervalo de dados ainda não estiver formatado como uma tabela, recomendamos que você faça isso. Os dados armazenados em tabelas têm vários benefícios sobre os dados em intervalos de células de uma planilha, especialmente ao usar tabelas dinâmicas ( leia mais sobre os benefícios do uso de tabelas ).

Para formatar um intervalo como uma tabela, selecione o intervalo de células e clique em Inserir> Tabela.

Verifique se o intervalo está correto, se você tem cabeçalhos na primeira linha desse intervalo e clique em “OK”.

O intervalo agora está formatado como uma tabela. Nomear a tabela tornará mais fácil consultá-la no futuro ao criar tabelas dinâmicas, gráficos e fórmulas.

Clique na guia “Design” em Ferramentas de Tabela e insira um nome na caixa fornecida no início da Faixa de Opções. Esta tabela foi chamada de “Vendas”.

Você também pode alterar o estilo da mesa aqui, se desejar.

Criar uma tabela dinâmica para exibir a variação percentual

Agora, vamos continuar com a criação da Tabela Dinâmica. Na nova tabela, clique em Inserir> Tabela Dinâmica.

A janela Criar Tabela Dinâmica é exibida. Ele terá detectado automaticamente sua mesa. Mas você pode selecionar a tabela ou intervalo que deseja usar para a tabela dinâmica neste momento.

Agrupe as datas em meses

Em seguida, arrastaremos o campo de data pelo qual queremos agrupar para a área de linhas da Tabela Dinâmica. Neste exemplo, o campo é denominado Data do pedido.

A partir do Excel 2016, os valores de data são agrupados automaticamente em anos, trimestres e meses.

Se a sua versão do Excel não faz isso, ou você simplesmente deseja alterar o agrupamento, clique com o botão direito em uma célula que contém um valor de data e selecione o comando “Grupo”.

Selecione os grupos que deseja usar. Neste exemplo, apenas anos e meses são selecionados.

O ano e o mês são agora campos que podemos usar para análise. Os meses ainda são nomeados como Data do pedido.

Adicione os campos de valor à tabela dinâmica

Mova o campo Ano de Linhas e para a área Filtro. Isso permite que o usuário filtre a Tabela Dinâmica por um ano, em vez de bagunçar a Tabela Dinâmica com muitas informações.

Arraste o campo que contém os valores (Valor total de vendas neste exemplo) que deseja calcular e apresente a alteração na área Valores duas vezes .

Pode não parecer muito ainda. Mas isso vai mudar muito em breve.

Ambos os campos de valor terão a soma padrão e atualmente não têm formatação.

Os valores da primeira coluna que gostaríamos de manter como totais. No entanto, eles requerem formatação.

Clique com o botão direito em um número na primeira coluna e selecione “Formatação de números” no menu de atalho.

Escolha o formato “Contabilidade” com 0 casas decimais na caixa de diálogo Formatar células.

A Tabela Dinâmica agora se parece com isto:

Criar a coluna de variação percentual

Clique com o botão direito em um valor na segunda coluna, aponte para “Mostrar valores” e clique na opção “% de diferença de”.

Selecione “(Anterior)” como o Item de Base. Isso significa que o valor do mês atual é sempre comparado ao valor dos meses anteriores (campo Data do Pedido).

A tabela dinâmica agora mostra os valores e a alteração percentual.

Clique na célula que contém os rótulos das linhas e digite “Mês” como cabeçalho dessa coluna. Em seguida, clique na célula do cabeçalho para a segunda coluna de valores e digite “Variância”.

Adicionar algumas setas de variação

Para realmente polir esta Tabela Dinâmica, gostaríamos de visualizar melhor a alteração percentual adicionando algumas setas verdes e vermelhas.

Isso nos fornecerá uma maneira adorável de ver se uma mudança foi positiva ou negativa.

Clique em qualquer um dos valores na segunda coluna e, a seguir, clique em Home> Formatação condicional> Nova regra. Na janela Editar regra de formatação que é aberta, execute as seguintes etapas:

1. Selecione a opção “Todas as células mostrando valores de“ Variância ”para Data do Pedido”.
2. Selecione “Conjuntos de ícones” na lista Estilo de formato.
3. Selecione os triângulos vermelho, âmbar e verde na lista Estilo de ícone.
4. Na coluna Tipo, altere a opção da lista para dizer “Número” em vez de Porcentagem. Isso mudará a coluna Value para 0’s. Exatamente o que queremos.

Clique em “OK” e a Formatação Condicional é aplicada à Tabela Dinâmica.

As tabelas dinâmicas são uma ferramenta incrível e uma das maneiras mais simples de exibir a variação percentual dos valores ao longo do tempo.