Excel 5

Introducerea şi editarea datelor

CAPITOLUL 5 INTRODUCEREA ŞI EDITAREA DATELOR 5.1. Utilizarea funcţiilor în Excel Excel ofer[ peste 200 de func\ii (formule predefinite), care permit crearea unor formule complexe pentru o mare diversitate de aplica\ii: =tiin\ifice, inginere=ti, de afaceri etc. O func\ie este definit[ de numele =i argumentele ei. Argumentele unei func\ii se introduc ]ntre paranteze. }n cazul ]n care se folosesc mai multe argumente, acestea se separ[ prin virgul[. Func\ia SUM, de exemplu, adun[ toate numerele dintr-un domeniu de celule. Adresa celulelor specificate formeaz[ argumentul func\iei: =SUM(A1:B2) argumentul func\iei numele func\iei Dac[ o func\ie nu are nici un argument, se scriu totu=i parantezele, numai c[ ]ntre ele nu se va mai nota nimic. De asemenea, func\iile pot con\ine at`t argumente obligatorii c`t =i argumente op\ionale. Cel mai folosit tip de argument este cel numeric, dar argumentele pot fi =i de tip text, dat[, or[ sau matrice. Dac[ un text este folosit ca argument ]ntr-o func\ie, el trebuie introdus ]ntre ghilimele. Datorit[ num[rului mare de func\ii ]ncorporate ]n Excel acestea au fost grupate ]n mai multe categorii: - Func\ii matematice - Func\ii financiare - Func\ii logice - Func\ii de c[utare - Func\ii de lucru cu texte - Func\ii pentru lucrul cu date =i ore. - Func\ii statistice, etc. etc. Pentru a introduce o func\ie ]n Excel se poate utiliza una din urm[toarele metode: 1. Func\ia este scris[ de utilizator. }n acest caz se presupune c[ utilizatorul =tie sintaxa func\iei.

Modelarea deciziilor utilizând foile de calcul

Figura 5. 1 – caseta de dialog Paste Function 2. Func\ia este introdus[ folosind aplica\ia Function Wizard, care se lanseaz[ la aplicarea comenzii Insert, Function. Pe ecran va ap[rea caseta de dialog Paste Function (figura 5.1). }n lista Function Category sunt afi=ate categoriile de func\ii ]ncorporate ]n Excel, iar ]n lista Function Name sunt trecute ]n ordine alfabetic[ func\iile existente pentru categoria selectat[. Dup[ selectarea unei func\ii, se aplic[ un clic pe butonul OK pentru a trece la urm[toarea caset[ de dialog (figura 5.2).

Figura 5. 2 }n caseta de dialog a func\iei alese (figura 5.2), trebuie introduse argumentele necesare pentru func\ia respectiv[. Casetele text pentru argumente trebuie s[ con\in[ valori sau referin\e de celule. Func\ia se termin[ de introdus select`nd butonul OK. }n continuare vor fi prezentate func\iile Excel ]nt`lnite mai frecvent, grupate pe categorii.

5.2. Funcţii matematice Func\iile matematice constituie infrastructura oric[rei foi de calcul. Majoritatea func\iilor =tiin\ifice =i inginere=ti pot fi reg[site ]n grupele func\iilor matematice.

Introducerea şi editarea datelor ABS (num[r) Func\ia ABS returneaz[ valoarea absolut[ a unui num[r. Exemple: ABS (–5) va returna valoarea 5 ABS (5) va returna valoarea 5 EXP (num[r) Func\ia EXP calculeaz[ exponen\iala unui num[r (e ridicat la puterea specificat[ de argumentul num[r). Exemplu: EXP (0) va returna valoarea 1 LN (num[r) Func\ia LN calculeaz[ logaritmul natural al num[rului specificat. Exemplu: LN (1) va returna valoarea 0 INT (num[r) Func\ia INT rotunje=te un num[r p`n[ la cea mai apropiat[ valoare ]ntreag[. Exemple: INT (7.6) va returna valoarea 7 INT (–7.6) va returna valoarea 8 MOD (a, b) Func\ia MOD calculeaz[ restul (modulul) lui a ]mp[r\it la b. Dac[ b este 0, se va afi=a valoarea de eroare #DIV/0. Exemplu: MOD (7, 6) va returna valoarea 1 MOD (32, 15) va returna valoarea 2 POWER (a, b) Func\ia POWER efectueaz[ ridicarea unui num[r a la puterea b. Exemplu: POWER (2, 2) va returna valoarea 4 RAND ( ) Func\ia RAND furnizeaz[ un num[r aleator ]ntre 0 =i 1. Func\ia nu accept[ argumente. Ap[sarea tastei F9 va produce generarea altor numere. ROUND (num[r, num[r de zecimale) Func\ia ROUND rotunje=te num[rul specificat ]n primul argument la num[rul de zecimale specificat ]n al doilea argument. Exemplu: ROUND (753.345, 2) va returna valoarea 753.35 ROUND (753.342, 2) va returna valoarea 753.34 ROUNDUP (num[r, num[r de zecimale) Func\ia ROUNDUP rotunje=te ]n sus num[rul specificat ]n primul argument, cu num[rul de zecimale specificat ]n al doilea argument. Exemplu: ROUNDUP (7.49, 1) va returna valoarea 7.5 ROUNDDOWN (num[r, num[r de zecimale) Func\ia ROUNDDOWN rotunje=te ]n jos num[rul specificat ]n primul argument, cu num[rul de zecimale specificat ]n al doilea argument. Exemplu: ROUNDDOWN (7.49, 1) va returna valoarea 7.4 SQRT (num[r) Func\ia SQRT extrage r[d[cina p[trat[ din argumentul specificat.

Modelarea deciziilor utilizând foile de calcul Exemplu: SQRT (4) va returna valoarea 2 SUM (num[r1, num[r2, …) Func\ia SUM calculeaz[ suma tuturor argumentelor. Argumentele pot fi valori, celule individuale sau domenii de celule, dar num[rul lor este limitat la 30. Argumentele numerice sunt ignorate. Un domeniu de celule este specificat prin celula din col\ul st`ng sus al domeniului, separatorul : =i celula din col\ul drept jos al domeniului. Exemplu: SUM (A1:B3) va calcula suma valorilor din celulele A1, A2, A3,B1,B2, B3 AVERAGE (num[r1, num[r2, …) Func\iile AVERAGE calculeaz[ media aritmetic[ a tuturor argumentelor. Argumentele pot fi valori, celule sau domenii de celule, dar num[rul lor este limitat la 30. Argumentele nenumerice sunt ignorate. Exemplu: AVERAGE (A1:B3) va calcula media aritmetic[ a valorilor din celulele A1, A2, A3, B1, B2, B3. COUNT (num[r1, num[r2, …) Func\ia COUNT num[r[ ]n argumentele specificate celulele care con\in numere. Func\ia poate avea ]ntre 1 =i 30 de argumente. Exemplu: COUNT (A2:A5) va returna valoarea 3 atunci c`nd domeniul A2:A4 con\ine numerele 2,3,4, iar celula A5 este goal[. MAX (num[r1, num[r2, …) Func\ia MAX returneaz[ valoarea celui mai mare argument. Func\ia 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, dac[ numerele din acest domeniu sunt: 1,10, 7, 4. MIN (num[r1, num[r2, …) Func\ia MIN returneaz[ valoarea celui mai mic argument. Func\ia 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, dac[ numerele din acest domeniu sunt: 1,10, 7, 4. IF (condi\ie, valoare adev[rat[, valoare fals[) Func\ia IF evalueaz[ o condi\ie. Dac[ condi\ia este adev[rat[, func\ia va returna al doilea aergument- valoarea adev[rat[. Dac[ condi\ia este fals[, func\ia va returna al treilea argument - valoarea fals[. Exemplu: IF (A1
5.3. Funcţii logice Func\iile logice sunt folosite ]n cazurile ]n care trebuie evaluate mai multe condi\ii. }n general, aceste func\ii nu se folosesc singure, ele apar ca argumente la alte func\ii (de exemplu ]n func\ia IF).

Introducerea şi editarea datelor

AND (condi\ia1, condi\ia2, …) Func\ia AND returneaz[ valoarea adev[rat[ (TRUE) dac[ toate condi\iile specificate ]n argumente sunt adev[rate. Dac[ cel pu\in o condi\ie nu este adev[rat[, func\ia AND va returna valoarea fals (FALSE). Func\ia poate avea cel mult 30 de argumente. OR (condi\ia1, condi\ia2, …) Func\ia OR returneaz[ valoarea adev[rat[ (TRUE) dac[ cel pu\in o condi\ie din cele specificate ]n argumente este adev[rat[. Dac[ nici o condi\ie nu este adev[rat[, func\ia OR va returna valoarea fals (FALSE). Func\ia poate avea cel mult 30 de argumente. NOT (condi\ie) Func\ia NOT returneaz[ valoarea adev[rat[ dac[ condi\ia este fals[ =i dac[ condi\ia este adev[rat[.

5.4. Funcţii text Func\iile text permit manipularea informa\iilor de tip text. Datele din foile de calcul pot fi concatenate pentru a alc[tui titluri, propozi\ii, etichete. CHAR (num[r) Func\iile CHAR returneaz[ caracterul care corespunde codului ASCII specificat ca argument. Exemplu: CHAR (65) va returna caracterul A. CONCATENATE (text1, text2, …) Func\ia CONCATENATE efectueaz[ reuniunea tuturor argumentelor (cel mult 30). Exemplu: CONCATENATE ("Microsoft", "Excel") va returna textul Microsoft Excel. EXACT (text1, text2) Func\ia EXACT compar[ textele text1 =i text2. Dac[ acestea sunt identice func\ia va returna valoarea adev[rat[ (TRUE), astfel se va re\ine valoarea logic[ FALSE. Func\ia face distinc\ie ]ntre literele mici =i mari. FIND (text-c[utat, surs[, start-num) Func\ia FIND caut[ primul argument, text-c[utat ]n textul din al doilea argument surs[, ]ncep`nd cu pozi\ia specificat[ de start-num. }n cazul ]n care acesta este g[sit, func\ia FIND returneaz[ pozi\ia de ]nceput a textului c[utat. Dac[ argumentul startnum este ]n afara limitelor sau dac[ nu este g[sit[ o valoare, se va afi=a codul de eroare #VALUE. Dac[ argumentul start-num nu este specificat, se presupune c[ acesta are valoarea 1. Exemplu: FIND (B12, "ABCDE", 1) va returna valoarea 3 dac[ celula B12 con\ine caracterul C. LEFT (text, num-car)

Modelarea deciziilor utilizând foile de calcul Func\ia afi=eaz[ primele num-car caractere din partea st`ng[ a unui text. Argumentul num-car trebuie s[ fie mai mare ca 0. Dac[ se omite introducerea sa se va presupune c[ este egal cu 1. Exemplu: LEFT (A1, 5) va returna valoarea Micro dac[ ]n celula A1 se g[se=te textul Microsoft. RIGHT (text, num-car) Func\ia afi=eaz[ primele num-car caractere din partea dreapt[ a unui text. Argumentul num-car trebuie s[ fie mai mare ca 0. Dac[ se omite introducerea sa se va presupune c[ este egal cu 1. Exemplu: RIGHT (A1, 4) va returna valoarea soft dac[ ]n celula A1 se g[se=te textul Microsoft. LEN (text) Func\ia LEN calculeaz[ num[rul de caractere din textul specificat de argument. Exemplu: LEN ("Microsoft") va returna valoarea 9. MID (text, start-num, num-car) Func\ia MID extrage un num[r de num-car caractere din text, ]ncep`nd cu pozi\ia start-num. Exemplu: MID ("Microsoft Excel 7.0", 11, 5) va returna textul Excel. LOWER (text) Func\ia LOWER converte=te eventualele majuscule din text ]n litere mici. Exemplu: LOWER ("Microsoft Excel") va returna microsoft excel PROPER (text) Func\ia PROPER determin[ afi=area textului cu litere mici, ]nceputurile de cuvinte fiind scrise cu majuscule. Exemplu: PROPER ("MICROSOFT EXCEL") va returna Microsoft Excel. TRIM (text) Func\ia TRIM =terge toate blank-urile din text, astfel ]nc`t ]ntre cuvinte s[ r[m`n[ un singur spa\iu. Exemplu: TRIM ("Microsoft Excel") va returna Microsoft Excel. TEXT (valoare, format-text) Func\ia TEXT converte=te o valoare numeric[ ]n text =i o afi=eaz[ corespunz[tor formatului indicat prin al doilea argument. Rezultatul apare afi=at ca un num[r formatat, dar ]n realitate este de tip text. Se pot utiliza oricare din formatele numerice predefinite sau personalizate, prezentate ]n lec\ia “Formatarea foilor de calcul”. Exemplu: TEXT (457989, "$#, ##0.00") va returna $4,579.89

5.5. Funcţii pentru date şi ore Programul Excel ata=eaz[ fiec[rei date calendaristice =i ore c`te un num[r serial. Numerele seriale ata=ate datelor calendaristice sunt mai mari ca 1, cele ata=ate orelor sunt subunitare. C`nd efectueaz[ calcule cu date =i ore, Excel folose=te aceste numere seriale, numai formatul de afi=are este de tip dat[ sau or[. Cele mai folosite func\ii de lucru cu date =i ore sunt:

Introducerea şi editarea datelor

DATE (an, lun[, zi) Func\ia DATE returneaz[ num[rul serial pentru data specificat[. Exemplu: DATE (1900, 1, 1) va returna 1 (num[rul serial al datei 1.1.1900) NOW ( ) Func\ia NOW calculeaz[ num[rul serial al datei =i al orei extrase din ceasul intern al calculatorului. Excel actualizeaz[ data =i ora doar la deschiderea sau recalcularea foii. Aceast[ func\ie nu are argumente, ]ns[ este necesar[ introducerea parantezelor. Exemplu: NOW ( ) va returna 9/ 10/ 99 10:43, dac[ aceasta este data curent[. Dac[ rezultatul nu apare sub forma unei date, ]nseamn[ c[ este afi=at num[rul serial ata=at. Pentru afi=area sub form[ de dat[ calendaristic[, celula respectiv[ trebuie formatat[ de tip dat[ (vezi lec\ia Formatarea foilor de calcul). YEAR (dat[ calendaristic[) Func\ia YEAR extrage anul din data specificat[. Exemplu: YEAR ( 7/ 3/ 1999) va returna 1999. MONTH (dat[ calendaristic[) Func\ia MONTH extrage luna din data specificat[. Exemplu: MONTH ( 7/ 3/ 1999) va returna 7 (se consider[ c[ data este introdus[ ]n formatul lun[/ zi/ an) DAY (dat[ calendaristic[) Func\ia DAY extrage ziua din data specificat[. Exemplu: DAY (7/ 3/ 1999) va returna 3. TIME (or[, minut, secund[) Func\ia TIME calculeaz[ num[rul serial corespunz[tor num[rului de ore, minute =i secunde indicate. Exemplu: TIME (18, 4, 19) furnizeaz[ valoarea 0,752998. HOUR (or[) Func\ia HOUR returneaz[ num[rul de ore corespunz[toar orei specificate. Exemplu: HOUR (19:10:30) va returna valoarea 19. MINUTE (or[) Func\ia MINUTE returneaz[ num[rul de minute corespunz[toare orei specificate. Exemplu: MINUTE (19:10:30) va returna valoarea 10. SECOND (or[) Func\ia SECOND returneaz[ num[rul de secunde corespunz[tor orei specificate. Exemplu: SECOND (19:10:30) va returna valoarea 30.

5.6. Funcţii financiare Programul Excel pune la dispozi\ie =i o serie de func\ii financiare. FV (dob`nd[, reper, plat[, vp, tip)

Modelarea deciziilor utilizând foile de calcul Func\ia FV calculeaz[ valoarea viitoare pentru o serie de ]ncas[ri/ pl[\i egale (specificate ]n argumentul plat[), f[cute ]ntr-un num[r de perioade reper, cu o anumit[ dob`nd[ (primul argument). Dob`nda trebuie s[ aib[ aceea=i unitate de m[sur[ ca reper. De exemplu, dob`nda anual[ trebuie s[ se ]mpart[ la 12 dac[ ]ncas[rile/ pl[\ile se fac lunar. Num[rul vp reprezint[ valoarea prezent[ sau suma care se investe=te/ ]mprumut[ in momentul ini\ial. Dac[ vp este omis se consider[ c[ este 0. Tip poate lua valoarea 0 sau 1. Dac[ are valoarea 0 se consider[ c[ pl[\ile se fac la sf`r=itul perioadei, dac[ are valoarea 1, pl[\ile se fac la ]nceputul perioadei. Dac[ argumentul tip este omis se consider[ c[ are valoarea 0. Banii care sunt pl[ti\i sunt reprezenta\i prin numere negative, iar cei ]ncasa\i sunt reprezenta\i prin numere pozitive. Exemplu: S[ presupunem c[ o persoan[ vrea s[ investeasc[ bani pentru un proiect care va fi realizat peste 1 an. De aceea, depune 1 000 $ ]ntr-un cont de economii cu o dob`nd[ de 6% pe an (dob`nda lunar[ va fi 6%/ 12, adic[ 0.5%). De asemenea, s[ presupunem c[ persoana respectiv[ va depune c`te 100 $ la ]nceputul fiec[rei luni, ]n urm[toarele 12 luni. C`\i dolari vor fi ]n cont la sf`r=itul celor 12 luni? Aplic[m func\ia =FV(0.5%, 12, –100, –1000, 1) ob\inem 2301.40 $. PV (dob`nd[, reper, plat[, vv, tip) Func\ia PV calculeaz[ valoarea prezent[ a unui flux de ]ncas[ri/ pl[\i viitoare. Argumentele func\iei au aceea=i semnifica\ie ca =i ]n func\ia FV. Argumentul vv reprezint[ valoarea viitoare, ob\inut[ dup[ efectuarea ultimei pl[\i/ ]ncas[ri. Dac[ vv este omis, se consider[ c[ este 0. De exemplu, dac[ vre\i s[ economisi\i 100 000 000 lei pentru un proiect de 20 de ani, atunci 100 000 000 lei este valoarea viitoare. Banii pl[ti\i sunt reprezenta\i prin numere negative, cei ]ncasa\i prin numere pozitive. Exemplu: O persoan[ =tie c[ ]=i poate permite s[ pl[teasc[ 220 $ pe lun[ ]n urm[torii 4 ani. Dob`nda curent[ de pia\[ este de 9%. C`t de mare este ]mprumutul pe care =i-l permite persoana ? Func\ia necesar[ pentru calcul este: =PV (0.09/12, 48, –220) care returneaz[ valoarea 8840.65 $. PMT (dob`nd[, reper, vp, vv, tip) Func\ia PMT calculeaz[ suma care trebuie achitat[ periodic pentru un ]mprumut/ economie, dac[ se indic[ dob`nda, num[rul perioadelor de plat[ (reper). Argumentele func\iei au aceea=i semnifica\ie ca =i ]n func\iile precedente. Pentru a determina suma total[ de pl[tit pe durata ]mprumutului se ]nmul\e=te valoarea returnat[ de func\ia PMT cu num[rul de perioade. Exemple: 1. Ce sum[ trebuie pl[tit[ lunar pentru un ]mprumut de 10 000 $ cu o dob`nd[ anual[ de 8%, care trebuie achitat ]n 10 luni. Formula de calcul este: ˆPMT (8%/ 12, 10, 10000) care returneaz[ valoarea –$ 1037.03 dac[ pl[\ile se fac la sf`r=itul lunii. sau ˆPMT (8%/ 12, 10, 10000, 0, 1) care returneaz[ valoarea –$ 1,030.16 dac[ pl[\ile se fac la ]nceputul lunii. S-au ob\inut valori negative pentru c[ sunt pl[\i care trebuie efectuate. 2. Urm[toarea formul[ returneaz[ suma pe care cineva trebuie s[ o primeasc[ lunar, dac[ a ]mprumutat 5 000 $ cu o dob`nd[ anual[ de 12% pe o perioad[ de 5 luni.

Introducerea şi editarea datelor ˆPMT (12%/12, 5, –5000) returneaz[ valoarea 1,030.20. S-au ob\inut valori pozitive pentru c[ sunt sume ce trebuie ]ncasate. 3. O persoan[ dore=te s[ str`ng[ 50 000 $ ]n 18 ani prin economisirea unei sume lunare constante. Dob`nda annual[ este de 6%. Formula de calcul este: ˆPMT (6%/ 12, 18*12, 0, 50000) care returneaz[ valoarea –129.08 $. NPV (dob`nd[, valoare1, valoare2, …) Func\ia NPV calculeaz[ valoarea prezent[ actualizat[ a unui flux de venituri/ cheltuieli. Dac[ n este num[rul de argumente din =irul de valori ( n nu poate fi mai mare de 29), atunci valoarea net[ actualizat[ se calculeaz[ cu formula: n

NPV = ∑ i =1

Vi (1 + dobanda) i

Valorile trebuie s[ fie echidistante ]n timp =i s[ fie valori pl[tite/ ]ncasate la sf`r=itul fiec[rei perioade. Dob`nda- reprezint[ dob`nda anual[. Func\ia NPV este asem[n[toare cu PV. Deosebirea const[ ]n faptul c[ valorile utilizate de PV trebuie s[ fie constante, iar PV accept[ valori fie la ]nceputul, fie la sf`r=itul perioadei. Exemplu: Pentru o investi\ie trebuie pl[ti\i 10 000 $ timp de 1 an. }n urm[torii trei ani se ob\in venituri anuale de 3 000 $, 4 200 $ =i 6 800 $. Dob`nda anual[ este de 10%. S[ se calculeze valoarea net[ actualizat[ a investi\iei. Formula de calcul este: ˆNPV (10%, –10 000, 3 000, 4 200, 6 800) care returneaz[ valoarea 1,188.44 $ Al doilea argument este negativ pentru c[ reprezint[ o cheltuial[. IRR (valori, aproxima\ie) Func\ia IRR calculeaz[ rata intern[ de rentabilitate a unei proiect. Rata intern[ de rentabilitate este valoarea coeficientului de actualizare (dob`nzii) pentru care venitul net actualizat este 0. Valori este o matrice sau un domeniu de celule care con\ine numerele pentru care trebuie calculat[ rata intern[ de rentabilitate. Pentru a putea calcula IRR ]n domeniu trebuie s[ fie cel pu\in o valoare negativ[ =i cel pu\in una pozitiv[. Aproxima\ie este un num[r care se consider[ a fi cel mai apropiat de rezultatul furnizat de c[tre func\ia IRR. Microsoft Excel folose=te un algoritm iterativ pentru calcularea valorii IRR. }ncep`nd cu aproxima\ia, func\ia IRR verific[ toate posibilit[\ile de calcul p`n[ c`nd rezultatul este dat cu o aproxima\ie de 0.00001%. Dac[ func\ia IRR nu g[se=te un rezultat care s[ fie bun dup[ 20 de ]ncerc[ri, se va returna valoarea de eroare #NUM!. }n cele mai multe cazuri nu este nevoie de acest argument. Dac[ aproxima\ie este omis, se va considera valoarea implicit[ 10%. Dac[ IRR returneaz[ eroarea #NUM! Se va ]ncerca din nou cu alte valori pentru aproxima\ie. Exemplu: O persoan[ vrea s[ fac[ o afacere. Pentru ]nceperea afacerii are nevoie de 70 000 $. Veniturile estimate din primii cinci ani sunt: 12 000, 15 000, 18 000, 21 000 =i 26 000 de dolari.

Modelarea deciziilor utilizând foile de calcul }n domeniul B1:B6 se introduc urm[toarele valori: -70 000, 12 000, 5 000, 18 000, 21 000, 26 000. Pentru a calcula rata intern[ de rentabilitate a investi\iei se folose=te formula: ˆIRR (B1:B6) care returneaz[ valoarea 8.66%.

5.7. Funcţii de căutare Dou[ din cele mai utilizate func\ii de c[utare din Excel sunt VLOOKUP =i HLOOKUP. VLOOKUP (valoare, domeniu, index-linie, tip-c[utare) HLOOKUP (valoare, domeniu, index-coloan[, tip-c[utare) Func\iile VLOOKUP/ HLOOKUP caut[ valoarea specificat[ ]n primul argument ]n prima linie/ coloan[ din domeniul specificat ]n al doilea argument. Apoi func\ia extrage din coloana/linia corespunz[toare valorii g[site elementul indicat ]n linia/ coloana specificat[ ]n al treilea argument- index linie/index coloan[. Valorile din prima linie/ coloan[ a domeniului trebuie s[ fie ordonata cresc[tor sau alfabetic. Argumentul tip-c[utare are o valoare logic[. El este op\ional. Dac[ lipse=te se consider[ c[ are valoare TRUE (adev[rat[). Dac[ acest argument are valoare TRUE este g[sit[ valoarea cea mai mare care este mai mic[ sau egal[ cu valoarea c[utat[. Dac[ are valoarea FALSE, este c[utat[ valoarea exact[. Dac[ aceast[ valoare nu este g[sit[ ]n prima linie/coloan[ din domeniul specificat este returnat[ eroarea #N/A. Aceste func\ii sunt folositoare ]n aplica\ii de calcul a impozitelor =i a comisioanelor. Exemplu: Distribuitorii unei firme sunt pl[\ii ]n func\ie de valoarea v`nz[rilor. Dac[ valoarea v`nz[rilor este mai mic[ de 5 000 000 comisionul este de 0%, ]ntre 5 000 000 =i 30 000 000 comisionul este de 4%, ]ntre 30 000 000 =i 70 000 000 comisionul este de 7%, peste 70 000 000 comisionul este de 10%. Se va crea urm[toarea foaie de calcul (figura 5.3):

Figura 5.3 }n B2 se introduce formula ˆVLOOKUP (B1, A5:B8, 2). Dac[ ]n B1 se introduce valoarea 80000000, Excel caut[ aceast[ valoare ]n prima coloan[ din domeniul A5:B8, deci ]n celulele A5, A6, A7, A8, B5, B6, B7, B8. Cum aceast[ valoare nu este g[sit[, func\ia g[se=te cea mai mare valoare care este mai mic[ sau egal[ cu valoarea c[utat[, deci 70000000. Aceast[ valoare se g[se=te pe a patra linie din tabel (linia 8 din Excel). Din aceast[ linie Excel returneaz[ valoarea g[sit[ ]n coloana 2 (al treilea argument), deci 10%.

Introducerea şi editarea datelor

5.8. Introducerea referinţelor la alte foi de calcul }ntr-o celul[ se pot introduce =i referin\e la date din alte foi ale registrului de calcul. Pentru aceasta se introduce denumirea foii respective, un semn de exclamare =i referin\a la celul[. De exemplu: ˆSheet1!A1 ]nseamn[ c[ se face referire la celula A1 din foaia Sheet1. Dac[ denumirea foii de calcul con\ine spa\ii libere, numele acesteia trebuie ]ncadrat ]ntre ghilimele. De exemplu: “Buget 2001”!A1.

5.9. Introducerea de referinţe la alte fişiere Exist[ situa\ii ]n care sunt necesare date care se afl[ ]n alt registru de calcul. Pentru a referi date din alt registru se introduce ]nt`ì numele registrului ]ntre paranteze drepte, numele foii de calcul, semnul exclam[rii =i referin\a la celul[. De exemplu: formula ˆ[vanzari.XLS]Sheet1!A10, face referire la celula A10 din foaia de calcul Sheet1 con\inut[ ]n registrul vanz[ri.XLS.

5.10. Aplicaţii 1. Un ]ntreprinz[tor vrea s[ fac[ o investi\ie =i face c`teva estim[ri privind valoarea investi\iei, cheltuielile anuale =i veniturile anuale. S[ se determine anul ]n care investi\ia devine profitabil[ (anul ]n care venitul total dep[=e=te cheltuielile totale). Pentru rezolvarea problemei se va crea foaia de calcul din figura 5.4. Celulele B1, B2, B3 vor con\ine valorile pentru investi\ie, cheltuielile anuale =i venitul anual. Se genereaz[ ]n coloana A, ]ncep`nd cu celula A6 o serie de numere ]ncep`nd cu valoarea 0 =i pasul seriei 1. }n anul 0 cheltuielile totale sunt cele cu investi\ia, iar venitul total este 0, deci ]n celula B6 se va introduce formula ˆB1, iar ]n celula C6 valoarea 0. }n anii urm[tori la cheltuielile totale =i venitul total din anul precedent se adaug[ cheltuielile anuale, respectiv venitul anual. Deci formulele din celulele B7 =i C7 sunt ˆB6‡B$2, respectiv ˆC6‡B$3. Pentru celulele B2 =i B3 s-a folosit referirea absolut[ deoarece aceste celule trebuie s[ r[m`n[ fixe la copiere (ele con\in cheltuielile anuale =i venitul anual). Se copiaz[ aceste formule pe coloan[.

Modelarea deciziilor utilizând foile de calcul

Figura 5. 4 }n coloana D se calculeaz[ profitul total (venit total - cheltuieli totale). Se va introduce ]n celula D6 formula ˆC6-B6. Se copiaz[ aceast[ formul[ pe coloan[. Pentru a determina pragul de rentabilitate (anul ]n care venitul total dep[=e=te cheltuielile totale), ]n celula E6 se introduce formula: ˆif (D6 rel="nofollow">ˆ0, “<ˆ”, “”). Deci dac[ veniturile totale sunt mai mari dec`t cheltuielile totale se va afi=a o s[geat[. Dac[ s-ar copia aceast[ formul[ pe coloan[, s[geata s-ar afi=a ]n dreptul tuturor lunilor ]n care profitul este pozitiv. Pragul de rentabilitate se ob\ine ]n prima lun[ ]n care profitul este pozitiv. Deci, pentru a afi=a s[geata doar ]n dreptul acestei luni ]n celula D7 se va introduce formula: ˆif (AND(D7>ˆ0,D6<0), “<ˆ”, “”) Prima lun[ ]n care profitul este pozitiv este testat[ verific`nd profitul din luna precedent[. Dac[ acesta este negativ, ]nseamn[ c[ avem prima lun[ cu profit, dac[ este pozitiv ]nseamn[ c[ =i ]n luna precedent[ s-a ob\inut profit. Se copiaz[ pe coloan[ formula din celula E7. 2. O firm[ vinde televizoare ]n rate. S[ se determine rata lunar[, totalul de plat[ =i s[ se construiasc[ tabela amortiz[rii ]n cazul ]n care un cump[r[tor achizi\ioneaz[ un televizor ]n valoare de 5 000 000 lei. Se va considera c[ a fost pl[tit un avans de 500 000 lei =i c[ televizorul va fi pl[tit ]n 12 rate lunare cu o dob`nd[ de 40%. S[ se construiasc[ o foaie de calcul cu ajutorul c[reia s[ se calculeze automat aceste valori. La proiectarea foii de calcul se va avea ]n vedere c[ dob`nda se poate modifica ]n timp. Se va crea urm[toarea foaie de calcul (figura 5.5):

Introducerea şi editarea datelor

Figura 5. 5 Prima parte a foii de calcul con\ine datele de intrare ]n problem[: numele cump[r[torului ( B1), valoarea obiectului cump[rat (B3), data de cump[rare (B4) dob`nda anual[ (B5) =i num[rul de rate(B6). }n a doua parte a foii de calcul se va calcula rata lunar[ ce trebuie pl[tit[ ]n ideea c[ dob`nda nu se va modifica. Tot aici se calculeaz[ totalul de plat[ =i valoarea total[ a dob`nzii. Se vor introduce urm[toarele formule: B9:ˆ -PMT(B5/12,B6,B3-B7) (rata lunar[) B10:ˆB11–B3 (totalul de plat[ - valoarea ini\ial[) B11:ˆB6*B9 ‡B7 (num[rul de rate*rata lunar[ ‡ avansul) }n a treia parte a foii de calcul se va crea un tabel care va con\ine pentru fiecare lun[ data la care trebuie pl[tit[ rata, rata dob`nzii ]n luna respectiv[, restul de plat[, valoarea dob`nzii =i rata lunar[. Restul de plat[ ]n luna a doua este egal cu restul de plat[ ]n prima lun[‡valoarea dob`nzii -rata lunar[.Se vor introduce urm[toarele 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 copiaz[ pe coloan[ formulele din celulele D15, E14, F14, A14. }n domeniul A14:A25 se genereaz[ o serie numeric[ care ]ncepe de la valoarea 1 =i cu pasul seriei de 1. 3. Un agent economic ]=i propune s[-=i dezvolte activitatea =i are nevoie de un capital de 240000000 lei. Acest capital este ]mprumutat de la BRD cu o dob`nd[ de 45% =i trebuie restituit ]n 5 ani. Care este suma lunar[ care trebuie pl[tit[, aici fiind inclus[ at`t dob`nda compus[ c`t =i plata ]mprumutului? Se va crea urm[toarea foaie de calcul (figura 5.6):

Modelarea deciziilor utilizând foile de calcul

Figura 5.6 Celulele B1:B3 con\in datele de intrare ]n problem[: valoarea ]mprumutului, dob`nda anual[ =i perioada de restituire. }n celula B4 se calculeaz[ num[rul de pl[\i ]nmul\ind perioada de restituire cu 12. Formula din B4 va fi ˆ12*B3. }n B6 se calculeaz[ rata lunar[ cu formula ˆ -PMT (B2/12, B4, B1) 4. Un proiect necesit[ un volum de investi\ii de 45.000.000 lei. Durata de execu\ie a proiectului este de doi ani, iar durata de via\[ economic[ este de 7 ani. Fluxul tran=elor anuale pentru investi\ii, cheltuielile de exploatare =i ]ncas[rile sunt cele din tabelul urm[tor: Anul Investi\ii Cheltuieli de exploatare }ncas[ri

1 15 -

2 30 -

3

4

5

6

7

8

9

20 40

25 45

25 45

25 47

24 47

24 48

22

S[ se calculeze: 1. Venitul net actualizat cumulat pentru o rat[ de actualizare de 20% 2. Rata intern[ de rentabilitate a proiectului.

Figura 5. 7 Se calculeaz[ pentru fiecare an fluxul de numerar sc[z`nd din ]ncas[ri cheltuielile de exploatare =i cheltuielile cu investi\ii (figura 5.7). }n celula B7 se introduce formula ˆB6– B5–B4. Se copiaz[ formula la domeniul C7:J7. }n celula C9 se calculeaz[ venitul net actualizat cumulat cu formula ˆNPV(B1,B7:J7). }n celula C10 se calculeaz[ rata intern[ de rentabilitate cu formula ˆIRR(B7:J7).

Introducerea şi editarea datelor 5. Un ]ntreprinz[tor care face o investi\ie ]ntr-un obiectiv economic c`=tig[ un venit net anual de 200000 lei, timp de 12 ani c`t este durata de func\ionare a obiectivului. Care este valoarea prezent[ a acestui flux de venituri ]n momentul investi\iei, la o valoare de discontare de 14%. Se va crea urm[toarea foaie de calcul (figura 5.8):

Figura 5. 8 }n celula B5 se va introduce formula: ˆ PV (B3, B2, B1), unde B3 reprezint[ dob`nda, B2 perioada ]n care se ob\in veniturile, B1 valoarea venitului anual. 6. O echip[ de muncitori este pl[tit[ ]n func\ie de num[rul de ore lucrate. Orele lucrate peste programul normal de lucru (8 ore) sunt pl[tite dublu. S[ se calculeze cu c`t este pl[tit zilnic fiecare muncitor, cunosc`nd tariful orar =i orele de intrare =i ie=ire din tur[. Pentru rezolvarea problemei se va folosi foaia de calcul din figura 5.9. Pentru fiecare muncitor, se calculeaz[ ]n coloana E num[rul de ore lucrate. Formula utilizat[ ]n celula E4 este ˆD4-C4, formul[ care se copieaz[ ]n domeniul E5:E8. }n coloana F se afi=eaz[ salariul calculat pentru orele lucrate ]n 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) Aceast[ formul[ se copieaz[ ]n domeniul F5:F8. Dac[ ]n func\ia IF ar fi fost utilizat[ condi\ia E4‹8, ar fi fost incorect. E4‹8 este ]ntotdeauna adev[rat[, deoarece ]n E4 avem o or[ =i se =tie c[ numerele seriale ata=ate orelor sunt mai mici dec`t 1, deci =i mai mici dec`t 8. Pentru a extrage num[rul de ore lucrate s-a folosit func\ia HOUR.

Figura 5.9 7. Un registru Excel este alc[tuit din dou[ foi de calcul. O foaie de calcul – cursuri con\ine cursurile de schimb pentru mai multe valute, cealalt[ foaie – casa – este folosit[ pentru a calcula echivalentul ]n lei al sumelor schimbate.

Modelarea deciziilor utilizând foile de calcul 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 }n coloana Nume se introduce numele persoanei care realizeaz[ o tranzac\ie, iar ]n coloana Tranzac\ie se introduce tipul tranzac\iei efectuate. Valorile permise ]n aceast[ coloan[ sunt C – pentru cump[rare =i V – pentru v`nzare. }n coloana Tip valut[ se introduce numele monedei schimbate (aten\ie, numele monedei trebuie s[ fie identic cu cel din foaia de calcul cursuri). }n coloana Suma schimbat[ se introduce suma care se schimb[. }n coloana Echivalent lei se calculeaz[ echivalentul ]n lei al sumei schimbate (]n func\ie de valut[, suma schimbat[ =i tipul tranzac\iei). Formula utilizat[ ]n celula E2 este: ˆD2*VLOOKUP(C2, cursuri!A$2:C$9, IF(B2ˆ”C”,2,3)) Se copieaz[ formula pe coloan[, =i pe m[sur[ ce se introduc date ]n foaia de calcul, echivalentul ]n lei al sumei schimbate se va calcula automat.