Microsoft Query možete koristiti za dohvaćanje podataka iz vanjskih izvora. Ako za dohvaćanje podataka iz poslovnih baza podataka i datoteka koristite Microsoft Query, ne morate ponovno upisivati podatke koje želite analizirati u programu Excel. Možete i automatski osvježiti izvješća i sažetke programa Excel iz izvorne izvorne baze podataka kad god se baza podataka ažurira novim informacijama.
Saznajte više o programu Microsoft Query
Pomoću programa Microsoft Query možete se povezati s vanjskim izvorima podataka, odabrati podatke iz tih vanjskih izvora, uvesti ih na radni list i po potrebi osvježiti podatke da bi podaci radnog lista bili sinkronizirani s podacima u vanjskim izvorima.
Vrste baza podataka kojima možete pristupiti Podatke možete dohvatiti iz nekoliko vrsta baza podataka, uključujući Microsoft Office Access, Microsoft SQL Server i Microsoft SQL Server OLAP Services. Podatke možete dohvatiti i iz radnih knjiga programa Excel te tekstnih datoteka.
Microsoft Office nudi upravljačke programe pomoću kojih možete dohvatiti podatke iz sljedećih izvora podataka:
- Microsoft SQL Server Analysis Services (davatelj OLAP)
- Microsoft Office Access
- dBASE
- Microsoft FoxPro
- Microsoft Office Excel
- Oracle
- Paradoks
- baze podataka tekstnih datoteka
Možete koristiti i ODBC upravljačke programe ili upravljačke programe izvora podataka drugih proizvođača da biste dohvatili informacije iz izvora podataka koji nisu ovdje navedeni, uključujući druge vrste OLAP baza podataka. Informacije o instalaciji ODBC upravljačkog programa ili upravljačkog programa izvora podataka koji nisu ovdje navedeni potražite u pratećoj dokumentaciji baze podataka ili se obratite dobavljaču baze podataka.
Odabir podataka iz baze podataka Podatke iz baze podataka dohvaćate stvaranjem upita, a to je pitanje koje postavljate u vezi s podacima pohranjenima u vanjskoj bazi podataka. Ako su podaci, primjerice, pohranjeni u bazi podataka programa Access, možda ćete htjeti znati rezultate prodaje određenog proizvoda po regiji. Dio podataka možete dohvatiti tako da odaberete samo podatke za proizvod i regiju koje želite analizirati.
Microsoft Query omogućuje odabir željenih stupaca podataka i uvoz samo tih podataka u Excel.
Ažuriranje radnog lista jednom operacijom Kada su vanjski podaci u radnoj knjizi programa Excel, svaki put kada se promijeni baza podataka, možete ih osvježiti da biste ažurirali analizu – bez potrebe za ponovnim stvaranjem izvješća sažetka i grafikona. Možete, primjerice, stvoriti mjesečni sažetak prodaje i osvježavati ga svaki mjesec kada stignu novi podaci o prodaji.
Način na koji Microsoft Query koristi izvore podataka Kada postavite izvor podataka za određenu bazu podataka, možete ga koristiti kad god želite stvoriti upit za odabir i dohvaćanje podataka iz te baze podataka, a da pritom ne morate ponovno upisivati sve podatke o vezi. Microsoft Query koristi izvor podataka za povezivanje s vanjskom bazom podataka i prikaz dostupnih podataka. Kada stvorite upit i vratite podatke u Excel, Microsoft Query radnoj knjizi programa Excel daje podatke o upitu i izvoru podataka da biste se mogli ponovno povezati s bazom podataka kada želite osvježiti podatke.
Korištenje programa Microsoft Query za uvoz podataka radi uvoza vanjskih podataka u Excel pomoću dodatka Microsoft Query slijedite ove osnovne korake koji su detaljnije opisani u sljedećim odjeljcima.
Povezivanje s izvorom podataka
Što je izvor podataka? Izvor podataka pohranjeni je skup informacija koji programu Excel i programu Microsoft Query omogućuje povezivanje s vanjskom bazom podataka. Kada koristite Microsoft Query za postavljanje izvora podataka, dajete izvoru podataka naziv, a zatim navodite naziv i mjesto baze podataka ili poslužitelja, vrstu baze podataka te podatke za korisničko ime i lozinku. Informacije obuhvaćaju i naziv upravljačkog programa OBDC-a ili upravljačkog programa za izvor podataka, odnosno programa koji uspostavlja vezu s određenom vrstom baze podataka.
Postavljanje izvora podataka pomoću programa Microsoft Query:
Na kartici Podaci u grupi Dohvaćanje vanjskih podataka kliknite Iz drugih izvora, a zatim Iz programa Microsoft Query.
Napomena
Excel 365 premjestio je Microsoft Query u grupu izbornika naslijeđenih čarobnjaka . Taj se izbornik ne prikazuje po zadanom. Da biste to omogućili, idite na Datoteka, Mogućnosti, Podaci i omogućite u odjeljku Prikaz čarobnjaka za uvoz naslijeđenih podataka .
Učinite nešto od sljedećeg:
- Da biste naveli izvor podataka za bazu podataka, tekstnu datoteku ili radnu knjigu programa Excel, kliknite karticu Baze podataka .
- Da biste odredili izvor podataka OLAP kocke, kliknite karticu OLAP kocke . Ta je kartica dostupna samo ako ste Microsoft Query pokrenuli iz programa Excel.
Dvokliknite <Novi izvor> podataka.
– ili –
Kliknite <Novi izvor> podataka, a zatim U redu.
Prikazat će se dijaloški okvir Stvaranje novog izvora podataka .U prvom koraku upišite naziv da biste prepoznali izvor podataka.
U drugom koraku kliknite upravljački program za vrstu baze podataka koju koristite kao izvor podataka.
Napomena
- Ako ODBC upravljački programi instalirani pomoću programa Microsoft Query ne podržavaju vanjsku bazu podataka kojoj želite pristupiti, morate nabaviti i instalirati ODBC upravljački program kompatibilan sa sustavom Microsoft Office od drugog proizvođača, kao što je proizvođač baze podataka. Upute za instalaciju zatražite od dobavljača baze podataka.
- OLAP baze podataka ne zahtijevaju ODBC upravljačke programe. Kada instalirate Microsoft Query, instaliraju se upravljački programi za baze podataka koje su stvorene pomoću komponente Microsoft SQL Server Analysis Services. Da biste se povezali s drugim OLAP bazama podataka, morate instalirati upravljački program izvora podataka i klijentski softver.
Kliknite Poveži, a zatim navedite informacije potrebne za povezivanje s izvorom podataka. Za baze podataka, radne knjige programa Excel i tekstne datoteke informacije koje navodite ovise o vrsti izvora podataka koju ste odabrali. Možda ćete morati unijeti ime za prijavu, lozinku, verziju baze podataka koju koristite, mjesto baze podataka ili neke druge podatke vezane uz vrstu baze podataka.
Važno
- Koristite jaku lozinku u kojoj ćete kombinirati velika i mala slova, brojeve i simbole. U slabim se lozinkama ti elementi ne kombiniraju. Jaka lozinka: Y6dh!et5. Slaba lozinka: Miro27. Lozinka bi se trebala sastojati od 8 znakova ili više. Najbolje bi bilo koristiti pristupni izraz koji sadrži 14 ili više znakova.
- Najvažnije je da lozinku zapamtite. Ako je zaboravite, Microsoft vam je ne može vratiti. Lozinke koje zapisujete pohranite na zaštićeno mjesto dalje od informacija koje štite.
Kada unesete potrebne informacije, kliknite U redu ili Završi da biste se vratili u dijaloški okvir Stvaranje novog izvora podataka .
Ako baza podataka sadrži tablice i želite da se određena tablica automatski prikaže u čarobnjaku za upite, kliknite okvir za četvrti korak, a zatim željenu tablicu.
Ako ne želite upisivati ime za prijavu i lozinku kada koristite izvor podataka, potvrdite okvir Spremi moj korisnički ID i lozinku u definiciji izvora podataka . Spremljena lozinka nije šifrirana. Ako potvrdni okvir nije dostupan, obratite se administratoru baze podataka da biste saznali može li se ta mogućnost staviti.
Napomena
Izbjegavajte spremanje podataka za prijavu prilikom povezivanja s izvorima podataka. Ti se podaci mogu pohraniti u obliku običnog teksta pa im zlonamjerni korisnik može pristupiti i tako ugroziti sigurnost izvora podataka.
Nakon toga se u dijaloškom okviru Odabir izvora podataka prikazuje naziv izvora podataka.
Definiranje upita pomoću čarobnjaka za upite
Za većinu upita koristite čarobnjak za upite Čarobnjak za upite pojednostavnjuje odabir i prikupljanje podataka iz različitih tablica i polja u bazi podataka. Pomoću čarobnjaka za upite možete odabrati tablice i polja koja želite uvrstiti. Unutarnji spoj (operacija upita koja određuje da se reci iz dvije tablice spajaju na temelju identičnih vrijednosti polja) stvara se automatski kada čarobnjak prepozna polje primarnog ključa u jednoj tablici i polje istog naziva u drugoj tablici.
Čarobnjak možete koristiti i za sortiranje skupa rezultata te za jednostavno filtriranje. U posljednjem koraku čarobnjaka možete odabrati vraćanje podataka u Excel ili dodatno suziti upit u programu Microsoft Query. Kada stvorite upit, možete ga pokrenuti u programu Excel ili Microsoft Query.
Da biste pokrenuli čarobnjak za upite, poduzmite sljedeće korake.
- Na kartici Podaci u grupi Dohvaćanje vanjskih podataka kliknite Iz drugih izvora, a zatim Iz programa Microsoft Query.
- U dijaloškom okviru Odabir izvora podataka provjerite je li potvrđen okvir Koristi čarobnjak za upite za stvaranje/uređivanje upita .
- Dvokliknite izvor podataka koji želite koristiti.
– ili –
Kliknite izvor podataka koji želite koristiti, a zatim U redu.
Rad izravno u programu Microsoft Query za druge vrste upita Ako želite stvoriti složeniji upit nego što to omogućuje čarobnjak za upite, to možete učiniti izravno u programu Microsoft Query. Microsoft Query omogućuje prikaz i promjenu upita koje započnete sa stvaranjem u čarobnjaku za upite ili bez čarobnjaka. Kada želite stvoriti upite koji izvršavaju sljedeće:
- Odabir određenih podataka iz polja U velikoj bazi podataka možda ćete htjeti odabrati neke podatke u polju, a izostaviti podatke koji vam nisu potrebni. Na primjer, ako su vam potrebni podaci za dva proizvoda u polju koje sadrži podatke za više proizvoda, možete koristiti kriterije da biste odabrali podatke samo za dva proizvoda koja želite.
- Dohvaćanje podataka na temelju različitih kriterija pri svakom pokretanju upita Ako morate stvoriti isto izvješće programa Excel ili sažetak za nekoliko područja u istim vanjskim podacima – primjerice zasebno izvješće o prodaji za svaku regiju – možete stvoriti parametarski upit. Kada pokrenete parametarski upit, od vas se traži da unesete vrijednost koja će se koristiti kao kriterij prilikom odabira zapisa u upitu. Na primjer, parametarski upit od vas može zatražiti da unesete određenu regiju pa možete ponovno upotrijebiti taj upit da biste stvorili svako izvješće o regionalnoj prodaji.
- Različiti načini spajanja podataka Unutarnji spojevi koje stvara čarobnjak za upite najčešća su vrsta spoja koja se koristi pri stvaranju upita. No ponekad ćete htjeti koristiti drugu vrstu spoja. Ako, primjerice, imate tablicu s podacima o prodaji proizvoda i tablicu s podacima o kupcima, unutarnji spoj (vrsta koju stvara čarobnjak za upite) onemogućit će dohvaćanje zapisa kupaca koji nisu ništa kupili. Pomoću programa Microsoft Query te tablice možete spojiti da bi se dohvatili svi zapisi o klijentima, zajedno s podacima o prodaji onih klijenata koji su izvršili kupnju.
Da biste pokrenuli Microsoft Query, učinite sljedeće.
- Na kartici Podaci u grupi Dohvaćanje vanjskih podataka kliknite Iz drugih izvora, a zatim Iz programa Microsoft Query.
- U dijaloškom okviru Odabir izvora podataka provjerite je li poništen potvrdni okvir Koristi čarobnjak za upite za stvaranje/uređivanje upita .
- Dvokliknite izvor podataka koji želite koristiti.
– ili –
Kliknite izvor podataka koji želite koristiti, a zatim U redu.
Ponovno korištenje i zajedničko korištenje upita I u čarobnjaku za upite i u programu Microsoft Query upite možete spremiti kao .dqy datoteku koju možete mijenjati, ponovno koristiti i zajednički koristiti. Excel može izravno otvarati .dqy datoteke, što vama ili drugim korisnicima omogućuje stvaranje dodatnih raspona vanjskih podataka iz istog upita.
Otvaranje spremljenog upita iz programa Excel:
- Na kartici Podaci u grupi Dohvaćanje vanjskih podataka kliknite Iz drugih izvora, a zatim Iz programa Microsoft Query. Prikazat će se dijaloški okvir Odabir izvora podataka .
- U dijaloškom okviru Odabir izvora podataka kliknite karticu Upiti .
- Dvokliknite spremljeni upit koji želite otvoriti. Upit se prikazuje u programu Microsoft Query.
Ako želite otvoriti spremljeni upit, a Microsoft Query već je otvoren, kliknite izbornik Datoteka upita Microsoft Query, a zatim Otvori.
Ako dvokliknete .dqy datoteku, otvara se Excel, izvodi upit, a zatim umeće rezultate na novi radni list.
Ako želite zajednički koristiti sažetak ili izvješće programa Excel utemeljeno na vanjskim podacima, drugim korisnicima dajte radnu knjigu koja sadrži raspon vanjskih podataka ili stvorite predložak. Predložak omogućuje spremanje sažetka ili izvješća bez spremanja vanjskih podataka da bi datoteka bila manja. Vanjski se podaci dohvaćaju kada korisnik otvori predložak izvješća.
Rad s podacima u programu Excel
Kada stvorite upit u čarobnjaku za upite ili programu Microsoft Query, podatke možete vratiti na radni list programa Excel. Podaci zatim postaju raspon vanjskih podataka ili izvješće zaokretne tablice koje možete oblikovati i osvježavati.
Oblikovanje dohvaćenih podataka U programu Excel možete koristiti alate, kao što su grafikoni ili automatski podzbrojevi, za prikaz i sažimanje podataka koje je dohvatio Microsoft Query. Podatke možete oblikovati, a oblikovanje se zadržava prilikom osvježavanja vanjskih podataka. Možete koristiti vlastite oznake stupaca umjesto naziva polja i automatski dodati brojeve redaka.
Excel može automatski oblikovati nove podatke koje upišete na kraju raspona tako da odgovaraju prethodnim recima. Excel može i automatski kopirati formule koje su ponovljene u prethodnim recima te ih proširiti na dodatne retke.
Napomena
Da bi se proširili na nove retke raspona, oblici i formule moraju se nalaziti u najmanje tri od pet prethodnih redaka.
Tu mogućnost možete uključiti (ili ponovno isključiti) u bilo kojem trenutku:
- KlikniteDodatne mogućnosti>datoteke>.
- U odjeljku Mogućnosti uređivanja odaberite provjeru Proširi raspon podataka Oblici i formule . Da biste ponovno isključili automatsko oblikovanje raspona podataka, poništite taj potvrdni okvir.
Osvježavanje vanjskih podataka Prilikom osvježavanja vanjskih podataka pokrećete upit da biste dohvatili sve nove ili promijenjene podatke koji odgovaraju vašim specifikacijama. Upit možete osvježiti i u programu Microsoft Query i u programu Excel. Excel nudi nekoliko mogućnosti osvježavanja upita, uključujući osvježavanje podataka prilikom svakog otvaranja radne knjige i njihovo automatsko osvježavanje u određenim intervalima. Tijekom osvježavanja podataka možete nastaviti raditi u programu Excel, kao i provjeriti status. Dodatne informacije potražite u članku Osvježavanje vanjske podatkovne veze u programu Excel.