Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 1
Capitolul 2 FUNCȚII DE CALCUL TABELAR __________________________________________________________________________________
Cuprins Capitolului 2
FUNCȚII DE CALCUL TABELAR Obiectivele Capitolului 2
2.1 Funcţii matematice Funcţii algebrice
Funcţii de rotunjire
2.2 Funcţii statitice
2.3 Funcţii logice
2.4 Funcţii de informare
2.5 Funcţii de dată şi timp
2.6 Funcţii text
2.7 Funcţii de căutare şi consultare 2.8 Funcţii financiare
Bibliografia Capitolului 2
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 2
����
Capitolului 2 FUNCȚII DE CALCUL TABELAR
OBIECTIVELE • Însuşirea cunoştintelor necesare pentru efectuarea operaţiilor fundamentale asupra
datelor dintr-un tabel EXCEL; • Utilizarea funcţiilor de calcul tabular pe grupe.
2.1 Funcţii matematice Unele funcţii predefinite sunt echivalente formulelor simple, ce operează cu adrese de celule sau
nume de câmpuri la care aplică operatori matematici: de exemplu, formula de adunare a
conţinutului celulelor A1, A2 şi A4 ( =A1+A2+A4) este echivalentă cu funcţia =SUM(A1:A2;A4). Alte
funcţii (în majoritatea cazurilor) nu au echivalent în rândul formulelor simple, rezultatul scontat
neputând fi obţinut decât prin intermediul funcţiilor predefinite sau putând fi obţinut pe cale
obişnuită, prin aplicarea succesivă a mai multor operaţii şi formule.
Folosirea funcţiilor predefinite este supusă unor reguli foarte stricte, a căror nerespectare poate
conduce la un rezultat incorect sau generator de eroare.
Toate funcŃiile Excel au trei componente (ilustrate în Figura 1):
� semnul = "egal");
� numele funcţiei;
� unul sau mai multe argumente.
=Numefuncţie([argument(e)])
Există funcţii fără
nici un argument:
=TODAY(),
=NOW(), =PI(), etc.
Există funcţii cu un
singur argument:
=ISBLANK(C2),
=LEN(C8),
=TRIM(A6), etc.
Majoritatea funcţiilor au mai multe argumente:
=IF(C2>5;B2*9;”Eroare”), etc.
Funcţiile pot avea ca argument(e) şi alte
funcţii (se pot imbrica):
RIGHT(C47;LEN(C47)-SEARCH(" ";C47))
Figura 1 Sintaxa generală a unei funcţii Excel Între cele trei componente ale funcŃiilor predefinite nu se admite ca separator nici un spaŃiu. Argumentele se află închise între paranteze rotunde şi sunt separate prin intermediul unui separator de argumente.
Acest separator poate fi virgulă sau punct şi virgulă, după cum au fost configuraŃi parametrii regionali
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 3
(de localizare) ai sistemului de operare Windows XP (Start → Settings → Control Panel → Regional and Language Options → caseta de dialog Regional and Language Options → tabul Regional Options). În exemplele luate, se va lua în consideraŃie ca separator de argumente caracterul "punct şi virgulă".
Exemplul următor ilustrează diferite tipuri de argumente care se pot întâlni la o funcŃie predefinită:
Funcţie predefinită Tip argument
=SUM(A2:A7) plajă continuă de celule
=SUM(A2:A7;A9;A11:A20) plajă discontinuă de celule
=AVERAGE(note) câmp numit „note”
=MAX(59;36;84) listă de constante numerice
=IF(A1=A2;TRUE();FALSE()) condiţii şi valori logice
=INT(SUM(D1:D9)) funcţie predefinită
=UPPER("Popa") şir de caractere
=REPT("Examen",3) şir de caractere şi valoare numerică
Excel acceptă următoarele tipuri de argumente: condiŃie: este o expresie logică care foloseşte unul din operatorii logici =, <, >, <>, <=, >=, NOT(…), AND(…), OR(…) pentru o adresă de celulă sau un nume de câmp. CondiŃia argumentului poate fi deci o formulă, un număr, un nume de câmp, un şir de caractere. FuncŃia evaluează condiŃia şi execută diferite operaŃii în funcŃie de faptul dacă aceasta este adevărată sau falsă. locaŃie: se identifică generic sub forma unei referinŃe celulare sau de câmp, unui nume de celulă sau câmp, unei formule sau funcŃii care generează o adresă sau un nume de câmp. un text: reprezintă orice secvenŃă de caractere incluse între ghilimele, adresa sau un nume de câmp ce conŃine o etichetă tip şir de caractere sau o formulă sau funcŃie care returnează o etichetă de tip text. Un şir de caractere utilizat ca argument într-o funcŃie trebuie marcat între ghilimele pentru a nu fi confundat cu un nume de câmp. valoare: reprezintă o constantă numerică, adresa sau numele unei celule care conŃine un număr, o formulă sau funcŃie predefinită care returnează un număr; expresie: reprezintă combinarea locaŃiilor, valorilor, operatorilor aritmetici şi de comparaŃie pentru a reprezenta o condiŃie sau o acŃiune. Toate tipurile de argumente pot fi folosite împreună într-o funcţie atunci când sintaxa este
respectată.
O funcţie predefinită se poate introduce într-o celulă în mod direct, tastând-o ca atare (conform
sintaxei) sau prin intermediul generatorului de funcţii. În momentul tastării numelui funcţiei, Excel
pune la dispoziţia utilizatorului în mod automat prin afişare interactivă (Figura 2), sintaxa şi ordinea
argumentelor funcţiei, marcând cu caractere aldine (Bold) argumentul curent, aflat în curs de
editare.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 4
Figura 2 Modul interactiv de introducere a funcţiilor Cea mai simplă metodă de editare a funcŃiilor este reprezentată de introducerea în mod direct a funcŃiilor predefinite, corespunzător sintaxei, în celula în care se va opera prelucrarea respectivă (metodă recomandată).
Excel permite utilizatorului în anumite cazuri, introducerea unei funcŃii predefinite care să genereze rezultate multiple printr-o abordare matriceală. Editarea unei formule matriceale, presupune selectarea plajei de celule destinaŃie (câmpul gol ce va reprezenta rezultatul formulei), iar apoi, pe selecŃia făcută se va introduce formula ce conŃine funcŃia predefinită. Într-o formulă matriceală argumentele formulei pot fi definite fie la nivelul celulelor, fie la nivelul unor plaje de celule. La sfârşitul acestui demers, se va valida editarea funcŃiei prin combinaŃia de taste Ctrl+Enter.
Dacă formula matriceală conŃine o funcŃie predefinită ce are ca argumente numai plaje de celule, atunci validarea formulei editate se va face prin combinaŃia de taste Ctrl+Shift+ Enter.
O formulă matriceală formează un tot unitar, nefiind posibilă ştergerea sau modificarea individuală a uneia din celulele câmpului matriceal. Pentru a executa o modificare, este necesar a se selecta în prealabil câmpul matriceal, iar apoi să se efectueze respectiva modificare, după care să se activeze din nou combinaŃia de taste Ctrl+Enter. Ştergerea formulei implică selectarea întregului câmp matriceal şi acŃionarea tastei Delete sau a comenzii Edit�Clear�All.
O altă posibilitate de introducere a funcŃiilor predefinite este operaŃională prin intermediul selectorului de funcŃii aflat pe bara de editare sau activabil prin comanda Insert����Function.
Figura 3 Casetele de dialog aferente selectorului de funcţii şi asistentului de sintaxă pentru funcţia aleasă
Din caseta de dialog Insert Function, funcŃia respectivă se alege, fie din lista funcŃiilor cele mai utilizate (Most Recently Used), fie din lista tuturor funcŃiilor disponibile ordonate alfabetic (All), sau din categoriile de funcŃii specializate (Financial, Date & Time, Math & Trig, Statistical ...), după care se validează şi se completează interactiv argumentele funcŃiei prin intermediul asistentului de funcŃii Function Arguments (Figura 3). Apelarea selectorului de funcŃii se mai poate face şi prin acŃionarea butonului =(egal) aflat pe bara de editare, după care se deschide lista funcŃiilor predefinite (cu ajutorul butonului de derulare aflat în partea stângă a barei de editare) şi se alege funcŃia dorită. Odată aleasă funcŃia, se completează interactiv argumentele în caseta de dialog Function Arguments. De regulă, utilizarea asistentului de funcŃii presupune parcurgerea a doi paşi (conform exemplului prezentat în Figura 3-dreapta):
pasul 1 semnifică alegerea tipului de funcŃie; pasul 2 presupune completarea interactivă a argumentelor impuse de sintaxa funcţiei respective
şi validarea acţiunii.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 5
Figura 4 Editarea interactivă (autocompletivă) a funcţiilor
predefinite
Categorii de funcţii predefinite
Excel posedă un set impresionant de funcŃii predefinite, în număr de peste 250, grupate pe tipuri de funcŃii potrivit utilităŃii acestora la rezolvarea diferitelor probleme şi aplicaŃii.
Astfel, în rezolvarea aplicaŃiilor EXCEL considerăm suficientă, prezentarea a celor mai importante 175 de funcŃii predefinite, grupate pe următoarele categorii:
funcŃii matematice (Madh & Trig): permit efectuarea de calcule algebrice, trigonometrice şi de rotunjire a rezultatului acestor calcule; funcŃii statistice (Statistical) : permit efectuarea unor calcule statistice utilizând distribuŃii de date pe serii de valori; funcŃii logice (Logical): permit evaluarea unor condiŃii, determinând valoarea de adevăr sau de fals - corespunzător acesteia; funcŃii de informare (Information): afişează informaŃii despre celule şi câmpuri; funcŃii dată calendaristică sau timp (Date & Time) : manipulează numere care reprezintă date calendaristice sau valori timp; funcŃii de căutare şi consultare (Lookup & Reference): permit identificarea informaŃiei conŃinute în celule sau câmpuri şi extragerea acesteia; funcŃii text sau şir de caractere (Text): oferă informaŃii legate de şirurile de caractere existente în celule şi permit efectuarea diferitelor operaŃii cu şiruri sau valori, evaluând valorile poziŃionale ale acestora; funcŃii bază de dată (Database): efectuează diferite calcule de grup asupra unor rubrici, într-o bază de date, corespunzător unor criterii definite; funcŃii economico-financiare (Financial): permit realizarea de calcule economico-financiare predefinite. În continuare, prezentăm cele mai importante funcŃii predefinite, precizând faptul că în cea mai mare a parte a lor sunt perfect compatibile ca sintaxă şi semnificaŃie cu funcŃiile arond (@) aferente procesorului de tabele LOTUS 1-2-3, parte a pachetului de aplicaŃii LotusSmartSuite, dezvoltat de IBM.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 6
FFuunnccţţiiiillee mmaatteemmaattiiccee ((MMaatthh && TTrriigg))
FuncŃiile Math&Trig permit efectuarea diferitelor calcule, de la cele mai simple la cele mai complexe, pentru rezolvarea de aplicaŃii ce solicită instrumente matematice şi trigonometrice de uz curent. FuncŃiile “MMaatthh && TTrriigg” pot fi clasificate după tipul de calcul efectuat, în următoarele categorii:
� funcţii algebrice;
� funcţii trigonometrice;
� funcţii de rotunjire a rezultatelor calculelor
FFuunnccţţiiii aallggeebbrriiccee
SUM, PRODUCT, SUBTOTAL, SUMPRODUCT, SUMIF, SUMIFS, LN, LOG, LOG10, EXP, MOD, FACT,
ROMAN, RAND, RANDBETWEEN, ABS, QUOTIENT, GCD, LCM, COMBIN, MMULT, MINVERSE,
MDETERM
�� CCaallccuullaarreeaa uunnuuii pprroodduuss..
FFuunnccŃŃiiaa PPRROODDUUCCTT
SSiinnttaaxxăă ==PPRROODDUUCCTT((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, adrese de celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă produsul elementelor listei.
�� CCaallccuullaarreeaa uunneeii ssuummee..
FFuunnccŃŃiiaa SSUUMM
SSiinnttaaxxăă ==SSUUMM((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă suma elementelor listei.
Observaţie. Funcţia de însumare este completată - spre uşurinţa utilizatorului - cu facilităţile
butonului tip listă derulantă AutoSum (situat pe ribbon-ul corespunzător tabului Formulas).
FuncŃiile generate de opŃiunile butonului de comandă AutoSum permit însumarea pe linie sau pe coloană a valorilor adiacente (valorile nu trebuie să fie întrerupte în succesiunea lor de celule vide sau de celule care să conŃină text), calculul mediei, a numărului de elemente numerice dintr-o serie de date, a celei mai mari / mici valori dintr-o listă, precum şi accederea către alte funcŃii predefinite (opŃiunea More Functions). Pot exista mai multe tehnici de utilizare a funcŃiei de însumare automată:
� Se plasează cursorul acolo unde se doreşte a se calcula suma (Figura 5) şi se activează butonul AutoSum prin dublu-clic (la primul clic se afişează sintaxa funcŃiei, iar la al doilea se afişează rezultatul). Dacă plaja de celule conŃine discontinuităŃi (celule vide), suma automată operează calculul de la prima celulă vidă, până în celula în care este plasat cursorul (Figura 6).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
� Însumarea automată poate opera şi prin selecŃia explicită a plajei de celule (A11:A19, din Figura 7 pentru care se doreşte efectuarea calculului, urmată de activarea butonului AutoSum. Rezultatul calculului se depune automat în celula situată imediat sub (sau la dreapta – în cazul unui câmp orizontal) plaja de celule (A20). ObservaŃie : această tehnică are avantajul faptului că pot fi operate calcule corecte în condiŃiile în care plaja de celule conŃine celule vide.
Figura 5 Suma automata cu plajă de celule continue
Figura 6 Suma automata cu plajă de celule discontinue
Figura 7 Suma automată realizată prin selecţia
explicită a unei plaje de celule (continue sau
discontinue) � Se selectează plaja de celule de însumat (Figura 8), inclusiv zona în care se vor plasa rezultatele
însumării (o linie de celule mai jos şi/sau o coloană de celule la dreapta), după care se activează butonul AutoSum prin dublu-clic.
Figura 8 Tehnici de însumare automată cu ajutorul butonului de comandă AutoSum
�� CCaallccuullaarreeaa uunneeii ssuummee ddee pprroodduussee..
FFuunnccŃŃiiaa SSUUMMPPRROODDUUCCTT
SSiinnttaaxxăă ==SSUUMMPPRROODDUUCCTT((lliissttăă__ddee__ccââmmppuurrii))
� lliissttăă__ddee__ccââmmppuurrii reprezintă un grup de câmpuri (cel puŃin două) ce conŃin elemente care se vor înmulŃi poziŃional unele cu altele (primul element din primul câmp se va înmulŃi cu primul element din al doilea câmp, al doilea element din primul câmp se va înmulŃi cu al doilea element din al doilea câmp şi aşa mai departe).
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă suma produselor câmpurilor listei. Altfel spus, rezultatul funcŃiei provine din multiplicarea valorilor situate în câmpurile specificate, după care sunt adunate agregatele obŃinute anterior.
Selecţie explicită
a câmpului sursă
(A11:A19) +
AutoSum
A14:Celulă vidă
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 8
OObbsseerrvvaaţţiiee. Grupul de câmpuri (organizat pe linie sau pe coloană) trebuie să conţină aceleaşi număr
de elemente.
�� CCaallccuullaarreeaa uunneeii ssuummee îînn ffuunnccţţiiee ddee eevvaalluuaarreeaa uunneeii ccoonnddiiţţiiii..
FFuunnccŃŃiiaa SSUUMMIIFF
SSiinnttaaxxăă ==SSUUMMIIFF((ccââmmpp__ddee__eevvaalluuaatt;;ccrriitteerriiuull__sseelleeccŃŃiiee[[;;ccââmmpp__ddee__îînnssuummaatt]]))
� ccââmmpp__ddee__eevvaalluuaatt reprezintă o listă compusă din adrese de celule sau nume de câmpuri. Asupra acestui argument operează condiŃia specificată de argumentul următor;
� ccrriitteerriiuull__sseelleeccŃŃiiee reprezintă o condiŃie impusă ccââmmppuulluuii ddee eevvaalluuaatt, care se specifică sub formă de şir de caractere (între ghilimele);
� [[ccââmmpp__ddee__îînnssuummaatt]] este argumentul care desemnează câmpul care va fi însumat în funcŃie de evaluarea condiŃiei precizate de al doilea argument. Dacă acest argument lipseşte, ccââmmppuull ddee îînnssuummaatt va fi chiar ccââmmppuull ddee eevvaalluuaatt.
RReezzuullttaatt rreettuurrnnaatt
Suma elementelor unei liste în funcŃie de evaluarea unei condiŃii precizate.
�� CCaallccuullaarreeaa uunneeii ssuummee îînn ffuunnccţţiiee ddee eevvaalluuaarreeaa mmaaii mmuullttoorr ccoonnddiiţţiiii..
FFuunnccŃŃiiaa SSUUMMIIFFSS
SSiinnttaaxxăă ==SSUUMMIIFFSS((ccââmmpp__ddee__îînnssuummaatt;; ccââmmpp__ddee__eevvaalluuaatt__11;;
ccrriitteerriiuull__sseelleeccŃŃiiee__11 [[;;ccââmmpp__ddee__eevvaalluuaatt__22;;ccrriitteerriiuull__sseelleeccŃŃiiee__22]].............. ......))
� ccââmmpp__ddee__îînnssuummaatt este argumentul care desemnează câmpul care va fi însumat în funcŃie de evaluarea condiŃiei sau condiŃiilor precizate.
� ccââmmpp__ddee__eevvaalluuaatt__11 reprezintă o listă compusă din adrese de celule sau nume de câmpuri. Asupra acestui argument operează condiŃia specificată de argumentul următor;
� ccrriitteerriiuull__sseelleeccŃŃiiee__11 reprezintă o condiŃie impusă ccââmmppuulluuii ddee eevvaalluuaatt__11, care se specifică sub formă de egalitate sau restricŃie (între ghilimele);
RReezzuullttaatt rreettuurrnnaatt
Suma elementelor unei liste în funcŃie de evaluarea mai multor condiŃii precizate.
Figura 9 ilustrează câteva exemple de utilizare a funcţiilor PRODUCT, SUM, SUMPRODUCT, SUMIF şi
SUMIFS.
Funcţia PRODUCT calculează în celula E10, iar apoi în câmpul E11:E15 produsul dintre cantităţile
(C10:C15) şi preţurile (D10:D15) aferente fiecărui produs.
Funcţia SUM calculează în celulele E16 şi E17 suma elementelor discontinue (E16) şi continue (E17)
ale unei liste de valori.
Funcţia SUMPRODUCT editată în celula E18, calculează produsele elementelor câmpului „Cantitate”
(C10:C15) şi „Preţ” (D10:D15), adună rezultatele intermediare şi înmulţeşte agregatul format cu
valoarea cursului Euro (din celula B7).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 9
Funcţia SUMIF calculează în celula E19 suma valorilor facturate în cuantum de peste 5.000 lei (se
observă faptul că lipseşte argumentul ccââmmpp__ddee__îînnssuummaatt, deoarece condiţia a fost pusă chiar pe
ccââmmppuull__ddee__eevvaalluuaatt). A doua funcţie SUMIF calculează în celula E20 suma valorilor facturate pentru
care preţul în Euro este mai mic decât 35. Funcţiile de însumare condiţionate multiplu, SUMIFS
calculează în celula E21 suma valorilor cuprinse între două intervale şi în E22 suma valorilor pentru
cantităţi mai mari de 50 şi preţuri mai mici de 25 €.
Figura 9 Exemplu de utilizare a funcţiilor PRODUCT, SUM, SUMPRODUCT, SUMIF şi SUMIFS
Excel 2007 pune la dispoziţia utilizatorului un asistent grafic (Wizard) care permite calcularea sumei
în funcţie de evaluarea unei condiţii. Facilităţile asistentului grafic devin operaţionale prin instalarea
unor componente incluse la cerere (activare Office Button > Excel Options, tabul Add-Ins, opţiunea
Conditional Sum Wizard, butonul de comandă Go), prin activarea casetei de validare Conditional
Sum Wizard aferente casetei de dialog Add-Ins.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
În momentul validării acţiunii prin
butonul OK, ribbon-ul tabului
Formulas este actualizat prin
adăugarea butonului de comandă
Conditional Sum.
Etapele (ilustrate în Figura 10) prin care asistentul grafic construieşte funcţia de însumare
condiţională SUMIF, pentru calcularea sumei valorilor facturate pentru care preţul în Euro este mai
mic decât 35, sunt următoarele:
Figura 10 Însumarea condiţională prin intermediul unui asistent grafic
Step 1 of 4 selectează plaja de celule (D9:E15) ce conŃine coloana de însumat (Valoare) şi coloana (PreŃ) sau coloanele de evaluat (pe baza cărora se construieşte condiŃia). SelecŃia plajelor de celule cuprinde etichetele câmpurilor (numele câmpurilor) şi domeniul de valori aferent. � Step 2 of 4 selectează (din lista derulantă) în partea superioară a casetei de dialog numele
(eticheta) coloanei („Valoare”) ce conţine valoarea de însumat, iar în partea inferioară permite construirea unei condiţii. Restricţia la care trebuie să răspundă câmpul de criterii se declară selectând numele coloanei restricţionate (Column), operatorul de comparaţie (Is) şi valoarea de comparaţie (This value). Aceasta din urmă poate fi selectată din lista derulantă, potrivit elementelor existente în domeniul respectiv sau poate fi tastată ca atare ca şi constantă numerică. Se recomandă ca în cazul valorilor numerice cărora li s-a aplicat un format explicit, să se introducă numai valoarea numerică de comparaţie, fără eticheta explicativă (se va introduce 35 şi nu 35 € cum propune respectiva rubrică). După ce condiţia a fost construită, aceasta se validează prin acţionarea butonului Add Condition. Acest demers (stabilire condiţie + validare) poate avea caracter repetitiv, urmând ca însumarea condiţională să evalueze mai multe condiţii (fapt imposibil de realizat pentru funcţia SUMIF).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 11
� Step 3 of 4 permite ramificarea execuţiei în funcţie de faptul dacă se doreşte numai afişarea rezultatului însumării condiţionale (opţiunea: Copy just a formula to a single cell) sau afişarea rezultatului împreună cu elementele ce desemnează restricţiile impuse (opţiunea Copy the formula and conditional values).
În varianta alegerii opţiunii Copy just a formula to a single cell, etapa Step 4 of 4 permite alegerea amplasamentului (adresa celulei) în care să se copieze rezultatul însumării condiţionale şi implicit funcţia SUMIF;
o În varianta alegerii opţiunii Copy the formula and conditional values, etapele: Step 4/5 of 5 permit specificarea amplasamentelor pentru copierea restricţiilor impuse câmpului de criterii.
�� CCaallccuullaarreeaa llooggaarriittmmiilloorr..
FFuunnccŃŃiiaa LLNN
SSiinnttaaxxăă ==LLNN((nnuummăărr))
� nnuummăărr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce o conŃine.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă logaritmul natural al unui număr specificat ca argument.
FFuunnccŃŃiiaa LLOOGG
SSiinnttaaxxăă ==LLOOGG((nnuummăărr;;[[bbaazzăă]]))
� nnuummăărr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce o conŃine;
� [[bbaazzăă]] reprezintă o constantă ce reprezintă baza logaritmului. Dacă se omite precizarea bazei, se consideră logaritmul în baza 10.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă logaritmul unui număr într-o bază specificată.
FFuunnccŃŃiiaa LLOOGG1100
SSiinnttaaxxăă ==LLOOGG1100((nnuummăărr))
� nnuummăărr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce o conŃine.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă logaritmul unui număr în baza 10.
�� CCaallccuulluull eexxppoonneennttuulluuii..
FFuunnccŃŃiiaa EEXXPP
SSiinnttaaxxăă ==EEXXPP((nnuummăărr))
� nnuummăărr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce o conŃine. Constanta numerică reprezintă exponentul aplicat la baza e.
RReezzuullttaatt O valoare numerică ce reprezintă baza logaritmul natural a unui număr,
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 12
rreettuurrnnaatt ridicată la exponentul X.
OObbsseerrvvaaţţiiii..
aa.. Baza este o constantă şi are valoarea 2,7182818….
bb.. Pentru a se calcula puterea bazei, se utilizează operatorul exponenţial (^).
FuncŃia EXP este opusul funcŃiei ce calculează logaritmul natural al unui număr.
Figura 11 ilustrează modul de calcul a logaritmilor naturali, într-o bază specificată, în bază 10,
precum şi exponentul unui număr ce returnează logaritmul natural.
Figura 11 Exemplu de utilizare a funcţiilor LN, LOG, LOG10 şi EXP
�������� CCaallccuullaarreeaa vvaalloorriiii aabbssoolluuttee aa uunnuuii nnuummăărr rreeaall..
FFuunnccŃŃiiaa AABBSS
SSiinnttaaxxăă ==AABBSS((nnuummăărr))
� nnuummăărr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce o conŃine.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică pozitivă extrasă dintr-un număr real.
�������� CCaallccuullaarreeaa ffaaccttoorriiaalluulluuii uunnuuii nnuummăărr..
FFuunnccŃŃiiaa FFAACCTT
SSiinnttaaxxăă ==FFAACCTT((nnuummăărr))
� nnuummăărr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce o conŃine.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică pozitivă ce reprezintă factorialul unui număr
(1*2*3* ....*număr).
�������� CCaallccuullaarreeaa uunnuuii nnuummăărr rriiddiiccaatt llaa ppuutteerree..
FFuunnccŃŃiiaa PPOOWWEERR
SSiinnttaaxxăă ==PPOOWWEERR((nnuummăărr ;; ppuutteerree))
� nnuummăărr reprezintă o constantă numerică pozitivă, o referinŃă celulară / nume de celulă ce o conŃine;
� ppuutteerree reprezintă o constantă numerică ce semnifică exponentul. RReezzuullttaatt O valoare ce reprezintă un număr ridicat la puterea specificată de
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 13
rreettuurrnnaatt argument.
OObbsseerrvvaaŃŃiiee.. Pentru a se ridica un număr la o putere, printr-o formulă simplă, se utilizează operatorul exponenŃial (^).
�������� CCaallccuullaarreeaa rrăăddăăcciinniiii ppăăttrraattee ddiinnttrr--uunn nnuummăărr..
FFuunnccŃŃiiaa SSQQRRTT
SSiinnttaaxxăă ==SSQQRRTT((nnuummăărr))
� nnuummăărr reprezintă o constantă numerică pozitivă, o referinŃă celulară / nume de celulă ce o conŃine.
RReezzuullttaatt rreettuurrnnaatt
O valoare ce reprezintă rădăcina pătrată extrasă dintr-un număr pozitiv.
Figura 12 ilustrează modul de utilizare a funcţiilor POWER şi SQRT.
Figura 12 Exemplu de utilizare a funcţiilor POWER şi SQRT
�� CCoonnvveerrssiiaa ffoorrmmaattuulluuii cciiffrriicc aarraabb îînn ffoorrmmaatt tteexxtt rroommaann..
FFuunnccŃŃiiaa RROOMMAANN
SSiinnttaaxxăă ==RROOMMAANN((nnuummăărr[[;;ffoorrmmaatt]]))
� nnuummăărr reprezintă o constantă numerică pozitivă (cuprinsă între 1 şi 3999), o referinŃă celulară / nume de celulă ce o conŃine;
� [[ffoorrmmaatt]] este un cod ce pleacă de la 0 (implicit este formatul clasic) la 4 şi semnifică gradul de concizie a numărului în format roman.
RReezzuullttaatt rreettuurrnnaatt
O valoare de tip şir de caractere ce semnifică transformarea unui număr din format cifric arab în format cifric roman.
Un exemplu de transformare a numărului 1999 din format cifric arab în format cifric roman (şir de
caractere) este prezentat în Figura 13.
Figura 13 Exemplu de utilizare a funcţiei ROMAN
�������� GGeenneerraarreeaa ddee nnuummeerree aalleeaattooaarree..
FFuunnccŃŃiiaa RRAANNDD
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 14
SSiinnttaaxxăă == RRAANNDD(())
� ffăărrăă aarrgguummeenntt RReezzuullttaatt rreettuurrnnaatt
Un număr aleator cuprins între 0 şi 1.
FFuunnccŃŃiiaa RRAANNDDBBEETTWWEEEENN
SSiinnttaaxxăă ==RRAANNDDBBEETTWWEEEENN((lliimmiittăă__iinnffeerriiooaarrăă ;; lliimmiittăă__ ssuuppeerriiooaarrăă))
� lliimmiittăă__iinnffeerriiooaarrăă reprezintă o constantă numerică întreagă, o referinŃă celulară / nume de celulă ce conŃine respectiva valoare numerică ce este considerată bornă inferioară;
� lliimmiittăă__ssuuppeerriiooaarrăă reprezintă o constantă numerică întreagă, o referinŃă celulară / nume de celulă ce conŃine respectiva valoare considerată bornă superioară.
RReezzuullttaatt rreettuurrnnaatt
Un număr aleator (întreg) cuprins între două limite, una inferioară şi una superioară.
OObbsseerrvvaaţţiiii..
La fiecare proces de recalcul, funcŃiile RAND şi RANDBETWEEN vor returna în mod aleator o nouă valoare. Excel recalculează toate formulele sale şi actualizează rezultatele odată cu orice intrare efectuată într-una dintre celulele sale. Procesul de recalcul poate fi activat explicit şi de către utilizator, odată cu acŃionarea tastei funcŃionale F9. Dacă se doreşte ca numerele aleatoare generate să nu mai fie înlocuite cu altele noi la fiecare proces de recalcul, se recomandă transformarea rezultatelor funcŃiilor RAND sau RANDBETWEEN în constante numerice prin convertirea acestor formule în valori (comanda Home�Copy la sursă şi Paste opŃiunea Paste Values). În Excel, funcŃia RANDBETWWEN nu este disponibilă în mod implicit. Aceasta se instalează la cererea utilizatorului, prin încărcarea unor componente. Acest demers este operaŃional prin comanda Office Button > Excel Options, tabul Add Ins > opŃiunea Analysis ToolPak.
Figura 14 ilustrează un exemplu prin care o societate comercială de distribuŃie organizează o tombolă cu ocazia promovării unui produs, ocazie cu care desemnează 5 câştigători într-o ordine aleatoare.
Figura 14 Exemplu de utilizare a funcţiei RANDBETWEEN
�������� CCaallccuullaarreeaa ppăărrţţiiii îînnttrreeggii aaffeerreennttee ccââttuulluuii uunneeii îîmmppăărrţţiirrii..
FFuunnccŃŃiiaa QQUUOOTTIIEENNTT
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 15
SSiinnttaaxxăă ==QQUUOOTTIIEENNTT((ddeeîîmmppăărrŃŃiitt ;; ddiivviizzoorr))
� ddeeîîmmppăărrŃŃiitt reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce conŃine respectiva valoare numerică ce urmează a fi divizată;
� ddiivviizzoorr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce conŃine respectiva valoare ce va fi considerată divizor.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă partea întreagă a rezultatului împărŃirii unui număr la altul.
Un exemplu de utilizare a funcţiei QUOTIENT este ilustrat în Figura 15.
Figura 15 Exemplu de utilizare a funcţiei QUOTIENT �������� CCaallccuullaarreeaa rreessttuulluuii îîmmppăărrţţiirriiii..
FFuunnccŃŃiiaa MMOODD
SSiinnttaaxxăă ==MMOODD((ddeeîîmmppăărrŃŃiitt ;; ddiivviizzoorr))
� ddeeîîmmppăărrŃŃiitt reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce conŃine respectiva valoare numerică ce urmează a fi divizată;
� ddiivviizzoorr reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce conŃine respectiva valoare ce va fi considerată divizor.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă restul împărŃirii primului argument la al doilea.
În exemplul ilustrat în Figura 16 este prezentată o formulă ce afişează dacă un număr este par sau
impar, după cum acesta se divide cu 2 (rest 0) sau nu (rest 1).
Figura 16 Exemplu de utilizare a funcţiei MOD
�������� CCaallccuullaarreeaa cceelluuii mmaaii mmaarree ddiivviizzoorr ccoommuunn..
FFuunnccŃŃiiaa GGCCDD
SSiinnttaaxxăă ==GGCCDD((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
O valoare ce este cel mai mare divizor comun al numerelor specificate ca argumente, adică cel mai mare număr întreg care divide fără rest lista de elemente numerice specificate.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 16
�������� CCaallccuullaarreeaa cceelluuii mmaaii mmiicc mmuullttiipplluu ccoommuunn..
FFuunnccŃŃiiaa LLCCMM
SSiinnttaaxxăă ==LLCCMM((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
O valoare ce este cel mai mic număr întreg care este multiplu comun pentru lista de elemente numerice specificate.
Figura 17 ilustrează două moduri de utilizare în care operează funcţiile ce returnează cel mai mare
divizor comun (CMMDC) şi cel mai mic multiplu comun (CMMMC).
Figura 17 Exemplu de utilizare a funcţiilor GCD şi LCM
�������� CCaallccuullaarreeaa ccoommbbiinnăărriilloorr ddee mm eelleemmeennttee,, lluuaattee ccââttee nn ..
FFuunnccŃŃiiaa CCOOMMBBIINN
SSiinnttaaxxăă ==CCOOMMBBIINN((nnuummăărr__eelleemmeennttee;;nnuummăărr__eelleemmeennttee__ppee__ggrruupp))
� nnuummăărr__eelleemmeennttee reprezintă un număr de elemente de repartizat;
� nnuummăărr__eelleemmeennttee__ppee__ggrruupp reprezintă numărul de elemente din fiecare combinare.
RReezzuullttaatt rreettuurrnnaatt
O valoare ce determină numărul de combinări pentru un număr dat de elemente, adică numărul total posibil de grupuri pentru un număr de elemente dat.
În Figura 18 este ilustrat un exemplu prin care se doreşte să se constituie echipe de control fiscal
pentru sectoarele 1 şi 6 din Municipiul Bucureşti, cunoscând numărul total de controlori fiscali
disponibili pentru fiecare sector în parte şi numărul de controlori fiscali ce fac parte din fiecare
echipă de control.
Figura 18 Exemplu de utilizare a funcţiei COMBIN �� ÎÎnnmmuullţţiirreeaa aa ddoouuăă mmaattrriiccee..
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 17
FFuunnccŃŃiiaa MMMMUULLTT
SSiinnttaaxxăă ==MMMMUULLTT((mmaattrriiccee__AA;;mmaattrriiccee__BB))
� mmaattrriiccee__AA şi mmaattrriiccee__BB reprezintă două matrice. RReezzuullttaatt rreettuurrnnaatt
O matricea produs ce are atâtea linii câte are prima matrice (A) şi atâtea coloane câte are a doua matrice (B).
OObbsseerrvvaaţţiiii..
Numărul de coloane aferente primei matrice trebuie să fie egal cu numărul de linii aferente celei de a doua matrice. Astfel, fiecare element (i,j) al matricei produs, reprezintă suma produselor termen cu termen a elementelor liniei i aferente primei matrice, cu elementele coloanei j, aferente celei de a doua matrice. Formula ce conŃine funcŃia MMULT se va introduce în formă matriceală. Un exemplu de produs a două matrice este prezentat în Figura 19.
Figura 19 Exemplu de utilizare a funcţiei MMULT �� CCaallccuulluull mmaattrriicceeii iinnvveerrssee..
FFuunnccŃŃiiaa MMIINNVVEERRSSEE
SSiinnttaaxxăă ==MMIINNVVEERRSSEE((mmaattrriiccee))
� mmaattrriiccee reprezintă o matrice stocată într-un tablou de date. RReezzuullttaatt rreettuurrnnaatt
Matricea inversă.
OObbsseerrvvaaţţiiii..
Formula ce conŃine funcŃia MINVERSE se va introduce în formă matriceală. Produsul unei matrice cu matricea sa inversă returnează matricea nulă. Un exemplu de calcul a matricei inverse şi de înmulŃire a acesteia cu matricea iniŃială este prezentat în Figura 20.
Figura 20 Exemplu de utilizare a funcţiei MINVERSE �� RReezzoollvvaarreeaa uunnuuii ssiisstteemm ddee mm eeccuuaaţţiiii ccuu nn nneeccuunnoossccuuttee..
Calculul matriceal permite şi rezolvarea de ecuaţii lineare.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 18
Prezentăm de exemplu, un
sistem de 4 ecuaţii cu 4
necunoscute, care se poate
scrie A*X=B, unde:
2x -3 y - z + t = -30
3x +4y +5z - t = 18
x -7 y +4z +4t = -16
-x +3y + z +6t = 9
A este matricea coeficienŃilor x, y, z, t;
X este vectorul ce reprezintă necunoscutele sistemului de ecuaŃii: x, y, z, t;
B este vectorul celui de al 2-lea membru, din dreapta: -30, 18, -16, 9.
Dacă determinantul matricei A este diferit de zero, matricea A este ireversibilă, atunci soluŃia sistemului de ecuaŃii se scrie: X = A-1*B, unde: A-1*B este produsul matricei inverse a lui A de înmulŃit cu B. Figura 21 ilustrează modul cum sunt organizate câmpurile într-o foaie de calcul, calculul matricei inverse a lui A şi înmulŃirea acesteia cu vectorul B.
Figura 21 Exemplu de rezolvare prin calcul matriceal a unui sistem de ecuaţii lineare cu n necunoscute
�� CCaallccuullaarreeaa ddeetteerrmmiinnaannttuulluuii uunneeii mmaattrriiccee..
FFuunnccŃŃiiaa MMDDEETTEERRMM
SSiinnttaaxxăă ==MMDDEETTEERRMM((mmaattrriiccee))
� mmaattrriiccee reprezintă o matrice stocată într-un tablou de date. RReezzuullttaatt rreettuurrnnaatt
Valoarea determinantului unei matrice.
Un exemplu de calcul a determinantului unei matrice este prezentat în Figura 22.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 19
Figura 22 Exemplu de utilizare a funcţiei MDETERM
FFuunnccţţiiii ddee rroottuunnjjiirree aa rreezzuullttaatteelloorr ccaallccuulleelloorr
ROUNDUP, ROUNDOWN, ROUND, INT, TRUNC, EVEN, ODD, FLOOR, CEILING, MROUND
�� RRoottuunnjjiirreeaa pprriinn aaddaaooss//lliippssăă aa uunnuuii nnuummăărr ccăăttrree zzeerroo..
FFuunnccŃŃiiiillee RROOUUNNDDUUPP // RROOUUNNDDDDOOWWNN
SSiinnttaaxxăă ==RROOUUNNDDUUPP//RROOUUNNDDDDOOWWNN((nnuummăărr__ddee__rroottuunnjjiitt ;;++//--ccoonnssttaannttăă))
� nnuummăărr__ddee__rroottuunnjjiitt reprezintă o constantă numerică cu zecimale, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente;
� ++//-- ccoonnssttaannttăă ddee rroottuunnjjiirree semnifică o valoare poziŃională la care să se efectueze rotunjirea.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică rotunjită prin adaos/lipsă la o anumită valoare poziŃională.
OObbsseerrvvaaţţiiee.. Dacă semnul constantei de rotunjire este negativ (-), rotunjirea operează la partea
întreagă, în stânga separatorului zecimal (de exemplu: -3=mii, -2=sute, -1=zeci, 0=întreg). Dacă
semnul constantei este pozitiv (+), rotunjirea operează la partea zecimală, în dreapta separatorului
zecimal (de exemplu: 3=miimi, 2=sutimi, 1=zecimi).
Figura 23 prezintă un exemplu de rotunjire prin lipsă a valorii veniturilor din exploatare aferente
unui cont de profit şi pierdere (la diferite valori poziţionale) şi un exemplu de rotunjire prin adaos a
cheltuielilor de exploatare aferente aceluiaşi document financiar.
�� RRoottuunnjjiirreeaa uunneeii vvaalloorrii nnuummeerriiccee pprriinn aaddaaooss ssaauu lliippssăă..
FFuunnccŃŃiiaa RROOUUNNDD
SSiinnttaaxxăă ==RROOUUNNDD((nnuummăărr__ddee__rroottuunnjjiitt ;; ++//--ccoonnssttaannttăă ddee rroottuunnjjiirree))
� nnuummăărr__ddee__rroottuunnjjiitt reprezintă o constantă numerică cu zecimale, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente;
� ++//-- ccoonnssttaannttăă ddee rroottuunnjjiirree semnifică o valoare poziŃională la care să se efectueze rotunjirea.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică rotunjită la o valoare poziŃională.
OObbsseerrvvaaţţiiee.. Rotunjirea valorilor numerice se face prin adaos sau lipsa, după cum valorile poziţionale
de la care pleacă rotunjirea sunt mai mari sau egale decât 5 (rotunjire prin adaos) sau mai mici decât
5 (rotunjire prin lipsă). Dacă semnul constantei de rotunjire este negativ (-), rotunjirea operează la
partea întreagă, altfel, dacă semnul constantei este pozitiv (+), rotunjirea operează la partea
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 20
zecimală. Dacă valoarea constantei de rotunjire este zero, atunci rotunjirea va opera la nivelul
următoarei valori întregi a numărului.
Figura 23 Exemple de utilizare a funcţiilor ROUNDDOWN, ROUNDUP şi ROUND
�� AAffiişşaarreeaa ppăărrţţiiii îînnttrreeggii aa uunnuuii nnuummăărr rreeaall..
FFuunnccŃŃiiaa IINNTT
SSiinnttaaxxăă ==IINNTT((nnuummăărr__ddee__rroottuunnjjiitt))
� nnuummăărr__ddee__rroottuunnjjiitt reprezintă o constantă numerică, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică rotunjită prin lipsă până la cel mai apropiat întreg.
FFuunnccŃŃiiaa TTRRUUNNCC
SSiinnttaaxxăă ==TTRRUUNNCC((nnuummăărr__ddee__ttrruunncchhiiaatt))
� nnuummăărr__ddee__ttrruunncchhiiaatt reprezintă o constantă numerică cu zecimale, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente.
RReezzuullttaatt
rreettuurrnnaatt
O valoare numerică trunchiată la nivelul părŃii sale întregi, fără rotunjire.
În exemplul prezentat în Figura 24 se calculează vechimea în muncă a unui salariat, ca diferenţă
între data curentă a sistemului şi la data angajării sale, raportându-se rezultatul la numărul de zile
dintr-un an (365). Numărul rezultat ce reprezintă vechimea în ani, nu poate fi utilizat rotunjit în
calculul sporului de vechime, deoarece prin acest calcul se poate accede către o altă tranşe de
vechime necuvenită (24,73 ani devin la rotunjire 25 ani şi deci se încadrează în altă tranşă de
vechime). Din aceste motive, se recomandă ca rezultatul calculului de vechime să fie trunchiat la
partea întreagă sau să fie rotunjit prin lipsă la nivelul părţii întregi a rezultatului.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 21
Figura 24 Exemplu de utilizare a funcţiilor INT şi TRUNC � RRoottuunnjjiirreeaa pprriinn aaddaaooss aa uunnuuii nnuummăărr ccăăttrree cceell mmaaii aapprrooppiiaatt îînnttrreegg ppaarr ssaauu iimmppaarr.
FFuunnccŃŃiiaa EEVVEENN
SSiinnttaaxxăă ==EEVVEENN((nnuummăărr__ddee__rroottuunnjjiitt))
� nnuummăărr__ddee__rroottuunnjjiitt reprezintă o constantă numerică, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică rotunjită prin adaos către cel mai apropiat număr întreg par.
FFuunnccŃŃiiaa OODDDD
SSiinnttaaxxăă ==OODDDD((nnuummăărr__ddee__rroottuunnjjiitt))
� nnuummăărr__ddee__rroottuunnjjiitt reprezintă o constantă numerică, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică rotunjită prin adaos către cel mai apropiat număr întreg impar.
�� RRoottuunnjjiirreeaa uunnuuii nnuummăărr pprriinn ll iippssăă ssaauu aaddaaooss,, ccăăttrree zzeerroo,, ppeennttrruu cceell mmaaii aapprrooppiiaatt mmuullttiipplluu ssaauu ssuubbmmuullttiipplluu ddee rroottuunnjjiirree..
FFuunnccŃŃiiaa FFLLOOOORR // CCEEIILLIINNGG
SSiinnttaaxxăă ==FFLLOOOORR((nnuummăărr__ddee__rroottuunnjjiitt ;; mmuullttiipplluu//ssuubbmmuullttiipplluu__ddee__rroottuunnjjiirree))
==CCEEIILLIINNGG((nnuummăărr__ddee__rroottuunnjjiitt;;mmuullttiipplluu//ssuubbmmuullttiipplluu__ddee__rroottuunnjjiirree))
� nnuummăărr__ddee__rroottuunnjjiitt reprezintă o constantă numerică, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente;
� mmuullttiipplluu__ddee__rroottuunnjjiirree reprezintă o constantă numerică întreagă ce semnifică un multiplu de rotunjire.
� ssuubbmmuullttiipplluu__ddee__rroottuunnjjiirree reprezintă o constantă numerică zecimală ce inhibă rotunjirea în mod poziŃional astfel: 0,001� se inhibă rotunjirea miilor; 0,01� se inhibă rotunjirea sutimilor; 0,1� se rotunjeşte la nivelul zecimilor.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică rotunjită prin lipsă (FLOOR) sau prin adaos (CEILING) în funcŃie de un multiplu sau submultiplu de rotunjire, corespunzător unei valori poziŃionale precizate.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 22
Figura 25 ilustrează funcţiile de rotunjire EVEN, ODD, FLOOR şi CEILING în contextul unui exemplu
ce prezintă calculul mediei (AVERAGE) notelor (câmpul B13:B16 se numeşte „Note”) la un examen.
Figura 25 Exemplu de utilizare a funcţiilor EVEN, ODD, FLOOR şi CEILING
�� RRoottuunnjjiirreeaa uunnuuii nnuummăărr llaa uunn mmuullttiipplluu ccee ccoorreessppuunnddee uunneeii vvaalloorrii nnuummeerriiccee iinnffeerriiooaarree ssaauu
ssuuppeerriiooaarree nnuummăărruulluuii rreeaall..
FFuunnccŃŃiiaa MMRROOUUNNDD
SSiinnttaaxxăă ==MMRROOUUNNDD((nnuummăărr__ddee__rroottuunnjjiitt;;mmuullttiipplluu__ddee__rroottuunnjjiirree))
� nnuummăărr__ddee__rroottuunnjjiitt reprezintă o constantă numerică, rezultatul unei expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente;
� mmuullttiipplluu__ddee__rroottuunnjjiirree reprezintă o constantă numerică întreagă ce semnifică un multiplu de rotunjire.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică rotunjită prin adaos sau prin lipsă către cel mai apropiat multiplu de rotunjire.
OObbsseerrvvaaţţiiii::
FuncŃia MROUND rotunjeşte prin adaos un număr, către următorul multiplu de rotunjire, dacă restul împărŃirii numărului la multiplu este mai mare sau egal cu jumătate din valoarea multiplului de rotunjire. Altfel spus, dacă un număr de rotunjit este mai aproape de limita superioară a intervalului (sau este egal cu mijlocul acestuia) dintre doi multipli consecutivi de rotunjire aplicaŃi numărului, atunci rotunjirea va fi făcută prin adaos către următorul (cel mai mare) multiplu de rotunjire. Dacă un număr de rotunjit este mai aproape de limita inferioară a intervalului dintre doi multipli consecutivi de rotunjire aplicaŃi numărului, atunci rotunjirea va fi făcută prin lipsă către cel mai mic multiplu de rotunjire. În Excel, funcŃia MROUND reprezintă o componentă ce se instalează la cerere, prin comanda Office Button� Excel Options � Add-Ins � opŃiunea Analysis ToolPak. Exemplul prezentat în Figura 26 ilustrează modul în care este calculat un preŃ ce este rotunjit la diferiŃi multipli de rotunjire (500, 1.000, 2.000, 3.000, 4.000 şi 5.000 lei). În celula C28, preŃul (B28 = 681 lei) a fost rotunjit la un multiplu de 500 lei, rezultatul acestei operaŃii fiind de 6.500 lei.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 23
Figura 26 Exemplu de utilizare a funcţiei MROUND Explicaţia rezultatului returnat de funcţia MROUND constă în faptul că: „următorul multiplu de
rotunjire de 500 de lei aplicat preţului este 6.500 lei. Se evaluează dacă valoarea preţului nerotunjit
de 681 lei este mai aproape de multiplul inferior de rotunjire (6.000 lei) sau de multiplul superior de
rotunjire (6.500 lei). Din această evaluare, rezultă că preţul vechi este mai apropiat ca valoare de
limita superioară a intervalului (mijlocul intervalului fiind de 650) şi în consecinţă, numărul va fi
rotunjit prin adaos la următorul multiplu de rotunjire (de 500 lei), adică la 6.500 lei. Acest exemplu a
confirmat regula potrivit căreia „rotunjirea prin adaos se aplică atunci când multiplul de rotunjire
cel mai apropiat este superior numărului ce se doreşte a fi rotunjit”.
Acelaşi raŃionament a fost aplicat de funcŃia MROUND şi în celula C29, pentru rotunjirea preŃului editat în celula B29. Rotunjirea a fost efectuată prin lipsă la nivelul unui multiplu de 1.000 lei, deoarece vechiul preŃ (681 lei) era mai apropiat ca valoare de limita inferioară a intervalului (6.000 lei) decât de cea superioară (7.000 lei). În acest exemplu s-a confirmat regula potrivit căreia „rotunjirea prin lipsă se aplică atunci când multiplul de rotunjire cel mai apropiat este inferior numărului ce se doreşte a fi rotunjit”. Exemplul ilustrat de Figura 27 rotunjeşte preŃul de 681 lei (din celula B62) pentru diferiŃi multipli de rotunjire declaraŃi pe intervalul (A64:A68), fie prin adaos sau lipsă prin funcŃia MROUND editată în câmpul (B64:B68), fie explicit prin adaos prin funcŃia CEILING (C64:C68), fie explicit prin lipsă prin funcŃia FLOOR (C64:C68). De asemenea, aceeaşi figură mai prezintă un exemplu de rotunjire prin lipsă la partea zecimală a unei medii aritmetice.
Figura 27 Exemplu de rotunjire prin adaos sau lipsa la un multiplu de rotunjire
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 24
Figura 28 ilustrează un exemplu de descrie un context în care un magazin doreşte să rotunjească preŃurile reale ale produselor sale la diferiŃi multipli de rotunjire, iar apoi să ajusteze aceste preŃuri la un nivel „psihologic” (preŃurile au terminaŃia 999, 990 sau 900). Formula editată în celula C31, testează dimensiunea în caractere a valorii preţului, iar în funcţie de
rezultatul evaluării, rotunjeşte succesiv preţul pe intervalele corespunzătoare unui multiplu. De
exemplu, dacă preţul are mai puţin sau egal cu 4 caractere (între 1 şi 9.999 lei), preţul se rotunjeşte
la un multiplu de 1.000 lei, dacă preţul are în componenţă 5 caractere (între 10.000 şi 99.999 lei),
acesta se rotunjeşte la un multiplu de 10.000 lei, dacă preţul are în componenţă 6 caractere (între
100.000 şi 999.999 lei), acesta se rotunjeşte la un multiplu de 100.000 lei, iar dacă preţul are în
componenţă mai mult de 6 caractere (peste 1.000.000 lei), rotunjirea va fi făcută la un multiplu de
1.000.000 lei.
Tot Figura 28 ilustrează în celula D28 un exemplu de calcul al unui preţ „psihologic” prin scăderea
unor unităţi valorice de preţ din preţul rotunjit succesiv pe intervalele corespunzătoare unui multiplu
de rotunjire.
Figura 28 Exemplu de rotunjire a unui preţ „psihologic” prin funcţia MROUND
2.2 Funcţii statitice
Funcţiile statistice permit efectuarea de calcule statistice utilizând serii de valori şi distribuţii
statistice.
MAX, MIN, AVERAGE, AVERAGEIF, AVERAGEIFS, GEOMEAN, HARMEAN, MEDIAN, MODE,
COUNT, COUNTA, COUNTIF, COUNTIFS, STDEVP, STDEVPA, STDEV, STDEVA, FREQUENCY,
QUARTILE, VARP, VARPA, VAR, VARA, COVAR, CORREL, LINEST, FORECAST, TREND
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 25
�� IIddeennttiiffiiccaarreeaa cceelleeii mmaaii mmaarrii // mmiiccii vvaalloorrii ddiinnttrr--oo sseerriiee ddee ddaattee..
FFuunnccŃŃiiaa MMAAXX
SSiinnttaaxxăă ==MMAAXX((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
Valoarea cea mai mare dintr-o distribuŃie statistică de date.
FFuunnccŃŃiiaa MMIINN
SSiinnttaaxxăă ==MMIINN((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
Valoarea cea mai mică dintr-o distribuŃie statistică de date.
�� CCaallccuulluull mmeeddiieeii aarriittmmeettiiccee aaffeerreennttee uunneeii sseerriiii ssttaattiissttiiccee ddee ddaattee..
FFuunnccŃŃiiaa AAVVEERRAAGGEE
SSiinnttaaxxăă ==AAVVEERRAAGGEE((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
Media aritmetică dintr-o distribuŃie statistică de date. Media este calculată ca o sumă de elemente numerice raportată la numărul de elemente.
OObbsseerrvvaaţţiiii::
Dacă un câmp pe care se calculează media conŃine celule vide, şiruri de caractere, valori logice, aceste elemente nu sunt incluse la calcularea mediei. Dacă se doreşte includerea valorilor logice (TRUE sau FALSE) în calculul mediei aritmetice se va utiliza funcŃia AVERAGEA, care va echivala valoarea TRUE cu 1 şi valoarea FALSE cu 0.
aa.. Dacă respectivul câmp pe care se calculează media conţine valoarea zero, aceasta intră în calculul mediei.
Dacă lista de elemente pe care se calculează media este o mulŃime vidă, atunci funcŃia AVERAGE va returna o valoare de eroare de tipul #DIV/0!, ce semnifică o împărŃire la zero.
bb.. Dacă plaja de valori pe care se calculează media conţine o valoare de eroare returnată de o funcţie, atunci media va genera la rândul ei o valoare de eroare.
Figura 29 prezintă un exemplu de utilizare a funcţiilor MAX, MIN şi AVERAGE, în contextul analizei
cursului valutar al monedei Euro. Aplicaţia evidenţiază pentru fiecare zi (lucrătoare) de cotaţie
recordul maximal, minimal şi media mobilă.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 26
Figura 29 Exemplu de utilizare a funcţiilor MAX, MIN şi AVERAGE
�� CCaallccuulluull ccoonnddiiţţiioonnaall aall mmeeddiieeii aarriittmmeettiiccee aaffeerreennttee uunneeii sseerriiii ssttaattiissttiiccee ddee ddaattee..
FFuunnccŃŃiiaa AAVVEERRAAGGEEIIFF
SSiinnttaaxxăă ==AAVVEERRAAGGEEIIFF((ccââmmpp__ddee__eevvaalluuaatt ;; ccrriitteerriiuull__sseelleeccŃŃiiee
[[;;ccââmmpp__ddee__ccaallccuullaatt__mmeeddiiaa]]))
� ccââmmpp__ddee__eevvaalluuaatt reprezintă o listă compusă din adrese de celule sau nume de câmpuri. Asupra acestui argument operează condiŃia specificată de argumentul următor;
� ccrriitteerriiuull__sseelleeccŃŃiiee reprezintă o condiŃie impusă ccââmmppuulluuii ddee eevvaalluuaatt, care se specifică sub formă de şir de caractere (între ghilimele);
� [[ccââmmpp__ddee__ccaallccuullaatt__mmeeddiiaa]] este argumentul care desemnează câmpul pe care se va calcula media aritmetică, în funcŃie de evaluarea condiŃiei precizate de al doilea argument. Dacă acest argument lipseşte, ccââmmppuull ddee ccaallccuullaatt mmeeddiiaa va fi chiar ccââmmppuull ddee eevvaalluuaatt.
RReezzuullttaatt rreettuurrnnaatt
Media aritmetică a elementelor unei liste în funcŃie de evaluarea unei condiŃii precizate.
�� CCaallccuullaarreeaa uunneeii mmeeddiiii aarriittmmeettiiccee îînn ffuunnccţţiiee ddee eevvaalluuaarreeaa mmaaii mmuullttoorr ccoonnddiiţţiiii..
FFuunnccŃŃiiaa AAVVEERRAAGGEEIIFFSS
SSiinnttaaxxăă ==AAVVEERRAAGGEEIIFFSS((ccââmmpp__ddee__ccaallccuullaatt__mmeeddiiaa;; ccââmmpp__ddee__eevvaalluuaatt__11 ;;
ccrriitteerriiuull__sseelleeccŃŃiiee__11 [[;;ccââmmpp__ddee__eevvaalluuaatt__22;;ccrriitteerriiuull__sseelleeccŃŃiiee__22]].............. ......))
� ccââmmpp__ddee__ccaallccuullaatt__mmeeddiiaa este argumentul care desemnează câmpul pe care va fi calculată media în funcŃie de evaluarea condiŃiei sau condiŃiilor precizate.
� ccââmmpp__ddee__eevvaalluuaatt__11 reprezintă o listă compusă din adrese de celule sau nume de câmpuri. Asupra acestui argument operează condiŃia specificată de argumentul următor;
� ccrriitteerriiuull__sseelleeccŃŃiiee__11 reprezintă o condiŃie impusă ccââmmppuulluuii ddee eevvaalluuaatt__11, care se
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 27
specifică sub formă de egalitate sau restricŃie (între ghilimele); RReezzuullttaatt rreettuurrnnaatt
Media aritmetică a elementelor unei liste în funcŃie de evaluarea mai multor condiŃii precizate.
Figura 30 ilustrează calculul mediei aritmetice în celula D141 ce ia în considerare toate notele din
câmpul rezervat notelor la examen (D132:D139), adică operează calculul mediei şi pentru cei care nu
au promovat examenul şi în consecinţă au nota mai mică decât 5. Câmpul ce conţine numele şi
prenumele (B132:B139) este formatat condiţional în funcţie de faptul dacă nota de la examen este
superioară mediei tuturor.
Calculul condiţional al mediei este operaţional în celula D142, şi restricţionează calculul la valorile ce
sunt mai mari sau egale decât 5. Câmpul ce conţine numărul matricol (A132:A139) este formatat
condiţional pentru a pune în evidenţă codurile studenţilor cu nota la examen peste media celor
promovaţi.
Figura 30 Exemplu de calcul a mediei aritmetice şi formatare condiţională pentru două condiţii precizate.
�� CCaallccuulluull mmeeddiieeii ggeeoommeettrriiccee şşii aarrmmoonniiccee aaffeerreennttee uunneeii sseerriiii ssttaattiissttiiccee ddee ddaattee..
FFuunnccŃŃiiaa GGEEOOMMEEAANN
SSiinnttaaxxăă ==GGEEOOMMEEAANN((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
Media geometrică dintr-o distribuŃie statistică de date.
FFuunnccŃŃiiaa HHAARRMMEEAANN
SSiinnttaaxxăă ==HHAARRMMEEAANN((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
Media armonică dintr-o distribuŃie statistică de date.
�� IIddeennttiiffiiccaarreeaa uunneeii vvaalloorrii îînn ffuunnccţţiiee ddee oorrddiinnuull ddee mmăărriimmee aall aacceesstteeiiaa..
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 28
FFuunnccŃŃiiaa LLAARRGGEE
SSiinnttaaxxăă ==LLAARRGGEE((lliissttăă__ddee__eelleemmeennttee ;; nnuummăărr ))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, formule sau nume de câmpuri.
� nnuummăărr reprezintă o valoare de clasare în sens crescător, cu alte cuvinte poziŃia unei valori în raport de ordinul de mărime al valorii celulelor din câmpul specificat ca argument.
RReezzuullttaatt rreettuurrnnaatt
A n-a valoare ca mărime în sens descrescător, plecând de la valoarea maximală.
FFuunnccŃŃiiaa SSMMAALLLL
SSiinnttaaxxăă ==SSMMAALLLL((lliissttăă__ddee__eelleemmeennttee ;; nnuummăărr ))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, formule sau nume de câmpuri.
� nnuummăărr reprezintă o valoare de clasare în sens descrescător. RReezzuullttaatt rreettuurrnnaatt
A n-a valoare ca mărime în sens crescător, plecând de la valoarea minimală.
Un exemplu de utilizare a funcţiilor LARGE şi SMALL este prezentat în Figura 31, prin care se
identifică în celula G2 a doua valoare (F2) în sens descrescător, plecând de la valoarea maximală, iar
în celula G5 a treia valoare (F5) în sens crescător, plecând de la valoarea minimală.
Figura 31 Exemplu de utilizare a funcţiilor LARGE şi SMALL
�� CCaallccuullaarreeaa mmeeddiiaanneeii aaffeerreennttee uunneeii sseerriiii ddee ddaattee ssttaattiissttiiccee..
FFuunnccŃŃiiaa MMEEDDIIAANN
SSiinnttaaxxăă ==MMEEDDIIAANN((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule,
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 29
formule sau nume de câmpuri. RReezzuullttaatt rreettuurrnnaatt
Mediana distribuŃiei statistice a elementelor listei.
OObbsseerrvvaaţţiiii..
Valoarea mediană împarte un ansamblu de date în două subansambluri egale, de o astfel de manieră încât “la atâtea valori superioare mediei vor corespunde atâtea valori inferioare mediei”. Dacă numărul datelor este par, funcŃia MEDIAN calculează media celor două numere situate la mijlocul seriei statistice. FuncŃia MEDIAN poate fi simulată cu ajutorul funcŃiei condiŃionale IF, astfel: „dacă numărul de elemente aferent distribuŃiei este par, atunci se calculează media între ultimul element al primei jumătăŃi şi primul element al ultimei jumătăŃi, altfel se ia ca referinŃă elementul situat la jumătatea distribuŃiei statistice”.
Figura 32 prezintă două exemple de utilizare a funcţiei MEDIAN, unul pentru o distribuţie cu un
număr impar de elemente, iar celălalt pentru o serie statistică cu un număr par.
Figura 32 Exemplu de utilizare a funcţiei MEDIAN �� CCaallccuullaarreeaa cceelleeii mmaaii ffrreeccvveennttee aappaarriiţţiiii ddiinnttrr--oo ddiissttrriibbuuţţiiee ssttaattiissttiiccăă ddee ddaattee..
FFuunnccŃŃiiaa MMOODDEE
SSiinnttaaxxăă ==MMOODDEE((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, rezultate ale formulelor sau nume de câmpuri.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 30
RReezzuullttaatt rreettuurrnnaatt
Valoarea cea mai frecventă dintr-o distribuŃie statistică de date.
OObbsseerrvvaaţţiiii::
Dacă distribuŃia statistică de date nu conŃine valori duplicate, funcŃia MODE va returna un mesaj de eroare de tipul „Not Available” (#N/A!); Dacă distribuŃia statistică de date conŃine mai multe seturi de valori duplicate, egale ca frecvenŃă de apariŃie, funcŃia MODE va returna cea mai frecventă apariŃie a valorii situate la adresa cea mai mică;
a. Dacă o distribuţie statistică de date conţine celule vide, şiruri de caractere, valori logice, acestea nu vor fi incluse la calculul celei mai frecvente apariţii.
Dacă respectiva serie statistică conŃine valoarea zero, valorile acesteia intră în calculul pe care operează funcŃia MODE;
b. Este de recomandat ca atunci când se calculează cea mai frecventă apariţie a unei valori numerice într-o distribuţie statistică de date, aceasta să poată fi localizată vizual printr-o formatare condiţională.
Figura 33 ilustrează un exemple de utilizare a funcţiei MODE, prin care s-au evidenţiat cele mai
frecvente note obţinute la cele trei examene. Pentru ca acestea să devină vizibile s-a operat câte o
formatare condiţională pentru fiecare examen în parte.
Figura 33 Exemplu de utilizare a funcţiei MODE �� CCaallccuullaarreeaa nnuummăărruulluuii ddee eelleemmeennttee aaffeerreennttee uunneeii ddiissttrriibbuuţţiiii ssttaattiissttiiccee..
FFuunnccŃŃiiiillee CCOOUUNNTT // CCOOUUNNTTAA
SSiinnttaaxxăă ==CCOOUUNNTT // CCOOUUNNTTAA((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, formule sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
Numărul de elemente aferente unei distribuŃii statistice.
ObservaŃii. aa.. FuncŃia COUNT ia în considerare numai elementele numerice ale distribuŃiei statistice,
returnând numărul acestora.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 31
bb.. FuncŃia COUNTA ia în considerare alături de elementele numerice ale unei distribuŃii statistice şi celulele ce conŃin valori logice şi şiruri de caractere, returnând numărul acestor elemente.
cc.. FuncŃiile COUNT şi COUNTA nu iau în considerare celulele vide, chiar dacă acestea sunt intercalate între elementele nevide ale distribuŃiei statistice.
�������� CCaallccuullaarreeaa nnuummăărruulluuii ddee eelleemmeennttee aaffeerreennttee uunneeii ddiissttrriibbuuţţiiii ssttaattiissttiiccee,, îînn ffuunnccţţiiee ddee eevvaalluuaarreeaa uunneeii ccoonnddiiţţiiii..
FFuunnccŃŃiiaa CCOOUUNNTTIIFF
SSiinnttaaxxăă ==CCOOUUNNTTIIFF((ccââmmpp__ddee__eevvaalluuaatt ;; ccrriitteerriiuu__ddee__sseelleeccŃŃiiee))
� ccââmmpp__ddee__eevvaalluuaatt reprezintă o listă compusă din constante numerice, celule, formule sau nume de câmpuri;
� ccrriitteerriiuu__ddee__sseelleeccŃŃiiee reprezintă o condiŃie care se specifică sub formă de şir de caractere (între ghilimele).
RReezzuullttaatt rreettuurrnnaatt
Numărul de elemente dintr-o serie de date în funcŃie de precizarea unei condiŃii.
�������� CCaallccuullaarreeaa nnuummăărruulluuii ddee eelleemmeennttee aaffeerreennttee uunneeii ddiissttrriibbuuţţiiii ssttaattiissttiiccee,, îînn ffuunnccţţiiee ddee eevvaalluuaarreeaa uunneeii mmaaii mmuullttoorr ccoonnddiiţţiiii..
FFuunnccŃŃiiaa CCOOUUNNTTIIFFSS
SSiinnttaaxxăă ==CCOOUUNNTTIIFFSS((ccââmmpp__ddee__eevvaalluuaatt__11;;
ccrriitteerriiuull__sseelleeccŃŃiiee__11 [[;;ccââmmpp__ddee__eevvaalluuaatt__22;;ccrriitteerriiuull__sseelleeccŃŃiiee__22]].............. ......))
� ccââmmpp__ddee__eevvaalluuaatt__11 reprezintă o listă compusă din adrese de celule sau nume de câmpuri. Asupra acestui argument operează condiŃia specificată de argumentul următor;
� ccrriitteerriiuull__sseelleeccŃŃiiee__11 reprezintă o condiŃie impusă ccââmmppuulluuii ddee eevvaalluuaatt__11, care se specifică sub formă de egalitate sau restricŃie (între ghilimele);
RReezzuullttaatt rreettuurrnnaatt
Numărul elementelor unei liste în funcŃie de evaluarea mai multor condiŃii precizate.
Figura 34 ilustrează câteva exemple de utilizare a funcţiilor COUNT, COUNTA, COUNTBLANK,
COUNTIF şi COUNTIFS. Funcţia COUNT calculează numărul de elemente (numerice) ale câmpului
„Valoare” (E3:E8), iar COUNTA numără elementele alfanumerice (fără celule vide) ale câmpului
A3:A10. COUNTBLANK numără celulele vide situate în câmpul A3:A8. Funcţia COUNTIF identifică în
celula E12 numărul de elemente de tip „valoare” care depăşesc 60.000 lei. Prima funcţie COUNTIFS
editată în celula E13 calculează numărul de produse facturate pentru cantităţi între două borne, iar a
doua, editată în E14 calculează numărul de produse facturate la o anumită dată (12 martie 2009) şi
peste o anumită valoare precizată printr-o restricţie.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 32
Figura 34 Exemplu de utilizare a funcţiilor COUNT, COUNTA, COUNTBLANK, COUNTIF şi COUNTIFS
Un alt exemplu de utilizare a funcţiei COUNTIF este ilustrat în Figura 35 printr-o procedură de
punere în evidenţă a valorilor duplicat, prin formatare condiţională. Formula editată pe plaja de
celule A20:A27, numără cu funcţia COUNTIF fiecare element al seriei de date şi compară rezultatul
cu valoarea supraunitară. Altfel spus, dacă fiecare element al seriei apare de mai multe ori se
activează condiţia pentru formatare.
Figura 35 Exemplu de localizare a valorilor duplicat prin funcţia COUNTIF Un alt exemplu prezentat în Figura 36 calculează media notelor la examenul de „Aplicaţii Office”
(câmpul B33:B37) cu funcţia AVERAGE numai dacă fiecare notă individuală depăşeşte pragul de
promovare. Astfel, dacă numărul notelor mai mici ca 5 este zero (adică, toate disciplinele sunt
promovate), atunci se va calcula media, iar altfel se va afişa textul „Nepromovat”.
Figura 36 Exemplu de calcul a mediei aritmetice numai dacă fiecare element ce o
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 33
compune îndeplineşte o condiţie
�������� VVeennttiillaarreeaa ddaatteelloorr uunneeii sseerriiii ddee vvaalloorrii îînn ccllaassee ddee nnuummeerree şşii nnuummăărraarreeaa vvaalloorriilloorr aasseemmăănnăăttooaarree
ggrruuppaattee îînn aacceessttee ccllaassee..
FFuunnccŃŃiiaa FFRREEQQUUEENNCCYY
SSiinnttaaxxăă == FFRREEQQUUEENNCCYY ((ttaabblloouu__ddee__vvaalloorrii ;; ccoollooaannăă__iinntteerrvvaallee__vvaalloorrii))
� ttaabblloouu__ddee__vvaalloorrii reprezintă seria de valori pentru care va fi calculată frecvenŃa pe clase (grupuri). Datele numerice pot fi organizate pe mai multe coloane ce formează un tablou;
� ccoollooaannăă__iinntteerrvvaallee__vvaalloorrii identifică pe coloană valorile limitelor intervalelor ce aparŃin claselor de valori. Prima valoare (V1) aparŃine intervalului (-∞,V1], a doua valoare (V2) aparŃine intervalului (V1,V2] şi aşa mai departe, până la ultima valoare.
RReezzuullttaatt rreettuurrnnaatt
Numărul de valori ce partajează aceleaşi caracteristici de clasă (calculează numărul de apariŃii corespunzătoare unui interval de valori precizat).
OObbsseerrvvaaŃŃiiii.
aa.. FuncŃia FREQUENCY trebuie introdusă în forma matriceală completă (validarea formulei se face cu Ctrl+Shift+Enter).
bb.. FrecvenŃa de apariŃie a unui element poate fi calculată şi prin intermediul tabelei de ipoteze cu o variabilă, cu ajutorul funcŃiilor DataBase (acest subiect va fi tratat pe larg în paragraful rezervat instrumentelor de asistare a deciziei);
cc.. Dacă se doreşte a se calcula frecvenŃa de apariŃie a unor elemente de tip text (şiruri de caractere), se va atribui o valoare numerică pentru fiecare apariŃie distinctă a unui element de text. Apoi, se va calcula distribuŃia pe baza valorilor numerice cu care au fost asignate şirurile de caractere.
Exemplificarea funcŃiei FREQUENCY este ilustrată printr-o aplicaŃie în care (Figura 37) este prezentat un exemplu de analiză a calificativelor şi a distribuŃiei notelor ce rezultă în urma unui examen.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 34
Figura 37 Exemplu de utilizare a funcţiei FREQUENCY �� CCaallccuullaarreeaa qquuaarrttiilleelloorr llaa oo vvaallooaarree ccee rreeggrruuppeeaazzăă 2255%%,, 5500%%,, 7755%% ssaauu 110000%% ddiinn eeffeeccttiivvuull ddaatteelloorr
oobbsseerrvvaattee..
FFuunnccŃŃiiaa QQUUAARRTTIILLEE
SSiinnttaaxxăă == QQUUAARRTTIILLEE ((ttaabblloouu__ddee__vvaalloorrii;; qquuaarrttiillăă))
� ttaabblloouu__ddee__vvaalloorrii reprezintă ansamblul datelor asupra cărora se va calcula quartila (Quart);
� qquuaarrttiillăă semnifică numărul de quartilă de calculat: 1=25% din efectiv, 2=50% din efectiv, 3=75% din efectiv şi 4=100% din efectiv (valoarea zero reprezintă valoarea minimă a seriei de valori).
RReezzuullttaatt rreettuurrnnaatt
O valoare ce regrupează un anumit procent din efectivul datelor observate.
OObbsseerrvvaaŃŃiiee. FuncŃia QUARTILE se introduce matriceal.
Un exemplu de utilizare a funcŃiilor FREQUENCY şi QUARTILE este prezentat în Figura 38. În exemplul dat este prezentat cazul unei companii aeriene care doreşte distribuirea uniformă a greutăŃii bagajelor pasagerilor săi. Pentru aceasta, se ventilează rezultatele (câmpul B64:B67) în patru clase de valori ale căror limite superioare sunt cunoscute (câmpul A64:A67). Apoi, se calculează (în câmpul B73:B76) diferitele valori ale quartilelor ce regrupează 25%, 50%, 75%, 100% din efectiv. În final, prin intermediul funcŃiei FREQUENCY se calculează (în câmpul C73:C76) efectivul pentru fiecare quartilă în parte.
Figura 38 Exemplu de utilizare a funcţiilor FREQUENCY şi QUARTILE �� CCaallccuullaarreeaa vvaarriiaannţţeeii ssaauu aa iinnddiicceelluuii ddee ddiissppeerrssiiee aaffeerreenntt uunneeii ddiissttrriibbuuţţiiii..
FFuunnccŃŃiiiillee VVAARRPP // VVAARRPPAA // VVAARR // VVAARRAA
SSiinnttaaxxăă ==VVAARRPP // VVAARRPPAA // VVAARR // VVAARRAA((lliissttăă__ddee__eelleemmeennttee))
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 35
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, formule sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
VarianŃa distribuŃiei elementelor listei.
ObservaŃii. aa.. VarianŃa permite caracterizarea dispersiei datelor dintr-o distribuŃie statistică. VarianŃa este
întotdeauna pozitivă. De exemplu, în cazul unei distribuŃii gauss-iene (clopotul lui Gauss), varianŃa exprimă aplatizarea curbei: cu cât curba este mai ascuŃită, cu atât varianŃa este mai mică.
bb.. În condiŃiile în care se urmăreşte calcularea varianŃei pe o întreagă populaŃie statistică se utilizează funcŃia VARP. În acest caz, formula de calcul a varianŃei este: (nΣx2-(Σx)2)/n2. Această funcŃie ignoră în argumentele sale, elementele de tip şir de caractere şi valorile logice. Dacă se doreşte ca aceste elemente să nu fie ignorate, se utilizează funcŃia VARPA.
cc.. În condiŃiile în care se urmăreşte calcularea varianŃei pe un eşantion al populaŃiei statistice se utilizează funcŃia VAR. În acest caz, formula de calcul a varianŃei este: (nΣx2-(Σx)2)/n(n-1). Această funcŃie ignoră în argumentele sale, elementele de tip şir de caractere şi valorile logice. Dacă se doreşte ca aceste elemente să nu fie ignorate, se utilizează funcŃia VARA.
�������� CCaallccuullaarreeaa aabbaatteerriiii ssttaannddaarrdd ((SSttaannddaarrdd DDeevviiaattiioonn))..
FFuunnccŃŃiiiillee
SSTTDDEEVVPP // SSTTDDEEVVPPAA // SSTTDDEEVV // SSTTDDEEVVAA
SSiinnttaaxxăă == SSTTDDEEVVPP // SSTTDDEEVVPPAA // SSTTDDEEVV //SSTTDDEEVVAA ((lliissttăă__ddee__eelleemmeennttee))
� lliissttăă__ddee__eelleemmeennttee reprezintă o listă compusă din constante numerice, celule, formule sau nume de câmpuri.
RReezzuullttaatt rreettuurrnnaatt
Abaterea standard înregistrată într-o distribuŃie statistică sub formă de listă.
ObservaŃii. aa.. Indicatorul „Standard Deviation” calculează abaterea-standard (ecartul-tip) prin extragerea
rădăcinii pătrate din varianŃă. Altfel spus, funcŃia calculează pentru o populaŃie statistică de date media abaterilor înregistrate între datele unei serii statistice. În această accepŃiune, abaterea standard exprimă mărimea dispersiei valorilor faŃă de media acestora.
bb.. În condiŃiile în care se urmăreşte calcularea abaterii-standard pe o întreagă populaŃie statistică se utilizează funcŃia STDEVP. Această funcŃie ignoră valorile logice (True, False), şirurile de caractere şi celulele vide. Pentru ca acestea să fie luate în consideraŃie, se utilizează funcŃia STDEVPA, cu aceeaşi semnificaŃie şi sintaxă.
cc.. În condiŃiile în care se urmăreşte calcularea abaterii-standard pe un eşantion al populaŃiei statistice se utilizează funcŃia STDEV. Această funcŃie ignoră în argumentele sale, elementele de tip şir de caractere şi valorile logice. Dacă se doreşte ca aceste elemente să nu fie ignorate, se utilizează funcŃia STDEVA.
Figura 39 ilustrează modul de utilizare a funcŃiilor VARP, VAR, STDEVP şi STDEV. Pentru ca aceste funcŃii statistice să fie relevante, a fost necesară generarea unei populaŃii statistice pentru ca distribuŃia statistică să fie suficient de mare. Câmpul A125:A5000, intitulat „Serie” conŃine o funcŃie (RANDBETWEEN) ce returnează la fiecare proces de recalcul un număr aleator, cuprins între 1 şi 500, astfel încât distribuŃia statistică să fie aproximativ gauss-iană.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 36
Figura 39 Exemplu de utilizare a funcţiilor STDEVP, STDEV, VARP, VAR
�� CCaallccuullaarreeaa ccoovvaarriiaannţţeeii..
FFuunnccŃŃiiaa CCOOVVAARR
SSiinnttaaxxăă == CCOOVVAARR((ttaabblloouu__11 ;; ttaabblloouu__22))
� ttaabblloouu__11 şi ttaabblloouu__22 reprezintă două serii statistice de date, valori numerice sau nume de câmpuri ce conŃin valori numerice întregi.
RReezzuullttaatt rreettuurrnnaatt
Un coeficient ce semnifică media produselor abaterilor a două serii de date.
OObbsseerrvvaaŃŃiiee.. CovarianŃa calculează media produselor abaterilor pentru fiecare pereche de puncte de date. FuncŃia se introduce ca formulă matriceală şi se utilizează pentru a determina relaŃia dintre 2 seturi de date.
Exemplul din Figura 40 calculează covarianŃa pentru 2 serii de valori ce reprezintă notele studenŃilor la examenul de informatică.
Figura 40 Exemplu de utilizare a funcţiei COVAR
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 37
�� MMăăssuurraarreeaa ggrraadduulluuii ddee ddeeppeennddeennţţăă îînnttrree ddoouuăă vvaarriiaabbiillee pprriinn iinntteerrmmeeddiiuull ccooeeffiicciieennttuulluuii ddee ccoorreellaaţţiiee..
FFuunnccŃŃiiaa CCOORRRREELL
SSiinnttaaxxăă == CCOORRRREELL((mmaattrriiccee__11;;mmaattrriiccee__22))
� mmaattrriiccee__11 reprezintă prima variabilă susceptibilă de a fi explicată prin intermediul valorilor celei de a doua variabile. Această variabilă reprezintă parametrul calitativ, de explicat;
� mmaattrriiccee__22 reprezintă valorile celei de a doua variabile susceptibile de a influenŃa prima variabilă. Această variabilă reprezintă parametrul cantitativ, explicativ.
RReezzuullttaatt rreettuurrnnaatt
Un coeficient ce semnifică gradul de dependenŃă între 2 variabile. Cu cât coeficientul de corelaŃie se apropie de 1, cu atât există o mai bună corelare între cele două variabile.
OObbsseerrvvaaŃŃiiee.. Coeficientul de corelaŃie cuantifică dependenŃa ce există între 2 fenomene observate (x şi y). FuncŃia de corelaŃie permite studierea legăturilor statistice ce există între o variabilă dependentă (cantitativă sau explicativă) şi o variabilă independentă (calitativă sau de explicat). AplicaŃia principală a funcŃiei de corelaŃie constă în construirea dreptei de regresie.
Coeficientul de corelaŃie între două variabile x şi y este cuprins întotdeauna între -1 şi 1. Un coeficient de corelaŃie care se apropie de valoarea +1 indică o puternică dependenŃă între fenomenele observate. Cu cât variabila x este mai mare decât media, cu atât variabila y tinde să depăşească media, iar cu cât x este mai mic decât media, cu atât y tinde să descrească sub medie. Un coeficient de corelaŃie care se apropie de zero indică o slabă corelaŃie intre cele două variabile. Atunci când o ecuaŃie a dreptei este aplicată datelor şi reprezentarea sa este o pantă pozitivă se poate spune că dreapta ajustează cel mai bine valorile.
Exemplul prezentat în Figura 41 studiază gradul de dependenŃă între mai multe perechi de variabile. Astfel, valoarea coeficientului de corelaŃie editat în celula E2 indică o dependenŃă ridicată (0,6606) între notele studenŃilor la laboratoarele de informatică şi notele acestora obŃinute la examen.. Cel de-al doilea coeficient de corelaŃie, editat în celula E4 indică o foarte slabă legătură între codul studentului şi nota sa la laboratorul de informatică (valoare = 0,03599), iar coeficientul calculat în celula E6 indică o la fel de slabă dependenŃă între codul studentului şi nota obŃinută la examenul de informatică (valoare = 0,0830).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 38
Figura 41 Exemplu de utilizare a funcţiei CORREL Coeficientul de corelaţie mai poate fi editat în foaia de calcul prin intermediul componentelor de
analiză instalate la cerere prin demersul Office Button� Excel Options � Add-Ins � opţiunea
Analysis ToolPak. Din caseta de dialog Add-Ins se selectează caseta de validare Data Analysis.
Analiza datelor prin calcularea coeficientului de corelaţie este operaţională prin butonul Data
Analysis (aflat în dreapta ribbon-ului ce corespunde tabului Data). În urma activării acestei comenzi
se alege opţiunea Correlation aferentă casetei de dialog Data Analysis.
Figura 42 ilustrează modul cum se calculează tabloul coeficienţilor de corelaţie între două variabile.
În caseta de dialog Correlation se execută următorii paşi:
se selectează câmpul ce conţine variabilele de analizat (în rubrica Input Range). Selecţia va cuprinde doar valorile numerice. Dacă se doreşte includerea capului de tabel în selecţie, se va bifa opţiunea Labels in first row. Se selectează orientarea datelor analizate (Grouped by Columns / Rows);
Se desemnează adresa celulei de la care se va genera tabloul coeficienţilor de corelaţie; Se validează operaţiunea cu OK.
Figura 42 Exemplu de calcul a coeficientului de corelaţie cu ajutorul instrumentelor Data Analysis.
�� EExxttrraappoollăărrii şşii pprreevviizziiuunnii pprriinn ffuunnccţţiiii ssttaattiissttiiccee..
FFuunnccŃŃiiaa LLIINNEESSTT
SSiinnttaaxxăă == LLIINNEESSTT((YY__ccuunnoossccuutt ;; XX__ccuunnoossccuutt ;; ccoonnssttaannttăă ;; ssttaattiissttiiccii))
� YY__ccuunnoossccuutt reprezintă o serie de valori observate (cifra de afaceri). Această variabilă reprezintă parametrul calitativ, de explicat;
� XX__ccuunnoossccuutt reprezintă o serie de valori ce explică variaŃiile (de exemplu cheltuielile cu publicitatea). Această variabilă reprezintă parametrul cantitativ, explicativ;
� ccoonnssttaannttăă semnifică o valoare logică TRUE dacă valoarea b a ecuaŃiei y=ax+b trebuie să fie egală cu zero. În caz contrar, constanta va identifica o valoare logică de FALSE;
� ssttaattiissttiiccii indică prin valoarea logică TRUE dacă se vor calcula informaŃii suplimentare, iar dacă nu se doreşte acest lucru, valoarea logică va fi FALSE.
RReezzuullttaatt rreettuurrnnaatt
CoeficienŃii a şi b aferenŃi ecuaŃiei dreptei y=ax+b prin care se poate stabili gradul de dependenŃă între 2 variabile.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 39
OObbsseerrvvaaŃŃiiii..
aa.. FuncŃia LLIINNEESSTT calculează coeficienŃii ecuaŃiei dreptei prin metoda celor mai mici pătrate. O astfel de dreaptă identifică punctele ce reprezintă două serii statistice (de exemplu: cifra de afaceri şi numărul personalului) în aşa fel încât acestea (punctele) să se situeze cât mai aproape de dreaptă. EcuaŃia dreptei este: y=ax+b.
bb.. FuncŃia LINEST se introduce matriceal. Figura 43 prezintă printr-un exemplu cum se calculează coeficienŃii a şi b ai dreptei de regresie ce reprezintă corelaŃia la nivelul unei întreprinderi între cifra de afaceri şi cheltuielile publicitare pentru generarea acesteia.
Figura 43 Exemplu de utilizare a funcţiei LINEST
FFuunnccŃŃiiaa TTRREENNDD
SSiinnttaaxxăă == TTRREENNDD((YY__ccuunnoossccuutt ;; XX__ccuunnoossccuutt ;; XX__nnoouu [[;;ccoonnssttaannttăă]]))
� YY__ccuunnoossccuutt reprezintă o serie de valori ale variabilei de explicat (de exemplu, cifra de afaceri este explicată prin cheltuielile cu publicitatea);
� XX__ccuunnoossccuutt reprezintă o serie de valori ale variabilei explicative (cheltuielile cu publicitatea vor explica creşterea cifrei de afaceri);
� XX__nnoouu semnifică noile date plecând de la care se va construi extrapolarea;
� [[ccoonnssttaannttăă]] semnifică o valoare logică TRUE dacă valoarea b a ecuaŃiei y=ax+b trebuie să fie egală cu zero. În caz contrar, constanta va identifica o valoare logică de FALSE.
RReezzuullttaatt rreettuurrnnaatt
Valoarea extrapolată. Rezultatul calculat reprezintă o informaŃie brută datorită faptului că mulŃi factori nu sunt luaŃi în consideraŃie la extrapolare.
OObbsseerrvvaaŃŃiiee.. FuncŃia TREND se introduce matriceal.
FFuunnccŃŃiiaa FFOORREECCAASSTT
SSiinnttaaxxăă == FFOORREECCAASSTT((XX__nnoouu ;; YY__ccuunnoossccuutt ;; XX__ccuunnoossccuutt))
� XX__nnoouu semnifică reperul de date pentru care se doreşte anticiparea unei valori
� YY__ccuunnoossccuutt reprezintă o serie de valori ale variabilei explicative sau dependente;
� XX__ccuunnoossccuutt reprezintă o serie de valori ale variabilei explicative sau independente. RReezzuullttaatt rreettuurrnnaatt
Valoarea extrapolată.
OObbsseerrvvaaŃŃiiee.. FuncŃia FORECAST se poate introduce şi matriceal. Figura 44 ilustrează câte un exemplu de utilizare a funcŃiilor de previziune pentru estimarea pe perioada 2009-2010 a cheltuielilor cu publicitatea (prin FORECAST) şi a cifrei de afaceri.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 40
Figura 44 Exemplu de utilizare a funcţiilor TREND şi FORECAST
2.3 Funcţii logice
Funcţiile logice determină evaluarea unor expresii şi în funcţie de rezultatul evaluărilor (adevărat sau
fals) generează acţiuni sau rezultate complexe.
IF, AND, OR, NOT, TRUE, FALSE
�� FFuunnccţţiiaa ccoonnddiiţţiioonnaallăă IIFF
FFuunnccŃŃiiaa IIFF
SSiinnttaaxxăă ==IIFF((ccoonnddiiŃŃiiee ;; rreezzuullttaatt__11 ;; rreezzuullttaatt__22))
� ccoonnddiiŃŃiiee este o expresie de comparaŃie ce utilizează unul dintre operatorii de comparaŃie: =, >, >=, <, <=, <>. Prin aceşti operatori se poate compara un element cu o constantă, sau două elemente între ele. Elementele care fac obiectul unei comparaŃii pot fi: nume de câmpuri sau referinŃe celulare ce conŃin valori numerice, şiruri de caractere, valori logice, rezultate ale unor expresii de calcul sau de evaluare.
� rreezzuullttaatt__11 specifică o valoare numerică, un şir de caractere sau o formulă;
� rreezzuullttaatt__22 specifică aceleaşi elemente ca şi rreezzuullttaatt__11. RReezzuullttaatt rreettuurrnnaatt
RReezzuullttaatt__11 dacă expresia condiŃională este evaluată ca adevărată sau rreezzuullttaatt__22 dacă expresia condiŃională este evaluată ca falsă.
OObbsseerrvvaaţţiiii..
FuncŃia IF testează argumentul ccoonnddiiŃŃiiee şi în funcŃie de rezultatul evaluării logice, generează rreezzuullttaatt__11 dacă condiŃia este adevărată sau rreezzuullttaatt__22 dacă aceasta este falsă. Dacă argumentele funcŃiei condiŃionale IF sunt şiruri de caractere, acestea vor fi plasate între ghilimele, spre a nu fi interpretate de Excel ca nume de câmpuri. Dacă argumentele funcŃiei sunt constante de tip dată calendaristică, se recomandă ca acestea să se abordeze fie prin intermediul unor funcŃii specifice (DATE, DATEVALUE, etc.), fie prin accesarea lor printr-o referinŃă celulară sau printr-un nume de câmp. Dacă rreezzuullttaatteellee (11 şi/sau 22) evaluării condiŃionale se doresc a fi prezentate în valori booleene (logice), se vor utiliza funcŃiile TRUE() şi/sau FALSE(). Dacă argumentul ccoonnddiiŃŃiiee testează o valoare logică, se va utiliza funcŃia ISLOGICAL ce interceptează o valoare booleană returnată de o funcŃie. Figura 45 prezintă două exemple de utilizare a valorilor booleene într-o funcŃie condiŃională (primul exemplu evaluează o condiŃie simplă şi returnează în celula D20 una dintre cele două valori booleene, iar al
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 41
doilea exemplu testează în condiŃie o valoare booleană şi returnează în celula D21 un rezultat ce reprezintă tot o valoare logică).
Figura 45 Utilizarea expresiilor booleene în funcţiile condiţionale a. În condiŃiile în care din sintaxa funcŃiei IF, lipsesc alternativ argumentele rreezzuullttaatt__11 sau rreezzuullttaatt__22
se returnează următoarele valori, conform exemplului prezentat în Figura 46.
Dacă unul dintre argumentele rreezzuullttaatt__11 sau rreezzuullttaatt__22 sunt omise şi înlocuite cu separatorul de argumente, funcŃia IF va returna valoarea zero, în condiŃiile în care evaluarea condiŃiei conduce către unul dintre rezultate lipsă. Altfel spus, dacă evaluarea condiŃiei conduce către un rezultat „adevărat” şi lipseşte din sintaxa funcŃiei IF, fie argumentul rreezzuullttaatt__22, fie rreezzuullttaatt__11 (IF(condiŃie;rreezzuullttaatt__11;) sau IF(condiŃie;;;;rreezzuullttaatt__22;)), acestea fiind înlocuite cu separatorul de argumente (punct şi virgulă sau virgulă1), funcŃia de evaluare va returna valoarea zero. Figura 46 ilustrează cazul în care celula C25 conŃine funcŃia ce returnează rreezzuullttaatt__22 care este substituit cu punct şi virgulă.
Celula C26 conŃine rezultatul returnat de funcŃia IF, corespunzător evaluării condiŃiei care angajează logic execuŃia argumentului rreezzuullttaatt__11 care, în acest caz este substituit cu punct şi virgulă).
Figura 46 Cazuri de utilizare a funcţiei IF, în condiţiile omisiunii argumentului rezultat_1 sau rezultat_2
Dacă unul dintre argumentele rreezzuullttaatt__11 sau rreezzuullttaatt__22 sunt omise (Figura 46, celula C27), funcŃia IF va returna valoarea logică de „fals” în condiŃiile în care evaluarea condiŃiei conduce către unul dintre rezultate lipsă. În mod contrar, dacă evaluarea condiŃiei conduce la activarea unui argument existent (rreezzuullttaatt__11 sau rreezzuullttaatt__22), acesta va fi executat (celula C27).
În locul argumentului rreezzuullttaatt__22 pot fi imbricate alte structuri condiŃionale IF, generându-se potrivit condiŃiilor specificate ulterior o substituire în „cascadă”. Această substituire ilustrată în Figura 47, are ca efect calcularea pe fiecare interval a argumentelor: rreezzuullttaatt__11..11;rreezzuullttaatt__11, …, rreezzuullttaatt__11..nn.. Potrivit unei analize logice, pot exista 3 demersuri ale imbricării funcŃiilor condiŃionale, în ceea ce priveşte abordarea intervalelor: logica crescătoare, descrescătoare şi între intervale. Logica crescătoare presupune testarea a valorilor condiŃiei cu valori de comparaŃie dispuse în succesiune crescătoare. CondiŃia va conŃine întotdeauna operatorul „mai mic” (<) sau „mai mic sau egal” (<=), iar valoarea de comparaŃie va fi din ce în ce mai mare (fapt ilustrat în partea superioară a următoarelor 2 figuri). Logica descrescătoare presupune testarea a valorilor condiŃiei cu valori de comparaŃie dispuse în succesiune descrescătoare. CondiŃia va conŃine întotdeauna operatorul „mai mare” (>) sau „mai mare sau
1 potrivit setărilor regionale ale sistemului de operare Windows XP
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 42
egal” (>=), iar valoarea de comparaŃie va fi din ce în ce mai mică (fapt ilustrat în partea inferioară a următoarelor 2 figuri).
=IF(BB3322<<33;00;IF((BB3322<<55;CC3322**55%%;IF((BB3322<<1100;CC3322**1100%%;IF((BB3322<<1155;CC3322**11
55%%;IF(BB3322<<2200;CC3322**2200%%;CC3322**2255%%))))))))
=IF(BB3333>>2200;CC3333**2255%%;IF(BB3333>>1155;CC3333**2200%%;IF(BB3333>>1100;CC3333**1155%%;IF(BB33
33>>55;CC3333**1100%%;IF(BB3333>>33;CC3333**55%%;00)))))
==IIFF((ccoonnddiiţţiiee__11;;rreezzuullttaatt__11;;____________________________))))))))
==IIFF((ccoonnddiiţţiiee__22;;rreezzuullttaatt__11..11;;__________________
==IIFF((ccoonnddiiţţiiee__33;;rreezzuullttaatt__11..22;;________________
==IIFF((ccoonnddiiţţiiee__nn;;rreezzuullttaatt__11..nn;;rreezzuullttaatt__22..nn
rreezzuullttaatt__22 Testare
crescătoare
Testare
descrescătoare
Figura 47 Imbricarea structurilor condiŃionale prin substituirea „în cascadă” a argumentului
rezultat_2
Figura 48 ilustrează un exemplu de abordare a celor două tipuri de imbricări, plecând de la
un exemplu inspirat dintr-un ştat de plată, în care se calculează sporul de vechime (plecând
de evaluarea anilor de vechime în muncă).
Figura 48 Exemplu practic de substituire „în cascadă” a argumentului rezultat_2 cu alte structuri condiŃionale
Logica testării explicite a intervalelor presupune verificarea pentru fiecare imbricare (a funcŃiei IF), a valorilor condiŃiei pentru fiecare interval în parte. Această tehnică se implementează cu ajutorul funcŃiei logice AND (bornă_minimă; bornă_maximă).
�� EEvvaalluuaarreeaa llooggiiccăă pprriinn ffuunnccţţiiiillee „„ŞŞII”” // „„SSAAUU””..
FFuunnccŃŃiiaa AANNDD
SSiinnttaaxxăă ==AANNDD((lliissttăă__ddee__eelleemmeennttee ;;…………))
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 43
� lliissttăă__ddee__ eelleemmeennttee reprezintă o înşiruire de elemente ce vor fi evaluate logic. RReezzuullttaatt rreettuurrnnaatt
TTRRUUEE dacă toate elementele din lista de argumentele sunt adevărate. FFAALLSSEE dacă cel puŃin un element din lista de argumentele nu se verifică (se evaluează ca fals).
OObbsseerrvvaaŃŃiiii.
a. FuncŃia AND returnează valori logice în funcŃie de evaluarea argumentelor. b. De regulă, funcŃia AND este utilizată pentru evaluarea în cadrul unei condiŃii a unui element
numeric între două limite: una superioară şi una inferioară. În acest caz, se vor utiliza 2 perechi de valori ce vor fi evaluate cu ajutorul operatorilor de comparaŃie: >, <, >=, <=.
c. FuncŃia AND nu poate fi substituită cu expresia logică: mm<xx<nn, unde xx este o valoare strict mai mare decât mm şi strict mai mică decât nn, fapt ilustrat şi de exemplele prezentate în Figura 49, celulele F66 şi F67.
d. Dacă un argument face apel la referinŃe celulare (nume sau adrese) vide, acestea vor fi ignorate de funcŃia AND.
e. FuncŃia logică AND poate conŃine maximum 30 de argumente. Un exemplu ce surprinde câteva ipostaze de utilizate a funcŃiei AND, este prezentat grafic în Figura 49.
Figura 49 Exemple de utilizare a funcţiei AND FFuunnccŃŃiiaa OORR
SSiinnttaaxxăă ==OORR((lliissttăă__ddee__eelleemmeennttee ;;…………))
� lliissttăă__ddee__ eelleemmeennttee reprezintă o înşiruire de elemente ce vor fi evaluate logic. RReezzuullttaatt rreettuurrnnaatt
TTRRUUEE dacă cel puŃin un element din lista de argumentele este evaluat ca adevărat.
FFAALLSSEE dacă nici un element din lista de argumentele nu este evaluat ca adevărat.
a. FuncŃia OR returnează valori logice în funcŃie de evaluarea argumentelor. b. De regulă, funcŃia OR este utilizată pentru evaluarea unor şiruri de caractere în cadrul unei
condiŃii. De asemenea, funcŃia OR mai face obiectul unor teste efectuate asupra conŃinutului unor celule, cu ajutorul funcŃiilor de informare. Nu în ultimul rând, este de menŃionat că funcŃia OR mai poate face obiectul unor testări multiple (şiruri de caractere) în câmpul de criterii al unei baze de date de tip listă.
c. Referitor la funcŃia OR, se poate spune că există aceleaşi observaŃii ca şi la punctele c) şi d) ale funcŃiei AND.
�������� IInnvveerrssaarreeaa uunneeii eevvaalluuăărrii llooggiiccee..
FFuunnccŃŃiiaa NNOOTT
SSiinnttaaxxăă ==NNOOTT((eevvaalluuaarree__llooggiiccăă))
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 44
� eevvaalluuaarree__llooggiiccăă reprezintă rezultatul unei evaluări condiŃionale. De regulă, evaluarea logică îmbracă forma unei expresii ce conŃine o funcŃie logică.
RReezzuullttaatt rreettuurrnnaatt
Inversul valorii logice a argumentului.
�������� VVaalloorrii llooggiiccee ddee „„aaddeevvăărraatt”” ssaauu „„ffaallss””..
FFuunnccŃŃiiaa TTRRUUEE
SSiinnttaaxxăă ==TTRRUUEE(())
� ffăărrăă aarrgguummeenntt RReezzuullttaatt rreettuurrnnaatt
Valoarea logică de „aaddeevvăărr”.
FFuunnccŃŃiiaa FFAALLSSEE
SSiinnttaaxxăă ==FFAALLSSEE(())
� ffăărrăă aarrgguummeenntt RReezzuullttaatt rreettuurrnnaatt
Valoarea logică de „ffaallss”.
Figura 50 ilustrează câteva cazuri de utilizare a funcţiilor OR (celulele F73, F74, F75, F76), NOT
(celula F76), TRUE şi FALSE (celula F73).
Figura 50 Exemple de utilizare a funcţiilor OR, NOT, TRUE, FALSE
FFuunnccţţiiiillee ddee iinnffoorrmmaarree ((IInnffoorrmmaattiioonn))
Funcţiile de informare evaluează conţinutul celulelor returnând valori logice de „adevărat” (True)
sau „fals” (False). De asemenea, aceste funcţii afişează şi informaţii referitoare la celule sau câmpuri.
FFuunnccţţiiii ddee iinnffoorrmmaarree ccee tteesstteeaazzăă ccoonnţţiinnuuttuull cceelluulleelloorr ((IISS FFuunnccttiioonnss))
ISBLANK, ISNUMBER, ISTEXT, ISNONTEXT, ISLOGICAL, ISERROR, ISERR, ISNA, ISODD, ISEVEN,
ISREF
FuncŃiile de tip IS Functions testează tipurile de valori identificate în diferite referinŃe celulare. Toate aceste funcŃii au un singur argument sub forma referinŃei unei celule prin adresă sau nume. Argumentul testat (numit în mod generic X) poate conŃine o valoare de blank (celulă vidă), o valoare de eroare (#N/A, #DIV/0!, #NUM, #NAME, #VALUE, #REF, #NULL), o valoare logică (True,
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 45
False), un şir de caractere, o valoare numerică, etc. De asemenea, argumentul unei funcŃii de informare poate fi o expresie sau chiar o altă funcŃie de regulă una de consultare (de exemplu ISNA(VLOOKUP(...)). Se recomandă ca argumentul funcţiilor de tip IS... să nu fie un câmp, deoarece rezultatul returnat
devine ambiguu în momentul în care plaja de celule (ce formează câmpul) conţine valori eterogene.
�������� TTeessttaarreeaa cceelluulleelloorr vviiddee..
FFuunnccŃŃiiaa IISSBBLLAANNKK
SSiinnttaaxxăă == IISSBBLLAANNKK((XX))
� XX este referinŃa unei celule. RReezzuullttaatt rreettuurrnnaatt
True (valoare logică de adevăr) dacă referinŃa testată este vidă;
False (valoarea logică de fals) în cazul în care referinŃa testată nu este vidă.
�������� TTeessttaarreeaa cceelluulleelloorr ccee ccoonnţţiinn vvaalloorrii nnuummeerriiccee..
FFuunnccŃŃiiaa IISSNNUUMMBBEERR
SSiinnttaaxxăă == IISSNNUUMMBBEERR((XX))
� XX este referinŃa unei celule. RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată conŃine o valoare numerică;
False în cazul în care referinŃa testată nu conŃine o valoare numerică.
OObbsseerrvvaaţţiiee.. Datele în format dată calendaristică sunt de natură numerică şi deci, când o celulă ce
conţine astfel de date este testată cu funcţia ISNUMBER, va fi returnată valoarea de „adevăr” (True).
�������� TTeessttaarreeaa cceelluulleelloorr ccee ccoonnţţiinn „„şşiirruurrii ddee ccaarraacctteerree””..
FFuunnccŃŃiiaa IISSTTEEXXTT
SSiinnttaaxxăă == IISSTTEEXXTT((XX))
� XX este referinŃa unei celule. RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată conŃine un şir de caractere;
False în cazul în care referinŃa testată nu conŃine un text.
�������� TTeessttaarreeaa cceelluulleelloorr ccee nnuu ccoonnţţiinn „„şşiirruurrii ddee ccaarraacctteerree””..
FFuunnccŃŃiiaa IISSNNOONNTTEEXXTT
SSiinnttaaxxăă == IISSNNOONNTTEEXXTT((XX))
� XX este referinŃa unei celule. RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată nu conŃine un şir de caractere;
False în cazul în care referinŃa testată conŃine un şir de caractere.
�������� TTeessttaarreeaa cceelluulleelloorr ccee ccoonnţţiinn vvaalloorrii llooggiiccee..
FFuunnccŃŃiiaa IISSLLOOGGIICCAALL
SSiinnttaaxxăă == IISSLLOOGGIICCAALL((XX))
� XX este referinŃa unei celule sau a unei expresii de evaluare.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 46
RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată conŃine o valoare logică;
False în cazul în care referinŃa testată nu conŃine o valoare logică.
�������� TTeessttaarreeaa cceelluulleelloorr ccee ccoonnţţiinn vvaalloorrii nnuummeerriiccee iimmppaarree..
FFuunnccŃŃiiaa IISSOODDDD
SSiinnttaaxxăă == IISSOODDDD((XX))
� XX este referinŃa unei celule sau o expresie de calcul. RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată conŃine o valoare impară;
False în cazul în care referinŃa testată nu conŃine o valoare pară.
�������� TTeessttaarreeaa cceelluulleelloorr ccee ccoonnţţiinn vvaalloorrii nnuummeerriiccee ppaarree..
FFuunnccŃŃiiaa IISSEEVVEENN
SSiinnttaaxxăă == IISSEEVVEENN((XX))
� XX este referinŃa unei celule sau o expresie de calcul. RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată conŃine o valoare pară;
False în cazul în care referinŃa testată nu conŃine o valoare impară.
�������� TTeessttaarreeaa cceelluulleelloorr ccee ccoonnţţiinn ffoorrmmuullee ccaarree rreettuurrnneeaazzăă vvaalloorrii ddee eerrooaarree..
FFuunnccŃŃiiaa IISSRREEFF
SSiinnttaaxxăă == IISSRREEFF((XX))
� XX este referinŃa unei celule sau o expresie de calcul. RReezzuullttaatt rreettuurrnnaatt
True dacă în foaia de calcul există referinŃa testată;
False dacă în foaia de calcul nu există referinŃa testată.
FFuunnccŃŃiiaa IISSEERRRROORR verifică dacă referinŃa definită de argument conŃine o valoare de eroare de tip #N/A, #DIV/0!, #NUM, #NAME, #VALUE, #REF, #NULL, ce a fost returnată de o formulă şi/sau o funcŃie.
SSiinnttaaxxăă == IISSEERRRROORR((XX))
� XX este referinŃa unei celule sau o expresie de calcul sau evaluare. RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată conŃine o valoare de eroare;
False în cazul în care referinŃa testată nu conŃine o valoare de eroare.
FFuunnccŃŃiiaa IISSEERRRR verifică dacă referinŃa definită de argument conŃine o valoare de eroare cu excepŃia valorii #N/A, ce a fost returnată de o formulă şi/sau o funcŃie.
SSiinnttaaxxăă == IISSEERRRR((XX))
� XX este referinŃa unei celule sau o expresie de calcul sau evaluare. RReezzuullttaatt True dacă referinŃa testată conŃine o valoare de eroare;
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 47
rreettuurrnnaatt False în cazul în care referinŃa nu conŃine o valoare de eroare.
Figura 51 ilustrează rezultatele returnate de principalele funcţii de informare asupra conţinutului
celulelor.
Figura 51 Exemple de utilizare a funcţiilor de informare asupra conţinutului celulelor FFuunnccŃŃiiaa IISSNNAA verifică dacă referinŃa definită de argument conŃine o valoare
nedisponibilă returnată ca eroare a unei funcŃii de consultare.
SSiinnttaaxxăă == IISSNNAA((XX))
� XX este referinŃa unei celule sau o expresie definită de o funcŃie de consultare. RReezzuullttaatt rreettuurrnnaatt
True dacă referinŃa testată conŃine o valoare de eroare sub forma non-disponibilităŃii valorii căutate;
False în cazul în care referinŃa nu conŃine o valoare de eroare generată de o funcŃie de consultare verticală.
Utilizarea funcţiilor ISNA şi ISERROR este exemplificată la nivelul acestui capitol în cadrul funcţiilor
de consultare LOOKUP (observaţia b, Figura 106, de la pagina 92) VLOOKUP (observaţia c şi d,
Figura 107, de la pagina 93) HLOOKUP (cazul 3, Figura 110, de la pagina 97).
FuncŃiile de informare prezentate pot interacŃiona şi cu alte funcŃii predefinite. De regulă, funcŃiile condiŃionale IF, AND sau OR cuprind între argumentele lor şi funcŃii de informare (IF(ISERROR(B25/A3)...). Figura 52 exemplifică un mod de interacŃiune între funcŃiile de informare şi funcŃiile logice, prin care se returnează valori logice în funcŃie de conŃinutul celulelor testate.
Figura 52 Exemple de utilizare a funcţiilor de informare împreună cu funcţiile logice
2.4 Funcţii de informare
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 48
FFuunnccţţiiii ddee iinnffoorrmmaarree ccee aaffiişşeeaazzăă iinnffoorrmmaaţţiiii rreeffeerriittooaarree llaa cceelluullee ssaauu ccââmmppuurrii
N, NA, COUNTBLANK, ERROR.TYPE, TYPE, CELL
�������� CCoonnvveerrttiirreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee îînnttrr--oo vvaallooaarree..
FFuunnccŃŃiiaa NN
SSiinnttaaxxăă ==NN((vvaallooaarree))
� vvaallooaarree reprezintă valoarea căreia i se va face conversia. RReezzuullttaatt rreettuurrnnaatt
Un număr, dacă se face referinŃă la acel număr;
Un număr de tip dată calendaristică, în cazul în care referinŃa conŃine o dată calendaristică într-un format specific;
1, în cazul în care referinŃa are valoarea logică TRUE;
0, în cazul în care referinŃa are valoarea logică FALSE, sau referinŃa conŃine orice altceva.
OObbsseerrvvaaŃŃiiee.. Datorită faptului că Excel face în mod automat conversia automată a valorilor, nu este necesar a se utiliza explicit funcŃia N. Majoritatea cazurilor de utilizare vizează conversia din şir de caractere în zero şi din valori logice în 0 sau 1. Figura 53 ilustrează câteva exemple de utilizare a funcŃiei N.
Figura 53 Exemple de utilizare a funcţiei N �������� MMaarrccaarreeaa cceelluulleelloorr ccuu vvaalloorrii nneeddiissppoonniibbiillee..
FFuunnccŃŃiiaa NNAA
SSiinnttaaxxăă ==NNAA(())
� ffăărrăă aarrgguummeenntt. RReezzuullttaatt rreettuurrnnaatt
Mesajul de eroare „Not Available” (#N/A).
�������� NNuummăărraarreeaa cceelluulleelloorr vviiddee aaffeerreennttee uunnuuii ccââmmpp ssppeecciiffiiccaatt..
FFuunnccŃŃiiaa CCOOUUNNTTBBLLAANNKK
SSiinnttaaxxăă ==CCOOUUNNTTBBLLAANNKK((ccââmmpp))
� ccââmmpp specifică referinŃa sau numele unui câmp RReezzuullttaatt rreettuurrnnaatt
Numărul de celule vide.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 49
OObbsseerrvvaaŃŃiiee.. Celulele editate cu caracterul „blank” (””) sunt asimilate celulelor vide. Celulele care au valoarea zero nu sunt considerate a fi vide. Două exemple de utilizare a funcţiei
COUNTBLANK sunt prezentate în Figura 54.
Figura 54 Exemplu de utilizare a funcţiei informare COUNTBLANK
�������� IIddeennttiiffiiccaarreeaa eerroorriiii ggeenneerraattee ddee oo ffoorrmmuullăă,, pprriinnttrr--oo vvaallooaarree nnuummeerriiccăă..
FFuunnccŃŃiiaa EERRRROORR..TTYYPPEE
SSiinnttaaxxăă ==EERRRROORR..TTYYPPEE((ttiipp__eerrooaarree))
� ttiipp__eerrooaarree este o valoare de eroare, o adresă sau un nume de celulă ce conŃine o valoare de eroare specifică Excel.
RReezzuullttaatt rreettuurrnnaatt
1 � dacă valoarea de eroare este #NULL! (specifică într-o formulă două câmpuri care se intersectează);
2 � #DIV/0! (împărŃire la zero);
3 � #VALUE! (operaŃie matematică între valori şi şiruri de caractere, utilizarea greşită a argumentelor şi/sau a operanzilor );
4 � #REF! (referinŃă celulară absentă sau incorectă);
5 � #NAME! (implicarea într-o formulă a unui nume de câmp inexistent);
6 � #NUM! (prezenŃa unor valori numerice incorecte într-o formulă);
7 � #N/A! (valoare nedisponibilă returnată de o funcŃie de consultare).
OObbsseerrvvaaŃŃiiee.. FuncŃia ERROR.TYPE prezentată în Figura 55 este utilizată cu precădere atunci când se doreşte testarea unor formule ce pot returna diferite valori de eroare: =IF(OR(ERROR.TYPE=1;ERROR.TYPE=2;ERROR.TYPE=3);…;…)
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 50
Figura 55 Exemple de valori returnate de funcţia de informare ERROR.TYPE �������� IIddeennttiiffiiccaarreeaa pprriinnttrr--oo vvaallooaarree nnuummeerriiccăă aa uunnuuii ttiipp ddee ddaattăă..
FFuunnccŃŃiiaa TTYYPPEE
SSiinnttaaxxăă ==TTYYPPEE((ttiipp__ddaattăă))
� ttiipp__ddaattăă este o valoare, o adresă sau un nume de celulă cu un conŃinut ce urmează a fi evaluat în termeni de tip de dată (numeric, şir de caractere, etc).
RReezzuullttaatt rreettuurrnnaatt
1 � dacă tipul de dată este numeric;
2 � dacă tipul de dată este de natură şir de caractere;
4 � dacă tipul de dată este de natură logică;
16 � dacă tipul de dată este o valoare de eroare;
64 � dacă tipul de dată este o formulă matriceală.
OObbsseerrvvaaţţiiee.. Funcţia TYPE prezentată în Figura 56 este utilizată ca substitut al funcţiilor: ISNUMBER,
ISTEXT, ISLOGICAL, ISERROR, atunci când o altă funcţie depinde de evaluarea tipului de dată sub
forma unui număr (1;2;4;16;64), într-o formă numerică, dintr-o anumită celulă.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 51
Figura 56 Exemple de utilizare a funcţiei de informare TYPE �������� IInnffoorrmmaaţţiiii ssuupplliimmeennttaarree rreeffeerriittooaarree oo rreeffeerriinnţţăă cceelluullaarrăă..
FFuunnccŃŃiiaa CCEELLLL
SSiinnttaaxxăă ==CCEELLLL((ttiipp__iinnffoorrmmaaŃŃiiee[[;;rreeffeerriinnŃŃăă]]))
� ttiipp__iinnffoorrmmaaŃŃiiee este o valoare de tip text, care specifică tipul de informaŃiei despre celula referită. Valorile acestui argument pot fi următoarele:
o "address" � adresa celulei specificate. Dacă argumentul rreeffeerriinnŃŃăă este un câmp, funcŃia returnează coordonatele primei celule ale acestuia.;
o "col" � numărul coloanei celulei din referinŃa celulară; o ”row” � numărul liniei din referinŃa specificată; o "filename" � specificatorul fişierului curent (inclusiv calea de foldere şi
foaia de calcul curentă) care conŃine referinŃa. Precizarea argumentului eturnează text gol ("") dacă fişierul nu a fost încă salvat;
o „protect” � returnează 0 (zero) dacă celula nu este blocată şi 1 dacă celula este blocată;
o "width" � lăŃimea coloanei referinŃei celulare. Acest parametru se exprimă prin numărul de caractere (corespunzătoare fontului implicit) aferent coloanei.
� [[rreeffeerriinnŃŃăă]] specifică adresa celulei, coordonatele sau numele unui câmp, pentru care se doreşte aflarea unui tip de informaŃie. În condiŃiile în care acest argument este opŃional, funcŃia CELL returnează informaŃii corespunzătoare celulei curente (în care este editată funcŃia).
RReezzuullttaatt rreettuurrnnaatt
Diferite informaŃii asupra referinŃei celulare, în funcŃie de valorile primului argument specificat.
Figura 57 exemplifică posibilităţile de utilizare a argumentului tip_informaţie, aferent funcţiei CELL.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 52
Figura 57 Exemple de utilizare a funcţiei de informare CELL
2.5 Funcţii de dată şi timp
FFuunnccţţiiiillee ttiipp ddaattăă ((ccaalleennddaarriissttiiccăă)) şşii ttiimmpp ((DDaattee && TTiimmee))
Funcţiile dată şi oră manipulează şi operează calcule cu valori numerice ce reprezintă date
calendaristice sau valori de timp.
FFuunnccţţiiii ddee ttiipp ddaattăă ccaalleennddaarriissttiiccăă
Datele calendaristice sunt stocate de Excel sub formă de numere seriale cuprinse între 1 (01 ianuarie
1900) şi 2.958.465 (31 decembrie 9999). Mai multe informaţii referitoare la valorile de tip dată
calendaristică se regăsesc în capitolul 1, subcapitolul intitulat „Formatarea spaţiului de lucru din
foaia de calcul”, paragraful „Formatul Date (dată calendaristică)”.
TODAY, NOW, DATEVALUE, DATE, YEAR, MONTH, DAY, WEEKDAY, WEEKNUM, DAYS360,
EOMONTH, EDATE, WORKDAY, NETWORKDAYS, YEARFRAC
�������� DDaattaa ((şşii oorraa)) ccuurreennttăă aa ssiisstteemmuulluuii..
FFuunnccŃŃiiaa TTOODDAAYY
SSiinnttaaxxăă ==TTOODDAAYY(())
� ffăărrăă aarrgguummeennttee. RReezzuullttaatt rreettuurrnnaatt
Data curentă a sistemului, într-un format implicit sau specificat de utilizator prin formatare.
FFuunnccŃŃiiaa NNOOWW
SSiinnttaaxxăă ==NNOOWW(())
� ffăărrăă aarrgguummeennttee. RReezzuullttaatt rreettuurrnnaatt
Data şi ora curentă a sistemului, într-un format implicit sau explicit specificat de utilizator prin operaŃiunea de formatare.
�������� MMaanniippuullaarreeaa îînn ffoorrmmuullee aa ccoonnssttaanntteelloorr ddee ttiipp ddaattăă ccaalleennddaarriissttiiccăă..
FFuunnccŃŃiiaa DDAATTEEVVAALLUUEE
SSiinnttaaxxăă ==DDAATTEEVVAALLUUEE((„„şşiirr__ddee__ccaarraacctteerree””))
� şşiirr__ddee__ccaarraacctteerree este un text ce prezintă similitudini cu formatul unei date calendaristice. În mod obligatoriu, şirul de caractere trebuie plasat între ghilimele.
RReezzuullttaatt rreettuurrnnaatt
Data calendaristică specificată în argument (sub formă de text), prezentată într-un format tip dată calendaristică.
FFuunnccŃŃiiaa DDAATTEE
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 53
SSiinnttaaxxăă ==DDAATTEE ((aann ;; lluunnăă ;; zzii))
� aann este o valoare numerică cuprinsă între 1900 şi 9999;
� lluunnăă este o valoare numerică cuprinsă între 01 şi 12;
� zzii este o valoare numerică cuprinsă între 01 şi 31. RReezzuullttaatt rreettuurrnnaatt
Data calendaristică ce corespunde argumentelor specificate.
OObbsseerrvvaaŃŃiiee.. FuncŃiile DATEVALUE şi DATE sunt utilizate pentru a referi diferite constante de tip dată calendaristică în formule. Astfel, într-o formulă ce face apel la o dată calendaristică, aceasta nu poate fi abordată printr-o constantă, ci prin intermediul unei adrese de celule ce conŃine constanta respectivă sau prin intermediul unei funcŃii specializate (DATEVALUE / DATE). Această observaŃie a fost prezentată anterior, în cadrul funcŃiei condiŃionale IF. Figura 58 reprezintă grafic câteva exemple de utilizare a funcŃiilor prezentate, împreună cu formatele personalizate aferente.
Figura 58 Exemplu de utilizare a funcţiilor NOW, TODAY, DATEVALUE, DATE
�������� EExxttrraaggeerreeaa aannuulluuii ddiinnttrr--oo ddaattăă ccaalleennddaarriissttiiccăă..
FFuunnccŃŃiiaa YYEEAARR
SSiinnttaaxxăă ==YYEEAARR((nnuummăărr__ddaattăă))
� nnuummăărr__ddaattăă poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie.
RReezzuullttaatt rreettuurrnnaatt
Anul (între 1 şi 9999) ce corespunde argumentului specificat.
�������� EExxttrraaggeerreeaa lluunniiii ddiinnttrr--oo ddaattăă ccaalleennddaarriissttiiccăă..
FFuunnccŃŃiiaa MMOONNTTHH
SSiinnttaaxxăă ==MMOONNTTHH((nnuummăărr__ddaattăă))
� nnuummăărr__ddaattăă poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 54
RReezzuullttaatt rreettuurrnnaatt
Luna (între 1 şi 12) ce corespunde argumentului specificat.
�������� EExxttrraaggeerreeaa zziilleeii ddiinnttrr--oo lluunnăă,, aaffeerreennttăă uunneeii ddaattee ccaalleennddaarriissttiiccee..
FFuunnccŃŃiiaa DDAAYY
SSiinnttaaxxăă ==DDAAYY((nnuummăărr__ddaattăă))
� nnuummăărr__ddaattăă poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie.
RReezzuullttaatt rreettuurrnnaatt
Numărul zilei din lună (între 1 şi 31) ce corespunde argumentului specificat.
�������� EExxttrraaggeerreeaa nnuummăărruulluuii zziilleeii ddiinn ssăăppttăămmâânnăă,, aaffeerreenntt uunneeii ddaattee ccaalleennddaarriissttiiccee..
FFuunnccŃŃiiaa WWEEEEKKDDAAYY
SSiinnttaaxxăă ==WWEEEEKKDDAAYY((nnuummăărr__ddaattăă ;; [[ccoonnssttaannttăă]]))
� nnuummăărr__ddaattăă poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie:
� [[ccoonnssttaannttăă]] reprezintă o constantă, plecând de la care se calculează începutul săptămânii:
o 1 (tip implicit) � prima zi a săptămânii este Duminică, iar ultima zi este sâmbătă;
o 2 � prima zi a săptămânii este luni, iar ultima zi este Duminică; o 3 � prima zi a săptămânii este marŃi, iar ultima zi este luni.
RReezzuullttaatt rreettuurrnnaatt
Numărul zilei din săptămână, potrivit semnificaŃiei parametrului opŃional ttiipp::
o 1 =Duminică; 7=sâmbătă � pentru ttiipp=1;
o 1=luni; 7=Duminică � pentru ttiipp = 2; o 1=marŃi; 6=Duminică; 7=luni � pentru tiip = 3.
�������� EExxttrraaggeerreeaa nnuummăărruulluuii ssăăppttăămmâânniiii ddiinnttrr--uunn aann,, aaffeerreenntt uunneeii ddaattee ccaalleennddaarriissttiiccee..
FFuunnccŃŃiiaa WWEEEEKKNNUUMM
SSiinnttaaxxăă ==WWEEEEKKNNUUMM((nnuummăărr__ddaattăă ;; [[ccoonnssttaannttăă]]))
� nnuummăărr__ddaattăă poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie:
� [[ccoonnssttaannttăă]] reprezintă o constantă, plecând de la care se calculează săptămâna ce începe cu Duminică sau cu luni:
o 1 (tip implicit) � prima zi a săptămânii este Duminică, iar ultima zi este sâmbătă;
o 2 � prima zi a săptămânii este luni, iar ultima zi este Duminică; RReezzuullttaatt rreettuurrnnaatt
Numărul săptămânii dintr-un an.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 55
Figura 58 ilustrează câteva exemple de utilizare a funcţiilor prezentate anterior.
Figura 59 Exemplu de utilizare a funcţiilor YEAR, MONTH, DAY, WEEKDAY, WEEKNUM
Excel nu posedă funcţii specializate care să calculeze trimestrul şi semestrul plecând de la o dată
calendaristică. În acest sens, prezentăm în Figura 60 două formule care returnează trimestrul şi
semestrul. Aceste elemente se obţin extrăgând întregul din expresia: MONTH(număr_dată)-
1/nr_de_luni)+1, unde nr_de_luni=3 pentru trimestru şi =6 pentru semestru.
Figura 60 Exemplu de algoritm de calcul pentru trimestru şi semestru Excel nu poate converti o dată calendaristică căreia i s-a aplicat o formatare personalizată completă
(ziua săptămânii, ziua lunii, luna în clar, anul) în text deoarece data calendaristică este de natură
numerică. Dacă o funcţie text va încerca convertească formatul complet al unei date în şir de
caractere, rezultatul va fi doar numărul serial al datei calendaristice ce a fost transformat în text.
Exemplul prezentat în Figura 61 ilustrează posibilitatea transformării unei date calendaristice cu
format complet în şiruri de caractere, prin concatenarea unor funcţii care returnează în format text
segmente din data agregată.
Figura 61 Exemplu de transformare prin funcţii concatenate a datei calendaristice în
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 56
şir de caractere Un exemplu de utilizare a funcţiei condiţionale IF cu funcţii sau date calendaristice este reprezentat
de calculul de majorărilor de întârziere pe tranşe cumulative progresive pentru depăşirea datei
scadenţei este prezentat în Figura 63. Caracteristica acestor majorări constă în faptul că procentul
de penalizare calculat la valoarea facturii se măreşte din ce în ce mai mult în funcţie intervalul de
timp ce separă data scadenţei de ziua curentă, dacă data scadenţei este depăşită (altfel spus,
procentele de penalizare sunt direct proporţionale cu întârzierea la plata facturii).
Practic, se vor calcula majorările aplicate la valoarea facturii, în raport de numărul de zile întârziere,
astfel:
dacă numărul de zile de întârziere <=30 zile de la data scadenţei → majorarea este de 0,3% pe fiecare zi de întârziere;
dacă numărul de zile de întârziere <=90 zile → majorarea este de 0,5% pe fiecare zi de întârziere ce depăşeşte 30 de zile;
dacă numărul de zile de întârziere <=180 zile → majorarea este de 0,7% pe fiecare zi de întârziere ce depăşeşte 90 de zile;
dacă numărul de zile de întârziere >180 zile → majorarea este de 1% pe fiecare zi de întârziere ce depăşeşte 180 de zile.
Reprezentarea grafică a algoritmului de calcul a majorărilor de întârziere este prezentată în Figura
62.
Figura 62 Model de calcul pentru majorări de întârziere Figura 63 ilustrează în celula K5 calculul majorărilor de întârziere pe tranşe cumulative progresive, în
care funcţia condiţională IF operează cu trei celule numite (H5=”Scadenta”, I5=”Valoare”,
J5=”Platit”).
Figura 63 Exemplu de calcul al majorărilor de întârziere, utilizând nume de celule
Figura 64 prezintă în celula O5 aceeaşi formulă de calcul a majorărilor, în condiţiile utilizării
adreselor de celule.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 57
Figura 64 Exemplu de calcul al majorărilor de întârziere, utilizând adrese de celule Un alt exemplu ce simbioză între funcţiile condiţionale (IF, OR) şi funcţiile dată calendaristică (în
acest caz WEEKDAY) este ilustrat în Figura 65 prin formatarea condiţională (bold, chenar, fundal
colorat) pentru acele intrări (E2:E17) unde data calendaristică este o zi de weekend.
Figura 65 Exemplu de formatare condiţională a zilelor de weekend �������� CCaallccuullaarreeaa ddiiffeerreennţţeeii ddee zziillee ccee sseeppaarrăă ddoouuăă ddaattee ccaalleennddaarriissttiiccee..
FFuunnccŃŃiiaa DDAAYYSS336600
SSiinnttaaxxăă ==DDAAYYSS336600((ddaattăă__ddeebbuutt ;; ddaattăă ssffâârrşşiitt))
� ddaattăă__ddeebbuutt poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie;
� ddaattăă__ssffâârrşşiitt idem. RReezzuullttaatt rreettuurrnnaatt
Numărul de zile ce separă data de debut de data de sfârşit.
OObbsseerrvvaaţţiiii..
a. Diferenţa între 2 date calendaristice este calculată luând ca bază un an ce are 360 zile.
b. În anumite cazuri, diferenţa de zile între două date calendaristice este returnată în format dată calendaristică (de exemplu, diferenţa DAYS360 între 05-feb-2009 şi 10- feb-2009, returnează 05-Ian-1900, adică numărul 5 formatat în dată calendaristică). În aceste cazuri, se impune formatarea numerică a rezultatului diferenţei.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 58
�������� CCaallccuullaarreeaa ddiiffeerreennţţeeii ((zziillee,, lluunnii,, aannii)) ccee sseeppaarrăă ddoouuăă ddaattee ccaalleennddaarriissttiiccee..
FFuunnccŃŃiiaa DDAATTEEDDIIFF
SSiinnttaaxxăă ==DDAATTEEDDIIFF((ddaattăă ssffâârrşşiitt ;; ddaattăă__ddeebbuutt ;; „„ccoodd__ffoorrmmaatt__ddaattăă”” ))
� ddaattăă__ddeebbuutt poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie;
� ddaattăă__ssffâârrşşiitt idem.
� ccoodd__ffoorrmmaatt__ddaattăă__ccaalleennddaarriissttiiccăă reprezintă simbolul componentelor unui format tip dată calendaristică: („d” = număr de zile, „m” = număr de luni, „y” = număr de ani.
RReezzuullttaatt rreettuurrnnaatt
Numărul de zile, de luni sau de ani ce separă două date calendaristice.
Figura 66 ilustrează un exemplu de calcul al diferenţelor în zile, luni şi ani între două date
calendaristice (12 martie 2000 şi 22 februarie 2009).
Figura 66 Exemplu de calcul al diferenţelor între două date calendaristice prin DATEDIF
�������� CCaallccuullaarreeaa nnuummăărruulluuii ddee zziillee lluuccrrăăttooaarree ccee sseeppaarrăă ddoouuăă ddaattee ccaalleennddaarriissttiiccee..
FFuunnccŃŃiiaa NNEETTWWOORRKKDDAAYYSS
SSiinnttaaxxăă ==NNEETTWWOORRKKDDAAYYSS((ddaattăă__ddeebbuutt ;; ddaattăă ssffâârrşşiitt [[;;ssăărrbbăăttoorrii]]))
� ddaattăă__ddeebbuutt poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie;
� ddaattăă__ssffâârrşşiitt idem;
� [[ssăărrbbăăttoorrii]] este un câmp ce conŃine date calendaristice ce semnifică în general o perioadă de inactivitate (indisponibilitate) ce este reglementată în funcŃie de contextul activităŃii utilizatorului (sărbători legale, vacanŃe, deplasări, sesiuni de examene, etc.).
RReezzuullttaatt rreettuurrnnaatt
Numărul de zile lucrătoare (fără a lua în considerare week-end-urile şi sărbătorile legale) dintre două date calendaristice.
OObbsseerrvvaaţţiiii..
În Excel, funcŃia NETWORKDAYS nu este disponibilă în mod implicit. Aceasta se instalează prin încărcarea unor componente, la cererea utilizatorului. Acest demers este operaŃional prin comanda Office Button � Excel Options � tabul Add-Ins, opŃiunea Analysis ToolPak. Idem observaŃia b) aferentă funcŃiei DAYS360.
Un exemplu de utilizare a funcţiei NETWORKDAYS este ilustrat în Figura 67.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 59
Figura 67 Exemplu de utilizare a funcţiei NETWORKDAYS
�������� CCaallccuullaarreeaa uunneeii ddaattee ccaalleennddaarriissttiiccee vviiiittooaarree ddeeccaallaattee ccuu uunn nnuummăărr ddee zziillee lluuccrrăăttooaarree..
FFuunnccŃŃiiaa WWOORRKKDDAAYY
SSiinnttaaxxăă ==WWOORRKKDDAAYY((ddaattăă__ddeebbuutt;;nnuummăărr__zziillee [[;;ssăărrbbăăttoorrii]]))
� ddaattăă__ddeebbuutt poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie;
� nnuummăărr__zziillee reprezintă o constantă numerică, o adresă (sau un nume) de celulă ce conŃine valoarea numerică ce decalează în timp ddaattaa__ddeebbuutt ;
� [[ssăărrbbăăttoorrii]] este un câmp ce conŃine date calendaristice ce semnifică în general o perioadă de inactivitate (indisponibilitate) ce este reglementată în funcŃie de contextul activităŃii utilizatorului (sărbători legale, vacanŃe, deplasări, sesiuni de examene, etc.).
RReezzuullttaatt rreettuurrnnaatt
O dată calendaristică viitoare, decalată cu n zile lucrătoare faŃă de o dată de iniŃială, considerată ca bază.
OObbsseerrvvaaţţiiee.. Idem observaţia a) aferentă funcţiei NETWORKDAYS.
Un exemplu de utilizare a funcŃiei WORKDAY calculează data livrării unui produs comandat, ştiindu-se că în zilele de week-end şi de sărbători legale, unitatea furnizoare nu lucrează. Numărul de zile necesar onorării comenzii este de 45. Figura 68 ilustrează acest exemplu de utilizare.
Figura 68 Exemplu de utilizare a funcţiei WORKDAY: aplicaţia 1 Un alt exemplu de utilizare (prezentat în Figura 69) calculează o serie incrementală de date
calendaristice (data cotaţiei valutei Euro), fără a ţine cont de weekend-uri şi sărbători legale, plecând
de la o dată de referinţă (B13=01 ianuarie 2009).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 60
Figura 69 Exemplu de utilizare a funcţiei WORKDAY: aplicaţia 2
�������� CCaallccuullaarreeaa uullttiimmeeii zziillee ddiinn lluunnăă..
FFuunnccŃŃiiaa EEOOMMOONNTTHH
SSiinnttaaxxăă ==EEOOMMOONNTTHH((ddaattăă__ddeebbuutt ;; ++//ffaaccttoorr__ddeeccaallaajj))
� ddaattăă__ddeebbuutt poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie;
� ffaaccttoorr__ddeeccaallaajj reprezintă numărul de luni (constantă numerică pozitivă/negativă) de decalare în avans/urmă, faŃă de o dată de referinŃă.
RReezzuullttaatt rreettuurrnnaatt
O dată calendaristică ce reprezintă ultima zi din lună.
OObbsseerrvvaaţţiiii..
În Excel, funcŃia EOMONTH (ca şi NETWORKDAYS sau WORKDAY) nu este disponibilă în mod implicit, aceasta trebuind instalată prin comanda Office Button � Excel Options � tabul Add-Ins, opŃiunea Analysis ToolPak. FuncŃia EOMONTH are semnificaŃie logică (raŃiune de utilizare) numai în condiŃiile în care rezultatele returnate sunt formatate personalizat cu ziua săptămânii, deoarece este evident pentru toŃi utilizatorii care este ziua de sfârşit a fiecărei luni, dar nu este evident şi în ce zi a săptămânii pică aceasta. Este de recomandat ca la editarea funcŃiei EOMONTH, în sintaxa acesteia să se utilizeze ddaattaa__ddeebbuutt ca referinŃă absolută sau să fie identificată prin nume. Această observaŃie este utilă, deoarece în cazul utilizării unei referinŃe relative pentru ddaattaa__ddeebbuutt, aceasta se va decala cu câte o celulă la procesul de copiere a formulei.
Un exemplu de utilizare a funcţiei EOMONTH este prezentat în Figura 70 (se doreşte a se afla în ce
zile pică data de sfârşit aferentă fiecărei luni, pentru a planifica diferite operaţiuni de plăţi la bancă).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 61
Figura 70 Exemplu de utilizare a funcţiei EOMONTH
�������� CCaallccuullaarreeaa uunneeii ddaattee ccaalleennddaarriissttiiccee,, ddeeccaallaattee ccuu ++nn lluunnii ssaauu ––nn lluunnii,, ffaaţţăă ddee oo ddaattăă ddee
rreeffeerriinnţţăă..
FFuunnccŃŃiiaa EEDDAATTEE
SSiinnttaaxxăă ==EEDDAATTEE((ddaattăă__ddeebbuutt ;; ++//-- ffaaccttoorr__ddeeccaallaajj))
� ddaattăă__ddeebbuutt poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie;
� ffaaccttoorr__ddeeccaallaajj reprezintă numărul de luni (constantă numerică pozitivă/negativă) de decalare în avans/urmă, faŃă de o dată de referinŃă.
RReezzuullttaatt rreettuurrnnaatt
O dată calendaristică decalată cu un număr de luni (exprimate într-o accepŃiune relativă) .
OObbsseerrvvaaţţiiii..
În Excel, funcŃia EDATE (ca şi EOMONTH, NETWORKDAYS sau WORKDAY) este o componentă ce se instalează la cerere, prin comanda Office Button � Excel Options � tabul Add-ins, opŃiunea Analysis ToolPak. FuncŃia EDATE exprimă într-o accepŃiune relativă un decalaj (pozitiv sau negativ) de dată calendaristică cu un anumit număr de luni. În mod normal, diferenŃa între luni, judecată absolut (matematic) este variabilă, deoarece acestea (lunile) conŃin un număr diferit de zile (28/29, 30, 31). FuncŃia EDATE se utilizează în calculul scadenŃelor relative, ce nu Ńin seamă de numărul de zile aferente fiecărei luni, ci iau în calcul luna ca entitate întreagă. Deci, contextul de utilizare a funcŃiei EDATE nu vizează luarea în considerare a numărului de zile aferent lunilor luate în calcul. FuncŃia EDATE are o raŃiune de utilizare numai în condiŃiile în care rezultatele returnate sunt formatate personalizat şi cu ziua săptămânii, deoarece este evident pentru toŃi utilizatorii că se poate deduce cu uşurinŃă data zilei dintr-o lună ce este decalată (+/-), dar nu este evident şi în ce zi a săptămânii pică aceeaşi zi din luna decalată. În Figura 71 am prezentat un context de utilizare a funcţiei EDATE în care se calculează dobânda
unui depozit, la o scadenţă de 6 luni, cu o un procent anual de 15%. Depozitul în cauză se va
capitaliza în cazul în care nu se lichidează la scadenţă, prelungindu-se implicit termenul către o nouă
scadenţă (astfel, este posibil ca din 6 în 6 luni să se ajungă la o nouă scadenţă). Funcţia EDATE
calculează pentru fiecare celulă a câmpului B27:B30 următoarea dată scadentă, decalată cu exact 6
luni.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 62
Figura 71 Exemplu de utilizare a funcţiei EDATE În plus, aplicaţia realizează o formatare condiţională a celulelor ce reprezintă scadenţa, numai dacă
aceasta se identifică cu luna şi anul curent, adică la data scrierii acestui material: februarie 2009.
Testul din formatarea condiţională a luat în calcul şi anul, deoarece luna ar fi fost instanţiată şi
pentru anul 2010 (ea ar fi corespuns cu luna februarie care făcea parte din data curentă). Formula
pentru formatarea condiţională este:
=AND(MONTH(B27)=MONTH(TODAY());YEAR(B27)=YEAR(TODAY()))
�������� CCaallccuullaarreeaa ppoonnddeerriiii uunnuuii iinntteerrvvaall ddee ttiimmpp îînnttrr--uunn aann ccaalleennddaarriissttiicc..
FFuunnccŃŃiiaa YYEEAARRFFRRAACC
SSiinnttaaxxăă ==YYEEAARRFFRRAACC((ddaattăă__ddeebbuutt ;; ddaattăă__ssffâârrşşiitt ;; ccoonnssttaannttăă))
� ddaattăă__ddeebbuutt poate fi un număr serial ce corespunde unei date calendaristice, o adresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este tratată printr-o funcŃie;
� ddaattăă__ssffâârrşşiitt idem ddaattăă__ddeebbuutt;
� ccoonnssttaannttăă este un cod ce semnifică numărul de zile dintr-un an: o 1 � numărul real de zile; o 2 � anul are 360 de zile; o 3 � anul are 365 de zile; o 4 � luna are 30 de zile şi anul are 360 zile
RReezzuullttaatt rreettuurrnnaatt
Un număr cu zecimale ce reprezintă o fracŃiune dintr-un an în care se compară (prin raportare) două date calendaristice.
Exemplul prezentat în Figura 72 ilustrează un caz de utilizare a funcţiei YEARFRAC, prin care o sumă
globală (D4 = 49.505 milioane lei) se repartizează proporţional cu fracţiile lunare ale anului (numărul
de zile aferente fiecărei luni, raportat la numărul de zile ale anului). De asemenea, exemplul de faţă
calculează cumulat şi sumele aferente fiecărei luni din an.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 63
Figura 72 Exemplu de utilizare a funcţiei YEARFRAC
FFuunnccţţiiii ddee ttiipp ttiimmpp
Valorile de tip timp sunt stocate de Excel sub formă de numere zecimale cuprinse între 0,000000
(12:00:00 AM) şi 0,999884 (11:59:59 PM).
Introducerea valorilor de tip timp se poate face utilizând unul dintre formatele predefinite specifice
acestor valori.
TIME, TIMEVALUE, HOUR, MINUTE, SECOND.
�������� MMaanniippuullaarreeaa îînn ffoorrmmuullee aa ccoonnssttaanntteelloorr ddee ttiipp ttiimmpp..
FFuunnccŃŃiiaa TTIIMMEEVVAALLUUEE
SSiinnttaaxxăă ==TTIIMMEEVVAALLUUEE((„„şşiirr__ddee__ccaarraacctteerree””))
� şşiirr__ddee__ccaarraacctteerree este un text ce prezintă similitudini cu formatul unei valori de tip timp. În mod obligatoriu, şirul de caractere trebuie plasat între ghilimele.
RReezzuullttaatt rreettuurrnnaatt
Valoarea de tip timp specificată în argument, prezentată într-un format valoric adecvat.
FFuunnccŃŃiiaa TTIIMMEE
SSiinnttaaxxăă ==TTIIMMEE ((oorrăă ;; mmiinnuutt ;; sseeccuunnddăă))
� oorrăă este o valoare numerică cuprinsă între 0 şi 23;
� mmiinnuutt este o valoare numerică cuprinsă între 0 şi 60;
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 64
� sseeccuunnddăă este o valoare numerică cuprinsă între 0 şi 60. Acest ultim argument poate fi substituit cu separatorul punct şi virgulă.
RReezzuullttaatt rreettuurrnnaatt
Valoarea de tip timp ce corespunde argumentelor specificate.
OObbsseerrvvaaŃŃiiee.. FuncŃiile TIMEVALUE şi TIME (prezentate în Figura 73) sunt utilizate pentru a referi în formule diferite constante de tip timp. Astfel, într-o formulă ce face apel la o valoare de tip timp, se recomandă ca aceasta nu fie abordată printr-o constantă-timp (IF(B8>18:22:08);....;.....), ci prin intermediul unei adrese de celule ce conŃine constanta respectivă (IF(B8>G12);....;.....), sau prin intermediul funcŃiilor specializate TIMEVALUE sau TIME ((IF(B8>TIME(18;22;08);....;.....)).
Figura 73 Exemplu de utilizare a funcţiilor TIMEVALUE şi TIME �������� EExxttrraaggeerreeaa oorreeii ddiinnttrr--uunn nnuummăărr ttiimmpp..
FFuunnccŃŃiiaa HHOOUURR
SSiinnttaaxxăă ==HHOOUURR((nnuummăărr__ttiimmpp))
� nnuummăărr__ttiimmpp poate fi un număr serial zecimal ce corespunde unei valori de tip timp, o adresă de celulă ce conŃine o valoare de tip timp sau o constantă de tip timp ce este tratată printr-o funcŃie specializată.
RReezzuullttaatt rreettuurrnnaatt
Un număr întreg sub forma orei (între 0 şi 24) ce corespunde argumentului specificat.
�������� EExxttrraaggeerreeaa mmiinnuuttuulluuii ddiinnttrr--uunn nnuummăărr ttiimmpp..
FFuunnccŃŃiiaa MMIINNUUTTEE
SSiinnttaaxxăă ==MMIINNUUTTEE((nnuummăărr__ttiimmpp))
� nnuummăărr__ttiimmpp idem HOUR. RReezzuullttaatt rreettuurrnnaatt
Un număr întreg sub forma minutului (între 0 şi 59) ce corespunde argumentului specificat.
�������� EExxttrraaggeerreeaa sseeccuunnddeeii ddiinnttrr--uunn nnuummăărr ttiimmpp..
FFuunnccŃŃiiaa SSEECCOONNDD
SSiinnttaaxxăă ==SSEECCOONNDD((nnuummăărr__ttiimmpp))
� nnuummăărr__ttiimmpp idem HOUR.. RReezzuullttaatt Un număr întreg sub forma secundei (între 0 şi 59) ce corespunde
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 65
rreettuurrnnaatt argumentului specificat.
Figura 74 ilustrează un exemplu de utilizare a funcţiilor prezentate în contextul calculului duratei
unei convorbiri telefonice care se taxează la minut, cu tarife diferite până la ora 22 şi după.
Secundele vorbite până într-un minut se rotunjesc la primul multiplu (minutul).
Figura 74 Exemplu de utilizare a funcţiilor HOUR, MINUTE, SECOND
2.6 Funcţii text
CHAR, CODE, LOWER, UPPER, PROPER, VALUE, TEXT, DOLLAR, FIXED
Excel pune la dispoziŃia utilizatorului o serie de funcŃii de tip şir de caractere, prin care se asigură următoarele conversii: caracter în cod ASCII zecimal (CODE) şi invers (CHAR); majusculele în minuscule şi invers (LOWER, UPPER, PROPER); caracter de tip şir (text) în valoare (VALUE) şi invers (TEXT, DOLLAR, FIXED).
�������� AAffiişşaarreeaa ccaarraacctteerruulluuii ccee ccoorreessppuunnddee uunnuuii ccoodd nnuummeerriicc..
FFuunnccŃŃiiaa CCHHAARR
SSiinnttaaxxăă ==CCHHAARR((ccoodd__nnuummeerriicc))
� ccoodd__nnuummeerriicc este, fie un număr întreg cuprins în intervalul [1,255], fie adresa unei celule sau a unui nume de câmp ce conŃine caracterul respectiv.
RReezzuullttaatt rreettuurrnnaatt
Caracterul ce corespunde unui cod numeric ASCII2
OObbsseerrvvaaŃŃiiii. a. Sistemul de operare, permite introducerea caracterelor alfanumerice sau semi-grafice, prin
activarea combinaŃiei de taste Alt + codul numeric ASCII al caracterului (codul numeric se tastează în timp ce tasta Alt a fost activată). De exemplu, Alt + 65 returnează caracterul A.
b. Dacă argumentul ccoodd__nnuummeerriicc este în afara intervalului, funcŃia CHAR va returna valoarea de eroare #VALUE!.
�������� AAffiişşaarreeaa ccoodduulluuii nnuummeerriicc ccee ccoorreessppuunnddee uunnuuii ccaarraacctteerr..
FFuunnccŃŃiiaa CCOODDEE
2 Cod internaŃional al reprezentării informaŃiei prin caractere (American Standard Coded for Information Interchange)
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 66
SSiinnttaaxxăă ==CCOODDEE((ccaarraacctteerr))
� ccaarraacctteerr este, fie unul dintre cele 255 de caractere ASCII, fie adresa unei celule sau a unui nume de câmp ce conŃine caracterul respectiv .
RReezzuullttaatt rreettuurrnnaatt
Caracterul ce corespunde unui cod numeric ASCII
OObbsseerrvvaaŃŃiiii.
a. Dacă argumentul funcŃiei este un caracter, acesta trebuie plasat între ghilimele. b. Dacă argumentul funcŃiei CODE este o celulă ce conŃine mai multe caractere, funcŃia va returna
întotdeauna primul caracter din stânga (indiferent de natura numerică, alfanumerică sau alfabetică a caracterului respectiv).
Figura 75 ilustrează tabloul codurilor ASCII aferente caracterelor, exemplificând funcŃiile CHAR şi CODE.
Figura 75 Exemplu de utilizare a funcţiilor CHAR şi CODE �������� CCoonnvveerrttiirreeaa ccaarraacctteerreelloorr mmiinnuussccuullee îînn mmaajjuussccuullee..
FFuunnccŃŃiiaa UUPPPPEERR
SSiinnttaaxxăă ==UUPPPPEERR((tteexxtt))
� tteexxtt este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau a unui nume de câmp ce conŃine respectivul şir de caractere .
RReezzuullttaatt rreettuurrnnaatt
Caracterele argumentului convertite în majuscule.
�������� CCoonnvveerrttiirreeaa ccaarraacctteerreelloorr mmaajjuussccuullee îînn mmiinnuussccuullee..
FFuunnccŃŃiiaa LLOOWWEERR
SSiinnttaaxxăă ==LLOOWWEERR((tteexxtt))
� tteexxtt este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau a unui nume de câmp ce conŃine respectivul şir de caractere.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 67
RReezzuullttaatt rreettuurrnnaatt
Caracterele argumentului convertite în minuscule.
�������� CCoonnvveerrttiirreeaa îînn mmaajjuussccuullăă aa ffiieeccăărruuii pprriimm ccaarraacctteerr aaffeerreenntt uunnuuii şşiirr ddee ccaarraacctteerree..
FFuunnccŃŃiiaa PPRROOPPEERR
SSiinnttaaxxăă ==PPRROOPPEERR((tteexxtt))
� tteexxtt este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau a unui nume de câmp ce conŃine respectivul şir de caractere .
RReezzuullttaatt rreettuurrnnaatt
Caracterele argumentului în care prima literă a fiecărui cuvânt este majusculă, iar restul caracterelor sunt minuscule.
Figura 76 prezintă câte un exemplu de utilizare pentru fiecare din cele 3 funcŃii prezentate.
Figura 76 Exemplu de utilizare a funcţiilor UPPER, LOWER şi PROPER �������� CCoonnvveerrttiirreeaa şşiirruurriilloorr ddee ccaarraacctteerree îînn vvaalloorrii nnuummeerriiccee..
FFuunnccŃŃiiaa VVAALLUUEE
SSiinnttaaxxăă ==VVAALLUUEE((tteexxtt))
� tteexxtt este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau a unui nume de câmp ce conŃine respectivul şir de caractere.
RReezzuullttaatt rreettuurrnnaatt
Valori numerice corespunzătoare şirului de caractere în format numeric.
OObbsseerrvvaaţţiiee.. Pentru ca şirul de caractere să fie convertit în valori numerice, acesta trebuie să conţină
fie o valoare într-un format numeric pur (Figura 77, celula D13), fie o valoare într-un format
alfanumeric, dar care să fie compatibil cu un format numeric tip: dată calendaristică (celula D12),
timp (celula D14), procentual (D16) sau monetar (D15). Dacă şirul de caractere este în format
alfabetic sau alfanumeric (ce nu prezintă similitudini cu un format numeric) funcţia VALUE va returna
o valoare de eroare (#VALUE!).
Figura 77 Exemple de utilizare a funcţiei VALUE �������� CCoonnvveerrttiirreeaa vvaalloorriilloorr nnuummeerriiccee îînn şşiirruurrii ddee ccaarraacctteerree..
FFuunnccŃŃiiaa TTEEXXTT
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 68
SSiinnttaaxxăă ==TTEEXXTT((vvaallooaarree ;; ffoorrmmaatt__tteexxtt))
� vvaallooaarree reprezintă, fie o valoare numerică, fie adresa unei celule sau a unui nume de câmp ce conŃine respectiva valoare;
� ffoorrmmaatt__tteexxtt reprezintă unul dintre formatele numerice disponibile din caseta de dialog Format Cells (tabul Number), comanda Home�Number (Number Format).
RReezzuullttaatt rreettuurrnnaatt
Un şir formatat de caractere ce corespunde unei valori numerice.
Trei cazuri de utilizare a funcţiei TEXT sunt ilustrate în Figura 78.
Figura 78 Exemple de utilizare a funcţiei TEXT
FFuunnccŃŃiiaa DDOOLLLLAARR
SSiinnttaaxxăă ==DDOOLLLLAARR((vvaallooaarree ;; ++//-- ccoonnssttaannttăă ppoozziiŃŃiioonnaallăă ddee rroottuunnjjiirree))
� vvaallooaarree reprezintă, fie o valoare numerică / şir de caractere, fie adresa unei celule sau a unui nume de câmp ce conŃine respectiva valoare / şir;
� ++//-- ccoonnssttaannttăă ppoozziiŃŃiioonnaallăă ddee rroottuunnjjiirree reprezintă expresia rotunjită a numărului de convertit în şir de caractere.
RReezzuullttaatt rreettuurrnnaatt
Un şir de caractere ce imită formatul monetar, ce corespunde unei valori numerice.
OObbsseerrvvaaţţiiii..
Dacă argumentul „++//-- ccoonnssttaannttăă ppoozziiŃŃiioonnaallăă ddee rroottuunnjjiirree”” lipseşte, funcŃia DOLLAR va afişa cu 2 zecimale (D18, A18) textul ce reprezenta valoarea numerică de convertit. În cazul în care ccoonnssttaannttăă ppoozziiŃŃiioonnaallăă ddee rroottuunnjjiirree este pozitivă, funcŃia DOLLAR operează o rotunjire la partea zecimală (la dreapta separatorului zecimal). Dacă respectiva ccoonnssttaannttăă ddee rroottuunnjjiirree este negativă funcŃia operează o rotunjire la partea întreagă (la stânga separatorului zecimal). Rezultatul returnat de funcŃia DOLLAR va fi transpus automat în formatul monetar impus de localizările şi de setările regionale ale sistemului de operare Windows.
Figura 79 ilustrează câteva exemple reprezentative de utilizare a funcŃiei DOLLAR.
Figura 79 Exemple de utilizare a funcţiei DOLLAR
FFuunnccŃŃiiaa FFIIXXEEDD
SSiinnttaaxxăă ==FFIIXXEEDD((vvaallooaarree ;; ++//-- ccoonnssttaannttăă ppoozziiŃŃiioonnaallăă ddee rroottuunnjjiirree
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 69
[[;;sseeppaarraattoorr__ddee__mmiiii]]))
� vvaallooaarree reprezintă, fie o valoare numerică / şir de caractere, fie adresa unei celule sau a unui nume de câmp ce conŃine respectiva valoare / şir de caractere;
� ++//-- ccoonnssttaannttăă ppoozziiŃŃiioonnaallăă ddee rroottuunnjjiirree reprezintă expresia rotunjită a numărului de convertit în format text;
� sseeppaarraattoorr__ddee__mmiiii este un argument opŃional care la valoarea FALSE, inhibă afişarea separatorului de mii a numărului ce a fost transformat în şir de caractere.
RReezzuullttaatt rreettuurrnnaatt
Un şir formatat de caractere ce corespunde unei valori numerice.
OObbsseerrvvaaţţiiii.. Idem aa) şi bb) de la funcţia DOLLAR.
Figura 80 ilustrează 4 exemple relevante de utilizare a funcŃiei FIXED.
Figura 80 Exemple de utilizare a funcţiei FIXED
FFuunnccţţiiii tteexxtt ddee aammeelliioorraarree aa pprreezzeennttăărriiii
REPT, TRIM, CONCATENATE, LEFT, RIGHT, MID
�������� RReepplliiccaarreeaa uunnuuii şşiirr ddee ccaarraacctteerree..
FFuunnccŃŃiiaa RREEPPTT
SSiinnttaaxxăă ==RREEPPTT((tteexxtt ;; nnuummăărr__ddee__mmuullttiipplliiccăărrii))
� tteexxtt este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau a unui nume de câmp ce conŃine respectivul şir de caractere;
� nnuummăărr__ddee__mmuullttiipplliiccăărrii reprezintă numărul multiplicări ale textului. RReezzuullttaatt rreettuurrnnaatt
Un şir de caractere replicat de un număr de ori..
Exemplul ilustrat în Figura 81 calculează frecvenţa de apariţie a notelor obţinute, ventilând cu
funcţia FREQUENCY o distribuţie statistică de date („Nota la examen”, pe plaja B4:B638) pe zece
intervale (D4:D13). Pentru o mai uşoară interpretare a datelor, aplicaţia va pune în evidenţă numărul
de note ce corespunde fiecărui interval statistic. Această marcare ce se aseamănă cu o reprezentare
grafică, poate fi implementată prin multiplicarea cu funcţia REPT a caracterului „pipe” (bara
verticală) de un număr de ori egal cu numărul de note pe fiecare interval statistic (număr returnat de
funcţia FREQUENCY). În plus, aplicaţia mai operează o formatare condiţională pe câmpul F4:F13, ce
marchează cu o culoare „bastonaşele” ce corespund notelor mai mici decât 5, şi cu altă culoare
notele mai mari sau egale cu 5.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 70
Figura 81 Exemplu de utilizare a funcţiilor REPT şi FREQUENCY
�������� EElliimmiinnaarreeaa ssppaaţţiiiilloorr iinnuuttiillee aaffeerreennttee uunnuuii şşiirr ddee ccaarraacctteerree..
FFuunnccŃŃiiaa TTRRIIMM
SSiinnttaaxxăă ==TTRRIIMM((tteexxtt))
� tteexxtt este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau a unui nume de câmp ce conŃine respectivul şir de caractere.
RReezzuullttaatt rreettuurrnnaatt
Un şir de caractere căruia i s-au anulat spaŃiile inutile (cu excepŃia spaŃiilor care separă cuvintele textului).
�������� CCoonnccaatteennaarreeaa şşiirruurriilloorr ddee ccaarraacctteerree..
FFuunnccŃŃiiaa CCOONNCCAATTEENNAATTEE
SSiinnttaaxxăă ==CCOONNCCAATTEENNAATTEE((tteexxtt11 ;; tteexxtt22 ;; ……tteexxttnn))
� tteexxtt11 ;; tteexxtt22 ;; ……tteexxttnn este fie, o listă de şiruri de caractere (între ghilimele), fie adresa unor celule sau a unor nume de câmpuri ce conŃin respectivele şiruri de caractere.
RReezzuullttaatt rreettuurrnnaatt
Mai multe şiruri de caractere sau valori numerice concatenate (unite unele cu altele).
OObbsseerrvvaaŃŃiiee.. Concatenarea şirurilor de caractere, a valorilor numerice (cu excepŃia numerelor de tip dată calendaristică sau timp), precum şi a şirurilor de caractere cu valorile numerice este operaŃională şi prin semnul && (ampersand) plasat între elementele ce fac obiectul concatenării. Pentru această operaŃie, trebuie respectate regulile de sintaxă pentru şirurile de caractere (plasarea acestora între ghilimele). Figura 82 ilustrează exemple de utilizare a funcŃiilor enunŃate.
Figura 82 Exemplu de utilizare a funcţiilor TRIM şi CONCATENATE �������� EExxttrraaggeerreeaa ppoozziiţţiioonnaallăă aa ccaarraacctteerreelloorr ddiinnttrr--uunn şşiirr ddee ccaarraacctteerree..
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 71
FFuunnccŃŃiiaa LLEEFFTT
SSiinnttaaxxăă ==LLEEFFTT((tteexxtt [[;;nnuummăărr__ccaarraacctteerree]]))
� tteexxtt este, fie un şir de caractere sau o valoare, fie adresa unei celule sau a unui nume de câmp ce conŃine valoarea sau respectivul şir de caractere;
� [[nnuummăărr__ccaarraacctteerree]] reprezintă numărul de caractere de extras din stânga primului argument. Dacă argumentul lipseşte, se va extrage doar primul caracter din stânga acestuia.
RReezzuullttaatt rreettuurrnnaatt
Primul caracter sau un grup de caractere extrase din stânga unui alt şir de caractere sau aferent unei valori.
FFuunnccŃŃiiaa RRIIGGHHTT
SSiinnttaaxxăă ==RRIIGGHHTT((tteexxtt [[;;nnuummăărr__ccaarraacctteerree]]))
� tteexxtt este, fie un şir de caractere sau o valoare, fie adresa unei celule sau a unui nume de câmp ce conŃine valoarea sau respectivul şir de caractere;
� [[nnuummăărr__ccaarraacctteerree]] reprezintă numărul de caractere de extras din dreapta primului argument. Dacă argumentul lipseşte, se va extrage doar primul caracter din dreapta acestuia.
RReezzuullttaatt rreettuurrnnaatt
Primul caracter sau un grup de caractere extrase din dreapta unui alt şir de caractere sau aferent unei valori.
FFuunnccŃŃiiaa MMIIDD
SSiinnttaaxxăă ==MMIIDD((tteexxtt ;; ddeebbuutt ;; nnuummăărr__ccaarraacctteerree))
� tteexxtt este, fie un şir de caractere sau o valoare, fie adresa unei celule sau a unui nume de câmp ce conŃine valoarea sau respectivul şir de caractere;
� ddeebbuutt indică un număr ce semnifică valoarea poziŃională (de la stânga spre dreapta) a unui caracter într-un şir, începând de la care să se extragă un număr de caractere.
� nnuummăărr__ccaarraacctteerree reprezintă numărul de caractere de extras începând cu valoarea poziŃională a argumentului ddeebbuutt.
RReezzuullttaatt rreettuurrnnaatt
Un număr de caractere extrase dintr-un şir de caractere în funcŃie de coordonatele dictate de o valoare poziŃională.
OObbsseerrvvaaţţiiii..
aa.. Funcţia MID este substituibilă cu funcţiile LEFT şi RIGHT.
În cazul în care primul argument este valoare numerică, funcŃiile LEFT, RIGHT şi MID returnează şiruri de caractere. Dacă rezultatul returnat constituie o intrare într-o formulă ce-l utilizează în calcule, acesta trebuie convertit în valoare numerică prin funcŃia VALUE.
bb.. Funcţiile LEFT, RIGHT şi MID sunt utilizate cu precădere atunci când este vorba de prelucrarea unor elemente ce fac parte dintr-un cod compozit. Acest cod conţine mai multe elemente disociabile în părţi sau componente care au o semnificaţie bine stabilită şi sunt compacte din punct de vedere poziţional. Regula, în acest caz este ca prima parte a codului să conţină fix n caractere, iar a doua parte a codului să conţină m caractere.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 72
Figura 83 ilustrează posibilităŃile de disociere a unui element numeric (Cod Produs) în părŃile sale componente (Cod Magazin) şi Identificator Produs), cu ajutorul funcŃiilor LEFT şi RIGHT.
Figura 83 Exemplu de utilizare a funcţiilor LEFT şi RIGHT. Disocierea unui cod compozit
Într-un alt exemplu, ilustrat în Figura 84 se concatenează primul caracter din stânga textului sursă
(A2), transformat în majusculă =UPPER(LEFT(A2;1)), cu rezultatul extragerii din dreapta a şirului de
caractere sursă, din care se scade primul caracter: RIGHT(A2;LEN(A2)-1).
Figura 84 Exemplu de convertire a primului caracter dintr-un text în majusculă
Exemplul următor, ilustrat în Figura 85 disociază Codul Numeric Personal (CNP) şi extrage din acesta
elementele cu care se formează data naşterii. CNP este un cod compozit format din 13 caractere.
Semnificaţia primelor 7 caractere este următoarea : primul caracter semnifică genul (sexul)
persoanei (1-pentru masculin şi 2-pentru feminin); următoarele două caractere (valorile poziţionale
2 şi 3) semnifică anul naşterii; următoarele două caractere (valorile poziţionale 4 şi 5) semnifică luna
naşterii, iar ultimele două (din primele 7, adică valorile poziţionale 6 şi 7) semnifică ziua naşterii.
Funcţia tip „dată calendaristică” DATE returnează un număr dată în format dată, dacă sunt precizate
argumentele „An”, „Lună” şi „Zi”. Practic, fiecare argument al funcţiei DATE este substituit cu funcţia
MID ce extrage din celula A16 (ce conţine CNP), poziţional, câte două caractere ce reprezintă anul,
luna şi ziua naşterii.
În plus, aplicaţia formatează personalizat (Format Cells > Number > Custom >Type) Codul Numeric
Personal, cu separator „.” (punct) între elementele componente şi validează printr-o formulă esenţa
numerică a codului şi lungimea acestuia de 13 caractere.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 73
Figura 85 Exemplu de disociere a Codului Numeric Personal şi transformarea unei părţi din acesta în data naşterii
În numeroase cazuri, funcţiile prezentate se utilizează în pereche cu funcţiile de consultare şi/sau
cu funcţiile condiţionale. În Figura 86 (celula B36), am prezentat un exemplu prin care se
localizează ID Produs (ultimele patru caractere ale codului de produs) într-un tabel de consultare
(Nomenclator) pentru a se recupera preţul de vânzare (corespunzător identificatorului căutat).
Acest element (preţul) recuperat prin VLOOKUP din tabelul „Nomenclator” se înmulţeşte cu un
coeficient de adaos comercial („Coeficient de ponderare”) propriu fiecărui magazin de desfacere
a respectivelor produse. Coeficientul, la rândul lui face parte din codul produsului şi este extras
din coloana a 2-a a câmpului „Magazine”.
Figura 86 Exemplu de utilizare a codurilor compozite în contexte de consultare Un ultim exemplu de utilizare a funcţiei MID (Figura 87) însumează pe câmpul A40:A45 valorile
disociate dintr-un cod compozit.
Suma ce se calculează asupra rezultatului funcţiei MID (convertit în valoare numerică cu VALUE) care
se aplică pe cele 2 caractere disociate începând de la poziţia a 3-a, aferente câmpului A40:A45 se
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 74
finalizează cu o valoare de eroare (în celula A46). Excel nu poate aplica o funcţie totalizatoare asupra
unor rezultate disociate dintr-un element unitar, pe o plajă de celule.
Dacă în coloana B s-ar fi disociat individual cele două caractere din codul de produs, începând cu al
3-lea caracter, s-ar fi obţinut un rezultat corect aplicând formula ce însumează elementele
individuale convertite în valoare.
O altă soluţie de rezolvare a cazului, ce conduce la un rezultat corect este folosirea primului demers
„încapsulat” printr-o formulă matriceală.
Figura 87 Exemplu de însumare a unor elemente identificate poziţional intr-un cod compozit
FFuunnccţţiiii tteexxtt ddee ccăăuuttaarree,, îînnllooccuuiirree,, ccoommppaarraarree şşii mmăăssuurraarree aa lluunnggiimmiiii ccaarraacctteerreelloorr..
LEN, EXACT, SUBSTITUTE, FIND, SEARCH, REPLACE
�������� CCaallccuullaarreeaa nnuummăărruulluuii ddee ccaarraacctteerree aaffeerreenntt uunnuuii şşiirr ddee ccaarraacctteerree ssaauu vvaalloorrii nnuummeerriiccee..
FFuunnccŃŃiiaa LLEENN
SSiinnttaaxxăă ==LLEENN((tteexxtt))
� tteexxtt este, fie un şir de caractere sau o valoare numerică, fie adresa unei celule sau a unui nume de câmp ce conŃine respectivul şir sau valoare.
RReezzuullttaatt rreettuurrnnaatt
Numărul de caractere aferent unui şir de caractere sau aferentă unei valori (numărul de cifre din care este formată).
În Figura 88 prezentăm un context de utilizare a funcţiei LEN, în care un cod compozit conţine mai
multe elemente disociabile: un element de lungime fixă (ultimele 4 cifre din dreapta codului) şi un
element de lungime variabilă plasat la stânga. Soluţia disocierii codului compozit constă în
extragerea cu funcţia LEFT a caracterelor din stânga, până la limita ocupată de ultimele 4 caractere.
Pentru a afla această limită, se face diferenţa între numărul total al caracterelor şi numărul
caracterelor din dreapta, după care se extrage rezultatul.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 75
Figura 88 Exemplu de utilizare a funcţiei LEN �������� CCoommppaarraarreeaa aa ddoouuăă şşiirruurrii ddee ccaarraacctteerree ssaauu vvaalloorrii nnuummeerriiccee..
FFuunnccŃŃiiaa EEXXAACCTT
SSiinnttaaxxăă ==EEXXAACCTT((tteexxtt11;;tteexxtt22))
� tteexxtt11 şi tteexxtt22 sunt, fie un două şiruri de caractere sau valori numerice, fie adresa a 2 celule sau nume de câmpuri ce conŃin şirurile sau valorile.
RReezzuullttaatt rreettuurrnnaatt
Valoarea logică TRUE, dacă şirurile de caractere sunt identice;
Valoarea logică FALSE, dacă şirurile de caractere diferă.
OObbsseerrvvaaţţiiee.. Funcţia EXCACT face deosebire între majuscule şi minuscule (este senzitivă), dar ignoră
diferenţele de formatare.
�������� CCăăuuttaarreeaa ppoozziiţţiioonnaallăă aa uunnuuii ccaarraacctteerr îînnttrr--uunn şşiirr ddee ccaarraacctteerree ssaauu vvaalloorrii nnuummeerriiccee..
FFuunnccŃŃiiaa FFIINNDD//SSEEAARRCCHH
SSiinnttaaxxăă ==FFIINNDD//SSEEAARRCCHH((tteexxtt__ccăăuuttaatt ;; tteexxtt__bbaazzăă [[;;nnuummăărr]]))
� tteexxtt__ccăăuuttaatt este fie un şir de caractere sau o valoare ce se doreşte a se identifica; � tteexxtt__bbaazzăă specifică şirul de caractere sau valoarea în care se identifică textul
căutat; � [[nnuummăărr]] este un număr ce reprezintă valoarea poziŃională a caracterului de la care să
înceapă căutarea.
RReezzuullttaatt rreettuurrnnaatt
O valoare numerică ce reprezintă valoarea poziŃională a şirului de caractere căutat.
OObbsseerrvvaaţţiiee.. Funcţia FIND este senzitivă la caractere (este sensibilă la tipul de litere majuscule sau
minuscule). Funcţia SEARCH nu este senzitivă.
Figura 89 ilustrează un exemplu de utilizare a funcţiei SEARCH, prin care se extrage numele şi
prenumele unei persoane din elementul compozit „Nume şi prenume”.
Pentru extragerea numelui (pe plaja de celule B3:B11) formula editată în B4 disociază prin funcţia
LEFT şirul de caractere din celula A4, începând cu primul caracter şi până unde se întâlneşte primul
spaţiu. Acesta este returnat de funcţia SEARCH, care la rândul ei caută de la stânga la dreapta primul
spaţiu dintre cuvinte (dintre nume şi prenume) în şirul de caractere din celula A4. Din valoarea
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 76
poziţională returnată de SEARCH se scade un caracter (deoarece din stânga a fost extras un număr
de caractere până la primul spaţiu, inclusiv spaţiul).
Un alt exemplu de utilizare a funcţiei SEARCH (prezentat tot in Figura 89 extrage prenumele (unul
sau mai multe) din elementul compozit „Nume şi prenume”. Formula editată în celula C4 extrage
partea din dreapta a şirului de caractere din celula A4. Numărul de caractere de extras se calculează
făcând diferenţa între numărul total de caractere (LEN(A4)) şi valoarea poziţională a primului spaţiu,
returnată de funcţia SEARCH. În acest mod, dacă se identifică un caracter de separaţie (spaţiul de
exemplu) se pot extrage dintr-un şir de caractere agregat („Nume şi prenume”) oricâte sub-şiruri
(prenume).
Ultima parte a aplicaţiei compune o adresă de e-mail, scrisă cu caractere minuscule (LOWER) prin
concatenarea în celula E4 a numelui (B4), a primului caracter al prenumelui (LEFT(C4)), a caracterului
„@”, şi a domeniului „ase.ro”.
Figura 89 Exemplu de utilizare a funcţiei SEARCH. În cele ce urmează, prezentăm în Figura 90 un exemplu de utilizare a funcţiei FIND, în care se
specifică valoarea poziţională a caracterului de la care să înceapă căutarea.
În coloana A este editat un cod compozit în patru trepte (exemplificat generic), separate de spaţiu.
Structura internă a codului agregat se compune din elemente inegale ca dimensiune. Aplicaţia
exemplificată îşi propune să disocieze elementele variabile ale codului şi să schimbe ordinea
acestora.
Pentru disocierea elementelor codului se pleacă de la identificarea primului spaţiu, celui de-al doilea,
şi aşa mai departe.
Prima treaptă a codului este extrasă în coloana F cu funcţia LEFT, ce disociază din stânga atâtea
caractere câte sunt identificate până la primul spaţiu (coloana B).
Extragerea celui de-al doilea grup de caractere (a doua treaptă a codului) operează în coloana G cu
funcţia MID asupra textului sursă (A5), plecând de la valoarea poziţională a celui de-al doilea spaţiu
(obţinut în coloana C prin localizarea celui de-al doilea spaţiu, plecând de la valoarea poziţională a
primului spaţiu), plus un caracter şi extrăgând un număr de caractere egal cu diferenţa dintre
valoarea poziţională a celui de-al doilea spaţiu (coloana C) şi valoarea poziţională a primului spaţiu
(coloana B), minus un caracter (spaţiul însuşi).
Extragerea celui de-al treilea grup de caractere operează în coloana H cu funcţia MID asupra textului
sursă (A5), plecând de la valoarea poziţională a celui de-al treilea spaţiu (obţinut în coloana D prin
localizarea celui de-al treilea spaţiu, plecând de la valoarea poziţională a celui de-al doilea, plus un
caracter) şi extrăgând un număr de caractere egal cu diferenţa dintre valoarea poziţională a celui de-
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 77
al treilea spaţiu (coloana D) şi valoarea poziţională a celui de-al doilea spaţiu (coloana C), minus un
caracter (spaţiul însuşi).
Ultimul grup de caractere este extras cu funcţia RIGHT, prin diferenţa între numărul total de
caractere (LEN(A5)) şi valoarea poziţională a ultimului spaţiu (coloana D), plus un caracter.
In coloana J, codul este recompus în altă ordine prin concatenarea segmentelor de cod extrase
anterior din codul sursă în coloanele F, G, H şi I.
Figura 90 Exemplu de utilizare a funcţiei FIND Un exemplu recapitulativ al funcţiilor EXACT, PROPER, LEN, SEARCH, UPPER, LEFT şi RIGHT este
ilustrat în Figura 91, potrivit următorului context de utilizare:
- Câmpul B4:B12 va fi supus validării introducerii datelor care să respecte următoarele condiţii:
numele salariatului se editează cu caractere majuscule; prenumele salariatului se editează cu caractere minuscule, dar începe cu majusculă; dimensiunea şirului de caractere va fi cuprinsă între 7 şi 30 de caractere; intrarea în celulă este validă, numai dacă se completează câmpul Marca.
Potrivit cerinţelor informaţionale, formula de validare a fost editată în caseta de dialog Data
Validation la nivelul câmpului B4:B12, dar pentru uşurinţa înţelegerii exemplului şi pentru lizibilitate,
formula a fost editată şi în celula D3.
Rezolvarea acestei cerinţe compuse presupune evaluarea logică prin funcţia AND a celor patru
condiţii. Dacă în mod cumulativ cele patru condiţii sunt adevărate, funcţia returnează valoarea TRUE,
altfel, dacă cel puţin o condiţie este evaluată ca falsă, funcţia returnează valoarea FALSE.
Materializarea rezultatului formulei în procedura de validare, permite introducerea datelor în celule,
dacă formula returnează valoare de adevăr sau inhibă validarea datei în celulă, dacă formula
returnează FALSE.
Pentru editarea cu majuscule a numelui salariatului se compară cu ajutorul funcţiei EXACT numele (extras cu LEFT din agregatul compozit „Nume Prenume”) cu acelaşi nume editat cu majuscule (UPPER...).
Pentru editarea cu minuscule a prenumelui salariatului (în care primul caracter este majusculă) se compară cu ajutorul funcţiei EXACT prenumele (extras cu RIGHT din agregatul compozit „Nume Prenume”) cu acelaşi prenume editat cu prima literă majusculă, iar următoarele minuscule (PROPER...);
Dimensiunea agregatului text „Nume Prenume” poate fi declarată între 7 şi 30 de caractere prin combinaţia funcţiilor LEN bornate pe intervalul 7-30 (LEN(B4)>7;LEN(B4)<30);
Intrarea în celulă este validă dacă marca va fi diferită de celulă vidă (NOT(ISBLANK(A3)).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 78
Figura 91 Exemplu recapitulativ de utilizare a funcţiilor text EXACT, PROPER, LEN, SEARCH, UPPER, LEFT şi RIGHT. Validarea Numelui şi Prenumelui
�������� ÎÎnnllooccuuiirreeaa ppoozziiţţiioonnaallăă aa uunneeii ppăărrţţii ddiinnttrr--uunn şşiirr ddee ccaarraacctteerree ccuu aalltt şşiirr..
FFuunnccŃŃiiaa RREEPPLLAACCEE
SSiinnttaaxxăă ==RREEPPLLAACCEE((tteexxtt__vveecchhii ;; vvaallooaarree__ppoozziiŃŃiioonnaallăă ;; nnuummăărr__ccaarraacctteerree ;; tteexxtt__nnoouu))
� tteexxtt__vveecchhii este fie un şir de caractere sau o valoare numerică sursă (de înlocuit), fie adresa celulei corespunzătoare;
� vvaallooaarree__ppoozziiŃŃiioonnaallăă specifică valoarea poziŃională din text vechi (de la stânga la dreapta), plecând de la care se doreşte înlocuirea cu tteexxtt__nnoouu;
� nnuummăărr__ccaarraacctteerree precizează numărul de caractere din textul vechi ce urmează a fi înlocuite cu textul nou.
� tteexxtt__nnoouu este fie un şir de caractere sau o valoare numerică destinaŃie (cu care tteexxttuull__vveecchhii urmează a fi înlocuit), fie adresa celulei corespunzătoare;
RReezzuullttaatt rreettuurrnnaatt
Un text în care un nou şir de caractere/valoare (tteexxtt__nnoouu) a înlocuit un şir sau o valoare veche, plecând de la o valoare poziŃională.
Un exemplu de utilizare a funcţiei REPLACE este ilustrat în Figura 92. În celula B6 s-a operat
modificarea structurală a unui cod compus din nouă caractere numerice, înlocuind a 5-a valoare
poziţională cu conţinutul celulei A6 (valoarea 50). În urma procesului de înlocuire poziţională a unui
caracter cu două a rezultat un cod compus din zece caractere, ce este mai mare cu un caracter faţă
de codul sursă.
În celula B9 s-a operat modificarea structurală a aceluiaşi cod sursă compus din nouă caractere
numerice, înlocuind de la a 3-a valoare poziţională, un număr de cinci caractere cu conţinutul celulei
A6 (valoarea 50). În urma procesului de înlocuire poziţională a cinci caractere cu două a rezultat un
cod de şase caractere, ce este mai mic cu trei caractere decât codul sursă.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 79
Figura 92 Exemplu de utilizare a funcţiei REPLACE
�������� ÎÎnnllooccuuiirreeaa uunnuuii şşiirr ddee ccaarraacctteerree ccuu aallttuull..
FFuunnccŃŃiiaa SSUUBBSSTTIITTUUTTEE
SSiinnttaaxxăă ==SSUUBBSSTTIITTUUTTEE((tteexxtt ;; tteexxtt__vveecchhii ;; tteexxtt__nnoouu [[;;nnuummăărr]]))
� tteexxtt este referinŃa sursă declarată, fie sub forma unui şir de caractere sau valori numerice, fie sub forma unei adrese de celule / nume de câmp ce conŃine respectivul şir sau valoare, pentru care se doreşte înlocuirea caracterelor.
� tteexxtt__vveecchhii este fie un şir de caractere sau o valoare numerică sursă (de înlocuit), fie adresa celulei corespunzătoare;
� tteexxtt__nnoouu este fie un şir de caractere sau o valoare numerică destinaŃie (cu care tteexxttuull__vveecchhii urmează a fi înlocuit), fie adresa celulei corespunzătoare;
� [[nnuummăărr]] precizează la a câta apariŃie a şirului specificat ca tteexxtt_vveecchhii să se efectueze înlocuirea cu tteexxtt_nnoouu.
RReezzuullttaatt rreettuurrnnaatt
Un text în care un nou şir de caractere/valoare (tteexxtt__nnoouu) a înlocuit un şir sau o valoare veche.
Un exemplu de utilizare a funcţiei SUBSTITUTE (prezentat în Figura 93) înlocuieşte prenumele din
agregatul tip şir de caractere „Nume şi prenume” cu un nou agregat „Cod salariat” format din nume
şi din indicativul locului de muncă. Prenumele (textul substituibil) a fost extras din şirul de caractere
sursă (celula C61) potrivit exemplului explicat la funcţia SEARCH şi ilustrat de Figura 89, de la pagina
76. Textul ce urmează a înlocui prenumele (Indicativ loc muncă) va fi extras prin consultarea verticală
a tabelului de personal (F65:G71) în funcţie de numele şi prenumele salariatului şi recuperarea
coloanei a 2-a, corespunzătoare persoanei identificate în tabel.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 80
Figura 93 Exemplu de utilizare a funcţiei SUBSTITUTE Un alt exemplu ilustrat în Figura 94 extrage ultimul element dintr-un agregat compozit format dintr-
un număr variabil de elemente (denumirea disciplinei de informatică). Pentru a disocia ultimul
cuvânt dintr-o expresie compozită de tip şir de caractere, este nevoie de identificarea numărului de
spaţii ce separă cuvintele şi implicit, localizarea ultimului spaţiu de la care să se extragă din dreapta
un număr de caractere, obţinut ca diferenţă între textul sursă şi valoarea poziţională a ultimului
spaţiu. Explicaţia acestui demers aplicativ poate fi abordat în mai mulţi paşi:
În coloana B se elimină spaţiile dintre cuvintele textului agregat (din coloana A), prin intermediul funcţiei SUBSTITUTE(A32;” ”;””);
În coloana C se face diferenţa între numărul de caractere al agregatului compozit cu spaţii (coloana A) şi numărul de caractere aferente aceluiaşi agregat, dar cu spaţiile eliminate (coloana B). Din această diferenţă rezultă numărul de spaţii dintre cuvinte;
În coloana D se marchează cu caracterul „*” ultimul spaţiu al agregatului text. Practic, prin funcţia SUBSTITUTE se înlocuieşte „ultima” apariţie a spaţiului (antecalculată anterior în coloana C) cu caracterul generic „*”.
Coloana E calculează prin funcţia FIND valoarea poziţională a caracterului „*” în cadrul agregatului text din coloana precedentă;
În ultima coloană, se extrage din dreapta şirului sursă (A32) diferenţa de caractere ce separă numărul de caractere al textului sursă (LEN(A32)) şi valoarea poziţională a ultimului spaţiu (E2).
Figura 94 Exemplu de extragere a ultimului element dintr-un agregat compozit format dintr-un număr variabil de elemente
2.7 Funcţii de căutare şi consultare
FFuunnccţţiiiillee ddee ccăăuuttaarree şşii ccoonnssuullttaarree ((LLooookkuupp && RReeffeerreennccee))
Funcţiile de consultare permit căutarea, identificarea sau referirea conţinutului unor celule. Mai
exact, aceste funcţii asigură, fie căutarea unei valori (număr, rezultat al unei formule sau şir de
caractere) într-o listă sau într-un câmp, fie poziţionarea pe o anumită celulă ce aparţine unui câmp.
CHOOSE, COLUMN, COLUMNS, ROW, ROWS, AREAS, ADDRESS, INDIRECT, MATCH, INDEX,
LOOKUP, VLOOKUP, HLOOKUP.
�������� AAlleeggeerreeaa ddiinn mmaaii mmuullttee ppoossiibbiilliittăăţţii aa uunnuuii eelleemmeenntt iiddeennttiiffiiccaatt ppoozziiţţiioonnaall..
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 81
FFuunnccŃŃiiaa CCHHOOOOSSEE
SSiinnttaaxxăă == CCHHOOOOSSEE((iinnddeexx__nnuummeerriicc ;; lliissttăă__ddee__eelleemmeennttee......))
� iinnddeexx__nnuummeerriicc precizează sub forma unui număr de ordine, care element (de tip text, numeric sau referinŃă celulară) din lista de argumente va fi selectat. Indexul este un număr cuprins între 0 şi 29.
� lliissttăă__ddee__eelleemmeennttee reprezintă o enumerare de elemente (declarate ca şi constante) sau de celule ce conŃin elementele respective.
RReezzuullttaatt rreettuurrnnaatt
În urma unei alegeri dintr-o listă de elemente, returnează o acŃiune sau o valoare, ce urmează a fi activată sau executată, corespunzător unui index numeric.
OObbsseerrvvaaŃŃiiee:: Argumentul ll iissttăă__ddee__eelleemmeennttee nu poate fi un câmp, ci doar o enumerare de constante (numerice sau şir de caractere) sau de celule. Totuşi, în contextul în care funcŃia CHOOSE se utilizează ca argument al unei funcŃii matematice sau statistice, argumentul ll iissttăă__ddee__eelleemmeennttee poate conŃine şi plaje de celule (câmpuri).
Un exemplu de utilizare a funcŃiei de consultare CHOOSE este ilustrat în Figura 95.
Figura 95 Exemple de utilizare a funcţiei de consultare CHOOSE �������� IIddeennttiiffiiccaarreeaa nnuummăărruulluuii ddee ccoollooaannăă ccee ccoorreessppuunnddee uunneeii rreeffeerriinnţţee ddiinnttrr--uunn ccââmmpp..
FFuunnccŃŃiiaa CCOOLLUUMMNN
SSiinnttaaxxăă == CCOOLLUUMMNN((rreeffeerriinnŃŃăă__cceelluullaarrăă ssaauu ccââmmpp))
� rreeffeerriinnŃŃăă__cceelluullaarrăă ssaauu ccââmmpp precizează adresa unei celule sau a unui câmp ce va referi numărul coloanei.
RReezzuullttaatt rreettuurrnnaatt
Numărul coloanei corespunzătoare primei referinŃe celulare aferente coordonatelor unui câmp specificat.
FFuunnccŃŃiiaa CCOOLLUUMMNNSS
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 82
SSiinnttaaxxăă == CCOOLLUUMMNNSS((ccââmmpp))
� ccââmmpp precizează adresa unui câmp ce va referi numărul coloanei. RReezzuullttaatt rreettuurrnnaatt
Numărul de coloane aferent câmpului specificat ca argument.
OObbsseerrvvaaŃŃiiee.. În timp ce funcŃia COLUMN returnează numărul de coloană în mod convenŃional pentru celula sau câmpul ce corespunde numărului curent al respectivei coloane din foaia de calcul (coloana A =1, coloana B=2,... coloana XFD=16.586), funcŃia COLUMNS returnează în mod real numărul de ordine a coloanei respectivului câmp specificat ca argument (număr ce începe întotdeauna cu 1 şi se termină cu un număr egal cu dimensiunea orizontală a câmpului).
Exemple de utilizare a funcŃiilor de consultare COLUMN şi COLUMNS sunt prezentate în Figura 96.
Figura 96 Exemple de utilizare a funcţiilor COLUMN şi COLUMNS �������� IIddeennttiiffiiccaarreeaa nnuummăărruulluuii ddee lliinniiee ccee ccoorreessppuunnddee uunneeii rreeffeerriinnţţee ddiinnttrr--uunn ccââmmpp ..
FFuunnccŃŃiiaa RROOWW
SSiinnttaaxxăă == RROOWW((rreeffeerriinnŃŃăă__cceelluullaarrăă ssaauu ccââmmpp))
� rreeffeerriinnŃŃăă__cceelluullaarrăă ssaauu ccââmmpp precizează adresa unei celule sau a unui câmp ce va referi numărul convenŃional al liniei.
RReezzuullttaatt rreettuurrnnaatt
Numărul liniei corespunzătoare primei referinŃe celulare aferente coordonatelor unui câmp specificat.
FFuunnccŃŃiiaa RROOWWSS
SSiinnttaaxxăă == RROOWWSS((ccââmmpp))
� ccââmmpp precizează adresa unui câmp ce va specifica numărul real al liniei. RReezzuullttaatt rreettuurrnnaatt
Numărul de linii aferente câmpului specificat ca argument.
OObbsseerrvvaaŃŃiiee.. În timp ce funcŃia ROW returnează numărul de linie în mod convenŃional (linia 1=1, linia 2=2,... ultima linie=1.048.576), funcŃia ROWS returnează numărul de linie în mod real (număr ce
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 83
începe întotdeauna cu 1 şi se termină cu un număr egal cu dimensiunea verticală a câmpului).
Exemple de utilizare a funcŃiilor de consultare ROW şi ROWS sunt ilustrate în Figura 97.
Figura 97 Exemple de utilizare a funcţiilor de consultare ROW şi ROWS �������� IIddeennttiiffiiccaarreeaa nnuummăărruulluuii ddee zzoonnee aaddiiaacceennttee ddiinnttrr--uunn ccââmmpp((uurrii))..
FFuunnccŃŃiiaa AARREEAASS
SSiinnttaaxxăă ==AARREEAASS((ccââmmpp//ccââmmppuurrii))
� ccââmmpp((uurrii)) precizează adresa unui câmp sau a mai multor câmpuri ce urmează a fi identificate.
RReezzuullttaatt rreettuurrnnaatt
Numărul de zone contigue dintr-un câmp.
OObbsseerrvvaaŃŃiiee.. Dacă argumentul cuprinde multe zone contigue (mai multe câmpuri), atunci acesta se mai închide într-o pereche de paranteze rotunde.
În Figura 98 este prezentat un exemplu de utilizare a funcŃiei AREAS.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 84
Figura 98 Exemplu de utilizare a funcţiei de consultare AREAS �������� AAffiişşaarreeaa aaddrreesseeii uunneeii cceelluullee..
FFuunnccŃŃiiaa AADDDDRREESSSS
SSiinnttaaxxăă ==AADDRREESSSS((nnrr__lliinniiee ;; nnrr__ccoollooaannăă
[[;;ttiipp__rreeffeerriinnŃŃăă,,vvaall__llooggiiccăă ;; ””nnuummee__ffooaaiiee__ccaallccuull””]]))
� nnrr__lliinniiee specifică numărul de linie al referinŃei celulare;
� nnrr__ccoollooaannăă precizează numărul de coloană al celulei;
� [[ttiipp__rreeffeerriinnŃŃăă]] specifică printr-un număr [1-4] referinŃe absolute pe linie şi pe coloană (1-implicit), numai pe linie (2), numai pe coloană (3), precum şi referinŃe relative (4);
� [[vvaallooaarree__llooggiiccăă]] specifică prin opŃiunea False (implicit opŃiunea are valoarea True) adresele referinŃei celulare sub forma RnCm - unde Rn indică linia (Row) n, iar Cm returnează coloana (Column) m. Aceste coordonate se numesc referenŃiale;
� [„nnuummee__ffooaaiiee__ccaallccuull”] afişează numele foii de calcul sau un text explicativ referitor la adresa celulei în cauză.
RReezzuullttaatt rreettuurrnnaatt
O adresă de celulă sub formă de text.
În Figura 99 sunt ilustrate câteva exemple de utilizare a funcŃiei ADDRESS.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 85
Figura 99 Exemple de utilizare a funcţiei ADDRESS �������� MMooddiiffiiccaarreeaa rreeffeerriinnţţeelloorr cceelluullaarree llaa ccaarree ffaaccee aappeell oo ffoorrmmuullăă,, ffăărrăă ccaa rreessppeeccttiivvaa ffoorrmmuullăă ssăă ffiiee
aajjuussttaattăă llaa nnooiillee ccoooorrddoonnaattee..
FFuunnccŃŃiiaa IINNDDIIRREECCTT
SSiinnttaaxxăă ==IINNDDIIRREECCTT((rreeffeerriinnŃŃăă__tteexxtt [[;; ppaarraammeettrruu__llooggiicc]]))
� rreeffeerriinnŃŃăă__tteexxtt specifică o referinŃă către o celulă, un nume de câmp definit ca referinŃă sau o referinŃă către o celulă specificată ca şir de caractere.
� [[ppaarraammeettrruu__llooggiicc]] specifică o valoare logică de tip True sau False, prin care se face precizarea tipului de referinŃă ce este conŃinut de primul argument:
True (parametru implicit): semnifică o referinŃă de tip A1 (referinŃă standard a foii de calcul);
False : semnifică o referinŃă de tip R1C1 (referinŃă a foii de calcul prin care se precizează explicit linia-Row şi coloana-Column);
RReezzuullttaatt rreettuurrnnaatt
ReferinŃa specificată printr-un şir de caractere.
OObbsseerrvvaaŃŃiiee.. FuncŃia INDIRECT returnează o valoare de eroare, dacă referinŃa_text nu este o referinŃă celulară validă. Necesitatea funcŃiei INDIRECT derivă din adresarea din interiorul formulelor a referinŃelor ce ulterior pot fi modificate sau anulate. Exemplul următor, ilustrat in Figura 100 calculează totalul vânzărilor pentru fiecare produs in parte (din cele patru), pe parcursul lunilor februarie – decembrie. Suma poate fi calculată uşor cu funcŃia SUM, pentru Produsul1 pe domeniul C6:C16. Dacă se doreşte schimbarea lunilor pentru care este operaŃional calculul (de exemplu, pentru lunile martie –decembrie), formula de calcul a sumei trebuie reajustată la noile coordonate pentru Produsul1, pe domeniul C7:C16. Pentru a evita acest inconvenient, funcŃia INDIRECT construieşte un model independent de coordonatele relative ale liniilor şi coloanelor care participă la calcul. Astfel, în celula D1 şi E1 se marchează numărul absolut al primei şi ultimei linii de însumat. Similar, în celulele câmpului C3:F3 se editează etichetele de coloană în expresie absolută (C, D, E, F). Formula din F19 aplică funcŃia SUM pe coordonatele rezultate din concatenarea adreselor celulelor ce reprezintă numărul de linie şi adresa coloanei. Modificând numărul de linie din D1 sau E1, calculul sumei poate fi ajustat astfel încât să includă orice linie, fără a se modifica formula de calcul.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 86
Figura 100 Exemplu de utilizare a funcţiei INDIRECT �������� IIddeennttiiffiiccaarreeaa ppoozziiţţiieeii rreellaattiivvee aa uunnuuii eelleemmeenntt îînn rraappoorrtt ddee ccoooorrddoonnaatteellee ssaallee îînnttrr--uunn vveeccttoorr ddee
ccoonnssuullttaarree..
FFuunnccŃŃiiaa MMAATTCCHH
SSiinnttaaxxăă ==MMAATTCCHH((cchheeiiee__ddee__ccoonnssuullttaarree ;; vveeccttoorr__ddee__ccoonnssuullttaarree [[;;ppaarraammeettrruu]]))
� cchheeiiee__ddee__ccoonnssuullttaarree specifică o valoare ce se doreşte a fi căutată într-un tabel de consultare. Cheia de consultare poate fi o constantă (numerică, şir de caractere, logică) sau o referinŃă celulară ce conŃine valoarea căutată;
� vveeccttoorr__ddee__ccoonnssuullttaarree este un câmp orizontal (o linie şi mai multe coloane) sau un camp vertical (o coloană şi mai multe linii) ce conŃine posibilele valori căutate;
� [[ppaarraammeettrruu]] specifică un număr cu următoarea semnificaŃie:
0 (zero): identifică prima valoare (pot fi mai multe) egală cu valoarea cheii de consultare. Tabelul de consultare poate fi în acest caz nesortat. Dacă nu este găsită nici o valoare, funcŃia returnează “valoare nedisponibilă” (#N/A);
1 (parametru implicit): caută cea mai mare valoare, cea mai mică, sau egală cu valoarea căutată. Tabelul de consultare trebuie să fie sortat în ordinea crescătoare a cheii de consultare;
-1: caută cea mai mică valoare imediat mai mare sau egală decât valoarea cheii de consultare. Tabelul de consultare trebuie să fie sortat în ordinea descrescătoare a cheii de consultare.
RReezzuullttaatt rreettuurrnnaatt
PoziŃia relativă a unui element într-un vector de consultare. Această poziŃie relativă caută să identifice numărul de linie sau de coloană pentru un element căutat într-un câmp orizontal sau vertical.
OObbsseerrvvaaŃŃiiee.. FuncŃia MATCH are relevanŃă împreună cu alte funcŃii de consultare care referă prin numărul de linie şi numărul de coloană coordonatele unei valori căutate (de exemplu, cu funcŃiile INDEX şi HLOOKUP). În Figura 101 sunt ilustrate două exemple de utilizare a funcŃiei MATCH. Un prim exemplu caută o cheie de consultare cu valoarea 104 într-un câmp vertical numit Cod_Client (B3:B10). FuncŃia MATCH va returna linia 5 din tabelul de consultare. Al doilea exemplu caută o cheie de consultare de tip şir de caractere cu valoarea „Cod_Fiscal” într-un câmp orizontal numit Linie_Antet (B3:G10). Datorită faptului că tabelul de consultare Linie_Antet, nu este sortat alfabetic, este necesar a se utiliza
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 87
ca ultim argument al funcŃiei, parametrul 0 (zero). În acest fel, funcŃia MATCH va returna coloana 6 din tabelul de consultare.
�������� RReeccuuppeerraarreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee îînn rraappoorrtt ccuu ppoozziiţţiiaa rreellaattiivvăă aa uunnuuii eelleemmeenntt îînnttrr--uunn ttaabblloouu
ddee ccoonnssuullttaarree..
FFuunnccŃŃiiaa IINNDDEEXX
SSiinnttaaxxăă ==IINNDDEEXX((ttaabbeell__ddee__ccoonnssuullttaarree ;; nnuummăărr__lliinniiee ;; nnuummăărr__ccoollooaannăă))
� ttaabbeell__ddee__ccoonnssuullttaarree este un câmp ce conŃine posibilele valori căutate;
� nnuummăărr__lliinniiee specifică un număr de linie de unde să fie extrasă valoarea căutată;
� nnuummăărr__ccoollooaannăă specifică un număr de coloană de unde să fie extrasă valoarea căutată;
RReezzuullttaatt rreettuurrnnaatt
PoziŃia relativă a unui element într-un tablou de consultare, la intersecŃia unei linii cu o coloană.
OObbsseerrvvaaŃŃiiii.. Este de preferat a se utiliza funcŃia IINNDDEEXX, faŃă de funcŃiile de consultare clasice (LOOKUP, VLOOKUP, HLOOKUP) în condiŃiile în care poziŃia relativă a conŃinutului unei celule va fi mai uşor de identificat decât adresa celulei exprimată prin referinŃa la conŃinutul liniilor sau coloanelor. INDEX utilizează de regulă funcŃia MATCH pentru referirea liniei şi coloanei la intersecŃia cărora se regăseşte valoarea căutată. În Figura 101 este ilustrat un exemplu de utilizare a funcŃiei INDEX prin care se recuperează o valoare şi anume codul fiscal al clientului cu codul 104. Această informaŃie se regăseşte la intersecŃia liniei 5 cu coloana 6 din tabloul de consultare. Cele două funcŃii MATCH permit în acest context identificarea numărului de linie şi de coloană, iar funcŃia INDEX extrage valoarea găsită la intersecŃia celor două coordonate ale tabelului de consultare ClienŃi.
Figura 101 Exemplu de utilizare a funcţiilor de consultare MATCH şi INDEX
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Excel 2007 pune la dispoziţia utilizatorului un asistent grafic
(Wizard) care permite identificarea unei valori într-un câmp.
Facilităţile asistentului grafic devin operaţionale prin
instalarea unor componente incluse la cerere, prin
intermediul comenzii Office Button� Excel Options�Add-
Ins, prin activarea butonului de comandă Lookup aferent
ribbon-ului ce corespunde tabului Formulas.
Etapele prin care asistentul grafic (ilustrate în Figura 102) construieşte funcţia de consultare INDEX,
plecând de la exemplul prezentat anterior sunt următoarele:
� Step 1 of 4 selectează tabelul de consultare (B3:G10);
� Step 2 of 4 selectează (din lista derulantă) în partea superioară a casetei de dialog numele (eticheta) coloanei („Cod Fiscal”) ce conţine valoarea de recuperat, iar în partea inferioară selectează (tot din lista derulantă) cheia de consultare (104) după care se doreşte a se identifica valoarea căutată.
Figura 102 Consultare prin intermediul unui asistent grafic � Step 3 of 4 permite ramificarea execuţiei în funcţie de faptul dacă se doreşte numai afişarea
rezultatului consultării (opţiunea: Copy just a formula to a single cell) sau afişarea rezultatului împreună cu elementele aferente (echivalente funcţiei MATCH) identificate pe coloană sau pe linie (opţiunea Copy the formula and lookup parameters).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 89
În varianta alegerii opţiunii Copy just a formula to a single cell, etapa Step 4 of 4 permite alegerea amplasamentului (adresa celulei) în care să se copieze rezultatul consultării şi implicit funcţia INDEX; În varianta alegerii opţiunii Copy the formula and lookup parameters, etapele: Step 4/5/6 of 6 permit specificarea amplasamentelor pentru copierea:
o în celula C25, a numelui câmpului (etichetei) căutat(e) pe coloană (Cod Fiscal):4/6; o în celula B25, a parametrului (etichetei) ce urmează a fi căutat(ă) pe linie (104):5/6; o în celula D25, a rezultatului consultării, adică valoarea codului fiscal ce corespunde clientului
cu codul 104: 6/6. Un alt exemplu de utilizare a funcţiilor INDEX şi MATCH este prezentat în Figura 103. În acest caz,
numărul liniei ce corespunde unui „Cod Client” este localizat prin intermediul controalelor de tip
„listă derulantă”. Etapele realizării unei aplicaţii ce returnează în celula D64 Codul Fiscal al clientului
şi formatează condiţional coloana „Cod Fiscal” (G37:G43) sunt:
se pleacă de la tabelul clienţilor, declarat pe coordonatele B36:G43; se inserează unul din controalele de tip listă derulantă (Combo Box sau List Box) prin comanda Developer > Insert > Form Controls. In caseta de dialog aferentă celor două controale (obţinută prin clic-dreapta-mouse pe controlul generat în foaia de calcul), în tabul Control se desemnează plaja de celule sursă (Input Range) pe care va opera căutarea poziţiei valorilor şi se va preciza adresa celulei ce va returna numărul de linie pe care se identifică valoarea căutată (Cell link = B45). Dacă dintr-una din listele derulante se alege valoarea 105, celula B45 va returna valoarea poziţională a valorii pe plaja de celule sursă (B37:B43) Formula editată în celula D64 returnează valoarea căutată prin funcţia INDEX la intersecţia coordonatelor date numărul de linie (celula B45) şi numărul de coloană returnat de MATCH(„Cod Fiscal”;B36:G36;0);
Coloana G37:G43 a fost formatată condiţional astfel încât să pună în evidenţă valoarea căutată (Codul fiscal).
Figura 103 Exemplu de căutare a unei valori printr-o listă derulantă şi formatarea condiţională a rezultatului
�� IIddeennttiiffiiccaarreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee îînnttrr--uunn ttaabblloouu ddee ccoonnssuullttaarree..
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 90
FFuunnccŃŃiiaa LLOOOOKKUUPP
SSiinnttaaxxăă ==LLOOOOKKUUPP((cchheeiiee__ddee__ccoonnssuullttaarree;;vveeccttoorr__ddee__ccoonnssuullttaarree;; vveeccttoorr__rreezzuullttaatt))
� cchheeiiee__ddee__ccoonnssuullttaarree este o valoare căutată într-un vector. Cheia poate fi o constantă o adresă absolută/relativă sau un nume de câmp;
� vveeccttoorr__ddee__ccoonnssuullttaarree specifică un câmp ce conŃine fie o singură linie, fie o singură coloană. Elementele vectorului de consultare (valori numerice, şiruri de caractere, valori logice) trebuie să fie sortate crescător.
� vveeccttoorr__rreezzuullttaatt specifică un câmp ce conŃine fie o singură linie, fie o singură coloană de unde să se extragă o valoare căutată;
RReezzuullttaatt rreettuurrnnaatt
Identifică valoarea unei chei de consultare într-un vector (o coloană dintr-un tabel de consultare) ce conŃine cheia de consultare, recuperând valoarea corespunzătoare cheii dintr-un vector rezultat.
Figura 104 ilustrează un exemplu în care se doreşte a se recupera dintr-un tabel de consultare (B4:G11), numele clientului şi codul fiscal al acestuia în funcŃie de valoarea codului de client. Astfel, funcŃia LOOKUP editată în celula C16, va identifica valoarea cheii de consultare (Cod Client=104) în coloana vectorului de consultare declarat pe coordonatele B4:B11 şi va recupera valoarea corespunzătoare Numelui de client din coloana vectorului rezultat (C4:C11) pentru valoarea cheii (Cod Client) identificate. În mod asemănător, în celula E16 se editează o formulă prin care funcŃia de consultare LOOKUP va recupera din vectorul rezultat (G4:G11) valoarea codului fiscal, în funcŃie de identificarea cheii de consultare (B16) în vectorul de consultare (B4:B11).
Figura 104 Exemplu de utilizare a funcţiei de consultare LOOKUP OObbsseerrvvaaŃŃiiii..
a. Dacă funcŃia LOOKUP nu găseşte valoarea căutată în vectorul de căutare, aceasta recuperează cea mai mare valoare identificată în vectorul de căutare ce este mai mică sau egală cu valoarea căutată. Altfel spus, în momentul în care se caută o cheie de consultare ce nu este găsită în vectorul de consultare, funcŃia LOOKUP returnează o valoare dintr-un vector rezultat ce corespunde celei mai apropiate valori a vectorului de căutare pentru cheia de consultare. De exemplu, dacă se caută numele clientului pentru care codul de identificare este 103, iar aceasta nu este găsit în vectorul de consultare (declarat pe coordonatele B4:B11 din Figura 104), se recuperează numele
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 91
clientului ce corespunde codului 102, existent în vectorul de consultare (deoarece codul 103 nu există, este recuperat numele clientului Client2, pentru cea mai apropiată valoare mai mică decât codul 102 – care există).
Acest dezavantaj al funcŃiei LOOKUP poate fi înlăturat printr-un test de existenŃă a cheii de consultare în vectorul de căutare. Potrivit acestui demers (ilustrat în Figura 105) se va testa (prin funcŃia condiŃională IF) dacă valoarea căutată (B16) este diferită de aceeaşi valoare identificată în vectorul de căutare şi returnată prin vectorul rezultat (LOOKUP(B16;B4:B11;B4:B11). Dacă valorile sunt diferite (deci nu a fost identificată cheia de consultare în vectorul de căutare), atunci funcŃia IF va returna un mesaj de eroare („Nu există clientul”&B16), altfel, (situaŃie în care cheia a fost identificată) se va returna valoarea corespunzătoare cheii de consultare din vectorul rezultat.
Figura 105 Exemplu de utilizare a funcţiei LOOKUP, cu test de existenţă a cheii de consultare într-un vector de căutare
b. Dacă valoarea căutată este mai mică decât cea mai mică (prima) valoare a vectorului de căutare, funcŃia LOOKUP va returna valoare nedisponibilă (#N/A). Altfel spus, la tastarea unei valori a cheii de consultare, mai mici decât limita inferioară a domeniului vectorului de consultare (Cod Client), funcŃia va returna valoare nedisponibilă.
Acest inconvenient poate fi înlăturat printr-un test de eroare IF(ISERROR(....), sau IF(ISNA(....) ce verifică dacă funcŃia LOOKUP returnează o valoare nedisponibilă, interpretată de Excel ca eroare. În acest mod, se va evita afişarea mesajului #N/A (Not Available), acesta înlocuindu-se cu un spaŃiu (””), un text explicativ sau cu zero3.
Se recomandă ca testul de eroare să fie completat cu un test de verificare a existenŃei cheii de consultare în vectorul de căutare (fapt menŃionat la punctul a). Figura 106 ilustrează printr-o formulă testul de eroare, completat cu testul de existenŃă pentru consultarea unui vector prin funcŃia LOOKUP.
3 În cazul nereturnării unui rezultat valid de natură numerică, se va înlocui valoarea de eroare #N/A cu zero, dacă celula respectivă este implicată în diverse calcule
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 92
Figura 106 Exemplu de utilizare a funcţiei LOOKUP, cu test de eroare �� IIddeennttiiffiiccaarreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee îînnttrr--uunn ttaabblloouu ddee ccoonnssuullttaarree vveerrttiiccaallăă..
FFuunnccŃŃiiaa VVLLOOOOKKUUPP
SSiinnttaaxxăă ==VVLLOOOOKKUUPP((cchheeiiee__ddee__ccoonnssuullttaarree ;; ttaabbeell__ddee__ccoonnssuullttaarree ;; nnuummăărr__ccoollooaannăă__ddee__rreeccuuppeerraatt [[;;vvaallooaarree__llooggiiccăă]]))
� cchheeiiee__ddee__ccoonnssuullttaarree este o valoare după care are loc căutarea sau consultarea într-un tablou. Cheia poate fi o constantă o adresă absolută/relativă sau un nume de câmp;
� ttaabbeell__ddee__ccoonnssuullttaarree este un câmp asupra căruia operează consultarea verticală prin căutarea valorii cheii precizate anterior (observaŃia a);
� nnuummăărr__ccoollooaannăă__ddee__rreeccuuppeerraatt reprezintă numărul coloanei (numerotarea începe cu 1) de unde va fi recuperată informaŃia găsită în tabelul de consultare, corespunzător valorii cheii de căutare;
� [[vvaallooaarree__llooggiiccăă]] reprezintă un argument logic (opŃional) cu valoare: TRUE (implicit) sau FALSE, ce specifică comportamentul funcŃiei VLOOKUP atunci când valoarea cheii de consultare nu va fi identificată în prima coloană a tabelului de căutare (observaŃiile b şi c).
RReezzuullttaatt rreettuurrnnaatt
Conţinutul unei celule ce figurează într-o anumită coloană dintr-un tablou
de consultare verticală, potrivit valorii unei chei de căutare.
OObbsseerrvvaaŃŃiiii..
Este de recomandat pentru versiunile inferioare de Excel (97, 2000) ca tabelul de consultare să conŃină în prima sa coloană elemente (valori numerice, şiruri de caractere sau valori logice) care să fie sortate crescător. Dacă tabelul nu este sortat după prima sa coloană, se va activa comanda Data Sort, iar în rubrica Sort by se va preciza numele coloanei după care se va face sortarea); Valoarea „afirmativă” a argumentului opŃional (valoarea logică TRUE) este implicită şi deci nu este obligatorie editarea sa în funcŃia VLOOKUP. Contextul în care operează valoarea TRUE este
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 93
momentul în care valoarea cheii de consultare nu este identificată în prima coloană a tabelului. În acest caz, funcŃia VLOOKUP identifică cea mai apropiată valoare (imediat mai mică) faŃă de cheia de consultare şi recuperează în consecinŃă o altă informaŃie. Altfel spus, în mod implicit, la tastarea unei chei de consultare inexistente, se localizează cea mai mare valoare identificată în prima coloană a tabelului, ce este mai mică sau egală faŃă de valoarea cheii căutate; Valoarea „de negaŃie” a argumentului opŃional (valoarea logică FALSE) în sintaxa funcŃiei VLOOKUP declanşează afişarea mesajului de eroare #N/A (Not Available) în cazul în care cheia de consultare nu este găsită în prima coloană a tabelului de consultare; La tastarea unei valori a cheii de consultare, mai mici decât limita inferioară a domeniului primei coloane din tabelul de consultare, funcŃia VLOOKUP va returna valoare nedisponibilă (#N/A). În Figura 107 sunt ilustrate mai multe exemple de utilizare ale funcŃiei VLOOKUP, prin care se doreşte ca la tastarea unui cod de identificare, existent într-un tablou să se recupereze automat anumite elemente cu care respectivul cod se găseşte în dependenŃă funcŃională.
Figura 107 Exemplu de utilizare a funcţiei de consultare VLOOKUP Tabelul de consultare din Figura 107 a fost definit pe coordonatele B5:D14 şi a fost denumit „Tabel” pentru o identificare mai uşoară. Tabelul conŃine trei rubrici, intitulate: „Marca”, „Nume şi Prenume” şi „Salariul de încadrare” şi este sortat după prima coloană (rubrica „Marca”).
În „Cazul 1” de utilizare, funcŃia de consultare verticală VLOOKUP se editează în celula H5 (în celula H4 este prezentat rezultatul funcŃiei) şi are ca semnificaŃie căutarea valorii cheii de consultare (G4 are valoarea 10) în tabelul de consultare (Tabel) şi recuperarea coloanei 2 (aferente rubricii „Nume şi Prenume”) corespunzător valorii cheii de consultare. În mod asemănător, în celula I5 se procedează la recuperarea din coloana 3 a tabelului („Salariul de încadrare”), a elementului dependent funcŃional de cheia de consultare. Cheia de consultare G4 a fost declarată ca adresă absolută pe coloană ($G4) deoarece s-a dorit ca la copierea formulei din H5 în I5, să nu se decaleze (la dreapta) adresa cheii de consultare. Odată copiată formula de consultare în I4, aceasta va fi editată numai pentru schimbarea numărului de coloană din 2 în 3. În „Cazul 2 a” ilustrat în Figura 107, funcŃia VLOOKUP utilizată fără argumentul opŃional FALSE nu va semnala inexistenŃa unei chei de consultare în tabelul de consultare. Astfel, la tastarea codului cu valoarea 30, funcŃia nu va identifica în prima coloană a tabelului valoarea căutată şi va returna elementul din coloana „Nume şi Prenume (sau „Salariul de încadrare”) ce corespunde cheii cu valoarea cea mai apropiată, imediat mai mică decât valoarea căutată (va returna elementele „Vasiliu Marcela” şi „3.000” ce corespund codului cu valoarea 16).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 94
„Cazul 2 b” prezintă o primă posibilitate de înlăturare a acestui inconvenient, şi anume, utilizarea în funcŃia VLOOKUP a argumentului opŃional FALSE. Acest argument face ca la neidentificarea valorii cheii de consultare în prima coloană a tabelului, funcŃia să returneze valoarea de nedisponibil #N/A. Această valoare returnată este de tip text şi în consecinŃă va propaga eroarea dacă există alte formule (ce conŃin îndeosebi calcule) care fac referinŃă la rezultatul returnat de VLOOKUP în acest caz. „Cazul 3” prezintă în Figura 107, posibilitatea testării existenŃei cheii de consultare în prima coloană a tabelului. Astfel, funcŃia VLOOKUP poate fi completată un test de eroare If(IsError(....) sau If(IsNA(....), ce verifică returnarea de către VLOOKUP a unei valori de non-disponibil (interpretată de Excel) ca eroare. Astfel, dacă nu se identifică valoarea cheii de consultare în prima coloană a tabelului, testul If(IsError(Vlookup(....;FFaallssee)) va înlocui mesajul #N/A (generat automat în prezenŃa argumentului opŃional FALSE al funcŃiei VLOOKUP), cu un spaŃiu (””), un text sau cu valoarea de zero (pentru elemente numerice). În mod contrar (dacă valoarea cheii a fost identificată), se va executa consultarea verticală, ce va returna o valoare validă.
Figura 108 ilustrează un alt exemplu de utilizare a funcŃiei VLOOKUP, prin care se doreşte a se recupera cursul valutar al monedei Euro în funcŃie de o dată calendaristică tastată de utilizator (şi declarată în acest caz în celulele E8, E9 drept cheie de consultare). Exemplul prezentat are următoarele particularităţi:
Cheia de consultare editată în celula E8 (iar apoi în E9) este de tip dată calendaristică (şi deci este de sorginte numerică) şi reprezintă prin funcŃia TODAY() data curentă a sistemului. Potrivit valorii cheii de consultare (E8=data de 18.01009), funcŃia VLOOKUP editată în celula F8, returnează din coloana a 2-a (Curs Euro) a tabelului de consultare (declarat pe coordonatele B7:C80 şi intitulat „CotaŃii”) valoarea de 4,1335. În cazul prezentat, funcŃia VLOOKUP nu are nevoie nici de parametrul opŃional FALSE (pentru a semnala prin #N/A o valoare neidentificată a cheii), iar în consecinŃă, nici de un test de existenŃă a cheii în prima coloană a tabelului „CotaŃii”. ExplicaŃia acestui demers constă în faptul că în zilele de weekend BNR nu cotează principalele valute şi deci, din punct de vedere informatic, dacă o dată calendaristică căutată nu este identificată (de exemplu data de 18 ianuarie 2009 - duminică) se va recupera cursul ce corespunde celei mai mari valori, imediat mai mici decât valoarea căutată (corespunzătoare datei de vineri 16 ianuarie 2009 – dată ce există în tabelul „CotaŃii”.).
Figura 108 Exemplu practic de utilizare a funcţiei VLOOKUP. Formatare condiţională Tabelul de consultare „CotaŃii” cuprinde două coloane: „Data cotaŃiei BNR” şi „Curs Euro”. Completarea datei cotaŃiei (fără a include weekend-urile) presupune editarea în celula B8 (ce a fost formatată personalizat şi cu ziua din săptămână) a unei date calendaristice (12 ianuarie 2009). Apoi, în celula B9 se va edita o formulă ce conŃine funcŃia WORDAY ce conŃine ca argumente: data
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 95
calendaristică anterioară – considerată ca bază şi numărul corespunzător zilei de cotaŃie (ce se va prelua din coloana stângă). Formula astfel editată va fi copiată pe amplasamentul definit de câmpul B10:B80).
� Formatarea condiţională a celulelor E9 (data calendaristică ce urmează a fi identificată) şi F9 (valoarea cursului Euro recuperat din coloana a 2-a a tabelului de consultare) presupune construirea a două formule de consultare verticală, ce vor fi implementate în caseta de dialog Conditional Formatting (Home�Conditional Formatting�New Rule�Use a formula...). Formulele de modificare a formatului implicit pot fi editate la nivelul oricărei linii a tabelului de consultare, iar apoi formatul poate fi copiat (fie prin butonul Format Painter, fie prin succesiunea de comenzi Copy, Paste Special�Formats). Astfel, celula B13 conţine o formatare condiţională prin care se compară valoarea ei (19 ianuarie 2009) cu rezultatul returnat de funcţia VLOOKUP ce caută să identifice valoarea editată în celula E10 şi căutată în coloana 1 a tabelului „Cotaţii”. Dacă cele două valori coincid, atunci se va aplica un format personalizat ales de utilizator prin caseta de dialog aferentă formatării condiţionale. În mod analog, în celula C13 se procedează la formatarea condiţională a liniilor aferente coloanei „Curs Euro”, prin compararea cursului unei zile (C13=4,1390) cu valoarea corespunzătoare extrasă din tabel, potrivit cheii de căutare cu valoarea 19 ianuarie 2009.
Un alt exemplu de utilizare a funcţiei de consultare verticală VLOOKUP ilustrează în Figura 109
calcularea valorii fiecărui produs facturat prin extragerea dintr-un tabel de consultare intitulat
„Nomenclator” şi declarat pe coordonatele E22:K22 a tuturor elementelor ce compun calculul valorii
facturii, în funcţie de cantitatea livrată conform unei politici de acordare a reducerilor comerciale.
Aceste discounturi sunt acordate numai în condiţiile în care cantitatea livrată depăşeşte un anumit
barem (specificat într-un tabel de consultare) şi sunt operaţionale numai pentru cantităţile
comandate în plus faţă de acel barem. Astfel, în tabelul A8:C12 se regăsesc următoarele categorii
informaţionale:
„Cod produs” – cheia de consultare, în funcţie de valorile căreia se recuperează „Valoare
factură”;
„Cantitate livrată” – parametru în funcţie de care se acordă reducerile comerciale, după următorul algoritm: „în cazul în care cantitatea livrată depăşeşte baremul cantităţii de la
care se acordă discount, (existent în tabelul de consultare), se aplică un preţ diminuat cu un
anumit procent, altfel, se aplică preţul din tabel” „Valoare factură” – se calculează în funcţie de elementele din tabelul „Nomenclator”, numai
în condiţiile în care data curentă (B3) se încadrează în intervalul de timp dintre „Data debut
promoţie” şi „Dată sfârşit promoţie” din tabelul de consultare.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 96
Figura 109 Exemplu de utilizare a funcţiei VLOOKUP. Calculul reducerilor comerciale.
Esenţa calculului valorii din exemplul prezentat este dat de faptul că toate elementele ce compun
valoarea se regăsesc în tabelul „Nomenclator”, calculul făcându-se doar dacă se respectă perioada
de promoţie, iar în cazul în care „Cantitatea livrată” depăşeşte limita cantităţii de la care se acordă
discount, valoarea se calculează cu un preţ diminuat cu procentul de discount, la care se adaugă
preţul standard multiplicat cu cantitatea până la care se acordă reduceri comerciale.
�� IIddeennttiiffiiccaarreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee îînnttrr--uunn ttaabblloouu ddee ccoonnssuullttaarree oorriizzoonnttaallăă..
FFuunnccŃŃiiaa HHLLOOOOKKUUPP
SSiinnttaaxxăă ==HHLLOOOOKKUUPP((cchheeiiee__ddee__ccoonnssuullttaarree ;; ttaabbeell__ddee__ccoonnssuullttaarree ;; nnuummăărr__lliinniiee__ddee__rreeccuuppeerraatt [[;;vvaallooaarree__llooggiiccăă]]))
� cchheeiiee__ddee__ccoonnssuullttaarree este o valoare (o constantă, o adresă absolută/relativă sau un nume de câmp) după care are loc căutarea în prima linie a unui tablou de consultare orizontală;
� ttaabbeell__ddee__ccoonnssuullttaarree este un câmp asupra căruia operează consultarea orizontală prin căutarea valorii cheii precizate anterior (observaŃia b);
� nnuummăărr__lliinniiee__ddee__rreeccuuppeerraatt reprezintă numărul implicit al liniei (prima va începe cu 1) de unde va fi recuperată informaŃia găsită în tabelul de consultare, corespunzător valorii cheii de căutare (observaŃia b);
� [[vvaallooaarree__llooggiiccăă]] reprezintă un argument logic (opŃional) cu valoare: TRUE (implicit) sau FALSE, ce specifică comportamentul funcŃiei HLOOKUP atunci când valoarea cheii de consultare nu va fi identificată în prima linie a tabelului de căutare (observaŃiile c şi d).
RReezzuullttaatt rreettuurrnnaatt
Conţinutul unei celule ce corespunde unei linii, dintr-un tablou de
consultare orizontală, potrivit valorii unei chei de consultare.
OObbsseerrvvaaŃŃiiii..
a) Tabelul de consultare orizontală este o transpunere (din orientarea pe coloane în orientarea pe linii) a unui tabel de consultare verticală. În prima linie a sa vor fi căutate valorile unei chei de consultare;
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 97
Este de recomandat ca tabelul de consultare să conŃină în prima sa linie elemente (valori numerice, şiruri de caractere sau valori logice) care să fie sortate crescător (de la stânga la dreapta). Dacă valorile cheii nu sunt sortate, se va selecta tabelul de consultare şi se va activa comanda de sortare: Data ���� Sort. Din caseta de dialog Sort se activează butonul Option, iar din rubrica Orientation se alege opŃiunea Sort left to right; Valoarea „afirmativă” a argumentului opŃional (valoarea logică TRUE) este implicită şi deci nu este obligatorie editarea sa în funcŃia HLOOKUP. Contextul în care operează valoarea TRUE este momentul în care valoarea cheii de consultare nu este identificată în prima linie a tabelului. În acest caz, funcŃia HLOOKUP localizează cea mai apropiată valoare (imediat mai mică) faŃă de cheia de consultare şi recuperează în consecinŃă o altă informaŃie; Valoarea „de negaŃie” a argumentului opŃional (valoarea logică FALSE) în sintaxa funcŃiei HLOOKUP declanşează afişarea mesajului de eroare #N/A (Not Available) în cazul în care cheia de consultare nu este găsită în prima linie a tabelului de consultare. Tot în condiŃiile precizării argumentului FALSE, la tastarea unei valori a cheii de consultare, mai mici decât limita inferioară a domeniului primei linii din tabelul de consultare, funcŃia HLOOKUP va returna tot valoare nedisponibilă (#N/A). Dacă argumentul FALSE este precizat în mod explicit, tabelul de consultare nu mai trebuie sortat după valorile crescătoare din prima linie; În Figura 110 sunt ilustrate mai multe cazuri de utilizare ale funcţiei HLOOKUP, prin care se doreşte
ca la tastarea unui identificator (sub forma unui „Cod Produs”), existent într-un tablou să se
recupereze automat anumite elemente cu care respectivul cod se găseşte în dependenţă
funcţională.
Figura 110 Exemplu de utilizare a funcţiei de consultare HLOOKUP. Aplicaţia 1 În „Cazul 1” de utilizare, funcŃia de consultare verticală HLOOKUP se editează în celula B11 şi are ca semnificaŃie căutarea valorii cheii de consultare „Cod Produs” (B10 = 102) în tabelul de consultare (B4:J6 = „Tabel_consultare_orizontala”) şi recuperarea numărului de linie ce corespunde denumirii de produs (linia 2). Astfel, dacă valoarea cheii de consultare (102) este identificată în tabloul „Tabel_consultare_orizontala”, se va recupera de pe a 2-a linie denumirea produsului ce corespunde produsului cu codul 102. În „Cazul 2” se observă că la tastarea unui număr de cod eronat de produs (B15 = 107), inexistent în tabelul de consultare, funcŃia HLOOKUP nu poate identifica în prima linie valoarea căutată, returnând denumirea produsului („ffffff”) şi preŃul (57,5) celui mai apropiat cod de produs identificat (105). „Cazul 3” ilustrează faptul că funcŃia HLOOKUP returnează o valoare de eroare (#N/A) în momentul în care se doreşte recuperarea unor elemente dependente funcŃional de un cod de identificare inexistent în prima linie a unui tabel de consultare orizontală. Pentru a semnala
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 98
inexistenŃa cheii de consultare, sintaxa funcŃiei HLOOKUP a fost completată cu al patrulea argument poziŃionat pe valoarea logică de FALSE. În „Cazul 4” funcŃia HLOOKUP a fost completată cu un test de existenŃă ce detectează o valoare nedisponibilă (după acelaşi raŃionament ca şi la VLOOKUP) în cazul în care valoarea cheii de consultare (B25=107) nu se regăseşte în prima linie a tabloului de consultare orizontală. Acest demers are avantajul că acoperă şi toate celelalte inconveniente semnalate în cazurile precedente de utilizare.
Exemplul prezentat în cele ce urmează şi ilustrat în Figura 111 calculează prin consultări verticale şi
orizontale în tabelul A16:D20 „Valoarea de inventar”, „Coeficientul de reevaluare” şi „Valoarea
reevaluată” a mijloacelor fixe puse în funcţiune în perioda 1995 – 2008.
Exemplul prezentat porneşte de la un tabel (declarat pe coordonatele B2:O11 şi intitulat „Indici”) ce cuprinde anumiŃi coeficienŃi (subunitari) de reevaluare a mijloacelor fixe, pe fiecare an în parte (prima linie a tabelului B2:O2 conŃine anii: 1995 – 2008) corespunzător fiecărei categorii (descrise pe coloana A3:A11). Categoriile mijloacelor fixe corespund rândurilor (liniilor), identificate în tabel de la 1 la 9. Tabelul de consultare Q2:U15 (intitulat „Imobilizări”) conŃine elemente de identificare pentru fiecare mijloc fix („Denumire mijloc fix”, „Data punerii în funcŃiune”, „Valoare de inventar”), dependente funcŃional de un cod de identificare („Număr de ordine”).
AplicaŃia extrage din tabelul de consultare „Indici” în celulele câmpului C17:C20 coeficientul de reevaluare, în funcŃie de valorile codului compozit „Număr de inventar” (ce este format din categoria mijlocului fix şi „Numărul de ordine”. FuncŃia HLOOKUP editată în celula C19 identifică anul punerii în funcŃiune în prima linie a tabelului „Indici”, extrăgând coeficientul de reevaluare ce corespunde categoriei mijlocului fix. Anul punerii în funcŃiune este extras cu funcŃia YEAR din ultimele patru caractere VALUE((RIGHT(A19,4))) ale numărului de inventar, localizat prin funcŃia VLOOKUP în tabelul „Imobilizări”, coloana a 3-a („Data punerii în funcŃiune”).
Categoria mijlocului fix (al treilea argument al funcŃiei HLOOKUP) este în fapt primul caracter din stânga al numărului de inventar (VALUE(LEFT(A19)+1). Decalarea cu o unitate a rândului ce conŃine categoria este explicată prin faptul că prima linie este rezervată anilor, a 2-a linie corespunde primei categorii, etc. Datorită faptului că procedura de consultare orizontală este operaŃională începând cu linia 1 (ce conŃine valorile cheilor de consultare sau numele câmpurilor tabelului), agregatul compozit „Număr Inventar” din care este extras primul caracter va indica numărul liniei de recuperat şi va avea valoarea incrementată cu o unitate pentru a exista o concordanŃă între valorile luate de aceasta şi numărul liniei de recuperat. Dacă nu s-ar fi operat acest artificiu, numărul liniei de recuperat ar fi fost decalat în minus cu o unitate şi deci, în condiŃiile în care formula VALUE(LEFT(A19) are valoarea 7, funcŃia HLOOKUP ar fi recuperat coeficientul de reevaluare corespunzător liniei 7 din tabel, adică coeficientul de reevaluare corespunzător anului 2007, şi al categoriei a 6-a de mijloace fixe (deoarece prima linie a tabelului conŃine numele câmpurilor).
Valoarea de inventar este extrasă în celula B18, prin funcŃia VLOOKUP din ultimele patru caractere VALUE((RIGHT(A18,4))) ale numărului de inventar, localizat tabelul „Imobilizări”, coloana a 4-a („Valoare de inventar”).
Valoarea reevaluată, calculată în celula D20 înmulŃeşte valoarea de inventar cu coeficientul de reevaluare, dacă anul de punere în funcŃiune (extras din tabelul „Imobilizări”) este anul curent, altfel se menŃine aceeaşi valoare de inventar.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 99
Figura 111 Exemplu de utilizare a funcţiei HLOOKUP. Aplicaţia 2 Un alt exemplu prezintă în Figura 112 procedura de consultare a unui tabel, prin precizarea
elementelor informaţionale generice de identificare a clienţilor (pe prima linie) şi produselor (pe
prima coloană), dimensiuni la intersecţia cărora se regăsesc cantităţile vândute (în bucăţi).
Aceste elemente „cheie” se regăsesc precizate în celulele B16 şi D16 sub formă de liste derulante
declarate prin comanda aferentă tabului Data > butonul Data Validation > opţiunea Data
Validation > tabul Settings > secţiunea Allow > opţiunea List. Celula B19 conţine funcţia de
consultare orizontală HLOOKUP, ce caută să identifice Clientul (B16) în prima linie a tabelului de
consultare Cantitati (declarat pe coordonatele B3:F13). Numărul liniei de recuperat din funcţia
HLOOKUP este returnat de identificarea poziţiei relative prin funcţia MATCH, a denumirii de
produs (D16) în vectorul de consultare declarat pe coordonatele A3:A13.
Figura 112 Exemplu de utilizare a funcţiei HLOOKUP. Procedură de consultare mixtă. Un exemplu asemănător, ilustrat în Figura 113 extrage în celula F3, prin funcŃia HLOOKUP se recuperează salariul de încadrare declarat într-un tabel de consultare, în funcŃie de precizarea vechimii şi a departamentului funcŃional. Astfel, funcŃia HLOOKUP caută să localizeze vechimea (E3, cheia de consultare) în prima linie a tabelului declarat pe coordonatele B26:G31. Această primă linie conŃine valori numerice formatate personalizat cu sufixul „ani”. Argumentul „numărul liniei de recuperat” este extras prin funcŃia MATCH, care returnează valoarea poziŃională a departamentului (C3) în
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 100
vectorul de consultare A26:A31. În acest caz, nu mai este necesar un test de existenŃă a cheii de consultare în prima linie a tabelului de consultare, deoarece, dacă valoarea căutată nu este identificată se returnează salariul de încadrare pentru cea mai apropiată vechime, imediat mai mică decât vechimea căutată.
Figura 113 Exemplu de utilizare a funcţiei HLOOKUP. Extragerea salariului de încadrare în funcţie de vechime şi departament
Un ultim exemplu ilustrat în Figura 114 calculează recapitulativ prin trei funcŃii de consultare cheltuielile de transport în lei, prin extragere dintr-un tabel de consultare, în funcŃie de destinaŃie şi de cantitatea livrată. O primă abordare de recuperare şi calcul a cheltuielilor de transport în celula C7, identifică prin HLOOKUP destinaŃia (conŃinutul celulei C4) în prima linie a tabelului de consultare „Tarife”, corespunzător numărului de linie returnat indirect ca valoare poziŃională de funcŃia MATCH. Această abordare este operaŃională în condiŃiile unui test de existenŃă (IF(ISERROR(......)) a destinaŃiei în tabelul de consultare şi a unor cantităŃi valide pentru care există tarife de transport. A doua abordare (din celula C8) constă în localizarea cantităŃii livrate (B8) într-un tabel de consultare extins (şi declarat pe coordonatele A12:F33), corespunzător unui număr de coloană ce corespunde destinaŃiei transportului. Acest ultim argument este specificat indirect sub forma unei valori poziŃionale date prin localizarea cu ajutorul funcŃiei MATCH a destinaŃiei. Ultima abordare (din celula C9) utilizează pentru extragerea tarifului de transport funcŃia INDEX care operează pe coordonatele tabelului de consultare A12:F33. Numărul de linie este returnat de funcŃia MATCH, prin furnizarea valorii poziŃionale a cantităŃii livrate (B9) în câmpul „Cantitati_transportate” A12:A33. Numărul de coloană este specificat indirect sub forma unei valori poziŃionale date prin localizarea cu ajutorul funcŃiei MATCH a destinaŃiei în tabelul de consultare A12:F12.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 101
Figura 114 Exemplu recapitulativ de utilizare a funcţiilor HLOOKUP, VLOOKUP şi INDEX. Calcularea cheltuielilor de transport
FFuunnccţţiiiillee tteexxtt ssaauu şşiirr ddee ccaarraacctteerree ((TTeexxtt))
Funcţiile text permit diferite operaţii cu şiruri de caractere (conversii, concatenări, trunchieri,
căutare şi înlocuire, comparare) şi furnizează în egală măsură informaţii legate de textul existent
(număr de caractere, poziţia caracterelor în cadrul unui şir) în celule.
Potrivit facilităţilor abordate din punct de vedere funcţional, se pot deosebi următoarele tipuri de
funcţii:
� funcţii text de conversie;
� funcţii text de ameliorare a prezentării;
� funcţii text de căutare, înlocuire şi măsurare a lungimii caracterelor.
2.8 Funcţii financiare
Funcţiile financiare efectuează o serie de calcule economico-financiare furnizând prin valorile
returnate informaţii utile referitoare la amortismente, la rentabilitatea investiţiilor, plasamentelor,
împrumuturilor, etc. Potrivit sferei de acţiune, a modului în care se calculează anuităţile, funcţiile
financiare se împart în funcţii pe bază de anuităţi şi funcţii economice de amortizare.
FFuunnccţţiiii ppee bbaazzăă ddee aannuuiittăăţţii
Sunt considerate funcţii pe bază de anuităţi, acele funcţii financiare care operează cu sume investite
sau depozitate la termen la bancă, pentru care toate plăţile sunt egale şi sunt efectuate la intervale
regulate.
PV, FV, PMT, RATE, NPER, NPV.
�� CCaallccuullaarreeaa vvaalloorriiii ccuurreennttee ppeennttrruu oo sseerriiee ddee ppllăăţţii vviiiittooaarree..
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 102
FFuunnccŃŃiiaa PPVV
SSiinnttaaxxăă ==PPVV((rraattaa__ddoobbâânnzziiii ;; nnuummăărr__ddee__ppeerriiooaaddee ;; mmăărriimmeeaa__ppllăăŃŃiiii [[;;vvaallooaarree__vviiiittooaarree ;; ttiipp]]))
� rraattaa__ddoobbâânnzziiii reprezintă procentul de dobândă perceput pentru o anumită perioadă;
� nnuummăărr__ddee__ppeerriiooaaddee reprezintă numărul total de plăŃi periodice;
� mmăărriimmeeaa__ppllăăŃŃiiii semnifică valoarea plăŃii făcute în fiecare perioadă;
� [[vvaallooaarree__vviiiittooaarree]] reprezintă suma totală care se doreşte a fi realizată după ultima plată (valoarea viitoare la care se ajunge după efectuarea ultimei plăŃi);
� [[ttiipp]] este un parametru care semnifică faptul că plata se face la începutul perioadei (valoarea 1) sau la sfârşitul perioadei (valoarea 0 – implicită).
RReezzuullttaatt rreettuurrnnaatt
Valoarea actuală (Present Value) aferentă unei sume investite sau
depozitate la bancă, prin plăţi periodice, în condiţiile unei rate constante
a dobânzii.
OObbsseerrvvaaţţiiii..
FuncŃia financiară PV calculează valoarea prezentă a unei sume investite, adică valoarea curentă a unei serii de plăŃi viitoare. FuncŃia se utilizează pentru a se determina dacă valoarea de revenire a unei anumite investiŃii este favorabilă sau nu, Ńinând cont de costul iniŃial al investiŃiei.
aa.. Dacă din sintaxa funcţiei sunt omise ultimele două argumente, acestea vor fi considerate ca având valori nule.
Argumentele rraattaa__ddoobbâânnzziiii şi nnuummăărruull__ddee__ppeerriiooaaddee trebuie exprimate în aceeaşi unitate de timp –lună sau an). De exemplu, dacă dobânda este exprimată în ani (majoritatea dobânzilor sunt anuale), iar plăŃile/încasările se vor face lunar, va trebui să se împartă rata_dobânzii (anuală) la 12 (luni) – pentru exprimarea acesteia în luni, iar termenele anuale să se înmulŃească la 12 (luni), pentru a se echivala rata şi termenele. În principiu funcŃiile financiare care operează cu anuităŃi şi calculează valori investite sau remunerate (PV, PMT, FV) Ńin cont natura şi sensul acestor valori, atribuindu-le semnul algebric + sau – în funcŃie de faptul dacă valoarea respectivă semnifică o cheltuială sau un venit. De exemplu, dacă argumentele mmăărriimmeeaa__ppllăăŃŃiiii, vvaallooaarree__pprreezzeennttăă sau vvaallooaarree__vviiiittooaarree sunt exprimate prin numere negative, acestea reprezintă o cheltuială (o ieşire de fonduri financiare), iar funcŃia care le utilizează returnează un rezultat pozitiv (un venit în urma unei investiŃii sau plasament). Invers, dacă argumentele mmăărriimmeeaa__ppllăăŃŃiiii, vvaallooaarree__pprreezzeennttăă sau vvaallooaarree__vviiiittooaarree sunt exprimate prin numere pozitive, acestea semnifică o încasare (o intrare de fonduri financiare), iar funcŃia care le utilizează va returna un rezultat negativ (o obligaŃie, o datorie de onorat ). Altfel spus, aceste funcŃii calculează implicit o plată negativă pentru un volum pozitiv sau un volum negativ pentru o plată negativă. Pentru exemplificarea funcŃiei financiare PV, furnizăm în următoarea aplicaŃie: O persoană fizică doreşte încheierea unei poliŃe de asigurare pe o perioadă de 20 de ani, cu o rată anuală a dobânzii de 12%, urmând să plătească lunar o primă de asigurare de 250 lei. Se doreşte a se calcula valoarea prezentă a anuităŃii. În Figura 115 s-a calculat valoarea prezentă a sumei investite cu ajutorul funcŃiei PV.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 103
Figura 115 Exemplu de utilizare a funcţiei PV Din calculul făcut (în celula E76), reiese că valoarea prezentă a anuităţii este de 22.705 lei. Se
observă că rata dobânzii a fost exprimată în luni (rata anuală a fost împărţită la 12), iar numărul de
ani pentru care s-a contractat asigurarea a fost exprimat tot în luni (numărul de ani a fost înmulţit cu
12).
De asemenea, se mai observă că funcţia PV a returnat un număr pozitiv datorită argumentului
„mărimea plăţii”, care a fost declarat cu semn negativ, deoarece reprezină o ieşire lunară de
numerar pentru investitor. Explicaţia semnului rezultatului este legată de faptul că funcţia PV
semnifică o un venit, o intrare de bani viitoare.
�� CCaallccuullaarreeaa vvaalloorriiii vviiiittooaarree ppeennttrruu oo sseerriiee ddee ppllăăţţii pprreezzeennttee..
FFuunnccŃŃiiaa FFVV
SSiinnttaaxxăă ==FFVV((rraattaa__ddoobbâânnzziiii ;; nnuummăărr__ddee__ppeerriiooaaddee ;; mmăărriimmeeaa__ppllăăŃŃiiii [[;;vvaallooaarree__pprreezzeennttăă ;; ttiipp]]))
� ……aceleaşi argumente;
� [[vvaallooaarree__pprreezzeennttăă]] reprezintă valoarea prezentă (actuală) a investiŃiei sau a plasamentului.
RReezzuullttaatt rreettuurrnnaatt
Valoarea viitoare (Future Value) a unei investiţii sau plasament în condiţii
de anuitate (plăţi şi rate ale dobânzii constante).
Pentru exemplificarea funcţiei financiare FV, oferim următoarea aplicaţie ilustrată în Figura 116: o
persoană fizică doreşte efectuarea unui plasament de 5.000 lei pe o perioadă de 9 luni la o bancă
comercială, pentru o dobândă anuală de 12%. Persoana fizică urmează a depune lunar la bancă,
alături de depozitul iniţial câte 300 lei pe aceeaşi perioadă. Valoarea viitoare a sumei depuse la
bancă de respectiva persoană fizică s-a calculat în celula E8, fiind de 879 lei.
Figura 116 Exemplu de utilizare a funcţiei FV Se observă că argumentele mmăărriimmeeaa__ppllăăţţiiii şi vvaallooaarree__pprreezzeennttăă, au fost introduse cu semn negativ
deoarece reprezintă o plată a investitorului către unitatea bancară depozitară (acesta depune la
bancă suma iniţială de 5.000 lei şi plăteşte lunar încă 300 lei). Rezultatul funcţiei este pozitiv,
deoarece investitorul primeşte la scadenţă o sumă de bani (egală cu suma investită, plus dobânda)
ce reprezintă o intrare de numerar în contul propriu. Dacă acest calcul ar fi fost făcut pentru unitatea
bancară depozitară, argumentele mmăărriimmeeaa__ppllăăţţiiii şi vvaallooaarree__pprreezzeennttăă, ar fi fost introduse cu semn
pozitiv (ce ar reprezenta o încasare iniţială şi lunară), iar rezultatul funcţiei ar fi fost negativ (la
scadenţă se restituie investitorului suma plasată, plus dobânda).
�� CCaallccuullaarreeaa mmăărriimmiiii ppllăăţţiiii lluunnaarree//aannuuaallee..
FFuunnccŃŃiiaa PPMMTT
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 104
SSiinnttaaxxăă ==PPMMTT((rraattaa__ddoobbâânnzziiii ;; nnuummăărr__ddee__ppeerriiooaaddee ;; vvaallooaarree__pprreezzeennttăă [[;;vvaallooaarree__vviiiittooaarree;;ttiipp]]))
� ……aceleaşi argumente. RReezzuullttaatt rreettuurrnnaatt
Mărimea lunară sau anuală a plăţii (PayeMenT) pentru o investiţie sau un
împrumut.
Figura 117 ilustrează funcŃia PMT printr-un exemplu în care se presupune acordarea unui împrumut de la o bancă comercială către o persoană fizică pentru achiziŃionarea unui autoturism în valoare de 76.500 lei. Rata dobânzii practicată de bancă este de 12% pe an, iar durata împrumutului a fost stabilită la 5 ani. Mărimea lunară a plăŃii către bancă pentru creditul acordat a fost calculată prin funcŃia PMT în celula E14.
Figura 117 Exemplu de utilizare a funcţiei PMT De asemenea în exemplul prezentat s-a calculat şi costul total al împrumutului ca un produs între mărimea lunară a plăŃii şi numărul de perioade de plată, exprimat în luni. Valoarea totală a dobânzii s-a calculat ca diferenŃă între costul total al împrumutului şi suma împrumutată.
�� CCaallccuullaarreeaa rraatteeii ddoobbâânnzziiii lluunnaarree//aannuuaallee..
FFuunnccŃŃiiaa RRAATTEE
SSiinnttaaxxăă ==RRAATTEE((nnuummăărr__ddee__ppeerriiooaaddee ;; mmăărriimmeeaa__ppllăăŃŃiiii ;; vvaallooaarree__pprreezzeennttăă))
� ……aceleaşi argumente. RReezzuullttaatt rreettuurrnnaatt
Rata dobânzii pe perioada unei anuităţi, pentru un împrumut sau o
investiţie.
Pentru exemplificarea funcŃiei RATE presupunem efectuarea unui împrumut printr-un credit de 11.000 lei pe timp de un an, cu o valoare lunară de rambursat (mărimea plăŃii) în sumă de 1.050 lei.
Figura 118 Exemplu de utilizare a funcţiei RATE În Figura 118 se calculează în celula E23, dobânda lunară percepută de bancă, iar în celula E22
dobânda anuală pentru suma împrumutată.
�� CCaallccuullaarreeaa nnuummăărruulluuii ddee ppeerriiooaaddee ddee rraammbbuurrssaarree..
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 105
FFuunnccŃŃiiaa NNPPEERR
SSiinnttaaxxăă ==NNPPEERR((rraattaa__ddoobbâânnzziiii ;; mmăărriimmeeaa__ppllăăŃŃiiii ;; vvaallooaarree__pprreezzeennttăă))
� ……aceleaşi argumente. RReezzuullttaatt rreettuurrnnaatt
Numărul de perioade de plată pentru o investiţie sau un plasament. Altfel
spus, se calculează câte vărsăminte sunt necesare pentru ca un capital
constituit printr-o investiţie şi remunerat printr-o dobândă să atingă o
valoare specificată.
În exemplul ilustrat în Figura 119 s-a calculat numărul de perioade în ani în care un întreprinzător
trebuie să restituie un împrumut de 11.000 lei, cu o dobândă anuală de 15%, plătind lunar 1.050 lei.
Figura 119 Exemplu de utilizare a funcţiei NPER Un exemplu recapitulativ este prezentat în Figura 120 de la pagina 106: Esenţa aplicaţiei constă în
întocmirea unui tablou de rambursare a unui împrumut contractat pe 6 luni, calculându-se pentru
fiecare lună, “Restul de rambursat”, “Dobânda lunară” şi “Rata lunară de plată”.
Plecând de la valoarea lunară a plăţii, exemplificată şi calculată anterior cu funcţia PMT (celula F35),
utilizatorul îşi poate organiza pe 5 coloane un tablou de rambursare a împrumutului, astfel:
- prima coloană (câmpul B41:B46) - ”Număr luni rămase de plată”) conţine numărul de luni (de la 6 la 1) pentru care se face calculul restului de rambursat, a dobânzii şi ratei de plată..
- a doua coloană (câmpul C41:C46 – “Rest de rambursat”) conţine suma care rămâne de restituit băncii la sfârşitul fiecărei luni. Pentru prima lună, restul de rambursat este egal cu suma împrumutată, adică 10.000 lei, iar pentru lunile următoare, se calculează ca diferenţă între restul de rambursat din luna precedentă şi rata plătită (rata anterioară);
- a treia coloană (câmpul D41:D46 – “Dobânda lunară”) conţine dobânda lunară calculată, fie aplicând procentul dobânzii lunare la restul de rambursat, fie prin funcţia RATE. Mărimea dobânzii lunare este în descreştere de la o lună la alta, pe măsura rambursării împrumutului;
- a patra coloană (câmpul E41:E46 – “Rata de plată”) reprezintă „Rata lunară de plată”, calculată ca diferenţă între valoarea lunară a plăţii sau „Valoarea lunară de rambursat” (celula F35) şi dobânda lunară. Rata de plată (lunară) reprezintă anuitatea împrumutului fără a se considera dobânda. Suma ratelor de plată pentru cele 6 luni reprezintă chiar suma luată cu împrumut;
- a cincea coloană (câmpul F41:F46 – “Total de plată”) reprezintă chiar mărimea plăţii lunare sau „Valoarea lunară de rambursat”, adică suma dintre dobânda lunară şi rata de plată (valoare egală cu PMT). Pentru cele 6 luni, mărimea plăţii este constantă, iar suma plăţilor lunare reprezintă costul împrumutului.
Împrumutul este complet rambursat la începutul celei de-a şaptea perioade, după ce a fost achitată a şasea rată.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 106
Figura 120 Tablou de rambursare a unui credit
Exemplul prezentat anterior este completat de cerinŃa plăŃii cu anticipaŃie a împrumutului, începând cu luna a 4-a. Pentru a se calcula rambursarea cu anticipaŃie a împrumutului, începând cu luna a 4-a, se va folosi funcŃia financiară PV (Present Value) în condiŃiile în care numărul de perioade este egal cu 3 (rambursarea începând din luna a 4-a presupune calcularea anticipată a vărsământului pentru cele 3 luni rămase: lunile 4,5 şi 6). Pentru exemplul ilustrat în Figura 121, suma de 5.161 lei (returnată de funcŃia PV în celula E53) reprezintă valoarea vărsământului pentru achitarea integrală a împrumutului începând din anul al 4-lea.
Figura 121 Plata cu anticipaţie a împrumutului
�� CCaallccuullaarreeaa vvaalloorriiii aaccttuuaallee nneettee..
FFuunnccŃŃiiaa NNPPVV
SSiinnttaaxxăă ==NNPPVV((rraattaa__aaccttuuaalliizzaarree ;; vvaallooaarree__11 ;; ……....;; vvaallooaarree__2299))
���� rraattaa__aaccttuuaalliizzaarree este rata de actualizare pe parcursul unei perioade ;
���� vvaallooaarree__11 ;; ……....;; vvaallooaarree__2299 reprezintă plăŃi sau încasări. RReezzuullttaatt rreettuurrnnaatt
Valoarea actuală netă a unei investiţii bazate pe o serie periodică de
intrări de numerar (cash flows).
OObbsseerrvvaaţţiiii..
FuncŃia NPV diferă de funcŃia PV (Present Value), deoarece se bazează pe vărsăminte care nu au aceeaşi mărime. Astfel, se calculează valoarea actuală netă a unor intrări viitoare de fonduri, pentru a se evalua rentabilitatea unei investiŃii. Intrările de fonduri sunt operaŃionale la intervale regulate, considerate a fi la sfârşitul fiecărei perioade. Valoare1, ……,valoare29 trebuiesc raportate la aceleaşi intervale de timp şi trebuie efectuate la sfârşitul fiecărei perioade. NPV foloseşte ordinea argumentelor valoare1, valoare2, ... pentru a interpreta ordinea fluxurilor de numerar.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 107
aa.. Pentru a calcula valoarea actuală netă prin utilizarea unei rate de actualizare, se utilizează serii de valori negative pentru plăţi viitoare şi serii de valori pozitive pentru încasări viitoare.
Pentru exemplificarea funcŃiei financiare NPV, furnizăm următoarea aplicaŃie: un întreprinzător doreşte realizarea unei investiŃii de 170 mii lei, care îi va permite încasarea unor fonduri estimate ca variabile pe parcursul a 6 ani. Aceste intrări de fonduri se presupun a fi de 223 mii lei. Astfel, se va pune problema rentabilităŃii investiŃiei. În Figura 122 s-a construit un model economic, cu ajutorul căruia s-a calculat prin funcŃia NPV (în celula B95) valoarea actuală netă a investiŃiei, care a fost de 128.901,16 lei dacă rata dobânzii a fost de 15% (celula B84). A face o investiŃie de 170 mii lei, antrenează cheltuieli suplimentare de 41.098,84 lei faŃă de cheltuielile iniŃial prevăzute.
Figura 122 Exemplu de utilizare a funcţiei NPV
FFuunnccţţiiii eeccoonnoommiiccee ddee aammoorrttiizzaarree
Sunt considerate funcŃii economice, acele funcŃii financiare care calculează amortismentul linear sau degresiv pentru imobilizări. Majoritatea funcŃiilor financiare pentru calculul amortismentului fac apel la noŃiunea de valoare reziduală. Această noŃiune semnifică valoarea ce va putea fi recuperată la revînzarea imobilizării, de regulă după expirarea duratei normate de funcŃionare.
SLN, AMORLINC, VDB, AMORDEGRC, SYD, DB, DDB
�� CCaallccuullaarreeaa aammoorrttiissmmeenntteelloorr lliinneeaarree..
FFuunnccŃŃiiaa SSLLNN
SSiinnttaaxxăă ==SSLLNN((vvaallooaarree__ddee__iinnvveennttaarr ;; vvaallooaarree__rreezziidduuaallăă ;;
dduurraattaa__ddee__ffuunnccŃŃiioonnaarree))
� vvaallooaarree__ddee__iinnvveennttaarr reprezintă valoarea cu care a fost înregistrată în evidenŃa contabilă imobilizarea;
� vvaallooaarree__rreezziidduuaallăă semnifică valoarea ce va putea fi recuperată la revînzarea imobilizării, după expirarea duratei normate de funcŃionare;
� dduurraattaa__ddee__ffuunnccŃŃiioonnaarree reprezintă perioada amortizabilă, adică durata cât timp amortismentul va fi repartizat pe costurile produsului căruia i s-a afectat o cotă-parte a imobilizării.
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 108
RReezzuullttaatt rreettuurrnnaatt
Valoarea amortismentului linear al unei imobilizări cu o valoare de
inventar dată, ţinând cont de o valoare reziduală estimată, pentru un
număr de perioade cât se presupune că va funcţiona investiţia.
ObservaŃii.
aa.. Această funcţie este recomandată pentru calculul amortismentului linear aferent imobilizărilor puse în funcţiune la începutul exerciţiului financiar. Amortizarea lineară aferentă unei imobilizări se face prin anuităţi constante (anuitatea se calculează raportând valoarea de inventar a imobilizării la durata de funcţionare a acesteia).
Prin funcŃia economică SLN, anuităŃile amortizării lineare se calculează raportând diferenŃa dintre valoarea de inventar şi valoarea reziduală la numărul de perioade cât a fost estimată durata de funcŃionare a imobilizării. Un exemplu de utilizare a funcţiei SLN este prezentat în Figura 123
Figura 123 Exemplu amortizare lineară prin funcţia SLN După cum se poate observa din exemplul prezentat grafic, rezultatele calculului amortismentului
linear pe domeniul B11:B18 pot fi interpretate în felul următor: “dacă imobilizarea a cărei valoare de
inventar este de 80 mii lei s-ar amortiza linear într-un an, amortismentul anual ar fi de 70 mii lei
(până la nivelul valorii rămase), dacă s-ar amortiza linear în trei ani, amortismentul anual ar fi de
23.333 lei (23.333 * 3 = 70.000 lei).
FFuunnccŃŃiiaa AAMMOORRLLIINNCC
SSiinnttaaxxăă ==AAMMOORRLLIINNCC((vvaallooaarree__ddee__iinnvveennttaarr ;; ddaattaa__ppuunneerriiii__îînn__ffuunnccŃŃiiuunnee ;;
ddaattaa__ssffâârrşşiittuulluuii__pprriimmeeii__ppeerriiooaaddee__ddee__aammoorrttiizzaarree ;;
vvaallooaarree__rreezziidduuaallăă ;; nnuummăărr__ddee__ppeerriiooaaddee__ddee__aammoorrttiizzaarree ;;
rraattaa__aammoorrttiizzăărriiii__lliinneeaarree ;; ccoommuuttaattoorr))
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 109
� nnuummăărr__ddee__ppeerriiooaaddee__ddee__aammoorrttiizzaarree exprimă durata pentru care se calculează amortismentul. Numărul de perioadă este întotdeauna zero la sfârşitul primei perioade;
� rraattaa__aammoorrttiizzăărriiii__lliinneeaarree reprezintă procentul de depreciere anuală a imobilizării supuse amortizării. Acest procent se regăseşte în “fişa mijlocului fix” sau se poate calcula raportând valoarea unitară la durata normată de funcŃionare în ani (1/DNFani);
� ccoommuuttaattoorr este în fapt un cod ce indică numărul anual de zile ce este luat în consideraŃie pentru calculul amortismentului. Astfel, 1 indică numărul real de zile dintr-un an calendaristic, 3 indică 365 de zile pe an, 4 indică 360 de zile pe an.
RReezzuullttaatt rreettuurrnnaatt
Valoarea amortismentului linear al unei imobilizări ţinând cont de data
reală a punerii în funcţiune, adică de prorata temporis.
OObbsseerrvvaaŃŃiiee.. FuncŃia AMORLINC este recomandată pentru calculul amortismentului imobilizărilor puse în funcŃiune în cursul anului. FuncŃia AMORLINC va fi exemplificată odată cu echivalentul său pentru amortizarea degresivă (funcŃia AMORDEGRC) în Figura 126 de la pagina 111.
�� CCaallccuullaarreeaa aammoorrttiissmmeenntteelloorr ddeeggrreessiivvee..
FFuunnccŃŃiiaa VVDDBB
SSiinnttaaxxăă ==VVDDBB((vvaallooaarree__ddee__iinnvveennttaarr ;; vvaallooaarree__rreezziidduuaallăă ;;
dduurraattaa__nnoorrmmaattăă__ddee__ffuunnccŃŃiioonnaarree ;; ddeebbuuttuull__ppeerriiooaaddeeii ;;
ssffâârrşşiittuull__ppeerriiooaaddeeii [[;;rraattăă__ddeepprreecciieerree;; ccoommuuttaattoorr]]))
� ddeebbuuttuull__ppeerriiooaaddeeii;;ssffâârrşşiittuull__ppeerriiooaaddeeii sunt argumente utilizate pentru calcularea anuităŃilor incomplete de amortisment (plecând de la o perioadă când se începe calculul amortizării, către ultima perioadă pentru care se calculează amortizarea);
� [[rraattăă__ddeepprreecciieerree]] este un parametru care influenŃează amortizarea în sensul creşterii gradului de depreciere al imobilizării.
� [[ccoommuuttaattoorr]] permite sau trecerea automată de la amortizarea degresivă la amortizarea lineară (valoare zero – implicită) sau împiedicarea acestei treceri (valoare unu).
RReezzuullttaatt rreettuurrnnaatt
Valoarea amortismentului degresiv ajustat (Variable Declining Balance)
aferent unei imobilizări cu o valoare de inventar dată amortizabilă pe mai
mulţi ani, o valoare reziduală semnificativă şi cu o anumită rată de
depreciere.
OObbsseerrvvaaŃŃiiee.. Amortismentul degresiv ajustat reprezintă amortismentul contabil descrescător (amortismentul este mai mare pentru primele anuităŃi) până ce anuitatea amortismentului este mai mică decât anuitatea ce corespunde amortismentului linear, iar de aici încolo suma amortizabilă este calculată linear. Pentru exemplificarea funcŃiei VDB, furnizăm următoarea aplicaŃie: O societate comercială efectuează la începutul anului o investiŃie de 100 mii lei, amortizabilă în cinci ani. Rata de depreciere pentru o astfel de investiŃie cu o durată de funcŃionare de 5 ani este de 2. Pentru că investiŃia a fost finalizată la începutul anului, aceasta este complet amortizată la sfârşitul celui de-al cincilea an. Tabloul de amortizare aferent acestei imobilizări este prezentat în Figura 124. Pentru fiecare an, perioadele de debut şi sfârşit sunt luate în calcul în coloanele D şi E. Primul an de amortizare a imobilizării începe la 1 ianuarie (valoare 0) şi durează până la 31 decembrie (valoare 1) şi
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 110
aşa mai departe pentru următorii ani (intervalul fiind de o unitate). Se remarcă faptul că ultimele două anuităŃi sunt egale (10.800 lei), metoda de calcul a amortizării trecând automat de la procedeul degresiv la cel linear în anul patru.
Dacă investiŃia ar fi fost pusă în funcŃiune la mijlocul anului, perioada de debut – sfârşit aferentă primului an, ar fi fost 0 şi 0,5, iar dacă investiŃia ar fi fost terminată la 1 aprilie, intervalul ar fi fost 0 şi 0,75 (9/12 dintr-un an). Dacă tabloul de amortizare ar fi recalculat utilizând comutatorul 1, calculul degresiv ar fi fost complet, ultima tranşă de amortizare pentru anul 5 făcându-se prin diferenŃă, după cum se observă şi din Figura 125.
Figura 124 Exemplu de utilizare a funcţiei VDB cu parametru
„comutator” = 0
Figura 125 Exemplu de utilizare a funcţiei VDB cu parametru
„comutator” = 1
FFuunnccŃŃiiaa AAMMOORRDDEEGGRRCC
SSiinnttaaxxăă ==AAMMOORRDDEEGGRRCC((vvaallooaarree__ddee__iinnvveennttaarr ;;
ddaattaa__ppuunneerriiii__îînn__ffuunnccŃŃiiuunnee ;;
ddaattaa__ssffâârrşşiittuulluuii__pprriimmeeii__ppeerriiooaaddee__ddee__aammoorrttiizzaarree ;;
vvaallooaarree__rreezziidduuaallăă ;; nnuummăărr__ddee__ppeerriiooaaddee__ddee__aammoorrttiizzaarree ;;
rraattaa__aammoorrttiizzăărriiii__lliinneeaarree ;; ccoommuuttaattoorr))
� argumente identice (şi cu aceeaşi semnificaŃie) ca funcŃia AMORLINC. RReezzuullttaatt rreettuurrnnaatt
Valoarea amortismentului degresiv al unei imobilizări ţinând cont de data
reală a punerii în funcţiune.
OObbsseerrvvaaŃŃiiee.. CoeficienŃii amortismentului degresiv, utilizaŃi de funcŃia AMORDEGRC variază în funcŃie de numărul de perioade de amortizare astfel: 1,5 pentru o perioadă cuprinsă între 3 şi 4 ani, 2 pentru o perioadă cuprinsă între 5 şi 6 ani şi 3 pentru o perioadă de peste 6 ani. Exemplul ilustrat în Figura 126 prezintă două variante de amortizare (lineară, calculată prin AMORLINC şi degresivă, calculată prin AMORDEGRC) pentru o imobilizare pusă în funcŃiune la data de 8 iunie 2007, cu o valoare de inventar de 100 mii lei, o valoare reziduală nulă, şi o durată normată de funcŃionare de 5 ani (rata amortizării = 1/5 * 100).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 111
Figura 126 Exemplu de utilizare a funcţiilor AMORLINC şi AMORDEGRC FFuunnccŃŃiiaa SSYYDD
SSiinnttaaxxăă ==SSYYDD((vvaallooaarree__ddee__iinnvveennttaarr ;; vvaallooaarree__rreezziidduuaallăă ;;
dduurraattăă__ddee__ffuunnccŃŃiioonnaarree ;; ppeerriiooaaddaa__ddee__ccaallccuull__aa__aammoorrttiizzăărriiii))
� argumente prezentate anterior. RReezzuullttaatt rreettuurrnnaatt
Valoarea amortismentului degresiv absolut, fără a corecta ultimele
anuităţi pentru amortizarea completă a investiţiei.
În Figura 127 este prezentat un model de calcul pentru amortismentul degresiv absolut cu ajutorul
funcţiei SYD. Rezultatele calculate pe domeniul C11:C18 pot fi interpretate în felul următor: “pentru
o durată normată de funcţionare de 8 ani, o imobilizare a cărei valoare de inventar este de 80 mii lei
s-ar amortiza degresiv în primul an cu 15.556 lei, în al doilea an cu 13.611 lei, …., în ultimul an cu
1.944 lei (suma amortismentelor în cei 8 ani fiind de 70 mii lei).
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 112
Figura 127 Exemplu de utilizare a funcţiei de amortizare degresivă SYD FFuunnccŃŃiiaa DDBB
SSiinnttaaxxăă ==DDBB((vvaallooaarree__ddee__iinnvveennttaarr ;; vvaallooaarree__rreezziidduuaallăă;;
dduurraattăă__ddee__ffuunnccŃŃiioonnaarree ;; ppeerriiooaaddaa__ddee__ccaallccuull__aa__aammoorrttiizzăărriiii ;;
nnuummăărruull__ddee__lluunnii__ppee__aann__ddee__ffuunnccŃŃiioonnaarree__aa__iimmoobbiilliizzăărriiii))
� argumente prezentate anterior. RReezzuullttaatt rreettuurrnnaatt
Valoarea amortismentului degresiv absolut, ţinând cont de numărul de
luni pe an de funcţionare a imobilizării (se ia în calcul un posibil aspect
sezonier de utilizare al acesteia).
FFuunnccŃŃiiaa DDDDBB
SSiinnttaaxxăă ==DDDDBB((vvaallooaarree__ddee__iinnvveennttaarr;;vvaallooaarree__rreezziidduuaallăă ;;
dduurraattăă__ddee__ffuunnccŃŃiioonnaarree;;ppeerriiooaaddaa__ddee__ccaallccuull__aa__aammoorrttiizzăărriiii ;;
nnuummăărruull__ddee__lluunnii__ppee__aann__ddee__ffuunnccŃŃiioonnaarree__aa__iimmoobbiilliizzăărriiii ;;
ffaaccttoorr__ddee__mmuullttiipplliiccaarree))
� argumente prezentate anterior;
� ffaaccttoorr__ddee__mmuullttiipplliiccaarree reprezintă un coeficient (3, 4, 5) care accelerează amortizarea imobilizării.
RReezzuullttaatt rreettuurrnnaatt
Valoarea amortismentului degresiv absolut, ţinând cont de numărul de
luni pe an de funcţionare a imobilizării (anuităţile sunt calculate de o aşa
manieră încât ultima anuitate este n, penultima anuitate este 2n,
Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________________________________________
Tehnologia aplicaţiilor Office 113
antepenultima este 3n şi aşa mai departe).
Figura 128 ilustrează un exemplu edificator de utilizare a funcŃiilor DB şi DDB pentru o imobilizare cu o valoare de inventar de 80 mii lei, o valoare reziduală de 10 mii lei, o durată de funcŃionare de 8 ani şi un număr de 10 luni pe an de exploatare a respectivului mijloc fix.
Figura 128 Exemplu de utilizare a funcţiilor DB şi DDB
� Bibliografia Capitolului 3 Nr. Crt.
Nume Prenume Titlu Editura
1. Ionescu Bogdan şi al.
Tehnologia Aplicatiilor
Office, Excel 2007
Ed. Infomega,
Bucureşti, 2009,pag
157-307
2. Ionescu Iuliana şi. al.
Office 2003 Aplicaţii şi teste
rezolvate de WORD şi
EXCEL
Ed. Infomega,
Bucureşti, 2005,
3. Walkenbach John
Microsoft Office Excel 2007
Bible
Ed. Wiley Publishing
Inc., 2007