Como adicionar e usar campos calculados de tabela dinâmica do Excel

Normalmente, depois de criar uma tabela dinâmica, você precisa expandir sua análise e incluir mais dados/cálculos nela.

Se você precisar de um novo ponto de dados que possa ser obtido usando um ponto de dados existente na tabela dinâmica, não há necessidade de voltar e adicioná-lo aos dados de origem.Em vez disso, você pode usarCampos calculados da tabela dinâmicapara fazer isso.

O que é um campo calculado de tabela dinâmica?

Vamos começar com um exemplo básico de uma tabela dinâmica.

Suponha que você tenha um conjunto de dados de varejistas e crie uma tabela dinâmica como esta:

A tabela dinâmica acima resume os valores de vendas e lucro do varejista.

Agora, e se você também quisesse saber quais eram as margens de lucro desses varejistas (a margem de lucro é "lucro" dividido por "vendas")?

Existem várias maneiras de fazer isso:

1. Volte ao conjunto de dados original e adicione este novo ponto de dados.Assim, você pode inserir uma nova coluna nos dados de origem e calcular a margem de lucro nela.Feito isso, você precisa atualizar os dados de origem da tabela dinâmica para obter essa nova coluna como parte dela.
   • Embora essa abordagem funcione, você precisa voltar manualmente ao conjunto de dados e fazer os cálculos.Por exemplo, você pode querer adicionar outra coluna para calcular a média de vendas por unidade (vendas/quantidade).Novamente, você precisa adicionar essa coluna aos dados de origem e atualizar a tabela dinâmica.
   • Esse método também incha sua tabela dinâmica à medida que você adiciona novos dados a ela.
2. Adicione cálculos fora das tabelas dinâmicas.Escolha esta opção se for improvável que a estrutura da sua tabela dinâmica mude.No entanto, se você alterar a tabela dinâmica, os cálculos podem não ser atualizados adequadamente e podem fornecer resultados incorretos ou erros.Conforme mostrado abaixo, calculei a margem de lucro quando há varejistas na linha.Mas quando mudo de cliente para área, a fórmula dá errado.
3. Calcular campos usando tabelas dinâmicas.isto éUse dados de tabela dinâmica existentes e calcule omaneira mais eficiente.Pense nos campos calculados como colunas virtuais que você adiciona usando colunas existentes em sua tabela dinâmica.O uso de campos calculados de tabela dinâmica tem muitos benefícios (como veremos mais adiante):
   • Não requer que você trabalhe com fórmulas ou atualize dados de origem.
   • É extensível porque leva em consideração automaticamente quaisquer novos dados que você possa adicionar à tabela dinâmica.Depois de adicionar um campo calculado, você pode usá-lo como qualquer outro campo em uma tabela dinâmica.
   • É fácil de atualizar e gerenciar.Por exemplo, se uma métrica for alterada ou você precisar alterar um cálculo, poderá fazê-lo facilmente na própria tabela dinâmica.

Adicionar campos calculados à tabela dinâmica

Vamos ver como adicionar um campo calculado de tabela dinâmica a uma tabela dinâmica existente.

Suponha que você tenha uma tabela dinâmica parecida com esta e queira calcular a margem de lucro de cada varejista:

Aqui estão as etapas para adicionar um campo calculado de tabela dinâmica:

Após adicionar o campo calculado, ele aparecerá como um dos campos da lista Campos da Tabela Dinâmica.

Agora você pode usar esse campo calculado como qualquer outro campo de tabela dinâmica (observe que você não pode usar um campo calculado de tabela dinâmica como filtro de relatório ou segmentação de dados).

Como mencionei antes, o benefício de usar um campo calculado de tabela dinâmica é que você pode alterar a estrutura da tabela dinâmica e ela se ajustará automaticamente.

Por exemplo, se eu arrastar e soltar a região na área da linha, você obterá resultados como o seguinte, onde são informados os valores da margem de lucro do varejista e da região.

 

No exemplo acima, usei uma fórmula simples (=Lucro/Vendas) para inserir o campo calculado.No entanto, você também pode usar algumas fórmulas avançadas.

Antes de mostrar um exemplo de uso de uma fórmula avançada para criar um campo calculado de tabela dinâmica, aqui estão algumas coisas que você deve saber:

• Você não pode usar referências ou intervalos nomeados ao criar campos calculados de tabela dinâmica.Isso excluirá muitas fórmulas como PROCV, ÍNDICE, DESLOCAMENTO, etc.No entanto, você pode usar fórmulas que funcionam sem referências (por exemplo, SUM, IF, COUNT, etc...).
• Você pode usar constantes em fórmulas.Por exemplo, se você quiser saber as vendas previstas com expectativa de crescimento de 10%, use a fórmula =Vendas*1.1 (onde 1.1 é uma constante).
• Siga a ordem de precedência nas fórmulas que geram campos calculados.Como prática recomendada, use parênteses para garantir que você não precise lembrar a precedência.

Agora vamos ver um exemplo de criação de um campo calculado usando uma fórmula avançada.

Suponha que você tenha o conjunto de dados mostrado abaixo e precise exibir as vendas previstas em uma tabela dinâmica.

Para valores de previsão, você usaria um aumento de vendas de 300% para grandes varejistas (mais de 5 milhões de vendas) e um aumento de vendas de 300% para pequenos e médios varejistas (menos de 10 milhões de vendas).

Nota: Os números de vendas aqui são falsos e foram usados ​​para ilustrar os exemplos neste tutorial.

Aqui está como fazê-lo:

Isso adicionará uma nova coluna à tabela dinâmica com valores de previsão de vendas.

Problema de campo calculado da tabela dinâmica

Os campos calculados são um recurso incrível que realmente aumenta o valor das tabelas dinâmicas com cálculos de campo, mantendo tudo escalável e gerenciável.

No entanto, há um problema com os campos calculados de tabela dinâmica que você deve entender antes de usá-los.

Suponha que eu tenha uma tabela dinâmica como abaixo e use um campo calculado para obter os números de vendas previstos.

Observe que os subtotais e totais estão incorretos.

Embora estes devam adicionar valores de previsão de vendas separados para cada varejista, na realidade, segue a mesma fórmula de campo calculada que criamos.

Assim, para South Total, enquanto o valor deveria ser 22,824,000, South Total reporta incorretamente como 22,287,000.Isso acontece porque ele usa a fórmula 21,225,800*1.05 para obter o valor.

Infelizmente, você não pode corrigir esse problema.

A melhor maneira de lidar com isso é remover os subtotais e totais gerais da tabela dinâmica.

Você também pode passar Apresentado por DéboraAlgumas soluções inovadoras para lidar com esse problema.

Como modificar ou excluir um campo calculado da tabela dinâmica?

Depois de criar um campo calculado de tabela dinâmica, você pode modificar a fórmula ou excluí-la usando as seguintes etapas:

Como obter uma lista de todas as fórmulas de campo calculadas?

Se você criar muitos campos calculados de tabela dinâmica, não precisará se preocupar em acompanhar as fórmulas usadas em cada campo.

O Excel permite que você crie rapidamente uma lista de todas as fórmulas usadas para criar campos calculados.

Aqui estão as etapas para obter rapidamente uma lista de todas as fórmulas de campo calculadas:

• Selecione qualquer célula na tabela dinâmica.
• Vá para Ferramentas de Tabela Dinâmica -> Analisar -> Campos, Itens e Conjuntos -> Listar Fórmulas.

Depois de clicar na fórmula da lista, o Excel inserirá automaticamente uma nova planilha com detalhes de todos os campos/itens calculados que você usou na tabela dinâmica.

Essa pode ser uma ferramenta muito útil se você precisar enviar trabalho para um cliente ou compartilhá-lo com sua equipe.