Excel4-Utilizarea Functiilor Excel

5/24/2018 Excel4-Utilizarea Functiilor Excel

1/33

1.4 UTILIZAREA FUNCIILOR EXCEL

Procesorul de tabele Excel include un numr mare de funcii predefinite (232), darofer i posibilitatea ca utilizatorul s-i defineasc propriile funcii, potrivit cerinelor deexploatare a aplicaiilor.

Funciile Excel permit efectuarea de calcule i prelucrri diverse, de la cele mai

simple pn la cele mai complexe.

1.4.1 FUNCII PREDEFINITEFunciile predefinite reprezint formule speciale care respectnd o anume sintax,

execut operaii i prelucrri specifice, fiind destinate rezolvrii unor probleme iaplicaii ce conin elemente predefinite de calcul.

Unele funcii predefinite sunt echivalente formulelor: de exemplu, formula deadunare a coninutului celulelor A1, A2 i A4, adic =A1+A2+A4 este echivalent cufuncia =Sum(A1:A2;A4). Alte funcii (majoritatea cazurilor) nu au echivalent n rndul

formulelor, rezultatul scontat neputnd fi obinut dect prin aplicarea funciilorpredefinite sau putnd fi obinut pe cale obinuit, prin aplicarea succesiv a mai multoroperaii i formule.

Folosirea funciilor predefinite este supus unor reguli foarte stricte, a crornerespectare poate conduce la un rezultat incorect sau generator de eroare.

Cea mai mare parte a funciilor predefinite au trei componente:- semnul "egal"= (sau semnul plus +, pentru compatibilitate cu 1-2-3);- numele funciei;- unul sau mai multe argumente;Nici un spaiu nu este admis ca separator ntre cele trei componente ale funciilor

predefinite. Argumentele se afl nchise ntre paranteze rotunde i sunt separate printr-un

separator zecimal. Acest separator poate fi virgul sau punct i virgul, dup cum a fostconfigurat iniial sistemul. n exemplele luate, se va lua n consideraie ca separatorzecimal caracterul "punct i virgul".

Exist i funcii care nu au nevoie de precizarea argumentului, deexemplu:=NOW(), =TRUE(), =TODAY(), etc.

Exemplul urmtor ilustreaz diferite argumente care se pot ntlni la o funciepredefinit:
http://www.ase.ro/ciedd/birotica/CAP15.htmhttp://www.ase.ro/ciedd/birotica/excel-cuprins.htmhttp://www.ase.ro/ciedd/birotica/CAP13.htm


2/33

Funcie predefinit Tip argument=SUM(A2:A7) plaj continu de celule=SUM(A2:A7;A9;A11:A20) plaj discontinu de celule

=MAX(59;36;84) list de valori=DATE(62;10;18) list de valori dat calendaristic=IF(A1=A2;"Bun";Rau") valoare logic=INT(SUM(D1:D9) funcie predefinit=UPPER("Ionescu") ir de caractere=REPT("Ionescu",3) ir i valoare numeric=FACT(6) valoare numeric

Excel accept urmtoarele tipuri de argumente:

-o condiie:este o expresie logic care folosete unul din operatorii logici =, ,, =, NOT( ), AND( ), OR( ) pentru o adres de celul sau un nume de cmp.

Condiia argumentului poate fi deci o formul, un numr, un nume de cmp, un text.Funcia evalueaz condiia i procedeaz la diferite operaii n funcie de faptul daccondiia este adevrat sau fals.

- o locaie: este o adres, un nume de cmp, o formul sau funcie care genereaz oadres sau un nume de cmp.

- un text: orice secven de caractere inclus ntre ghilimele, adresa sau un numede cmp ce conine o etichet tip ir de caractere sau o formul sau funcie carereturneaz o etichet. Un ir de caractere folosit ntr-o funcie trebuie pus ntre ghilimelepentru a nu fi confundat cu un nume de cmp.

- o valoare: un numr, adresa sau numele unei celule care conine un numr, oformul sau funcie predefinit care returneaz un numr.

Toate tipurile de argumente pot fi folosite mpreun ntr-o funcie atunci cndsintaxa este respectat.O funcie predefinit se poate introduce ntr-o celul tastnd-o ca atare (conform

sintaxei) sau prin intermediul generatorului de funcii.Cea mai simpl metod o reprezint introducerea nemijlocit a funciilor

predefinite, corespunztor sintaxei, n celula unde se va opera calculul respectiv (metodrecomandat).

n cel de-al doilea caz, se activeaz selectorul funciilor predefinite aflat pe bara deeditare sau se activeaz comanda InsertFunction (figura 1.52). Apoi, se alege funciarespectiv, din caseta de dialog Paste Function, se valideaz i se completeaz sintaxagenerat automat.


3/33

Fig 1.52 Etapele inserrii unei funciiApelarea selectorului de funcii se face prin apsarea butonului = (egal) aflat pebara de editare, dup care se deschide lista funciilor predefinite, se alege funcia dorit,dup care se completeaz interactiv argumentele.

Fig. 1.53/1.54 Asistentul de funcii/Exemple de dateAcest procedeu este prezentat n figura 1.53

1. Sepoziioneazcursorul acolounde se vainsera funcia

2. Se apasbutonul egal depe bara de editare

3. Se alegefunia dorit4. Secompleteazinteractivsintaxa


4/33

Funcia poate fi aleas din lista funciilor cele mai utilizate (Most Recently Used), dinlista tuturor funciilor disponibile ordonate alfabetic (All), sau din categoriile de funciispecializate (Financial, Date & Time, Math & Trig, Statistical...)

n celula din care s-a apelat funcia predefinit va apare sintaxa funciei selectate ivalidndu-se operaia prin butonul OKse va genera rezultatul respectivei funcii.

Generatorul de funcii sau mai corect asistentul de funcii este prezentat n figura1.53.De regul, utilizarea asistentului de funcii presupune parcurgerea a doi pai:-pasul 1semnific alegerea tipului de funcie;-pasul 2presupune completarea interactiv a sintaxei funciei respective conform

exemplului prezentat n figura 1.54.Categorii de funcii predefiniteExcel posed un set impresionant de funcii predefinite, n numr de 232, grupate

pe tipuri potrivit utilitii acestora la rezolvarea diferitelor probleme.Astfel, considerm suficient n rezolvarea aplicaiilor EXCEL, prezentarea a celor

mai importante 99 de funcii predefinite, grupate pe urmtoarele categorii (figura 1.55):

Fig. 1.55 Categorii de funcii1. 1. funcii matematice i trigonometrice (Math & Trig): permit efectuarea

de calcule matematice simple i complexe;2. 2. funcii statistice (Statistical):permit efectuarea unor calcule statistice

utiliznd serii de valori;3. 3. funcii de informare (Information): afieaz informaii despre celule i

cmpuri;4. 4. funcii logice (Logical): determin valoarea de adevr sau de fals -

corespunztor unei condiii;

5. 5. funcii baz de dat (Database): efectueaz diferite calcule asupra unorrubrici, ntr-o baz de date, corespunztor unor criterii definite;6. 6. funcii de cutare i consultare (Lookup & Reference): permit

localizarea coninutului unei celule;7. 7. funcii calendar sau dat calendaristic (Date & Time):manipuleaz

numere care reprezint date calendaristice sau timp;8. 8. funcii text sau ir de caractere (Text): ofer informaii legate de textul

existent n celule i permit operaii cu etichete;


5/33

9. 9. funcii financiare (Financial): permit realizarea de calcule economico-financiare predefinite.

n continuare, prezentm cele mai importante funcii predefinite, preciznd c ceamai mare a parte a lor sunt perfect compatibile ca sintax i ca semnificaie cu funciilearond aferente procesorului de tabele LOTUS 1-2-3.

1.4.1.1 FUNCIILE MATEMATICE I TRIGONOMETRICEFunciile matematice i trigonometrice (Math & Trig) permit efectuarea

diferitelor calcule, de la cele mai simple la cele mai complexe, pentru rezolvarea deaplicaii ce solicit instrumente matematice i trigonometrice de uz curent.

Fig. 1.56 Funcia SUM=SUM(list) adun valorile dintr-o list precizat ca argument.Lista poate conine cpuri continue sau discontinue referite prin adrese

(coordonate) sau prin nume de cmp(uri).Funcia de nsumare este completat - spre uurina utilizatorului - cu butonul Auto

Sum. Funcia generat de butonul respectiv nsumeaz pe linie sau pe coloan valori

adiacente (valorile nu trebuie s fie ntrerupte n succesiunea lor de celule vide sau decelule care s conin texte). Auto-nsumarea opereaz astfel pe linie sau pe coloan pnacolo unde se ntlnete primul semn de discontinuitate (figura 1.56).

Pot exista mai multe cazuri (exemplificate n figura 1.56):- - se plaseaz cursorul acolo unde se dorete a se calcula suma (eventual selectnd

o plaj de celule pe linie sau o coloan unde s se depun rezultatele- - calculelor) i se activeaz butonul AutoSumprin dublu-click;


6/33

Fig. 1.57 Funcia AutoSum- se selecteaz plaja de celule de nsumat, inclusiv zona unde se vor plasa

rezultatele nsumrii (o linie mai jos i/sau o coloan mai la dreapta), dup care seactiveaz butonul AutoSumprin dublu-click.

=PRODUCT (list) multiplic valorile coninute ntr-o list. Un exempluedificator este prezentat n figura 1.58.

=SUBTOTAL(referin-tip;cmp de regrupat)calculeaz un rezultat ce provinedintr-o grupare a datelor opernd diferite operaii specifice (conform referinelor-tip)asupra unui cmp de regrupat.

Exemple de referine-tip ar fi:1AVERAGE Medie Fig. 1.58 Funcia PRODUCT2COUNT Numr4MAX Maximum

5MIN Minimum6PRODUCT Produs9SUM Sumn exemplul prezentat n figura 1.59 se calculeaz suma (referina-tip 9) valorilor

produselor vndute pe 01-Iul-98 (cmpul de regrupat este E31:E33).=SUMPRODUCT(list) multiplic valorile situate n celulele corespondente,

aferente unor serii de cmpuri, iar apoi adun rezultatele obinute. n exemplul prezentat

u u-cpebutonulAutoSum

Seselecteaz celulsauplajade celuleundesevacalculaautomat suma


7/33

n figura urmtoare se calculeaz prin funcia SUMPRODUCT valoarea total avnzrilor, adic suma dintre produsele cantitilor (C31:C36) i preurilor (D31:D36).

=SUMIF(cmp de evaluat; criteriu; cmp de nsumat) adun coninutulcelulelor potrivit unui criteriu dat.

n exemplul din figura 1.59 se calculeaz prin funcia SUMIF, suma

Fig. 1.59 Funciile SUMTOTAL,

SUMPRODUCT, SUMIFcomisioanelor la vnzrile de produse (5% din valoare) pentru valorile vndute de peste10.000.000 lei. n acest caz cmpul de evaluat reprezint valoarea (E31:E36), criteriuleste de tip text i anume >10000000, iar cmpul de nsumat este comisionul (F31:F36).

=ROMAN(numr;format) convertete numerele din format cifric arab n text cesemnific numere cu format cifric roman. Formatul cu valori de la 0 la 4- reprezintgradul de concizie al numrului roman nou generat. Numrul arab de transformat trebuies fie ntreg. Un exemplu de astfel de transformare este prezentat n figura 1.60.

=RAND()returneaz un numr aleator cuprins ntre 0 i 1;=ABS(numr)returneaz valoarea absolut dintr-un numr;=LN(numr)calculeaz logaritmul natural

Fig. 1.60 Funcia ROMANal unui numr specificat ca argument;=LOG(numr;baz)returneaz logaritmul unui numr ntr-o baz specificat;


8/33

Fig. 1.61 Funcii trigonometrice i POWER iSQRT

=LOG10(numr)returneaz logaritmul n baza 10 dintr-un numr;=EXP(X)calculeaz baza logaritmului natural ridicat la puterea X. Baza este o

constant i are valoarea 2,7182818.;

Fig. 1.62 Funcii matematice=MOD(X;Y)calculeaz restul mpririi argumentului X la arg. Y;

=FACT(numr)calculeaz factorialul unui numr pozitiv;=POWER(numr;putere)returneaz rezultatul unui numr ridicat la putere(figura 1.61);

=SQRT(numr)calculeaz rdcina ptrat a argumentului;=SIN(X)returneaz valoarea argumentului X n radiani;=COS(X) calculeaz cosinusul argumentului X n radiani ;=TAN(X)calculeaz tangenta argumentului X n radiani;=ASIN(X)calculeaz arc-sinusul argumentului X n radiani (similar =ACOS(X)i

=ATAN(X);=DEGREES(unghi)convertete radianii n grade;=RADIANS(unghi) convertete grade n radiani;

=ROUND(X,numr de zecimale)rotunjete argumentul numeric X la un

Fig. 1.63 Funcia de rotunjire


9/33

numr specificat de zecimale;=PI()returneaz valoarea numrului PI;=INT(numr)afieaz partea ntreag a argumentului (a numrului real), fr a-l

rotunji.1.4.1.2 FUNCIILE STATISTICE

Funciile statistice (Statistical) permit efectuarea de calcule statistice utilizndserii de valori:=MAX(list) returneaz cea mai mare valoare din list. Lista poate fi compus din:

numere, formule numerice, adrese sau nume de cmpuri;=MIN(list)returneaz cea mai mic valoare din list;=AVERAGE(list)calculeaz media valorilor din list;=GEOMEAN(list)calculeaz media geometric a valorilor dintr-o list=HARMEAN(list)calculeaz media armonic a valorilor dintr-o list;=MEDIAN(list)calculeaz valoarea median dintr-o list;=COUNT(list)numr celulele ocupate dintr-o list de cmpuri;Exemplul din figura 1.64 ilustraz utilizarea funciilor statistice prezentate:

Fig. 1.64 Funcii statistice

1.4.1.3 FUNCIILE DE INFORMARE

Funciile de informare (Information) afieaz informaii referitoare la celule icmpuri:

=ISBLANK(X)determin dac X sau amplasamentul definit de argumentul X estesau nu o celul vid. Funcia returneaz TRUE -valoarea logic de adevr- dacamplasamentul este o celul vid i FALSE valoarea logic de fals- n caz contrar;

=ISNUMBER(X)verific dac X conine o valoare numeric. Funcia returneazTRUE adevrat- dac X conine un numr, altfel returneaz FALSE sau fals.Argumentul X poate fi o valoare, o adres, text sau o condiie);

=ISTEXT(X) verific dac X conine un ir de caractere, returnnd dup cazTRUE sau FALSE.

=ISNONTEXT(X)verific dac X nu conine un ir de caractere, returnnd dupcaz TRUE sau FALSE.


10/33

=ISLOGICAL(X) verific dac argumentul X conine o valoare de tip logicreturnnd dup caz TRUE sau FALSE.

=ISERROR(X) verific dac argumentul X conine o valoare de tip eroare,returnnd dup caz TRUE sau FALSE.

O parte din funciile de informare sunt exemplificate mpreun cu funciile logice.

1.4.1.4 FUNCIILE LOGICEFunciile logice (Logical) determin evaluarea unor expresii i n funcie de

acestea furnizeaz aciuni sau rezultate complexe, genernd valori de adevr sau de fals -corespunztor unor condiii (acestea pot fi evaluate i nlnuite cu ajutorul operatorilorlogici AND, OR, NOT).

=IF(condiie;X;Y)testeaz argumentul condiie i n funcie de rezultatul evaluriilogice, genereaz argumentul X dac condiia este adevrat sau argumentul Y dacaceasta este fals.

Argumentele X sau Y pot fi valori, iruri de caractere (plasate ntre ghilimele),

nume de cmpuri sau adrese de celule sau cmpuri care conin aceste valori. n loculargumentelor X sau Y se pot imbrica alte structuri condiionale IF, generndu-se potrivitcondiiilor ulterioare, X1,Y1 sau X2,Y2 i aa mai departe.

=AND(evaluare logic1,evaluare logic2,...) returneaz valoarea logic TRUEdac toate argumentele sunt adevrate i valoarea logic FALSE dac unul sau mai multeargumente sunt false;

=OR(evaluare logic1,evaluarea logic2,...) returneaz valoarea logic TRUEdac orice argument este adevrat i valoarea logic FALSE dac toate argumentele suntfalse;

=NOT(evaluarea logic) inverseaz valoarea argumentului, returnnd dup cazTRUE sau FALSE;

=TRUE()returneaz valoarea logic TRUE;=FALSE()returneaz valoarea logic TRUE;Pentru exemplificarea funciei logice IF, furnizm urmtoarea aplicaie pentru

calculul impozitului pe salariile colaboratorilor angajai cu Convenie Civil de PrestriServicii:

Astfel, dac salariul brut este sub 500.000 lei, impozitul este de 10% din brut, altfel,dac salariul brut este cuprins ntre 500.000 lei i 1.500.000 lei, impozitul este de 50.000lei + 20% din ceea ce depete 500.000 lei salariu brut, dac salariul brut este cuprinsntre 1.500.000 lei i 2.500.000 lei, impozitul este de 250.000 lei + 25% din ceea cedepete 1.500.000 lei salariu brut, dac salariul brut este cuprins ntre 2.500.000 lei i3.500.000 lei, impozitul este de 500.000 lei + 30% din ceea ce depete 2.500.000 lei

salariu brut, dac salariul brut depete 3.500.000 lei, impozitul este de 800.000 lei +40% din ceea ce depete 3.500.000 lei salariu brut.Aplicaia este astfel construit nct s exemplifice (didactic) toate funciile logice

(figura 1.65, 1.66).ntr-un prim pas s-a construit o coloan de Evaluare logic care returneaz n

funcie de un test fcut asupra salariului brut, valoarea logic de fals (FALSE) dacsalariul brut este text, blank sau este mai mic ca zero i returneaz valoarea logic deadevr (TRUE) n caz contrar.


11/33

Fig. 1.65 Funcii logice (I)

ntr-un al doilea pas se calculeaz impozitul pe salarii dac coloana Evaluarelogic (s-a utilizat funcia NOT()) nu conine valoarea logic FALSE.

n aplicaia de mai sus s-a construit o structur condiional imbricat, unde s-aexemplificat ntr-o ramur IF i funcia logic AND.

Fig. 1.66 Funcii logice(II)1.4.1.5 FUNCIILE BAZ DE DATE

Funciile baz de date (Database) returneaz aciuni - valori sau etichete(sum;medie;maxim;minim; caut;numr) dintr-un cmp de date - corespunztor uneibaze de date, dup o anumit rubric, conform unui criteriu de selecie

Funciile tip baz de date au n mod invariabil aceeai list de argumente:- - baza de date: reprezint tabelul Excel sub forma unui cmp de date, de unde

informaia va fi consultat sau extras;- - rubrica: semnific atributul sau proprietatea asupra cruia opereaz calculul

fcut de funcia tip baz de date. Rubrica poate fi identificat prin numele su sau prinnumrul de ordine al acesteia n cadrul bazei de date;

- - cmp de criterii: reprezint unul sau mai multe cmpuri continue n care se potpreciza restriciile, care se regrupeaz n criterii de selecie la care trebuie s rspundinterogarea respectiv.

Principalele funcii tip baz de date sunt:=DSUM(baz de date;rubric/nr.rubric;cmp de criterii) returneaz suma

valorilor unei rubrici aferente unei baze de date, care rspunde unui criteriu de selecie;=DMAX(baz de date;rubric/nr.rubric;cmp de criterii)returneaz cea mai

mare valoare dintr-o rubric aferent unei baze de date, corespunztor unui criteriu deselecie;


12/33

Fig. 1.67 Aplicaie pentru funciile bazde date

=DMIN(baz de date;rubric/nr.rubric;cmp de criterii) returneaz cea maimic valoare dintr-o rubric aferent unei baze de date, corespunztor unui criteriu deselecie;

=DAVERAGE(baz de date;rubric/nr.rubric;cmp de criterii) calculeazmedia valorilor unei rubrici aferente unei tabele - pentru o baz de date, potrivitcriteriului de selecie specificat=DCOUNT(baz de date;rubric/nr.rubric;cmp decriterii) numr celulele ocupate ntr-o tabel baz de date, conform unor criteriispecificate;

=DGET(baz de date;rubric/nr.rubric;cmp de criterii)returneaz coninutulunei rubrici pentru o baz de date, corespunztor unui criteriu specificat. Funcia esteutil pentru a regsi o informaie unic; Exemple edificatoare de utilizare a funciilor tipbaz de date sunt ilustrate n figura urmtoare, urmnd ca alte aplicaii mai complexe sfie prezentate n detaliu n capitolul ce trateaz bazele de date create i exploatate subExcel.

Pornind de la un tabel definit pe coordonatele A6:H17, considerat a fi o baz dedate care repertoriaz facuturile emise de o firm ctre clienii si, se pot pune neviden cu ajutorul funciilor tip baz de date, informaii calculate potrivit unorinterogri specifice.

Prezentm n figurile 1.67- 1.68 cteva exemple de utilizare a funciilor tip baz dedate, utiliznd cmpuri de criterii definite de utilizator potrivit unor cerine de interogare.

1.4.1.6 1.4.1.6 FUNCIILE DE CUTARE I CONSULTARE

Funciile de cutare i consultare (Lookup & Reference) permit cutarea,identificarea i referirea coninutului unor celule:


13/33

Fig. 1.68 Modul de utilizare a funciilor bazde date

=CHOOSE(index-numeric;list de valori)returneaz n urma unei alegeri dintr-o

list de valori, o aciune sau o valoare, ce urmeaz a fi activat sau executat,corespunztor unui index numeric. Indexul numeric determin care valoare (de tip text,numeric sau referin celular) din lista de argumente va fi selectat. Indexul este unnumr cuprins ntre 0 i 29.

=COLUMN(referin celular sau cmp) returneaz numrul colaneicorespunztoare referinei celulare sau numrul primei coloane pentru cmpul specificat;

=COLUMNS(cmp)returneaz numrul de coloane aferente cmpului specificatca argument;

=ROW(referin celular sau cmp)returneaz numrul liniei corespunztoarereferinei celulare sau numrul primei linii a cmpului specificat ca argument;

=ROWS(cmp)returneaz numrul de linii pe care l ocup cmpul specificat ca

argument;=AREAS(referin celular) indic numrul de zone contigue dintr-un cmp.Dac respectivul cmp conine mai multe zone contigue, atunci argumentul se mainchide ntr-o parantez suplimentar;

n figura 1.69 sunt prezentate mai multe exemple de utilizare a funciilorenumerate mai sus.


14/33

Fig. 1.69 Aplicaie pentru funciile de cutare iconsultare=VLOOKUP(cheie;cmp de consultare;coloan de recuperat) returneaz

coninutul unei celule ce figureaz ntr-o coloan dintr-un tablou de consultare vertical.Sintaxa funciei de consultare vertical admite trei argumente i anume:- - cheie: reprezint valoarea dup care are loc cutarea sau consultarea, (adres

absolut/relativ sau nume de cmp);- - cmp (sau tabel) de consultare: este cmpul asupra cruia opereaz consultarea

prin cutarea valorii cheii precizate anterior;- - coloan de recuperat: este numrul coloanei (numerotarea ncepe cu 1) de unde

va fi recuperat informaia gsit n tabelul de consultare, corespunztor valorii cheii de

cutare.n mod obligatoriu tabelul de consultare va fi sortat cresctor dup coloana careconine valorile cheii de consultare (comanda Data Sort, iar n rubrica Sort by se vapreciza numrul sau numele coloanei dup care se va face sortarea)

=HLOOKUP(cheie;cmp de consultare;linie de recuperat) returneazconinutul unei celule ce figureaz ntr-o anumit linie a unui tablou de consultareorizontal.

Argumentul cheie (sub forma unei referine celulare sau nume de cmp) va fi cutatn prima linie a cmpului de consultare, iar dac valoarea va fi gsit pe un numrul delinie precizat de ultimul argument, valoarea respectiv va fi returnat de funciaHLOOKUP.

n mod obligatoriu tabelul de consultare orizontal trebuie sortat dup valorilecresctoare ale cheii de consultare aflate n prima linie (sortare de la stnga la dreapta).Dac valorile cheii nu sunt sortate, se va selecta tabelul de consultare i se va activacomanda de sortare (de la stnga spre dreapta):Data Sort, butonul Optioni din rubricaOrientationse alege opiunea Sort left to right.

Dac informaia cutat n tabelul de consultare vertical sau orizontal nu va figsit, se va returna cea mai apropiat valoare (pe verical sau pe orizontal) de cheia deconsultare.


15/33

Pentru exemplificarea celor dou funcii de consultare propunem urmtoareaaplicaie:

O societate comercial de distribuie ntocmete, cu ajutorul procesorului de tabeleEXCEL,facturi pentru livrrile efectuate.

Opional, respectiva societate efectueaz i transportul mrfii comandate la

domiciliul clientului, firma practicnd tarife difereniate n funcie de cantitateatransportat (n tone) i de oraul de destinaie.Tarifele de transport sunt grupate ntr-un tablou n funcie de destinaie (prima

linie) i de cantitatea transportat (prima coloan). Tabloul care urmeaz a fi considerattabel de consultare orizontal a fost definit pe coordonatele F20:J28 (figura 1.70) i a fostn prealabil sortat de la stnga la dreapta dup prima linie, adic dup destinaie.

Fig. 1.70 Date pentru aplicaia de cutare.Firma i are nregistrai clienii ntr-o baz de date (definit pe coordonatele E1:I7)

(figura 1.71) care regrupeaz elementele de identificare ale acestora (Client, Adresa,Localitate, Cod fiscal, Cont bancar).

n egal msur exist i o alt baz de date definit pe coordonatele A20:C28(figura 1.72) sub forma unui nomenclator de preuri pentru fiecare produs n parte. Cele

dou baze de date sunt sortate dup valorile cresctoare ale primei coloane i conininformaii pertinente ce concur la realizarea automat a facturii.

Fig. 1.71 Date pentru aplicaia decutare

Factura procesat cu Excel are urmtoarea form (figura 1.73):Utilizatorul va introduce prin tastare, pentru completarea facturii

doar denumirea clientului, codul produsului facturat, cota de adaoscomercial, cantitatea livrat, iar opional dac se dorete sau nutransport, precum i destinaia transportului. n rest toate operaiilesunt fcute automat cu ajutorul formulelor i a funciilor Excel.


16/33

Factura se proceseaz n mod obinuit, ncepnd a se calcula ntr-un prim timp"Valoarea", Majorrile, "TVA-ul" i "Valoarea facturat". La

Fig. 1.72 Nomenclatorul de preuri

calculul "Valorii" se va lua n calcul i o cot variabil de adaos comercial (celula D10 afost fixat cu adres absolut -$D$10- pentru a nu se decala la

Fig. 1.73 Factura obinutcopierea formulei ce calculeaz valoarea), precum icheltuielile de transport. Majorrile de ntrziere se potcalcula pe trane, prin structuri condiionale imbricate.TVA-ul reprezint 22% din Valoare + Majorri, iarValoarea facturii reprezint suma dintre Valoare,

Majorri i TVA.

ntr-un al doilea timp se pot calcula totalurile pe rubricile procesate anteriorutiliznd clasica funcie SUM.Interesante de prezentat suntfacilitile de consultare vertical i orizontal.La tastarea numelui de client n celula C3, se vor recupera automat dintr-un tabel de

consultare vertical (definit anterior pe coordonatele E1:I7), informaiile legate de acestidentificator i anume: Adresa, Localitatea, Codul fiscal i Contul bancar.

Astfel n celula C4 s-a scris formula de consultare vertical (VLOOKUP) (figura1.74) pentru recuperarea adresei clientului, anume: se caut cheia de consultare (celula$C$3-Client) n tabelul de consultare definit pe coordonatele $E$1:$I$7 i n caz cvaloarea este gsit, se va recupera informaia din coloana 2, corespunztoare cheii deconsultare.

Coordonatele cheii i tabelului de consultare au fost blocate prin utilizarea deadrese absolute pentru ca formula ce conine consultarea vertical s poat fi copiat frca respectivele coordonate s se decaleze.

Fig. 1.74 Funcia de consultare vertical


17/33

Cheia de consultare fiind n acest caz de tip text nu trebuie s aib valori vide inici numerice. Pentru aceasta, procedura de consultarea vertical a fost completat cuteste fcute asupra celulei care conine cheia de consultare ($C$3). Dac cheia arevaloarea vid ISBLANK($C$3) sau (OR()) dac conine o valoare alta dect textISNONTEXT($C$3), atunci se va afia un spaiu (), altfel se va face consultarea

vertical.n aceste condiii, consultarea vertical va avea urmtoarea form:=IF(OR(ISBLANK($C$3);ISNONTEXT($C$3));;VLOOKUP($C$3;$E$1:$H$7;

3)), fapt ilustrat i n figura 1.75.

Fig. 1.75 Funcia de consultare vertical

Dac se tasteaz un client care nu exist n nomenclatorul de clieni (n tabelul deconsultare vertical), funcia VLOOKUP nu va semnala lipsa informaiei din tabel ci vareturna informaia legat de cea mai apropiat valoare a cheii de consultare. De exemplu,dac s-ar introduce clientul cu numele Sarmis, se vor recupera prin VLOOKUPinformaiile adiionale corespunztoare celei mai apropiate valori ale cheii, adicinformaiile legate de clientul Star. Funcia VLOOKUP nu va semnala inexistena cheiide consultare Sarmis. Pentru nlturarea acestui neajuns, procedura de consultarevertical a fost completat cu un test de existena cheii ce consultare n tabelul deconsultare.

Acest test de existen verific dac valoarea cheii de consultare este gsit nprima coloan a tabelului de consultare. Dac valoarea respectiv exist n tabel

nseamn c s-a gsit cheia de consultare i n consecin consultarea vertical se vaefectua returnnd un rezultat corect, altfel se va afia spaiu sau zero (ultimul cazfolosindu-se dac celula respectiv particip ulterior la calcule) sau un mesaj de genulcheie inexistent.

Formula de testare a existenei cheii de consultare n tabel este urmtoarea:IF(VLOOKUP($C$3;$E$1:$I$7;1)$C$3;;VLOOKUP($C$3;$E$1:$I$7;4)).Rubricile: Adresa C4-, Localitatea C5-, Cod fiscal C6-, Cont

Fig. 1.76 Consultare vertical.bancar C7- se vor recupera prin acelai procedeu de consultare vertical, recuperndu-se dup caz, prin funcia VLOOKUP coninutul coloanelor 2, 3, 4, i 5, corespunztorvalorilor cheii de consultare declarate la adresa $C$3 (figura 1.76).


18/33

n mod asemntor se procedeaz i cu a doua consultare vertical, anume: nmomentul tastrii "Codului de produs" este consultat vertical tabelul "PREURI" declaratla adresa A20:C28, i dac n tabelul respectiv este gsit cheia de consultare "Codprodus" - se vor recupera automat: coninutul coloanei 2 i 3 din tablou, adic "Denumireprodus" i "Pre".

Fig. 1.77 Aplicaie de consultare verticaln figura 1.77 este prezentat procedura complet (cu teste fcute asupra celulei ce

conine cheia de consultare i cu test de existen a valorii cheii n tabelul de consultare)de extragere a denumirii produsului, prin consultare vertical. Similar se procedeazpentru extragerea preului din tablou, corespunzror valorilor luate de codul produsului.

Consultarea orizontala tabelului declarat pe coordonatele $F$20:$J$28 are locdup valorile luate de cheia de consultare - $G$10 Destinaia. Dac cheia este gsit ntablou, se va recupera numrul de linie care va conine valoarea cheltuielilor de transportcorespunztoare destinaiei specificate.

n exemplul prezentat n figura 1.78, s-a operat un mic artificiu, anume "Cantitatealivrat" coincide logic cu numrul de linie de recuperat orizontal din tablou (astfel, nu s-aprecizat numrul liniei recuperate, ci celula care conine livrat, aflat la adresa C13).Datorit faptului c procedura de consultare orizontal este operaional ncepnd cu linia1 (care conine invariabil titlurile rubricilor aferente destinaiei), celula Cantitatealivrat va indica numrul liniei de recuperat i va avea valoarea incrementat cu ounitate pentru a exista o concordan ntre valorile luate de aceasta i numrul liniei derecuperat. Dac nu s-ar fi operat acest artificiu, numrul liniei de recuperat ar fi decalatcu o unitate (adic, dac celula C13 Cantitatea livrat ar fi avut valoarea 3, s-ar fi

recuperat linia numrul 3 din tabel prima linie conine titlul rubricilor-, adic valoareacheltuielilor de transport aferente pentru 2 tone transportate)

Prin funcia HLOOKUP s-a consultat deci respectivul tablou, cutndu-se valoarealuat de Destinaie n celula $G$10, recuperndu-se numrul de linie ce corespundelogic cu Cantitatea livrat.

Tabloul de consultare trebuie n mod obligatoriu sortat alfabetic dup prima linie asa.

Procedura de consultare orizontal poate fi completat i astfel mbuntit(celula D14) prin urmtoarele teste:

- un test fcut asupra celulelor ce conin: Destinaia (s nu fie valoare vid saunumeric) i Cantitatea livrat (s nu fie valoare de tip text, vid sau zero):

=IF(OR(ISNONTEXT($G$10),ISBLANK($G$10),ISTEXT(C14),ISBLANK(C14),C14=0;0;IF() . Dac cel puin unul din argumente este adevrat, funcia va returnavaloarea zero, altfel se vor testa i alte condiii de ndeplinit;


19/33

Fig. 1.78 Aplicaie de

consultare orizontal- un test de existen a Destinaiei (celula $G$10) n prima linie a tabloului deconsultare orizontal: =IF(OR(;$G$10HLOOKUP($G$10;$F$20:$J$28;1));0;..).Dac Destinaia este inexistent n tablou, funcia va returneaz valoarea zero, altfel seprocedeaz la consultarea propriu-zis;

- un test de existen a Cantitii livrate n prima coloan a unui tablou deconsultare vertical:

=IF(OR(C14VLOOKUP(C14;$E$20:$E$28;1);.). Dac nu exist valoareaunei cantiti livrate n tabloul de consultare vertical definit pe coordonatele$E$20:$E$28, funcia returneaz zero, altfel se procedeaz la consultarea propriu-zis:

(HLOOKUP($G$10;$F$20:$J$28;C14+1));

- procedura ar putea fi completat i cu un test de efectuare a transportului: astfel,dac transportul este fcut de furnizor (celula $D$8 are valoarea da), atunci seprocedeaz la testele de mai sus i se execut n final consultarea orizontal, altfelcheltuielile de transport vor fi zero.

Figura 1.79 indic corespondenele creeate ntre diferitele cmpuri, n procesul deconsultare vertical i orizontal.


20/33

Fig. 1.79 Consultarea vertical i orizontal.Corespondene

1.4.1.71.4.1.7FUNCIILE TIP DAT CALENDARISTIC I OR

Funciile tip dat calendaristica i ora (Date & Time) manipuleaz i opereazcalcule cu valori numerice ce reprezint date calendaristice sau timp:

=NOW() returneaz un numr corespunztor datei curente - cu zecimale cereprezint ora;

=TODAY()returneaz un numr-dat corespunztor datei curente;=DATEVALUE("ir de caractere") calculeaz numrul-dat corespunztor

irului de caractere n format dat calendaristic (irul trebuie plasat ntre ghilimele);=DATE(an;lun;zi) calculeaz numrul-dat pentru data calendaristic specificat

ca argument;=YEAR(numr-dat) returneaz corespunztor anului, un numr cuprins ntre 0

(1900) i 199 (2099) - extrgnd rezultatul dintr-un numr-dat;=MONTH(numr-dat) extrage luna dintr-un numr-dat, sub form de valori

cuprinse ntre 1 i 12;=DAY(numr-dat)genereaz un numr corespunztor zilei cu valori ntre 1 i

31;=WEEKDAY(X) returneaz numrul zilei din sptmn corespunztor

argumentului X care poate fi de tip numr dat calendaristic sau text n format dat

calendaristic;=DAYS360(dat debut;dat sfrit) calculeaz numrul de zile ntre dou date

calendaristice considernd anul ca avnd 360 de zile;=TIME(or;minut;secund) calculeaz un numr-timp corespunztor orei,

minutului i secundei;=TIMEVALUE(ir de caractere) returneaz numrul-timp corespunztor

irului de caractere specificat n format dat/or (ntre ghilimele);


21/33

=HOUR(numr-timp) extrage ora dintr-un numr-timp (0,000000 pentru ora24:00:00 i 9,999988426 pentru ora 23:59:59), sub forma unui numr cuprins ntre 0 i23;

=MINUTE(numr-timp) extrage minutul dintr-un numr-timp, sub forma unuinumr ntreg cuprins ntre 0 i 59;

=SECOND(numr-timp) extrage secunda dintr-un numr-timp sub forma unuinumr ntreg cuprins ntre 0 i 59;Un exemplu edificator de utilizare a funciilor de tip dat calendaristic i or este

prezentat n figura 1.80.

Fig. 1.80 Funciile tip dat i or

1.4.1.8 FUNCIILE TEXT SAU IR DE CARACTERE

Functiile text (Text):permit diferite operaii cu iruri de caractere i furnizeaz n

egal msur informaii legate de textul existent n celule:=CHAR(cod numeric ASCII) returneaz caracterul corespunztor codului

numeric ASCII specificat ca argument;=TRIM(text) afieaz irul de caractere specificat ca argument n care toate

spaiile inutile sunt anulate (cu excepia spaiilor care separ cuvintele textului);=CODE(text)returneaz codul numeric pentru primul caracter din textul specificat

ca argument;=CONCATENATE(text1;text2;)concateneaz mai multe iruri de caractere

specificate ca argumente, ntr-unul singur;

=EXACT(tect1;text2) verific dac dou iruri de caractere sunt identice.Comparnd cele dou iruri, funcia returneaz valoarea logic TRUE dac acestea suntidentice sau valoarea logic FALSE n caz contrar;

=UPPER(text)afieaz cu majuscule textul specificat ca argument;=LOWER(text)afieaz cu minuscule textul specificat ca argument;=MID(text;N;X)afieaz X caractere ale textului specificat ca argument, ncepnd

cu poziia N;


22/33

=LEN(text) returneaz numrul caracterelor ce formeaz textul specificat caargument;

=SUBSTITUTE(text-surs;N;X;text-nou) returneaz un nou ir de caractere(text-nou) la a N-a poziie a textului-surs, dup ce au fost anulate X caractere;

=REPT(text;numr de ori)repet afiarea textului de un numr specificat de ori;

=PROPER(text)determin scrierea cu majuscul a fiecrei prime litere din textulspecificat ca argument;

Fig. 1.81 Exemple de funcii text=VALUE(text)convertete un text ce reprezint un numr ntr-o valoare numeric

(numrul ce figureaz n textul tespectiv, trebuie s corespund unuia din formatenumerice consacrate);

=DOLLAR(numr;zecimale) convertete un numr n text, folosind un format

monetar;=FIND(text1;text2;N) localizeaz poziia la care ncepe textul1 n textul2ncepnd cutarea cu poziia N;

Exemplificrile funciilor de tip text sau ir de caractere se gsesc prezentate nfigura 1.81.

1.4.1.9 FUNCIILE FINANCIARE

Funciile financiare (Financial) efectueaz o serie de calcule economico-

financiare furniznd prin valorile returnate informaii utile referitoare la amortismente, larentabilitatea investiiilor, plasamentelor, mprumuturilor etc.

=PV(rata dobnzii;numr de perioade;mrimea plii;[valoare viitoare;tipul])returneaz valoarea actual (present value) aferent unei sume investite sau depozitate labanc, prin pli periodice, n condiiile unei rate constante a dobnzii.

Funcia financiar PV calculeaz deci valoarea prezent a unei sume investite,adic valoarea curent a unei serii de pli viitoare. Funcia se utilizeaz pentru a sedetermina dac valoarea de revenire a unei anumite investiii este favorabil sau nu,innd cont de costul iniial al investiiei.


23/33

Funcia PV (ca i alte funcii financiare PMT, FV) este considerat a fi o funcie-anuitate, adic opereaz cu o investiie sau un depozit la care toate plile sunt egale isunt efectuate la intervale regulate.

Argumentele funciei PV au urmtoarea semnificaie:- - rata dobnzii reprezint procentul de dobnd perceput pentru o anumit

perioad;- - numr de periodereprezint numrul total de pli periodice;- - mrimea pliisemnific valoarea plii fcute n fiecare perioad;- - valoare viitoarereprezint suma total care se dorete a fi realizat dup ultima

plat;- - tipul este un parametru care semnific faptul c plata se face la nceputul

perioadei (valoarea 1) sau la sfritul perioadei (valoarea 0 implicit).Dac sunt omise ultimele dou argumente, acestea vor fi considerate ca avnd

valori nule.Argumentele rata dobnzii i numrul de perioade trebuie exprimate n aceeai

unitate de timp lun sau an).

Pentru exemplificarea funciei financiare PV, furnizm urmtoarea aplicaie nfigura 1.82.O persoan fizic dorete ncheierea unei polie de asigurare pentru o perioad de

20 de ani cu o rat anual a dobnzii de 40%. Asiguratul urmeaz s plteasc lunar oprim de asigurare de 600.000 lei. Costul anuitii perceput de asigurator este estimat la19.000.000 lei. n figura urmtoare s-a calculat valoarea prezent cu ajutorul funciei PV.

Fig. 1.82 Aplicaie pentru funcii financiareDin calculul fcut (n celula B34), reiese c valoarea prezent a anuitii este de

17.993.120 lei, adic mai mic dect valoarea anuitii calculate de asigurator care estede 19.000.000 lei. Deci aceast investiie nu este rentabil.

Se observ c rata dobnzii a fost exprimat n luni (rata anual a fost mprit la12), iar numrul de ani pentru care s-a contractat asigurarea a fost exprimat tot n luni(numrul de ani a fost nmulit cu 12).

De asemenea se observ c funcia PV a returnat un numr negativ. Explicaiaacestui rezultat este legat de faptul c funcia PV semnific o cheltuial, o ieire de bani.Pentru ca funcia s returneze un rezultat pozitiv, ar fi trebuit ca argumentul valoarea

plii s fie introdus ca numr negativ (de exemplu 600.000).=FV(rata dobnzii;numr de periode[;mrimea plii; valoarea prezent;

tipul]) returneaz valoarea viitoare (future value) a unei investiii sau plasament ncondiii de anuitate (pli i rate ale dobnzii constante). Argumentele funciei financiareFV sunt identice ce cele ale funciei PV cu excepia faptului c unul din argumentereprezint valoarea prezent a investiiei sau plasamentului.

Pentru exemplificarea funciei financiare FV, oferim urmtoarea aplicaie: opersoan fizic dorete efectuarea unui plasament de 25.000.000 lei pe o perioad de 9


24/33

luni la o banc comercial, pentru o dobnd anual de 55%. Persoana fizic urmeaz adepune lunar la banc, alturi de depozitul iniial cte 1.000.000 lei pe aceeai perioad.

n figura 1.83 s-a calculat (n celula B42) valoarea viitoare a sumei depuse la bancde respectiva persoan fizic (48.259037,70 lei).

Fig. 1.83 Aplicaie pentru funcii financiare (FV)=PMT(rata dobnzii;numr de perioade;valoare prezent[;valoare

viitoare;tip])calculeaz valoarea lunar sau anual a plii pentru o investiie sau unmprumut.

Pentru exemplificarea funciei PMT (paiement), presupunem un mprumut la o

banc comercial pentru achiziionarea unui bun de folosin ndelungat n valoare de35.000.000 lei. Rata dobnzii pentru creditele de consum este de 43% pe an, iar duratamprumutului a fost stabilit la 5 ani.

Fig. 1.84 Aplicaie pentru funcii financiare

(PMT)Valoarea lunar a plii ctre banc pentru creditul acordat a fost calculat (n

celula B49) prin funcia PMT n figura 1.84:De asemenea s-a calculat costul total al mprumutului ca un produs ntre valoarea

lunar a plii i numrul de perioade de plat n luni. Valoarea total a dobnzii s-acalculat ca diferen ntre costul total al mprumutului i suma mprumutat.

=RATE(numr de perioade;valoarea plii;valoare prezent) returneaz ratadobnzii pe perioada unei anuiti, pentru un mprumut sau o investiie. Pentruexemplificare presupunem efectuarea unui mprumut printr-un credit de 15 milioane leipe timp de un an, cu o valoare lunar de rambursat n sum de 1.800.000 lei.

n figura 1.85 se calculeaz n celula E58, dobnda lunar perceput de banc, iarn celula E59 dobnda anual pentru suma mprumutat.


25/33

Fig. 1.85 Aplicaie pentru funcii financiare (RATE)Funciile financiare PMT, RATE i PV prezentate anterior, permit construirea

tablourilor de rambursare pentru mprumuturi, aplicaie exemplificat n figura 1.86.Un ntreprinztor particular solicit unei bnci comerciale un mprumut de 15

milioane lei pentru achiziionarea unui utilaj. Banca acord creditul pe o durat de 6 lunicu o dobnd de 60% pe an.

Solicitantul creditului i poate ntocmi n Excel un tablou de rambursare almprumutului, altfel spus un scadenar.

Fig. 1.86 Tablou de rambursare a creditelorntr-o prim faz, se poate calcula valoarea lunar de rambursare (celula D64) cu

ajutorul funciei PMT.n a doua faz, se organizeaz tabloul de rambursare pe patru coloane:- - prima coloan (coloana A) conine numrul lunii pentru care se face calculul

dobnzii i restul de rambursat;- - a doua coloan (coloana B) conine restul de rambursat, adic suma care rmne

de restituit bncii la sfritul lunii n curs. Pentru prima lun restul de rambursat este egal

cu creditul, adic 15 milioane, iar lunile urmtoare se calculeaz ca diferen ntre restulde rambursat i rata de plat;

- - a treia coloan (coloana C) conine dobnda lunar calculat cu funcia RATE,adic suma lunar ce reprezint dobnda calculat asupra restului de rambursat;

- - a patra coloan (coloana D) reprezint rata de rambursat, calculat ca diferenntre valoarea lunar de rambursare (celula D64) i dobnda lunar.

mprumutul este complet rambursat la nceputul celei de-a aptea perioad, dupce a fost achitat a asea rat.


26/33

Dac s-ar fi dorit rambursarea integral a mprumutului n luna a 4-a, deci cnd armai fi 3 luni de pltit, suma de rambursat ar fi de 8.047.911,48 lei (suma a fost calculatcu funcia financiar PV).

Formulele utilizate pentru studiul de caz prezentat se gsesc ilustrate n figura 1.87.

Fig. 1.87 Formulele tabloului de rambursare acreditelor

=NPER(rata dobnzii;valoarea plii;valoare prezent)returneaz numrul deperioade de plat pentru o investiie sau un plasament. Altfel spus, se calculeaz ctevrsminte sunt necesare pentru ca un capital constituit printr-o investiie i remuneratprintr-o dobnd s ating o valoare specificat.

Fig. 1.88 Aplicaie pentru fucii financiare(NPER)

n exemplul ilustrat n figura 1.88 s-a calculat numrul de perioade n ani n care unntreprinztor trebuie s restituie un mprumut de 35 milioane lei, cu o dobnd anual de20%, pltind lunar 1.200.000 lei.

=NPV(rata dobnzii;valoare1,valoare2,..) calculeaz valoarea actual net aunei investiii bazate pe o serie periodic de intrri de numerar (cash flows). FunciaNPV difer de funcia PV (present value), pentru c se bazeaz pe vrsminte care nu au

aceeai mrime. Astfel se calculeaz valoarea actual net a unor intrri viitoare defonduri, pentru a se evalua rentabilitatea unei investiii. Intrrile de fonduri suntoperaionale la intervale regulate, la sfritul fiecrei perioade.

Pentru exemplificarea funciei financiare NPV, furnizm urmtoarea aplicaie: ontreprindere dorete realizarea unei investiii de 170 milioane lei, care i va permiteintrarea unor fonduri estimate ca variabile pe parcursul a 6 ani. Aceste intrri de fondurise presupun a fi de 223 milioane lei. Astfel se va pune problema rentabilitii investiiei.


27/33

n figura 1.89 s-a construit un model economic, cu ajutorul cruia s-a calculat prinfuncia NPV (n celula B91) valoarea actual net a investiiei, care a fost de 95.291.904lei dac rata dobnzii a fost de 25% (celula B80). A face o investiie de 170 milioane lei,antreneaz cheltuieli suplimentare de 74.708.096 lei fa de cheltuielile iniial prevzute.

=SLN(valoare de inventar;valoare rezidual;durata normat de funcionare)

calculeaz amortismentul linear al unei imobilizri cu o valoare de inventar dat, inndcont de o valoare rezidual estimat, pentru un numr de periode ct se presupune c vafunciona investiia.

Amortizarea linear a unei imobilizri se face prin anuiti constante (anuitatea secalculeaz raportnd valoarea de inventar a imobilizrii la durata de funcionare aacesteia).

Toate funciile financiare pentru calculul amortismentului fac apel la noiunea devaloare rezidual. Aceast noiune semnific valoarea ce va putea fi recuperat larevnzarea imobilizrii.

Fig. 1.89 Aplicaie pentru funcii financiare (NPV)Prin funcia financiar SLN, anuitile amortizrii lineare se calculeaz raportnddiferena dintre valoarea de inventar i valoarea rezidual la numrul de perioade ct afost estimat durata de funcionare a imobilizrii.

=VDB(valoare de inventar;valoare rezidual;durata normat defuncionare;debutul perioadei;sfritul perioadei[;rata de depreciere;comutator])calculeaz amortismentul degresiv ajustat (variable declining balance) al unei imobilizricu o valoare de inventar anume, o oarecare valoare rezidual; amortizabil pe mai muliani; cu o anumit rat de depreciere.

Amortismentul degresiv ajustat reprezint amortismentul contabil descresctor(amortismentul este mai mare pentru primele anuiti) pn ce anuitatea amortismentuluieste mai mic dect anuitatea ce corespunde amortismentului linear, iar de aici ncolosuma amortizabil este calculat linear.

Argumentele debutul i sfritul perioadei sunt utilizate pentru calculareaanuitilor incomplete de amortisment, adic plecnd de la o perioad cnd se ncepecalculul amortizrii, ctre ultima perioad pentru care se calculeaz amortizarea.

Rata de depreciere este un parametru care influieneaz amortizarea n sensulcreterii gradului de depreciere al imobilizrii.


28/33

Argumentul facultativ comutator permite sau trecerea automat de la amortizareadegresiv la amortizarea linear (valoare zero implicit) sau mpiedicarea acestei treceri(valoare unu).

Pentru exemplificare, furnizm urmtoarea aplicaie: o societate comercialefectueaz la nceputul anului o investiie de 10 milioane de lei, amortizabil n cinci ani.

Rata de depreciere pentru o astfel de investiie cu o durat de funcionare de 5 ani este de2. Pentru c investiia a fost finalizat la nceputul anului, aceasta este complet amortizatla sfritul celui de-al cincilea an.

Tabloul de amortizare aferent acestei imobilizri este prezentat n figura 1.90.Pentru fiecare an, perioadele de debut i sfrit sunt luate n calcul n coloanele C i

D. Primul an de amortizare a imobilizrii ncepe la 1 ianuarie (valoare 0) i dureaz pnla 31 decembrie (valoare 1) i aa mai departe pentru anii urmtori (intervalul fiind de 1).

Fig. 1.90 Tablou de amortizare (I)Se remarc faptul c ultimele dou anuiti sunt egale, metoda de calcul a

amortiztii trecnd automat de la procedeul degresiv la cel linear n anul patru.

Dac investiia ar fi fost pus n funciune la mijlocul anului, perioada de debut sfrit aferent primului an, ar fi fost 0 i 0,5 (celulele C112 i C113), iar dac investiiaar fi fost terminat la 1 aprilie, intervalul ar fi fost 0 i 0,75 (9/12 dintr-un an).

Dac tabloul de amortizare ar fi recalculat utiliznd comutatorul 1, calcululdegresiv ar fi fost complet, ultima tran de amortizare pentru anul 5 fcndu-se prindiferen, dup cum se observ i din figura 1.91.

Fig. 191 Tablou de amortizare (II)


29/33

=SYD(valoare de inventar;valoare rezidual;durat normat de funcionare;perioada pentru care se calculeaz amortizarea) returneaz amortismentul degresivabsolut, fr a corecta ultimele anuiti pentru amortizarea complet a investiiei.

Fig. 192 Aplicaii pentru funcii economice=DB(valoare de inventar;valoare rezidual;durat normat de funcionare;

perioada pentru care se calculeaz amortizarea;numrul de luni pe an defuncionare a imobilizrii)returneaz amortismentul degresiv absolut, innd cont denumrul de luni pe an de funcionare a imobilizrii, deci ia n calcul un posibil aspectsezonier de utilizare al acesteia. Cele dou funcii SYD i DB sunt calculate pentru o ratde depreciere egal cu 2.

=DDBcalculeaz amortizarea dup metoda softy i este o funcie asemntoare cuDB, cu excepia faptului c ultimul argument este un factor de multiplicare al amortizrii

degresive. Astfel, anuitile sunt calculate de o aa manier nct ultima anuitate este n,penultima anuitate este 2n, antepenultima este 3n i aa mai departe.Prezentm n figura 1.92 un exemplu edificator de utilizare a funciilor SLN, SYD,

DB i DDB.

1.4.2 FUNCII DEFINITE DE UTILIZATOR1[1](categoria UserDefined)

Funciile proprii sunt funcii definite de utilizator i care se comport n esen caorice funcie predefinit. Numele acestor funcii, dup ce au fost definite, apar n casetacorespunztoare categoriei User Defined, categorie creat n momentul definirii primei

funcii utilizator.Aceste funcii, odat definite, devin disponibile inclusiv prin asistentul de funcii,dar pot fi introduse i prin tastare direct n bara de formule.

Utilizatorul recurge la definirea de funcii proprii atunci cnd expresia de calculeste prea lung i trebuie s o utilizeze frecvent (deci prefer o funcie care s abreviezeexpresia de calcul respectiv) sau cnd conine calcule ce nu sunt posibil de efectuat doarcu ajutorul operatorilor utilizabili n formule.

1[1]se mai numesc i funcii proprii


30/33

Definirea unei funcii proprii se realizeaz cu ajutorul limbajului Visual Basic.Faptul c n lucrarea de fa nu este prezentat acest limbaj, nu ne mpiedic s artmmodul n care se poate defini o funcie. Se procedeaz astfel:

- se alege comanda Tools; Macro; Visual Basic Editor; Insert Module. nregistrul de lucru activ este inserat o foaie al crei nume implicit este Module. Este o

foaie de lucru Visual Basic i difer de foile de calcul att prin structur ct i princomenzile din bara de meniu. Foaia este inserat i deschis.- se poziioneaz cursorul n foaie i se tasteaz cuvntul Functionurmat dup un

spatiu de numele funciei i de lista parametrilor plasat ntre paranteze;- ncepnd cu linia urmtoare se tasteaz instruciunile necesare pentru efectuarea

prelucrrilor atribuite funciei;- ultima linie din definirea funciei trebuie s conin doar cuvintele obligatorii End

Function. Aici se ncheie procesul de definire a funciei.Pentru nelegerea procesului de definire a unei funcii proprii, furnizm urmtorul

exemplu: s se defineasc o funcie numit Spor, care pe baza salariului i a vechimiiunui angajat, s calculeze sporul de vechime ce i se cuvine. Se tie c algoritmul de

calculare a sporului de vechime este urmtorul:- pentru o vechime sub 3 ani nu se acord spor;- pentru o vechime ntre 3 si 5 ani sporul reprezint 5% din salariu;- pentru o vechime ntre 5 i 10 ani sporul este de 10% din salariu;- pentru o vechime ntre 10 si 15 ani sporul este 15% din salariu;- pentru o vechime > 15 ani sporul este 20% din salariu.Urmnd procedeul prezentat anterior, vom obine foaia Module (pe care am

redenumit-o sugestiv Funcii proprii) figura 1.93.

Fig. 193 Aplicaie funcii propriiSe procedeaz ca pentru orice alt funcie predefinit. Pentru exemplificare, vom

utiliza funcia Sporpentru a calcula sporul de vechime aferent angajailor oficiului decalcul.


31/33

Sursa de date este prezentat n figura 1.94.Pentru rezolvarea aplicaiei, se parcurg urmtorii pai:

Fig. 194 Sursa de date pentru aplicaie1. se deschide foaia de calcul care contine lista angajailor cu toate informaiile

aferente (marca, nume, prenume, vechime, salariu);2. se adaug listei o coloan intitulat spor vechime;3. n celula corespunztoare primului angajat se introduce formula de calcul printr-

una din metodele cunoscute:a) se tasteaz: =SPOR(F2;E2) unde F2, E2 sunt coordonatele corespunztoare

salariului i vechimii angajatului respectivb) se apeleaz asistentul de funcii. n prima fereastr a asistentului Paste

Functionse selecteaz din categoria User Defined, funcia Spor(figura 1.95a).

Fig. 195a Asistentul de funcii

n a doua fereastr a asistentului de funcii seprecizeaz valorile parametrilor (fig.1.95b)

Fig. 195b Fereastr parametri


32/33

Prin validarea formulei, n celul este afiat rezultatul calculului.Pentru a calcula sporul de vechime pentru toi angajaii se copiaz formula.

Rezutatul final este prezentat n figura 1.96.

Fig. 196Rezultatul aplicaiei cu funciiproprii
http://www.ase.ro/ciedd/birotica/CAP15.htmhttp://www.ase.ro/ciedd/birotica/excel-cuprins.htmhttp://www.ase.ro/ciedd/birotica/CAP13.htm


33/33

Excel4-Utilizarea Functiilor Excel

Documents

Transcript of Excel4-Utilizarea Functiilor Excel