Tabela Dinamica do Excel: O Guia Completo para Iniciantes e Avançados
As Tabelas Dinâmicas são um dos recursos mais poderosos do Excel. Mesmo se você for um novato, poderá transformar grandes quantidades de dados em informações úteis. A Tabela Dinâmica pode ajudar você a criar relatórios em minutos. Analise seus dados limpos facilmente e, se eles não estiverem limpos, isso pode ajudar você a limpá-los. Não quero te entediar, então vamos começar a explorar.
Como criar uma Tabela Dinâmica
É simples. Basta selecionar seus dados. Ir para Inserir. Clique na Tabela Dinâmica e pronto.
Mas espere. Antes de criar uma tabela dinâmica, certifique-se de que todas as colunas tenham um título.
Se algum título de coluna for deixado em branco, a tabela dinâmica não será criada e passará por uma mensagem de erro.
Requisito 1: Todas as colunas devem ter um título para começar a usar tabelas dinâmicas no Excel
Você deve ter seus dados organizados com o cabeçalho adequado. Depois de obtê-lo, você pode inserir a tabela dinâmica.
Insira a Tabela Dinâmica da Fita
Para inserir uma tabela dinâmica no menu, siga estas etapas:
- Selecione seu intervalo de dados
- Vá para a aba de inserção
- Clique no ícone da Tabela Dinâmica.
- A caixa de opções Criar uma tabela dinâmica aparecerá.
- Aqui, você pode ver o intervalo de dados selecionado. Se você acha que este não é o intervalo que deseja selecionar, redimensione seu intervalo diretamente a partir daqui, em vez de voltar e selecionar dados novamente.
- Em seguida, você pode selecionar onde deseja sua tabela dinâmica. Recomendo usar a nova planilha, mas você também pode usar a planilha atual. Basta definir a localização na caixa Localização.
- Agora, se você terminou as configurações, clique no botão OK. Você terá sua tabela em uma nova planilha. Basta selecionar seus campos para resumos. Veremos como criamos um resumo dos dados usando a tabela , mas primeiro vamos esclarecer o básico. Neste tutorial de tabela do Excel, você aprenderá mais do que espera.
Inserir Atalho de Tabela Dinâmica (Alt > N > V)
Este é um atalho de teclado sequencial para abrir a caixa de opções Criar tabela dinâmica. Pressione o botão Alt e solte-o. Pressione N e solte. Pressione V e solte. A caixa de opções Criar uma tabela será aberta.
Agora basta seguir o procedimento acima para criar uma tabela no Excel.
Inserir Atalho de Tabela Dinâmica Usando o Atalho Antigo do Excel (Alt > D > P)
Uma coisa que mais gosto no Microsoft Excel é que em cada nova versão do Excel eles introduzem novos recursos, mas não descartam os recursos antigos. Isso permite que os usuários antigos trabalhem normalmente em novas versões, como costumavam trabalhar em versões mais antigas.
Se você pressionar sequencialmente ALT, D e P no teclado, o Excel abrirá o assistente de criação de tabela dinâmica.
Selecione a opção apropriada. A opção selecionada na captura de tela acima nos levará a criar uma tabela dinâmica como criamos antes. Clique em Enter ou clique em Avançar se quiser verificar o intervalo selecionado.
Pressione Enter novamente.
Selecione uma nova planilha ou onde você deseja que sua tabela dinâmica pressione Enter. E está feito.
Crie Relatórios Usando Tabelas Dinâmicas
Agora você sabe como inserir uma tabela dinâmica. Vamos nos preparar para fazer relatórios usando tabelas dinâmicas em minutos.
Temos os dados para a ordem estacionária.
Os campos da coluna são:
- Data do pedido: Data do pedido (obviamente)
- Região: A região do pedido no país
- Cliente: Nome do cliente
- Item: Item pedido
- Unidade: Número de unidades de um item pedido
- Custo unitário: Por custo unitário
- Total: Custo total do pedido (unidade * custo unitário).
Para criar relatórios usando tabelas dinâmicas, usaremos:
Campos do recurso de análise: contém a lista de nomes de colunas em seus dados.
Áreas dinâmicas: Essas 4 áreas são usadas para mostrar seus dados de forma educada.
FILTROS: Coloque aqui os campos dos quais você gostaria de aplicar filtros em seu relatório.
COLUNAS: Coloque aqui os campos que você deseja em suas colunas no relatório.
LINHAS: Arraste os campos que você deseja mostrar em termos brutos, como na imagem acima. Mostrei Região em ROWS.
VALORES: Selecione um campo para obter Contagem, Soma, Média, Porcentagem (e muito mais) etc. que você vai querer ver.
Agora, usando as informações acima, preparamos este relatório rápido que mostra de qual região quantos pedidos são feitos para cada item.
Agora que você entende seus dados e campos dinâmicos (afinal, você é inteligente), vamos responder algumas perguntas relacionadas a esses dados usando o recurso de análise rapidamente.
Q1. Quantas ordens existem?
O recurso de análise fica inicialmente em branco, conforme mostrado na imagem abaixo.
Você precisa selecionar campos (nomes de colunas) em áreas apropriadas para ver o resumo ou os detalhes desse campo.
Agora, para responder à pergunta acima, selecionarei Item (selecione qualquer coluna, apenas certifique-se de que não haja uma célula em branco no meio) nos campos de valor.
Selecione Item na lista de campos e arraste-o para o campo Valor.
Temos a nossa resposta. As tabelas dinâmicas me dizem que há um total de 43 pedidos. Isso está correto.
Agora que selecionei OrderDate na área Valores, ele mostra 42. Isso significa que OrderDate tem uma célula em branco, pois sabemos que o número total de pedidos é 43.
Identifique Dados Irregulares Usando Tabelas Dinâmicas e Limpe-os
O recurso de análise pode ajudar você a encontrar informações incorretas nos dados. Na maioria das vezes, nossos dados são preparados por operadores de entrada de dados ou por usuários, que geralmente são irregulares e precisam de alguma limpeza para preparar relatórios e análises precisos.
Você deve sempre limpar e preparar seus dados de forma educada, antes de preparar qualquer relatório. Mas por vezes só depois de prepararmos o relatório é que ficamos a saber que os nossos dados apresentam alguma irregularidade.
Q2: Informe o número de pedidos de cada região
Agora, para responder a esta pergunta:
Selecione Região e arraste-a para a Área de Linhas e Item para a Área de Valores. Obteremos dados divididos por região. Podemos responder de qual região quantos pedidos vieram.
Há um total de 4 regiões em nossos dados de acordo com o recurso de análise. Mas espere, observe a região Central e Central. Sabemos que Centrle deveria ser Central. Há uma irregularidade. Precisamos acessar nossos dados e fazer a limpeza de dados.
Para limpar os dados no campo região filtramos o nome incorreto da região (Centrle) e o corrigimos (Central).
Agora volte aos seus dados de pivô.
Clique com o botão direito em qualquer lugar do recurso de análise e clique em Atualizar.
Seu relatório já foi atualizado.
Agora, você pode ver que existem apenas 3 regiões de fato.
Formatação de Relatório Dinâmico com Linhas Categorizadas
Às vezes você precisará de relatórios como a imagem acima. Isso facilita a visualização de seus dados de forma estruturada. Você pode facilmente dizer de qual região quantos itens são encomendados. Vamos ver como você pode fazer isso.
Mova a região e o item para a área ROWS. Certifique-se de que a região esteja na parte superior e os itens na parte inferior, conforme mostrado na imagem. Arraste o item para a área de valor. Como resultado, você obterá este relatório. Ele servirá. Mas às vezes seu chefe quer relatar em forma de tabela, sem subtotais.
Para fazer isso, precisamos formatar nossa recurso de análise.
Remover Subtotais da Tabela Dinâmica
Siga estes passos:
- Clique em qualquer lugar da sua recurso de análise.
- Vá para a aba Design.
- Clique no menu Subtotais.
- Clique em Não mostrar subtotais.
Você pode ver que não há subtotais agora. Tudo bem. Mas ainda não está em forma de tabela. Regiões e itens são mostrados em uma única coluna. Mostre-os separadamente.
Crie uma Tabela Dinâmica
Agora, para mostrar regiões e itens em colunas diferentes, siga estas etapas:
- Clique em qualquer lugar do recurso de análise
- Vá para a aba Design
- Clique em Layout do relatório.
- Clique na opção Mostrar para o formulário tabular. Por fim, você terá essa visão sofisticada do seu relatório.
Agora sabemos o número total de pedidos feitos para cada item de cada região. Ele é mostrado nas colunas Contagem de Itens.
Altere o nome da coluna para Pedidos.
Parece melhor agora.
Q3: Quantas unidades de cada item são encomendadas?
Para responder a esta pergunta precisamos de uma soma de Unidades. Para fazer isso, basta mover o campo Unidades para Valores. Ele somará automaticamente o número de unidades de cada item. Se uma coluna no recurso de análise contiver apenas valores, o recurso de análise mostrará por padrão a soma desses valores. Mas pode ser alterado a partir de uma configuração de campo de valor.
Configurações do Campo de Valor da Tabela Dinâmica
Q4: O preço médio de cada item?
Em nossos dados de amostra, o preço de um item é diferente para pedidos diferentes. Por exemplo, veja Encadernadores.
Quero saber o custo médio de cada item no recurso de análise. Para descobrir isso, arraste o campo Custo Unitário para Valor. Ele mostrará a Soma do Custo Unitário.
Não queremos a Soma do Custo Unitário, queremos a Média do Custo Unitário. Para fazer isso…
- Clique com o botão direito em qualquer lugar na soma da coluna Custo Unitário no recurso de análise
- Clique em Configurações do campo de valor
- Com base nas opções disponíveis, selecione Média e clique em OK.
Por fim, você terá este Relatório Pivô:
Campos Calculados da Tabela Dinâmica
Uma das funções mais úteis do recurso de análise são seus Campos Calculados. Campos calculados são campos obtidos por algumas operações em colunas disponíveis.
Vamos entender como inserir campos calculados em um recurso de análise com um exemplo:
Com base nos nossos dados, elaborámos este relatório.
Aqui, temos Soma das Unidades e Custo Total. Acabei de mover a coluna total para o campo Valores e depois renomeei-a para Custo Total. Agora quero saber o preço médio de uma unidade de cada item para cada região. E isso seria:
Vamos inserir um campo no recurso de análise que mostre o preço médio de cada item por região:
Siga estas etapas para inserir um campo calculado no recurso de análise:
- Clique em qualquer lugar do recurso de análise e vá para a guia Analisar
- Clique em Campos, Itens e Conjuntos no grupo de cálculo.
- Clique em Campos calculados.
Você verá esta caixa de entrada para seu campo de cálculo:
- Na caixa de entrada de nome, escreva o Custo Médio ou algo parecido, o Excel não se importará. Na caixa de entrada de fórmula, escreva e clique em OK.
Você pode escrever isso manualmente no teclado ou clicar duas vezes nos nomes dos campos listados na área Campos para executar operações.
- Agora você tem seu campo calculado adicionado à sua recurso de análise. É chamada de Soma do Custo Médio, mas não é uma soma. O Excel apenas executa a função padrão para nomear a coluna (como um ritual). Renomeie esta coluna e limite os dígitos decimais mostrados.
E aí você tem um campo calculado. Você pode torná-lo tão complexo quanto quiser. Para fins de exemplo, tomei esta operação média simples.
Agrupamento na Tabela Dinâmica
Você preparou este relatório dinâmico.
Agora quero que este relatório seja dividido anualmente. Veja o instantâneo abaixo.
Temos uma coluna sobre Data do Pedido. Mova o campo OrderDate para linhas na parte superior.
Não se parece em nada com o relatório necessário. Precisamos ter datas de grupo por ano.
Agora, para agrupar um campo no recurso de análise do Excel, siga estas etapas:
- Clique com o botão direito no campo que deseja agrupar.
- Clique em Grupo. Você terá esta caixa de opções para personalização. Como esta é uma coluna de dados, o Excel nos mostra o agrupamento correspondente. Você pode escolher sua data de início e término.
- Escolha por anos e clique em OK. Você fez agrupamentos anuais no recurso de análise do Excel.
Fatiadores de Mesa Giratória
Os fatiadores foram introduzidos no Excel 2010 como um addIn. No Excel 2013 e 2016, ele está disponível por padrão, assim como os filtros. Fatiadores nada mais são do que filtros. Ao contrário dos Filtros, os Fatiadores mostram todas as opções disponíveis bem na sua frente. Isso torna seu painel mais interativo.
Como Adicionar Fatiadores em Tabela Dinâmica (Excel 2016 e 2013)
Os fatiadores de mesa giratórios são fáceis de adicionar. Siga estes passos:
- Clique em qualquer lugar no recurso de análise e vá para a aba Analisar.
- Clique em Inserir fatiador. Você terá uma lista de campos para seus dados. Selecione quantos quiser.
- Para este exemplo, selecione Região e clique em OK.
Você adicionou Slicer ao seu relatório. Agora aplique o filtro com apenas um clique.
Inserir Linha do Tempo no Excel 2016 e 2013
Esta é uma das minhas funcionalidades favoritas do Excel Pivot Tables. Esta nova funcionalidade só funciona com datas. Usando isso, você pode selecionar visualmente um intervalo de tempo para filtrar seus dados.
Para inserir um cursor de linha do tempo, siga estas etapas:
Como Adicionar Linha do Tempo na Tabela Dinâmica (Excel 2016 e 2013)
- Clique em qualquer lugar no recurso de análise e vá para a aba Analisar.
- Clique em Inserir linha do tempo do grupo de filtros. Todas as colunas que contêm valores de tempo nos dados de origem serão listadas em uma caixa de opções para escolher. Aqui só temos um. Então sim…
- Escolha suas opções e clique em Enter ou clique em OK. Está pronto e você tem a linha do tempo da sua recurso de análise bem na sua frente. Você pode escolher mostrá-la diariamente, mensalmente, trimestralmente ou anualmente. Escolhi o Monthly aqui.
Conclusão
Neste artigo abordei as funcionalidades mais importantes e úteis do Pivot Table. Exploramos novos recursos do recurso de análise no Excel 2016 e 2013. Aprendemos sobre o uso clássico de um recurso de análise executada no Excel 2007, 2010 e anteriores. Eles ainda são úteis. Se não encontrou sua resposta relacionada à sua recurso de análise aqui, pergunte na seção de comentários.
Há muitas outras funções que ainda precisam ser explicadas. Aprenderemos a função avançada do recurso de análise no próximo artigo. Até lá, seja excelente em tudo.
Quer aprender mais Excel? Continue explorando os tutoriais do ExcelTipsWorld e descubra novas formas de trabalhar com planilhas de maneira mais rápida e eficiente.
Para mais informações oficiais, consulte a página de suporte da Microsoft.
