Ustvarjanje odnosa med tabelama v Excelu

Ali ste že uporabljali VLOOKUP za prenos stolpca iz ene tabele v drugo tabelo? Ker ima Excel 2013 vgrajen podatkovni model, je VLOOKUP zastarel. Med dvema tabelama s podatki lahko ustvarite relacijo, ki temelji na ujemajočih se podatkih v posamezni tabeli. Nato lahko ustvarite liste funkcije Power View ter iz polj iz vsake tabele ustvarite vrtilne tabele in druga poročila, tudi če sta tabeli iz različnih virov. Če imate na primer podatke o prodaji, morda želite uvoziti in povezati podatke o času, da lahko analizirate vzorce prodaje po letih in mesecih.

Vse tabele v delovnem zvezku so navedene na seznamih polj vrtilne tabele in funkcije Power View.

Ko uvažate tabele v relaciji iz relacijske zbirke podatkov, Excel pogosto ustvari te relacije v podatkovnem modelu, ki ga sestavlja v ozadju. V vseh drugih primerih boste morali relacije ustvariti ročno.

  1. Prepričajte se, da delovni zvezek vsebuje najmanj dve tabeli in da ima vsaka od njih stolpec, ki ga je mogoče preslikati v stolpec druge tabele.
  2. Podatke oblikujte kot tabelo ali

uvozite zunanje podatke kot tabelo na nov delovni list.

  1. Dajte vsaki tabel pomenljivo ime: V razdelku Orodja za tabele kliknite Načrt > Ime tabele > in vnesite ime.
  2. Preverite, ali ima stolpec v eni od tabel enolične podatkovne vrednosti brez podvojitev. Excel lahko ustvari relacijo samo, če en stolpec vsebuje enolične vrednosti.

Če želite npr. ustvariti relacijo med prodajo strankam in podatki o času, morajo biti v obe tabeli vključeni podatki v isti obliki zapisa (npr. 1.1.2012), najmanj ena tabela (podatki o času) pa navaja vsak datum le enkrat v stolpcu.

  1. Kliknite Podatki> Relacije.

Če je možnost Relacije obarvana sivo, vsebuje delovni zvezek le eno tabelo.

  1. V pogovornem oknu Upravljanje relacij kliknite gumb Novo.
  2. V polju Ustvari relacijo kliknite puščico dol za možnost Tabela in izberite tabelo na seznamu. V relaciji »ena proti mnogo« bi morala biti ta tabela na strani »mnogo«. Pri primeru »stranka - podatki o času« bi najprej izbrali tabelo prodaje strankam, ker pride do številnih prodaj ne glede na dan.
  3. Za možnost Stolpec (zunanji) izberite stolpec, ki vsebuje podatke, ki so v relaciji z možnostjo Stolpec v relaciji (primarni). Če bi imeli npr. stolpec z datumi v obeh tabelah, bi zdaj izbrali ta stolpec.
  4. Za možnost Povezana tabela izberite tabelo, ki ima vsaj en stolpec podatkov, povezan s tabelo, ki ste jo pravkar izbrali za Tabela.
  5. Za Povezana tabela (Primarna) izberite stolpec z enoličnimi vrednostmi, ki ustrezajo vrednostim v stolpcu, ki ste ga izbrali za Stolpec.
  6. Kliknite V redu.
Več o relacijah med tabelami v Excelu


Opombe o relacijah

  • Ali relacije obstajajo, boste vedeli, ko povlečete polja iz različnih tabel na seznam polj vrtilne tabele. Če vas Excel ne pozove, da ustvarite relacijo, pomeni, da že ima informacije o relaciji, ki jih potrebuje za ustvarjanje relacij med podatki.
  • Ustvarjanje relacij je podobno uporabi funkcij VLOOKUP: potrebujete stolpce, ki vsebujejo ujemajoče se podatke, tako da lahko Excel navzkrižno preverja vrstice ene tabele z vrsticami druge. V primeru »podatki o času« bi bilo treba tabelo »Stranke« dopolniti z datumskimi vrednostmi, ki prav tako obstajajo v tabeli s podatki o času.
  • V podatkovnem modelu so lahko odnosi tabel »ena proti ena« (vsak potnik ima eno vozovnico) ali »ena proti mnogo« (vsak let ima več potnikov), vendar ne »mnogo proti mnogo«. Odnosi »mnogo proti mnogo« povzročijo napake krožne odvisnosti, kot je »Zaznana je krožna odvisnost«. Do te napake pride, če ustvarite neposredno povezavo med dvema tabelama z odnosom »mnogo proti mnogo« ali posredne povezave (veriga tabelnih odnosov »ena proti mnogo« znotraj posameznih odnosov, ki pa imajo odnos »mnogo proti mnogo« pri ogledu od enega konca do drugega). Več preberite v članku Odnosi med tabelami v podatkovnem modelu.
  • Podatkovni tipi v dveh stolpcih morajo biti združljivi. Za podrobnosti glejte Podatkovni tipi v Excelovih podatkovnih modelih.
  • Drugi načini ustvarjanja relacij so lahko bolj intuitivni, zlasti če ne veste, katere stolpce bi uporabili. Glejte Ustvarjanje odnosa v pogledu diagrama v orodju Power Pivot.

Primer: Ustvarjanje relacije med podatki o času in podatki o letih letalskih družb

Naučite se lahko o relacijah med tabelami in podatkih o času z uporabo brezplačnih podatkov Tržnice Microsoft Azure. Nekateri od teh naborov podatkov so ogromni in zahtevajo hitro internetno povezavo za dokončanje prenosa podatkov v sprejemljivem času.

  1. Zaženite dodatek Power Pivot v programu Microsoft Excel 2013 in odprite okno Power Pivot.
  2. Kliknite Pridobi zunanje podatke > Iz podatkovnih storitev > Iz Tržnice Microsoft Azure. Domača stran Tržnice Microsoft Azure se odpre v čarovniku za uvoz tabele.
  3. V razdelku Cena kliknite Brezplačno.
  4. V razdelku Kategorija kliknite Znanost in statistika.
  5. Poiščite DateStream in kliknite Naroči. Več o tem najdete v odseku Vir podatkov o času.
  6. Vnesite svoj Microsoftov račun in kliknite Vpis. V oknu bi moral biti prikazan predogled podatkov.
  7. Pomaknite se na dno in kliknite Poizvedba za izbiranje.
  8. Kliknite Naprej.
  9. Izberite BasicCalendarUS in kliknite Dokončaj, da uvozite podatke. S hitro internetno povezavo bi moral uvoz trajati približno eno minuto. Ko je prenos dokončan, bi moralo biti prikazano poročilo o stanju prenesenih 73.414 vrstic. Kliknite Zapri.
  10. Kliknite Pridobi zunanje podatke > Iz podatkovnih storitev > Iz Tržnice Microsoft Azure, da uvozite drugi nabor podatkov.
  11. V razdelku Vrsta kliknite Podatki.
  12. V razdelku Cena kliknite Brezplačno.
  13. Poiščite Zamude pri letih ameriških letalskih družb in kliknite Izberi.
  14. Pomaknite se na dno in kliknite Poizvedba za izbiranje.
  15. Kliknite Naprej.
  16. Kliknite Dokončaj, da uvozite podatke. S hitro internetno povezavo bi moral uvoz trajati približno 15 minut. Ko je prenos dokončan, bi moralo biti prikazano poročilo o stanju prenesenih 2.427.284 vrstic. Kliknite Zapri. V podatkovnem modelu bi morali biti dve tabeli. Če želite ustvariti relacijo med njima, boste potrebovali združljive stolpce v obeh tabelah.
  17. Opazite lahko, da je DateKey v BasicCalendarUS v obliki zapisa 1/1/2012 12:00:00 AM. Tabela On_Time_Performance ima tudi stolpec »Datetime«, FlightDate, katerih vrednosti so določene v enaki obliki zapisa: 1/1/2012 12:00:00 AM. Oba stolpca vsebujeta ujemajoče se podatke, iste vrste podatkov in vsaj eden od stolpcev (DateKey) vsebuje le enolične vrednosti. V naslednjih nekaj korakih boste te stolpce uporabili, da ustvarite relacije med tabelami.
  18. V oknu dodatka Power Pivot kliknite Vrtilna tabela, da ustvarite vrtilno tabelo na novem ali obstoječem delovnem listu.
  19. V podoknu »Seznam polj« razširite On_Time_Performance in kliknite možnost ArrDelayMinutes, da jo dodate v območje »Vrednosti«. V vrtilni tabeli bi moral biti prikazan skupni znesek časov, ko so bili leti preloženi, merjen v minutah.
  20. Razširite BasicCalendarUS in kliknite možnost MonthInCalendar, da jo dodate v območje »Vrstice«.
  21. Opazite lahko, da so v vrtilni tabeli navedeni meseci, vendar je skupna vsota minut enaka za vsak mesec. Ponavljajoče se, enake vrednosti nakažejo nujnost ustvarjanja relacije.
  22. V podoknu »Seznam polj« v razdelku »Morda je treba ustvariti relacije med tabelami« kliknite Ustvari.
  23. V tabeli v relaciji izberite On_Time_Performance in v stolpcu v relaciji (primarnem) izberite FlightDate.
  24. V tabeli izberite BasicCalendarUS, v stolpcu (zunanji) pa izberite DateKey. Kliknite V redu, da ustvarite relacijo.
  25. Opazite lahko, da je vsota minut zamude različna za vsak mesec.
  26. V razdelku BasicCalendarUS povlecite YearKey v območje »Vrstice« nad možnost MonthInCalendar.

Zdaj lahko razčlenite zamude pri prihodih po letu ali mesecu ali po drugih vrednostih na koledarju.

 Namig    Privzeto so meseci navedeni po abecedi. Če uporabljate dodatek Power Pivot , lahko spremenite vrstni red tako, da si meseci sledijo v kronološkem zaporedju.

  1. Prepričajte se, da se tabela BasicCalendarUS odpre v oknu Power Pivot .
  2. V tabeli »Osnovno« kliknite Razvrsti po stolpcih .
  3. V razdelku »Razvrsti« izberite MonthInCalendar
  4. V razdelku »Po« izberite MonthOfYear .

Vrtilna tabela razvršča posamezno kombinacijo »mesec – leto« (oktober 2011, november 2011) po številki meseca v letu (10, 11). Spreminjanje vrstnega reda razvrščanja je preprosto, saj vir DateStream posreduje vse obvezne stolpce za uspešno delovanje tega scenarija. Če uporabljate drugačno tabelo s podatki o času, bo ta korak drugačen.

»Morda je treba ustvariti relacije med tabelami«

Ko dodajate polja v vrtilno tabelo, ste obveščeni, če je zahtevana relacija med tabelami, da ugotovite, ali je bilo smiselno izbirati polja v vrtilni tabeli.

Ko je zahtevana relacija, se pojavi gumb »Ustvari«

Čeprav vam Excel lahko nakaže, kdaj je relacija nujna, vam ne more priporočiti, katere tabele in stolpce je treba uporabiti, ali vas obvestiti, ali je relacija med tabelami sploh mogoča. Sledite tem korakom, če želite pridobiti odgovore, ki jih potrebujete.

1. korak: Določite, katere tabele bodo določene v relaciji

Če vsebuje vaš model le nekaj tabel, je lahko nemudoma jasno, katere je treba uporabiti. Za večje modele pa bi verjetno potrebovali pomoč. Eden od pristopov je, da uporabite pogled diagrama v Power Pivot dodatku. Pogled diagrama ponuja grafično ponazoritev vseh tabel v podatkovnem modelu. S pogledom diagrama lahko hitro določite, katere tabele so ločene od drugih v modelu.

Pogled diagrama, ki prikaže nepovezane tabele

 Opomba    Ustvariti je mogoče dvoumne relacije, ki niso veljavne, ko jih uporabite v vrtilni tabeli ali poročilu Power View. Denimo, da so vse vaše tabele v nekakšni relaciji z drugimi tabelami v modelu, toda ko poskusite združiti polja iz različnih tabel, je prikazano sporočilo »Morda je treba ustvariti relacije med tabelami«. Najverjetneje ste se soočili z relacijo »mnogo proti mnogo«. Če sledite verigi relacij tabel, ki so povezane s tabelami, ki jih želite uporabiti, boste verjetno ugotovili, da imate več relacij tabel »ena proti mnogo«. Ni preproste rešitve, ki bi delovala v vsaki situaciji, lahko pa poskusite ustvariti izračunane stolpce , da uskladite želene stolpce v eni tabeli.

2. korak: Poiščite stolpce, s katerimi lahko ustvarite pot od ene tabele do naslednje

Ko ugotovite, katera tabela ni povezana z drugimi v modelu, preglejte njene stolpce, da določite, ali kateri od drugih stolpcev v modelu vsebuje ujemajoče se vrednosti.

Denimo, da imate model, ki vsebuje prodajo izdelkov po prodajnem področju, in nato uvozite demografske podatke, da ugotovite, ali obstaja korelacija med prodajo in demografskimi trendi na posameznem območju. Ker pridejo demografski podatki iz različnih podatkovnih virov, so njihove tabele na začetku ločene od drugih v modelu. Če želite povezati demografske podatke s preostankom modela, poiščite stolpec v eni od tabel z demografskimi podatki, ki ustreza stolpcu, ki ga že uporabljate. Če so npr. demografski podatki organizirani po območju in podatki prodaje določajo, v katerem območju je prišlo do prodaje, lahko dodate relacijo med dvema podatkovnima nizoma z iskanjem skupnega stolpca, kot je »Država«, »Poštna številka« ali »Območje«.

Poleg ujemajočih se vrednosti obstajajo nekatere dodatne zahteve za ustvarjanje relacije:

  • Podatkovne vrednosti v stolpcu za iskanje morajo biti enolične. Izraženo drugače: stolpec ne sme vsebovati dvojnikov. V podatkovnem modelu so ničle in prazni nizi enaki prazni vrednosti, ki je razlikovalna podatkovna vrednost. To pomeni, da ne morete imeti več ničel v stolpcu za iskanje.
  • Podatkovni tipi v izvornem stolpcu in stolpcu za iskanje morajo biti združljivi. Če želite več informacij o podatkovnih tipih, preberite članek Podatkovni tipi v podatkovnih modelih.

Če želite izvedeti več o relacijah med tabelami, preberite članek Relacije med tabelami v podatkovnem modelu.

Na vrh strani Na vrh strani

 
 
Velja za:
Excel 2013, Power Pivot v programu Excel 2013