Ați utilizat vreodată VLOOKUP pentru a aduce o coloană dintr-un tabel într-un alt tabel? Acum, că Excel are un Model de date încorporat, VLOOKUP este depășit. Puteți crea o relație între două tabele de date, bazată pe potrivirea datelor din fiecare tabel. Apoi puteți să creați foi Power View și să construiți rapoarte PivotTable ș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 de vânzări către clienți, vă recomandăm să importați și să corelați date „time intelligence” pentru a analiza modelele de vânzări în funcție de an și lună.
Toate tabelele dintr-un registru de lucru sunt în listele de câmpuri PivotTable și Power View.
Atunci când importați tabele corelate dintr-o bază de date relațională, Excel poate crea adesea acele relații în Modelul de date pe care îl generează în culise. Pentru toate celelalte cazuri, va trebui să creați manual relații.
-
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ă din celălalt tabel.
-
Alegeți una dintre următoarele variante: 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 tabel, faceți clic pe Proiectare > Nume tabel > introduceți un nume.
-
Verificați dacă în coloana dintr-un tabel există valori de date unice, fără dubluri. Excel poate crea relația doar dacă o coloană conține valori unice.
De exemplu, pentru a corela datele de clienți cu cele de tip „time intelligence”, ambele tabele trebuie să conțină date calendaristice în același format (de exemplu, 1.1.2012) și cel puțin un tabel (Time Intelligence) trebuie să listeze fiecare dată calendaristică o singură dată în coloană.
-
Faceți clic pe Date > Relații.
Dacă opțiunea Relații este estompată, registrul de lucru conține un singur tabel.
-
În caseta Gestionare relații, faceți clic pe Nou.
-
În caseta Creare relații, faceți clic pe săgeata pentru Tabel, apoi selectați un tabel din listă. Într-o relație de tip unu-la-mai mulți, acest tabel trebuie să fie în partea mai mulți. Utilizând exemplul cu clienții și datele de tip „time intelligence”, ați alege mai întâi tabelul cu vânzările spre clienți, deoarece este posibil să aibă loc multe vânzări în orice zi dată.
-
Pentru Coloană (Străină), selectați coloana ce conține datele legate de Coloană asociată (Principală). De exemplu, dacă aveți o coloană de date calendaristice în ambele tabele, alegeți coloana respectivă acum.
-
Pentru Tabel asociat, selectați un tabel care conține cel puțin o coloană de date ce este corelată cu tabelul pe care tocmai l-ați selectat pentru Tabel.
-
Pentru Coloană asociată (Principală), selectați o coloană care conține valori unice ce corespund valorilor din coloana pe care ați selectat-o pentru Coloană.
-
Faceți clic pe OK.
Mai multe despre relațiile dintre tabelele din Excel
Note despre relații
-
Veți ști dacă există o relație când glisați câmpurile din diferite tabele în lista Câmpuri PivotTable. Dacă nu vi se solicită să creați o relație, aplicația Excel conține deja informațiile despre relații de care are nevoie pentru a corela datele.
-
Crearea relațiilor este similară cu folosirea funcțiilor VLOOKUP: vă trebuie coloane care conțin date de potrivire, astfel încât Excel să poată compara rândurile dintr-un tabel cu cele din celălalt tabel. În exemplul cu datele de tip „time intelligence”, tabelul Clienți trebuie să aibă valorile de date calendaristice care există și în tabelul cu date „time intelligence”.
-
Într-un model de date, relațiile de tabel pot fi unu-la-unu (fiecare pasager are un singur bilet) sau unu-la-mai-mulți (fiecare zbor are mai mulți pasageri), dar nu mai-mulți-la-mai-mulți. Relațiile mai-mulți-la-mai-mulți au ca rezultat erori de dependență circulară, cum ar fi „S-a detectat o dependență circulară”. Această eroare va apărea în cazul în care creați o conexiune directă între două tabele care sunt mai-mulți-la-mai-mulți sau conexiuni indirecte (un lanț de relații de tabel care sunt de tip unu-la-mai-mulți în fiecare relație, dar mai-mulți-la-mai-mulți atunci când sunt văzute de la un capăt la altul). Citiți mai multe despre Relațiile între tabelele dintr-un model de date.
-
Tipurile de date din cele două coloane trebuie să fie compatibile. Pentru detalii, consultați Tipurile de date în modelele de date din Excel.
-
Alte modalități de a crea relații ar putea fi mai intuitive, mai ales dacă nu știți sigur ce coloane să utilizați. Consultați Crearea unei relații în vizualizarea diagramă din Power Pivot.
Exemplu: asocierea datelor de tip „time intelligence” la datele de zbor ale unei companii aeriene
Puteți afla informații despre relațiile de tabel și despre „time intelligence” utilizând date gratuite din Microsoft Azure Marketplace. Unele dintre aceste seturi de date sunt foarte mari și necesită o conexiune rapidă la internet pentru ca descărcarea lor să nu dureze foarte mult timp.
-
Porniți programul de completare Power Pivot in Microsoft Excel și deschideți fereastra Power Pivot.
-
Faceți clic pe Obținere date externe > Din Serviciul de date > Din Microsoft Azure Marketplace. Se va deschide pagina de pornire Microsoft Azure Marketplace în Expert import tabel.
-
Sub Price (Preț), faceți clic pe Free (Gratuit).
-
Sub Category (Categorie), faceți clic pe Science & Statistics (Științe și statistică).
-
Găsiți DateStream și faceți clic pe abonare.
-
Introduceți contul Microsoft și faceți clic pe Sign in (Conectare). Ar trebui să apară în fereastră o examinare a datelor.
-
Defilați în partea de jos și faceți clic pe Select Query (Selectare interogare).
-
Faceți clic pe Next (Următorul).
-
Alegeți BasicCalendarUS și faceți clic pe Finish (Terminare) pentru a termina importul. Folosind o conexiune rapidă la internet, importul ar trebui să dureze în jur de un minut. Când s-a terminat, ar trebui să vedeți un raport de stare cu 73.414 de rânduri transferate. Faceți clic pe Close (Închidere).
-
Faceți clic pe Obținere date externe > Din Serviciul de date > Din Microsoft Azure Marketplace pentru a importa al doilea set de date.
-
Sub Type (Tip), faceți clic pe Data (Date).
-
Sub Price (Preț), faceți clic pe Free (Gratuit).
-
Găsiți US Air Carrier Flight Delays și faceți clic pe Select.
-
Defilați în partea de jos și faceți clic pe Select Query (Selectare interogare).
-
Faceți clic pe Next (Următorul).
-
Faceți clic pe Finish (Terminare) pentru a importa datele. Cu o conexiune rapidă la internet, importul ar trebui să dureze 15 minute. Când s-a terminat, ar trebui să vedeți un raport de stare care arată că s-au transferat 2.427.284 de rânduri. Faceți clic pe Close (Închidere). Acum, modelul de date ar trebui să conțină două tabele. Pentru a le corela, vom avea nevoie de coloane compatibile în fiecare tabel.
-
Observați că DateKey (CheieDată) din BasicCalendarUS este în formatul 1/1/2012 12:00:00 AM. De asemenea, tabelul On_Time_Performance are, de asemenea, o coloană de dată și oră, FlightDate, ale căror valori sunt specificate în același format: 1/1/2012 12:00:00 AM. Cele două coloane conțin date care se potrivesc, de același tip de date și cel puțin una dintre coloane (DateKey) conține doar valori unice. În următorii câțiva pași, vom utiliza coloanele pentru a corela tabelele.
-
În fereastra Power Pivot, faceți clic pe PivotTable pentru a crea un PivotTable î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 adăuga opțiunea la zona Valori. În PivotTable, ar trebui să vedeți totalul timpului de întârziere pentru zboruri, măsurat în minute.
-
Extindeți BasicCalendarUS și faceți clic pe MonthInCalendar pentru a-l adăuga la zona Rânduri.
-
Observați că PivotTable listează acum luni, dar că suma totală de minute este aceeași pentru fiecare lună. Prin repetare, valorile identice arată faptul că este necesară o relație.
-
În Lista de câmpuri, în „Este posibil să fie necesare relații între tabele”, faceți clic pe Creare.
-
În Tabel asociat, selectați On_Time_Performance, iar în Coloană asociată (Principală) alegeți FlightDate.
-
În Tabel, selectați BasicCalendarUS, iar în Coloană (Străină) alegeți DateKey (CheieDată). Faceți clic pe OK pentru a crea relația.
-
Observați cum totalul în minute afișat variază acum în funcție de lună.
-
În BasicCalendarUS și glisați YearKey în zona Rânduri, deasupra MonthInCalendar.
Acum, puteți împărți creând un slice pentru întârzierile la sosire în funcție de an și lună sau după alte valori din calendar.
Sfaturi: Implicit, lunile sunt listate în ordine alfabetică. Utilizând programul de completare Power Pivot, puteți modifica sortarea astfel încât lunile să apară în ordine cronologică.
-
Asigurați-vă că este deschis tabelul BasicCalendarUS în fereastra Power Pivot.
-
În tabelul din Pagina de pornire, faceți clic pe Sortarea după coloană.
-
În Sortare, alegeți MonthInCalendar
-
În După, alegeți MonthOfYear.
Acum, PivotTable sortează fiecare combinație lună-an (octombrie 2011, noiembrie 2011) după numărul lunii din an (10, 11). Schimbarea ordinii de sortare este simplă, deoarece fluxul DateStream oferă toate coloanele necesare pentru a face acest scenariu să funcționeze. Dacă utilizați alt tabel pentru „time intelligence”, pasul pe care-l faceți va fi diferit.
„Pot fi necesare relații între tabele”
În timp ce adăugați câmpuri la un tabel PivotTable, veți fi informați dacă este necesară o relație de tabel pentru a înțelege câmpurile pe care le selectați în PivotTable.
Deși Excel vă poate informa atunci când este necesară o relație, nu vă poate spune ce tabele și coloane să utilizați, nici dacă este posibilă o relație între tabele. Urmați pașii de mai jos pentru a obține răspunsurile dorite.
Pasul 1: determinați tabelele de specificat în relație
Dacă modelul conține doar câteva tabele, poate fi evident pe care dintre ele trebuie să le utilizați. Dar, pentru modelele mai mari, probabil că ați avea nevoie de ajutor. O abordare este să utilizați Vizualizarea diagramă în programul de completare Power Pivot. Vizualizarea diagramă oferă o reprezentare vizuală a tuturor tabelelor din modelul de date. Utilizând Vizualizarea diagramă, puteți determina rapid ce tabele sunt separate de restul modelului.
Notă: Este posibil să creați relații ambigue care nu sunt valide atunci când sunt utilizate într-un raport PivotTable sau Power View. Să presupunem că toate tabelele sunt asociate într-un fel cu alte tabele din model, dar atunci când încercați să combinați câmpuri din tabele diferite, primiți mesajul "Pot fi necesare relații între tabele". Cauza cea mai probabilă este faptul că ați avut o relație mai-mulți-la-mai-mulți. Dacă urmați lanțul de relații între tabele 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 între tabele unu-la-mai-mulți. Nu există nicio soluție simplă care să funcționeze pentru fiecare situație, dar puteți încerca să creați coloane calculate pentru a sintetiza 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 următorul
După ce ați identificat care tabel este deconectat de restul modelului, revizuiți-i coloanele pentru a determina dacă altă coloană, aflată în altă parte a modelului, conține valorile potrivite.
De exemplu, să presupunem că aveți un model care conține vânzările de produse în funcție de regiune și că importați ulterior date demografice pentru a afla dacă există o corelație între vânzări și tendințele demografice din fiecare regiune. Deoarece datele demografice provin dintr-o altă sursă de date, tabelele sale sunt izolate inițial de restul modelului. Pentru a integra datele demografice cu restul modelului, va trebui să găsiți o coloană într-unul dintre tabelele cu date demografice care să corespundă cu una pe care o utilizați deja. De exemplu, dacă datele demografice sunt organizate în funcție de regiune și datele de vânzări menționează regiunea în care au avut loc vânzările, puteți corela cele două seturi de date găsind o coloană comună, cum ar fi Județ, Cod poștal sau Regiune, ca să fie coloana de căutare.
Pe lângă potrivirea valorilor, există câteva cerințe suplimentare pentru crearea unei relații:
-
Valorile de date din coloana de căutare trebuie să fie unice. Cu alte cuvinte, coloana nu poate conține valori dublate. Într-un Model de date, șirurile cu valoare Null și cele goale sunt echivalente cu un șir necompletat, care reprezintă o valoare de date distinctă. Acest lucru înseamnă că nu puteți avea valori Null multiple în coloana de căutare.
-
Tipurile de date ale coloanei sursă și ale coloanei de căutare trebuie să fie compatibile. Pentru mai multe informații despre tipurile de date, consultați Tipurile de date din Modelele de date.
Pentru a afla mai multe despre relațiile de tabel, consultați Relații între tabelele dintr-un Model de date.
