Išoriniams duomenims gauti naudojant "„Microsoft“ Query"

Norėdami gauti duomenis iš išorinių šaltinių, galite naudoti "„Microsoft“ Query". Naudojant "„Microsoft“ Query" duomenims iš įmonės duomenų bazių ir failų nuskaityti, jums nereikia iš naujo įvesti duomenų, kuriuos norite analizuoti programoje "Excel". Taip pat galite automatiškai atnaujinti "Excel" ataskaitas ir suvestines iš pradinio šaltinio duomenų bazės, kai duomenų bazė atnaujinama nauja informacija.

Sužinokite daugiau apie "„Microsoft“ Query"

Naudodami "„Microsoft“ Query", galite prisijungti prie išorinių duomenų šaltinių, pasirinkti duomenis iš tų išorinių šaltinių, importuoti juos į darbalapį ir prireikus atnaujinti duomenis, kad darbalapio duomenys būtų sinchronizuoti su išorinių šaltinių duomenimis.

Duomenų bazių tipai, kuriuos galite pasiekti 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 iš teksto failų.

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

• "Microsoft" SQL serverio 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ų šaltinių tvarkykles iš kitų gamintojų, kad gautumėte informaciją iš čia neišvardytų duomenų šaltinių, įskaitant kitų tipų OLAP duomenų bazes. Informacijos apie ODBC tvarkyklės ar duomenų šaltinio tvarkyklės, kuri čia nenurodyta, diegimą ieškokite duomenų bazės dokumentacijoje arba kreipkitės į duomenų bazės tiekėją.

Duomenų pasirinkimas iš duomenų bazės Duomenys iš duomenų bazės gaunami sukuriant užklausą, kuri yra klausimas, kurį užduodate 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ą. Galite gauti dalį duomenų pasirinkdami tik tuos produkto ir regiono, kuriuos norite analizuoti, duomenis.

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

Darbalapio naujinimas viena operacija Kai "Excel" darbaknygėje turėsite išorinių duomenų, kiekvieną kartą pasikeitus duomenų bazei galite atnaujinti duomenis, kad atnaujintumėte analizę. Jums nereikės iš naujo kurti suvestinių ataskaitų ir diagramų. Pavyzdžiui, galite sukurti mėnesio pardavimo suvestinę ir atnaujinti ją kiekvieną mėnesį, kai gaunate naujus pardavimo duomenis.

Kaip "„Microsoft“ Query" naudoja duomenų šaltinius Nustatę konkrečios duomenų bazės duomenų šaltinį, galite jį naudoti kaskart, 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 pasiekiami. Sukūrus užklausą ir grąžinus duomenis į "Excel", "„Microsoft“ Query" pateikia "Excel" darbaknygei užklausos ir duomenų šaltinio informaciją, kad galėtumėte iš naujo prisijungti prie duomenų bazės, kai norėsite atnaujinti duomenis.

Naudodami "„Microsoft“ Query" importuoti duomenis Norėdami importuoti išorinius duomenis į programą "Excel" su "„Microsoft“ Query", atlikite šiuos pagrindinius veiksmus, kiekvienas iš jų išsamiau aprašytas tolesniuose skyriuose.

Prisijungimas prie duomenų šaltinio

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 pavadinimą ir vietą, duomenų bazės tipą ir prisijungimo bei slaptažodžio informaciją. Informacija taip pat apima OBDC tvarkyklės arba duomenų šaltinio tvarkyklės, kuri yra programa, kuri užmezga ryšius su tam tikro tipo duomenų baze, pavadinimą.

Duomenų šaltinio nustatymas naudojant "„Microsoft“ Query":

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

   Pastaba

   Programa "Excel 365" perkėlė "„Microsoft“ Query" į meniu grupę Senstelėję vedikliai .  Pagal numatytuosius parametrus šis meniu nerodomas.  Norėdami įjungti, eikite į Failas, Parinktys, Duomenys ir įgalinkite skyriuje Rodyti senstelėjusius duomenų importavimo vedlius .

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 paleidus "„Microsoft“ Query" iš "Excel".

3. Dukart spustelėkite <Naujas duomenų šaltinis>.
   –arba–
   Spustelėkite <Naujas duomenų šaltinis, tada spustelėkite Gerai>.
   Rodomas dialogo langas Kurti naują duomenų šaltinį .

4. Atlikdami 1 veiksmą įveskite pavadinimą, kuris identifikuotų duomenų šaltinį.

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

   Pastaba

   • Jei išorinės duomenų bazės, kurią norite pasiekti, nepalaiko ODBC tvarkyklės, įdiegtos su "„Microsoft“ Query", turite įsigyti ir įdiegti su "Microsoft Office" suderinamą ODBC tvarkyklę iš trečiosios šalies tiekėjo, pvz., duomenų bazės gamintojo. Dėl diegimo instrukcijų kreipkitės į duomenų bazės tiekėją.
   • OLAP duomenų bazėms nereikia ODBC tvarkyklių. Įdiegus "„Microsoft“ Query", įdiegiamos tvarkyklės duomenų bazėms, sukurtoms naudojant "„Microsoft“" SQL serverio analizės tarnybos. Norėdami prisijungti prie kitų OLAP duomenų bazių, turite įdiegti duomenų šaltinio tvarkyklę ir kliento programinę įrangą.

6. Spustelėkite Prisijungti, tada pateikite informaciją, reikalingą prisijungti prie duomenų šaltinio. Duomenų bazių, "Excel" darbaknygių ir teksto failų atveju pateikta informacija 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ą arba kitą konkrečiam duomenų bazės tipui būdingą informaciją.

   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 užklausų vediklyje automatiškai būtų rodoma konkreti lentelė, spustelėkite 4 veiksmo lauką ir spustelėkite norimą lentelę.

9. Jei naudodamiesi duomenų šaltiniu nenorite įvesti prisijungimo vardo ir slaptažodžio, pažymėkite žymės langelį Įrašyti mano vartotojo ID ir slaptažodį duomenų šaltinio apraše . Įrašytas slaptažodis nešifruojamas. Jei žymės langelis nepasiekiamas, kreipkitės į duomenų bazės administratorių ir išsiaiškinkite, ar ši parinktis gali būti pasiekiama.

   Pastaba

   Venkite įrašyti prisijungimo informaciją, kai jungiatės prie duomenų šaltinių. Ši informacija gali būti saugoma kaip paprastas tekstas, o kenkėjiškas vartotojas gali ją pasiekti, kad pažeistų duomenų šaltinio saugą.

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

Užklausos apibrėžimas naudojant užklausų vediklį

Užklausų vediklio naudojimas daugeliui užklausų Užklausų vediklis padeda lengvai pasirinkti ir sujungti duomenis iš skirtingų duomenų bazės lentelių ir laukų. Naudodami užklausų vediklį galite pasirinkti norimas įtraukti lenteles ir laukus. Vidinis sujungimas (užklausos operacija, nurodanti, kad dviejų lentelių eilutės yra sujungiamos pagal identiškas lauko reikšmes) sukuriamas automatiškai, kai vediklis atpažįsta pirminio rakto lauką vienoje lentelėje ir lauką tuo pačiu pavadinimu kitoje lentelėje.

Taip pat galite naudoti vediklį rezultatų rinkiniui rikiuoti ir paprastam filtravimui atlikti. Paskutiniame vediklio žingsnyje galite pasirinkti grąžinti duomenis į "Excel" arba dar labiau patikslinti užklausą "„Microsoft“ Query". Sukūrus užklausą galima vykdyti ją 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. Dialogo lange Duomenų šaltinio pasirinkimas įsitikinkite, kad pažymėtas žymės langelis Užklausų kūrimui / redagavimui naudoti užklausų vediklį .
3. Dukart spustelėkite norimą naudoti duomenų šaltinį.
   –arba–
   Spustelėkite norimą naudoti duomenų šaltinį, tada spustelėkite Gerai.

Tiesioginis darbas 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". Norėdami peržiūrėti ir keisti užklausas, kurias pradedate kurti užklausų vediklyje, galite naudoti "„Microsoft“ Query" arba galite kurti naujas užklausas nenaudodami vediklio. Dirbkite tiesiogiai su "„Microsoft“ Query", jei norite kurti užklausas, kurios atlieka šiuos veiksmus:

• Konkrečių duomenų pasirinkimas iš lauko Didelėje duomenų bazėje galbūt norėsite pasirinkti kai kuriuos lauko duomenis ir praleisti duomenis, kurių jums nereikia. Pavyzdžiui, jei jums reikia dviejų produktų duomenų lauke, kuriame yra daugelio produktų informacija, galite naudoti kriterijus, kad pasirinktumėte tik dviejų norimų produktų duomenis.
• Nuskaitykite duomenis pagal skirtingus kriterijus kiekvieną kartą, kai vykdote užklausą Jei reikia sukurti tą pačią "Excel" ataskaitą arba suvestinę kelioms sritims tuose pačiuose išoriniuose duomenyse, pvz., atskirą pardavimo ataskaitą kiekvienam regionui, galite sukurti parametro užklausą. Kai vykdote parametro užklausą, esate paraginami nurodyti reikšmę, kuri bus naudojama kaip kriterijus užklausai pasirenkant įrašus. Pvz., parametrų užklausa gali paraginti įvesti konkretų regioną, o šią užklausą galima pakartotinai naudoti kuriant regiono pardavimo ataskaitas.
• Duomenų sujungimas įvairiais būdais Vidinės jungtys, kurias sukuria užklausų vediklis, yra dažniausiai naudojamas sujungimo tipas, naudojamas kuriant užklausas. Tačiau kartais norite naudoti kitokio tipo sujungimą. Pavyzdžiui, jei turite produkto pardavimo informacijos lentelę ir kliento informacijos lentelę, vidinis sujungimas (užklausų vediklio sukurtas tipas) neleis gauti klientų, kurie nepirko, įrašų. Naudodami "„Microsoft“ Query" galite sujungti šias lenteles, kad būtų gauti visi klientų įrašai ir pirkusių klientų pardavimo duomenys.

Norėdami paleisti "„Microsoft“ Query", atlikite šiuos veiksmus.

1. Skirtuko Duomenys grupėje Gauti išorinius duomenis spustelėkite Iš kitų šaltinių, tada spustelėkite Iš "„Microsoft“ Query".
2. Dialogo lange Duomenų šaltinio pasirinkimas įsitikinkite, kad išvalytas žymės langelis Užklausų kūrimui / redagavimui naudoti užklausų vediklį .
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 savo užklausas kaip .dqy failą, kurį galite modifikuoti, pakartotinai naudoti ir bendrinti. "Excel" gali tiesiogiai atidaryti .dqy failus, o tai leidžia jums arba kitiems vartotojams sukurti papildomus išorinių duomenų diapazonus naudojant tą pačią užklausą.

Norėdami atidaryti įrašytą užklausą programoje "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ą, o "„Microsoft“ Query" jau atidaryta, spustelėkite meniu "„Microsoft“" užklausos failas , tada spustelėkite Atidaryti.

Jei .dqy failą spustelėsite du kartus, "Excel" atsidarys, vykdys užklausą ir įterps rezultatus į naują darbalapį.

Jei norite bendrinti programos "Excel" suvestinę arba ataskaitą, kuri pagrįsta išoriniais duomenimis, galite suteikti kitiems vartotojams darbaknygę, kurioje yra išorinių duomenų diapazonas, arba galite sukurti šabloną. Naudodami šabloną galite į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ą.

Darbas su duomenimis programoje "Excel"

Sukūrę užklausą naudodami užklausų vediklį arba "„Microsoft“ Query", galite grąžinti duomenis į "Excel" darbalapį. Duomenys tampa išoriniu duomenų diapazonu arba "PivotTable" ataskaita, kurią galite formatuoti ir atnaujinti.

Gautų duomenų formatavimas "Excel" galite naudoti tokius įrankius, kaip diagramos ir automatinės tarpinės sumos, kad pateiktumėte ir apibendrintumėte "„Microsoft“ Query" nuskaitytus duomenis. Galite formatuoti duomenis, o formatavimas bus išsaugotas, kai atnaujinsite išorinius duomenis. Vietoj laukų pavadinimų galite naudoti savo stulpelių etiketes ir automatiškai įtraukti eilučių numerius.

"Excel" gali automatiškai formatuoti naujus duomenis, kuriuos įvedate diapazono pabaigoje, kad jie atitiktų prieš tai pateiktas eilutes. Programa "Excel" taip pat gali automatiškai kopijuoti ankstesnėse eilutėse pasikartojančias formules ir išplėsti jas į papildomas eilutes.

Šią parinktį galite bet kada įjungti (arba vėl išjungti):

1. Spustelėkite Failo>parinktys>išsamiau.
2. Redagavimo parinkčių dalyje pasirinkite varnelę 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 visus naujus arba pakeistus duomenis, kurie atitinka jūsų specifikacijas. Galite atnaujinti užklausą ir "„Microsoft“ Query", ir "Excel". Programa "Excel" siūlo kelias užklausų atnaujinimo parinktis, įskaitant duomenų atnaujinimą atidarius darbaknygę ir automatinį jos atnaujinimą nustatytais laiko intervalais. Galite toliau dirbti programoje "Excel", kol atnaujinami duomenys, taip pat galite patikrinti būseną, kol duomenys atnaujinami. Daugiau informacijos rasite " Excel" išorinio duomenų ryšio atnaujinimas.

Puslapio viršus