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:
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!
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.
Na planilha 2, você terá acesso ao relatório de vendas da empresa para o ano de 2022.
E na planilha 3, encontrará o relatório de vendas referente ao ano de 2023.
Nosso objetivo será construir análises de dados a partir dessas 3 planilhas, que contêm informações bastante extensas.
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.
O Excel selecionará automaticamente a tabela ativa, então só precisamos clicar em OK.
Feito isso, será aberta uma janela do 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.
Selecione o arquivo do Excel com o qual estamos trabalhando.
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.
Com isso, importaremos para o Power Query nossa base de vendas referente ao ano de 2022.
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.
Na janela que será aberta, selecione o número de linhas que deseja remover. No nosso caso, serão apenas duas.
Além disso, precisamos pegar os valores que ficaram na primeira linha da nossa consulta e transformá-los nos títulos das colunas.
Faremos isso clicando na opção 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.
Selecione as duas tabelas na janela que abrirá.
O Power Query criará uma única consulta com as informações de 2021 e 2022. Vamos renomeá-la para 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.
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.
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.
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.
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.
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.
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.
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.
Por fim, basta selecionar as colunas Data da Venda e Nascimento e alterar a formatação delas para Data.
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.
Assim que habilitada, a guia do Power Pivot estará disponível para uso. Nela, clique em Adicionar ao Modelo de Dados.
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.
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]
Agora, ainda dentro do Power Pivot, clique na guia Página Inicial e selecione Tabela Dinâmica.
Marque a opção Nova Planilha, clique em OK e uma tabela dinâmica será criada no Excel.
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.
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.
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.
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.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Avançado, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Power BI e impressionar no Mercado de Trabalho? Inscreva-se no Power BI Impressionador!