Excel 4

Utilizarea formulelor în Excel

CAPITOLUL 4 UTILIZAREA FORMULELOR ÎN EXCEL 4.1. Utilizarea formulelor în Excel Microsoft Excel a fost creat special pentru efectuarea de calcule numerice. Pentru a indica programului modul de calcul trebuie scrise formule. Formulele sunt formate, de regul[, din adrese de celule, valori =i operatori matematici. La efectuarea calculelor sunt respectate toate regulile algebrice: prioritatea opera\iilor de ]nmul\ire =i ]mp[r\ire, a parantezelor etc. Toate formulele se introduc cu semnul = ]n fa\[. Dac[ se omite semnul ˆ, Excel consider[ c[ ]n celula respectiv[ a fost introdus un text. }n continuare sunt prezenta\i principalii operatori utiliza\i ]n Excel. Operatori aritmetici Operator – + * / % ^

Scop sc[dere adunare ]nmul\ire ]mp[r\ire procente ridicare la putere

Operatori de text Folosind operatorii pentru text se pot concatena texte cuprinse ]ntre ghilimele (“”) sau texte din celule diferite. De exemplu dac[ se scrie formula ˆ”Total v`nz[ri: ”$B4 rezultatul este Total v`nz[ri: 28, dac[ celula B4 are valoarea 28. Operatori pentru compara\ii Pentru a compara rezultatele se pot folosi operatorii pentru compara\ii: Operator

Scop

ˆ < > <ˆ >ˆ

egal cu mai mic dec`t mai mare dec`t mai mic sau egal cu mai mare sau egal cu

<>

diferit de

Pentru introducerea unei formule se execut[ pa=ii urm[tori: 1. Se selecteaz[ celula ]n care trebuie s[ apar[ rezultatul formulei; 2. Se scrie formula precedat[ de semnul =. 3. Se apas[ tasta <Enter> Dup[ ap[sarea tastei <Enter> ]n celul[ apare automat rezultatul calculelor. Dac[ celula care con\ine formula este selectat[ din nou, se constat[: • ]n bara de formule este afi=at[ formula de calcul; • ]n celul[ apare rezultatul. Exemplu:

Modelarea deciziilor utiliz`nd foile de calcul O foaie de calcul con\ine urm[toarele valori: • ]n celula A1 valoarea 7; • ]n celula B1 valoarea 3. Dac[ ]n celula C1 se scrie formula =A1+B1, dup[ ap[sarea tastei <Enter> ]n celul[ se va afi=a rezultatul 10 (=7+3). Excel poate fi setat astfel ]nc`t =i ]n celule s[ fie afi=ate formulele. Pentru aceasta trebuie efectua\i pa=ii urm[tori: 1. Se aplic[ comanda Tools, Options. 2. Se selecteaz[ butonul View (figura 4.1).

Figura 4.1 - caseta de dialog Options - sec\iunea View 3. 4.

}n zona Window Options se aplic[ un clic ]n c`mpul Formulas, pentru a-l marca. Se selecteaz[ butonul OK.

Vizualizarea formulelor din celulele unei foi de calcul ar fi necesar[ doar ]n cazul ]n care trebuie vizualizate toate formulele deodat[ (pentru tip[rire sau corec\ii). Pentru revenirea la situa\ia ]n care ]n celule apar rezultatele formulelor se aplic[ procedura anterioar[, numai c[ se va demarca c`mpul Formulas.

4.2. Copierea formulelor La copierea unei formule aceasta este adaptat[, pentru a corespunde pozi\iei celulei ]n care va fi copiat[. De exemplu, dac[ se copiaz[ formula =A1+B1 din celula C1 ]n celula D1, aceasta va fi adaptat[ pentru coloana D, devenind =B1+C1. La copierea ]n celula C2, aceasta devine =A2+B2.

Utilizarea formulelor în Excel Dac[ se copiaz[ o formul[ pe aceea=i linie, to\i indicatorii de linie din celulele formulei r[m`n neschimba\i. Indicatorii de coloan[ se modific[. Peste o coloan[ la dreapta, indicatorii de coloan[ se modific[ cu o liter[, peste dou[ coloane cu dou[ litere etc. Dac[ se copiaz[ o formul[ pe aceea=i coloan[, prin copiere r[m`n neschimba\i indicatorii de coloan[. }n schimb se modific[ indicatorii de linie. Cu o linie mai jos indicatorii de linie cresc cu 1, peste dou[ linii cu 2 etc. Exemplu: }n celula A5 se introduce formula =B2. Excel =tie c[ ]n celula A5 este folosit[ o celul[ care se g[se=te cu trei r`nduri mai sus =i o coloan[ la dreapta. Prin copierea formulei din A5 ]n C8, Excel actualizeaz[ indicatorii de linie =i de coloan[ astfel ]nc`t ]n celula C8 va fi referit[ o celul[ care se g[se=te tot cu trei r`nduri mai sus =i o coloan[ la dreapta, dar fa\[ de C8. Deci formula din aceast[ celul[ va fi =D5. Pentru a copia o formul[ ]ntr-un domeniu de celule al[turat, se efectueaz[ urm[torii pa=i: 1. Se aplic[ un clic pe celula care con\ine formula ce trebuie copiat[. 2. Se pozi\ioneaz[ cursorul mouse-ului ]n col\ul din dreapta jos al celulei. 3. C`nd cursorul ]=i modific[ forma (apare un cursor de forma unei cruci mici) se apas[ butonul st`ng al mouse-ului =i se trage de cursor peste celulele ]n care trebuie copiat[ formula. 1. 2. 3. 4.

Formulele pot fi copiate =i ]n celule neadiacente, folosind comenzile Copy =i Paste: Se aplic[ un clic pe celula care con\ine formula; Se aplic[ comanda Edit, Copy; Se aplic[ un clic pe celula ]n care se face copierea; Se aplic[ comanda Edit, Paste.

4.3. Aplicaţie Un fond mutual de\ine ac\iuni la mai multe societ[\i comerciale. Pentru fiecare tip de ac\iune se cunosc: valoarea de cump[rare, valoarea curent[ de pe pia\[ =i num[rul de ac\iuni cump[rate. S[ se calculeze profitul (sau pierderea) pentru fiecare tip de ac\iune. Se va crea urm[toarea foaie de calcul (figura 4.2)

Figura 4.2 Foaia de calcul creat[ con\ine urm[toarele informa\ii: • ]n coloana A – numele ac\iunilor; • ]n coloana B – num[rul de ac\iuni cump[rate; • ]n coloana C – valoarea la cump[rare a ac\iunilor; • ]n coloana D – valoarea curent[ a ac\iunilor; • ]n coloana E – se va calcula profitul sau pierderea pentru fiecare ac\iune.

Modelarea deciziilor utiliz`nd foile de calcul }n celula E2 se va introduce formula =B2*(D2-C2). Se copiaz[ formula din E2 ]n domeniul E3:E4, astfel: 1. Se aplic[ un clic ]n celula E2; 2. Se pozi\ioneaz[ cursorul ]n col\ul din dreapta jos al celulei; 3. C`nd cursorul ]=i modific[ forma (apare un cursor de forma unei cruci mici ‡) se apas[ butonul st`ng al mouse-ului =i se trage de cursor peste celulele E3 =i E4.

4.4. Utilizarea adreselor absolute A=a cum am v[zut, la copierea unei formule Excel adapteaz[ indicatorii de linie =i de coloan[ ai celulelor (referin\ele celulei) ]n func\ie de noua pozi\ie din foaia de calcul. Modul de adresare al celulelor folosit p`n[ ]n prezent (nota\ia A7) folose=te sistemul de adresare relativ. Exist[ multe situa\ii ]n care, prin copierea unor formule, unele celule trebuie s[ r[m`n[ fixe (nu trebuie s[ se modifice indicatorii de linie/coloan[). }n acest caz se folose=te sistemul de adresare absolut. }n fa\a indicatorilor care nu trebuie s[ se modifice se pune simbolul $. De exemplu $A$7. Exemplu: }n celula A5 se introduce formula ˆ$B$2. Dup[ copierea formulei din A5 ]n C8, ]n celula C8 formula va fi tot ˆ$B$2. Prin copiere nu s-a modificat nici indicatorul de linie, nici cel de coloan[. La copierea pe linie/coloan[ indicatorii de linie/coloan[ nu se modific[. }n aceste cazuri formulele pot con\ine referiri mixte: A$7 – linia este adresat[ absolut, coloana relativ. $A7 - linia este adresat[ relativ, coloana absolut.

4.5. Aplicaţie La firma XYZ pre\urile sunt stabilite ]n $. Datorit[ modific[rii cursului de schimb, trebuie s[ recalculeze destul de frecvent pre\ul ]n lei al produselor sale. S[ se proiecteze o foaie de calcul astfel ]nc`t pre\ul ]n lei s[ se calculeze automat la modificarea cursului de schimb. Se va crea urm[toarea foaie de calcul (figura 4.3):

Figura 4.3 Dac[ ]n celula C4 s-ar introduce formula =B4*B1 (pre\ul ]n lei * cursul de schimb) =i acest[ formul[ s-ar copia ]n C5 =i C6, rezultatele nu ar fi corecte. }n C5 s-ar ob\ine valoarea 0, iar ]n C6 #N/A. Dac[ analiz[m formulele din aceste celule constat[m c[: • celula C5 con\ine formula =B5*B2 (B4 s-a transformat ]n B5, iar B1 ]n B2); • celula C6 con\ine formula =B6*B3 (B4 s-a transformat ]n B6, iar B1 ]n B3).

Utilizarea formulelor în Excel Formulele corecte din aceste celule ar trebui s[ fie =B5*B1, respectiv =B6*B1. Deci celula B1 nu trebuie s[ se modifice atunci c`nd este copiat[. Pentru a realiza acest lucru celula B1 trebuie referit[ absolut. Deci trebuie introdus simbolul $ ]n fa\a indicatorului de linie. Formula corect[ care trebuie introdus[ ]n celula C4 este ˆB4*B$1. La copierea acestei formule ]n C5 =i C6 se constat[ c[ formulele din aceste celule sunt corecte. Celula C5 con\ine formula =B5*B$1, iar celula C6 con\ine formula =B6*B$1.