Baze de date

În cadrul bazelor de date sunt cuprinse două activităţi principale :

Sortarea datelor ;

Filtrarea datelor.

Comezile corespunzătoare acestor activităţi se găsesc în meniul Home zona Editing opţiunea

Sort&Filter.

Exemplu.

Să se realizeze foaia de calcul sub formă de bază de date. Se va introduce prima linie, se va

selecta prima linie plus liniile până la linia 21 inlcusiv, iar din meniul Home, Format as table se

va alege un format predefinit. Fereastra generată după alegerea modelului de formatare este :

Observaţie. Se bifează opţiunea My table has headers.

Se selectează celulele H2:H21 şi din meniul Data se alege opţiunea Data Validation. Coloana

Stare factură conţine două valori, şi anume, achitat, respectiv neachitat. Fiind valori repetitive se

vor defini ca listă de opţiuni astfel :

Se aplică şi formatarea condiţionată astfel: se selectează coloana, se aplică Conditional

Formatting/Hightlight Cells Rules/ Equal To.

De la Custom Format se aplică formatare pe Fill şi pe text.

Se selectează celulele E2:E21 şi F2:F21 şi se definesc de tip dată calendaristică. Din lista

derulantă de opţiuni se alege Custom şi se defineşte data calendaristică astfel :

Se va completa câmpul Data factură, iar câmpul Data scadentă se completează folosind formula :

Observaţie. Se va introduce formatul luna.ziua.anul iar la vizualizare Excelul va returna la

ziuă.lună.an în foaia de calcul.

Se selectează celulele G2:G21 şi se aplică formatare condiţionată din meniul Home/Conditional

Formatting de tipul Icon Sets modelul Ratings.

Câmpului Penalităţi i se aplică formatare condiţionată. Dacă raspunsul este Da atunci se va folosi

Fill de o anumită culoare, contur, font diferit.

Câmpul Penalităţi se determină utilizând funcţia IF. Sintaxa formulei aplicată este :

Foaia de calcul completată este :

Cerinţe problemă :

1) Se cere să se ordoneze crescător informaţiile din baza de date după câmpul Localitate.

Se selectează celulele C2 :C21 şi se aplică Sort A to Z. Se copiază rezultatele obţinute în altă

foaia de calcul şi se redenumeşte Ordonare Crescatoare Localitate.

2) Se cere să se ordoneze descrescător după câmpul Valoare.

Se selectează celulele G2 :G21. Se aplică Sort Z to A.

3) Să se afişeze o selecţie pe baza de date astfel încât să se afişeze doar clienţii care au achiat

facturile.

Se deschide banda derulantă cu opţiuni din cadrul câmpului şi se lasă selectată doar versiunea

achitat.

4) Să se afişeze o selecţie pe baza de date astfel încât să se afişeze doar clienţii care au penalităţi

de plătit.

Se deschide banda derulantă cu opţiuni din cadrul câmpului şi se lasă selectată doar versiunea

Da.

5) Să se afişeze clienţii care au data scadentă în lunile iulie şi august, respectiv starea facturii

achitat. Pentru aceste facturi să se calculeze valoarea totală.

Pentru a aplica selecţie pe vizualizarea conţinutului câmpului Data scadentă se deschide lista

derulantă cu opţiuni şi se alege Date Filters.

Se debifează toate opţiunile prin Select All. Apoi se bifează July and August.

6) Să se afişeze clienţii care au data scadentă în intervalul 18/08/2013 – 28/09/2013. Pentru

aceste facturi să se calculeze valoarea totală.

Din Date Filters se alege opţiunea Between :

Observaţie. Se foloseşte icon-ul de tip calendar şi se aleg datele dorite. Atenţia se foloseşte

calendar şi nu se alege data din lista derulantă deoarece noi am schimbat returnarea rezultatului

în tabel. Formatul standard este luna/ziua/anul, iar prin modificarea noastră această editare

returnează ziua/anul/anul.

Pentru aceste facturi să se calculeze valoarea totală.

7) Să se completeze baza de date cu o coloană care reprezintă valoarea TVA corespunzătoare

fiecărei facturi.

8) Utilizând icon-ul Form să se completeze baza de date cu două linii.

Pentru adăugarea icon-ului Form pe toolbar-ul Home se alege opţiunea Options din meniul File.

Din fereastra Excel Options se alege Customize Ribbon.

În lista Commands Not in the Ribbon se găseşte icon-ul Form. În zona

se poziţionează cursorul pe butonul Home apoi se execută click pe butonul . Se

obţine astfel un grup nou care, utilizându-se butonul Rename, se va redenumi în Form. Se mută

opţiunea Form cu ajutorul butonului Add în noul Grup format pe toolbar. Rezultatul acestor

comenzi este

Se poziţionează cursorul în interiorul bazei de date şi se activează butonul Form. Apoi utilizând

butonul Criteria se pot adăuga noi înregistrări în baza de date :

9) Să se afişeze firmele din Cluj-Napoca care au facturi de plătit în valoare mai mare de 100.000

Ron.

Pe câmpul Valoare se adaugă restricţia :

Pe câmpul Localitatea se adaugă restricţia :

10) Să se completeze baza de date cu un câmp de tip judeţ.

Funcţii pentru gestionarea bazelor de date

1) DSUM(coordonatele zonei corespunzătoare bazei de date, ˝Nume câmp˝, coordonatele

celulelor care conţin criteriul aplicat) = funcţie de tip adunare condiţionată.

2) DCOUNT(coordonatele zonei corespunzătoare bazei de date, ˝Nume câmp˝, coordonatele

celulelor care conţin criteriul aplica) = funcţie de tip numărare condiţionată.

3) DMAX(coordonatele zonei corespunzătoare bazei de date, ˝Nume câmp˝, coordonatele

celulelor care conţin criteriul aplica) = funcţie de tip determinarea valorii maxime pe baza unei

condiţii de căutare.

4) DMIN(coordonatele zonei corespunzătoare bazei de date, ˝Nume câmp˝, coordonatele

celulelor care conţin criteriul aplica) = funcţie de tip determinarea valorii minime pe baza unei

condiţii de căutare.

Exemplu. Pentru exemplul anterior să se determină pentru fiecare oraş:

a) valoarea facturilor;

b) numărul de firme care au facturi de plătit;

c) valoarea cea mai mare a facturii;

d) valoarea cea mai mică a facturii;

e) valoarea medie a facturilor.

Rezolvare.

Se introduce în celulele K1:K2 criteriul de selecţie :

Atenţie : Criteriul de selecţie se introduce pe coloană nu pe linie.

a) În celula L1 se trece numele caloanei de calcul « Valoarea facturilor/oraş »

În celula L2 se trece formula de calcul pentru adunarea valorii tuturor facturilor emise de firme

din oraşul Cluj-Napoca:

b) În celula M1 se trece numele coloanei de calcul « Numar de firme/localitate »

În celula M2 se trece formula de calcul pentru determinarea numărului de firme din oraşul Cluj-

Napoca pe baza numărului de valori corespunzătatoare facturilor emise de firme din oraşul Cluj-

Napoca :

c) În celula N1 se trece numele coloanei de calcul « Valoarea maximă »

În celula N2 se trece formula de calcul pentru determinarea valorii celei mai mari din lista

facturilor emise de firme din oraşul Cluj-Napoca :

d) În celula O1 se trece numele coloanei de calcul « Valoarea minimă »

În celula O2 se trece formula de calcul pentru determinarea valorii celei mai mici din lista

facturilor emise de firme din oraşul Cluj-Napoca :

e) În celula P1 se trece numele caloanei de calcul « Valoarea medie »

În celula P2 se trece formula de calcul pentru valoarea medie a tuturor facturilor emise de firme

din oraşul Cluj-Napoca :

Foaia de calcul cu rezultatele corespunzătoare calculelor anterioare este :

Pentru determinarea imediată a valorilor corespunzătoare celorlalte oraşe din baza de date se

crează în celula K2 o listă predefinită utilizând opţiunea Data Validation din meniul Data :

Rezultatul în foaia de calcul este :

Alegându-se, de exemplu, din lista derulantă un alt oraş – Brasov avem automat rezultatele:

Observaţie. Dacă se doreşte un tabel complet cu aceste rezultate pentru fiecare oraş în parte se

copiază linie de linie într-o foaie nouă.

Probleme propuse

1) Să se determine pe baza de date anterioară :

a) valoarea facturilor ;

b) numărul de firme care au facturi de plătit ;

c) valoarea cea mai mare a facturii ;

d) valoarea cea mai mică a facturii ;

e) valoarea medie a facturilor.

pentru fiecare judeţ în parte.

2) Să se determine pe baza de date anterioară

a) valoarea TVA corespunzătoare facturilor ;

b) valoarea cea mai mare a TVA-ului corespunzător unei facturi ;

c) valoarea cea mai mică a TVA-ului corespunzător unei facturi ;

d) valoarea medie a TVA-ului corespunzător facturilor ;

pentru fiecare localitate, respectiv judeţ în parte.

Funcţii de căutare

VLOOKUP() = este o funcţie de căutare pe coloană. Înainte de aplicarea acestei funcţii coloana

din care se alege valoarea căutată se ordonează crescător. Căutarea se face în coloanele aflate în

partea dreaptă relativ la coloana în care se află valoarea căutată. Funcţia nu ştie să facă căutare în

coloanele din partea stângă.

Sintaxa funcţiei :

VLOOKUP(valoarea căutată ; se selectează baza de date începând cu coloana în care se află

valoarea căutată ; numărul coloanei din interiorul bazei de date, relativ la a câta coloană selectată

este, în care se face căutarea - corespondentului valorii căutate ; FALSE = returnează valoarea

exactă, TRUE =returnează o valoare aproximativă)

Exemplu.

Să se determine starea facturii cu numărul 111. De asemenea să se determine dacă are penalităti

de plătit.

Rezolvare.

Formula corespunzătoare câmpului Stare factură :

Explicarea formulei :

D11 – în această celulă s-a introdus valoarea 111 în scopul determinării tabelului de mai sus ;

Table2[[#All],[Nr. factura] :[Penalitati]] – din baza de date s-a facut selecţia coloanelor

Selecţia a început cu coloana D deoarece valoarea căutată se află pe această coloană ;

5 – coloana din care se doreşte returnarea corespondentului valorii 111 este coloana a 5 – a în

cadrul selecţiei anterioare ;

False – se doreşte returnarea valorii exacte.

Observaţie. În loc de D11 în formulă se poate edita exact valoarea căutată, adică 111. Funcţia

ştie să facă căutarea în coloana corectă deoarece întotdeauna se va selecta zona din baza de date

începând cu coloana corespunzătoare valorii căutate.

Formula corespunzătoare câmpului Penalităţi :

Exerciţiu.

Să se determine starea facturii corespunzătoare clientului SRL CC. De asemenea să se determine

dacă are penalităti de plătit. Să se determine din ce localitate provine acest client. Să se

determine data scadentă a facturii pentru acest client. Aceleaşi cerinţe pentru clientul SRL NN.

Observaţie. Înainte de a introduce formula necesară returnării datei calendaristice se modifică

structura celulei din General în Date. Dacă nu se face această transformare nu se returnează

răspunsul corect.

HLOOKUP() = este o funcţie de căutare pe linie. Căutarea se face în liniile aflate în partea de

sub linia în care se află valoarea căutată. Funcţia nu ştie să facă căutare în liniile de deasupra

liniei căutate.

Sintaxa funcţiei :

HLOOKUP(valoarea căutată ; se selectează baza de date începând cu linia în care se află

valoarea căutată ; numărul liniei din interiorul bazei de date, relativ la a câta linie selectată este,

în care se face căutarea - corespondentului valorii căutate ; FALSE = returnează valoarea exactă,

TRUE =returnează o valoare aproximativă)

Exemplu.

Se vor copia coloanele

din baza de date iniţială în altă foaie de calcul. Pentru a modifica coloanele în linii se va folosi

Paste Special/Transpose.

Se cere :

Pentru clientul SRL BA se cere să se determine localitatea, valoarea facturii, respectiv numărul

facturii.

Rezolvare.

Se obţine tabelul

Formulele de calcul sunt pe rând pentru localitatea, nr factură, valoarea următoarele :

Probleme propuse

Se dă baza de date :

Să se utilizeze toate formulele de calcul şi de căutare învăţare în această lucrare practică. Se va

realiza cel puţin câte un exemplu de calcul pentru fiecare funcţie. Se va face un studiu

comparativ al vânzărilor pe 4 semestre pentru fiecare an, respectiv tip de carburant. Se vor

formula întrebările şi răspunsurile corespunzătoare. Problema va fi redactată ulterior în Word şi

predată.