Praktični vodič: uvoz podataka u Excel i stvaranje podatkovnog modela
Applies ToExcel za Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Sažetak:    Ovo je prvi u nizu vodiča pomoću kojih ćete se uputiti u rad s programom Excel i njegovim ugrađenim značajkama objedinjavanja i analize podataka. U tim se vodičima izrađuju i dotjeruju radne knjige programa Excel, sastavljaju podatkovni modeli i stvaraju čudesna interaktivna izvješća pomoću značajke Power View. Vodiči su osmišljeni kao demonstracija Microsoftovih značajki i funkcija za poslovno obavještavanje u programu Excel, zaokretnim tablicama, dodatku, Power Pivoti značajci Power View.

Pomoću tih ćete vodiča naučiti uvoziti i istraživati podatke u programu Excel, sastavljati i dotjerivati podatkovni model pomoću dodatka Power Pivot te stvarati interaktivna izvješća pomoću značajke Power View koje možete objavljivati, štiti i zajednički koristiti.

Vodiči u ovoj seriji su:

  1. Uvoz podataka u Excel 2016 i stvaranje podatkovnog modela

  2. Proširivanje odnosa podatkovnog modela pomoću programa Excel, dodatka Power Pivot i DAX

  3. Stvaranje izvješća značajke Power View utemeljenih na karti

  4. Uključivanje internetskih podataka i postavljanje zadanih vrijednosti izvješća značajke Power View

  5. Pomoć za Power Pivot

  6. Stvaranje nevjerojatnih izvješća značajke Power View – drugi dio

U ovom vodiču počet ćete od prazne radne knjige programa Excel.

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

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 vanjskih podataka > Iz programa Access. Vrpca se dinamično prilagođuje širini radne knjige, pa izgled naredbi na njoj može malo odstupati od sljedećih snimki zaslona. Na prvom zaslonu prikazuje se vrpca kada je radna knjiga široka, a na drugoj je slici radna knjiga kojoj je veličina promijenjena tako da zauzima samo dio zaslona.Uvoz podataka iz programa AccessUvoz podataka iz programa Access s malom vrpcom  

  4. Odaberite preuzetu datoteku OlympicMedals.accdb pa kliknite Otvori. Otvorit će se sljedeći prozor Odabir tablice u kojem se prikazuju tablice pronađene u bazi podataka. Tablice u bazi podataka slične su radnim listovima ili tablicama u programu Excel. Potvrdite okvir Omogući odabir više tablica pa odaberite sve tablice. Zatim kliknite U redu.Prozor Odabir tablice

  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.Prozor Uvoz podataka

  6. Nakon uvoza podataka pomoću uvezenih tablica stvorit će se zaokretna tablica.Prazna 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.

Četiri područja polja zaokretne tablice

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.Prozor Filtar vrijednosti

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

Ažurirana zaokretna tablica

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.Prozor Stvaranje tablice 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 ALATI ZA TABLICE > DIZAJN > Svojstva pronađite polje Naziv tablice pa upišite Sportovi. Radna knjiga izgleda kao na sljedećem zaslonu.Imenujte tablicu u programu Excel

  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.

Grad

NOO_DržavaRegija

Alfa-2 kod

Izdanje

Sezona

Melbourne / Stockholm

AUS

AS

1956

Ljeto

Sydney

AUS

AS

2000

Ljeto

Innsbruck

AUT

AT

1964

Zima

Innsbruck

AUT

AT

1976

Zima

Antwerpen

BEL

BE

1920

Ljeto

Antwerpen

BEL

BE

1920

Zima

Montreal

CAN

CA

1976

Ljeto

Lake Placid

CAN

CA

1980

Zima

Calgary

CAN

CA

1988

Zima

St. Moritz

SUI

SZ

1928

Zima

St. Moritz

SUI

SZ

1948

Zima

Peking

CHN

CH

2008

Ljeto

Berlin

GER

GM

1936

Ljeto

Garmisch-Partenkirchen

GER

GM

1936

Zima

Barcelona

ESP

SP

1992

Ljeto

Helsinki

FIN

FI

1952

Ljeto

Pariz

FRA

FR

1900

Ljeto

Pariz

FRA

FR

1924

Ljeto

Chamonix

FRA

FR

1924

Zima

Grenoble

FRA

FR

1968

Zima

Albertville

FRA

FR

1992

Zima

London

GBR

Velika Britanija

1908

Ljeto

London

GBR

Velika Britanija

1908

Zima

London

GBR

Velika Britanija

1948

Ljeto

München

GER

DE

1972

Ljeto

Atena

GRC

GR

2004

Ljeto

Cortina d'Ampezzo

ITA

IT

1956

Zima

Rim

ITA

IT

1960

Ljeto

Torino

ITA

IT

2006.

Zima

Tokio

JPN

JA

1964

Ljeto

Saporo

JPN

JA

1972

Zima

Nagano

JPN

JA

1998

Zima

Seul

KOR

KS

1988

Ljeto

Ciudad de Mexico

MEX

MX

1968

Ljeto

Amsterdam

NED

NL

1928

Ljeto

Oslo

NOR

NO

1952

Zima

Lillehammer

NOR

NO

1994

Zima

Stockholm

SWE

SW

1912

Ljeto

St Louis

USA

US

1904

Ljeto

Los Angeles

USA

US

1932

Ljeto

Lake Placid

USA

US

1932

Zima

Squaw Valley

USA

US

1960

Zima

Moskva

URS

RU

1980

Ljeto

Los Angeles

USA

US

1984

Ljeto

Atlanta

USA

US

1996

Ljeto

Salt Lake City

USA

US

2002

Zima

Sarajevo

YUG

YU

1984

Zima

  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 ALATI ZA TABLICE > DIZAJN > Svojstva pronađite polje Naziv tablice pa upišite Domaćini.

  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.

Matična tablica

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.Kliknite Sve na popisu Polja zaokretne tablice da bi vam se prikazale sve dostupne tablice

  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.Upit o stvaranju odnosa na popisu Polja zaokretne tablice  

    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.Prozor Stvaranje odnosa

  5. U odjeljku Tablica s padajućeg popisa odaberite Discipline.

  6. U odjeljku Stupac (vanjski) odaberite IDSporta.

  7. U odjeljku Povezana tablica odaberite 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.Zaokretna tablica s neželjenim redoslijedom

  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.Zaokretna tablica s ispravljenim redoslijedom

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

Napomene: Podaci i slike u nizu praktičnih vodiča temelje se na sljedećim podacima:

  • Olympics Dataset © Guardian News & Media Ltd.

  • slike zastava dobivene su ljubaznošću web-mjesta CIA Factbook (cia.gov)

  • podaci o broju stanovnika dobiveni su s web-mjesta Svjetske banke (worldbank.org)

  • piktogrami olimpijskih sportova koje su izradili Thadius856 i Parutakupiu

Potrebna vam je dodatna pomoć?

Želite dodatne mogućnosti?

Istražite pogodnosti pretplate, pregledajte tečajeve za obuku, saznajte kako zaštititi uređaj i još mnogo toga.

Zajednice vam pomažu da postavljate pitanja i odgovarate na njih, pošaljete povratne informacije i čujete se sa stručnjacima s bogatim znanjem.