Excel5-Baze de Date

8/14/2019 Excel5-Baze de Date

1/23

1.5 LUCRUL CU BAZE DE DATE EXCEL

Datele cu care se lucreaz ntr-o foaie de calcul sunt organizate de regul sub

form tabelar. Dac tabelul are o anumit structur i anume, liniile nu au etichete, iar

coloanele au cte un nume (etichet) distinct, atunci spunem c tabelul respectiv este o

list. Lista este un tabel ce poate fi privit ca o baz de date. n aceasta accepiune tabelul

din fig. 1.97 este o list.

Fig. 1.97 Baza de date

Tabelul de mai sus este desigur n primul rnd un tabel bidimensional, mprit n

celule ce conin informaii despre angajaii unui oficiu de calcul. Acest tabel are toate

proprietile unei foi de calcul; asupra lui se pot efectua toate prelucrrile permise ntr-o

foaie de calcul. Dac dorim ns s facem prelucrri suplimentare, de exemplu cutri i

extrageri de informaii dup anumite criterii, atunci tabelul va trebui s aibe forma unei

liste. n acest caz tabelul va fi interpretat ca fiind o baz de date i avnd toate

proprietile ce decurg din aceast nou ipostaz.
http://www.ase.ro/ciedd/birotica/CAP16A.htmhttp://www.ase.ro/ciedd/birotica/excel-cuprins.htmhttp://www.ase.ro/ciedd/birotica/CAP14.htm


2/23

Pentru Excel, o baza de date este o plaj de celule care ocup dou sau mai multe

linii i cel puin o coloan. Crearea i utilizarea unei baze de date permite stocarea i

manipularea facil a unui volum mare de date complexe.

Sursele de date organizate sub form de liste, pe care Excel 7.0 le poate prelucra

ca baze de date, pot fi interne sau externe.n acest capitol ne vom ocupa numai de sursele

de date interne, deci numai de liste elaborate cu ajutorul aplicaiei Excel 7.0.

Aceste liste pot fi privite din dou unghiuri diferite: fie ca foaie de calcul, fie ca baz

de date. O list privit ca baz de date are urmtoarele particulariti:

a) a) liniile reprezintnregistrri ale bazei de date i au o structur uniform;

b) b) coloanele reprezint cmpurile din care sunt alctuitenregistrrile;

c) c) prima linie din list definete numele cmpurilor. Numele unui cmp poate avea

maximum 256 de caractere;

d) d) asupra ei se pot efectua toate operaiile permise ntr-o baz de date: adugri,

modificri, tergeri, ordonri cresctoare sau descresctoare dup unul sau mai multe

cmpuri, cutri i extrageri functie de anumite criterii, imprimri de date structurate;

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

La versiunile anterioare lui EXCEL 5.0 era necesar ca lista s fie declarat expres ca

fiind baz de date, pentru a fi recunoscut ca atare. ncepnd cu versiunea 5.0, Excel

identific automat o list ca fiind baz de date n momentul n care utilizatorul solicit

efectuarea unor operaii specifice bazelor de date. O baz de date este recunoscut prin

simpla poziionare a cursorului ntr-o celul a listei i apelarea unei comenzi specifice

bazelor de date. Comenzile pentru lucrul cu baze de date se regsesc n meniul Data.

Crearea unei baze de date

Pentru a realiza a baz de date cu ajutorul aplicaiei Excel 7.0 este necesar s se

parcurg urmtorii pai:

1. 1. Definirea structurii bazei de date, desemnarea informaiilor care vor figura n

cadrul ei.

Acest prim pas este foarte important deoarece el reprezint conceperea bazei de date cnd

trebuie avut n vedere cel puin un criteriu de baz i anume, s permit regsirea rapid a

tuturor informaiilor de care utilizatorul are nevoie.

MS Excel are mai multe faciliti automate care uureaz gestiunea i analiza datelor

dintr-o list. Pentru a beneficia din plin de aceste faciliti trebuie avute n vedere ctevarecomandri:

- - ntr-o foaie de calcul s existe o singur baz de date;

- - baza de date s fie izolat de celelalte date din foaie prin cel puin o coloan;

- - sub baza de date s nu se plaseze alte informaii pentru a nu mpiedica o

eventual extindere a bazei de date prin adaugarea de noi nregistrri;


3/23

- - primul rnd al listei s conin denumirile de cmpuri. Excel utilizeaz aceste

denumiri pentru a crea rapoarte, a regsi i organiza date;

- - formatarea primului rnd al listei (titlurile coloanelor) s difere de formatarea

celorlalte rnduri (nregistrrile).

2. 2. Introducerea datelor. O list

trebuie s

fie compact

, adic

nregistr

rile trebuieintroduse ncepnd cu rndul doi al listei, de ci imediat dup rndul ce conine

titlurile coloanelor.

Introducerea datelor se poate face direct pe foaia de calcul sau prin utilizarea unui

formular. Indiferent de modalitate, trebuie evitat inserarea de spaii suplimentare la

nceputul unei celule. Acest fenomen are efect negativ n sortarea i cutarea

informaiilor.

3. Formatarea datelor. Pentru datele din cadrul nregistrrilor se recomand:

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

- - utilizarea aceluiai format pentru toate celulele dintr-o coloan.

Actualizarea bazei de date

Prin actualizarea bazei de date nelegem ntreinerea acesteia, adic adugarea,

modificarea sau tergerea de nregistrri. Acest lucru se poate realiza n dou moduri:

direct pe list sau utiliznd un formular (predefinit sau personalizat).

1.Actualizarea direct pe list.

n aceast variant se utilizeaz proprietile foii de calcul.

a) adugarea unei nregistrri se face prin tastarea coninutului noii nregistrari n rndul

ce urmeaz ultimei nregistrri din list. n cazul n care se dorete adugarea mai multor

nregistrri se repet procedeul;

b) modificarea unei nregistrari presupune poziionarea cursorului pe nregistrarea

respectiv, n cmpul de modificat. Pentru regsirea nregistrrii corespunztoare exist

trei posibiliti:

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

b2) se utilizeaz comanda Edit -Find (figura 1.98) , caz n care apare o fereastr

de dialog care ne invit s precizm informaia care s permit poziionarea pe

nregistrarea dorit sau ct mai aporape de ea.

De exemplu, dorim s facem o modificare privind majorarea salariului operatorilor

(vezi figura 1.97) cu 100.000 lei. Pentru a realiza aceast actualizare a salariului, se va

proceda astfel, conform figurii urmtoare:

- - se alege comanda Edit - Find... Apare fereastra Find ;

- - se poziioneaz cursorul de mouse n rubrica Find i se tasteaz cuvntul

operator ;


4/23

- - se acioneaz butonul FindNext. Va fi selectat prima apariie a textului aferent

funciei de operator ;

- - se deplaseaz cursorul n cmpul corespunztor salariului i n loc de 400.000 se

va scrie noul salariu, 500.000.

Fig. 1.98 Cutarea n baza de date- pentru celelaltenregistrri se va relua procedeul

- - senchide fereastra Find actionnd butonul Close.

b3) se alege comanda Edit - Replace... Apare o fereastr de dialog care ne invit s

precizm valoarea cutatn vederea modificrii (poziia Find), precum i noua valoare

(poziia Replace). Presupunnd c doar operatorii au salariul de 400.000 lei, atunci

actualizarea se poate realiza astfel (conform figurii 1.99):

- - se alege comanda Find -Replace... Apare fereastra Replace ;

- - se poziioneaz cursoruln csua Find i se tasteaz valoarea 400000 ;

- - se poziioneaz cursoruln caseta Replace i se tasteaz valoarea 500000 ;

- - se acioneaz butonul Replace All ;

Fig. 1.99 nlocuirea datelor n baza de date

- - senchide fereastra Replace acionnd butonul Close.

c) tergerea unei nregistrri. Se procedeaz astfel:

- - se poziioneaz cursorul pe antetul de linie corespunztor nregistrrii dorite ;

efectul va fi selectarea ntregii linii.


5/23

- - se alege comanda Edit; Delete... sau se acioneaz tasta < Del> sau conform

figurii 1.100 se selecteaz antetul de linie corespunztor nregistrrii de ters i se

acioneaz butonul drept al mouse-lui, iar din meniul contextual astfel generat se

alege comanda Delete.

Fig. 1.100 tergerea datelor din baza de date

2. Utiliznd formularul. Pentru afiarea formularului predefinit se poziioneaz cursorulntr-o celul a listei i se alege comanda Data - Form...Va fi afiat fereastra ce conine

formularul i pe care o prezentm n continuare (vezi figura 1.101). Fereastra formularului

de date constn principal din urmtoarele elemente:

- - bara de titlu care conine numele foii de calcul n care este plasat lista, precum i

butoanele de Help i de nchidere a ferestrei;

Fig. 1.101 Utilizarea formularului de date

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

- - casetele de text, utilizate pentru introducerea, afiarea, editarea, sau tergerea

datelor din cmpurile bazei de date. Cmpurile apar n formular n ordinea n care

apar i n foaia de calcul, doar ca sunt plasate vertical. Limea celei mai largi coloane

determin lungimea casetelor de text ;


6/23

- - bara de defilare, prevzut cu posibiliti de parcurgere a bazei de date

nregistrare cu nregistrare (prin butoanele aflate la extremiti) sau rapid, utiliznd

ascensorul ;

- - indicatorul numrului de nregistrare, care arat att numrul nregistrrii curente

(afiate

n formular), c

t i num

rul total de

nregistr

ri din list

;- - butoanele de comand, care permit gestionarea datelor din baza prin intermediul

formularului ;

n cazul n care lista conine i cmpuri calculate ( de ex. sporul de vechime)1[1]

coninutul2[2] acestora va fi afiat informativ , deci nu necesit caset de text, ntruct

valoarea respectiv fiind rezultatul unui calcul, nu poate i nu trebuie s poat fi

modificat.

a1) introducerea primei nregistrri presupune c n foaia de calcul a fost deja

precizat structura bazei de date, cu alte cuvinte au fost deja tastate titlurile coloanelor

listei, adic numele cmpurilor bazei de date.

Pentru acesta:

- - se poziioneaz cursoruln baza de date ;

- - se alege comanda Data - Form... Apare ferestra formularului ;

- - se tasteaz valorile corespunztoare primei nregistrri n casetele de text

respective. Pentru deplasarea de la o caset (cmp) la alta se utilizeaza tasta .

Sfritul introducerii nregistrrii se marcheaz tastnd . nregistrarea va fi

preluat din formular n list, iar casetele de text vor fi

- - golite n vederea introducerii unei noi nregistrri.

a2) adugarea unei nregistrri n baza de date se face astfel:

- - se poziioneaz cursoruln list ;

- - se alege comanda Data - Form... Apare fereastra formularului

- - se acioneaz butonul New. Casetele de text vor fi golite ;

- - se introduc datele corespunztoare.

Dac se dorete adugarea a nc unei nregistrri se va aciona din nou butonul

New.n caz contrar, se va actiona butonul Close.

b) modificarea unei nregistrri. Formularul prezentat mai sus se poate utiliza pentru a

modifica valoarea oricrui cmp cu excepia cmpurilor protejate i a celor calculate.

1[1]Pentru calcularea sporului de vechime am considerat urmtorul algoritm:

- pentru o vechime sub 3 ani, nu se acord spor

- pentru o vechime ntre 3 si 5 ani, sporul reprezint 7% din salariu

- pentru o vechime ntre 5 si 10 ani sporul este de 10% din salariu

- pentru o vechime mai mare de 10 ani sporul este de 15% din salariu2[2]

Ne referim la informaia care reprezint rezultatul calculului i nu formula de calcul


7/23

Pentru a modifica o nregistrare va trebui s se afieze mai nti coninutul su n

casetele de text. Pentru a afia nregistrarea dorit putem apela la bara de defilare a

formularului sau la butoanele de comandFind Prev (caut precedentul) sau Find Next

(caut urmtorul)3[3].

n cazul n care se dorete modificarea mai multor nregistrri, trecerea de la onregistrare la alta se face prin aceleai metode (bara de defilare/FindNext/FindPrev).

Odat afiate datele corespunztoare nregistrrii n formular, se efectueaz

modificrile. Se observ cn timpul modificrilor butonul de comandRestore devine

activ. n continuare se verific vizual corectitudinea modificrilor efectuate. n caz de

valabilitate se tasteaz pentru ca modificrile s fie preluate n list. n caz de

invaliditate se acioneaz butonul Restore i modificrile efectuate sunt anulate.

Dac se dorete ca anumite cmpuri s fie protejate mpotriva modificrilor, se va

utiliza comanda Tools - Protection; ProtectSheet... Protecia se realizeaz efectiv prin

parole.c) tergerea unei nregistrri utiliznd formularul se realizeaz astfel:

- - se alege comanda Data - Form... Apare fereastra formularului ;

- - se afieaznregistrarea dorit prin metodele prezentate la modificare ;

- - se acioneaz butonul Delete. nregistrarea va fi definitiv tears din list,

nemaiputnd fi recuperat. De aceea se cere mare atenie n utilizarea acestui buton

de comand.

II. Consultarea bazei de date

Consultarea unei baze de date const n cutarea i eventual extragerea

nregistrrilor care corespund anumitor criterii ce pot fi simple, complexe, multiple

(simultane sau exclusive), calculate. Criteriile reprezint reguli care permit selectarea

unor nregistrri.

n Excel o baz de date se poate consulta n trei moduri:

- cu ajutorulformularelor (mtilor) de selecie ;

- cu ajutorul filtrului automat ;

- cu ajutorulfiltrului avansat..

a) Consultarea bazei de date cu ajutorul formularelor de selecie.Afiarea formularului se face prin comanda Data - Form... Exist trei modaliti de a

regsi nregistrri utiliznd acest procedeu, i anume:

- - rsfoind nregistrrile din list prin intermediul butoanelor Find Prev i Find

Next ;

3[3] Pentru o regsire rapid a nregistrri/nregistrrilor de modificat se poate formula un criteriu de cautare.

Aceast metod este prezentatn capitolul Consultarea bazei de date.


8/23

- - rsfoind baza de date utiliznd bara de defilare din formular ;

- - utiliznd butonul Criteria(Criterii) pentru a gsi un subset de nregistrri ce

satisfac un anumit criteriu.

ntruct primele dou variante au fost prezentate n subcapitolul Actualizarea bazei de

date , paragraful modificare , ne vom opri asupra ultimei modaliti.Prin acionarea butonului Criteria formularul deja cunoscut (figura anterioar) este

modificat n sensul golirii automate a casetelor de text ce corespundeau valorilor

rubricilor bazei de date. Astfel, zonele destinate cmpurilor devin goale. n aceste zone

(casete de text) utilizatorul va tasta condiiile de cutare numite criterii de comparare.

Exemple:

a1) Dorim vizualizarea prin intermediul formularului a tuturor informaiilor

privind angajatul Moise Toma.

Rezolvare :- - se poziioneaz cursoruln baza de date ;

- - se alege comanda Data - Form...

- - se acioneaz butonul Criteria

- - n caseta de text Nume se tasteaz Moise

- - n caseta de text Prenume se tasteaz Toma

- - se actioneaz butonul Find Next4[4]

- - dac Moise Toma este gsit n baza de date, formularul va fi completat

automat cu nregistrarea referitoare la informaiile solicitate.

a2) Se dorete consultarea bazei de date n vederea actualizrii salariului operatorilor.Acetia primesc o majorare a salariului cu 100.000 lei.

Rezolvare.

- - se pozitioneaz cursoruln baza de date



- - n caseta de text Funcie se tasteaz operator

- - se acioneaz butonul Find Next5[5]

- - formularul va afia coninutul primei nregistrri care conine funcia operator.

Utilizatorul va tasta n caseta de text Salariu noua valoare i anume 500.000,reprezentnd salariul majorat.

4[4]ntruct cutarea are loc ncepnd cu poziia curent a cursorului n baza de date se recomand

poziionarea acestuia la nceputul sursei de date.5[5]ntruct cutarea are loc ncepnd cu poziia curent a cursorului n baza de date se recomand

poziionarea acestuia la nceputul sursei de date.


9/23

Pentru a afia celelalte nregistrri care satisfac criteriul (funcia = operator) se utilizeaza

butoanele Find Next i Find Prev.

Precizare : odat specificat un criteriu (simplu sau multiplu) utilizatorul va avea

acces doar la nregistrrile care ndeplinesc criteriile respective. Deci nu are loc o rsfoire

a ntregii baze de date, ci doar a subsetului de nregistrri care satisfac conditiile deselecie. Pentru a avea acces din nou la ntreaga baz de date se acioneaz butoanele

Criteria i apoi Clear.

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

Rezolvare :

- - se poziioneaz cursorul la nceputul bazei de date ;



- - n caseta de text Vechime se tasteaz : > 5

-- se acioneaz butonul Find Next

6[6]

- - formularul va afia toate informaiile despre primul salariat cu vechimea > 5 ani.

- - se acioneaz succesiv butonul Find Next pentru a vedea toi salariaii care

ndeplinesc acest criteriu.

Prin acest exemplu sunt scoase n eviden cteva dezavantaje ale metodei:

- - vizualizarea se face nregistrare cu nregistrare

- - se afieaz toate informaiile dei ne intereseaz doar numele i prenumele

angajailor

- - nu se accept dect criterii de comparare simple i simultane, cum ar fi:

-- informaii despre salariatul cu numele Moise iprenumele Toma (a1)

- - funcia= operator (a2) i vechimea > 5 ani (a3) sunt criterii simple .

a4) Care sunt salariaii cu vechimea ntre 5 i 10 ani ?

a5) Care sunt angajaii cu salariul ntre 500000 i 1000000 ?

a6) Care sunt angajaii cu numele Moise sau Adam ?

Exemplele a4)-a6) solicit formularea unor criterii complexe i nu pot fi rezolvate

prin aceast metod. Pentru soluionarea lor trebuie folosit una din metodele de

utilizeaz comanda Data - Filter... i pe care le prezentm n continuare.

b)Consultarea bazei de date cu ajutorul filtrului automat.

Aceast

metod

pare a fi cea mai utilizat

. Ea permite afiarea dintr-o dat

7[7]

antregului subset de inregistrri care satisfac anunite criterii de selecie. Excel realizeaz

o filtrare n list, ascunznd nregistrrile care nu satisfac criteriul/criteriile respective,

astfelnct lista care rmne vizibil conine doar nregistrrile care intereseaz.

6[6]ntruct cutarea are loc ncepnd cu poziia curent a cursorului n baza de date se recomand

poziionarea acestuia la nceputul sursei de date.7[7]

Se elimin dezavantajul parcurgerii nregistrare cu nregistrare


10/23

Conform acestei metode procedeul de consultare este urmtorul:

- - se poziioneaz cursoruln baza de date ;

- - se alege comanda Data - Filter...; AutoFilter. Comanda AutoFilter determin

completarea fiecrui titlu (nume de cmp) cu un buton de extensie (figura 1.102) prin

a crui acionare este afiat o list de varinate ce asist utilizatorul n formulareacriteriilor de cutare/extragere.

- - se definesc criteriile de filtrare pentru fiecare cmp, utiliznd butoanele de

extensie afiate.

Fig. 1.102 Filtrarea automat bazei de date

Exist urmtoarele posibiliti:

b1) s se defineasc un criteriu simplu pe un singur cmp. n acest caz se va selecta, din

lista de variante oferit de butonul de extensie, o valoare.

Exemplu. Care sunt angajaii cu numele Moise ?

Rezolvare.

- - precizarea criteriului : din lista numelor se selecteaza Moise

- - rezultatul filtrrii apare n figur 1.103.

Fig. 1.103 Rezultatul filtrrii automate (I)

b2) s se defieasc criterii simple pe mai multe cmpuri, criterii ce vor trebui s fie

ndeplinite simultan.Exemplu. Care sunt informaiile din baz referitoare la Moise Toma ?

Rezolvare.

- - precizarea criteriilor : din lista numelor se selecteaz Moise, iar din lista

prenumelor Toma

- - rezultatul filtrrii se vede n figura 1.104.


11/23

Fig. 1.104 Rezultatul filtr rii automate (II)

b3) s se defineasc un criteriu complex pe un singur cmp. n acest caz din lista de

variante oferit de butonul de extensie ataat cmpului respectiv, vom alege Custom....

Apare o fereastr de dialog care ghideaz utilizatorul n precizarea criteriului/criteriilor

de cautare i extragere8[8], rezultatul fiind afiat n foaia de calcul, prin filtrare direct n

list (figura 1.105).

Exemple:

Care sunt salariaii cu vechimea ntre 5 i 10 ani (inclusiv) ?

- - precizarea criteriului:

Fig. 1.105 Fereastr pentru filtrarea automat-

Custom

- - rezultatul filtrrii n figura 1.106

Fig. 1.106 Rezultatul filtrrii automate (III)

8[8]n precizarea criteriilor se pot utiliza caractere generice:

? - reprezinta un singur caracter

* - reprezinta un grup de caractere dintr-un sir


12/23

Se recomand utilizarea butoanelor de extensie din cadrul ferestrei de dialog. Ele

permit spre exemplu selectarea operatorului de comparaie din lista operatorilor posibili.

b4) Definirea criteriilor complexe pentru mai multe cmpuri.

Exemplu.

S se afieze doar nregistrrile referitoare la angajaii cu funcia analist sau

programator care au salariul ntre 600 de mii si 1milion de lei (inclusiv).

- precizarea criteriilor se face dupa metoda anterioar, reinnd c pentru funcie se va

alege operatorul Or (sau), iar pentru salariu operatorul And (i)

- rezultatul filtrrii n figura 1.107.

Fig. 1.107 Rezultatul filtr rii automate (IV)

Pentru a se reveni la afiarea bazei de date iniiale (nlturarea filtrrii) se poate alege:

- - varianta All din lista afiat prin acionarea butonului de extensie ataat cmpului

respectiv sau

- - comanda Data - Filter, ShowAll.

Pentru a reveni la modul de afiare normal se alege comanda Data - AutoFilter, care

dezactiveaz de aceast dat comanda AutoFilter.

Avantajul utilizrii comenzii Autofilter constn faptul c afieaz dintr-o dat toate

nregistrrile care satisfac criteriile specificate, sub forma unei liste al crui coninut

poate fi modificat, tiprit, formatat sau chiar ters dintr-o dat.

Dezavantajul utilizrii acestei metode de interogare constn faptul c nu se pot defini

restricii construite pe baz de formule de calcul simple sau complexe. Alt dezavantaj ar

rezulta din faptul c rezultatul unei interogri nu poate fi plasat dect n zona sursei bazei

de date i nu n alt zon a foii de calcul desemnate de utilizator.

c) Consultarea bazelor de date cu ajutorul filtrului avansat

Metoda consultrii bazelor de date cu ajutorul filtrului avansat presupune parcurgerea

urmtoarelor etape:

-definirea unei zone de criterii ;

-definirea criteriilor ;


13/23

-definirea opional a unei zone de extragere ;

-lansarea cutrii i extragerea propriu-zis din baza de date a nregistrrilor ce

corespund criteriilor de cutare i extragere definite n zona de criterii.

Interogrile bazate pe procedeul filtrrii avansate (Advanced Filter) sunt prezentate

pe exemplul unei baze de date pentru evidena facturilor emise ctre clieni i urmrireancasrii facturilor.

Baza de date declarat pe coordonatele (sau numit : Baz ) este ilustratn figura

1.108.

Fig. 1.108 Baza de date pentru filtrarea avansat

c1) Zona de criterii poate fi definitn aceeai foaie de calcul (n care se afl baza de

date) sau ntr-o alt foaie. Adesea este necesar ca zonele de criterii odat definite s fie

pstrate n vederea unor cutri/extrageri repetate. Chiar se recomand ca zonele de

criterii s fie definite separat, ntr-o alt foaie de calcul, pentru a nu sufoca foaia de calcul

ce conine baza de date, cu att mai mult cu ct datele rezultate n urma extragerii vor fi

afiate obligatoriu n aceeai foaie de calcul n care se afl i sursa de date.Zona de criterii este compus dintr-o linie ce conine numele cmpurilor ce servesc la

formularea criteriilor i una sau mai multe linii pentru definirea acestora.

Cmpul pe care se definesc criteriile, poate conine n prima linie a sa, totalitatea

rubricilor bazei de date sau numai o parte a acestora, dup cum diferitele rubrici particip

la consultarea bazei de date.


14/23

Precizare : prima linie a zonei de criterii se va obine prin copierea numelor respective de

cmpuri din list (din linia de titluri a rubricilor bazei de date). n caz contrar exist riscul

apariiei unei incompatibiliti ntre denumirile din list i cele din zona de criterii.

c2) Definirea criteriilor. Se pot defini criterii de comparaie, criterii multiple i criterii

calculate.Criteriile de comparaie se realizeaz cu ajutorul operatorilor de comparaie i anume: >,

=,


15/23

Fig. 1.109 Zona de criterii (I)

- al doilea exemplu (fig. 1.110) vizeaz construirea unui cmp de criterii pe anumite

rubrici ale bazei de date (Localitate, Platit, Majorri). Cmpul conine

Fig. 1.110 Zona de criterii (II)

o linie (sub titlurile rubricilor) pentru precizarea restriciilor legate prin operatorul

logic I , i o linie pentru precizarea operatorului logic SAU . O interogare bazat

pe un astfel de cmp de criterii ar avea urmtorul enun : care sunt clienii nepltitoridin Bucureti sau Ploieti care au majorri de ntrziere la plata facturilor de peste

1.000.000 lei .

Criteriile multiple evideniaz n esen faptul c restriciile pe care se

construiete interogarea pot fi definite pe mai multe rubrici.

O particularitate a acestor criterii este aceea c se pot preciza restricii ntre

dou limite (sau borne) : o limit maxim pe care valorile rubricii trebuie s le

ndeplineasc i o limit minim.

Pentru a se construi criterii ce s rspund la astfel de interogri ntre dou

intervale, rubrica (rubricile) care va conine respectivele intervale se dubleaz. Astfel, orubric va avea precizat o restricie ce semnific borna minim, iar a doua rubric

(dublatn cmpul de criterii) va conine borna maxim.

De exemplu, n figura 1.111 este prezentat un cmp de criterii, pentru urmtoarea

interogare : s se selecteze toi clienii nepltitori (Pltit=NU) din Bucureti

(Localitate=Bucureti), n primul semestru al anului 1998 (Data facturii >01/01/98 I Data

facturii500000 I Majorari


16/23

Fig. 1.111 Zon de criterii (III)

Criteriile calculate sunt bazate pe formule care returneaz un rezultat logic

(TRUE sau FALSE). n acest caz prima linie a zonei de criterii este goal, iar cea de a

doua va cuprinde formula/formulele respective, dar va afia rezultatul logic al evalurii

formulei.

Sintaxa unor astfel de criterii calculate este urmtoarea :

semnul = (egal) pentru a se specifica faptul c este vorba de o formul ; adresa primei celule din domeniul rubricii bazei de date ; un operator logic de comparaie (=, >, =, B2); o expresie (ex . : =D2>B2*22%); funcie predefinit (ex : =D2>AVERAGE(D2 :D32).

Figura 1.112 ilustreaz urmtoarea formulare pentru selectarea clienilor

nepltitori din Bucureti pe ultimele 6 luni (celula E42) i care au penaliti cuprinse

ntre tranele de 25% din valoare i 75% din valoare (celulele F42 i G42).

Fig. 1.112 Zon de criterii (IV)

Un alt exemplu de interogare bazat pe o rubric calculat, vizeaz utilizarea

funciilor predefinite.

Astfel, dac s-ar dori selectarea clienilor pltitori (Pltit=DA), care au achitat

valoarea facturilor peste media valoric a acestoara, s-ar introduce o formul ce calcul a

mediei pentru rubrica Valoare factur.


17/23


18/23

Fig. 1.115 Comenzi pentru filtrare

avansat

Zona de extragere este compus dintr-o linie ce conine numele cmpurilor (titlul

rubricilor) din baza de date despre care se dorete a se obine informaii. Preluarea

acestor nume n prima linie a zonei de extragere se poate face prin copierea numelor

respective din antetul listei.

c4) Lansarea cutrii i extragerea propriu-zis.

Aceast operaie se realizeaz prin apelarea comenzii Data Filter -Advanced

Filter... Pe ecran va fi afiat caseta de dialog Advanced Filter - fig.1.115) care invit

utilizatorul s precizeze plaja de celule care reprezint baza de date (List Range) i plaja

care reprezint zona de criterii (Criteria Range). Utilizatorul va activa pe rnd fiecare

din casetele de text, selectnd de fiecare datn foaia de calcul plaja respectiv.

Concomitent cu operaia de selectare, n caseta de text respectiv vor fi preluate9[9]

coordonatele absolute ale plajei respective de celule (figura 1.116). Reamintim c

definirea zonei de extragere este opional. Astfel, dac se dorete vizualizarea

rezultatului filtrrii, direct pe sursa de date (cnd nregistrrile care nu satisfac criteriile

din zona de criterii vor fi ascunse, rmnnd vizibile doar nregistrrile care satisfac

criteriile respective) se va aciona butonul OK.

Efectul extragerii pentru o interogare de genul care sunt clienii pltitori din

Bucureti i care sunt clienii nepltitori din Iai sau Ploieti , este prezentat n figura

1.116.

9[9] precizarea coordonatelor se poate face i prin tastarea directn caseta de editare, procedeu ns mai

greoi.

Prin List Rangese precizeazcoordonatele pecare s-a definitbaza de date

Prin Criteria

Range seprecizeazcoordonatelecmpului de

Implicit, rezultatul filtrrii se vaafia pe baza de date, care va firedimensionatn funcie decerinele interogrii

Buton pentruvalidareaprocesului defiltrare a bazeide date


19/23

Fig. 1.116 Rezultatul filtr rii avansate

Revenirea afirii bazei de date la forma iniial (care includea totalitatea

nregistrrilor) se face prin comanda Data - Filter - ShowAll

n cazul n care a fost definit o zon de extragere atunci utilizatorul va completa caseta

de dialog Advanced Filter (figura urmtoare) i cu coordonatele absolute ale acestei

zone (n caseta de text Copy to).

Cmpul de rezultate poate fi generat ca avnd toate rubricile bazei de date (i

atunci se plaseaz cursorul pe prima celul a cmpului de rezultate) sau poate fi generatca avnd numai anumite rubrici. n acest ultim caz, respectivele rubrici se vor edita pe

prima linie a cmpului de rezultate, iar selecia cmpului va ncepe de la coordonatele

rubricilor definite.

ntruct caseta de text Copy to nu este activ implicit, este necesar ca pentru a o

activa s se selecteze cellalt mod de acionare i anume Copy to another location.

Relum exemplul precedent de interogare, ce va fi fcut prin extragere, cu

deosebirea c se vor afia clienii, adresele acestora, localitile din care provin, numerele

de factur i sumele datorate.

Caseta de dialog Advanced Filter, corespunztoare procedeului de extragere,este prezentatn figura 1.117.

Fig. 1.117 Comenzi pentru filtrare avansat (II)

Pentru interogareaprin extragere, seactiveaz tipul de

aciune Copy to

another location

Prin Copy To seprecizeazcoordonatelecmpului derezultate


20/23

Fig. 1.118 Rezultatul filtr rii avansate

Lansarea cutrii i extragerii se declaneaz prin acionarea butonului OK.

Rezultatele interogrii sunt prezentate n figura 1.118

Prin selectarea casetei de selectare Unique Records Only nu vor fi preluate n zona de

rezultatenregistrrile duble (care au valori identice, corespunztor restriciilor impuse n

cmpul de criterii).

Exemplul di figura 1.119 ilustreaz construirea cmpurilor de criterii i rezultate pentru

interogarea : care sunt clienii crora firma le-a emis facturi de la nceputul anului pn

azi .

Fig. 1.119 Date i criterii pentru filtrare avansat

Se impun cteva precizri privind caracteristicile extragerii:

- - nregistrrile extrase conin numai valori (formulele de calcul sunt extrase ca

valori) ;

- - nregistrrile extrase nu sunt legate de sursa de date; eventualele modificri n

baza de date nu se vor reflecta n zona nregistrrilor deja extrase ;

- - dimensiunea zonei de extragere este ajustat automat la numrul de nregistrri

extrase. De aceea cnd se precizeaz coordonatele zonei de extragere (caseta de text

Cmp de

Cmp de rezultate

Selecie unic a nregistrrilor


21/23

Copy to din fereastra Advanced Filter) se selecteaz doar o singur linie i anume

aceea care conine numele cmpurilor.

- - lista (subsetul de nregistrri) afiatn zona de extragere poate fi prelucrat ca

orice list, poate fi stocat, sintetizat, imprimat.

Sortarea bazelor de date

Realizarea sortrii unei baze de date se face cu ajutorul comenzii Sort din meniul

Data.

Aceast comand permite ordonarea cresctoare (Ascending) sau descresctoare

(Descending) a nregistrrilor din baza de date dup maximim trei cmpuri (simultan).

Alegerea comenzii Data - Sort... determin apariia pe ecran a casetei de dialog Sort

(fig. 1.120)

Folosind butoanele de extensie din dreptul fiecrei casete de text utilizatorul vaputea preciza cmpurile dup care s se fac sortarea, ct i ordinea sortrii pentru fiecare

cmp.

Din punct de vedere conceptual, sortarea dup mai multe cmpuri implic

precizarea mai multor niveluri de regrupare a datelor. Astfel cheile de sortare se

precizeazn ordinea nivelului de consisten a datelor : primul nivel de sortare trebuie s

conin elementele celui de-al doilea nivel, iar al doilea nivel trebuie s conin

elementele celui de-al treilea nivel, .a.m.d. Altfel spus ordinea sortrii trebuie s fie de la

domeniul cel mai cuprinztor, la domeniul cel mai puin cuprinztor.

Dac

se dorete ordonarea doar dup

un c

mp, va fi completat

doar prima caset

de text, restul rmnnd goale.

Operaiunea de sortare ncepe prin selectarea bazei de date sau prin poziionarea

cursorului pe prima celul din baz, continu prin activarea comenzii Data Sort i

definirea cheilor de sortare concomitent cu precizarea ordinii n care se va face operaia

de sortare i se ncheie prin validarea operaiei cu butonul OK.

Exemplu. S se reordoneze baza de date crector dup localitatea clientului, apoi

dup data emiterii facturii i n final dup numele clientului.

Precizarea criteriilor de sortare este ilustratn figura 1.120.

Rezultatul operaiunii de sortare este prezentat

n figura 1.121.


22/23

Fig. 1.120 Caseta de dialog pentru sortare

Fig. 1.121 Rezultatul sortrii


23/23
http://www.ase.ro/ciedd/birotica/CAP16A.htmhttp://www.ase.ro/ciedd/birotica/excel-cuprins.htmhttp://www.ase.ro/ciedd/birotica/CAP14.htm

Date post:	30-May-2018
Category:	Documents
Upload:	pavelandrei
View:	224 times
Download:	0 times

Excel5-Baze de Date

Documents