Conteúdo
Tabelas dinâmicas é uma das ferramentas mais poderosas do Excel. Eles permitem analisar e somar vários resumos de grandes quantidades de dados com apenas alguns cliques do mouse. Neste artigo, vamos conhecer as tabelas dinâmicas, entender o que são, aprender a criá-las e personalizá-las.
Este artigo foi escrito usando o Excel 2010. O conceito de Tabelas Dinâmicas não mudou muito ao longo dos anos, mas a maneira como você as cria é um pouco diferente em cada nova versão do Excel. Se você tiver uma versão do Excel que não seja a 2010, esteja preparado para que as capturas de tela deste artigo sejam diferentes do que você vê na tela.
Um pouco de história
Nos primeiros dias do software de planilha, a bola de regras Lotus 1-2-3. Seu domínio era tão completo que os esforços da Microsoft para desenvolver seu próprio software (Excel) como uma alternativa ao Lotus pareciam uma perda de tempo. Agora avance para 2010! O Excel domina as planilhas mais do que o código Lotus jamais fez em sua história, e o número de pessoas que ainda usam o Lotus é próximo de zero. Como isso pôde acontecer? Qual foi a razão para uma reviravolta tão dramática?
Os analistas identificam dois fatores principais:
- Primeiro, a Lotus decidiu que essa nova plataforma GUI chamada Windows era apenas uma moda passageira que não duraria muito. Eles se recusaram a construir uma versão Windows do Lotus 1-2-3 (mas apenas por alguns anos), prevendo que a versão DOS de seu software seria tudo o que os consumidores precisariam. A Microsoft naturalmente desenvolveu o Excel especificamente para Windows.
- Em segundo lugar, a Microsoft introduziu uma ferramenta no Excel chamada Tabelas Dinâmicas que não estava disponível no Lotus 1-2-3. As tabelas dinâmicas, exclusivas do Excel, provaram ser tão extremamente úteis que as pessoas tendiam a ficar com o novo pacote de software Excel em vez de continuar com o Lotus 1-2-3, que não os tinha.
As tabelas dinâmicas, além de subestimar o sucesso do Windows em geral, representaram a marcha da morte para o Lotus 1-2-3 e deram início ao sucesso do Microsoft Excel.
O que são tabelas dinâmicas?
Então, qual é a melhor maneira de caracterizar o que são as Tabelas Dinâmicas?
Em termos simples, as tabelas dinâmicas são resumos de alguns dados, criados para facilitar a análise desses dados. Ao contrário dos totais criados manualmente, as Tabelas Dinâmicas do Excel são interativas. Uma vez criados, você pode modificá-los facilmente se eles não fornecerem a imagem que você esperava. Com apenas alguns cliques do mouse, os totais podem ser invertidos para que os títulos das colunas se tornem títulos das linhas e vice-versa. Você pode fazer muitas coisas com tabelas dinâmicas. Em vez de tentar descrever em palavras todas as funcionalidades das tabelas dinâmicas, é mais fácil demonstrar na prática…
Os dados que você analisa com Tabelas Dinâmicas não podem ser aleatórios. Deve ser dados brutos brutos, como uma lista de algum tipo. Por exemplo, pode ser uma lista de vendas feitas pela empresa nos últimos seis meses.
Observe os dados mostrados na figura abaixo:
Observe que não são dados brutos brutos, pois já foram resumidos. Na célula B3 vemos $ 30000, que provavelmente é o resultado total que James Cook fez em janeiro. Onde estão os dados originais então? De onde veio o valor de $ 30000? Onde está a lista original de vendas da qual esse total mensal foi derivado? É claro que alguém fez um ótimo trabalho organizando e classificando todos os dados de vendas dos últimos seis meses e transformando-os na tabela de totais que vemos. Quanto tempo você acha que levou? Hora? Dez horas?
O fato é que a tabela acima não é uma tabela dinâmica. Foi feito à mão a partir de dados brutos armazenados em outro lugar e levou pelo menos algumas horas para ser processado. Apenas essa tabela de resumo pode ser criada usando tabelas dinâmicas em apenas alguns segundos. Vamos descobrir como…
Se voltarmos à lista de vendas original, seria algo assim:
Você pode se surpreender que a partir desta lista de negociações com a ajuda de tabelas dinâmicas e em apenas alguns segundos, possamos criar um relatório mensal de vendas em Excel, que analisamos acima. Sim, podemos fazer isso e muito mais!
Como criar uma tabela dinâmica?
Primeiro, certifique-se de ter alguns dados de origem em uma planilha do Excel. A lista de transações financeiras é a mais típica que ocorre. Na verdade, pode ser uma lista de qualquer coisa: detalhes de contato de funcionários, uma coleção de CDs ou dados de consumo de combustível da sua empresa.
Então, iniciamos o Excel… e carregamos essa lista…
Depois de abrirmos essa lista no Excel, podemos começar a criar uma tabela dinâmica.
Selecione qualquer célula desta lista:
Então na aba Inclusão (Inserir) comando de seleção Tabela Dinâmica (Tabela dinâmica):
Uma caixa de diálogo aparecerá Criar Tabela Dinâmica (Criando uma tabela dinâmica) com duas perguntas para você:
- Quais dados usar para criar uma nova tabela dinâmica?
- Onde colocar a tabela dinâmica?
Como no passo anterior já selecionamos uma das células da lista, toda a lista será selecionada automaticamente para criar uma tabela dinâmica. Observe que podemos selecionar um intervalo diferente, uma tabela diferente e até mesmo alguma fonte de dados externa, como uma tabela de banco de dados Access ou MS-SQL. Além disso, precisamos escolher onde colocar a nova tabela dinâmica: em uma nova planilha ou em uma das existentes. Neste exemplo, vamos escolher a opção – Nova planilha (para uma nova folha):
O Excel criará uma nova planilha e colocará uma tabela dinâmica vazia nela:
Assim que clicarmos em qualquer célula da tabela dinâmica, outra caixa de diálogo aparecerá: Lista de campos da tabela dinâmica (Campos da tabela dinâmica).
A lista de campos na parte superior da caixa de diálogo é uma lista de todos os títulos da lista original. As quatro áreas vazias na parte inferior da tela permitem que você informe à Tabela Dinâmica como deseja resumir os dados. Enquanto essas áreas estiverem vazias, também não há nada na tabela. Tudo o que temos a fazer é arrastar os títulos da área superior para as áreas vazias abaixo. Ao mesmo tempo, uma tabela dinâmica é gerada automaticamente, de acordo com nossas instruções. Se cometermos um erro, podemos remover os títulos da área inferior ou arrastar outros para substituí-los.
Área Valores (Significados) é provavelmente o mais importante dos quatro. Qual título é colocado nesta área determina quais dados serão resumidos (soma, média, máximo, mínimo, etc.) Estes são quase sempre valores numéricos. Um excelente candidato a um lugar nesta área são os dados sob o título Valor (Custo) da nossa mesa original. Arraste este título para a área Valores (Valores):
Observe que o título Valor agora está marcado com uma marca de seleção, e na área Valores (Valores) uma entrada apareceu Soma do valor (campo Valor Valor), indicando que a coluna Valor resumido.
Se olharmos para a própria tabela dinâmica, veremos a soma de todos os valores da coluna Valor tabela original.
Então, nossa primeira tabela dinâmica está criada! Conveniente, mas não particularmente impressionante. Provavelmente queremos obter mais informações sobre nossos dados do que temos atualmente.
Vamos voltar aos dados originais e tentar identificar uma ou mais colunas que podem ser usadas para dividir essa soma. Por exemplo, podemos formar nossa tabela dinâmica de forma que o valor total das vendas seja calculado para cada vendedor individualmente. Aqueles. linhas serão adicionadas à nossa tabela dinâmica com o nome de cada vendedor da empresa e seu valor total de vendas. Para alcançar este resultado, basta arrastar o título vendedor (representante de vendas) para a região Rótulos de linha (Cordas):
Fica mais interessante! Nossa Tabela Dinâmica está começando a tomar forma…
Veja os benefícios? Em alguns cliques, criamos uma tabela que levaria muito tempo para ser criada manualmente.
O que mais podemos fazer? Bem, de certa forma, nossa tabela dinâmica está pronta. Criamos um resumo útil dos dados originais. Informações importantes já recebidas! No restante deste artigo, veremos algumas maneiras de criar Tabelas Dinâmicas mais complexas, além de aprender como personalizá-las.
Configuração de tabela dinâmica
Primeiro, podemos criar uma tabela dinâmica bidimensional. Vamos fazer isso usando o cabeçalho da coluna Pagamentos (Forma de pagamento). Basta arrastar o título Pagamentos para a área Rótulos de coluna (Colunas):
Obtemos o resultado:
Parece muito legal!
Agora vamos fazer uma tabela tridimensional. Como seria uma mesa dessas? Vamos ver…
Arraste o cabeçalho Pacote (Complexo) para a área filtros de relatório (Filtros):
Observe onde ele está...
Isso nos dá a oportunidade de filtrar o relatório com base em "Qual complexo de férias foi pago". Por exemplo, podemos ver um detalhamento por vendedores e por métodos de pagamento para todos os complexos ou, em alguns cliques do mouse, alterar a visualização da tabela dinâmica e mostrar o mesmo detalhamento apenas para quem solicitou o complexo Caçadores do sol.
Então, se você entendeu isso corretamente, nossa tabela dinâmica pode ser chamada de tridimensional. Vamos continuar configurando...
Se de repente acontecer que apenas o pagamento por cheque e cartão de crédito (ou seja, pagamento sem dinheiro) deve ser exibido na tabela dinâmica, podemos desativar a exibição do título dinheiro (Dinheiro). Para isso, ao lado Rótulos de coluna clique na seta para baixo e desmarque a caixa no menu suspenso dinheiro:
Vamos ver como nossa tabela dinâmica se parece agora. Como você pode ver, a coluna dinheiro desapareceu dela.
Formatando Tabelas Dinâmicas no Excel
As tabelas dinâmicas são obviamente uma ferramenta muito poderosa, mas até agora os resultados parecem um pouco simples e chatos. Por exemplo, os números que somamos não parecem quantias em dólares – são apenas números. Vamos consertar isso.
É tentador fazer o que você está acostumado em tal situação 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 definir o formato desejado. O problema com essa abordagem é que se você alterar a estrutura da tabela dinâmica no futuro (o que acontece com 99% de chance), a formatação será perdida. O que precisamos é de uma maneira de torná-lo (quase) permanente.
Primeiro, vamos encontrar a entrada Soma do valor in Valores (Valores) e clique nele. No menu que aparece, selecione o item Configurações do campo de valor (Opções do campo de valor):
Uma caixa de diálogo aparecerá Configurações do campo de valor (Opções do campo de valor).
Clique no botão Formato de número (Formato numérico), uma caixa de diálogo será aberta. formatar células (formato da célula):
Da lista Categoria (Formatos numéricos) selecione Contabilidade (Financeiro) e defina o número de casas decimais para zero. Agora pressione algumas vezes OKpara voltar à nossa tabela dinâmica.
Como você pode ver, os números são formatados como valores em dólares.
Enquanto estamos nisso com a formatação, vamos configurar o formato para toda a Tabela Dinâmica. Existem várias maneiras de fazer isso. Usamos o que é mais simples…
Clique na Ferramentas de tabela dinâmica: design (Trabalhando com Tabelas Dinâmicas: Construtor):
Em seguida, expanda o menu clicando na seta no canto inferior direito da seção Estilos de tabela dinâmica (Estilos de Tabela Dinâmica) para ver a extensa coleção de estilos embutidos:
Escolha qualquer estilo adequado e veja o resultado em sua tabela dinâmica:
Outras configurações de tabela dinâmica no Excel
Às vezes, você precisa filtrar dados por datas. Por exemplo, em nossa lista de negócios há muitas, muitas datas. O Excel fornece uma ferramenta para agrupar dados por dia, mês, ano e assim por diante. Vamos ver como é feito.
Primeiro remova a entrada. Pagamentos da região Rótulos de coluna (Colunas). Para fazer isso, arraste-o de volta para a lista de títulos e, em seu lugar, mova o título Data reservada (data da reserva):
Como você pode ver, isso tornou nossa tabela dinâmica temporariamente inútil. O Excel criou uma coluna separada para cada data em que uma negociação foi feita. Como resultado, conseguimos uma mesa bem ampla!
Para corrigir isso, clique com o botão direito do mouse em qualquer data e selecione no menu de contexto GRUPO (Grupo):
A caixa de diálogo de agrupamento aparecerá. Nós escolhemos Meses (Meses) e clique em OK:
Voilá! Esta tabela é muito mais útil:
Aliás, esta tabela é quase idêntica à apresentada no início do artigo, onde os totais de vendas foram compilados manualmente.
Há outro ponto muito importante que você precisa saber! Você pode criar não um, mas vários níveis de cabeçalhos de linha (ou coluna):
… e vai ficar assim…
O mesmo pode ser feito com títulos de colunas (ou até mesmo filtros).
Vamos voltar à forma original da tabela e ver como exibir médias em vez de somas.
Para começar, clique em Soma do valor e no menu que aparece selecione Configurações do campo de valor (Opções do campo de valor):
A lista Resuma o campo de valor por (Operação) na caixa de diálogo Configurações do campo de valor (Opções do campo de valor) selecione Média (Média):
Ao mesmo tempo, enquanto estamos aqui, vamos mudar Nome personalizado (Nome personalizado) com Média do valor (campo Valor Valor) para algo menor. Digite neste campo algo como Média:
Press OK e veja o que acontece. Observe que todos os valores mudaram de totais para médias, e o cabeçalho da tabela (na célula superior esquerda) mudou para Média:
Se desejar, você pode obter imediatamente o valor, a média e o número (vendas) colocados em uma tabela dinâmica.
Aqui está um guia passo a passo sobre como fazer isso, começando com uma tabela dinâmica vazia:
- Arraste o cabeçalho vendedor (representante de vendas) para a região Rótulos de coluna (Colunas).
- Arraste o título três vezes Valor (Custo) para a área Valores (Valores).
- Para o primeiro campo Valor mude o título para Total (Valor), e o formato do número neste campo é Contabilidade (Financeiro). O número de casas decimais é zero.
- Segundo campo Valor nome Médiae, defina a operação para isso Média (Média) e o formato do número neste campo também mudam para Contabilidade (Financeiro) com zero casas decimais.
- Para o terceiro campo Valor definir um título Contar e uma operação para ele – Contar (Quantidade)
- No Rótulos de coluna Campo (Colunas) criado automaticamente Σ Valores (Σ Valores) – arraste-o para a área Rótulos de linha (Linhas)
Aqui está o que vamos terminar com:
Valor total, valor médio e número de vendas – tudo em uma tabela dinâmica!
Conclusão
As tabelas dinâmicas no Microsoft Excel contêm muitos recursos e configurações. Em um artigo tão pequeno, eles não estão nem perto de cobrir todos eles. Seria necessário um pequeno livro ou um grande site para descrever completamente todas as possibilidades de tabelas dinâmicas. Leitores ousados e curiosos podem continuar sua exploração de tabelas dinâmicas. Para fazer isso, basta clicar com o botão direito do mouse em quase qualquer elemento da tabela dinâmica e ver quais funções e configurações são abertas. Na Faixa de Opções, você encontrará duas guias: Ferramentas de tabela dinâmica: opções (análise) e Design (Construtor). Não tenha medo de errar, você sempre pode excluir a Tabela Dinâmica e começar de novo. Você tem uma oportunidade que os usuários de longa data do DOS e do Lotus 1-2-3 nunca tiveram.