Frequentemente a decisão é se vamos perder algum dado importante, ou trabalhar ou se vamos trabalhar em um tedioso workbook. Às vezes, temos de ser criativos para combinar dois grandes conjuntos de dados. Recorremos a fórmulas enroladas e esperamos para sempre que os cálculos sejam resolvidos.
Felizmente, você não precisa mais tomar essas decisões. A funcionalidade Power Pivot do Excel fornece uma maneira de extrair, combinar e analisar grandes conjuntos de dados. Apesar de ter sido lançado com o Excel 2010, a maioria dos analistas financeiros que conheço ainda não sabe como usar o Power Pivot, e muitos não sabem que ele existe.
Neste artigo, vou mostrar-lhes como usar o Power Pivot para superar problemas comuns do Excel e dar uma olhada em vantagens adicionais do software usando alguns exemplos. Este tutorial Power Pivot para Excel deve servir como um guia e, no final, explorarei alguns exemplos de casos de uso em que o Power Pivot pode ser inestimável.
O que é Power Pivot e porque ele é útil?
Power Pivot é uma característica do Microsoft Excel que foi introduzida como um complemento para o Excel 2010 e 2013 e agora é uma característica nativa para o Excel 2016. Como a Microsoft explica, o Power Pivot para Excel "permite que você importe milhões de linhas de dados de múltiplas fontes de dados em uma única pasta de trabalho do Excel, criar relacionamentos entre dados heterogêneos, criar colunas e medidas calculadas usando fórmulas, criar tabelas dinâmicas e gráficos dinâmicos e depois analisar os dados de forma que você possa tomar decisões de negócios oportunas sem requerer assistência de TI".
O idioma de expressão primário que a Microsoft usa no Power Pivot é DAX (Data Analysis Expressions), embora outros possam ser usados em situações específicas. Mais uma vez, como a Microsoft explica, "DAX é uma coleção de funções, operadores e constantes que podem ser usadas 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 já em seu modelo. "Felizmente, para aqueles que já estão familiarizados com o Excel, as fórmulas DAX serão familiares, uma vez que 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 vs. Excel básico podem ser resumidos a seguir:
- Ele permite importar e manipular centenas de milhões de linhas de dados onde o Excel tem uma restrição difícil de pouco mais de um milhão de linhas.
- Ele permite importar dados de várias fontes para uma única pasta de trabalho de origem sem ter que criar várias folhas de origem que sofram problemas de controle de versão e transferibilidade.
- Ele permite que você manipule os dados importados, analise-o e tire conclusões sem diminuir o ritmo do seu computador.
- Ele permite que você visualize os dados com PivotCharts e Power BI.
Nas seções a seguir, vou percorrer cada um dos itens acima e mostrar-lhes como o Power Pivot para Excel pode ser útil.
1) Importando grandes Datasets
Conforme discutido 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 o 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 produtos 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 a partir do arquivo CSV. Essa funcionalidade costumava ser chamada de PowerQuery, mas a partir do Excel 2016, ela está integrada na guia Dados do Excel.
Exibição 1: Criando a Nova Query
De uma pasta de trabalho em branco no Excel para carregar todos os dois milhões de linhas no Power Pivot, demorou cerca de um minuto! Observe que eu consegui realizar alguma formatação leve de dados promovendo a primeira linha para se tornar o nome da coluna. Ao longo dos últimos anos, a funcionalidade Power Query melhorou grandemente de um suplemento do Excel para uma parte bem integrada da guia Dados na barra de ferramentas. Power Query pode girar, aplainar, limpar e moldar seus dados através de seu conjunto de opções e seu próprio idioma, M.
2) Importando Dados de Multiplas Fontes
Um dos 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 envolveu escrever código VBA e copiar/colar dessas fontes diferentes. Felizmente, para nós, o Power Pivot permite que você importe dados de fontes de dados diferentes diretamente para o Excel sem ter que executar os problemas mencionados acima.
Usando a função Query na Exibição 1, podemos tirar de qualquer uma das seguintes fontes:
- Microsoft Azure
- SQL Server
- Teradata
- Salesforce
- JSON files
- Excel workbooks
- ...e muito mais
3) Trabalhando com grandes Datasets
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. Eu vou percorrer alguns exemplos comuns abaixo para lhe dar uma idéia do poder da ferramenta.
Medidas
Junkies do Excel, sem dúvida, concordam que as tabelas dinâmicas são uma das mais úteis e, ao mesmo tempo, uma das tarefas mais frustrantes que realizamos. Frustrante especialmente quando se trata de trabalhar com conjuntos de dados maiores. Felizmente, o Power Pivot para Excel nos permite criar, facilmente e rapidamente, tabelas dinâmicas ao trabalhar com conjuntos maiores de dados.
Na imagem abaixo (Exibição 2), observe como a janela do Power Pivot é separada em dois painéis. O painel superior possui os dados e o painel inferior abriga as medidas. Uma medida é um cálculo que é executado em todo o conjunto de dados. Eu entrei uma medida digitando a célula destacada.
Exibição 2: Criando Medidas
A partir daí, observe como é rápido criar uma tabela dinâmica familiar em um conjunto de dados grande.
Exibição 3: Criando uma PivotTable
Dimensão de Tabelas
Como analistas financeiros usando o Excel, nos tornamos adeptos do uso de fórmulas enroladas para dobrar a tecnologia à nossa vontade. Nós dominamos VLOOKUP, SUMIF, e até mesmo o temido INDEX(MATCH()). No entanto, usando o Power Pivot, podemos lançar muito disso na janela.
Exibição 4: Adicionando uma User-Created Table no 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 (Figura 4 acima).
Eu também criei uma tabela de datas para usar com nosso conjunto de dados. 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 data mais personalizada para analisar por semanas, anos fiscais ou qualquer grupo específico da organização.
Exibição 5: Criando um Date Table
Colunas Calculadas
Além das medidas, existe outro tipo de cálculo: colunas calculadas. Os usuários do Excel estarão à vontade para escrever essas fórmulas, pois são muito semelhantes às fórmulas de escrita em tabelas de dados. Criei uma nova coluna calculada abaixo, que classifica a tabela de Dados de Contabilidade por Montante. As vendas abaixo de US$50 são rotuladas como "Pequenas", e todas as outras são rotuladas como "Grande". A fórmula não se parece intuitiva?
Exibição 6: Criando uma coluna calculada
Relacionamentos
Podemos então criar uma relação entre o campo Categoria da tabela de dados contábeis e o campo Categoria usando a visualização do diagrama. Além disso, podemos definir um relacionamento entre o campo Data de vendas da Tabela de dados contábeis e o campo Data da tabela do calendário.
Exibição 7: Definindo Relacionamentos
Agora, sem nenhuma função SUMIF ou VLOOKUP necessária, podemos criar uma tabela dinâmica que calcula as vendas totais por ano e tipo, com um "slicer" para o tamanho da transação.
Exibição 8: PivotTable Usando Relacionamentos
Ou, podemos criar um gráfico de vendas médias para cada dia da semana usando a nova tabela de calendário.
Exibição 9: PivotChart Usando Relacionamentos
Embora este gráfico pareça simples, é impressionante que demorou 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.
Ao ser capaz de executar todos esses cenários de relatórios consolidados, sempre podemos seguir detalhando os itens de linha individuais. Nós 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, eu quero demonstrar algumas das capacidades mais avançadas desta plataforma.
Inteligência de Tempo
Muitas vezes, quando examinamos os resultados financeiros, queremos compará-lo a um prazo comparável ao ano anterior. Power Pivot possui algumas funções de inteligência do tempo incorporado.
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 para a tabela de dados contábeis no Power Pivot me permite produzir a seguinte tabela dinâmica em poucos cliques.
Exibição 10: Time Intelligence PivotTable
Granulidades Desiguais
Como analista financeiro, um problema que tenho para resolver muitas vezes é a de granularidades incompatíveis. Em nosso exemplo, os dados de vendas reais é mostrada no nível de categoria, mas vamos preparar esse orçamento só no nível sazonal. Para promover essa incompatibilidade, vamos preparar orçamento trimestral, embora os dados de venda sejam diários.
Exibição 11: Granularidades Desiguais - Tabela de Orçamento
Com Power Pivot para Excel, esta inconsistência é facilmente resolvida. Ao criar duas tabelas de referência adicionais ou tabelas de dimensão na nomenclatura de banco de dados, agora podemos criar os relacionamentos apropriados para analisar nossas vendas reais contra os valores orçamentados.
Exibição 12: Granularidades Desiguais - Relacionamentos
No Excel, a seguinte tabela dinâmica se junta rapidamente.
Exibição 13: Granularidades Desiguais - Orçamento vs. Resultados Atuais
Além disso, podemos definir novas medidas que calculem a variação entre as vendas reais e as vendas orçamentadas conforme abaixo:
Actual-to-Budget Variance:=if(ISBLANK([Total Budgeted Sales]),BLANK(),[Total Sales]/[Total Budgeted Sales]-1)
Usando essa medida, podemos mostrar a variância em uma tabela dinâmica.
Exibição 14: Granularidades Desiguais - Variação de Resultados
Total percentual
Finalmente, vamos examinar as vendas em uma categoria específica como uma porcentagem de todas as vendas (por exemplo, contribuição da categoria para as vendas globais) e as vendas em uma categoria específica como uma porcentagem de todas as vendas do mesmo tipo (por exemplo, contribuição da categoria para o tipo de vendas sazonal). Eu 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:
Observe como os cálculos são realizados na categoria e no tipo sazonal. Eu amo o quão rápido e sem esforço esses cálculos são realizados em um conjunto de dados tão grande. Estes são apenas alguns exemplos da elegância e pura potência computacional do Power Pivot.
Compressão
Outro benefício é que o tamanho dos arquivos diminui. O tamanho do arquivo original era de 91MB, e agora ele está abaixo de 4MB. Essa é uma compressão de 96% do arquivo original.
Exibição 16: Tamanho dos arquivos
Como isso acontece? Power Pivot usa o mecanismo xVelocity para comprimir os dados. Em termos simples, os dados são armazenados em colunas em vez de linhas. Esse método de armazenamento permite que o computador comprima valores duplicados. No nosso conjunto de dados de exemplo, existem apenas quatro regiões que são repetidas em todos os dois milhões de linhas. Power Pivot para Excel pode armazenar mais eficientemente esses dados. O resultado é que, para dados que possuem muitos valores de repetição, custa muito menos para armazenar esses dados.
Uma coisa a ser observada é que usei valores de dólares inteiros neste conjunto de dados de amostra. Se eu incluísse dois pontos decimais para refletir centavos, o efeito de compressão diminui para 80%.
SSAS Tabular
Os modelos Power Pivot também podem ser escaláveis para toda a empresa. Digamos que você crie um modelo Power Pivot que começa a ganhar muitos usuários na organização, ou os dados crescem para dez milhões de linhas, ou ambos. Neste ponto, você pode não querer que trinta usuários diferentes atualizem o modelo ou façam mudanças. O modelo pode ser perfeitamente convertido em tabelas SSAS. Todas as tabelas e relacionamentos são mantidos, mas agora você pode controlar a freqüência de atualização, atribuir funções (por exemplo, somente leitura, ler e processar) para vários usuários e implantar apenas um pequeno front-end do Excel que se vincula ao modelo Tabular. O resultado é que seus usuários podem então acessar o modelo Tabular implantado com uma pasta de trabalho pequena, mas não ter acesso às fórmulas e medidas.
4) Data Visualization and Analysis
CUBE Formulas
Um dos constantes pedidos de meus clientes é que eu crie relatórios que estejam de acordo com um layout estritamente definido. Eu tenho clientes que solicitam larguras de colunas específicas, códigos de cores RGB e nomes e tamanhos de fontes pré-definidos. Considere o seguinte painel:
Como preenchemos os números de vendas sem gerar tabelas dinâmicas se todas as nossas vendas estiverem alojadas no Power Pivot for Excel? Usando fórmulas CUBE! Podemos escrever fórmulas de CUBE dentro de qualquer célula do Excel e isso irá 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:"
A primeira parte da fórmula, destacada em amarelo, refere-se ao nome do modelo Power Pivot. Em geral, é ThisWorkbookDataModel para versões mais recentes do Power Pivot para Excel. A porção em verde define que queremos usar a medida Vendas totais. A parte em azul instrui o Excel a filtrar apenas as linhas que possuem uma Data de Vendas com um ano igual a 2016.
Por trás das cenas, o Power Pivot construiu um cubo OLAP (Online Analytical Processing) com os dados, colunas calculadas e medidas. Este design permite que o usuário do Excel acesse os dados obtendo diretamente com as funções CUBE. Usando as fórmulas do CUBE, consegui construir declarações financeiras completas que estejam em conformidade com layouts predefinidos. Esta capacidade é um dos destaques do uso do Power Pivot para Excel para análise financeira.
Power BI
Outra vantagem do Power Pivot para o Excel é que você pode rapidamente tomar qualquer pasta de trabalho Power Pivot que você cria e convertê-la rapidamente em um modelo Power BI. Ao importar a pasta de trabalho do Excel diretamente no aplicativo Power BI Desktop ou no Power BI Online, você pode analisar, visualizar e compartilhar seus dados com qualquer pessoa em sua organização. Essencialmente, o Power BI é o Power Pivot, o PowerQuery e o SharePoint juntos. Abaixo, criei um painel de bordo ao importar o Power Pivot para Excel anterior para o aplicativo de desktop Power BI. Observe como a interface é interativa:
Exibição 18: Power BI
Uma ótima coisa sobre o Power BI é a Q&A da linguagem natural. Para demonstrar, eu carreguei o modelo Power BI na minha conta de Power BI online. No site, posso fazer perguntas e o Power BI constrói a análise apropriada à medida que escrevo:
Exibição 19: Linguagem Natural do Q&A
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 uma 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 pelo usuário, são lançados mensalmente. O melhor de tudo, é uma transição perfeita do Power Pivot para Excel. Então, 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 para muitos usuários em dispositivos variados, o Power BI pode valer a pena explorar.
Melhores Práticas
Uma vez que você começa, há algumas boas práticas que você deve seguir.
O primeiro é decidir cuidadosamente o que importa em primeiro lugar. Você já usa o endereço residencial do vendedor? Preciso conhecer o endereço de e-mail do meu cliente no contexto desta pasta de trabalho? Se o objetivo é agregar os dados em um painel, alguns dos dados disponíveis não serão necessários para esses cálculos.
Outra prática recomendada é lembrar que Power Pivot não é o Excel. No Excel, estamos acostumados a criar cálculos expandindo constantemente nossas planilhas para a direita. Power Pivot para Excel processa os dados de forma mais eficiente 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. Este hábito assegurará tamanhos menores de arquivos e cálculos mais rápidos.
Finalmente, gostaria de sugerir o uso de nomes simples em inglês para medidas. Passei os primeiros anos fazendo nomes como SumExpPctTotal, mas uma vez que outras pessoas começaram a usar os mesmos workbooks, eu tinha que dar muitas explicações. Agora, quando eu iniciar uma nova pasta de trabalho, uso nomes de medidas como "Linha de despesas como porcentagem de despesas totais".
Casos de Uso do Mundo Real
Neste artigo, eu mostrei apenas um punhado das maneiras pelas quais o Power Pivot para Excel permite que você dê um passo importante além do Excel simples. Eu pensei que seria útil realçar alguns casos de uso do mundo real em que Power Pivot para Excel é extremamente útil.
Analise do desempenho de um grande portfólio de ativos em diferentes intervalos de tempo: desde que o Power Pivot for Excel nos permite definir medidas que comparem um período de tempo com o anterior, podemos rapidamente ter um trimestre a trimestre, ano a ano, E o desempenho de um mês a outro de forma contínua, escrevendo apenas algumas medidas.
Resumir dados contábeis usando níveis de agregação personalizados: ao identificar cada item de linha de razão geral por nome, categoria e demonstração financeira, os relatórios podem ser criados rapidamente, que incluem os itens de linha apropriados.
As cadeias podem identificar vendas na mesma loja: usando uma tabela que mapeia quando as lojas estão online, os resultados financeiros podem ser comparados na mesma loja.
BY ELLEN SU - FINANCE EXPERT @ TOPTOTAL
Óptima postagem sobre estas ferramentas.
ResponderExcluirÓtimo artigo!
ResponderExcluirJá havia ouvido falar sobre o Pivot, mas não sabia direito qual a sua função.
Vou começar a estudar a ferramenta e já aplica-la no trabalho.
Muito obrigado!!!
Excelente post! Muito bem explanado. Obrigado!
ResponderExcluirGreat post and informative contend check your eye sight color blind test
ResponderExcluir