Tabelas dinâmicas no Excel - análise de dados eficaz

As tabelas dinâmicas são essenciais para levar as suas análises no Excel para o nível seguinte. Neste guia, aprenderá a efetuar uma análise pivot abrangente que lhe fornecerá informações valiosas sobre os seus dados. Iremos percorrer os passos necessários para criar e analisar diferentes métricas a partir de um determinado conjunto de dados.

Principais conclusões

• A utilização de tabelas dinâmicas permite uma análise flexível dos dados.
• Os cálculos nas tabelas dinâmicas dão-lhe resultados imediatos para vendas e lucros.
• As análises pormenorizadas permitem filtrar e exportar conjuntos de dados específicos.
• O agrupamento de dados pode ajudar a criar uma visão geral mais clara.

Guia passo-a-passo

Passo 1: Criar a tabela dinâmica

Comece por criar a sua tabela dinâmica. Abra o Excel e selecione os seus dados. Pode fazê-lo rapidamente utilizando o atalho de teclado Control + A e, em seguida, Control + T para selecionar e formatar tudo. Em seguida, crie a tabela dinâmica com a opção "Resumir com tabela dinâmica" e posicione-a numa nova folha de cálculo a que pode chamar "Análise dinâmica".

Passo 2: Visão geral das vendas por cidade e unidade de negócio

Uma vez criada a tabela dinâmica, dê os primeiros passos para criar uma visão geral dos parágrafos. Arraste o campo "Vendas" para os valores e os campos "Unidades empresariais" e "Cidades" para as linhas e colunas. Agora deve ser possível ver uma síntese clara. Para facilitar a leitura da síntese, faz sentido distribuir as cidades e as divisões em conformidade.

Passo 3: Calcular o volume de negócios

Agora pode começar a calcular o volume de negócios. Para tal, multiplica-se os valores das vendas pelos preços unitários. Para tal, adicione uma nova coluna, clicando com o botão direito do rato e selecionando "Inserir células". Dê à nova coluna o nome "Volume de negócios" e calcule o volume de negócios utilizando a fórmula "Vendas * Preço unitário". Arraste esta fórmula para baixo em todas as linhas. Este formato garante que a tabela continua a poder ser actualizada.

Passo 4: Determinar o lucro

Para calcular o lucro, pode criar outra coluna. Em alternativa, pode simplesmente utilizar as opções dinâmicas, criando uma nova tabela dinâmica e inserindo os campos correspondentes ao volume de negócios e aos custos. O lucro é calculado a partir de "Volume de negócios - Custos totais".

Passo 5: Calcular o lucro líquido

Para calcular o lucro líquido, volte à sua análise pivot. Clique em "Analisar" e depois em "Campo, elemento e campo calculado". Aqui insere o lucro líquido tomando o lucro vezes 0,7, o que representa 70% do seu lucro total como lucro líquido.

Passo 6: Análise detalhada através da exportação

Para criar uma análise detalhada, pode criar uma nova tabela dinâmica e concentrar-se nos lucros brutos por cidade. Remova as áreas de negócio da tabela dinâmica e arraste o campo "Lucro" para o intervalo de valores. Isto permite-lhe obter informações específicas sobre as regiões de vendas, por exemplo, para Hamburgo.

Passo 7: Exportação de dados para vendas

Agora crie outra tabela dinâmica para a exportação de dados. Para isso, copie a tabela dinâmica anterior e concentre-se nas vendas para as cidades de Colónia e Estugarda. Faça duplo clique nos números das cidades para exportar os dados. Certifique-se de que organiza e funde as folhas de dados em conformidade.

Passo 8: Ordenar os lucros

Ao analisar os lucros, convém agora ordená-los por ordem crescente, de modo a poder ver melhor como os lucros diferem por cidade. Crie uma nova tabela dinâmica, arraste os lucros para os valores e as cidades para as linhas. Em seguida, selecione os resultados, vá a "Dados" e ordene-os conforme necessário.

Passo 9: Agrupar as cidades

Para criar uma melhor visão geral, pode agrupar as cidades. Por exemplo, pode categorizar Munique e Estugarda no grupo "Sul" e Berlim, Colónia e Hamburgo no grupo "Norte". Isto é feito copiando e personalizando a tabela dinâmica e utilizando as funções de agrupamento.

Passo 10: Síntese percentual dos lucros líquidos

Para apresentar os lucros líquidos por cidade como uma percentagem, crie uma nova tabela dinâmica. Arraste os lucros líquidos e as cidades para os valores e linhas. Certifique-se de que seleciona "Percentagem dos resultados totais" nas definições do campo de valor para mostrar as percentagens.

Passo 11: Representação percentual das vendas

Para a última tarefa, copie a última tabela dinâmica e substitua os lucros líquidos pelas vendas. Adicione as unidades de negócio e vá novamente às definições do campo de valor para apresentar os valores como "Percentagem dos resultados totais". Agora, já recolheu tudo o que era importante.

Resumo

Este guia passo-a-passo mostrou-lhe como analisar e preparar dados complexos com tabelas dinâmicas. Desde vendas a cálculos de lucros, aprendeu a estruturar os seus dados e a obter informações mais aprofundadas. Depois de dominar estas técnicas, pode expandir significativamente as suas competências em Excel.