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.
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:
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.
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.
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.
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 planilhaMarcos Rieper
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
