BAZE DE DATE IN EXCEL

Baze de date īn Excel

Pentru a putea  utiliza baze de date cu ajutorul programului Excel se folosesc comenzile din meniul Data. Aici se regasesc cāteva din comenzile uzuale pentru crearea, interogarea, manipularea bazelor de date:

Figura 1.16 - Sortarea listelor

Sortarea datelor

Sort - permite sortarea datelor dintr-o lista aflata īntr-o foaie de calcul, dupa maxim trei criterii, putāndu-se specifica pentru fiecare criteriu ordinea de sortare (crescatoare sau descrescatoare).

Realizarea unei sortari se face astfel (Figura 1.16):

Se selecteaza zona din foaia de calcul ce contine lista cu datele de sortat;

Se activeaza comanda Sort;

Īn caseta Sort by se selecteaza coloana care reprezinta primul criteriu de sortare si se stabileste modul de sortare folosind acest criteriu (Ascending - crescator sau Descending - descrescator);

Se procedeaza la fel pentru al dooilea si eventual al treilea criteriu de sortare folosind casetele Then by.

Daca zona selectata din foaia de calcul contine o lista complet, inclusiv linia cu denumirea coloanelor (Header row - cap de lista), se bifeaza Header row pentru a nu include īn sortare si aceasta linie. Daca lista nu are o asemenea linie cu denumirea coloanelor sau pur si simplu nu a fost selectata aceasta linie se bifeaza optiunea No header row.

Alte optiuni suplimentare de sortare se mai gasi folosind butonul Options..

Filtrarea datelor

Comanda Filter permite afisarea doar a elementelor dintr-o lista care īndeplinesc anumite conditii, restul fiind ascunse. Realizarea unei filtrari interactive, se poate face relativ usor folosind comanda AutoFilter din submeniul Filter. La activarea acestei comenzi īn linia care contine numele fiecarei coloane din lista vor apare niste casete ComboBox (liste ascunse) care permit selectarea anumitor elemente de afisat (All - toate, Top 10. - primele/ultimele 10 elemente din lista, Custom - permite construirea unor criterii mai complicate). Alegerea unui alt element din lista afisata - permite afisarea doar a liniilor pentru care īn coloana din care s-a facut alegerea apare valoarea selectata din lista.

Exemplu (Figura 1.17): Daca pentru o lista de studenti care studiaza doua limbi straine (E- engleza, F - franceza, G - germana, I - italiana, R - rusa), se doreste afisarea doar a celor care au prima limba de studiu engleza si a doua italiana, se alege din coloana LB1 - limba 1, valoarea E, iar din coloana LB2 - se alege valoarea I.

Figura 1.17 - Filtrarea datelor īn Excel

Daca se doreste construirea de filtre pe baza unor criterii mai complicate se alege optiunea Custom din coloana pentru care se construieste filtrul. Va declansa afisarea unei ferestre Custom AutoFilter (Figura 1.18). Īn imagine este aratat modul cum trebuie completate casetele pentru afisarea liniilor care contin studentii ce au a doua limba de studiu orice alta limba diferita de engleza si italiana.

Figura 1.18.- Setarea filtrului pentru afisarea selectiva

Efectuarea subtotalurilor īn liste se face cu ajutorul comenzii Subtotals din meniul Data.

Figura 1.19 - Lista de date   pregatita pentru efectuarea de subtotaluri

Exemplu: Se doreste aflarea cantitatii din fiecare tip de produs care exista īntr-un depozit oarecare. Se cunosc datele din imaginea urmatoare dreapta, īn care elementele din lista au fost īn prealabil sortate alfabetic dupa denumire (Figura 1.19):

Se selecteaza zona care cuprinde toate elementele din lista inclusiv denumirile coloanelor;

Se activeaza comanda Data, Subtotals;

Din fereastra Subtotal (Figura 1.20), īn caseta At each change in: (la fiecare schimbare īn) se alege Denumire, īn caseta Use function (foloseste functia) se alege Sum iar īn caseta Add subtotal to (adauga subtotal la) se bifeaza coloana Cantitate.

Fig. 1.20 - Caseta de dialog Subtotals

Daca se doreste afisarea subtotalurilor sub fiecare tip de produs se bifeaza caseta Summary bellow data, daca se doreste afisarea subtotalului deasupra fiecarui tip de produs se debifeaza. Pentru afisarea fiecarui subtotal pe cāte o pagina separata la imprimanta se bifeaza Page break between groups.

Pentru renuntarea la subtotaluri se foloseste Remove All.

Validarea datelor

Comanda Validation  - permite definirea conditiilor pe care trebuie sa le īndeplineasca valoarea ce se introduce īntr-o celula, a mesajului afisat la selectia respectivei celule si a mesajului care trebuie sa apara la īncercarea de a introduce īn celula o valoare care nu īndeplineste conditiile cerute.

Exemplu: Īn lista cu produsele din depozit de la exemplul anterior, se doreste ca la inserarea unei cantitati pentru orice produs sa poata fi introduse decāt valori numerice pozitive.

La selectia oricarei celule din coloana respectiva sa fie afisat mesajul "Sunt permise doar valori numerice pozitive.", iar la īncercarea de introducere a unei valori diferite de numar pozitiv sa fie avertizat utilizatorul prin urmatorul mesaj: Valoarea introdusa trebuie sa fie numerica si pozitiva!. Pentru aceasta elementele din fereastra Data Validation se completeaza ca configurarile din figurile 1.21, 1.22 si 1.23.

Figura - 1.21. Setarile pentru permiterea numai a numerelor īntregi, pozitive

Figura 1.22 - Configurarea mesajului la introducerea datelor

Figura 1.23 - Configurarea mesajului de eroare

Exercitii

Deschideti aplicatia de calcul tabelar Excel.

Setati programul Excel astfel īncāt:

īntr-un nou registru (fisier .XLS) vor fi 4 foi de calcul

dupa introducerea unor date īntr-o celula si apasarea tastei Enter, selectia se va muta īn dreapta

fontul implicit (standard) este Arial 12

separatorul zecimal este caracterul virgula, iar separatorul de mie (de exemplu 1.000.000) este punctul

fisierul se va salva automat dupa 7 minute

Creati un fisier nou. Salvati-l īn directorul dumneavoastra de lucru cu numele Excel_1.xls.

Inserati 4 noi foi de calcul. stergeti trei dintre ele

Redenumiti cele cinci foi de calcul ramase astfel: Trim1, Trim2, Trim3, Trim4, TotalAn

Introduceti, īn acelasi timp īn toate cele patru foi de calcul Trim1, Trim2, Trim3, Trim4, datele de mai jos:

Īn foaia de calcul Trim1, plasati indicatorul de selectie īn celula A5. Scrieti formula =A1+A2. Folosind butonul de extindere a formatarii, copiati aceasta formula īn celulele B5, C5 si D5.

Īn foaia de calcul Trim1, plasati indicatorul de selectie īn celula F1. Scrieti formula =Sum(A1:D1). Folosind butonul de extindere a formatarii, copiati aceasta formula īn celulele F2 si F3.

Selectati foaia de calcul Trim2. Copiati-o īntr-un nou fisier numit Adresari.xls (folositi meniul Edit-Move or Copy Sheet...). Redenumiti foaia de calcul Trim2 īn Adresare_Absoluta. Inserati doua noi foi de calcul. Redenumiti-le Adresare_Relativa si Adresare_Mixta. Copiati īn cele doua foi de calcul Adresare_Relativa si Adresare_Mixta datele din foaia de calcul Adresare_Absoluta. Folosind si exemplele de la paginile 10 si 11, exemplificati modurile de adresare a celulelor īn formule Excel.

Salvati spatiul de lucru din Excel, care contine cele doua fisiere pe care  le aveti deschise (Excel_1.xls si Adresari.XLS) īn fisierul Comun1.xlw. Īnchideti toate fisierele .

Creati un fisier nou cu numele Studenti.xls. Redenumiti prima foaie de calcul īn Examen. Colorati numele foii de calcul īn rosu. Īncepānd din celula A4 scrieti tabelul de mai jos:

Folosind facilitatile de generare a seriilor de date din Excel, completati coloana Nr.   Calculati media.

Sortati baza de date īn ordinea descrescatoare a mediilor.

Filtrati baza de date si permiteti lucru numai cu studentii din grupa 3. Eliminati filtru si activati unul pentru toti studentii cu media 10.

Eliminati toate filtrele. Calculati media generala si media pe grupe.

Salvati fisierul si apoi īnchideti-l.

Deschideti un nou registru de calcul. Salvati-l cu numele Produse.xls. Scrieti īn prima foaie de calcul, pe care o denumiti Realizari, datele de mai jos:

Folosind facilitatile de generare a seriilor de date din Excel completati coloana Nr. Crt. Calculati valoarea pe fiecare produs.

Calculati, pe coloanele Cantitate si Valoare, subtotaluri pe produse.

Calculati pe coloana Valoare subtotaluri pe gestiuni. Salvati si īnchideti registrul de calcul.

Deschideti un nou registru de calcul. Salvati-l cu numele BDate.xls Redenumiti prima foaie de calcul Validari. Creati īn ea un tabel care sa contina urmatoarele coloane:

Nr. Crt. (se va completa folosind facilitatea Edit-Fill-Series)

Numele si Prenumele

Sexul; īn aceasta coloana se va permite doar introducerea caracterelor M sau F

Profesia; īn aceasta coloana nu vor fi permise decāt valorile: economist, inginer, profesor, arhitect si medic

Nota1, Nota2 si Nota; īn aceste coloane vor fi permise numai numere cuprinse īn intervalul 1.00 - 10.00

Media

Introduceti date si calculati media cu rotunjire la o singura pozitie zecimala.

Salvati fisierul. Redenumiti a doua foaie de calcul Grafic. Introduceti datele de mai jos:

Reprezentati grafic, īn aceeasi foaie de calcul, evolutia productiei īn cele patru trimestre.