Crearea unui model de date în Excel

Un model de date vă permite să integrați date din mai multe tabele, construind efectiv o sursă de date relațională într-un registru de lucru Excel. În Excel, modelele de date sunt utilizate în mod transparent, furnizând date tabelare utilizate în rapoartele PivotTable și PivotChart. Un model de date este vizualizat ca o colecție de tabele într-o Listă de câmpuri și, de cele mai multe ori, nici nu veți ști că este acolo.

Înainte de a începe lucrul cu modelul de date, trebuie să obțineți niște date. Pentru aceasta vom utiliza experiența Obțineți & Transformare (Power Query), așa că se recomandă să faceți un pas înapoi și să urmăriți un videoclip sau să urmați ghidul nostru de învățare despre Obțineți & Transformare și Power Pivot.

Unde este Power Pivot?

Excel 2016 & Excel pentru Microsoft 365 - Power Pivot este inclus în panglică.
Excel 2013 - Power Pivot face parte din Office Professional Plus ediție de Excel 2013, dar nu este activat în mod implicit. Aflați mai multe despre pornirea programului de completare Power Pivot pentru Excel 2013.
Excel 2010 - Descărcați programul de completare Power Pivot, apoi instalați programul de completare Power Pivot.

Unde este Get & Transform (Power Query)?

Excel 2016 & Excel pentru Microsoft 365 - Get & Transform (Power Query) a fost integrat cu Excel pe fila Date.
Excel 2013 - Power Query este un program de completare inclus în Excel, dar trebuie să fie activat. Accesați Opțiuni > fișier > Programe de completare, apoi, în lista verticală Gestionare din partea de jos a panoului, selectați Programe de completare COM > Salt. Verificați Microsoft Power Query pentru Excel, apoi ok pentru a-l activa. O filă Power Query va fi adăugată la panglică.
Excel 2010 - Descărcați și instalați programul de completare Power Query.. După activare, o filă Power Query va fi adăugată în panglică.

Noțiuni de bază

Mai întâi, trebuie să obțineți niște date.

În Excel 2016 și Excel pentru Microsoft 365, utilizați Date > Obțineți & Transformare date > Preluare date pentru a importa date din orice număr de surse de date externe, cum ar fi un fișier text, un registru de lucru Excel, un site web, Microsoft Access, SQL Server sau o altă bază de date relațională care conține mai multe tabele asociate.

În Excel 2013 și 2010, accesați Power Query > Preluare date externe și selectați sursa de date.
Aplicația Excel vă solicită să selectați un tabel. Dacă doriți să obțineți mai multe tabele din aceeași sursă de date, bifați opțiunea Activați selectarea mai multor tabele . Atunci când selectați mai multe tabele, Excel creează automat un model de date pentru dvs.

Notă: Pentru aceste exemple, folosim un registru de lucru Excel cu detalii fictive despre elevi/studenți în clase și note. Puteți să descărcați registrul nostru de lucru eșantion Model de date student și să continuați. De asemenea, puteți descărca o versiune cu un model de date finalizat..
Selectați unul sau mai multe tabele, apoi faceți clic pe Încărcare.

Dacă trebuie să editați datele sursă, puteți alege opțiunea Editare . Pentru mai multe detalii, consultați: Introducere în Editor Power Query (Power Query).

Acum aveți un Model de date care conține toate tabelele importate și acestea vor fi afișate în Lista de câmpuri PivotTable.

Note:

Modelele sunt create implicit atunci când importați simultan două sau mai multe tabele în Excel.
Modelele sunt create explicit atunci când utilizați programul de completare Power Pivot pentru a importa date. În programul de completare, modelul este reprezentat într-un aspect în file similar cu Excel, unde fiecare filă conține date tabelare. Consultați Obținerea datelor utilizând programul de completare Power Pivotpentru a afla noțiunile de bază despre importul de date utilizând o bază de date SQL Server.
Un model poate conține un singur tabel. Pentru a crea un model pe baza unui singur tabel, selectați tabelul și faceți clic pe Adăugare la model de date în Power Pivot. Puteți face acest lucru dacă doriți să utilizați caracteristicile Power Pivot, cum ar fi seturi de date filtrate, coloane calculate, câmpuri calculate, indicatori KPI și ierarhii.
Relațiile dintre tabele pot fi create automat dacă importați tabele corelate care conțin relații între cheile primare și cheile externe. Aplicația Excel poate utiliza de obicei informații importate legate de relații ca bază pentru relațiile dintre tabele în modelul de date.
Pentru sfaturi despre cum să reduceți dimensiunea unui model de date, consultați Crearea unui model de date eficient din punct de vedere al memoriei utilizând Excel și Power Pivot.
Pentru explorare ulterioară, consultați Tutorial: Importul datelor în Excel și Crearea unui model de date.

Sfat: Cum vă puteți da seama dacă registrul de lucru are un model de date? Accesați Power Pivot > Gestionare. Dacă vedeți date similare foii de lucru, atunci există un model. Consultați: Aflați ce surse de date sunt utilizate într-un model de date de registru de lucru pentru a afla mai multe.

Crearea relațiilor între tabele

Următorul pas este să creați relații între tabele, astfel încât să puteți extrage date din oricare dintre ele. Fiecare tabel trebuie să aibă o cheie primară sau un identificator unic de câmp, cum ar fi ID student sau număr de clasă. Cea mai simplă modalitate este să glisați și să fixați acele câmpuri pentru a le conecta în Vizualizarea diagramă din Power Pivot.

Accesați Power Pivot > Gestionare.
Pe fila Pornire , selectați Vizualizare diagramă.
Toate tabelele importate vor fi afișate și poate doriți să vă faceți timp pentru a le redimensiona, în funcție de numărul de câmpuri pe care le are fiecare.
În continuare, glisați câmpul cheie primară dintr-un tabel în următorul. Următorul exemplu este Vizualizarea diagramă a tabelelor noastre pentru elevi/studenți:

Am creat următoarele linkuri:
- tbl_Students | ID student > tbl_Grades | Student ID
  
  Cu alte cuvinte, glisați câmpul ID student din tabelul Elevi/studenți în câmpul ID student din tabelul Note.
- tbl_Semesters | Id semestrul > tbl_Grades | Semestru
- tbl_Classes | > tbl_Grades | numărului de clasă Număr clasă
Note:
- Numele de câmpuri nu trebuie să fie aceleași pentru a crea o relație, dar trebuie să fie de același tip de date.
- Conectorii din Vizualizarea diagramă au un "1" pe o parte și un "*" pe cealaltă parte. Aceasta înseamnă că există o relație unu-la-mai-mulți între tabele și că determină modul în care se utilizează datele în rapoartele PivotTable. Consultați: Relațiile dintre tabelele dintr-un model de date pentru a afla mai multe.
- Conectorii indică doar faptul că există o relație între tabele. Acestea nu vă vor arăta de fapt ce câmpuri sunt legate între ele. Pentru a vedea linkurile, accesați > Gestionarea > >Relații > relații >Gestionare relații. În Excel, puteți să accesațiRelații > date.

Utilizarea unui model de date pentru a crea un raport PivotTable sau PivotChart

Un registru de lucru Excel poate conține un singur model de date, dar acel model poate conține mai multe tabele care pot fi utilizate în mod repetat în tot registrul de lucru. Puteți adăuga oricând mai multe tabele la un model de date existent.

În Power Pivot, accesați Gestionare.
Pe fila Pornire , selectați PivotTable.
Selectați locul unde doriți să fie plasat raportul PivotTable: o foaie de lucru nouă sau locația curentă.
Faceți clic pe OK și Excel va adăuga un Raport PivotTable gol cu panoul Listă de câmpuri afișat în partea dreaptă.

În continuare, creați un raport PivotTable sau creați un Pivot Chart. Dacă ați creat deja relații între tabele, puteți utiliza oricare dintre câmpurile lor în raportul PivotTable. Am creat deja relații în registrul de lucru eșantion Model de date student.

Adăugarea datelor existente, necorelate la un model de date

Să presupunem că ați importat sau ați copiat o mulțime de date pe care doriți să le utilizați într-un model, dar nu le-ați adăugat la modelul de date. Introducerea noilor date într-un model este mai simplă decât credeați.

Începeți prin a selecta orice celulă din datele pe care doriți să le adăugați la model. Poate fi orice zonă de date, dar datele formatate ca tabel Excel sunt cele mai bune.
Utilizați una dintre aceste abordări pentru a adăuga datele dvs.:
Faceți clic pe Power Pivot > Adăugare la model de date.
Faceți clic pe Inserare > PivotTable, apoi bifați Adăugare date la Model de date din caseta de dialog Creare PivotTable.

Intervalul sau tabelul este acum adăugat la model ca tabel legat. Pentru a afla mai multe despre lucrul cu tabelele legate dintr-un model, consultați secțiunea Adăugarea datelor utilizând tabelele legate din Excel în Power Pivot.

Adăugarea datelor la un tabel Power Pivot

În Power Pivot, nu puteți adăuga un rând la un tabel tastând direct într-un rând nou, așa cum ați face într-o foaie de lucru Excel. Dar puteți adăuga rânduri copiind și lipind sau actualizând datele sursă și reîmprospătând modelul Power Pivot.