7 Dicas para Aprimorar o Uso de Tabelas Dinâmicas – Excel

Objetivo: Apresentar algumas dicas para aprimorar o uso da tabela dinâmica no Excel.

 

 

Lomadee, uma nova espécie na web. A maior plataforma de afiliados da América Latina.

 

1 – Criando Fórmulas – Campo calculado

Muitas vezes precisamos criar fórmulas para tabelas dinâmicas, como verificar o percentual atingido da meta de vendas.

Para isso clique na tabela dinâmica e na guia Opções clique no botão Cálculos, selecione a opção Campos, Itens e Conjuntos.

Na tela que segue você deve dar um nome para o campo calculado e criar a fórmula para o mesmo. Esta fórmula obrigatoriamente deve retornar um valor.

No exemplo abaixo que utilizei na planilha do download foi feito o seguinte cálculo:

Após isto basta formatar e você terá o seguinte resultado:

2 – Agrupando Datas

É possível realizar o agrupamento de datas em Segundos, Minutos, Horas, Dias, Meses, Trimestres e Anos. Evitando desta forma, que você tenha que criar colunas específicas na lista com estes campos.

Para realizarmos este agrupamento, você deve incluir o campo data na sua planilha conforme abaixo, indiferente se no agrupamento das linhas ou das colunas.

Agora clique com o botão direito sobre uma das datas e clique na opção Agrupar, na tela que segue selecione um dos agrupamentos, no caso usei anosmeses, porém você pode agrupar por vários campos de uma vez.

3 – Formatação

Você pode formatar a tabela dinâmica com as opções de design que surgem na aba Design ao você clicar na tabela dinâmica, ali você pode selecionar uma das formatações automáticas.

4 – Campo Acumulado

É possível criar campos acumulados de uma das colunas, assim como criar uma coluna de percentual do total como veremos.

Clique na tabela dinâmica com o botão direito e selecione a opção Mostrar Lista de Campos, desmarque todos os campos de valores exceto o campo Venda.

Inclua novamente um campo Venda, clique com o botão direito sobre ele, em configurações de campo selecione Soma, clique na aba Mostrar Valores Como e selecione Soma Acumulada em, na opção Campo Base deixe marcado a opção Vendedor. Com isto você terá um valor cumulativo do faturamento dos vendedores nos períodos.

Outra opção muito interessante de configuração do campo é criar um campo percentual do valor com relação a linha em que se encontra.

Inclua um novo campo Venda na opção Valores da tabela dinâmica, repetindo os primeiros passos desta dica.

Clique na guia Resumir dados por e selecione a opção Soma, depois clique na guia Mostrar Valores Como e selecione a opção % do Total de Linhas Pais. Fazendo isso você terá o percentual do total que cada vendedor faturou no período.

Abaixo o resultado esperado destas tarefas:

5 – Formatação Condicional

Da mesma forma que em qualquer célula do Excel, na tabela dinâmica é possível utilizar a Formatação Condicional, isso pode melhorar a apresentação e por conseguinte a leitura dos dados da planilha.

Selecione por exemplo as células I4:I23 e clique na guia Página Inicial em Formatação Condicional e em Conjuntos de Ícones selecione uma das várias opções, no caso escolhemos flâmulas conforme abaixo.

Lembrando que é possível fazer qualquer tipo de formatação condicional na tabela dinâmica. Para mais informações sobre formatação condicional veja o artigo http://guiadoexcel.com.br/formatacao-condicional-excel-2007-e-2010.

6 – Tabela Dinâmica Modelo Antigo

Algumas vezes a tabela dinâmica ficaria melhor se fosse no modelo antigo, é possível alterar esta configuração.

Clique na tabela dinâmica com o botão direito e em Opções de Tabela Dinâmica, selecione a guia Exibição e marque a opção Layout clássico de tabela dinâmica (permite arrastar campos na grade).

Abaixo o resultado esperado.

7 – Conciliação de dados usando tabela dinâmica

Agora uma lição importante.

A melhor forma de você saber se um valor consta em duas lista de dados é utilizando tabelas dinâmicas, e não utilizando PROCV.

O PROCV só é útil para conciliação de poucos dados, quando temos muitos dados a planilha torna-se inviável.

Veja como fazer a conciliação de uma forma extremamente rápida e eficiente neste artigo aonde você pode tanto ver o vídeo, quanto ir passo-a-passo: http://guiadoexcel.com.br/conciliacao-de-dados-com-excel-video.

GUT PPT

Abraço

Marcos Rieper

 

Lomadee, uma nova espécie na web. A maior plataforma de afiliados da América Latina.

 

excel vba

SISTEMA DE GESTÃO CONTA AZUL

O Excel é uma plataforma de trabalho muito aberta para servir ao gerenciamento de dados de uma empresa, dado a complexidade e a integração entre as diversas áreas que a compõe. Mas é a melhor para analisar suas informações.

Com a intenção de atender as micro e pequenas empresas a Conta Azul criou um software de gestão aonde você tudo o que você precisa para gerir a sua empresa: Estoque, Vendas, Financeiro e ainda emite Nota Fiscal Eletrônica, tudo isso em um sistema simples de trabalhar, que você acessa pela internet, não há a necessidade de comprar servidores e preocupar-se com backup e atendimento através de 0800.

O preço da ferramenta também chama a atenção, sendo um preço mais do que justo para se ter o controle dos dados e a geração de informações para a análise do seu negócio.

ContaAzul é um software de gestão financeira e fiscal para micro e pequenas empresas. Ele funciona 100% em plataforma web, não necessitando nenhuma instalação ou atualização. Você pode acessar de qualquer lugar e não tem necessidade de comprar nova licença caso troque de computador.

  O Guia do Excel recomenda o Conta Azul no gerenciamento de sua empresa.
Use QR-Code to get this permaking using your Smartphone. QR Code for 7 Dicas para Aprimorar o Uso de Tabelas Dinâmicas – Excel

11 Comentários

  1. Walter Bernal disse:

    Muito interessante essas dicas. Nunca usei os campos calculados e com formulas dentro do excel. Mas com certeza, começarei a usar…

    Muito bom Marquito!

    Abraço

  2. As tabelas dinâmicas são incríveis se bem utilizadas. Os campos calculados são muito úteis mesmo. Belo post Marcos.

  3. Victor Hugo disse:

    Marcos,

    Concordo que a melhor forma não é o procv mas usando índice e corresp vc tem um rápido resultado dos dados distintos também!

    []s

    VH

  4. Diego disse:

    Marcos,
    Estou utilizando uma tabela dinâmica, porém tenho duas colunas em “valores”, onde esta sendo feito a contagem de produtos que contém nestas colunas.
    Gostaria de acrescentar esta formula que ensinou e fazer dividindo uma coluna pela outro para ter o percentual de aproveitamento entre as duas colunas.
    Fiz a formula e meu deu como resultado como não divisivel.
    Gostaria de saber se pode me ajudar com relação este problema.
    Obrigado

  5. Tiago disse:

    Parabéns pelo site. Trabalho no are “ADM” e suas dicas com o Excel são fantásticas.
    O que mais me chamou a atenção neste site e que vocês postam o arquivo (planilha)
    E como se faz (exercício para que possamos aprender). Isso e um diferencial muito importante. E que faz desse site sobressair com qualidade em comparação a outros, que vemos na web.

    Abraço aos moderadores.

    Atenciosamente;

    Tiago Ferraz.

  6. Diego disse:

    Bom dia Marcos,
    Realizei a alteração com esta formula, mas ele não efetua o calculo que desejo e sim somente aceita o 0% como resposta.
    Será que por ele ter uma informação devido a uma contagem da tabela de dado, o excel não esta entendendo?
    Obrigado
    Diego

  7. Diego disse:

    Boa noite Marcos,
    Verifiquei o problema que continha na minha planilha, pois estava efetuando a contagem de “x” que estava marcado no banco de dados, efetuei a troca para o numero “1″ e agora funcionou perfeitamente.
    Muito obrigado, seu site realmente é muito diferenciado.

    Abraço

  8. Guilherme Alves disse:

    Parabéns!!!
    Muito boa as dicas!!

  9. Dorival Sepulveda Villatoro disse:

    Prezado Marcos Rieper, boa tarde!

    QUE PRESTAÇÃO DE SERVIÇO VOLUNTÁRIO E FANTÁSTICO QUE VOCÊ TRAZ GRACIOSAMENTE PARA TODO O PÚBLICO!!!
    PARABENS!
    Meus incontáveis agradecimentos e que Deus lhe ilumine sempre.
    Um forte abraço e meu muito, muito obrigado mesmo!

Deixe o seu comentário


 
%d blogueiros gostam disto: