Parametru vaicājuma izveide

Veicot vaicājumu datus programmā Excel, iespējams, vēlēsities izmantot ievades vērtību-parametru —, lai norādītu kaut ko par vaicājumu. Lai to izdarītu, izveidojiet parametru vaicājumu. Veids, kā veidojat parametru vaicājumus un kā tie darbojas, ir atkarīgs no tā, vai izmantojat Microsoft Query vai Power Query.

Padoms.: Power Query parametri ir ļoti atšķirīgi no parametriem, kas izmantoti SQL vaicājumos. Turklāt, ja nepieciešams, varat izmantot vaicājumu faktiskā parametra vietā, lai filtrētu datus. Pirms parametru izveides līdzeklī Power Query apsveriet iespēju lasīt Power Query piemēra sadaļas.

Microsoft Query

Power Query

Kā parametri ietekmē vaicājumus

Parametri tiek izmantoti vaicājuma klauzulā WHERE, un tie vienmēr darbojas kā filtru izgūtajiem datiem.

Parametrus var izmantot jebkurā vaicājuma darbībā. Papildus darbībai datu filtrā parametrus var izmantot, lai norādītu tādus vienumus kā faila ceļu vai servera nosaukumu.

Parametru ievades opcijas

Parametri var lūgt lietotājam ievadīt vērtību, kad vaicājums tiek palaists vai atsvaidzināts, izmantot konstanti kā ievades vērtību vai izmantot noteiktas šūnas saturu kā ievades vērtību.

Parametri neprasa ievadi. Tā vietā varat mainīt to vērtību, izmantojot Power Query redaktoru. Vai arī bona fide parametra vietā varat izmantot vaicājumu, kas norāda uz ārēju atrašanās vietu ar vērtību, kuru varat viegli rediģēt.

Parametra tvērums

Parametrs ir daļa no vaicājuma, ko tas modificē, un to nevar atkārtoti izmantot citiem vaicājumiem.

Parametri ir atdalīti no vaicājumiem — pēc nepieciešamības varat pievienot parametru vaicājumiem.

  1. Noklikšķiniet uz datu > Iegūstiet & transformēšanas datu > Iegūstiet datu > no citiem avotiem > no Microsoft Query.

  2. Izpildiet vaicājuma vedņa darbības. Vaicājumu vednī — pabeigt ekrānā atlasiet Skatīt datus vai rediģēt vaicājumu programmā Microsoft Query un pēc tam noklikšķiniet uz pabeigt. Tiek atvērts logs Microsoft Query, kurā tiek rādīts vaicājums.

  3. Noklikšķiniet uz skatīt> SQL. Tiek parādīts dialoglodziņš SQL, kurā ir atrodama klauzula WHERE — rindiņa, kas sākas ar vārdu, kur parasti ir SQL koda beigās. Ja klauzula WHERE nav, pievienojiet to, ierakstot jaunu rindu vaicājuma beigās.

  4. Pēc WHERE, ierakstiet lauka nosaukumu, salīdzinājuma operatoru (=, <, >, piemēram, u.c.) un kādu no šīm darbībām:

    • Lai iegūtu vispārēju parametru uzvedni, ierakstiet jautājuma zīmi (?). Uzvednes, kas tiek parādīta vaicājuma izpildes laikā, nav redzama noderīga frāze.

      MS vaicājuma SQL skats, kurā akcentēta klauzula WHERE

    • Parametru uzvednei, kas palīdz lietotājiem nodrošināt derīgu ievadi, ierakstiet frāzi, kas iekļauta kvadrātiekavās. Frāze tiek parādīta parametra uzvednē, kad tiek palaists vaicājums.

      MS vaicājuma SQL skats, kurā akcentēta klauzula WHERE

  5. Pēc tam, kad esat pabeidzis pievienot nosacījumus ar parametriem klauzulā WHERE, noklikšķiniet uz Labi , lai izpildītu vaicājumu. Excel piedāvā norādīt vērtību katram parametram, un pēc tam Microsoft Query parāda rezultātus.

  6. Kad esat gatavs ielādēt datus, slēdziet Microsoft vaicājuma logu, lai atgrieztu rezultātus programmā Excel. Tiek atvērts dialoglodziņš datu importēšana.

    Dialoglodziņš datu importēšana programmā Excel

  7. Lai pārskatītu parametrus, noklikšķiniet uz Rekvizīti. Pēc tam dialoglodziņā Savienojuma rekvizīti cilnē definīcija noklikšķiniet uz Parametri.

    Dialoglodziņš savienojuma rekvizīti

  8. Dialoglodziņā parametri tiek parādīti vaicājumā izmantotie parametri. Atlasiet parametru sadaļā parametra nosaukums , lai pārskatītu vai mainītu parametra vērtības ieguvi. Varat mainīt parametru uzvedni, ievadīt noteiktu vērtību vai norādīt šūnas atsauci.

    Dialoglodziņš MS vaicājuma parametrs

  9. Noklikšķiniet uz Labi , lai saglabātu veiktās izmaiņas un aizvērtu dialoglodziņu parametri, un pēc tam dialoglodziņā datu importēšana noklikšķiniet uz Labi , lai vaicājuma rezultāti tiktu parādīti programmā Excel.

Tagad darbgrāmatā ir parametru vaicājums. Ikreiz, kad izpildāt vaicājumu vai atsvaidzināt tā datu savienojumu, programma Excel pārbauda parametru, lai pabeigtu vaicājuma klauzulu WHERE. Ja parametra uzvednē norāda vērtību, Excel parāda dialoglodziņu Parametra vērtības ievadīšana, lai savāktu ievadi — varat ierakstīt vērtību vai noklikšķināt uz šūnas, kurā ir ietverta vērtība. Varat arī norādīt, ka jūsu sniegtā vērtība vai atsauce vienmēr ir jāizmanto, un, ja izmantojat šūnas atsauci, varat norādīt, lai Excel automātiski atsvaidzinātu datu savienojumu (piemēram, palaistu vaicājumu vēlreiz), ja norādītās šūnas vērtība mainās.

Piezīme.: Šajā tēmā tiek pieņemts, ka jūs zināt, kā izveidot savienojumu ar Access datu bāzi, izmantojot Power Query. Papildinformāciju skatiet rakstā savienojuma izveide ar Access datu bāzi.

Varat izmantot parametrus vairākos Power Query scenārijos nekā tikai datu filtrēšanai — jebkurā Power Query vaicājuma darbībā var būt parametri. Piemēram, varat izmantot parametru, lai norādītu savienojuma virknes daļas avota darbībā, piemēram, faila nosaukumu.

Power Query parametros ir nosaukumi. Lai izmantotu parametru, jums ir jāatsaucas uz to pēc nosaukuma formulas solī. Piemēram, pieņemsim, ka vēlaties pārskatīt datus par jūsu saglabātajām tīmekļa lapām un vēlaties filtrēt datus pēc publikācijas datuma. Lai gan jūs vienmēr varat vienkārši lietot iebūvētos filtrus vaicājuma priekšskatījumā, izmantojot parametru, kas nodrošina filtrēšanas datumu, tiks ietaupīts laiks un sniegta plašāka elastība. Aplūkosim šo piemēru.

Tukšā darbgrāmatā mēs izveidojam savienojumu ar Access datu bāzi, kurā ir pieejami tīmekļa trafika ieraksti — arī lauki, kas norāda, kad katra lappuse tika sākotnēji publicēta. Ielādēts pievienojumprogrammā Power Query, tā izskatās šādi:

Power Query redaktors, kurā attēloti ielādētie dati

Tā kā mēs vēlamies filtrēt pēc datuma, mēs mainām tās kolonnas datu tipu, ko mēs izmantojam, FirstPublishDate. Datu avotā ir datuma/laika dati, taču mums nav jārūpējas par to, cik ilgi notiek publikācijas laiks un ir jānorāda, ka tas var būt apnicīgs, tāpēc mēs to mainīsim uz datuma datu tipu.

Power Query redaktors, kurā parādīti rezultāti

Pēc tam mēs izveidojam parametru, kas sniedz rezultātu ierobežojumus līdz lapas oriģinālajai publicēšanai. Noklikšķiniet uz sākums> Parametri > Pārvaldiet parametrus , lai atvērtu dialoglodziņu parametri.

Power Query parametru dialogs

Noklikšķiniet uz Jaunsun veidlapā tiek parādīts jauns parametrs ar nosaukumu Parameter1, neizmantojot citu informāciju.

Mēs mainām dažus parametru rekvizītus:

  • Nosaukuma maiņa uz FirstPubD

  • Mainiet aprakstu uz datuma lapas pirmo publicēšanu.

  • Mainīt tipadatumu , lai parametrs akceptētu tikai datuma vērtības

  • Iestatiet pašreizējo vērtību , lai parametrā netiktu filtrētas visas rindas, ja nav norādīta ievade-mēs izmantojam 1/1/2010.

Padoms.: Nosaukums un apraksts nodrošina pietiekami daudz kontekstu, lai palīdzētu lietotājiem saprast, kā un kāpēc izmantot parametru. Pat tad, ja esat vienīgais lietotājs, kurš izmantos šo parametru, laiku pa laikam var būt nepieciešams atgādinājums.

Lai izveidotu parametru un to redzētu Power Query redaktorā, noklikšķiniet uz Labi .

Power Query redaktors, kurā attēlots parametrs

Tagad mūsu parametrs ir norādīts vaicājumu panelī — to var atlasīt, lai to rādītu galvenajā panelī, vai arī varat ar peles labo pogu noklikšķināt uz tā, lai skatītu papildu opcijas. Ja ir atlasīts parametrs, mēs varam rediģēt pašreizējo vērtību galvenajā panelī vai noklikšķināt uz Pārvaldīt parametru , lai mainītu tā pārējos iestatījumus.

Tagad mēs varam izmantot šo parametru mūsu sākotnējā vaicājumā. Mēs noklikšķina uz sākotnējā vaicājuma vaicājumu panelī, lai to atvērtu. Mēs vēlamies izmantot mūsu parametru, lai filtrētu rezultātus, pamatojoties uz pirmās publikācijas datumu, un pēc tam atlasiet kolonnu FirstPublishDate , noklikšķiniet uz filtra/kārtošanas bultiņas kolonnas virsraksta labajā malā, norādiet uz datumu filtriun pēc tam noklikšķiniet uz pēc....

Power Query redaktors, kurā tiek rādīta datuma filtra izvēlne

Dialoglodziņā filtra rindas atlasiet parametru no filtra izvēļu saraksta.

Dialoglodziņš filtra rindas

Ievadiet vai atlasiet vērtību aizstāj ar pieejamo parametru sarakstu. Ir tikai viens, tikko izveidotais, FirstPubD.

Dialoglodziņš filtra rindas, kurā tiek rādīts atlasītais parametrs

Mēs to atlasījis un noklikšķināt uz Labi. Power Query redaktors ielādē vaicājumu, izmantojot jauno parametru kā filtru.

Power Query redaktors, kas rāda filtrētus rezultātus

Lai pārbaudītu parametru, mēs mainām tā vērtību uz 1/1/2018.

Power Query redaktors, kurā attēlots parametrs

Mēs atsvaidzinām vaicājumu, kas tagad rāda tikai rindas ar FirstPublishDate pēc 1/1/2018.

Power Query redaktors, kas rāda filtrētus rezultātus

Tagad mums ir vaicājums, kas filtrē pēc datuma, izmantojot parametru. Lai filtrētu rezultātus, FirstPublishDate mums vairs nav jāatrod lauks, noklikšķiniet uz bultiņas filtrēt/kārtot, izvēlieties pogu pēc... filtra tips un ievadiet datuma vērtību — mēs varam vienkārši mainīt FirstPubD vērtību un atsvaidzināt vaicājumu. Turklāt mēs varam atkārtoti izmantot jauno parametru, piemēram, ja izlemjat, ka no oriģinālā datu avota vēlaties izvilkt kādu citu lauku kopu jaunā darblapā, bet joprojām vēlos iekļaut FirstPubDate un izmantot to rezultātu filtrēšanai.

Parametri ir ļoti noderīgi, bet joprojām ir jāizmanto Power Query redaktors, lai mainītu parametra vērtību. Mēs vēlamies mainīt filtra vērtību, neatverot Power Query redaktoru. Lai to izdarītu, izveidosim tabulu darblapā, kur vaicājums tiek ielādēts, un jaunu Power Query savienojumu ar tabulu, un pēc tam izmantojiet jauno vaicājumu, lai filtrētu mūsu galveno vaicājumu.

Darblapā, kurā tiek ielādēti mūsu vaicājumi, tiek ievietotas dažas rindas virs importētajiem datiem. Pēc tam mēs izveidojam Excel tabulu ar vienu rindu, kurā ir mūsu parametra vērtība.

Excel darbgrāmata, kurā tiek rādīta parametru tabula un dati, kas ielādēti no Power Query

Lai izmantotu jauno tabulu vaicājumu filtrēšanai, ir jāizveido savienojums ar to pievienojumprogrammā Power Query. Mēs izveidojam savienojumu ar tabulu, atlasot to un pēc tam cilnē dati noklikšķinot uz no tabulas/diapazona . Tiek atvērts jaunais savienojums, un tiek parādīta jaunā tabula Power Query redaktorā.

Excel tabulas dati, kas ielādēti Power Query redaktorā

Tā kā datu tips Date/Time ir ielādēts, mums tas ir jāmaina uz datu tipu Date, lai tas atbilst mūsu parametram, tāpēc mēs noklikšķina uz sākums > transformācijas > datu tipa > datumu.

Ar peles labo pogu noklikšķiniet uz komandas datu tips, kas atrodas Power Query redaktora lentes cilnes Sākums grupā transformācija.

Mēs Pārdēvējam mūsu vaicājumu arī par to, kas ir nozīmīgāks par Tabula2. Lai precizētu, kam tas ir paredzēts, mēs to FirstPubDate.

Power Query redaktors ar izceltu nosaukumu lodziņu

Tā kā mēs vēlamies nodot vērtību, nevis pašu tabulu, mums ir nepieciešams detalizēti noteikt datuma vērtību. Lai to izdarītu, ar peles labo pogu noklikšķiniet uz vērtības, kas ir priekšskatītajos datos, un pēc tam noklikšķiniet uz Rādīt Detalizēti.

Power Query redaktora kontekstizvēlne lauka vērtībai

Priekšskatījumā tagad tiek rādīta vērtība, nevis tabula.

Power Query redaktors, kurā redzama viena datuma vērtība

Mums nav nepieciešami jauni vaicājuma dati, lai tos ielādētu neatkarīgi no tā, vai dati jau ir darblapā, kur vēlaties to redzēt. Mums ir tikai jāpievieno savienojums, lai Power Query var iegūt parametra vērtību. Tāpēc, lai atvērtu dialoglodziņu datu importēšana, noklikšķiniet uz fails> slēgt & ielādēt ..., un pēc tam atlasiet tikai izveidot savienojumu.

Dialoglodziņš datu importēšana, atlasīta opcija tikai savienojuma izveide

Tagad mums ir vaicājums ar nosaukumu "FirstPubDate", kas izvelk vienu datuma vērtību no tabulas darblapā tieši virs vietas, kur tiek ielādēts mūsu galvenais vaicājums. Tagad ir jāizmanto šis vaicājums kā parametrs mūsu galvenā vaicājuma filtrēšanai. Tāpēc mēs atveram galveno vaicājumu un rediģējat darbību, kas filtrē rindas, izmantojot kolonnu FirstPublishDate. Izvērsiet formulas joslu un atlasiet iepriekš izveidoto parametru (FirstPubD). Pēc tam mēs ierakstām "a" pēc FirstPubD -tā kā jaunais vaicājuma nosaukums tiek sākts ar tiem pašiem burtiem, kas atbilst parametram Power Query, tas tiek parādīts kā opcija Atlasīt.

Power Query redaktora formulu josla izvērsta

Atlasiet to un pēc tam noklikšķiniet ārpus formulu joslas, lai lietotu šo darbību.

Power Query redaktors ar ielādētiem datiem

Viss izskatās pareizi, tāpēc mēs izejam no Power Query redaktora un saglabājam savas izmaiņas. Lai pārbaudītu parametru, atskaites darblapā mēs mainām šūnas vērtību, kas atrodas augšā uz 5/4/2019, un pēc tam atsvaidzināt savienojumu, lai skatītu filtrētos datus.

Filtrētie dati programmā Excel

Mūsu jaunais filtrs darbojas! Lai saglabātu un aizvērtu darbgrāmatu. Tagad visi, kas izmanto darbgrāmatu, var norādīt pirmās publikācijas datumu, kas jāizmanto kā vaicājuma filtrs, turpat tajā pašā darblapā, kur tiek ielādēts vaicājums.

  1. Noklikšķiniet uz datu > Iegūstiet & transformēšanas datu > Iegūstiet datus > palaist Power Query redaktoru.

  2. Power Query redaktorā noklikšķiniet uz sākums > Parametri , > pārvaldītu parametrus.

  3. Dialoglodziņā parametri noklikšķiniet uz Jauns.

  4. Pēc nepieciešamības iestatiet tālāk norādītās darbības.

    • Name -tas atspoguļo parametra funkciju, bet saglabāt to pēc iespējas īsākā veidā.

    • Apraksts — tajā var būt ietverta detalizēta informācija, kas lietotājiem palīdzēs pareizi izmantot parametru.

    • Obligāts — atlasiet, lai šim parametram būtu nepieciešama vērtība.

    • Tips -tas norāda datu tipu, kas nepieciešams parametram.

    • Ieteicamās vērtības — ja nepieciešams, pievienojiet vērtību sarakstu vai norādiet vaicājumu, lai iegūtu ieteikumus ievadei.

    • Noklusējuma vērtība — tas tiek parādīts tikai tad, ja ieteicamās vērtības ir iestatītas kā vērtību saraksts, un norāda, kurš saraksta elements ir noklusējuma.

    • Pašreizējā vērtība atkarībā no tā, kur izmantojat parametru, ja tas ir tukšs, vaicājums var neatgriezt rezultātus. Ja ir atlasīts obligāts , pašreizējā vērtība nevar būt tukša.

  5. Noklikšķiniet uz Labi , lai izveidotu parametru.

  1. Atveriet vaicājumu Power Query redaktorā.

  2. Noklikšķiniet uz bultiņas, kas atrodas tās kolonnas galvenes labajā malā, kuru vēlaties izmantot datu filtrēšanai, un pēc tam izvēlnē, kas tiek parādīta, izvēlieties filtru.

  3. Dialoglodziņā filtrēt rindas noklikšķiniet uz pogas pa labi no filtra nosacījuma un pēc tam veiciet kādu no šīm darbībām:

    • Lai izmantotu esošu parametru, noklikšķiniet uz parametrsun pēc tam sarakstā, kas tiek parādīts labajā pusē, atlasiet nepieciešamo parametru.

    • Lai izmantotu jaunu parametru, noklikšķiniet uz jauns parametrs...un pēc tam Izveidojiet parametru.

  1. Darblapā, kurā tiek ielādēts vaicājums, kuru vēlaties filtrēt, izveidojiet tabulu ar divām šūnām: galveni un vērtību.

  2. Noklikšķiniet uz vērtības un pēc tam uz dati > Iegūstiet & transformētu datu > no tabulas/diapazona.

  3. Power Query redaktorā veiciet jebkādus tabulas savienojuma pielāgojumus (piemēram, datu tipa vai nosaukuma maiņu), pēc tam noklikšķiniet uz Sākums , > aizvērtu > aizvērtu & ielādēt > aizvērtu & ielādēt....

  4. Dialoglodziņā datu importēšana noklikšķiniet uz tikai izveidot savienojumu, pēc izvēles atlasiet pievienot datu modelimun pēc tam noklikšķiniet uz Labi.

  5. Atveriet vaicājumu, ko vēlaties filtrēt Power Query redaktorā.

  6. Noklikšķiniet uz bultiņas, kas atrodas tās kolonnas galvenes labajā malā, kuru vēlaties izmantot datu filtrēšanai, un pēc tam izvēlnē, kas tiek parādīta, izvēlieties filtru.

  7. Veiciet vienu no šīm darbībām:

    • Vērtības nolaižamajā sarakstā atlasiet vērtību (tās nāk no vaicājuma datiem).

    • Atlasiet vērtību, izmantojot pogu filtra nosacījuma labajā malā.

  8. Noklikšķiniet uz bultiņas pie formulu joslas labās malas, lai tiktu parādīts viss vaicājums.

  9. Filtra nosacījums ir šāds:

    • Tās kolonnas nosaukums, kas tiek filtrēta, tiek rādīta kvadrātiekavās.

    • Salīdzinājuma operators tūlīt seko kolonnas nosaukumam.

    • Filtra vērtība uzreiz seko salīdzinājuma operatoram un beidzas ar aizverošo iekavu. Atlasiet šo visu vērtību.

  10. Sāciet rakstīt tā tabulas savienojuma nosaukumu, kuru tikko izveidojāt, un pēc tam atlasiet to sarakstā, kas tiek parādīts.

  11. Noklikšķiniet Home uz sākums > > aizvērtu& ielādēt.

    Jūsu vaicājums tagad izmanto vērtību tabulā, ko izveidojāt, lai filtrētu vaicājuma rezultātus. Lai izmantotu jaunu vērtību, rediģējiet šūnu saturu un pēc tam atsvaidziniet vaicājumu.

Skatiet arī

Nolaižamā saraksta izveide

Piezīme.:  Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai jūs varat mūs informēt, vai informācija bija noderīga? Šeit ir raksts angļu valodā jūsu atsaucei.​

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×