Ați folosit vreodată VLOOKUP pentru a aduce o coloană dintr-o masă în alta? Acum că Excel are un model de date încorporat, VLOOKUP este învechit. Puteți crea o relație între două tabele de date, pe baza datelor potrivite din fiecare tabel. Apoi, puteți crea foi Power View și puteți crea tabele pivot și alte rapoarte cu câmpuri din fiecare tabel, chiar și atunci când tabelele provin din surse diferite. De exemplu, dacă aveți date despre vânzări ale clienților, este posibil să doriți să importați și să raportați date cu informații de timp pentru a analiza tiparele de vânzări în funcție de an și lună.

Toate tabelele dintr-un registru de lucru sunt listate în listele PivotTable și Power View Fields.

relație

Când importați tabele conexe dintr-o bază de date relațională, Excel poate crea adesea acele relații în modelul de date pe care îl construiește în culise. Pentru toate celelalte cazuri, va trebui să creați relații manual.

Asigurați-vă că registrul de lucru conține cel puțin două tabele și că fiecare tabel are o coloană care poate fi mapată la o coloană dintr-un alt tabel.

Efectuați una dintre următoarele acțiuni: Formatați datele ca tabel sau Importați date externe ca tabel într-o foaie de lucru nouă.

Dați fiecărui tabel un nume semnificativ: în Instrumente de tabel, faceți clic pe Design > Nume tabel > introduceți un nume.

Verificați că coloana dintr-unul dintre tabele are valori de date unice, fără duplicate. Excel poate crea relația numai dacă o coloană conține valori unice.

De exemplu, pentru a lega vânzările clienților cu informațiile de timp, ambele tabele trebuie să includă date în același format (de exemplu, 01.01.2012) și cel puțin un tabel (informații de timp) listează fiecare dată o singură dată în coloană.

Faceți clic pe Date > Relații.

Dacă relațiile este gri, registrul dvs. de lucru conține un singur tabel.

În Gestionați relațiile, faceți clic pe Nou.

În Creare relație, faceți clic pe săgeata pentru Tabel, și selectați un tabel din listă. Într-o relație unu-la-mulți, acest tabel ar trebui să fie din partea multor. Folosind exemplul nostru de informații despre clienți și timp, ați alege mai întâi tabelul de vânzări pentru clienți, deoarece este posibil ca multe vânzări să apară într-o anumită zi.

Pentru coloană (străin), selectați coloana care conține datele legate de Coloana asociată (primară). De exemplu, dacă ați avea o coloană de dată în ambele tabele, ați alege acea coloană acum.

Pentru tabelul aferent, selectați un tabel care are cel puțin o coloană de date care este legată de tabelul pe care tocmai l-ați selectat pentru Tabel.

Pentru coloana asociată (primară), selectați o coloană care are valori unice care corespund valorilor din coloana pe care ați selectat-o ​​pentru Coloană.

Mai multe despre relațiile dintre tabele în Excel

Note despre relații

Veți ști dacă există o relație atunci când trageți câmpuri din diferite tabele pe lista Câmpuri ale tabelului pivot. Dacă nu vi se solicită să creați o relație, Excel are deja informațiile de relație de care are nevoie pentru a raporta datele.

Crearea relațiilor este similară cu utilizarea VLOOKUP-urilor: aveți nevoie de coloane care să conțină date potrivite, astfel încât Excel să poată face referințe încrucișate într-un tabel cu cele ale altui tabel. În exemplul de informații de timp, tabelul Client ar trebui să aibă valori de dată care există și într-un tabel de informații de timp.

Într-un model de date, relațiile de masă pot fi unu la unu (fiecare pasager are o carte de îmbarcare) sau unul la mulți (fiecare zbor are mulți pasageri), dar nu mulți la mulți. Multe relații duc la erori de dependență circulară, cum ar fi „A fost detectată o dependență circulară”. Această eroare va apărea dacă faceți o conexiune directă între două tabele care sunt multe-la-multe sau conexiuni indirecte (un lanț de relații de tabel care sunt unu-la-mulți în cadrul fiecărei relații, dar mulți la mulți când este vizualizat sfârșit pentru a finaliza Citiți mai multe despre relațiile dintre tabele într-un model de date.

Tipurile de date din cele două coloane trebuie să fie compatibile. Consultați Tipuri de date în Modele de date Excel pentru detalii.

Alte modalități de a crea relații ar putea fi mai intuitive, mai ales dacă nu sunteți sigur ce coloane să utilizați. Consultați Crearea unei relații în Diagrama View în Power Pivot.

Exemplu: corelarea datelor de informații de timp cu datele de zbor ale companiilor aeriene

Puteți afla atât despre relațiile de masă, cât și despre inteligența temporală folosind date gratuite pe Microsoft Azure Marketplace. Unele dintre aceste seturi de date sunt foarte mari, necesitând o conexiune rapidă la internet pentru a finaliza descărcarea datelor într-o perioadă rezonabilă de timp.

Faceți clic pe Obțineți date externe > De la serviciul de date > Din Microsoft Azure Marketplace. Pagina de pornire Microsoft Azure Marketplace se deschide în Expertul pentru importarea tabelelor.

Sub Preț, faceți clic pe Gratuit.

Sub Categorie, faceți clic pe Știință și statistici.

Găsiți DateStream și faceți clic pe Abonare.

Introduceți contul dvs. Microsoft și faceți clic pe Conectare. O previzualizare a datelor ar trebui să apară în fereastră.

Derulați în partea de jos și faceți clic pe Selectare interogare.

Alege BasicCalendarUS și apoi faceți clic pe Finalizare pentru a importa datele. Pentru o conexiune rapidă la internet, importul ar trebui să dureze aproximativ un minut. Când ați terminat, ar trebui să vedeți un raport de stare de 73.414 rânduri transferate. Faceți clic pe Închidere.

Faceți clic pe Obțineți date externe > De la serviciul de date > Din Microsoft Azure Marketplace pentru a importa un al doilea set de date.

Sub Tip, faceți clic pe Date.

Sub Preț, faceți clic pe Gratuit.

Găsiți întârzieri de zbor ale transportatorului aerian american și faceți clic pe Selectare.

Derulați până jos și faceți clic pe Selectare interogare.

Faceți clic pe Finalizare pentru a importa datele. Printr-o conexiune rapidă la internet, acest lucru poate dura 15 minute pentru a fi importat. Când ați terminat, ar trebui să vedeți un raport de stare de 2.427.284 de rânduri transferate. Faceți clic pe Închidere. Acum ar trebui să aveți două tabele în modelul de date. Pentru a le raporta, vom avea nevoie de coloane compatibile în fiecare tabel.

Observați că DateKey în BasicCalendarUS este în format 01/01/2012 00:00:00. Performanța_la_timp tabelul are, de asemenea, o coloană datetime, FlightDate, ale căror valori sunt specificate în același format: 01.01.2012 12:00:00 AM. Cele două coloane conțin date potrivite, de același tip de date și cel puțin una dintre coloane (DateKey) conține doar valori unice. În următorii pași, veți utiliza aceste coloane pentru a raporta tabelele.

În fereastra Power Pivot, faceți clic pe PivotTable pentru a crea un tabel pivot într-o foaie de lucru nouă sau existentă.

În lista de câmpuri, extindeți On_Time_Performance și faceți clic pe ArrDelayMinutes pentru a-l adăuga în zona Valori. În tabelul pivot, ar trebui să vedeți durata totală a întârzierii zborurilor, măsurată în minute.

Extindeți BasicCalendarUS și faceți clic pe MonthInCalendar pentru a-l adăuga în zona Rânduri.

Observați că tabelul pivot listează acum luni, dar suma totală a minutelor este aceeași pentru fiecare lună. Valorile repetate și identice indică necesitatea unei relații.

În Lista câmpurilor, în „Relațiile dintre tabele pot fi necesare”, faceți clic pe Creați.

În Tabel asociat, selectați Performanță_Timp_Timp iar în Coloana corelată (primară) alegeți FlightDate.

În tabel, selectați BasicCalendarUS iar în Coloană (străin) alegeți DateKey. Faceți clic pe OK pentru a crea relația.

Observați că suma de minute întârziate variază acum pentru fiecare lună.

În BasicCalendarUS și trageți YearKey în zona Rânduri, deasupra MonthInCalendar.

Acum puteți repara întârzierile de sosire după an și lună sau alte valori din calendar.

Sfaturi: În mod implicit, lunile sunt listate în ordine alfabetică. Folosind programul de completare Power Pivot, puteți schimba sortarea astfel încât lunile să apară în ordine cronologică.

Asigurați-vă că BasicCalendarUS tabelul este deschis în fereastra Power Pivot.

În tabelul Acasă, faceți clic pe Sortare după coloană.

În Sortare, alegeți MonthInCalendar

În By, alegeți MonthOfYear.

Tabelul pivot sortează acum fiecare combinație lună-an (octombrie 2011, noiembrie 2011) după numărul lunii într-un an (10, 11). Schimbarea ordinii de sortare este ușoară, deoarece DateStream feed oferă toate coloanele necesare pentru ca acest scenariu să funcționeze. Dacă folosiți un alt tabel de informații temporale, pasul dvs. va fi diferit.

„Ar putea fi necesare relații între tabele”

Pe măsură ce adăugați câmpuri la un tabel pivot, veți fi informat dacă este necesară o relație de tabel pentru a da sens câmpurilor selectate în tabelul pivot.

Deși Excel vă poate spune când este nevoie de o relație, nu vă poate spune ce tabele și coloane să utilizați sau dacă o relație de tabel este chiar posibilă. Încercați să urmați acești pași pentru a obține răspunsurile de care aveți nevoie.

Pasul 1: Determinați ce tabele să specificați în relație

Dacă modelul dvs. conține doar câteva tabele, ar putea fi imediat evident pe care trebuie să le utilizați. Dar pentru modelele mai mari, probabil că ați putea folosi un ajutor. O abordare este utilizarea Diagram View în programul de completare Power Pivot. Diagrama View oferă o reprezentare vizuală a tuturor tabelelor din Modelul de date. Folosind Diagram View, puteți determina rapid ce tabele sunt separate de restul modelului.

Notă: Este posibil să creați relații ambigue care sunt nevalide atunci când sunt utilizate într-un raport PivotTable sau Power View. Să presupunem că toate tabelele dvs. sunt legate în vreun fel de alte tabele din model, dar când încercați să combinați câmpuri din tabele diferite, primiți mesajul „Relațiile dintre tabele ar putea fi necesare”. Cea mai probabilă cauză este că ați intrat într-o relație de la mulți la mulți. Dacă urmați lanțul de relații de tabelă care se conectează la tabelele pe care doriți să le utilizați, veți descoperi probabil că aveți două sau mai multe relații de tabelă unu la mai multe. Nu există o soluție ușoară care să funcționeze pentru fiecare situație, dar ați putea încerca să creați coloane calculate pentru a consolida coloanele pe care doriți să le utilizați într-un singur tabel.

Pasul 2: Găsiți coloane care pot fi utilizate pentru a crea o cale de la un tabel la altul

După ce ați identificat ce tabel este deconectat de la restul modelului, examinați coloanele sale pentru a determina dacă o altă coloană, în altă parte a modelului, conține valori potrivite.

De exemplu, să presupunem că aveți un model care conține vânzări de produse pe teritoriu și că importați ulterior date demografice pentru a afla dacă există o corelație între vânzări și tendințele demografice în fiecare teritoriu. Deoarece datele demografice provin dintr-o sursă de date diferită, tabelele sale sunt inițial izolate de restul modelului. Pentru a integra datele demografice cu restul modelului dvs., va trebui să găsiți o coloană într-unul din tabelele demografice care să corespundă cu una pe care o utilizați deja. De exemplu, dacă datele demografice sunt organizate în funcție de regiune, iar datele dvs. de vânzări specifică regiunea în care s-a produs vânzarea, puteți asocia cele două seturi de date găsind o coloană comună, cum ar fi un stat, un cod poștal sau o regiune, pentru a furniza căutarea.

Pe lângă potrivirea valorilor, există câteva cerințe suplimentare pentru crearea unei relații:

Valorile datelor din coloana de căutare trebuie să fie unice. Cu alte cuvinte, coloana nu poate conține duplicate. Într-un model de date, nulele și șirurile goale sunt echivalente cu un gol, care este o valoare de date distinctă. Aceasta înseamnă că nu puteți avea mai multe valori nule în coloana de căutare.

Tipurile de date atât ale coloanei sursă, cât și ale coloanei de căutare trebuie să fie compatibile. Pentru mai multe informații despre tipurile de date, consultați Tipuri de date în Modele de date.

Pentru a afla mai multe despre relațiile de tabel, consultați Relațiile dintre tabele într-un model de date.