Post on 07-Dec-2015
transcript
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ă.