Baze de date si calcul tabelar in Microsoft Excel

Obiectivul lucrarii

Microsoft Excel este o aplicatie de calcul tabelar care integreaza sub acelasi program foile de calcul, grafica si administrarea datelor. Excel poate realiza functii matematice, statistice si de organizare a datelor. Acest program poate gestiona baze de date de mici dimensiuni si de complexitate redusa. Face parte din suita de programe Microsoft Office.

In aceasta lucrare se va face o prezentare generala a programului de calcul tabelar Microsoft Excel. Veti invata sa creati, sa modificati, sa formatati, sa realizati calcule, sa sortati si sa interogati o baza de date plata (adica cu un singur tabel). In final aveti o tema pe care o veti include pe pagina web pe care ati creat-o data trecuta in Microsoft Frontpage.

Operatiile pe care trebuie sa le executati voi pe calculator in aceasta lucrare sunt scrise subliniat (underline).

Generalitati

O baza de date in Excel este numita lista fiind o foaie de calcul in care datele sunt grupate pe linii si coloane. Elementele unei liste sunt inregistrarile, campurile si linia de antet.

O inregistrare (record) este o grupare de date ce pot fi tratate si manevrate ca un tot unitar.

Un camp (field) este o informatie distincta in cadrul unei inregistrari.

Linia de antet (header row) reprezinta schema (structura) bazei de date, adica titlurile de coloane de la inceputul listei.

In figura urmatoare este prezentata o baza de date ce cuprinde informatii despre niste persoane si sunt evidentiate elementele ei:

Recomandari la crearea unei baze de date:

Microsoft Excel se porneste apasand: Start->All Programs->Microsoft Excel. Fereastra care se deschide ar trebui sa arate asemanator cu imaginea de mai jos:

 

Crearea unei liste

Crearea unei liste se poate face prin introducerea datelor in celulele foii de calcul sau folosind un formular adecvat.

In primul caz, mai intai se activeaza celula in care urmeaza sa se faca modificari.

Pentru a incepe editarea, se executa un clic pe bara de formule (pentru editare in aceasta zona) sau dublu clic sau <F2> in celula (pentru editare direct in celula). Modul editare este semnalat prin cuvantul Edit afisat in linia de stare.

Se efectueaza modificarile, editarea fiind incheiata de tasta <Enter>.

Realizati urmatoarele operatii: Creati o baza de data noua introducand in celule elementele persoane din prima figura. In afara de cele 11 persoane, mai introduceti inca 5 astfel incat sa apara 2 persoane cu acelasi nume de familie si 3 persoane din aceeasi localitate (la localitati folositi localitatile deja existente). Salvati fisierul cu numele Persoane.xls in directorul X:\public_html.

Exista date numerice si date sir de caractere. Deasemenea in celule mai pot fi introduse formule sau functii.

Daca primul caracter este o litera, data este considerata sir de caractere. Daca incepe cu o cifra este de tip numeric. Textele se aliniaza la stanga in interiorul celulei, in timp ce numerele sunt aliniate la dreapta. Pentru ca numerele sa fie considerate drept text, se tasteaza in fata numarului semnul apostrof. Dupa scriere apostroful nu mai este vizibil in celula iar numarul respectiv apare aliniat la stanga fiind considerat text.

Observatie: Numerele in Excel sunt aliniate la dreapta celulei. In exemplul de mai sus prefixul telefonic va fi de tip text (altfel se va sterge automat 0 din fata). Pentru ca prefixul sa fie considerat de tip text, se introduce semnul apostrof (') inaintea numarului. Atunci acesta se va alinia la stanga si va fi tratat ca text.

Pentru a introduce o fractie, se scrie in fata ei un zero urmat de spatiu. De exemplu, introduceti intr-o celula sirul: "0 1/3" si vizualizati efectul. Stergeti-o apoi.

Daca introducand un numar, el apare in final sub forma unei succesiuni de simboluri diez, inseamna ca celula nu este suficient de lata ca numarul sa poata fi afisat in intregime.

Data si ora se pot introduce in diferite formate (exista 13 formate prestabilite). Daca Excel recunoaste intrarea ca fiind o data sau o ora, aceasta va aparea aliniata la dreapta in celula.

Pentru a potrivi latimea coloanei la dimensiunea celei mai mari date din coloana respectiva se face dublu clic pe linia ce desparte denumirea (litera) coloanei respective si litera coloanei urmatoare. Acum adaptati latimea coloanelor pentru baza de date Persoane.xls.

 

Sortarea si filtrarea

Prin SORTARE se intelege aranjarea intr-o anumita ordine a inregistrarilor.

De exemplu:

1). vrem sa sortam dupa coloana "Nume" crescator (de la A la Z). Pentru aceasta se face clic pe celula "Nume" si se acceaseaza din meniu Data->Sort. Se selecteaza Ascending.

2). vrem sa sortam dupa coloana "Prefix Telefonic" in ordine descrescatoare. Se face clic pe celula "Prefix Telefonic" si se acceseaza Data->Sort. Vom selecta Descending.

Ambele exemple se pot realiza si apasand pe casuta pentru crescator sau  pentru descrescator.

Putem face o sortare avand 2 sau 3 criterii. De exemplu dupa "Nume Localitate" crescator si "An Nastere" descrescator. Facem mai intai clic pe celula "Nume Localitate", accesam din meniul Data->SORT. In casutele Sort by si Then by se face clic pe coloanele pe care vrem sa le sortam incepand cu cea mai importanta. Se selecteaza optiunile si se apasa OK.

Filtrarea presupune extragerea unor date din lista, pe scurt, este un mod simplu de a extrage din baza de date informatii care indeplinesc anumite criterii.

Se selecteaza Data->Filter->Auto Filter. In dreapta fiecarui nume de coloana va aparea o casuta de derulare ().

Daca dati clic cu mouseul pe acest buton, veti gasi o lista care va contine valorile campului respectiv, precum si optiuni de sortare crescatoare sau descrescatoare, afisarea primelor 10 sau se poate face o filtrare mai complexa particulara (Custom...).

Sa facem o filtare Custom pentru a selecta persoanele din Brasov si Bucuresti. Se procedeaza ca in figura:

Salvati baza de date Persoane.xls in directorul X:\public_html.

 

Formule

Creati o alta baza de date cu numele RezistenteParalel.xls (File->New->Blank Workbook). Fisierul il veti salva in X:\public_html.

Aceasta corespunde la doua rezistente legate in paralel ca in figura urmatoare:

Introduceti linia de antet care va fi U, I1, I2, R1, R2, I, R.

FORMAT NUMERE in EXCEL: pentru a introduce numere cu zecimale trebuie folosit caracterul punct. In cazul in care formulele introduse nu functioneaza incercati caracterul virgula.

Un element important este marcajul de umplere a celulei (patratelul din coltul din dreapta jos al celulei). El foloseste la umplerea altor celule dupa cum a fost completata celula curenta. Pentru aceasta se scrie o valoare sau o formula in celula curenta si se trage de marcajul de umplere a celulei tinand butonul stanga al mouse-ului apasat si tragand peste celulele de dedesubt sau de deasupra. Astfel vor fi completate si celelalte celule.

Spre exemplu: dorim ca coloana U sa se completeze cu valori intre 1 si 10 (crescator cu pasul 1). Pentru aceasta, in celula de sub U se scrie 1 si apoi dedesubt se scrie 2. Se selecteaza cele doua celule apasand tasta SHIFT iar apoi se trage de marcajul de umplere a celulei in jos pana cand se obtine valoarea 10 (a se vedea figura de mai jos). (Intrebare: ce am fi obtinut daca in celula de sub U am fis scris 1 iar dedesubt, in urmatoarea am fi scris 3 si am fi tras similar de marcajul de umplere?)

 

Formulele sunt destinate executarii unor calcule si contin valori numerice, operatori, functii predefinite.

Orice formula este precedata de semnul =.

Se pot folosi:

Caracterul ":" este folosit pentru a defini un domeniu. Se mai numeste si operator de domeniu (range operator). De exemplu:

Un alt operator de referire este virgula numit operator de reuniune (union operator). De exemplu:

Sa introducem acum datele in exemplul cu rezistentele legate in paralel.

Coloanele I1 si I2 se vor completa cu valori aleatoare cuprinse intre 0.15 si 1. Se va introduce o formula pentru a automatiza procesul. Formulele se introduc selectand celula si apasand semnul = , dupa care se scrie formula. Stim ca functia rand (de la random (engl.) = aleatoriu) intoarce o valoare aleatoare intre 0 si 1, deci pentru a avea valori aleatoare intre 0.15 si 1, scriem formula: =0.15+0.85*rand() in celula de sub I1. Tragem de marcajul de umplere a celulei pana se completeaza intreaga coloana I1. Se procedeaza similar cu coloana I2. Sa nu va deranjeze faptul ca datele din coloanele corespunzatoare curentilor I1 si I2 se modifica la fiecare recalculare. Aceasta se datoreaza faptului ca functia rand se actualizeaza.

 

Pasul urmator este introducerea unei formule pentru calculul coloanelor R1, R2, I, R. In formula pot aparea referinte absolute care se construiesc cu ajutorul simbolului "$" plasat inaintea codului de coloana si a codului de linie (de exemplu $A$2 - celula de pe coloana A, randul 2) care referentiaza aceleasi celule indiferent de mutarea sau copierea datelor in alta zona. Pot aparea si referinte relative (de exemplu A2).

Selectam casuta de sub R1 (adica celula D2) si scriem =A2/B2 (adica corespunzand formulei R1=U/I1) si apasam ENTER. Apoi tragem de marcajul de umplere al celulei astfel incat sa se completeze toate datele din coloana respectiva.

Celula A2 este celula de la intersectia coloanei A cu randul 2. Similar, celula B2 este intersectia coloanei B cu randul 2.

Se procedeaza similar pentru a completa coloanele R2, I, R dupa formulele de la rezistentele paralel. Adica:

R2=U/I2, I=I1+I2 si R=R1*R2/(R1+R2)

(Atentie! Mai sus variabilele U, I1, I2, R1, R2, R sunt nume de coloane. Voi va trebuie sa le inlocuiti cu referinte de celule corespunzatoare. Pentru a completa coloana I de exemplu, nu veti scrie in celula F2 =I1+I2. Aici I1 este un curent si nu este referinta celulei I1 care se afla la intersectia coloanei I cu randul 1. De fapt celula I1 nici nu apare in baza noastra de date. Voi veti scrie formulele folosind referinte de celule corespunzatoare variabilelor din formule, in cazul de fata scrieti F2=B2+C2 si nu F2=I1+I2).

Se formateaza aceste coloane completate astfel incat sa nu contina decat cate 2 zecimale. Pentru aceasta se selecteaza coloana, se face click dreapta si se selecteaza Format Cells, Numbers sau se apasa de doua ori butonul    din bara de instrumente.

Se salveaza fisierul.

 

Operatii de mutare, copiere, stergere a datelor

Selectarea zonei se face astfel:

Operatia de mutare poate fi realizata prin taiere (Cut) si lipire (Paste). Se procedeaza astfel:

Se selecteaza celula sau domeniul respectiv. Se face clic dreapta cu mouseul si se selecteaza Cut . Chenarul zonei selectate devine o linie dinamica intrerupta, de culoare gri. Se activeaza celula din coltul stanga sus al domeniului in care se va lipi selectia. Se apasa butonul Paste si selectia va fi lipita in pozitia respectiva.

Exercitiu: Mai adaugati un rand in baza de date cu rezistentele paralel. Cum s-ar putea realiza aceasta?Am putea pur si simplu sa introducem niste valori dar varianta preferata este de a copia unul din randurile anterioare si a-l modifica (se modifica doar valoarea corespunzatoare tensiunii, de xemplu U=11). Observati ca astfel se pastreaza formulele din rand cu referintele relative.

Observatie: Mutarea sau copierea unei formule presupun modificarea referintelor din formula. Daca de exemplu, se doreste manevrarea numai a valorilor obtinute prin calcul, se utilizeaza optiunea Values din fereastra Paste Special rezultata din Edit->Paste Special.

 

Formatarea conditionata

Este posibil ca unele date care indeplinesc anumite conditii sa fie evidentiate prin intermediul unor optiuni de formatare care se schimba automat la schimbarea datelor. Aceasta este asa numita formatare conditionata.

Pentru a descrie modul in care se realizeaza acest tip de formatare se considera drept exemplu evidentierea prin scrierea cu rosu a curentilor I1, I2 mai mici decat 0,5.

Se procedeaza astfel:

Se obtine urmatorul rezultat:

Se observa ca numerele mai mici decat 0,5 sunt trecute cu rosu.

 

Diagrame Excel

Pentru baza de date cu rezistentele in paralel se vor trasa graficele I1=f(U), I2=f(U) si I=f(U).

Pentru aceasta se selecteaza o casuta din afara zonei de date acolo unde vrem sa punem diagramele. Se acceseaza Insert -> Chart -> XY (Scatter), se selecteaza la Chart sub-type prima varianta din randul al doilea.

 

Se apasa Next iar apoi se selecteaza Series -> Add.

 

Reprezentam I1 ca functie de U. Dati un nume al graficului, de exemplu I1.

Pe axa X avem tensiunea U iar pe axa Y avem curentul I1. La X Values faceti clic la iconita din dreapta (), selectati celulele U (valorile din celulele U trebuie sa fie in ordine crescatoare, altfel datele trebuie ordonate) si se face clic pe iconita din dreapta ().

Similar se procedeaza cu Y Values selectand celulele I1. Veti obtine astfel un grafic I1=f(U). Procedati similar (cu Series -> Add) pentru I2=f(U) si I=f(U).

 

La Chart Title scrieti Grafic Curenti si puneti si etichetele pentru axele x si y.  Salvati fisierul.

 

In final realizati doua linkuri in pagina web personala realizata la laboratorul trecut in Frontpage. Unul cu Persoane.xls si unul cu RezistenteParalel.xls.

 

Tema

Realizati o baza de date corespunzatoare la doua rezistente in serie strabatute de curentul I ca in figura de mai jos.

Avem formulele: U=U1+U2, R1=U1/I, R2=U2/I, R=U/I (sau R=R1+R2)

Linia de antet va fi: I, U1, U2, R1, R2, U

Curentul I ia valori crescatoare de la 0,1 la 0,9.

Tensiunile U1 si U2 iau valori aleatorii intre 1 si 10.

Se vor face graficele U1(I), U2(I) si U(I).

Salvati fisierul si includeti-l pe pagina personala realizata in Frontpage in laboratorul trecut. Pentru aceasta fisierul va trebui salvat in directorul X:\public_html. Se va face un hyperlink pe pagina personala catre fisierul respectiv.