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 o 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 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 é na verdade 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:
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 valores de consumo de combustível para a 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 ($ A $ 1: $ G $ 88 no Pagamentos folha, neste exemplo). Observe que poderíamos selecionar uma lista em qualquer outra região de qualquer outra planilha, ou mesmo 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 uma existir 1. Neste exemplo, vamos selecionar um Novo 1:
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 a maneira 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 essa caixa em nossos dados de amostra é o campo / valor "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:
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 dos nossos dados onde 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:
AgoraFinalmente, as coisas começam a ficar interessantes! Nossa Tabela Dinâmica começa a tomar forma…
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 de coluna. Basta arrastar o cabeçalho "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êsMesa tridimensional. Como uma mesa assim poderia parecer? Bem vamos ver…
Arraste a coluna / título "Pacote" para o Filtro de Relatório caixa:
Isso nos permite filtrar nosso relatório com base no "pacote de férias" que estava sendo comprado. Por exemplo, podemos ver o detalhamento do vendedor versus o método de pagamento para todos pacotes, ou, com alguns cliques, alterá-lo para mostrar o mesmo detalhamento para o pacote "Sunseekers":
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 colunae desmarque "Dinheiro":
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 a somar 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úmero 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-) permanentes.
Primeiro, localizamos a entrada "Soma do valor" no campo Valores caixa, e clique sobre ele. 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) Contabilidadee solte o número de casas decimais para 0. Clique em Está bem algumas vezes para voltar para a Tabela Dinâmica …
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:
Outras opções
Podemos trabalhar com datas também. 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. Vejamos 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":
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:
Outra coisa interessante de se estar ciente é que você pode ter mais de um conjunto de cabeçalhos de linha (ou cabeçalhos de coluna):
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á beme 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":
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 o nome personalizado para "Total" e o 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
Image campo de Rótulos da coluna para Rótulos de linha
Aqui está o que acabamos com:
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 das Tabelas Dinâmicas, seria necessário um pequeno livro (ou um site grande). 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 começar de novo - 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 dar uma olhada no 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 do Excel
