+ All Categories
Home > Documents > Functii Excel

Functii Excel

Date post: 27-Dec-2015
Category:
Upload: alexya89ph
View: 75 times
Download: 4 times
Share this document with a friend
70
Referate Meniu Astronomie Biologie Chimie Desen Diverse Drept Economie Engleza Filozofie Fizica Franceza Geografie Germana Informatica Istorie Italiana Marketing Matematica Medicina Muzica Psihologie Romana Romana1 Spaniola Excel - Utilizarea functiilor Excel p UTILIZAREA FUNCºIILOR EXCEL Procesorul de tabele Excel include un numar mare de functii predefinite (232), dar ofera si posibilitatea ca utilizatorul sa-si defineasca propriile functii, potrivit cerintelor de exploatare a aplicatiilor. Functiile Excel permit efectuarea de calcule si prelucrari diverse, de la cele mai simple pana la cele mai complexe. 1.4.1 FUNCºII PREDEFINITE Functiile predefinite reprezinta formule speciale care respectand o anume sintaxa, executa operatii si prelucrari specifice, fiind destinate rezolvarii unor probleme si aplicatii ce contin elemente predefinite de calcul. Unele functii predefinite sunt echivalente formulelor: de
Transcript
Page 1: Functii Excel

 

 

Referate Meniu

Astronomie

Biologie

Chimie

Desen

Diverse

Drept

Economie

Engleza

Filozofie

Fizica

Franceza

Geografie

Germana

Informatica

Istorie

Italiana

Marketing

Matematica

Medicina

Muzica

Psihologie

Romana

Romana1

Spaniola

Home Referatele

Ultimele

 

Excel - Utilizarea functiilor Excel

p

 

 

UTILIZAREA FUNCºIILOR EXCEL

 

 

Procesorul de tabele Excel include un numar mare de functii predefinite (232), dar ofera si posibilitatea ca utilizatorul sa-si defineasca propriile functii, potrivit cerintelor de exploatare a aplicatiilor.

Functiile Excel permit efectuarea de calcule si prelucrari diverse, de la cele mai simple pana la cele mai complexe.

1.4.1 FUNCºII PREDEFINITE

 

Functiile predefinite reprezinta formule speciale care respectand o anume sintaxa, executa operatii si prelucrari specifice, fiind destinate rezolvarii unor probleme si aplicatii ce contin elemente predefinite de calcul.

Unele functii predefinite sunt echivalente formulelor: de exemplu, formula de adunare a continutului celulelor A1, A2 si A4, adica =A1+A2+A4 este echivalenta cu functia =Sum(A1:A2;A4). Alte functii (majoritatea cazurilor) nu au echivalent in randul formulelor, rezultatul scontat neputand fi obtinut decat prin aplicarea functiilor predefinite sau putand fi obtinut pe cale obisnuita, prin aplicarea succesiva a mai multor operatii si formule.

Folosirea functiilor predefinite este supusa unor reguli foarte stricte, a caror nerespectare

Page 2: Functii Excel

Referate

Gestiunea fiscala a intreprinderii

Lucrare de licenta psihologie

Alte lucrari de diploma

Plati prin carduri

Publicitate

poate conduce la un rezultat incorect sau generator de eroare.

Cea mai mare parte a functiilor predefinite au trei componente:

- semnul "egal"= (sau semnul “plus” +, pentru compatibilitate cu 1-2-3);

- numele functiei;

- unul sau mai multe argumente;

Nici un spatiu nu este admis ca separator intre cele trei componente ale functiilor predefinite. Argumentele se afla inchise intre paranteze rotunde si sunt separate printr-un separator zecimal. Acest separator poate fi virgula sau punct si virgula, dupa cum a fost configurat initial sistemul. ¥n exemplele luate, se va lua in consideratie ca separator zecimal caracterul "punct si virgula".

Exista si functii care nu au nevoie de precizarea argumentului, de exemplu:=NOW(), =TRUE(), =TODAY(), etc.

Exemplul urmator ilustreaza diferite argumente care se pot intalni la o functie predefinita:

 

 

Functie predefinita Tip argument

 

=SUM(A2:A7) plaja continua de celule

=SUM(A2:A7;A9;A11:A20) plaja discontinua de celule

=MAX(59;36;84) lista de valori

=DATE(62;10;18) lista de valori data calendaristica

=IF(A1=A2;"Bun";Rau") valoare logica

=INT(SUM(D1:D9) functie predefinita

=UPPER("Ionescu") sir de caractere

Page 3: Functii Excel

=REPT("Ionescu",3) sir si valoare numerica

=FACT(6) valoare numerica

 

Excel accepta urmatoarele tipuri de argumente:

- o conditie: este o expresie logica care foloseste unul din operatorii logici =, <, >, <>, <=, >=, NOT( ), AND( ), OR( ) pentru o adresa de celula sau un nume de camp. Conditia argumentului poate fi deci o formula, un numar, un nume de camp, un text. Functia evalueaza conditia si procedeaza la diferite operatii in functie de faptul daca conditia este adevarata sau falsa.

- o locatie: este o adresa, un nume de camp, o formula sau functie care genereaza o adresa sau un nume de camp.

- un text: orice secventa de caractere inclusa intre ghilimele, adresa sau un nume de camp ce contine o eticheta tip sir de caractere sau o formula sau functie care returneaza o eticheta. Un sir de caractere folosit intr-o functie trebuie pus intre ghilimele pentru a nu fi confundat cu un nume de camp.

- o valoare: un numar, adresa sau numele unei celule care contine un numar, o formula sau functie predefinita care returneaza un numar.

Toate tipurile de argumente pot fi folosite impreuna intr-o functie atunci cand sintaxa este respectata.

O functie predefinita se poate introduce intr-o celula tastand-o ca atare (conform sintaxei) sau prin intermediul generatorului de functii.

Cea mai simpla metoda o reprezinta introducerea nemijlocita a functiilor predefinite, corespunzator sintaxei, in celula unde se va opera calculul respectiv (metoda recomandata).

¥n cel de-al doilea caz, se activeaza selectorul functiilor predefinite aflat pe bara de editare sau se activeaza comanda Insert Function (figura 1.52). Apoi, se alege functia respectiva, din caseta de dialog Paste Function, se valideaza si se completeaza sintaxa generata automat.

Fig 1.52 Etapele inserarii unei functii

Apelarea selectorului de functii se face prin apasarea butonului = (egal) aflat pe bara de editare, dupa care se deschide lista functiilor predefinite, se alege functia dorita, dupa care se completeaza interactiv argumentele.

Page 4: Functii Excel

 

 

Fig. 1.53/1.54 Asistentul de functii/Exemple de date

Acest procedeu este prezentat in figura 1.53

Functia poate fi aleasa din lista functiilor cele mai utilizate (Most Recently Used), din lista tuturor functiilor disponibile ordonate alfabetic (All), sau din categoriile de functii specializate (Financial, Date & Time, Math & Trig, Statistical ...)

¥n celula din care s-a apelat functia predefinita va apare sintaxa functiei selectate si validandu-se operatia prin butonul OK se va genera rezultatul respectivei functii.

Generatorul de functii sau mai corect asistentul de functii este prezentat in figura 1.53.

De regula, utilizarea asistentului de functii presupune parcurgerea a doi pasi:

- pasul 1 semnifica alegerea tipului de functie;

- pasul 2 presupune completarea interactiva a sintaxei functiei respective conform exemplului prezentat in figura 1.54.

 

Categorii de functii predefinite

Excel poseda un set impresionant de functii predefinite, in numar de 232, grupate pe tipuri potrivit utilitatii acestora la rezolvarea diferitelor probleme.

Astfel, consideram suficienta in rezolvarea aplicatiilor EXCEL, prezentarea a celor mai importante 99 de functii predefinite, grupate pe urmatoarele categorii (figura 1.55):

Fig. 1.55 Categorii de functii

1. 1.      functii matematice si trigonometrice (Math & Trig): permit efectuarea de calcule matematice simple si complexe;

2. 2.      functii statistice (Statistical): permit efectuarea unor calcule statistice utilizand serii de valori;

3. 3.      functii de informare (Information): afiseaza informatii despre celule si campuri;

4. 4.      functii logice (Logical): determina valoarea de adevar sau

Page 5: Functii Excel

de fals - corespunzator unei conditii;5. 5.      functii baza de data (Database): efectueaza diferite calcule

asupra unor rubrici, intr-o baza de date, corespunzator unor criterii definite;

6. 6.      functii de cautare si consultare (Lookup & Reference): permit localizarea continutului unei celule;

7. 7.      functii calendar sau data calendaristica (Date & Time): manipuleaza numere care reprezinta date calendaristice sau timp;

8. 8.      functii text sau sir de caractere (Text): ofera informatii legate de textul existent in celule si permit operatii cu etichete;

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

¥n continuare, prezentam cele mai importante functii predefinite, precizand ca cea mai mare a parte a lor sunt perfect compatibile ca sintaxa si ca semnificatie cu functiile arond aferente procesorului de tabele LOTUS 1-2-3.

 

1.4.1.1 FUNCºIILE MATEMATICE ªI TRIGONOMETRICE

 

Functiile matematice si trigonometrice (Math & Trig) permit efectuarea diferitelor calcule, de la cele mai simple la cele mai complexe, pentru rezolvarea de aplicatii ce solicita instrumente matematice si trigonometrice de uz curent.

Fig. 1.56 Functia SUM

=SUM(lista) aduna valorile dintr-o lista precizata ca argument.

Lista poate contine capuri continue sau discontinue referite prin adrese (coordonate) sau prin nume de camp(uri).

Functia de insumare este completata - spre usurinta utilizatorului - cu butonul Auto Sum. Functia generata de butonul respectiv insumeaza pe linie sau pe coloana valori adiacente (valorile nu trebuie sa fie intrerupte in succesiunea lor de celule vide sau de celule care sa contina texte). Auto-insumarea opereaza astfel pe linie sau pe coloana pana acolo unde se intalneste primul semn de discontinuitate (figura 1.56).

Pot exista mai multe cazuri (exemplificate in figura 1.56):

Page 6: Functii Excel

-   se plaseaza cursorul acolo unde se doreste a se calcula suma (eventual selectand o plaja de celule pe linie sau o coloana unde sa se depuna rezultatele

-   calculelor) si se activeaza butonul AutoSum prin dublu-click;

Fig. 1.57 Functia AutoSum

- se selecteaza plaja de celule de insumat, inclusiv zona unde se vor plasa rezultatele insumarii (o linie mai jos si/sau o coloana mai la dreapta), dupa care se activeaza butonul AutoSum prin dublu-click.

=PRODUCT (lista) multiplica valorile continute intr-o lista. Un exemplu edificator este prezentat in figura 1.58.

=SUBTOTAL(referinta-tip;camp de regrupat) calculeaza un rezultat ce provine dintr-o grupare a datelor operand diferite operatii specifice (conform referintelor-tip) asupra unui camp de regrupat.

 

Exemple de referinte-tip ar fi:

1 AVERAGE Medie Fig. 1.58 Functia PRODUCT

2 COUNT Numara

4 MAX Maximum

5 MIN Minimum

6 PRODUCT Produs

9 SUM Suma

 

¥n exemplul prezentat in figura 1.59 se calculeaza suma (referinta-tip 9) valorilor produselor vandute pe 01-Iul-98 (campul de regrupat este E31:E33).

=SUMPRODUCT(lista) multiplica valorile situate in celulele corespondente, aferente unor serii de campuri, iar apoi aduna rezultatele obtinute. ¥n exemplul prezentat in figura urmatoare se calculeaza prin functia SUMPRODUCT valoarea totala a vanzarilor, adica suma dintre produsele cantitatilor (C31:C36) si preturilor (D31:D36).

Page 7: Functii Excel

=SUMIF(camp de evaluat; criteriu; camp de insumat) aduna continutul celulelor potrivit unui criteriu dat.

¥n exemplul din figura 1.59 se calculeaza prin functia SUMIF, suma

Fig. 1.59 Functiile SUMTOTAL, SUMPRODUCT, SUMIF

comisioanelor la vanzarile de produse (5% din valoare) pentru valorile vandute de peste 10.000.000 lei. ¥n acest caz campul de evaluat reprezinta valoarea (E31:E36), criteriul este de tip text si anume “>10000000”, iar campul de insumat este comisionul (F31:F36).

=ROMAN(numar;format) converteste numerele din format cifric arab in text ce semnifica numere cu format cifric roman. Formatul –cu valori de la 0 la 4- reprezinta gradul de concizie al numarului roman nou generat. Numarul arab de transformat trebuie sa fie intreg. Un exemplu de astfel de transformare este prezentat in figura 1.60.

=RAND() returneaza un numar aleator cuprins intre 0 si 1;

=ABS(numar) returneaza valoarea absoluta dintr-un numar;

=LN(numar) calculeaza logaritmul natural

Fig. 1.60 Functia ROMAN

al unui numar specificat ca argument;

=LOG(numar;baza) returneaza logaritmul unui numar intr-o baza specificata;

Fig. 1.61 Functii trigonometrice si POWER si SQRT

=LOG10(numar) returneaza logaritmul in baza 10 dintr-un numar;

=EXP(X) calculeaza baza logaritmului natural ridicata la puterea X. Baza este o constanta si are valoarea 2,7182818….;

Fig. 1.62 Functii matematice

=MOD(X;Y) calculeaza restul impartirii argumentului X la arg. Y;

=FACT(numar) calculeaza factorialul unui numar pozitiv;

=POWER(numar;putere) returneaza rezultatul unui numar ridicat la putere (figura 1.61);

=SQRT(numar) calculeaza radacina patrata a argumentului;

Page 8: Functii Excel

=SIN(X) returneaza valoarea argumentului X in radiani;

=COS(X) calculeaza cosinusul argumentului X in radiani ;

=TAN(X) calculeaza tangenta argumentului X in radiani;

=ASIN(X) calculeaza arc-sinusul argumentului X in radiani (similar =ACOS(X) si =ATAN(X);

=DEGREES(unghi) converteste radianii in grade;

=RADIANS(unghi) converteste grade in radiani;

=ROUND(X,numar de zecimale) rotunjeste argumentul numeric X la un

Fig. 1.63 Functia de rotunjire

numar specificat de zecimale;

=PI() returneaza valoarea numarului PI;

=INT(numar) afiseaza partea intreaga a argumentului (a numarului real), fara a-l rotunji.

1.4.1.2 FUNCºIILE STATISTICE

Functiile statistice (Statistical) permit efectuarea de calcule statistice utilizand serii de valori:

=MAX(lista) returneaza cea mai mare valoare din lista. Lista poate fi compusa din: numere, formule numerice, adrese sau nume de campuri;

=MIN(lista) returneaza cea mai mica valoare din lista;

=AVERAGE(lista) calculeaza media valorilor din lista;

=GEOMEAN(lista) calculeaza media geometrica a valorilor dintr-o lista

=HARMEAN(lista) calculeaza media armonica a valorilor dintr-o lista;

=MEDIAN(lista) calculeaza valoarea mediana dintr-o lista;

=COUNT(lista) numara celulele ocupate dintr-o lista de campuri;

Exemplul din figura 1.64 ilustraza utilizarea functiilor statistice prezentate:

Page 9: Functii Excel

Fig. 1.64 Functii statistice

 

1.4.1.3 FUNCºIILE DE INFORMARE 34965iqe48plk6q

Functiile de informare (Information) afiseaza informatii referitoare la celule si campuri:

=ISBLANK(X) determina daca X sau amplasamentul definit de argumentul X este sau nu o celula vida. Functia returneaza TRUE -valoarea logica de adevar- daca amplasamentul este o celula vida si FALSE –valoarea logica de fals- in caz contrar;

=ISNUMBER(X) verifica daca X contine o valoare numerica. Functia returneaza TRUE – adevarat- daca X contine un numar, altfel returneaza FALSE sau fals. Argumentul X poate fi o valoare, o adresa, text sau o conditie);

=ISTEXT(X) verifica daca X contine un sir de caractere, returnand dupa caz TRUE sau FALSE.

=ISNONTEXT(X) verifica daca X nu contine un sir de caractere, returnand dupa caz TRUE sau FALSE.

=ISLOGICAL(X) verifica daca argumentul X contine o valoare de tip logic returnand dupa caz TRUE sau FALSE.

=ISERROR(X) verifica daca argumentul X contine o valoare de tip eroare, returnand dupa caz TRUE sau FALSE.

O parte din functiile de informare sunt exemplificate impreuna cu functiile logice.

 

1.4.1.4 FUNCºIILE LOGICE

 

Functiile logice (Logical) determina evaluarea unor expresii si in functie de acestea furnizeaza actiuni sau rezultate complexe, generand valori de adevar sau de fals - corespunzator unor conditii (acestea pot fi evaluate si inlantuite cu ajutorul operatorilor logici AND, OR, NOT).

=IF(conditie;X;Y) testeaza argumentul conditie si in functie de rezultatul evaluarii logice, genereaza argumentul X daca conditia este adevarata sau argumentul Y daca aceasta este falsa.

Page 10: Functii Excel

Argumentele X sau Y pot fi valori, siruri de caractere (plasate intre ghilimele), nume de campuri sau adrese de celule sau campuri care contin aceste valori. ¥n locul argumentelor X sau Y se pot imbrica alte structuri conditionale IF, generandu-se potrivit conditiilor ulterioare, X1,Y1 sau X2,Y2 si asa mai departe.

=AND(evaluare logica1,evaluare logica2,...) returneaza valoarea logica TRUE daca toate argumentele sunt adevarate si valoarea logica FALSE daca unul sau mai multe argumente sunt false;

=OR(evaluare logica1,evaluarea logica2,...) returneaza valoarea logica TRUE daca orice argument este adevarat si valoarea logica FALSE daca toate argumentele sunt false;

=NOT(evaluarea logica) inverseaza valoarea argumentului, returnand dupa caz TRUE sau FALSE;

=TRUE() returneaza valoarea logica TRUE;

=FALSE() returneaza valoarea logica TRUE;

Pentru exemplificarea functiei logice IF, furnizam urmatoarea aplicatie pentru calculul impozitului pe salariile colaboratorilor angajati cu Conventie Civila de Prestari Servicii:

Astfel, daca salariul brut este sub 500.000 lei, impozitul este de 10% din brut, altfel, daca salariul brut este cuprins intre 500.000 lei si 1.500.000 lei, impozitul este de 50.000 lei + 20% din ceea ce depaseste 500.000 lei salariu brut, daca salariul brut este cuprins intre 1.500.000 lei si 2.500.000 lei, impozitul este de 250.000 lei + 25% din ceea ce depaseste 1.500.000 lei salariu brut, daca salariul brut este cuprins intre 2.500.000 lei si 3.500.000 lei, impozitul este de 500.000 lei + 30% din ceea ce depaseste 2.500.000 lei salariu brut, daca salariul brut depaseste 3.500.000 lei, impozitul este de 800.000 lei + 40% din ceea ce depaseste 3.500.000 lei salariu brut.

Aplicatia este astfel construita incat sa exemplifice (didactic) toate functiile logice (figura 1.65, 1.66).

¥ntr-un prim pas s-a construit o coloana de “Evaluare logica” care returneaza in functie de un test facut asupra salariului brut, valoarea logica de fals (FALSE) daca salariul brut este text, blank sau este mai mic ca zero si returneaza valoarea logica de adevar (TRUE) in caz contrar.

Fig. 1.65 Functii logice (I)

 

 

Page 11: Functii Excel

 

 

 

 

 

¥ntr-un al doilea pas se calculeaza impozitul pe salarii daca coloana “Evaluare logica” (s-a utilizat functia NOT()) nu contine valoarea logica FALSE.

¥n aplicatia de mai sus s-a construit o structura conditionala imbricata, unde s-a exemplificat intr-o ramura IF si functia logica AND.

Fig. 1.66 Functii logice(II)

1.4.1.5 FUNCºIILE BAZÅ DE DATE

Functiile baza de date (Database) returneaza actiuni - valori sau etichete (suma;medie;maxim;minim; cauta;numara) dintr-un camp de date - corespunzator unei baze de date, dupa o anumita rubrica, conform unui criteriu de selectie

Functiile tip baza de date au in mod invariabil aceeasi lista de argumente:

-   baza de date: reprezinta tabelul Excel sub forma unui camp de date, de unde informatia va fi consultata sau extrasa;

-   rubrica: semnifica atributul sau proprietatea asupra caruia opereaza calculul facut de functia tip baza de date. Rubrica poate fi identificata prin numele sau sau prin numarul de ordine al acesteia in cadrul bazei de date;

-   camp de criterii: reprezinta unul sau mai multe campuri continue in care se pot preciza restrictiile, care se regrupeaza in criterii de selectie la care trebuie sa raspunda interogarea respectiva.

Principalele functii tip baza de date sunt:

=DSUM(baza de date;rubrica/nr.rubrica;camp de criterii) returneaza suma valorilor unei rubrici aferente unei baze de date, care raspunde unui criteriu de selectie;

Page 12: Functii Excel

=DMAX(baza de date;rubrica/nr.rubrica;camp de criterii) returneaza cea mai mare valoare dintr-o rubrica aferenta unei baze de date, corespunzator unui criteriu de selectie;

Fig. 1.67 Aplicatie pentru functiile baza de date

=DMIN(baza de date;rubrica/nr.rubrica;camp de criterii) returneaza cea mai mica valoare dintr-o rubrica aferenta unei baze de date, corespunzator unui criteriu de selectie;

=DAVERAGE(baza de date;rubrica/nr.rubrica;camp de criterii) calculeaza media valorilor unei rubrici aferente unei tabele - pentru o baza de date, potrivit criteriului de selectie specificat=DCOUNT(baza de date;rubrica/nr.rubrica;camp de criterii) numara celulele ocupate intr-o tabela baza de date, conform unor criterii specificate;

=DGET(baza de date;rubrica/nr.rubrica;camp de criterii) returneaza continutul unei rubrici pentru o baza de date, corespunzator unui criteriu specificat. Functia este utila pentru a regasi o informatie unica; Exemple edificatoare de utilizare a functiilor tip baza de date sunt ilustrate in figura urmatoare, urmand ca alte aplicatii mai complexe sa fie prezentate in detaliu in capitolul ce trateaza bazele de date create si exploatate sub Excel.

Pornind de la un tabel definit pe coordonatele A6:H17, considerat a fi o baza de date care repertoriaza facuturile emise de o firma catre clientii sai, se pot pune in evidenta cu ajutorul functiilor tip baza de date, informatii calculate potrivit unor interogari specifice.

Prezentam in figurile 1.67- 1.68 cateva exemple de utilizare a functiilor tip baza de date, utilizand campuri de criterii definite de utilizator potrivit unor cerinte de interogare.

 

6. 1.4.1.6  FUNCºIILE DE CÅUTARE ªI CONSULTARE

Functiile de cautare si consultare (Lookup & Reference) permit cautarea, identificarea si referirea continutului unor celule:

Fig. 1.68 Modul de utilizare a functiilor baza de date

=CHOOSE(index-numeric;lista de valori) returneaza in urma unei alegeri dintr-o lista de valori, o actiune sau o valoare, ce urmeaza a fi activata sau executata,

corespunzator unui index numeric. Indexul numeric determina care valoare (de tip text, numerica sau referinta celulara) din lista de argumente va fi selectata. Indexul este un numar cuprins intre 0 si 29.

=COLUMN(referinta celulara sau camp) returneaza numarul colanei corespunzatoare

Page 13: Functii Excel

referintei celulare sau numarul primei coloane pentru campul specificat;

=COLUMNS(camp) returneaza numarul de coloane aferente campului specificat ca argument;

=ROW(referinta celulara sau camp) returneaza numarul liniei corespunzatoare referintei celulare sau numarul primei linii a campului specificat ca argument;

=ROWS(camp) returneaza numarul de linii pe care il ocupa campul specificat ca argument;

=AREAS(referinta celulara) indica numarul de zone contigue dintr-un camp. Daca respectivul camp contine mai multe zone contigue, atunci argumentul se mai inchide intr-o paranteza suplimentara;

¥n figura 1.69 sunt prezentate mai multe exemple de utilizare a functiilor enumerate mai sus.

Fig. 1.69 Aplicatie pentru functiile de cautare si consultare

=VLOOKUP(cheie;camp de consultare;coloana de recuperat) returneaza continutul unei celule ce figureaza intr-o coloana dintr-un tablou de consultare verticala.

Sintaxa functiei de consultare verticala admite trei argumente si anume:

-   cheie: reprezinta valoarea dupa care are loc cautarea sau consultarea, (adresa absoluta/relativa sau nume de camp);

-   camp (sau tabel) de consultare: este campul asupra caruia opereaza consultarea prin cautarea valorii cheii precizate anterior;

-   coloana de recuperat: este numarul coloanei (numerotarea incepe cu 1) de unde va fi recuperata informatia gasita in tabelul de consultare, corespunzator valorii cheii de cautare.

¥n mod obligatoriu tabelul de consultare va fi sortat crescator dupa coloana care contine valorile cheii de consultare (comanda Data Sort, iar in rubrica Sort by se va preciza numarul sau numele coloanei dupa care se va face sortarea)

=HLOOKUP(cheie;camp de consultare;linie de recuperat) returneaza continutul unei celule ce figureaza intr-o anumita linie a unui tablou de consultare orizontala.

Argumentul cheie (sub forma unei referinte celulare sau nume de camp) va fi cautat in prima linie a campului de consultare, iar daca valoarea va fi gasita pe un numarul de linie

Page 14: Functii Excel

precizat de ultimul argument, valoarea respectiva va fi returnata de functia HLOOKUP.

¥n mod obligatoriu tabelul de consultare orizontala trebuie sortat dupa valorile crescatoare ale cheii de consultare aflate in prima linie (sortare de la stanga la dreapta). Daca valorile cheii nu sunt sortate, se va selecta tabelul de consultare si se va activa comanda de sortare (de la stanga spre dreapta): Data Sort, butonul Option si din rubrica Orientation se alege optiunea Sort left to right.

Daca informatia cautata in tabelul de consultare verticala sau orizontala nu va fi gasita, se va returna cea mai apropiata valoare (pe vericala sau pe orizontala) de cheia de consultare.

Pentru exemplificarea celor doua functii de consultare propunem urmatoarea aplicatie:

O societate comerciala de distributie intocmeste, cu ajutorul procesorului de tabele EXCEL, facturi pentru livrarile efectuate.

Optional, respectiva societate efectueaza si transportul marfii comandate la domiciliul clientului, firma practicand tarife diferentiate in functie de cantitatea transportata (in tone) si de orasul de destinatie.

Tarifele de transport sunt grupate intr-un tablou in functie de destinatie (prima linie) si de cantitatea transportata (prima coloana). Tabloul care urmeaza a fi considerat tabel de consultare orizontala a fost definit pe coordonatele F20:J28 (figura 1.70) si a fost in prealabil sortat de la stanga la dreapta dupa prima linie, adica dupa destinatie.

Fig. 1.70 Date pentru aplicatia de cautare.

Firma isi are inregistrati clientii intr-o baza de date (definita pe coordonatele E1:I7) (figura 1.71) care regrupeaza elementele de identificare ale acestora (“Client”, “Adresa”, “Localitate”, “Cod fiscal”, “Cont bancar”).

¥n egala masura exista si o alta baza de date –definita pe coordonatele A20:C28 (figura 1.72) sub forma unui nomenclator de preturi pentru fiecare produs in parte. Cele doua baze de date sunt sortate dupa valorile crescatoare ale primei coloane si contin informatii pertinente ce concura la realizarea automata a facturii.

Fig. 1.71 Date pentru aplicatia de cautare

Factura procesata cu Excel are urmatoarea forma (figura 1.73):

Utilizatorul va introduce prin tastare, pentru completarea facturii doar denumirea clientului, codul produsului facturat, cota de adaos comercial, cantitatea livrata, iar optional daca se doreste sau nu transport, precum si destinatia transportului. ¥n rest toate operatiile sunt facute automat cu ajutorul formulelor si a functiilor Excel.

Page 15: Functii Excel

Factura se proceseaza in mod obisnuit, incepand a se calcula intr-un prim timp "Valoarea", “Majorarile”, "TVA-ul" si "Valoarea facturata". La

Fig. 1.72 Nomenclatorul de preturi

calculul "Valorii" se va lua in calcul si o cota variabila de adaos comercial (celula D10 a fost fixata cu adresa absoluta -$D$10- pentru a nu se decala la

Fig. 1.73 Factura obtinuta

copierea formulei ce calculeaza valoarea), precum si cheltuielile de transport. “Majorarile” de intarziere se pot calcula pe transe, prin structuri conditionale imbricate. “TVA”-ul reprezinta 22% din “Valoare” + “Majorari”, iar “Valoarea facturii” reprezinta suma dintre “Valoare”, “Majorari” si “TVA”.

¥ntr-un al doilea timp se pot calcula totalurile pe rubricile procesate anterior utilizand clasica functie SUM.

Interesante de prezentat sunt facilitatile de consultare verticala si orizontala.

La tastarea numelui de client in celula C3, se vor recupera automat dintr-un tabel de consultare verticala (definit anterior pe coordonatele E1:I7), informatiile legate de acest identificator si anume: “Adresa”, “Localitatea”, “Codul fiscal” si “Contul bancar”.

Astfel in celula C4 s-a scris formula de consultare verticala (VLOOKUP) (figura 1.74) pentru recuperarea adresei clientului, anume: “se cauta cheia de consultare (celula $C$3-Client) in tabelul de consultare definit pe coordonatele $E$1:$I$7 si in caz ca valoarea este gasita, se va recupera informatia din coloana 2, corespunzatoare cheii de consultare”.

Coordonatele cheii si tabelului de consultare au fost blocate prin utilizarea de adrese absolute pentru ca formula ce contine consultarea verticala sa poata fi copiata fara ca respectivele coordonate sa se decaleze.

 

Fig. 1.74 Functia de consultare verticala ql965i4348pllk

Cheia de consultare fiind in acest caz de tip text nu trebuie sa aiba valori vide si nici numerice. Pentru aceasta, procedura de consultarea verticala a fost completata cu teste facute asupra celulei care contine cheia de consultare ($C$3). Daca cheia are valoarea vida “ISBLANK($C$3) sau (OR()) daca contine o valoare alta decat text “ISNONTEXT($C$3), atunci se va afisa un spatiu (“”), altfel se va face consultarea verticala.

¥n aceste conditii, consultarea verticala va avea urmatoarea forma:

Page 16: Functii Excel

=IF(OR(ISBLANK($C$3);ISNONTEXT($C$3));””;VLOOKUP($C$3;$E$1:$H$7;3)), fapt ilustrat si in figura 1.75.

Fig. 1.75 Functia de consultare verticala

Daca se tasteaza un client care nu exista in nomenclatorul de clienti (in tabelul de consultare verticala), functia VLOOKUP nu va semnala lipsa informatiei din tabel ci va returna informatia legata de cea mai apropiata valoare a cheii de consultare. De exemplu, daca s-ar introduce clientul cu numele “Sarmis”, se vor recupera prin VLOOKUP informatiile aditionale corespunzatoare celei mai apropiate valori ale cheii, adica informatiile legate de clientul “Star”. Functia VLOOKUP nu va semnala inexistenta cheii de consultare “Sarmis”. Pentru inlaturarea acestui neajuns, procedura de consultare verticala a fost completata cu un test de existenta a cheii ce consultare in tabelul de consultare”.

Acest test de existenta verifica daca valoarea cheii de consultare este gasita in prima coloana a tabelului de consultare. Daca valoarea respectiva exista in tabel inseamna ca s-a gasit cheia de consultare si in consecinta consultarea verticala se va efectua returnand un rezultat corect, altfel se va afisa spatiu sau zero (ultimul caz folosindu-se daca celula respectiva participa ulterior la calcule) sau un mesaj de genul “cheie inexistenta”.

Formula de testare a existentei cheii de consultare in tabel este urmatoarea:

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

bancar” –C7- se vor recupera prin acelasi procedeu de consultare verticala, recuperandu-se dupa caz, prin functia VLOOKUP continutul coloanelor 2, 3, 4, si 5, corespunzator valorilor cheii de consultare declarate la adresa $C$3 (figura 1.76).

¥n mod asemanator se procedeaza si cu a doua consultare verticala, anume: in momentul tastarii "Codului de produs" este consultat vertical tabelul "PREºURI" declarat la adresa A20:C28, si daca in tabelul respectiv este gasita cheia de consultare "Cod produs" - se vor recupera automat: continutul coloanei 2 si 3 din tablou, adica "Denumire produs" si "Pret".

Fig. 1.77 Aplicatie de consultare verticala

In figura 1.77 este prezentata procedura completa (cu teste facute asupra celulei ce contine cheia de consultare si cu test de existenta a valorii cheii in tabelul de consultare) de extragere a denumirii produsului, prin consultare verticala. Similar se procedeaza pentru extragerea pretului din tablou, corespunzaror valorilor luate de codul produsului.

Consultarea orizontala a tabelului declarat pe coordonatele $F$20:$J$28 are loc dupa

Page 17: Functii Excel

valorile luate de cheia de consultare - $G$10 “Destinatia”. Daca cheia este gasita in tablou, se va recupera numarul de linie care va contine valoarea cheltuielilor de transport corespunzatoare destinatiei specificate.

¥n exemplul prezentat in figura 1.78, s-a operat un mic artificiu, anume "Cantitatea livrata" coincide logic cu numarul de linie de recuperat orizontal din tablou (astfel, nu s-a precizat numarul liniei recuperate, ci celula care contine livrata, aflata la adresa C13). Datorita faptului ca procedura de consultare orizontala este operationala incepand cu linia 1 (care contine invariabil titlurile rubricilor aferente destinatiei), celula “Cantitatea livrata” va indica numarul liniei de recuperat si va avea valoarea incrementata cu o unitate pentru a exista o concordanta intre valorile luate de aceasta si numarul liniei de recuperat. Daca nu s-ar fi operat acest artificiu, numarul liniei de recuperat ar fi decalat cu o unitate (adica, daca celula C13 – “Cantitatea livrata” – ar fi avut valoarea 3, s-ar fi recuperat linia numarul 3 din tabel –prima linie contine titlul rubricilor-, adica valoarea cheltuielilor de transport aferente pentru 2 tone transportate)

Prin functia HLOOKUP s-a consultat deci respectivul tablou, cautandu-se valoarea luata de “Destinatie” in celula $G$10, recuperandu-se numarul de linie ce corespunde logic cu “Cantitatea livrata”.

Tabloul de consultare trebuie in mod obligatoriu sortat alfabetic dupa prima linie a sa.

Procedura de consultare orizontala poate fi completata si astfel imbunatatita (celula D14) prin urmatoarele teste:

- un test facut asupra celulelor ce contin: “Destinatia” (sa nu fie valoare vida sau numerica) si “Cantitatea livrata” (sa nu fie valoare de tip text, vida sau zero):

=IF(OR(ISNONTEXT($G$10),ISBLANK($G$10),ISTEXT(C14),ISBLANK(C14),C14=0;0;IF(……) . Daca cel putin unul din argumente este adevarat, functia va returna valoarea zero, altfel se vor testa si alte conditii de indeplinit;

Fig. 1.78 Aplicatie de consultare orizontala

- un test de existenta a “Destinatiei” (celula $G$10) in prima linie a tabloului de consultare orizontala: =IF(OR(…;$G$10<>HLOOKUP($G$10;$F$20:$J$28;1));0;…..). Daca “Destinatia” este inexistenta in tablou, functia va returneaza valoarea zero, altfel se procedeaza la consultarea propriu-zisa;

- un test de existenta a “Cantitatii livrate” in prima coloana a unui tablou de consultare verticala:

=IF(OR(C14<>VLOOKUP(C14;$E$20:$E$28;1);…….). Daca nu exista valoarea unei cantitati livrate in tabloul de consultare verticala definit pe coordonatele $E$20:$E$28, functia

Page 18: Functii Excel

returneaza zero, altfel se procedeaza la consultarea propriu-zisa:

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

- procedura ar putea fi completata si cu un test de efectuare a transportului: astfel, daca transportul este facut de furnizor (celula $D$8 are valoarea “da”), atunci se procedeaza la testele de mai sus si se executa in final consultarea orizontala, altfel cheltuielile de transport vor fi zero.

Figura 1.79 indica corespondentele creeate intre diferitele campuri, in procesul de consultare verticala si orizontala.

 

Fig. 1.79 Consultarea verticala si orizontala. Corespondente

 

7. 1.4.1.7  FUNCºIILE TIP DATÅ CALENDARISTICÅ ªI ORÅ

Functiile tip data calendaristica si ora (Date & Time) manipuleaza si opereaza calcule cu valori numerice ce reprezinta date calendaristice sau timp:

=NOW() returneaza un numar corespunzator datei curente - cu zecimale ce reprezinta ora;

=TODAY() returneaza un numar-data corespunzator datei curente;

=DATEVALUE("sir de caractere") calculeaza numarul-data corespunzator sirului de caractere in format data calendaristica (sirul trebuie plasat intre ghilimele);

=DATE(an;luna;zi) calculeaza numarul-data pentru data calendaristica specificata ca argument;

=YEAR(numar-data) returneaza corespunzator anului, un numar cuprins intre 0 (1900) si 199 (2099) - extragand rezultatul dintr-un numar-data;

=MONTH(numar-data) extrage luna dintr-un numar-data, sub forma de valori cuprinse intre 1 si 12;

=DAY(numar-data) genereaza un numar corespunzator zilei cu valori intre 1 si 31;

=WEEKDAY(X) returneaza numarul zilei din saptamana corespunzator argumentului X

Page 19: Functii Excel

care poate fi de tip numar data calendaristica sau text in format data calendaristica;

=DAYS360(data debut;data sfarsit) calculeaza numarul de zile intre doua date calendaristice considerand anul ca avand 360 de zile;

=TIME(ora;minut;secunda) calculeaza un numar-timp corespunzator orei, minutului si secundei;

=TIMEVALUE(“sir de caractere”) returneaza numarul-timp corespunzator sirului de caractere specificat in format data/ora (intre ghilimele);

=HOUR(numar-timp) extrage ora dintr-un numar-timp (0,000000 pentru ora 24:00:00 si 9,999988426 pentru ora 23:59:59), sub forma unui numar cuprins intre 0 si 23;

=MINUTE(numar-timp) extrage minutul dintr-un numar-timp, sub forma unui numar intreg cuprins intre 0 si 59;

=SECOND(numar-timp) extrage secunda dintr-un numar-timp sub forma unui numar intreg cuprins intre 0 si 59;

Un exemplu edificator de utilizare a functiilor de tip data calendaristica si ora este prezentat in figura 1.80.

Fig. 1.80 Functiile tip data si ora

 

1.4.1.8 FUNCºIILE TEXT SAU ªIR DE CARACTERE

Functiile text (Text): permit diferite operatii cu siruri de caractere si furnizeaza in egala masura informatii legate de textul existent in celule:

=CHAR(cod numeric ASCII) returneaza caracterul corespunzator codului numeric ASCII specificat ca argument;

=TRIM(text) afiseaza sirul de caractere specificat ca argument in care toate spatiile inutile sunt anulate (cu exceptia spatiilor care separa cuvintele textului);

=CODE(text) returneaza codul numeric pentru primul caracter din textul specificat ca argument;

=CONCATENATE(text1;text2;…) concateneaza mai multe siruri de caractere specificate ca argumente, intr-unul singur;

=EXACT(tect1;text2) verifica daca doua siruri de caractere sunt identice. Comparand

Page 20: Functii Excel

cele doua siruri, functia returneaza valoarea logica TRUE daca acestea sunt identice sau valoarea logica FALSE in caz contrar;

=UPPER(text) afiseaza cu majuscule textul specificat ca argument;

=LOWER(text) afiseaza cu minuscule textul specificat ca argument;

=MID(text;N;X) afiseaza X caractere ale textului specificat ca argument, incepand cu pozitia “N”;

=LEN(text) returneaza numarul caracterelor ce formeaza textul specificat ca argument;

=SUBSTITUTE(text-sursa;N;X;text-nou) returneaza un nou sir de caractere (text-nou) la a “N”-a pozitie a textului-sursa, dupa ce au fost anulate X caractere;

=REPT(text;numar de ori) repeta afisarea textului de un numar specificat de ori;

=PROPER(text) determina scrierea cu majuscula a fiecarei prime litere din textul specificat ca argument;

Fig. 1.81 Exemple de functii text

=VALUE(text) converteste un text ce reprezinta un numar intr-o valoare numerica (numarul ce figureaza in textul tespectiv, trebuie sa corespunda unuia din formate numerice consacrate);

=DOLLAR(numar;zecimale) converteste un numar in text, folosind un format monetar;

=FIND(text1;text2;N) localizeaza pozitia la care incepe textul1 in textul2 incepand cautarea cu pozitia N;

Exemplificarile functiilor de tip text sau sir de caractere se gasesc prezentate in figura 1.81.

 

1.4.1.9 FUNCºIILE FINANCIARE

 

Functiile financiare (Financial) efectueaza o serie de calcule economico-financiare furnizand prin valorile returnate informatii utile referitoare la amortismente, la rentabilitatea investitiilor, plasamentelor, imprumuturilor etc.

=PV(rata dobanzii;numar de perioade;marimea platii;[valoare viitoare;tipul]) returneaza

Page 21: Functii Excel

valoarea actuala (present value) aferenta unei sume investite sau depozitate la banca, prin plati periodice, in conditiile unei rate constante a dobanzii.

Functia financiara PV calculeaza deci valoarea prezenta a unei sume investite, adica valoarea curenta a unei serii de plati viitoare. Functia se utilizeaza pentru a se determina daca valoarea de revenire a unei anumite investitii este favorabila sau nu, tinand cont de costul initial al investitiei.

Functia PV (ca si alte functii financiare PMT, FV) este considerata a fi o functie-anuitate, adica opereaza cu o investitie sau un depozit la care toate platile sunt egale si sunt efectuate la intervale regulate.

Argumentele functiei PV au urmatoarea semnificatie:

-   rata dobanzii reprezinta procentul de dobanda perceput pentru o anumita perioada;

-   numar de periode reprezinta numarul total de plati periodice; -   marimea platii semnifica valoarea platii facute in fiecare

perioada; -   valoare viitoare reprezinta suma totala care se doreste a fi

realizata dupa ultima plata; -   tipul este un parametru care semnifica faptul ca plata se face la

inceputul perioadei (valoarea 1) sau la sfarsitul perioadei (valoarea 0 – implicita).

Daca sunt omise ultimele doua argumente, acestea vor fi considerate ca avand valori nule.

Argumentele rata dobanzii si numarul de perioade trebuie exprimate in aceeasi unitate de timp –luna sau an).

Pentru exemplificarea functiei financiare PV, furnizam urmatoarea aplicatie in figura 1.82.

O persoana fizica doreste incheierea unei polite de asigurare pentru o perioada de 20 de ani cu o rata anuala a dobanzii de 40%. Asiguratul urmeaza sa plateasca lunar o prima de asigurare de 600.000 lei. Costul anuitatii perceput de asigurator este estimat la 19.000.000 lei. ¥n figura urmatoare s-a calculat valoarea prezenta cu ajutorul functiei PV.

Fig. 1.82 Aplicatie pentru functii financiare

Din calculul facut (in celula B34), reiese ca valoarea prezenta a anuitatii este de

Page 22: Functii Excel

17.993.120 lei, adica mai mica decat valoarea anuitatii calculate de asigurator care este de 19.000.000 lei. Deci aceasta investitie nu este rentabila.

Se observa ca rata dobanzii a fost exprimata in luni (rata anuala a fost impartita la 12), iar numarul de ani pentru care s-a contractat asigurarea a fost exprimat tot in luni (numarul de ani a fost inmultit cu 12).

De asemenea se observa ca functia PV a returnat un numar negativ. Explicatia acestui rezultat este legata de faptul ca functia PV semnifica o cheltuiala, o iesire de bani. Pentru ca functia sa returneze un rezultat pozitiv, ar fi trebuit ca argumentul “valoarea platii” sa fie introdus ca numar negativ (de exemplu –600.000).

=FV(rata dobanzii;numar de periode[;marimea platii; valoarea prezenta; tipul]) returneaza valoarea viitoare (future value) a unei investitii sau plasament in conditii de anuitate (plati si rate ale dobanzii constante). Argumentele functiei financiare FV sunt identice ce cele ale functiei PV cu exceptia faptului ca unul din argumente reprezinta valoarea prezenta a investitiei sau plasamentului.

Pentru exemplificarea functiei financiare FV, oferim urmatoarea aplicatie: o persoana fizica doreste efectuarea unui plasament de 25.000.000 lei pe o perioada de 9 luni la o banca comerciala, pentru o dobanda anuala de 55%. Persoana fizica urmeaza a depune lunar la banca, alaturi de depozitul initial cate 1.000.000 lei pe aceeasi perioada.

¥n figura 1.83 s-a calculat (in celula B42) valoarea viitoare a sumei depuse la banca de respectiva persoana fizica (48.259037,70 lei).

Fig. 1.83 Aplicatie pentru functii financiare (FV)

=PMT(rata dobanzii;numar de perioade;valoare prezenta[;valoare viitoare;tip]) calculeaza valoarea lunara sau anuala a platii pentru o investitie sau un imprumut.

Pentru exemplificarea functiei PMT (paiement), presupunem un imprumut la o banca comerciala pentru achizitionarea unui bun de folosinta indelungata in valoare de 35.000.000 lei. Rata dobanzii pentru creditele de consum este de 43% pe an, iar durata imprumutului a fost stabilita la 5 ani.

Fig. 1.84 Aplicatie pentru functii financiare (PMT)

 

Valoarea lunara a platii catre banca pentru creditul acordat a fost calculata (in celula B49) prin functia PMT in figura 1.84:

De asemenea s-a calculat costul total al imprumutului ca un produs intre valoarea lunara a platii si numarul de perioade de plata in luni. Valoarea totala a dobanzii s-a calculat ca

Page 23: Functii Excel

diferenta intre costul total al imprumutului si suma imprumutata.

=RATE(numar de perioade;valoarea platii;valoare prezenta) returneaza rata dobanzii pe perioada unei anuitati, pentru un imprumut sau o investitie. Pentru exemplificare presupunem efectuarea unui imprumut printr-un credit de 15 milioane lei pe timp de un an, cu o valoare lunara de rambursat in suma de 1.800.000 lei.

¥n figura 1.85 se calculeaza in celula E58, dobanda lunara perceputa de banca, iar in celula E59 dobanda anuala pentru suma imprumutata.

Fig. 1.85 Aplicatie pentru functii financiare (RATE)

 

Functiile financiare PMT, RATE si PV prezentate anterior, permit construirea tablourilor de rambursare pentru imprumuturi, aplicatie exemplificata in figura 1.86.

Un intreprinzator particular solicita unei banci comerciale un imprumut de 15 milioane lei pentru achizitionarea unui utilaj. Banca acorda creditul pe o durata de 6 luni cu o dobanda de 60% pe an.

Solicitantul creditului isi poate intocmi in Excel un tablou de rambursare al imprumutului, altfel spus un scadentar.

Fig. 1.86 Tablou de rambursare a creditelor

¥ntr-o prima faza, se poate calcula valoarea lunara de rambursare (celula D64) cu ajutorul functiei PMT.

¥n a doua faza, se organizeaza tabloul de rambursare pe patru coloane:

-   prima coloana (coloana A) contine numarul lunii pentru care se face calculul dobanzii si restul de rambursat;

-   a doua coloana (coloana B) contine restul de rambursat, adica suma care ramane de restituit bancii la sfarsitul lunii in curs. Pentru prima luna restul de rambursat este egal cu creditul, adica 15 milioane, iar lunile urmatoare se calculeaza ca diferenta intre restul de rambursat si rata de plata;

-   a treia coloana (coloana C) contine dobanda lunara calculata cu functia RATE, adica suma lunara ce reprezinta dobanda calculata asupra restului de rambursat;

-   a patra coloana (coloana D) reprezinta rata de rambursat,

Page 24: Functii Excel

calculata ca diferenta intre valoarea lunara de rambursare (celula D64) si dobanda lunara.

¥mprumutul este complet rambursat la inceputul celei de-a saptea perioada, dupa ce a fost achitata a sasea rata.

Daca s-ar fi dorit rambursarea integrala a imprumutului in luna a 4-a, deci cand ar mai fi 3 luni de platit, suma de rambursat ar fi de 8.047.911,48 lei (suma a fost calculata cu functia financiara PV).

Formulele utilizate pentru studiul de caz prezentat se gasesc ilustrate in figura 1.87.

<i< span=""></i<> 

Copyright © Contact | Trimite referat

  4.4  Formule  şi  funcţii  

Formulele şi funcţiile sunt oferite de programul Excel pentru efectuarea de calcule folosind conţinutul unor celule dintr-o foaie de calcul sau din mai multe foi de calcul în conformitate cu proiectarea şi cerinţele aplicaţiei.

Definiţie. Formulele sunt  expresii formate din operanzi (constante şi/sau referiri de celule), operatori

matematici (aritmetici şi relaţionali) şi funcţii; formula este precedată de semnul “=”; funcţia este o formulă complexă predefinită identificată printr-un nume şi conţine între paranteze o listă de argumente ce reprezintă o expresie.

Cea mai simplă formulă este cea care conţine o valoare (constantă numerică sau text). Formulele mai complexe sunt construite cu ajutorul expresiilor matematice, dar care sunt precedate de

“=”. În cazul în care formula nu este precedata de semnul “=”, expresia matematică este interpretata ca o dată (numerică sau de tip text).

Orice formulă trebuie scrisă într-o celulă a foii de calcul şi va apărea scrisă în bara formulei Formula Bar). Formula se termină prin tasta <CR>, şi ca efect în celulă nu va fi afişată formula , ci

rezultatul calculelor în conformitate cu expresia corespunzătoare formulei.

Page 25: Functii Excel

Exemplu de mai sus este preluat din foaia de calcul Orders ataşată la cartea “Excel 97 pas cu Microsoft Press, 1998 (http://www.microsoft.com/mspress).

În practică, există două moduri diferite de a crea (scrie) o formulă:

tehnica traditională – se selectează celula în care trebuie să fie creată formula şi se introduce formula scriind adresele de celulă ale tuturor celulelor care urmează a fi introduse în formulă ; nu se recomandă pentru formulele complexe, deoarece pot apărea erori prin tastare; tehnica de tip indicare – se selectează celula unde trebuie să apară rezultatul şi se tastează semnul “=”; introducerea formulei se face prin repetarea următoarei acţiuni: se efectuează click pe celula ce este operand în formulă şi se tastează semnul pentru operatorul corespunzător, până la terminarea întregii formule; se încheie formula prin apăsarea tastei <CR>;

În cazul tehnicii de tip indicare, dacă este necesară referirea unor celule la mare distanţă unele de altele, se recomandă atribuirea de nume pentru aceste celule şi apoi referirea numelor atribuite.

Denumirea domeniilor de celule este o facilitate prin care utilizatorul poate aplica un nume pentru a desemna o celulă sau un grup de celule (rolul variabilelor într-un limbaj de programare), în loc de a folosi adresele de celule ca referinţe.

Numele definite prezintă multiple avantaje:

numele sunt mai descriptive şi mai uşor de memorat decât adresele de celule; când o celulă este deplasată, numele este deplasat o dată cu celula;

Page 26: Functii Excel

se poate folosi un nume în locul unei celule sau al unui domeniu de celule într-o formulă sau în argumentul unei funcţii, similar utilizării unei etichete de rând sau de coloană; la copierea unei formule care foloseşte un nume, efectul este acelaşi ca şi cum s-ar folosi o referinţă absolută de celulă.

Numele pot avea maximum 255 de caractere şi pot include litere, cifre, liniuţe de subliniere şi puncte, primul caracter trebuie să fie o literă sau un caracter de subliniere, deci diferit de cifre şi punct. Un nume definit nu poate folosi adrese valide de celule.

Un domeniu de celule poate fi denumit în trei moduri:1.      folosind caseta Name Box – se selectează domeniul de celule ce urmează a fi denumit şi

se efectuează click în caseta Name Box din partea stângă a barei de formule; se tastează un nume valabil şi se apasă <CR>;

2.      folosind caseta de dialog Define Name - se selectează domeniul de celule ce urmează a fi denumit şi se efectuează Insert Name Define care deschide caseta de dialog Define Name ce oferă o listă de nume deja folosite în toate foile de calcul ale agendei de lucru; în casta de text Names and workbook se tastează un nume valabil de domeniu, după care se efectuează clik pe butonul Add; se poate realiza ştergerea (delete) unui nume deja introdus folosind butonul Delete; la încheierea operaţiunii se efectuează click pe butonul OK;

3.      folosind caseta de dialog Create Names – se poate atribui un nume unei celule din textul existent într-o celula, chiar dacă textul nu este o etichetă de coloană sau de linie; se selectează domeniul de celule ce urmează a fi denumit; se includ celulele al caror conţinut urmează a fi folosit ca nume, fie pe primul sau pe ultimul rând, fie pe prima sau pe ultima coloană selectată; se selectează opţiunea Insert Name Create din bara de meniuri pentru a deschide caseta de dialog Create Names; în caseta de text  Create name in , se selectează rândul (Top-sus sau Bottom-jos) şi/sau coloana (Left-stânga sau Right-dreapta) care conţine etichetele ce urmează a fi folosite pentru a denumi domeniul selectat; se efectuează click pe butonul OK pentru a aplica numele.

Page 27: Functii Excel

Numele de domeniu se poate folosi ca argument pentru o funcţie oferită de programul Excel. De asemenea, numele îndeplineşte un rol de navigare, cu precădere în agendele de lucru şi foi de calcul de mari dimensiuni. Pentru a comuta la sau pentru a selecta un domeniu cu nume, oriunde în interiorul agendei de lucru, se efectuează click pe săgeata orientată în jos din caseta Name şi se selectează numele din lista.

ompletarea formulelor este operaţia prin care trebuie să se utilizeze facilitaţile Excel pentru copierea  conţinutului unei celule ce reprezintă formule de calcul, şi în alte celule vecine, dar calculele se referă la celule corespunzătoare unor rânduri sau coloane. Operaţia de completare

este un fel de operaţie de « copiere ». Această operaţie se utilizează şi când celula conţine numere sau C

Indicatorul de mouse (cursorul) va lua mai multe forme în timpul deplasării într-o foaie de calcul, şi anume :

cruce mare – în această formă se poate utiliza pentru activarea sau selecţia celulelor ; săgeată – această formă se va obţine când se deplasează spre marginea unei celule active; când indicatorul are această formă, se poate utiliza mouse-ul pentru deplasarea celulei (copierea conţinutului celulei în altă celulă-mutarea celulei) ; cruce mică neagră – se va obţine această formă când mouse-ul se deplasează spre indicatorul de celula (colţul jos-dreapta = pătrat min negru ;instrument de umplere), forma ce se utilizează pentru a comunica utilizatorului că este permisă operaţia de completare.

În timpul lucrului în Excel, utilizatorul trebuie să urmarească frecvent forma indicatorului. O deplasare de mouse egală cu a 32-a parte dintr-un inch (aproximativ 0.8 mm) reprezintă diferenţa între operaţiile de selectare, deplasare şi copiere.

Pentru completarea unei formule (este o operaţie de copiere), se selectează celula care conţine formula ce se doreşte a fi copiată. Se deplasează indicatorul de mouse în direcţia instrumentului de umplere până când indicatorul capătă forma indicatorului de umplere (cruce mică neagră). Se apasă butonul de mouse şi se trage de indicatorul de completare pentru a selecta celulele unde se doreşte copierea formulei. Se eliberează butonul de mouse şi astfel formula va fi copiată şi în celulele selectate.

Însumarea pe rânduri şi pe coloane este o metodă de creare a totalurilor pe rânduri şi pe coloane, dintr-un singur pas folosind butonul AutoSum ( -însumare automată) de pe bara de

Page 28: Functii Excel

instrumente Standard. Acesta apelează funcţia SUM.De exemplu, dacă există un tabel de numere şi se doreşte realizarea totalurilor valorilor pe

fiecare rând şi pe fiecare coloană, se selectează celulele care conţin întregul tabel, se poate include şi celula vidă care va conţine totalul. După aceea, se efectuează click pe butonul AutoSum. Excel va insera în celula vidă o formulă de calcul (indiferent dacă acestă celulă a fost selectată sau nu) şi va calcula totalul. Dacă se doreşte inserţia unui rând alb înaintea totalurilor, se selectează două celule suplimentare. Totalul se va plasa în ultima celulă vidă selectată.

După cum se va vedea din imaginea următoare, pentru tabelul ce conţine numere în domeniul B4:G14, automat în celula I4 se va crea formula =SUM(B4:H4), în celula I5 se va crea  formula =SUM(B5:H5), ş.a.m.d., iar în celula I16 se va crea celula formula =SUM(B16:H16). Deliberat, coloana H este cu celule vide, iar rândul 15 conţine, de asemenea celule vide. Prin utilizarea butonului AutoSum calculul totalurilor în coloana I şi în rândul 16 se va realiza automat şi imediat.       

Modificarea formulelor (revizuire/actualizare) poate interveni în diverse situaţii :s-a introdus o formulă incorectă ; au fost adăugate date noi şi este necesară modificarea formulei pentru reflectarea datelor noi introduse.

Page 29: Functii Excel

tilizatorul se poate deplasa în celula care conţine formula şi să creeze o nouă formulă, scriind formula corectă, sau poate edita formula existentă. La efectuarea unui dublu click pe o formulă pentru a o deschide în vederea editării, programul Excel va desena fiecare adresă de celulă sau

adresă de domeniu într-o culoare diferită şi va amplasa o margine de aceeaşi culoare în jurul celulei sau a domeniului. Marginea are numele de identificator de domeniu (Range Finder).  O altă modalitate este aceea de a selecta celula care conţine formula şi apoi se selectează bara de formule unde se face click pe ea. Dacă se doreşte modificarea unor referinţe, utilizatorul poate folosi tastatura, fie identificatorul de domeniu. Pentru a folosi tastatura, se selectează referinţa din formulă şi apoi fie se efectuează click pe celula cu care se doreşte a se face înlocuirea, fie se tastează adresa celulei de înlocuire. Pentru a folosi identificatorul de domeniu, se « apucă » marginea identificatorului de domeniu şi se deplasează în celula corespunzătoare.  Dacă este necesară includerea în domeniu a unui număr mai mare sau mai mic de celule, se trage de instrumentul de selecţie situat în partea de jos a indentificatorului de domeniu pentru a extinde sau pentru a reduce selecţia. După încheierea editării formulei, se apasă pe <CR> sau se execută click pe butonul Enter.

U

Observaţie. Dacă referinţa care se doreşte a fi modificată este un domeniu de celule, referinţa este

construită lexical din prima celulă din domeniu (un colţ), două puncte « : » şi ultima celulă din domeniu (celălalt colţ diagonal), adică în cazul de mai sus, dacă este considerat întregul tabel , vom

Page 30: Functii Excel

B16:H16. Pentru a modifica această referinţa într-o formulă, se selectează întreaga referinţă şi apoi se trece în foaia de calcul şi se trage pentru a selecta domeniul de celule pentru noua formulă, sau se deplasează şi apoi se extinde identificatorul de domeniu.

Exerciţiu. Să se considere tabelul de numere din exemplul precedent, să se înlocuiască cuvântul Total » cu « Media » şi să se realizeze acţiuni pentru ca pe rândul 16 şi pe coloana I să apară

valorile medii ale numelor din tabel. Se va folosi funcţia AVERAGE (medie) oferită de programul Excel prin butonul Paste Function (fx).

Utilizarea funcţiilor Excel este o facilitate puternică şi performantă a programului Excel. O este o formulă predefinită, prin care utilizatorul economiseşte timp pentru efectuarea unor

calcule complexe. De asemenea, pentru a economisi timp, se recomandă utilizarea referinţelor la alte celule, în loc de a include toate caluculele care determină rezultatele în aceste celule. Utilizarea butonului AutoSum () are ca efect apelarea funcţiei SUM.

Funcţiile sunt formule, deci dacă se apelează o singură funcţie, aceasta trebuie să fie precedată semnul « = ». După semnul egal urmează numele funcţiei, urmat de unul sau mai multe argumente

separate prin virgule sau « ; » şi incluse între paranteze :

=Nume_funcţie (<lista-argumente>)

Page 31: Functii Excel

De regulă, funcţiile sunt operanţi în expresiile care constituie formula.rogramul Excel oferă utilizatorului sute de funcţii (235) care se pot apela pentru a calcula rezultate folosite în finanţe, contabilitate, statistică, matematică, inginerie sau în alte

domenii ştiinţifice, economice, sociale. Funcţiile sunt proceduri (programe structurate) care calculează un anumit rezultat cu o precizie foarte mare ţinând seama de performanţele actualelor microprocesoare cu care sunt înzeztrarte sisteme de calcul.

    P

Categorii de funcţii Excel

Categorie Exemple 

Financial (financiare) Calculează rata dobânzii, rata lunară de rambursare a împrumutului, valoarea uzurii, etc. (17 funcţii)

Date & Time (data şi ora) Determină ora curentă, ziua din săptamână sau din an, ora sau data

Math & Trig (matematică şi trigonometrie)

Calculează valoarea absolută, rădăcina pătratică, suma, funcţia exponenţială, funcţia logaritmică, funcţii trigonometrice, etc. 

Statistical (statistică) Calculează medii, maxime şi minime, abateri medii pătratice, cuantile, etc.

Look & Reference (căutare şi referinţă)

Caută şi returnează valori dintr-un domeniu, creează hiperconexiuni în reţele sau documente din INTERNET

Database (bază de date) Prelucrează valori dintr-o bază de date (tabel) din Excel

Text (text) Converteşte text în majuscule sau minuscule, elimină caractere din dreapta sau stânga, concatenează şiruri de caractere, etc.

Logical (logic) Evaluează o expresie logică şi returnează o valoare TRUE (adevărat) sau FALSE (fals), folosită pentru diverse acţiuni sau pentru formatare (condiţionată)

Information (informaţie) Returnează informaţii din programul Excel sau Windows, referitoare la starea unei celule, a unui obiect sau mediu în ansamblu

Engineering (inginerie) Calcule inginereşti, funcţii incluse în Office 2000, dar trebuie instalate separat din Analysis  Toolpack

    

Observaţie. Evident, nu este necesară memorarea tuturor funcţiilor dintr-o categorie, dar utilizatorul

Page 32: Functii Excel

trebuie să se informeze despre existenţa acestor funcţii şi să cunoască funcţiile de bază şi să aibă suficiente informaţii despre alte funcţii pentru a le putea apela atunci când este nevoie. Este posibilă accesarea tuturor funcţiilor prin utilizarea paletei de formule (Formula Palette).

Daca trebuie să se apeleze o funcţie mai rar folosită, se foloseşte butonul Paste Function (fx) de bara de instrumente Standard care deschide caseta de dialog Paste Function ce oferă lista

categoriilor de funcţii (Function category) şi lista numelor de funcţii din categoria selectată (Function ). Caseta de dialog Paste Function se poate deschide şi prin comanda Insert Function.

Lista categoriilor de funcţii include categoria funcţiilor cel mai recent utilizate (Most Recently ) şi categoria tuturor funcţiilor (All).

Înainte de a apela o funcţie, utilizatorul trebuie să cunoască rezultatul returnat (întors) de funcţie, ce argumente trebuie sa aibă funcţia (tipul şi semnificaţia lor) şi modul de apelare al funcţiei.

regimul de asistenţă (Help) programul Excel oferă toate aceste aspecte pentru fiecare funcţie, utilizatorul urmănd să fie în cunoştinţă de cauză în ceea ce priveşte oportunitatea accesării unor funcţii în cadrul aplicaţiei la care lucrează.

De exemplu, dacă se doreşte valoarea unui împrumut sau a unei investiţii într-un moment de timp viitor, după efectuarea tuturor plăţilor, utilizatorul trebuie să cunoască faptul că există în categoria funcţiilor financiare (Financial) funcţia FV (future value ; valoare viitoare) ce se apelează cu argumentele :

rate – de tip number ; rata dobânzii la împrumut ;nper (number of periods) – de tip number ; număr de perioade (număr de luni, ani, zile sau alte unităţi) ;pmt (payment) – de tip number ; plată ; sumă platită periodic ca rată la împrumut ;pv (present value) – de tip number ; valoarea actuală ; valoarea iniţială a unui împrumut ;type – de tip number ; tip având valoarea 1 sau 0.

Apelul funcţiei se va face sub următoarea formă : FV(rate ; nper ; pmt ; pv ;type).

Page 33: Functii Excel

Observaţie. Când se lucrează cu funcţii financiare, trebuie verificat dacă toate argumentele unei funcţii sunt bazate pe aceeaşi perioadă de timp : o zi, o lună sau un an .

Page 34: Functii Excel

Numele funcţiilor oferite de programul Excel pe categorii de funcţii sunt :

Financial – DB, DDB, FV, IPM, IRR, ISPMT, MIRR, NPER, NPV, PMT, PPMT,    PV, RATE, SLN, SYD, VDB ;

Date & Time – DATE, DATEVALUE, DAY, DAYS360, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, YEAR ;

Math & Trig – ABS, ACOS, ACOSH, ASIN, ASINH, ATAN, ATAN2, ATANH, CEILING, COMBIN, COS, COSH, DEGREES, EVEN, EXP, FACT, FLOOR, INT, LN, LOG, LOG10, MDETERM, MINVERSE, MMULT, MOD, ODD, PI, POWER, PRODUCT, RADIANS, RAND, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, SIGN, SIN, SINH, SQRT, SUBTOTAL, SUM, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2, TAN, TANH,TRUNC ;

Statistical – AVEDEV, AVERAGE, AVERAGEA, BETADIST, BETAINV, BINOMDIST, CHIDIST, CHIINV, CHITEST, CONFIDENCE, CORREL, COUNT, COUNTA, COUNTBLANK, COUNTIF, COVAR, CRITBINOM, DEVSQ, EPONDIST, FDIST, FINV, FISHER, FISHERINV, FORECAST, FREQUENCY, FTEST, GAMMADIST, GAMMAINV, GAMMALN, GEOMEAN, GROWTH, HARMEAN, HYPGEOMDIST, INTERCEPT, KURT, LARGE, LINEST, LOGEST, LOGINV, LOGNORMDIST, MAX, MEXA,MEDIAN, MIN, MINA, MODE, NEGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSINV, PERSON, PERCENTILE, PERCENTRANK,PERMUT, POISSON, PROB, QUARTILE, RANK, RSQ, SKEW, SLOPE, SMALL, STANDARDIZE, STDEV, STDEVA, STDEVP, STDEVPA, STEYX, TDIST, TINV, TREAD, TRIMMEAN, TTEST, VAR, VARA, VARP, VARPA, WEIBULL, ZTEST ;

Look & Reference – ADDRESS, AREAS, CHOOSE, COLUMN, COLUMNS, GETPIVOTDATA, HLOOKUP, HYPERLINK, INDEX, INDIRECT, LOOKUP, MATH, OFFSET, ROW, ROWS, TRANSPOSE, VLOOKUP ; Database – DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSUM, DVAR, DVARP ;Text – CHAR, CLEAN, CODE, CONCATENATE, DOLLAR, EXACT, FIND, FIXED, LEFT, LEN, LOWER, MID, PROPER, REPLACE, REPT, RIGHT, SERCH, SUBSTITUTE, T, TEXT, TRIM, UPPER, VALUE ;Logical – AND, FALSE, IF, NOT, OR, TRUE ; Information – CELL, ERROR, TYPE, INFO, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNOTEXT, ISNUMBER, ISREF, ISTEXT, N, NA, TYPE.

Tabelul funcţiilor Excel utilizate mai frecvent în aplicaţii

FUNCŢIA REZULTAT – DESCRIERE 

AVERAGE (x1, x2,…, xn) Media aritmetică a numerelorm = (x1+ x2 +  … + xn) / n

Page 35: Functii Excel

SUM(x1, x2,…, xn) Suma aritmetică a numerelors = x1+ x2 +  … + xn

PRODUCT(x1, x2,…, xn) Produsul arimetic al numerelor  p = x1

x2  … xn

MIN (x1, x2,…, xn) Minimul dintre numerele x1, x2,…, xn

MAX (x1, x2,…, xn) Maximul dintre numerele x1, x2,…, xn

COUNT (val1, val2, … , valn) Numărul elementelor ce conţin valori numerice

COUNTA (val1, val2, … , valn) Numărul elementelor nevideABS (x) Valoarea absolută (modulul) numărului

x INT(x) Partea întreagă inferioră a numărului xFLOOR (x,n) Partea întreagă inferioară sau superioră

a numărului x SQRT(x) Radăcina pătratică a numărului xROUND(x,n) Rotunjeşte valoarea numărului x la n

zecimale PI() Valoarea numărului =

3,14159265358979RAND() Număr aleator uniform în intervalul

(0,1)VAR(x1, x2,…, xn) Dispersia estimată a valorilor x1, x2,…,

xn

D= (xi – m)2/(n-1), unde m este media aritmetică a valorilor

STDEV(x1, x2,…, xn) Deviaţia standard estimată a valorilor x1, x2,…, xn (radăcina pătrată a dispersiei estimate)

VARP(x1, x2,…, xn) Dispersia calculată a valorilor x1, x2,…,

xn

D= (xi – m)2 /n, unde m este media aritmetică a valorilor

STDEVP(x1, x2,…, xn) Deviaţia standard calculată a valorilor x1, x2,…, xn (radăcina pătrată a dispersiei calculate)

POWER(b,e) Puterea be

MOD(a,b) Modulo – restul împărţirii numerelor întregi  a : b

AND(exp1, exp2, … , expn) Conjuncţia expresiiloe logice exp1, exp2, … , expn .Valoarea returnată este TRUE dacă fiecare expresie este TRUE, altfel este FALSE

OR(exp1, exp2, … , expn) Disjuncţia expresiilor logice exp1, exp2, … , expn .

Page 36: Functii Excel

Valoarea returnată este TRUE dacă cel puţin o  expresie este TRUE, altfel este FALSE

NOT(exp) Negaţia expresiei exp. Valoarea returnată este TRUE dacă expresia este FALSE, altfel este FALSE

TRUE() Valoarea returnată este constanta logică TRUE 

FALSE() Valoarea returnată este FALSE ; întotdeauna

FUNCŢIA 

REZULTAT – DESCRIERE 

IF(exp, exp1, exp2) Se evaluează valoarea expresiei logice exp. Dacă valoarea expresiei exp este TRUE, atunci funcţia IF returnează valoarea specificată prin exp1, altfel returnează  valoarea specificată prin exp2

CHOOSE(i, x1, x2,…, xn) Instrucţiune de ramificaţie multiplă. Selectarea unei valori dintr-o listă de valori, pe baza valorii unui indice i.

TODAY() Data calendaristică curentăNOW() Data şi ora calendaristică curentăVLOOKUP(val, reg, k) Căutare verticală. Se caută o valoare în

prima coloană a unei regiuni de date. Datele din această coloană sunt aşezate în ordine crescătoare. Se localizează primul element al coloanei care este mai mare sau egal cu elementul căutat. Dacă elementul localizat este în linia i, valoarea returnată de funcţie este conţinutul celulei din linia i şi coloana k a regiunii de date. Prima coloană are numărul de ordine 1.

HLOOKUP(val, reg, k) Căutare orizontală. Se caută o valoare în prima linie a unei regiuni de date. Datele din această linie sunt aşezate în ordine crescătoare. Se localizează primul element al liniei care este mai mare sau egal cu elementul căutat. Dacă elementul localizat este în coloana i, valoarea returnată de funcţie este conţinutul celulei din coloana i şi linia k a regiunii de date. Prima linie are numărul de ordine 1.

Page 37: Functii Excel

OFSET(baza, lin, col, i, l) Obţinerea unei referinţe la o celulă sau la o regiune de celule de dimensiune specificată (i=înălţime, l=lăţime). Argumentul baza reprezintă o referinţă la o celulă faţă de care se determină noua referinţă. Celula pentru care se generează noua referinţă este deplasată faţă de celula de bază cu lin linii şi col coloane 

Funcţii    pentru  date   de  tip  TEXTCODE(s) Codul ASCII al primului caracter din

şirul de caractere s (şirul de caractere s va fi delimitat de ghilimele)

CHAR(n) Caracterul al carui cod ASCII este numărul întreg n

VALUE(s) Valoarea numerică a şirului de caractere s

LEN(s) Numarul de caractere din şirul de caractere s

LEFT(s ,n) Primele n caractere din şirul de caractere s

RIGHT(s,n) Ultimele n caractere din şirul de caractere s

FUNCŢIA 

REZULTAT – DESCRIERE 

MID(s,n1,n2) Returnează n2 caractere din şirul de caractere s începând cu poziţia n1

REPT (s,n) Un şir de caractere obţinut prin repetarea de n ori a şirului de caractere s

TRIM(s) Un şir de caractere obţinut prin eliminarea tuturor spaţiilor, cu excepţia spaţiului care desparte cuvintele

LOWER(s) Transformă toate literele mari din şirul s în litere mici

UPPER(s) Transformă toate literele mici  din şirul s în litere mari

PROPER(s) Un şir de caractere în care prima literă a fiecărui cuvânt este transformată în literă mare

CONCATENATE(s1,s2, …, sn) Un şir de caractere obţinut prin concatenarea şirurilor s1,s2, …, sn

REPLACE(s1,n1,s2,n2) Şirul de caractere obţinut prin înlocuirea în şirul s1 a n2 caractere

Page 38: Functii Excel

începând din poziţia n1 cu şirul de caractere s2

Functii    financiare în   Excel 

FV(rate, nper, pmt, pv, type) Valoarea viitoare (Future value) a unui împrumut într-un moment de timp viitor, după efectuarea tuturor plăţilor, unde rate = rata dobânzii, nper = numărul de perioade, pmt = suma plătită ca rată la împrumut, pv = valoarea actuală a împrumutului, type = 1 sau 0 după cum plata se face la începutul perioadei sau la sfârşitul peroadei

NPER(rate, pmt, pv, fv,  type) Number of periods - Numărul de luni, ani, zile sau alte unităţi de timp necesare pentru un împrumut

PMT(rate, nper, pv, fv, type) Payment-Suma plătită periodic ca rată la împrumut

PV(rate, nper, pmt, fv, type) Present value – Valoarea actuală a unui împrumut

RATE(nper, pmt, pv, fv,type, quess)

Rate – Rata dobânzii la un împrumut

Funcţii   pentru  procesarea  bazelor  de  date 

DAVERAGE(db,col,crit) Returnează media aritmetică a valorilor din coloana col a bazei de date db care verifică criteriul crit, unde db = referinţă la o regiune (domeniu) de celule care conţine baza de date, col = referinţă la un nume de câmp al bazei de date ( o celulă din prima linie a bazei de date) prin care se accesează coloana utilizată de funcţie, crit = referinţă la o regiune de celule care specifică criteriul utilizat (tabelul de condiţii)

FUNCTIA REZULTAT – DESCRIERE

DCOUNT(db,col,crit) Returnează numărul celulelor cu valori numerice din coloana col a bazei de date db care verifică citeriul crit. Argumentul col este opţional. Dacă acest argument este omis, funcţia va determina numărul tuturor celulelor bazei de date care verifică criteriul

DCOUNTA(db,col,crit) Returnează numărul celulelor nevide

Page 39: Functii Excel

din coloana col a bazei de date db care verifică citeriul crit

DGET(db,col,crit) Returnează conţinutul unei singure celule din baza de date db. Celula este situată în coloana col şi verifică criteriul crit. Dacă nici un articol nu verifică criteriul, se va returna valoarea de eroare #VALUE !, iar dacă mai multe articole verifică criteriul, se va returna #NUM !

DMAX(db,col,crit) Returnează valoarea maximă a numerelor din coloana col a bazei de date db care verifică criteriul crit

DMIN(db,col,crit) Returnează valoarea minimă a numerelor din coloana col a bazei de date db care verifică criteriul crit

DPRODUCT(db,col,crit) Returnează produsul valorilor din coloana col a bazei de date db care verifică criteriul crit

DVAR(db,col,crit) Returnează dispersia estimată a valorilor din coloana col a bazei de date db care verifică criteriul crit. Dacă x1, x2,…, xn sunt aceste valori, dispersia estimată a valorilor x1, x2,…, xn esteD= (xi – m)2/(n-1), unde m este media aritmetică a valorilor

DVARP(db,col,crit) Returnează dispersia calculată a valorilor din coloana col a bazei de date db care verifică criteriul crit. Dacă x1, x2,…, xn sunt aceste valori, dispersia calculata a valorilor x1, x2,…, xn esteD= (xi – m)2/n, unde m este media aritmetică a valorilor

DSTDEV(db,col,crit) Returnează deviaţia standard estimată (rădăcina pătrată a dispersiei estimate) a valorilor din coloana col a bazei de date db care verifică criteriul crit

DSTDEVP(db,col,crit) Returneaza deviatia standard calculata (radacina patrata a dispersiei calculate) a valorilor din coloana col a bazei de date db care verifica criteriul crit

DSUM(db,col,crit) Returnează suma volorilor din coloana col a bazei de date db care verifică citeriul crit

Tipul şi numărul argumentelor utilizate în definirea funcţiilor depinde de funcţia respectivă.

Page 40: Functii Excel

Argumentele sunt separate de virgulă sau de « ; », iar unele funcţii nu au argumente (de exemplu,  PI), în acest caz apelul funcţiei de face sub forma :

=Nume_functie()

Argumentele pot fi :constante (valori numerice sau text) ;referinţe de celule ;referinţe de regiuni de celule (domenii).

nainte de a introduce o funcţie(formulă),  utilizatorul trebuie să se asigure că celula în care se doreşte afişarea rezultatului, este activă (selectată). Sunt două modalităţi, ori se execută click

caseta Formula Bar şi se tastează  « = » şi apoi conţinutul formulei, ori se execută click pe butonul Edit Formula (editare formulă) din bara de formule pentru a deschide paleta de formule. În ultimul caseta Name aflată în partea stângă a barei de formule (Formula Bar), se va transforma în caseta

Function (funcţie), afişând numele ultimei funcţii folosite şi o lista cu cele mai recente funcţii utilizate, dacă se execută click în lista derulantă a casetei Function. Dacă funcţia dorită se află în listă, se selectează şi astfel programul Excel va scrie funcţia în bara de formule şi în paleta de formule. Paleta

    I

Page 41: Functii Excel

de formule include o descriere a funcţiei şi una sau mai multe casete de text pentru fiecare argument al funcţiei. Pentru unele funcţii care folosesc un singur domeniu de celule apare şi descriere domeniului, deoarece se « intuieşte » argumentul. Casetele corespunzătoare argumentelor trebuie completate pentru a utiliza funcţia apelată.

De exemplu, dacă în foia de calcul (tabel) din imaginea precedentă se doreşte ca în celula G21 să se calculeze media valorilor de pe coloana G, deci din domeniul G12 :G20, se selectează celula G21 şi apoi se execută click pe butonul Edit Formula şi apoi se selectează funcţia AVERAGE din caseta Function. Se va deschide paleta de formule, şi anume caseta pentru funcţia AVERAGE aşa cum apare în imaginea următoare. Automat apare primul argument domeniul G12 :G20. Dacă selecţia este corectă se confirmă prin efectuarea unui click pe butonul OK. În cazul în care se doresc alte argumente, în casetele de texte Number 1, Number 2, etc. se pot tasta sau introduce alte argumente. Se poate utiliza butonul Colapse Dialog (Inhibare casetă)

        Ca în cazul oricărei formule, rezultatele funcţiei sunt afişate în celula activă, iar funcţia este afişată în bara de formule (Formula Bar), în momentul când celula respectivă este activă (selectată). 

Page 42: Functii Excel

Exista doua tipuri de functii/formule :        Functii obisnuite la care rezultatul va fi returnat doar intr-o celula, adica sunt functii de tipul

f : D1 x D2 x … x Dn D ;        Functii matriceale la care rezultatul va fi returnat intr-o zona de celule(domeniu), adica sunt

functii de tipul                            f : D1 x D2 x … x Dn E1 x E2 x … x Em ;

 în acest caz argumentele funcţiei pot fi : a) un domeniu(o regiune) n : m,      unde , {A,B, …, Z} si n, m {1,2, … 65536} ;b) un nume de domeniu/regiune ;c) o matrice numerica data sub forma   { <linia_1> ; <linia_2> ; … ;<linia_n>}, unde <linia_i> este lista elementelor liniei ‘i’ separate de virgula;

EXEMPLE.   a)      Calculul determinantului si inversei unei matrice(se utilizeaza functiile MDETERM si

MINVERSE) Consideram A o matrice 3 x 3 si sa calculam determinantul matricei.In celulele domeniului A4 :C6 se introduc elementele matricei A, apoi se selecteaza celula E4 unde se doreste sa apara rezultatul, dupa care in bara de formula se tasteaza ‘=MDETERM(A4 :C6)’  sau se apeleaza functia MDETERM indicandu-se argumentul ce este domeniul in care sunt elementele matricei A. Pentru calculul inversei, se va selecta domeniul A10 :C12 care in final va stoca inversa matricei, apoi se apeleaza functia MINVERSE cu argumentul A4 :C6, dupa care se apasa combinatia de taste <CTRL> + <SHIFT> + <CR>. b)      Calculul puterilor unei matrice(se va utiliza functia MMULT). Pentru A2 se va selecta zona

G4:I6 unde se va stoca matricea rezultat, apoi se va apela functia MMULT indicandu-se argumentele A4 :C6, respectiv A4 :C6, dupa care se apasa combinatia de taste <CTRL> + <SHIFT> + <CR>. Asemanator se va proceda pentru A3, A4, …, An.

 

  matricea A  det(A)    A*A    

                 19 0 7   72   382 0 154

3 2 8       87 4 613 0 3       66 0 30

                 

 inversa A        

A*A*A    

                 

0.083333 0

-0.19444      

7720 0

3136

0.208333 0.5

-1.81944      

1848 8 824

- 0 0.52       134 0 552

Page 43: Functii Excel

0.08333 7778 4

                 

    Opţiunea AutoCalculate (calcul automat) oferită de programul Excel permite efectuarea anumitor

verificări referitoare la conţinutul unor celule (ce conţin constante sau valori ca rezultat al unei funcţii sau formule). Se poate cere afişarea automată în bara de stare a calculului pentru sumă, medie, etc. a unor valori dintr-un domeniu de celule. Pentru activarea acestei opţiuni se selectează regiunea (domeniul) de celule care urmează a fi verificate şi se efectuează click cu butonul drept pe bara de

. Se afişează un meniul local (meniu rapid) ce va permite selectarea funcţiei dorite de utilizator. Efectul apelării funcţiei solicitate se va afişa în bara de stare.

Page 44: Functii Excel

Formulele şi funcţiile utilizate într-o foaie de calcul fac referinţe la diferite celule sau domenii de celule. Aceste referinţe pot fi destul de complicate pentru unele foi de calcul complexe, astfel că urmărirea lor poate fi anevoioasă.

Programul Excel oferă utilizatorului diferite mijloace grafice destinate verificării interdependenţei celulelor. Aceste mijloace pot fi utilizate cel mai simplu prin afişarea pe ecran a barei de unelte (Auditing Toolbar) care este destinată verificării (Auditing) formulelor şi funcţiilor. Prin comanda Tools Auditing  se deschide un meniul din care se alege comanda Show Auditing Toolbar (afişarea barei de unelte pentru verificare).

Uneltele din această bară trebuie utilizate numai după ce a fost selectată celula corespunzătoare. Comenzile (uneltele) acestei bare sunt :

        Trace Precedents – celula actuală va fi legată cu săgeţi (de culoare albastră) de celulele precedente (folosite la determinarea conţinutului celulei active) ; se indică acele celule, la care formula sau funcţia din celula activă face referinţe (celulele precedente, adică ascendenţii celulei active) ;

        Remove Precedent Arrows – ştergerea săgeţilor care indică celulele ascendente de nivel cel mai înalt pentru celula activă ;

        Trace Dependents – indicarea acelor celule care utilizează informaţia din celula activă

Page 45: Functii Excel

(celulele care depind de celula activă, adică descendenţii) ;         Remove Depentent Arrows - ştergerea săgeţilor care indică celulele descendente de

nivel cel mai înalt pentru celula activă ;        Remove All Arrows – ştergerea tuturor săgeţilor de indicare a interdependenţelor ;

        Trace Error – indicarea celulei sau celulelor care cauzează eroarea în celula activă ; comanda poate fi utilizată dacă celula activă conţine o valoare ce indică o eroare ;

        New Comment – ataşarea unui comentariu celulei active ;        Circle Invalid Data – încercuirea datelor invalide (în context cu comanda de validare a

datelor) ;        Clear Validation Circles – ştergerea cercurilor de validare .

Page 46: Functii Excel

Referinţe absolute şi relative de celule    Operaţia fundamentală de copiere a celulelor în Excel este o operaţie mai specială ţinând seama

celulă poate conţine valori de date (constante - numere, text) sau formule (apeluri de funcţii şi expresii). Dacă o foaie de calcul este mai complexă (volum mare de date şi calcule complexe), este incomod să se repete introducerea unor formule în mai multe celule. De exemplu, dacă trebuie să se realizeze suma valorilor pe mai multe linii sau pe mai multe coloane, ar trebui  să se introduca acelaşi tip de formulă de mai multe ori, schimbându-se doar coordonatele(referinţele) unor coloane sau linii.

Pentru a fi un instrument puternic în calculul tabelar, programul Excel a introdus referinţe de celule absolute şi relative.

Faţă de metodele fundamentale de copiere în Office 2000, utilizatorul va detecta câteva diferenţe la copierea şi deplasarea celulelor şi a domeniilor de celule în Excel :

dacă se inserează celulele deasupra unor date existente, aceste date vor fi suprascrise, prin urmare trebuie să existe suficiente celule vide pentru a se accepta selecţia ce urmează a fi inserată ;decuparea şi lipirea, precum şi copierea şi lipirea au efecte diferite în Excel faţă de pachetul Office 2000 ; este imposibilă copierea, apoi interclasarea unor operaţii şi apoi lipirea ; datele trebuie inserate imediat, altfel decuparea sau copierea sunt anulate ;

Page 47: Functii Excel

la decuparea unei celule în Excel, aceasta este copiata în memoria Clipboard, dar nu este eliminată din foaia de calcul decât în momentul inserării ei în noua locaţie prin apăsarea tastei <CR> sau efectuarea unui click pe butonul Paste ;la decuparea unei selecţii, aceasta poate fi lipită o singură dată, nu se pot realiza copii multiple ; copierea şi lipirea sunt operaţii care se pot repeta ; la apăsarea tastei <CR> la sfârşitul unei operaţii de lipire, programul Excel va goli memoria Clipboard, deci se poate folosi butonul Paste pentru a insera prima, a doua, şi a n-a copie ; se apasă <CR> numai la inserarea ultimei copii.

Dacă se selectează o regiune (domeniu) de celule pentru a fi copiate în alt loc din foaia de calcul, selectare se foloseşte butonul Copy care are ca efect încadrarea domeniului cu un dreptunghi cu

laturi din linii întrerupte mişcătoare.

Utilizarea facilităţilor de tragere şi aşezare (drag and drop) este eficientă în operaţiile de copiere şi în acest caz. Această metodă este folosită dacă celulele care urmează a fi copiate, cât şi destinaţia lor, sunt vizibile pe ecran.

După selectarea celulelor, se deplasează mouse-ul ca să indice spre laturile indicatorului de celulă(domeniu), cu excepţia instrumentului de completare (unde indicatorul  de mouse se va transforma într-o săgeată). Se menţine apăsat butonul drept şi se trage de celule în noile lor locaţii. La eliberarea butonului de mouse, se va deschide un meniu rapid, care permite opţiunea între deplasarea mutarea) şi copierea celulelor.

Page 48: Functii Excel

După cum se poate vedea din exemplele precedente, copierea valorilor s-a realizat obişnuit conform metodei generale oferite de pachetul Office 2000.  

In cazul în care este vorba de copierea unei formule dintr-o celulă în alta, programul Excel modifică automat fiecare referinţă de celula din formulă. 

Exemplu demonstrativ. Să presupunem că celula H18 conţine formula =SUM(C18 :G18) care calculează suma valorilor din domeniul de celule C18 :G18 şi dorim să copiem celula H18 în celula

. Indiferent de modalitatea de copiere, în final în celula J20 se va obţine valoarea 0, iar formula afişată în bara de formule va fi =SUM(E20 :I20). Aceste efecte sunt rezultatul modificării automate a referinţelor de celule din formula continută în H18. Evident, în celula J20 se obţine valoarea 0 deoarece domeniul E20 :I20 este vid. Se poate observa din imaginea de mai jos că referinţele au fost translatate.

anslaţia referinţelor este identică cu translaţia celulei J20 faţă de celula H18 care conţine formula iniţială. Acest tip de referinţă se numeşte referinţă relativă.

Page 49: Functii Excel

Prin urmare, în procesul de copierea a celulelor care conţin formule, referinţele celulelor din formula copiată sunt translatate în funcţie de poziţia destinaţiei faţă de celula copiată. Prin copiere se poate ajungeţi la cazuri în care unele referenţieri de formule devin invalide, când se afişează textul

#REF ! ».

În exemplul următor (imaginea următoare)  vom ilustra procesul de referenţiere absolută a celulelor unei formule.Vom selecta celula H18 şi o vom edita (se execută click dublu pe ea sau click

pe ea şi apoi click simplu în bara de formule) prin trecerea la fiecare referinţă de celulă, când se tasta <F4> care face ca în faţa literei de celulă şi în faţa numărului liniei să apară simbolul « $ »

( sau se va tasta efectiv simbolul « $ »). După aceasta editare se apasă tasta <CR> şi astfel rezultatul în H18 va fi acelaşi ca cel anterior. Dacă se repetă procesul anterior de copiere a formulei H18 în J20, rezultatul obţinut în celula J20 va fi identic cu valoarea din H18, iar în bara de formule va fi formula nemodificată.

Page 50: Functii Excel

În acest caz s-a utilizat referenţierea absolută, formula copiată referenţiază (accesează), fără nici o translatare, celulele originale.

Există posibilitatea utilizării şi a referinţelor mixte. În acest caz, componenta în faţa căreia este plasat simbolul «$»  este o referinţă absolută , iar componenta care nu este precedată de simbolul  « $ »

referinţă relativă.   

Exercitiu. Folosind referinţe mixte, vom genera un tabel al înmulţirii numerelor (Tabla înmulţirii) . În domeniul de celule A2 :A19 vom introduce în ordine numerele 1, 2, …, 19 ( se recomandă utilizarea seriilor de valori ; Edit Fill Series), iar în domeniul B1 : M12 aceleaşi valori.

celula B2 vom introduce (edita) formula « =$A2*B$1 », după care vom selecta celula B2 şi executăm Copy. Vom selecta domeniul întreg B2 : M12 şi executăm lipirea (Paste) şi asfel se va genera tabelul înmulţirii (Tabla înmulţirii) din imaginea care urmează.

Page 51: Functii Excel

© Universitatea din Bucuresti 2002. No part of this text may be reproduced in any form without written permission of the University of Bucharest,

except for short quotations with the indication of the website address and the web page.Comments to: Vlada Marin; Text editor: Vladimir Ioan Vararu and Magdalena Bostan; Last update: November, 2002


Recommended