Parametro užklausos kūrimas

Kai "Excel" užklausate duomenis, galbūt norėsite naudoti įvesties reikšmę – parametrą – norėdami nustatyti užklausą. Norėdami tai padaryti, sukurkite parametro užklausą. Taip, kaip kuriate parametro užklausas ir kaip jie veikia, priklauso nuo to, ar naudojate "Microsoft Query", ar "Power Query".

Patarimas: "Power Query" parametrai labai skiriasi nuo parametrų, naudojamų SQL pagrįstoms užklausoms. Be to, galite naudoti užklausą, o ne faktinį parametrą, jei jums tereikia filtruoti duomenis. Prieš kurdami parametrus "Power Query", apsvarstykite "Power Query" pavyzdinės sekcijos skaitymą.

"Microsoft" užklausa

Power Query

Kaip parametrai paveikia užklausas

Parametrai naudojami užklausos WHERE sąlygoje – jos visada veikia kaip nuskaitytų duomenų filtras.

Parametrus galima naudoti bet kuriame užklausos veiksme. Be duomenų filtro veikimo, parametrus galima naudoti norint apibrėžti tokius dalykus kaip failo maršrutas arba serverio vardas.

Parametro įvesties parinktys

Parametrai gali paraginti vartotoją įvesti įvesties reikšmę, kai užklausa vykdoma arba atnaujinama, naudoti konstantą kaip įvesties reikšmę arba naudoti nurodyto langelio turinį kaip įvesties reikšmę.

Parametrai neparagina įvesties. Vietoj to galite pakeisti jų reikšmę naudodami "Power Query" rengyklę. Arba vietoj bona fide parametro galite lengvai redaguoti užklausą, kuri nukreipia į išorinę vietą su reikšme.

Parametro aprėptis

Parametras yra modifikuojamos užklausos dalis ir jo negalima pakartotinai naudoti kitose užklausose.

Parametrai yra atskirti nuo užklausų – sukūrę, galite įtraukti parametrą į užklausas, jei reikia.

  1. Spustelėkite duomenys > gauti & transformuoti duomenis > gauti duomenis > iš kitų šaltinių > iš "Microsoft Query".

  2. Vykdykite užklausos vediklio veiksmus. Užklausos vediklyje – baigtas ekranas Pasirinkite peržiūrėti duomenis arba redaguoti užklausą programoje "Microsoft Query " ir spustelėkite baigta. Atidaromas "Microsoft" užklausos langas ir rodoma užklausa.

  3. Spustelėkite peržiūrėti> SQL. Rodomame SQL dialogo lange raskite sąlygą WHERE – eilutę, pradedant nuo žodžio, kuris paprastai yra SQL kodo pabaigoje. Jei sąlygos WHERE nėra, įtraukite vieną, įvesdami vietą į naują eilutę užklausos pabaigoje.

  4. Po to įveskite lauko pavadinimą, palyginimo operatorių (=, <, >, pvz., ir kt.) ir vieną iš šių veiksmų:

    • Norėdami naudoti bendrąją parametro eilutę, įveskite klaustuką (?). Raginime, rodomame vykdant užklausą, nerodomas joks naudingas sakinys.

      MS užklausos SQL rodinys pabrėžiant sąlygą WHERE

    • Parametro raginimo, kuris padeda žmonėms pateikti tinkamą įvestį, lauke įveskite frazę, kuri yra laužtiniuose skliaustuose. Kai užklausa paleista, parametro eilutėje rodoma frazė.

      MS užklausos SQL rodinys pabrėžiant sąlygą WHERE

  5. Baigę įtraukti sąlygas su parametrais į sąlygą WHERE, spustelėkite gerai , kad paleistumėte užklausą. "Excel" paragins pateikti kiekvieno parametro reikšmę, tada "Microsoft Query" rodys rezultatus.

  6. Kai būsite pasirengę įkelti duomenis, uždarykite "Microsoft" užklausos langą, kad grąžintumėte rezultatus į "Excel". Atidaromas dialogo langas duomenų importavimas.

    Dialogo langas duomenų importavimas programoje "Excel"

  7. Norėdami peržiūrėti parametrus, spustelėkite Ypatybės. Tada dialogo lange jungties ypatybės, skirtuke apibrėžimas , spustelėkite Parametrai.

    Dialogo langas sujungimo ypatybės

  8. Parametrų dialogo lange rodomi užklausoje naudojami parametrai. Pasirinkite parametrą dalyje parametro pavadinimas ir peržiūrėkite arba keiskite, kaip gaunama parametro reikšmė. Galite pakeisti parametro raginimą, įvesti konkrečią reikšmę arba nurodyti langelio nuorodą.

    Dialogo langas MS užklausos parametras

  9. Spustelėkite gerai , kad įrašytumėte savo atliktus pakitimus ir uždarytumėte dialogo langą parametrai, tada dialogo lange duomenų importavimas spustelėkite gerai , kad užklausos rezultatai būtų rodomi programoje "Excel".

Dabar darbaknygėje yra parametro užklausa. Kaskart paleidus užklausą arba atnaujinus jos duomenų jungtį, programa "Excel" patikrina parametrą ir užpildo užklausos sąlygą WHERE. Jei parametras paragina įvesti reikšmę, programa "Excel" rodo dialogo langą įvesti parametro reikšmę, skirtą įvesti – galite įvesti reikšmę arba spustelėti langelį, kuriame yra reikšmė. Taip pat galite nurodyti, kad jūsų pateikta reikšmė arba nuoroda visada būtų naudojama, o jei naudojate langelio nuorodą, galite nurodyti, kad "Excel" turėtų automatiškai atnaujinti duomenų jungtį (t.y. paleisti užklausą dar kartą), kai tik pasikeičia nurodyto langelio reikšmė.

Pastaba: Šioje temoje daroma prielaida, kaip sukurti ryšio su "Access" duomenų baze naudojant "Power Query". Daugiau informacijos ieškokite prisijungimas prie "Access" duomenų bazės.

Parametrus galite naudoti daugiau "Power Query" scenarijų nei tik filtravimo duomenys – bet kuris "Power Query" užklausos veiksmas gali turėti parametrus. Pvz., galite naudoti parametrą, kad nustatytumėte jungimosi eilutės dalis šaltinio žingsnyje, pvz., failo vardą.

"Power Query" parametrai turi vardus. Norėdami naudoti parametrą, galite jį nurodyti pagal pavadinimą, esantį veiksmo formulėje. Pavyzdžiui, Tarkime, kad norite peržiūrėti duomenis apie jūsų prižiūrimą tinklalapį, ir norite filtruoti duomenis pagal publikavimo datą. Nors visada galite naudoti įtaisytuosius filtrus užklausos peržiūroje, naudodami parametrą, kuris pateiks filtravimo datą, sutaupysite laiko ir suteiks daugiau lankstumo. Apžvelkime šį pavyzdį.

Tuščioje darbaknygėje sukursime prisijungimą prie "Access" duomenų bazės, kurioje yra jūsų norimų žiniatinklio srauto duomenų, įskaitant laukus, nurodančius, kada kiekvienas puslapis buvo publikuotas. Įkelta į "Power Query", atrodo taip:

"Power Query" rengyklė, vaizduojanti įkeltus duomenis

Kadangi mes norime filtruoti pagal datą, mes pakeisime jūsų naudojamo stulpelio duomenų tipą, FirstPublishDate. Tai yra datos/laiko duomenys šaltinyje, bet mes nerūpinamės, kokio laiko paskelbimo metu kilo, ir reikia jį nustatyti, kad būtų galima gauti varginančią funkciją, todėl pakeisime duomenų tipą data.

"Power Query" rengyklės rezultatų rodymas

Toliau pateikiame parametro, kaip apriboti rezultatus pagal datą, kada puslapis buvo publikuotas. Spustelėkite pagrindinis> Parametrai > tvarkyti parametrus , kad atidarytumėte dialogo langą parametrai.

"Power Query" parametrų dialogo langas

Spustelėkite naujasir formoje rodomas naujas parametras pavadinimu 1 parametras be jokios kitos informacijos.

Pakeisime kai kurias parametro ypatybes:

  • Pavadinimo keitimas į firstpubd

  • Keisti aprašą iki datos, kai puslapis buvo pirmą kartą publikuotas.

  • Keisti tipą iki datos , kad parametras priimtų tik datos reikšmes

  • Nustatykite dabartinę reikšmę , kad parametras neišfiltruotų visų eilučių, kai nepateikėte įvesties – naudojame 1/1/2010.

Patarimas: Pavadinimas ir aprašas turėtų suteikti pakankamai konteksto, kad padėtų žmonėms suprasti, kaip ir kodėl naudoti parametrą. Net jei esate vienintelis asmuo, kuris naudos šį parametrą, jums gali reikėti priminimo laiko.

Spustelėkite gerai , kad sukurtumėte parametrą ir pamatytumėte jį "Power Query" rengyklėje.

"Power Query" rengyklė, kurioje rodomas parametras

Dabar mūsų parametras pateiktas užklausų skydelyje – galime jį pažymėti, kad jis būtų rodomas pagrindiniame skydelyje, arba galime spustelėti jį dešiniuoju pelės mygtuku, kad būtų daugiau parinkčių. Pasirinkus parametrą, galime redaguoti dabartinę reikšmę pagrindiniame skydelyje arba spustelėti parametrą valdyti , kad pakeistumėte kitus parametrus.

Dabar šį parametrą galime naudoti pradinėje užklausoje. Spustelėkite pradinę užklausą užklausų skydelyje, kad ją rodytumėte. Mes norime naudoti mūsų parametrą, kad filtruotumėte rezultatus pagal pirmojo leidinio datą, tad toliau pasirinkite stulpelį Firstpublishdate , spustelėkite rodyklę filtruoti/rikiuoti dešiniosios stulpelio antraštės krašte, nukreipkite žymiklį į datos filtrai, tada spustelėkite po...

"Power Query" rengyklė, kurioje rodomas datos filtro meniu

Dialogo lange filtruoti eilutes pasirinkite parametrą iš filtro pasirinkimų sąrašo.

Dialogo langas filtruoti eilutes

Įvedus arba pasirinkus reikšmę , bus pakeistas galimų parametrų sąrašas. Yra tik vienas, ką tik sukūrėte, FirstPubD.

Dialogo langas filtruoti eilutes, kuriame rodomas pasirinktas parametras

Ją pasirinkome ir spustelėkite gerai. "Power Query" rengyklė įkelia užklausą naudodama naująjį parametrą kaip filtrą.

"Power Query" rengyklė, kurioje rodomi filtruojami rezultatai

Norėdami išbandyti parametrą, pakeisime jo reikšmę į " 1/1/2018".

"Power Query" rengyklė, kurioje rodomas parametras

Atnaujiname užklausą, kurioje dabar rodomos tik eilutės, kurių FirstPublishDate po 1/1/2018.

"Power Query" rengyklė, kurioje rodomi filtruojami rezultatai

Dabar turime užklausą, kuri filtruoja pagal datą, naudodama parametrą. Norėdami filtruoti rezultatus pagal FirstPublishDate, mes nebereikia ieškoti lauko, spustelėkite rodyklę filtruoti/rikiuoti, pasirinkite po... filtro tipas ir įveskite datos reikšmę – galime tiesiog pakeisti FirstPubD reikšmę ir atnaujinti mūsų užklausą. Be to, galime pakartotinai naudoti naują parametrą, pvz., jei nuspręsime iš pirminio duomenų šaltinio išvesti kitą laukų rinkinį į naują darbalapį, bet vis tiek norite įtraukti FirstPubDate ir naudoti ją rezultatams filtruoti.

Parametrai yra neabejotinai labai naudingi, bet vis tiek reikia naudoti "Power Query" rengyklę, kad pakeistumėte parametro reikšmę. Norėtume, kad galėtumėte keisti filtro reikšmę neatidarydami "Power Query" rengyklės. Norėdami tai padaryti, sukursime lentelę darbalapyje, kuriame užklausa įkeliamas ir sukuriamas naujas "Power Query" Prisijungimas prie lentelės, tada naudokite naująją užklausą, kad filtruotumėte pagrindinę užklausą.

Darbalapyje, kuriame yra mūsų užklausa, įterpiame keletą eilučių virš importuotų duomenų. Tada sukursime "Excel" lentelę su viena eilute ir laikykite mūsų parametro reikšmę.

"Excel" darbaknygė, kurioje rodoma parametro lentelė ir duomenys, įkelti iš "Power Query"

Norėdami filtruoti užklausas naudodami naująją lentelę, turime prie jos prisijungti naudodami "Power Query". Mes sukuriame ryšius su lentele pažymėdami jį ir spustelėję lentelė/intervalas skirtuke duomenys . Atidaromas naujas Prisijungimas ir rodoma nauja lentelė "Power Query" rengyklėje.

"Excel" lentelės duomenys, įkelti į "Power Query" rengyklę

Kadangi duomenys įkelti kaip datos/laiko duomenų tipą, mums reikia pakeisti datos duomenų tipą, kad jis atitiktų mūsų parametrą, todėl mes spustelėkite pagrindinis > transformuoti > duomenų tipą > Date.

"Power Query" rengyklės juostelės skirtuko Pagrindinis grupėje transformacija užveskite pelės žymiklį ant komandos duomenų tipas.

Mes taip pat pervardijame užklausą į kažką reikšmingesnį nei "Lentelė2". Kad būtų aišku, kas tai yra, mes pavadiname tai FirstPubDate.

"Power Query" rengyklė su paryškintu pavadinimo lauku

Kadangi mes norime perduoti reikšmę, o ne pačią lentelę, reikia detalizuoti iki datos reikšmės. Norėdami tai padaryti, mes dešiniuoju pelės mygtuku spustelėkite peržiūrėto duomenų reikšmę ir spustelėkite detalizuoti.

"Power Query" rengyklės kontekstinis meniu lauko reikšmei

Dabar peržiūroje rodoma reikšmė, o ne lentelė.

"Power Query" rengyklė, kurioje rodoma viena datos reikšmė

Naujos užklausos duomenų įkelti nereikia bet kur – jo duomenys jau yra darbalapyje, kur norite. Jums tereikia jungties, kad "Power Query" galėtų gauti parametro reikšmę. Taigi, mes spustelėkite failas> uždaryti & įkelti į... , kad atidarytumėte dialogo langą duomenų importavimas, tada pasirinkite tik kurti jungtį.

Dialogo langas duomenų importavimas su pasirinktu tik jungimosi parinktimi

Dabar mes turime užklausą, pavadintą "FirstPubDate", kuri ištraukia vieną datos reikšmę iš darbalapio lentelės, tiesiog aukščiau, kur mūsų pagrindinė užklausa įkeliamas. Dabar mums tereikia naudoti šią užklausą kaip mūsų pagrindinės užklausos filtravimo parametrą. Taigi atidarykite pagrindinę užklausą ir redaguokite veiksmus, kurie filtruoja eilutes naudodami stulpelį FirstPublishDate. Mes išplečiame formulės juostą ir pasirinkite anksčiau sukurtą parametrą (FirstPubD). Tada mes įrašome "a" po Firstpubd – nes naujos užklausos pavadinimas prasideda tomis pačiomis raidėmis kaip parametras, "Power Query" rodo ją kaip pasirenkamą parinktį.

"Power Query" rengyklės formulės juosta išplėsta

Ją pasirinkome, tada spustelėkite už formulės juostos ribų, kad taikytumėte veiksmą.

"Power Query" rengyklė su įkeltais duomenimis

Viskas atrodo tinkamai, todėl išeisite iš "Power Query" redaktoriaus ir įrašykite savo atliktus pakitimus. Norėdami patikrinti parametrą, ataskaitos darbalapyje mes pakeisime langelio reikšmę, esančią lentelėje nuo viršaus iki 5/4/2019, tada atnaujiname jungtį, kad pamatytumėte filtruotus duomenis.

Filtruoti duomenys programoje "Excel"

Mūsų naujasis filtras veikia! Kad įrašytume ir uždarytume darbaknygę. Dabar bet kuris asmuo, kuris naudoja darbaknygę, gali nustatyti pirmojo leidinio, kuris bus naudojamas kaip užklausos filtras, datą – toje pačioje darbaknygėje, kurioje užklausa įkeliamas.

  1. Spustelėkite duomenys > gauti & transformuoti duomenis > gauti duomenis > paleisti "Power Query" rengyklę.

  2. "Power Query" rengyklėje spustelėkite pagrindinis > Parametrai > valdyti parametrus.

  3. Dialogo lange parametrai spustelėkite naujas.

  4. Nustatykite šiuos dalykus pagal poreikį:

    • Pavadinimas – tai turėtų atspindėti parametro funkciją, bet išlaikyti ją kiek įmanoma trumpiau.

    • Aprašas – jame gali būti informacijos, kuri padės žmonėms tinkamai naudoti parametrą.

    • Būtina – pasirinkite, kad šiam parametrui būtų reikalinga reikšmė.

    • Įveskite – nurodo duomenų tipą, kuriam reikia parametro.

    • Rekomenduojamos reikšmės – jei norite, įtraukite reikšmių sąrašą arba nurodykite užklausą, kuri pateiks įvesties pasiūlymus.

    • Numatytoji reikšmė – rodoma tik jei siūlomos reikšmės nustatytos kaip reikšmių sąrašas ir nurodo, kuris sąrašo elementas yra numatytasis.

    • Dabartinė reikšmė – atsižvelgiant į tai, kur naudojate parametrą, jei ji tuščia, užklausa gali pateikti jokių rezultatų. Jei reikia , pasirinkta reikšmė negali būti tuščia.

  5. Spustelėkite gerai , kad sukurtumėte parametrą.

  1. Atidarykite užklausą "Power Query" rengyklėje.

  2. Spustelėkite rodyklę, esančią dešiniajame stulpelio antraštės krašte, kurią norite naudoti duomenims filtruoti, tada rodomame meniu pasirinkite filtrą.

  3. Dialogo lange filtruoti eilutes spustelėkite filtro sąlygos dešinėje esantį mygtuką, tada atlikite vieną iš šių veiksmų:

    • Norėdami naudoti esamą parametrą, spustelėkite parametras, tada iš dešinėje pasirodžiusiame sąraše pasirinkite norimą parametrą.

    • Norėdami naudoti naują parametrą, spustelėkite naujas parametras..., tada sukurkite parametrą.

  1. Darbalapyje, kuriame yra norima filtruoti užklausa, sukurkite lentelę su dviem langeliais: antrašte ir reikšme.

  2. Spustelėkite reikšmę, tada spustelėkite duomenys > gauti & transformuoti duomenis > iš lentelės/ribos.

  3. "Power Query" rengyklėje atlikite bet kokius lentelės ryšių pataisymus (pvz., duomenų tipo arba pavadinimo keitimą), tada spustelėkite pagrindinis > uždaryti > uždaryti & įkelti > uždaryti & įkelti į....

  4. Dialogo lange duomenų importavimas spustelėkite tik kurti jungtį, pasirinktinai pasirinkite įtraukti į duomenų modelį, tada spustelėkite gerai.

  5. Atidarykite užklausą, kurią norite filtruoti "Power Query" rengyklėje.

  6. Spustelėkite rodyklę, esančią dešiniojo stulpelio, kurį norite naudoti duomenims filtruoti, antraštės krašte ir pasirodžiusiame meniu pasirinkite filtrą.

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

    • Išplečiamajame reikšmių sąraše pasirinkite reikšmę (tai yra iš užklausos duomenų).

    • Pasirinkite reikšmę, naudodami mygtuką, esantį dešiniajame filtro sąlygos krašte.

  8. Spustelėkite rodyklę, esančią dešiniajame formulės juostos krašte, kad būtų rodoma visa užklausa.

  9. Filtro sąlyga atitinka žodį:

    • Filtruoto stulpelio pavadinimas rodomas laužtiniuose skliaustuose.

    • Palyginimo operatorius iškart stebi stulpelio pavadinimą.

    • Filtro reikšmė iškart seka palyginimo operatorių ir baigiasi uždarymo skliaustuose. Pasirinkite šią visą reikšmę.

  10. Pradėkite įvesti ką tik sukurtą lentelės ryšių pavadinimą, tada pasirinkite jį pasirodžiusiame sąraše.

  11. Spustelėkite pagrindinis > uždaryti > uždarykite & apkrova.

    Jūsų užklausa dabar naudoja lentelės, kurią sukūrėte užklausos rezultatams filtruoti, reikšmę. Norėdami naudoti naują reikšmę, redaguokite langelio turinį ir atnaujinkite užklausą.

Taip pat žr.

Išplečiamojo sąrašo kūrimas

Pastaba:  Šis puslapis išverstas automatiškai, todėl gali būti gramatikos klaidų ar netikslumų. Mūsų tikslas – padaryti, kad šis turinys būtų jums naudingas. Prašome mus informuoti, ar radote reikiamos informacijos. Čia yra straipsnis anglų kalba, kuriuo galite pasinaudoti kaip nuoroda.

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×