Como adicionar e usar um campo calculado de tabela dinâmica do Excel

Freqüentemente, depois de criar uma tabela dinâmica, é necessário expandir sua análise e incluir mais dados / cálculos como parte dela.

Se você precisar de um novo ponto de dados que pode ser obtido usando pontos de dados existentes na Tabela Dinâmica, você não precisa voltar e adicioná-lo aos dados de origem. Em vez disso, você pode usar um Campo Calculado da Tabela Dinâmica para fazer isso.

Baixe o conjunto de dados e acompanhe.

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 conforme mostrado abaixo:

A Tabela Dinâmica acima resume os valores de vendas e lucro para os varejistas.

Agora, e se você também quiser saber qual foi a margem de lucro desses varejistas (onde a margem de lucro é "Lucro" dividido por "Vendas").

Há algumas maneiras de fazer isso:

1. Volte para o 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. Depois de fazer isso, você precisa atualizar os dados de origem da Tabela Dinâmica para obter essa nova coluna como parte dela.
   • Embora esse método seja uma possibilidade, você precisaria voltar manualmente para o conjunto de dados e fazer os cálculos. Por exemplo, você pode precisar adicionar outra coluna para calcular a venda média por unidade (Vendas / Quantidade). Novamente, você terá que adicionar essa coluna aos dados de origem e, em seguida, atualizar a tabela dinâmica.
   • Este método também incha sua Tabela Dinâmica conforme você adiciona novos dados a ela.
2. Adicione cálculos fora da Tabela Dinâmica. Isso pode ser uma opção se for improvável que a estrutura da Tabela Dinâmica mude. Mas se você alterar a tabela dinâmica, o cálculo pode não ser atualizado de acordo e pode fornecer resultados incorretos ou erros. Conforme mostrado abaixo, calculei a margem de lucro quando havia varejistas na linha. Mas quando mudei de clientes para regiões, a fórmula apresentou um erro.
3. Usando um campo calculado de tabela dinâmica. Isto é o maneira mais eficiente para usar os dados existentes da Tabela Dinâmica e calcular a métrica desejada. Considere o Campo Calculado como uma coluna virtual que você adicionou usando as colunas existentes da Tabela Dinâmica. Há muitos benefícios em usar um Campo Calculado de Tabela Dinâmica (como veremos em um minuto):
   • Não requer que você lide com fórmulas ou atualize os dados de origem.
   • É escalonável, pois contabilizará automaticamente todos os novos dados que você adicionar à sua Tabela Dinâmica. Depois de adicionar um Calcular Campo, você pode usá-lo como qualquer outro campo em sua Tabela Dinâmica.
   • É fácil de atualizar e gerenciar. Por exemplo, se as métricas forem alteradas ou você precisar alterar o cálculo, poderá fazer isso facilmente na própria Tabela Dinâmica.

Adicionando um Campo Calculado à Tabela Dinâmica

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

Suponha que você tenha uma Tabela Dinâmica conforme mostrado abaixo e deseja calcular a margem de lucro de cada varejista:

Aqui estão as etapas para adicionar um Campo Calculado de Tabela Dinâmica:

• Selecione qualquer célula na Tabela Dinâmica.
• Vá para Ferramentas de Tabela Dinâmica -> Analisar -> Cálculos -> Campos, Itens e Conjuntos.
• No menu suspenso, selecione Campo calculado.
• Na caixa de diálogo Inserir Arquivado Calculado:
  • Dê a ele um nome inserindo-o no campo Nome.
  • No campo Fórmula, crie a fórmula desejada para o campo calculado. Observe que você pode escolher entre os nomes dos campos listados abaixo. Nesse caso, a fórmula é ‘= Lucro / Vendas’. Você pode inserir manualmente os nomes dos campos ou clicar duas vezes no nome do campo listado na caixa Campos.
• Clique em Adicionar e feche a caixa de diálogo.

Assim que você adicionar o Campo Calculado, ele aparecerá como um dos campos na lista Campos da Tabela Dinâmica.

Agora você pode usar este campo calculado como qualquer outro campo da Tabela Dinâmica (observe que você não pode usar o Campo Calculado da Tabela Dinâmica como um filtro de relatório ou segmentação).

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 uma região na área das linhas, você obterá o resultado conforme mostrado abaixo, onde o valor da Margem de Lucro é relatado para varejistas e também para a região.

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

Antes de mostrar um exemplo do uso de uma fórmula avançada para criar um Campo de cálculo 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 um Campo Calculado de Tabela Dinâmica. Isso excluiria muitas fórmulas como VLOOKUP, INDEX, OFFSET e assim por diante. No entanto, você pode usar fórmulas que podem funcionar sem referências (como SUM, IF, COUNT e assim por diante …).
• Você pode usar uma constante na fórmula. Por exemplo, se você deseja saber as vendas projetadas onde está previsto um crescimento de 10%, você pode usar a fórmula = Vendas * 1.1 (onde 1.1 é constante).
• A ordem de precedência é seguida na fórmula que cria o campo calculado. Como prática recomendada, use parênteses para garantir que não seja necessário lembrar a ordem de precedência.

Agora, vamos ver um exemplo do uso de uma fórmula avançada para criar um campo calculado.

Suponha que você tenha o conjunto de dados conforme mostrado abaixo e precise mostrar o valor de vendas previsto na Tabela Dinâmica.

Para o valor previsto, você precisa usar um aumento de vendas de 5% para grandes varejistas (vendas acima de 3 milhões) e um aumento de vendas de 10% para pequenos e médios varejistas (vendas abaixo de 3 milhões).

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

Aqui está como fazer isso:

• Selecione qualquer célula na Tabela Dinâmica.
• Vá para Ferramentas de Tabela Dinâmica -> Analisar -> Cálculos -> Campos, Itens e Conjuntos.
• No menu suspenso, selecione Campo calculado.
• Na caixa de diálogo Inserir Arquivado Calculado:
  • Dê a ele um nome inserindo-o no campo Nome.
  • No campo Fórmula, use a seguinte fórmula: = IF (Região = ”Sul”, Vendas * 1,05, Vendas * 1,1)
• Clique em Adicionar e feche a caixa de diálogo.

Isso adiciona uma nova coluna à tabela dinâmica com o valor da previsão de vendas.

Clique aqui para baixar o conjunto de dados.

Um problema com campos calculados de tabela dinâmica

Campo Calculado é um recurso incrível que realmente aumenta o valor da sua Tabela Dinâmica com cálculos de campo, enquanto mantém tudo escalonável e gerenciável.

No entanto, há um problema com os Campos calculados da tabela dinâmica que você deve conhecer antes de usá-la.

Suponha que eu tenha uma Tabela Dinâmica, conforme mostrado abaixo, onde usei o campo calculado para obter os números de previsão de vendas.

Observe que o subtotal e os totais gerais não estão corretos.

Embora devam adicionar o valor da previsão de vendas individual para cada varejista, na realidade, ele segue a mesma fórmula de campo calculado que criamos.

Portanto, para o Sul Total, embora o valor deva ser 22.824.000, o Sul Total informa erroneamente como 22.287.000. Isso acontece porque ele usa a fórmula 21.225.800 * 1,05 para obter o valor.

Infelizmente, não há como corrigir isso.

A melhor maneira de lidar com isso seria remover subtotais e totais gerais de sua Tabela Dinâmica.

Você também pode passar por algumas soluções alternativas inovadoras que Debra mostrou para lidar com esse problema.

Como modificar ou excluir um campo calculado de 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:

• Selecione qualquer célula na Tabela Dinâmica.
• Vá para Ferramentas de Tabela Dinâmica -> Analisar -> Cálculos -> Campos, Itens e Conjuntos.
• No menu suspenso, selecione Campo calculado.
• No campo Nome, clique na seta suspensa (pequena seta para baixo no final do campo).
• Na lista, selecione o campo calculado que deseja excluir ou modificar.
• Altere a fórmula caso deseje modificá-la ou clique em Excluir caso deseje excluí-la.

Como obter uma lista de todas as fórmulas dos campos calculados?

Se você criar muitos campos Pivot Table Calculated, não se preocupe em acompanhar a fórmula usada em cada um deles.

O Excel permite que você crie rapidamente uma lista de todas as fórmulas usadas na criação de Campos Calculados.

Aqui estão as etapas para obter rapidamente a lista de fórmulas de todos os campos calculados:

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

Assim que você clicar em Listar Fórmulas, o Excel irá inserir automaticamente uma nova planilha que terá os detalhes de todos os campos / itens calculados que você usou na Tabela Dinâmica.

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

Você também pode achar úteis os seguintes tutoriais de tabela dinâmica:

• Preparando dados de origem para tabela dinâmica.
• Usando Slicers na Tabela Dinâmica do Excel: Um Guia para Iniciantes.
• Como agrupar datas em tabelas dinâmicas no Excel.
• Como agrupar números em uma tabela dinâmica no Excel.
• Como filtrar dados em uma tabela dinâmica no Excel.
• Como substituir células em branco por zeros em tabelas dinâmicas do Excel.
• Como aplicar formatação condicional em uma tabela dinâmica no Excel.
• Cache dinâmico no Excel - o que é e como usá-lo da melhor maneira?