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 semnul “=”. Î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.

            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 tuturor celulelor care urmează a fi introduse în formulă; nu se recomandă pentru formulele complexe, deoarece pot apărea erori prin tastare;
  2. tehnica de tip indicarese 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;
  • 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.

            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.

C

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

 

U

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.

 

 

            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 avea 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 funcţie 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ă de 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>)

 

            De regulă, funcţiile sunt operanţi în expresiile care constituie formula.

    P

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.

 

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 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 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 şi semnificaţ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 :

·        ratede 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 ;

·        typede 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ă de timp : 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 numerelor

m = (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 x… 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

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= ĺ(xim)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= ĺ(xim)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 .

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.

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  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 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 î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 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 este

D= ĺ(xim)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 este

D= ĺ(xim)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ă. 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ă î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 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ă). 

 

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

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 (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 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 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 Precedentscelula 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ă (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 .

 

        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 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 ;

·        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, 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 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.

            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 J20. 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. Translaţ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ă.

            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 simplu pe ea şi apoi click simplu în bara de formule) prin trecerea la fiecare referinţă de celulă, când se apasă 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 formulei H18 î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ă , iar componenta 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 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. În 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ă.


© 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