Model de date în Excel | Cum se creează un model de date? (cu exemple)

Ce este modelul de date în Excel?

Modelul de date în Excel este un tip de tabel de date în care noi două sau mai mult de două tabele sunt în relație între ele printr-o serie comună sau mai multe date, în tabelele modelului de date și datele din diferite alte foi sau surse se reunesc pentru a forma tabel care poate avea acces la datele din toate tabelele.

Explicaţie

Permite integrarea datelor din mai multe tabele prin crearea de relații bazate pe o coloană comună.
Modelele de date sunt utilizate în mod transparent, oferind date tabulare care pot fi utilizate în tabelul pivot în Excel și în diagramele pivot în excel. Acesta integrează tabelele, permițând analize extinse folosind Tabelele pivot, Power Pivot și Power View în Excel.
Modelul de date permite încărcarea datelor în memoria Excel.
Este salvat în memorie unde nu îl putem vedea direct. Apoi, Excel poate fi instruit să raporteze datele între ele folosind o coloană comună. Partea „Model” a Modelului de date se referă la modul în care toate tabelele se raportează între ele.
Modelul de date poate accesa toate informațiile de care are nevoie chiar și atunci când informațiile sunt în mai multe tabele. După crearea modelului de date, Excel are datele disponibile în memoria sa. Cu datele din memoria sa, datele pot fi accesate în mai multe moduri.

Exemple

Puteți descărca acest șablon Excel pentru modelul de date aici - Șablon Excel pentru modelul de date

Exemplul nr. 1

Dacă avem trei seturi de date referitoare la agentul de vânzări: primul conține informații despre venituri, un al doilea conține venituri ale vânzătorului și al treilea conține cheltuieli ale vânzătorului.

Pentru a conecta aceste trei seturi de date și a face o relație cu acestea, realizăm un model de date cu următorii pași:

Convertiți seturile de date în obiecte Table:

Nu putem crea o relație cu seturi de date obișnuite. Modelul de date funcționează numai cu obiecte Excel Tables. Pentru a face acest lucru:

Pasul 1 - Faceți clic oriunde în setul de date, apoi faceți clic pe fila „Inserați” și apoi faceți clic pe „Tabel” din grupul „Tabeluri”.

Pasul 2 - Bifați sau debifați opțiunea: „Tabelul meu are antete” și faceți clic pe OK.

Pasul 3 - Cu noul tabel selectat, introduceți numele tabelului în „Nume tabel” din grupul „Instrumente”.

Pasul 4 - Acum putem vedea că primul set de date este convertit în obiect „Tabel”. Repetând acești pași pentru celelalte două seturi de date, vedem că aceștia sunt convertiți și în obiecte „Tabel” după cum urmează:

Adăugarea obiectelor „Tabel” la modelul de date: prin conexiuni sau relații.

Prin conexiuni

Selectați un tabel și faceți clic pe fila „Date”, apoi faceți clic pe „Conexiuni”.

În caseta de dialog rezultată, există o pictogramă „Adăugați”. Extindeți meniul derulant „Adăugați” și faceți clic pe „Adăugați la modelul de date”.

Faceți clic pe „Tables” în caseta de dialog rezultată, apoi selectați unul dintre tabele și faceți clic pe „Open”.

În acest sens, un model de date de registru de lucru ar fi creat cu un singur tabel și va apărea o casetă de dialog după cum urmează:

Deci, dacă repetăm acești pași și pentru celelalte două tabele, Modelul de date va conține acum toate cele trei tabele.

Acum putem vedea că toate cele trei tabele apar în Conexiuni de registru de lucru.

Prin relații

Creați relația: Odată ce ambele seturi de date sunt obiecte Table, putem crea o relație între ele. Pentru a face acest lucru:

Faceți clic pe fila „Date” și apoi faceți clic pe „Relații”.

Vom vedea o casetă de dialog goală, deoarece nu există conexiuni curente.

Faceți clic pe „Nou” și apare o altă casetă de dialog.

Extindeți meniurile drop-down „Tabel” și „Tabel asociat”: apare o casetă de dialog „Creați o relație” pentru a alege tabelele și coloanele de utilizat pentru o relație. În extinderea „Tabelelor”, selectați setul de date pe care dorim să îl analizăm într-un fel, iar în „Tabel asociat”, selectați setul de date care are valori de căutare.
Tabelul de căutare în Excel este tabelul mai mic în cazul unei relații cu mai multe relații și nu conține valori repetate în coloana comună. În extinderea „Coloană (străină)”, selectați coloana comună din tabelul principal, în „Coloană asociată (primară)”, selectați coloana comună din tabelul aferent.

Cu toate aceste patru setări selectate, faceți clic pe „OK”. O casetă de dialog apare după cum urmează, făcând clic pe „OK”.

Dacă repetăm acești pași pentru a face legătura cu alte două tabele: tabelul veniturilor cu tabelul cheltuielilor, atunci acestea sunt legate și în modelul de date după cum urmează:

Excel creează acum relația din culise prin combinarea datelor din Modelul de date pe baza unei coloane comune: ID-ul vânzătorului (în acest caz).

Exemplul nr. 2

Acum, să spunem că în exemplul de mai sus dorim să creăm un tabel pivot care să evalueze sau să analizeze obiectele tabelului:

Faceți clic pe „Inserați” -> „Tabel pivot”.

În caseta de dialog rezultată, faceți clic pe opțiunea care menționează: „Utilizați o sursă de date externă”, apoi faceți clic pe „Alegeți conexiunea”.

Faceți clic pe „Tables” în caseta de dialog rezultată și selectați Modelul de date al registrului de lucru care conține trei tabele și faceți clic pe „Open”.

Selectați opțiunea „Foaie de lucru nouă” în locație și faceți clic pe „OK”.

Panoul Câmpuri tabel pivot va afișa obiecte de tabel.

Acum, modificările din Tabelul pivot pot fi făcute în consecință, astfel încât să se analizeze obiectele tabelului, după cum este necesar.

De exemplu, în acest caz, dacă dorim să găsim venitul total sau veniturile pentru un anumit agent de vânzări, atunci se creează un tabel pivot după cum urmează:

Acest lucru este de un ajutor imens în cazul unui model / tabel care conține un număr mare de observații.

Deci, putem vedea că Tabelul pivot folosește instantaneu Modelul de date (alegându-l alegând conexiunea) în memoria Excel pentru a arăta relațiile dintre tabele.

Lucruri de amintit

Folosind Modelul de date, putem analiza date din mai multe tabele simultan.
Prin crearea de relații cu modelul de date, depășim nevoia de a folosi VLOOKUP, SUMIF, funcția INDEX și formulele MATCH, deoarece nu este nevoie să obținem toate coloanele dintr-un singur tabel.
Când seturile de date sunt importate în Excel din surse externe, atunci modelele sunt create implicit.
Relațiile de tabelă pot fi create automat dacă importăm tabele conexe care au relații cheie primare și externe.
În timp ce creăm relații, coloanele pe care le conectăm în tabele ar trebui să aibă același tip de date.
Cu tabelele pivot create cu modelul de date, putem adăuga și feliere și putem tăia tabelele pivot pe orice câmp dorim.
Avantajul modelului de date față de funcțiile LOOKUP () este că necesită mult mai puțină memorie.
Excel 2013 acceptă numai relații de la una la una sau de la mai multe relații, adică unul dintre tabele nu trebuie să aibă valori duplicate pe coloana la care conectăm.