Utilizarea adreselor absolute si a functiilor.

Asa cum am vazut, la copierea unei formule, Excel adapteaza indicatorii de linie si de coloana ai celulelor (referintele celulei) in functie de noua pozitie din foaia de calcul.

Modul de adresare al celulelor folosit pana in prezent (notatia B2, D2, C2) foloseste sistemul de adresare relativ.

Exista multe situatii in care, prin copierea unor formule, unele celule trebuie sa ramana fixe (nu trebuie sa se modifice indicatorii de linie/coloana). In acest caz se foloseste sistemul de adresare absolut. In fata indicatorilor care nu trebuie sa se modifice se pune simbolul $. De exemplu $B$2.

Exemplu: In celula A5 se introduce formula =$B$2. Dupa copierea formulei din A5 in C8, in celula C8 formula va fi tot =$B$2. Prin copiere nu s-a modificat nici indicatorul de linie, nici cel de coloana.

La copierea pe linie/coloana indicatorii de linie/coloana nu se modifica. In aceste cazuri formulele pot contine referiri mixte:

B$2 - linia este adresata absolut, coloana relativ.

$B2 - linia este adresata relativ, coloana absolut.

Aplicatie.

La firma XYZ salariile sunt stabilite in $. Datorita modificarii cursului de schimb, trebuie sa recalculeze destul de frecvent salarul in lei al angajatilor. Sa se proiecteze o foaie de calcul astfel incat salarul in lei sa se calculeze automat la modificarea cursului de schimb. Se va crea urmatoarea foaie de calcul:

Daca in celula C4 s-ar introduce formula =B4*B1 (salarul in lei * cursul de schimb) si acesta formula s-ar copia in C5 si C6, rezultatele nu ar fi corecte. In C5 s-ar obtine valoarea 0, iar in C6 #N/A.

Daca analizam formulele din aceste celule constatam ca:

celula C5 contine formula =B5*B2 (B4 s-a transformat in B5, iar B1 in B2);

celula C6 contine formula =B6*B3 (B4 s-a transformat in B6, iar B1 in B3).

Formulele corecte din aceste celule ar trebui sa fie =B5*B1, respectiv =B6*B1.

Deci celula B1 nu trebuie sa se modifice atunci cand este copiata. Pentru a realiza acest lucru celula B1 trebuie referita absolut. Deci trebuie introdus simbolul $ in fata indicatorului de linie si a celui de coloana. Formula corecta care trebuie introdusa in celula C4 este =B4*$B$1. La copierea acestei formule in C5 si C6 se constata ca formulele din aceste celule sunt corecte. Celula C5 contine formula =B5*$B$1, iar celula C6 contine formula =B6*$B$1.

Utilizarea functiilor in Excel.

Excel ofera peste 200 de functii (formule predefinite), care permit crearea unor formule complexe pentru o mare diversitate de aplicatii: stiintifice, ingineresti, de afaceri etc.

O functie este definita de numele si argumentele ei. Argumentele unei functii se introduc intre paranteze. In cazul in care se folosesc mai multe argumente, acestea se separa prin virgula. Daca o functie nu are nici un argument, se scriu totusi parantezele, numai ca intre ele nu se va mai nota nimic. De asemenea, functiile pot contine atat argumente obligatorii cat si argumente optionale.

Cel mai folosit tip de argument este cel numeric, dar argumentele pot fi si de tip text, data ora sau matrice. Daca un text este folosit ca argument intr-o functie, el trebuie introdus intre ghilimele.

Datorita numarului mare de functii incorporate in Excel acestea au fost grupate in mai multe categorii:

Functii matematice

Functii financiare

Functii logice

Functii de cautare

Functii de lucru cu texte

Functii pentru lucrul cu date si ore.

Functii statistice, etc.

Pentru a introduce o functie in Excel se poate utiliza una din urmatoarele metode:

Functia este scrisa de utilizator. In acest caz se presupune ca utilizatorul stie sintaxa functiei.

Functia este introdusa folosind aplicatia Function Wizard, care se lanseaza la aplicarea comenzii Insert, Function.

In lista Or select a category sunt afisate toate categoriile de functii incorporate in Excel. La selectarea unei categorii in caseta Select a function sunt afisate in ordine alfabetica functiile existente in categoria selectata.

Dupa selectarea unei functii, se aplica un clic pe butonul OK pentru a trece la urmatoarea caseta de dialog .

In caseta de dialog a functiei alese, trebuie introduse argumentele necesare pentru functia respectiva. Casetele text pentru argumente trebuie sa contina valori sau referinte de celule.Functia se termina de introdus selectand butonul OK

In continuare vor fi prezentate functiile Excel intalnite mai frecvent, grupate pe categorii.

Functii matematice

- ABS (numar): returneaza valoarea absoluta a unui numar.

Exemple: ABS (-5) va returna valoarea 5

ABS (5) va returna valoarea 5

- EXP (numar): calculeaza exponentiala unui numar (e ridicat la puterea specificata de argumentul numar).

Exemplu: EXP (0) va returna valoarea 1

- LN (numar): calculeaza logaritmul natural al numarului specificat.

Exemplu: LN (1) va returna valoarea 0

- INT (numar): rotunjeste un numar pana la cea mai apropiata valoare intreaga.

Exemple: INT (7.6) va returna valoarea 7

INT (-7.6) va returna valoarea 8

- MOD (a, b): calculeaza restul (modulul) lui a impartit la b. Daca b este 0, se va afisa valoarea de eroare #DIV/0.

Exemple: MOD (7, 6) va returna valoarea 1

MOD (32, 15) va returna valoarea 2

- POWER (a, b): efectueaza ridicarea unui numar a la puterea b.

Exemplu: POWER (2, 2) va returna valoarea 4

- RAND ( ): furnizeaza un numar aleator intre 0 si 1. Functia nu accepta argumente. Apasarea tastei F9 va produce generarea altor numere.

- ROUND (numar, numar de zecimale): rotunjeste numarul specificat in primul argument la numarul de zecimale specificat in al doilea argument.

Exemple: ROUND (753.345, 2) va returna valoarea 753.35

ROUND (753.342, 2) va returna valoarea 753.34

- ROUNDUP (numar, numar de zecimale): rotunjeste in sus numarul specificat in primul argument, cu numarul de zecimale specificat in al doilea argument.

Exemplu: ROUNDUP (7.49, 1) va returna valoarea 7.5

- ROUNDDOWN (numar, numar de zecimale): rotunjeste in jos numarul specificat in primul argument, cu numarul de zecimale specificat in al doilea argument.

Exemplu: ROUNDDOWN (7.49, 1) va returna valoarea 7.4

- SQRT (numar): extrage radacina patrata din argumentul specificat.

Exemplu: SQRT (4) va returna valoarea 2

SUM (numar1, numar2, .) calculeaza suma tuturor argumentelor. Argumentele pot fi valori, celule individuale sau domenii de celule, dar numarul lor este limitat la 30. Argumentele numerice sunt ignorate.

Exemplu: SUM (A1:B3) va calcula suma valorilor din celulele A1, A2, A3,B1,B2, B3

- AVERAGE (numar1, numar2, .): calculeaza media aritmetica a tuturor argumentelor. Argumentele pot fi valori, celule sau domenii de celule, dar numarul lor este limitat la 30. Argumentele nenumerice sunt ignorate.

Exemplu: AVERAGE (A1:B3) va calcula media aritmetica a valorilor din celulele A1, A2, A3, B1, B2, B3.

COUNT (numar1, numar2, .): numara in argumentele specificate celulele care contin numere. Functia poate avea intre 1 si 30 de argumente.

Exemplu: COUNT (A2:A5) va returna valoarea 3 atunci cand domeniul A2:A4 contine numerele 2,3,4, iar celula A5 este goala.

- MAX (numar1, numar2, .): returneaza valoarea celui mai mare argument. Functia poate avea cel mult 30 de argumente. Celulele goale, valorile de tip text, logic sau de tip eroare vor fi ignorate.

Exemplu: MAX (A1:A3) va returna valoarea 10, daca numerele din acest domeniu sunt: 1,10, 7, 4.

- MIN (numar1, numar2, .): returneaza valoarea celui mai mic argument. Functia poate avea cel mult 30 de argumente. Celulele goale, valorile de tip text, logic sau de tip eroare vor fi ignorate.

Exemplu: MIN (A1:A3) va returna valoarea 1, daca numerele din acest domeniu sunt: 1,10, 7, 4.

- IF (conditie, valoare adevarata, valoare falsa): evalueaza o conditie. Daca conditia este adevarata functia va returna al doilea aergument- valoarea adevarata. Daca conditia este falsa functia va returna al treilea argument - valoarea falsa.

Exemplu:

IF (A1<A2, "mai mic", "mai mare") va returna textul mai mic daca celula A1 contine valoarea 7 si celula A2 contine valoarea 10.

Functii logice.

Functiile logice sunt folosite in cazurile in care trebuie evaluate mai multe conditii. In general, aceste functii nu se folosesc singure, ele apar ca argumente la alte functii (de exemplu in functia IF

- AND (conditia1, conditia2, .): returneaza valoarea adevarata (TRUE) daca toate conditiile specificate in argumente sunt adevarate. Daca cel putin o conditie nu este adevarata functia AND va returna valoarea fals (FALSE).

- OR (conditia1, conditia2, .): returneaza valoarea adevarata (TRUE) daca cel putin o conditie din cele specificate in argumente este adevarata. Daca nici o conditie nu este adevarata functia OR va returna valoarea fals (FALSE).

- NOT (conditie): returneaza valoarea adevarata daca conditia este falsa si daca conditia este adevarata.

Functii text.

Functiile text permit manipularea informatiilor de tip text. Datele din foile de calcul pot fi concatenate pentru a alcatui titluri, propozitii, etichete.

- CHAR (numar): returneaza caracterul care corespunde codului ASCII specificat ca argument.

Exemplu: CHAR (65) va returna caracterul A.

- CONCATENATE (text1, text2, .): efectueaza reuniunea tuturor argumentelor .

Exemplu: CONCATENATE ('Microsoft', 'Excel') va returna textul Microsoft Excel.

- EXACT (text1, text2): compara textele text1 si text2. Daca acestea sunt identice functia va returna valoarea adevarata (TRUE), astfel se va retine valoarea logica FALSE. Functia face distinctie intre literele mici si mari.

FIND(text-cautat,sursa start-num): cauta primul argument, text-cautat in textul din al doilea argument sursa incepand cu pozitia specificata de start-num. In cazul in care acesta este gasit, functia FIND returneaza pozitia de inceput a textului cautat. Daca argumentul start-num este in afara limitelor sau daca nu este gasita o valoare, se va afisa codul de eroare #VALUE. Daca argumentul start-num nu este specificat, se presupune ca acesta are valoarea 1.

Exemplu: FIND (B12, 'ABCDE', 1) va returna valoarea 3 daca celula B12 contine caracterul C.

- LEFT (text, num-car): afiseaza primele num-car caractere din partea stanga a unui text. Argumentul num-car trebuie sa fie mai mare ca 0. Daca se omite introducerea sa se va presupune ca este egal cu 1.

Exemplu: LEFT (A1, 5) va returna valoarea Micro daca in celula A1 se gaseste textul Microsoft.

- RIGHT (text, num-car): afiseaza primele num-car caractere din partea dreapta a unui text. Argumentul num-car trebuie sa fie mai mare ca 0. Daca se omite introducerea sa se va presupune ca este egal cu 1.

Exemplu: RIGHT (A1, 4) va returna valoarea soft daca in celula A1 se gaseste textul Microsoft.

LEN (text): calculeaza numarul de caractere din textul specificat de argument.

Exemplu: LEN ('Microsoft') va returna valoarea 9.

- MID (text, start-num, num-car): extrage un numar de num-car caractere din text, incepand cu pozitia start-num.

Exemplu: MID ('Microsoft Excel 7.0', 11, 5) va returna textul Excel.

- LOWER (text): converteste eventualele majuscule din text in litere mici.

Exemplu: LOWER ('Microsoft Excel') va returna microsoft excel

- PROPER (text): determina afisarea textului cu litere mici, inceputurile de cuvinte fiind scrise cu majuscule.

Exemplu: PROPER ('Microsoft Excel') va returna Microsoft Excel.

- TRIM (text): sterge toate blank-urile din text, astfel incat intre cuvinte sa ramana un singur spatiu.

Exemplu: TRIM ('Microsoft Excel') va returna Microsoft Excel.

- TEXT (valoare, format-text): converteste o valoare numerica in text si o afiseaza corespunzator formatului indicat prin al doilea argument. Rezultatul apare afisat ca un numar formatat, dar in realitate este de tip text. Se pot utiliza oricare din formatele numerice predefinite sau personalizate, prezentate in lectia "Formatarea foilor de calcul".

Exemplu: TEXT (457989, '$#, ##0.00') va returna $4,579.89

Functii pentru date si ore.

Programul Excel ataseaza fiecarei date calendaristice si ore cate un numar serial. Numerele seriale atasate datelor calendaristice sunt mai mari ca 1, cele atasate orelor sunt subunitare. Cand efectueaza calcule cu date si ore, Excel foloseste aceste numere seriale, numai formatul de afisare este de tip data sau ora. Cele mai folosite functii de lucru cu date si ore sunt:

DATE (an, luna zi): returneaza numarul serial pentru data specificata.

Exemplu: DATE (1900,1,1) va returna 1 (numarul serial al datei 1.1.1900)

- NOW ( ): calculeaza numarul serial al datei si al orei extrase din ceasul intern al calculatorului. Excel actualizeaza data si ora doar la deschiderea sau recalcularea foii. Aceasta functie nu are argumente, insa este necesara introducerea parantezelor.

Exemplu: NOW ( ) va returna 01/ 01/ 07 10:43, daca aceasta este data curenta.

- YEAR (data calendaristica): extrage anul din data specificata.

Exemplu: YEAR ( 1/ 3/ 2007) va returna 2007.

- MONTH (data calendaristica): extrage luna din data specificata.

Exemplu: MONTH ( 1/ 3/ 2007) va returna 1 (se considera ca data este introdusa in formatul luna/ zi/ an)

- DAY (data calendaristica): extrage ziua din data specificata.

Exemplu: DAY (1/ 3/ 1999) va returna 3.

- TIME (ora minut, secunda): calculeaza numarul serial corespunzator numarului de ore, minute si secunde indicate.

Exemplu: TIME (18, 4, 19) furnizeaza valoarea 0,752998.

- HOUR (ora): returneaza numarul de ore corespunzatoar orei specificate.

Exemplu: HOUR (19:10:30) va returna valoarea 19.

- MINUTE (ora): returneaza numarul de minute corespunzatoare orei specificate.

Exemplu: MINUTE (19:10:30) va returna valoarea 10.

SECOND (ora): returneaza numarul de secunde corespunzator orei specificate.

Exemplu: SECOND (19:10:30) va returna valoarea 30.

Functii financiare.

- FV (dobanda reper, plata vp, tip): calculeaza valoarea viitoare pentru o serie de incasari/ plati egale (specificate in argumentul plata), facute intr-un numar de perioade reper, cu o anumita dobanda (primul argument). Dobanda trebuie sa aiba aceeasi unitate de masura ca reper. De exemplu, dobanda anuala trebuie sa se imparta la 12 daca incasarile/ platile se fac lunar.

Numarul vp reprezinta valoarea prezenta sau suma care se investeste/ imprumuta in momentul initial. Daca vp este omis se considera ca este 0.

Tip poate lua valoarea 0 sau 1. Daca are valoarea 0 se considera ca platile se fac la sfarsitul perioadei, daca are valoarea 1, platile se fac la inceputul perioadei. Daca argumentul tip este omis se considera ca are valoarea 0.

Banii care sunt platiti sunt reprezentati prin numere negative, iar cei incasati sunt reprezentati prin numere pozitive.

Exemplu: Sa presupunem ca o persoana vrea sa investeasca bani pentru un proiect care va fi realizat peste 1 an. De aceea, depune 1 000 $ intr-un cont de economii cu o dobanda de 6% pe an (dobanda lunara va fi 6%/ 12, adica 0.5%). De asemenea, sa presupunem ca persoana respectiva va depune cate 100 $ la inceputul fiecarei luni, in urmatoarele 12 luni. Cati dolari vor fi in cont la sfarsitul celor 12 luni?

Aplicam functia =FV(0.5%, 12, -100, -1000, 1) obtinem 2301.

- PV (dobanda reper, plata vv, tip): calculeaza valoarea prezenta a unui flux de incasari/ plati viitoare.

Argumentele functiei au aceeasi semnificatie ca si in functia FV

Argumentul vv reprezinta valoarea viitoare, obtinuta dupa efectuarea ultimei plati/ incasari. Daca vv este omis, se considera ca este 0. De exemplu, daca vreti sa economisiti 100 000 000 lei pentru un proiect de 20 de ani, atunci 100 000 000 lei este valoarea viitoare.

Banii platiti sunt reprezentati prin numere negative, cei incasati prin numere pozitive.

Exemplu: O persoana stie ca isi poate permite sa plateasca 220 $ pe luna in urmatorii 4 ani. Dobanda curenta de piata este de 9%. Cat de mare este imprumutul pe care si-l permite persoana ?

Functia necesara pentru calcul este: =PV (0.09/12, 48, -220) care returneaza valoarea 8840.65 $.

- PMT (dobanda reper, vp, vv, tip): calculeaza suma care trebuie achitata periodic pentru un imprumut/ economie, daca se indica dobanda, numarul perioadelor de plata (reper).

Argumentele functiei au aceeasi semnificatie ca si in functiile precedente.

Pentru a determina suma totala de platit pe durata imprumutului se inmulteste valoarea returnata de functia PMT cu numarul de perioade.

Exemple:

Ce suma trebuie platita lunar pentru un imprumut de 10 000 $ cu o dobanda anuala de 8%, care trebuie achitat in 10 luni.

Formula de calcul este:

=PMT (8%/ 12, 10, 10000)

care returneaza valoarea -$ 1037.03 daca platile se fac la sfarsitul lunii, sau

=PMT

care returneaza valoarea -$ 1,030.16 daca platile se fac la inceputul lunii.

S-au obtinut valori negative pentru ca sunt plati care trebuie efectuate.

Urmatoarea formula returneaza suma pe care cineva trebuie sa o primeasca lunar, daca a imprumutat 5 000 $ cu o dobanda anuala de 12% pe o perioada de 5 luni. Formula folosita:

=PMT (12%/12, 5, -5000)

returneaza valoarea 1,030.20. S-au obtinut valori pozitive pentru ca sunt sume ce trebuie incasate.

O persoana doreste sa stranga 50 000 $ in 18 ani prin economisirea unei sume lunare constante. Dobanda annuala este de 6%.

Formula de calcul este:

=PMT (6%/ 12, 18*12, 0, 50000)

care returneaza valoarea -129.08 $.

- NPV (dobanda valoare1, valoare2, .): calculeaza valoarea prezenta actualizata a unui flux de venituri/ cheltuieli.

- IRR (valori, aproximatie): calculeaza rata interna de rentabilitate a unei proiect. Rata interna de rentabilitate este valoarea coeficientului de actualizare (dobanzii) pentru care venitul net actualizat este 0.

Functii de cautare.

Doua din cele mai utilizate functii de cautare din Excel sunt:

VLOOKUP (valoare, domeniu, index-linie, tip-cautare)

- HLOOKUP (valoare, domeniu, index-coloana tip-cautare)

Functiile VLOOKUP/ HLOOKUP cauta valoarea specificata in primul argument in prima coloana/linie din domeniul specificat in al doilea argument. Apoi functia extrage din linia/coloana corespunzatoare valorii gasite elementul indicat in coloana/linia specificata in al treilea argument- index coloana/index linie.Valorile din prima coloana/linie a domeniului trebuie sa fie ordonata crescator sau alfabetic.Argumentul tip-cautare are o valoare logica. El este optional.

Aceste functii sunt folositoare in aplicatii de calcul a impozitelor si a comisioanelor.

Exemplu: Distribuitorii unei firme sunt platii in functie de valoarea vanzarilor. Daca valoarea vanzarilor este mai mica de 5 000 000 comisionul este de 0%, intre 5 000 000 si 30 000 000 comisionul este de 4%, intre 30 000 000 si 70 000 000 comisionul este de 7%, peste 70 000 000 comisionul este de 10%.

Se va crea urmatoarea foaie de calcul :

In B2 se introduce formula =VLOOKUP (B1, A5:B8, 2). Daca in B1 se introduce valoarea 80000000, Excel cauta aceasta valoare in prima coloana din domeniul A5:B8, deci in celulele A5, A6, A7, A8, B5, B6, B7, B8. Cum aceasta valoare nu este gasita functia gaseste cea mai mare valoare care este mai mica sau egala cu valoarea cautata deci 70000000. Aceasta valoare se gaseste pe a patra linie din tabel (linia 8 din Excel). Din aceasta linie Excel returneaza valoarea gasita in coloana 2 (al treilea argument), deci 10%.

Aplicatie.

1. O echipa de muncitori este platita in functie de numarul de ore lucrate. Orele lucrate peste programul normal de lucru (8 ore) sunt platite dublu. Sa se calculeze cu cat este platit zilnic fiecare muncitor, cunoscand tariful orar si orele de intrare si iesire din tura.

Pentru rezolvarea problemei se va folosi foaia de calcul:

Pentru fiecare muncitor, se calculeaza in coloana E numarul de ore lucrate. Formula utilizata in celula E4 este =D4-C4, formula care se copieaza in domeniul E5:E8.

In coloana F se afiseaza salariul calculat pentru orele lucrate in fiecare zi. Formula din celula F4 este:

=IF(HOUR(E4)<8,HOUR(E4)*B$1,8*B$1+(HOUR(E4)-8)*2*B$1)

Aceasta formula se copieaza in domeniul F5:F8.

Daca in functia IF ar fi fost utilizata conditia E4<8, ar fi fost incorect. E4<8 este intotdeauna adevarata deoarece in E4 avem o ora si se stie ca numerele seriale atasate orelor sunt mai mici decat 1, deci si mai mici decat 8. Pentru a extrage numarul de ore lucrate s-a folosit functia HOUR.

2. Fie registrul de calcul Ferma.xls:

a)      Pentru luna februarie aflati care a fost cel mai mic si cel mai mare salariu net in lei si in dolari.

b)      Pentru luna ianuarie sa se calculeze cu ajutorul functiilor Excel numarul de angajati.

c)      Sa se calculeze pe fiecare luna suma si media aritmetica a salariului brut si a salariului net in lei si in dolari.

d)      In fata foii Ianuarie sa se insereze o foaie de calcul cu numele Centralizator.

e)      In foaia de calcul Centralizator din celula B4 sa se creeze o serie care sa cuprinda toate lunile anului.

f)        In celula C3 sa se insereze titlul Salariu net [lei] iar in celula D3 Salariu net [$]. In celula C4 sa apara valoarea sumei totale a salariului net in lei din foaia de calcul Ianuarie iar in celula D4 sa apara valoarea sumei totale a salariului net in $.