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.
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:
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
Criando campos
calculados na Tabela Dinâmica
Caso você queira fazer ler este post com mais calma,
você poderá fazer o download da versão digital clicando no link abaixo:
Boa leitura, e que o
post tenha sido útil.
Até a próxima!
