Beneficii di Pivot da Data Model

Quandu custruisce una tabella di pivot in Excel, in a prima finestra di dialogu, induve ci hè dumandatu di stabilisce u intervallu iniziale è sceglite un locu per inserisce a tabella di pivot, ci hè una casella di spunta pocu ma assai impurtante sottu - Aghjunghjite sta dati à u Modellu di Dati (Aghjunghje sta dati à u mudellu di dati) è, un pocu più altu, u cambiamentu Aduprate u mudellu di dati di stu libru (Usà u mudellu di dati di stu libru di travagliu):

Sfurtunatamente, parechji utilizatori chì anu familiarizatu cù e tavule pivot per un bellu pezzu è l'utilizanu bè in u so travagliu qualchì volta ùn capiscenu micca veramente u significatu di queste opzioni è ùn l'utilizanu mai. È in vanu. Dopu tuttu, a creazione di una tabella pivot per u Mudellu di Dati ci dà parechji vantaghji assai impurtanti cumparatu cù a tabella pivot classica di Excel.

In ogni casu, prima di cunsiderà questi "buns" da vicinu, capimu prima ciò chì, in fattu, hè stu Modellu di Dati?

Cosa hè un mudellu di dati

Modellu di dati (abbreviatu cum'è MD o DM = Data Model) hè una zona speciale in un schedariu Excel induve pudete almacenà dati tabulari - una o più tavule ligati, se vulete, l'una à l'altru. In fatti, questa hè una piccula basa di dati (cube OLAP) incrustata in un libru di Excel. Paragunatu à l'almacenamiento classicu di dati in forma di tavule regulare (o intelligente) nantu à fogli di Excel stessu, u mudellu di dati hà parechji vantaghji significativi:

• I tavulini ponu esse finu à 2 miliardi di linii, è un fogliu di Excel pò mette un pocu più di 1 milione.
• Malgradu a dimensione gigantesca, u trattamentu di tali tavule (filtrazione, classificazione, calculi nantu à elli, riassuntu di custruzzione, etc.) hè realizatu. assai veloce Moltu più veloce di Excel stessu.
• Cù i dati in u Modellu, pudete fà calculi supplementari (se vulete, assai cumplessu) utilizendu lingua DAX integrata.
  
• Tutte l'infurmazioni caricate in u Modellu Dati hè assai fortemente compressa aduprendu un archiviu speciale integratu è aumenta moderatamente a dimensione di u schedariu Excel originale.
  

U mudellu hè gestitu è ​​calculatu da un add-in speciale integratu in Microsoft Excel - powerpivotcirca chì aghju digià scrittu. Per attivà, nantu à a tabulazione documentaliste cliccate Add-ins COM (Sviluppatore - Add-ins COM) è verificate a casella adatta:

Sè tabs documentaliste (Sviluppatore)ùn pudete micca vede nantu à a cinta, pudete accende à traversu File - Opzioni - Ribbon Setup (File - Opzioni - Customize Ribbon). Se in a finestra mostrata sopra in a lista di add-ins COM ùn avete micca Power Pivot, allora ùn hè micca inclusu in a vostra versione di Microsoft Office 🙁

In a tabulazione Power Pivot chì appare, ci sarà un grande buttone verde chjaru Management (Gestisce), clicchendu nantu à quale apre a finestra Power Pivot in cima di Excel, induve vedemu u cuntenutu di u Modellu di Dati di u libru attuale:

Una nota impurtante nantu à a strada: un libru di travagliu Excel pò cuntene solu un mudellu di dati.

Caricà e tavule in u Modellu di Dati

Per carricà dati in u Modellu, prima turnemu a tavula in una scurciatoia dinamica "intelligente". Ctrl+T è dà un nome amichevule nantu à a tabulazione regala (Cuncepimentu). Questu hè un passu necessariu.

Allora pudete aduprà qualsiasi di i trè metudi per sceglie:

• Pulsà u buttone Add to Model (Aghjunghje à u mudellu di dati) Insignia powerpivot Insignia Home (Casa).
  
• Scelta di squadre Inserisce - PivotTable (Inserisci - Pivot Table) è attivate a casella di cuntrollu Aghjunghjite sta dati à u Modellu di Dati (Aghjunghje sta dati à u mudellu di dati). In questu casu, sicondu i dati caricati in u Modellu, un tavulu pivot hè ancu custruitu immediatamente.
  
• In a tabulazione Avanzata Dati (Data) cliccate nantu à u buttone Da Table/Range (Da a Tavola / Gamma)per carricà a nostra tavula in l'editore Power Query. Stu percorsu hè u più longu, ma, se vulete, quì pudete eseguisce a pulizia di dati supplementari, edità è ogni tipu di trasfurmazioni, in quale Power Query hè assai forte.

  Allora i dati pettinati sò caricati in u Modellu da u cumandamentu Casa — Chiudi è Carica — Chiudi è Carica in… (Home — Close&Load — Close&Load to…). In a finestra chì apre, selezziunate l'opzione Basta à creà una cunnessione (Crea solu cunnessione) è, più impurtante, mette un tick Aghjunghjite sta dati à u Modellu di Dati (Aghjunghje sta dati à u mudellu di dati).
  

Custruemu un riassuntu di u Modellu di Dati

Per custruisce un mudellu di dati riassuntu, pudete aduprà qualsiasi di trè approcci:

• Pulsà u buttone tavula riassuntu (Table Pivot) in a finestra Power Pivot.
• Selezziunate i cumandamenti in Excel Inserisce - PivotTable è cambia in modu Aduprate u mudellu di dati di stu libru (Inserisci - Pivot Table - Aduprate u mudellu di dati di stu libru).
• Scelta di squadre Inserisce - PivotTable (Inserisci - Pivot Table) è attivate a casella di cuntrollu Aghjunghjite sta dati à u Modellu di Dati (Aghjunghje sta dati à u mudellu di dati). A tavula "intelligente" attuale serà caricata in u Modellu è una tabella riassuntu serà custruitu per tuttu u Modellu.

Avà chì avemu capitu cumu caricate dati in u Modellu di Dati è custruisce un riassuntu nantu à questu, scopremu i benefici è i vantaghji chì questu ci dà.

Beneficiu 1: Relazioni trà e tavule senza aduprà formule

Un riassuntu regulare pò esse custruitu solu utilizendu dati da una tavola fonte. Sè vo avete parechji di elli, per esempiu, vendite, lista di prezzi, annuariu di i clienti, registru di cuntratti, etc., allura vi tuccherà prima à cullà dati da tutti i tavulini in unu utilizendu funzioni cum'è VLOOKUP. (CERCA V), INDICE (INDICE), PIÙ ESPOSTA (MATCH), SUMMESLIMN (SUMIFS) è simili. Questu hè longu, tedioso è cunduce u vostru Excel in un "pensu" cù una grande quantità di dati.

In u casu di un riassuntu di u Modellu di Dati, tuttu hè assai più simplice. Hè abbastanza per stabilisce relazioni trà e tavule una volta in a finestra Power Pivot - è hè fatta. Per fà questu, nantu à a tabulazione powerpivot premi u buttone Management (Gestisce) è dopu in a finestra chì appare - u buttone Vista Chart (Vista di schema). Resta à trascinà i nomi di colonna cumuni (chiavi) (campi) trà e tavule per creà ligami:

Dopu quì, in u riassuntu per u Modellu di Dati, pudete scaccià in l'area di riassuntu (fila, culonne, filtri, valori) qualsiasi campi da qualsiasi tabelle rilativi - tuttu serà ligatu è calculatu automaticamente:

Beneficiu 2: Cuntà i valori unichi

Un tavulu pivot regulare ci dà l'uppurtunità di sceglie una di parechje funzioni di calculu integrate: somma, media, count, minimum, maximum, etc. In u riassuntu di Data Model, una funzione assai utile hè aghjuntu à sta lista standard per cuntà i numeru di unicu (valori micca ripetitivi). Cù u so aiutu, per esempiu, pudete facilmente cuntà u numeru di articuli unichi di merchenzie (range) chì vendemu in ogni cità.

Cliccate dritta nant'à u campu - cumanda Opzioni di campu di valore è nantu à a tabulazione Internet Scegli Numero di elementi diffirenti (Conte distintu):

Beneficiu 3: Formule DAX persunalizate

Calchì volta ci vole à fà diversi calculi supplementari in tavule pivot. In sintesi regulare, questu hè fattu cù campi è oggetti calculati, mentre chì u riassuntu di u mudellu di dati usa misure in una lingua DAX especial (DAX = Data Analysis Expressions).

Per creà una misura, selezziunate nantu à a tabulazione powerpivot Command Misure - Crea una misura (Misure - Nova misura) o ghjustu cliccà nantu à a tavula in a lista Pivot Fields è selezziunate Aghjunghjite a misura (Aghjunghje misura) in u menu cuntestu:

In a finestra chì si apre, stabilisce:

• Nome di a tavolainduve a misura creata serà almacenata.
• Misura u nome - qualsiasi nome chì avete capitu per u novu campu.
• Description - optional.
• Formula - u più impurtante, perchè quì avemu o entre manually, o cliccate nant'à u buttone fx è selezziunà una funzione DAX da a lista, chì deve calculà u risultatu quandu avemu allora a nostra misura in l'area di i Valori.
• In a parte inferiore di a finestra, pudete stabilisce immediatamente u formatu numericu per a misura in a lista Catigurìa.
  

A lingua DAX ùn hè micca sempre faciule da capisce perchè ùn opera micca cù i valori individuali, ma cù e culonni è e tavule sanu, vale à dì esige una certa ristrutturazione di u pensamentu dopu à e formule classic di Excel. Tuttavia, vale a pena, perchè u putere di e so capacità in u processu di grande quantità di dati hè difficiule di sopravalutà.

Beneficiu 4: Gerarchie di campi persunalizati

Spessu, quandu creanu rapporti standard, avete da scaccià e stesse cumminazzioni di campi in tabelle pivot in una sequenza data, per esempiu. Annu-Quarter-Mesi-Giornu, o Category-Produttu, o Paese-Cità-Client etc In u riassuntu Modellu Dati, stu prublemu hè facirmenti risolta da creà u vostru propriu ierarchie - set di campi persunalizati.

In a finestra di Power Pivot, cambia à u modu di carta cù u buttone Vista Chart Insignia Home (Casa — Vista di schema), sceglite cù Ctrl campi desiderati è cliccate dritta nantu à elli. U menù di cuntestu cuntene u cumandimu Crea Gerarchia (Creà ierarchia):

A ghjerarchia creata pò esse rinominata è trascinata in questu cù u mouse i campi richiesti, perchè più tardi in un muvimentu ponu esse ghjittati in u riassuntu:

Beneficiu 5: stencils persunalizati

Cuntinuendu l'idea di u paràgrafu precedente, in u riassuntu di u Mudellu di Dati, pudete ancu creà u vostru propiu set di elementi per ogni campu. Per esempiu, da tutta a lista di e cità, pudete facilmente fà un set di solu quelli chì sò in a vostra zona di rispunsabilità. O cullate solu i vostri clienti, i vostri beni, etc. in un set speciale.

Per fà questu, nantu à a tabulazione Analisi di a tabella di pivot in a lista drop-down Campi, Articuli è Sets ci sò cumandamenti currispundenti (Analise - Campi, Items & Sets - Crea un set basatu nantu à elementi di fila / colonna):

In a finestra chì si apre, pudete sguassate selettivamente, aghjunghje o cambià a pusizione di qualsiasi elementi è salvà u settore risultatu sottu un novu nome:

Tutti i setti creati seranu visualizati in u pannellu Campi di PivotTable in un cartulare separatu, da induve ponu esse trascinati liberamente à e fila è e colonne di ogni nova PivotTable:

Beneficiu 6: Oculte Selettivamente Tavule è Culonni

Ancu s'ellu hè un vantaghju chjucu, ma assai piacevule in certi casi. Facendu clic destro nantu à u nome di u campu o nantu à a tabulazione di a tavula in a finestra Power Pivot, pudete selezziunate u cumandamentu Oculta da Client Toolkit (Oculta da i Strumenti di u Cliente):

A colonna o tavula oculta sparirà da u pane PivotTable Field List. Hè assai còmuda s'è avete bisognu di ammuccià da l'utilizatori alcune colonne ausiliarie (per esempiu, calculate o colonne cù valori chjave per creà relazioni) o ancu tavule intere.

Beneficiu 7. Advanced drill-down

Se fate un doppiu clicu nantu à qualsiasi cellula in l'area di valore in una tabella pivot regulare, allora Excel mostra nantu à una foglia separata una copia di u fragmentu di dati fonte chì era implicatu in u calculu di sta cellula. Questa hè una cosa assai utile, ufficialmente chjamata Drill-down (in solitu dicenu "fail").

In u riassuntu di u Mudellu di Dati, stu strumentu praticu travaglia più sottili. Standendu nant'à ogni cellula cù u risultatu chì ci interessa, pudete cliccà nantu à l'icona cù una lente d'ingrandimentu chì apparisce accantu à questu (hè chjamatu Express Trends) è dopu selezziunate qualsiasi campu chì site interessatu in ogni tavula ligata:

Dopu quì, u valore attuale (Model = Explorer) andrà in l'area di filtru, è u riassuntu serà custruitu da l'uffizii:

Di sicuru, una tale prucedura pò esse ripetuta parechje volte, cunsistenti in i vostri dati in a direzzione chì vi interessa.

Beneficiu 8: Cunvertite Pivot à Funzioni Cube

Se selezziunate una cellula in u riassuntu per u Modellu di Dati è dopu selezziunate nantu à a tabulazione Analisi di a tabella di pivot Command Strumenti OLAP - Cunvertite in Formule (Analise - Strumenti OLAP - Cunvertite in formule), allura tuttu u riassuntu serà automaticamente cunvertitu in formule. Avà i valori di u campu in l'area di a fila-colonna è i risultati in l'area di u valore seranu recuperati da u Modellu di Dati utilizendu e funzioni speciali di cubi: CUBEVALUE è CUBEMEMBER:

Tecnicamente, questu significa chì avà ùn avemu micca trattatu cù un riassuntu, ma cù parechje cellule cù formule, vale à dì pudemu fà facilmente qualsiasi trasfurmazioni cù u nostru rapportu chì ùn sò micca dispunibili in u riassuntu, per esempiu, inserisce novi fila o culonni in u mezu. di u rapportu, fate ogni calculu supplementu in u riassuntu, arrangeli in ogni modu desideratu, etc.

À u listessu tempu, a cunnessione cù i dati fonte, sicuru, ferma è in u futuru sti formule seranu aghjurnati quandu i fonti cambianu. A bellezza !

• Analisi di i fatti di u pianu in una tabella pivot cù Power Pivot è Power Query
• Tavola pivotante cù intestazione multilinea
• Crea una basa di dati in Excel cù Power Pivot