Modello di dati in Excel

Qual è il modello di dati in Excel?

Il modello di dati in Excel è un tipo di tabella di dati in cui due o più di due tabelle sono in relazione tra loro attraverso una o più serie di dati comuni, in tabelle del modello di dati e dati da vari altri fogli o fonti si uniscono per formare un unico tabella che può avere accesso ai dati di tutte le tabelle.

Spiegazione

  • Consente di integrare i dati di più tabelle creando relazioni basate su una colonna comune.
  • I modelli di dati vengono utilizzati in modo trasparente, fornendo dati tabulari che possono essere utilizzati nella tabella pivot in Excel e nei grafici pivot in excel. Integra le tabelle, consentendo un'analisi approfondita utilizzando tabelle pivot, Power Pivot e Power View in Excel.
  • Il modello di dati consente di caricare i dati nella memoria di Excel.
  • Viene salvato in memoria dove non possiamo vederlo direttamente. Quindi è possibile indicare a Excel di correlare i dati tra loro utilizzando una colonna comune. La parte "Modello" del modello di dati si riferisce al modo in cui tutte le tabelle si relazionano tra loro.
  • Data Model può accedere a tutte le informazioni di cui ha bisogno anche quando le informazioni sono in più tabelle. Dopo aver creato il modello di dati, Excel ha i dati disponibili nella sua memoria. Con i dati nella sua memoria, è possibile accedere ai dati in molti modi.

Esempi

È possibile scaricare questo modello Excel del modello di dati qui - Modello Excel del modello di dati

Esempio 1

Se disponiamo di tre set di dati relativi al venditore: il primo contenente le informazioni sulle entrate, un secondo contenente il reddito del venditore e il terzo contenente le spese del venditore.

Per connettere questi tre set di dati e creare una relazione con questi, creiamo un modello di dati con i seguenti passaggi:

  • Converti i set di dati in oggetti tabella:

Non possiamo creare una relazione con set di dati ordinari. Il modello di dati funziona solo con oggetti Tabelle di Excel. Per fare questo:

  • Passaggio 1: fare clic in un punto qualsiasi all'interno del set di dati, quindi fare clic sulla scheda "Inserisci" e quindi su "Tabella" nel gruppo "Tabelle".

  • Passaggio 2: seleziona o deseleziona l'opzione: "La mia tabella ha intestazioni" e fai clic su OK.

  • Passaggio 3: con la nuova tabella selezionata, immettere il nome della tabella nel "Nome tabella" nel gruppo "Strumenti".

  • Passaggio 4: ora possiamo vedere che il primo set di dati viene convertito in oggetto "Tabella". Ripetendo questi passaggi per gli altri due set di dati, vediamo che vengono anche convertiti in oggetti "Tabella" come di seguito:

Aggiunta degli oggetti "Tabella" al modello di dati: tramite connessioni o relazioni.

Tramite connessioni

  • Selezionare una tabella e fare clic sulla scheda "Dati", quindi fare clic su "Connessioni".

  • Nella finestra di dialogo risultante, è presente un'icona di "Aggiungi". Espandi il menu a discesa "Aggiungi" e fai clic su "Aggiungi al modello di dati".

  • Fare clic su "Tabelle" nella finestra di dialogo risultante, quindi selezionare una delle tabelle e fare clic su "Apri".

In questo modo, verrà creato un modello di dati della cartella di lavoro con una tabella e verrà visualizzata una finestra di dialogo come segue:

Quindi, se ripetiamo questi passaggi anche per le altre due tabelle, il modello di dati conterrà ora tutte e tre le tabelle.

Ora possiamo vedere che tutte e tre le tabelle vengono visualizzate nelle connessioni della cartella di lavoro.

Via Relationships

Creare la relazione: una volta che entrambi i set di dati sono oggetti Tabella, possiamo creare una relazione tra di loro. Per fare questo:

  • Fare clic sulla scheda "Dati" e quindi su "Relazioni".

  • Vedremo una finestra di dialogo vuota poiché non ci sono connessioni correnti.

  • Fare clic su "Nuovo" e verrà visualizzata un'altra finestra di dialogo.

  • Espandi i menu a discesa "Tabella" e "Tabella correlata": viene visualizzata la finestra di dialogo "Crea relazione" per selezionare le tabelle e le colonne da utilizzare per una relazione. Nell'espansione di "Tabelle", seleziona il set di dati che desideri analizzare in qualche modo e in "Tabella correlata", seleziona il set di dati che ha valori di ricerca.
  • La tabella di ricerca in Excel è la tabella più piccola in caso di relazioni da uno a molti e non contiene valori ripetuti nella colonna comune. Nell'espansione di 'Colonna (Esterno)', seleziona la colonna comune nella tabella principale, in 'Colonna correlata (Principale)', seleziona la colonna comune nella tabella correlata.

  • Con tutte queste quattro impostazioni selezionate, fare clic su "OK". Facendo clic su "OK" appare una finestra di dialogo come segue.

Se ripetiamo questi passaggi per mettere in relazione altre due tabelle: Tabella dei ricavi con tabella delle spese, anche queste vengono correlate nel modello di dati come segue:

Excel ora crea la relazione dietro le quinte combinando i dati nel modello di dati in base a una colonna comune: ID venditore (in questo caso).

Esempio n. 2

Ora, diciamo nell'esempio precedente di voler creare una tabella pivot che valuta o analizza gli oggetti Table:

  • Fare clic su "Inserisci" -> "Tabella pivot".

  • Nella finestra di dialogo risultante, fare clic sull'opzione che indica: "Utilizza un'origine dati esterna", quindi fare clic su "Scegli connessione".

  • Fare clic su "Tabelle" nella finestra di dialogo risultante e selezionare il modello di dati della cartella di lavoro contenente tre tabelle e fare clic su "Apri".

  • Seleziona l'opzione "Nuovo foglio di lavoro" nella posizione e fai clic su "OK".

  • Il riquadro Campi tabella pivot mostrerà gli oggetti tabella.

  • Ora le modifiche nella tabella pivot possono essere apportate di conseguenza in modo da analizzare gli oggetti della tabella come richiesto.

Ad esempio, in questo caso, se desideriamo trovare le entrate o le entrate totali per un particolare venditore, viene creata una tabella pivot come segue:

Questo è di immenso aiuto nel caso di un modello / tabella contenente un gran numero di osservazioni.

Quindi, possiamo vedere che la tabella pivot utilizza immediatamente il modello di dati (selezionandolo scegliendo la connessione) nella memoria di Excel per mostrare le relazioni tra le tabelle.

Cose da ricordare

  • Utilizzando il modello di dati, possiamo analizzare i dati da più tabelle contemporaneamente.
  • Creando relazioni con il modello di dati, superiamo la necessità di utilizzare CERCA.VERT, SOMMA.SE, la funzione INDICE e le formule CONFRONTA in quanto non è necessario ottenere tutte le colonne all'interno di una singola tabella.
  • Quando i set di dati vengono importati in Excel da origini esterne, i modelli vengono creati in modo implicito.
  • Le relazioni tra tabelle possono essere create automaticamente se importiamo tabelle correlate che hanno relazioni di chiave primaria ed esterna.
  • Durante la creazione delle relazioni, le colonne che stiamo collegando nelle tabelle dovrebbero avere lo stesso tipo di dati.
  • Con le tabelle pivot create con il modello dati, possiamo anche aggiungere filtri dei dati e suddividere le tabelle pivot su qualsiasi campo desideriamo.
  • Il vantaggio del modello di dati rispetto alle funzioni LOOKUP () è che richiede una quantità di memoria sostanzialmente inferiore.
  • Excel 2013 supporta solo le relazioni uno a uno o uno a molti, ovvero una delle tabelle non deve avere valori duplicati nella colonna a cui ci colleghiamo.