Come creare relazioni tra più tabelle utilizzando il modello di dati in Excel
Excel è un potente strumento per l’analisi dei dati e la successiva automazione durante la gestione di grandi set di dati. Potresti dedicare molto tempo all’analisi di tonnellate di dati utilizzando VLOOKUP, INDEX-MATCH, SUMIF, ecc.
Grazie al modello di dati Excel, puoi risparmiare tempo prezioso attraverso report automatici dei dati. Scopri con quanta facilità puoi assegnare una relazione tra due tabelle utilizzando il modello di dati e un’illustrazione di tale relazione in una tabella pivot nella sezione seguente.
I requisiti di base
Avrai bisogno di Power Pivot e Power Query (Recupera e trasforma) per eseguire diverse attività durante la creazione del modello di dati Excel . Ecco come ottenere queste funzionalità nella cartella di lavoro di Excel:
Come ottenere Power Pivot
1. Excel 2010: è necessario scaricare il componente aggiuntivo Power Pivot da Microsoft e quindi installarlo per il programma Excel sul computer.
2. Excel 2013: l’ edizione Office Professional Plus di Excel 2013 include Power Pivot. Ma è necessario attivarlo prima del primo utilizzo. Ecco come:
- Fare clic su File sulla barra multifunzione di una cartella di lavoro di Excel.
- Quindi fare clic su Opzioni per aprire le Opzioni di Excel .
- Ora, fai clic su Componenti aggiuntivi .
- Seleziona Componenti aggiuntivi COM facendo clic sul menu a discesa della casella Gestisci .
- Fare clic su Vai e quindi selezionare la casella di controllo per Microsoft Power Pivot per Excel .
3. Excel 2016 e versioni successive: troverai il menu PowerPivot sulla barra multifunzione .
Come ottenere Power Query (Recupera e trasforma)
1. Excel 2010: è possibile scaricare il componente aggiuntivo Power Query da Microsoft . Dopo l’installazione, Power Query verrà visualizzato sulla barra multifunzione .
2. Excel 2013: è necessario attivare Power Query seguendo gli stessi passaggi appena eseguiti per rendere funzionante Power Pivot in Excel 2013.
3. Excel 2016 e versioni successive: è possibile trovare Power Query (Recupera e trasforma) accedendo alla scheda Dati sulla barra multifunzione di Excel.
Crea modello di dati importando i dati nella cartella di lavoro di Excel
Per questo tutorial, puoi ottenere dati di esempio preformattati da Microsoft:
Download : dati degli studenti di esempio (solo dati) | Dati di esempio degli studenti (modello completo)
È possibile importare un database con più tabelle correlate da molte origini come cartelle di lavoro di Excel, Microsoft Access, siti Web, SQL Server, ecc. Quindi è necessario formattare il set di dati in modo che Excel possa utilizzarlo. Ecco i passaggi che puoi provare:
1. In Excel 2016 e versioni successive, fare clic sulla scheda Dati e selezionare Nuova query .
2. Sono disponibili diversi modi per importare dati da fonti esterne o interne. Scegli quello che fa per te.
3. Se si utilizza l’edizione di Excel 2013, fare clic su Power Query sulla barra multifunzione e quindi selezionare Carica dati esterni per scegliere i dati per l’importazione.
4. Verrà visualizzata la casella Navigatore in cui è necessario scegliere quali tabelle importare. Fare clic sulla casella di controllo Seleziona più elementi per selezionare più tabelle per l’importazione.
5. Fare clic su Carica per completare il processo di importazione.
6. Excel creerà un modello di dati utilizzando queste tabelle. È possibile visualizzare le intestazioni delle colonne della tabella negli elenchi dei campi della tabella pivot .
Puoi anche utilizzare le funzioni di PowerPivot come colonne calcolate, KPI, gerarchie, campi calcolati e set di dati filtrati da Excel Data Model. A tale scopo, dovrai generare il modello di dati da una singola tabella. Puoi provare questi passaggi:
1. Formattare i dati in un modello tabulare selezionando tutte le celle contenenti dati e quindi fare clic su Ctrl + T.
2. Selezionare ora l’intera tabella e quindi fare clic sulla scheda PowerPivot sulla barra multifunzione .
3. Nella sezione Tabelle , fare clic su Aggiungi al modello di dati .
Excel creerà relazioni tra tabelle tra i dati correlati dal modello di dati. Per questo, dovrebbero esserci relazioni di chiave primaria ed esterna all’interno delle tabelle importate.
Excel utilizza le informazioni sulla relazione dalla tabella importata come base per generare connessioni tra le tabelle in un modello di dati.
Crea relazioni tra le tabelle nel modello di dati
Ora che hai un modello di dati nella cartella di lavoro di Excel, dovrai definire le relazioni tra le tabelle per creare report significativi. È necessario assegnare un identificatore di campo univoco o una chiave primaria a ciascuna tabella, come ID semestre, numero di classe, ID studente, ecc.
La funzionalità di visualizzazione diagramma di PowerPivot ti consentirà di trascinare e rilasciare questi campi per creare una relazione. Segui questi passaggi per creare collegamenti a tabelle in Excel Data Model:
1. Sulla barra multifunzione della cartella di lavoro di Excel, fare clic sul menu PowerPivot .
2. Ora, fai clic su Gestisci nella sezione Modello di dati . Vedrai l’editor di Power Pivot come mostrato di seguito:
3. Fare clic sul pulsante Visualizza diagramma situato nella sezione Visualizza della scheda Home di Power Pivot. Vedrai le intestazioni delle colonne della tabella raggruppate in base al nome della tabella.
4. Ora sarai in grado di trascinare e rilasciare l’identificatore di campo univoco da una tabella a un’altra. Di seguito è riportato lo schema della relazione tra le quattro tabelle del modello di dati di Excel:
Di seguito viene descritto il collegamento tra le tabelle:
- Tabella Studenti | ID studente per la tabella Grades | ID studente
- Semestri di tavolo | ID semestre per la tabella Grades | Semestre
- Classi da tavolo | Numero di classe per classificare i voti | ID della classe
5. È possibile creare relazioni scegliendo una coppia di colonne di valori univoci. Se sono presenti duplicati, vedrai il seguente errore:
6. Noterai Star (*) su un lato e One (1) sull’altro nella vista diagramma delle relazioni. Definisce l’esistenza di una relazione uno-a-molti tra le tabelle.
7. Nell’editor di Power Pivot, fare clic sulla scheda Progettazione e quindi selezionare Gestisci relazioni per sapere quali campi effettuano le connessioni.
Genera una tabella pivot utilizzando il modello di dati di Excel
È ora possibile creare una tabella pivot o un grafico pivot per visualizzare i dati da Excel Data Model. Una cartella di lavoro di Excel può contenere un solo modello di dati, ma puoi continuare ad aggiornare le tabelle.
Poiché i dati cambiano nel tempo, puoi continuare a utilizzare lo stesso modello e risparmiare tempo lavorando con lo stesso set di dati. Noterai un maggiore risparmio di tempo quando lavori sui dati in migliaia di righe e colonne. Per creare un rapporto basato su tabella pivot, segui questi passaggi:
1. Nell’editor di Power Pivot, fare clic sulla scheda Home .
2. Sulla barra multifunzione , fare clic su Tabella pivot .
3. Scegliere uno qualsiasi tra Nuovo foglio di lavoro o Foglio di lavoro esistente.
4. Seleziona OK . Excel aggiungerà una tabella pivot che mostrerà il riquadro Elenco campi a destra.
Di seguito è riportata una visualizzazione olistica di una tabella pivot creata utilizzando il modello di dati di Excel per i dati degli studenti di esempio utilizzati in questo tutorial. Puoi anche creare tabelle o grafici pivot professionali da big data utilizzando lo strumento Excel Data Model.
Trasforma set di dati complessi in report semplici utilizzando il modello di dati di Excel
Il modello di dati di Excel utilizza i vantaggi della creazione di relazioni tra tabelle per produrre tabelle o grafici pivot significativi per scopi di reporting dei dati.
È possibile aggiornare continuamente la cartella di lavoro esistente e pubblicare report sui dati aggiornati. Non è necessario modificare le formule o investire tempo nello scorrimento di migliaia di colonne e righe ogni volta che i dati di origine vengono aggiornati.
