Baze de date (Liste) în Excel

 

În această parte vom rezolva exerciţii de sortare şi filtrare avansată a bazelor de date. Vom grupa datele pentru a putea calcula subtotaluri şi vom realiza validări de date. Vom obţine situaţii centralizatoare de tip matriceal prin tabele pivot sau vom centraliza datele  într-un fişier nou.

 

Operaţii care trebuie cunoscute

 

-          Sortare date: poziţionare în tabel→fila Pornire→Sortare şi filtrare sau fila Date→Sortare

-          Filtrare date: poziţionare în tabel, fila Pornire→Filtru sau fila Date→Filtru/ Complex

-           Subtotaluri: se ordonează tabelul după câmpul care calculează subtotalul, fila Date→Subtotaluri.

-          Validare date: poziţionare în tabel, Date→Validare date.

-          Definire listă particularizată: selectăm coloana în care se vor introduce valorile din listă, Date→Validare→Setări→Se permite şi alegem Listă după care definim valorile din listă despărţite prin virgulă în caseta Sursă.

-          Tabele pivot: fila Inserare→PivotTable sau PivotChart

-          Centralizarea datelor: deschidem un registru nou şi alegem Date→Centralizare

 

 

 

 

Aplicaţia 1

 

Introduceţi următoarea structură de tabel care va conţine 20 rânduri şi pentru care vom defini următoarele reguli de validare a datelor:

 

 

1.      Pentru prima coloană, câmpul va fi de tip text cu lungimea cuprinsă între 12 şi 25

 caractere.

2.      Pentru coloana Clasa se va defini o listă formată numai din valorile 10A, 10B, 10C şi 10D.

3.      Pentru coloana Căminist vom defini o listă cu valorile Da / Nu.

4.      Coloanele cu medii vor conţine numai valori numerice cuprinse între 1 şi 10.

 

Rezolvare:

 

a.

1.      Selectăm întreaga coloană începând de la celula A2 până la A21.

2.      Alegem fila Date→Validare date→Setări.

3.      Din caseta Se permite alegem Lungime text , la Date alegem operaţia între, la minim scriem 12 şi la maxim 25. OK.

 

4.      Dacă introducem un nume mai lung de 25 caractere sau sub 12 caractere o să apară un mesaj de eroare specific programului. Dar putem modifica noi textul mesajului astfel:

5.      Date→Validare date→Mesaj de intrare. Datele le puteţi completa ca în figura de mai jos şi OK. Astfel că de fiecare dată când veţi activa o celulă din domeniul în care s-a impus mesajul de intrare pentru validare (A2:A21)  va apărea o fereastră de atenţionare cu titlul atentie şi mesajul scris de noi.

 

 

6.      De asemenea, putem introduce un mesaj care să apară atunci când generăm o eroare  folosindu-ne de tab-ul Avertizare la eroare (vezi figura de mai jos).

 

Mesajul de avertizare va arăta ca în figură:

 

 

b.

1.      Selectăm domeniul B2:B21 adică coloana Clasa.

2.      Deschidem Date→Validare date→Setări.

3.      Completăm datele ca în figura de mai jos.

 

 

4.      În dreptul fiecărei celule active din coloana Clasa va apărea o săgeată de derulare. Apăsăm pe ea şi alegem una din valorile dorite.

 

 

c.

1.      Pentru coloana Căminist vom proceda ca la punctul b. Lista va formată din valorile DA, NU.

 

d.

1.      Selectăm celulele din cele două coloane cu medii.

2.      Alegem în prima casetă opţiunea Zecimal deoarece introducem numere cu zecimale. Apoi selectăm operatorul între şi vom scrie limitele : minim =1 şi maxim =10.

3.      Pentru ajutor putem crea şi un mesaj ajutător ca şi mesaj de intrare sau poate chiar ca şi Avertizare la eroare.

 

 

 

 

 

Aplicaţia 2

 

Folosind acelaşi tabel de la prima problemă vom realiza ordonări ale datelor, şi anume:

a.       Să se ordoneze tabelul alfabetic după câmpul Nume elev. Faceţi o captură de ecran şi salvaţi-o într-un fişier din folderul personal.

b.      Să se ordoneze tabelul ascendent după câmpul Clasa. La aceeaşi clasă ordonaţi după câmpul Căminist, alfabetic. Faceţi o captură de ecran şi salvaţi-o într-un fişier din folderul personal.

c.       Introduceţi o nouă coloană cu numele Media finală şi ordonaţi tabelul descrescător după ea. Faceţi o captură de ecran şi salvaţi-o într-un fişier din folderul personal.

 

Rezolvare:

 

a.

1.      Activăm o celulă din tabel. Din fila Date selectăm Sortare.

2.      Din prima casetă text Sortare după derulăm şi alegem câmpul Nume elev. Deoarece se cere o ordonare alfabetică vom alege în caseta Ordine , De la A la Z. Şi OK.

3.      Apăsăm Print Screen şi deschidem utilitarul Paint . Din meniul Edit alegem Paste şi apoi salvăm fişierul în folderul personal sub numele de Ordonare1.jpg.

 

 

b.

1.      Activăm o celulă din tabel. Din fila Date selectăm Sortare.

2.      Din prima casetă text Sortare după derulăm şi alegem câmpul Clasa. Deoarece se cere o ordonare alfabetică vom selecta opţiunea De la A la Z în caseta Ordine..

3.      Apăsăm pe butonul Adăugare nivel, derulăm a doua casetă cu numele Apoi după şi alegem câmpul Căminist. Selectăm opţiunea De la A la Z în caseta Ordine din dreptul câmpului Căminist.

 

 

4.      Apăsăm Print Screen şi deschidem utilitarul Paint . Din meniul Edit alegem Paste şi apoi salvăm fişierul în folderul personal sub numele de Ordonare2.jpg.

 

 

c.

1.      Introducem noua coloană cu numele Media finală.

2.      Apelăm funcţia Trunc şi apoi Average pentru a calcula media între cele două note cu o precizie de două zecimale:  =TRUNC(AVERAGE(D2:E2),2).

3.      Din fila Date selectăm Sortare.

4.      În prima casetă text Sortare după derulăm şi alegem câmpul Media finală. Deoarece se cere o ordonare descrescătoare vom alege opţiunea: De la cel mai mare la cel mai mic şi OK.

4.      Apăsăm Print Screen şi deschidem utilitarul Paint . Din meniul Edit alegem Paste şi apoi salvăm fişierul în folderul personal sub numele de Ordonare3.jpg.(vezi figura)

 

 

 

 

 

Aplicaţia 3

 

Folosind acelaşi tabel de la prima problemă vom defini filtre de afişare ale datelor, şi anume:

a.      Filtru ce va afişa doar elevii din clasa 10C ;

b.      Filtru de afişare a elevilor al elevilor din clasa 10D şi care sunt căminişti;

c.       Se vor afişa numai elevii cu media scris cuprinsă între 6.50 şi 8.45.

 

Rezolvare:

 

a.

1.      Activăm o celulă din tabel.

2.      Deschidem fila Date → Filtru. În dreptul fiecărei coloane va apărea o săgeată de derulare.

3.      Apăsăm pe săgeata din dreptul coloanei Clasa şi bifăm numai 10C .

 

Tabelul ar trebui să arate ca în figura de mai jos.

 

 

 

 

b.

1.      Pentru a afişa toate valorile din tabel apelăm Date→Filtru sau Date→Golire.

2.      Apăsăm pe săgeata din dreptul coloanei Clasa şi alegem 10D. Apoi apăsăm pe săgeata corespunzătoare coloanei Căminist şi alegem valoarea DA.

 

c.

1.      Reafişăm toate datele tabelului.

2.      Derulăm lista din dreptul câmpului Media scris şi alegem Filtre de număr→Filtru particularizat.

 

3.      În prima casetă alegem operatorul matematic „este mai mare sau egal cu”.  Pe aceeaşi linie vom scrie în a doua casetă text valoarea 6.50.

4.      Bifăm căsuţa Şi,deoarece este vorba de două condiţii ce trebuie îndeplinite simultan.

5.      În a doua casetă text alegem operatorul matematic „este mai mic sau egal cu” şi în caseta aflată pe aceeaşi linie vom scrie valoarea 8.45. Şi OK.

 

 

6.      Tabelul ar trebui să arate ca mai jos:

 

 

 

 

 

 

Aplicaţia 4

 

Pe acelaşi tabel vom aplica o filtrare avansată care să permită vizualizarea elevilor:

a.       Din clasa 10A sau 10C ;

b.      Care au media la scris sub 7.50;

c.       Care au media la oral cuprinsă între 5 şi 9;

d.      Care stau în cămin şi au media la oral 10;

e.       Care au medii egale la scris şi oral.

 

 

Rezolvare:

 

a.

1.      În coloana liberă de lângă tabel vom scrie condiţia de filtrare, şi anume:

-          În celula H1 vom reproduce exact numele coloanei ce apare în condiţia de filtrare: Clasa sau , pentru a fi siguri că nu vom greşi vom edita formula: =B1.

-           Sub celula H1 vom scrie prima valoare a condiţiei adică 10A

-          În celula I1 vom scrie, la fel numele câmpului: Clasa iar dedesubt , în celula I3 vom scrie a doua valoare căutată: 10C .

-          Cele două valori căutate,  nu le-am scris pe aceeaşi linie, deoarece între ele există operatorul SAU care se simulează în acest mod.

 

 

2.      Activăm o celulă din tabel şi apoi alegem din meniul Date→Complex.

3.      Bifăm în căsuţa Copiere în altă locaţie. În general, caseta Zonă listă este gata încărcată cu domeniul de celule ocupat de tabel. Dacă nu este gata scris atunci vom da click în această casetă şi vom selecta noi întreg tabelul.

4.      Dăm click în caseta Zonă de criterii. Vom selecta zona în care am editat condiţiile cerute de problemă , adică H1:I3, care se va scrie automat în aceasta.

5.      Dăm click în caseta Copiere în. Selectăm o zonă corespunzătoare sub tabel, puţin mai mare decât am considera necesar că vor intra noile date. Apoi OK.

6.      Sub tabel, corespunzător zonei selectate, va apărea noul tabel cu datele filtrate.

 

 

 

b.

1.      În următoarea coloană liberă de lângă tabel vom scrie condiţia de filtrare, şi anume:

-          În celula J1 vom reproduce exact numele coloanei ce apare în condiţia de filtrare: Media scris sau , pentru a fi siguri că nu vom greşi vom edita formula: =D1.

-           În celula J2 vom edita formula: <7.50

2.      Repetăm aceeaşi paşi ca la punctul a. , modificând corespunzător Zona de criterii şi Copiere în. Şi terminăm cu OK.

 

 

 

c.

1.      În următoarea coloană liberă de lângă tabel vom scrie condiţia de filtrare, şi anume:

-          În celulele K1 şi L1 vom reproduce numele coloanei ce apare în condiţia de filtrare: Media oral  sau  edităm formula: =E1.

-           În celula K2 vom edita formula: >=5 iar în celula L2 avem formula : <=9 .

-          Cele două valori căutate se vor scrie pe aceeaşi linie, deoarece între ele există operatorul ŞI care se simulează în acest mod.

2.      Repetăm aceeaşi paşi ca la punctul a. , modificând corespunzător Zona de criterii (K1:L2) şi Copiere în. Şi terminăm cu OK.

 

 

d.       

Pentru simularea condiţiei cerute în acest subpunct , zona de criterii se va edita astfel:

 

e.        

Pentru simularea condiţiei cerute  zona de criterii se va edita astfel:

 

 

 

 

 

Aplicaţia 5

 

Într-un magazin se comercializează următoarele produse din tabel, în primul trimestru al anului. Multiplicaţi aceleaşi produse pentru următoarele trei trimestre modificând cantităţile.

Creaţi următorul tabel şi calculaţi subtotalurile:

 

Trimestru	Denumire produs	Cantitate	Preţ unitar	Total
I	Calculator	15	1500	22500
I	Monitor	15	680	10200
I	Căşti	10	12	120
I	Tastatura	23	13	299
I	Microfon	6	6	36
I	Birou	28	120	3360
I	Scaune	28	90	2520
I	Mocheta	13	34	442

 

a.       Care este totalul vândut din fiecare produs, pe tot anul?

b.      Ce cantitate din fiecare produs s-a vândut la sfârşitul anului?

c.       Care este totalul vânzărilor la sfârşitul fiecărui trimestru?

 

 

Rezolvare:

 

a.

1.      Primul pas constă în a ordona tabelul după câmpul sau câmpurile în funcţie de care se grupează datele. În cazul nostru gruparea se va realiza după câmpul Denumire produs.

2.      Din fila Date alegem Subtotaluri.

3.      În prima casetă text alegem câmpul care arată locul unde apare scris subtotalul. El este acelaşi cu cel după care se realizează gruparea datelor, (Denumire produs).

4.      În a doua casetă selectăm funcţia ce va calcula operaţia cerută în enunţul problemei, (Sumă).

5.      În a treia casetă bifăm câmpul ce participă la efectuarea calculului (Total).

 

 

b.

1.      Deschidem Date→Subtotaluri.

2.      În prima casetă text alegem câmpul Denumire produs.

3.      În a doua casetă selectăm funcţia ce va realiza operaţia matematică, adică Sumă.

4.      În a treia casetă bifăm câmpul unde se va efectua calculul, adică Cantitate.

 

 

c.

1.      Se va ordona tabelul după câmpul Trimestru.

2.      În fereastra de dialog deschisă cu Date→Subtotaluri vom alege pe rând:

-          În prima casetă: Trimestru.

-          În a doua casetă: Sumă.

-          În a treia casetă: Total.

 

 

 

 

Aplicaţia 6

 

Realizaţi următorul tabel ca va conţine elevi din clasele 9-12 participanţi la olimpiada TIC:

 

Nume elev

Clasa

Zona (urbană/rurală)

Nota TIC

 

Cu ajutorul tabelelor pivot centralizaţi datele astfel încât:

a.       Să se afişeze numărul de elevi care au luat aceeaşi notă pe clase.

b.      Să se afişeze numărul de elevi care au luat aceeaşi notă - pe clase şi separat pe zone.

 

Rezolvare:

 

a.

1.      Din fila Inserare alegem PivotTable.

2.      La pasul 1 este selectată zona în care se află tabelul şi foaia de lucru în care se va crea pivotul.

3.      La Pasul 2 vom alege câmpurile din partea dreaptă a ecranului (Listă de câmpuri Pivot Table) pentru a construi raportul pivot ce va centraliza datele.

-          Vom glisa cu mouse-ul câmpul clasa din dreapta ferestrei peste zona RÂND

-          Vom glisa câmpul nota TIC peste zona COLOANĂ

-          În zona VALORI ( elemente de date) aducem câmpul nume şi prenume deoarece trebuie număraţi elevii şi în acest câmp avem numai valori distincte (plecăm de la presupunerea că nu avem doi elevi cu acelaşi nume şi prenume).

-          În zona PAGINĂ aducem câmpul zona.

 

 

4.      Aceleaşi glisări de câmpuri le putem realiza in meniul din dreapta – Listă de câmpuri PivotTable. În zona VALORI trebuie stabilită funcţia ce realizează numerotarea elevilor. Acest lucru îl realizăm dând click pe comanda Setări câmp ( stânga filei Opţiuni). Din lista de funcţii alegem Contor. OK.

 

 

5.      Raportul pivot va arăta ca în figura de mai jos.

 

 

 

b.       

Pentru a stabili numărul elevilor separat pe zone vom alege din colţul stânga sus zona , apăsând pe săgeata de derulare.

 

 

Observaţie:

 

Dacă apar modificări ale datelor în tabelul original, pentru a reface raportul pivot trebuie să apăsăm pe semnul exclamării de culoare roşie (Reîmprospătare date) din meniul Pivot Table.