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-planilha-HA102840046.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:

• 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 é:

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.

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:

Na caixa de diálogo Configurações do campo de valor você poderá:

• 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.

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:

Os dados já passam a ser apresentados de maneira muito mais organizada pelo Microsoft Excel:

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 direito do mouse em qualquer um dos valores apresentados e escolher a opção Classificar > Classificar do maior para o menor:

Assim você poderá verificar, de fato, quais são os tipos de projeto com maior volume de investimento da sua empresa:

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:

Quando finalizar a organização dos dados, o resultado será:

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:

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:

Assim você chegará ao resultado conforme abaixo:

Observe que, após agrupadas as informações por ano/mês, a Tabela Dinâmica não exibe por padrão a 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:

O resultado:

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.

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 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:

Ações adicionais

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:

Inserindo uma segmentação de dados na Tabela Dinâmica

http://raphaelrsantos.blogspot.com.br/2012/01/segmentacao-de-dados-no-excel-2010.html

Criando campos calculados na Tabela Dinâmica

http://raphaelrsantos.blogspot.com.br/2012/08/criando-campos-calculados-em-tabelas.html

Caso você queira fazer ler este post com mais calma, você poderá fazer o download da versão digital clicando no link abaixo:

https://www.box.com/shared/mvm9rw7m000f0m3kreah

Boa leitura, e que o post tenha sido útil.

Até a próxima!