Excel - Lucrul in excel - tutorial

Excel

1. Prezentare generala

Este un produs MicroSoft folosit pentru tratarea datelor in forma tabelara, asa-numita foaie electronica de calcul - spreadsheet - feuilles de calcul - pentru tratarea datelor tabelei ca o baza de date si de asemenea permite reprezentarea grafica a informatiei.

Lansarea aplicatiei EXCEL

Se lanseaza ca orice aplicatie Windows, de exemplu se face dublu clic (DC) pe iconita produsului.

Apare fereastra standard pentru o foaie electronica de calcul (figura de mai jos)

care se compune din:

. linia I - denumirea produsului Microsoft Excel.

. linia a II-a - bara orizontala de sub-meniuri standard pentru un produs Microsoft Office. Ceea ce individualizeaza Excel este sub-meniul Data.

. linia a III-a - bara de instrumente standard.

. linia a IV-a - foaia de lucru care reprezinta partea principala a ferestrei si este organizata ca o retea de linii, numerotate incepand cu 1, si coloane, numerotate incepand cu A. Dupa epuizarea alfabetului, coloanele sunt numite AA,., AZ,BA,.,BZ,.

Intersectia dintre o linie si o coloana se numeste celula si se adreseaza prin litera(e) urmata de un numar.

Adresarea poate fi :

. relativa, adica intr-o formula dintr-o celula se poate utiliza continutul altei celule in calcule, precizand identificatorul celulei, iar la copierea formulei adresa celulei se modifica automat, sau

. absoluta si aceasta adresare se foloseste atunci cand celula utilizata in formula nu trebuie sa se schimbe la copierea formulei. Se utilizeaza semnul $ pentru fixarea fie a liniei, fie a coloanei fie a liniei si coloanei deodata.

Observatie. Daca se adreseaza o celula care nu apartine registrului curent atunci identificatorul celulei trebuie sa precizeze si foaia in care se afla celula respectiva Sheetn !LnCm .

Exemple. Celula de pe linia intai si coloana intai se adreseaza cu A1, celula de pe linia a treia si coloana a doua se adreseaza cu B3 etc.

Pentru exemplificarea adresarii relative sa consideram ca in coloana A sunt valorile argumentului functiei sin, iar in coloana B valorile lui sin in aceste puncte. In celula B2 se scrie formula =sin(A1), se electeaza celula B2 si se pentru copiere se prinde cu indicatorul mouse-ului de coltul de jos dreapta si se trage in jos pana se termina valorile din coloana A. Automat se modifica si celulele argumentului.

Sa consideram un tabel care sa contina studentii integralisti pe fiecare an de studiu si se doreste sa se stie care este procentul integralistilor din totalul studentilor facultatii pe fiecare an de studiu. In celulele B2-B6 sunt trecuti integralistii pe fiecare an de studiu, celula B7 avand numarul total al studentilor facultatii. Pentru calculul procentului de integralisti din anul I din totalul studentilor facultatii, in celula C2 se tasteaza =B2/$B$7 . Pentru a aparea semnul % se selecteaza celula C2, se alege Cells din sub-meniul Format,apare fereastra Cells Format si de aici Number, apoi Pecentage. Se selecteaza celula C2 si se trage de coltul de jos dreapta pana in celula C6. Pentru ca prin copiere pana in coloana C6 deimpartitul sa nu se modifice, s-a tastat $ si in fata coloanei si in fata liniei ($B$). S-a facut adresare absoluta pentru celula B7.

Daca celula B7 ar fi apartinut foii Student atunci in celula C2 s-ar fi tastat =B2/Student!$B$7

Documentele Excel se numesc Workbooks (registre de lucru) care contin foi electronice de calcul (spreadsheet) precum si diagrame. In continuare elementele unui registru vor fi denumite foi (sheet).

Fereastra de tip document, are un nume, automat Bookn, inscris in prima linie, deasupra liniei care numeroteaza coloanele.

O celula poate contine: valori numerice, texte, formule.

In interiorul acestei ferestre cursorul este o cruciulita.

Parasirea aplicatiei EXCEL se poate realiza

. cu salvarea foii curente: din sub-meniul File se alege Save As, se tasteaza specificatorul de fisier (extensia nu este necesara, o pune automat sistemul si ea este XLS), apoi se apasa butonul OK.

Daca documentul avea nume si se pastreaza, atunci se selecteaza File si de aici Save. Se selecteaza Close, apoi Exit ambele din meniul vertical care apare facand selectand sub-meniul File.

. fara salvarea foii curente: din sub-meniul File, se selecteaza Close, apoi Exit.

2. Lucrul cu foaia electronica de calcul

Deplasarea in cadrul foii de calcul se poate face sus/jos sau stanga/dreapta utilizand cursorul din bara de defilare verticala din dreapta ecranului, respectiv orizontala din partea de jos a ecranului, prinzandu-l cu mouse-ul si tragand de el, cu sagetile ↑↓, cu Tab, cu Shift si Tab simultan, cu Enter.

Activarea unei celule: se face clic pe celula respectiva.

Selectarea mai multor celule se poate face:

. folosind mouse-ul:

- celule adiacente ( totdeauna zona are forma dreptunghiulara)

se alege o celula dintr-un colt a zonei si se tine butonul stang apasat pana se ajunge in coltul diagonal opus si se elibereaza butonul mouse-ului;

- celule neadiacente: prima zona se selecteaza ca mai sus, iar pentru celelalte se apasa tasta Ctrl, se muta cursorul in coltul din stanga sus al urmatoarei zone si se deplaseaza cursorul tinand apasat butonul stang pana se ajunge in coltul diagonal opus al zonei, apoi se elibereaza butonul si daca mai sunt alte zone se procedeaza la fel, daca nu, se elibereaza tasta Ctrl.

Selectarea intregii foi electronice: se plaseaza cursorul pe coltul din stanga sus a ferestrei document, apoi se face clic pe butonul stang al mouse-ului.

Selectarea unei linii sau coloane: se plaseaza cursorul la inceputul liniei/coloanei, acesta se transforma in sageata in directia liniei/coloanei de selectat si se face clic pe butonul stang al mouse-ului.

. folosind tastatura :

- celule adiacente: se plaseaza cursorul in coltul din stanga sus al zonei si se tine apasata tasta Shift si se deplaseaza cu ajutorul tastelor sageti dreapta si jos pana in coltul dreapta jos al zonei de selectat. Se elibereaza tasta Shift .

- celule neadiacente: se selecteaza prima zona ca mai sus apoi se apasa tasta F8 (nu se elibereaza tasta Shift) ; se elibereaza tasta Shift, se plaseaza cursorul la inceputul celei de-a doua zone si se apasa tasta Shift si se deplaseaza pana in coltul din dreapta jos al acestei zone si se elibereaza tasta Shift.

- selectarea unei linii: se plaseaza cursorul la inceputul liniei si se apasa tasta Shift si bara de spatiu.

- selectarea intregii foi de calcul se realizeaza cu Ctrl + Shift + bara de spatiu apasate simultan.

Introducerea datelor se realizeaza in urmatorii pasi:

. Pozitionarea pe celula in care se doreste sa se introduca ceva

. Textul tastat apare

o in celula in care s-a facut pozitionarea

o in bara cu formule fx (deasupra liniei cu numele coloanelor) si va fi memorat dupa apasarea tastei Enter, Tab sau a unei sageti directionale sau prin mutarea indicatorului mouse-ului intr-un alt loc;

Pentru a anula intregul text introdus, inainte de memorare se apasa Esc, iar dupa memorare se apasa tasta Del.

Pentru a corecta un text memorat, se pozitioneaza cursorul pe celula respectiva, apoi in zona de editare fx se pozitioneaza indicatorul mouse-ului pe pozitia unde trebuie facuta corectia.

Pentru vizualizarea formulei continuta de o celula se face dublu clic pe celula respectiva (formula poate fi vazuta in zona de editare a formulelor fx si dupa pozitionarea cu clic pe celula respectiva).

Daca Excel nu "intelege" ceea ce s-a introdus in celula respectiva apare un mesaj de eroare #NAME, #REF sau altceva ca in tabelul de mai jos.

Aranjarea intr-un format dorit a ceea ce s-a introdus intr-o celula se poate face alegand Cells din Format. Apare ferestra Format Cells care are optiuni pentru formatarea dupa dorinta a continutului celulei selectate:

. Number ofera posibilitatea de a fixa numarul de zecimale la un numar real, exprimarea in procente, diferite forme pentru data calendaristica etc.;

. Alignment da posibilitatea alinierii pe orizontala si pe verticala a continutului celulei selectate, orientarea textului precizand inclinarea in grade, scrierea textului pe mai multe linii intr-o celula (Warp), scrierea indicilor superiori sau inferiori etc.;

. Font permite stabilirea unui tip de caractere, dimensiunea acestora;

. Border este utila atunci cand se doreste trasarea de linii pe marginea celulelor, putand alege tipul si grosimea liniilor;

. Patterns ofera posibilitatea alegerii unei culori dintr-o paleta pusa la dispozitie.

Introducerea seriilor de date

. Date numerice

Daca trebuie introduse date in progresie aritmetica pe o linie sau o coloana se completeaza prima celula si a doua, se selecteaza cele doua celule si se trage de coltul de jos dreapta al celei de-a doua celule pana se completeaza seria.

Exemple.

. 1,2,3,.. Pentru aceasta se scrie 1in prima celula, 2 in cea de-a doua, se selecteaza cele doua celule si se trage de coltul de jos dreapta al zonei selectate pana se completeaza seria;

. 2,5,8,.; se scrie 2 in prima celula, 5 in cea de-a doua celula, se selecteaza celulele si se trage de coltul jos dreapta al zonei selectate pana se completeaza seria;

. repeta acelasi text (sau valoare): se scrie prima celula si se trage de coltul de jos dreapta peste zona care se doreste sa fie completata cu aceeasi valoare.

. Date nenumerice (zilele saptamanii, lunile anului etc)

Se completeaza prima celula cu data de inceput a seriei, se selecteaza celula si se trage de coltul de jos din dreapta pana de obtine seria dorita.

Exemple.

. luni, marti, miercuri, .; se scrie luni si se trage de coltul jos dreapta al celulei si Excel completeaza automat zilele saptamanii

. ianuarie, februarie, martie, .; analog.

3. Calcule

Lucrul cu formule

Introducerea unei formule se face scriind in bara de formule (editare formule) = expresie urma de Enter.

Expresia aritmetica poate contine valori, functii (exemplu: max, min, sin, cos, exp, ln etc), referinte la alte celule legate cu operatori matematici: + - * / % (calculeaza n% din ceva) ∧ (ridicare la putere). Se pot folosi paranteze ori de cate ori este nevoie.

Expresia relationala este formata din valori, functii si referinte la alte celule legate de operatorii relationali < <= > >= = <> (diferit) .

Expresia logica poate contine valori, functii, referinte la alte celule si expresii relationale legate cu operatorii logici not and or.

Cateva functii din biblioteca Excel (Excel are peste 200 )

Exemple.

SUM ( B2 : B20 ) calculeaza suma valorilor numerice din domeniul B2-B20

AVERAGE ( B2 : C15 ) calculeaza media aritmetica a valorilor din domeniul B2-C15

PROPER("Universitatea tehnica de constructii bucuresti")

ROUND (2.1416;3) → 2.141

Functia SUM: exista in bara de instrumente butonul Σ, care permite calculul sumei unei linii daca este cursorul plasat pe prima celula libera din dreapta liniei sau calculeaza suma celulelor unei coloane daca este plasat cursorul pe prima celula libera de sub coloana cu celula cu numere.

4. Reprezentarea grafica a datelor

4.1. Reprezentarea functiilor de o variabila

Daca o linie (coloana) reprezinta valorile unei functii de o variabila, Excel permite reprezentarea grafica a acestei functii. Pe acelasi sistem de axe pot fi mai multe grafice. Se poate da un nume graficului, axelor, se poate reprezenta o legenda.

In celula A1 se introduce textul Reprezentari grafice de functii. Se selecteaza celulele A1-I1, din sub-meniul Format se alege Cells, de aici Alignment, iar din lista ascunsa Horizomtal se alege Center Across Selection pentru centrarea titlului pe foaie.

In celula A2 se introduce x, in celula B2 se introduce f(x)=exp(-2*x)/ (x^2+1)+3*x-x^2, iar in celule C2 g(x)=2*sin(3*x) -2*cos(2*x) . Pentru a scrie pe doua randuri aceste formule, din sub-meniul Format se alege Cells, de aici Alignment si apoi Warp text.

Pentru a scrie centrat pe verticala x in celula A2 se selecteaza celula si din sub-meniul Format se alege Cells, apoi Alignment si din lista ascunsa de la Vertical se alege Center.

Valorile argumentului celor doua functii vor fi o progresie aritmetica avand primul termen -2 si ratia 0.2. Pentru a introduce aceasta serie de numere se procedeaza astfel: in celula A3 se introduce -2, in celula A4 se introduce -1.8, se selecteaza cele doua celule si se trage de coltul de jos dreapta pana se ajunge la valoarea 2 (celula A23).

Pentru a introduce expresia functiei f(x) incelula B3 se tasteaza =EXP(-2*A3)/ (A3^2+1)+3*A3-A3^(1/3) iar pentru a introduce expresia functiei g(x) incelula C3 se tasteaza =2*SIN(3*A3)-A3*COS(2*A3) .

Se face clic pe celula B3, se prinde de coltul de jos dreapta si se trage in jos pana in celula B23, pentru a se calcula valorile functiei f(x) in toate punctele din coloana A. Analog pentru coloana cu valorile functiei g(x).

Se selecteaza zona B2-C23, se selecteaza Chart din sub-meniul Insert si apare fereastra de dialog Chart Wizard care initiaza un dialog pentru a preciza tipul graficului, denumirile axelor, titlul graficului, legenda etc.

Pentru acest exemplu se alege Line si de aici primul tip, ca in figura de mai jos, apoi se apasa butonul Next.

In pasul al doilea, daca nu s-au precizat datele de reprezentat se pot da acum in zona de ediatare Data Range si se poate specifica legenda in zona de ediatre Name dupa ce s-a selectat Series, ca in figura de mai jos.

In pasul al treilea se da un nume graficului (Graficul a doua functii de o variabila) axelor (x pe axa Ox si f(x), g(x) pe axa Oy).

In pasul al patrulea se precizeaza daca graficul va fi inclus in foaia de calcul sau intr-o foaie separata de tip Chart.

Se obtine o foaie ca in figura de mai jos.

4.1. Reprezentarea grafica a datelor dintr-un tabel

Excel permite reprezentarea datelor unui tabel in doua sau trei dimensiuni, intr-o mare varietate de forme care se pot alege din fereatra Chart Wizard.

Exemplu. Sa se intocmeasca un tabel cu situatia scolara a studentilor facultatii, continand numarul de restantieri, numarul studentilor cu note intre 5 si 7, 7 si 8 , 8 si 10, numarul total al restantierilor pe facultate, procentul de promovati pe fiecare an si pe facultate. Sa se intocmeasca diagrame cu aceste date.

Se creeaza foia de calcul cu titlul "Situatia scolara a studentilor", tastand acest text in celula A1. In celula A2 se tasteaza An, in celula B2 Restantieri, in celula C2 5-7, in celula D2 7-8, in celula E2 8-10, in celula F2 Total studenti in an, in celula G2 Promovati, in celula H2 Promovati pe an de studiu si in celula I2 Promovati pe facultate. Se completeaza celulele A3-A7 cu anii de studiu, zona B3-E7 cu numarul studentilor conform cu ceea ce reprezinta fiecare celula.

Pentru calculul numarului de studenti promovati pe fiecare an de studiu in celula G3 se tasteaza =SUM(C3:E3) , apoi se apasa Enter, se selecteaza celula G3 si se trage de coltul de jos dreapta pana in celula G7.

In celula A8 se tasteaza Total, iar in celula B8 se tasteaza = , se apasa butonul Σ apoi Enter. Se prinde de coltul de jos dreapta celula B8 si se trage pana in celula G8.

Pentru calculul procentelor de promovati pe an in celula H3 se tasteaza =G3/$G$8 , apoi se apasa Enter, se prinde coltul de jos dreapta al celulei H3 si se trage pana in celula H7.

Pentru calculul procentelor de promovati pe facultate in celula I3 se tasteaza =G3/$F$8, apoi se apasa Enter, se prinde coltul de jos dreapta al celulei I3 si se trage pana in celula I7.

Pentru reprezentarea grafica datelor din tabelul dat de zona A2-E7 se procedeaza astfel (reprezentare tridimensionala 3-D):

. se selecteaza zona A2-E7;

. se alege Chart din Insert si de aici 3-D Column (ca in figura). Se initiaza un dialog, in patru pasi, prin intermediul ferestrei Char Wizard. In pasul al doilea alegand Series se pot da alte denumiri coloanelor;

. in pasul al treilea se da un titlu graficului, se dau nume axelor;

. in pasul al patrulea se precizeaza daca se salveaza garficul pe aceeasi foie de calcul sau pe una separata de tip Chart.

Punand graficul pe aceeasi foie aspectul foii rezultate este cel dat de figura urmatoare.

Pentru reprezentarea unei singure linii sau coloane din aceeasi foaie de calcul se alege linia sau coloana respectiva, de exemplu, coloana Restantieri, selectand celulele B2-B7. Se urmeaza etapele:

. se selecteaza Chart din sub-meniul Insert. Se lanseaza un dialog prin intermediul ferestrei Char Wizard;

. se alege un tip de reprezentare. De exemplu Pie;

. in pasul al treilea se poate preciza trecerea valorilor numerice pe grafic selectand Data Labels apoi Value. Daca se selecteaza si Percentage, valorile trecute pe grafic sunt in procente.

Modificarea unei valori pornind de la grafic

Excel permite modificarea datelor dintr-o foaie de calcul pornind de la grafic. Daca se apasa tasta Ctrl si se puncteaza cu mouse-ul pe un punct al graficului si se trage de acel punct, la eliberarea mouse-ului se constata ca nu numai graficul si-a schimbat forma, dar si valorile in foaia de calcul se modifica in mod corespunzator.

De exemplu, daca in foaia de mai sus se reprezinta grafic prin linie zona D2-D7, reprezentand numarul studentilor din fiecare an de studiu cu medii intre 7 si 8 si se apasa tasta Ctrl, se puncteaza cu mouse-ul pe anul intai si se ridica pana la valoarea 50, la eliberarea mouse-lui se modifica tabelul in mod corespunzator ca in figura urmatoare).

Modificari in diagrama deja creata

Se puncteaza cu butonul drept al mouse-ului pe zona cu diagrama si apare din nou fereastra cu tipurile de diagrame (Chart Type). Se alege noul tip de diagrama facandu-se modificarile dorite.

Adaugarea de noi date in histograma In zona de editare Range din fereastra Add Data

Rotirea graficului

Se deplaseaza cursorul mouse-ului tinand butonul stang apasat deasupra graficului si ea cum arata graficul astfel rotit si se apasa Ctrl. Cand pozitia graficului este convenabila se elibereaza butonul drept al mouse-ului

Adaugarea unei legende explicative si individualizarea etichetelor inscrise pe axe :

Daca nu ar avea explicatii pe fiecare axa si se doreste sa se adauge, se procedeaza a

face clic cu butonul drept al mouse-ului in zona Plot Area (cea care contine diagrama), iar din meniul imediat se alege Chart Options. Apare fereastra Chart Options cu ajutorul careia se pot face modificarile dorite. Listarea se poate face fie Portrait fie Landscape.

5. Reprezentarea suprafetelor in Excel

Pentru reprezentarea grafica a unei suprafete in Excel este necesar ca punctele suprafetei fie dispuse intr-o matrice. Se creeaza foia electronica avand in celule depuse valorile functiei de reprezentat, se selecteaza celulele continand aceste valori si din Insert se selecteaza Chart si se deschide un dialog in patru pasi: in pasul 1 se alege Surface, se apsa butonul Next, in pasul 2 se pot da nume seriilor de date (liniilor sau coloanelor), se apasa butonul Next, in pasul 3 se pot da nume graficului, axelor, personaliza legenda. Apasand Next se ajunge in pasul 4 in care trebuie precizat daca graficul va fi pe aceeasi foaie electronica sau pe una separata. Se apasa butonul Finish pentru a incheia.

Pentru aceasta se creaza foaia electronica de calcul ca in figura de mai jos. In celulele A4-A44 se introduce seria de valori pentru x , , cu pasul 0.2, iar in celulele B4-AZ4, seria de valori pentru y, , cu pasul 0.2 . In celulele B5-AZ44 se introduc formulele corespunzatoare pentru calculul valorii suprafetei in punctele de la intersectia liniei cu coloana respectiva. Se selecteaza zona B5-AZ44 si se procedeaza ca in descrierea de mai sus.

5. Mutarea si copierea datelor Cand se indica diferite parti dintr-o celula, indicatorul mouse-lui se transforma capatand

diferite forme in functie de spatiile care sunt permise in acel loc.

Decuparea, copierea si lipirea unei selectii de celule se face astfel:

. Se pozitioneaza indicatorul mouse-ului pe domeniul selectat, clic pe butonul drept al mouse-lui si se alege din meniul imediat optiunea dorita Cut sau Copy.

. Se pozitioneaza in locul unde se doreste sa se faca lipirea sau copierea si se apasa butonul drept al mouse-lui.

. Se alege din meniul imediat optiunea Paste. Daca se renunta se apasa tasta Esc.

6. Deplasarea in cadrul unei foi de calcul folosind tastatura

7. Corectii

Pentru a corecta unele caractere dintr-o celula se face dublu clic pe celula si se pozitioneaza punctul de inserare pe caracterul de modificat.

Pentru a sterge continutul unei celule, se selecteaza celula si se apasa tasta Del.

Pentru a inlocui continutul unei celule se face clic pe celula si se introduce noul text.

Adaugarea si stergerea celulelor, liniilor si coloanelor

Pentru aceasta se foloseste meniul imediat obtinut prin apasarea butonului drept al mouse-ului.

. Daca se sterge o linie, liniile de dedesubt sunt mutate automat in sus.

. Daca se sterge o coloana, coloanele de la dreapta se deplaseaza automat spre stanga pentru a ocupa coloana ramasa libera.

Pentru inserare/stergere de celule se selecteaza celulele, se apasa butonul drept al mouse-ului si apare o caseta de dialog in care se precizeaza modul de reorganizare a foii de calcul.

Numele foilor de calcul trebuie sa fie sugestive pentru identificarea usoara atunci cand trebuie regasita in directoare. Pentru aceasta se apasa pe botonul drept al mouse-ului, din meniul imediat se alege Rename si numele poate avea max 31 de caractere intre care si spatii si paranteze, dar nu / ?

Trecerea de la o foaie de calcul la alta in cadrul aceluiasi registru de calcul se face executand clic deasupra numelui foii de calcul. Aceste nume se afla in partea de jos a ecranului. Daca registrul are multe foi de calcul, cu ajutorul cursorului de defilare orizontala se poate parcurge intreaga lista.

Mutarea, copierea, inserarea sau stergerea foilor de calcul dintr-un registru de calcul

. Pentru a insera o noua foaie de calcul se selecteaza o foaie de calcul din lista, apoi clic pe butonul drept al mouse-ului si se alege Insert, Work sheet. Noua foaie va fi adaugata la stanga celei selectate.

. Pentru a sterge o foaie de calcul se selecteaza foaia de sters din lista, apoi clic pe butonul drept al mouse-ului si se alege Delete. Se solicita confirmarea de stergere si se apasa butonul OK.

. Pentru a muta o foaie de calcul se selecteaza foaia, se tine apasat butonul stang al mouse-ului pana cand se transforma intr-o sageata pe o coala de hartie. Se deplaseaza indicatorul mouse-ului in lista de foi de calcul pana in locul unde trebuie mutata si se elibereaza butonul mouse-ului.

. Pentru a copia foaia de calcul in interiorul aceluiasi registru de calcul, se apasa tasta Ctrl si se trage de numele foii de copiat in stanga sau dreapta pana in locul unde trebuie sa fie plasata copia.

. Pentru a copia foaia de calcul in alt registru de calcul se foloseste meniul imediat (butonul drept al mouse-ului).

8. Blocarea unor linii sau coloane

Daca de exemplu intr-un tabel cu mai multe linii si/sau coloane decat ar incapea pe ecran, se doreste sa se vada numele coloanelor si ale liniilor si coloane si linii care sunt la marginea documentului, se pot bloca linia/coloana cu nume astfel:

. Se selecteaza orice celula din dreapta coloanei (coloanelor) care trebuie sa se vada, sau de sub linia (liniile) care trebuie sa se vada.

. Se alege Freeze Panes din sub-meniul Window.

Pentru deblocare se procedeaza astfel din sub-meniul Window se alege Unfreeze Panes.

Exemplu. Pentru a bloca linia 1 si coloana A se selecteaza celula B2.

Redimensionarea liniilor si coloanelor

Liniile sunt redimensionate automat atunci cand se modifica fonturile.

Daca se doreste sa se modifice latimea unei coloane sau sa se modifice inaltimea unei linii se poate proceda astfel:

. se puncteaza pe linia mai groasa dintre titlurile liniilor sau coloanelor pana cand indicatorul mouse-ului devine o cruce cu sageta dubla.

. Se tine apasat butonul mouse-ului si se trage de marginea liniei sau coloanei pana la dimensiunea dorita;

Redimensionarea liniilor sau coloanelor se poate face si automat de catre Excel la dimensiunile cerute de fiecare linie, respectiv coloana in parte astfel: din meniul bara orizontal se alege Format, apoi Row si de aici Auto Fit. Analog pentru coloane.

Tiparirea unei foi de calcul

Pentru a avea o foaie de calcul bine aranjata in pagina, inainte de tiparire se face vizualizarea pe ecran (Print Preview) si se stabileste daca foaia de calcul va fi tiparita pe hartie in format Portrait sau Landscape.

Pentru centrarea in pagina: Page Setup → Center on Page

Inserarea de antet sau subsol de pagina intr-o foaie electronica de calcul este indicata mai ales atunci cand documentul ocupa mai mult de o pagina si se face astfel:

File → Page Setup → Header/Footer

si din dialogul care se initiaza se poate introduce textul pentru antet si /sau subsol.

Potrivirea foii de calcul in pagina (daca este prea mare):

File → Page Setup → Scaling.

Se fac clic-uri cu mouse-ul pentru micsorare (dar se poate si pentru marire) pana cand se incadreaza bine in pagina foaia de calcul respectiva.

9. Lucrul cu baze de date

Pentru lucrul cu o baza de date relationala formata dintr-o singura tabela, Excel este indicat, acest lucru realizandu-se foarte simplu.

9.1. Definirea structurii tabelei

Se introduc pe o linie numele campurilor tabelei si acestea vor constitui structura. In vederea realizarii unei interfete grafice atractive pentru lucrul cu o baza de date in Excel se poate crea un formular (form).

Pentru realizarea unui formular cu care sa se lucreze asupra acestei tabele se selecteaza celulele liniei cu numele campurilor si din submeniul Data se alege Form. In fereastra care apare se cere confirmarea ca structura este data de celulele selectate. Se apasa butonul OK. Apare formularul atasat acestei tabele.

Daca intre campurile tabelei sunt si unele care rezulta din calcule utilizand campuri ale inregistrarii, atunci in campul respectiv se va tasta =formula de calcul . Excel va scrie in formular numele campului, dar nu va rezerva zona de editare, ci atunci cand se introduc date, se vor face automat calculele si va afisa valoarea rezultata pentru campul respectiv.

9.2. Folosirea formularului

Formularul poate fi utilizat la:

Adaugarea de noi inregistrari se realizeaza astfel : se apasa butonul New, se creaza o inregistrare vida in care se introduc date pentru campurile inregistrarii. Trecerea de la un camp la altul se face cu Tab, revenirea la un camp se face cu Shift + Tab. Daca se apasa Enter se trece la o alta inregistrare noua, cea in lucru ramnand incompleta. Daca un camp are aceeasi valoare si pentru inregistrarea urmatoare el se copiaza cu Ctrl+' .

Stergerea unei inregistrari se face apasand butonul Delete dupa ce s-a facut pozitionarea pe inregistrarea respectiva. Excel cere confirmarea stergerii definitive din tabela a inregistrarii.

Cautarea unei inregistrari care verifica un criteriu se face apasand butonul Criteria si in zona de editare a campului dupa care de face cautarea se tasteaza valoare dorita.

9.3. Sortarea si filtrarea unei baze de date

Excel permite sortarea tabelei dupa 1-3 coloane in ordinea dorita. Pentru aceasta din submeniul Data se alege Sort, apare fereastra Sort in care se precizeaza campul (campurile) dupa care se va face sortare si ordinea (crescatoare sau descrescatoare). Se afiseaza baza de date sortata.

Prin filtrarea unei tabele se intelege extragerea din tabela a unor inregistrari care verifica anumite conditii si cu aceste inregistrari sa se obtina o noua tabela (sau o noua lista). Pentru filtrare se selecteaza coloana (sau coloanele) care trebuie sa verifice anumite conditii (daca filtrul se face dupa toate coloanele atunci nu se selecteaza o coloana anume) si din sub-meniul Data se alege Filter. Din meniul vertical afisat se poate alege AutoFilter, iar in capul coloanei (coloanelor) apare un buton cu sageata. Apasand pe sageata se precizeaza conditiile de filtrare, care pot fi si compuse.

Exemplu. Sa se intocmeasca un formular cu situatia scolara dupa sesiunea de iarna a anului I. Sa se calculeze media studentilor integralisti, iar in caz contrar sa se scrie in locul mediei restantier . Sa se sorteze alfabetic dupa nume aceasta lista, apoi dupa medie in ordine descrescatoare. Sa se creeze un formular pentru incarcarea datelor. Folosind formularul sa se gaseasca situatia unui student al carui nume se cunoaste. Sa se alcatuiasca o lista cu studentii care au medie mai mare decat 8 si o alta cu studentii restantieri.

Rezolvare.

In celula A1 se scrie Situatia scolara a anului I, iar in linia a doua incepand cu coloana intai se scriu : Nr. Crt. Nume si prenume, Grupa, Analiza, Algebra, Geometrie Descriptiva, Chimie, Topografie, Bazele Informaticii, Media .

Pentru centrarea titlului se selecteaza celulele A1-J1 si din sub-meniul Format se alege Cells, apoi Alignment si din lista ascunsa de la Horizontal se alege Center across selection. Se selecteaza celulele A2-J2 si din sub-meniul Format, se alege Alignment si din lista ascunsa de la Horizontal si Vertical se alege Center, iar Warp Text se activeaza.

Pentru introducerea numarului curent se foloseste facilitatea Excel oferita la introducerea seriilor numerice : in celula A3 se introduce 1, in celula A4 se introduce 2, se selecteaza cele doua celule, si cu mouse-ul se trage de coltul de jos dreapta pana se atinge numarul dorit.

Pentru calculul mediei in celula J3 se tasteaza

=IF(AND((D3>=5);(E3>=5);(F3>=5);(G3>=5);(H3>=5);(I3>=5)); SUM(D3:I3)/6;'restantier')

apoi se apasa Enter.

Pentru realizarea formularului care permite introducerea datelor se selecteaza celulele B2-J2, se alege Form din sub-meniul Data, iar in formular se vor trece datele. Pentru adaugarea unei noi inregistrari se apasa butonul New.

Pentru sortarea listei dupa nume, se selecteaza B2-J9 (pentru a lasa numerele de ordine nemodificate), se alege Sort din sub-meniul Data, din fereastra Sort se alege campul Nume si prenume pentru sortare crescatoare si se apasa butonul OK. Apare lista sortata. Analog se realizeaza sortarea dupa medie.

Obtinerea listei cu studentii care au medii mai mari sau egale cu 8 este obtinuta prin filtrarea listei de mai sus, astfel :

. Se selecteaza coloana de sub Media,

. Se alege Filter din submeniul Data,

. Se apasa sageata din coltul de sus dreapa a celulei de sub Media,

. Se formeaza criteriul de filtrare ca in figura de mai jos.

Apare lista ceruta. Analog se obtine lista studentilor restantieri.

9. Utilizarea Solver-ului

Excel are o componenta numita Solver care permite rezolvarea unor probleme de matematica. Aceasta componenta se lanseaza din sub-meniul Tools. Daca nu apare in Tools, atunci pentru instalare din Add-Ins se selecteaza Solver Add-in si apoi se apasa butonul OK.

9.1. Optimizare in Excel

Functia f se numeste functia obiectiv, matricea A se numeste matricea coeficientilor restrictiilor, vectorul b este vectorul termenilor liberi, iar vectorul c este vectorul coeficientilor functiei obiectiv.

Exemplu. Intr−o sectie a unei intreprinderi se produc trei tipuri de produse P₁, P₂ , P₃ , folosind rezerve de forta de munca (F) si resurse financiare (B) limitate conform tabelului de mai jos

care contine si consumurile din aceste rezerve la unitatea de produs pentru fiecare tip, precum si beneficiile aduse de o unitate de fiecare tip de produs. Datorita conditiilor impuse de stocare intreaga productie nu trebuie sa depaseasca 8 unitati.

Sa se determine planul optim de productie care in conditiile date sa dea un profit total maxim pe sectie.

Crearea foii electronice de calcul care rezolva probelema de mai sus.

In linia intai sunt trecute denumirile coloanelor (comentarii). Celulele A2-A4 sunt numele variabilelor, iar celulele B2-B4 sunt variabilele problemei. Celula D2 se completeaza astfel: in linia fx se tasteaza =2*B2+3*B3+2*B4 , in celula D3 se tasteaza =1*B2+2*B3+3*B4 , iar in celula D4 se tasteaza =B2+B3+B4 . Celula F2 contine functia obiectiv si pentru aceasta in fx se tasteaza =1.5*B2+4*B3+3*B4 Coloana termenilor liberi este depusa in celulele E2-E4.

Apelarea Solverului. Se selecteaza celula care contine functia obiectiv, F2, se selecteaza Solver din sub-meniul Tools si Excel initiaza un dialog cu utilizatorul, prin fereastra Solver Parameters.

Se precizeaza daca se cauta sa se maximizeze sau minimizeze functia obiectiv (max), care sunt celulele care reprezinta variabilele (B2-B4), apoi se introduc restrictiile apasand butonul Add prin fereastra Add Constraint.

Pentru restrictiile liniare aflate in celulele D2-D4 se selecteaza aceste celule se pastreaza semnul iar termenii liberi corespunzatori sunt in celulele E2-E4. Se apasa butonul Add, apoi se trece la restrictiile de semn selectand celulele B2-B4, se schimba semnul in si se selecteaza celulele C2-C4, dupa care se apasa butonul OK. Pentru rezolvare se apasa butonul Solve .

Apare fereastra Solver Results in care trebuie precizat daca se doresc toate cele trei rapoarte pe care le furnizeaza Solverul la rezolvare.

Semnificatia informatiei din cele trei rapoarte

1. Answer Report prezinta:

. valoarea initiala si valoarea finala, pentru valorile actuale ale variabilelor, pentru functia obiectiv obtinuta;

. valorile initiale si finale pentru variabile (necunoscutele problemei);

. valorile restrictiilor pentru valorile finale ale variabilelor.

2. Sensitivity Report prezinta valorile finale ale necunoscutelor si ale multiplicatorilor lui Lagrange.

3. Limits Report prezinta valoarea functiei obiectiv si intervalul in care o necunoscuta poate varia, celelate ramanand nemodificate, fara ca valoarea functiei obiectiv sa se modifice.

Problema de transport

Se considera ca exista m centre de aprovizionare (depozite) si n centre de consum (puncte de lucru, uzine, magazine etc.). Se pune problema sa se determine un plan de transport pentru un produs omogen care se afla in cantitatea a_i la depozitul i (1 ≤ i ≤ m) si este cerut in cantitatea b_j la centrul j (1 ≤ j ≤ n). Se noteaza cu x_ij cantitatea necunoscuta ce va fi transportata de la depozitul i la centrul de consum j si cu c_ij costul transportului unei unitati din produsul considerat de la depozitul i la centrul j (pentru simplificare se presupune ca acest cost unitar nu depinde de cantitatea transportata pe ruta respectiva).

Se pot exprima atunci urmatoarele marimi:

- cantitatea ceruta de la depozitul i la toate cele n centre de consum

a_i = x_i1 + x_i2 + + x_in = cantitatea aflata la depozitul i,

- cantitatea transportata de la toate cele m depozite la centrul de consum j

b_j = x_1j + x_2j + + x_mj = necesarul la centrul de consum j,

O conditie evidenta este x_ij ≥ 0, 1 ≤ i ≤ m, 1 ≤ j ≤ n .

Trebuie rezolvata urmatoarea problema:

O astfel de problema este cunoscuta sub numele de problema de transport.

Exemplu. Trei statii de betoane, S_i, se aprovizioneaza cu ciment de la trei rampe de descarcare, R_i . Cantitatile necesare fiecarei statii si cele oferite de fiecare rampa de descarcare, precum si costurile de transport de la fiecare rampa la fiecare statie de betoane sunt trecute in tabelul de mai jos.

Sa se precizeze planul de transport care sa conduca la costul minim de transport si cat este acest cost.

Trebuie determinate volorile nenegative ale variabilelor x_ij pentru care se obtine minf .

Crearea foii electronice de calcul pentru problema de transport.

In celula D2 se introduce functia obiectiv

=B7*B13+C7*D13+D7*F13+B8*B14+C8*D14+D8*F14+B9*B15+C9*D15+D9*F15

Celulele B7-D9 contin costurile de transport, E7-E9 contin cantitatile disponibile, B10-D10 cantitatile solicitate.

Necunoscutele vor fi depuse in celulele B13-B15, D13-D15, F13-F15, iar restrictiile vor fi depuse in celulele B18-B20 si E18-E20.

9.2. Extreme cu legaturi

Solver-ul permite rezolvarea unor probleme care revin la determinarea valorilor maxime si/sau minime ale unor functii de mai multe variabile supuse unor restrictii - binecunoscuta problema a extremelor cu legaturi:

unde: x este vectorul necunoscutelor, g₁,,g_k restrictiile pe care trebuie sa le verifice x.

Rezolvare. Functia de optimizat (maximizat/minimizat) este distanta de la un punct al elipsoidului la plan si anume

Crearea foii electronice de calcul de mai jos se realizeaza astfel:

. coloana A contine comentarii;

. celula B1 contine

=(ABS(3*B5+4*B6+12*B7-288))/SQRT(3^2+4^2+12^2),

. functia ale carei valori extreme sunt cautate;

. celula B3 contine restrictia (legatura) =B5^2/96+B6^2+B7^2-1

. celulele B5-B7 contin variabilele.

. se lanseaza Solver-ul si in fereatra care apare se precizeaza celula in care este functia de optimizat (B2), daca se cauta maximul sau minimul functiei (Min), care sunt celulele ce contin necunoscutele (B5-B7).

. se puncteaza in caseta restrictiilor (Subject to the Constraints), se apasa butonul Add si in ferestra Add Constraint se tasteaza B3 in zona de editare Cell Reference, se alege semnul = si in zona constraint se tasteaza 0 (zero).

. se apasa butonul OK si la revenirea in fereastra Solver Parameters se apasa butonul Solve. Apare fereastra Solver Results din care selectam toate rapoartele pe care le furnizeaza Excel.

Punctul de pe elipsoid care realizeaza distanta minima (19.69) la planul dat este (9, 0.125, 0.375) . Pentru determinarea punctului de pe elipsoid care da distanta maxima, in fereastra Solver Parameters se alege Max.

9.3. Rezolvarea sistemelor de ecuatii (ne)liniare

1. Sisteme de ecuatii liniare

Pentru rezolvarea unui sistem de ecuatii liniare de tip Cramer se pot folosi functiile MMULT si MINVERSE aflate in biblioteca Excel astfel:

. se introduc in foaia electronica de calcul matricea coeficientilor si vectorul termenilor liberi;

. se selecteaza zona de memorie care urmeaza sa contina solutia sistemului;

. in zona de editare fx se tasteaza =MMULT(MINVERSE(css:cjd);tli:tls), unde: css este coltul din stanga sus si cjd este coltul din dreapta jos al matricei coeficientilor, tli si tls sunt celulele de inceput si sfarsit ale zonei in care se alfla termenii liberi.

Exemplu. Sa se gaseasca solutia sistemului .

Se creaza foaia electronica de calcul astfel:

. coloana intai contine comentarii;

. zona A2-B5 contine matricea coeficientilor;

. zona B2-B5 contine termenii liberi;

. se selecteaza zona H2-H5 si in fx se tasteaza =MMULT(MINVERSE(A2:D5);F2:F5)

. se apasa simultan tastele Ctrl+Shift+Enter;

. in zona H2-H5 se afiseaza solutia gasita, iar in zona fx apare

Ca sa se poata aplica aceasta metoda trebuie ca matricea coeficientilor sa fie nesingulara. Solver-ul permite calculul determinantului pentru a sti daca se poate aplica sau nu metoda. Pentru aceasta se foloseste functia MDETERM(css:cjd). In figura de mai jos sunt prezentati pasi pentru rezolvarea problemei.

2. Sisteme de ecuatii neliniare

Pentru rezolvarea unui sistem de ecuatii neliniare formam o functie din suma patratelor ecuatiilor sistemului si consideram ecuatiile sistemului ca restrictii intr-o problema de extrem cu legaturi.

Foaia electronica de calcul din figura de mai jos are pe prima coloana comentarii, in celula A2 functia de optimizat, adica     =SQRT((B6^2+B7^2-6*B6+3)^2+(B6^2-6*B7^3-6*B7+2)^2)

rolul variabilelor x si y este luat de celulele B6 si B7, iar celulele C6 si C7 contin restrictiile, adica:

=B6^2+B7^2-6*B6+3 , respectiv =B6^2-6*B7^3-6*B7+2