domingo, 14 de outubro de 2012

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.


Para identificar diferenças entre listas de dados, eu trabalho de duas formas básicas, sem envolver VBA. Uma utilizando a fórmula PROCV e outra utilizando tabelas dinâmicas.
Ambas as formas de conciliação funcionam, porém, quando utilizamos a fórmula PROCV para análise de listas que possuem muitos dados, como por exemplo mais de 100.000 linhas o processador irá fazer um trabalho que pode ser muito pesado podendo demorar muito tempo e até travar o computador.




Por esse motivo recomendo a forma de análise de diferenças que será demonstrada agora.
  • O primeiro passo é identificar os dados que poderão ser utilizados como chave, ou seja, que serão coincidentes, caso existam, nas listas. Estas informações poderiam ser o código do cliente, do pedido, da nota fiscal, etc.
  • Em listas de notas fiscais estas informações poderiam ser a série, a nota e o emissor, no nosso caso iremos utilizar apenas a série e a nota fiscal.
Criação da Chave Utilizando Concatenação
Criação da Chave Utilizando Concatenação
  • No nosso caso como disse a chave é formada pela série e o número, para isso concatene estas informações separando com um traço usando a fórmula: =A2&”-”&B2.
  • Arraste a fórmula para toda a lista.
  • Repita esta operação também para a outra lista de dados.
Analisando os dados
  • Com as chaves prontos devemos agora buscar as diferenças, que serão as notas não encontradas em uma lista ou outra, e as diferenças de valor.
  • Abra uma nova planilha e clique em Inserir->Tabela Dinâmica, selecione os dados das colunas de A á D da Lista 1 e clique em OK.
  • Arraste o campo Chave para a área Rótulo de Linha.
  • Arraste o campo Valor para a área Valores, este campo será o sempre o campo aonde você deseja encontrar a diferença.
  • Na tabela dinâmica criada clique sobre um valor da coluna Contagem de Valor com o botão direito, clique no campo Configurações de Campo de Valor e altere para Soma.
  • Na coluna C digite no título Lista e abaixo digite 1 e arraste até a última linha como na figura.
Lista de dados
Lista de dados
  • Repita as operações acima para criar a Lista 2, conforme a figura.
Lista 2 de dados
Lista 2 de dados - Clique para ampliar
Criando Uma Lista Única e Analisando os Dados
  • Crie uma nova planilha
  • Selecione os dados das lsitas de dados criadas para análise e copie e cole especial Somente Valor nesta nova planilha, desta forma os dados não serão mais tabelas dinâmicas.
  • Agora coloque os dados das listas 1 e 2 uma abaixo da outra, assim você terá uma lista única com todos os dados para a análise.
  • Clique em uma nova célula e clique em Inserir->Tabela Dinâmica, selecione os dados da última lista criada.
Base para análise dos dados
Base para análise dos dados
  • Agora arraste os campos Rótulo de Linha para a área Rótulo de Linha, o campo Soma de Valor para a área Valores e o campo Lista para a área Rótulo de Colunas, conforme a figura.
Campos
Campos
  • Desta forma você terá como resultado a lista de dados da figura abaixo.
Tabela para análise
Tabela para análise
  • Copie a tabela criada para uma nova planilha colando somente valores.
  • Exclua as colunas D e E e a linha 1.
Nova Lista
Nova Lista - Clique para Ampliar
  • Agora digite na célula D2 a palavra Diferença.
  • Na célula D3 digite =B3-C3 e arraste até a última linha. O resultado desta conta deverá dar sempre 0, caso seja diferente, você localizou a diferença de valor ou a nota fiscal que falta e em qual lista.
  • Selecione os dados das colunas e organize pelo campo Diferença, os extremos mostrarão as notas faltantes e as diferenças de valores conforme a figura:
Diferenças Encontradas
Diferenças Encontradas
Está pronta a análise dos dados com as diferenças encontradas e as notas que não constam nas listas.

Nenhum comentário:

Postar um comentário

Visualizações do blog

Seguidores

About

Ads 468x60px

Blogger templates