Tutorial do Power Pivot para Excel: principais casos de uso e exemplos

Sumário executivo

Baixe o conjunto de dados aqui para acompanhar o tutorial.

As Novas Roupas do Imperador: Tutorial Power Pivot

Nos vários campos e sub-subcampos que abrangem finanças, análise financeira, mercados financeiros e investimentos financeiros, o Microsoft Excel é o rei. Com a chegada e o crescimento exponencial do big data, no entanto, impulsionado por décadas de agregação e acumulação de dados, o advento do armazenamento em nuvem barato e a ascensão da internet das coisas – ou seja, comércio eletrônico, mídia social e a interconexão de dispositivos – o Excel a funcionalidade e os recursos herdados foram levados ao limite.

Mais especificamente, a infraestrutura e as limitações de processamento do Excel de geração mais antiga, como o limite de linhas de 1.048.576 linhas, ou a inevitável desaceleração do processamento em grandes conjuntos de dados, tabelas de dados e planilhas interconectadas, reduziram sua usabilidade como uma ferramenta eficaz de big data. Em 2010, no entanto, a Microsoft adicionou uma nova dimensão ao Excel, chamada Power Pivot. O Power Pivot ofereceu inteligência de negócios de última geração e funcionalidade de análise de negócios ao Excel em sua capacidade de extrair, combinar e analisar conjuntos de dados quase ilimitados sem prejudicar a velocidade de processamento. Apesar de seu lançamento há oito anos, no entanto, a maioria dos analistas financeiros ainda não sabe como usar o Power Pivot, e muitos nem sabem que ele existe.

Neste artigo, mostrarei como usar o Power Pivot para superar problemas comuns do Excel e examinar as principais vantagens adicionais do software usando alguns exemplos. Este tutorial do Power Pivot serve como um guia para o que você pode alcançar com essa ferramenta e, no final, explorará alguns exemplos de casos de uso em que o Power Pivot geralmente se mostra inestimável.

O que é Power Pivot e por que é útil?

O Power Pivot é um recurso do Microsoft Excel que foi introduzido como um suplemento do Excel 2010 e 2013 e agora é um recurso nativo do Excel 2016 e 365. Como a Microsoft explica, o Power Pivot para Excel “permite importar milhões de linhas de dados de várias fontes de dados em uma única pasta de trabalho do Excel, crie relacionamentos entre dados heterogêneos, crie colunas e medidas calculadas usando fórmulas, crie tabelas dinâmicas e gráficos dinâmicos e analise ainda mais os dados para que você possa tomar decisões de negócios oportunas sem precisar de assistência de TI. ”

A linguagem de expressão primária que a Microsoft usa no Power Pivot é DAX (Expressões de Análise de Dados), embora outras possam ser usadas em situações específicas. Novamente, como a Microsoft explica, “DAX é uma coleção de funções, operadores e constantes que podem ser usados ​​em uma fórmula ou expressão para calcular e retornar um ou mais valores. Dito de forma mais simples, o DAX ajuda você a criar novas informações a partir de dados que já estão em seu modelo.” Felizmente para aqueles que já estão familiarizados com o Excel, as fórmulas DAX parecerão familiares, pois muitas das fórmulas têm uma sintaxe semelhante (por exemplo, SUM , AVERAGE , TRUNC ).

Para maior clareza, os principais benefícios do uso do Power Pivot em relação ao Excel básico podem ser resumidos da seguinte forma:

• Ele permite importar e manipular centenas de milhões de linhas de dados onde o Excel tem uma restrição rígida de pouco mais de um milhão de linhas.
• Ele permite que você importe dados de várias fontes em uma única pasta de trabalho de origem sem precisar criar várias planilhas de origem que sofrem com problemas de controle de versão e transferibilidade.
• Ele permite que você manipule os dados importados, analise-os e tire conclusões sem diminuir a velocidade do seu computador.
• Ele permite que você visualize os dados com gráficos dinâmicos e Power BI.

Nas seções a seguir, analisarei cada um dos itens acima e mostrarei como o Power Pivot para Excel pode ser útil.

Como usar o Power Pivot

1) Importando grandes conjuntos de dados

Como mencionado anteriormente, uma das principais limitações do Excel diz respeito ao trabalho com conjuntos de dados extremamente grandes. Felizmente para nós, o Excel agora pode carregar bem acima do limite de um milhão de linhas diretamente no Power Pivot.

Para demonstrar isso, gerei um conjunto de dados de amostra de dois anos de vendas para um varejista de artigos esportivos com nove categorias de produtos diferentes e quatro regiões. O conjunto de dados resultante é de dois milhões de linhas.

Usando a guia Dados na faixa de opções, criei uma Nova Consulta do arquivo CSV (consulte Criando uma Nova Consulta abaixo). Essa funcionalidade costumava ser chamada de PowerQuery, mas a partir do Excel 2016 e 365, foi mais integrada à guia Dados do Excel.

Criando uma nova consulta

De uma pasta de trabalho em branco no Excel para carregar todos os dois milhões de linhas no Power Pivot, levou cerca de um minuto! Observe que consegui realizar uma formatação leve de dados promovendo a primeira linha para se tornar os nomes das colunas. Nos últimos anos, a funcionalidade do Power Query melhorou muito de um suplemento do Excel para uma parte totalmente integrada da guia Dados na barra de ferramentas. O Power Query pode dinamizar, nivelar, limpar e moldar seus dados por meio de seu conjunto de opções e sua própria linguagem, M.

2) Importando dados de várias fontes

Um dos outros principais benefícios do Power Pivot para Excel é a capacidade de importar facilmente dados de várias fontes. Anteriormente, muitos de nós criamos várias planilhas para nossas várias fontes de dados. Muitas vezes, esse processo envolvia escrever código VBA e copiar/colar dessas fontes diferentes. Felizmente para nós, porém, o Power Pivot permite importar dados de diferentes fontes de dados diretamente para o Excel sem ter que se deparar com os problemas mencionados acima.

Usando a função Query no Anexo 1, podemos extrair de qualquer uma das seguintes fontes:

• Microsoft Azure
• servidor SQL
• Teradata
• o Facebook
• Força de vendas
• Arquivos JSON
• Pastas de trabalho do Excel
• …e muitos mais

Além disso, várias fontes de dados podem ser combinadas na função de consulta ou na janela do Power Pivot para integrar os dados. Por exemplo, você pode extrair dados de custo de produção de uma pasta de trabalho do Excel e resultados de vendas reais do SQL Server por meio da Consulta no Power Pivot. A partir daí, você pode combinar os dois conjuntos de dados combinando os números do lote de produção para produzir margens brutas por unidade.

3) Trabalhando com grandes conjuntos de dados

Outra vantagem importante do Power Pivot para Excel é a capacidade de manipular e trabalhar com grandes conjuntos de dados para tirar conclusões e análises relevantes. Vou analisar alguns exemplos comuns abaixo para dar uma ideia do poder da ferramenta.

Medidas

Os viciados em Excel, sem dúvida, concordarão que as Tabelas Dinâmicas são uma das tarefas mais úteis e, ao mesmo tempo, uma das mais frustrantes que realizamos. Frustrante principalmente quando se trata de trabalhar com conjuntos de dados maiores. Felizmente, o Power Pivot para Excel nos permite criar tabelas dinâmicas com facilidade e rapidez ao trabalhar com conjuntos de dados maiores.

Na imagem abaixo, intitulada Criando Medidas , observe como a janela do Power Pivot é separada em dois painéis. O painel superior contém os dados e o painel inferior contém as medidas. Uma medida é um cálculo que é executado em todo o conjunto de dados. Inseri uma medida digitando na célula destacada.

 Total Sales:=SUM('Accounting Data'[Amount])

Isso cria uma nova medida que soma na coluna Valor. Da mesma forma, posso digitar outra medida na célula abaixo

 Average Sales:=AVERAGE('Accounting Data'[Amount])

Criando medidas

A partir daí, observe como é rápido criar uma tabela dinâmica familiar em um grande conjunto de dados.

Criando uma tabela dinâmica

Tabelas de dimensão

Como analistas financeiros que usam o Excel, nos tornamos adeptos do uso de fórmulas complicadas para dobrar a tecnologia à nossa vontade. VLOOKUP , SUMIF , e até mesmo o temido INDEX(MATCH()) . No entanto, usando o Power Pivot, podemos jogar muito disso pela janela.

Adicionando uma tabela criada pelo usuário a um modelo do Power Pivot

Para demonstrar essa funcionalidade, criei uma pequena tabela de referência na qual atribuí cada Categoria a um Tipo. Ao escolher "Adicionar ao modelo de dados", esta tabela é carregada no Power Pivot (consulte Adicionando uma tabela criada pelo usuário a um modelo do Power Pivot acima).

Também criei uma tabela de datas para usar com nosso conjunto de dados (consulte Criando uma tabela de datas abaixo). O Power Pivot para Excel facilita a criação rápida de uma tabela de datas para consolidar por meses, trimestres e dias da semana. O usuário também pode criar uma tabela de datas mais personalizada para analisar por semanas, anos fiscais ou qualquer agrupamento específico da organização.

Criando uma tabela de datas

Colunas Calculadas

Além das medidas, existe outro tipo de cálculo: colunas calculadas. Os usuários do Excel ficarão à vontade para escrever essas fórmulas, pois elas são muito semelhantes à escrita de fórmulas em tabelas de dados. Eu criei uma nova coluna calculada abaixo (veja Criando uma Coluna Calculada abaixo) que classifica a tabela Dados Contábeis por Valor. As vendas abaixo de US$ 50 são rotuladas como "Pequenas" e todas as outras são rotuladas como "Grandes". A fórmula não parece intuitiva?

Criando uma coluna calculada

Relacionamentos

Podemos então criar uma relação entre o campo Categoria da tabela Dados Contábeis e o campo Categoria da tabela Categoria usando a Visualização de Diagrama. Além disso, podemos definir uma relação entre o campo Data de venda da tabela Dados contábeis e o campo Data da tabela Calendário.

Definindo relacionamentos

Agora, sem a necessidade de nenhuma SUMIF ou VLOOKUP , podemos criar uma Tabela Dinâmica que calculou o Total de Vendas por ano e tipo com uma segmentação de dados para o Tamanho da Transação.

Tabela Dinâmica Usando Relacionamentos

Ou podemos criar um gráfico de vendas médias para cada dia da semana usando a nova tabela Calendário.

Gráfico dinâmico usando relacionamentos

Embora este gráfico pareça simples, é impressionante que tenha levado menos de dez segundos para criar uma consolidação de mais de dois milhões de linhas de dados, sem adicionar uma nova coluna aos dados de vendas.

Apesar de podermos executar todos esses cenários de relatórios consolidados, sempre podemos detalhar os itens de linha individuais. Mantemos nossos dados altamente granulares.

Funções avançadas

Até agora, a maioria das análises que mostrei são cálculos relativamente diretos. Agora, quero demonstrar alguns dos recursos mais avançados dessa plataforma.

Inteligência de tempo

Muitas vezes, quando examinamos os resultados financeiros, queremos compará-los com um período de tempo comparável do ano anterior. O Power Pivot possui algumas funções integradas de inteligência de tempo.

 Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

Por exemplo, adicionar apenas duas medidas acima à tabela Dados de Contabilidade no Power Pivot me permite produzir a seguinte Tabela Dinâmica em apenas alguns cliques.

Tabela Dinâmica de Inteligência de Tempo

Granularidades incompatíveis

Como analista financeiro, um problema que muitas vezes tenho que resolver é o de granularidades incompatíveis. Em nosso exemplo, os dados de vendas reais são mostrados no nível da categoria, mas vamos preparar um orçamento que seja apenas sazonal. Para aumentar esse descompasso, vamos elaborar um orçamento trimestral, mesmo que os dados de vendas sejam diários.

Granularidades incompatíveis - Tabela de orçamento

Com o Power Pivot para Excel, essa inconsistência é facilmente resolvida. Ao criar duas tabelas de referência adicionais, ou tabelas de dimensão na nomenclatura do banco de dados, agora podemos criar os relacionamentos apropriados para analisar nossas vendas reais em relação aos valores orçados.

Granularidades incompatíveis - Relacionamentos

No Excel, a tabela dinâmica a seguir é reunida rapidamente.

Granularidades incompatíveis - Orçamento vs. Resultados reais

Além disso, podemos definir novas medidas que calculam a variação entre as vendas reais e as vendas orçadas conforme abaixo:

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

Usando essa medida, podemos mostrar a variação em uma tabela dinâmica.

Granularidades incompatíveis - Resultados de variação

Porcentagem do Total

Por fim, vamos examinar as vendas em uma categoria específica como porcentagem de todas as vendas (por exemplo, contribuição da categoria para as vendas gerais) e as vendas em uma categoria específica como porcentagem de todas as vendas do mesmo tipo (por exemplo, contribuição da categoria para o tipo sazonal vendas). Criei as duas medidas abaixo:

 Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

Essas medidas agora podem ser implantadas em uma nova tabela dinâmica:

Porcentagem do Total

Observe como os cálculos são realizados no nível de categoria e tipo sazonal. Adoro a rapidez e a facilidade com que esses cálculos são realizados em um conjunto de dados tão grande. Estes são apenas alguns exemplos da elegância e do poder computacional do Power Pivot.

Compressão

Outro benefício é que os tamanhos dos arquivos diminuem. O tamanho original do arquivo era de 91 MB e agora está abaixo de 4 MB. Essa é uma compactação de 96% do arquivo original.

Tamanhos de arquivo

Como isso acontece? O Power Pivot usa o mecanismo xVelocity para compactar os dados. Em termos simples, os dados são armazenados em colunas em vez de linhas. Este método de armazenamento permite que o computador comprima valores duplicados. Em nosso conjunto de dados de exemplo, há apenas quatro regiões que se repetem em todos os dois milhões de linhas. O Power Pivot para Excel pode armazenar esses dados com mais eficiência. O resultado é que, para dados com muitos valores repetidos, custa muito menos armazenar esses dados.

Uma coisa a notar é que eu usei valores inteiros em dólares neste conjunto de dados de amostra. Se eu tivesse incluído dois pontos decimais para refletir centavos, o efeito de compactação diminuiria para impressionantes 80% do tamanho do arquivo original.

Tabela SSAS

Os modelos Power Pivot também podem ser escaláveis ​​para toda a empresa. Digamos que você crie um modelo Power Pivot que comece a ganhar muitos usuários na organização, ou os dados cresçam para dez milhões de linhas, ou ambos. Neste ponto, talvez você não queira trinta usuários diferentes atualizando o modelo ou fazendo alterações. O modelo pode ser facilmente convertido em SSAS Tabular. Todas as tabelas e relacionamentos são retidos, mas agora você pode controlar a frequência de atualização, atribuir funções (por exemplo, somente leitura, leitura e processo) a vários usuários e implantar apenas um pequeno front-end do Excel vinculado ao modelo Tabular. O resultado é que seus usuários podem acessar o modelo Tabular implantado com uma pequena pasta de trabalho, mas não têm acesso às fórmulas e medidas.

4) Visualização e Análise de Dados

Fórmulas CUBO

Uma das solicitações constantes dos meus clientes é que eu crie relatórios que obedecem a um layout estritamente definido. Tenho clientes que solicitam larguras de coluna específicas, códigos de cores RGB e nomes e tamanhos de fontes predefinidos. Considere o seguinte painel:

Fórmulas CUBE incorporadas

Como preencher os números de vendas sem gerar Tabelas Dinâmicas se todas as nossas vendas estiverem hospedadas com o Power Pivot para Excel? Usando fórmulas CUBO! Podemos escrever fórmulas CUBE dentro de qualquer célula do Excel e ele realizará o cálculo usando o modelo Power Pivot que já construímos.

Por exemplo, a seguinte fórmula é digitada na célula em “2016 Total Sales”:

=CUBEVALUE(" ThisWorkbookDataModel "," [Medidas].[Vendas Totais] "," [Calendário].[Ano].[2016] ")

A primeira parte da fórmula, destacada em amarelo, refere-se ao nome do modelo Power Pivot. Em geral, geralmente é ThisWorkbookDataModel para versões mais recentes do Power Pivot para Excel. A parte em verde define que queremos usar a medida Total Sales. A parte em azul instrui o Excel a filtrar apenas as linhas que têm uma data de vendas com um ano igual a 2016.

Nos bastidores, o Power Pivot construiu um cubo de processamento analítico online (OLAP) com os dados, colunas calculadas e medidas. Esse design permite que o usuário do Excel acesse os dados buscando diretamente com as funções CUBE. Usando fórmulas CUBE, consegui construir demonstrações financeiras completas que obedecem a layouts predefinidos. Esse recurso é um dos destaques do uso do Power Pivot para Excel para análise financeira.

Power BI

Outra vantagem do Power Pivot para Excel é que você pode pegar rapidamente qualquer pasta de trabalho do Power Pivot criada e convertê-la rapidamente em um modelo do Power BI. Ao importar a pasta de trabalho do Excel diretamente para o aplicativo Power BI Desktop ou Power BI Online, você pode analisar, visualizar e compartilhar seus dados com qualquer pessoa em sua organização. Essencialmente, o Power BI é Power Pivot, PowerQuery e SharePoint, tudo em um. Abaixo, criei um painel importando a pasta de trabalho anterior do Power Pivot para Excel para o aplicativo de área de trabalho do Power BI. Observe como a interface é interativa:

Power BI

Uma grande coisa sobre o Power BI é o Natural Language Q&A. Para demonstrar, carreguei o modelo do Power BI na minha conta online do Power BI. No site, posso fazer perguntas e o Power BI constrói a análise apropriada enquanto digito:

Perguntas e respostas sobre linguagem natural

Esse tipo de capacidade de consulta permite que o usuário faça perguntas sobre o modelo de dados e interaja com os dados de maneira mais fácil do que no Excel.

Outro benefício do Power BI é que os desenvolvedores da Microsoft estão constantemente lançando atualizações para ele. Novos recursos, muitos solicitados pelos usuários, são lançados mensalmente. O melhor de tudo é que é uma transição perfeita do Power Pivot para Excel. Assim, o tempo que você investiu aprendendo as fórmulas DAX pode ser implantado no Power BI! Para o analista que precisa compartilhar sua análise com muitos usuários em vários dispositivos, vale a pena explorar o Power BI.

Melhores Práticas

Depois de começar, há algumas práticas recomendadas que você deve seguir.

A primeira é decidir cuidadosamente o que importar em primeiro lugar. Você usará o endereço residencial do vendedor? Preciso saber o endereço de e-mail do meu cliente no contexto desta pasta de trabalho? Se o objetivo for agregar os dados em um painel, alguns dos dados disponíveis não serão necessários para esses cálculos. Gastar tempo curando os dados recebidos aliviará bastante os problemas e o uso de memória posteriormente, quando seu conjunto de dados for expandido.

Outra prática recomendada é lembrar que o Power Pivot não é o Excel. No Excel, estamos acostumados a criar cálculos expandindo constantemente nossas planilhas para a direita. O Power Pivot para Excel processa os dados com mais eficiência se limitarmos esse desejo de destino manifesto. Em vez de criar continuamente colunas calculadas à direita de seus dados, aprenda a escrever medidas no painel inferior. Esse hábito garantirá tamanhos de arquivo menores e cálculos mais rápidos.

Finalmente, sugiro usar nomes em inglês simples para as medidas. Essa demorei muito para adotar. Passei os primeiros anos inventando nomes como SumExpPctTotal , mas quando outras pessoas começaram a usar as mesmas pastas de trabalho, tive muitas explicações a dar. Agora, quando inicio uma nova pasta de trabalho, uso nomes de medidas como Expense Line Item as Percent of Total Expenses . Embora o nome seja mais longo, é muito mais fácil para outra pessoa usar.

Casos de uso do mundo real

Neste artigo, apresentei apenas algumas das maneiras pelas quais o Power Pivot para Excel permite que você dê um passo importante além do Excel simples. Achei que seria útil destacar alguns casos de uso do mundo real nos quais descobri que o Power Pivot para Excel é extremamente útil.

Aqui estão alguns:

• Analise o desempenho de um grande portfólio de ativos em intervalos de tempo variados: como o Power Pivot para Excel nos permite definir medidas que comparam um período de tempo com um anterior, podemos rapidamente ter trimestre a trimestre, ano a ano, e desempenho mês a mês, tudo de forma contínua, escrevendo apenas algumas medidas.
• Resuma os dados contábeis usando níveis de agregação personalizados: Ao identificar cada item de linha do razão geral por nome, categoria e demonstrativo financeiro, os relatórios podem ser criados rapidamente que incluem os itens de linha apropriados.
• As redes podem identificar as vendas nas mesmas lojas: usando uma tabela que mapeia quando as lojas ficam online, os resultados financeiros podem ser comparados com base nas mesmas lojas.
• Identifique os desempenhos superior e inferior nas vendas: podem ser criadas tabelas dinâmicas que destacam os cinco principais SKUs e os cinco últimos SKUs por vendas, margens brutas, prazos de produção, etc.
• Os varejistas podem definir tabelas de calendário que usam uma configuração 4-4-5: Usando uma tabela de data personalizada, um varejista pode atribuir facilmente cada dia a um mês 4-4-5 específico, e os resultados de vendas diárias podem ser transferidos para o mês correspondente.

De planilhas desajeitadas a pastas de trabalho modernas

Como analistas financeiros, somos obrigados a realizar cálculos complexos em conjuntos de dados em constante expansão. Como o Excel já é a ferramenta analítica padrão, a curva de aprendizado do Power Pivot é fácil e muitas das funções espelham as funções nativas do Excel.

Com o uso de funções CUBE, o Power Pivot para Excel combina perfeitamente com suas pastas de trabalho existentes do Excel. O ganho de eficiência computacional não pode ser negligenciado. Assumindo uma velocidade de processamento 20% mais rápida, o que é conservador, o analista financeiro que passa seis horas por dia no Excel pode economizar 300 horas por ano!

Além disso, agora podemos analisar conjuntos de dados muito maiores do que anteriormente com nosso Excel tradicional. Com modelos projetados de forma eficiente, podemos facilmente ter 10x a quantidade de dados que antes era permitido no Excel tradicional, mantendo a agilidade analítica rápida. Com a capacidade de converter os modelos de Power Pivot para SSAS Tabular, a quantidade de dados que podem ser processados ​​é de 100 a 1.000 vezes o que podemos alcançar no Excel.

A capacidade do Power Pivot para Excel de realizar cálculos extremamente rápidos em grandes quantidades de dados e ainda manter a capacidade de mergulhar nos detalhes pode transformar a análise financeira de planilhas desajeitadas em pastas de trabalho modernas.

Se você estiver interessado em experimentar o Power Pivot para Excel, abaixo estão alguns materiais úteis para você começar.

Referências e guias úteis

Collie, R., & Singh, A. (2016). Power Pivot e Power BI: o guia do usuário do Excel para DAX, Power Query, Power BI e Power Pivot no Excel 2010-2016. Estados Unidos: Santo Macro! Livros.

Ferrari, A., & Russo, M. (2015). O guia definitivo para DAX: Business intelligence com Microsoft Excel, SQL Server Analysis Services e Power BI. Estados Unidos: Microsoft Press, EUA.