tabele

Adăugați mai multă putere analizei de date prin crearea de relații între diferite tabele. O relație este o legătură între două tabele care conțin date: o coloană în fiecare tabel este baza relației. Pentru a vedea de ce relațiile sunt utile, imaginați-vă că urmăriți datele pentru comenzile clienților din afacerea dvs. Puteți urmări toate datele într-un singur tabel având o structură ca aceasta:

Această abordare poate funcționa, dar implică stocarea multor date redundante, cum ar fi adresa de e-mail a clientului pentru fiecare comandă. Stocarea este ieftină, dar dacă adresa de e-mail se schimbă, trebuie să vă asigurați că actualizați fiecare rând pentru clientul respectiv. O soluție la această problemă este împărțirea datelor în mai multe tabele și definirea relațiilor între aceste tabele. Aceasta este abordarea utilizată în bazele de date relaționale, cum ar fi SQL Server. De exemplu, o bază de date pe care o importați poate reprezenta date de comandă utilizând trei tabele conexe:

Relațiile există într-un model de date - unul pe care îl creați în mod explicit sau unul pe care Excel îl creează automat în numele dvs. atunci când importați simultan mai multe tabele. De asemenea, puteți utiliza suplimentul Power Pivot pentru a crea sau gestiona modelul. Consultați Crearea unui model de date în Excel pentru detalii.

Dacă utilizați programul de completare Power Pivot pentru a importa tabele din aceeași bază de date, Power Pivot poate detecta relațiile dintre tabele pe baza coloanelor din [paranteze] și poate reproduce aceste relații într-un model de date pe care îl construiește în spatele acestuia scenele. Pentru mai multe informații, consultați Detectarea automată și deducerea relațiilor în acest articol. Dacă importați tabele din mai multe surse, puteți crea manual relații așa cum este descris în Creați o relație între două tabele.

Relațiile se bazează pe coloane din fiecare tabel care conțin aceleași date. De exemplu, ai putea relaționa cu un client masa cu o Comenzi tabel dacă fiecare conține o coloană care stochează un ID de client. În exemplu, numele coloanelor sunt aceleași, dar aceasta nu este o cerință. Unul ar putea fi CustomerID și altul CustomerNumber, atâta timp cât toate rândurile din tabelul Comenzi conțin un ID care este, de asemenea, stocat în tabelul Clienți.

Într-o bază de date relațională, există mai multe tipuri de chei. O cheie este de obicei o coloană cu proprietăți speciale. Înțelegerea scopului fiecărei chei vă poate ajuta să gestionați un Model de date cu mai multe tabele care furnizează date unui raport PivotTable, PivotChart sau Power View.

Deși există multe tipuri de chei, acestea sunt cele mai importante pentru scopul nostru aici:

Cheia principala: identifică în mod unic un rând dintr-un tabel, cum ar fi CustomerID în clienți masa.

Tastă alternativă (sau cheie candidat): o altă coloană decât cheia primară care este unică. De exemplu, un tabel Angajați poate stoca un ID de angajat și un număr de securitate socială, ambele fiind unice.

Cheie externă: o coloană care se referă la o coloană unică dintr-un alt tabel, cum ar fi CustomerID în Ordinele tabel, care se referă la CustomerID în tabelul Clienți.

Într-un model de date, cheia primară sau cheia alternativă este denumită coloană aferentă. Dacă un tabel are atât o cheie principală, cât și o cheie alternativă, puteți utiliza una ca bază a unei relații de tabel. Cheia externă este denumită coloană sursă sau doar coloană. În exemplul nostru, o relație ar fi definită între CustomerID în Ordinele tabel (coloana) și CustomerID în clienți tabel (coloana de căutare). Dacă importați date dintr-o bază de date relațională, în mod implicit Excel alege cheia străină dintr-un tabel și cheia primară corespunzătoare din celălalt tabel. Cu toate acestea, puteți utiliza orice coloană care are valori unice pentru coloana de căutare.

Relația dintre un client și o comandă este o relație de la unu la mulți. Fiecare client poate avea mai multe comenzi, dar o comandă nu poate avea mai mulți clienți. O altă relație importantă de masă este one-to-one. În exemplul nostru de aici, CustomerDiscounts tabelul, care definește o singură rată de reducere pentru fiecare client, are o relație de la unu la unu cu tabelul Clienți.

Acest tabel prezintă relațiile dintre cele trei tabele (Clienți, Reduceri clienți, și Comenzi):

Notă: Relațiile multe-la-multe nu sunt acceptate într-un model de date. Un exemplu de relație de la mulți la mulți este o relație directă între produse și clienți, în care un client poate cumpăra multe produse și același produs poate fi cumpărat de mulți clienți.

După ce a fost creată orice relație, Excel trebuie să recalculeze în mod obișnuit orice formulă care utilizează coloane din tabele din relația nou creată. Procesarea poate dura ceva timp, în funcție de cantitatea de date și de complexitatea relațiilor. Pentru mai multe detalii, consultați Recalculați formulele.

Un model de date poate avea relații multiple între două tabele. Pentru a construi calcule precise, Excel are nevoie de o singură cale de la o tabelă la alta. Prin urmare, o singură relație între fiecare pereche de tabele este activă la un moment dat. Deși celelalte sunt inactive, puteți specifica o relație inactivă în formule și interogări.

În Diagram View, relația activă este o linie continuă, iar cele inactive sunt linii punctate. De exemplu, în AdventureWorksDW2012, tabelul DimDate conține o coloană, DateKey, care este legat de trei coloane diferite în tabelul FactInternetSales: OrderDate, DueDate, și ShipDate. Dacă relația activă este între DateKey și OrderDate, aceasta este relația implicită în formule, cu excepția cazului în care specificați altfel.

O relație poate fi creată atunci când sunt îndeplinite următoarele cerințe:

Identificator unic pentru fiecare tabel

Fiecare tabel trebuie să aibă o singură coloană care identifică în mod unic fiecare rând din tabelul respectiv. Această coloană este adesea denumită cheia principală.

Coloane de căutare unice

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.

Tipuri de date compatibile

Tipurile de date din coloana sursă și coloana de căutare trebuie să fie compatibile. Pentru mai multe informații despre tipurile de date, consultați Tipuri de date acceptate în Modele de date.

Într-un model de date, nu puteți crea o relație de tabel dacă cheia este o cheie compusă. De asemenea, sunteți limitat la crearea de relații de la unu la unu și de la unul la mai multe. Alte tipuri de relații nu sunt acceptate.

Taste compozite și coloane de căutare

O cheie compusă este compusă din mai multe coloane. Modelele de date nu pot utiliza chei compuse: un tabel trebuie să aibă întotdeauna exact o coloană care identifică în mod unic fiecare rând din tabel. Dacă importați tabele care au o relație existentă bazată pe o cheie compusă, Expertul pentru importarea tabelelor din Power Pivot va ignora relația respectivă, deoarece nu poate fi creată în model.

Pentru a crea o relație între două tabele care au mai multe coloane care definesc cheile primare și străine, mai întâi combinați valorile pentru a crea o singură coloană de chei înainte de a crea relația. Puteți face acest lucru înainte de a importa datele sau creând o coloană calculată în Modelul de date utilizând suplimentul Power Pivot.

Un model de date nu poate avea relații de la mai mulți la mulți. Nu puteți adăuga pur și simplu tabele de joncțiune în model. Cu toate acestea, puteți utiliza funcțiile DAX pentru a modela relații între mai mulți.

Auto-îmbinări și bucle

Auto-îmbinările nu sunt permise într-un model de date. O auto-îmbinare este o relație recursivă între o masă și sine. Auto-îmbinările sunt adesea folosite pentru a defini ierarhiile părinte-copil. De exemplu, ați putea să vă alăturați unei tabele Angajați pentru a produce o ierarhie care să arate lanțul de management al unei companii.

Excel nu permite crearea de bucle între relațiile dintr-un registru de lucru. Cu alte cuvinte, următorul set de relații este interzis.

Tabelul 1, coloana a până la Tabelul 2, coloana f

Tabelul 2, coloana f până la Tabelul 3, coloana n

Tabelul 3, coloana n până la Tabelul 1, coloana a

Dacă încercați să creați o relație care ar duce la crearea unei bucle, se generează o eroare.

Unul dintre avantajele importării datelor folosind suplimentul Power Pivot este că Power Pivot poate detecta uneori relații și poate crea noi relații în modelul de date pe care îl creează în Excel.

Când importați mai multe tabele, Power Pivot detectează automat orice relație existentă între tabele. De asemenea, atunci când creați un tabel pivot, Power Pivot analizează datele din tabele. Detectează relațiile posibile care nu au fost definite și sugerează coloane adecvate pentru a le include în acele relații.

Algoritmul de detectare folosește date statistice despre valorile și metadatele coloanelor pentru a face inferențe despre probabilitatea relațiilor.

Tipurile de date din toate coloanele aferente ar trebui să fie compatibile. Pentru detectarea automată, sunt acceptate doar numărul întreg și tipurile de date text. Pentru mai multe informații despre tipurile de date, consultați Tipuri de date acceptate în Modele de date.

Pentru ca relația să fie detectată cu succes, numărul de chei unice din coloana de căutare trebuie să fie mai mare decât valorile din tabelul de pe latura multiplă. Cu alte cuvinte, coloana cheie din partea multor relații nu trebuie să conțină valori care nu se află în coloana cheie a tabelului de căutare. De exemplu, să presupunem că aveți un tabel care listează produsele cu ID-urile lor (tabelul de căutare) și un tabel de vânzări care listează vânzările pentru fiecare produs (aspectele multiple ale relației). Dacă înregistrările dvs. de vânzări conțin ID-ul unui produs care nu are un ID corespunzător în tabelul Produse, relația nu poate fi creată automat, dar este posibil să o puteți crea manual. Pentru ca Excel să detecteze relația, trebuie mai întâi să actualizați tabelul de căutare a produsului cu ID-urile produselor lipsă.

Asigurați-vă că numele coloanei cheii din partea laterală este similar cu numele coloanei cheii din tabelul de căutare. Numele nu trebuie să fie exact aceleași. De exemplu, într-o setare de afaceri, aveți adesea variații ale numelor coloanelor care conțin în esență aceleași date: ID Emp, ID angajat, ID angajat, EMP_ID, și așa mai departe. Algoritmul detectează nume similare și atribuie o probabilitate mai mare acelor coloane care au nume similare sau exact potrivite. Prin urmare, pentru a crește probabilitatea de a crea o relație, puteți încerca să redenumiți coloanele din datele pe care le importați în ceva similar cu coloanele din tabelele existente. Dacă Excel găsește mai multe relații posibile, atunci nu creează o relație.

Aceste informații vă pot ajuta să înțelegeți de ce nu sunt detectate toate relațiile sau cum modificările în metadate - cum ar fi numele câmpului și tipurile de date - ar putea îmbunătăți rezultatele detectării automate a relațiilor. Pentru mai multe informații, consultați Depanarea relațiilor.

Detectare automată pentru seturi numite

Relațiile nu sunt detectate automat între seturile numite și câmpurile conexe dintr-un tabel pivot. Puteți crea aceste relații manual. Dacă doriți să utilizați detectarea automată a relației, eliminați fiecare set de nume și adăugați câmpurile individuale din setul de nume direct în tabelul pivot.

Deducerea relațiilor

În unele cazuri, relațiile dintre tabele sunt înlănțuite automat. De exemplu, dacă creați o relație între primele două seturi de tabele de mai jos, se deduce că există o relație între celelalte două tabele și o relație este stabilită automat.

Produse și categorie - create manual

Categorie și subcategorie - create manual

Produse și subcategorie - se deduce relația

Pentru ca relațiile să fie înlănțuite automat, relațiile trebuie să meargă într-o singură direcție, așa cum se arată mai sus. Dacă relațiile inițiale erau între, de exemplu, Vânzări și produse, și Vânzări și clienți, nu se deduce o relație. Acest lucru se datorează faptului că relația dintre produse și clienți este o relație de la mulți la mulți.