Ali ste že uporabljali VLOOKUP za prenos stolpca iz ene tabele v drugo tabelo? Ker ima Excel 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.
-
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.
-
Naredite nekaj od tega: Oblikujte podatke kot tabelo ali Uvozite zunanje podatke kot tabelo na novem delovnem listu.
-
Dajte vsaki tabel pomenljivo ime: V razdelku Orodja za tabele kliknite Načrt > Ime tabele > in vnesite ime.
-
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.
-
Kliknite Podatki> Relacije.
Če je možnost Relacije obarvana sivo, vsebuje delovni zvezek le eno tabelo.
-
V pogovornem oknu Upravljanje relacij kliknite gumb Novo.
-
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.
-
Za Stolpec (tuji) izberite stolpec s podatki, ki so povezani s povezanim stolpcem (primarni) . Če ste na primer imeli datumski stolpec v obeh tabelah, bi zdaj izbrali ta stolpec.
-
Za možnost Povezana tabela izberite tabelo, ki ima vsaj en stolpec podatkov, povezan s tabelo, ki ste jo pravkar izbrali za Tabela.
-
Za Povezana tabela (Primarna) izberite stolpec z enoličnimi vrednostmi, ki ustrezajo vrednostim v stolpcu, ki ste ga izbrali za Stolpec.
-
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.
-
Zaženite dodatek Power Pivot in Microsoft Excel in odprite okno Power Pivot.
-
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.
-
V razdelku Cena kliknite Brezplačno.
-
V razdelku Kategorija kliknite Znanost in statistika.
-
Iskanje DateStream in kliknite naroči.
-
Vnesite svoj Microsoftov račun in kliknite Vpis. V oknu bi moral biti prikazan predogled podatkov.
-
Pomaknite se na dno in kliknite Poizvedba za izbiranje.
-
Kliknite Naprej.
-
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.
-
Kliknite Pridobi zunanje podatke > Iz podatkovnih storitev > Iz Tržnice Microsoft Azure, da uvozite drugi nabor podatkov.
-
V razdelku Vrsta kliknite Podatki.
-
V razdelku Cena kliknite Brezplačno.
-
Poiščite Zamude pri letih ameriških letalskih družb in kliknite Izberi.
-
Pomaknite se na dno in kliknite Poizvedba za izbiranje.
-
Kliknite Naprej.
-
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.
-
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.
-
V oknu dodatka Power Pivot kliknite Vrtilna tabela, da ustvarite vrtilno tabelo na novem ali obstoječem delovnem listu.
-
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.
-
Razširite BasicCalendarUS in kliknite možnost MonthInCalendar, da jo dodate v območje »Vrstice«.
-
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.
-
V podoknu »Seznam polj« v razdelku »Morda je treba ustvariti relacije med tabelami« kliknite Ustvari.
-
V tabeli v relaciji izberite On_Time_Performance in v stolpcu v relaciji (primarnem) izberite FlightDate.
-
V tabeli izberite BasicCalendarUS, v stolpcu (zunanji) pa izberite DateKey. Kliknite V redu, da ustvarite relacijo.
-
Opazite lahko, da je vsota minut zamude različna za vsak mesec.
-
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.
Namigi: Privzeto so meseci navedeni po abecedi. Če uporabljate dodatek Power Pivot, lahko spremenite vrstni red tako, da si meseci sledijo v kronološkem zaporedju.
-
Prepričajte se, da se tabela BasicCalendarUS odpre v oknu Power Pivot.
-
V tabeli »Osnovno« kliknite Razvrsti po stolpcih.
-
V razdelku »Razvrsti« izberite MonthInCalendar
-
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.
Č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.
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.
Recimo, da imate model, ki vsebuje prodajo izdelkov po ozemljah in ki ga nato uvozite demografske podatke, da ugotovite, ali obstaja povezava med prodajo in demografskimi trendi na posameznem ozemlju. Ker demografski podatki prihajajo iz drugega vira podatkov, so njegove tabele najprej izolirane od preostalega modela. Če želite demografske podatke integrirati s preostalim modelom, boste morali poiskati stolpec v eni od demografskih tabel, ki ustrezajo tisti, ki jo že uporabljate. Če so na primer demografski podatki organizirani po regiji, podatki o prodaji pa določajo, v katerih regijah je prišlo do prodaje, lahko dva nabora podatkov povezujete tako, da poiščete skupni stolpec, na primer Zvezna država, Poštna številka ali Regija, da zagotovite iskanje.
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.
