Como criar campo calculado em tabela dinâmica no Excel passo-a-passo com download gratuito da planilha de exemplo.
Download Planilha Campos Calculados em Tabelas Dinâmicas
Para realizar o download da planilha com exemplo de uso de campos calculados em tabelas dinâmicas no Excel clique no botão abaixo e preencha o formulário.
1. O que são os campo calculado em tabela dinâmica
Em tabelas dinâmicas do Excel os campos calculados são colunas personalizadas através de fórmulas que podemos criar para realizar cálculos dentro da própria tabela dinâmica, utilizando os campos já disponíveis na fonte de dados.
Desta forma, poderemos ter diversos campos adicionais não presentes nos dados originais, mas que ficarão disponíveis para a criação das tabelas dinâmicas.
Esses campos permitem realizar operações aritméticas, como somas, multiplicações, ou divisões, sem a necessidade de alterar a fonte de dados original. As fórmulas inseridas nos campos calculados podem usar parte das funções do Excel, especialmente aquelas que não usam argumentos que requerem intervalos de células de planilhas.
2. Quando usar campo calculado em tabela dinâmica
Devemos usar campos calculados nas ocasiões em que precisamos adicionar cálculos personalizados, como margens de lucro, percentuais ou outras métricas, e a fonte de dados de origem não contém esses dados diretamente. A grande vantagem é que poderemos evitar criar colunas auxiliares nos dados de origem.
Um exemplo disso é a criação de um campo calculado na tabela dinâmica para exibir o preço médio, baseado nas colunas de Receita (R$) e de Volume (quantificado em kg, por exemplo). A divisão da coluna da Receita pela coluna do Volume já resultará no preço médio. Neste nosso caso, não há nos dados uma coluna que destaque os preços médios (e nem se faz necessária).
A principal recomendação para uso de campos calculados ocorre para dados que não estão adicionados ao modelo de dados – Power Pivot.
3. Restrições
No modelo de dados do Power Pivot os campos calculados são substituídos por medidas, cálculos expressivamente mais complexos e abrangentes do que os campos calculados, e que usam DAX – Data Analysis Expressions. Para dados adicionados ao modelo de dados, os campos calculados ficam indisponíveis.
As fórmulas usadas nos campos calculados não poderão usar todas as funções do Excel porque o contexto a ser considerado é apenas o contexto da fonte de dados. PROCV, por exemplo, não será possível de ser utilizada. Como os campos calculados são criados com base nos valores agregados (como soma ou contagem) dos campos existentes não é possível usar referências a células específicas.
4. Como criar campo calculado na tabela dinâmica do Excel
4.1. Cenário de dados
Tomemos como base a tabela dinâmica a seguir, que resume dados de vendas, detalhando por Regionais e Estados e totalizando as vendas em valor total e quantidade total de caixas. Desejamos calcular o preço médio de cada caixa vendida.
4.2. Etapas para criação de campos calculados em tabela dinâmica
Clicando sobre a tabela dinâmica (em qualquer uma de suas células), surgirá a guia contextual Análise de Tabela Dinâmica. Devemos acessar no grupo Cálculos a opção Campos, Itens e Conjuntos e em seguida Campo Calculado.
A janela a seguir será apresentada.
Nesta janela é que efetivamente poderemos criar, editar e excluir campos calculados. No item “Nome” devemos definir o nome do campo calculado em criação (este nome deve ser diferente de todos os nomes de colunas da fonte de dados e de outros campos calculados já existentes).
A imagem a seguir mostra a inserção do nome desejado para nosso campo calculado.
No item “Fórmula” devemos inserir a fórmula para o cálculo de campo. Aqui devemos usar as colunas disponíveis nas fontes de dados e podemos aproveitar ainda para usar campos calculados já criados e disponíveis. Em nosso caso, a fórmula será a divisão da coluna de Valor (que presumidamente terá os valores somados) pela coluna de Qtde cx (que presumidamente terá os valores somados).
Para inserir as colunas desejadas na fórmula podemos selecionar a coluna no item “Campos”, na parte inferior da janela, e em seguida clicar no botão “Inserir campo”, destacado em verde na imagem anterior. Ao finalizar, podemos confirmar em “Ok” ou ainda “Adicionar”.
Conforme a imagem seguinte, temos agora o campo calculado de Preço Médio na tabela dinâmica informando os preços médios de cada Regional e Estado. Note também que na lista de colunas disponíveis para a edição da tabela dinâmica o campo calculado criado é o último exibido.
