Blog

Postado em em 19 de setembro de 2023

Análise de Dados no Excel – Power Query e Power Pivot

Aprenda a fazer análise de dados no Excel utilizando as ferramentas Power Query e Power Pivot, passo a passo!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

Para receber por e-mail o(s) arquivo(s) que utilizamos na aula, preencha:

Análise de Dados no Excel – Power Query e Power Pivot

Nesta aula eu vou te ensinar como fazer análise de dados no Excel utilizando as ferramentas Power Query e Power Pivot.

Você aprenderá a tratar a base de dados, juntar tabelas e adicionar consultas no Power Query. Além disso, vou mostrar como utilizar a ferramenta Coluna de Exemplos para extrair informações de textos maiores.

Para finalizar, mostrarei como fazer análise de dados com o Power Pivot, que consiste em criar uma tabela dinâmica a partir dessa ferramenta.

Esses processos são importantes para facilitar e otimizar a análise de dados no Excel. Então, faça o download do material disponível e vamos começar!

Apresentando as planilhas

No arquivo disponível para download, você encontrará 3 planilhas. A primeira apresenta o relatório de vendas da empresa para o ano de 2021.

Vendas da empresa para o ano de 2021

Na planilha 2, você terá acesso ao relatório de vendas da empresa para o ano de 2022.

Vendas da empresa para o ano de 2022

E na planilha 3, encontrará o relatório de vendas referente ao ano de 2023.

Vendas da empresa para o ano de 2023

Nosso objetivo será construir análises de dados a partir dessas 3 planilhas, que contêm informações bastante extensas.

Tratando a base de dados – Power Query

O primeiro passo antes de iniciarmos nossa análise de dados no Excel é tratar essas bases de dados para melhor adequá-las ao nosso objetivo. Para fazer esse tratamento, utilizaremos o Power Query.

O Power Query nos permite tratar bases de dados provenientes de diversos lugares, como SQL Server, Microsoft Access, Azure e de arquivos e pastas em nosso computador.

No nosso caso, queremos apenas utilizar as tabelas já abertas no arquivo. Portanto, com a primeira planilha aberta, vamos na guia Dados e selecionamos De Tabela/Intervalo.

Guia Dados e selecionando De Tabela/Intervalo

O Excel selecionará automaticamente a tabela ativa, então só precisamos clicar em OK.

Selecionando a tabela

Feito isso, será aberta uma janela do Power Query com a nossa tabela.

Power Query com a nossa tabela

O Power Query é um suplemento do Excel e é nele que realizamos os ajustes e tratamentos da nossa base de dados.

Vamos começar renomeando nossa tabela para Vendas 2021 para deixar nossa consulta (como chamamos as planilhas no Power Query) com um nome mais claro e intuitivo.

Além disso, vamos carregar as nossas vendas de 2022. Para isso, clique com o botão direito dentro da aba de consultas e selecione Nova Consulta > Arquivo > Pasta de Trabalho do Excel.

Criando Nova Consulta

Selecione o arquivo do Excel com o qual estamos trabalhando.

Selecionando o arquivo

Em seguida, na nova janela, escolha a Planilha2 que contém as informações de 2022. Não utilizaremos a Planilha3, pois o ano ainda não está completo, o que não é ideal para construir a análise que desejamos.

Selecionando a Planilha2

Com isso, importaremos para o Power Query nossa base de vendas referente ao ano de 2022.

Planilha2 no Power Query

Nessa base, precisaremos fazer alguns ajustes. Repare que ela possui duas linhas vazias no início da tabela, vamos começar removendo-as. Para fazer isso, vá à guia Página Inicial do Power Query, selecione a opção Remover Linhas e, em seguida, escolha Remover Linhas Superiores.

Removendo Linhas Superiores

Na janela que será aberta, selecione o número de linhas que deseja remover. No nosso caso, serão apenas duas.

Selecionando as linhas que serão removidas

Além disso, precisamos pegar os valores que ficaram na primeira linha da nossa consulta e transformá-los nos títulos das colunas.

Primeira linha com os títulos do cabeçalho

Faremos isso clicando na opção Usar a Primeira Linha como Cabeçalho.

Usar a Primeira Linha como Cabeçalho

Feito isso, vamos renomear essa Planilha2 para Vendas 2022 e unir as duas consultas (2021 e 2022) em uma só. Na guia Página Inicial, clique em Acrescentar Consultas como Novas.

Unindo as consultas de 2021 e 2022

Selecione as duas tabelas na janela que abrirá.

Selecionando as duas tabelas

O Power Query criará uma única consulta com as informações de 2021 e 2022. Vamos renomeá-la para Vendas 21-22.

Tabela Vendas 21-22

Agora que temos uma única tabela com todas as informações, podemos fazer alguns tratamentos nos dados contidos nela. Vamos começar separando a coluna Produto em duas. Na guia Transformar, selecione a opção Dividir Coluna e escolha Por Delimitador.

Dividindo a coluna por delimitador

O delimitador é o caractere que separa as duas informações que queremos, ou seja, tudo que está antes do traço () é o nome do produto e depois dele é a cor. Na nova janela, vamos especificar o traço como delimitador e clicar em OK.

Determinando o Delimitador

Perceba que ele dividiu nossa coluna Produto em duas, uma com o nome e outra com a cor dos produtos. Vamos renomear essas duas colunas para Nome do Produto e Cor do Produto.

Renomeando as duas colunas para Nome do Produto e Cor do Produto

Na coluna Loja, temos os endereços completos das lojas, o que não seria viável para construirmos uma análise com base nas cidades. Para solucionar isso, vamos criar uma coluna que retorne apenas o nome das cidades.

Para fazer isso, selecione a opção Adicionar Colunas e clique em Coluna de Exemplos.

Adicionar Coluna de Exemplos

Essa função nos permite criar uma coluna a partir de exemplos. Vamos renomear essa coluna para Cidade e fornecer o primeiro exemplo, inserindo o nome da cidade da primeira loja.

Passando a primeira cidade como exemplo

Note que o Power Query ainda não foi capaz de identificar quais informações queremos adicionar nessa coluna, pois as linhas abaixo da primeira não estão sendo preenchidas automaticamente com o nome da cidade. Portanto, vamos fornecer um segundo exemplo.

Passando o segundo exemplo de cidade

Com o segundo exemplo, o Power Query compreenderá qual informação queremos representar nessa coluna e preencherá automaticamente as outras linhas da tabela. Podemos clicar em OK para criar nossa coluna.

Até agora, realizamos duas etapas de tratamento de dados dentro do Power Query. Primeiro removemos as linhas em branco, ajustamos o cabeçalho e unimos as nossas tabelas em uma só. Em seguida, adicionamos as colunas que queremos analisar. Agora, precisamos remover as colunas que não iremos utilizar.

Esse procedimento é bastante simples: basta selecionar as colunas que não queremos e pressionar a tecla Delete.

Nesse caso, iremos excluir a coluna Coluna1, que contém apenas valores Null (em branco), e as colunas que não serão úteis para a nossa análise de dados. No exemplo, removeremos as colunas Cor do Produto, Loja, Nome Cliente, Sobrenome e Num Filhos.

Tabela após a remoção das colunas que não serão usadas

Essa etapa dependerá muito da análise que você deseja construir, ponderando quais informações são relevantes ou não.

Com isso, concluímos o tratamento da nossa base de dados. Podemos clicar na guia Página Inicial e em Fechar e Carregar. E pronto! Nossa tabela, apenas com as informações tratadas, será criada no Excel.

Tabela tratada gerada no Excel

Por fim, basta selecionar as colunas Data da Venda e Nascimento e alterar a formatação delas para Data.

Formatando a Data da Venda e a data de Nascimento
Datas formatadas

Análise de dados – Power Pivot

Com nossa base de dados pronta e devidamente tratada, podemos utilizar o Power Pivot para realizar as análises de dados no Excel.

Se a guia do PowerPivot não estiver visível no seu Excel, siga estes passos: vá em Arquivo, clique em Opções, selecione Personalizar Faixa de Opções e marque a opção Power Pivot.

Ativando a guia do Power Pivot

Assim que habilitada, a guia do Power Pivot estará disponível para uso. Nela, clique em Adicionar ao Modelo de Dados.

Adicionar ao Modelo de Dados ao Power Pivot

Assim como o Power Query, o Power Pivot é um suplemento do Excel e abrirá uma nova janela para carregar nossa tabela, enquanto mantém o Excel aberto. Dentro do Power Pivot, podemos explorar um vasto conjunto de fórmulas para auxiliar na construção das nossas análises.

Power Pivot

Por exemplo, se quisermos calcular o faturamento de cada venda, podemos criar uma coluna chamada Faturamento e definir uma fórmula para preencher seus valores. Utilizaremos a seguinte fórmula:

[Preco Unitario] * [Quantidade Vendida]
Criando a coluna Faturamento

Agora, ainda dentro do Power Pivot, clique na guia Página Inicial e selecione Tabela Dinâmica.

Tabela Dinâmica

Marque a opção Nova Planilha, clique em OK e uma tabela dinâmica será criada no Excel.

Tabela Dinâmica criada

A tabela dinâmica gerada pelo Power Pivot é um pouco diferente da tabela dinâmica tradicional do Excel. Isso ocorre porque, nos campos do relatório, aparecem todas as consultas e bases de dados que foram criadas no Power Query, e não apenas os campos da tabela atual.

Campos disponíveis

Com isso, podemos construir nossas análises conforme desejado. Por exemplo, podemos analisar o faturamento e a quantidade vendida por cidade.

Para isso, arraste o campo Cidade da tabela Vendas 21-22 para as linhas e os campos Faturamento e Quantidade Vendida para os valores.

Criando as análises com a tabela dinâmica

Dessa forma, conseguimos realizar as análises desejadas, utilizando a funcionalidade das tabelas dinâmicas. A vantagem do Power Pivot é poder utilizar as informações provenientes do Power Query e trazê-las para nossas análises de forma integrada e eficiente.

Conclusão – Análise de Dados no Excel – Power Query e Power Pivot

Nessa aula, você aprendeu como realizar análise de dados no Excel, utilizando as poderosas ferramentas do Power Query e Power Pivot.

Desde o tratamento da base de dados realizado através do Power Query, até a construção de tabelas dinâmicas e análises efetivas com o Power Pivot, exploramos todas as etapas essenciais desse processo.

O poder combinado do Power Query e Power Pivot é extremamente importante, e dominar essa combinação pode ajudá-lo a obter insights valiosos e impulsionar seus resultados através de uma análise de dados no Excel mais eficiente e precisa.

Portanto, pratique e estude essas duas funcionalidades para aproveitar ao máximo o potencial delas em suas próximas análises de dados no Excel.

Hashtag Free Excel Básico

Apostila Básica de Excel

Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!


Quer aprender mais sobre Excel com um minicurso básico gratuito?

Quer aprender a criar Dashboards Incríveis no Excel para impressionar?Coloque seu e-mail e comece agora esse minicurso!