Jeste li ikad pomoću funkcije VLOOKUP stupac iz jedne tablice dohvaćali u drugu tablicu? Sad kad je u Excel ugrađen podatkovni model, funkcija VLOOKUP postala je zastarjela. Možete stvoriti odnos između dvije tablice s podacima na temelju podudarnih podataka u obje tablice. Potom možete stvoriti listove dodatka Power View te sastaviti zaokretne tablice i druga izvješća pomoću polja iz obiju tablica, čak i kad potječu iz različitih izvora. Ako, primjerice, imate podatke o klijentovim prodajnim rezultatima, možete ih uvesti pa povezati vremenske podatke da biste analizirali prodajne obrasce prema godini i mjesecu.
Sve tablice u radnoj knjizi navedene su na popisima polja zaokretne tablice i dodatka Power View.
Ako povezane tablice uvozite iz relacijske baze podataka, Excel te odnose često može stvoriti u podatkovnom modelu koji sastavlja u pozadini. U svim ćete drugim slučajevima odnose morati stvoriti ručno.
-
Radna knjiga mora sadržavati najmanje dvije tablice i svaka tablica mora imati stupac koji je moguće mapirati u stupac u drugoj tablici.
-
Učinite nešto od sljedećeg: Oblikovanje podataka kao tabliceili Uvoz vanjskih podataka kao tablice na novom radnom listu.
-
Svakoj tablici dodijelite smisleni naziv: Na vrpci Alati za tablice kliknite Dizajn > Naziv tablice pa unesite naziv.
-
Provjerite sadrži li stupac u jednoj tablici jedinstvene vrijednosti bez duplikata. Excel odnose može stvoriti samo ako jedan stupac sadrži jedinstvene vrijednosti.
Da biste, primjerice, klijentove prodajne rezultate povezali s vremenskim podacima, obje tablice moraju sadržavati datume u istom obliku (primjerice 1.1.2012.) i u najmanje jednoj tablici (vremenski podaci) svaki datum mora biti naveden samo jedanput u stupcu.
-
Kliknite Podaci > Odnosi.
Ako je stavka Odnosi zasivljena, radna knjiga možda sadrži samo jednu tablicu.
-
U okviru Upravljaj odnosima kliknite Novo.
-
U okviru Stvaranje odnosa kliknite strelicu uz stavku Tablica i na popisu odaberite tablicu. U odnosu jedan-prema-više ta bi se tablica trebala nalaziti na strani "više". U našem primjeru s klijentom i vremenskim podacima najprije biste odabrali tablicu s klijentovim prodajnim rezultatima jer se svakog dana vjerojatno odvija više prodaja.
-
U odjeljku Stupac (vanjski) odaberite stupac koji sadrži podatke vezane uz Povezani stupac (glavni). Da, primjerice, u obje tablice imate stupac s datumima, sada biste odabrali taj stupac.
-
U odjeljku Povezana tablica odaberite tablicu koja sadrži barem jedan stupac podataka povezanih s tablicom koju ste upravo odabrali u odjeljku Tablica.
-
U odjeljku Povezani stupac (glavni) odaberite stupac s jedinstvenim vrijednostima koje odgovaraju vrijednostima u stupcu koji ste odabrali u odjeljku Stupac.
-
Kliknite U redu.
Dodatne informacije o odnosima između tablica u programu Excel
Napomene o odnosima
-
Kada polja iz različitih tablica povučete na popis polja zaokretne tablice, znat ćete postoji li odnos. Ako se ne zatraži da stvorite odnos, Excel već ima podatke o odnosu potrebne za povezivanje podataka.
-
Stvaranje odnosa slično je korištenju funkcija VLOOKUP: da bi Excel povezao retke u jednoj tablici s recima u drugoj tablici, potrebni su stupci koji sadrže podudarne podatke. U primjeru s inteligencijom vremena tablica Klijent morala bi sadržavati datumske vrijednosti koje postoje i u tablici inteligencije vremena.
-
U podatkovnom modelu odnosi između tablica mogu biti jedan-prema-jedan (svaki putnik ima jednu zrakoplovnu kartu) ili jedan-prema-više (na svakom letu ima više putnika), ali ne i više-prema-više. Odnosi više-prema-više stvaraju pogreške kružne ovisnosti, primjerice "Otkrivena je kružna ovisnost". Ta će se pogreška pojaviti ako stvorite izravnu vezu između dviju tablica s odnosom više-prema-više ili ako stvorite neizravne veze (lanac odnosa između tablica u kojem je svaki odnos jedan-prema-više, ali je više-prema-više u cjelini). Dodatne informacije o odnosima potražite u članku Odnosi između tablica u podatkovnom modelu.
-
Vrste podataka u dva povezana stupca moraju biti kompatibilne. Detalje potražite u članku Vrste podataka u podatkovnim modelima programa Excel.
-
Drugi načini stvaranja odnosa mogu biti intuitivniji, osobito ako niste sigurni koje stupce koristiti. Pročitajte članak Stvaranje odnosa u prikazu dijagrama u dodatku Power Pivot.
Primjer: povezivanje vremenskih podataka s podacima o zrakoplovnim letovima
O odnosima između tablica i inteligenciji vremena možete se informirati pomoću besplatnih podataka iz trgovine Microsoft Azure Marketplace. Neki od tih skupova podataka vrlo su veliki, zbog čega je za preuzimanje podataka u razumnom roku potrebna brza internetska veza.
-
Pokrenite dodatak Power Pivot in Microsoft Excel i otvorite prozor dodatka Power Pivot.
-
Kliknite Dohvaćanje vanjskih podataka > S podatkovnog servisa > Iz trgovine Microsoft Azure Marketplace. Početna stranica trgovine Microsoft Azure Marketplace otvorit će se u čarobnjaku za uvoz tablica.
-
U odjeljku Cijena kliknite Besplatno.
-
U odjeljku Kategorija kliknite Znanost i statistika.
-
Traženje DateStream kliknite pretplati.
-
Unesite Microsoftov račun i kliknite Prijava. U prozoru će se pojaviti pretpregled podataka.
-
Pomaknite se do dna pa kliknite Odabir upita.
-
Kliknite Dalje.
-
Odaberite OsnovniKalendarHR, a zatim kliknite Dovrši da biste uvezli podatke. Putem brze internetske veze uvoz traje približno jednu minutu. Kada uvoz završi, vidjet ćete izvješće o stanju o prenesenih 73 414 redaka. Kliknite Zatvori.
-
Kliknite Dohvaćanje vanjskih podataka > Iz podatkovnog servisa > Iz trgovine Microsoft Azure Marketplace da biste uvezli drugi skup podataka.
-
U odjeljku Vrsta kliknite Podaci.
-
U odjeljku Cijena kliknite Besplatno.
-
Potražite stavku Kašnjenja letova hrvatskih zrakoplovnih tvrtki i kliknite Odaberi.
-
Pomaknite se do dna pa kliknite Odabir upita.
-
Kliknite Dalje.
-
Da biste uvezli podatke, kliknite Završi. Putem brze internetske veze uvoz traje 15 minuta. Kada uvoz završi, vidjet ćete izvješće o stanju o prenesenih 2 427 284 redaka. Kliknite Zatvori. Sada biste trebali imati dvije tablice u podatkovnom modelu. Da biste ih povezali, potrebni su kompatibilni stupci u svakoj od njih.
-
Imajte na znanje da je datumski ključ u osnovnomkalendarUS-u u obliku 1.1.2012. 12.00.00. Tablica On_Time_Performance sadrži i stupac datetime, FlightDate, čije su vrijednosti navedene u istom obliku: 1. 1. 2012. 12.00.2012. Ta dva stupca sadrže podudarne podatke iste vrste podataka, a najmanje jedan od stupaca (DateKey) sadrži samo jedinstvene vrijednosti. U sljedećih nekoliko koraka te ćete stupce koristiti za povezivanje tablica.
-
U prozoru dodatka Power Pivot kliknite Zaokretna tablica da biste stvorili zaokretnu tablicu na novom ili postojećem radnom listu.
-
Na popisu polja proširite Stizanje_na_vrijeme i kliknite MinuteKašnjenjaDol da biste tu stavku dodali u područje Vrijednosti. U zaokretnoj tablici vidjet ćete ukupno vrijeme kašnjenja letova navedeno u minutama.
-
Proširite OsnovniKalendarHR i kliknite stavku KalendarskiMjesec da biste je dodali u područje Reci.
-
Uočite da su sada u zaokretnoj tablici navedeni mjeseci, ali da je ukupni zbroj minuta isti za svaki mjesec. Ponavljajuće, identične vrijednosti upućuju na to da je potreban odnos.
-
Na popisu polja u odjeljku "Možda su potrebni odnosi između tablica" kliknite Stvori.
-
U povezanoj tablici odaberite Stizanje_na_vrijeme i u odjeljku Povezani stupac (glavni) odaberite DatumLeta.
-
U tablici odaberite OsnovniKalendarHR i u odjeljku Stupac (vanjski) odaberite KljučDatuma. Kliknite U redu da biste stvorili odnos.
-
Uočite da se zbroj minuta kašnjenja sada razlikuje za svaki mjesec.
-
U tablici OsnovniKalendarHR povucite KljučGodine u područje Reci iznad stavke MjesecNaKalendaru.
Sada možete sortirati kašnjenja u dolasku po godini i mjesecu ili drugim vrijednostima na kalendaru.
Savjeti: Mjeseci su po zadanom navedeni abecednim redom. Pomoću dodatka Power Pivot možete promijeniti redoslijed sortiranja da bi se mjeseci prikazivali kronološkim redoslijedom.
-
Provjerite je li u prozoru dodatka Power Pivot otvorena tablica OsnovniKalendarHR.
-
Na kartici Polazno kliknite Sortiraj po stupcu.
-
Na izborniku Sortiranje odaberite KaledarskiMjesec
-
Na izborniku Po odaberite MjesecUGodini.
Zaokretna tablica sada sortira svaku kombinaciju mjesec-godina (listopad 2011., studeni 2011.) po broju mjeseca u godini (10., 11.). Promjena redoslijeda sortiranja jednostavna je jer sažetak sadržaja TokDatuma nudi sve stupce koji su potrebni da bi ovaj scenarij funkcionirao. Ako koristite neku drugu tablicu inteligencije vremena, koraci će se razlikovati.
“Možda je potreban odnos između tablica”
Prilikom dodavanja polja u zaokretnu tablicu dobit ćete obavijest ako je potreban odnos između tablica da bi polja koja ste odabrali u zaokretnoj tablici imala smisla.
Premda vam Excel može reći kada je potreban odnos, on vam ne može reći koje je tablice i stupce potrebno koristiti ni je li odnos između tablica uopće moguć. Da biste dobili potrebne odgovore, slijedite korake u nastavku.
Prvi korak: utvrđivanje koje je tablice potrebno navesti u odnosu
Ako model sadrži samo nekoliko tablica, možda će biti odmah jasno koje one morate koristiti. No za veće modele vjerojatno vam je potrebna pomoć. Jedan je pristup korištenje prikaza dijagrama u dodatku Power Pivot. Prikaz dijagrama omogućuje vizualni prikaz svih tablica u podatkovnom modelu. Pomoću prikaza dijagrama možete brzo odrediti koje su tablice odvojene od ostatka modela.
Napomena: Moguće je stvoriti višeznačne odnose koji nisu valjani kada se koriste u zaokretnoj tablici ili izvješću značajke Power View. Pretpostavimo da su sve vaše tablice na neki način povezane s drugim tablicama u modelu, ali kada pokušate kombinirati polja iz različitih tablica, pojavljuje se poruka "Možda su potrebni odnosi između tablica". Najvjerojatnije ste naišli na odnos više-prema-više. Ako slijedite lanac odnosa između tablica povezan s tablicama koje želite koristiti, vjerojatno ćete otkriti da imate dva ili više odnosa između tablica jedan-prema-više. Ne postoji jednostavno rješenje koje funkcionira u svakoj situaciji, ali možete probati stvoriti izračunate stupce da biste stupce koje želite koristiti konsolidirali u jednu tablicu.
Drugi korak: pronalaženje stupaca koje je moguće koristiti za stvaranje puta od jedne tablice do druge
Kada otkrijete koja je tablica odvojena od ostatka modela, pregledajte njezine stupce da biste utvrdili sadrži li neki drugi stupac negdje drugdje u modelu podudarne vrijednosti.
Pretpostavimo, primjerice, da imate model koji sadrži rezultate prodaje proizvoda po području i da ste kasnije uvezli demografske podatke da biste otkrili postoji li korelacija između rezultata prodaje i demografskih trendova u svakom području. Budući da demografski podaci potječu iz drugog izvora podataka, tablice s njima isprva su odvojene od ostatka modela. Da biste demografske podatke integrirali s ostatkom modela, u jednoj od tablica s demografskim podacima morat ćete pronaći stupac koji odgovara nekom koji već koristite. Ako su, primjerice, demografski podaci organizirani po području i u podacima o rezultatima prodaje navedeno je u kojem je području prodaja obavljena, dva skupa podataka možete povezati tako da pronađete zajednički stupac, primjerice Država, Poštanski broj ili Regija da biste omogućili pretraživanje.
Osim podudarnih vrijednosti postoji još nekoliko preduvjeta za stvaranje odnosa:
-
Podatkovne vrijednosti u stupcu za pretraživanje moraju biti jedinstvene. Drugim riječima, stupac ne smije sadržavati duplikate. U podatkovnom modelu vrijednosti null i prazni nizovi istovjetni su praznini, koja je posebna podatkovna vrijednost. To znači da u stupcu za pretraživanje ne možete imati više vrijednosti null.
-
Vrste podataka izvorišnog stupca i stupca za pretraživanje moraju biti kompatibilne. Dodatne informacije o vrstama podataka potražite u članku Vrste podataka u podatkovnim modelima.
Dodatne informacije o odnosima između tablica potražite u članku Odnosi između tablica u podatkovnom modelu.
