Trabalhando com Tabelas Dinâmicas no Excel

Exemplo do mundo real 

Neste tutorial, vamos usar um conjunto de dados sobre vendas globais de bicicletas de 2011 a 2016. As informações demográficas de clientes e produtos estão organizadas com colunas de lucro, custo e receita. Você vai criar uma Tabela Dinâmica a partir desse dataset para analisar os dados. 

Os dados estão disponíveis aqui para você acompanhar. 

Revisando os dados 

Vamos começar conferindo uma amostra dos dados deste tutorial. Temos uma tabela com 11 colunas, incluindo tipos de campo de data, texto e numéricos. A partir desse subconjunto, já dá para ver várias maneiras de trabalhar com os dados para fazer a análise e encontrar bons insights. 

Criando sua primeira Tabela Dinâmica 

Para criar sua primeira Tabela Dinâmica, selecione a tabela que você quer usar, vá até a guia Inserir e escolha Tabela Dinâmica nas opções abaixo.

Depois disso, será exibida uma janela perguntando se você quer alterar o intervalo de dados para a Tabela Dinâmica e se ela deve ser criada em uma nova planilha ou na planilha existente. Para este tutorial, as opções padrão já atendem. Clique em OK para criar a Tabela Dinâmica em uma nova planilha. 

Nossa nova planilha mostra a estrutura da Tabela Dinâmica criada automaticamente. No lado esquerdo da tela, você vê o contorno de onde a Tabela Dinâmica aparecerá quando for montada. No lado direito, está o painel de campos da Tabela Dinâmica, onde você fará a maior parte do trabalho. 

Quando seus dados já estão em uma tabela do Excel, a Tabela Dinâmica inclui automaticamente tudo dentro dela. Mas é importante notar que, se seus dados não estiverem em um objeto de tabela, você pode selecionar manualmente o intervalo desejado, destacando os dados e criando a Tabela Dinâmica do mesmo jeito. 

Adicionando seus dados 

Vamos assumir que cada linha está associada a um cliente individual; portanto, não há pedidos repetidos. Essa é uma visão bastante simplificada do conjunto de dados, mas como não há um identificador único de cliente, fica mais fácil para o tutorial supor um único cliente por linha. 

Primeiro, vamos trazer um campo de categoria para a seção de Linhas. Vamos usar Country como categoria; basta encontrá-lo na lista de campos do editor da Tabela Dinâmica e arrastá-lo para Linhas. Em seguida, precisamos escolher o valor que queremos avaliar — neste caso, queremos contar clientes, mas como não temos um campo de ID, podemos usar o campo Customer_Age, arrastando-o para a seção Valores. Por padrão, a agregação é SOMA, mas precisamos alterar para CONTAGEM. Para isso, clique com o botão direito em Customer_Age em Valores, selecione Configurações de Campo, mude Resumir de Soma para Contagem e clique em OK.

Ótimo! Agora vemos a contagem de clientes por Country de onde fizeram o pedido. Isso já é útil, mas vamos organizar por Country com mais clientes. Para isso, clique com o botão direito em um valor na coluna Count of Customer_Age, selecione Classificar e, depois, Classificar do maior para o menor. Podemos ver que os Estados Unidos tiveram mais clientes, com 39.206.

Avançando mais um pouco 

Agora que entendemos como configurar uma Tabela Dinâmica básica, vamos dar um passo além e adicionar filtros e colunas, além de atualizar a Tabela Dinâmica. Remova as linhas e valores adicionados no passo anterior clicando com o botão direito e escolhendo Remover campo. Assim, temos uma tela em branco para trabalhar. 

Nesta Tabela Dinâmica, vamos responder: “Em 2015, qual trimestre gerou mais receita e a que produto/subcategoria ele pertence?” 

Comece adicionando as linhas pelas quais queremos analisar: no nosso caso, Product_Category e Sub_Category. Para as colunas, queremos ver a distribuição por Quarters (trimestres). Mas não temos uma coluna Quarters… sem problema: o Excel detectou automaticamente um campo de data; ao arrastar Date para Colunas, dois novos campos aparecem: Years e Quarters. Como não precisamos visualizar por data individual, podemos remover esse campo de Colunas. 

Como queremos filtrar para 2015, podemos mover o campo Years para a seção Filtros. Sua Tabela Dinâmica deve ficar assim:

Com a estrutura pronta, podemos pré-selecionar o ano no filtro clicando na seta de Years e escolhendo 2015. A Tabela Dinâmica fica assim: 

No momento, nossos pontos de dados estão em nível unitário, então o preço unitário e o custo unitário se referem a um único item. Queremos resumir corretamente as informações, então precisamos voltar à tabela de dados para criar um cálculo de receita por linha.

Na guia de dados, vamos criar uma nova coluna calculada para obter a receita por linha. Para isso, precisamos multiplicar Order Quantity por Unit Price. 

Para adicionar a nova coluna, volte à planilha de dados e, ao lado da coluna Unit Price, na célula L2, insira o novo cálculo. A fórmula será:

[@[Order_Quantity]]*[@[Unit_Price]]

Agora temos uma nova coluna que podemos usar na Tabela Dinâmica. Volte para a planilha da Tabela Dinâmica e note que o campo ainda não aparece. Para atualizar, clique com o botão direito em qualquer área da Tabela Dinâmica e selecione Atualizar.

A nova coluna foi adicionada e podemos arrastá-la para a seção Valores. Se quiser criar mais colunas calculadas, use nosso Excel Cheatsheet para ver outros tipos de cálculos que você pode aproveitar. 

Parece um pouco bagunçado, então vamos limpar removendo todos os totais gerais. Vá até a guia Design, abra o menu Grand Totals e selecione Off for Rows and Columns. Depois, vamos formatar os números como moeda. Selecione os valores na tabela e, na guia Início, altere a formatação para Moeda. 

Pronto, agora temos nossa Tabela Dinâmica finalizada. Vemos que Bikes gerou a maior receita em 2015, especialmente no quarto trimestre (Q4). Além disso, entre as bicicletas vendidas pela empresa, Road Bikes são as mais populares e geram a maior receita para a organização.

Vá além!

Este tutorial foi uma boa introdução às Tabelas Dinâmicas no Excel; se você conseguiu acompanhar com tranquilidade, parabéns! Se travou em algum ponto, o arquivo de solução está aqui. 

Tente experimentar com um conjunto de dados mais complexo, aplicando diferentes atributos, mexendo neles e vendo se você consegue tirar conclusões a partir dos dados.

Se quiser testar ainda mais suas habilidades no Excel, vale conferir nosso curso Data Analysis in Excel, caso ainda não tenha feito.