Vantaggi di Pivot per modello di dati

Quando si crea una tabella pivot in Excel, nella primissima finestra di dialogo, in cui ci viene chiesto di impostare l'intervallo iniziale e scegliere un posto in cui inserire la tabella pivot, è presente una casella di controllo poco appariscente ma molto importante di seguito: Aggiungi questi dati al modello di dati (Aggiungi questi dati al Modello di Dati) e, un po' più in alto, l'interruttore Usa il modello dati di questo libro (Utilizza il modello dati di questa cartella di lavoro):

Sfortunatamente, molti utenti che hanno familiarità con le tabelle pivot da molto tempo e le utilizzano con successo nel loro lavoro a volte non capiscono davvero il significato di queste opzioni e non le usano mai. E invano. Dopotutto, la creazione di una tabella pivot per il Data Model ci offre diversi vantaggi molto importanti rispetto alla classica tabella pivot di Excel.

Tuttavia, prima di considerare da vicino questi “panini”, capiamo prima cosa, in effetti, è questo Data Model?

Che cos'è un modello di dati

Modello di dati (abbreviato in MD o DM = Data Model) è un'area speciale all'interno di un file Excel in cui è possibile memorizzare dati tabulari - una o più tabelle collegate, se lo si desidera, tra loro. In realtà, questo è un piccolo database (cubo OLAP) incorporato in una cartella di lavoro di Excel. Rispetto alla classica conservazione dei dati sotto forma di tabelle regolari (o smart) su fogli di Excel stesso, il Data Model presenta diversi vantaggi significativi:

• I tavoli possono essere fino a 2 miliardi di righee un foglio Excel può contenere poco più di 1 milione.
• Nonostante le dimensioni gigantesche, l'elaborazione di tali tabelle (filtraggio, ordinamento, calcoli su di esse, riepilogo edificio, ecc.) viene eseguita molto veloce Molto più veloce di Excel stesso.
• Con i dati nel modello, è possibile eseguire calcoli aggiuntivi (se lo si desidera, molto complessi) utilizzando linguaggio DAX integrato.
  
• Tutte le informazioni caricate nel Data Model sono molto fortemente compresso utilizzando uno speciale archiviatore integrato e aumenta moderatamente le dimensioni del file Excel originale.
  

Il Modello è gestito e calcolato da uno speciale add-in integrato in Microsoft Excel – PowerPivotdi cui ho già scritto. Per abilitarlo, nella scheda sviluppatore clicca Componenti aggiuntivi COM (Sviluppatore: componenti aggiuntivi COM) e spuntare l'apposita casella:

Se le schede sviluppatore (Sviluppatore)non puoi vederlo sul nastro, puoi accenderlo File – Opzioni – Configurazione della barra multifunzione (File — Opzioni — Personalizza barra multifunzione). Se nella finestra mostrata sopra nell'elenco dei componenti aggiuntivi COM non hai Power Pivot, allora non è incluso nella tua versione di Microsoft Office 🙁

Nella scheda Power Pivot visualizzata, ci sarà un grande pulsante verde chiaro Management (Gestire), cliccando sul quale si aprirà la finestra di Power Pivot sopra Excel, dove vedremo i contenuti del Data Model del libro corrente:

Una nota importante lungo il percorso: una cartella di lavoro di Excel può contenere un solo modello di dati.

Carica le tabelle nel modello di dati

Per caricare i dati nel modello, prima trasformiamo la tabella in una scorciatoia da tastiera dinamica "intelligente". Ctrl+T e assegnagli un nome descrittivo nella scheda Costruttore (Design). Questo è un passaggio obbligatorio.

Quindi puoi utilizzare uno dei tre metodi tra cui scegliere:

• Premere il pulsante Aggiungi al modello (Aggiungi al modello di dati) linguetta PowerPivot linguetta Casa (Casa).
  
• Scegliere le squadre Inserisci – Tabella pivot (Inserisci — Tabella pivot) e attiva la casella di controllo Aggiungi questi dati al modello di dati (Aggiungi questi dati a Data Model). In questo caso, in base ai dati caricati nel Modello, viene immediatamente costruita anche una tabella pivot.
  
• Nella scheda Avanzate Dati (Data) clicca sul pulsante Dalla tabella/intervallo (Dalla tabella/intervallo)per caricare la nostra tabella nell'editor di Power Query. Questo percorso è il più lungo, ma, se lo desideri, qui puoi eseguire ulteriori operazioni di pulizia, modifica e tutti i tipi di trasformazioni dei dati, in cui Power Query è molto forte.

  Quindi i dati pettinati vengono caricati sul modello dal comando Home — Chiudi e carica — Chiudi e carica in... (Home — Chiudi&Carica — Chiudi&Carica in...). Nella finestra che si apre, seleziona l'opzione Basta creare una connessione (Crea solo connessione) e, soprattutto, metti un segno di spunta Aggiungi questi dati al modello di dati (Aggiungi questi dati a Data Model).
  

Costruiamo una sintesi del Data Model

Per creare un modello di dati riepilogativo, puoi utilizzare uno dei tre approcci seguenti:

• premi il bottone tabella riassuntiva (Tabella pivot) nella finestra di PowerPivot.
• Seleziona i comandi in Excel Inserisci – Tabella pivot e passare alla modalità Usa il modello dati di questo libro (Inserisci — Tabella pivot — Usa il modello di dati di questa cartella di lavoro).
• Scegliere le squadre Inserisci – Tabella pivot (Inserisci — Tabella pivot) e attiva la casella di controllo Aggiungi questi dati al modello di dati (Aggiungi questi dati a Data Model). La tabella "intelligente" corrente verrà caricata nel Modello e verrà creata una tabella riepilogativa per l'intero Modello.

Ora che abbiamo capito come caricare i dati nel Data Model e costruirne un riepilogo, esploriamo i vantaggi e i vantaggi che questo ci offre.

Vantaggio 1: relazioni tra tabelle senza utilizzare formule

È possibile creare un riepilogo regolare solo utilizzando i dati di una tabella di origine. Se ne hai diversi, ad esempio vendite, listino prezzi, elenco clienti, registro dei contratti, ecc., dovrai prima raccogliere i dati da tutte le tabelle in una utilizzando funzioni come CERCA.VERT (CERCA.VERT), INDICE (INDICE), PIÙ ESPOSTO (INCONTRO), SUMMESLIMN (SOMME) e simili. Questo è lungo, noioso e guida il tuo Excel in un "pensiero" con una grande quantità di dati.

Nel caso di una sintesi del Data Model, tutto è molto più semplice. È sufficiente impostare le relazioni tra le tabelle una volta nella finestra di Power Pivot e il gioco è fatto. Per fare ciò, nella scheda PowerPivot premi il bottone Management (Gestire) e poi nella finestra che appare – il pulsante Vista grafico (Vista diagramma). Resta da trascinare i nomi (campi) delle colonne (chiave) comuni tra le tabelle per creare collegamenti:

Dopodiché, nel riepilogo per il Data Model, puoi inserire nell'area di riepilogo (righe, colonne, filtri, valori) qualsiasi campo di qualsiasi tabella correlata: tutto verrà collegato e calcolato automaticamente:

Vantaggio 2: conta valori univoci

Una normale tabella pivot ci dà l'opportunità di scegliere una delle numerose funzioni di calcolo integrate: somma, media, conteggio, minimo, massimo, ecc. Nel riepilogo del modello di dati, a questo elenco standard viene aggiunta una funzione molto utile per contare i numero di univoci (valori non ripetuti). Con il suo aiuto, ad esempio, puoi facilmente contare il numero di articoli unici di merce (gamma) che vendiamo in ogni città.

Fare clic con il tasto destro sul campo – comando Opzioni campo valore e nella scheda Funzionamento Scegli Numero di elementi diversi (conteggio distinto):

Vantaggio 3: formule DAX personalizzate

A volte è necessario eseguire vari calcoli aggiuntivi nelle tabelle pivot. Nei riepiloghi regolari, ciò viene eseguito utilizzando campi e oggetti calcolati, mentre il riepilogo del modello di dati utilizza misure in un linguaggio DAX speciale (DAX = Espressioni di analisi dei dati).

Per creare una misura, seleziona nella scheda PowerPivot Comando Misure – Crea misura (Misure — Nuova misura) oppure fai semplicemente clic con il pulsante destro del mouse sulla tabella nell'elenco Campi pivot e seleziona Aggiungi misura (Aggiungi misura) nel menu contestuale:

Nella finestra che si apre, imposta:

• Nome della tabelladove verrà memorizzata la misura creata.
• Nome della misura – qualsiasi nome che capisci per il nuovo campo.
• Descrizione – facoltativo.
• Formula – la cosa più importante, perché qui entriamo manualmente o clicchiamo sul pulsante fx e selezionare una funzione DAX dall'elenco, che dovrebbe calcolare il risultato quando poi gettiamo la nostra misura nell'area Valori.
• Nella parte inferiore della finestra è possibile impostare immediatamente il formato numerico per la misura nell'elenco Categoria.
  

Il linguaggio DAX non è sempre di facile comprensione perché opera non con singoli valori, ma con intere colonne e tabelle, cioè richiede qualche ristrutturazione di pensiero dopo le classiche formule di Excel. Tuttavia, ne vale la pena, perché è difficile sopravvalutare la potenza delle sue capacità nell'elaborazione di grandi quantità di dati.

Vantaggio 4: gerarchie di campi personalizzate

Spesso, quando si creano report standard, è necessario inserire le stesse combinazioni di campi nelle tabelle pivot in una determinata sequenza, ad esempio Anno-Trimestre-Mese-Giorno, o Categoria-Prodotto, o Paese-Città-Cliente ecc. Nel riepilogo del modello di dati, questo problema è facilmente risolvibile creando il tuo gerarchie — set di campi personalizzati.

Nella finestra di Power Pivot, passare alla modalità grafico con il pulsante Vista grafico linguetta Casa (Home — Vista diagramma), selezionare con Ctrl campi desiderati e fare clic con il pulsante destro del mouse su di essi. Il menu contestuale conterrà il comando Crea gerarchia (Crea gerarchia):

La gerarchia creata può essere rinominata e trascinata al suo interno con il mouse i campi richiesti, in modo che successivamente in un solo movimento possano essere gettati nel riepilogo:

Vantaggio 5: stencil personalizzati

Continuando l'idea del paragrafo precedente, nella sintesi del Data Model, puoi anche creare i tuoi insiemi di elementi per ogni campo. Ad esempio, dall'intero elenco di città, puoi facilmente creare un insieme solo di quelle che sono nella tua area di responsabilità. Oppure raccogli solo i tuoi clienti, le tue merci, ecc. in un set speciale.

Per fare ciò, nella scheda Analisi della tabella pivot nell'elenco a discesa Campi, elementi e set ci sono comandi corrispondenti (Analizza — Fields, Iarticoli e set: crea set in base a elementi di riga/colonna):

Nella finestra che si apre, puoi rimuovere, aggiungere o modificare selettivamente la posizione di qualsiasi elemento e salvare il set risultante con un nuovo nome:

Tutti i set creati verranno visualizzati nel pannello Campi tabella pivot in una cartella separata, da dove possono essere trascinati liberamente nelle aree di righe e colonne di qualsiasi nuova tabella pivot:

Vantaggio 6: nascondi selettivamente tabelle e colonne

Anche se questo è un piccolo, ma in alcuni casi molto piacevole vantaggio. Facendo clic con il pulsante destro del mouse sul nome del campo o sulla scheda della tabella nella finestra di Power Pivot, è possibile selezionare il comando Nascondi da Client Toolkit (Nascondi dagli strumenti client):

La colonna o la tabella nascosta scomparirà dal riquadro Elenco campi tabella pivot. È molto comodo se è necessario nascondere all'utente alcune colonne ausiliarie (ad esempio calcolate o colonne con valori chiave per la creazione di relazioni) o anche intere tabelle.

Vantaggio 7. Drill-down avanzato

Se fai doppio clic su qualsiasi cella nell'area del valore in una normale tabella pivot, Excel visualizza su un foglio separato una copia del frammento di dati di origine coinvolto nel calcolo di questa cella. Questa è una cosa molto utile, ufficialmente chiamata Drill-down (di solito dicono “fail”).

Nel riepilogo del modello di dati, questo pratico strumento funziona in modo più sottile. Stando in piedi su una qualsiasi cella con il risultato che ci interessa, puoi cliccare sull'icona con una lente d'ingrandimento che compare accanto ad essa (si chiama Tendenze espresse) e quindi seleziona qualsiasi campo che ti interessa in qualsiasi tabella correlata:

Successivamente, il valore corrente (Modello = Explorer) andrà nell'area del filtro e il riepilogo verrà costruito dagli uffici:

Naturalmente, tale procedura può essere ripetuta molte volte, approfondendo costantemente i tuoi dati nella direzione che ti interessa.

Vantaggio 8: Converti Pivot in Funzioni Cubo

Se selezioni una cella qualsiasi nel riepilogo per il modello di dati, quindi seleziona nella scheda Analisi della tabella pivot Comando Strumenti OLAP – Converti in formule (Analizza — Strumenti OLAP — Converti in formule), l'intero riepilogo verrà automaticamente convertito in formule. Ora i valori dei campi nell'area riga-colonna e i risultati nell'area dei valori verranno recuperati dal Data Model utilizzando le speciali funzioni del cubo: CUBEVALUE e CUBEMEMBER:

Tecnicamente, questo significa che ora non abbiamo a che fare con un riepilogo, ma con più celle con formule, ovvero possiamo facilmente fare con il nostro report qualsiasi trasformazione che non sia disponibile nel riepilogo, ad esempio inserire nuove righe o colonne al centro del rapporto, eseguire eventuali calcoli aggiuntivi all'interno del riepilogo, disporli nel modo desiderato, ecc.

Allo stesso tempo, il collegamento con i dati di origine, ovviamente, rimane e in futuro queste formule verranno aggiornate al variare delle fonti. La bellezza!

• Analisi dei fatti in una tabella pivot con Power Pivot e Power Query
• Tabella pivot con intestazione multilinea
• Crea un database in Excel usando Power Pivot