Criando tabelas dinâmicas (PivotTables)

Descrição

Tabela de resumo (Pivot Table) é uma poderosa ferramenta de cálculo, resumo e análise de dados que permite ver comparações, padrões e tendências em seus dados.

Imagine que você precisa fazer um pequeno relatório analítico para tomada de decisão a partir de uma grande tabela que contém muitas informações diferentes. Suponha que você tenha dados que exibem informações sobre a carteira de crédito do banco, onde são registradas informações detalhadas sobre cada mutuário.

Um exemplo de problema

Vamos supor que a tabela a seguir seja carregada do banco de dados em Excel para analisar a carteira de empréstimos:

Nele, cada linha individual contém informações completas sobre um contrato de crédito:

• nome do mutuário;
• número do contrato;
• data de emissão e data de reembolso do empréstimo;
• tipo de empréstimo;
• montante do empréstimo

Naturalmente, se os gerentes de vendas conhecerem seu negócio e trabalharem duro, todos os dias várias dezenas de linhas serão adicionadas a esta tabela e, ao final de, por exemplo, um ano ou pelo menos um trimestre, o tamanho da tabela se tornará monstruoso.

No entanto, um horror ainda maior causará a necessidade de criar relatórios com base nesses dados. Quantos empréstimos foram emitidos por mês? Qual é o tamanho da carteira de empréstimos em termos de empréstimos por quantidade e valor? Dez principais mutuários? etc.

As respostas para todas as perguntas podem ser obtidas mais facilmente do que você imagina. Cerca de três minutos. Com uma das ferramentas mais incríveis Microsoft Excel — Tabelas de resumo .

Vamos...

Preparação de dados

Considere as seguintes dicas de preparação de dados antes de criar tabelas dinâmicas:

• use dados tabulares limpos para obter melhores resultados;
• coloque os dados em colunas, não em linhas;
• certifique-se de que todas as colunas tenham cabeçalhos com uma linha de legendas exclusivas não vazias para cada coluna. Evite linhas de cabeçalho duplicadas ou células mescladas;
• formatar os dados como "tabela inteligente Excel" (clique em qualquer lugar dos seus dados e selecione "Insira a tabela" ( Insert - Table ) no menu da faixa de opções (ribbon);
• se você tiver dados complexos ou aninhados (como uma tabela cruzada), use Power Query para convertê-los (por exemplo, você pode usar uma macro para cancelar resumo de dados ) para que sejam organizados em colunas com uma única linha de cabeçalho.

Criando uma tabela dinâmica

Selecione toda a nossa tabela de dados e clique no menu Inserir - Tabela Resumo ( Insert - PivotTable ).

Esta caixa de diálogo aparecerá, na qual podemos escolher onde colocar nossa tabela dinâmica e ajustar o intervalo de dados.

Por padrão, o Excel opta por colocar a tabela dinâmica em uma nova planilha (New Worksheet) , mas podemos, se necessário, selecionar manualmente a folha atual ou qualquer outra do livro (Existing Worksheet) . É melhor escolher uma nova planilha para isso - então não há risco de que a tabela de resumo “se sobreponha” à lista original e obteremos vários links cíclicos. aperte o botão OK e passamos para a parte mais interessante – a etapa de construção do nosso relatório.

Trabalhando com o layout

O que você verá depois de pressionar o botão OK chamado de layout (layout) tabela de resumo:

É fácil trabalhar com isso - você precisa arrastar os nomes das colunas (campos) da janela com o mouse Lista de campos da tabela de resumo (PivotTable Field List) na região:

• linhas (Row Labels);
• colunas (Column Labels);
• filtros (Report Filter);
• dados de layout (Values).

A única ressalva é fazer com mais precisão, não erre! Vamos...

No processo de arrastar, a tabela consolidada começará a mudar sua aparência diante de seus olhos, exibindo os dados que você precisa. Depois de cruzar todos os quatro campos que precisamos da lista, você deverá obter um relatório quase pronto. Resta apenas formatá-lo bem.

Formatação da Tabela Dinâmica

Selecione a formatação da tabela de resumo no menu PivotTables Tools - Design :

Mas temos a seguinte aparência:

Não é tão complicado, não é?

Desvantagens

A única desvantagem das tabelas de resumo é a falta de atualização automática (recálculo) quando os dados da lista original são alterados. Para realizar tal cálculo, é necessário clicar na tabela resumo com o botão direito do mouse e selecionar o comando no menu de contexto Atualizar (Refresh) .

Artigos sobre o tema:

• Tabelas não dinâmicas (Unpivot)
• PowerPivot - um aplicativo para expandir os recursos de tabelas dinâmicas