Excel: utilizando tabelas dinâmicas e gráficos dinâmicos

Conceitos do Excel - Apresentação

Olá! Eu sou o Roberto Sabino, instrutor na Alura, e estou aqui para falar sobre tabelas dinâmicas, gráficos dinâmicos e dashboards.

Audiodescrição: Roberto se descreve como um homem de pele clara, com cabelo e barba escuros, um pouco grisalhos, e olhos castanhos. Ele veste uma camiseta escura totalmente lisa e está sentado em frente a uma parede branca iluminada em gradiente azul.

Para quem é este curso?

Este curso é destinado a quem precisa analisar e apresentar dados, além de ser útil para quem deseja elaborar um dashboard mais sofisticado ou para quem está iniciando no trabalho com tabelas dinâmicas e necessita de auxílio.

Nós preparamos um curso precisamente focado nisso!

O que vamos aprender?

Vamos iniciar observando os conceitos de tabela dinâmica, como e onde usamos tabelas dinâmicas, com exemplos mais simples. No decorrer do curso, progrediremos até alcançar exemplos mais complexos, como fazer uma tabela dinâmica com duas origens diferentes, duas tabelas distintas que darão origem a uma única tabela dinâmica.

Para isso, faremos uso do modelo de dados, entenderemos um pouco sobre o Power Pivot e também veremos o Power Query, para compreender como consultar dados.

Em seguida, vamos entender como aplicar esses dados, com os quais já trabalhamos, consultamos, e estabelecemos relação, em um dashboard. Este é o momento de apresentar os dados para a nossa clientela ou para quem as pessoas responsáveis pela planilha exigem.

Lembre-se: este curso foi projetado para proporcionar uma aprendizagem ativa. Assim, você terá contato com o conhecimento a todo momento, vai interagir com os conteúdos e poderá usar, por exemplo, o fórum, para tirar suas dúvidas ou responder perguntas de outras pessoas estudantes.

Vamos começar de imediato com a prática, então se prepare para desde a primeira aula começar a trabalhar com tabelas dinâmicas. Aguardamos você!

Conceitos do Excel - Noções de tabela dinâmica

Tudo pronto para começarmos mais uma etapa da nossa jornada aprendendo Excel? Dessa vez, vamos falar sobre tabelas dinâmicas, um recurso que pode ser bastante útil no cotidiano. Algumas pessoas podem achar um pouco complicado inicialmente, mas vamos abordar o tema de forma tranquila.

Noções de tabela dinâmica

Continuaremos trabalhando em cima do mesmo projeto que temos trabalhado, a cliente Meteora, que está desenvolvendo um controle de vendas para seu e-commerce.

Para quem está seguindo a formação, continuamos do mesmo jeito. E para quem ainda não fez a formação, também é possível entender, caso você queira aprender apenas sobre tabelas dinâmicas.

Vamos começar com uma planilha que contém um cadastro de produtos. Esse cadastro tem várias opções de produtos, com seus códigos à esquerda. Depois temos os vendedores e um controle de botões que permite navegar pelas planilhas.

Temos também a lista de vendas. Nessa planilha específica, algumas coisas estão duplicadas. Deixamos assim por enquanto, mas depois vamos limpar isso, o que também é importante.

Além disso, temos algumas consultas na planilha correspondente. Deixamos escondido o modelo de dashboard, porque vamos trabalhar nele mais para frente. Nós vamos explorar o dashboard também utilizando as tabelas dinâmicas.

Desta vez, queremos mostrar o que é uma tabela dinâmica e seus usos, pois sabemos que muitas pessoas têm curiosidade. Nós vamos fazer uma tabela dinâmica em cima dos dados da planilha Produtos, apenas para começar.

Criando uma tabela dinâmica

Essa tabela dinâmica vai ser feita em cima de outra tabela. Este é um ponto que pode gerar confusão. A planilha Produtos contém uma tabela. Como sabemos disso? Conseguimos visualizar a guia "Design da Tabela" na parte superior do Excel, ou seja, ela está formatada como tabela. Isso não é uma tabela dinâmica, é um recurso chamado "Tabela".

Vamos analisar juntos: essa tabela só está em um pedaço da planilha. Por exemplo, clicando acima da primeira linha da tabela, a guia "Design da Tabela" some, porque o intervalo que contém esta célula não faz parte da tabela. A tabela começa em B6.

Então, o que é a tabela? Ela corresponde ao intervalo desde os rótulos de dados na parte superior, os cabeçalhos de dados, até a última linha da tabela.

Podemos criar tabelas dinâmicas em cima de tabelas. A estratégia de construir uma tabela e, em seguida, uma tabela dinâmica sobre ela pode ser uma boa prática. Compreenderemos as diferenças ao longo do curso.

Para criar uma tabela dinâmica, podemos clicar em qualquer local da tabela (não vamos selecionar um espaço, basta clicar em cima de uma célula qualquer), ir na aba "Inserir", e selecionar a opção "Tabela Dinâmica".

Há duas formas de acessá-la: clicando na parte de cima do botão, que leva ao padrão, ou clicando na parte de baixo para abrir as opções. Quando são abertas as opções, o Excel oferece a possibilidade de criar uma tabela dinâmica:

• Em cima de uma tabela ou intervalo ("Da Tabela/Intervalo");
• Em cima de uma fonte de dados externos ("Da Fonte de Dados Externos");
• Ou em cima de algo do Power BI ("Do Power BI (Office Resolve)"), se você possui o Power BI já conectado à mesma conta que usa no Excel.

Não precisa se preocupar, pois não vamos mexer com Power BI neste curso. Também não estamos trabalhando com dados externos, mas sim com uma tabela ou intervalo.

Isso é equivalente a clicar no topo do botão. Portanto, se clicarmos no topo, abrirá uma caixa de diálogo. Da mesma forma, quando estamos dentro da tabela ou do intervalo, ao clicar em "Tabela Dinâmica > Da Tabela/Intervalo" a mesma caixa de diálogo se abre.

Nessa caixa de diálogo, é solicitada a tabela ou o intervalo da tabela dinâmica. Nesse caso, é a tabela TB_Produtos. Porém, o que é TB_Produtos?

Vamos revisitar: clicando sobre a tabela e acessando a guia "Design da Tabela", identificamos que o nome da tabela é TB_Produtos.

Ao abrir a janela de criação da tabela dinâmica, o Excel marca apenas a parte inferior, excluindo os rótulos de dados na parte superior. Não se preocupe, pois o Excel consegue identificar que isso é uma tabela, onde estão os dados e como fazer a tabela dinâmica.

Na caixa de diálogo, há outra pergunta: se queremos criar a tabela dinâmica em uma nova planilha ou em uma planilha existente. Primeiro, vamos criar em uma nova planilha, assim, seguimos o padrão; depois analisamos os detalhes, e também vamos trabalhar com a outra opção ao longo do curso.

Também devemos escolher se desejamos adicionar esses dados ao modelo de dados. Neste momento, não, mas depois vamos dar uma olhada nisso.

Temos aqui na plataforma um curso sobre Business Intelligence (Inteligência de Negócios) com Excel, onde vamos aprofundar todos esses conteúdos, porque isso está relacionado com Business Intelligence.

Mas neste curso, vamos sim ver o que é o modelo de dados, como podemos usar o modelo de dados, e aprenderemos, por exemplo, a fazer uma tabela dinâmica com duas tabelas diferentes, algo que você pode precisar fazer no seu dia a dia.

Então, clicamos em "OK" e ele abrirá uma nova planilha chamada "Planilha2", e dentro dela, vai criar um retângulo que será ocupado pela tabela dinâmica.

Se você nunca usou tabela dinâmica, poderia dizer: "Isso aqui não serve para nada, apenas aparece uma planilha em branco, e eu não sei como usar isso". Isso pode ser ainda mais confuso se aparecer sem a aba lateral "Campos da Tabela Dinâmica".

Vamos ver algumas coisas básicas, mas que são importantes. Primeiro, se acessarmos a opção "Mostrar", temos a subopção "Lista de Campos". Se a selecionarmos, abriremos um seletor de campos, uma lista de campos, um painel, que é esse que aparece à direita na tela. É nele que vamos determinar como a dinâmica funcionará.

No interior da planilha, do lado esquerdo, temos a área onde ele vai montar a tabela dinâmica.

Afinal, o que é essa tabela dinâmica? Imaginemos que pegamos todos os dados que tínhamos em produtos, misturamos tudo, jogamos em um lugar, e agora podemos reorganizar esses dados para fazer uma análise de dados, ter visualizações diferentes, criar painéis de controle (dashboards), criar gráficos dinâmicos e várias outras coisas. Normalmente, fazemos a análise de dados.

Então, vamos acessar a nossa tabela dinâmica e vamos escolher, por exemplo, que queremos ver os produtos e a quantidade em estoque. Para isso, marcaremos as caixas de seleção "Produtos" e "Estoque" na aba do seletor de campos.

Neste caso, reorganizamos e ele está mostrando uma coluna com todos os produtos que temos e outra com a quantidade em estoque de cada um deles.

Pegamos uma planilha mais simples, como se tivéssemos escolhido duas colunas, mas também podemos resumir esses dados. Por exemplo, se não quiséssemos todos os produtos, e apenas as categorias, vamos marcar os itens "Categoria" e "Estoque".

Neste caso, a tabela dinâmica realizou automaticamente um resumo de dados.

Ela somou todas as quantidades dos produtos contidos em cada uma das categorias. Se nós quiséssemos fazer a soma, provavelmente faríamos uma soma condicional (SOMASE), que nós já aprendemos a fazer. Contudo, a tabela dinâmica fez isso automaticamente, utilizando as categorias "Vestuário", "Acessórios" e "Calçado" e já agrupou na coluna "Soma de Estoque" a quantidade em estoque somada.

A tabela dinâmica tem essa finalidade, facilitar a nossa vida ao fazer resumos e agrupamentos de dados. Ela pega dados, como as quantidades, e realiza a soma ou a média. Vamos fazer isso?

Nós vamos aprender com calma posteriormente, mas vamos dar uma olhada aqui para finalizar essa primeira visão de tabela dinâmica. Na aba do seletor de campos, temos quatro quadrantes abaixo da lista. Na esquerda superior, a seção "Filtro", à direita desta, a seção "Colunas", na esquerda inferior, a seção "Linhas" alocando a coluna "Categoria" e à direita dela, a seção "Valores" alocando a coluna "Soma de Estoque".

Conforme movemos os campos de um quadrante para outro, eles irão mudar. Poderíamos ter, por exemplo, o item "Categoria" na seção "Colunas" e colocar alguma outra coisa em "Linhas". Isso é um pouco do que nós vamos aprender ao longo deste curso.

É exatamente em "Valores" que os dados estão sendo agrupados. É ali onde se realiza o que queremos ver. Poderíamos clicar no botão "Soma de Estoque" e alterar para a opção "Configurações do Campo de Valor", o que abrirá uma janela de mesmo nome.

Na seção "Nome Personalizado", podemos mudar o nome, mas o que interessa é que na seção "Resumir campo de valor por" que possui uma lista suspensa, podemos mudar como os campos estão sendo agrupados. Qual é o cálculo usado para resumir esses dados? Isso é um resumo de dados.

Atualmente, a opção selecionada é "Soma". Poderia ser, por exemplo, uma média. Para isso, selecionaremos "Média". Com isso, teremos a média de quantidades por produto.

Neste caso, talvez nem faça sentido usar a média, mas temos uma média de produtos em estoque por categoria.

Vamos voltar para a opção "Soma", acessando novamente a janela de configuração do campo de valor. Esse formato faz mais sentido.

Conseguimos entender, então, o início do funcionamento da tabela dinâmica. Mas ainda tem muitas coisas para nós vermos. Como organizar essa tabela dinâmica? Quais opções podemos mudar em uma tabela dinâmica? Nós continuaremos isso, passo a passo, a partir do próximo vídeo.

Conceitos do Excel - Opções da tabela dinâmica

Vamos explorar alguns detalhes que, apesar de parecerem simples, podem nos auxiliar no dia a dia. Na primeira aula, vamos começar de maneira tranquila, adicionando depois algumas informações interessantes para aprofundar nosso conhecimento.

Atualizando a Tabela Dinâmica

Como exemplo, imagine que criamos uma tabela dinâmica e alteramos os dados de um item específico. Vamos usar a ação de alterar a quantidade em um dos produtos da tabela "Produtos".

Para ilustrar, modificaremos a quantidade do produto de vestuário "PR004" para um valor absurdo, digamos, 10.000. O que aconteceu?

Voltando à aba "Planilha2" com a tabela dinâmica, aparentemente, nada mudou. Por que não vemos os 10.000 que adicionamos? De fato, se possuíssemos um estoque com um total de 10.000 itens, isso deveria estar refletido nela. E estará, mas precisamos atualizar a tabela dinâmica manualmente, pois ela não é atualizada automaticamente para evitar sobrecarga no processamento.

Para atualizar a tabela, podemos clicar em uma célula dela com o botão direito do mouse e selecionar "Atualizar" ou acessando as duas guias específicas para manipulação da tabela dinâmica, localizadas na barra de menus superior:

• "Design" e
• "Análise de Tabela Dinâmica".

Na primeira, podemos alterar mais a parte visual da tabela e na segunda, encontramos ferramentas importantes. Vamos clicar na segunda e verificar que em seu interior também temos a opção "Atualizar".

Se clicarmos nela, veremos também as subopções "Atualizar" e "Atualizar tudo". Esta última irá atualizar todas as tabelas dinâmicas usadas na aba atual. Em nosso caso, ambas opções de atualização teriam o mesmo resultado. Ao atualizar, a tabela exibirá a quantidade correta de itens na categoria "Vestuário": 11457.

Porém, há aspectos importantes a se considerar durante a atualização. Vamos ilustrar um desses aspectos. Vamos aumentar a largura da coluna e a fonte da tabela dinâmica.

Voltaremos à tabela de produtos e alterarmos a quantidade do item "PR004" novamente, dessa vez para 25. Sabemos que precisamos atualizar a tabela após a modificação, então voltamos à tabela dinâmica e clicamos em "Atualizar". O que irá acontecer?

A tabela dinâmica atualizará também o tamanho da coluna, diminuindo-a para o padrão. Ela fará isso cada vez que for atualizada. Isso pode não ser um problema, mas, em alguns contextos, pode causar inconvenientes com o ajuste das dimensões ou a estética da tabela.

Para evitar isso, temos algumas configurações na tabela dinâmica.

Configurações de Tabela Dinâmica

Podemos acessar essas configurações na guia "Análise de Tabela Dinâmica", em cujo canto esquerdo encontramos a opção "Tabela dinâmica". Apesar do nome redundante, nela estão as configurações mais básicas da tabela dinâmica.

Clicando nela, podemos alterar o nome da tabela dinâmica. Vamos chamá-la de "TBDN_Produtos", onde "TBDN" significa tabela dinâmica.

Abaixo do campo de nome, temos o botão "Opções". Se clicarmos nele, exibiremos uma janela na qual podemos modificar a tabela dinâmica. Nestas opções, por exemplo, temos uma marcada por padrão: "Ajustar automaticamente a largura das colunas ao atualizar". Podemos desmarcar essa opção se não quisermos que a largura das colunas seja ajustada com cada atualização.

Após desmarcarmos e clicarmos em "OK", a janela sumirá. Com isso, se refazermos a largura da coluna e atualizarmos a tabela, nada ocorrerá. A largura será mantida da mesma forma e não teremos mais o problema de continuar retornando.

É importante entender que há várias funções a serem exploradas e, com o tempo e conforme a necessidade, podemos buscá-las.

Nós vamos trabalhar com algumas dessas funções, mas sempre vale a pena explorar. Existem vários componentes a serem utilizados, nós usaremos alguns deles, mas, posteriormente, é interessante revê-los no seu cotidiano. Na tabela dinâmica que estiver sendo elaborada, você pode ajustar essas opções.

Para não manter a fonte com tamanho estranho, vamos retornar o seu tamanho para 12. Digamos que queremos aplicar algum formato nos números da coluna "Soma de Estoque". Suponhamos que, em vez de trabalhar com quantidades, queremos trabalhar com o preço. Como podemos alterar a minha tabela dinâmica?

Aqui reside a grande vantagem da tabela dinâmica. Se precisarmos fazer uma mudança, podemos simplesmente, acessar a aba do seletor de campos, na lateral direita, marcar a caixa de seleção de algum campo na lista de campos disponíveis e adicionar informações, ou desmarcar e remover informações.

Logo, se desmarcarmos "Estoque" e marcarmos "Preço Unitário", por exemplo, nós teremos na tabela a coluna "Soma de Preço Unitário", no lugar de "Soma de Quantidades", e fizemos isso de forma muito rápida.

Mas agora, queremos formatar isso como moeda. Antes de mostrar a opção de tabela dinâmica, poderíamos selecionar os campos numéricos, acessar o menu "Página Inicial", o botão com ícone de cifrão e selecionar um formato de moeda. Ao atualizar, ele mantém o formato, não é um problema.

Vamos retornar ao estado anterior para ver uma opção que vai ao campo da tabela dinâmica. A vantagem é que sempre que utilizarmos esse campo, ele será exibido nesse formato.

A opção "Configurações do Campo" está no menu "Análise de Tabela Dinâmica", à direita da opção "Tabela Dinâmica".

Devemos observar que as configurações do campo alteram conforme a seleção dos campos. Por exemplo, se clicarmos na célula "Vestuário", estaremos falando da categoria, ela é exibida na seção "Campo ativo" acima da opção "Configurações do campo". Se mudarmos a categoria, ela aparecerá no lugar de "Vestuário".

Ao clicar em configurações do campo, ele exibirá uma janela com todas as opções do campo selecionado. Na parte de baixo, ha o botão "Formato de Número". Clicando nele, veremos outra janela com duas seções. Na esquerda, podemos formatar como moeda selecionando essa opção.

Na seção à direita, veremos a opção "Símbolo" na qual selecionaremos o símbolo de real e definiremos como moeda. Ou, se preferir usar a opção "Contábil" na lista esquerda, dependerá de como você está formatando a sua planilha.

Ao formatar, ele começará a usar essa opção sempre que você utilizar esse campo. Ou seja, ao formatar o campo, ele entenderá que esse campo deve permanecer com esse formato.

Recapitulando

Quais são as regras básicas de tabelas dinâmicas que precisamos lembrar?

Atualizar é fundamental, pois sem as atualizações, podemos estar analisando dados incorretos. Em cursos anteriores, vimos que é possível criar um VBA para automatizar esse processo.

Podemos fazer isso aqui também? Sim, provavelmente faremos. Mas atenção, se tiver muitas tabelas dinâmicas, não é recomendável atualizar todas, pois a planilha pode ficar pesada. Por isso, o padrão da tabela dinâmica não é atualizar.

Outro ponto importante é definir as configurações, como a largura da coluna, por exemplo. Não se esqueça, confira em "Análise de Tabela Dinâmica > Tabela Dinâmica > Opções", lá você encontrará diversas opções interessantes para ajustar no cotidiano.

Por fim, não deixe de verificar as configurações do campo, elas serão muito úteis ao trabalhar com campos de formatos diferentes.

Neste ponto, concluímos o básico sobre tabelas dinâmicas e tudo que é necessário para começar a trabalhar com elas. A seguir, analisaremos detalhadamente os quadrantes (filtros, colunas, linhas e valores). Como eles funcionam, para que servem e como vamos utilizá-los? Veremos isso no próximo vídeo.

Sobre o curso Excel: utilizando tabelas dinâmicas e gráficos dinâmicos

O curso Excel: utilizando tabelas dinâmicas e gráficos dinâmicos possui 142 minutos de vídeos, em um total de 44 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:

• Conceitos do Excel
• Origens dos Dados
• Gráficos dinâmicos
• Modelo de dados
• Finalizando o dashboard