Excel Formule

3/28/2014 ebooks.unibuc.ro/informatica/Birotica/4.4_files/text.htm

http://ebooks.unibuc.ro/informatica/Birotica/4.4_files/text.htm 1/37

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 dinmai 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) şifuncţii; formula este precedată de semnul “=”; funcţia este o formulă complexă predefinită identificată printr-un nume şi conţine întreparanteze 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 ajutorulexpresiilor matematice, dar care sunt precedate de semnul “=”. În cazul în care formula nu este precedata de semnul “=”, expresia matematică esteinterpretata 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.



Exemplu de mai sus este preluat din foaia de calcul Orders ataşată la cartea “Excel 97 pas cu pas”, Microsoft Press, 1998

(http://www.microsoft.com/mspress).

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

1. tehnica traditională – se selectează celula în care trebuie să fie creată formula şi se introduce formula scriind adresele de celulă ale tuturorcelulelor care urmează a fi introduse în formulă; nu se recomandă pentru formulele complexe, deoarece pot apărea erori prin tastare;

2. tehnica de tip indicare – se selectează celula unde trebuie să apară rezultatul şi se tastează semnul “=”; introducerea formulei se face prinrepetarea 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;

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



C

Numele de domeniu se poate folosi ca argument pentru o funcţie oferită de programul Excel. De asemenea, numele îndeplineşte un rol denavigare, 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 text. 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 utilizamouse-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 minnegru ;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ă. Sedeplasează 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ă butonulde 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 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 vainsera î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.



U

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.

tilizatorul se poate deplasa în celula care conţine formula şi să creeze o nouă formulă, scriind formula corectă, sau poate edita formulaexistentă. 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 seselectează 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ţiesituat î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.



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 (uncolţ), două puncte « : » şi ultima celulă din domeniu (celălalt colţ diagonal), adică în cazul de mai sus, dacă este considerat întregul tabel , vomavea 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 tragepentru 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 realizezeacţ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ă deprogramul Excel prin butonul Paste Function (fx).



Utilizarea funcţiilor Excel este o facilitate puternică şi performantă a programului Excel. O funcţie este o formulă predefinită, prin careutilizatorul 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ţieiSUM.

Funcţiile sunt formule, deci dacă se apelează o singură funcţie, aceasta trebuie să fie precedată de semnul « = ». După semnul egal urmeazănumele funcţiei, urmat de unul sau mai multe argumente separate prin virgule sau « ; » şi incluse între paranteze :



P

=Nume_funcţie (<lista-argumente>) 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 decalcul.

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 saudata

Math & Trig (matematică şitrigonometrie)

Calculează valoarea absolută, rădăcina pătratică, suma, funcţiaexponenţ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 şireferinţă)

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ă caracteredin 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 saupentru formatare (condiţionată)

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

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 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. Esteposibilă 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 pe 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 name). 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 Used) ş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 şisemnificaţia lor) şi modul de apelare al funcţiei. Prin 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).



Observaţie. Când se lucrează cu funcţii financiare, trebuie verificat dacă toate argumentele unei funcţii sunt bazate pe aceeaşi perioadă detimp : o zi, o lună sau un an. 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

SUM(x1, x2,…, xn) Suma aritmetică a numerelor

s = 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 nevide

ABS (x) Valoarea absolută (modulul) numărului x

INT(x) Partea întreagă inferioră a numărului x

FLOOR (x,n) Partea întreagă inferioară sau superioră a numărului x

SQRT(x) Radăcina pătratică a numărului x

ROUND(x,n) Rotunjeşte valoarea numărului x la n zecimale

PI() Valoarea numărului p = 3,14159265358979

RAND() 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 esteTRUE, altfel este FALSE

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

Valoarea returnată este TRUE dacă cel puţin o expresie esteTRUE, 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ă valoareaexpresiei exp este TRUE, atunci funcţia IF returnează valoareaspecificată prin exp1, altfel returnează valoarea specificată prin

exp2

CHOOSE(i, x1, x2,…, xn) Instrucţiune de ramificaţie multiplă. Selectarea unei valoridintr-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 uneiregiuni de date. Datele din această coloană sunt aşezate înordine crescătoare. Se localizează primul element al coloaneicare este mai mare sau egal cu elementul căutat. Dacă elementullocalizat este în linia i, valoarea returnată de funcţie esteconţinutul celulei din linia i şi coloana k a regiunii de date. Primacoloană are numărul de ordine 1.

HLOOKUP(val, reg, k) Căutare orizontală. Se caută o valoare în prima linie a uneiregiuni de date. Datele din această linie sunt aşezate în ordine



crescătoare. Se localizează primul element al liniei care este maimare 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ărulde ordine 1.

OFSET(baza, lin, col, i, l) Obţinerea unei referinţe la o celulă sau la o regiune de celulede dimensiune specificată (i=înălţime, l=lăţime). Argumentulbaza 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 TEXT

CODE(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 decaractere 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 estetransformată î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 î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-unmoment de timp viitor, după efectuarea tuturor plăţilor, underate = rata dobânzii, nper = numărul de perioade, pmt = sumaplătită ca rată la împrumut, pv = valoarea actuală aîmprumutului, type = 1 sau 0 după cum plata se face la începutulperioadei sau la sfârşitul peroadei

NPER(rate, pmt, pv, fv, type) Number of periods - Numărul de luni, ani, zile sau alte unităţide 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 bazeide date db care verifică criteriul crit, unde db = referinţă la oregiune (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ă coloanautilizată de funcţie, crit = referinţă la o regiune de celule carespecifică criteriul utilizat (tabelul de condiţii)

FUNCTIA REZULTAT – DESCRIERE

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



DCOUNTA(db,col,crit) Returnează numărul celulelor nevide din coloana col a bazeide 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 deeroare #VALUE !, iar dacă mai multe articole verifică criteriul,se va returna #NUM !

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

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

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

DVAR(db,col,crit) Returnează dispersia estimată a valorilor din coloana col abazei de date db care verifică criteriul crit. Dacă x1, x2,…, xn

sunt aceste valori, dispersia estimată a valorilor x1, x2,…, xn

este

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

DVARP(db,col,crit) Returnează dispersia calculată a valorilor din coloana col abazei de date db care verifică criteriul crit. Dacă x1, x2,…, xn

sunt aceste valori, dispersia calculata a valorilor x1, x2,…, xn

este

D= å(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ă adispersiei estimate) a valorilor din coloana col a bazei de datedb care verifică criteriul crit

DSTDEVP(db,col,crit) Returneaza deviatia standard calculata (radacina patrata adispersiei 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 dbcare verifică citeriul crit



Tipul şi numărul argumentelor utilizate în definirea funcţiilor depinde de funcţia respectivă. Argumentele sunt separate de virgulă sau de « ; », iar

unele funcţii nu au argumente (de exemplu, funcţia 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).



I

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 în 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 caz, caseta Name aflată înpartea 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 astfelprogramul Excel va scrie funcţia în bara de formule şi în paleta de formule. Paleta 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, deoarecese « 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 dincaseta Function. Se va deschide paleta de formule, şi anume caseta pentru funcţia AVERAGE aşa cum apare în imaginea următoare. Automat apareprimul argument domeniul G12 :G20. Dacă selecţia este corectă se confirmă prin efectuarea unui click pe butonul OK. În cazul în care se doresc alteargumente, în casetele de texte Number 1, Number 2, etc. se pot tasta sau introduce alte argumente. Se poate utiliza butonul Colapse Dialog (Inhibarecasetă)



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), înmomentul când celula respectivă este activă (selectată).

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) an : bm,

unde a ,b Î{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> estelista 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 barade formula se tasteaza ‘=MDETERM(A4 :C6)’ sau se apeleaza functia MDETERM indicandu-se argumentul ce este domeniul in care suntelementele matricei A.Pentru calculul inversei, se va selecta domeniul A10 :C12 care in final va stoca inversa matricei, apoi se apeleaza functia MINVERSE cuargumentul 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 61

3 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.08333 0 0.527778 1344 0 552

Opţiunea AutoCalculate (calcul automat) oferită de programul Excel permite efectuarea anumitor verificări referitoare la conţinutul unor celule (ceconţ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. aunor valori dintr-un domeniu de celule. Pentru activarea acestei opţiuni se selectează regiunea (domeniul) de celule care urmează a fi verificate şi seefectuează click cu butonul drept pe bara de stare. 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.



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 decomplicate 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 celmai simplu prin afişarea pe ecran a barei de unelte (Auditing Toolbar) care este destinată verificării (Auditing) formulelor şi funcţiilor. Prin comandaTools à 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 determinareaconţ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ă (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 ovaloare 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 .



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 că o 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 pemai 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 adomeniilor 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 ase 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, apoiinterclasarea unor operaţii şi apoi lipirea ; datele trebuie inserate imediat, altfel decuparea sau copierea sunt anulate ;

· 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ăriiei î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 Pastepentru 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, după 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 decompletare (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. Laeliberarea butonului de mouse, se va deschide un meniu rapid, care permite opţiunea între deplasarea (mutarea) şi copierea celulelor.



După cum se poate vedea din exemplele precedente, copierea valorilor s-a realizat obişnuit conform metodei generale oferite de pachetul Office2000. In cazul în care este vorba de copierea unei formule dintr-o celulă în alta, programul Excel modifică automat fiecare referinţă de celula dinformulă. 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 J20. Indiferent de modalitatea de copiere, în final în celula J20 se va obţine valoarea 0, iarformula afişată în bara de formule va fi =SUM(E20 :I20). Aceste efecte sunt rezultatul modificării automate a referinţelor de celule din formulacontinută î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. Translaţia referinţelor este identică cu translaţia celulei J20 faţă de celula H18 care conţine formula iniţială. Acest tipde referinţă se numeşte referinţă relativă.



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ţiadestinaţ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 ovom edita (se execută click dublu pe ea sau click simplu pe ea şi apoi click simplu în bara de formule) prin trecerea la fiecare referinţă de celulă, când seapasă 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 celula H18 va fi acelaşi ca cel anterior. Dacă se repetă procesul anterior de copiere a formuleiH18 în celula J20, rezultatul obţinut în celula J20 va fi identic cu valoarea din H18, iar în bara de formule va fi afişată formula nemodificată.



Î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ă , iarcomponenta care nu este precedată de simbolul « $ » este o referinţă relativă. Exercitiu. Folosind referinţe mixte, vom genera un tabel al înmulţirii numerelor (Tabla înmulţirii) . În domeniul de celule A2 :A19 vomintroduce în ordine numerele 1, 2, …, 19 ( se recomandă utilizarea seriilor de valori ; Edit à Fill à Series), iar în domeniul B1 : M12 aceleaşi valori. Încelula 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ă.

© 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

http://www.unibuc.ro/

mailto:[email protected]

mailto:[email protected]

Excel Formule

Documents

Transcript of Excel Formule