Microsoft Query kasutamine välisandmete allalaadimiseks

Microsoft Query abil saate luua ühenduse väliste andmeallikatega, valida nendest välistest allikatest andmeid, importida need andmed oma töölehele ja värskendada andmeid vastavalt vajadusele, et töölehe andmed oleksid väliste allikate andmetega sünkroonitud.

Andmebaasitüübid, millele pääsete juurde     Andmeid saate tuua mitut tüüpi andmebaasidest, sh Microsoft Office Accessist, Microsoft SQL Serverist ja Microsoft SQL Serveri OLAP-i teenustest. Andmeid saate tuua ka Exceli töövihikutest ja tekstifailidest.

Microsoft Office pakub draivereid, mille abil saate tuua andmeid järgmistest andmeallikatest.

• Microsoft SQL Serveri analüüsiteenused (OLAP-pakkuja )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradoks

• Tekstifailide andmebaasid

Siin loetlemata andmeallikatest (sh muud tüüpi OLAP-andmebaasidest) teabe toomiseks saate kasutada ka teiste tootjate ODBC-draivereid või andmeallikadraivereid. Lisateavet sellise ODBC-draiveri või andmeallika draiveri installimise kohta, mida siin loendis pole, vaadake andmebaasi dokumentatsiooni või pöörduge oma andmebaasi tarnija poole.

Andmete valimine andmebaasist     Andmete toomiseks andmebaasist looge päring, mis on küsimus, mida küsite välises andmebaasis talletatud andmete kohta. Näiteks kui teie andmed on talletatud Accessi andmebaasis, soovite võib-olla teada kindla toote müüginäitajaid piirkondade kaupa. Saate tuua osa andmetest, valides ainult analüüsitava toote ja regiooni andmed.

Microsoft Query abil saate valida soovitud andmeveerud ja importida ainult need andmed Excelisse.

Töölehe värskendamine ühe toiminguga     Kui exceli töövihikus on välisandmed, saate andmebaasi igal muutmisel analüüsi värskendamiseks andmeid värskendamine ilma kokkuvõttearuandeid ja diagramme uuesti loomata. Näiteks saate luua igakuise müügikokkuvõtte ja seda uute müüginäitajate ilmumisel iga kuu värskendada.

Kuidas Microsoft Query andmeallikaid kasutab?     Pärast kindla andmebaasi andmeallika häälestamist saate seda kasutada iga kord, kui soovite luua päringu andmete valimiseks ja toomiseks sellest andmebaasist , ilma et peaksite kogu ühenduseteavet uuesti tippima. Microsoft Query kasutab andmeallikat välise andmebaasiga ühenduse loomiseks ja teile saadaolevate andmete kuvamiseks. Pärast päringu loomist ja andmete Excelisse tagastamist annab Microsoft Query Exceli töövihikule nii päringu kui ka andmeallika teabe, et saaksite andmete värskendamisel andmebaasiga uuesti ühenduse luua.

Andmete importimine Microsoft Query abil     välisandmete Importimiseks Excelisse Microsoft Query abil järgige neid põhitoiminguid, millest igaüks on üksikasjalikumalt kirjeldatud järgmistes jaotistes.

Mis on andmeallikas?     Andmeallikas on salvestatud teabekogum, mis võimaldab Excelil ja Microsoft Queryl luua ühenduse välise andmebaasiga. Kui kasutate andmeallika häälestamiseks Microsoft Queryt, pange andmeallikale nimi ja seejärel sisestage andmebaasi või serveri nimi ja asukoht, andmebaasi tüüp ning sisselogimis- ja parooliteave. Teave sisaldab ka OBDC-draiveri või andmeallika draiveri nime, mis on programm, mis loob ühenduse teatud tüüpi andmebaasiga.

Andmeallika häälestamiseks Microsoft Query abil tehke järgmist.

1. Klõpsake menüü Andmed jaotises Välisandmete toomine nuppu Muudest allikatest ja seejärel käsku Microsofti päringust.

   Märkus.: Excel 365 on microsoft Query teisaldanud menüürühma Pärandviisardid.  Seda menüüd vaikimisi ei kuvata.  Lubamiseks avage jaotis Fail, Suvandid, Andmed ja lubage jaotises Andmete importimise pärandviisardite kuvamine .

2. Tehke ühte järgmistest.

   • Andmebaasi, tekstifaili või Exceli töövihiku andmeallika määramiseks klõpsake vahekaarti Andmebaasid .

   • OLAP-kuubi andmeallika määramiseks klõpsake vahekaarti OLAP-kuubid . See vahekaart on saadaval ainult siis, kui käivitasite Microsoft Query Exceli kaudu.

3. Topeltklõpsake nuppu <Uus andmeallikas>.

   -või-

   Klõpsake nuppu<Uus andmeallikas>ja seejärel nuppu OK.

   Kuvatakse dialoogiboks Uue andmeallika loomine .

4. Tippige 1. juhises andmeallika tuvastamiseks nimi.

5. 2. juhises klõpsake andmeallikana kasutatava andmebaasi tüübi draiverit.

   Märkused: 

   • Kui Microsoft Queryga installitud ODBC-draiverid ei toeta välist andmebaasi, millele soovite juurde pääseda, peate hankima ja installima Microsoft Office'iga ühilduva ODBC-draiveri kolmanda osapoole tarnijalt (nt andmebaasi tootjalt). Installimisjuhiste saamiseks pöörduge andmebaasi tarnija poole.

   • OLAP-andmebaasid ei vaja ODBC-draivereid. Microsoft Query installimisel installitakse Microsoft SQL Serveri analüüsiteenuste abil loodud andmebaaside draiverid. Muude OLAP-andmebaasidega ühenduse loomiseks peate installima andmeallika draiveri ja klienttarkvara.

6. Klõpsake nuppu Ühenda ja sisestage andmeallikaga ühenduse loomiseks vajalik teave. Andmebaaside, Exceli töövihikute ja tekstifailide puhul sõltub edastatav teave valitud andmeallika tüübist. Teil võidakse paluda sisestada sisselogimisnimi, parool, kasutatava andmebaasi versioon, andmebaasi asukoht või muu andmebaasitüübiga seotud teave.

   NB!: 

   • Kasutage keerukaid paroole, mis sisaldavad nii suur- kui väiketähti, numbreid ja sümboleid. Nõrkades paroolides ei kasutata neid elemente koos. Keerukas parool: Y6dh!et5. Nõrk parool: Maja27. Parooli pikkus peaks olema kaheksa või enam märki. Pääsufraas, kus kasutatakse 14 või enamat märki, on parem lahendus.

   • Oma parooli meelespidamine on väga oluline. Parooli unustamise korral ei saa Microsoft seda teie jaoks taastada. Hoidke üleskirjutatud paroole kindlas kohas ja eemal teabest, mida kaitsta soovite.

7. Pärast nõutava teabe sisestamist klõpsake dialoogiboksi Uue andmeallika loomine naasmiseks nuppu OK või Valmis.

8. Kui teie andmebaasis on tabeleid ja soovite, et konkreetne tabel kuvataks päringuviisardis automaatselt, klõpsake 4. juhise välja ja seejärel klõpsake soovitud tabelit.

9. Kui te ei soovi andmeallika kasutamisel oma sisselogimisnime ja parooli tippida, märkige ruut Salvesta kasutaja ID ja parool andmeallika määratluses . Salvestatud parool pole krüptitud. Kui märkeruut pole saadaval, küsige andmebaasi administraatorilt, kas seda suvandit saab kättesaadavaks teha.

   Turbeteade.: Andmeallikatega ühendust luues vältige sisselogimisteabe salvestamist. Seda teavet võidakse talletada lihttekstina ja mõni pahatahtlik kasutaja võib teabele ligi pääsedes andmeallika turvalisuse ohtu seada.

Pärast nende juhiste täitmist kuvatakse andmeallika nimi dialoogiboksis Andmeallika valimine .

Enamiku päringute jaoks päringuviisardi kasutamine     Päringuviisard hõlbustab andmebaasi eri tabelite ja väljade andmete valimist ja koondamist. Päringuviisardi abil saate valida kaasatavad tabelid ja väljad. Sisemine ühendamine (päringutoiming, mis määrab, et kahe tabeli read kombineeritakse identsete väljaväärtuste põhjal) luuakse automaatselt, kui viisard tuvastab ühe tabeli primaarvõtmevälja ja teises tabelis sama nimega välja.

Samuti saate viisardi abil tulemikomplekti sortida ja lihtsat filtreerimist teha. Viisardi viimases etapis saate valida andmete Excelisse tagastamise või päringu täpsema piiritlemise Microsoft Querys. Pärast päringu loomist saate selle käivitada Excelis või Microsoft Querys.

Päringuviisardi käivitamiseks tehke järgmist.

1. Klõpsake menüü Andmed jaotises Välisandmete toomine nuppu Muudest allikatest ja seejärel käsku Microsofti päringust.

2. Veenduge, et dialoogiboksis Andmeallika valimine oleks märgitud ruut Kasuta päringute loomiseks/redigeerimiseks päringuviisardit .

3. Topeltklõpsake andmeallikat, mida soovite kasutada.

   -või-

   Klõpsake andmeallikat, mida soovite kasutada, ja seejärel klõpsake nuppu OK.

Muud tüüpi päringute puhul otse Microsoft Querys töötamine     Kui soovite luua päringuviisardiga võrreldes keerukama päringu, saate töötada otse Microsoft Querys. Microsoft Query abil saate vaadata ja muuta päringuviisardis loodavaid päringuid või luua uusi päringuid viisardit kasutamata. Kui soovite luua päringuid, mis teevad järgmist, saate töötada otse Microsoft Querys.

• Kindlate andmete valimine väljalt     Suures andmebaasis võite soovida valida osa väljal olevatest andmetest ja jätta välja andmed, mida te ei vaja. Näiteks kui vajate andmeid kahe toote kohta väljal, mis sisaldab teavet paljude toodete kohta, saate kriteeriumid abil valida andmed ainult kahe soovitud toote kohta.

• Eri kriteeriumidel põhinevate andmete toomine iga kord, kui päringu käivitate     Kui teil on vaja luua sama Exceli aruanne või kokkuvõte mitme ala jaoks samas välisandmetes (nt eraldi müügiaruanne iga piirkonna kohta), saate luua parameeterpäring. Parameetripäringu käivitamisel palutakse teil kirjete valimisel kasutada kriteeriumina väärtust. Näiteks võib parameetripäring paluda teil sisestada kindla piirkonna ja saate seda päringut iga piirkondliku müügiaruande loomiseks uuesti kasutada.

• Andmete ühendamine mitmel viisil     Päringuviisardi loodavad sisemised ühendamised on kõige levinumad päringute loomisel kasutatavad ühendamistüübid. Mõnikord aga soovite kasutada mõnda muud tüüpi ühendamist. Näiteks kui teil on toote müügiteabe tabel ja klienditeabe tabel, takistab sisemine ühendamine (päringuviisardi loodud tüüp) nende klientide jaoks, kes pole ostnud, kliendikirjete toomist. Microsoft Query abil saate need tabelid ühendada nii, et kõik kliendikirjed (koos nende klientide müügiandmetega, kes on ostnud).

Microsoft Query käivitamiseks tehke järgmist.

1. Klõpsake menüü Andmed jaotises Välisandmete toomine nuppu Muudest allikatest ja seejärel käsku Microsofti päringust.

2. Veenduge, et dialoogiboksis Andmeallika valimine oleks ruut Kasuta päringute loomiseks/redigeerimiseks päringuviisardit tühi.

3. Topeltklõpsake andmeallikat, mida soovite kasutada.

   -või-

   Klõpsake andmeallikat, mida soovite kasutada, ja seejärel klõpsake nuppu OK.

Päringute korduvkasutamine ja ühiskasutus     Nii päringuviisardis kui ka Microsoft Querys saate salvestada oma päringud DQY-failina, mida saate muuta, uuesti kasutada ja ühiskasutusse anda. Excel saab DQY-faile otse avada, mis võimaldab teil või teistel kasutajatel luua sama päringu põhjal täiendavaid välisandmevahemikke.

Salvestatud päringu avamiseks Excelis tehke järgmist.

1. Klõpsake menüü Andmed jaotises Välisandmete toomine nuppu Muudest allikatest ja seejärel käsku Microsofti päringust. Kuvatakse dialoogiboks Andmeallika valimine .

2. Klõpsake dialoogiboksis Andmeallika valimine vahekaarti Päringud .

3. Topeltklõpsake salvestatud päringut, mille soovite avada. Päring kuvatakse Microsoft Querys.

Kui soovite avada salvestatud päringu ja Microsoft Query on juba avatud, klõpsake menüüd Microsoft Query fail ja seejärel nuppu Ava.

Kui topeltklõpsate DQY-faili, avatakse Excel, käivitatakse päring ja seejärel lisatakse tulemid uuele töölehele.

Kui soovite jagada välisandmetel põhinevat Exceli kokkuvõtet või aruannet, saate anda teistele kasutajatele välisandmevahemikku sisaldava töövihiku või luua mall. Mall võimaldab salvestada kokkuvõtte või aruande välisandmeid salvestamata, et fail oleks väiksem. Välisandmed tuuakse siis, kui kasutaja avab aruandemalli.

Pärast päringu loomist päringuviisardis või Microsoft Querys saate andmed Tagastada Exceli töölehele. Seejärel muutuvad andmed välisandmevahemik või PivotTable-liigendtabeli aruanne, mida saate vormindada ja värskendada.

Allalaaditud andmete vormindamine     Excelis saate Microsoft Query toodavate andmete esitamiseks ja summeerimiseks kasutada tööriistu (nt diagramme või automaatseid vahekokkuvõtteid). Saate andmeid vormindada ja teie vorming säilitatakse välisandmete värskendamisel. Väljanimede asemel saate kasutada oma veerusilte ja lisada reanumbrid automaatselt.

Excel saab automaatselt vormindada vahemiku lõppu tipitud uusi andmeid, et need vastaksid eelnevatele ridadele. Excel saab ka automaatselt kopeerida valemid, mida on korratud eelnevates ridades, ja laiendada need täiendavatele ridadele.

Saate selle suvandi igal ajal (või uuesti välja lülitada) järgmiselt.

1. Valige Fail > Suvandid > Täpsemalt.

2. Märkige jaotises Redigeerimissuvandid ruut Laienda andmevahemiku vorminguid ja valemeid . Automaatse andmevahemiku vormingu uuesti väljalülitamiseks tühjendage see ruut.

Välisandmete värskendamine.     Välisandmete värskendamisel käivitate päringu, et tuua teie spetsifikatsioonidele vastavad uued või muudetud andmed. Päringut saate värskendada nii Microsoft Querys kui ka Excelis. Excelis on päringute värskendamiseks mitu võimalust, sh andmete värskendamine iga kord, kui avate töövihiku ja värskendate neid automaatselt kindla intervalliga. Andmete värskendamise ajal saate Excelis tööd jätkata ja andmete värskendamise ajal olekut kontrollida. Lisateavet leiate teemast Välise andmeühenduse värskendamine Excelis.

Lehe algusse