Excel

Introducere în Informatică

Capitolul 10. Elemente de calcul tabelar

10.1. Introducere

O categorie importantă de aplicaţii furnizate de marile case de software o reprezintă aplicaţiile de calcul tabelar. Aceste aplicaţii sunt utilizate, aşa cum sugerează şi numele, pentru lucru şi manevrarea datelor organizate sub formă de tabele. Aplicaţiile de calcul tabelar permit efectuarea calculelor matematice exprimate prin formule, calcule statistice şi reprezentări grafice ale datelor tabelare. Din grupa de aplicaţii tabelare fac parte Lotus 123, Quattro-Pro, Excel, Works, Multiplan etc. Având în vedere faptul că cel mai răspândit produs este Excel, majoritatea exemplificărilor le vom face în această aplicaţie.

La modul cel mai general, lucrul cu o aplicaţie de calcul tabelar constă în: lansarea în execuţie a aplicaţiei de calcul tabelar, deschiderea foii de lucru sau a sheet-ului, introducerea datelor, prelucrarea acestora, operaţii de gestiune internă privind tipărirea şi salvarea foii, închiderea foii şi închiderea aplicaţiei de calcul tabelar. Intervalul existent între lansarea în execuţie a aplicaţiei de calcul tabelar şi închiderea aplicaţiei se numeşte sesiunea de lucru a aplicaţiei de calcul tabelar. Principalele operaţiuni ce se efectuează în cadrul unei sesiuni de lucru sunt: introducerea datelor în foaia de lucru, procesarea acestor date din punct de vedere matematic, introducerea textului explicativ şi a textului informativ, reprezentarea grafică a datelor din foaie, operaţiuni de formatarea şi punerea la punct a întregului document, tipărirea documentului la imprimantă sau într-un fişier pe disc (în vederea tipăririi ulterioare) şi salvarea foii.

197


Fig. 10.1. Foaia de calcul a produsului Microsoft Excel

Documentul tabelar este foia de calcul sau sheet-ul. O aplicaţie de calcul tabelar poate gestiona una sau mai multe foi de calcul, din care numai una este activă la un moment dat. Foaia activă este cea în care se execută operaţii la un anumit moment şi ea se numeşte foaie curentă (Fig. 10.1).

Elementele componente ale unei foi se numesc celule. O celulă poate fi individualizată prin precizarea coloanei şi liniei în care este situată. Celula în care s-a plasat cursorul + al mouse-ului şi pe are s-a executat un click se numeşte celulă curentă (Fig. 10.2.). Individualizarea unei celule se face prin precizarea cuplului ColLin, unde Col reprezintă coloana iar Lin reprezintă linia. În exemplul din figura 10.2 celula curentă este B3.

Fig. 10.2. Celula curentă şi cursorul celulei

Identificatorul celulei curente se precizează în prima poziţie a barei de deasupra foii. Eventualele formule matematice specifice celulei curente sunt precizate în a doua poziţie a barei de deasupra foii, imediat după semnul =.

Mai multe celule adiacente pot fi grupate într-un bloc de celule. Individualizarea unui bloc de celule se face prin precizarea celulei aflate într-un colţ, urmată de caracterul : sau de caracterele .. după care se precizează celula aflată în colţ opus.

198


Fig. 10.3. Bloc de celule

În exemplul din figura 10.3. grupul de celule poate fi referit prin: B3:D6, D3:B6, D6:B3, B6:D3 sau : B3..D6, D3..B6, D6..B3, B6..D3.

În mod practic, operarea într-o foaie de calcul se reduce la completarea şi formatarea conţinutului celulelor componente ale foii.

10.2. Tipuri de date

Aplicaţiile de calcul tabelar lucrează cu următoarele tipuri de date: numerice, text, date de tip dată calendaristică, date de tip timp şi date de tip logic.

Datele de tip numeric se utilizează pentru reprezentarea datelor întregi sau raţionale, prin folosirea caracterelor 0, 1, … , 9, +, -, (, ), /, % , $, virgula, punct, E şi e. Aşa de exemplu, -722.165, 0.6354, 7E9 (reprezintă numărul 7*109) sau 7e9, 51/(239-193) reprezintă date de tip numeric. Pot fi aplicaţi următorii operatori asupra datelor de tip numeric: + (adunare), - (scădere sau semnul unar minus), / (împărţire), * (înmulţire) şi ^ (ridicare la putere).

Datele de tip text sunt de fapt şiruri de caractere ASCII. Orice combinaţie de cifre, litere, spaţii şi semne speciale sunt considerare în aplicaţiile de calcul tabelar ca date de tip text. Datele de tip text pot fi pe o lungime foarte mare, totuşi ele se consideră ca aparţinând unei singure celule, aceleia în care a fost introdusă data în cauză. Atunci când se doreşte introducerea explicită a unei date tip text se foloseşte caracterul ’ (apostrof). Aşa de exemplu, data ’194 este un şir de caractere care conţine codurile ASCII pentru caracterele 1, 9 şi 4. O singură operaţie se poate efectua asupra şirurilor de caractere: concatenarea. Ea se indică prin operatorul & şi constă în alipirea şirurilor asupra cărora acţionează operatorul.

Datele de tip dată calendaristică şi de tip timp sunt reprezentate intern prin date numerice care pot fi afişate în formule predefinite de forma: ll/zz/aa, zz-ll-aa (în cazul datelor calendaristice, unde ll este luna din an, zz ziua din lună şi aa anul) sau hh:mm AM ori PM (pentru date de tip timp, unde hh reprezintă ora şi mm minutul).

Datele de tip logic pot lua doar două valori adevărat (True) şi fals (False). Aceste valori sunt returnate de funcţii ori expresii logice incluse în celule. Aceste valori logice rezultă şi din aplicarea unor operatori relaţionali asupra a două date numerice. Operatorii relaţionali utilizaţi în calculul tabelar sunt: > (mai mare), < (mai mic), >= (mai mare sau egal), <= (mai mic sau egal) şi <> (diferit de). Operatorul relaţional poate fi aplicat şi

199


referinţelor a două celule, caz în care se compară conţinutul numeric al celor două celule.

10.3. Formule matematice

Prin definiţie, o formulă matematică sau o expresie este o combinaţie de valori constante sau variabile, referinţe de celule, funcţii şi operatori, prin a căror evaluare se obţine un rezultat corect. În celula care conţine formula sau expresia este vizibil doar rezultatul obţinut prin evaluarea ei. Formula sau expresia se precizează la introducere prin introducerea mai întâi a caracterului =, după care se tastează formula sau expresia.

Dacă formula sau expresia matematică nu este precedată de semnul pentru indicarea formulelor sau expresiilor, adică semnul =, atunci caracterele ce se introduc sunt interpretate ca un şir de caractere (un text), în celulă fiind afişat doar textul respectiv, altfel formula sau expresia nu este afişată în clar ci doar rezultatul evaluării (Fig. 10.4.).

a. Introducere incorectă (este interpretată ca şir)

b. Introducere corectă a unei formule

Fig. 10. 4. Introducerea formulelor şi expresiilor matematice

10.4. Referinţe absolute şi relative

Referinţele reprezintă nişte identificatori prin care pot fi desemnate celule, din aceeaşi foaie sau din foi diferite, utilizate atât în formule şi expresii matematice cât şi în funcţii oferite de aplicaţiile de calcul tabelar. Utilizând referinţe de celule în formule matematice, se realizează legături cu caracter permanent astfel de aşa manieră încât modificarea

200


conţinutului unei celule produce automat modificări în toate celulele care fac referire la celula în cauză.

Unei celule i se poate asocia un nume la dispoziţia utilizatorului. Un astfel de nume poate fi utilizat la fel ca si numele de variabile folosite în limbajele de programare. Conţinutul celulelor cărora li s-a acordat un nume este afişat în permanenţă pe ecran, fără a fi nevoie de comenzi explicite de afişare. Atribuirea unui nume pentru o celulă se face cu ajutorul comenzii Name din opţiunea Insert. De exemplu, dacă se atribuie celulei A5 numele Cel1, atunci în celula B3 putem introduce formula =2*Cel1/5 şi se afişează rezultatul înmulţirii lui 2 cu conţinutul lui A5 împărţit la 5.

Există posibilitatea să lucrăm cu mai multe foi deschise simultan, din care desigur una este foaia activă sau foaia curentă. Aplicaţiile de calcul tabelar permit referirea oricărei celule din foile deschise, cu precizarea că identificarea se face cu Foaie!ColLin, unde Foaie reprezintă numele foii, Col reprezintă coloana iar Lin reprezintă linia celulei din foaia specificată. Semnul exclamării ! specifică faptul că se face referinţă la o foaie deja deschisă. Aşa de exemplu, în celula curentă se poate introduce formula =2*Foai11!B3+3/Foaie2!A2, care arată faptul că se va afişa în celula curentă rezultatul înmulţirii celulei B3 din foaia Foaie1 cu 2, la care se adună rezultatul împărţirii lui 3 la conţinutul celulei A2 din foaia Foaie2.

Referinţele sunt de două tipuri: referinţe relative şi referinţe absolute; ele permiţând copierea formulelor cu păstrarea poziţiei relative la celula în cauză sau copierea cu păstrarea poziţiei absolute faţă de foaia de calcul a celulelor implicate in aceste formule.

Referinţa relativă reprezintă o referinţă a cărei adresă se calculează relativ la celula curentă. De exemplu, dacă celula A2 conţine formula =2*A1, atunci formula trebuie interpretată ca înmulţirea lui 2 cu prima celulă din stânga, şi dacă vom copia formula din A2 (să zicem în B5) atunci în B5 se va afişa rezultatul înmulţiri lui 2 cu B4, adică 2 înmulţit cu conţinutul primei celule din stânga (s-a păstrat doar semnificaţia formulei, relativ la celula curentă, nu şi formula în sine). Referinţele relative sunt de forma ColLin, unde Col reprezintă coloana iar Lin reprezintă linia celulei referite.

Referinţele absolute reprezintă referinţele la o celulă a cărei adresă se calculează relativ la foaia de calcul, care este considerată ca un reper absolut. De aceea formulele care conţin numai referinţe absolute sunt copiate ca şi constantele, fără a suferi nici o modificare. Referinţele absolute sunt de forma $Col$Lin, unde $ specifică faptul că se prezintă o valoare absolută fie de coloană fie de linie, Col reprezintă coloana iar Lin reprezintă linia celulei referite. Exemple de adrese absolute sunt: $A$10, Foai11!$B$3, Foai12!$CA$12. La construirea unei referinţe de celule se pot folosi şi referinţe mixte de forma: $ColLin sau Col$Lin.

201


10.5. Utilizarea funcţiilor în calculul tabelar

Aplicaţiile de calcul tabelar oferă utilizatorului o multitudine de funcţii (de ordinul sutelor, ele pot fi obţinute prin informaţii de help) care pot acţiona asupra datelor aflate în foaia de calcul. Funcţiile pot intra în componenţa expresiilor matematice pentru că ele returnează un rezultat. O funcţie este apelată prin invocarea numelui, precedat de caracterul = şi urmat de precizarea referinţelor şi constantelor (în paranteză, separate virgulă).

Funcţiile sunt grupate pe următoarele categorii: funcţii matematice şi funcţii trigonometrice, funcţii referitoare la matrice, funcţii statistice, funcţii text, funcţii de căutare şi de referinţe, funcţii financiare, funcţii de tratare a datelor de tip dată calendaristică şi timp, funcţii de administrare a listelor şi bazelor de date. Vom aminti doar câteva dintre ele.

a. Funcţii matematiceSintaxa Valoarea returnatăABS(n) Valoarea absolută a lui nEXP(n) en

FACT(n) Valoarea factorialului n!INT(n) Partea întreagă a lui nLOG(n,b) Logaritmul în baza b din nLOG10(n) Logaritm în baza 10 din nLN(n) Logaritm natural din nMOD(n,d) Restul (modulul) împărţirii lui n la dPI() Valoarea constantei POWER(n,p) Valoarea puterii np

PRODUCT(n1,n2, …) Valoarea produsului n1*n2*…RAND() O valoare aleatoare zecimală intre 0 şi 1ROMAN(n) Converteşte numărul arab n într-unul romanROUND(n1;n2) Rotunjirea valorii lui n1 la un număr cu n2 zecimaleROUNDUP(n,np) Rotunjeşte în sus la un număr cu np zecimaleROUNDDOWN(n,np) Rotunjeşte în jos la un număr cu np zecimaleSIGN(n) Valoarea 1 pentru n > 0 şi 0 pentru n < 0SQRT(n) Radical din n, unde n >= 0SUM(n1,n2, … , nn) Valoarea sumei n1 + n2 + … + nnSUMSQ(n1,n2, .. , nn) Valoarea sumei n12 + n22 + … + nn2

TRUNC(n,np) Trunchierea lui n până la np poziţii zecimale

b. Funcţii trigonometrice (lucrează numai cu unghiuri exprimate în radiani)

Sintaxa Valoarea returnatăACOS(n) Valoarea arccosinusului lui n exprimat in radiani

(n[-1,+1])

202


Sintaxa Valoarea returnatăACOSH(n) Valoarea arccosinusului hiperbolic a lui n (n >= 1)ASIN(n) Valoarea arcinusului lui n exprimat in radiani (n[-

1,+1])ASINH(n) Valoarea arcsinusului hiperbolic a lui n (n >= 1)ATAN(n) Valoarea arctangentei lui n exprimat in radianiATAN2(n1,n2) Val. Arctangentei corespunzătoare coordonatelor n1

si n2ATANH(n) Valoarea arctangentei hiperbolice a lui n (n(-1,+1))COS(n) Valoarea cosinusului lui n exprimat in radianiCOSH(n) Valoarea cosinusului hiperbolic de nDEGREES(rad) Valoarea în grade a unghiului rad exprimat în

radianiRADIANS(grd) Valoarea în radiani a unghiului grd exprimat în

gradeSIN(n) Valoarea sinusului lui n exprimat in radianiSINH(n) Valoarea sinusului hiperbolic de nTAN(n) Valoarea tangentei lui n exprimat in radianiTANH(n) Valoarea tangentei hiperbolice de n

c. Funcţii referitoare la matrice (m. poate fi definită ca un bloc de celule)

Sintaxa Valoarea returnatăMDETERM(m) Valoarea determinantului matricei mMINVERSE(m) Calculează matricea inversă (dacă det(m) diferit

de 0)MMUL(m1,m2) Calculează produsul matricelor m1 şi m2SUMPRODUCT(m1,m2) Calculează suma produsului matricelor m1 şi m2SUMX2MY(m1,m2) Calculează suma diferenţei pătratelor valorilor

din cele două matrice m1 şi m2SUMX2PY(m1,m2) Calculează suma pătratelor valorilor din cele

două matrice m1 şi m2

d. Funcţii statistice

Sintaxa Valoarea returnatăAVERAGE(n1,n2,..,n30) Valoarea medie a maximum 30 de numereCOUNT(n1,n2, … ,n30) Numărul elementelor numerice din blocurile

referiteCOUNTA(n1,n2,…,n30) Numărul elementelor inclusiv text din blocurile

referiteMAX(n1,n2, … ,n30) Valoarea maximă a dintre argumenteMEDIAN(n1,n2, … ,n30) Valoarea medie dintre argumenteMIN(n1,n2, … ,n30) Valoarea minimă a dintre argumente

203


VAR(n1,n2, … ,n30) Valoarea estimată a variaţiei unei populaţii din argumente

VARP(n1,n2, … ,n30) Valoarea variaţiei întregii populaţii din argumente

e. Funcţii text (permit manipularea valorilor de tip text)

Sintaxa Valoarea returnatăCHAR(n) Valoarea ASCII care corespunde caracterului nCLEAN(t) Îndepărtează caracterele care nu sunt de tip

ASCII (curat)CODE(t) Valoarea ASCII a primei litere din textul tCONCATENATE(t1,t2) Şirul rezultat din reuniunea textului t1 şi t2 (max.

32 car.)EXACT(t1,t2) Compară valorile t1 şi t2; identice dă True, altfel

FalseFIIND(tc,ts,st) Valoarea poziţiei caracterului tc în ts începând de

la stLEFT(t,nc) Afişează primele nc caractere din stânga textului

tLEN(t) Valoarea lungimii textului tLOWER(t) Eventualele majuscule din t le face minusculeMID(t,ns,nc) Şirul de lungime nc începând de la ns din textul tPROPER(t) Şir cu minuscule şi cu începutul cuv. Sunt cu

majusculeREPLACE(tv,st,nc,tn) Înlocuieşte în tv începând de la st, nc caractere cu

tnREPT(t,n) Şirul obţinut prin repetarea de n ori a textului tRIGHT(t,n) Afişează ultimele n caractere din dreapta textului

tT(t) Furnizează text dacă valoarea este de tip, spaţiu

altfel TEXT(v,f) Converteşte valoarea numerică v în text, în

formatul fTRIM(t) Lasă numai un spaţiu între cuvinte, restul le

ştergeUPPER(t) Transformă toate caracterele textului t în

majusculeVALUE(t) Converteşte valorile numerice de tip text sau dată

calen-daristică ori timp în numere pentru utilizare în formule

f. Funcţii de căutare şi de referinţe

204


Sintaxa Valoarea returnatăADDRESS(nl,nc,n,cel,fc) Referinţă de celulă, de tip textAREAS(ref) Furnizează numărul de zone din referinţa refCHOOSE(ni,v1, v2, … ) Selectează dintr-o listă de valori v1, v2, …

valoarea care corespunde numărului de index niCOLUMN(ref) Furnizează numărul de coloană a celulei cu

referinţa refCOLUMNS(m) Returnează numărul de coloane din matricea mINDEX(m,nl,nc) Dă valoarea unei celule care corespunde lui nc şi

nl ROW(ref) Furnizează numărul liniei pentru celule referită

prin refROWS(m) Dă numărul de linii a matricei mTRANSPOSE(m) Transpusa matricei m

g. Funcţii financiare

Sintaxa Valoarea returnatăDB(c,r,v,p,l) Calculează depreciere pe perioada p la costul c cu valoare

recuperată r cu durata de viaţă economică v, ştiind l luni din primul an

FV(d,np,p,v,tip) Calculează valoarea viitoare pentru o serie de încasări provenite din p plăţi egale, făcute în np perioade, cu o dobândă d, cu valoarea globală vp investită la începutul operaţie, tip=1 dacă plata se face la începutul perioadei, altfel tip=0

IRR(valori,e) Furnizează rata internă pentru seria de beneficii nete plus amortizări din estimarea e

Lista funcţiilor oferite de aplicaţia Excel este mult mai mare decât ce am prezentat aici, motiv pentru care vă sugerăm ca la orele de laborator să testaţi si alte funcţii decât cele arătate anterior.

10.6. Sortarea datelor

Aplicaţiile de calcul tabelar oferă posibilitatea de ordonare a datelor situate în grupuri de celule adiacente (în general pe verticală) după criterii impuse de utilizator. Această operaţie de ordonare criterială se numeşte sortare. Pot fi supuse operaţiei de sortare datele de tip numeric, tip text, tip dată calendaristică şi timp. Criteriul de sortare se aplică uneia sau mai multor coloane din blocul de celule care urmează a fi sortat. Sortarea se face în funcţie de valorile din prima coloană, la valori egale în prima coloană se sortează după cea de-a doua coloană (criteriu de sortare secundar) ş.a.m.d.

205


Aşadar, cheia principală de sortare este furnizată de prima coloană, iar în caz de egalitate a cheilor principale se sortează după cheia secundară reprezentată de a doua coloană, şi procesul continuă până la epuizarea tuturor coloanelor selectate pentru sortare.

Operaţia de sortare trebuie să se realizeze în felul următor:1. Se selectează blocul de celule reprezentând conţinutul cheilor de

sortare (cheia principală şi cheile secundare de sortare);2. Se specifică criteriile de sortare (crescător, descrescător, alfabetic);3. Declanşarea efectivă a sortării prin utilizarea comenzii Sort.

Ordonările pe criterii complexe pot fi realizate prin copierea într-o coloană adiacentă blocului ce urmează a fi sortat, a valorilor calculate utilizând expresii de sortare, urmată de sortarea blocului nou obţinut ca un criteriu suplimentar de sortare.

10.7. Reprezentarea grafică a datelor tabelare

Este cunoscut faptul că o reprezentare grafică bine făcută poate crea o imagine sugestivă privind evoluţia unor fenomene sau procese. Aplicaţiile de calcul tabelar oferă posibilitatea reprezentării grafice a unor tabele în următoarele formate: linie, puncte XY, bară, arie, plăcintă, bară 3D, arie 3D, linie 3D şi suprafaţă 3D etc. (Fig.10.5.).

LUNA APA GAZ CURENTIa 8 182 461Fe 7 179 450Mr 14 105 274Ap 11 147 295Ma 15 72 320Iu 16 27 332

a. Tabelul de referinţă folosit la reprezentările grafice din figura 10.5.

206


b. Reprezentare grafică tip coloană

c. Reprezentare grafică tip coloană 3D

d. Reprezentare grafică tip bară

207


e. Reprezentare grafică tip bară 3D

f. Reprezentare grafică tip linie

g. Reprezentare grafică tip linie 3D

208


h. Reprezentare grafică tip tuburi

i. Reprezentare grafică logaritmică

j. Reprezentare grafică liniară pe două axe

209


k. Reprezentare grafică tip radar

l. Reprezentare grafică tip suprafaţă

m. Reprezentare grafică inelară

210


n. Reprezentare grafică prin puncte

o. Reprezentare grafică prin puncte folosind linii netezite

p. Reprezentare grafică stratificată

211


p. Reprezentare grafică stratificată 3D

Fig. 10.5. Reprezentări grafice utilizate în calcul tabelar

10.8. Operaţii cu matrice

Faptul că o foaie de calcul este organizată pe linii şi coloane, la fel ca şi o matrice, sugerează utilizatorului posibilitatea de a lucra foarte facil cu matricele. Aplicaţiile de calcul tabelar permit efectuarea tuturor operaţiilor cu matrice, fie prin intermediul unor funcţii predefinite proprii aplicaţiei de calcul tabelar, fie prin intermediul formulelor definite de utilizator. Câteva din operaţiile cu matrice sunt prezentate în figura 10.6.

Cele mai utilizate operaţii cu matrice sunt: MMULT(m1,m2) – pentru operaţia de înmulţire a matricelor; MINVERSE(m) – pentru inversarea unei matrice; MDETERM(m) – pentru calculul determinantului unei matrice; TRANSPOSE(m) – pentru obţinerea transpusei unei matrice.

Fig. 10.6. Exemple de operaţii cu matrice

212


Pentru utilizarea acestor funcţii se procedează în felul următor: se selectează prin dragare cu mouse-ul blocul de celule care trebuie

să conţină matricea rezultat al operaţiei de efectuat; în funcţie de operaţia dorită a fi executată, se tastează în zona de

editare de deasupra foii de calcul, formula de calcul care necesită funcţia dorită cu specificarea argumentelor de apel;

pentru ca formula de calcul să fie valabilă pentru toate celulele blocului receptor al rezultatului, se tastează combinaţia de taste Ctrl+Shift+Enter, care face ca formula de calcul să se distribuie în toate celulele şi să se lanseze în lucru funcţia care va furniza rezultatul; elementele matricei rezultat va fi imediat vizibil în blocul selectat. În figura 10.6. am considerat că este bine să arătăm şi conţinutul

real, adică conţinutul cu funcţii a celulelor blocului rezultat. Aşa de exemplu, celulele blocului E6:G8 arată formula care calculează inversa matricei A (adică A-1) care este o formulă de forma =MINVERSE(A2:C4), celulele blocului E19:G21 arată formula care calculează produsul matricial A*A-1

efectuat cu scop de verificare a calculului inversei matricei A care se face după o formulă de forma =MMULT(A2:C4,E2:G4), precum şi celulele blocului E29:G31 ce arată formula care calculează transpusa matricei A (adică AT) şi care este o formulă de forma =TRANSPOSE(A2:C4). Menţionăm că aceste formule nu apar în clar ci doar rezultatul calculelor prezentate în blocurile: E2:G4 pentru A-1 , E14:G16 pentru matricea unitate A*A-1 , E24:G26 pentru matricea AT .

Observaţie. Celula G15 trebuia să conţină valoarea 0 ca să avem matricea unitate. Totuşi celula conţine valoarea –5.55112E-17 care este reprezentarea pentru numărul -5.55112*10-17 care este o valoare foarte apropiată de 0. Valoarea rezultată nu este nulă datorită preciziei de calcul a calculatorului şi preciziei de reprezentare a numerelor. La această precizie putem considera rezultatul corect.

10.9. Tipărirea documentelor (date şi grafice)

Rezultatul prelucrării datelor cu ajutorul aplicaţiilor de calcul tabelar se finalizează, în general, cu tipărirea datelor de interes la imprimantă. Calitatea listing-ului obţinut depinde imprimantă şi de aplicaţia de calcul tabelar.

Imprimarea datelor de interes conţinute într-o foaie de calcul trebuie realizată cu respectarea următoarelor etape:

213


1. se finalizează formatarea foii prin punerea la punct a titlului tabelului, a antetului tabelului, a coloanei de numerotare, a altor informaţii ajutătoare;

2. se selectează prin dragare blocul de celule care trebuie imprimat;3. se stabilesc caracteristicile de tipărire ca titlul paginii, orientarea

paginii (portret sau landscape) etc;4. se face o previzualizare pe ecran a paginii tipărite, aşa cum

urmează ea să apară în cazul imprimării;5. se lansează efectiv imprimarea prin comanda Print.

Deoarece aplicaţiile de calcul tabelar, care se respectă, dispun de facilitatea WYSIWYG (What You See Is What You Get – ceea ce vezi este ceea ce obţii), se oferă utilizatorului garanţia că ceea ce este afişat pe ecranul monitorului se va obţine pe hârtie şi în urma imprimării. Această facilitate permite imprimarea atât a tabelelor cât şi a graficelor. În plus este permisă şi transmiterea listei într-un fişier care va fi listat ulterior, cu condiţia existenţei drver-ului software al imprimantei pe care se va lista fişierul, instalat pe sistemul curent.

10.10. Aplicaţii în Excel

A. Adrese Excel

O pagină de calcul Excel este formată dintr-un tablou cu două dimensiuni. Coloanele sunt adresate cu combinaţii de litere, iar liniile cu combinaţii de cifre (numere). O celulă poate fi referită prin adresă. Adresa a unei celule este formată din numele (adresa) coloanei şi numele liniei.Exemplu: Celula de la intersecţia coloanei D cu linia 7 are adresa: D7.

Adresa D7 este o adresă relativă. Adresa absolută se marchează prin caracterul ("$"), pus înaintea numelui liniei şi/sau coloanei.

O zonă este un dreptunghi. Adresa dreptunghiului este determinată de adresa celulei din stânga sus şi adresa celulei din dreapta jos a dreptunghiului, separate prin simbolul două puncte (":").

Exemplu: Zona mărginită de coloanele C şi F, şi liniile 2 şi 4 (inclusiv), are celula C2 în colţul din stânga sus şi celula F4 în colţul din dreapta jos. Această zonă se numeşte C2:F4.

Adresa unei linii este dată prin scrierea repetată a numelui linie separat prin simbolul două puncte (":"). O zonă mărginită de două linii, este adresată de numele celor două linii separate prin simbolul(":").

Exemplu: Linia 5 se numeşte 5:5. Zona mărginită de liniile 5 şi 7 inclusiv se numeşte 5:7.

Adresa unei coloane este dată prin scrierea repetată a numelui coloanei separat prin simbolul două puncte (":"). O zonă mărginită de

214


două coloane, este adresată de numele celor două coloane separate prin simbolul(":").

Exemplu: Coloana D se numeşte D:D. Zona mărginită de coloanele D şi H inclusiv se numeşte D:H..

Reuniunea a două zone se scrie prin numele zonelor separate prin simbolul (",").

Exemplu: Reuniunea dintre celula A2 şi zona D3:E7, se scrie A2,D3:E7.

Intersecţia a două zone se scrie prin numele zonelor separate prin simbolul (" ").

Exemplu: Intersecţia dintre zona 2:3 şi zona C3:F4, se scrie 2:3 C3:F4 şi este egală cu zona C3:F3.

Zonele Excel pot fi selectate pentru realizarea diferitelor funcţii astfel:

- tastând adresele corespunzătoare;- cu ajutorul tastaturii, dacă se ţine apăsată tasta Shift, cu ajutorul

săgeţilor se poate selecta o zonă,- cu ajutorul mouse-ului:- Se poziţionează cursorul mouse-ului pe celula din stânga sus a

zonei,- Se apasă butonul mouse-ului şi se ţine apăsat,- Se mută cursorul mouse-ului pe celula din dreapta jos a zonei,- Se eliberează butonul mouse-ului (în acest mod a fost selectată o

zonă),- Dacă se apasă pe marginile liniilor sau a coloanelor se poate

selecta o linie sau o coloană întreagă,- Dacă se apasă butonul mouse-ului pe o margine de linie sau

coloană şi se ţine apăsat, eliberându-se pe altă linie sau coloană, se va selecta o zonă marginită de două linii sau două coloane,

- Dacă se doreşte selectarea mai multor zone, se ţine apăsată tasta Control.

B. Formule în Excel

Pentru a scrie o formulă într-o celulă Excel facem următoarele operaţii:

- poziţionăm cursorul pe celula dorită, apoi introducem simbolul

("="), sau facem click cu mouse-ul pe butonul de pe bara de formule. Apoi se introduce formula: operatorii se introduc de la tastatură, iar operanzii de la tastatură (scriind adresele) sau se face click cu mouse-ul pe celulele respective.

215


C. Funcţii în Excel

Pentru a scrie o formulă într-o celulă Excel facem următoarele operaţii:

Poziţionăm cursorul pe celula dorită, apoi se alege opţiunea Insert -

> Function ... din meniu, sau facem click cu mouse-ul pe butonul de pe bara de formule. Funcţia dorită se poate tasta sau se poate alege din lista oferită de Excel. În stânga barei de formule se află ultima formulă folosită,

care poate fi utilizată. Dacă dorim altă formulă se apasă butonul de pe bara de formule şi vor fi afişate ultimele 10 funcţii utilizate. Dacă nici în această listă nu se află funcţia dorită se apasă pe opţiunea More functions ..., din lista de funcţii. Se deschide o fereastra care conţine toate funcţiile Excel, grupate pe categorii. Din fereastră se poate alege funcţia dorită. Se deschide o fereastră pentru alegerea argumentului funcţiei.

Fig. 10.7. Fereastră pentru selectarea argumentului funcţiei.

În lista Number1 se poate introduce numele zonei. Dacă se deschide lista (făcând click pe butonul din dreapta listei), poate fi selectată zona cu mouse-ul sau tastatura pe pagina Excel, apoi se închide lista făcând din nou click pe acelaşi buton. La sfârşit se apasă butonul OK. În fereastră se află o a doua listă (numită Number2). Excel va face reuniune între zonele scrise în cele două liste. După ce se completează şi a doua listă Excel va deschide o a treia listă. Se pot deschide mai multe liste şi apăsând tasta Tab, în interiorul unei liste.

În bara de formule se află o zonă de text în care este afişată formula din celula selectată. Dacă poziţionăm cursorul între parantezele funcţiei în bara de formule, se pot utiliza toate informaţiile de la capitolul Adrese Excel, pentru scrierea argumentului funcţiei.

216


D. Serii Excel

Excel poate utiliza şiruri aritmetice sau şiruri de text.Şirurile aritmetice se generează scriind în celule adiacente (pe linii

sau coloane), primele două numere ale şirului, se selectează zona formată din cele două celule. Se observă că în colţul din dreapta jos al zonei selectate se află un mic pătrat.

Fig. 10.8. Zona selectată pentru generarea unui şir.

Drag (a trage)- În mediile cu interfeţe grafice cu utilizatorul, a muta o imagine sau o fereastră dintr-un loc al ecranului în altul prin " apucarea " ei şi tragerea către noua poziţie cu ajutorul mouse-ului. Se plasează indicatorul mouse-ului pe obiectivul vizat, apoi se apasă butonul mouse-ului şi se ţine aşa până când mouse-ul ajunge în noua poziţie.

Se apropie cursorul mouse-ului de acest pătrat până în momentul când forma cursorului devine o cruce foarte subţire. Se execută o operaţie Drag până în locul unde dorim să generăm şirul.

Excel poate genera şi şiruri de text. Excel are o listă proprie de şiruri de text. Este destul de probabil că dorim liste care nu se află incluse în produsul Excel. În acest caz este foarte uşor să creăm liste noi. Pentru aceasta alegem din meniu, opţiunea: Tools -> Options ... . Se deschide fereastra Options.

217


Fig. 10.9. Fereastra Options.

Din fereastra Options se alege pagina Custom Lists. În această pagină se pot insera cu uşurinţă liste noi sau se pot corecta listele existente. Generarea listelor de text (din cele aflate în Custom Lists) în pagina Excel se face la fel ca şi pentru şirurile numerice.

Procedeul de generare al şirurilor poate fi utilizat şi pentru multiplicarea formulelor şi a funcţiilor. Pentru aceasta se selectează celula în care se află formula care trebuie multiplicată, apoi se trage formula executând operaţia Drag cu mouse-ul, ca la şiruri, până în locul în care dorim să repetăm formula.

Trebuie precizat că adresele relative se comportă ca variabile, adică operanzii se deplasează o dată cu formula.

Adresele absolute se comportă ca şi constante, adică operanzii nu-şi schimbă locul.

Exemplu: Să presupunem că dorim să calculăm:

,

Ecuaţia 10.1. Exemplu de funcţie pentru calcul Excel.

Deci a este o constantă = 7, X este o variabilă discretă care ia valorile {1, 2, 3, ..., 9}. Deci şi Y va fi tot o variabilă discretă.

218


Vom proceda astfel în Excel:- În celula B2 vom scrie 7 (valoarea constantei a);- În celulele B3 şi C3 vom scrie 1 respectiv 2 (primele două valori

din variabila discretă X;- Selectăm celulele B3 şi C3;- Agăţăm dreptunghiul din dreapta jos al zonei selectate şi efectuăm

operaţia Drag până în celula J3.

Fig. 10.10. Celulele B3 şi C3 selectate.

- Pentru funcţia Y, plasăm indicatorul mouse-ului în celula B4, executăm click, introducem simbolul ("="), executăm click în celula B2, introducem simbolul ("/"), de pe tastatura numerică şi executăm click în celula B3. Operaţia se încheie cu Enter. În acest moment în celula B4 va fi afişat numărul 7, rezultatul împărţirii 7 / 1. În formula lui Y, a = 7, este o constantă, deci trebuie să o scriem ca adresă absolută. Pentru aceasta facem click în celula B4 (În urma acestei operaţii în zona de scriere a formulelor din bara de formule apare textul "=B2/B3", formula introdusă anterior.), corectăm formula scriind simbolul ("$") înaintea lui B din adresa B2. Se poate scrie şi înaintea lui 2, dar nu este necesar deoarece vom executa multiplicarea formulei numai pe linie, deci numai adresa de coloană este necesar să fie absolută, adresa de linie nu contează. După corecţie tastăm din nou Enter.

Fig. 10.11. Celula B4 corectată (adresa $B2 este absolută pe coloane).

- Selectăm din nou celula B4. Agăţăm cu mouse-ul colţul din dreapta jos şi executăm Drag până în celula J4.

219


Aplicaţia 4

E. Tabel cu o variabilă de intrare

Exemplu:Să presupunem că dorim să construim un tabel care calculează trei

funcţii:

,

Ecuaţia 10.2. Exemplu de funcţie pentru un tabel cu o variabilă.

Completăm astfel pagina Excel:- Variabila de intrare X (o valoare oarecare, de exemplu prima

valoare 2.0) în tabel o introducem în celula B3;- Valorile variabilei X vor fi trecute pe coloana A7:A17 (fiind pe

coloană, va trebui ca în fereastra Table să deschidem lista Column input cell);

- valoarea constantei a = 5, va fi înscrisă în celula B4;- valorile constantelor {7, 9, 11} vor fi înscrise în linia B5:D5 (se

poate operaţia de generare şiruri);- valorile funcţiilor se vor înscrie în linia B6:D6 (Pentru aceasta se

scrie funcţia $B3*$B4*B5 în celula B6, deci primii doi parametrii sunt constante, ultimul fiind variabilă. Restul funcţiilor se vor genera efectuând operaţia Drag.);

220


Fig. 10.12. Introducerea parametrilor de intrare în tabelul Excel.

- Se selectează întregul tabel (A6:D17);- Din meniu se selectează opţiunea Data -> Table ..., pentru a

deschide fereastra Table.

Fig. 10.13. Fereastra Table.

- În fereastra Table deschidem lista Column input cell, deoarece valorile variabilei X se află pe coloană în tabel;

- Se face click în celula B3, unde se află variabila X în formulă;- Se închide lista Column input cell;- Se tastează OK.Tabelul va arăta astfel:

221


Fig. 10.14. Tabelul cu o variabilă de intrare.

F. Tabel cu două variabile de intrare

Exemplu:Să presupunem că dorim să construim un tabel cu două variabile de

intrare:,

Fig. 10.15. Funcţie de două variabile pentru calcul tabel.

Completăm astfel pagina Excel:- În celula B3 se înscrie valoarea constantei a = 5;- În celula B4 se înscrie o valoare a variabilei X, de exemplu 1,2;- Valorile variabilei X se înscriu pe o coloană în tabel, zona

A7:A15;- În celula B5 se înscrie o valoare a variabilei Y, de exemplu 2,5;- Valorile variabilei Y se înscriu pe o linie în tabel, zona B6:I6;- Expresia funcţiei se scrie la intersecţia liniei şi coloanei - celula A6

(funcţia este =B3*B4*B5)

222


Fig. 10.16. Datele de intrare pentru tabelul cu două variabile.

- Se selectează întregul tabel (A6:I15);- Din meniu se selectează opţiunea Data -> Table ..., pentru a

deschide fereastra Table.- Deschidem lista Column input cell şi facem click pe celula B4,

unde se află în formulă variabila X;- Închidem lista Column input cell;- Deschidem lista Row input cell şi facem click pe celula B5, unde

se află în formulă variabila Y;- Închidem lista Roww input cell;- Tastăm OK.Tabelul va arăta ca în figura 10.17.

G. Rezolvarea ecuaţiilor cu Excel

Exemplu:Să se rezolve ecuaţia:

Ecuaţia 3. Ecuaţie de rezolvat în Excel.

223


Fig. 10.17. Tabelul cu două variabile de intrare.

Completăm astfel pagina Excel:- În celula B3 va fi afişat rezultatul X;- În celula B4 se scrie funcţia: =COS(B3)-B3;

Fig. 10.18. Funcţia =COS(B3)-B3.

- Se selectează din meniu opţiunea Tools -> Goal Seek ...;- Se deschide lista Set cell şi se face click pe celula B4, unde se află funcţia;- În cutia To value se specifică valoarea 0, cu cât este egală ecuaţia;- Se deschide lista By changing cell şi se face click pe celula B3 unde va fi afişat rezultatul;- Tastăm OK.

Rezultatul va arăta ca în figura 10.19.Comentarii:- În celula B3 se află soluţia ecuaţiei; X = 0,739062461;- În celula B4 se află valoare funcţiei f(X) = COS(X) - X, pentru

valoarea lui X = 0,739062461 (valoarea 3,79447E+05 este egala cu ; .

224


Fig. 10.19. Rezolvarea ecuaţiei COS(X) - X = 0.

- În celula C4 se află rezultatul convertit în grade centesimale cu formula:

Ecuaţia 10.4. Conversia radianilor în grade centesimale.

- Soluţia 47,0501776 se va regăsi şi la rezolvarea aceleiaşi ecuaţii în Visual Basic (vezi Rezolvarea ecuaţiilor prin metoda reprezentării).

- Trebuie specificat că această metodă are limite foarte serioase, deoarece poate găsi o singură rădăcină. Ecuaţia dată are o singură soluţie, lucru ce va fi demonstrat la rezolvarea aceleiaşi ecuaţii cu Visual Basic.

H. Diagrame

Diagramele permit afişarea unor funcţii de variabile discrete.Presupunem că dorim să calculăm funcţia

Ecuaţia10.5. Funcţie pentru trasat diagramă.şi să-i trasăm diagrama.

Completăm astfel pagina Excel:- În celulele C4 şi D4 se trec primele 2 valori ale lui X;- Se agaţă colţul din dreapta jos cu mouse-ul şi se execută Drag până

la celula L4 (s-au generat toate valorile argumentului X;- În celula C5 se scrie valoarea funcţiei =3*C4*C4*C4-

4*C4*C4+7;- Cu Drag se copiază formula până în celula L5;- Se selectează linia C5:L5;- Se selectează opţiunea Insert -> Chart ..., pentru a deschide

fereastra Chart (diagramă, histogamă);

225


Fig. 10.20. Primele două valori ale argumentului X.

- Se selectează un tip de diagramă cu linii (line);- Se apasă butonul Finish;Pentru adăugarea altor serii în diagramă se execută paşii următori:- Se efectuează click dreapta în interiorul diagramei şi se selectează

dim meniu apărut Source Data ...;- În pagina Series se deschide lista Series se execută click pe

butonul Add. Se crează astfel o nouă serie. Cu ajutorul listelor Name şi Values se pot introduce noile serii. Aceste noi serii trebuie completate şi în foaia de calcul.

- Se tastează OK.Pentru completarea seriei axei X în diagramă se execută paşii

următori:- Se efectuează click dreapta în interiorul diagramei şi se selectează

dim meniu apărut Source Data ...;- În pagina Series se deschide lista Category (x) axis labels şi se

selectează în pagina Excel linia C4:L4, variabila de intrare X;- Se tastează OK;Pentru completarea titlului şi a legendelor se execută paşii următori:- Se efectuează click dreapta în interiorul diagramei şi se selectează

dim meniu apărut Chart Options ...;- Se selectează pagina Titles;- Pe linia Chart title: se completează un titlu (de exemplu:

Creşterea funcţiei);- Se selectează pagina Legend;- Se debifează opţiunea Show legend;- Se apasă butonul OK;- Diagrama trebuie să arate ca în figura 10.21.

226


Fig. 10.21. Diagrama Creşterea funcţiei.

I. Scenarii

Scenariile sunt seturi de variabile de intrare, care pot fi schimbate cu mare uşurinţă. Pentru realizarea de scenarii trebuie completate pe rând câte un set de date de intrare.

Presupunem că dorim să calculăm funcţia anterioară pentru 5 seturi de variabile de intrare,

,

Fig. 10.22. Funcţie pentru creare scenarii.

şi să-i trasăm diagrama.Completăm astfel pagina Excel:- Cu ajutorul tehnicii Drag introducem prima serie de variabile de

intrare în linia C4:L4;- Selectăm linia C4:L4;- Din meniu selectăm opţiunea Tools -> Scenarios ...;- Apăsăm butonul Add ...;- Se completează numele scenariului (de ex: Scenariu 1);- Se apasă butonul OK;- Apare o fereastra în care se permite modificare parametrilor

scenariului (nu o folosim);- Se apasă butonul OK;- Se apasă butonul Close;

227


- Paşii de mai sus se execută de 5 ori pentru toate cele cinci serii de parametri de intrare;

- Pentru vizualizare pentru fiecare scenariu se selectează din meniu opţiunea Tools -> Scenarios ...;

- În fereastra Scenario Manager se alege scenariul dorit şi se apasă butonul Show (scenariul apare în tabel, dar diagrama nu este afectată de noul scenariu);

- Se apasă butonul Close, se închide fereastra Scenario Manager şi Diagrama va fi refăcută conform noului scenariu.

J. Funcţii trigonometrice

Dorim să calculăm SIN(x), în grade centesimale pentru unghiurile {0, 10, 20, ...,100}. Trebuie ştiut că Excel şi Visual Basic face calculele în Radiani.

- În celulele B2 şi C2 scriem valorile 10 şi 20, apoi cu tehnica Drag până în celula L2 generăm valoarea unghiurilor în grade centesimale;

- Ştiind că raportul a două mărimi este acelaşi indiferent de unitatea de măsură utilizată, aplicând pentru unghiuri obţinem următoarea relaţie:

,

Ecuaţia 10.6. Relaţia pentru schimbarea unităţii de măsură a unghiurilor.

- Pentru calculul lui folosim următoarea relaţie:

Ecuaţia 10.7. Calculul lui .

- Pentru transformarea unghiurilor în grade centesimale se utilizează relaţia:

Ecuaţia 10.8. Relaţia de transformare din grade centesimale în sexazecimale.

228


Fig. 10.23. Relaţia de transfomare în grade sexazecimale în tabel Excel.

- Restul valorilor se generează cu tehnica Drag;- Pentru transformarea unghiurilor în radiani se utilizează relaţia:

Ecuaţia 10.9. Relaţia de transfomare din grade centesimale în radiani.

Fig. 10.24. Relaţia de transfomare în radiani în tabel Excel.

- Restul valorilor se generează cu tehnica Drag;- În celula B5 se scrie SIN(B4);-Cu tehnica Drag se generează valorile lui SIN(X) până în celula L5;- Se selectează linia B5:L5 (funcţia SIN(X);- Se selectează din meniu opţiunea Insert -> Chart ..., pentru a

deschide fereastra Chart (diagramă, histogamă);- Se urmează paşii de la capitolul Diagrame,- Se va obţine următoarea diagramă ca în figura 10.26.

229


K. Calcul statistic în Excel

Există funcţii statistice predefinite în Excel. De asemenea în Excel există posibilitatea construirii cu foarte mare uşurinţă a acestor funcţii. De exemplu pentru media aritmetică există funcţia AVERAGE(). De exemplu dacă dorim să calculăm media aritmetică a şirului {1, 2, 3, 4, 5, 6, 7}, procedă astfel:- Introducem numerele {1, 2, 3, 4, 5, 6, 7} în coloana A:A, de la A1, la A7;

- În B1 scriem =AVERAGE(A1:A7);- Rezultatul este 7.

Fig. 10.25. Funcţia SIN(X) în tabel Excel.

Fig. 10.26. Funcţia SIN(X), X în grade centesimale.

O metodă mult mai generală este utilizând formule matematice.- Introducem numerele {1, 2, 3, 4, 5, 6, 7} în coloana A:A, de la A1, la A7;

- În B1 scriem: =ABS(SIGN(A1)) (adică 1 pentru A1 diferit de 0);- Multiplicăm formula pe coloană efectuând operaţia Drag;- În C1 scriem: =A1*A1;- Multiplicăm formula pe coloană efectuând operaţia Drag;- În D1 scriem: =SUM(B:B), adică valoarea lui N;

230


- În D1 vom avea suma numerelor diferite de 0, care au fost introduse în coloana A:A. Dacă dorim să avem suma numerelor (inclusiv cele care au valoarea zero), atunci folosim funcţia COUNT(A:A).

- În D2 scriem: =SUM(A:A), adică ;

- În D3 scriem: =SUM(C:C), adică ;

- În D4 scriem: =D2/D1, adică , media aritmetică;

- În D5 scriem: =D3/D1, adică ;

- Pagina Excel va arăta astfel:

Fig. 10.27. Calcul statistic în Excel.

În acest mod, foarte uşor se calculează abaterea medie pătratică, etc.

231

Excel

Documents

Transcript of Excel