O recurso de Tabela Dinâmica é um elemento tecnologicamente sofisticado, capaz de nos ajudar a resolver muitos dos problemas do tratamento do excesso de dados e de como converter isso em informação.

É esta capacidade de analisar os dados e transformá-los em informação fiável que nos vai permitir decidir melhor, mas muitas vezes é difícil saber por onde começar este processo de agregar e consolidar dados.

As Tabelas Dinâmicas são uma excelente ferramenta para resumir, analisar, explorar e apresentar os dados. Podemos transformar imensas linhas e colunas de números numa apresentação de dados de fácil leitura e fazer relatórios sobre esses dados. E quando estes dados são estão relacionados entre si, podemos construir uma Tabela Dinâmica a partir de várias.

Com apenas alguns cliques no rato, podemos também “partir” uma tabela de dados em diferentes partes e produzir, praticamente, qualquer tipo de resumo que queiramos.

Sobre as tabelas dinâmicas

Uma tabela dinâmica é essencialmente um relatório de resumo dinâmico gerado a partir de uma base de dados. Por outras palavras, tabelas dinâmicas são tabelas interativas que resumem grandes quantidades de dados numa estrutura com métodos de cálculo definidos.

O banco de dados pode residir numa folha de cálculo (na forma de uma tabela) ou num arquivo de dados externo. Uma tabela dinâmica ajuda, assim, a transformar infinitas linhas e colunas de números numa apresentação significativa de dados.

Anatomia de uma tabela dinâmica

Uma tabela dinâmica é composta por quatro áreas: Valores, Linhas, Colunas e Filtros. Os dados que se coloca nessas áreas definem tanto o uso como a apresentação dos dados na tabela dinâmica. Agora, iremos ver a função de cada uma dessas quatro áreas. A figura abaixo reproduz o ambiente de trabalho de uma tabela dinâmica «à moda antiga».

Área Valores

A área Valores permite calcular e contar os dados de origem. É a grande área retangular abaixo e à direita dos cabeçalhos de coluna e linha. Os campos de dados que arrastamos e largamos aqui são, tipicamente, aqueles que desejamos medir, como a soma dos valores, a contagem das unidades ou a média dos preços.

Área Linhas

Quando arrastamos um campo de dados para a Área de Linhas é exibido o valor exclusivo desse campo no lado esquerdo da tabela dinâmica. Esta área, normalmente, possui, pelo menos, um campo, embora seja possível não ter campos. Os tipos de campos que colocamos aqui, são aqueles que agrupamos e categorizamos, tais como produtos, nomes e locais.

Área Colunas

Esta área contém títulos que se estendem no topo das colunas na tabela dinâmica. A Área Colunas é ideal para criar uma matriz de dados ou mostrar tendências ao longo do tempo.

Área Filtros

Na parte superior da tabela dinâmica, a área Filtro do Relatório é um conjunto opcional de um ou mais controles drop-down (suspensos). Colocar campos de dados nesta área permite alterar as visualizações para toda a tabela dinâmica com base na seleção efetuada. Os tipos de campos de dados que devemos escolher para aqui devem ser aqueles que queremos destacar.

Antes de começar

Os nossos dados devem estar organizados num formato de tabela e não devem existir linhas ou colunas em branco. No nosso exemplo utilizamos uma tabela do Excel.

As tabelas (estáticas) são boa fonte de dados para as tabelas dinâmicas porque quando adicionamos linhas ou colunas a uma tabela, estas podem ser atualizadas a partir do intervalo de origem de dados ou utilizar uma fórmula de intervalo com nome dinâmico.

Os tipos de dados nas colunas devem ser os mesmos. Por exemplo, não deve misturar datas e texto na mesma coluna.

As tabelas dinâmicas funcionam como um instantâneo dos nossos dados, denominado cache, para que os dados reais não sejam alterados.

Criar uma Tabela Dinâmica

Se temos pouca experiência com Tabelas Dinâmicas ou não soubermos como começar, podemos usar um recurso de ajuda do Excel - Tabela Dinâmica Recomendada.

Quando usamos este recurso, o Excel determina um layout específico, combinando os dados com as áreas mais adequadas da Tabela Dinâmica.

Vamos, então, partir da tabela sugerida acima e criar uma tabela dinâmica manualmente.

1. Clique em qualquer célula dentro dos dados de origem (a tabela que você usará para alimentar a tabela dinâmica).
2. Inserir > Tabelas Dinâmicas.
3. O Excel apresentará a caixa de diálogo Criar Tabela – podemos escolher a tabela de dados ou o local onde queremos que fique o relatório da tabela dinâmica.
4. Se quiser optar pelas opções apresentadas, clique em OK. Temos agora, um relatório vazio da tabela dinâmica numa nova folha.

Campos da Tabela Dinâmica

Ao lado da tabela dinâmica vazia, vemos a caixa de Campos da Tabela Dinâmica.

Podemos adicionar os campos que queremos, arrastando e soltando os nomes dos campos para uma das quatro áreas que falamos anteriormente.

Agora, antes de começarmos a construir a tabela, que é como quem diz, a colocar ou arrastar os campos para as várias áreas, devemos fazer duas perguntas:

1. "O que quero medir?"
2. "Como quero ver a tabela?"

A resposta a essas perguntas ajuda a perceber para onde devem ir os campos.

Respondendo à primeira questão, queremos medir o valor faturado por cliente. Temos, então, de selecionar os campos "valor fatura" e "cliente".

E como queremos ver isso?

Queremos que os clientes fiquem no lado esquerdo do relatório e o valor da faturação seja calculado ao lado de cada cliente. Temos de colocar o campo na área Linhas e o campo na área Valores. Mas, o Excel 2016 poder fazer isso por nós. Bastar selecionar os campos e automaticamente são colocados nas referidas áreas. Porquê?

Porque a colocação de uma verificação ao lado de qualquer campo que não seja numérico (texto ou data) coloca esse campo automaticamente na área Linhas da tabela dinâmica. A colocação de uma verificação ao lado de qualquer campo que seja numérico, coloca automaticamente esse campo na área Valores da tabela dinâmica.

Facilmente analisamos o valor de faturação por cliente. O nosso exemplo é muito básico, mas pense que isto se aplicava a uma tabela com centenas de linhas e dezenas de colunas. Quanto tempo de trabalho seria desperdiçado se não tivéssemos este recurso disponível?

Modificando a tabela dinâmica

Agora, que temos uma tabela básica, podemos acrescentar ou rearranjar os campos (dados para análise) que desejarmos, para obtermos a tabela pretendida.

Por exemplo, queremos medir a performance dos vendedores no primeiro trimestre e ver quantidade vendida por cliente.

 Esta apresentação não está de acordo com o que queriamos? Prefiro que o campo "vendedor" fique na área colunas. Só tenho de arrastar esse campo da área Linhas e colocá-lo na área Colunas.

Isto implica uma reestruturação instantânea da tabela dinâmica, conforme vemos na figura abaixo.

Uma situação que pode acontecer é ter de criar relatórios por determinados segmentos (região, mercado, produto, etc). Em vez de criarmos tabelas dinâmicas separadas para cada segmento possível, podemos aproveitar as tabelas dinâmicas para a criar várias visualizações dos mesmos dados. Por exemplo, podemos fazê-lo criando um filtro de "produto" e "cliente" na nossa tabela dinâmica.

Eis o gráfico que resultou da construção e das alterações efetuadas na nossa tabela.

Criar uma tabela dinâmica é um processo interativo. Devemos experimentar vários “layouts” até se encontrar aquele que satisfaz a nossa mensagem.

Renomeando os campos

Podemos observar que cada campo da tabela dinâmica tem um nome. Os campos nas áreas de linha, coluna e filtro herdam os nomes das etiquetas de dados como os dados de origem. Por exemplo, os campos na área de Valores recebem um nome, como Soma de Quantidade.

Se quisermos o nome de Vendas Totais em vez deste nome pouco atraente, podemos fazê-lo.

Atenção, se colocarmos o mesmo nome da etiqueta de dados que especificamos nos dados de origem, recebemos um erro.

No nosso exemplo, tentamos renomear o campo de Valor de quantidade como Quantidade, e recebemos uma mensagem de erro, conforme figura abaixo.

Para contornar isto, basta adicionar um espaço ao final de qualquer nome de campo. O Excel considera Quantidade (seguido de um espaço) diferente do valor de quantidade. Desta forma, podemos usar o nome que desejamos e ninguém notará nenhuma diferença.

Formatando números

Os números numa tabela dinâmica podem ser formatados para atender às nossas necessidades (como moeda, percentagem ou número). Por exemplo, podemos controlar a formatação numérica de um campo usando a caixa de diálogo Definições do Campo de Valor.

Vejamos como:

1. Clique com o botão direito do mouse em qualquer valor dentro do campo de destino.
   Por exemplo, se você deseja alterar o formato dos valores no campo Quantidade, clique com o botão direito do mouse em qualquer valor nesse campo. Também podemos ir logo para a caixa Formato Numérico.
2. Para exibir a caixa de diálogo, selecionar Definições de Campo de Valor.
3. Para exibir a caixa de diálogo Formatar Células, clique em Formato Numérico.
4. Indicamos o formato do número desejado, tal como faríamos normalmente na folha de cálculo.
5. Clique em OK.

Depois de definir um novo formato para um campo, a formatação aplicada mantém-se mesmo se você atualizarmos ou reorganizarmos a tabela dinâmica.

Resumir Valores 

Por predefinição, os campos da tabela dinâmica que são colocados na área Valores serão apresentados como uma SOMA. Se o Excel interpretar os nossos dados como texto, será apresentado como CONTAGEM. É por isso que é tão importante garantir que não misturamos tipos de dados em campos de valores. Podemos alterar o cálculo predefinido na opção Definições do Campo de Valor.

Quando criamos a tabela dinâmica, o Excel, por padrão, resume os nossos dados contando ou somando os itens. Em vez de Soma ou Contagem, podemos querer escolher outras funções, como Média, Mínimo, Máximo, e assim por diante. No total, são 11 opções que estão disponíveis, incluindo:

• Soma: adiciona todos os dados numéricos. Depois de colocar o campo na área Valores, podemos alterar a função de resumo para Soma, e quaisquer valores em branco ou não numéricos são alterados para 0 na Tabela Dinâmica para que eles possam ser somados.
• Contagem: conta todos os itens de dados dentro de um determinado campo, incluindo células numéricas, de texto e formatadas na data.
• Média: calcula uma média para os valores.
• Máximo: exibe o maior valor nos itens de dados de destino.
• Mínimo: exibe o menor valor nos itens de dados de destino.
• Produto: multiplica todos os itens de dados alvo em conjunto.
• Contar Números: conta apenas as células numéricas nos itens de dados de destino.
• Desvio Padrão e Desvio Padrão P: calcula o desvio padrão para os itens de dados de destino. Usamos Desvio Padrão se a nossa fonte de dados contiver uma amostra da população e usamos Desvio Padrão P se a nossa fonte de dados contiver a população completa.
• Var e VarP: calcula a variância estatística para os itens de dados alvo. Se os nossos dados contiverem apenas uma amostra da população completa usamos Var, para estimar a variância. Se os nossos dados contiverem uma população completa, usamos VarP.

Mostrar valores como...

Podemos também formatar a forma como os valores se apresentam nas células. Em vez de utilizarmos um cálculo para resumir os dados, também podemos apresentá-los como uma percentagem de um campo. Eis como:

1. Clicar com o botão direito do mouse em qualquer valor dentro do campo de destino.
2. Selecionar a caixa de diálogo Definições do Campo de Valor.
3. Selecione o tipo de cálculo que deseja usar na lista de cálculos.
4. Clique em OK.

Sabia que uma única célula em branco faz com que o Excel conte em vez de somar? Se todas as células de uma coluna contiverem dados numéricos, o Excel escolhe Soma. Se apenas uma célula estiver em branco ou contiver texto, o Excel escolhe Contagem. Certifique-se de prestar atenção aos campos que você coloca na área Valores da tabela dinâmica. Se o nome do campo começar com Contagem, o Excel conta os itens no campo em vez de somar os valores.

As tabelas dinâmicas foram apresentadas no Excel 97. Muitos usuários ignoram este recurso porque acham que é muito complicado. O recurso de tabela dinâmica foi melhorado significativamente no Excel 2007 e Excel 2010.

No Excel 2016, criar e trabalhar com tabelas dinâmicas é mais fácil do que nunca.