Interogarea bazelor de date cu ajutorul filtrului elaborat

Interogarea bazelor de date cu ajutorul filtrului elaborat

Metoda consultarii bazelor de date cu ajutorul filtrului elaborat presupune parcurgerea urmatoarelor etape:

definirea unei zone de criterii ;

definirea criteriilor ;

definirea optionala a unei zone de extragere ;

lansarea cautarii si extragerea propriu-zisa din baza de date a inregistrarilor ce corespund criteriilor de cautare si extragere definite in zona de criterii.

Interogarile bazate pe procedeul filtrarii elaborate (Advanced Filter) sunt prezentate pe exemplul unei baze de date pentru evidenta facturilor emise catre clienti si urmarirea incasarii facturilor.

Baza de date declarata pe coordonatele A5:K19 (numita : «Baza ») este ilustrata in figura urmatoare :

Zona de criterii poate fi definita in aceeasi foaie de calcul (in care se afla baza de date) sau intr-o alta foaie. Adesea, este necesar ca zonele de criterii odata definite sa fie pastrate in vederea unor cautari/extrageri repetate. Se recomanda ca zonele de criterii sa fie definite separat, intr-o alta foaie de calcul, pentru a nu sufoca foaia de calcul ce contine baza de date, cu atat mai mult cu cat datele rezultate in urma extragerii vor fi afisate obligatoriu in aceeasi foaie de calcul in care se afla si sursa de date.

Zona de criterii este compusa dintr-o linie ce contine numele campurilor ce servesc la formularea criteriilor si una sau mai multe linii pentru definirea acestora.

Campul pe care se definesc criteriile, poate contine in prima linie a sa, totalitatea rubricilor bazei de date, numai o parte a acestora sau nici una, dupa cum diferitele rubrici participa la consultarea bazei de date.

Precizare : prima linie a zonei de criterii se va obtine prin copierea numelor respective de campuri din lista (din linia de titluri a rubricilor bazei de date). In caz contrar exista riscul aparitiei unei incompatibilitati intre denumirile din lista si cele din zona de criterii.

Definirea criteriilor. Se pot defini criterii de comparatie, criterii multiple si criterii calculate.

Criteriile de comparatie se realizeaza cu ajutorul operatorilor de comparatie si anume: >, <, >=, <=, = , urmati de o valoare. In precizarea valorilor se pot utiliza caracterele generice * sau ? .

De exemplu, utilizand baza de date anterioara definita pe coordonatele A5 :K19 :

pentru a afla care sunt clientii al caror nume incepe cu litera G vom utiliza sintaxa G* ;

pentru a afla care sunt clientii al caror nume incepe cu litera B si se termina cu litera n vom utiliza sintaxa B*n ;

pentru a afla care sunt clientii a caror localitate de domiciliu se termina in literele « sti » (Bucuresti, Ploiesti, Pitesti, etc.) vom utiliza sintaxa *sti ;

pentru a afla care sunt clientii al caror nume este format din cinci litere, dintre care prima este g si ultima este o (ex : GriRo, GenRo, Gesto) vom utiliza sintaxa g???o, s.a.m.d.

Criteriile multiple se obtin prin combinarea criteriilor utilizand operatorii logici (SI; SAU). Aceste criterii respecta urmatoarea regula:

daca valorile diferitelor criterii sunt precizate in zona de criterii pe aceeasi linie, ele trebuie indeplinite simultan, fiind considerate legate prin operatorul logic SI, constituind astfel un criteriu multiplu.

daca valorile diferitelor criterii sunt precizate in zona de criterii pe linii diferite, se considera ca ele sunt legate prin operatorul logic SAU.

Prezentam mai jos doua exemple de construire a campurilor de criterii multiple :

- Primul exemplu vizeaza construirea unui camp de criterii pe totalitatea rubricilor bazei de date. Campul contine o linie (sub titlurile rubricilor) pentru precizarea restrictiilor legate prin operatorul logic « SI », si doua linii pentru precizarea operatorului logic « SAU ». O interogare bazata pe un astfel de camp de criterii ar avea urmatorul enunt : « care sunt clientii platitori din Bucuresti si care sunt clientii neplatitori din Iasi sau Ploiesti ».

Al doilea exemplu vizeaza construirea unui camp de criterii pe anumite rubrici ale bazei de date (Localitate, Platit, Majorari). Campul contine o linie (sub titlurile rubricilor) pentru precizarea restrictiilor legate prin operatorul logic « SI », si o linie pentru precizarea operatorului logic « SAU ».

Criteriile multiple evidentiaza in esenta faptul ca restrictiile pe care se construieste interogarea pot fi definite pe mai multe rubrici.

O particularitate a acestor criterii este aceea ca se pot preciza restrictii « intre doua limite (sau borne) » : o limita maxima pe care valorile rubricii trebuie sa le indeplineasca si o limita minima.

Pentru a se construi criterii ce sa raspunda la astfel de interogari intre doua intervale, rubrica (rubricile) care va contine respectivele intervale se dubleaza. Astfel, o rubrica va avea precizata o restrictie ce semnifica borna minima, iar a doua rubrica (dublata in campul de criterii) va contine borna maxima.

500.000 lei si 1.000.000 lei (Majorari>500000 SI Majorari<1000000) ».

Criteriile calculate sunt bazate pe formule ce returneaza un rezultat logic (TRUE sau FALSE). In acest caz prima linie a zonei de criterii (ce contine numele campului pe care se face selectia) este goala, iar cea de a doua va cuprinde formula/formulele respective, dar va afisa un rezultat logic al evaluarii formulei.

Sintaxa unor astfel de criterii calculate este urmatoarea :

semnul  = (egal) pentru a se specifica faptul ca este vorba de o formula ;

adresa primei celule din domeniul rubricii bazei de date ;

un operator logic de comparatie (=, >, <, >=, <=, AND, OR, NOT);

un argument de comparatie care poate fi :

adresa unei celule (ex. : =D2>B2);

o expresie (ex . : =D2>B2*22%);

functie predefinita (ex : =D2>AVERAGE(D2 :D32).

Figura urmatoare ilustreaza urmatoarea formulare pentru selectarea clientilor neplatitori din Bucuresti pe ultimele 6 luni (celula E42) si care au penalitati cuprinse intre transele de 25% din valoare si 75% din valoare (celulele F42 si G42).

Un alt exemplu de interogare bazata pe o rubrica calculata, vizeaza utilizarea functiilor predefinite.

Astfel, daca s-ar dori selectarea clientilor platitori (Platit=DA), care au achitat valoarea facturilor peste media valorica a acestora, s-ar introduce o formula de calcul a mediei pentru rubrica Valoare factura.

Figura urmatoare prezinta campul de criterii pentru o astfel de interogare :

Functiile tip « baze de date » prezentate in capitolul referitor la functiile Excel, opereaza si ele cu criterii, fapt relevat si de figura urmatoare :

De exemplu, daca s-ar calcula suma facturilor neachitare de clientii din Bucuresti pe ultimul an, formula DSUM ar avea ca ultim argument, un camp de criterii definit pe coordonatele B87 :D88.

Zona de extragere

Zona de extragere trebuie definita obligatoriu in foaia de calcul ce contine baza de date. Este de preferat ca zonele de extragere sa fie plasate lateral sau in jos, in raport cu sursa de date.

Zona de extragere este compusa dintr-o linie ce contine numele campurilor (titlul rubricilor) din baza de date despre care se doreste a se obtine informatii.

Preluarea acestor nume in prima linie a zonei de extragere se poate face prin copierea numelor respective din antetul listei.

Lansarea cautarii si extragerea propriu-zisa

Aceasta operatie se realizeaza prin apelarea comenzii Data - Filter - Advanced Filter Pe ecran va fi afisata caseta de dialog Advanced Filter - fig) care invita utilizatorul sa precizeze plaja de celule care reprezinta baza de date (List Range) si plaja care reprezinta zona de criterii (Criteria Range).

Utilizatorul va activa pe rand fiecare din casetele de text, selectand de fiecare data in foaia de calcul plaja respectiva de celule.

Reamintim ca definirea zonei de extragere este optionala. Astfel, daca se doreste vizualizarea rezultatului filtrarii, direct pe sursa de date (cand inregistrarile care nu satisfac criteriile din zona de criterii vor fi ascunse, ramanand vizibile doar inregistrarile care satisfac criteriile respective) se va actiona butonul OK. Efectul extragerii pentru o interogare de genul « care sunt clientii platitori din Bucuresti si care sunt clientii neplatitori din Iasi sau Ploiesti », este prezentat in figura urmatoare :

Revenirea afisarii bazei de date la forma initiala (care includea totalitatea inregistrarilor) se face prin comanda Data - Filter - Show All

In cazul in care a fost definita o zona de extragere atunci utilizatorul va completa caseta de dialog Advanced Filter (figura urmatoare) si cu coordonatele absolute ale acestei zone (in caseta de text Copy to).

Campul de rezultate poate fi generat ca avand toate rubricile bazei de date (si atunci se plaseaza cursorul pe prima celula a campului de rezultate) sau poate fi generat ca avand numai anumite rubrici. In acest ultim caz, respectivele rubrici se vor edita pe prima linie a campului de rezultate, iar selectia campului va incepe de la coordonatele rubricilor definite.

Precizare. Intrucat caseta de text Copy to nu este activa implicit, este necesar ca pentru a o activa sa se selecteze celalalt mod de actionare si anume Copy to another location.

Reluam exemplul precedent de interogare, ce va fi operationala prin extragere, cu deosebirea ca se vor afisa clientii, adresele acestora, localitatile din care provin, numerele de factura si sumele datorate.

Caseta de dialog Advanced Filter, corespunzatoare procedeului de extragere, este prezentata in figura urmatoare :

Lansarea cautarii si extragerii se declanseaza prin actionarea butonului OK.

Rezultatele interogarii sunt prezentate in figura urmatoare : 

Prin selectarea casetei de validare Unique Records Only nu vor fi preluate in zona de rezultate inregistrarile duble (care au valori identice, corespunzator restrictiilor impuse in campul de criterii).

Exemplul urmator ilustreaza construirea campurilor de criterii si rezultate pentru interogarea : « care sunt clientii carora firma le-a emis facturi de la inceputul anului pana azi ».

Selectie unica a inregistrarilor

Camp de rezultate

Camp de criterii

Se impun cateva precizari privind caracteristicile extragerii:

inregistrarile extrase contin numai valori (formulele de calcul sunt extrase ca valori) ;

inregistrarile extrase nu sunt legate de sursa de date; eventualele modificari in baza de date nu se vor reflecta in zona inregistrarilor deja extrase ;

dimensiunea zonei de extragere este ajustata automat la numarul de inregistrari extrase. De aceea cand se precizeaza coordonatele zonei de extragere (caseta de text Copy to din fereastra Advanced Filter) se selecteaza doar o singura linie si anume aceea care contine numele campurilor.

lista (subsetul de inregistrari) afisata in zona de extragere poate fi prelucrata ca orice lista, poate fi stocata, sintetizata, imprimata.