LUCRUL CU BAZE DE DATE EXCEL

LUCRUL CU BAZE DE DATE EXCEL

Datele cu care se lucreaza intr-o foaie de calcul sunt organizate de regula sub forma tabelara. Daca tabelul are o anumita structura si anume, liniile nu au etichete, iar coloanele au cate un nume (eticheta) distinct, atunci spunem ca tabelul respectiv este o lista. Lista este un tabel ce poate fi privit ca o baza de date. n aceasta acceptiune tabelul din fig. 97 este o lista.

Fig. 97 Baza de date

Tabelul de mai sus este desigur in primul rand un tabel bidimensional, impartit in celule ce contin informatii despre angajatii unui oficiu de calcul. Acest tabel are toate proprietatile unei foi de calcul; asupra lui se pot efectua toate prelucrarile permise intr-o foaie de calcul. Daca dorim insa sa facem prelucrari suplimentare, de exemplu cautari si extrageri de informatii dupa anumite criterii, atunci tabelul va trebui sa aibe forma unei liste. In acest caz tabelul va fi interpretat ca fiind o baza de date si avand toate proprietatile ce decurg din aceasta noua ipostaza.

Pentru Excel, o baza de date este o plaja de celule care ocupa doua sau mai multe linii si cel putin o coloana. Crearea si utilizarea unei baze de date permite stocarea si manipularea facila a unui volum mare de date complexe.

Sursele de date organizate sub forma de liste, pe care Excel 7.0 le poate prelucra ca baze de date, pot fi interne sau externe. In acest capitol ne vom ocupa numai de sursele de date interne, deci numai de liste elaborate cu ajutorul aplicatiei Excel 7.0.

Aceste liste pot fi privite din doua unghiuri diferite: fie ca foaie de calcul, fie ca baza de date. O lista privita ca baza de date are urmatoarele particularitati:

a)      a) liniile reprezinta inregistrari ale bazei de date si au o structura uniforma;

b)      b) coloanele reprezinta campurile din care sunt alcatuite inregistrarile;

c)      c) prima linie din lista defineste numele campurilor. Numele unui camp poate avea maximum 256 de caractere;

d)      d) asupra ei se pot efectua toate operatiile permise intr-o baza de date: adaugari, modificari, stergeri, ordonari crescatoare sau descrescatoare dupa unul sau mai multe campuri, cautari si extrageri functie de anumite criterii, imprimari de date structurate;

e)      e) poate ocupa o intreaga foaie de calcul: 16384 de linii pe 256 de coloane.

La versiunile anterioare lui EXCEL 5.0 era necesar ca lista sa fie declarata expres ca fiind baza de date, pentru a fi recunoscuta ca atare. Incepand cu versiunea 5.0, Excel identifica automat o lista ca fiind baza de date in momentul in care utilizatorul solicita efectuarea unor operatii specifice bazelor de date. O baza de date este recunoscuta prin simpla pozitionare a cursorului intr-o celula a listei si apelarea unei comenzi specifice bazelor de date. Comenzile pentru lucrul cu baze de date se regasesc in meniul Data.

Crearea unei baze de date

Pentru a realiza a baza de date cu ajutorul aplicatiei Excel 7.0 este necesar sa se parcurga urmatorii pasi:

Definirea structurii bazei de date, desemnarea informatiilor care vor figura in cadrul ei.

Acest prim pas este foarte important deoarece el reprezinta conceperea bazei de date cand trebuie avut in vedere cel putin un criteriu de baza si anume, sa permita regasirea rapida a tuturor informatiilor de care utilizatorul are nevoie.

MS Excel are mai multe facilitati automate care usureaza gestiunea si analiza datelor dintr-o lista. Pentru a beneficia din plin de aceste facilitati trebuie avute in vedere cateva recomandari:

intr-o foaie de calcul sa existe o singura baza de date;

baza de date sa fie izolata de celelalte date din foaie prin cel putin o coloana;

sub baza de date sa nu se plaseze alte informatii pentru a nu impiedica o eventuala extindere a bazei de date prin adaugarea de noi inregistrari;

primul rand al listei sa contina denumirile de campuri. Excel utilizeaza aceste denumiri pentru a crea rapoarte, a regasi si organiza date;

formatarea primului rand al listei (titlurile coloanelor) sa difere de formatarea celorlalte randuri (inregistrarile).

Introducerea datelor. O lista trebuie sa fie compacta, adica inregistrarile trebuie introduse incepand cu randul doi al listei, de ci imediat dupa randul ce contine titlurile coloanelor.

Introducerea datelor se poate face direct pe foaia de calcul sau prin utilizarea unui formular. Indiferent de modalitate, trebuie evitata inserarea de spatii suplimentare la inceputul unei celule. Acest fenomen are efect negativ in sortarea si cautarea informatiilor.

3. Formatarea datelor. Pentru datele din cadrul inregistrarilor se recomanda:

utilizarea unui format diferit de cel al numelor campurilor (titulurile coloanelor);

utilizarea aceluiasi format pentru toate celulele dintr-o coloana.

Actualizarea bazei de date

Prin actualizarea bazei de date intelegem intretinerea acesteia, adica adaugarea, modificarea sau stergerea de inregistrari. Acest lucru se poate realiza in doua moduri: direct pe lista sau utilizand un formular (predefinit sau personalizat).

Actualizarea direct pe lista.

In aceasta varianta se utilizeaza proprietatile foii de calcul.

a) adaugarea unei inregistrari se face prin tastarea continutului noii inregistrari in randul ce urmeaza ultimei inregistrari din lista. In cazul in care se doreste adaugarea mai multor inregistrari se repeta procedeul;

b) modificarea unei inregistrari presupune pozitionarea cursorului pe inregistrarea respectiva, in campul de modificat. Pentru regasirea inregistrarii corespunzatoare exista trei posibilitati:

b1) se utilizeaza bara de defilare a foii de calcul;

b2) se utilizeaza comanda Edit - Find (figura 98) , caz in care apare o fereastra de dialog care ne invita sa precizam informatia care sa permita pozitionarea pe inregistrarea dorita sau cat mai aporape de ea.

De exemplu, dorim sa facem o modificare privind majorarea salariului operatorilor (vezi figura 97) cu 100.000 lei. Pentru a realiza aceasta actualizare a salariului, se va proceda astfel, conform figurii urmatoare:

se alege comanda Edit - Find Apare fereastra Find ;

se pozitioneaza cursorul de mouse in rubrica Find si se tasteaza cuvantul  operator  ;

se actioneaza butonul Find Next. Va fi selectata prima aparitie a textului aferent functiei de  operator  ;

se deplaseaza cursorul in campul corespunzator salariului si in loc de 400.000 se va scrie noul salariu, 500.000.

Fig. 98 Cautarea in baza de date

pentru celelalte inregistrari se va relua procedeul

se inchide fereastra Find actionand butonul Close.

b3) se alege comanda Edit - Replace Apare o fereastra de dialog care ne invita sa precizam valoarea cautata in vederea modificarii (pozitia Find), precum si noua valoare (pozitia Replace). Presupunand ca doar operatorii au salariul de 400.000 lei, atunci actualizarea se poate realiza astfel (conform figurii 99):

se alege comanda Find - Replace Apare fereastra Replace ;

se pozitioneaza cursorul in casuta Find si se tasteaza valoarea 400000 ;

se pozitioneaza cursorul in caseta Replace si se tasteaza valoarea 500000 ;

se actioneaza butonul Replace All ;

Fig. 99 nlocuirea datelor in baza de date

se inchide fereastra Replace actionand butonul Close.

c) stergerea unei inregistrari. Se procedeaza astfel:

se pozitioneaza cursorul pe antetul de linie corespunzator inregistrarii dorite ; efectul va fi selectarea intregii linii.

se alege comanda Edit; Delete sau se actioneaza tasta < Del> sau conform figurii 100 se selecteaza antetul de linie corespunzator inregistrarii de sters si se actioneaza butonul drept al mouse-lui, iar din meniul contextual astfel generat se alege comanda Delete.

Fig. 100 Stergerea datelor din baza de date

2. Utilizand formularul. Pentru afisarea formularului predefinit se pozitioneaza cursorul intr-o celula a listei si se alege comanda Data - FormVa fi afisata fereastra ce contine formularul si pe care o prezentam in continuare (vezi figura 101). Fereastra formularului de date consta in principal din urmatoarele elemente:

bara de titlu care contine numele foii de calcul in care este plasata lista, precum si butoanele de Help si de inchidere a ferestrei;

Fig. 101 Utilizarea formularului de date

etichetele, realizate prin preluarea numelor de campuri din baza de date;

casetele de text, utilizate pentru introducerea, afisarea, editarea, sau stergerea datelor din campurile bazei de date. Campurile apar in formular in ordinea in care apar si in foaia de calcul, doar ca sunt plasate vertical. Latimea celei mai largi coloane determina lungimea casetelor de text ;

bara de defilare, prevazuta cu posibilitati de parcurgere a bazei de date inregistrare cu inregistrare (prin butoanele aflate la extremitati) sau rapid, utilizand ascensorul ;

indicatorul numarului de inregistrare, care arata atat numarul inregistrarii curente (afisate in formular), cat si numarul total de inregistrari din lista ;

butoanele de comanda, care permit gestionarea datelor din baza prin intermediul formularului ;

n cazul in care lista contine si campuri calculate ( de ex. sporul de vechime) continutul acestora va fi afisat  informativ , deci nu necesita caseta de text, intrucat valoarea respectiva fiind rezultatul unui calcul, nu poate si nu trebuie sa poata fi modificata.

a1) introducerea primei inregistrari presupune ca in foaia de calcul a fost deja precizata structura bazei de date, cu alte cuvinte au fost deja tastate titlurile coloanelor listei, adica numele campurilor bazei de date.

Pentru acesta:

se pozitioneaza cursorul in baza de date ;

se alege comanda Data - Form Apare ferestra formularului ;

se tasteaza valorile corespunzatoare primei inregistrari in casetele de text respective. Pentru deplasarea de la o caseta (camp) la alta se utilizeaza tasta <Tab>. Sfarsitul introducerii inregistrarii se marcheaza tastand <Enter>. Inregistrarea va fi preluata din formular in lista, iar casetele de text vor fi

golite in vederea introducerii unei noi inregistrari.

a2) adaugarea unei inregistrari in baza de date se face astfel:

se pozitioneaza cursorul in lista ;

se alege comanda Data - Form Apare fereastra formularului

se actioneaza butonul New. Casetele de text vor fi golite ;

se introduc datele corespunzatoare.

Daca se doreste adaugarea a inca unei inregistrari se va actiona din nou butonul New.In caz contrar, se va actiona butonul Close.

b) modificarea unei inregistrari. Formularul prezentat mai sus se poate utiliza pentru a modifica valoarea oricarui camp cu exceptia campurilor protejate si a celor calculate.

Pentru a modifica o inregistrare va trebui sa se afiseze mai intai continutul sau in casetele de text. Pentru a afisa inregistrarea dorita putem apela la bara de defilare a formularului sau la butoanele de comanda Find Prev (cauta precedentul) sau Find Next (cauta urmatorul)^[3][3].

In cazul in care se doreste modificarea mai multor inregistrari, trecerea de la o inregistrare la alta se face prin aceleasi metode (bara de defilare/Find Next / Find Prev).

Odata afisate datele corespunzatoare inregistrarii in formular, se efectueaza modificarile. Se observa ca in timpul modificarilor butonul de comanda Restore devine activ. In continuare se verifica vizual corectitudinea modificarilor efectuate. In caz de valabilitate se tasteaza <Enter> pentru ca modificarile sa fie preluate in lista. In caz de invaliditate se actioneaza butonul Restore si modificarile efectuate sunt anulate.

Daca se doreste ca anumite campuri sa fie protejate impotriva modificarilor, se va utiliza comanda Tools - Protection; Protect Sheet Protectia se realizeaza efectiv prin parole.

c) stergerea unei inregistrari utilizand formularul se realizeaza astfel:

se alege comanda Data - Form Apare fereastra formularului ;

se afiseaza inregistrarea dorita prin metodele prezentate la modificare ;

se actioneaza butonul Delete. Inregistrarea va fi definitiv stearsa din lista, nemaiputand fi recuperata. De aceea se cere mare atentie in utilizarea acestui buton de comanda.

II. Consultarea bazei de date

Consultarea unei baze de date consta in cautarea si eventual extragerea inregistrarilor care corespund anumitor criterii ce pot fi simple, complexe, multiple (simultane sau exclusive), calculate. Criteriile reprezinta reguli care permit selectarea unor inregistrari.

In Excel o baza de date se poate consulta in trei moduri:

- cu ajutorul formularelor (mastilor) de selectie ;

- cu ajutorul filtrului automat ;

- cu ajutorul filtrului avansat..

a) Consultarea bazei de date cu ajutorul formularelor de selectie.

Afisarea formularului se face prin comanda Data - Form Exista trei modalitati de a regasi inregistrari utilizand acest procedeu, si anume:

rasfoind inregistrarile din lista prin intermediul butoanelor Find Prev si Find Next ;

rasfoind baza de date utilizand bara de defilare din formular ;

utilizand butonul Criteria(Criterii) pentru a gasi un subset de inregistrari ce satisfac un anumit criteriu.

Intrucat primele doua variante au fost prezentate in subcapitolul  Actualizarea bazei de date , paragraful  modificare , ne vom opri asupra ultimei modalitati.

Prin actionarea butonului Criteria formularul deja cunoscut (figura anterioara) este modificat in sensul golirii automate a casetelor de text ce corespundeau valorilor rubricilor bazei de date. Astfel, zonele destinate campurilor devin goale. In aceste zone (casete de text) utilizatorul va tasta conditiile de cautare numite criterii de comparare.

Exemple:

a1) Dorim vizualizarea prin intermediul formularului a tuturor informatiilor privind angajatul Moise Toma.

Rezolvare :

se pozitioneaza cursorul in baza de date ;

se alege comanda Data - Form

se actioneaza butonul Criteria

in caseta de text Nume se tasteaza Moise

in caseta de text Prenume se tasteaza Toma

se actioneaza butonul Find Next

daca  Moise Toma  este gasit in baza de date, formularul va fi completat automat cu inregistrarea referitoare la informatiile solicitate.

a2) Se doreste consultarea bazei de date in vederea actualizarii salariului operatorilor. Acestia primesc o majorare a salariului cu 100.000 lei.

Rezolvare.

se pozitioneaza cursorul in baza de date

se alege comanda Data - Form

se actioneaza butonul Criteria

in caseta de text Functie se tasteaza operator

se actioneaza butonul Find Next

formularul va afisa continutul primei inregistrari care contine functia operator.

Utilizatorul va tasta in caseta de text Salariu noua valoare si anume 500.000, reprezentand salariul majorat.

Pentru a afisa celelalte inregistrari care satisfac criteriul (functia = operator) se utilizeaza butoanele Find Next si Find Prev.

Precizare : odata specificat un criteriu (simplu sau multiplu) utilizatorul va avea acces doar la inregistrarile care indeplinesc criteriile respective. Deci nu are loc o rasfoire a intregii baze de date, ci doar a subsetului de inregistrari care satisfac conditiile de selectie. Pentru a avea acces din nou la intreaga baza de date se actioneaza butoanele Criteria si apoi Clear.

a3) Care sunt salariatii care au o vechime mai mare de 5 ani ?

Rezolvare :

se pozitioneaza cursorul la inceputul bazei de date ;

se alege comanda Data - Form

se actioneaza butonul Criteria

in caseta de text Vechime se tasteaza : > 5

se actioneaza butonul Find Next

formularul va afisa toate informatiile despre primul salariat cu vechimea > 5 ani.

se actioneaza succesiv butonul Find Next pentru a vedea toti salariatii care indeplinesc acest criteriu.

Prin acest exemplu sunt scoase in evidenta cateva dezavantaje ale metodei:

vizualizarea se face inregistrare cu inregistrare

se afiseaza toate informatiile desi ne intereseaza doar numele si prenumele angajatilor

nu se accepta decat criterii de comparare simple si simultane, cum ar fi:

informatii despre salariatul cu numele Moise si prenumele Toma (a1)

functia= operator (a2) si vechimea > 5 ani (a3) sunt criterii simple .

a4) Care sunt salariatii cu vechimea intre 5 si 10 ani ?

a5) Care sunt angajatii cu salariul intre 500000 si 1000000 ?

a6) Care sunt angajatii cu numele Moise sau Adam ?

Exemplele a4)-a6) solicita formularea unor criterii complexe si nu pot fi rezolvate prin aceasta metoda. Pentru solutionarea lor trebuie folosita una din metodele de utilizeaza comanda Data - Filter si pe care le prezentam in continuare.

b)Consultarea bazei de date cu ajutorul filtrului automat.

Aceasta metoda pare a fi cea mai utilizata. Ea permite afisarea dintr-o data^[7][7] a intregului subset de inregistrari care satisfac anunite criterii de selectie. Excel realizeaza o filtrare in lista, ascunzand inregistrarile care nu satisfac criteriul/criteriile respective, astfel incat lista care ramane vizibila contine doar inregistrarile care intereseaza.

Conform acestei metode procedeul de consultare este urmatorul:

se pozitioneaza cursorul in baza de date ;

se alege comanda Data - Filter; AutoFilter. Comanda AutoFilter determina completarea fiecarui titlu (nume de camp) cu un buton de extensie (figura 102) prin a carui actionare este afisata o lista de varinate ce asista utilizatorul in formularea criteriilor de cautare/extragere.

se definesc criteriile de filtrare pentru fiecare camp, utilizand butoanele de extensie afisate.

Fig. 102 Filtrarea automata bazei de date

Exista urmatoarele posibilitati:

b1) sa se defineasca un criteriu simplu pe un singur camp. In acest caz se va selecta, din lista de variante oferita de butonul de extensie, o valoare.

Exemplu. Care sunt angajatii cu numele Moise ?

Rezolvare.

precizarea criteriului : din lista numelor se selecteaza Moise

rezultatul filtrarii apare in figur 103.

Fig. 103 Rezultatul filtrarii automate (I)

b2) sa se defieasca criterii simple pe mai multe campuri, criterii ce vor trebui sa fie indeplinite simultan.

Exemplu. Care sunt informatiile din baza referitoare la Moise Toma ?

Rezolvare.

precizarea criteriilor : din lista numelor se selecteaza Moise, iar din lista prenumelor Toma

rezultatul filtrarii se vede in figura 104.

Fig. 104 Rezultatul filtrarii automate (II)

b3) sa se defineasca un criteriu complex pe un singur camp. In acest caz din lista de variante oferita de butonul de extensie atasat campului respectiv, vom alege Custom. Apare o fereastra de dialog care ghideaza utilizatorul in precizarea criteriului/criteriilor de cautare si extragere^[8][8], rezultatul fiind afisat in foaia de calcul, prin filtrare direct in lista (figura 105).

Exemple:

Care sunt salariatii cu vechimea intre 5 si 10 ani (inclusiv) ?

precizarea criteriului:

Fig. 105 Fereastra pentru filtrarea automata-Custom

rezultatul filtrarii in figura 106

Fig. 106 Rezultatul filtrarii automate (III)

Se recomanda utilizarea butoanelor de extensie din cadrul ferestrei de dialog. Ele permit spre exemplu selectarea operatorului de comparatie din lista operatorilor posibili.

b4) Definirea criteriilor complexe pentru mai multe campuri.

Exemplu.

Sa se afiseze doar inregistrarile referitoare la angajatii cu functia analist sau programator care au salariul intre 600 de mii si 1milion de lei (inclusiv).

- precizarea criteriilor se face dupa metoda anterioara, retinand ca pentru functie se va alege operatorul Or (sau), iar pentru salariu operatorul And (si)

- rezultatul filtrarii in figura 107.

Fig. 107 Rezultatul filtrarii automate (IV)

Pentru a se reveni la afisarea bazei de date initiale (inlaturarea filtrarii) se poate alege:

varianta All din lista afisata prin actionarea butonului de extensie atasat campului respectiv sau

comanda Data - Filter, Show All.

Pentru a reveni la modul de afisare normal se alege comanda Data - AutoFilter, care dezactiveaza de aceasta data comanda AutoFilter.

Avantajul utilizarii comenzii Autofilter consta in faptul ca afiseaza dintr-o data toate inregistrarile care satisfac criteriile specificate, sub forma unei liste al carui continut poate fi modificat, tiparit, formatat sau chiar sters dintr-o data.

Dezavantajul utilizarii acestei metode de interogare consta in faptul ca nu se pot defini restrictii construite pe baza de formule de calcul simple sau complexe. Alt dezavantaj ar rezulta din faptul ca rezultatul unei interogari nu poate fi plasat decat in zona sursei bazei de date si nu in alta zona a foii de calcul desemnate de utilizator.

c) Consultarea bazelor de date cu ajutorul filtrului avansat

Metoda consultarii bazelor de date cu ajutorul filtrului avansat 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 avansate (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 (sau numita : Baza ) este ilustrata in figura 108.

Fig. 108 Baza de date pentru filtrarea avansata

c1) 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. Chiar 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 sau numai o parte a acestora, 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.

c2) 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 anterior 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 urmatoarele reguli:

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 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 in fig. 109-110 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 .

Fig. 109 Zona de criterii (I)

- al doilea exemplu (fig. 110) vizeaza construirea unui camp de criterii pe anumite rubrici ale bazei de date (Localitate, Platit, Majorari). Campul contine

Fig. 110 Zona de criterii (II)

o linie (sub titlurile rubricilor) pentru precizarea restrictiilor legate prin operatorul logic  SI , si o linie pentru precizarea operatorului logic  SAU . O interogare bazata pe un astfel de camp de criterii ar avea urmatorul enunt :  care sunt clientii neplatitori din Bucuresti sau Ploiesti care au majorari de intarziere la plata facturilor de peste 000.000 lei .

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.

De exemplu, in figura 111 este prezentat un camp de criterii, pentru urmatoarea interogare :  sa se selecteze toti clientii neplatitori (Platit=NU) din Bucuresti (Localitate=Bucuresti), in primul semestru al anului 1998 (Data facturii >01/01/98 SI Data facturii<=06/30/98), care au penalitati intre 500.000 lei si 000.000 lei (Majorari>500000 SI Majorari<1000000) .

Fig. 111 Zona de criterii (III)

Criteriile calculate sunt bazate pe formule care returneaza un rezultat logic (TRUE sau FALSE). In acest caz prima linie a zonei de criterii este goala, iar cea de a doua va cuprinde formula/formulele respective, dar va afisa rezultatul 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 112 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).

Fig. 112 Zona de criterii (IV)

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 acestoara, s-ar introduce o formula ce calcul a mediei pentru rubrica Valoare factura.

Figura 113 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 114.

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.

Fig. 113 Zona de criterii cu functii predefinite

Fig. 114 Zona de criterii cu functii pentru baze de date

c3) Definirea zonei 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.

Fig. 115 Comenzi pentru filtrare avansata

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.

c4) 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.115) 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.

Concomitent cu operatia de selectare, in caseta de text respectiva vor fi preluate^[9][9] coordonatele absolute ale plajei respective de celule (figura 116). 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 116.

Fig. 116 Rezultatul filtrarii avansate

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. n 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.

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 facuta 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 117.

Fig. 117 Comenzi pentru filtrare avansata (II)

Fig. 118 Rezultatul filtrarii avansate

Lansarea cautarii si extragerii se declanseaza prin actionarea butonului OK.

Rezultatele interogarii sunt prezentate in figura 118

Prin selectarea casetei de selectare 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 di figura 119 ilustreaza construirea campurilor de criterii si rezultate pentru interogarea :  care sunt clientii carora firma le-a emis facturi de la inceputul anului pana azi .

Fig. 119 Date si criterii pentru filtrare avansata

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.

Sortarea bazelor de date

Realizarea sortarii unei baze de date se face cu ajutorul comenzii Sort din meniul Data.

Aceasta comanda permite ordonarea crescatoare (Ascending) sau descrescatoare (Descending) a inregistrarilor din baza de date dupa maximim trei campuri (simultan). Alegerea comenzii Data - Sort determina aparitia pe ecran a casetei de dialog Sort (fig. 120)

Folosind butoanele de extensie din dreptul fiecarei casete de text utilizatorul va putea preciza campurile dupa care sa se faca sortarea, cat si ordinea sortarii pentru fiecare camp.

Din punct de vedere conceptual, sortarea dupa mai multe campuri implica precizarea mai multor niveluri de regrupare a datelor. Astfel cheile de sortare se precizeaza in ordinea nivelului de consistenta a datelor : primul nivel de sortare trebuie sa contina elementele celui de-al doilea nivel, iar al doilea nivel trebuie sa contina elementele celui de-al treilea nivel, s.a.m.d. Altfel spus ordinea sortarii trebuie sa fie de la domeniul cel mai cuprinzator, la domeniul cel mai putin cuprinzator.

Daca se doreste ordonarea doar dupa un camp, va fi completata doar prima caseta de text, restul ramanand goale.

Operatiunea de sortare incepe prin selectarea bazei de date sau prin pozitionarea cursorului pe prima celula din baza, continua prin activarea comenzii Data - Sort si definirea cheilor de sortare concomitent cu precizarea ordinii in care se va face operatia de sortare si se incheie prin validarea operatiei cu butonul OK.

Exemplu. Sa se reordoneze baza de date crecator dupa localitatea clientului, apoi dupa data emiterii facturii si in final dupa numele clientului.

Precizarea criteriilor de sortare este ilustrata in figura 120.

Rezultatul operatiunii de sortare este prezentat in figura 12

Fig. 120 Caseta de dialog pentru sortare

Fig. 121 Rezultatul sortarii