Esercitazione su PowerPivot per Excel: casi d'uso ed esempi principali

Sintesi

Scarica il set di dati qui per seguire il tutorial.

I vestiti nuovi dell'imperatore: tutorial Power Pivot

In tutti i vari campi e sotto-sottocampi che abbracciano finanza, analisi finanziaria, mercati finanziari e investimenti finanziari, Microsoft Excel è il re. Con l'arrivo e la crescita esponenziale dei big data, tuttavia, trainata da decenni di aggregazione e accumulazione di dati, l'avvento del cloud storage a basso costo e l'ascesa dell'Internet delle cose, ovvero l'eCommerce, i social media e l'interconnessione dei dispositivi, Excel le funzionalità e le capacità legacy sono state spinte al limite.

Più specificamente, l'infrastruttura di Excel di vecchia generazione e le limitazioni di elaborazione come il limite di riga di 1.048.576 righe o l'inevitabile rallentamento dell'elaborazione per quanto riguarda set di dati di grandi dimensioni, tabelle di dati e fogli di calcolo interconnessi, ne hanno ridotto l'usabilità come strumento per big data efficace. Nel 2010, tuttavia, Microsoft ha aggiunto una nuova dimensione a Excel, denominata Power Pivot. Power Pivot ha offerto a Excel funzionalità di business intelligence e analisi aziendale di nuova generazione grazie alla sua capacità di estrarre, combinare e analizzare set di dati pressoché illimitati senza compromettere la velocità di elaborazione. Nonostante il suo rilascio otto anni fa, tuttavia, la maggior parte degli analisti finanziari non sa ancora come utilizzare Power Pivot e molti non sanno nemmeno che esiste.

In questo articolo, ti mostrerò come utilizzare Power Pivot per superare i problemi comuni di Excel e darò un'occhiata agli ulteriori vantaggi chiave del software usando alcuni esempi. Questo tutorial di Power Pivot ha lo scopo di fungere da guida per ciò che puoi ottenere con questo strumento e, alla fine, esplorerà alcuni casi d'uso di esempio in cui Power Pivot si rivela spesso inestimabile.

Che cos'è Power Pivot e perché è utile?

Power Pivot è una funzionalità di Microsoft Excel che è stata introdotta come componente aggiuntivo per Excel 2010 e 2013 e ora è una funzionalità nativa per Excel 2016 e 365. Come spiega Microsoft, Power Pivot per Excel "consente di importare milioni di righe di dati provenienti da più origini dati in un'unica cartella di lavoro di Excel, creare relazioni tra dati eterogenei, creare colonne e misure calcolate utilizzando formule, creare tabelle pivot e grafici pivot e quindi analizzare ulteriormente i dati in modo da poter prendere decisioni aziendali tempestive senza richiedere assistenza IT. "

Il linguaggio di espressione principale utilizzato da Microsoft in Power Pivot è DAX (Data Analysis Expressions), sebbene sia possibile utilizzarne altri in situazioni specifiche. Ancora una volta, come spiega Microsoft, “DAX è una raccolta di funzioni, operatori e costanti che possono essere utilizzati in una formula o espressione per calcolare e restituire uno o più valori. Detto più semplicemente, DAX ti aiuta a creare nuove informazioni dai dati già presenti nel tuo modello". Fortunatamente per coloro che hanno già familiarità con Excel, le formule DAX sembreranno familiari, poiché molte formule hanno una sintassi simile (ad esempio, SUM , AVERAGE , TRUNC ).

Per chiarezza, i principali vantaggi dell'utilizzo di Power Pivot rispetto a Excel di base possono essere riassunti come segue:

• Ti consente di importare e manipolare centinaia di milioni di righe di dati in cui Excel ha un rigido vincolo di poco più di un milione di righe.
• Consente di importare dati da più origini in un'unica cartella di lavoro di origine senza dover creare più fogli di origine che soffrono di problemi di controllo della versione e trasferibilità.
• Ti consente di manipolare i dati importati, analizzarli e trarre conclusioni senza rallentare il tuo computer a passo di lumaca.
• Ti consente di visualizzare i dati con grafici pivot e Power BI.

Nelle sezioni seguenti, analizzerò ciascuno dei precedenti e ti mostrerò come Power Pivot per Excel può essere utile.

Come utilizzare PowerPivot

1) Importazione di grandi set di dati

Come accennato in precedenza, una delle principali limitazioni di Excel riguarda l'utilizzo di set di dati estremamente grandi. Fortunatamente per noi, Excel ora può caricare ben oltre il limite di un milione di righe direttamente in Power Pivot.

Per dimostrarlo, ho generato un set di dati campione di due anni di vendite per un rivenditore di articoli sportivi con nove diverse categorie di prodotti e quattro regioni. Il set di dati risultante è di due milioni di righe.

Utilizzando la scheda Dati sulla barra multifunzione, ho creato una nuova query dal file CSV (vedere Creazione di una nuova query di seguito). Questa funzionalità si chiamava PowerQuery, ma a partire da Excel 2016 e 365 era più strettamente integrata nella scheda Dati di Excel.

Creazione di una nuova query

Da una cartella di lavoro vuota in Excel al caricamento di tutti e due milioni di righe in Power Pivot, ci è voluto circa un minuto! Si noti che sono stato in grado di eseguire una formattazione dei dati leggera promuovendo la prima riga in modo che diventasse i nomi delle colonne. Negli ultimi anni, la funzionalità di Power Query è notevolmente migliorata da un componente aggiuntivo di Excel a una parte strettamente integrata della scheda Dati sulla barra degli strumenti. Power Query può ruotare, appiattire, pulire e modellare i tuoi dati attraverso la sua suite di opzioni e il suo linguaggio, M.

2) Importazione di dati da più origini

Uno degli altri vantaggi chiave di Power Pivot per Excel è la possibilità di importare facilmente dati da più origini. In precedenza, molti di noi creavano più fogli di lavoro per le varie origini dati. Spesso, questo processo prevedeva la scrittura di codice VBA e il copia/incolla da queste fonti disparate. Fortunatamente per noi, tuttavia, Power Pivot consente di importare dati da diverse origini dati direttamente in Excel senza dover incorrere nei problemi sopra menzionati.

Usando la funzione Query nell'Allegato 1, possiamo estrarre da una qualsiasi delle seguenti fonti:

• Microsoft Azure
• server SQL
• Teradata
• Facebook
• forza vendita
• File JSON
• Cartelle di lavoro di Excel
• …e tanti altri

Inoltre, più origini dati possono essere combinate nella funzione Query o nella finestra Power Pivot per integrare i dati. Ad esempio, puoi estrarre i dati sui costi di produzione da una cartella di lavoro di Excel e i risultati di vendita effettivi da SQL Server tramite la query in Power Pivot. Da lì, puoi combinare i due set di dati facendo corrispondere i numeri dei lotti di produzione per produrre margini lordi per unità.

3) Lavorare con set di dati di grandi dimensioni

Un altro vantaggio fondamentale di Power Pivot per Excel è la capacità di manipolare e lavorare con set di dati di grandi dimensioni per trarre conclusioni e analisi pertinenti. Di seguito esaminerò alcuni esempi comuni per darti un'idea della potenza dello strumento.

Le misure

I drogati di Excel saranno senza dubbio d'accordo sul fatto che le tabelle pivot sono sia una delle attività più utili che, allo stesso tempo, una delle più frustranti che svolgiamo. Frustrante soprattutto quando si tratta di lavorare con set di dati più grandi. Fortunatamente, Power Pivot per Excel ci consente di creare facilmente e rapidamente tabelle pivot quando si lavora con set di dati più grandi.

Nell'immagine seguente, intitolata Creazione di misure , si noti come la finestra di Power Pivot sia separata in due riquadri. Il riquadro superiore contiene i dati e il riquadro inferiore contiene le misure. Una misura è un calcolo che viene eseguito sull'intero set di dati. Ho inserito una misura digitando nella cella evidenziata.

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

In questo modo viene creata una nuova misura che somma nella colonna Importo. Allo stesso modo, posso digitare un'altra misura nella cella sottostante

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

Creazione di misure

Da lì, osserva quanto è veloce creare una tabella pivot familiare su un set di dati di grandi dimensioni.

Creazione di una tabella pivot

Tabelle dimensionali

In qualità di analisti finanziari che utilizzano Excel, diventiamo esperti nell'utilizzare formule contorte per piegare la tecnologia alla nostra volontà. VLOOKUP , SUMIF e persino il temuto INDEX(MATCH()) . Tuttavia, usando Power Pivot, possiamo gettare gran parte di questo fuori dalla finestra.

Aggiunta di una tabella creata dall'utente a un modello PowerPivot

Per dimostrare questa funzionalità, ho creato una piccola tabella di riferimento in cui ho assegnato ogni Categoria a un Tipo. Scegliendo "Aggiungi al modello di dati", questa tabella viene caricata in Power Pivot (vedere Aggiunta di una tabella creata dall'utente a un modello Power Pivot sopra).

Ho anche creato una tabella delle date da utilizzare con il nostro set di dati (vedi Creazione di una tabella delle date di seguito). Power Pivot per Excel semplifica la creazione rapida di una tabella di date da consolidare in base a mesi, trimestri e giorni della settimana. L'utente può anche creare una tabella di date più personalizzata da analizzare per settimane, anni fiscali o raggruppamenti specifici dell'organizzazione.

Creazione di una tabella delle date

Colonne calcolate

Oltre alle misure, esiste un altro tipo di calcolo: le colonne calcolate. Gli utenti di Excel si sentiranno a proprio agio scrivendo queste formule, poiché sono molto simili alla scrittura di formule nelle tabelle di dati. Ho creato una nuova colonna calcolata di seguito (vedere Creazione di una colonna calcolata di seguito) che ordina la tabella Dati contabili per importo. Le vendite inferiori a $ 50 sono etichettate "Piccole" e tutte le altre sono etichettate "Grandi". La formula non ti sembra intuitiva?

Creazione di una colonna calcolata

Relazioni

Possiamo quindi creare una relazione tra il campo Categoria della tabella Dati contabili e il campo Categoria della tabella Categoria utilizzando la Visualizzazione diagramma. Inoltre, possiamo definire una relazione tra il campo Data di vendita della tabella Dati contabili e il campo Data della tabella Calendario.

Definizione delle relazioni

Ora, senza alcuna SUMIF o VLOOKUP necessaria, possiamo creare una tabella pivot che calcola le vendite totali per anno e digita, con uno slicer per Transaction Size.

Tabella pivot utilizzando le relazioni

Oppure, possiamo creare un grafico delle vendite medie per ogni giorno della settimana utilizzando la nuova tabella Calendario.

Grafico pivot usando le relazioni

Anche se questo grafico sembra semplice, è impressionante che ci siano voluti meno di dieci secondi per creare un consolidamento di oltre due milioni di righe di dati, senza aggiungere una nuova colonna ai dati di vendita.

Pur essendo in grado di eseguire tutti questi scenari di rapporti consolidati, possiamo sempre approfondire i singoli elementi pubblicitari. Conserviamo i nostri dati altamente granulari.

Funzioni avanzate

Finora, la maggior parte delle analisi che ho mostrato sono calcoli relativamente semplici. Ora, voglio dimostrare alcune delle capacità più avanzate di questa piattaforma.

Intelligenza del tempo

Spesso, quando esaminiamo i risultati finanziari, vogliamo confrontarli con un periodo di tempo comparabile rispetto all'anno precedente. Power Pivot ha alcune funzioni di intelligenza temporale integrate.

 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())

Ad esempio, l'aggiunta di due sole misure sopra alla tabella Dati contabili in Power Pivot mi consente di produrre la seguente tabella pivot in pochi clic.

Tabella pivot di Time Intelligence

Granularità non corrispondenti

In qualità di analista finanziario, un problema che devo spesso risolvere è quello delle granularità non corrispondenti. Nel nostro esempio, i dati di vendita effettivi vengono mostrati a livello di categoria, ma prepariamo un budget solo a livello stagionale. Per favorire questa discrepanza, prepareremo un budget trimestrale, anche attraverso i dati di vendita giornalieri.

Granularità non corrispondenti – Tabella del budget

Con Power Pivot per Excel, questa incoerenza è facilmente risolvibile. Creando due tabelle di riferimento aggiuntive, o tabelle delle dimensioni nella nomenclatura del database, ora possiamo creare le relazioni appropriate per analizzare le nostre vendite effettive rispetto agli importi preventivati.

Granularità non corrispondenti – Relazioni

In Excel, la seguente tabella pivot si riunisce rapidamente.

Granularità non corrispondenti: budget e risultati effettivi

Inoltre, possiamo definire nuove misure che calcolano la varianza tra le vendite effettive e le vendite previste come di seguito:

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

Usando questa misura, possiamo mostrare la varianza su una tabella pivot.

Granularità non corrispondenti – Risultati della varianza

Percentuale del totale

Infine, esaminiamo le vendite in una particolare categoria come percentuale di tutte le vendite (ad esempio, contributo di categoria alle vendite complessive) e le vendite in una particolare categoria come percentuale di tutte le vendite dello stesso tipo (ad esempio, contributo di categoria al tipo stagionale i saldi). Ho creato le due misure seguenti:

 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]))

Tali misure possono ora essere implementate in una nuova tabella pivot:

Percentuale del totale

Nota come i calcoli vengono eseguiti sia a livello di categoria che di tipo stagionale. Adoro la rapidità e la semplicità con cui questi calcoli vengono eseguiti su un set di dati così ampio. Questi sono solo alcuni esempi dell'eleganza e della potenza di calcolo di Power Pivot.

Compressione

Un altro vantaggio è che le dimensioni dei file si riducono. La dimensione del file originale era di 91 MB e ora è inferiore a 4 MB. Questa è una compressione del 96% del file originale.

Dimensioni dei file

Come succede? Power Pivot utilizza il motore xVelocity per comprimere i dati. In parole povere, i dati vengono archiviati in colonne anziché in righe. Questo metodo di archiviazione consente al computer di comprimere i valori duplicati. Nel nostro set di dati di esempio, ci sono solo quattro regioni che si ripetono su tutti i due milioni di righe. Power Pivot per Excel può archiviare questi dati in modo più efficiente. Il risultato è che per i dati che hanno molti valori ripetuti, la memorizzazione di questi dati costa molto meno.

Una cosa da notare è che ho usato importi di dollari interi in questo set di dati di esempio. Se avessi incluso due punti decimali per riflettere i centesimi, l'effetto di compressione si ridurrebbe a un impressionante 80% della dimensione del file originale.

Tabella SSAS

I modelli Power Pivot possono anche essere scalabili per l'intera azienda. Supponiamo che tu crei un modello Power Pivot che inizia a guadagnare molti utenti nell'organizzazione, o che i dati crescono fino a dieci milioni di righe, o entrambi. A questo punto, potresti non volere che trenta diversi utenti aggiornino il modello o apportino modifiche. Il modello può essere convertito senza problemi in SSAS Tabular. Tutte le tabelle e le relazioni vengono mantenute, ma ora puoi controllare la frequenza di aggiornamento, assegnare ruoli (ad es. di sola lettura, lettura ed elaborazione) a vari utenti e distribuire solo un piccolo front-end di Excel che si collega al modello tabulare. Il risultato è che gli utenti possono quindi accedere al modello tabulare distribuito con una piccola cartella di lavoro, ma non avere accesso alle formule e alle misure.

4) Visualizzazione e analisi dei dati

Formule CUBO

Una delle richieste costanti dei miei clienti è che creo report conformi a un layout rigorosamente definito. Ho clienti che richiedono larghezze di colonna specifiche, codici colore RGB e nomi e dimensioni dei caratteri predefiniti. Considera la seguente dashboard:

Formule CUBE integrate

Come possiamo popolare i numeri di vendita senza generare tabelle pivot se tutte le nostre vendite sono ospitate con Power Pivot per Excel? Usando le formule CUBE! Possiamo scrivere formule CUBE all'interno di qualsiasi cella di Excel ed eseguirà il calcolo utilizzando il modello Power Pivot che abbiamo già costruito.

Ad esempio, la seguente formula viene digitata nella cella in "Vendite totali 2016:"

=VALORECUBO(" Questo modello di dati della cartella di lavoro "," [Misure]. [Vendite totali] "," [Calendario].[Anno].[2016] ")

La prima parte della formula, evidenziata in giallo, fa riferimento al nome del modello Power Pivot. In generale, di solito è ThisWorkbookDataModel per le versioni più recenti di Power Pivot per Excel. La porzione in verde definisce che vogliamo utilizzare la misura Total Sales. La parte in blu indica a Excel di filtrare solo le righe che hanno una data di vendita con un anno uguale al 2016.

Dietro le quinte, Power Pivot ha costruito un cubo OLAP (Online Analytical Processing) con i dati, le colonne calcolate e le misure. Questo design consente all'utente di Excel di accedere ai dati recuperando direttamente con le funzioni CUBE. Utilizzando le formule CUBE, sono stato in grado di costruire bilanci completi conformi a schemi predefiniti. Questa funzionalità è uno dei punti salienti dell'utilizzo di Power Pivot per Excel per l'analisi finanziaria.

Power BI

Un altro vantaggio di Power Pivot per Excel è che puoi prendere rapidamente qualsiasi cartella di lavoro di Power Pivot che crei e convertirla rapidamente in un modello Power BI. Importando la cartella di lavoro di Excel direttamente nell'app Power BI Desktop o Power BI Online, puoi analizzare, visualizzare e condividere i tuoi dati con chiunque nell'organizzazione. In sostanza, Power BI è Power Pivot, PowerQuery e SharePoint riuniti in uno. Di seguito è stato creato un dashboard importando la cartella di lavoro di Power Pivot per Excel precedente nell'applicazione desktop Power BI. Nota quanto sia interattiva l'interfaccia:

Power BI

Un aspetto eccezionale di Power BI è la sessione di domande e risposte in linguaggio naturale. A scopo dimostrativo, ho caricato il modello Power BI nel mio account Power BI online. Dal sito Web, posso porre domande e Power BI costruisce l'analisi appropriata mentre digito:

Domande e risposte sul linguaggio naturale

Questo tipo di capacità di query consente all'utente di porre domande sul modello di dati e interagire con i dati in un modo più semplice rispetto a Excel.

Un altro vantaggio di Power BI è che gli sviluppatori di Microsoft rilasciano costantemente aggiornamenti. Nuove funzionalità, molte richieste dagli utenti, vengono pubblicate mensilmente. Soprattutto, è una transizione senza interruzioni da Power Pivot per Excel. Quindi, il tempo che hai investito nell'apprendimento delle formule DAX può essere distribuito in Power BI! Per l'analista che ha bisogno di condividere la sua analisi con molti utenti su diversi dispositivi, potrebbe valere la pena esplorare Power BI.

Migliori pratiche

Una volta iniziato, ci sono alcune best practice che dovresti seguire.

Il primo è decidere attentamente cosa importare in primo luogo. Utilizzerai mai l'indirizzo di casa del venditore? Devo conoscere l'indirizzo e-mail del mio cliente nel contesto di questa cartella di lavoro? Se l'obiettivo è aggregare i dati in un dashboard, alcuni dei dati disponibili non saranno necessari per tali calcoli. Trascorrere del tempo a curare i dati in entrata allevierà notevolmente i problemi e l'utilizzo della memoria in seguito, quando il set di dati si espande.

Un'altra procedura consigliata consiste nel ricordare che Power Pivot non è Excel. In Excel, siamo abituati a creare calcoli espandendo costantemente i nostri fogli di lavoro a destra. Power Pivot per Excel elabora i dati in modo più efficiente se limitiamo questo desiderio di destino manifesto. Invece di creare continuamente colonne calcolate a destra dei tuoi dati, impara a scrivere misure nel riquadro inferiore. Questa abitudine garantirà file di dimensioni inferiori e calcoli più rapidi.

Infine, suggerirei di utilizzare nomi in inglese semplice per le misure. Mi ci è voluto molto tempo per adottarlo. Ho passato i primi anni a inventare nomi come SumExpPctTotal , ma una volta che altre persone hanno iniziato a utilizzare le stesse cartelle di lavoro, ho avuto molte spiegazioni da fare. Ora, quando inizio una nuova cartella di lavoro, utilizzo i nomi delle misure come Expense Line Item as Percent of Total Expenses . Sebbene il nome sia più lungo, è molto più facile da usare per qualcun altro.

Casi d'uso nel mondo reale

In questo articolo ho presentato solo una manciata dei modi in cui Power Pivot per Excel ti consente di fare un passo importante oltre il semplice Excel. Ho pensato che sarebbe stato utile evidenziare alcuni casi d'uso del mondo reale in cui ho riscontrato che Power Pivot per Excel è estremamente utile.

Qui ce ne sono alcuni:

• Analizzare le prestazioni di un ampio portafoglio di asset in diversi intervalli di tempo: poiché Power Pivot per Excel ci consente di definire misure che confrontano un periodo di tempo con uno precedente, possiamo rapidamente avere un trimestre su trimestre, anno su anno, e le prestazioni mese su mese, il tutto su base continuativa scrivendo solo alcune misure.
• Riepiloga i dati contabili utilizzando livelli di aggregazione personalizzati: identificando ogni voce di contabilità generale per nome, categoria e rendiconto finanziario, è possibile creare rapidamente report che includono le voci appropriate.
• Le catene possono identificare le vendite nello stesso negozio: utilizzando una tabella che mappa quando i negozi sono online, i risultati finanziari possono essere confrontati sullo stesso negozio.
• Individuare i risultati con prestazioni superiori e inferiori nelle vendite: è possibile creare tabelle pivot che evidenzino le prime cinque SKU e le ultime cinque SKU in base a vendite, margini lordi, tempi di produzione, ecc.
• I rivenditori possono definire tabelle di calendario che utilizzano una configurazione 4-4-5: utilizzando una tabella di date personalizzata, un rivenditore può assegnare facilmente ogni giorno a un mese specifico 4-4-5, quindi i risultati delle vendite giornaliere possono essere inseriti nel mese corrispondente.

Dai fogli di calcolo goffi alle cartelle di lavoro moderne

In qualità di analisti finanziari, siamo tenuti a eseguire calcoli complessi su set di dati in continua espansione. Poiché Excel è già lo strumento analitico predefinito, la curva di apprendimento di Power Pivot è semplice e molte delle funzioni rispecchiano le funzioni native di Excel.

Con l'uso delle funzioni CUBE, Power Pivot per Excel si integra perfettamente con le cartelle di lavoro di Excel esistenti. Il guadagno di efficienza computazionale non può essere trascurato. Supponendo una velocità di elaborazione più veloce del 20%, che è prudente, l'analista finanziario che trascorre sei ore al giorno in Excel può risparmiare 300 ore all'anno!

Inoltre, ora possiamo analizzare set di dati molto più grandi di quanto potessimo in precedenza con il nostro Excel tradizionale. Con modelli progettati in modo efficiente, possiamo facilmente disporre di una quantità di dati 10 volte superiore a quella consentita in precedenza in Excel tradizionale, pur mantenendo una rapida agilità analitica. Con la possibilità di convertire i modelli da Power Pivot a SSAS Tabular, la quantità di dati che può essere elaborata è 100–1.000 volte quella che possiamo ottenere in Excel.

La capacità di Power Pivot per Excel di eseguire calcoli fulminei su grandi quantità di dati pur mantenendo la capacità di immergersi nei dettagli può trasformare l'analisi finanziaria da ingombranti fogli di calcolo a moderne cartelle di lavoro.

Se sei interessato a provare Power Pivot per Excel, di seguito sono riportati alcuni materiali utili per iniziare.

Riferimenti e Guide Utili

Collie, R. e Singh, A. (2016). Power Pivot e Power BI: Guida per l'utente di Excel a DAX, Power Query, Power BI e Power Pivot in Excel 2010-2016. Stati Uniti: Santa Macro! Libri.

Ferrari, A., e Russo, M. (2015). La guida definitiva a DAX: Business intelligence con Microsoft Excel, SQL Server Analysis Services e Power BI. Stati Uniti: Microsoft Press, Stati Uniti.