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 aparecerá
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 a localização na caixa de localização.
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 e mas eles não descartam os recursos antigos (como o MS fez com o win 8. Foi patético) Esse permite que o usuário mais antigo trabalhe normalmente nas novas versões da mesma forma que funcionava nas versões mais antigas.
Se você pressionar sequencialmente ALT, D e P no teclado, o Excel será aberto 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.
Crie 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 (unidade * custo unitário).
Á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 células em branco no meio) nos campos de valor.
Selecione Item da 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.
Dica profissional: Você deve verificar se seus dados estão corretos ou não. Se este número não corresponder aos dados, isso significa que você selecionou algum intervalo incorreto ou que o campo tem células em branco.
Info: O campo de valor por padrão conta o número de entradas em uma coluna se contiver texto e somas se o campo contiver apenas valores. Você pode alterar isso nas configurações do campo de valor. Como? Veremos você mais tarde neste tutorial de tabela dinâmica.
Agora que selecionei Data do pedido na área de Valores, mostra 42. Este 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.
INFO: Não importa quais alterações você faça em seus dados de origem, a tabela dinâmica continuará trabalhando em dados antigos até que você o atualize. O Excel cria um cache dinâmico e uma tabela dinâmica é executada nesse cache. Quando atualizado, o cache antigo é alterado com dados novos.
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 no menu Subtotais.
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 do relatório.
4. Clique em Show para a forma tabular opção. 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 itens colunas.
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 do custo unitário. Para fazer isso …
- Clique com o botão direito em qualquer lugar na soma da coluna Custo Unitário na Tabela Dinâmica
- Clique Configurações do campo de valor
- Com base nas opções disponíveis, selecionar 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 da Tabela Dinâmica e vá para 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 a média Custo ou qualquer coisa que você goste, o Excel não se importará. No Fórmula caixa de entrada, escrever e clicar 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.