BI com Excel: trabalhando com tabelas dinâmicas com Power Pivot

Relembrando a tabela dinâmica - Apresentação

Olá! Meu nome é Roberto Sabino, sou instrutor da Alura e te desejo as boas-vindas à mais um curso da formação de Business Intelligence com Excel!

Roberto Sabino é um homem de pele clara, cabelos curtos ondulados na cor castanho escuro e repartidos na lateral direita. Possui barba e bigode na mesma cor do cabelo, mas o cavanhaque é grisalho. Veste uma camiseta lisa na cor preta e está sentado sobre uma cadeira preta acolchoada. À sua frente, um microfone na altura da boca. Ao fundo, uma parede lisa, na cor verde.

O foco, agora, é Power Pivot! Neste curso, aprenderemos a juntar duas tabelas. Temos, portanto, uma tabela de produtos - que a Clara utiliza em sua cafeteria; uma tabela de entradas - que corresponde às compras feitas para a cafeteria, e as saídas - que são as vendas.

Note que, nas tabelas de entradas e saídas, não colocamos nome, custo ou valor do produto, mas somente o código. Isso, porque, este código deve relacionar todos os dados. Aprenderemos, portanto, a fazer análises dinâmicas como, por exemplo, saber a quantidade que vendemos ou compramos de determinado produto e o valor total desta compra ou venda relacionada ao produto.

Desta forma, conseguimos relacionar a tabela de produtos com as tabelas de entradas e saídas por meio do código, o que nos possibilita fazer uma modelagem de dados mais robusta. Para fazer isso, utilizaremos o Power Pivot, ferramenta do Excel relativamente simples que nos permite subir, importar e criar tabelas dinâmicas, entre outras funcionalidades.

Lembre-se que este curso faz parte de uma formação, então temos pré-requisitos necessários para melhor aproveitá-lo, são eles:

• BI com Excel: crie bancos de dados com planilhas;
• BI com Excel: criando um Dashboard sem complicação.

Vamos lá?

Relembrando a tabela dinâmica - Conceito de tabela dinâmica

O intuito deste curso é aprofundar o uso de tabela dinâmica, mas antes precisamos relembrar brevemente os fundamentos.

Conceito de tabela dinâmica

Uma tabela dinâmica pode ser entendida como um grande repositório de dados que nos ajuda a calcular, resumir e analisar os dados. Normalmente, o objetivo é fazer análise de dados ou representação das principais informações, ou seja, criar dashboards.

Há alguns pontos que nos ajudam a entender porque as tabelas dinâmicas são tão utilizadas no mercado:

• facilidade no uso;
• criação rápida de relatórios;
• apresentação clara e objetiva dos dados;
• sem necessidade de criar fórmulas complexas.

Sobre as fórmulas, precisamos saber que há quem prefira utilizar fórmulas e funções, enquanto há quem prefira utilizar o VBA ou, até mesmo, a tabela dinâmica. Neste caso, cabe a cada um escolher qual a melhor forma para se trabalhar.

No curso anterior, fizemos um dashboard. Agora, veremos uma maneira diferentes de chegar no mesmo resultado. Basicamente, primeiro aprenderemos a usar tabela dinâmica e manipular dados.

Principais elementos da tabela dinâmica

• Campos: títulos dos relatórios;
• Filtros: verificar informações específicas;
• Rótulos de linhas: informações nas linhas;
• Rótulos de coluna: informações nas colunas;
• Valores: operação matemáticas.

Esses são os elementos que precisamos aprender para saber trabalhar com tabelas dinâmicas.

Vamos ao Excel relembrar como é nossa planilha!

Visualização dos 5 primeiros registros.

Note que foi feita uma limpeza e retirados os ajustes do dashboard, ficando somente a base de dados crua. Tendo isso em vista, não é recomendado que você utilize a planilha do curso anterior, mas sim que faça o download da tabela que foi disponibilizada neste curso.

O primeiro questionamento que surge é: como fazer uma tabela dinâmica?

Primeiramente, nosso arquivo trata-se de uma tabela, algo com o qual já aprendemos a trabalhar. A partir do momento que começamos a utilizá-la, já temos algumas facilidades para criar uma tabela dinâmica, pois em Inserir nos é dada esta opção.

Se tivermos dentro de uma tabela, não precisamos selecioná-la por inteiro. Podemos, por exemplo, só clicar em uma das células e ir em Inserir > Tabela Dinâmica que automaticamente nossa tabela será reconhecida. Ao fazê-lo, note que já aparece TB_Produtos como intervalo.

Nas opções de Tabela Dinâmica, temos "De Tabela/Intervalo", que funciona da mesma forma e automaticamente reconhece nossa tabela. Já na opção "De Fonte de Dados Externos", podemos escolher uma conexão e a tabela de nossa preferência. A diferença entre essas duas alternativas é que, quando temos uma tabela, ela funciona como uma base de dados em alguns lugares do Excel, o que facilita outras questões.

Ao optarmos por uma fonte externa, por exemplo, temos a opção de criar uma tabela dinâmica a partir de outra tabela que não a que estamos no momento. Mas a maioria dos usuários de tabelas dinâmicas opta por clicar diretamente na opção Tabela Dinâmica que é criada a partir da tabela que estamos atualmente.

Vamos fazer uma tabela dinâmica para darmos início à prática. Portanto, clicaremos em Inserir > Tabela Dinâmica e note que, na nova janela, TB_Produtos deve aparecer como intervalo. Ainda nesta janela, optamos por colocar a tabela em uma nova planilha e clicamos em "OK".

Pronto! Nossa tabela dinâmica foi criada! À direita, temos um seletor de campos, e à esquerda, o que aparece na tabela em si. Basta, portanto, selecionar o campo desejado e os dados serão mostrados. Mas este é apenas o passo inicial, seguiremos avançando no vídeo seguinte!

Relembrando a tabela dinâmica - Seletor de campos

Tabela dinâmica nada mais é do que os dados originais disponíveis onde possamos reorganizá-los. Por exemplo, em nossa tabela dinâmica conseguimos mostrar os produtos e o estoque mínimo ao selecionar estes campos. Mas note que não é mostrado o valor específico, e sim uma soma de estoque mínimo. Ou seja, a tabela dinâmica resume os dados.

Veja bem, temos os dados à direita e a visualização deles à esquerda. A questão é sabermos quais dados queremos mostrar e como, pois, ainda na aba à direita, abaixo dos campos, temos 4 quadrantes, filtros, colunas, linhas e valores, que servem para reorganizarmos as informações.

Note que ao selecionar "Produto", por exemplo, ele aparece no quadrante de linhas. Mas se quiséssemos que fosse um filtro, bastaria arrastá-lo de "Linhas" para "Filtros" e teríamos um filtro onde podemos selecionar um produto específico para que seus dados sejam mostrados, já que em "Linhas", todos os produtos são mostrados como uma listagem.

Vamos manter somente a seleção do campo "Produto". Ao fazê-lo, note que os quadrantes "Colunas" e "Valores" estão vazios. Se arrastássemos "Custo Unitário" para "Colunas", não faria sentido, então podemos colocá-lo como "Valores". Ou seja, precisamos saber como reorganizar os dados da maneira necessária.

A tabela dinâmica pode vir em uma nova planilha ou em uma planilha já existente. Vamos renomear esta tabela dinâmica como "Dinâmica" e criar uma para a planilha de entradas da seguinte forma: Inserir > Tabela Dinâmica, selecionar TB_Entradas como intervalo, optar por "Planilha Existente" e selecionar a tabela que criamos chamada "Dinâmica".

Para selecionar esta planilha existente, clicamos na opção "Local" e, em seguida, na aba de "Dinâmica", na barra inferior. Em seguida, precisamos, também, selecionar o local desta planilha em que a nova será colocada como, por exemplo, a célula D3. Ao clicar nesta célula, note que "Local" foi preenchido da seguinte maneira:

Dinâmica!$D$3

Por fim, clicamos em "OK".

Agora, esta nova tabela dinâmica foi criada a partir da célula D3 em "Dinâmica". Com ela selecionada, escolheremos os campos "Produto" e "Valor da Compra".

Perceba que temos duas tabelas dinâmicas na mesma planilha: uma advinda de TB_Produtos, e outra, de TB_Entradas. Desta forma, quando clicamos em uma delas, o seletor de campos passa a corresponder à tabela selecionada.

Para esta nova tabela, após selecionarmos os campos, temos o valor somado das compras de determinado produto. Isso acontece porque na tabela de entradas, usada como base, temos diversas compras de um mesmo produto, então na tabela dinâmica nos é trazida esta soma.

É possível fazer várias tabelas dinâmicas em uma mesma planilha, mas isso normalmente não é recomendado porque conforme acrescermos as tabelas, elas podem se encontrar dentro do campo disposto e isso deve gerar um problema de espaço. Ou seja, devemos ter o cuidado de evitar colocar, em uma mesma planilha, tabelas dinâmicas que possam se encostar devido ao tamanho.

Outro detalhe é que, com os dados de soma, fica claro que não se trata somente de uma compra de determinado produto, mas sim de várias compras deste produto. Portanto, está ficando mais compreensível que a tabela dinâmica são dados reorganizados em um formato que nos permite escolher através dos 4 quadrantes (filtros, colunas, linhas e valores). Desta forma, ao organizarmos os campos nestes quadrantes, organizamos, também, a tabela dinâmica.

Sobre o curso BI com Excel: trabalhando com tabelas dinâmicas com Power Pivot

O curso BI com Excel: trabalhando com tabelas dinâmicas com Power Pivot possui 121 minutos de vídeos, em um total de 37 atividades. Gostou? Conheça nossos outros cursos de Excel em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Relembrando a tabela dinâmica
• Opções de tabela dinâmica
• Conhecendo Power Pivot
• Usando o modelo de dados
• Finalizando as tabelas