LUCRUL CU FUNCTII EXCEL

Cuprins

Utilizarea functiilor in Excel

Functii matematice

Functii logice

Functii text

Functii pentru date si ore

Functii financiare

Functii de cautare

Introducerea referintelor la alte foi de calcul

Introducerea de referinte la alte fisiere

Aplicatii

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. Functia SUM, de exemplu, aduna toate numerele dintr-un domeniu de celule.

Adresa celulelor specificate formeaza argumentul functiei:

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.

etc.

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

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

2.     Functia este introdusa folosind aplicatia Function Wizard, care se lanseaza la aplicarea comenzii Insert, Function. Pe ecran va aparea caseta de dialog Insert Function (figura 5.1).

Figura 5. 1 – Caseta de dialog 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 (figura 5.2).

In caseta de dialog a functiei alese (figura 5.2), 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.

Figura 5. 2

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

Functii matematice

Functiile matematice constituie infrastructura oricarei foi de calcul. Majoritatea functiilor stiintifice si ingineresti pot fi regasite in grupele functiilor matematice.

ABS (numar)

Functia ABS returneaza valoarea absoluta a unui numar.

Exemple: ABS (–5) va returna valoarea 5

ABS (5) va returna valoarea 5

EXP (numar)

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

Exemplu: EXP (0) va returna valoarea 1

LN (numar)

Functia LN calculeaza logaritmul natural al numarului specificat.

Exemplu: LN (1) va returna valoarea 0

INT (numar)

Functia INT 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)

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

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

MOD (32, 15) va returna valoarea 2

POWER (a, b)

Functia POWER efectueaza ridicarea unui numar a la puterea b.

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

RAND ( )

Functia 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)

Functia ROUND rotunjeste numarul specificat in primul argument la numarul de zecimale specificat in al doilea argument.

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

ROUND (753.342, 2) va returna valoarea 753.34

ROUNDUP (numar, numar de zecimale)

Functia ROUNDUP 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)

Functia ROUNDDOWN 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)

Functia SQRT extrage radacina patrata din argumentul specificat.

Exemplu: SQRT (4) va returna valoarea 2

SUM (numar1, numar2, …)

Functia SUM 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.

Un domeniu de celule este specificat prin celula din coltul stang sus al domeniului, separatorul : si celula din coltul drept jos al domeniului.

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

AVERAGE (numar1, numar2, …)

Functiile AVERAGE 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, …)

Functia COUNT 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, …)

Functia Max 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, …)

Functia MIN 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)

Functia IF 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, …)

Functia AND 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).

Functia poate avea cel mult 30 de argumente.

OR (conditia1, conditia2, …)

Functia OR 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). Functia poate avea cel mult 30 de argumente.

NOT (conditie)

Functia NOT 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)

Functiile CHAR returneaza caracterul care corespunde codului ASCII specificat ca argument.

Exemplu: CHAR (65) va returna caracterul A.

CONCATENATE (text1, text2, …)

Functia CONCATENATE efectueaza reuniunea tuturor argumentelor (cel mult 30).

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

EXACT (text1, text2)

Functia EXACT 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)

Functia FIND 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)

Functia 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)

Functia 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)

Functia LEN calculeaza numarul de caractere din textul specificat de argument.

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

MID (text, start-num, num-car)

Functia MID 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)

Functia LOWER converteste eventualele majuscule din text in litere mici.

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

PROPER (text)

Functia PROPER determina afisarea textului cu litere mici, inceputurile de cuvinte fiind scrise cu majuscule.

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

TRIM (text)

Functia TRIM 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)

Functia 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)

Functia DATE returneaza numarul serial pentru data specificata.

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

NOW

Functia 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 9/ 10/ 99 10:43, daca aceasta este data curenta.

Daca rezultatul nu apare sub forma unei date, inseamna ca este afisat numarul serial atasat. Pentru afisarea sub forma de data calendaristica celula respectiva trebuie formatata de tip data (vezi lectia Formatarea foilor de calcul).

YEAR (data calendaristica)

Functia YEAR extrage anul din data specificata.

Exemplu: YEAR( 7/3/1999”) va returna 1999.

MONTH (data calendaristica)

Functia MONTH extrage luna din data specificata.

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

DAY (data calendaristica)

Functia DAY extrage ziua din data specificata.

Exemplu: DAY( 7/3/1999”) va returna 3.

TIME (ora minut, secunda)

Functia TIME calculeaza numarul serial corespunzator numarului de ore, minute si secunde indicate.

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

HOUR (ora)

Functia HOUR returneaza numarul de ore corespunzatoar orei specificate.

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

MINUTE(ora)

Functia MINUTE returneaza numarul de minute corespunzatoare orei specificate.

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

SECOND(ora)

Functia SECOND returneaza numarul de secunde corespunzator orei specificate.

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

Functii financiare

Programul Excel pune la dispozitie si o serie de functii financiare.

FV (dobanda reper, plata vp, tip)

Functia FV 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.40 $.

PV (dobanda reper, plata vv, tip)

Functia PV 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)

Functia PMT 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:

1.     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 (8%/ 12, 10, 10000, 0, 1) 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.

2.     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.

=PMT(12%/12, 5, –5000) returneaza valoarea 1,030.20.

S-au obtinut valori pozitive pentru ca sunt sume ce trebuie incasate.

3.     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, …)

Functia NPV calculeaza valoarea prezenta actualizata a unui flux de venituri/ cheltuieli.

Daca n este numarul de argumente din sirul de valori (n nu poate fi mai mare de 29), atunci valoarea neta actualizata se calculeaza cu formula:

Valorile trebuie sa fie echidistante in timp si sa fie valori platite/ incasate la sfarsitul fiecarei perioade.

Dobanda- reprezinta dobanda anuala.

Functia NPV este asemanatoare cu PV.

Deosebirea consta in faptul ca valorile utilizate de PV trebuie sa fie constante, iar PV accepta valori fie la inceputul, fie la sfarsitul perioadei.

Exemplu: Pentru o investitie trebuie platiti 10 000 $ timp de 1 an. In urmatorii trei ani se obtin venituri anuale de 3 000 $, 4 200 $ si 6 800 $. Dobanda anuala este de 10%. Sa se calculeze valoarea neta actualizata a investitiei.

Formula de calcul este:

NPV (10%, –10 000, 3 000, 4 200, 6 800) care returneaza valoarea 1,188.44 $

Al doilea argument este negativ pentru ca reprezinta o cheltuiala.

IRR (valori, aproximatie)

Functia IRR 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.

Valori este o matrice sau un domeniu de celule care contine numerele pentru care trebuie calculata rata interna de rentabilitate. Pentru a putea calcula IRR in domeniu trebuie sa fie cel putin o valoare negativa si cel putin una pozitiva.

Aproximatie este un numar care se considera a fi cel mai apropiat de rezultatul furnizat de catre functia IRR.

Microsoft Excel foloseste un algoritm iterativ pentru calcularea valorii IRR. Incepand cu aproximatia, functia IRR verifica toate posibilitatile de calcul pana cand rezultatul este dat cu o aproximatie de 0.00001%. Daca functia IRR nu gaseste un rezultat care sa fie bun dupa 20 de incercari, se va returna valoarea de eroare #NUM!.

In cele mai multe cazuri nu este nevoie de acest argument. Daca aproximatie este omis, se va considera valoarea implicita 10%.

Daca IRR returneaza eroarea #NUM! Se va incerca din nou cu alte valori pentru aproximatie.

Exemplu: O persoana vrea sa faca o afacere. Pentru inceperea afacerii are nevoie de 70 000 $. Veniturile estimate din primii cinci ani sunt: 12 000, 15 000, 18 000, 21 000 si 26 000 de dolari.

In domeniul B1:B6 se introduc urmatoarele valori: -70 000, 12 000, 5 000, 18 000, 21 000, 26 000.

Pentru a calcula rata interna de rentabilitate a investitiei se foloseste formula:

IRR (B1:B6) care returneaza valoarea 8.66%.

Functii de cautare

Doua din cele mai utilizate functii de cautare din Excel sunt VLOOKUP si HLOOKUP.

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. Daca lipseste se considera ca are valoare TRUE (adevarata). Daca acest argument are valoare TRUE este gasita valoarea cea mai mare care este mai mica sau egala cu valoarea cautata. Daca are valoarea FALSE, este cautata valoarea exacta. Daca aceasta valoare nu este gasita in prima linie/coloana din domeniul specificat este returnata eroarea #N/A.

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 (figura 5.3):

Figura 5.3

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%.

Introducerea referintelor la alte foi de calcul

Intr-o celula se pot introduce si referinte la date din alte foi ale registrului de calcul. Pentru aceasta se introduce denumirea foii respective, un semn de exclamare si referinta la celula. De exemplu: =Sheet1!A1 inseamna ca se face referire la celula A1 din foaia Sheet1.

Daca denumirea foii de calcul contine spatii libere, numele acesteia trebuie incadrat intre apostrofuri. De exemplu: ‘Buget 2001’!A1.

Introducerea de referinte la alte fisiere

Exista situatii in care sunt necesare date care se afla in alt registru de calcul. Pentru a referi date din alt registru se introduce intaě numele registrului intre paranteze drepte, numele foii de calcul, semnul exclamarii si referinta la celula. De exemplu: formula =[vanzari.XLS]!Sheet1!A10, face referire la celula A10 din foaia de calcul Sheet1 continuta in registrul vanzari.XLS.

Aplicatii

1. Un intreprinzator vrea sa faca o investitie si face cateva estimari privind valoarea investitiei, cheltuielile anuale si veniturile anuale. Sa se determine anul in care investitia devine profitabila (anul in care venitul total depaseste cheltuielile totale).

Pentru rezolvarea problemei se va crea foaia de calcul din figura 5.4.

Figura 5. 4

Celulele B1, B2, B3 vor contine valorile pentru investitie, cheltuielile anuale si venitul anual.

Se genereaza in coloana A, incepand cu celula A6 o serie de numere incepand cu valoarea 0 si pasul seriei 1.

In anul 0 cheltuielile totale sunt cele cu investitia, iar venitul total este 0, deci in celula B6 se va introduce formula =B1, iar in celula C6 valoarea 0.

In anii urmatori la cheltuielile totale si venitul total din anul precedent se adauga cheltuielile anuale, respectiv venitul anual. Deci formulele din celulele B7 si C7 sunt =B6‡B$2, respectiv =C6‡B$3. Pentru celulele B2 si B3 s-a folosit referirea absoluta deoarece aceste celule trebuie sa ramana fixe la copiere (ele contin cheltuielile anuale si venitul anual).

Se copiaza aceste formule pe coloana.

In coloana D se calculeaza profitul total (venit total - cheltuieli totale). Se va introduce in celula D6 formula =C6-B6. Se copiaza aceasta formula pe coloana.

Pentru a determina pragul de rentabilitate (anul in care venitul total depaseste cheltuielile totale), in celula E6 se introduce formula:

=IF(D6>=0, “<

Deci daca veniturile totale sunt mai mari decat cheltuielile totale se va afisa o sageata.

Daca s-ar copia aceasta formula pe coloana sageata s-ar afisa in dreptul tuturor lunilor in care profitul este pozitiv. Pragul de rentabilitate se obtine in prima luna in care profitul este pozitiv. Deci, pentru a afisa sageata doar in dreptul acestei luni in celula D7 se va introduce formula:

=IF(AND(D7> 0,D6<0), “<

Prima luna in care profitul este pozitiv este testata verificand profitul din luna precedenta. Daca acesta este negativ, inseamna ca avem prima luna cu profit, daca este pozitiv inseamna ca si in luna precedenta s-a obtinut profit.

Se copiaza pe coloana formula din celula E7.

2. O firma vinde televizoare in rate. Sa se determine rata lunara totalul de plata si sa se construiasca tabela amortizarii in cazul in care un cumparator achizitioneaza un televizor in valoare de 5 000 000 lei. Se va considera ca a fost platit un avans de 500 000 lei si ca televizorul va fi platit in 12 rate lunare cu o dobanda de 40%. Sa se construiasca o foaie de calcul cu ajutorul careia sa se calculeze automat aceste valori. La proiectarea foii de calcul se va avea in vedere ca dobanda se poate modifica in timp.

Se va crea urmatoarea foaie de calcul (figura 5.5):

Figura 5. 5

Prima parte a foii de calcul contine datele de intrare in problema: numele cumparatorului ( B1), valoarea obiectului cumparat (B3), data de cumparare (B4) dobanda anuala (B5) si numarul de rate(B6).

In a doua parte a foii de calcul se va calcula rata lunara ce trebuie platita in ideea ca dobanda nu se va modifica. Tot aici se calculeaza totalul de plata si valoarea totala a dobanzii. Se vor introduce urmatoarele formule:

B9:= -PMT(B5/12,B6,B3-B7) (rata lunara)

B10:=B11–B3(totalul de plata - valoarea initiala)

B11:=B6*B9 +B7 (numarul de rate*rata lunara + avansul)

In a treia parte a foii de calcul se va crea un tabel care va contine pentru fiecare luna data la care trebuie platita rata, rata dobanzii in luna respectiva restul de plata valoarea dobanzii si rata lunara. Restul de plata in luna a doua este egal cu restul de plata in prima luna‡valoarea dobanzii -rata lunara.Se vor introduce urmatoarele formule:

B14:=DATE(year(B$4),MONTH(B$4)+A14,DAY(B$4))

D14:=B3-B7

D15:=D14+F14–­E14

E14:= -PMT(C14/12,B$6-A14+1,D14)

F14:=D14*C14/12

Se copiaza pe coloana formulele din celulele D15, E14, F14, A14. In domeniul A14:A25 se genereaza o serie numerica care incepe de la valoarea 1 si cu pasul seriei de 1.

3. Un agent economic isi propune sa-si dezvolte activitatea si are nevoie de un capital de 240000000 lei. Acest capital este imprumutat de la BRD cu o dobanda de 45% si trebuie restituit in 5 ani. Care este suma lunara care trebuie platita aici fiind inclusa atat dobanda compusa cat si plata imprumutului?

Se va crea urmatoarea foaie de calcul (figura 5.6):

Figura 5.6

Celulele B1:B3 contin datele de intrare in problema: valoarea imprumutului, dobanda anuala si perioada de restituire.

In celula B4 se calculeaza numarul de plati inmultind perioada de restituire cu 12. Formula din B4 va fi =12*B3.

In B6 se calculeaza rata lunara cu formula = -PMT (B2/12, B4, B1)

4. Un proiect necesita un volum de investitii de 45.000.000 lei. Durata de executie a proiectului este de doi ani, iar durata de viata economica este de 7 ani. Fluxul transelor anuale pentru investitii, cheltuielile de exploatare si incasarile sunt cele din tabelul urmator:

Sa se calculeze:

1.     Venitul net actualizat cumulat pentru o rata de actualizare de 20%

2.     Rata interna de rentabilitate a proiectului.

Figura 5. 7

Se calculeaza pentru fiecare an fluxul de numerar scazand din incasari cheltuielile de exploatare si cheltuielile cu investitii (figura 5.7). In celula B7 se introduce formula =B6–B5–B4. Se copiaza formula la domeniul C7:J7.

In celula C9 se calculeaza venitul net actualizat cumulat cu formula =NPV(B1,B7:J7).

In celula C10 se calculeaza rata interna de rentabilitate cu formula =IRR(B7:J7).

5. Un intreprinzator care face o investitie intr-un obiectiv economic castiga un venit net anual de 200000 lei, timp de 12 ani cat este durata de functionare a obiectivului. Care este valoarea prezenta a acestui flux de venituri in momentul investitiei, la o valoare de discontare de 14%.

Se va crea urmatoarea foaie de calcul (figura 5.8):

Figura 5. 8

In celula B5 se va introduce formula: = PV(B3, B2, B1), unde B3 reprezinta dobanda, B2 perioada in care se obtin veniturile, B1 valoarea venitului anual.

6. 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 din figura 5.9.

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+MINUTE(E4)*B$1/60,8*B$1+(HOUR(E4)-8)*2*B$1+ MINUTE(E4)*2*B$1/60)

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.

Figura 5.9

7. Un registru Excel este alcatuit din doua foi de calcul. O foaie de calcul – cursuri - contine cursurile de schimb pentru mai multe valute, cealalta foaie – casa – este folosita pentru a calcula echivalentul in lei al sumelor schimbate.

Foaia de calcul cursuri are structura din figura 5.10.

Figura 5.10

Foaia de calcul casa are structura din figura 5.11.

Figura 5.11

In coloana Nume se introduce numele persoanei care realizeaza o tranzactie, iar in coloana Tranzactie se introduce tipul tranzactiei efectuate. Valorile permise in aceasta coloana sunt C – pentru cumparare si V – pentru vanzare.

In coloana Tip valuta se introduce numele monedei schimbate (atentie, numele monedei trebuie sa fie identic cu cel din foaia de calcul cursuri).

In coloana Suma schimbata se introduce suma care se schimba.

In coloana Echivalent lei se calculeaza echivalentul in lei al sumei schimbate (in functie de valuta suma schimbata si tipul tranzactiei). Formula utilizata in celula E2 este:

=D2*VLOOKUP(C2, cursuri!A$2:C$9, IF(B2=”C”,2,3))

Se copieaza formula pe coloana si pe masura ce se introduc date in foaia de calcul, echivalentul in lei al sumei schimbate se va calcula automat.