Se você está lendo este tutorial, há uma grande chance de que você já ouviu falar (ou até mesmo usou) a Tabela Dinâmica do Excel. É um dos recursos mais poderosos do Excel (sem brincadeira).
A melhor parte de usar uma Tabela Dinâmica é que mesmo que você não saiba nada no Excel, você ainda pode fazer coisas incríveis com ela com um conhecimento básico dela.
Vamos começar.
Clique aqui para baixar os dados de amostra e acompanhar.
O que é uma tabela dinâmica e por que você deve se importar?
Uma tabela dinâmica é uma ferramenta do Microsoft Excel que permite resumir rapidamente grandes conjuntos de dados (com apenas alguns cliques).
Mesmo se você for absolutamente novo no mundo do Excel, você pode usar facilmente uma Tabela Dinâmica. É tão fácil quanto arrastar e soltar cabeçalhos de linhas / colunas para criar relatórios.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo:
Estes são dados de vendas que consistem em aproximadamente 1000 linhas.
Ele contém os dados de vendas por região, tipo de varejista e cliente.
Agora, seu chefe pode querer saber algumas coisas a partir desses dados:
- Quais foram as vendas totais na região Sul em 2016?
- Quais são os cinco principais varejistas por vendas?
- Como o desempenho do The Home Depot se compara ao de outros varejistas no Sul?
Você pode ir em frente e usar as funções do Excel para obter as respostas a essas perguntas, mas e se de repente seu chefe aparecer com uma lista de mais cinco perguntas.
Você terá que voltar aos dados e criar novas fórmulas sempre que houver uma mudança.
É aqui que as tabelas dinâmicas do Excel se tornam realmente úteis.
Em segundos, uma Tabela Dinâmica responderá a todas essas perguntas (como você aprenderá abaixo).
Mas o benefício real é que ele pode acomodar seu chefe meticuloso, orientado por dados, respondendo suas perguntas imediatamente.
É tão simples que você também pode reservar alguns minutos e mostrar a seu chefe como fazer isso sozinho.
Esperançosamente, agora você tem uma ideia de por que as tabelas dinâmicas são tão incríveis. Vamos continuar e criar uma Tabela Dinâmica usando o conjunto de dados (mostrado acima).
Inserindo uma Tabela Dinâmica no Excel
Aqui estão as etapas para criar uma tabela dinâmica usando os dados mostrados acima:
- Clique em qualquer lugar do conjunto de dados.
- Vá para Inserir -> Tabelas -> Tabela Dinâmica.
- Na caixa de diálogo Criar Tabela Dinâmica, as opções padrão funcionam bem na maioria dos casos. Aqui estão algumas coisas a serem verificadas:
- Tabela / intervalo: Ele é preenchido por padrão com base em seu conjunto de dados. Se seus dados não tiverem linhas / colunas em branco, o Excel identificará automaticamente o intervalo correto. Você pode alterar isso manualmente, se necessário.
- Se você deseja criar a Tabela Dinâmica em um local específico, na opção ‘Escolha onde deseja que o relatório de Tabela Dinâmica seja colocado’, especifique o Local. Caso contrário, uma nova planilha é criada com a Tabela Dinâmica.
- Clique OK.
Assim que você clicar em OK, uma nova planilha será criada com a Tabela Dinâmica nela.
Embora a Tabela Dinâmica tenha sido criada, você não veria nenhum dado nela. Tudo o que você veria é o nome da Tabela Dinâmica e uma instrução de linha única à esquerda e os Campos da Tabela Dinâmica à direita.
Agora, antes de pularmos para a análise de dados usando esta Tabela Dinâmica, vamos entender quais são as porcas e parafusos que compõem uma Tabela Dinâmica do Excel.
As porcas e parafusos de uma mesa dinâmica do Excel
Para usar uma tabela dinâmica com eficiência, é importante conhecer os componentes que criam uma tabela dinâmica.
Nesta seção, você aprenderá sobre:
- Cache Dinâmico
- Área de Valores
- Área de linhas
- Área de Colunas
- Área de Filtros
Cache Dinâmico
Assim que você cria uma Tabela Dinâmica usando os dados, algo acontece no back-end. O Excel tira um instantâneo dos dados e os armazena em sua memória. Este instantâneo é denominado Cache Dinâmico.
Quando você cria exibições diferentes usando uma Tabela Dinâmica, o Excel não volta para a fonte de dados, em vez disso, usa o Cache Dinâmico para analisar rapidamente os dados e fornecer o resumo / resultados.
O motivo pelo qual um cache dinâmico é gerado é para otimizar o funcionamento da tabela dinâmica. Mesmo quando você tem milhares de linhas de dados, uma tabela dinâmica é muito rápida para resumir os dados. Você pode arrastar e soltar itens nas caixas de linhas / colunas / valores / filtros e os resultados serão atualizados instantaneamente.
Observação: uma desvantagem do cache dinâmico é que ele aumenta o tamanho da pasta de trabalho. Como é uma réplica dos dados de origem, quando você cria uma tabela dinâmica, uma cópia desses dados é armazenada no Cache Dinâmico.
Consulte Mais informação: O que é cache dinâmico e como usá-lo da melhor maneira.
Área de Valores
A Área de Valores é o que contém os cálculos / valores.
Com base no conjunto de dados mostrado no início do tutorial, se você deseja calcular rapidamente as vendas totais por região em cada mês, você pode obter uma tabela dinâmica conforme mostrado abaixo (veremos como criar isso mais tarde no tutorial) .
A área destacada em laranja é a Área de Valores.
Neste exemplo, ele tem o total de vendas em cada mês para as quatro regiões.
Área de linhas
Os títulos à esquerda da área Valores formam a área Linhas.
No exemplo abaixo, a área Linhas contém as regiões (destacadas em vermelho):
Área de Colunas
Os títulos na parte superior da área Valores formam a área Colunas.
No exemplo abaixo, a área Colunas contém os meses (destacados em vermelho):
Área de Filtros
A área de filtros é um filtro opcional que você pode usar para detalhar ainda mais o conjunto de dados.
Por exemplo, se você deseja ver apenas as vendas de varejistas Multiline, pode selecionar essa opção no menu suspenso (destacado na imagem abaixo), e a Tabela Dinâmica seria atualizada com os dados apenas para varejistas Multiline.
Analisando dados usando a tabela dinâmica
Agora, vamos tentar responder às perguntas usando a Tabela Dinâmica que criamos.
Clique aqui para baixar os dados de amostra e acompanhar.
Para analisar dados usando uma Tabela Dinâmica, você precisa decidir como deseja que o resumo de dados apareça no resultado final. Por exemplo, você pode querer todas as regiões à esquerda e o total de vendas ao lado delas. Depois de ter essa clareza em mente, você pode simplesmente arrastar e soltar os campos relevantes na Tabela Dinâmica.
Na seção Pivot Tabe Fields, você tem os campos e as áreas (conforme destacado abaixo):
Os Campos são criados com base nos dados de back-end usados para a Tabela Dinâmica. A seção Áreas é onde você coloca os campos, e de acordo com aonde um campo vai, seus dados são atualizados na Tabela Dinâmica.
É um mecanismo simples de arrastar e soltar, onde você pode simplesmente arrastar um campo e colocá-lo em uma das quatro áreas. Assim que você fizer isso, ele aparecerá na Tabela Dinâmica da planilha.
Agora vamos tentar responder às perguntas que seu gerente teve usando esta Tabela Dinâmica.
Q1: Quais foram as vendas totais na região Sul?
Arraste o campo Região na área Linhas e o campo Receita na área Valores. Ele atualizaria automaticamente a Tabela Dinâmica na planilha.
Observe que, assim que você soltar o campo Receita na área Valores, ele se tornará Soma da receita. Por padrão, o Excel soma todos os valores de uma determinada região e mostra o total. Se desejar, você pode alterar isso para Contagem, Média ou outras métricas estatísticas. Nesse caso, a soma é o que precisávamos.
A resposta a esta pergunta seria 21225800.
Q2 Quais são os cinco principais varejistas por vendas?
Arraste o campo Cliente na área Linha e o campo Receita na área de valores. Caso haja algum outro campo na seção da área e você queira removê-lo, basta selecioná-lo e arrastá-lo para fora dele.
Você obterá uma Tabela Dinâmica conforme mostrado abaixo:
Observe que, por padrão, os itens (neste caso, os clientes) são classificados em ordem alfabética.
Para obter os cinco principais varejistas, você pode simplesmente classificar essa lista e usar os cinco principais nomes dos clientes. Para fazer isso:
- Clique com o botão direito em qualquer célula da área Valores.
- Vá para Classificar -> Classificar do maior para o menor.
Isso lhe dará uma lista classificada com base nas vendas totais.
P3: Como o desempenho da The Home Depot se compara ao de outros varejistas no Sul?
Você pode fazer muitas análises para esta questão, mas aqui vamos apenas tentar comparar as vendas.
Arraste o Campo de Região na área Linhas. Agora arraste o campo Cliente na área Linhas abaixo do campo Região. Quando você fizer isso, o Excel entenderá que você deseja categorizar seus dados primeiro por região e, em seguida, por clientes dentro das regiões. Você terá algo como mostrado abaixo:
Agora arraste o campo Receita na área Valores e você terá as vendas de cada cliente (bem como a região geral).
Você pode classificar os varejistas com base nos números de vendas, seguindo as etapas abaixo:
- Clique com o botão direito em uma célula que tenha o valor de venda de qualquer varejista.
- Vá para Classificar -> Classificar do maior para o menor.
Isso classificaria instantaneamente todos os varejistas pelo valor de vendas.
Agora você pode examinar rapidamente a região sul e identificar que as vendas da The Home Depot foram 3004600 e teve um desempenho melhor do que quatro varejistas na região sul.
Agora, há mais de uma maneira de tirar a pele do gato. Você também pode colocar a região na área Filtro e então selecionar apenas a região sul.
Clique aqui para baixar os dados de amostra.
Espero que este tutorial forneça uma visão geral básica das tabelas dinâmicas do Excel e ajude você a começar a usá-las.
Aqui estão mais alguns tutoriais de tabela dinâmica de que você pode gostar:
- Preparando dados de origem para tabela dinâmica.
- Como aplicar formatação condicional em uma tabela dinâmica no Excel.
- Como agrupar datas em tabelas dinâmicas no Excel.
- Como agrupar números em uma tabela dinâmica no Excel.
- Como filtrar dados em uma tabela dinâmica no Excel.
- Usando Segmentações de Dados na Tabela Dinâmica do Excel.
- Como substituir células em branco por zeros em tabelas dinâmicas do Excel.
- Como adicionar e usar campos calculados de uma tabela dinâmica do Excel.
- Como atualizar a tabela dinâmica no Excel.
