As tabelas dinâmicas são um dos recursos mais poderosos do Excel. Mesmo se você for um novato, você pode transformar grandes quantidades de dados em informações úteis. A Tabela Dinâmica pode ajudá-lo a fazer relatórios em minutos. Analise seus dados limpos facilmente e, se os dados não estiverem limpos, isso pode ajudá-lo a limpá-los. Eu não quero aborrecê-lo, então vamos pular e explorar.
Como criar uma tabela dinâmica
É simples. Basta selecionar seus dados. Vá 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 apresentará 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 título adequado. Depois de obtê-lo, você pode inserir a tabela dinâmica.
Insira a tabela dinâmica da faixa de opções
Para inserir uma tabela dinâmica do menu, siga estas etapas:
1. Selecione o seu intervalo de dados
2. Vá para a guia de inserção
3. Clique no ícone Tabela Dinâmica.
4. A caixa de opção Criar uma Tabela Dinâmica será exibida.
5. Aqui, você pode ver o intervalo de dados que selecionou. Se você acha que este não é o intervalo que deseja selecionar, redimensione o intervalo diretamente a partir daqui, em vez de voltar e selecionar os dados novamente.
6. Em seguida, você pode selecionar onde deseja sua tabela dinâmica. Eu recomendo usar a nova planilha, mas você também pode usar a planilha atual. Basta definir o local na caixa Local.
7. Agora, se você tiver concluído as configurações, clique no botão OK. Você terá sua tabela dinâmica em uma nova planilha. Basta selecionar seus campos para resumos. Veremos como criamos um resumo de dados usando a tabela dinâmica, mas primeiro vamos deixar o básico claro. Neste tutorial de tabela dinâmica 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 o Criar Tabela Dinâmica caixa de opção.
Aperte o botão Alt e solte-o. Bater N e libere-o. Bater V e libere-o. A caixa de opção Criar uma Tabela Dinâmica será aberta.
Agora basta seguir o procedimento acima para criar uma tabela dinâmica no Excel.
Insira o atalho da tabela dinâmica usando o antigo atalho do Excel (Alt> D> P)
Uma coisa que eu mais gosto no Microsoft Excel é que em cada nova versão do Excel eles introduzem novos recursos, mas eles não descarte os recursos antigos (como o MS fez com o win 8. Foi patético). Esse permite que os usuários antigos trabalhem normalmente nas novas versões como costumavam trabalhar nas versões mais antigas.
Se você pressionar sequencialmente ALT, D e P no teclado, o Excel abrirá para criar um assistente de tabela dinâmica.
Selecione a opção apropriada. A opção selecionada na imagem acima nos levará a criar uma tabela dinâmica como criamos antes.
Pressione Enter ou clique em Avançar se quiser verificar o intervalo selecionado.
Pressione Enter novamente.
Selecione uma nova planilha ou onde quiser que sua tabela dinâmica pressione Enter. E está feito.
Criar relatórios usando tabelas dinâmicas
Agora você sabe 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 da ordem no país
Cliente: Nome do cliente (o que mais pode ser)
Item: Item pedido
Unidade: Número de unidades de um item pedido
Custo unitário: Custo por unidade
Total: Custo total do pedido (custo unitário * unitário).
Para criar relatórios usando tabelas dinâmicas, usaremos
Campos da tabela dinâmica: Contém a lista de nomes de colunas em seus dados.
Áreas de pivô: Essas 4 áreas são usadas para mostrar seus dados de maneira bem-educada.
FILTROS: Coloque aqui os campos dos quais você gostaria de aplicar filtros em seu relatório.
COLUNAS: coloque aqui os campos que deseja nas colunas do relatório: (é melhor mostrar do que explicar)
LINHAS: Arraste os campos que você deseja mostrar em bruto como na imagem acima, mostrei Região em ROWS.
VALORES: Selecione um campo para obter Contagem, Soma, Média, Porcentagem (e muitos mais) etc. que você deseja ver.
Agora, usando as informações acima, preparamos este relatório dinâmico 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 a algumas perguntas relacionadas a esses dados usando a tabela dinâmica rapidamente.
T1. Quantos pedidos existem?
A tabela dinâmica está inicialmente em branco, conforme mostrado na imagem abaixo.
Você precisa selecionar campos (nomes de coluna) nas á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 nossa resposta. As tabelas dinâmicas informam que há um total de 43 pedidos. Isto está correto.
Agora que selecionei Data do pedido na área de Valores, mostra 42. Esse meios Data do pedido tem uma célula em branco Desde a sabemos que o número total de pedidos é 43.
Identifique dados irregulares usando tabelas dinâmicas e limpe-os.
A Tabela Dinâmica pode ajudá-lo 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 precisas.
Você deve sempre limpar e preparar seus dados de maneira educada, antes de preparar qualquer relatório. Mas às vezes só depois de preparar o relatório é que ficamos sabendo que nossos dados têm alguma irregularidade. Venha, vou te mostrar como …
Q2: informe o número de pedidos de cada região
Agora, para responder a esta pergunta:
Selecione Região e arraste-o para Linhas Área e Item para Valores Área.
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 a tabela dinâmica. Mas espere, observe que Central e Centrle região. Nós sabemos que Centrle deve ser Central. Existe uma irregularidade. Precisamos acessar nossos dados e fazer a limpeza de dados.
Para limpar os dados no campo de região, filtramos o nome de região incorreto (Centrle) e o corrigimos (Central).
Agora volte para seus dados de pivô.
Clique com o botão direito em qualquer lugar da tabela dinâmica e clique em Atualizar.
Seu relatório foi atualizado.
Agora, você pode ver que existem apenas 3 regiões de fato.
Formatação de relatórios dinâmicos com linhas categorizadas.
Às vezes, você precisará de relatórios como a imagem acima. Isso torna mais fácil ver seus dados de forma estruturada. Você pode saber facilmente de qual região quantos itens são pedidos. Vamos ver como você pode fazer isso.
Mover Região e Item para LINHAS área. Certifique-se de que a região está na parte superior e os itens na parte inferior, conforme mostrado na imagem.
Arrastar Item para Valor Área.
Como resultado, você receberá este relatório.
Vai servir. Mas às vezes seu chefe deseja relatar em forma de tabela sem subtotais. Para fazer isso, precisamos formatar nossa Tabela Dinâmica.
Remover subtotais da tabela dinâmica
Siga esses passos:
1. Clique em qualquer lugar da sua tabela dinâmica.
2. Vá para o Projeto Aba.
3. Clique em os subtotais cardápio.
4. Clique em Não mostrar subtotais.
Você pode ver que não há subtotais agora.
Multar. Mas ainda não está em forma tabular. Regiões e itens são mostrados em uma única coluna. Mostre-os separadamente.
Faça uma tabela dinâmica tabular
Agora, para mostrar regiões e itens em colunas diferentes, siga estas etapas:
1. Clique em qualquer lugar da Tabela Dinâmica
2. Vá para a guia Design
3. Clique em Layout de relatório.
4. Clique em Show para a opção de forma tabular. Finalmente, você terá essa visão sofisticada de seu relatório.
Agora sabemos o número total de pedidos feitos para cada item de cada região. É mostrado na contagem de colunas de itens.
Por favor, mude o nome da coluna para Pedidos.
Parece melhor agora.
Q3: quantas unidades de cada item são solicitadas?
Para responder a esta pergunta, precisamos de uma soma de unidades. Para isso, basta mover o campo Unidades para Valores. Ele somará automaticamente o número de unidades de cada item. Se uma coluna na Tabela Dinâmica contiver apenas valores, a Tabela Dinâmica mostrará por padrão a soma desses valores. Mas pode ser alterado a partir de uma configuração de campo de valor. Como? Eu vou te mostrar o último.
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, consulte Fichários.
Quero saber o custo médio de cada item da Tabela Dinâmica. Para descobrir isso, arraste Custo Unitário para o Campo de Valor. Ele mostrará a Soma do Custo Unitário.
Nós não queremos Soma do Custo Unitário, nós queremos Média de Custo Unitário. Para fazer isso …
1. Clique com o botão direito em qualquer lugar na soma da coluna Custo Unitário na Tabela Dinâmica
2. Clique Configurações do campo de valor
3. Com base nas opções disponíveis, selecione Média e clique em OK.
Finalmente, você terá este Relatório Dinâmico:
Campos calculados da tabela dinâmica
Uma das funções mais úteis da Tabela Dinâmica são seus Campos Calculados. Os campos calculados são campos obtidos por algumas operações nas colunas disponíveis.
Vamos entender como inserir campos calculados na tabela dinâmica com um exemplo:
Com base em nossos dados, preparamos este relatório.
Aqui temos Soma de unidades e Custo total. Acabei de mover a coluna total para o campo Valores e, em seguida, renomeei-o para Custo total. Agora quero saber o preço médio de uma unidade de cada item para cada região. E isso seria:
Preço médio = custo total / unidades totais
Vamos inserir um campo na tabela dinâmica que mostra o preço médio de cada região do item:
Siga estas etapas para inserir um campo calculado na Tabela Dinâmica
1. Clique em qualquer lugar na Tabela Dinâmica e vá para a guia Analisar
2. Clique em Campos, Itens e Conjuntos no grupo de cálculo.
3. Clique em Campos calculados.
Você verá esta caixa de entrada para o seu campo de cálculo:
4. Na caixa de entrada de nome, escreva o Custo Médio ou qualquer coisa que você quiser, o Excel não se importará. Na caixa de entrada da fórmula, escreva e clique em OK.
= Total / Unidades
Você pode escrever isso manualmente com o teclado ou pode clicar duas vezes nos nomes dos campos listados na área Campos para executar as operações.
5. Você tem seu campo calculado adicionado agora à sua tabela dinâmica. É denominado 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 desejar. Por causa de um exemplo, peguei essa 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 a data do pedido. Mova o campo OrderDate para Linhas no topo.
Não se parece em nada com o relatório necessário. Precisamos ter datas de grupo no decorrer do ano.
Agora, para agrupar um campo na Tabela Dinâmica do Excel, siga estas etapas:
1. Clique com o botão direito no campo que deseja agrupar.
2. Clique em Grupo. Você terá esta caixa de opção para personalização. Como esta é uma coluna de dados, o Excel nos mostra o agrupamento de acordo. Você pode escolher a data de início e de término.
3. Escolha por anos e clique em OK. Você fez o agrupamento anual na Tabela Dinâmica do Excel.
Segmentações de dados de tabela dinâmica
Os Slicers foram introduzidos no Excel 2010 como um suplemento. No Excel 2013 e 2016, ele está disponível por padrão, assim como os filtros.
Os Slicers nada mais são do que filtros. Ao contrário dos filtros, os Slicers mostram todas as opções disponíveis bem na sua frente. Isso torna seu painel mais interativo.
Como adicionar segmentação de dados na tabela dinâmica, Excel 2016 e 2013
Os Slicers da Tabela Dinâmica são fáceis de adicionar. Siga esses passos:
1. Clique em qualquer lugar na tabela dinâmica e vá para a guia Analisar.
2. Clique em Inserir Slicer. Você terá uma lista de campos para seus dados. Selecione quantos quiser.
3. Para este exemplo, selecione Região e clique em OK.
Você adicionou o 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 funções favoritas Tabelas Dinâmicas do Excel. 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 da linha do tempo, siga estas etapas:
Como adicionar cronograma na tabela dinâmica, Excel 2016 e 2013
1. Clique em qualquer lugar na Tabela Dinâmica e vá para a guia Analisar.
2. Clique em Insert Timeline from Filter Group. Todas as colunas contendo valores de tempo em dados de origem serão listadas em uma caixa de opção para escolher. Aqui temos apenas um. Então sim…
2. Escolha suas opções e pressione Enter ou clique em OK. Está feito e você tem a linha do tempo da tabela dinâmica bem na sua frente.
Você pode optar por exibi-lo diariamente, mensalmente, trimestralmente ou anualmente. Eu escolhi Mensal aqui.
Neste artigo, abordei as funcionalidades mais importantes e úteis da Tabela Dinâmica. Exploramos os novos recursos da tabela dinâmica no Excel 2016 e 2013. Aprendemos sobre o uso clássico de uma tabela dinâmica que era executada no Excel 2007, 2010 e anteriores. Eles ainda são úteis. Se não encontrou sua resposta relacionada à sua tabela dinâmica aqui, pergunte na seção de comentários.
Existem muitas outras funções que ainda não foram explicadas. Aprenderemos a função de Tabela Dinâmica avançada no próximo artigo. Até então se destacar em tudo.