Excel Data Model (Sommario)

  • Introduzione al modello di dati in Excel
  • Come creare un modello di dati in Excel?

Introduzione al modello di dati in Excel

La funzionalità del modello di dati di Excel consente di creare facilmente relazioni tra la creazione di report facili e i relativi set di dati in background. Rende molto più semplice l'analisi dei dati. Permette l'integrazione dei dati da una pletora di tabelle sparse su più fogli di lavoro semplicemente costruendo relazioni tra colonne corrispondenti. Funziona completamente dietro le quinte e semplifica notevolmente le funzionalità di reportistica come la tabella pivot ecc.

Nel nostro articolo, tenteremo di mostrare come creare una tabella pivot da due tabelle utilizzando la funzione Modello dati, stabilendo così una relazione tra due oggetti tabella e creando quindi una tabella pivot.

Come creare un modello di dati in Excel?

Comprendiamo come creare il modello di dati in Excel con alcuni esempi.

Puoi scaricare questo modello Excel modello dati qui - Modello Excel modello dati

Esempio 1

  • Abbiamo un elenco di prodotti e abbiamo un codice di scaffalatura per ogni prodotto. Abbiamo bisogno di un tavolo in cui abbiamo la descrizione della scaffalatura insieme ai codici della scaffalatura. Quindi, come possiamo incorporare le descrizioni di scaffalature rispetto a ciascun codice di scaffalatura? Forse molti di noi ricorrerebbero all'uso di VLOOKUP qui, ma elimineremo del tutto la necessità di utilizzare VLOOKUP qui utilizzando Excel Data Model.

  • La tabella a sinistra è la tabella dei dati e la tabella a destra è la tabella di ricerca. Come possiamo vedere dai dati, è possibile creare una relazione basata su colonne comuni.

  • Ora, il modello dati è compatibile solo con gli oggetti tabella. Pertanto, a volte potrebbe essere necessario convertire set di dati in oggetti tabella. Per farlo, segui i passaggi seguenti.
  1. Fare clic con il tasto sinistro del mouse in un punto qualsiasi del set di dati.
  2. Fai clic sulla scheda Inserisci e vai a Tabella nel gruppo Tabelle o premi semplicemente Ctrl + T.
  3. Deseleziona o seleziona l'opzione La mia tabella ha l'opzione Intestazione. Nel nostro esempio, ha davvero un'intestazione. Clicca OK.
  4. Mentre siamo ancora concentrati sulla nuova tabella, dobbiamo fornire un nome significativo nella casella Nome (a sinistra della barra della formula).

Nel nostro esempio, abbiamo chiamato la tabella Personale.

  • Ora dobbiamo fare lo stesso processo anche per la tabella di ricerca e denominarlo Shelf Code.

Creare una relazione

Quindi, in primo luogo, andremo alla scheda Dati e quindi selezioneremo Relazioni nel sottogruppo Strumenti dati. Dopo aver fatto clic sull'opzione Relazioni, all'inizio poiché non esiste alcuna relazione, quindi non avremo nulla.

Faremo prima clic su Nuovo per creare una relazione. Ora dovremo fornire i nomi delle tabelle primarie e di ricerca dall'elenco a discesa e quindi menzionare anche la colonna comune tra le due tabelle in modo da poter stabilire la relazione tra le due tabelle, dall'elenco a discesa di colonne.

  • Ora, la tabella principale è la tabella che contiene i dati. È la tabella dei dati primari - Tabella 5. D'altra parte, la tabella correlata è la tabella che contiene i dati di ricerca - è la nostra tabella di ricerca ShelfCodesTable. La tabella principale è quella che viene analizzata in base alla tabella di ricerca che contiene i dati di ricerca che renderanno i dati riportati alla fine più significativi.

  • Pertanto, la colonna comune tra le due tabelle è la colonna Codice scaffale. Questo è ciò che abbiamo usato per stabilire la relazione tra le due tabelle. Venendo alle colonne, la colonna (estranea) è quella che fa riferimento alla tabella dei dati in cui possono essere presenti valori duplicati. D'altra parte, la colonna correlata (primaria) si riferisce alla colonna nella tabella di ricerca in cui abbiamo valori univoci. Stiamo semplicemente impostando il campo per cercare i valori dalla tabella di ricerca nella tabella dei dati.
  • Una volta impostato, Excel creerebbe una relazione tra i due dietro la scena. Integra i dati e crea un modello di dati basato sulla colonna comune. Questo non è solo chiaro sui requisiti di memoria, ma è anche molto più veloce rispetto all'utilizzo di VLOOKUP in grandi cartelle di lavoro. Dopo aver definito il modello dati, Excel tratta questi oggetti come tabelle del modello dati anziché come tabella del foglio di lavoro.
  • Ora per vedere cosa ha fatto Excel, possiamo fare clic su Gestisci modelli di dati in Dati -> Strumenti dati.

  • Possiamo anche ottenere la rappresentazione schematica del modello di dati modificando la vista. Faremo clic sull'opzione Visualizza. Questo aprirà le opzioni di visualizzazione. Selezioneremo quindi la vista diagramma. Quindi vedremo la rappresentazione schematica, che mostra le due tabelle e la relazione tra loro, cioè la colonna comune - Codice scaffale.

  • Il diagramma sopra mostra una relazione uno-a-molti tra i valori della tabella di ricerca univoci e la tabella dei dati con valori duplicati.
  • Ora dovremo creare una tabella pivot. Per fare ciò, andremo alla scheda Inserisci e quindi fare clic sull'opzione Tabella pivot.

Nella finestra di dialogo Crea tabella pivot della tabella Pivot, selezioneremo l'origine come "Usa il modello di dati di questa cartella di lavoro".

  • Questo creerà la tabella Pivot e possiamo vedere che entrambe le tabelle di origine sono disponibili nella sezione sorgente.

  • Ora creeremo una tabella pivot che mostra il conteggio di ogni persona che ha accantonato gli oggetti.

  • Selezioneremo Personale nella sezione Righe dalla Tabella 5 (tabella dei dati), seguito da Descrizione (tabella di ricerca).

  • Ora trascineremo il Codice Shelf dalla Tabella 5 nella sezione Valori.

  • Ora aggiungeremo Mesi dalla Tabella 5 alla sezione Righe.

  • Oppure potremmo aggiungere i mesi come filtro e aggiungerlo alla sezione Filtri.

Esempio n. 2

  • Ora abbiamo il signor Basu che gestisce una fabbrica chiamata Basu Corporation. Basu sta cercando di stimare le entrate per il 2019 sulla base dei dati del 2018.
  • Abbiamo una tabella in cui abbiamo le entrate per il 2018 e le entrate successive a diversi livelli incrementali.

  • Quindi, abbiamo le entrate per il 2018: $ 1, 5 milioni e la crescita minima prevista per l'anno successivo è del 12%. Basu vuole una tabella che mostri le entrate a diversi livelli incrementali.
  • Creeremo la seguente tabella per le proiezioni a diversi livelli incrementali per il 2019.

  • Ora forniremo alla prima riga Entrate un riferimento alle entrate minime stimate per il 2019, ovvero $ 1, 68 M.

  • Dopo aver usato la formula la risposta è mostrata di seguito.

  • Ora selezioneremo l'intera tabella, ovvero D2: E12, quindi andremo su Dati -> Previsione -> Analisi what-if -> Tabella dati.

  • Questo aprirà la finestra di dialogo Tabella dati. Qui inseriremo la percentuale minima di incremento dalla cella B4 nella cella Input colonna. La ragione di ciò è che le nostre percentuali di crescita stimate previste nella tabella sono disposte in modo colonnare.

  • Dopo aver fatto clic su OK, l'analisi what-if popolerà automaticamente la tabella con le entrate previste alle diverse percentuali incrementali.

Esempio n. 3

  • Supponiamo ora di avere lo stesso scenario di cui sopra, tranne per il fatto che ora abbiamo anche un altro asse da considerare. Supponiamo che oltre a mostrare le entrate previste nel 2019 sulla base dei dati del 2018 e del tasso di crescita minimo previsto, ora abbiamo anche il tasso di sconto stimato.

  • Innanzitutto, avremo una tabella mostrata di seguito.

  • Ora faremo riferimento alle entrate minime previste per il 2019, ovvero dalla cella B5 alla cella D8.

  • Ora selezioneremo l'intera tabella, ovvero D8: J18, quindi andremo su Dati -> Previsioni -> Analisi what-if -> Tabella dati.

  • Questo aprirà la finestra di dialogo Tabella dati. Qui inseriremo la percentuale di incremento minima dalla cella B3 nella cella di input della colonna. La ragione di ciò è che le nostre percentuali di crescita stimate previste nella tabella sono disposte in modo colonnare. Ora inseriremo anche la percentuale minima di sconto dalla cella B4 nella cella Input riga. La ragione di ciò è che le nostre percentuali di sconto previste nella tabella sono disposte secondo una riga.

  • Clicca OK. Ciò renderà l'analisi what-if per popolare automaticamente la tabella con entrate previste alle diverse percentuali incrementali secondo le percentuali di sconto.

Cose da ricordare sul modello di dati in Excel

  • Dopo aver calcolato correttamente i valori dalla tabella dei dati, un semplice Annulla, ovvero Ctrl + Z, non funzionerà. Tuttavia, è possibile eliminare manualmente i valori dalla tabella.
  • Non è possibile eliminare una singola cella dalla tabella. È descritto come un array internamente in Excel, quindi dovremo eliminare tutti i valori.
  • Dobbiamo selezionare correttamente la cella di input di riga e la cella di input di colonna.
  • La tabella dei dati, a differenza della tabella pivot, non deve essere aggiornata ogni volta.
  • Utilizzando il modello di dati in Excel, non solo possiamo migliorare le prestazioni, ma anche andare piano con i requisiti di memoria in fogli di lavoro di grandi dimensioni.
  • I modelli di dati rendono anche la nostra analisi molto più semplice rispetto all'utilizzo di una serie di formule complicate in tutta la cartella di lavoro.

Articoli consigliati

Questa è una guida al modello di dati in Excel. Qui discutiamo come creare un modello di dati in Excel insieme a esempi pratici e modelli Excel scaricabili. Puoi anche consultare i nostri altri articoli suggeriti:

  1. Barra della formula in Excel
  2. Stampa griglie in Excel
  3. Guarda la finestra in Excel
  4. SUMIFS di Excel con date

Categoria:

Suggerimenti Excel