Para baixar a planilha utilizada nessa aula preencha seu e-mail abaixo:
A Tabela Dinâmica é uma tabela que irá conter todos os dados de uma tabela completa com dados, no entanto, como o próprio nome diz, ela é dinâmica. Isso quer dizer que a medida em que o usuário modifica algum critério, filtro, expande uma categoria, entre outros, essas modificações serão atualizadas automaticamente na tabela para facilitar a visualização dos dados a serem analisados.
A Tabela Dinâmica é utilizada quando o usuário pretende visualizar os dados de uma forma mais dinâmica, podendo expandir e recolher categorias, modificar o que será apresentado na tabela, modificar o formato em que os dados aparecem na tabela… E tudo isso é feito de maneira rápida e bem intuitiva, basta o usuário decidir quais dados serão representados naquele momento.
Para utilizar a essa ferramenta precisamos de uma tabela comum com os dados a serem representados e só depois iremos criar a Tabela Dinâmica. Neste caso iremos utilizar uma tabela de compras com o nome do comprador, produto comprado, preço unitário do produto, quantidade comprada e o preço total da compra.
Podemos ver que temos compradores repetidos, ou seja, a mesma pessoa fez mais de uma compra, temos preços de produtos diferentes e quantidades diferentes. Desta forma podemos analisar como cada um desses dados irão aparecer na nossa Tabela Dinâmica.
Obs. Recomendo que antes de inserir a tabela dinâmica, formatar como tabela, isso ajuda depois a atualizar as informações. Para formatar como tabela clique em qualquer célula da tabela -> use o atalho control+t para selecionar toda a tabela -> na guia Página inicial clique em Formatar como tabela. Escolha a opção minha tabela tem cabeçalho e dê ok.
O próximo passo é criar a tabela dinâmica. Para isso, iremos acessar a guia Inserir e em seguida clicar na opção Tabela Dinâmica. Feito isso irá aparecer uma nova janela com o nome de Criar Tabela Dinâmica, nesta janela iremos escolher onde estão os nossos dados, no caso $B$4:$F$28 da aba Tabela Dinâmica, e decidir se vamos ou não criar a Tabela em uma Nova Planilha, para o exemplo selecionamos a opção de Nova Planilha.
Vale lembrar que o Excel chama de planilha o que conhecemos como aba. Caso queira que a nova tabela seja exibida na aba atual, basta selecionar a opção “Planilha Existente” e clicar na célula desejada.
Feito isso será aberta uma nova aba na planilha com alguns pontos importantes:
Clicando na guia Analisar, que só aparece quando a tabela dinâmica está selecionada, em Verde temos as opções que devem estar marcadas para que apareça a Lista de Campos, Botões +/- (que são para expandir ou recolher alguma das categorias que temos) e Cabeçalhos dos Campos (que são nossas categorias);
Em Vermelho temos os Campos da Tabela Dinâmica, é aqui que iremos trabalhar com as categorias que temos para construir a Tabela Dinâmica;
Em Azul temos os Cabeçalhos/Categorias dos dados da tabela que utilizamos para gerar a Tabela Dinâmica;
E por fim em Amarelo temos as áreas em que colocaremos nossos cabeçalhos/categorias para criar a tabela dinâmica.
Para colocarmos nossas categorias nos campos desejados podemos clicar na caixa de cada uma ou, a melhor opção, arrastá-los para um dos quatro quadrantes da seleção Amarela.
Vamos então arrastar Nome, Produto e Preço para Linhas enquanto Quantidade e Total arrastamos para Valores. Feito isso teremos a seguinte tabela.
Podemos ver que a ordem que colocamos os dados na seção de Linhas é a mesma ordem em que eles aparecem na tabela. Então primeiro aparecem os nomes, em seguida os produtos e por fim o preço de cada um dos produtos.
Já na parte de valores colocamos a quantidade e o total que aparecem em colunas, mostrando a quantidade comprada de cada item e o valor total da compra. É possível observar que a coluna de totais não está formatada, então basta o usuário formatar como faria em uma célula comum, indo até a guia Página Inicial e selecionando o formato Contábil na seção de números.
Desta forma a coluna de totais ficará com a formatação correta.
Outra forma de alterar a formatação dos dados é utilizando as opções da tabela, basta ir na opção que queremos modificar, neste caso será a Soma de Total e pressionar a seta logo ao lado, feito isso irá aparecer um menu com algumas opções, iremos selecionar a opção Configurações do Campo de Valor.
Ao selecionar essa opção abrirá uma nova janela, nesta parte inicial podemos modificar o tipo de cálculo que será mostrado também. Temos soma (que é o padrão), contagem, média, máximo, mínimo, produto, entre outras opções. Portanto se o usuário quiser modificar o cálculo de algum dado essa modificação será feita por aqui.
Neste caso foi modificada a Soma de Quantidade para Máx. de Quantidade, então é possível observar a quantidade máxima de cada produto que foi comprada e dentro de cada produto vemos que realmente aquele valor é o valor máximo.
Para mudar a formatação desses dados selecionados basta agora selecionar a opção Formato do Número, que irá abrir uma janela de Formatar Células. Nesta janela é possível escolher a formatação adequada para os dados utilizados, no nosso caso a soma de total deve estar em formato Contábil.
Ao pressionar OK a coluna será formatada com o formato contábil.
Como foi dito, a ordem em que colocamos os dados nas caixas de Linhas, Nomes, Colunas e Valores altera a ordem em que os dados aparecem para nós, então se alterarmos por exemplo o nome dos compradores para Filtros e deixarmos em Linhas os valores de produto, quantidade e preço teremos a seguinte mudança.
É possível observar que agora temos apenas duas colunas, temos uma parte superior à tabela que é o nosso filtro de nomes, então poderemos pesquisar por pessoas separadamente e ver apenas os produtos comprados por ela.
Nossa representação na tabela é agora de produto, quantidade e preço, ou seja, primeiro iremos ver quais produtos foram comprados, em seguida a quantidade de produto comprado e por fim o preço pago por cada um desses produtos.
Então se filtramos por um comprador apenas podemos observar só o que foi comprado por ele sem que seja outros dados atrapalhem nossa análise.
Neste caso podemos ver que Mariana comprou três produtos diferentes, podemos ver a quantidade comprada e o preço pago por cada um deles, além do total geral da compra.
Então podemos modificar os dados a fim de obter a melhor representação para cada caso. Por exemplo, podemos colocar ao invés de filtro de nomes, filtro de produtos, para sabermos quem comprou aqueles produtos.
Temos aqui que apenas Marcos e Mariana compraram laranja. Desta maneira o usuário poderá analisar quem comprou cada tipo de produto, então a tabela é realmente dinâmica, ela muda conforme a necessidade do usuário e responde automaticamente à medida que os dados são alterados nos campos Filtros, Colunas, Linhas e Valores.
Inserir Segmentação de Dados
Outra opção interessante é inserir segmentação de dados, nesta opção vamos ter uma caixa com botões, cada botão corresponde a uma das colunas da tabela e você consegue selecionar apenas clicando em cada botão.
Para inserir a segmentação clique sobre a tabela dinâmica, clicando sobre a tabela vai aparecer a guia Análise de Tabela Dinâmica nesta guia clique em Inserir Segmentação de Dados.
Você pode fazer a segmentação de dados a partir das fábricas, das marcas, vendas ou produtos, escolha um e clique em ok. Pronto, agora sua segmentação de dados está pronta!
A segmentação funciona como o filtro, mas além de ser mais visual com ela você pode selecionar várias opções de uma vez, para isso é só clicar em uma fábrica -> segurar a tecla control do seu teclado e escolher outra fábrica.
Assim você consegue filtrar as informações de quantos itens da segmentação quiser de uma única vez.
Acrescentar Coluna com Cálculos da Forma Certa!
Vamos supor que você precise calcula o imposto em cima de algum produto da tabela, se você calcular este valor fora da tabela dinâmica não vai conseguir usar depois para outras análises, então vamos fazer do jeito certo!
Clique em alguma célula da tabela e vá na guia Análise da tabela dinâmica -> clique em Campos, itens e conjuntos -> Campo calculado.
Neste campo você coloca o nome para a nova coluna, que pode ser “imposto” no campo Nome, e abaixo no campo Fórmula você vai escrever o cálculo do importo que pode ser =30% * Valor do produto ou Valor total dependendo das análises da sua tabela.
Feito isso, clique em ok e a nova coluna Imposto aparece com os valores já calculados.
Atenção!!
Os dados alterados entre os campos da Tabela são alterados normalmente dentro da tabela utilizada como base. No entanto, se o usuário acrescentar, retirar ou modificar os dados contidos na tabela utilizada para criar a Tabela Dinâmica, esses dados NÃO SERÃO ATUALIZADOS AUTOMATICAMENTE na Tabela Dinâmica. Para isso, é necessário realizar um processo manual para atualizar esses valores.
Para atualizar os dados basta ir na guia Analisar e selecionar a opção Atualizar, desta forma os dados da tabela inicial utilizada para criar a tabela serão passados para ela e essa tabela será atualizada com os novos dados.
Outra opção é antes de clicar em tabela dinâmica, formatar como tabela, quando formatamos como tabela qualquer informação que acrescentamos no final já entra automaticamente na formatação e isso reflete na tabela dinâmica.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Intermediário, clique aqui!
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador