Análise de Dados com Power Pivot e Power Query no Excel
Aprenda a fazer análise de dados com Power Pivot e Power Query no Excel, passo a passo de forma fácil e prática!
Power Pivot e Power Query são os recursos do Excel para análise de dados em escala. O Power Query faz a extração e a limpeza dos dados (ETL), enquanto o Power Pivot cria o modelo, relaciona tabelas e permite medidas em DAX. Juntos, transformam o Excel em uma ferramenta de Business Intelligence, lidando com milhões de linhas.
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:
Não vamos te encaminhar nenhum tipo de SPAM! A Hashtag Treinamentos é uma empresa preocupada com a proteção de seus dados e realiza o tratamento de acordo com a Lei Geral de Proteção de Dados (Lei n. 13.709/18). Qualquer dúvida, nos contate.
Análise de Dados com Power Pivot e Power Query no Excel
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.
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.
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.
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.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
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.
Análise de Dados no Excel - Análise de dados com 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.
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.
Conclusão - Análise de Dados com Power Pivot e Power Query no Excel
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.
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!
Posts mais recentes de Excel Avançado
- Claude e ChatGPT no Excel: 3 Usos Profissionais
Descubra como usar Claude e ChatGPT no Excel para criar bases de dados, corrigir fórmulas com erro e analisar planilhas completas na prática. Introdução Usar Claude e ChatGPT no Excel… Read more: Claude e ChatGPT no Excel: 3 Usos Profissionais - Problemas no PROCV: 10 Erros Comuns e Como Resolver
Veja os 10 problemas no PROCV mais comuns, como #N/D, #REF! e #NOME?, e o passo a passo para resolver cada erro e fazer a fĂłrmula voltar a funcionar. - Como Fazer Macro no Excel: Tutorial com Exemplos
Aprenda como fazer macro no Excel do zero: ative a guia Desenvolvedor, grave suas ações e automatize tarefas repetitivas com VBA, mesmo sem saber programar.
Posts mais recentes da Hashtag Treinamentos
- Planilha de Controle de Notas Fiscais no Excel [Grátis]
Baixe a planilha de controle de notas fiscais no Excel grátis: calcule ISS e retenções, veja o que está em aberto e gere a cobrança de cada nota de serviço. - Qual IA usar na empresa: ChatGPT, Claude ou Perplexity?
Qual IA usar na empresa: veja as diferenças entre ChatGPT, Claude e Perplexity e quando cada um rende mais em cada área da sua equipe. - Apostilas Gratuitas em PDF: Excel, Power BI, Python e IA
Baixe apostilas gratuitas em PDF de Excel, Power BI, Python, Claude e agentes de IA, com exercĂcios e gabarito. Escolha a sua e comece hoje.
1. Qual a diferença entre Power Query e Power Pivot?
O Power Query faz a extração, transformação e limpeza dos dados (ETL) antes de carregá-los; o Power Pivot cria o modelo de dados, relaciona tabelas e permite medidas em DAX. Um prepara, o outro analisa.
2. Power Pivot e Power Query sĂŁo gratuitos no Excel?
Sim. O Power Query já vem integrado nas versões modernas do Excel (guia Dados), e o Power Pivot está disponĂvel no Microsoft 365 e em algumas edições do Office, bastando habilitá-lo nos suplementos.
3. O que Ă© DAX no Excel?
DAX (Data Analysis Expressions) é a linguagem de fórmulas do Power Pivot (e do Power BI) usada para criar medidas e cálculos sobre o modelo de dados, como totais, percentuais e indicadores de tempo.
4. Vale a pena usar Power Pivot em vez de fĂłrmulas comuns?
Para grandes volumes e análises recorrentes, sim. O Power Pivot lida com milhões de linhas, evita fórmulas repetidas em cada célula e centraliza os cálculos em medidas reutilizáveis, deixando a planilha mais rápida e organizada.
