quinta-feira, 2 de agosto de 2012

Criando campos calculados em Tabelas Dinâmicas

Como já falado anteriormente neste post, os relatórios de Tabela Dinâmica são, sem a menor sombra de dúvidas, uma das mais fascinantes e importantes funcionalidades do Microsoft Excel. Só quem já teve que calcular grandes quantidades de informações de maneira manual sabe o poder que é criar e manipular uma Tabela Dinâmica, não é verdade? Neste post irei abordar uma funcionalidade que é oferecida pela Tabela Dinâmica, mas que acaba sendo pouco utilizada pelos seus usuários em geral: a possibilidade de se criar campos calculados.

Imagine que você possua uma planilha com a relação simples dos impostos vencidos de duas empresas que compõem o grupo para o qual você trabalha:
Você deseja criar uma Tabela Dinâmica que consolide os dados, comparando o Valor Original dos impostos com o Saldo Devedor. Coisa simples:
Entretanto, após configurar a Tabela Dinâmica, você percebe que também será necessário apresentar uma informação complementar no relatório, que é a diferença entre o Saldo Devedor e o Valor Original, pois assim terá condições de saber qual o valor efetivo de juros que serão pagos em virtude no atraso no pagamento.

Para resolver esta questão, o caminho natural adotado pela maioria das pessoas seria a criação de uma nova coluna na planilha original, responsável por calcular a diferença entre os dois campos. Em seguida, seria necessário apenas atualizar a fonte de dados e inserir o novo campo na Tabela Dinâmica. Contudo, todavia, entretanto, porém.... você pode criar, na própria Tabela Dinâmica, um campo calculado que faça essa conta. Siga os passos: 
  1. Clique na Tabela Dinâmica e, no menu dinâmico Ferramentas de Tabela Dinâmica, clique em Opções
  2. Na seção Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Campo Calculado 
O Excel irá exibir a caixa de diálogo Inserir campo calculado. Renomeie o campo Nome para Juros. Em seguida, clique no campo Fórmula. Selecione abaixo a opção Saldo Devedor e clique em Inserir Campo (você pode também dar um duplo-clique no campo): 
Insira o sinal de subtração e dê duplo clique no item Valor Original: 
Clique em Adicionar e então OK. Nesse momento, o Excel irá acrescentar o campo calculado à Tabela Dinâmica:
Muito bacana essa funcionalidade, não é? A título de informação, a criação de campos calculados em Tabelas Dinâmicas também pode ser realizada com fontes de dados externas (Microsoft Access, Microsoft SQL, Arquivos no formato TXT e etc). Isso é extremamente útil, uma vez que, num cenário como este, não seria tão fácil (e acessível) manipular os dados criando fórmulas na base original.

E se quisesse agora saber qual foi o percentual de juros pagos em relação ao valor original do imposto? Para isso, bastaria criar um novo campo calculado, dividindo o valor do campo Juros pelo campo Valor Original:
O resultado:
Após terminar o trabalho com os campos calculados, você pode visualizar as regras e condições utilizadas em cada campo calculado. Para isso, selecione a Tabela Dinâmica e clique em Opções > Campos Itens e Conjuntos > Listar Fórmulas:
Se quiser fazer alguns testes com a criação de campos calculados, você poderá baixar o arquivo de exemplo deste post clicando aqui. Você também pode fazer o download do arquivo digital deste post em PDF. Para isso, clique aqui.
 
Até a próxima!

38 comentários:

  1. Muito bom. Conteúdo útil com ótima didática. Parabéns!

    ResponderExcluir
  2. Muito bom o conteúdo, porem para mim o item campo calculado não está habilitado. O que será que pode estar ocorrendo?

    ResponderExcluir
    Respostas
    1. Olá Rodrigo. Estranho isso acontecer... por tentativa e erro, qual a versão do Excel que você está utilizando?

      Um dos possíveis problemas é o origem dos dados. Se as informações da planilha que você utilizou como referência para gerar a Tabela Dinâmica estiverem formatadas como texto, os campos calculados vão ficar inacessíveis. Sugiro dar uma olhada nisso.

      Um abraço

      Excluir
  3. Raphael, te mandei um e-mail com uma dúvida. Pode abrir que não é virus. Abraços

    Vitor

    ResponderExcluir
  4. Muito bom! Finalmente aprendi isso! Obrigado por disponibilizar a informação e parabéns pela didática, excelente! ;)

    ResponderExcluir
  5. Muito bom o conteúdo, porem para mim o item campo calculado não está habilitado. O que será que pode estar ocorrendo? Uso excel 2013 e estou usando a sua planilha anexa. Obrigada !!

    ResponderExcluir
    Respostas
    1. Olá Elaine,

      Que estranho. Normalmente a opção de criação de campos calculados é desabilitada quando os dados são oriundos de um banco de dados externos que o Excel não consegue realizar os cálculos, o que não é o seu caso.

      O que você acha de me escrever com um print de tela do seu Excel? Talvez dessa maneira consiga te ajudar -- raphael@raphael-santos.net

      Excluir
  6. Bom dia Raphael,
    tenho uma planilha com várias fórmulas, mas precisava adicionar novas colunas e quando faço isso, todas os campos calculados desaparecem, então inseri na antepenúltima coluna mas mesmo assim perdi alguns campos, gostaria de saber se tem como travar o campo calculado para ele não mudar quando altero fonte de dados para acrescentar as novas colunas?

    Obrigada.

    Bruna

    ResponderExcluir
    Respostas
    1. Olá Bruna,

      Eu não entendi muito bem a sua dúvida. Vamos ver se estou correto:

      - Você possui uma tabela dinâmica, originária de uma fonte de dados do Excel
      - Nessa tabela dinâmica, foram criados alguns campos calculados
      - Se você inserir uma nova coluna na planilha que alimenta a tabela dinâmica, os campos calculados perdem a referência e deixam de funcionar

      É isso?

      Excluir
  7. Depois de muito tempo a tentar perceber como se fazia, finalmente consegui!
    Obrigado pelo conteúdo. Informação muito clara ;)

    ResponderExcluir
  8. boa noite
    como posso calcular quando tenho um relacionamento entre dias tabelas, mas a minha dificuldade é criar um calculo entre dois campos em tabelas diferentes.
    obrigado!
    at
    Fabio

    ResponderExcluir
    Respostas
    1. Olá Fábio,

      Não tenho 100% de certeza, mas acredito que isso não é possível. O ideal seria, em primeiro lugar, estabelecer uma conexão entre as tabelas fora da Tabela Dinâmica, talvez usando um mecanismo de manipulação de dados como o Power Pivot.

      Dê uma olhada nesse post para mais informações:

      http://www.raphael-santos.net/2014/12/power-pivot-estabelecendo-relacoes.html

      Espero que seja útil. Um abraço!

      Excluir
  9. Como Excluir definitivamente (não remover) um campo calculado da TB?

    ResponderExcluir
    Respostas
    1. Olá Celso,

      Basta seguir os mesmos passos que foram usados para criar o campo calculado:

      1) Clique na Tabela Dinâmica e, no menu dinâmico Ferramentas de Tabela Dinâmica, clique em Opções

      2) Na seção Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Campo Calculado

      3) Selecione o Campo Calculado desejado e clique em Excluir

      Um abraço!

      Excluir
  10. Excelente explanação, esta de parabéns esta é uma das maiores duvidas dos alunos.

    ResponderExcluir
  11. Olá Raphael,
    Primeiramente parabéns pela didática. Suas recomendações sempre são úteis.
    Estou com um desafio e gostaria de saber se pode me ajudar.
    Em uma tabela dinâmica, tenho 3 referências de valores = Receita, Despesa e Investimentos. Ao gerar a dinâmica, o "total geral" envolve os três valores, porém gostaria de incluir um "SubTotal" somente com o cálculo (RECEITA - DESPESA) e deixar os investimentos a mostra somente no total geral, é possível?

    ResponderExcluir
    Respostas
    1. Olá Douglas,

      Como a sua tabela dinâmica está organizada (quais são e onde estão distribuídos cada um dos elementos na TD)? Gostaria de entender isso um pouco melhor para tentar te ajudar.

      Um abraço!

      Excluir
    2. Olá Raphael,
      A disposição dos campos está da seguinte forma:

      - Competência por mês nas colunas;
      - Tipo do custo nas linhas (Receita, Despesa e Investimentos);
      - Valor dos lançamentos em Valores (os valores de despesas e dos investimentos estão negativos na base, os outros valores de receita estão positivos).

      Gostaria que a TD apresentasse:
      SUBTOTAL = (RECEITA - DESPESA)
      TOTAL GERAL = (RECEITA - DESPESA - INVESTIMENTOS)

      Excluir
    3. Olá Douglas,

      Entendi. Cara, fiz uma simulação aqui e não consegui chegar nesse resultado. Não tenho certeza se é possível realizar essa quebra dos valores apenas para o subtotal, de acordo com o tipo de despesa.

      Vou tentar outras opções,caso consiga chegar em um resultado satisfatório eu respondo por aqui.

      Boa sorte!

      Excluir
    4. Bom dia Raphael,

      A solução temporária foi criar duas TD's na mesma aba fazendo essa separação dos totais. Mas fica aí o desafio, qualquer novidade ficarei muito grato!

      Continuarei acompanhando a página e mais uma vez parabéns pelos métodos didáticos.

      Abs

      Excluir
  12. Olá Raphael!

    Ao salvar e fechar o excel.
    quando abro o arquivo novamente, e atualizo a tabela dinâmica...
    o campo calculado dá um erro, que perde a fórmula.
    Você sabe me dizer o por quê?

    ResponderExcluir
    Respostas
    1. Olá Karen -

      É um comportamento inesperado. O Excel não deveria apresentar nenhum problema uma vez que o campo foi criado com sucesso.

      É apresentado algum erro de cálculo na fórmula ou o campo simplesmente desaparece?

      Excluir
  13. Olá Raphael! Td bem? Eu criei um Item Calculado da diferença entre valores que estão dentro da coluna de data, e depois criei outro Item Calculado com o % dessa diferença, ocorre que no total geral o % fica somado e não calculando o % dos valores totais.. nossa, não entendi o q eu fiz de errado. Se puder me dar uma luz, agradeço

    ResponderExcluir
    Respostas
    1. Olá!

      Qual o tipo de data que você possui na coluna que serve como referência para os novos campos calculados criados? Também ajudaria bastante se você pudesse compartilhar as fórmulas aplicadas em cada um dos campos criados.

      Obrigado!
      Raphael

      Excluir
  14. Fiz uma simulação para aplicar o aprendizado e está dando erro.

    Estou tentando de todos os modos entender a origem do erro no campo calculado que criei na tabela dinâmica.

    Dividi o campo: 'Total de compromissos' por 'Quant de empregados'. Vejam que o valor dos divisores é diferente de ZERO e mesmo assim retorna o erro (#DIV/0!).

    Quant de empregados Total de compromissos Média-índice
    22 8 #DIV/0!
    34 67 #DIV/0!
    44 144 #DIV/0!
    39 89 #DIV/0!
    32 94 #DIV/0!
    18 37 #DIV/0!
    42 96 #DIV/0!

    Não sei se faz diferença, mas o valor da coluna 'Quant de empregados' é resultado de uma contagem e o valor da coluna Total de compromissos' é resultado de uma soma.

    Poderiam ajudar?

    ResponderExcluir
    Respostas
    1. Olá Eloy -

      Alguma chance de você compartilhar o arquivo? Dessa maneira seria mais fácil tentar entender qual a origem do problema.

      Se for possível, envie nesse email: raphael@raphael-santos.net

      Um abraço!

      Excluir
  15. Muito bom! Obrigado Rafael ;)
    Abraço,
    Wellington

    ResponderExcluir
  16. Boa tarde. Tenho uma outra necessidade, calcular representatividade de um campo sobre o total dele. A1 = Venda Janeiro | A10 = Venda total. Em uma planilha simples preciso de A1/A10. Como trabalho essa divisão no campo calculado?

    ResponderExcluir
    Respostas
    1. Olá Diogo,

      Não tenho certeza se conseguimos obter este cálculo através de um campo personalizado. Entretanto, você já testou clicar na Tabela Dinâmica com o botão direito do mouse e escolher a opção 'Mostrar Valores Como' > '% do Total de Colunas'?

      Talvez você consiga obter o resultado esperado apenas modificando a configuração de exibição da Tabela Dinâmica.

      Por favor, me diga se esta opção oferece alguma ajuda.

      Um abraço!

      Excluir
  17. Raphael, boa tarde,

    Muito obrigado pela ajuda, e parabéns pelo site.

    Segui sua sugestão, obtive os valores em %.

    A melhor solução para exibir os dois resultados é duplicar o valor na base? Preciso de uma coluna com % do lado do valor.

    Grato,
    Diogo Alesandro

    ResponderExcluir
    Respostas
    1. Fala Diogo,

      Muito bom que tenha funcionado. E você já respondeu a sua pergunta :-D

      De fato, basta duplicar os valores para que você demonstre um coluna com os valores totais e outra com os valores percentuais.

      Um abraço!

      Excluir
  18. Olá Raphael Santos. Na busca de aprimorar o conhecimento no excel encontrei seu material e vou apenas "repetir" o que já falaram. Boa didática, dedicado, parabéns.

    Com o uso do campo calculado esbarrei em uma dúvida. Vou exemplificar a baixo e depois digo qual.

    Mês Receita Dif. mês Anterior Saldo Acumulado
    Jan 5.000
    Fev 4.500 -500 -500
    Mar 7.000 2.500 2.000
    Abr 8.500 1.500 3.500
    Mai 7.000 -1.500 2.000

    Eu consegui encontrar os valores referentes a diferença do mês anterior, só não consigo encontrar a soma dos valores acumulados dessa diferença. A única soma acumulada que consigo é dos valores do campo receita ou outro campo que foi criado automaticamente pela TD. Tentei usar um campo calculado para somar a diferença mas não consegui...

    Se possível, gostaria de sua ajuda.

    ResponderExcluir
    Respostas
    1. Olá Inaldo, tudo bem? Obrigado pela visita e pelo comentário.

      Eu não consegui entender muito bem a sua dúvida. Será que vc consegue me mandar um email explicando com maiores detalhes a sua necessidades e, se possível, uma cópia da planilha?

      Obrigado e um forte abraço!

      Excluir
    2. Olá Raphael, diante mão muito obrigado.
      Comigo está tudo bem sim e espero que com você também.
      Os valores do exemplo não ficaram legal, mas ficará mais fácil com o envio da planilha. Para qual e-mail posso enviar?
      Obrigado.

      Excluir
    3. Manda para: raphael@raphael-santos.net

      Excluir