Trabalhando com tabelas dinâmicas no Microsoft Excel

Tabelas dinâmicas são um dos recursos mais poderosos do Microsoft Excel. Eles permitem que grandes quantidades de dados sejam analisadas e resumidas em apenas alguns cliques do mouse. Neste artigo, exploramos Tabelas Dinâmicas, entendemos o que são e aprendemos como criá-las e personalizá-las.

Nota: Este artigo foi escrito usando o Excel 2010 (Beta). O conceito de uma tabela dinâmica mudou pouco ao longo dos anos, mas o método de criar um mudou em quase todas as iterações do Excel. Se você estiver usando uma versão do Excel que não seja 2010, espere telas diferentes das que você vê neste artigo.

Um pouco de história

Nos primeiros dias dos programas de planilhas, o Lotus 1-2-3 dominava o poleiro. Seu domínio era tão completo que as pessoas achavam que era uma perda de tempo para a Microsoft se preocupar em desenvolver seu próprio software de planilha eletrônica (Excel) para competir com a Lotus. Avance para 2010, e o domínio do Excel no mercado de planilhas é maior do que o da Lotus, enquanto o número de usuários que ainda executam o Lotus 1-2-3 está se aproximando de zero. Como isso aconteceu? O que causou uma reversão tão dramática de fortunas?

Os analistas do setor resumem isso em dois fatores: primeiro, a Lotus decidiu que essa nova e sofisticada plataforma GUI, chamada “Windows”, era uma moda passageira que nunca decolaria. Eles se recusaram a criar uma versão para Windows do Lotus 1-2-3 (por alguns anos, de qualquer forma), prevendo que sua versão DOS do software era tudo que alguém precisaria. A Microsoft, naturalmente, desenvolveu o Excel exclusivamente para o Windows. Em segundo lugar, a Microsoft desenvolveu um recurso para o Excel que a Lotus não forneceu no 1-2-3, Tabelas Dinâmicas. O recurso Tabelas Dinâmicas, exclusivo do Excel, foi considerado tão incrivelmente útil que as pessoas estavam dispostas a aprender todo um novo pacote de software (Excel) em vez de ficar com um programa (1-2-3) que não o tinha. Esse recurso, juntamente com o equívoco do sucesso do Windows, foi o death-knell para o Lotus 1-2-3 e o início do sucesso do Microsoft Excel..

Noções básicas sobre tabelas dinâmicas

Então, o que é uma Tabela Dinâmica, exatamente?

Simplificando, uma Tabela Dinâmica é um resumo de alguns dados, criado para permitir uma análise fácil dos dados. Mas, ao contrário de um resumo criado manualmente, as Tabelas Dinâmicas do Excel são interativas. Depois de criar um, você poderá alterá-lo facilmente se ele não oferecer as informações exatas sobre os dados que você esperava. Em alguns cliques, o resumo pode ser "girado" - girado de tal forma que os cabeçalhos das colunas se tornem cabeçalhos de linha e vice-versa. Há muito mais que pode ser feito também. Em vez de tentar descrever todos os recursos das Tabelas Dinâmicas, vamos simplesmente demonstrá-las…

Os dados que você analisa usando uma Tabela Dinâmica não podem ser qualquer dados - tem que ser cru dados, anteriormente não processados (não-verificados) - geralmente uma lista de algum tipo. Um exemplo disso pode ser a lista de transações de vendas em uma empresa nos últimos seis meses.

Examine os dados mostrados abaixo:

Observe que isso é não dados não tratados. Na verdade, já é um resumo de algum tipo. Na célula B3 podemos ver US $ 30.000, que aparentemente é o total de vendas da James Cook para o mês de janeiro. Então, onde estão os dados brutos? Como chegamos ao valor de US $ 30.000? Onde está a lista original de transações de vendas da qual esse valor foi gerado? É claro que, em algum lugar, alguém deve ter se dado ao trabalho de agrupar todas as transações de vendas dos últimos seis meses no resumo que vemos acima. Quanto tempo você acha que isso aconteceu? Uma hora? Dez?

Muito provavelmente sim. Você vê, a planilha acima é realmente não uma tabela dinâmica. Ele foi criado manualmente a partir de dados brutos armazenados em outro local e, na verdade, levou algumas horas para ser compilado. No entanto, é exatamente o tipo de resumo que poderia ser criado usando Tabelas Dinâmicas, caso em que levaria apenas alguns segundos. Vamos descobrir como…

Se fôssemos rastrear a lista original de transações de vendas, poderia ser algo como isto:

Você pode se surpreender ao saber que, usando o recurso de tabela dinâmica do Excel, podemos criar um resumo de vendas mensal semelhante ao acima em alguns segundos, com apenas alguns cliques do mouse. Nós podemos fazer isso - e muito mais!

Como criar uma tabela dinâmica

Primeiro, verifique se você possui alguns dados brutos em uma planilha no Excel. Uma lista de transações financeiras é típica, mas pode ser uma lista de praticamente qualquer coisa: detalhes de contato do funcionário, sua coleção de CDs ou números de consumo de combustível da frota de carros da sua empresa..

Então, começamos o Excel… e nós carregamos essa lista…

Depois de abrir a lista no Excel, estamos prontos para começar a criar a Tabela Dinâmica.

Clique em uma única célula na lista:

Então, do Inserir guia, clique no Tabela Dinâmica ícone:

o Criar tabela dinâmica caixa aparece, fazendo duas perguntas: quais dados deve basear sua nova tabela dinâmica e onde deve ser criado? Como já clicamos em uma célula da lista (na etapa acima), toda a lista em torno dessa célula já está selecionada para nós (US $ 1: US $ 88 no Pagamentos folha, neste exemplo). Observe que podemos selecionar uma lista em qualquer outra região de qualquer outra planilha, ou mesmo em alguma fonte de dados externa, como uma tabela de banco de dados do Access ou até mesmo uma tabela de banco de dados do MS-SQL Server. Também precisamos selecionar se queremos que nossa nova Tabela Dinâmica seja criada em um Novo planilha, ou em um existir 1. Neste exemplo, vamos selecionar um Novo 1:

A nova planilha é criada para nós e uma tabela dinâmica em branco é criada nessa planilha:

Outra caixa também aparece: Lista de campos de tabela dinâmica. Esta lista de campos será mostrada sempre que clicarmos em qualquer célula dentro da Tabela Dinâmica (acima):

A lista de campos na parte superior da caixa é, na verdade, a coleção de títulos de coluna da planilha original de dados brutos. As quatro caixas em branco na parte inferior da tela nos permitem escolher o modo como gostaríamos que nossa Tabela Dinâmica resumisse os dados brutos. Até agora, não há nada nessas caixas, portanto, a tabela dinâmica está em branco. Tudo o que precisamos fazer é arrastar os campos para baixo da lista acima e soltá-los nas caixas inferiores. Uma Tabela Dinâmica é então criada automaticamente para corresponder às nossas instruções. Se errarmos, precisamos apenas arrastar os campos de volta para onde eles vieram e / ou arrastar Novo campos para baixo para substituí-los.

o Valores caixa é sem dúvida o mais importante dos quatro. O campo que é arrastado para esta caixa representa os dados que precisam ser resumidos de alguma forma (somando, calculando a média, encontrando o máximo, mínimo, etc). Quase sempre numérico dados. Um candidato perfeito para esta caixa em nossos dados de amostra é o campo / coluna "Valor". Vamos arrastar esse campo para o Valores caixa:

Observe que (a) o campo "Valor" na lista de campos está marcado e "Soma do valor" foi adicionado ao campo Valores caixa, indicando que a coluna montante foi somada.

Se examinarmos a Tabela Dinâmica propriamente dita, realmente encontraremos a soma de todos os valores de "Valor" da planilha de dados brutos:

Criamos nossa primeira tabela dinâmica! Útil, mas não particularmente impressionante. É provável que precisemos de um pouco mais de insight sobre nossos dados do que.

Referindo-se aos dados de nossa amostra, precisamos identificar um ou mais cabeçalhos de coluna que poderíamos usar para dividir esse total. Por exemplo, podemos decidir que gostaríamos de ver um resumo de nossos dados em que temos um cabeçalho de linha para cada um dos diferentes vendedores da nossa empresa e um total para cada um. Para conseguir isso, tudo o que precisamos fazer é arrastar o campo "Vendedor" para o Rótulos de linha caixa:

Agora, finalmente, as coisas começam a ficar interessantes! Nossa Tabela Dinâmica começa a tomar forma… .

Com alguns cliques, criamos uma tabela que levaria muito tempo para ser feita manualmente.

Então, o que mais podemos fazer? Bem, em um sentido, nossa Tabela Dinâmica está completa. Criamos um resumo útil de nossos dados de origem. O material importante já está aprendido! Para o restante do artigo, examinaremos algumas maneiras pelas quais Tabelas Dinâmicas mais complexas podem ser criadas e como essas Tabelas Dinâmicas podem ser personalizadas.

Primeiro, podemos criar um dois-tabela dimensional. Vamos fazer isso usando o "Método de pagamento" como título da coluna. Basta arrastar o título "Método de pagamento" para o Rótulos da coluna caixa:

Que se parece com isso:

Começando a ficar muito legal!

Vamos fazer disso três-tabela dimensional. Como uma mesa assim poderia parecer? Bem vamos ver…

Arraste a coluna / título "Pacote" para o Filtro de Relatório caixa:

Observe onde acaba ... .

Isso nos permite filtrar nosso relatório com base no "pacote de férias" que estava sendo comprado. Por exemplo, podemos ver a divisão do vendedor vs o método de pagamento para todos pacotes, ou, com alguns cliques, altere-o para mostrar o mesmo detalhamento do pacote “Sunseekers”:

E assim, se você pensar sobre isso da maneira certa, nossa tabela dinâmica é agora tridimensional. Vamos continuar personalizando ...

Se acontecer, digamos, que só queremos ver cheque e cartão de crédito transações (ou seja, sem transações em dinheiro), então podemos desmarcar o item "Caixa" dos títulos das colunas. Clique na seta suspensa ao lado de Rótulos da coluna, e desmarque "Dinheiro":

Vamos ver o que parece ... Como você pode ver, "Cash" desapareceu.

Formatação

Este é obviamente um sistema muito poderoso, mas até agora os resultados parecem muito simples e chatos. Para começar, os números que estamos somando não se parecem com valores em dólares - simplesmente números antigos. Vamos corrigir isso.

Uma tentação pode ser fazer o que estamos acostumados a fazer em tais circunstâncias e simplesmente selecionar a tabela inteira (ou a planilha inteira) e usar os botões de formatação de números padrão na barra de ferramentas para concluir a formatação. O problema com essa abordagem é que, se você alterar a estrutura da Tabela Dinâmica no futuro (o que é 99% provável), esses formatos numéricos serão perdidos. Precisamos de um caminho que os torne (semi-) permanente.

Primeiro, localizamos a entrada "Soma do valor" no campo Valores caixa e clique nele. Um menu aparece. Nós selecionamos Configurações de campo de valor… no menu:

o Configurações de campo de valor caixa aparece.

Clique no Formato numérico botão, e o padrão Caixa Formatar Células aparece:

De Categoria lista, selecione (diga) Contabilidade, e solte o número de casas decimais para 0. Clique em Está bem algumas vezes para voltar para a tabela dinâmica…

Como você pode ver, os números foram formatados corretamente como valores em dólar.

Enquanto estamos no assunto de formatação, vamos formatar toda a Tabela Dinâmica. Há algumas maneiras de fazer isto. Vamos usar um simples ...

Clique no Ferramentas de Mesa Dinâmica / Design aba:

Em seguida, solte a seta no canto inferior direito do Estilos de Tabela Dinâmica lista para ver uma vasta coleção de estilos internos:

Escolha qualquer um que apele e observe o resultado na sua Tabela Dinâmica:

Outras opções

Podemos trabalhar com datas também. Agora, geralmente, há muitas, muitas datas em uma lista de transações, como a que começamos. Mas o Excel oferece a opção de agrupar itens de dados por dia, semana, mês, ano, etc. Vamos ver como isso é feito.

Primeiro, vamos remover a coluna "Método de pagamento" do Rótulos da coluna caixa (basta arrastá-lo de volta para a lista de campos) e substituí-lo pela coluna "Data de Reserva":

Como você pode ver, isso torna nossa Tabela Dinâmica instantaneamente inútil, fornecendo uma coluna para cada data em que ocorreu uma transação - uma tabela muito ampla!

Para corrigir isso, clique com o botão direito em qualquer data e selecione Grupo… a partir do menu de contexto:

A caixa de agrupamento é exibida. Nós selecionamos Meses e clique em OK:

Voila! UMA Muito de tabela mais útil:

(Incidentalmente, essa tabela é praticamente idêntica à mostrada no começo deste artigo - o resumo de vendas original criado manualmente).

Outra coisa interessante a saber é que você pode ter mais de um conjunto de cabeçalhos de linha (ou cabeçalhos de coluna):

… O que parece com isso… .

Você pode fazer uma coisa semelhante com cabeçalhos de coluna (ou até mesmo reportar filtros).

Mantendo as coisas simples novamente, vamos ver como traçar média valores, em vez de valores somados.

Primeiro, clique em "Soma do valor" e selecione Configurações de campo de valor… no menu de contexto que aparece:

No Resumir campo de valor por lista no Configurações de campo de valor caixa, selecione Média:

Enquanto estamos aqui, vamos mudar o Nome personalizado, de "Average of Amount" para algo um pouco mais conciso. Digite algo como "Avg":

Clique Está bem, e ver o que parece. Observe que todos os valores mudam de totais somados para médias e o título da tabela (célula superior esquerda) foi alterado para "Avg":

Se quisermos, podemos ter somas, médias e contagens (contagens = quantas vendas existem) todas na mesma Tabela Dinâmica!

Aqui estão os passos para obter algo parecido no local (a partir de uma tabela dinâmica em branco):

Arraste "Vendedor" para o Rótulos da coluna
Arraste o campo "Valor" para o Valores caixa três vezes
Para o primeiro campo "Valor", altere seu nome personalizado para "Total" e seu formato numérico para Contabilidade (0 casas decimais)
Para o segundo campo "Valor", altere seu nome personalizado para "Média", sua função para Média e é o formato numérico para Contabilidade (0 casas decimais)
Para o terceiro campo "Valor", altere seu nome para "Contagem" e sua função para Contagem
Arraste o criado automaticamente campo de Rótulos da coluna para Rótulos de linha

Aqui está o que acabamos com:

Total, média e contagem na mesma tabela dinâmica!

Conclusão

Há muitos, muitos mais recursos e opções para Tabelas Dinâmicas criadas pelo Microsoft Excel - muitas para listar em um artigo como este. Para cobrir totalmente o potencial de tabelas dinâmicas, um pequeno livro (ou um site grande) seria necessário. Leitores corajosos e / ou nerds podem explorar as tabelas dinâmicas mais facilmente: basta clicar com o botão direito em praticamente tudo e ver quais opções ficam disponíveis para você. Há também as duas guias da faixa de opções: Ferramentas de Tabela Dinâmica / Opções e desenhar. Não importa se você cometer um erro - é fácil excluir a Tabela Dinâmica e iniciar novamente - uma possibilidade que os antigos usuários do Lotus 1-2-3 do DOS nunca tiveram.

Se você estiver trabalhando no Office 2007, talvez queira verificar nosso artigo sobre como criar uma tabela dinâmica no Excel 2007.

Incluímos uma pasta de trabalho do Excel que você pode baixar para praticar suas habilidades de Tabela Dinâmica. Deve funcionar com todas as versões do Excel a partir de 97.

Baixe nosso livro de exercícios Excel