Praktični vodič: uvoz podataka u Excel i stvaranje podatkovnog modela

Ovaj se praktični vodič sastoji od sljedećih odjeljaka:

• Uvoz podataka iz baze podataka

• Uvoz podataka iz proračunske tablice

• Uvoz podataka kopiranjem i lijepljenjem

• Stvaranje odnosa između uvezenih podataka

• Kontrolna točka i test

Na kraju ovog praktičnog vodiča nalazi se test kojim možete provjeriti što ste naučili.

U ovoj seriji vodiča koriste se podaci koji opisuju olimpijske medalje, zemlje domaćine i razne olimpijske sportske događaje. Predlažemo da sve vodiče pregledate po redu. 

Uvoz podataka iz baze podataka

Ovaj vodič počinjemo s praznom radnom knjigom. Cilj je ovog dijela povezati vanjski izvor podataka te uvesti te podatke u Excel radi daljnje analize.

Počnimo preuzimanjem nekih podataka s interneta. U tim podacima, sadržanim u bazi podataka programa Microsoft Access, opisuju se olimpijske medalje.

1. Da biste preuzeli datoteke koje koristimo u ovoj seriji vodiča, kliknite veze u nastavku. Preuzmite svaku od četiri datoteke na mjesto koje je jednostavno dostupno, kao što su Preuzimanja ili Moji dokumenti ili u novu mapu koju stvorite:
   > baze podataka programa OlympicMedals.accdb Access
   > OlympicSports.xlsx excel
   > Population.xlsx excel
   > DiscImage_table.xlsx excel

2. U programu Excel otvorite praznu radnu knjigu.

3. Kliknite Podaci > dohvaćanje podataka > iz baze podataka > iz baze podataka programa Microsoft Access. Vrpca se dinamički prilagođava na temelju širine radne knjige, pa se naredbe na vrpci mogu neznatno razlikovati od sljedećeg zaslona.
   
   

4. Odaberite preuzetu datoteku OlympicMedals.accdb pa kliknite Uvezi. Prikazat će se sljedeći prozor navigatora s prikazanim tablicama koje se nalaze u bazi podataka. Tablice u bazi podataka slične su radnim listovima ili tablicama u programu Excel. Potvrdite okvir Odabir više tablica i odaberite sve tablice. Zatim kliknite Učitaj > Učitaj u.
   
   

5. Prikazat će se prozor Uvoz podataka.

   Napomena: Obratite pozornost na potvrdni okvir pri dnu prozora koji vam omogućuje dodavanje tih podataka u podatkovni model prikazan na sljedećem zaslonu. Podatkovni se model stvara automatski kada istovremeno uvozite ili radite s dvije ili više tablica. Podatkovni model integrira tablice, što omogućuje opsežnu analizu pomoću zaokretnih tablica, Power Pivot i dodatka Power View. Kada uvozite tablice iz baze podataka, postojeći odnosi baze podataka između tih tablica koriste se za stvaranje podatkovnog modela u programu Excel. Podatkovni je model transparentan u programu Excel, ali ga možete izravno pregledavati i mijenjati pomoću Power Pivot dodatka. Podatkovni model detaljnije je opisan u ovom vodiču.

   
   Odaberite mogućnost Izvješće zaokretne tablice koja uvozi tablice u Excel i priprema zaokretnu tablicu za analizu uvezenih tablica, a zatim kliknite U redu.
   
   

6. Nakon uvoza podataka pomoću uvezenih tablica stvorit će se zaokretna tablica.
   
   

Nakon uvoza podataka u Excel i automatskog stvaranja podatkovnog modela spremni ste za pregledavanje podataka.

Pregledavanje podataka pomoću zaokretne tablice

Uvezeni se podaci istražuju pomoću zaokretne tablice. U zaokretnoj tablici povlačite polja (slično stupcima programa Excel) iz tablica (poput tablica koje ste uvezli iz baze podataka programa Access) u razna područja zaokretne tablice radi prilagodbe načina na koji se prikazuju podaci. Zaokretna tablica ima četiri područja: FILTRI, STUPCI, RECIi VRIJEDNOSTI.

Da bi se odredilo u koje područje valja odvući polje, potrebno je malo eksperimentirati. Iz tablica možete vući koliko god želite polja sve dok se u zaokretnoj tablici podaci ne prikažu onako kako ih želite vidjeti. Slobodno istražujte povlačenje polja u razna područja zaokretne tablice – razmještaj polja u zaokretnoj tablici ne utječe na podatke na kojima se ona temelji.

Pogledajmo sada podatke o olimpijskim medaljama u zaokretnoj tablici počevši od osvajača olimpijskih medalja organiziranih po disciplini, vrsti metalje i državi ili regiji sportaša.

1. U odjeljku Polja zaokretne tablice proširite tablicu Medalje klikom na strelicu pokraj nje. U proširenoj tablici Medalje pronađite polje NOO_DržavaRegija pa ga povucite u područje STUPCI. NOO je kratica za Nacionalni olimpijski odbor, što je organizacijska jedinica države ili regije.

2. Zatim iz tablice Discipline povucite polje Disciplina u područje RECI.

3. Filtrirajmo Discipline tako da se među njima prikazuje samo pet sportova: streličarstvo, skokovi u vodu, mačevanje, umjetničko klizanje i brzo klizanje. To možete učiniti iz područja Polja zaokretne tablice ili iz filtra Oznake redaka u samoj zaokretnoj tablici.

   1. Kliknite bilo gdje u zaokretnoj tablici da biste bili sigurni da je odabrana zaokretna tablica programa Excel. Na popisu Polja zaokretne tablice, gdje je tablica Discipline proširena, postavite pokazivač miša iznad polja Disciplina, a s desne strane polja pojavit će se strelica padajućeg izbornika. Kliknite padajući izbornik, kliknite (Odaberi sve )da biste uklonili sve odabire, a zatim se pomaknite prema dolje i odaberite Streličarstvo, Ronjenje, Mačevanje, Klizanje na slici i Brzo klizanje. Kliknite U redu.

   2. Ili u odjeljku Oznake redaka zaokretne tablice kliknite padajući popis pokraj naslova Oznake redaka, kliknite (Odaberi sve) da biste uklonili sve odabire, a zatim se pomaknite dolje i odaberite Streličarstvo, Skokovi u vodu, Mačevanje, Umjetničko klizanje i Brzo klizanje. Kliknite U redu.

4. Na popisu Polja zaokretne tablice iz tablice Medalje povucite polje Medalja u područje VRIJEDNOSTI. Bdući da Vrijednosti moraju biti numeričke, Excel automatski mijenja Medalja u Broj medalja.

5. Iz tablice Medalje ponovno odaberite polje Medalja i povucite ga u područje FILTRI.

6. Filtrirajmo zaokretnu tablicu tako da se u njoj prikazuju samo države ili regije s ukupno više od 90 medalja. Evo kako.

   1. U zaokretnoj tablici kliknite padajući popis s desne strane odjeljka Oznake stupaca.

   2. Odaberite Filtri vrijednosti pa Veće od….

   3. U zadnje polje (s desne strane) upišite 90. Kliknite U redu.
      

Zaokretna tablica će tablica izgledati kao ona na sljedećem zaslonu.

Uz malo truda sada imate osnovnu zaokretnu tablicu koja sadrži polja iz triju različitih tablica. Taj je zadatak bio tako jednostavan zbog postojećih odnosa između tablica. Budući da su u izvornoj bazi podataka postojali odnosi između tablica i budući da ste sve tablice uvezli u sklopu jedne operacije, Excel je mogao ponovno stvoriti te odnose u podatkovnom modelu.

No što ako podaci potječu iz različitih izvora ili ako se uvezu kasnije? Obično možete stvoriti odnose s novim podacima na temelju podudarnih stupaca. U sljedećem koraju uvest ćete dodatne tablice i naučiti stvarati nove odnose.

Uvoz podataka iz baze proračunske tablice

Uvezimo sada podatke iz nekog drugog izvora, tj. iz postojeće radne knjige, a zatim odredimo odnose između postojećih i novih podataka. Odnosi vam omogućuju analizu zbirki podataka u programu Excel i stvaranje zanimljivih i dojmljivih vizualizacija podataka koje uvezete.

Najprije ćemo stvoriti prazan radni list, a zatim ćemo uvesti podatke iz neke radne knjige programa Excel.

1. Umetnite novi radni list programa Excel pa mu dajte naziv Sportovi.

2. Pomaknite se do mape koja sadrži preuzete ogledne datoteke s podacima i otvorite OlympicSports.xlsx.

3. Odaberite i kopirajte podatke na listu List1. Ako odaberete ćeliju s podacima, npr. ćeliju A1, možete pritisnuti Ctrl + A da biste odabrali sve susjedne podatke. Zatvorite radnu OlympicSports.xlsx knjigu.

4. Na radnom listu Sportovi postavite pokazivač u ćeliju A1 i zalijepite podatke.

5. Dok su podaci još označeni, pritisnite Ctrl + T da biste ih oblikovali kao tablicu. Podatke kao tablicu možete oblikovati i tako da na vrpci odaberete POLAZNO > Oblikuj kao tablicu. Budući da podaci imaju zaglavlja, odaberite postavku Moja tablica ima zaglavlja u prozoru Stvaranje tablice koji će se pojaviti, kao što je prikazano ovdje.
   
   
   
   Oblikovanje podataka u obliku tablice ima brojne prednosti. Tablici možete dodijeliti naziv, što omogućuje njezino jednostavnije prepoznavanje. Možete postaviti odnose između tablica, što omogućuje istraživanje i analizu u zaokretnim tablicama, dodatku Power Pivot i značajci Power View.

6. Tablici dodijelite naziv. U odjeljku TABLE DESIGN > Svojstva pronađite polje Naziv tablice i upišite Sportovi. Radna knjiga izgleda kao na sljedećem zaslonu.
   

7. Spremite radnu knjigu.

Uvoz podataka kopiranjem i lijepljenjem

Sad kad ste uvezli podatke iz radne knjige programa Excel, uvezimo podatke iz tablice koju pronađemo na nekoj web-stranici ili u bilo kojem drugom izvoru iz kojega možemo kopirati i lijepiti u Excel. U sljedećim koracima dodat ćemo gradove domaćina olimpijskih igara iz tablice.

1. Umetnite novi radni list programa Excel pa mu dajte naziv Domaćini.

2. Odaberite i kopirajte sljedeću tablicu, uključujući zaglavlja.

1. Na radnom listu Domaćini orograma Excel postavite pokazivač u ćeliju A1 i zalijepite podatke.

2. Oblikujte podatke kao tablicu. Kao što je već opisano u ovom vodiču, podatke kao tablicu možete oblikovati pritiskom na kombinaciju tipki Ctrl + T ili pomoću nardbe HOME > Oblikuj kao tablicu. Budući da podaci imaju zaglavlja, u prozoru Stvaranje tablice koji se pojavi odaberite Moja tablica ima zaglavlja.

3. Tablici dodijelite naziv. U odjeljku TABLE DESIGN > Svojstva pronađite polje Naziv tablice i upišite Glavna računala.

4. Odaberite stupac Izdanje pa ga na kartici POLAZNO oblikujte kao Broj s 0 decimalnih mjesta.

5. Spremite radnu knjigu. Radna knjiga izgleda kao na sljedećem zaslonu.

Sad kada imate radnu knjigu programa Excel s tablicama, možete stvarati odnose između njih. Stvaran odnosa između tablica omogućuje vam objedinjavanje podataka iz dviju tablica.

Stvaranje odnosa između uvezenih podataka

Odmah možete pomoći koristiti polja u zaokretnoj tablici iz uvezenih tablica. Ako Excel ne može odrediti kako ugraditi polje u zaokretnu tablicu, potrebno je uspostaviti odnos pomoću postojećeg podatkovnog modela. U sljedećim koracima naučit ćete stvarati odnos između podataka uvezenih iz različitih izvora.

1. Na listu1 pri vrhu poljazaokretne tablice kliknite Sve da bi vam se prikazao cijeli popis dostupnih tablica, kao što je prikazano na sljedećem zaslonu.
   

2. Pomaknite se niz popis da biste vidjeli nove tablice koje ste upravo dodali.

3. Proširite odjeljak Sportovi pa odaberite Sport da biste ga dodali u zaokretnu tablicu. Uočite da Excel traži da stvorite odnos, što se vidi i na sljedećem zaslonu.
   
    

   Ova se obavijest pojaljuje jer ste koristili polja iz tablice koja nije dio temeljnog podatkovnog modela. Jedan je od načina za dodavanje tablice u podatkovni model stvaranje odnosa s tablicom koja se već nalazi u podatkovnom modelu. Da biste stvorili taj odnos, jedna od tablica mora imati stupac s jedinstvenim, neponovljenim vrijednostima. U oglednim podacima tablic Disciplines uvezena iz baze podataka sadrži polje s kodovima sportova pod nazivom IDSporta. Ti su isti kodovi sportova prisutni kao polje u podacima programa Excel koje smo uvezli. Stvorimo sada odnos.

4. Klikom na STVARANJE... u istaknutom području Polja zaokretne tablice otvorite dijaloški okvir Stvaranje odnosa, kao što je prikazano na sljedećem zaslonu.
   
   

5. U tablici na padajućem popisu odaberite Tablica podatkovnog modela: Discipline.

6. U odjeljku Stupac (vanjski) odaberite IDSporta.

7. U povezanoj tablici odaberite Tablica podatkovnog modela: Sportovi.

8. U odjeljku Povezani stupac (primarni) odaberite IDSporta.

9. Kliknite U redu.

Zaokretna se tablica mijenja u skladu s novim odnosom. No ona još ne izgleda kako treba zbog redoslijeda polja u području RECI. Disciplina je potkategorija sporta, ali budući da smo je u području RECI postavili iznad kategorije sporta, ona nije valjano organizirana. Na sljedećem se zaslonu prikazuje taj neželjeni redoslijed.

1. U području RECI pomaknite Sport iznad stavke Disciplina. To je mnogo bolje i podaci u zaokretnoj tablici sada se prikazuju kako želimo, što se vidi i na sljedećem zaslonu.
   
   

Excel u pozadini stvara podatkovni model koji se može koristiti u cijeloj radnoj knjizi te u bilo kojoj zaokretnoj tablici i zaokretnom grafikonu, u značajci Power Pivot ili izvješću značajke Power View. Odnosi tablica osnova su podakotvnog modela i ono što određuje putove navigacije i izračuna.

U sljedećem vodiču proširite odnose podatkovnog modela pomoću programa Excel, Power Pivot i DAX, nadodat ćete ono što ste ovdje naučili i proći kroz proširivanje podatkovnog modela pomoću snažnog i vizualnog dodatka programa Excel pod nazivom Power Pivot. Naučit ćete i kako izračunati stupce u tablici te koristiti taj izračunati stupac da bi se u podatkovni model mogla dodati inače nepovezana tablica.

Kontrolna točka i test

Pregled naučenog

Sad imate radnu knjigu programa Excel sa zaokretnom tablicom koja pristupa podacima iz više tablica, od kojih ste nekoliko uvezli zasebno. Naučili ste uvoziti podatke iz baze podataka, neke druge radne knjige programa Excel i kopiranjem i lijepljenjem u Excel.

Da biste objedinili te podatke, morali ste stvoriti odnose između tablica koje Excel koristi za koreliranje redaka. Naučili ste i da je za stvaranje odnosa i pretraživanje povezanih stupaca nužno imati stupce u jednoj tablici koji koreliraju s podacima u drugoj tablici.

Sad ste spremni za sljedeći vodič u ovoj seriji. Evo veze:

Praktični vodič: Proširivanje odnosa u podatkovnom modelu pomoću programa Excel, dodatka Power Pivot i jezika DAX

TEST

Želite li provjeriti što ste od naučenoga zapamtili? Ovo je vaša prilika. U sljedećem testu istaknute su značajke, mogućnosti ili preduvjeti o kojima ste saznali više u ovom praktičnom vodiču. Odgovori se nalaze pri dnu stranice. Sretno!

Pitanje 1: Zašto je važno pretvoriti uvezene podatke u tablice?

A: Nije ih potrebno pretvoriti u tablice jer se svi uvezeni podaci automatski pretvaraju u tablice.

B: Ako uvezene podatke pretvorite u tablice, oni će biti izostavljeni iz podatkovnog modela. Samo ako su izostavljeni iz podatkovnog modela, dostupni su u zaokretnim tablicama, dodatku Power Pivot i značajci Power View.

C: Ako uvezene podatke pretvoerite u tablice, moguće ih je uvrstiti u podatkovni model i učiniti dostupnima za zaokretne tablice, Power Pivot i Power View.

D: Uvezeni se podaci ne mogu pretvoriti u tablice.

Pitanje 2: Iz kojeg je od sljedećih izvora moguće uvoziti podatke u Excel i uvrštavati ih podatkovni model?

A: Iz baza podataka programa Access i mnogih drugih baza podataka.

B: Iz postojećih datoteka programa Excel.

C: Iz svega iz čega se podaci mogu kopirati i zalijepiti u Excel te oblikovati u kao tablica, uključujući tablice na web-mjestima, u dokumentima i na svim drugim mjestima s kojih je moguće lijepiti u Excel.

D: Sve od navedenoga.

Pitanje 3: Što se u zaokretnoj tablici događa kada promijenite redoslijed polja u četiri područja polja zaokretne tablice?

A: Ništa – poljima postavljenim u područja polja zaokretne tablice ne možete promijeniti redoslijed.

B: U skladu s rasporedom mijenja se oblik zaokretne tablice, ali temeljni podaci ostaju isti.

C: U skladu s rasporedom mijenja se oblik zaokretne tablice, a trajno se mijenjaju i svi temeljni podaci.

D: Temeljni se podaci mijenjaju, a posljedica su novi skupovi podataka.

Pitanje 4: Što je potrebno za stvaranje odnosa između tablica?

A: Nijedna tablica ne smije imati nijedan stupac s jedinstvenim vrijednostima koje se ne ponavljaju.

B: Jedna tablica ne smije biti dio radne knjige programa Excel.

C: Stupci se ne smiju pretvoriti u tablice.

D: Ništa od navedenog nije točno.

Odgovori na pitanja u testu

1. Točan odgovor: C

2. Točan odgovor: D

3. Točan odgovor: B

4. Točan odgovor: D