Raphael Santos
http://raphaelrsantos.blogspot.com
@rapharsantos
Tabela Dinâmica – Dicas de Produtividade
Olá pessoal,
Em algumas oportunidades tenho escrito aqui no blog que sou fã incondicional da utilização de Tabelas Dinâmicas – e
quem usa regularmente este recurso sabe que isso não é nenhum exagero.
Pois bem, neste post vou procurar listar ótimos recursos oferecidos pela ferramenta, que podem aumentar
significativamente a produtividade dos usuários na consolidação, organização, extração e análise de informações – e,
porque não, tornar mais agradável a visualização final dos dados.
Antes de começar
Alguns pontos importantes:
Versão do Excel: para escrever este post estou usando a versão 2013 do Microsoft Excel. Entretanto, todas as
funcionalidades exploradas também podem ser reproduzidas nas versões 2010 e 2007. Se algum recurso não estiver
disponível em uma das versões, sinalizarei para não haver dúvidas.
Público-alvo: o público-alvo deste post são aqueles usuários que já utilizam o recurso de Tabela Dinâmica. Se você está
começando a usar (ou ainda não conhece) essa funcionalidade, sugiro dar uma olhada no ótimo tutorial da Microsoft
(http://office.microsoft.com/pt-br/excel-help/criar-uma-tabela-dinamica-para-analisar-dados-da-planilhaHA102840046.aspx), para poder aproveitar melhor as dicas deste post.
Isto posto, vamos ao que interessa.
A planilha
Suponhamos que você trabalha em uma empresa e, dentre suas atribuições, você precisa analisar e gerar relatórios do
portfólio de projetos da sua companhia. Você possui uma planilha com a relação de projetos, contendo as seguintes
informações:
o
o
o
o
o
o
o
o
o
Id do Projeto
Tipo de Projeto
Status
Vice Presidência
Origem Comercial
Impacto Estratégico
Conclusão Prevista
Duração Prevista
Valor do Projeto
Iremos utilizar este conjunto de informações como referência para este trabalho. Caso você queira experimentar estes
passos na prática, você poderá baixar a planilha no link abaixo:
https://www.box.com/shared/8tge1ctjel9i3a8rd8oo
Aplicando formatação aos dados
Pra começar, vamos supor que sua necessidade inicial
é descobrir o montante total dos projetos (valores
monetários), agrupado por Tipo de Projeto. Criada a
Tabela Dinâmica, o resultado é:
Na caixa de diálogo Configurações do campo de valor
você poderá:
o
o
O ponto aqui é que os valores da soma não estão
organizados com separador de milhar, casas decimais
ou símbolo de moeda. O que muitas pessoas acabam
fazendo para atenuar o problema é a seleção dos dados
apresentados pela Tabela Dinâmica, aplicando em
seguida a formatação necessária. A desvantagem desse
método é que, caso a Tabela Dinâmica seja alterada e
a relação de dados seja maior do que a atual, não existe
a garantia de que as informações adicionais sejam elas
também formatadas automaticamente.
Trocar o label do campo para um nome mais
agradável, no item Campo Personalizado;
Clicar em Formato do Número para formatar o
resultado da Tabela Dinâmica de acordo com
suas preferências.
Para resolver este impasse, você poderá clicar com o
botão direito do mouse em qualquer célula da Tabela
Dinâmica (campo de valor) e escolher a opção
Configurações do Campo de Valor:
Neste exemplo, vou inserir o nome Total do
Investimento no campo personalizado. Já a formatação
do número vou configurar como Contábil, para
apresentar a separação de milhar e as devidas casas
decimais, em conjunto com o símbolo de moeda:
direito do mouse em qualquer um dos valores
apresentados e escolher a opção Classificar 
Classificar do maior para o menor:
Os dados já passam a ser apresentados de maneira
muito mais organizada pelo Microsoft Excel:
Assim você poderá verificar, de fato, quais são os tipos
de projeto com maior volume de investimento da sua
empresa:
Classificando e filtrando dados
Olhando para esta Tabela Dinâmica é possível verificar
que a classificação dos Tipos de Projeto se dá via ordem
alfabética. Entretanto, esta ordem não representa a
classificação da grandeza dos montantes por cada um
dos tipos. Caso seja necessário efetuar a classificação
por ordem de montante, você poderá clicar com o botão
Outra interessante opção disponibilidade pelo Microsoft
Excel é a possibilidade de filtrar os dados, para exibir
apenas uma parcela das informações disponíveis.
Suponha que você está analisando uma planilha de
vendas, a qual contém informações das compras
efetuadas pelos seus clientes – e são mais de 500
clientes, por exemplo. Você gostaria de verificar quais
são os 10 clientes que mais compraram da sua
empresa, para gerar o relatório à equipe de vendas.
Para isso, basta clicar com o botão direito na Tabela
Dinâmica (em alguma informação que esteja disponível
nos campos de Linha) e escolher a opção Filtrar  10
Primeiros:
Então você poderá escolher se o filtro será aplicado para
os 10 primeiros ou outra opção que se encaixe na sua
necessidade:
Visualizando os dados em percentuais
Outro recurso muito eficiente disponibilizado pela
Tabela Dinâmica, e que a grande maioria dos usuários
desconhece, é a possibilidade de modificar o modo de
exibição dos dados. Olhando para a tabela atual,
sabemos que o valor total de projetos de Consultoria é
de R$ 1.830.150. Porém, e se for necessário descobrir
qual a representatividade dos projetos de consultoria
em relação ao montante total? Na maioria dos casos, os
usuários acabam criando uma tabela paralela e
efetuando a conta manualmente. A grande
desvantagem dessa abordagem é que, caso o layout da
Tabela Dinâmica seja alterado, a tabela manual não
terá mais serventia.
Para modificar a maneira como as informações são
exibidas pela Tabela Dinâmica, clique com o botão
direito sobre a tabela e escolha a opção Configurações
do Campo de Valor. Perceba que a caixa de diálogo
apresentada possui duas abas – Resumir Valores por e
Mostrar Valores como. Clique na segunda aba e, na
opção Mostrar Valores como, selecione a opção % do
Total de Colunas:
As informações da Tabela Dinâmica passarão a ser
exibidas da seguinte maneira:
Se você acrescentar informações ao layout da sua
Tabela Dinâmica, você poderá continuar modificando a
exibição dos dados de acordo com sua preferência,
através da escolha de outras opções do Campo de Valor
(% do Total Geral, % do Total de Linhas e etc.). Para
voltar à exibição padrão, escolha a opção Sem Cálculo.
Modificando o tipo de cálculo
Por padrão, sempre que uma nova Tabela Dinâmica é
inserida, o Microsoft Excel resume os dados utilizando a
função de SOMA (considerando que os dados inseridos
no campo Valores sejam numéricos). Porém, nem
sempre o usuário que está criando a Tabela Dinâmica
precisa que os dados sejam agrupados através da
função SOMA. Na tabela que estamos utilizando, é
possível verificar o valor total do investimento de acordo
com o Tipo de projeto. Mas e se, ao invés de visualizar
o montante em termos monetários, fosse necessário
visualizar o número de projetos por tipo? E se, ao invés
do valor total, fosse necessário visualizar o valor médio
de investimento por tipo de projeto?
Para responder a essas dúvidas, você poderá clicar com
o botão direito do mouse sobre um dos valores
apresentados na Tabela Dinâmica e escolher a opção
Configurações do Campo de valor. Em seguida, na caixa
de diálogo disponível, você poderá escolher qual o
método de cálculo deseja aplicar à sua Tabela Dinâmica
(fique atento à mudança no rótulo do campo):
Após modificar escolher o método de cálculo e modificar
o Nome Personalizado, clique em Formato do Número e
substitua o formato contábil (caso o esteja utilizando)
pelo formato número. Clique em OK para aplicar o
critério à Tabela Dinâmica e visualizar o resultado:
Organizando a posição dos dados
Se você estiver utilizando esta planilha para executar os
passos deste guia, substitua o item Tipo de Projeto por
Impacto Estratégico, nos rótulos de linha. O resultado da
alteração pode ser visto no exemplo abaixo:
Notou algo de errado? Em termos de organização das
informações, a classificação dos campos não está legal.
Temos o impacto Alto seguido do Baixo, e então Médio
e Muito Alto. Logicamente falando, a classificação dos
dados está incorreta – isso porque a Tabela Dinâmica
classifica os dados de acordo com sua ordem alfabética.
Para organizar os dados de maneira lógica, você poderá
clicar sobre o rótulo de um dos itens com o botão direito
do mouse e escolher a opção Mover. Em seguida, de
acordo com sua necessidade, você poderá mover a
informação para cima ou para baixo:
Agrupando datas
Imagine o seguinte cenário: você está trabalhando na
sua empresa com um arquivo similar a este que
estamos usando como exemplo. O board executivo
solicita um relatório com quantidade de projetos a
serem encerrados, mês a mês. Como você sabe que o
seu relatório possui uma coluna chamada Conclusão
Prevista, você adiciona esta coluna ao campo de Linhas
da Tabela Dinâmica. Aqui o resultado:
Quando finalizar a organização dos dados, o resultado
será:
E qual é o problema agora? Como o objetivo principal da
Tabela Dinâmica é agrupar informações que sejam
similares, o intervalo de dados a ser exibido é muito
grande, pois a Tabela Dinâmica só irá agrupar os
projetos que sejam encerrados exatamente no mesmo
dia.
Para resolver este impasse e organizar os dados numa
escala mensal/anual, clique com o botão direito do
mouse em uma das datas da Tabela Dinâmica e escolha
a opção Agrupar. Na caixa de diálogo apresentada, você
poderá especificar quais os critérios serão utilizados
para consolidar os dados:
quantidade de projetos realizados em cada ano. Para
passar a exibir a informação, clique com o botão direito
do mouse sobre um dos anos e escolha a opção
Subtotal Anos:
Assim você chegará ao resultado conforme abaixo:
O resultado:
Observe que, após agrupadas as informações por
ano/mês, a Tabela Dinâmica não exibe por padrão a
determinado pelo usuário. Para inserir uma Linha do
Tempo à sua Tabela Dinâmica, clique em qualquer
campo da tabela. Em seguida, no menu dinâmico
Ferramentas de Tabela Dinâmica, procure a guia
Analisar. Nessa guia, clique na opção Inserir Linha do
Tempo:
Selecione o campo Conclusão Prevista e clique OK. A
partir de agora, você poderá filtrar as informações por
Anos, Trimestres ou Anos especificamente:
Inserindo uma linha do tempo
Observação: este recurso está disponível somente para
a versão 2013 do Microsoft Excel.
Vamos supor que você tenha aplicado o recurso de
agrupamento de datas na sua Tabela Dinâmica, mas
que não tenha mais a necessidade de visualizar o
resultado deste agrupamento. Como a partir de agora
você precisará gerar um relatório dos projetos por
Origem Comercial, você substitui os itens Anos e Meses
pelo campo Origem Comercial.
Ações adicionais
Mesmo tendo feito essa substituição dos campos que
devem ser exibidos, a Tabela Dinâmica mantém os
campos Anos e Meses (representado pelo campo
Conclusão Prevista) na lista de campos disponíveis. E é
aí que entra o recurso Linha do Tempo. Este recurso
permite, de maneira simplificada, a aplicação de filtros
na Tabela Dinâmica, de acordo com um período a ser
Inserindo uma segmentação de dados na Tabela
Dinâmica
http://raphaelrsantos.blogspot.com.br/2012/01/segm
entacao-de-dados-no-excel-2010.html
Além de todas as dicas destacadas neste documento,
existem outras ações que podem dinamizar o uso da
Tabela Dinâmica. Algumas dessas dicas já foram
discutidos em outros posts, e para não ficar repetitivo,
não irei incluí-las neste guia. Caso queira saber mais,
você poderá visualizar os respectivos posts:
Criando campos calculados na Tabela Dinâmica
http://raphaelrsantos.blogspot.com.br/2012/08/crian
do-campos-calculados-em-tabelas.html