Crearea unei interogări cu parametri

Atunci când interogați date în Excel, se recomandă să utilizați o valoare de intrare-un parametru-pentru a specifica ceva despre interogare. Pentru a face acest lucru, creați o interogare cu parametri. Modul în care creați interogările de parametri și modul în care se comportă depinde de faptul că utilizați Microsoft Query sau Power Query.

Sfat: Parametrii Power Query sunt foarte diferiți de parametrii utilizați în interogările bazate pe SQL. În plus, puteți utiliza o interogare în locul unui parametru real, dacă nu aveți nevoie decât de filtrarea datelor. Luați în considerare citirea secțiunilor exemplu Power Query înainte de a crea parametri în Power Query.

Microsoft Query

Power Query

Cum afectează parametrii interogările

Parametrii sunt utilizați în clauza WHERE a interogării – acestea funcționează întotdeauna ca un filtru pentru datele regăsite.

Parametrii pot fi utilizați în orice pas de interogare. În plus față de funcționarea ca filtru de date, parametrii pot fi utilizați pentru a specifica astfel de lucruri ca o cale de fișier sau un nume de server.

Opțiuni de intrare pentru parametri

Parametrii pot solicita utilizatorului o valoare de intrare atunci când interogarea este executată sau reîmprospătată, să utilizeze o constantă ca valoare de intrare sau să utilizeze conținutul unei celule specificate ca valoare de intrare.

Parametrii nu solicită intrare. În schimb, puteți să le modificați valoarea utilizând editorul Power Query. Sau, în locul unui parametru bona fide, puteți utiliza o interogare care face referire la o locație externă cu o valoare pe care o puteți edita cu ușurință.

Domeniu de parametri

Un parametru face parte din interogarea pe care o modifică și nu poate fi reutilizată în alte interogări.

Parametrii sunt separați de interogări-o dată creată, puteți adăuga un parametru la interogări, după cum este necesar.

  1. Faceți clic pe > de dateObțineți & transformați datele > obțineți date > din alte surse > din Microsoft Query.

  2. Urmați pașii expertului interogare. În Expertul interogare-Terminare ecran, selectați Vizualizare date sau editare interogare în Microsoft Query , apoi faceți clic pe Terminare. Fereastra Microsoft Query se deschide și afișează interogarea.

  3. Faceți clic pe vizualizare> SQL. În caseta de dialog SQL care apare, găsiți clauza WHERE – o linie care începe cu cuvântul în care, de obicei, la sfârșitul codului SQL. Dacă nu există nicio clauză WHERE, adăugați una tastând unde pe o linie nouă la sfârșitul interogării.

  4. După aceea, tastați numele câmpului, un operator de comparație (=, <, >, LIKE, etc.) și una dintre următoarele:

    • Pentru o solicitare de parametru generic, tastați un semn de întrebare (?). Nu se afișează nicio expresie utilă în solicitarea care apare atunci când se execută interogarea.

      Vizualizarea SQL a MS Query subliniind clauza WHERE

    • Pentru o solicitare de parametru care îi ajută pe utilizatori să furnizeze o intrare validă, tastați o expresie încadrată în paranteze drepte. Expresia se afișează în solicitarea de parametru atunci când se execută interogarea.

      Vizualizarea SQL a MS Query subliniind clauza WHERE

  5. După ce terminați de adăugat condiții cu parametrii la clauza WHERE, faceți clic pe OK pentru a executa interogarea. Excel vă solicită să furnizați o valoare pentru fiecare parametru, apoi Microsoft Query afișează rezultatele.

  6. Când sunteți gata să încărcați datele, închideți fereastra Microsoft Query pentru a returna rezultatele în Excel. Se deschide caseta de dialog Import date.

    Caseta de dialog Import date din Excel

  7. Pentru a revizui parametrii, faceți clic pe Proprietăți. Apoi, în caseta de dialog Proprietăți conexiune, în fila definiție , faceți clic pe parametri.

    Caseta de dialog Proprietăți conexiune

  8. Caseta de dialog parametri afișează parametrii utilizați în interogare. Selectați un parametru sub Nume parametru pentru a revizui sau a modifica modul în care se obține valoarea parametrului. Puteți să modificați solicitarea parametrului, să introduceți o anumită valoare sau să specificați o referință de celulă.

    Caseta de dialog parametru de interogare MS

  9. Faceți clic pe OK pentru a salva modificările și a închide caseta de dialog parametri, apoi, în caseta de dialog Import date, faceți clic pe OK pentru a afișa rezultatele interogării în Excel.

Acum, registrul de lucru are o interogare cu parametri. De fiecare dată când difuzați interogarea sau reîmprospătați conexiunea de date, Excel verifică parametrul pentru a finaliza clauza WHERE a interogării. Dacă parametrul solicită o valoare, Excel afișează caseta de dialog Introduceți valoarea parametrului pentru a colecta intrarea-puteți să tastați o valoare sau să faceți clic pe o celulă care conține valoarea. De asemenea, puteți specifica faptul că valoarea sau referința pe care le furnizați trebuie să fie utilizată întotdeauna și, dacă utilizați o referință de celulă, puteți specifica faptul că Excel ar trebui să reîmprospăteze automat conexiunea de date (de exemplu, să ruleze din nou interogarea) de fiecare dată când se modifică valoarea celulei specificate.

Notă: Acest subiect presupune că știți cum să creați o conexiune la o bază de date Access utilizând Power Query. Pentru mai multe informații, consultați conectarea la o bază de date Access.

Puteți utiliza parametri în mai multe scenarii Power Query decât să filtrați date, orice pas al unei interogări Power Query poate avea parametri. De exemplu, puteți utiliza un parametru pentru a specifica părțile din șirul de conectare din pasul sursă, cum ar fi un nume de fișier.

Parametrii Power Query au nume. Pentru a utiliza un parametru, consultați-l după nume în formulă pentru un pas. De exemplu, să presupunem că doriți să revizuiți datele despre paginile web pe care le mențineți și doriți să filtrați datele după data publicării. Deși puteți utiliza întotdeauna filtrele predefinite din examinarea interogării, utilizând un parametru pentru a furniza o dată pentru filtrare, veți economisi timp și veți avea mai multă flexibilitate. Să parcurgem acest exemplu.

Într-un registru de lucru necompletat, creăm o conexiune la baza de date Access care are înregistrările de trafic web dorite, inclusiv câmpurile care indică când a fost publicată inițial fiecare pagină. Încărcat în Power Query, arată astfel:

Editor Power Query care prezintă date încărcate

Deoarece dorim să filtrăm după dată, modificăm tipul de date al coloanei pe care o utilizăm, FirstPublishDate. Datele despre dată/oră sunt în sursă, dar nu ne pasă ce publicare a avut loc în timpul zilei și trebuie să specificați că este posibil să fie obositoare-așa că o vom schimba la tipul de date dată.

Editor Power Query care afișează rezultate

Apoi, creăm un parametru pentru limitarea rezultatelor la data publicării inițiale a paginii. Faceți clic pe> parametri de pornire > Gestionați parametrii pentru a deschide caseta de dialog parametri.

Caseta de dialog parametri Power Query

Faceți clic pe nou, iar formularul afișează un nou parametru denumit parametru1 fără alte informații.

Modificăm unele proprietăți de parametri:

  • Modificarea numelui în FirstPubD

  • Modificați Descrierea la data la care a fost publicată prima pagină.

  • Modificați tipul la dată , astfel încât parametrul să accepte numai valorile de dată

  • Setați valoarea curentă , astfel încât parametrul să nu filtreze toate rândurile atunci când nu am furnizat input-utilizăm 1/1/2010.

Sfat: Numele și Descrierea trebuie să furnizeze suficient context pentru a ajuta alte persoane să înțeleagă cum și de ce să utilizeze parametrul. Chiar dacă sunteți singura persoană care va utiliza parametrul, este posibil să aveți nevoie de un memento din când în când.

Faceți clic pe OK pentru a crea parametrul și a-l vedea în editorul Power Query.

Editor Power Query afișând un parametru

Acum, parametrul nostru este listat în panoul interogări-îl putem selecta acolo pentru a-l afișa în panoul principal sau putem face clic dreapta pentru mai multe opțiuni. Atunci când este selectat un parametru, putem edita valoarea curentă în panoul principal sau faceți clic pe gestionare parametru pentru a-i modifica celelalte setări.

Acum putem utiliza acest parametru în interogarea inițială. Facem clic pe interogarea inițială din panoul interogări pentru a o afișa. Dorim să utilizăm parametrul pentru a filtra rezultatele pe baza datei primei publicații, așadar, apoi vom selecta coloana FirstPublishDate , faceți clic pe săgeata de filtrare/sortare din marginea din dreapta a titlului de coloană, indicați spre filtre dată, apoi faceți clic pe după....

Editor Power Query afișând un meniu filtru de date

În caseta de dialog Filtrare rânduri, selectăm parametru din lista de opțiuni din filtru.

Caseta de dialog Filtrare rânduri

Introduceți sau selectați o valoare este înlocuită cu o listă de parametri disponibili. Există o singură persoană pe care tocmai am creat-o, FirstPubD.

Caseta de dialog Filtrare rânduri afișând un parametru selectat

Îl selectăm și faceți clic pe OK. Editorul Power Query încarcă interogarea utilizând parametrul nou ca filtru.

Editor Power Query afișând rezultate filtrate

Pentru a testa parametrul, îi modificăm valoarea în 1/1/2018.

Editor Power Query afișând un parametru

Reîmprospătăm interogarea, care acum afișează doar rândurile care au o FirstPublishDate după 1/1/2018.

Editor Power Query afișând rezultate filtrate

Acum avem o interogare care filtrează după dată utilizând un parametru. Pentru a filtra rezultatele după FirstPublishDate, nu mai trebuie să găsim câmpul, faceți clic pe săgeata filtru/sortare, alegeți după... Tipul de filtru și introduceți o valoare de dată-putem să modificăm valoarea FirstPubD și să reîmprospătăm interogarea. Mai mult, putem reutiliza noul parametru, de exemplu, dacă decidem să tragem un alt set de câmpuri din sursa de date originală într-o foaie de lucru nouă, dar încă doriți să includeți FirstPubDate și să-l utilizați pentru a filtra rezultatele.

Parametrii sunt în mod clar foarte utili, dar trebuie să utilizăm în continuare editorul Power Query pentru a modifica valoarea parametrului. Am dori să putem schimba valoarea de filtrare fără a deschide editorul Power Query. Pentru a face acest lucru, vom crea un tabel în foaia de lucru unde se încarcă interogarea și o nouă conexiune Power Query la tabel, apoi utilizați noua interogare pentru a filtra interogarea principală.

În foaia de lucru în care se încarcă interogarea noastră, inseram câteva rânduri deasupra datelor importate. Apoi creăm un tabel Excel cu un rând pentru a reține valoarea parametrilor.

Registru de lucru Excel afișând un tabel de parametri și datele încărcate din Power Query

Pentru a utiliza tabelul nou pentru a filtra interogările, trebuie să vă conectați la acesta în Power Query. Creăm o conexiune la tabel selectând-o, apoi făcând clic pe din tabel/zonă pe fila date . Noua conexiune se deschide și afișează tabelul nou în editorul Power Query.

Date de tabel Excel încărcate în editorul Power Query

Deoarece datele încărcate ca tip de date dată/oră, trebuie să le modificăm în tipul de date dată, astfel încât să se potrivească cu parametrul nostru, astfel încât să faceți clic pe pornire > transformați > tip de date > dată.

Mouse-ul peste comanda tip de date din grupul transformare din fila pornire din panglica Editor Power Query.

De asemenea, ne redenumim interogarea la ceva mai semnificativ decât Tabel2. Pentru a-l face mai explicit pentru ce este, îl numim FirstPubDate.

Editor Power Query cu caseta nume evidențiată

Pentru că vrem să trecem o valoare, nu tabelul propriu-zis, trebuie să detaliăm până la valoarea dată. Pentru a face acest lucru, faceți clic cu butonul din dreapta pe valoarea din datele previzualizate, apoi faceți clic pe detaliere.

Meniul contextual Power Query editor pentru o valoare de câmp

Previzualizarea afișează acum valoarea în locul tabelului.

Editor Power Query afișând o valoare dată unică

Nu este necesar ca datele noii interogări să se încarce nicăieri – datele sale se află deja în foaia de lucru unde o dorim. Avem nevoie doar de conexiune, astfel încât Power Query să poată obține valoarea parametrului. Prin urmare, faceți clic pe fișier> Închidere & încărcare la... pentru a deschide caseta de dialog Import date, apoi selectați Creare conexiune.

Caseta de dialog Import date cu opțiunea Creare conexiune numai selectată

Acum avem o interogare numită "FirstPubDate" care trage o valoare dată unică dintr-un tabel din foaia de lucru, chiar deasupra locului în care se încarcă principalele interogări. Acum trebuie doar să utilizăm această interogare ca parametru pentru filtrarea interogării principale. Prin urmare, deschidem interogarea principală și editați pasul care filtrează rândurile utilizând coloana FirstPublishDate. Extindem bara de formule și selectăm parametrul pe care l-am creat anterior (FirstPubD). Apoi, vom tasta un "a" după FirstPubD -deoarece numele noii interogări începe cu aceleași litere ca parametrul, Power Query îl afișează ca opțiune de selectare.

Bara de formule Power Query editor extinsă

Îl selectăm, apoi faceți clic în exteriorul barei de formule pentru a aplica pasul.

Editor Power Query cu date încărcate

Totul pare corect, astfel încât să ieșim din editorul Power Query și să ne salvăm modificările. Pentru a testa parametrul, pe foaia de lucru raport modificăm valoarea celulei din tabel în partea de sus la 5/4/2019, apoi reîmprospătăm conexiunea pentru a vedea datele filtrate.

Date filtrate în Excel

Noul nostru filtru funcționează! Pentru a salva și a închide registrul de lucru. Acum, oricine utilizează registrul de lucru poate specifica o dată a primei publicații de utilizat ca filtru de interogare-chiar acolo în aceeași foaie de lucru în care se încarcă interogarea.

  1. Faceți clic pe > de dateObțineți & transformați datele > obțineți date > lansare Power Query editor.

  2. În editorul Power Query, faceți clic pe > parametri de pornire > gestionarea parametrilor.

  3. În caseta de dialog parametri, faceți clic pe nou.

  4. Setați următoarele după cum este necesar:

    • Name -aceasta ar trebui să reflecte funcția parametrului, dar să o păstreze cât mai scurtă posibil.

    • Descriere -acest lucru poate conține orice detalii care îi vor ajuta pe utilizatori să utilizeze corect parametrul.

    • Obligatoriu -selectați pentru a face acest parametru să solicite o valoare.

    • Type -aceasta specifică tipul de date necesar pentru parametru.

    • Valori sugerate -dacă doriți, adăugați o listă de valori sau specificați o interogare pentru a oferi sugestii pentru intrare.

    • Valoare implicită -aceasta apare doar dacă valorile sugerate sunt setate la lista de valori și specifică ce element de listă este implicit.

    • Valoarea curentă -în funcție de locul în care utilizați parametrul, dacă aceasta este necompletată, interogarea poate returna niciun rezultat. Dacă este necesar este selectat, valoarea curentă nu poate fi goală.

  5. Faceți clic pe OK pentru a crea parametrul.

  1. Deschideți o interogare în editorul Power Query.

  2. Faceți clic pe săgeata din marginea din dreapta a antetului unei coloane pe care doriți să o utilizați pentru a filtra datele, apoi alegeți un filtru din meniul care apare.

  3. În caseta de dialog Filtrare rânduri, faceți clic pe butonul din partea dreaptă a stării de filtrare, apoi procedați astfel:

    • Pentru a utiliza un parametru existent, faceți clic pe parametru, apoi selectați parametrul dorit din lista care apare în partea dreaptă.

    • Pentru a utiliza un nou parametru, faceți clic pe parametru nou..., apoi Creați un parametru.

  1. În foaia de lucru în care se încarcă interogarea pe care doriți să o filtrați, creați un tabel cu două celule: un antet și o valoare.

  2. Faceți clic pe valoare, apoi faceți clic pe > de dateObțineți & transforma datele > din tabel/zonă.

  3. În editorul Power Query, efectuați ajustări ale conexiunii de tabel (de exemplu, modificarea tipului de date sau a numelui), apoi faceți clic pe pornire > închidere > închidere & încărcare > Închidere & încărcare la....

  4. În caseta de dialog Import date, faceți clic pe Creare conexiune, opțional selectați Adăugare la model de date, apoi faceți clic pe OK.

  5. Deschideți interogarea pe care doriți să o filtrați în editorul Power Query.

  6. Faceți clic pe săgeata din marginea din dreapta a antetului coloanei pe care doriți să o utilizați pentru a filtra datele, apoi alegeți un filtru din meniul care apare.

  7. Alegeți una dintre următoarele variante:

    • Selectați o valoare din lista verticală de valori (acestea provin din datele interogate).

    • Selectați o valoare utilizând butonul de pe marginea din dreapta a stării de filtrare.

  8. Faceți clic pe săgeata din marginea din dreapta a barei de formule pentru a afișa întreaga interogare.

  9. Condiția de filtrare urmează cuvântul fiecare:

    • Numele coloanei filtrate apare în paranteze drepte.

    • Operatorul de comparație urmează imediat numele coloanei.

    • Valoarea filtrului urmează imediat operatorul de comparație și se termină la paranteza de închidere. Selectați această valoare întreagă.

  10. Începeți să tastați numele conexiunii de tabel pe care tocmai ați creat-o, apoi selectați-o din lista care apare.

  11. Faceți clic pe pornire > închidere > Închidere & încărcare.

    Interogarea utilizează acum valoarea din tabelul pe care l-ați creat pentru a filtra rezultatele interogării. Pentru a utiliza o nouă valoare, editați conținutul celulei, apoi reîmprospătați interogarea.

Consultați și

Crearea unei liste verticale

Notă:  Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Ne puteți spune dacă informațiile au fost utile? Aici se află articolul în limba engleză, ca referință.

Aveți nevoie de ajutor suplimentar?

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×