Tutorial: Importul datelor în Excel și crearea unui Model de date

Secțiunile acestui tutorial sunt următoarele:

• Importul datelor dintr-o bază de date

• Importul datelor dintr-o foaie de lucru

• Importul datelor folosind copierea și lipirea

• Crearea unei relații între date importate

• Punct de control și test

La sfârșitul acestui tutorial este un test pe care îl puteți face pentru a vă testa cunoștințele.

Această serie de tutoriale folosește date care descriu medalii olimpice, țări gazdă și diferite evenimente sportive olimpice. Vă sugerăm să treceți prin fiecare tutorial în ordine. 

Importul datelor dintr-o bază de date

Vom începe acest tutorial cu un registru de lucru gol. Obiectivul acestei secțiuni este de a vă conecta la o sursă externă de date și de a importa datele în Excel pentru analiză ulterioară.

Să începem prin a descărca unele date de pe internet. Datele descriu medaliile olimpice și reprezintă o bază de date Microsoft Access.

1. Faceți clic pe următoarele linkuri pentru a descărca fișiere pe care le vom folosi în timpul acestei serii de tutoriale. Descărcați fiecare dintre cele patru fișiere într-o locație ușor accesibilă, cum ar fi Descărcări sau Documentele mele, sau într-un folder nou pe care îl creați:
   > baza de date
   Access OlympicMedals.accdb > OlympicSports.xlsx registru de lucru
   Excel > Population.xlsx registru de lucru
   Excel > DiscImage_table.xlsx registru de lucru Excel

2. În Excel, deschideți un registru de lucru necompletat.

3. Faceți clic pe DATE > Preluare date externe > Din Access. Panglica se ajustează dinamic pe baza lățimii registrului de lucru, astfel încât comenzile de pe panglică pot arăta puțin diferit față de cele de pe următoarele ecrane. Primul ecran prezintă panglica atunci când un registru de lucru este larg, a doua imagine prezintă un registru de lucru care a fost redimensionat pentru a ocupa doar o parte a ecranului.
   
   
   
   
   
    

4. Selectați fișierul OlympicMedals.accdb descărcat și faceți clic pe Deschidere. Apare următoarea fereastră Selectare tabel, care afișează tabelele găsite în baza de date. Tabelele dintr-o bază de date sunt similare cu foile de lucru sau tabelele din Excel. Bifați caseta de selectare Activați selectarea multiplă a tabelelor și selectați toate tabelele. Apoi faceți clic pe OK.
   
   

5. Apare fereastra Import date.

   Notă: Observați caseta de selectare din partea de jos a ferestrei care vă permite să Adăugați aceste date la modelul de date, afișată în următorul ecran. Un model de date este creat automat atunci când importați sau lucrați cu două sau mai multe tabele simultan. Un model de date integrează tabelele, activând analiza extinsă utilizând rapoarte PivotTable, Power Pivot și Power View. Când importați tabele dintr-o bază de date, relațiile existente între aceste tabele se utilizează pentru a crea modelul de date în Excel. Modelul de date este transparent în Excel, dar îl puteți vizualiza și modifica direct utilizând programul de completare Power Pivot. Modelul de date este discutat în detaliu mai târziu în acest tutorial.

   
   Selectați opțiunea Raport PivotTable , care importă tabelele în Excel și pregătește un PivotTable pentru analiza tabelelor importate, apoi faceți clic pe OK.
   
   

6. Odată ce datele sunt importate, este creat un PivotTable folosind tabelele importate.
   
   

Cu datele importate în Excel și cu modelul de date creat automat, sunteți gata să explorați datele.

Explorarea datelor utilizând un raport PivotTable

Explorarea datelor importate este ușoară dacă se folosește un PivotTable. Într-un PivotTable, glisați câmpuri (similare cu coloanele din Excel) din tabele (cum ar fi tabelele tocmai importate din baza de date Access) în diferite zone ale raportului PivotTable pentru a regla modul în care prezintă datele. Un PivotTable are patru zone: FILTRE, COLOANE, RÂNDURI și VALORI.

Poate fi nevoie să faceți mai multe experimente pentru a determina în ce zonă ar trebui glisat un câmp. Puteți glisa cât de multe sau cât de puține câmpuri doriți din tabele, până când PivotTable prezintă datele așa cum doriți să le vedeți. Explorați prin glisarea câmpurilor în diferite zone PivotTable; datele fundamentale nu sunt afectate atunci când aranjați câmpurile într-un PivotTable.

Să explorăm datele despre medalii olimpice din PivotTable, începând cu medaliații olimpici organizați după disciplină, tipul medaliei și țara sau regiunea sportivului.

1. În Câmpuri PivotTable, extindeți tabelul Medals făcând clic pe săgeata de lângă acesta. Găsiți câmpul NOC_CountryRegion din tabelul Medals extins și glisați-l în zona COLOANE. NOC reprezintă Comitetele Olimpice Naționale (National Olympic Committees), unitatea organizațională pentru o țară sau regiune.

2. Apoi, din tabelul Disciplines, glisați Discipline în zona RÂNDURI.

3. Să filtrăm disciplinele pentru a afișa doar cinci sporturi: Archery, Diving, Fencing, Figure Skating și Speed Skating. Puteți face acest lucru din interiorul zonei Câmpuri PivotTable sau din filtrul Etichete de rânduri din raportul PivotTable.

   1. Faceți clic oriunde în raportul PivotTable pentru a vă asigura că este selectat PivotTable Excel. În lista Câmpuri PivotTable , unde este extins tabelul Disciplines , treceți cu mouse-ul peste câmpul Discipline și apare o săgeată verticală în partea dreaptă a câmpului. Faceți clic pe lista verticală, faceți clic pe (Selectare totală)pentru a elimina toate selecțiile, apoi defilați în jos și selectați Archery, Diving, Fencing, Figure Skating și Speed Skating. Faceți clic pe OK.

   2. Sau, în secțiunea Etichete de rânduri din PivotTable, faceți clic pe meniul vertical de lângă Etichete de rânduri în raportul PivotTable, faceți clic pe (Selectare totală) pentru a elimina toate selecțiile, apoi defilați în jos și selectați Archery, Diving, Fencing, Figure Skating și Speed Skating. Faceți clic pe OK.

4. În Câmpuri PivotTable, din tabelul Medals, glisați Medal în zona VALORI. Deoarece valorile trebuie să fie numerice, Excel modifică automat Medal în Count of Medal.

5. Din tabelul Medals, selectați Medal din nou și glisați-o în zona FILTRE.

6. Să filtrăm raportul PivotTable pentru a afișa numai acele țări sau regiuni cu mai mult de 90 medalii în total. Iată cum.

   1. În raportul PivotTable, faceți clic pe meniul vertical, la dreapta de Etichete de coloană.

   2. Selectați Filtre de valori și selectați Mai mare decât...

   3. Tastați 90 în ultimul câmp (în dreapta). Faceți clic pe OK.
      

Raportul dvs. PivotTable arată ca următorul ecran.

Cu foarte puțin efort, aveți acum un raport PivotTable simplu care conține câmpuri din trei tabele diferite. Ceea ce a făcut această activitate atât de simplă a fost relația prealabilă dintre tabele. Deoarece au existat relații între tabele în baza de date sursă și ați importat toate tabelele într-o singură operațiune, Excel a putut crea din nou acele relații în modelul său de date.

Dar ce se întâmplă dacă datele dvs. provin din surse diferite sau sunt importate ulterior? De obicei, puteți crea relații cu date noi pe baza coloanelor care se potrivesc. La pasul următor, importați tabele suplimentare și aflați cum să creați relații noi.

Importul datelor dintr-o foaie de lucru

Acum, să importăm date din altă sursă, de data aceasta dintr-un registru de lucru existent, apoi să specificăm relațiile dintre datele noastre existente și noile date. Relațiile vă permit analizarea colecțiilor de date în Excel și crearea de vizualizări interesante și captivante din datele importate.

Să începem prin crearea unei foi de lucru necompletate, apoi importăm date dintr-un registru de lucru Excel.

1. Inserați o nouă foaie de lucru Excel și denumiți-o Sports.

2. Răsfoiți la folderul care conține fișierele de date eșantion descărcate și deschideți OlympicSports.xlsx.

3. Selectați și copiați datele din Foaie1. Dacă selectați o celulă cu date, cum ar fi celula A1, puteți apăsa Ctrl + A pentru a selecta toate datele adiacente. Închideți registrul de lucru OlympicSports.xlsx.

4. Pe foaia de lucru Sports, plasați cursorul în celula A1 și lipiți datele.

5. Cu datele în continuare evidențiate, apăsați Ctrl + T pentru a formata datele ca un tabel. De asemenea, puteți formata datele ca un tabel din panglică, prin selectarea PORNIRE > Formatare ca tabel. Deoarece datele au anteturi, selectați Tabelul meu are anteturi în fereastra Creare tabel care apare, așa cum se arată aici.
   
   
   
   Formatarea datelor ca tabel are multe avantaje. Puteți asocia un nume la un tabel, ceea ce îl face ușor de identificat. De asemenea, puteți stabili relații între tabele, care permit explorarea și analiza în PivotTable, Power Pivot și Power View.

6. Denumiți tabelul. În INSTRUMENTE TABEL > PROIECTARE > Proprietăți, găsiți câmpul Nume tabel și tastați Sports. Registrul de lucru arată ca următorul ecran.
   

7. Salvați registrul de lucru.

Importul datelor folosind copierea și lipirea

Acum, că am importat date dintr-un registru de lucru Excel, să importăm date dintr-un tabel găsit pe o pagină web sau din orice altă sursă din care putem copia și lipi în Excel. În pașii următori, veți adăuga orașele gazdă ale Jocurilor Olimpice dintr-un tabel.

1. Inserați o nouă foaie de lucru Excel și denumiți-o Hosts.

2. Selectați și copiați tabelul următor, inclusiv anteturile tabelului.

1. În Excel, plasați cursorul în celula A1 din foaia de lucru Hosts și lipiți datele.

2. Formatați datele ca tabel. Așa cum s-a descris anterior în acest tutorial, apăsați Ctrl + T pentru a formata datele ca tabel, sau PORNIRE > Formatare ca tabel. Deoarece datele au anteturi, selectați Tabelul meu are anteturi în fereastra Creare tabel care apare.

3. Denumiți tabelul. În INSTRUMENTE TABEL > PROIECTARE > Proprietăți, găsiți câmpul Nume tabel și tastați Hosts.

4. Selectați coloana Edition (Ediție), iar din fila PORNIRE, formatați-o ca Număr cu 0 zecimale.

5. Salvați registrul de lucru. Registrul de lucru arată ca următorul ecran.

Acum, că aveți un registru de lucru Excel cu tabele, puteți crea relații între ele. Crearea de relații între tabele vă permite să combinați datele din cele două tabele.

Crearea unei relații între date importate

Puteți începe imediat să utilizați câmpurile din raportul PivotTable din tabelele importate. Dacă Excel nu poate determina cum să includă un câmp în raportul PivotTable, trebuie stabilită o relație cu modelul de date existent. În pașii următori, veți afla cum să creați o relație între datele importate din surse diferite.

1. În Foaie1, în partea de sus aCâmpuri PivotTable, faceți clic peToate pentru a vizualiza lista completă de tabele disponibile, așa cum se arată în următorul ecran.
   

2. Defilați prin listă pentru a vedea noile tabele pe care le-ați adăugat.

3. Extindeți Sports și selectați Sport pentru a-l adăuga la PivotTable. Observați că Excel vă solicită să creați o relație, așa cum se vede în ecranul următor.
   
    

   Această notificare are loc pentru că ați folosit câmpuri dintr-un tabel care nu este parte a modelului de date de bază. O modalitate de a adăuga un tabel la modelul de date este de a crea o relație cu un tabel care este deja în modelul de date. Pentru a crea relația, unul dintre tabele trebuie să aibă o coloană de valori unice, non-repetate. În datele eșantion, tabelul Disciplines importat din baza de date conține un câmp cu coduri de sport, numit SportID. Aceleași coduri de sport sunt prezente ca un câmp de date Excel importat. Să creăm relația.

4. Faceți clic pe CREARE... în zona evidențiată Câmpuri PivotTable pentru a deschide caseta de dialog Creare relație, așa cum se prezintă în următorul ecran.
   
   

5. În Tabel, selectați Disciplines din lista verticală.

6. În Coloană (Străină), alegeți SportID.

7. În Tabel asociat, alegeți Sports.

8. În Coloană asociată (Principală), alegeți SportID.

9. Faceți clic pe OK.

Raportul PivotTable se modifică pentru a reflecta noua relație. Dar PivotTable nu arată încă bine, din cauza ordinii câmpurilor din zona RÂNDURI. Disciplina este o subcategorie a unui anumit sport, dar deoarece am aranjat Discipline deasupra de Sport în zona RÂNDURI, ea nu este organizată corect. Ecranul următor arată această ordine nedorită.

1. În zona RÂNDURI, mutați Sport deasupra Discipline. Este mult mai bine, și PivotTable afișează datele așa cum doriți să le vedeți, ca în următorul ecran.
   
   

În fundal, Excel generează un Model de date care poate fi utilizat în tot registrul de lucru în orice raport PivotTable, PivotChart,în Power Pivot sau în orice raport Power View. Relațiile între tabele constituie baza unui Model de date și determină căile de navigare și de calcul.

În următorul tutorial, Extindeți relațiile din Modelul de date utilizând Excel, Power Pivotși DAX, construiți pe baza a ceea ce ați învățat aici și parcurgeți extinderea modelului de date utilizând un program de completare Excel puternic și vizual denumit Power Pivot. De asemenea, aflați cum să calculați coloanele dintr-un tabel și să utilizați acea coloană calculată, astfel încât un tabel altfel neasociat să poată fi adăugat la modelul dvs. de date.

Punct de control și test

Revizuiți ce ați învățat

Acum aveți un registru de lucru Excel care include un raport PivotTable care accesează date din mai multe tabele, unele importate separat. Ați învățat să importați dintr-o bază de date, dintr-un alt registru de lucru Excel și din date copiate și lipite în Excel.

Pentru a corobora aceste date, a trebuit să creați o relație între tabele utilizată de Excel pentru a corela rândurile. Ați învățat, de asemenea, că a avea coloane într-un tabel care se corelează cu datele din alt tabel este esențial pentru crearea de relații, precum și pentru căutarea de rânduri asociate.

Sunteți gata pentru următorul tutorial din această serie. Iată un link:

Tutorial: Extinderea relațiilor din Modelul de date folosind Excel, Power Pivot și DAX

TEST

Vreți să vedeți cât de bine vă amintiți ce ați învățat? Iată șansa dvs. Testul următor evidențiază caracteristicile, capabilitățile sau cerințele despre care ați învățat în acest tutorial. În partea de jos a paginii, veți găsi răspunsurile. Succes!

Întrebarea 1: De ce este important să facem conversia datelor importate în tabele?

A: Nu trebuie să le transformați în tabele, deoarece toate datele importate sunt transformate automat în tabele.

B: Dacă faceți conversia datelor importate în tabele, ele vor fi excluse din modelul de date. Numai când acestea sunt excluse din modelul de date sunt disponibile în PivotTable, Power Pivot și Power View.

C: Dacă faceți conversia datelor importate în tabele, acestea pot fi incluse în modelul de date și făcute disponibile pentru PivotTable, Power Pivot și Power View.

D: Nu se poate face conversia datelor importate în tabele.

Întrebarea 2: Care din următoarele surse de date pot fi importate în Excel și incluse în modelul de date?

A: Bazele de date Access și multe alte baze de date.

B: Fișierele Excel existente.

C: Orice element pe care îl puteți copia și lipi în Excel și formata ca tabel, inclusiv tabelele de date din site-uri, documente sau orice altceva care poate fi lipit în Excel.

D: Toate cele de mai sus

Întrebarea 3: Într-un PivotTable, ce se întâmplă atunci când reordonați câmpurile din cele patru zone de Câmpuri PivotTable?

A: Nimic - nu puteți reordona câmpurile odată ce le plasați în zonele Câmpuri PivotTable.

B: Formatul PivotTable se modifică pentru a reflecta structura, dar datele subiacente nu sunt afectate.

C: Formatul PivotTable se modifică pentru a reflecta structura, iar toate datele subiacente sunt modificate definitiv.

D: Datele subiacente sunt schimbate, rezultând la seturi de date noi.

Întrebarea 4: Atunci când creați o relație între tabele, ce este necesar?

A: Niciun tabel nu poate avea o coloană ce conține valori unice, nerepetate.

B: Un tabel nu trebuie să facă parte din registrul de lucru Excel.

C: Coloanele nu trebuie să fie transformate în tabele.

D: Niciuna dintre variantele de mai sus nu este corectă.

Răspunsuri test

1. Răspuns corect: C

2. Răspuns corect: D

3. Răspuns corect: B

4. Răspuns corect: D