formule si functii in excel final

FPC

FORMARE PENTRU COMPETITIVITATE CURS: OPERATOR INTRODUCERE, VALIDARE SI PRELUCRARE DATEREGIUNEA SUD-MUNTENIA

CENTRUL REGIONAL TARGOVISTEPROIECTFORMULE SI FUNCTII IN EXCEL NUME CURSANT: IORGA GABRIELACUPRINS1. Tipurile de funcii coninute de EXCEL..pagina 32. Introducerea unei formule - expresie de calcul......................................................pagina 73. Utilizarea funciilor Excel......................................................................................pagina 8a. Funcii matematice...pagina 11b. Funcii statistice ...pagina 13c. Funcia logic IF..pagina 15d. Funcii pentru date calendaristice (Dat & Or)...pagina 174. Referine absolute, mixte i relative..pagina 19Formule i funcii1. Tipurile de funcii coninute de EXCEL sunt:

a) Funcii referitoare la baze de date i administrarea listelor;b) Funcii referitoare la dat i timp;c) Funcii financiare;d) Funcii informaionale;e) Funcii logice;

f) Funcii de cutare i de referin;g) Funcii matematice-inginereti;h) Funcii statistice;i) Funcii referitoare la text;j) Funcii trigonometrice.a) Funcii referitoare la baze de date i administrarea listelor

Argumentele acestor funcii sunt: baz de date, cmp i criteriu. Funcii sunt:

Nr

crtFunciaEfectul funciei

1.DAVERAGE (baz de date, cmp, criteriu)Calculeaz media numerelor din cmpul indicat al bazei de date, care respect criteriul dat. Exemplu: DAVERAGE (B12:H534), Zile, Criteriu. Vnzri); argumentul baz de date se afl n domeniul B12:H534, cmpul cruia i se calculeaz media are titlul Zile, iar criteriul se gsete ntr-un domeniu cu numele Criteriu. Vnzri. Atunci cnd se utilizeaz un titlu de coloan pentru argumentul cmp, trebuie pus ntre ghilimele.

2.DCOUNT (baz de date, cmp, criteriu)Numr nregistrrile numerice din cmpul bazei de date care satisfac un criteriu. Dac argumentul cmp este omis, sunt numrate toate nregistrrile din baza de date care satisfac criteriul.

3.DCOUNT (baz de date, cmp, criteriu)Numr celulele care au coninut unul din cmpul bazei de date, pentru acele nregistrri care satisfac un criteriu.

4.DGET (baz de date, cmp, criteriu)Extrage din baza de date o singur nregistrare care respect un criteriu.

5.DMAX (baz de date, cmp, criteriu)Este gsit numrul maxim din cmpul bazei de date, pentru nregistrrile care respect un criteriu.

6.DMIN (baz de date, cmp, criteriu)Este gsit numrul minim din cmpul bazei de date, pentru nregistrrile care respect un criteriu.

7.DPRODUCT (baz de date, cmp, criteriu)nmulete toate valorile din cmpul bazei de date pentru nregistrrile care respect un criteriu. Funcia este similar cu SUM ( ), cu diferena c valorile sunt nmulite, nu adunate.

8.DSTDEV (baz de date, cmp, criteriu)Calculeaz abaterea standard a unui ealon de date, pe baza numerelor dintr-un cmp al bazei de date, pentru nregistrrile care respect un cirteriu.

9.DSTDEVP (baz de date, cmp, criteriu)Calculeaz abaterea standard a ntregului set de date, pe baza numerelor dintr-un cmp al bazei de date, pentru nregistrrile care respect un criteriu.

10.DSUM (baz de date, cmp, criteriu)nsumeaz toate numerele dintr-un cmp al bazei de date, pentru nregistrrile care respect un anumit criteriu.

11.DVAR (baz de date, cmp, criteriu)Calculeaz abaterea estimat (sau se abate un eantion de la medie) a unui eantion de date, pe baza numerelor dintr-un cmp al bazei de date, pentru nregistrrile care respect un criteriu.

12.DVARP (baz de date, cmp, criteriu)Calculeaz abaterea ntregului set de date, pe baza numerelor dintr-un cmp al bazei de date, pentru nregistrrile care respect un criteriu.

13.SUBTOTAL (baz de date, cmp, criteriu)Calculeaz o sum parial pentru o list sau pentru o baz de date.

b) Funcii pentru dat i timp

Programul Excel nregistreaz valorile de tip dat i or ca numere seriale, avnd posibilitatea s numere zilele care au trecut de la nceputul anului 1900.

Argumentele de tip dat i or se pot introduce utiliznd formatele (10/12/98 sau 9-Sep-98). Funciile uzuale pentru dat i timp sunt:

Nr


1.DATE (an, lun, zi)Returneaz numrul serial pentru data specificat.

2.DATEVALUE (dat-text)Execut corecia datei introduse ca text n numr serial. Exemplu: DATEVALUE ("24-Dec-96") returneaz 35423.

3.DAY (numr-serial)Convertete un numr-serial ntr-un numr de zile ale lunii, n intervalul 1 i 31.

4.DAYS360 (dat-nceput, dat-sfrit, metod)Afieaz numrul de zile scurs ntre dat-nceput i dat-sfrit.

5.EDATE (dat-nceput, luni)Afieaz numrul serial al datei situate la un numr de luni fa de dat-nceput. Formateaz coninutul celulei ca numr. Este o funcie util pentru calcule de mprumuturi.

6.EOMONTH (dat-nceput, luni)Furnizeaz numrul serial al ultimei zile a lunii din data situat la luni fa de dat-nceput.

7.HOUR (numr-serial)Orele sunt pri fracionare ale unei zile exprimate ca numr serial.

8.MINUTE (numr-serial)Returneaz numrul de minute dintr-un numr-serial.

9.MONTH (numr-serial)Convertete valoarea numr-serial ntr-un numr de luni (ntre 1 i 12).

10.NETWORKDAYS (dat-nceput, dat-sfrit, vacan)Furnizeaz numrul de zile lucrtoare dintre dat-nceput i dat-sfrit.

11.NOW ( )Calculeaz numrul serial al datei i orei date de ceasul intern al calculatorului.

12.SECOND (numr-serial)Returneaz numrul de secunde (ntre 0 i 59) din partea fracionar a unui numr-serial.

13.TIME (or, minut, secund)Calculeaz numrul serial corespunztor numrului de ore, minute i secunde indicate.

14.TIMEVALUE (timp-text)Convertete o valoare de tip or n numr serial.

15. TODAY ( )Calculeaz numrul serial corespunztor datei din sistem.

16.WEEKDAY (numr-serial, tip-returnat)Valoarea numr-serial este convertit ntr-o zi a sptmnii. Rezultatul este un numr ntre 1 (duminic) i 7 (smbt).

17.YEAR (numr-serial)Convertete valoarea numr-serial n numrul unui an.

18.YEARFRAC (dat-nceput, dat-sfrit, baz)Furnizeaz fracia anului dintre dat-nceput i dat-sfrit.

c) Funcii financiare

Nr


1.FV (dobnd, uper, plat, vp, tip)Calculeaz valoarea viitoare pentru o serie de ncasri provenite din pli egale fcute ntr-un numr de perioade uper, cu o anumit dobnd considerat. O sum global, vp, poate fi investit la nceputul operaiei.

2.IPMT (dobnd, per, uper, vp, vv, tip)Calculeaz profitul din plata unei rente. Se poate utiliza pentru a determina dobnda la o ipotec ntr-o perioad per din intervalul uper.

3. IRR (valori, estimare)Furnizeaz rata intern a randamentului pentru seria de beneficii nete plus amortizri din estimare.

4.MIRR (valori, rat-finanare,

rat-reinvestire)Calculeaz rata inter modificat a randamentului din seria de beneficii nete pozitive sau negative din irul de valori.

5.NPER (dobnd, plat, vp, vv, tip)Calculeaz numrul de perioade necesare pentru a crea unitatea specific prin argumentele date.

6.NPV (dobnd, valoare 1, valoare 2, ...)Calculeaz valoarea net prezent din seria de beneficii aflate n irul sau matricea valoare 1, valoare 2 etc., fiind dat un rabat egal cu dobnda.

7.PMT (dobnd, uper, vp, vv, ltip)Calculeaz plile periodice pentru diferite tipuri i viitoare valori ale investiei, fiind date dobnda investiiei, termenul (uper) i valoarea prezent (vp).

8.PPMT (dobnd, per, uper, vp, vv, tip)Calculeaz principala poriune a unei pli fcut pentru o investiie amortizat.

9.PV (dobnd, uper, plat, vv, tip)Calculeaz valoarea curent a unei serii de beneficii nete plus amortizri n valori plat egale, fcute timp de uper cu o dobnd constant.

d) Funcii informaionale

Nr


1.CELL (tip-info, referin)Sunt returnate informaii despre coninutul celulei active sau despre celula indicat prin referin.

2.COUNTBLANK (domeniu)Se execut numrarea celulelor goale dintr-un domeniu.

3.ERROR.TYPE

(val-eroare)Furnizeaz un numr depinznd de tipul de eroare din celula referit de val-eroare.

4.INFO (tip-text)Furnizeaz informaii despre sistemul de operare i variabilele de mediu corespunztoare acestuia.

5.ISfunction (valoare)Mediul EXCEL are 11 funcii prin intermediul crora se poate determina dac o celul verific unele condiii. Rezultatul returnat este o valoare logic de tip TRUE sau FALSE.

6.N (valoare)Transform o valoare ntr-un numr.

7.TYPE (valoare)Determin tipul de coninut al unei celule, returnnd un cod corespunztor:

ValoareRezultat

Numr1

Text2

Valoare logic4

Formula8

Valoare de tip eroare16

Matrice64

e) Funcii logice

Nr


1.AND (logic 1, logic 2, ...)Reunete condiii.

2.FALSE ( )Returneaz ntotdeauna valoarea logic FALSE.

3.NOT (logic)Inverseaz valoarea de adevr a argumentului logic de la TRUE la FALSE sau de la FALSE la TRUE.

4.OR (logic 1, logic 2, ...)Reunete condiiile de testare: returneaz TRUE dac cel puin unul din argumentele logice este TRUE i FALSE atunci cnd toate sunt false.

5.TRUE ( )Furnizeaz ntotdeauna valoarea logic TRUE.

f) Funcii de cutare i referin

Nr


1.AREAS (referin)Furnizeaz numrul de zone de referin.

2.CHOOSE (num-index, valoare 1, valoare 2, ...)Seteaz dintr-o list o valoare care corespunde unui num-index.

3.COLUMN (referin)Furnizeaz numrul de coloan a celulei cu o anumit referin.

4.COLUMNS (matrice)Returneaz numrul de coloane dintr-o matrice.

5.TRANSPOSE (matrice)Efectueaz operaia de transpunere a matricei curente.

g) Funcii matematice

Nr


1.ABS (numr)Furnizeaz valoarea absolut a unui numr.

2.CEILING (numr, semnificaie)Produce rotunjirea unui numr pn la nivelul de semnificaie indicat.

3.COMBIN (numr, numr-abs)Produce combinarea elementelor, fr a avea importan ordinea acestora.

4.CONTIF (domeniu, criteriu)Numr celulele dintr-un domeniu al cror coninut satisface un criteriu.

5.EVEN (numr)Rotunjete un numr pn la o valoare par superioar.

6.EXP (numr)Calculeaz exponeniala numrului, funcia invers este LN ( ).

7.FACT (numr)Calculeaz factorialul unui numr.

8.FLOOR (numr, semnificaie)Rotunjete n jos un numr pn la nivelul de semnificaie indicat.

9.INT (numr)Rotunjete un numr pn la cea mai apropiat valoare ntreag.

10.MDETERM (matrice)Calculeaz determinantul unei matrice.

11.MINVERSE (matrice)Calculeaz inversa unei matrice.

12.MMULT (matrice 1, matrice 2)Efectueaz nmulirea a dou matrice.

13.MOD (numr, divizor)Calculeaz restul (modulul) unui numr mprit la divizor.

14.POWER (numr, putere)Efectueaz ridicarrea unui numr la o putere.

15.RAND ( )Furnizeaz un numr zecimal aleator ntre 0 i 1.

16.SIGN (numr)Va furniza 1 dac numrul este pozitiv i 1 dac este negativ.

17.SQRT (numr)Extrage rdcina ptrat dintr-un numr.

18.SUMIF (domeniu, criteriu, domeniu-sum)Calculeaz suma coninutului tuturor celulelor dintr-un domeniu, care verific un criteriu.

h) Funcii statistice

Nr


1.AVERAGE (numr 1, numr 2, ...)Calculeaz media argumentelor.

2.COUNT (valoare 1, valoare 2, ...)Efectueaz numrarea elementelor numere din argumente.

3.COUNTA (valoare 1, valoare 2, ...)Efectueaz o numrare a elementelor valoare. Funcia COUNTA ( ) numr i valori de tip text.

4.GROWTH (date-y, date-x, noi-x, const)Calculeaz curba de cretere exponenial care se potrivete cel mai bine datelor coninute n domeniul date-x i date-y.

5.VAR (numr 1,

numr 2, ...)Calculeaz o estimare a variaiei unei populaii de la un eantion dat ca argument.

i) Funcii text

Nr


1.EXACT (text 1, text 2)Se compar valorile text 1 i text 2.

2.LEN (text)Calculeaz numrul de caractere din text.

3.TRIM (text)terge toate blancurile din text, astfel nct ntre cuvinte s rmn cte un singur spaiu.

4.UPPER (text)Transform n majuscule toate caracterele din text.

j) Funcii trigonometrice

Nr


1.SIN (numr); COS (numr); TAN (numr)Calcuelaz funcia trigonometric a unui numr de radiani.

2.ASIN (numr); ACOS (numr); ATAN (numr)Calculeaz inversul funciei trigonometrice.

2. Introducerea unei formule - expresie de calculFormula reprezint expresie de calcul format din:

Semnul = este n mod obligatoriu primul caracter n orice formul

Operanzi (etichete, valori, referine de celule/blocuri de celule, funcii);

Operatori:

aritmetici (+ adunare, - scdere, / mprire, * nmulire, ^ ridicare la putere);

logici (and, or, not); relaionali (> mai mare, >= mai mare sau egal, < mai mic, 1000000;A2:A5) = 14 000 000Suma adaosurilor mai mari de 1.000.000

=SUMIF(B2:B5;>1000000) = 2 800 000ROUNDFuncia ROUND rotunjete un numr la numrul specificat de zecimale

Sintaxa: ROUND(number;num_digits)unde

number este numrul ce se va rotunji

num_digits reprezint numrul de zecimale la care va fi rotunjit numrul

Observaii:1) dac num_digits este 0, numrul va fi rotunjit la cel mai apropiat ntreg

2) dac count este mai mic ca 0, numrul este rotunjit ctre stnga separatorului de zecimal

Exemple:=ROUND (133,12548;2) = 133,13

=ROUND (133,12548;0) = 133

=ROUND (133,12548;) = 133

=ROUND (133,12548;-1) = 130

=ROUND (133,12458;-2) = 100

INTFuncia INT rotunjete n jos un numr pn la cel mai apropiat ntreg (furnizeaz drept rezultat partea ntreag a unui numr)

Sintaxa: INT (number)Exemple:INT(3,85) = 3INT(-3,85) = -4

b. Funcii statisticentre funciile din categoria Statistice (Statistical) se regsesc att funcii simple, pentru returnarea mediei aritmetice, minimului, maximului unui domeniu, numrare (AVERAGE, MIN, MAX, COUNT), dar i funcii statistice mai complexe, cum ar fi cele pentru calculul abaterilor absolute i standard sau a diverselor tipuri de distribuii sau de probabiliti.

MAXFuncia MAX calculeaz maximul valorilor de tip numeric referite ntr-o list de argumente

Sintaxa: MAX(number 1;number 2;...)unde number 1; number 2;sunt ntre 1 i 30 de argumente ce pot conine sau pot referi diverse tipuri de informaii, dintre care ns se va calcula maximul doar al celor de tip numeric.

Exemplu:

Cea mai mare not din list

=MAX(B13:B16) = 9

MINFuncia MIN calculeaz minimul valorilor de tip numeric referite ntr-o list de argumente

Sintaxa: MIN(number 1;number 2;...)unde number 1; number 2;sunt ntre 1 i 30 de argumente ce pot conine sau pot referi diverse tipuri de informaii, dintre care ns se va calcula minimul doar al celor de tip numeric.

Exemplu:Cea mai mic not din list

=MIN(B13:B16) = 2

AVERAGEFuncia AVERAGE calculeaz media aritmetic valorilor de tip numeric referite ntr-o list de argumente

Sintaxa: AVERAGE(number 1;number 2;...)unde number 1; number 2;sunt ntre 1 i 30 de argumente ce pot conine sau pot referi diverse tipuri de informaii, dintre care ns se va calcula media aritmetic doar pentru cele de tip numeric.

Exemplu:Media aritmetic a notelor obinute

=AVERAGE(B13:B16) = 5,25

COUNTFuncia COUNT numr celulele ce conin informaii de tip numeric i numerele introduse ntr-o list de argumente.

Sintaxa: COUNT(value1;value2;...)unde value1; value2;sunt ntre 1 i 30 de argumente ce pot conine sau pot referi diverse tipuri de informaii, dintre care ns vor fi numrate doar cele de tip numeric.

Exemplu:Numrul valorilor de tip numeric din blocul de celule A12:B16

=COUNT(A12:B16) = 4

Observaie: Funciile MAX, MIN, AVERAGE, COUNT pot fi apelate, pe lng metodele generale enunate anterior, i prin selectarea lor din lista derulant a butonului de comand nsumare automat (AutoSum), procedndu-se asemntor ca pentru funcia SUM, cu meniunea c funcia COUNT se va regsi n aceast list sub denumirea de Count Numbers.

COUNTIFFuncia COUNTIF numr, dintr-o zon de celule, doar pe acelea care ndeplinesc un criteriu dat.

Sintaxa: COUNTIF(range;criteria)unde

range este zona n care se va face numrarea

criteria este un criteriu de selectare, sub form de numr, expresie sau text

Exemplu:Numrul elevilor cu numele Vlad

=COUNTIF(A13:B16;vlad) = 2

Numrul elevilor cu nota mai mic dect 5

COUNTIF(A13:B16;=5, atunci Promovat, altfel Respins.

2) n tabelul urmtor, Sporul se va calcula dup formula:

Dacavechimea < 3 ani 1%*Salariu 3< vechimea5 ani 5%*Salariu

Pentru salariatul Popa formula poate fi:

=IF(C2

formule si functii in excel final

Documents