Campos Calculados em Tabelas Dinâmicas no Excel

Campos Calculados em Tabelas Dinâmicas Excel

A tabela dinâmica é um dos grandes recursos do Excel, porém muitas vezes não utilizado na sua totalidade. Neste artigo você verá como criar campos calculados em tabelas dinâmicas.

É possível realizar cálculos diretamente nas tabelas dinâmicas, trabalhando então com os seus próprios campos sumarizados, inclusive com funções do Excel, veja como abaixo.

Realizar Cálculos em Tabelas Dinâmicas Excel

Imagine que precise realizar alguns cálculos em uma tabela dinâmica sem mexer na base de dados, como fazer?

A resposta pode ser usar um recurso chamado Campo calculado, vejamos um exemplo.

João é um analista tributário e precisa realizar uma prévia dos impostos com base no faturamento.

Para isso precisa calcular o PIS, COFINS e o ICMS com base no faturamento e calcular a diferença entre o faturamento e estes impostos.

A base de dados que será utilizada para análise será a seguinte, na qual temos os campos simplificados de faturamento, Série, Nota fiscal, valor e Região.

 Campo calculado em tabela dinâmica 10

Para isso primeiro é necessário criar uma tabela dinâmica, mesmo que simplificada.

No caso incluímos a Região em Linhas e o Faturamento em Valores:

Campo calculado em tabela dinâmica 6

Os demais campos que incluiremos serão todos calculados.

Inserir Campos Calculados em Tabela Dinâmicas

Para inserir os campos calculados em tabelas dinâmicas você deve clicar sobre um dos valores da tabela dinâmica e no menu Análise de Tabela Dinâmica em Campos, Itens e Conjuntos e clique em Campo calculado.

Campo calculado em tabela dinâmica 0

No formulário que é aberto temos a seguinte estrutura:

No campo nome você deve digitar o nome do campo calculado, ele não deve se repetir ou ser igual a um dos campos da lista Campos.

Em fórmula é aonde será realizado o cálculo.

Então pode usar determinadas funções do Excel como SE entre outras, mas não pode referenciar células, mas pode usar valores fixos. Veja como.

Campos calculados PIS e COFINS

Dois dos cálculos que deveriam ser inclusos na tabela dinâmica são os impostos de PIS e COFINS.

O cálculo para PIS é Valor do faturamento * 0,03. Clique então no campo Valor e em Inserir campo ou ainda dê um duplo clique sobre ele, e em seguida digite *0,03 e clique no botão Adicionar.

  • PIS: =Valor*0,03

O cálculo para COFINS é Valor do faturamento * 0,0165. Clique então no campo Valor e em Inserir campo ou ainda dê um duplo clique sobre ele, e em seguida digite *0,0165 e clique no botão Adicionar.

  • COFINS: =Valor*0,0165

Incluir Campo Calculado de ICMS

Outro campo que é necessário incluir é o de ICMS.

Para este campo João precisa realizar dois cálculos diferentes.

Caso seja da região Leste o valor deverá ser multiplicado por 17%, senão o valor do faturamento deverá ser multiplicado por 10%.

Para isso digite no campo Nome ICMS e coloque a fórmula =SE(Região = “Leste”; Valor*0,17; Valor*0,1)

Na fórmula basta selecionar em Campos e selecionar Região e adicionar na fórmula.

Veja que na fórmula foi utilizada a função SE verificando se a região é Leste e se for é feito o cálculo do valor do faturamento * 17%, senão o valor de faturamento multiplicado por 10%.

Como pode ver, há funções que podem ser utilizadas, não é possível apenas referenciar células, apenas campos da tabela dinâmica e valores fixos.

Realizar Cálculos com Vários Campos

Também é possível realizar cálculos com vários campos ao mesmo tempo.

No caso lembre que sempre os campos referenciados são da mesma linha.

O analista precisa de mais um campo que é um cálculo do faturamento menos os impostos.

Crie então na fórmula a seguinte fórmula = Valor-ICMS-IPI-COFINS selecionando os campos.

Campo calculado em tabela dinâmica 5

Veja que estes campos que foram utilizados na fórmula temos também os campos calculados, além dos outros campos.

Colocando os Campos Calculados na Tabela Dinâmica

Agora com os campos calculados basta selecionar os campos da tabela dinâmica e colocar em Valores.

Estes campos calculados funcionarão da mesma forma que que qualquer campo de tabela dinâmica.

Campo calculado em tabela dinâmica 8

Como resultado então você terá os campos de Faturamento, ICMS, PIS, COFINS e Faturamentos-Impostos.

Download da Planilha de Exemplo

Para realizar o download deste exemplo basta clicar no botão abaixo e preencher seu e-mail.

Baixe a planilha

Marcos Rieper