Uporaba programa Microsoft Query za pridobivanje zunanjih podatkov

Z aplikacijo Microsoft Query lahko pridobite podatke iz zunanjih virov. Če uporabite Microsoft Query za pridobivanje podatkov iz zbirk podatkov in datotek podjetja, vam ni treba znova vnašati podatkov, ki jih želite analizirati v Excelu. Excelova poročila in povzetke lahko tudi samodejno osvežite iz izvirne izvorne zbirke podatkov, ko je zbirka podatkov posodobljena z novimi informacijami.

Več informacij o aplikaciji Microsoft Query

Z aplikacijo Microsoft Query lahko vzpostavite povezavo z zunanjimi viri podatkov, izberete podatke iz teh zunanjih virov, uvozite te podatke na delovni list in po potrebi osvežite podatke, da so podatki delovnega lista sinhronizirani s podatki v zunanjih virih.

Vrste zbirk podatkov, do katerih lahko dostopate Pridobite lahko podatke iz več vrst zbirk podatkov, vključno z Microsoft Office Access, Microsoft SQL Server in Microsoft SQL Server OLAP Services. Podatke lahko pridobite tudi iz Excelovih delovnih zvezkov in besedilnih datotek.

Microsoft Office ponuja gonilnike, ki jih lahko uporabite za pridobivanje podatkov iz teh virov podatkov:

• Microsoft SQL Server Analysis Services (ponudnik OLAP)
• Microsoft Office Access
• dBASE
• Microsoft FoxPro
• Microsoft Office Excel
• Oracle
• Paradoks
• Zbirke podatkov besedilnih datotek

Uporabite lahko tudi gonilnike ODBC ali gonilnike virov podatkov drugih proizvajalcev, da pridobite informacije iz virov podatkov, ki niso navedeni tukaj, vključno z drugimi vrstami zbirk podatkov OLAP. Če želite več informacij o namestitvi gonilnika ODBC ali gonilnika vira podatkov, ki ni naveden tukaj, preberite dokumentacijo zbirke podatkov ali se obrnite na dobavitelja zbirke podatkov.

Izbiranje podatkov iz zbirke podatkov Podatke iz zbirke podatkov pridobite tako, da ustvarite poizvedbo, ki jo postavite o podatkih, shranjenih v zunanji zbirki podatkov. Če so na primer vaši podatki shranjeni v Accessovi zbirki podatkov, boste morda želeli vedeti podatke o prodaji za določen izdelek po regijah. Del podatkov lahko pridobite tako, da izberete le podatke za izdelek in regijo, ki jo želite analizirati.

Z aplikacijo Microsoft Query lahko izberete želene stolpce podatkov in uvozite le te podatke v Excel.

Posodabljanje delovnega lista z enim postopkom Ko imate zunanje podatke v Excelovem delovnem zvezku, lahko vsakič, ko se zbirka podatkov spremeni, osvežite podatke in posodobite analizo, ne da bi morali znova ustvariti poročila s povzetkom in grafikone. Ustvarite lahko na primer mesečni povzetek prodaje in ga osvežite vsak mesec, ko pridejo nove številke prodaje.

Kako Microsoft Query uporablja vire podatkov Ko nastavite vir podatkov za določeno zbirko podatkov, ga lahko uporabite, kadar želite ustvariti poizvedbo za izbiro in pridobivanje podatkov iz te zbirke podatkov, ne da bi morali znova vnesti vse informacije o povezavi. Microsoft Query uporablja vir podatkov za povezavo z zunanjo zbirko podatkov in za prikaz podatkov, ki so na voljo. Ko ustvarite poizvedbo in vrnete podatke v Excel, Microsoft Query Excelovemu delovnemu zvezku zagotovi informacije o poizvedbi in viru podatkov, tako da lahko znova vzpostavite povezavo z zbirko podatkov, ko želite osvežiti podatke.

Če uporabljate Microsoft Query za uvoz podatkov za uvoz zunanjih podatkov v Excel z Microsoft Query, sledite tem osnovnim korakom, ki so podrobneje opisani v spodnjih razdelkih.

Vzpostavitev povezave z virom podatkov

Kaj je vir podatkov?  Vir podatkov je shranjen nabor informacij, ki Excelu in Microsoft Queryju omogoča, da vzpostavita povezavo z zunanjo zbirko podatkov. Ko uporabite Microsoft Query za nastavitev vira podatkov, mu dodelite ime in nato vnesete ime in mesto zbirke podatkov ali strežnika, vrsto zbirke podatkov ter podatke za prijavo in geslo. Informacije vključujejo tudi ime gonilnika OBDC ali gonilnika vira podatkov, ki je program, ki vzpostavi povezave z določeno vrsto zbirke podatkov.

Če želite nastaviti vir podatkov z Microsoft Query:

1. Na zavihku Podatki v skupini Pridobi zunanje podatke kliknite Iz drugih virov in nato Iz Microsoft Queryja.

   Opomba

   Excel 365 je Microsoft Query premaknil v skupino menijev »Podedovani čarovniki «.  Ta meni privzeto ni prikazan.  Če želite omogočiti, pojdite na Datoteka, Možnosti, Podatki in omogočite v razdelku Pokaži podedovane čarovnike za uvoz podatkov .

2. Naredite nekaj od tega:

   • Če želite določiti vir podatkov za zbirko podatkov, besedilno datoteko ali Excelov delovni zvezek, kliknite zavihek Zbirke podatkov .
   • Če želite določiti vir podatkov kocke OLAP, kliknite zavihek Kocke OLAP . Ta zavihek je na voljo le, če ste Microsoft Query zagnali iz Excela.

3. Dvokliknite <Nov vir> podatkov.
   -ali-
   Kliknite <Nov vir> podatkov in nato V redu.
   Prikaže se pogovorno okno Ustvari nov vir podatkov .

4. V 1. koraku vnesite ime, da prepoznate vir podatkov.

5. V 2. koraku kliknite gonilnik za vrsto zbirke podatkov, ki jo uporabljate kot vir podatkov.

   Opomba

   • Če gonilniki ODBC, ki so nameščeni z aplikacijo Microsoft Query, ne podpirajo zunanje zbirke podatkov, do katere želite dostopati, morate gonilnik ODBC, združljiv z Microsoft Officeom, pridobiti in namestiti pri neodvisnem ponudniku, kot je izdelovalec zbirke podatkov. Za navodila za namestitev se obrnite na prodajalca zbirke podatkov.
   • Podatkovne zbirke OLAP ne zahtevajo gonilnikov ODBC. Ko namestite Microsoft Query, so nameščeni gonilniki za zbirke podatkov, ki so bile ustvarjene s storitvijo Microsoft SQL Server Analysis Services. Če želite vzpostaviti povezavo z drugimi zbirkami podatkov OLAP, morate namestiti gonilnik vira podatkov in odjemalsko programsko opremo.

6. Kliknite Vzpostavi povezavo in nato vnesite informacije, ki so potrebne za vzpostavitev povezave z virom podatkov. Za zbirke podatkov, Excelove delovne zvezke in besedilne datoteke so informacije, ki jih vnesete, odvisne od vrste izbranega vira podatkov. Morda boste morali vnesti uporabniško ime, geslo, različico zbirke podatkov, ki jo uporabljate, lokacijo zbirke podatkov ali druge informacije, ki so značilne za vrsto zbirke podatkov.

   Pomembno

   • Uporabljajte zapletena gesla, ki so sestavljena iz velikih in malih črk, številk in simbolov. Šibka gesla niso sestavljena iz teh elementov. Primer zapletenega gesla: Y6dh!et5. Primer šibkega gesla: Hiša27. Gesla naj bodo sestavljena iz 8 ali več znakov. Geslo s 14 ali več znaki je še boljše.
   • Geslo si morate obvezno zapomniti. Če ga pozabite, ga Microsoft ne more pridobiti. Gesla, ki si jih zabeležite, shranite na varno mesto, ki je ločeno od informacij, ki so zaščitene z geslom.

7. Ko vnesete zahtevane podatke, kliknite V redu ali Dokončaj , da se vrnete v pogovorno okno Ustvari nov vir podatkov .

8. Če so v zbirki podatkov tabele in želite, da se določena tabela samodejno prikaže v čarovniku za poizvedbe, kliknite polje za 4. korak in nato želeno tabelo.

9. Če pri uporabi vira podatkov ne želite vnesti uporabniškega imena in gesla, potrdite polje Shrani moj ID uporabnika in geslo v definicijo vira podatkov . Shranjeno geslo ni šifrirano. Če potrditveno polje ni na voljo, se obrnite na skrbnika zbirke podatkov in ugotovite, ali je ta možnost na voljo.

   Opomba

   Izognite se shranjevanju podatkov, ki vzpostavljate povezavo z viri podatkov. Ti podatki se lahko shranijo kot navadno besedilo, do katerega lahko dostopa zlonameren uporabnik in ogrozi varnost vira podatkov.

Ko dokončate te korake, se ime vira podatkov prikaže v pogovornem oknu Izberite vir podatkov .

Uporaba čarovnika za poizvedbe za določanje poizvedbe

Uporaba čarovnika za poizvedbe za večino poizvedb Čarovnik za poizvedbe omogoča preprosto izbiro in združevanje podatkov iz različnih tabel in polj v zbirki podatkov. S čarovnikom za poizvedbe lahko izberete tabele in polja, ki jih želite vključiti. Notranji spoj (postopek poizvedbe, ki določa, da so vrstice iz dveh tabel združene na podlagi enakih vrednosti polj) se ustvari samodejno, ko čarovnik prepozna polje primarnega ključa v eni tabeli in polje z istim imenom v drugi tabeli.

S čarovnikom lahko razvrstite nabor rezultatov in preprosto filtrirate. V zadnjem koraku čarovnika lahko podatke vrnete v Excel ali dodatno natančneje določite poizvedbo v programu Microsoft Query. Ko ustvarite poizvedbo, jo lahko zaženete v Excelu ali Microsoft Queryju.

Če želite zagnati čarovnika za poizvedbe, izvedite te korake.

1. Na zavihku Podatki v skupini Pridobi zunanje podatke kliknite Iz drugih virov in nato Iz Microsoft Queryja.
2. V pogovornem oknu Izberite vir podatkov preverite, ali je potrjeno potrditveno polje Uporabi čarovnika za poizvedbe za ustvarjanje/urejanje poizvedb.
3. Dvokliknite vir podatkov, ki ga želite uporabiti.
   -ali-
   Kliknite vir podatkov, ki ga želite uporabiti, in nato V redu.

Neposredno delo v programu Microsoft Query za druge vrste poizvedb Če želite ustvariti bolj zapleteno poizvedbo, kot jo dovoljuje čarovnik za poizvedbe, lahko delate neposredno v programu Microsoft Query. Uporabite lahko Microsoft Query za ogled in spreminjanje poizvedb, ki jih začnete ustvarjati v čarovniku za poizvedbe, ali pa ustvarite nove poizvedbe brez uporabe čarovnika. Delajte neposredno v programu Microsoft Query, če želite ustvariti poizvedbe, ki naredijo to:

• Izbiranje določenih podatkov iz polja V veliki zbirki podatkov boste morda želeli izbrati nekatere podatke v polju in izpustiti podatke, ki jih ne potrebujete. Če na primer potrebujete podatke za dva izdelka v polju, ki vsebuje informacije za številne izdelke, lahko uporabite pogoje za izbiro podatkov le za dva želena izdelka.
• Pridobivanje podatkov na podlagi različnih pogojev vsakič, ko zaženete poizvedbo Če želite ustvariti isto Excelovo poročilo ali povzetek za več območij v istih zunanjih podatkih – na primer ločeno poročilo o prodaji za vsako regijo – lahko ustvarite poizvedbo s parametri. Ko zaženete poizvedbo s parametri, ste pozvani k vnosu vrednosti, ki bo uporabljena kot merilo, ko poizvedba izbere zapise. Poizvedba s parametri vas lahko na primer pozove, da vnesete določeno regijo, to poizvedbo pa lahko znova uporabite za ustvarjanje posameznih poročil o prodaji regij.
• Združevanje podatkov na različne načine Notranja združevanja, ki jih ustvari čarovnik za poizvedbe, so najpogostejša vrsta združevanja, ki se uporablja pri ustvarjanju poizvedb. Včasih pa boste želeli uporabiti drugačno vrsto združevanja. Če imate na primer tabelo s podatki o prodaji izdelka in tabelo s podatki o strankah, notranje združevanje (vrsta, ki jo ustvari čarovnik za poizvedbe) prepreči pridobivanje zapisov strank za stranke, ki niso opravile nakupa. Z dodatkom Microsoft Query lahko združite te tabele, tako da se pridobijo vsi zapisi strank skupaj s podatki o prodaji tistih strank, ki so opravile nakup.

Če želite zagnati Microsoft Query, izvedite te korake.

1. Na zavihku » Podatki « v skupini »Pridobi zunanje podatke « kliknite »Iz drugih virov« in nato » Iz programa Microsoft Query«.
2. V pogovornem oknu »Izbira vira podatkov « se prepričajte, da potrditveno polje »Ustvari/uredi poizvedbe« s čarovnikom za poizvedbe ni potrjeno .
3. Dvokliknite vir podatkov, ki ga želite uporabiti.
   -ali-
   Kliknite vir podatkov, ki ga želite uporabiti, in nato kliknite »V redu«.

Vnovična uporaba in skupna raba poizvedb Tako v čarovniku za poizvedbe kot tudi v programu Microsoft Query lahko shranite poizvedbe kot datoteko .dqy, ki jo lahko spreminjate, znova uporabite in dajete v skupno rabo. V Excelu lahko neposredno odprete datoteke .dqy, kar vam in drugim uporabnikom omogoča, da iz iste poizvedbe ustvarite dodatne obsege zunanjih podatkov.

Če želite odpreti shranjeno poizvedbo v Excelu:

1. Na zavihku » Podatki « v skupini »Pridobi zunanje podatke « kliknite »Iz drugih virov« in nato » Iz programa Microsoft Query«. Odpre se pogovorno okno »Izbira vira podatkov «.
2. V pogovornem oknu » Izbira vira podatkov « kliknite zavihek »Poizvedbe «.
3. Dvokliknite shranjeno poizvedbo, ki jo želite odpreti. Poizvedba se prikaže v programu Microsoft Query.

Če želite odpreti shranjeno poizvedbo in je Microsoft Query že odprt, kliknite meni » Datoteka poizvedbe« Microsoft in nato Odpri.

Če dvokliknete datoteko .dqy, Excel odpre, zažene poizvedbo in vstavi rezultate na nov delovni list.

Če želite dati v skupno rabo Excelov povzetek ali poročilo, ki temelji na zunanjih podatkih, lahko drugim uporabnikom podelite delovni zvezek z zunanjim obsegom podatkov ali pa ustvarite predlogo. S predlogo lahko shranite povzetek ali poročilo, ne da bi shranili zunanje podatke, tako da je datoteka manjša. Zunanji podatki so pridobljeni, ko uporabnik odpre predlogo poročila.

Delo s podatki v Excelu

Ko ustvarite poizvedbo v čarovniku za poizvedbe ali v programu Microsoft Query, lahko podatke vrnete v Excelov delovni list. Podatki nato postanejo zunanji obseg podatkov ali poročilo vrtilne tabele, ki ga lahko oblikujete in osvežite.

Oblikovanje pridobljenih podatkov V Excelu lahko z orodji, kot so grafikoni ali samodejne delne vsote, predstavite in povzamete podatke, ki jih pridobi Microsoft Query. Podatke lahko oblikujete, oblikovanje pa se ohrani, ko osvežite zunanje podatke. Namesto imen polj lahko uporabite lastne oznake stolpcev in samodejno dodate številke vrstic.

Excel lahko samodejno oblikuje nove podatke, ki jih vnesete na konec obsega, tako da se ujemajo s prejšnjimi vrsticami. Excel lahko tudi samodejno kopira formule, ki so se ponovile v prejšnjih vrsticah, in jih razširi v dodatne vrstice.

To možnost lahko kadar koli vklopite (ali znova izklopite) tako:

1. Kliknite»Dodatne možnosti>datoteke>«.
2. V razdelku » Možnosti urejanja « potrdite polje »Razširi oblike obsega podatkov in formule «. Če želite samodejno oblikovanje obsega podatkov znova izklopiti, počistite to potrditveno polje.

Osveževanje zunanjih podatkov Ko osvežite zunanje podatke, zaženete poizvedbo, da pridobite morebitne nove ali spremenjene podatke, ki se ujemajo z vašimi specifikacijami. Poizvedbo lahko osvežite v programu Microsoft Query in Excelu. Excel ponuja več možnosti za osveževanje poizvedb, vključno z osveževanjem podatkov vsakič, ko odprete delovni zvezek, in samodejnim osveževanjem v časovnih intervalih. Med osveževanjem podatkov lahko še naprej delate v Excelu, med osveževanjem podatkov pa lahko preverite tudi stanje. Če želite več informacij, glejte »Osveževanje zunanje podatkovne povezave v Excelu«.

Na vrh strani