Lectia 11 Notiuni de baze de date si subtotaluri

Scopul lectiei:

Baze de date

Subtotaluri

Ce este o Baza de date

Pentru EXCEL si un simplu tabel (lista) poate fi considerat o baza de date (BD).

Teoretic o lista daca are cel putin 2 linii si mai multe coloane este o BD.

Se obisnuieste sa se înteleaga prin BD, tabele ori pe mai multe foi, ori tabele mai complicate, în sensul ca au mai multe coloane (nu doua, trei si destule rânduri ca sa faca greoaie parcurgerea lor "din ochi").

Un magazin de cartier are câteva mii de produse, care înseamna tot atâtea mii de rânduri, iar vizualizarea lor înseamna sute de ecrane. O baza de date facuta în EXCEL rezolva multe probleme, încât nu e nevoie sa stiti FoxPro sau ACCESS. Iar daca stiti, cu atât mai bine, pentru ca exista o conlucrare EXCELenta între aceste 3 produse-program, care sunt facute de aceiasi firma. Functiile VLOOKUP si HLOOKUP ajuta la crearea unei baze de date (prescurtat BD).

Pentru exploatarea unei BD e necesar sa învatam sa facem:

Cum se fac sortarile

Sortarea este cea mai importanta operatie din prelucrarea automata a datelor si cea mai des folosita. Termenul de sortare în informatica înseamna a aranja într-o anumita ordine, crecatoare sau descrescatoare. Nu are legatura cu termenul literar. E adevarat ca în urma sortarii unui tabel din informatica poate rezulta si o selectie, 19119t192t dar aceasta e o întâmplare nesemnificativa. Apelarea operatiunii de sortare se poate face în 2 moduri:

Când e vorba sa sortam, prima grija este sa facem un clic în lista, sa aratam ce lista este supusa sortarii. Altfel ne trezim cu mesajul:

Fig 11.2

Dupa Clic pe o celula din lista, clic pe Date si apoi clic pe Sortare apare:

Aici e nevoie de explicatii: Sortarea se poate face dupa 3 coloane, adica dupa 3 chei. Nu e nevoie sa scriem cheile, ca le mai si gresim! Ele se gasesc în meniul ascuns din Fig 11.4. Daca este nevoie de 3 chei la fel se procedeaza cu fiecare.

Ordinea Ascedent sau Descedent nu are importanta întotdeauna. Ramâne la aprecierea utilizatorului, dar mentiunea daca lista are sau nu are Rând antet, e foarte importanta. Daca nu este marcata existenta Rândului antet, acesta intra în sortare! Rezultatul este Fig 11.5 dreapta, unde capul de tabel a ajuns printre rândurile tabelului, complet aiurea.

Ce înseamna structura

BD este obligatoriu o lista structurata. Exemplu: Un tabel (lista) contine mai multi clienti, un client are mai multe facturi, si pe o factura sunt mai multe produse si fiecare produs ocupa un rând în tabel. Aceasta lista este o într-adevar o BD.

Exista o structura Tabel (lista) Client Factura Produs ca în Fig 11.16.

Fig 11.6 (Structura schematica a unei liste structurate)

Avem:

3 clienti (se pot repeta în tabel, pentru ca un client poate veni de mai multe ori sa cumpere),

7 facturi (nu se pot repeta, fiindca nu permite legea) si

3 produse (se pot repeta, dar nu pe o factura si nu este obligatoriu ca sa fie toate trei produsele pe o factura)

În realitate exista pentru exemplul de mai sus, o structura mai complexa, pentru ca nu este suficient sa spunem "o factura", atâta timp cât factura are numar si data. Nu putem spune numai "un produs" cât timp produsul are: cod, denumire si pret.

În Fig 11.2 este un tabel (lista) care poate fi o BD. E vorba de înregistrarea unor facturi. Fiecare factura este scrisa pe mai multe rânduri, câte produse sunt pe ea. Livrarea marfurilor se poate face din 3 depozite, dar se emite o singura factura, lucru specific depozitelor en-gros. Sunt 351 de rânduri, adica s-au emis 39 facturi, fiecare cu 9 produse, pentru 4 clienti: Maria srl, Ionica srl, Costica srl si Vasile srl.

Tabelul din Fig 11.7 are urmatoarele semnificatii ale coloanelor :

În tabelul din Fig 11.7a si Fig 11.7b sunt introduse facturile pentru produsele vândute, pe masura ce aceste facturi au fost emise. Exista cîte 3 produse în fiecare depozit, dar nu aceleasi. Un depozit este o gestiune. În realitate un depozit poate fi alcatuit din mai multe gestiuni. Nu exista o regula prestabilita.

Urmariti capul de tabel. Fiecare nume (titlu) de coloana este o data (informatie).

Fig 11.7 (O parte din tabelul facturilor)

Fig 11.8 (În total sunt 39 facturi, 351 linii)

Putem face un total al tuturor valorilor. Este 68.871,85 RON. Aceasta informatie spune prea putin. Aici trebuie sa intervina operatiunea de obtinere a subtotalurilor, care furnizeaza mult mai multe informatii. Este nevoie de totaluri pe facturi, pe zile, pe clienti etc.

Ce înseamna subtotaluri

Subtotal înseamna un total partial, spre deosebire de Total general.

• Dorim sa se obtina un subtotal al valorii marfurilor separat pe fiecare factura acesta este un Subtotal. Pot fi si altele. Fig 11.9.
• Dorim si un Total general, adica al tuturor facturilor emise în mai multe zile si pentru mai multi clienti.

Fig 11.9 (Subtotaluri pe facturi cu detalierea facturilor)

Daca se doresc subtotaluri pe facturi se va obtine un tabel ca în Fig 11.9.

Observati ca exista o totalizare pe fiecare factura în parte, dar se vede si continutul fiecarei facturi. Putem face ca sa fie afisate numai facturile cu totalul valorii lor, Fig 11.11.

Facând automat aceste Subtotaluri în partea stânga a ecranului, mai la stânga numerelor rândurilor a aparut niste coloane noi 1, 2, 3 care indica nivelurile de ierarhizare.

Numai când se cere sa se faca Subtotaluri, apar aceste nivele de ierarhizare. Daca facem clic pe ele se obtine: 1 = numai Total general, 2 = Total general si total pe facturi, 3 = Total general, total pe facturi si detalierea fiecarei facturi în parte.

Fig 11.10

Fig 11.11 (Subtotaluri pe facturi fara detalierea facturilor nivelul 2)

Descrierea nivelurilor de însumare

Un Total pe factura poate fi numit si nivel de însumare. Cu alte cuvinte s-a facut o însumare pe facturi. Din punct de vedere al unei BD nivelele de însumare sunt si nivele de agregare.

Nivelul 3 înseamna afisare ca în  Fig 11.9 adica o afisare detaliata. Nivelul 2 înseamna afisare ca în  Fig 11.11 adica afisarea numai a totalurilor pe factura. Nivelul 1 înseamna afisare ca în Fig  11.16 adica Totalul general. Nu uitati si de afisarile partiale când se face clic pe un plus ca în Fig 11.9 si 11.13

Sunt 3 nivele de afisare, de ierarhizare (afisare)

Fig 11.12 (Afisarea nivelelor de agregare)

Facând clic pe nivelul 2 se obtine Fig 11.13 si facând clic pe butonul cu plus, detaliem continutul ascuns al facturii respective (Factura 100039 în cazul nostru). Butonul plus s-a transformat în butonul minus si daca facem clic cu butonul stâng pe minus dispare (se ascunde) continutul detaliat al facturii respective.

Fig 11.13

Ce înseamna date agregate

Date agregate sunt acele date care contin mai multe informatii, de cât o data simpla, primara. Pretul este o data primara, valoarea este o data agregata, pentru ca rezulta din înmultirea pretului cu cantitatea. Totalul valorii marfurilor este o data agregata s.a.m.d.

Agregarea datelor si Obtinerea de date agregate este acelasi lucru. Operatiunile pe care le efectuam asupra informatiilor dintr-o BD au tocmai scopul de a obtine date agregate.

Exemple de date agregate:

Fig 11.14(Subtotaluri pe depozite)

Fig 11.15 (Subtotaluri pe clienti)

Retineti termenii asemanatori: Nivel de subtotal, nivel de agregare, nivel de însumare.

În Fig 11.14 si Fig 11.15 avem tot lista facturilor dar structurata diferit! Din una si aceiasi lista la un moment dat nu poti obtine decât una din cele doua figuri.

Cum valorificam informatiile din Subtotaluri

Daca facem clic cu butonul stâng pe nivelul 1 se obtine urmatorul tabel:

Fig 11.16

Daca facem clic pe nivelul 2 obtinem ori Fig 11.14 ori Fig 11.15, daca facem clic pe nivelul 3 obtinem Fig 11.9. Deci unul si acelasi tabel (lista) poate avea 3 forme de baza, plus forme intermediare. Acum putem vorbi de o baza de date sau cu alte cuvinte, avem o lista structurata! O lista a prietenilor nu este o lista structurata si nici nu o putem numi baza de date în adevaratul sens al cuvântului.

Cum se obtin tabele cu Subtotaluri pe facturi

În primul rând trebuie facuta o sortare. Sortarea trebuie facuta, chiar daca se pare ca este deja facuta. Usor ne putem însela. Este prea simpla ca sa sarim peste ea.

Clic pe coloana si apoi clic pe si lista este sortata.

În majoritatea cazurilor functia este sum (suma) dar poate fi si medie, etc. Utilizatorul este cel care decide. Pentru ca el stie ce vrea!.

Aici sunt meniuri ascunse, liste derulante cum se mai numesc. Facem clic cu butonul stâng pe vârful de sageata si alegem ce dorim (si în acelasi timp ce este logic) Aici este cheia dupa care am sortat si dupa ea se vor face subtotalurile

Fig 11.18 (Stabilirea felului de subtotal dorit, aici Subtotal pe facturi)

Urmariti Fig 11.8 si recapitulam cum se obtine subtotaluri pe factura (adica pe numar de factura). Sa interpretam optiunile oferite de Fig 11.18

În final facem clic pe OK în Fig 11.18 si se obtine Fig 11.13. Alegându-se nivelul 2 sau 1, se obtin celelalte liste cu subtotaluri.

Observatii:.

• Sa nu încurcam coloana pe care (dupa care) am facut sortarea si în Fig 11.18 la optiunea La fiecare modificare sa notam alta coloana. E dezastru! Priviti exemplul urmator.

(Sortarea a fost facuta pe NRF iar "La fiecare modificare" din Fig 11.18 a fost marcat DEN)

• Sa nu cerem totaluri absurde, cum ar fi total pe pret, pentru ca nu are sens.
• Sa nu bifam cantitatea pentru ca se va aduna cantitati din produse diferite
• Bifam si VAL si CANT atunci si numai atunci, când facem subtotal pe CODP sau pe DEN, pentru se vor aduna cantitati de acelasi fel (din acelasi produs), ceea ce are sens.

Când se considera ca s-a gresit si dorim imaginea initiala a tabelului facem clic cu butonul stâng pe Eliminare totala. (din Fig 11.8).

Daca am obtinut subtotaluri pe facturi, ori le tiparim, ori le salvam sub un alt nume (Save as. sau Salvare ca) si facem clic pe Eliminare totala din Fig 11.8 pentru a reveni la forma initiala a tabelului. Tabelul ramâne sortat ! Se elimina numai Subtotalurile.

Nota: Dupa ce am încarcat lista cu toate facturile, facem urmatoarele redenumiri în Foaie de lucru. Copiem întreaga lista în Sub-Fact si în Sub-Clienti si facem subtotalurile în aceste foi.

Obtinerea tabelului cu Subtotaluri pe Gestiuni (depozite)

Pentru subtotaluri pe alte criterii, chei principale (client, data facturii, produs) o luam din nou de la sortare si se obtine o alta situatie[3]. Exemplu: Dorim sa se obtine lista din Fig 11.19, unde cheia principala de sortare este CGEST.

Fig 11.19a

Fig 11.19b

În Fig 11.19a avem un Total general pe gestiuni, subtotaluri pe fiecare gestiune în parte si posibilitatea afisarii continutului fiecarei gestiuni. S-a facut clic pe semnul plus al liniei 237 unde avem Birot Total si s-a obtinut Fig 11.19b.

Aceste lucruri se obtin procedând astfel:

Facem sortarea pe CGEST vezi Fig 11.20 (stânga) adica pe gestiuni-depozite si apoi operatiile pentru obtinerea unui subtotal Date Subtotal. Vezi Fig 11.20 (dreapta) în final am putut obtine totaluri valorice pe gestiuni-depozite).

De retinut ca nu ne-am propus decât Total general pe gestiuni si subtotaluri pe fiecare gestiune. Continutul din Fig 11.19b poate apare dezordonat, pentru ca nu am facut decât o sortare pe o singura cheie.

Fig 11.20 (Obtinerea de subtotaluri pe Gestiuni-depozite)

În concluzie am sortat pe o coloana si am obtinut subtotaluri pe ce reprezinta coloana aceea (sau pe facturii, sau pe zile, sau pe depozite-gestiuni, sau pe clienti, sau pe produs si atât ca în rest nu are sens).

Nota: S-a obtinut subtotaluri numai pe o coloana! Se pot obtine subtotaluri si pe mai multe coloane! Se vor folosi mai multe chei de sortare. Asta înseamna ca vor fi mai multe nivele de agregare nu numai 3.

Prin sortarile ce se vor face uneia si aceleiasi liste i se va stabili o alta structura.

Ceva mai complicat Subtotaluri pe Gestiuni, Clienti si Facturi

Dorim un tabel (lista) unde sa avem subtotaluri pe mai multe coloane. (Fig 11.21).

Cu alte cuvinte dorim sa avem în final:

Subtotaluri pe Gestiuni   CGEST

Subtotaluri pe Clienti în cadrul unei gestiuni CLIENT

Subtotaluri pe Facturi pentru fiecare client NRF

În Fig 11.21 avem 5 nivele de agregare.

Sortarea se va face dupa trei chei exact ca în Fig 11.22. Subtotalul pe prima cheie de sortare da 3 nivele de agregare si apoi subtotalurile pe urmatoarele chei de sortare adauga câte un nivel de agregare.

Fig 11.21 (Sunt 5 niveluri de ierarhizare)

Trebuie acordata o atentie deosebita sortarii, Ordinea cheilor e importanta!

În continuare va trebui sa facem de trei ori câte un subtotal adica :

• întâi pe CGEST  apoi
• pe CLIENT  si apoi
• pe NRF (este ordinea cheilor de sortare!)

Sa avem grija, ca sa nu fie marcata optiunea Înlocuire subtotaluri curente, ca sa nu se stearga ce am realizat în etapa anterioara. Cu alte cuvinte facem o succesiunea de operatii de subtotaluri. (Sfârsit de pagina între grupuri sa nu fie marcat, ca se consuma foarte multa hârtie!)

			Nu recomand varianta aceasta. Consuma multa hârtie! Aici lasati bifa
	Aici sa fie spatiu.

Fig 11.23

Fig 11.24 (Nivelul 1 Total General)

Observatie. Când s-a urmarit obtinerea unui subtotal pe o singura coloana nu avea importanta bifa din Fig 11.23. În cazul de fata când se va obtine subtotaluri de mai multe ori , daca optiunea este bifata un nou subtotal va înlocui pe altul anterior. În final se obtine tabelul (lista) din Fig 11.24 care este nivelul 1. Apar 3 Totaluri generale pentru ca de 3 ori s-a facut câte un subtotal. stergem ultimele doua.

Fig 11.28

Nivelul 5 când sunt afisate asa numitele date primare reprezinta lista initiala ca atare.

Nivele 4, 3 , 2 si 1 sunt nivele superioare si ele prezinta aceleasi date, dar agregate.

În Fig 11.30 sunt prezentate nivele totale si cele partiale. Daca le încercati va veti familiarizati cu ele.

Nivele partiale pentru expandarea informatiilor pentru un depozit, un client sau o factura.

Fig 11.30 (Reprezentarea complexa a tabelului dupa obtinerea subtotalurilor)

În acelasi tabel ne putem pozitiona pe un nivel total si în cadrul lui se poate activa unul sau doua nivele partiale ca în Fig 11.31

A) clic cu butonul stâng pe minus

B) clic cu butonul stâng pe plusul ce se gasea aici

Fig 11.31 (Modalitati de consultare a Bazei de date)

Scopul acestor manevre este acela de a consulta baza de date. De aici începe ceea ce se numeste navigarea într-o baza de date. Operatiunei de navigare, i se mai spune consultare.

Aceleasi date dar alta structura.

Am realizat pâna acum din aceiasi lista Fig 11.2 urmatoarele liste cu subtotaluri

Fig 11.31

Ne folosim de acelasi tabel pentru ca sa obtinem situatii pe clienti, lucru pe care nu s-a facut pâna acum, pentru ca un client aparea la toate depozitele de unde a cumparat. Acum dorim sa avem situatii în care clientul sa fie prioritar. Sa avem total pe clienti, indiferent de la care depozit (gestiune) a cumparat.

Fig 11.33 (Nivel 5)

Fig 11.34 (Nivel 4)

Fig 11.35 (Nivel 3)

Fig 11.36 (Nivel 2)

Fig 11.37 (Nivel 1)

Concluzie

Comparati tabele din Fig 11.29 si cel din Fig 11.33.

Ele au structuri diferite, chiar daca e vorba de exact aceleasi facturi.

Structura diferita se datoreste sortarilor diferite.

Fig 11.38 (Compararea cheilor de sortare)

Subtotaluri pe zile

Se pot crea si alte structuri, facând alte sortari. Ex:

DATAF

CGEST

Nu e obligatoriu sa fie 3 chei.

Nivelul 1 este mereu acelasi, fiind Totalul general al livrarilor

Celelalte nivele au alta structura si alte informatii.

Fig 11.39 (Nivelul 2 Totalurile pe zile)

Fig 11.40 (Nivelul 3 Totaluri pe zile si gestiuni în fiecare zi)

În Fig 11.40 exista posibilitatea expandarii unui depozit pentru a vedea ce clienti au cumparat. De retinut ca si aici o factura apare la toate depozitele. E normal sa fie asa pentru ca pe aceiasi factura s-au cumparat marfuri din toate cele 3 depozite.

Fig 11.41 Nivelul 3 (Totalurile zile si gestiuni pe fiecare zi cu expandarea unui depozit)

Fig 11.41 (Nivelul 4 )

Atentie:

Vorbim de subtotaluri dar pe ecranul EXCEL nu apare cuvântul Subtotal.

Retineti ca:

• Exista un singur TOTAL care este TOTALUL GENERAL al întregii Liste(Tabel)
• Restul sunt TOTALURI PARŢIALE si pe acestea le numim Subtotaluri.

Cum anulam Subtotalurile

Avem nevoie sa anulam lista cu Subtotaluri dupa ce am tiparit-o sau am salvat-o sub un nume diferit, de cel avut initial. Anularea subtotalurilor este necesara pentru a face noi sortari ca sa obtinem liste cu alte Subtotaluri, adica cu alta structura.

Fig 11.42

Mai multe Subtotaluri

Daca avem urmatorul cap de tabel, unde a aparut si coloana TRIM (trimestru). Dorim sa avem si un subtotal pe trimestre. Pâna acum am facut 3 sortari, ca atâtea ne sunt permise (Fig 11.22) si de atâtea aveam nevoie. Acum trebuie sa facem urmatoarele 4 sortari în doua etape:

Fig 11.43

O simpla sortare, la început, dupa numarul de factura (NRF), sortare care nu va intra în stabilirea subtotalurilor, dar va permite ca în cadrul aceleiasi zile sa fie afisate în ordine facturile pentru ca facturile vor ramâne sortate în ordinea crescatoare a NRF.

Sortare pe trei coloane CGEST, CLIENT, TRIM sortare clasica care va conta la stabilirea Subtotalurilor.

Se vor face apoi subtotaluri pe CGEST, CLIENT, TRIM, DATAF cu atentie la Fig 11.23 si se obtine Fig 11.44.

Fig 11.44

Lista structurata ca în Fig 11.44 poate avea extrem de multe variante, depinde câte "plusuri" si "minusuri" sunt activate. Exista 6 nivele de agregare, în cadrul fiecarui nivel se pot deschide (expanda) unul sau mai multe nivele inferioare. Totul trebuie facut cu discernamânt si daca s-a gresit se reia de la început. La sfârsitul listei se va pastra numai un singur Total general. De remarcat este faptul ca am avut 3 chei de sortare:

CGEST, CLIENT, TRIM

Dar am facut 4 subtotaluri, dupa:

CGEST, CLIENT, TRIM, DATAF

În final treaba a iesit bine, fiindca am facut acea sortare pe NRF, înainte de cele 3 sortari.

Daca nu se facea prima sortare pe NRF (numar de factura), era posibil ca atunci când se expandeaza Subtotal pe zi, facturile nu apar în ordine crescatoare sau descrescatoare. Nu se întâmpla nimic, nu este o greseala, dar nu este frumos!

Fig 11.46

Aceste lucruri se învata pe masura ce lucrati si faceti efortul de a întelege.

Subtotaluri pe produse

Chiar daca nu lucrati într-o societate comerciala retineti aspectele urmatoare. Un conducator (în româneasca noua manager) are atentia îndreptata în 2 directii relativ la tabelul facturilor noastre.

1. Catre cine a vândut marfa sa stie ce bani primeste si atunci face o situatie cu Subtotaluri pe Clienti, facturi
2. Ce marfuri a vândut sa stie cu ce trebuie sa se aprovizioneze si atunci face o situatie cu Subtotaluri pe Depozite si produse

P na acum am rezolvat problemmele desfacerii. Am avut situatii pe depozite, pe clienti, acum sa ne ocupam de aprovizionare.

Pentru aprovizionare urmatoarea situatie este necesara:

Fig 11.47 (Situatia produselor vândute)