Item calculado em tabela dinâmica Excel

Dashboard de Vendas - Painel de Vendas Excel
Dashboard de Vendas – Painel de Vendas Excel
3 de outubro de 2013
Recibo de aluguel Excel
Recibo de aluguel Excel
25 de outubro de 2013

Objetivo: Demonstrar como realizar cálculos entre itens que constam em uma tabela dinâmica Excel.

Item calculado em tabela dinâmicaEste artigo demonstra como realizar o cálculo com itens em tabelas dinâmicas, tanto em colunas quanto em linhas, realizando cálculos agrupados.

No exemplo temos uma lista de dados de uma empresa que tem algumas filiais e na tabela possui os períodos de faturamento para cada uma delas.

Item calculado em tabela dinâmica 2

Estas filiais estão agrupadas em Regiões, sendo na Região 1 as filiais 1 e 2, na Região 2 as filiais 3 e 4 e na Região 3 as filiais 5, 6 e 7.

É possível na tabela dinâmica utilizando a opção Item calculado criar somas das filiais agrupadas ou ainda verificar o crescimento das vendas entre o mês de janeiro e o mês de outubro.

Vamos realizar primeiro o procedimento de criação dos agrupamentos de regiões.

Na tabela dinâmica criada clique na célula Filial 2 e depois clique no botão Cálculos->Campos, Itens e Conjuntos->Item calculado.

Item calculado em tabela dinâmica 4

Na tela que segue digite no Nome a descrição como Região, na fórmula selecione Filial 1 + Filial 2.

Item calculado em tabela dinâmica 5

Perceba que o campo quando inserido ficará no final da tabela dinâmica, neste caso basta selecionar o campo e arrastar para cima.

Item calculado em tabela dinâmica 6

Repita a operação para criar os campos da Região 2 e Região 3.

Item calculado em tabela dinâmica 7

Selecione agora o campo da coluna 2013/01 e clique novamente em Inserir item calculado conforme demonstrado antes.

Item calculado em tabela dinâmica 8

No nome insira o nome Crescimento e no campo de fórmula insira =1-(‘2013/01’/’2013/10’) selecionando o campo Ano/Mês e nos campos dos Itens digite os mesmos.

Veja que o cálculo realizado é para demonstrar o crescimento entre o mês de janeiro e o mês de outubro.

Agora clique com o botão direito em Opções de tabela dinâmica, selecione a coluna Totais e Filtros e desmarque o total geral de colunas para que não seja realizada a soma de forma duplicada.

Item calculado em tabela dinâmica 9

Clique na tabela dinâmica e em Página Inicial->Formatação Condicional->Nova Regra e selecione Usar uma fórmula para determinar quais células devem ser formatadas.

Digite a função =PROCURAR(“Região”;$E3;1)=1 para que seja identificado se o campo possui Região digitado e assim formatar destacando estes totais no relatório.

Item calculado em tabela dinâmica 10

Na formatação clique em Formatar->Efeitos de preenchimento para que seja destacado.

Na aplicação da fórmula desmarque coloque em Aplica-se a =$E$3:$P$310000.

Item calculado em tabela dinâmica 11

O resultado final é um relatório que é atualizado automaticamente e com agrupamentos diferentes, você pode fazer diversas combinações como também verificar a crescimento entre meses ou ainda, em uma conciliação de dados identificar as diferenças entre as colunas que estão sendo conciliadas. O resultado final é:

Item calculado em tabela dinâmica

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/

17 Comentários

  1. Douglas disse:

    Bom dia Marcos, as imagens não estão carregando.

  2. Esses Firewall podem até ser úteis, mas é uma canseira trabalhar com eles bloqueando de tudo. Robson Eustáquio de Mesquita

  3. Marcos,

    Parabéns pelo site, estou sempre acessando para adquirir novos conhecimentos.

    Eu tentei fazer isso com uma tabela dinâmica que possui uns 20 itens e com nomes extensos e dividir em 2 totais, um total com 3 itens e o outro total com os outros 17. Porém, o total que tem os outros 17 não é possível de colocar pois há um limite de carateres. Tem alguma outra forma de eu somar estes outros 17 itens e formar esse total?

    Um abraço.

    • Marcos Rieper disse:

      Bom dia João Henrique,

      Ás vezes quando o cálculo é complexo você pode incluir uma coluna com o cálculo diretamente na fonte de dados reduzindo desta forma a complexidade ou como no seu caso solucionando o problema de quantidade de caracteres.

      Abraço

      Marcos Rieper

  4. Curso VBA disse:

    Olá Marcos!

    Este curso de VBA vc faz parcelado!

  5. Andre disse:

    Boa tarde.
    Estou com uma dificuldade enorme trabalhando com tabela dinâmica, será que alguém pode me ajudar?
    Tenho um campo chamado “estado” que pode ter três valores: “Recebido”, “Feito” ou “Devolvido”, referentes aos documentos que são recebidos em formulários de Comunicação Interna (CI). Cada CI vem de um local diferente e classificada por tipo de documento. Se eu colocar o campo “quantidade” na tabela ele me da o total de Recebido+Feito+Devolvido, o que eu quero é separado uma coluna para cada situação.
    Exemplo: Na CI 120 recebi 5 documentos, destes foram feitos 3 e devolvidos 2, a coluna quantidade mostra 10…
    Para separar, coloquei o campo Estado como Rótulo de Coluna, ai ele me mostra a quantidade para cada estado… feito isso, quero inserir uma nova coluna que fiz como “item calculado” onde faço a conta recebido-feito-devolvido informando assim quantos ainda faltam para dar tratamento.
    Acontece que quando faço isso, cada CI é replicada para todos os locais, ficando com “0”(zero) nas colunas de estado, assim a CI 120 que veio da filial A, aparece também na filial B e na C.

    • Marcos Rieper disse:

      Boa tarde André,

      Sim, esta é uma característica da própria ferramenta.

      Faça o seguinte. Na sua planilha de dados crie 4 colunas, e coloque uma fórmula que identifique se é Recebido, Feito ou Devolvido e na outra uma fórmula com o total de documentos menos a soma destas três colunas.

      Depois aplique a tabela dinâmica, veja que você terá o que precisa com somente as colunas, sem repetição.

      At.
      Marcos Rieper

  6. Higor disse:

    Olá Marcos Rieper,

    Parabéns pelo artigo, me ajudou muito.

    A propósito, eu gostaria de perguntar uma coisa:

    Na hora de criar um campo calculado com a fórmula =’Filial X’+’Filial Y’+’Filial Z’ como eu faria para calcular de todas as filiais, nesse mesmo passo, o que eu devo colocar na fórmula?

  7. Anderson disse:

    Boa Tarde, gostaria de tirar uma dúvida, os meus crescimentos não estão batendo, o valor que está no arquivo que foi baixado está diferente do arquivo da imagem.
    Ex.:
    Crescimento da Região 3: na imagem está -20,87
    No arquivo está: -105,84
    Sendo que o valor correto seria o que está na imagem.
    O arquivo está somando os valores de crescimento, e quando tento fazer um exemplo também o meu está somando os itens calculados. Como devo fazer? Muito Obrigado e parabéns pela dica.

  8. pedro disse:

    Boa tarde meu caro,
    Gostaria de receber a planilha em meu correio, agradeço se me encaminhasse tabm.
    att,

    Pedro

  9. Aline disse:

    Bom dia, Marcos!

    Preciso de uma ajuda.
    Estou com uma tabela dinâmica, onde, no campo de valor eu tenho o valor real de cada conta (que consta nas colunas), e nas colunas eu tenho os períodos (jan, fev, mar, ….) e o tipo (realizado e orçado)…
    Estou tentando colocar uma coluna com a variação… que a diferença do valor total de 1 período com outro, referente ao orçado e realizado, por exemplo ( o total acumulado do real – total acumulado do orçado)… fiz de todas as formas e não estou conseguindo.

    Como preciso que fique na coluna a variação (x-y) e em outra coluna a variação % (x/y-1).

    Pode me ajudar?

    Muito obrigada!

    Att,
    Aline

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.