Situatie
Excel pentru Office 365 Excel 2019 Excel 2016 Excel 2013
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, oferind date tabelare utilizate în rapoarte 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, nu veți ști niciodată că este acolo.
Înainte să începeți lucrul cu modelul de date, trebuie să obțineți date. Pentru aceasta, vom utiliza experiența obțineți & transformare (Power Query)
Noțiuni de bază
Mai întâi, trebuie să obțineți date.
- În Excel 2016 și Excel pentru Office 365, utilizați datele > Obțineți & transformați datele > obțineți 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 > obțineți 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 Activare selecție din mai multe tabele . Atunci când selectați mai multe tabele, Excel creează automat un model de date pentru dvs.
Notă: Pentru aceste exemple, utilizăm un registru de lucru Excel cu detalii de student fictive în clase și note.
- 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 de Editare . Pentru mai multe detalii,
Acum aveți un model de date care conține toate tabelele pe care le-ați importat și acestea vor fi afișate în lista de câmpuriPivotTable.
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 cu File similar cu Excel, unde fiecare filă conține date tabelare. Consultați pentru 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.
Sfat: Cum vă puteți da seama dacă registrul de lucru are un model de date? Accesați Power Pivot > Gestionați. Dacă vedeți date asemănătoare foii de lucru, atunci există un model.
Solutie
Pasi de urmat
Crearea relațiilor între tabele
Următorul pas este să creați relații între tabele, astfel încât să puteți extrage date de la oricare dintre ele. Fiecare tabel trebuie să aibă o cheie primară sau un Identificator unic de câmp, cum ar fi ID-ul de student sau numărul clasei. Cea mai simplă metodă 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 > Gestionați.
- Pe fila pornire , selectați vizualizare diagramă.
- Toate tabelele importate vor fi afișate și poate că doriți să dureze ceva timp pentru a le redimensiona, în funcție de numărul de câmpuri pe care le are fiecare.
- Apoi, glisați câmpul cheie primară dintr-un tabel în altul. Următorul exemplu este vizualizarea diagramă a tabelelor noastre studențești:
Am creat următoarele linkuri:
-
- tbl_Students | ID student > tbl_Grades | ID student
Cu alte cuvinte, glisați câmpul ID student din tabelul elevi în câmpul ID student din tabelul note.
-
- tbl_Semesters | ID semestru > tbl_Grades | Semestrul
- tbl_Classes | Număr de clasă > tbl_Grades | Număr de clasă
Note:
-
- Numele câmpurilor nu trebuie să fie identice pentru a crea o relație, dar trebuie să aibă același tip de date.
- Conectorii din vizualizarea diagramă au un “1” pe o parte și un “*” pe celălalt. Acest lucru înseamnă că există o relație unu-la-mai-mulți dintre tabele și care determină modul în care sunt utilizate datele în rapoartele PivotTable. Conectorii indică doar faptul că există o relație între tabele. Nu vă vor arăta de fapt ce câmpuri sunt legate între ele. Pentru a vedea linkurile, accesați Power Pivot > gestionați > > relații de Proiectare > gestionarea relațiilor. În Excel, puteți să accesați date > relații.
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 conține mai multe tabele, care pot fi utilizate în mod repetat în tot registrul de lucru. Puteți adăuga în orice moment mai multe tabele la un model de date existent.
- În Power Pivot, accesați gestionare.
- Pe fila pornire , selectați PivotTable.
- Selectați locul în care doriți să fie plasat raportul PivotTable: o foaie de lucru nouă sau locația curentă.
- Faceți clic pe OK, iar Excel va adăuga un raport PivotTable gol cu panoul listă de câmpuri afișat în partea dreaptă.
Apoi, creați un raport PivotTable sau creați o diagramă pivot. 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 copiat multe date pe care doriți să le utilizați într-un model, dar nu l-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.