Prelucrarea datelor cu operatii specifice bazelor de date

O baza de date contine un ansamblu de date structurate intr-o forma specifica; structura de memorare si organizare a informatiilor faciliteaza gestiunea lor (operatii de memorare, regasire a datelor etc.).

Un tabel Excel poate fi privit ca o baza de date pentru care numele de date sau campurile sunt numele coloanelor tabelului iar fiecare set de valori pentru aceste nume (inregistrare) se gaseste intr-o linie a tabelului. Astfel, un tabel Excel implementeaza in mod natural ceea ce este cunoscut sub numele de model relational de organizare a bazelor de date. In Excel, pentru o structura de acest tip se mai foloseste si denumirea de lista.

Operatii cu liste

a) Crearea unei liste se face prin introducere de valori, respectand principiile descrise in subcapitolele anterioare (numele campurilor apar ca nume de coloane). Listei astfel construite i se poate asocia un nume cu optiunea Insert - Name - Define.

b) Actualizarea listei - presupune operatii de modificare a valorilor, adaugare si eliminare de inregistrari din lista. Aceste operatii se realizeaza foarte usor folosind formularele de date. Un astfel de formular se poate crea prin selectarea comenzii Data - Form in timp ce cursorul este pozitionat in cadrul tabelului de date. Fereastra formularului permite modificarea valorilor prin operatii uzuale de editare, adaugarea unei inregistrari (New), eliminarea inregistrarii curente (Delete) (a se vedea figura nr.8).

Dupa efectuarea acestor operatii, modificarile corespunzatoare apar si in foaia de lucru.

Figura nr.8 - Exemplu de formular pentru date

c) Cautarea inregistrarii care indeplineste anumite proprietati se poate face folosind tot formularul de date: Data - Form. Butonul Criteria permite specificarea valorilor cautate, care se vor introduce intr-o fereastra de tip inregistrare. Se va realiza pozitionarea pe inregistrarea (inregistrarile) care indeplinesc conditiile specificate. In cazul mai multor inregistrari, se va putea trece de la una la alta.

De exemplu, in lista din figura nr.7 se poate cauta o anumita persoana sau sau toate persoanele cu proprietatea 'Media >9'.

d) Sortarea inregistrarilor dintr-o lista este operatia de rearanjare a inregistrarilor, dupa valorile unuia sau mai multor campuri, numite chei, in ordine alfabetica, numerica sau cronologica (crescatoare sau descrescatoare).

Sortarea se realizeaza folosind comanda Data - Sort. In fereastra care se deschide se specifica cheile de sortare (a se vedea figura nr.9). Primul camp dupa care se face ordonarea se numeste cheie principala. Daca exista mai multe inregistrari care au aceeasi valoare pentru aceasta cheie, putem specifica alta cheie de ordonare (secundara). Astfel, in Excel, la o operatie de sortare se pot specifica maximum trei chei de sortare.

Figura nr. 9 - Fereastra de dialog Sort

Pentru fiecare cheie se poate opta intre sortare crescatoare sau descrescatoare (Ascending/Descending).

Observatie.

Cu o tehnica mai deosebita, utilizatorul isi poate defini ordini in liste speciale care contin anumite valori (de exemplu: zilele saptamanii, lunile anului) folosind Tools - Options - Custom List - List Entries.

e) Selectarea inregistrarilor care indeplinesc anumite proprietati (filtrare).

Sa presupunem, de exemplu, ca dorim ca toate persoanele retinute in foaia de lucru din figura nr. 7 si care au media mai mare decat 9 (Media > 9) sa fie afisate separat in foaia curenta de lucru.

Aceast tip de prelucrare a informatiilor se realizeaza prin intermediul comenzii Data - Filter. Exista doua posibilitati de obtinere a rezultatelor dorite:

1) cu AutoFilter - specificam pentru fiecare camp valoarea dorita sau o proprietate (fiecarui camp i se ataseaza o lista de forma: All, Custom, lista de valori). In exemplul nostru, pentru toate campurile specificam All in afara de campul Media, unde specificam cu Custom conditia >9 (greater than 9).

2) cu Advanced Filter - se pot introduce domenii de selectare mai complexe. Se va cere:

• domeniul listei din care se face selectia (List range);
• domeniul de criterii (Criteria range). Intr-un domeniu de criterii campurile pentru care se specifica conditiile se depun ca si cap de tabel (vezi figura nr.11). Intre conditiile din acelasi rand se aplica operatorul logic And iar intre conditiile din randuri diferite - operatorul logic Or.
• eventual domeniul destinatie sau coltul sau stanga sus, daca nu se doreste ca selectia sa se realizeze in pozitia curenta (Copy to - a se vedea figura nr.10).

Figura nr. 10 - Ferestra de dialog Advanced Filter

Exemplul 1:

Folosim exemplul din figura nr.7  . Sa presupunem ca dorim sa copiem toate inregistrarile studentilor care au media cuprinsa in intervalul [8.5, 9.5] in foaia de lucru curenta, incepand cu celula I30. Campurile ferestrei de dialog Advanced Filter se vor completa dupa cum urmeaza: List range va contine intreaga lista ($A$3:$F$24), Criteria range va contine celulele criteriului de selectie ($H$19:$I$20) - vezi figura nr.11, iar Copy to va contine locatia unde se va realiza copierea ($I$30).

Figura nr.11 - Exemplu de domeniu de criterii

Observatie.

Daca se doreste reafisarea tuturor inregistrarilor din lista, se alege optiunea Data - Filter - Show All.

f) Rezumarea datelor

In fiecare grup de inregistrari care au aceeasi valoare pentru un camp dat, asupra valorilor unor campuri specificate se pot aplica diverse functii de sinteza (medie, numarare, implicit suma). Astfel, se pot realiza simplu totaluri si subtotaluri. In locul functiei suma, se pot specifica si alte functii: numar de elemente (Count), medie (Avg), maxim (Max), minim (Min) etc. Operatia se poate repeta pentru valorile altui camp; astfel se va obtine un rezumat al listei initiale.

Anterior, inregistrarile trebuie sortate dupa campul/campurile ale carui/caror grupe de valori egale se urmaresc.

Daca se doreste sa se realizeze calcule asupra unei liste folosind un domeniu de conditii (adica sa se prelucreze doar liniile care indeplinesc acele conditii), se vor utiliza functiile specifice bazelor de date: DSUM (suma pentru o baza de date), DAVERAGE (medie pentru o baza de date), DCOUNT (numarare pe o baza de date) etc. Aceste functii sunt caracterizate de trei parametri: domeniul bazei de date, campul pe care se face calculul si domeniul de criterii care selecteaza liniile implicate in calcul.

In exemplul din figura nr.7 se calculeaza numarul de note cuprinse in fiecare din intervalele: [0.5, 1.5), [1.5, 2.5), [2.5, 3.5), ., [8.5, 9.5), [9.5, 10]. In acest scop se utilizeaza functia DCOUNT specificand pe rand: domeniul asupra caruia se aplica functia, campul dupa care se realizeaza numararea, precum si domeniul de criterii care contine conditia de indeplinit pentru inregistrarile asupra carora se aplica operatia.

g) Tabele de sinteza

Tabelele de sinteza sau tabelele pivot (Pivot Table) realizeaza sintetizarea datelor dintr-o baza de date Excel din mai multe perspective. Utilizatorul poate alege campurile care sa constituie titluri de pagina, de linie sau de coloana, sau campuri care sa fie utilizate in realizarea unor calcule.

Vom explica utilizarea tabelelor pivot pe un exemplu concret. Sa presupunem ca avem baza de date Studenti care contine inregistrarile din figura nr.12 si ca dorim sa aflam cati studenti din fiecare grupa sunt deja memorati in baza de date.

Figura nr. 12 - Baza de date Studenti

Se vor efectua urmatorii pasi:

• se lanseaza in executie comanda Pivot Table din meniul Data;
• in fereastra de dialog deschisa (Step 1 of 4), se alege optiunea Microsoft Excel List or Database si se va actiona butonul Next;
• in fereastra de dialog deschisa (Step 2 of 4), campul Range se completeaza cu seria de celule care contin datele ce vor fi analizate (in cazul nostru, seria este A1:D12) si actioneaza butonul Next;
• in fereastra de dialog deschisa (Step 3 of 4), se gliseaza butonul Grupa in campul DATA. Se va afisa Sum of GRUPA (functia care realizeaza insumarea datelor din coloana GRUPA);
• deoarece nu dorim insumarea datelor din coloana GRUPA, vom realiza un dublu clic cu mouse-ul pe butonul Sum of GRUPA din campul DATA. Se afiseaza o noua fereastra de dialog care permite alegerea functiei Excel dorite (in acest caz - Count pentru numarare);
• dupa apasarea butonului OK si inchiderea ultimei ferestre de dialog, in campul DATA va fi afisat butonul Count of GRUPA;
• se gliseaza butonul GRUPA in campul ROW (linie) pentru a utiliza aceasta denumire ca titlu de linie. Deasupra campului ROW se va afisa butonul GRUPA (figura nr.13);
• se actioneaza butonul Next pentru a trece la urmatorul pas;

Figura nr.13 - Construirea tabelului pivot

• in fereastra de dialog nou deschisa (Step 4 of 4), campul Pivot Table Starting Cell se completeaza cu adresa celulei la care va incepe afisarea tabelului pivot (F1) in cadrul foii de calcul, iar campul Pivot Table Name va contine numele tabelului pivot;
• dupa actionarea butonului Finish, foaia de calcul va aspectul din figura nr.14.

Figura nr. 14 - Aspectul final al foii de calcul

Teme propuse

1. Sa se lanseze in executie tabelatorul Excel si sa se completeze celulele primei linii cu titlurile de coloane din figura nr.15. Foaia de lucru va purta numele Studenti, iar fisierul creat se va numi Stud.xls.

2. Sa se introduca datele din figura nr.15 in tabelul nou creat, prin intermediul unui formular de date (Form), apoi sa se sorteze crescator dupa numarul matricol inregistrarile din baza de date creata.

3. Sa se adauge o noua coloana care sa contina media studentului pe ultimul semestru. Se vor completa celule respectivei coloane, dupa care se va calcula media generala a studentilor cu medii mai mari decat 8.

4. Sa se calculeze, folosind un tabel de sinteza, numarul studentilor din fiecare an de studiu. Sa se rezolve aceeasi problema cu functia DCount.