Da li ste ikada koristili funkciju VLOOKUP kako biste kolonu preneli iz jedne tabele u drugu? Sada kada Excel ima ugrađeni model podataka, funkcija VLOOKUP je zastarela. Možete da kreirate vezu između dve tabele sa podacima zasnovanu na podacima koji se podudaraju u svakoj tabeli. Zatim možete da kreirate Power View listove i izgradite izvedene tabele i druge izveštaje pomoću polja iz svake tabele, čak iako su tabele iz različitih izvora. Na primer, ako imate podatke o prodaji po klijentima, možda ćete želeti da uvezete i povežete podatke vremenske inteligencije kako biste analizirali prodajne trendove po godinama i mesecima.
Sve tabele u radnoj svesci navedene su u listama izvedene tabele i Power View polja.
Kada uvezete povezane tabele iz relacione baze podataka, Excel često može da kreira te relacije u modelu podataka koji pravi u pozadini. U svim ostalim slučajevima moraćete ručno da kreirate relacije.
-
Uverite se da radna sveska sadrži najmanje dve tabele i da svaka tabela sadrži kolonu koja se može mapirati sa kolonom u drugoj tabeli.
-
Uradite nešto od sledećeg: Oblikujte podatke kao tabelu ili Uvezi spoljne podatke kao tabelu u novom radnom listu.
-
Svakoj tabeli dajte ima koje ima smisla: U okviru Alatke za tabelu izaberite stavke Dizajn > Ime tabele > i unesite ime.
-
Potvrdite da kolona u jednoj od tabela ima jedinstvene vrednosti podataka bez duplikata. Excel može da kreira relaciju samo ako jedna kolona sadrži jedinstvene vrednosti.
Na primer, za povezivanje prodaje kupcima s vremenskom inteligencijom, obe tabele moraju imati datume u istom formatu (na primer, 01.01.2012.) i da najmanje jedna tabela (vremenska inteligencija) svaki datum prikazuje samo jednom u koloni.
-
Izaberite stavke Podaci > Relacije.
Ako je stavka Relacije neaktivna, to je zato što radna sveska sadrži samo jednu tabelu.
-
U dijalogu Upravljanje relacijama kliknite na dugme Novo.
-
U dijalogu Kreiranje relacija kliknite na strelicu za polje Tabela, a zatim sa liste izaberite tabelu. U relaciji „jedan-prema-više“, ova tabela bi trebalo da bude na strani „više“. Koristeći primer „klijent-vremenska inteligencija“, prvo biste odabrali tabelu prodaje po klijentima zato što u određenom danu može biti više prodaja.
-
Za vrednost Kolona (Strano) izaberite kolonu koja sadrži podatke povezane sa vrednošću Povezana kolona (primarna). Na primer, ako ste u obe tabele imali kolonu sa datumima, sada biste odabrali tu kolonu.
-
Za vrednost Srodna tabela izaberite tabelu koja ima najmanje jednu kolonu sa podacima povezanu sa tabelom koju ste izabrali za polje Tabela.
-
Za vrednost Srodna kolona (primarna) izaberite kolonu sa jedinstvenim vrednostima koje se podudaraju sa vrednostima u koloni koju ste izabrali za polje Kolona.
-
Kliknite na dugme U redu.
Više o relacijama između tabela u programu Excel
Beleške o relacijama
-
Znaćete da li relacije postoje kada prevučete polja iz različitih tabela na listu polja izvedene tabele. Ako ne bude zatraženo da kreirate relaciju, Excel već ima informacije o relaciji koje su mu potrebne za povezivanje podataka.
-
Kreiranje relacija je slično korišćenju funkcija VLOOKUP: potrebne su vam kolone sa podacima koji se podudaraju tako da Excel može međusobno povezati redove u jednoj tabeli s redovima u drugoj. U primeru s vremenskom inteligencijom, tabela „Klijent“ bi trebalo da sadrži vrednosti podataka koji takođe postoje u tabeli s podacima vremenske inteligencije.
-
U modelu podataka, relacije između tabela mogu da budu „jedan-prema-jedan“ (svaki putnik ima jednu kartu za ukrcavanje) ili „jedan-prema-više“ (svaki let ima više putnika), ali ne „više-prema-više“. Relacije „više-prema-više“ za posledicu imaju grešku kružne zavisnosti, kao što je „Otkrivena je kružna zavisnost“. Ova greška će se pojaviti ako napravite direktnu vezu između dve tabele tipa „više-prema-više“ ili indirektne veze (lanac relacija između tabela koje su „jedan-prema-više“ unutar svake relacije, ali „više-prema-više“ kada se posmatraju od jednog kraja do drugog). Saznajte više u članku Relacije između tabela u modelu podataka.
-
Tipovi podataka u dve kolone moraju biti kompatibilni. Više informacija potražite u članku Tipovi podataka u Excel modelima podataka.
-
Drugi načini da kreirate relacije mogu da budu intuitivniji, posebno ako niste sigurni koje kolone da koristite. Pročitajte članak Kreiranje relacije u prikazu dijagrama u programskom dodatku Power Pivot.
Primer: povezivanje podataka vremenske inteligencije sa podacima o letovima avio-kompanije
O relacijama između tabela i vremenskoj inteligenciji možete da naučite koristeći besplatne podatke sa lokacije Microsoft Azure Marketplace. Neki od tih skupova podataka su veoma veliki i za njih je neophodna brza internet veza da bi se preuzimanje podataka završilo u razumnom vremenskom periodu.
-
Odaberite stavke Preuzmi spoljne podatke > Iz usluge podataka > Sa lokacije Microsoft Azure Marketplace. Matična stranica lokacije Microsoft Azure Marketplace se otvara u čarobnjaku za uvoz tabele.
-
U okviru Cena izaberite stavku Besplatno.
-
U okviru Kategorija izaberite stavku Nauka i statistika.
-
Pronalaženje Tokdatuma i kliknite na dugme pretplata.
-
Unesite svoj Microsoft nalog i kliknite na dugme Prijavi se. U prozoru bi trebalo da se pojavi pregled podataka.
-
Pomerite se do dna i kliknite na dugme Upit za izdvajanje.
-
Izaberite stavku Dalje.
-
Odaberite OsnovniKalendarSAD i zatim kliknite na dugme Završi da biste uvezli podatke. Preko brze internet veze uvoz bi trebalo da traje otprilike minut. Kada se završi trebalo bi da vidite izveštaj o statusu koji kaže da je preneto 73.414 redova. Kliknite na dugme Zatvori.
-
Da biste uvezli drugi skup podataka, izaberite stavke Preuzimanje spoljnih podataka > Iz usluge sa podacima > Sa lokacije Microsoft Azure Marketplace.
-
U okviru Tip izaberite stavku Podaci.
-
U okviru Cena izaberite stavku Besplatno.
-
Pronađite Kašnjenje letova prevoznika SAD i kliknite na dugme Izaberi.
-
Pomerite se do dna i kliknite na dugme Upit za izdvajanje.
-
Kliknite na dugme Dalje.
-
Kliknite na dugme Završi da biste uvezli podatke. Preko brze internet veze ovaj uvoz može da potraje 15 minuta. Kada se završi, trebalo bi da vidite izveštaj o statusu koji kaže da je preneto 2.427.284 redova. Kliknite na dugme Zatvori. U modelu podataka sada bi trebalo da imate dve tabele. Da bismo ih povezali, biće nam potrebne kompatibilne kolone u svakoj od tabela.
-
Obratite pažnju na to da je DateKey u BasicCalendarUS formatu 01.01.2012. u 12:00:00. Tabela On_Time_Performance ima i kolonu datum/vreme, Datum Letenja, čije su vrednosti navedene u istom formatu: 01.01.2012. Dve kolone sadrže podatke koji se podudaraju, istog tipa podataka, a najmanje jedna od kolona (DateKey) sadrži samo jedinstvene vrednosti. U sledećih nekoliko koraka ćete koristiti ove kolone za relacije tabela.
-
U prozoru Power Pivot izaberite stavku Izvedena tabela da biste kreirali izvedenu tabelu u novom ili postojećem radnom listu.
-
U listi polja razvijte Izvršeno_na_vreme i izaberite KašnjenjePriDolaskuMinuta da biste ga dodali u oblast „Vrednosti“. U izvedenoj tabeli trebalo bi da vidite ukupno kašnjenje letova, mereno u minutima.
-
Razvijte OsnovniKalendarSAD i kliknite na MesecUKalendaru da biste ga dodali u oblast „Redovi“.
-
Primetite da izvedena tabela sada navodi mesece, ali je ukupan broj minuta isti za svaki mesec. Ponavljanje identičnih vrednosti ukazuje na to da je potrebna relacija.
-
U listi polja, u odeljku „Možda su potrebne relacije između tabela“ kliknite na dugme Kreiraj.
-
U padajućoj listi „Povezana tabela“ izaberite Izvršeno_na_vreme, a u padajućoj listi „Povezana kolona (primarna)“ odaberite DatumLeta.
-
U padajućoj listi „Tabela“ izaberite OsnovniKalendarSAD, a u padajućoj listi „Kolona (sporedna)“ odaberite ŠifraDatuma. Kliknite na dugme U redu da biste kreirali relaciju.
-
Primetite da se zbir minuta kašnjenja sada razlikuje za svaki mesec.
-
U tabeli OsnovniKalendarSAD izaberite i prevucite ŠifraGodine u oblast „Redovi“, iznad MesecUKalendaru.
Sada možete da isecate kašnjenja u dolasku po godini i mesecu, ili drugim vrednostima u kalendaru.
Saveti: Meseci su podrazumevano navedeni abecednim redom. Koristeći programski dodatak Power Pivot, možete da promenite sortiranje tako da se meseci pojavljuju hronološkim redom.
-
Proverite da li je tabela BasicCalendarUS otvorena u Power Pivot prozoru.
-
Na tabeli „Početno“ׂizaberite Sortiraj po koloni.
-
U padajućoj listi „Sortiranje“ odaberite MesecUKalendaru
-
U padajućoj listi „Po“ odaberite MesecUKalendaru
Izvedena tabela sada sortira svaku kombinaciju mesec-godina (oktobar 2011, novembar 2011.) po broju meseca u godini (10, 11). Menjanje redosleda sortiranja je lako jer feed TokDatuma pruža sve kolone potrebne da bi ovaj scenario radio. Ako koristite drugu tabelu sa podacima vremenske inteligencije, koraci koje treba da primenite biće drugačiji.
„Može biti potrebno kreiranje relacija između tabela“
Dok dodajete polja u izvedenu tabelu, bićete informisani ako je potrebna relacija između tabela kako bi polja koja ste izabrali u izvedenoj tabeli imala smisla.
Iako Excel može da vam kaže kada je potrebna relacija, on ne može da vam kaže koje tabele i kolone da koristite ili je li relacija između tabela uopšte moguća. Pokušajte da sledite ove korake kako biste dobili potrebne odgovore.
1. korak: utvrđivanje koje će tabele biti navedene u relaciji
Ako model sadrži samo nekoliko tabela, može biti odmah vidljivo koje od njih treba da koristite. Ali za veće modele verovatno će vam biti potrebna pomoć. Jedan pristup je da upotrebite prikaz dijagrama u programskom dodatku Power Pivot. Prikaz dijagrama pruža vizuelnu predstavu svih tabela u modelu podataka. Korišćenjem prikaza dijagrama možete brzo utvrditi koje su tabele odvojene od ostatka modela.
Napomena: Moguće je kreirati dvosmislene relacije koje su nevažeće kada se koriste u izvedenoj tabeli ili Power View izveštaju. Pretpostavimo da su sve tabele na neki način povezane sa drugim tabelama u modelu, ali kada pokušate da kombinujete polja iz različitih tabela, dobijete poruku „Možda su potrebne relacije između tabela“. Najverovatniji uzrok je što ste naišli na vezu „više-prema-više“. Ako pratite lanac relacija između tabela što vode do tabela koje želite da koristite, verovatno ćete otkriti da imate dve relacije „jedan-prema-više“ ili više njih. Nema jednostavnog zaobilaznog rešenja koje radi u svakoj situaciji, ali možete probati da kreirate izračunate kolone kako biste kolone koje želite koristiti konsolidovali u jednu tabelu.
2. korak: pronalaženje kolona koje se mogu upotrebiti za kreiranje putanje od jedne tabele do druge
Kada ste utvrdili koja je tabela odvojena od ostatka modela, pregledajte njene kolone kako biste utvrdili da li druga kolona, negde drugde u modelu, sadrži vrednosti koje se podudaraju.
Na primer, pretpostavimo da imate model koji sadrži podatke o prodaji proizvoda po teritoriji i da ste kasnije uvezli demografske podatke kako biste pronašli postoji li korelacija između prodaje i demografskih trendova na svakoj teritoriji. Pošto demografski podaci dolaze iz drugog izvora podataka, njegove tabele su na početku izolovane od ostatka modela. Da biste demografske podatke integrisali sa ostatkom modela, moraćete da pronađete kolonu u jednoj od tabela sa demografskim podacima koja odgovara koloni koju već koristite. Na primer, ako su demografski podaci organizovani po regionima, a podaci o prodaji navode u kom regionu je bila prodaja, dva skupa podataka možete povezati pronalaženjem zajedničke kolone, kao što je „Država“, „Poštanski broj“ ili „Region“, koja će obezbediti pronalaženje.
Pored podudaranja vrednosti, postoji i nekoliko dodatnih zahteva prilikom kreiranja relacije:
-
Vrednosti podataka u koloni za pronalaženje moraju biti jedinstvene. Drugim rečima, ta kolona ne sme da sadrži duplikate. U modelu podataka, nule i prazne niske su jednake praznoj vrednosti, koja predstavlja posebnu vrednost podataka. To znači da u koloni za pretraživanje ne možete imati više polja bez vrednosti.
-
Tipovi podataka izvorne kolone i kolone za pronalaženje moraju biti kompatibilni. Više informacija o tipovima podataka pronaći ćete u članku Tipovi podataka u modelima podataka.
Da biste naučili više o relacijama između tabela, pogledajte članak Relacije između tabela u modelu podataka.
