Objetivo: Apresentar algumas dicas para aprimorar o uso da tabela dinâmica no Excel.
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 anos e meses, 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.
Abraço
Marcos Rieper
