Išorinių duomenų gavimas naudojant Microsoft Query

Naudodami "Microsoft Query" galite prisijungti prie išorinių duomenų šaltinių, pasirinkti tų išorinių šaltinių duomenis, importuoti juos į darbalapį ir atnaujinti duomenis, jei reikia, kad jūsų darbalapio duomenys būtų sinchronizuojami su išorinių šaltinių duomenimis.

Duomenų bazių, kurias galite pasiekti, tipai     Galite gauti duomenis iš kelių tipų duomenų bazių, įskaitant "Microsoft Office Access", "Microsoft SQL Server" ir "Microsoft SQL Server OLAP Services". Taip pat galite gauti duomenis iš "Excel" darbaknygių ir tekstinių failų.

"Microsoft Office" teikia tvarkykles, kurias galite naudoti norėdami gauti duomenis iš šių duomenų šaltinių:

• "Microsoft SQL Server" analizės tarnybos (OLAP teikėjas )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradoksas

• Tekstinių failų duomenų bazės

Taip pat galite naudoti ODBC tvarkykles arba duomenų šaltinio tvarkykles iš kitų gamintojų, kad gautumėte informaciją iš duomenų šaltinių, kurie čia nenurodyti, įskaitant kitų tipų OLAP duomenų bazes. Informacijos apie ODBC tvarkyklės arba duomenų šaltinio tvarkyklės diegimą, kurios čia nėra, ieškokite duomenų bazės dokumentacijoje arba kreipkitės į duomenų bazės tiekėją.

Selecting data from a database     Duomenis iš duomenų bazės gaunate sukurdami užklausą, kuri yra klausimas, kurį klausiate apie išorinėje duomenų bazėje saugomus duomenis. Pavyzdžiui, jei jūsų duomenys saugomi "Access" duomenų bazėje, galbūt norėsite sužinoti konkretaus produkto pardavimo skaičius pagal regioną. Dalį duomenų galite gauti pasirinkdami tik duomenis produktui ir regionui, kuriuos norite analizuoti.

Naudodami "Microsoft Query" galite pasirinkti norimus duomenų stulpelius ir importuoti tik tuos duomenis į "Excel".

Darbalapio naujinimas vienos operacijos metu     Kai turite išorinių duomenų "Excel" darbaknygėje, pasikeitus duomenų bazei, galite naujinti duomenis, kad atnaujintumėte analizę, iš naujo nekurdami suvestinės ataskaitų ir diagramų. Pavyzdžiui, galite sukurti mėnesio pardavimo suvestinę ir atnaujinti ją kiekvieną mėnesį, kai pateikiami nauji pardavimo skaičiai.

Kaip Microsoft Query naudoja duomenų šaltinius     Nustatę tam tikros duomenų bazės duomenų šaltinį, galite jį naudoti kiekvieną kartą, kai norite sukurti užklausą, kad pasirinktumėte ir nuskaitytumėte duomenis iš tos duomenų bazės, iš naujo neįvesdami visos ryšio informacijos. Microsoft Query naudoja duomenų šaltinį, kad prisijungtų prie išorinės duomenų bazės ir parodytų, kokie duomenys yra pasiekiami. Kai sukuriate užklausą ir grąžinate duomenis į "Excel", "Microsoft Query" pateikia "Excel" darbaknygei tiek užklausos, tiek duomenų šaltinio informaciją, kad galėtumėte iš naujo prisijungti prie duomenų bazės, kai norėsite atnaujinti duomenis.

Microsoft Query naudojimas duomenims importuoti     norėdami importuoti išorinius duomenis į "Excel" su "Microsoft Query", atlikite šiuos pagrindinius veiksmus, kurie išsamiau aprašyti tolesniuose skyriuose.

Kas yra duomenų šaltinis?     Duomenų šaltinis yra saugomas informacijos rinkinys, leidžiantis Excel ir Microsoft Query prisijungti prie išorinės duomenų bazės. Kai naudojate Microsoft Query duomenų šaltiniui nustatyti, suteikiate duomenų šaltiniui pavadinimą, tada nurodote duomenų bazės arba serverio vardą ir vietą, duomenų bazės tipą ir prisijungimo bei slaptažodžio informaciją. Ši informacija taip pat apima OBDC tvarkyklės arba duomenų šaltinio tvarkyklės, kuri yra programa, kuri jungiasi prie konkretaus tipo duomenų bazės, pavadinimą.

Norėdami nustatyti duomenų šaltinį naudodami Microsoft Query:

1. Skirtuko Duomenys grupėje Gauti išorinius duomenis spustelėkite Iš kitų šaltinių, tada spustelėkite Iš "Microsoft Query".

   Pastaba: "Excel 365" perkėlė "Microsoft Query" į meniu grupę Senstelėję vedikliai.  Šis meniu pagal numatytuosius nustatymus nerodomas.  Norėdami įjungti, eikite į Dalį Failas, Parinktys, Duomenys ir įgalinkite sekcijoje Rodyti senstelėjusius duomenų importavimo vediklius .

2. Atlikite vieną iš šių veiksmų:

   • Norėdami nurodyti duomenų bazės, teksto failo arba "Excel" darbaknygės duomenų šaltinį, spustelėkite skirtuką Duomenų bazės .

   • Norėdami nurodyti OLAP kubo duomenų šaltinį, spustelėkite skirtuką OLAP kubai . Šis skirtukas galimas tik tada, jei "Excel" paleidote "Microsoft Query".

3. Dukart spustelėkite <Naujo duomenų šaltinio>.

   –arba–

   Spustelėkite <Naujas duomenų šaltinis>, tada spustelėkite Gerai.

   Rodomas dialogo langas Naujo duomenų šaltinio kūrimas .

4. Atlikdami 1 veiksmą įveskite duomenų šaltinį identifikuojantį pavadinimą.

5. Atlikdami 2 veiksmą spustelėkite duomenų bazės, kurią naudojate kaip duomenų šaltinį, tvarkyklę.

   Pastabos: 

   • Jei ODBC tvarkyklės, įdiegtos su Microsoft Query, nepalaiko išorinės duomenų bazės, kurią norite pasiekti, tada iš trečiosios šalies tiekėjo, pvz., duomenų bazės gamintojo, turite gauti ir įdiegti su Microsoft Office suderinamą ODBC tvarkyklę. Dėl diegimo instrukcijų kreipkitės į duomenų bazės tiekėją.

   • OLAP duomenų bazėms odbc tvarkyklių nereikia. Diegiant Microsoft Query įdiegiamos duomenų bazių, kurios buvo sukurtos naudojant Microsoft SQL serverio analizės tarnybas, tvarkyklės. Norėdami prisijungti prie kitų OLAP duomenų bazių, turite įdiegti duomenų šaltinio tvarkyklę ir kliento programinę įrangą.

6. Spustelėkite Jungtis, tada pateikite informaciją, reikalingą norint prisijungti prie duomenų šaltinio. Duomenų bazių, "Excel" darbaknygių ir teksto failų informacija, kurią pateikiate, priklauso nuo pasirinkto duomenų šaltinio tipo. Jūsų gali paprašyti pateikti prisijungimo vardą, slaptažodį, naudojamos duomenų bazės versiją, duomenų bazės vietą ar kitą informaciją, susijusią su duomenų bazės tipu.

   Svarbu: 

   • Naudokite sudėtingus slaptažodžius, sudarytus iš didžiųjų ir mažųjų raidžių, skaičių ir simbolių. Lengvuose slaptažodžiuose šie elementai nėra derinami. Sudėtingas slaptažodis: Y6dh!et5. Lengvas slaptažodis: Namas27. Slaptažodžiai turi būti sudaryti iš 8 ar daugiau simbolių. Geriau naudoti prieigos slaptažodį, kuriame yra 14 ar daugiau simbolių.

   • Labai svarbu nepamiršti savo slaptažodžio. Jeigu pamiršote slaptažodį, „Microsoft“ negalės jo atkurti. Užrašytus slaptažodžius saugokite saugioje vietoje, atskirai nuo informacijos, kurią jie turi apsaugoti.

7. Įvedę reikiamą informaciją, spustelėkite Gerai arba Baigti , kad grįžtumėte į dialogo langą Kurti naują duomenų šaltinį .

8. Jei jūsų duomenų bazėje yra lentelių ir norite, kad konkreti lentelė būtų automatiškai rodoma užklausų vediklyje, spustelėkite 4 veiksmo lauką, tada spustelėkite norimą lentelę.

9. Jei nenorite įvesti prisijungimo vardo ir slaptažodžio naudodami duomenų šaltinį, pažymėkite žymės langelį Įrašyti mano vartotojo ID ir slaptažodį duomenų šaltinio apibrėžime . Įrašytas slaptažodis neužšifruotas. Jei žymės langelio nėra, kreipkitės į duomenų bazės administratorių, kad sužinotumėte, ar ši parinktis gali būti pasiekiama.

   Saugos pranešimas: Jungiantis prie duomenų šaltinių stenkitės neįrašyti prisijungimo informacijos. Ši informacija gali būti saugoma kaip paprastasis tekstas ir kenkėjiškas vartotojas gali pasiekti informaciją, kad pakenktų duomenų šaltinio saugai.

Atlikus šiuos veiksmus, duomenų šaltinio pavadinimas rodomas dialogo lange Duomenų šaltinio pasirinkimas .

Užklausų vediklio naudojimas daugumai užklausų     Užklausų vediklis leidžia lengvai pasirinkti ir sujungti duomenis iš skirtingų lentelių ir laukų jūsų duomenų bazėje. Naudodami užklausų vediklį, galite pasirinkti lenteles ir laukus, kuriuos norite įtraukti. Vidinis sujungimas (užklausos operacija, kuri nurodo, kad eilutės iš dviejų lentelių sujungiamos remiantis vienodomis laukų reikšmėmis) sukuriama automatiškai, kai vediklis atpažįsta pirminio rakto lauką vienoje lentelėje, o laukas su tokiu pačiu pavadinimu antroje lentelėje.

Norėdami rikiuoti rezultatų rinkinį ir atlikti paprastą filtravimą, taip pat galite naudoti vediklį. Paskutiniame vediklio veiksme galite pasirinkti grąžinti duomenis į "Excel" arba dar labiau patikslinti užklausą programoje "Microsoft Query". Sukūrę užklausą, galite ją vykdyti programoje "Excel" arba "Microsoft Query".

Norėdami paleisti užklausų vediklį, atlikite šiuos veiksmus.

1. Skirtuko Duomenys grupėje Gauti išorinius duomenis spustelėkite Iš kitų šaltinių, tada spustelėkite Iš "Microsoft Query".

2. Įsitikinkite, kad dialogo lange Duomenų šaltinio pasirinkimas pažymėtas žymės langelis Užklausų vediklio naudojimas užklausoms kurti / redaguoti .

3. Dukart spustelėkite norimą naudoti duomenų šaltinį.

   –arba–

   Spustelėkite norimą naudoti duomenų šaltinį, tada spustelėkite Gerai.

Tiesiogiai dirbkite su "Microsoft Query" kitų tipų užklausoms     Jei norite sukurti sudėtingesnę užklausą, nei leidžia užklausų vediklis, galite dirbti tiesiogiai naudodami Microsoft Query. Naudodami Microsoft Query galite peržiūrėti ir keisti užklausas, kurias pradedate kurti naudodami užklausų vediklį, arba galite kurti naujas užklausas nenaudodami vediklio. Dirbkite tiesiogiai su "Microsoft Query", kai norite kurti užklausas, kurios atlieka šiuos veiksmus:

• Select specific data from a field     Didelėje duomenų bazėje galite pasirinkti kai kuriuos lauko duomenis ir praleisti duomenis, kurių jums nereikia. Pavyzdžiui, jei jums reikia dviejų produktų duomenų lauke, kuriame yra informacijos apie daugelį produktų, galite naudoti kriterijai, kad pasirinktumėte tik dviejų norimų produktų duomenis.

• Duomenų gavimas pagal skirtingus kriterijus kiekvieną kartą vykdant užklausą     Jei jums reikia sukurti tą pačią "Excel" ataskaitą arba suvestinę kelioms sritims tuose pačiuose išoriniuose duomenyse, pvz., atskiroje kiekvieno regiono pardavimo ataskaitoje, galite sukurti parametrų užklausa. Vykdant parametro užklausą, rodomas raginimas, kad reikšmė būtų naudojama kaip kriterijus, kai užklausa pasirenka įrašus. Pvz., parametro užklausa gali paraginti įvesti konkretų regioną ir galite pakartotinai naudoti šią užklausą, kad sukurtumėte kiekvieną regiono pardavimo ataskaitą.

• Duomenų jungimas skirtingais būdais     Vidiniai sujungimai, kuriuos sukuria užklausų vediklis, yra dažniausias sujungimo tipas, naudojamas kuriant užklausas. Kartais vis dėlto norite naudoti kito tipo sujungimą. Pavyzdžiui, jei turite lentelę su produkto pardavimo informacija ir kliento informacijos lentelę, vidinis sujungimas (užklausų vediklio sukurtas tipas) neleis gauti klientų įrašų klientams, kurie nepirko. Naudodami "Microsoft Query", galite sujungti šias lenteles, kad būtų nuskaityti visi klientų įrašai ir tų klientų, kurie atliko pirkimus, pardavimo duomenys.

Norėdami paleisti "Microsoft Query", atlikite toliau nurodytus veiksmus.

1. Skirtuko Duomenys grupėje Gauti išorinius duomenis spustelėkite Iš kitų šaltinių, tada spustelėkite Iš "Microsoft Query".

2. Įsitikinkite, kad dialogo lange Duomenų šaltinio pasirinkimas išvalytas žymės langelis Užklausų vediklio naudojimas užklausoms kurti / redaguoti .

3. Dukart spustelėkite norimą naudoti duomenų šaltinį.

   –arba–

   Spustelėkite norimą naudoti duomenų šaltinį, tada spustelėkite Gerai.

Užklausų pakartotinis naudojimas ir bendrinimas     Tiek užklausų vediklyje, tiek "Microsoft Query" galite įrašyti užklausas kaip .dqy failą, kurį galite modifikuoti, pakartotinai naudoti ir bendrinti. "Excel" gali atidaryti .dqy failus tiesiogiai, o tai leidžia jums ar kitiems vartotojams sukurti papildomus išorinių duomenų diapazonus iš tos pačios užklausos.

Norėdami atidaryti įrašytą užklausą iš "Excel":

1. Skirtuko Duomenys grupėje Gauti išorinius duomenis spustelėkite Iš kitų šaltinių, tada spustelėkite Iš "Microsoft Query". Rodomas dialogo langas Duomenų šaltinio pasirinkimas.

2. Dialogo lange Duomenų šaltinio pasirinkimas spustelėkite skirtuką Užklausos .

3. Dukart spustelėkite įrašytą užklausą, kurią norite atidaryti. Užklausa rodoma Microsoft Query.

Jei norite atidaryti įrašytą užklausą ir Microsoft Query jau atidaryta, spustelėkite meniu Microsoft Query failas , tada spustelėkite Atidaryti.

Jei dukart spustelėsite .dqy failą, programa "Excel" atsidarys, vykdo užklausą ir tada įterps rezultatus į naują darbalapį.

Jei norite bendrinti "Excel" suvestinę arba ataskaitą, pagrįstą išoriniais duomenimis, galite kitiems vartotojams suteikti darbaknygę, kurioje yra išorinių duomenų diapazonas, arba galite sukurti šablonas. Šablonas leidžia įrašyti suvestinę arba ataskaitą neįrašant išorinių duomenų, kad failas būtų mažesnis. Išoriniai duomenys nuskaitomi, kai vartotojas atidaro ataskaitos šabloną.

Sukūrę užklausą užklausų vediklyje arba "Microsoft Query", galite grąžinti duomenis į "Excel" darbalapį. Tada duomenys tampa išorinių duomenų diapazonas arba „PivotTable“ ataskaita, kuriuos galite formatuoti ir atnaujinti.

Nuskaitytų duomenų formatavimas     Programoje "Excel" galite naudoti įrankius, pvz., diagramas arba automatines tarpines sumas, norėdami pateikti ir apibendrinti duomenis, nuskaitytus naudojant Microsoft Query. Galite formatuoti duomenis, o formatavimas išsaugomas, kai atnaujinate išorinius duomenis. Vietoje laukų pavadinimų galite naudoti savo stulpelių žymas ir automatiškai įtraukti eilučių numerius.

"Excel" gali automatiškai formatuoti naujus duomenis, kuriuos įvedate diapazono pabaigoje, kad atitiktų ankstesnes eilutes. "Excel" taip pat gali automatiškai kopijuoti formules, kurios buvo pakartotos ankstesnėse eilutėse, ir išplėsti jas iki papildomų eilučių.

Šią parinktį galite bet kada įjungti (arba išjungti dar kartą):

1. Spustelėkite Failas> Parinktys > Išplėstinės.

2. Sekcijoje Redagavimo parinktys pažymėkite žymės langelį Išplėsti duomenų diapazono formatus ir formules . Norėdami vėl išjungti automatinį duomenų diapazono formatavimą, išvalykite šį žymės langelį.

Išorinių duomenų atnaujinimas     Kai atnaujinate išorinius duomenis, paleidžiate užklausą, kad gautumėte naujus arba pakeistus duomenis, atitinkančius jūsų specifikacijas. Galite atnaujinti užklausą ir programoje "Microsoft Query", ir "Excel". "Excel" pateikia kelias parinktis užklausoms atnaujinti, įskaitant duomenų atnaujinimą kiekvieną kartą atidarius darbaknygę ir automatiškai atnaujinant ją tam tikrais intervalais. Galite toliau dirbti programoje "Excel", kol atnaujinami duomenys, taip pat galite patikrinti būseną, kol atnaujinami duomenys. Daugiau informacijos žr. Išorinių duomenų ryšio atnaujinimas programoje "Excel".

Puslapio viršus