Tao

7/13/2019 Tao

http://slidepdf.com/reader/full/tao557201a24979599169a20159 1/113

Capitol ul 2 Funcții de calcul tabelar _________________________________________________________________________________ ________________________________

Tehnologia aplicaţiilor Office 1

Capitolul 2FUNCȚII DE CALCUL TABELAR

__________________________________________________________________________________

Cuprins

Capitolului 2FUNCȚII DE CALCUL TABELAR

Obiectivele Capitolului 2

2.1 Funcţii matematiceFuncţii algebriceFuncţ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

7/13/2019 Tao




Capitolului 2 FUNCȚII DE CALCUL TABELAR OBIECTIVELE

• Însuşirea cunoştintelor necesare pentru efectuarea operaţiilor fundamentale asupradatelor dintr-un tabel EXCEL;

• Utilizarea funcţiilor de calcul tabular pe grupe.

2.1 Funcţii matematiceUnele 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 caleobiş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 separatorde argumente.

Acest separator poate fi virgulă sau punct şi virgulă , după cum au fost configuraŃi parametrii regionali

7/13/2019 Tao




(de localizare) ai sistemului de operare Windows XP ( Start → Settings → Control Panel →Regional and Language Options → caseta de dialog Regional and Language Options → tabulRegional Options ). În exemplele luate, se va lua în consideraŃie ca separator de argumentecaracterul "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Ńiaargumentului poate fi deci o formulă, un număr, un nume de câmp, un şir de caractere. FuncŃiaevaluează 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ă saucâ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 ceconŃ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 ficonfundat cu un nume de câmp.

valoare: reprezintă o constantă numerică, adresa sau numele unei celule care conŃine un număr, oformulă sau funcŃie predefinită care returnează un număr;expresie: reprezintă combinarea locaŃiilor, valorilor, operatorilor aritmetici şi de comparaŃie pentru areprezenta 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, Excelpune 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.

7/13/2019 Tao




Figura 2 Modul interactiv de introducere a funcţiilorCea mai simplă metodă de editare a funcŃiilor este reprezentată de introducerea în mod direct afuncŃ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ă generezerezultate 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, peselecŃ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. Lasfâ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ă auneia din celulele câmpului matriceal. Pentru a executa o modificare, este necesar a se selecta înprealabil câmpul matriceal, iar apoi să se efectueze respectiva modificare, după care să se activeze dinnou combinaŃia de taste Ctrl+Enter. Ştergerea formulei implică selectarea întregului câmp matricealşi acŃionarea tastei Delete sau a comenzii EditClear All.

O altă posibilitate de introducere a funcŃiilor predefinite este operaŃională prin intermediulselectorului de funcŃii aflat pe bara de editare sau activabil prin comanda InsertFunction.

Figura 3 Casetele de dialog aferente selectorului defuncţ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 maiutilizate ( 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 asistentuluide funcŃii Function Arguments (Figura 3). Apelarea selectorului de funcŃii se mai poate face şi prin acŃionarea butonului =(egal) aflat pe bara deeditare, după care se deschide lista funcŃiilor predefinite (cu ajutorul butonului de derulare aflat înpartea 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.

7/13/2019 Tao




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 defuncŃ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 importante175 de funcŃii predefinite, grupate pe următoarele categorii:

funcŃii matematice (Madh & Trig): permit efectuarea de calcule algebrice, trigonometrice şi derotunjire a rezultatului acestor calcule;funcŃii statistice (Statistical) : permit efectuarea unor calcule statistice utilizând distribuŃii de datepe 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ă datecalendaristice 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 încelule şi permit efectuarea diferitelor operaŃii cu şiruri sau valori, evaluând valorile poziŃionale aleacestora;funcŃii bază de dată (Database): efectuează diferite calcule de grup asupra unor rubrici, într-obază de date, corespunzător unor criterii definite;

funcŃii economico-financiare (Financial): permit realizarea de calcule economico-financiarepredefinite.În continuare, prezentăm cele mai importante funcŃii predefinite, precizând faptul că în cea mai marea parte a lor sunt perfect compatibile ca sintaxă şi semnificaŃie cu funcŃiile arond (@) aferenteprocesorului de tabele LOTUS 1-2-3, parte a pachetului de aplicaŃii LotusSmartSuite, dezvoltat deIBM.

7/13/2019 Tao




FFuunnccţţiiiillee mmaatteemmaattiiccee ((MMaatthh && TTrriigg))

FuncŃiile Math&Trig permit efectuarea diferitelor calcule, de la cele mai simple la cele maicomplexe, pentru rezolvarea de aplicaŃii ce solicită instrumente matematice şi trigonometrice de uzcurent.FuncŃiile “MMaatthh && T Trriigg” 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 PPR R OODDUUCC T T

SSiinnttaaxxăă ==PPR R OODDUUCC T T((l l i i s s t t ă ă _d d e e _ _e e l l e e m m e e n n t t e e ))

l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e reprezintă o listă compusă din constante numerice, adrese decelule, rezultate ale formulelor sau nume de câmpuri.

R R eezzuullttaatt rreettuurrnnaatt

O valoare numerică ce reprezintă produsul elementelor listei .

CCaallccuullaarreeaa uunneeii ssuummee..

FFuunnccŃŃiiaa SSUUMM

SSiinnttaaxxăă ==SSUUMM((l l i i s s t t ă ă _d d e e _ _e e l l e e m m e e n n t t e e ))

l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e reprezintă o listă compusă din constante numerice, celule,rezultate ale formulelor sau nume de câmpuri.R R eezzuullttaatt 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ă AutoSumpermit însumarea pe linie sau pe coloană a valorilor adiacente (valorile nutrebuie să fie întrerupte în succesiunea lor de celule vide sau decelule 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Ńiipredefinite (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 ).

7/13/2019 Tao



Î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. Rezultatulcalculului se depune automat în celula situată imediat sub (sau la dreapta – în cazul unui câmporizontal) plaja de celule (A20).ObservaŃie : această tehnică are avantajul faptului că pot fi operate calcule corecte în condiŃiile încare 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ţiaexplicită a unei plaje decelule (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 SSUUMMPPR R OODDUUCC T T

SSiinnttaaxxăă ==SSUUMMPPR R OODDUUCC T T((l l i i s s t t ă ă _ _d d e e _ _c c â â m m p p u u r r i i ))

l l i i s s t t ă ă _ _d d e e _ _c c â â m m p p u u r r i i reprezintă un grup de câmpuri (cel puŃin două) ce conŃinelemente care se vor înmulŃi poziŃional unele cu altele (primul element din primulcâmp se va înmulŃi cu primul element din al doilea câmp, al doilea element dinprimul câmp se va înmulŃi cu al doilea element din al doilea câmp şi aşa maideparte).


O valoare numerică ce reprezintă suma produselor câmpurilor listei . Altfel spus,rezultatul funcŃiei provine din multiplicarea valorilor situate în câmpurilespecificate, după care sunt adunate agregatele obŃinute anterior.

Selecţie explicită

a câmpului sursă

(A11:A19) +

A14:Celulă vidă

7/13/2019 Tao




OObbsseerrvvaaţţiiee. Grupul de câmpuri (organizat pe l inie sau pe coloană) trebuie să conţină aceleaşi număr

de elemente.

CCaallccuullaarreeaa uunneeii ssuummee î î nn f f uunnccţţiiee ddee eevvaalluuaarreeaa uunneeii ccoonnddiiţţiiii..

FFuunnccŃŃiiaa SSUUMMIIFF

SSiinnttaaxxăă ==SSUUMMIIFF((c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t ; ; c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c c Ń Ń i i e e [ [ ; ; c c â â m m p p _ _ d d e e _ _ î î n n s s u u m m a a t t ] ] ))

c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t reprezintă o listă compusă din adrese de celule sau nume decâmpuri. Asupra acestui argument operează condiŃia specificată de argumentulurmător;

c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c c Ń Ń i i e e reprezintă o condiŃie impusă c c â â m m p pu u l l u u i i d d e e e e v v a a l l u u a a t t , care se specificăsub formă de şir de caractere (între ghilimele);

[ [ c c â â m m p p _ _ d d e e _ _ î î n n s s u u m m a a t t ] ] este argumentul care desemnează câmpul care va fi însumat înfuncŃie de evaluarea condiŃiei precizate de al doilea argument. Dacă acest argumentlipseşte, c c â â m m p pu u l l d d e e î î n n s s u u m m a a t t va fi chiar c c â â m m p pu u l l d d e e e e v v a a l l u u a a t t .


Suma elementelor unei liste în funcŃie de evaluarea unei condiŃii precizate.

CCaallccuullaarreeaa uunneeii ssuummee î î nn f f uunnccţţiiee ddee eevvaalluuaarreeaa mmaaii mmuullttoorr ccoonnddiiţţiiii..

FFuunnccŃŃiiaa SSUUMMIIFFSS

SSiinnttaaxxăă ==SSUUMMIIFFSS((c c â â m m p p _ _d d e e _ _î î n n s s u u m m a a t t ; ; c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t _ _1 1 ; ;

c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c c Ń Ń i i e e _ _1 1 [ [ ; ; c c â â m m p p _ _ d d e e _ _ e e v v a a l l u u a a t t _ _ 2 2 ; ; c c r r i i t t e e r r i i u u l l _ _ s s e e l l e e c c Ń Ń i i e e _ _ 2 2 ] ] .............. ...... ) )

c c â â m m p p _ _d d e e _ _î î n n s s u u m m a a t t este argumentul care desemnează câmpul care va fi însumat înfuncŃie de evaluarea condiŃiei sau condiŃiilor precizate.

c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t _ _1 1 reprezintă o listă compusă din adrese de celule sau nume decâmpuri. Asupra acestui argument operează condiŃia specificată de argumentul

următor; c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c c Ń Ń i i e e _ _1 1 reprezintă o condiŃie impusă c c â â m m p pu u l l u u i i d d e e e e v v a a l l u u a a t t _ _ 11, care se

specifică sub formă de egalitate sau restricŃie (între ghilimele);R R eezzuullttaatt 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).

7/13/2019 Tao




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ââmm p p _ _d d ee _ _ î î nnssuummaat t , deoarece condiţia a fost pusă chiar pe

ccââmm p puul l _ _d d ee _ _eev v aal l uuaat t ). 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.

7/13/2019 Tao



Î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 celulecuprinde 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ă permiteconstruirea 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ă, potrivitelementelor 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 introduce35 şi nu 35 € cum propune respectiva rubrică). După ce condiţia a fost construită, aceasta sevalidează 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).

7/13/2019 Tao




Step 3 of 4 permite ramificarea execuţiei în funcţie de faptul dacă se doreşte numai afişarearezultatului însumării condiţionale (opţiunea: Copy just a formula to a single cell ) sau afişarearezultatului împreună cu elementele ce desemnează restricţiile impuse (opţiunea Copy theformula and conditional values).

În varianta alegerii opţiunii Copy just a formula to a single cell, etapa Step 4 of 4 permitealegerea 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 decriterii.

CCaallccuullaarreeaa llooggaarriittmmiilloorr..

FFuunnccŃŃiiaa LLNN

SSiinnttaaxxăă ==LLNN((n n u u m m ă ă r r ))

n n u u m m ă ă r r reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce oconŃine.

R R eezzuullttaatt rreettuurrnnaatt O valoare numerică ce reprezintălogaritmul natural

al unui număr specificatca argument.

FFuunnccŃŃiiaa LLOOGG

SSiinnttaaxxăă ==LLOOGG((n n u u m m ă ă r r ; ; [ [ b b a a z z ă ă ] ] ))

n n u u m m ă ă r r reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce oconŃine;

[ [ b b a a z z ă ă ] ] reprezintă o constantă ce reprezintă baza logaritmului. Dacă se omiteprecizarea bazei, se consideră logaritmul în baza 10.

R R eezzuullttaatt rreettuurrnnaatt O valoare numerică ce reprezintă logaritmul unui număr într-o bază specificată .

FFuunnccŃŃiiaa LLOOGG1100

SSiinnttaaxxăă ==LLOOGG1100((n n u u m m ă ă r r ))



O valoare numerică ce reprezintă logaritmul unui număr în baza 10.

CCaallccuulluull eexxppoonneennttuulluuii..

FFuunnccŃŃiiaa EEXXPP

SSiinnttaaxxăă ==EEXXPP((n n u u m m ă ă r r ))

n n u u m m ă ă r r reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ce oconŃine. Constanta numerică reprezintă exponentul aplicat la baza e.

R R eezzuullttaatt O valoare numerică ce reprezintă baza logaritmul natural a unui număr,

7/13/2019 Tao




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

SSiinnttaaxxăă == A A BBSS((n n u u m m ă ă r r ))



O valoare numerică pozitivă extrasă dintr-un număr real.

CCaallccuullaarreeaa f f aaccttoorriiaalluulluuii uunnuuii nnuummăărr..

FFuunnccŃŃiiaa FF A A CC T T

SSiinnttaaxxăă ==FF A A CC T T((n n u u m m ă ă r r ))



O valoare numerică pozitivă ce reprezintă factorialul unui număr

(1*2*3* ....*număr).

CCaallccuullaarreeaa uunnuuii nnuummăărr rriiddiiccaatt llaa ppuutteerree..

FFuunnccŃŃiiaa PPOOWWEER R

SSiinnttaaxxăă ==PPOOWWEER R ((n n u u m m ă ă r r ; ; p p u u t t e e r r e e ))

n n u u m m ă ă r r reprezintă o constantă numerică pozitivă, o referinŃă celulară / nume decelulă ce o conŃine;

p p u u t t e e r r e e reprezintă o constantă numerică ce semnifică exponentul.R R eezzuullttaatt O valoare ce reprezintă un număr ridicat la puterea specificată de

7/13/2019 Tao




rreettuurrnnaatt argument.

OObbsseerr v v aaŃŃiiee.. Pentru a se ridica un număr la o putere, printr-o formulă simplă, se utilizează operatorulexponenŃial (^).

CCaallccuullaarreeaa rrăăddăăcciinniiii ppăăttrraattee ddiinnttrr--uunn nnuummăărr..

FFuunnccŃŃiiaa SSQQR R T T

SSiinnttaaxxăă ==SSQQR R T T((n n u u m m ă ă r r ))

n n u u m m ă ă r r reprezintă o constantă numerică pozitivă, o referinŃă celulară / nume decelulă ce o conŃine.


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 f f oorrmmaattuulluuii cciif f rriicc aarraabb î î nn f f oorrmmaatt tteexxtt rroommaann..

FFuunnccŃŃiiaa R R OOMM A A NN

SSiinnttaaxxăă ==R R OOMM A A NN((n n u u m m ă ă r r [ [ ; ; f f oor r m m a a t t ] ] ))

n n u u m m ă ă r r reprezintă o constantă numerică pozitivă (cuprinsă între 1 şi 3999), oreferinŃă celulară / nume de celulă ce o conŃine;

[ [ f f oor r m m a a t t ] ] 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.


O valoare de tip şir de caractere ce semnifică transformarea unui numărdin 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 R R A A NNDD

7/13/2019 Tao




SSiinnttaaxxăă == R R A A NNDD(())

f f ă ă r r ă ă a a r r g g u u m m e e n n t t R R eezzuullttaatt rreettuurrnnaatt

Un număr aleator cuprins între 0 şi 1.

FFuunnccŃŃiiaa R R A A NNDDBBEE T TWWEEEENN

SSiinnttaaxxăă ==R R A A NNDDBBEE T TWWEEEENN((l l i i m m i i t t ă ă _ _i i n n f f e e r r i i o o a a r r ă ă ; ; l l i i m m i i t t ă ă _ _ s s u u p p e e r r i i o o a a r r ă ă ))

l l i i m m i i t t ă ă _ _i i n n f f e e r r i i o o a a r r ă ă reprezintă o constantă numerică întreagă, o referinŃă celulară /nume de celulă ce conŃine respectiva valoare numerică ce este considerată bornăinferioară;

l l i i m m i i t t ă ă _ _s s u u p p e e r r i i o o a a r r ă ă reprezintă o constantă numerică întreagă, o referinŃă celulară /nume de celulă ce conŃine respectiva valoare considerată bornă superioară.


Un număr aleator (întreg) cuprins între două limite, una inferioară şi unasuperioară.


La fiecare proces de recalcul, funcŃiile RAND şi RANDBETWEEN vor returna în mod aleator onouă valoare. Excel recalculează toate formulele sale şi actualizează rezultatele odată cu orice intrareefectuată î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 procesde recalcul, se recomandă transformarea rezultatelor funcŃiilor RAND sau RANDBETWEEN înconstante numerice prin convertirea acestor formule în valori (comanda HomeCopy la sursă şiPaste opŃiunea Paste Values ).În Excel, funcŃia RANDBETWWEN nu este disponibilă în mod implicit. Aceasta se instalează lacererea utilizatorului, prin încărcarea unor componente. Acest demers este operaŃional prin comandaOffice Button > Excel Options, tabul Add Ins > opŃiunea Analysis ToolPak .Figura 14 ilustrează un exemplu prin care o societate comercială de distribuŃie organizează otombolă cu ocazia promovării unui produs, ocazie cu care desemnează 5 câştigători într-o ordinealeatoare.

Figura 14 Exemplu de utilizare a funcţiei RANDBETWEEN

CCaallccuullaarreeaa ppăărrţţiiii î î nnttrreeggii aaf f eerreennttee ccââttuulluuii uunneeii î î mmppăărrţţiirrii ..

FFuunnccŃŃiiaa QQUUOO T TIIEENN T T

7/13/2019 Tao




SSiinnttaaxxăă ==QQUUOO T TIIEENN T T((d d e e î î m m p p ă ă r r Ń Ń i i t t ; ; d d i i v v i i z z o o r r ))

d d e e î î m m p p ă ă r r Ń Ń i i t t reprezintă o constantă numerică, o referinŃă celulară / nume de celulăce conŃine respectiva valoare numerică ce urmează a fi divizată;

d d i i v v i i z z o o r r reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ceconŃine respectiva valoare ce va fi considerată divizor.


O valoare numerică ce reprezintă partea întreagă a rezultatului împărŃiriiunui 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((d d e e î î m m p p ă ă r r Ń Ń i i t t ; ; d d i i v v i i z z o o r r ))

d d e e î î m m p p ă ă r r Ń Ń i i t t reprezintă o constantă numerică, o referinŃă celulară / nume de celulăce conŃine respectiva valoare numerică ce urmează a fi divizată;

d d i i v v i i z z o o r r reprezintă o constantă numerică, o referinŃă celulară / nume de celulă ceconŃine respectiva valoare ce va fi considerată divizor.


O valoare numerică ce reprezintă restul împărŃirii primului argument la aldoilea.

Î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((l l i i s s t t ă ă _d d e e _ _e e l l e e m m e e n n t t e e ))

l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e reprezintă o listă compusă din constante numerice, celule,rezultate ale formulelor sau nume de câmpuri.


O valoare ce este cel mai mare divizor comun al numerelor specificate caargumente, adică cel mai mare număr întreg care divide fără rest lista deelemente numerice specificate.

7/13/2019 Tao




CCaallccuullaarreeaa cceelluuii mmaaii mmiicc mmuullttiipplluu ccoommuunn..

FFuunnccŃŃiiaa LLCCMM

SSiinnttaaxxăă ==LLCCMM((l l i i s s t t ă ă _ _d d e e _e e l l e e m m e e n n t t e e ))

l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e reprezintă o listă compusă din constante numerice, celule,

rezultate ale formulelor sau nume de câmpuri.R R eezzuullttaatt rreettuurrnnaatt

O valoare ce este cel mai mic număr întreg care este multiplu comun pentrulista 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((n n u u m m ă ă r r _ _e e l l e e m m e e n n t t e e ; ; n n u u m m ă ă r r _ _e e l l e e m m e e n n t t e e _ _ p p e e _ _ g g r r u u p p ))

n n u u m m ă ă r r _ _e e l l e e m m e e n n t t e e reprezintă un număr de elemente de repartizat;

n n u u m m ă ă r r _ _e e l l e e m m e e n n t t e e _ _ p p e e _ _ g g r r u u p p reprezintă numărul de elemente din fiecarecombinare.


O valoare ce determină numărul de combinări pentru un număr dat deelemente, adică numărul total posibil de grupuri pentru un număr deelemente 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..

7/13/2019 Tao




FFuunnccŃŃiiaa MMMMUULL T T

SSiinnttaaxxăă ==MMMMUULL T T((m m a a t t r r i i c c e e _ _ A A ; ; m m a a t t r r i i c c e e _ _B B ))

m m a a t t r r i i c c e e _ _ A A şi m m a a t t r r i i c c e e _ _B B reprezintă două matrice.R R eezzuullttaatt

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 adoua matrice. Astfel, fiecare element ( i,j ) al matricei produs, reprezintă suma produselor termen cutermen a elementelor liniei i aferente primei matrice, cu elementele coloanei j , aferente celei de adoua matrice. Formula ce conŃine funcŃia MMULT se va introduce în formă matriceală. Un exemplu de produs adouă matrice este prezentat în Figura 19.

Figura 19 Exemplu de utilizare a funcţiei MMULT CCaallccuulluull mmaattrriicceeii iinnvveerrssee..

FFuunnccŃŃiiaa MMIINN V V EER R SSEE

SSiinnttaaxxăă ==MMIINN V V EER R SSEE((m m a a t t r r i i c c e e ))

m m a a t t r r i i c c e e reprezintă o matrice stocată într-un tablou de date.R R eezzuullttaatt rreettuurrnnaatt

Matricea inversă.


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

7/13/2019 Tao




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Ńiasistemului 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 matriceiinverse a lui A şi înmulŃirea acesteia cu vectorul B.

Figura 21 Exemplu de rezolvare prin calcul matriceal a unui sistem de ecuaţii linearecu n necunoscute

CCaallccuullaarreeaa ddeetteerrmmiinnaannttuulluuii uunneeii mmaattrriiccee..

FFuunnccŃŃiiaa MMDDEE T TEER R MM

SSiinnttaaxxăă ==MMDDEE T TEER R MM((m m a a t t r r i i c c e e ))

m m a a t t r r i i c c e e reprezintă o matrice stocată într-un tablou de date.R R eezzuullttaatt rreettuurrnnaatt

Valoarea determinantului unei matrice.

Un exemplu de calcul a determinantului unei matrice este prezentat în Figura 22.

7/13/2019 Tao




Figura 22 Exemplu de utilizare a funcţiei MDETERM

FFuunnccţţiiii ddee rroottuunn j jiirree aa rreezzuullttaatteelloorr ccaallccuulleelloorr

ROUNDUP, ROUNDOWN, ROUND, INT, TRUNC, EVEN, ODD, FLOOR, CEILING, MROUND

RRoottuunn j jiirreeaa pprriinn aaddaaooss//lliippssăă aa uunnuuii nnuummăărr ccăăttrree zzeerroo..

FFuunnccŃŃiiiillee R R OOUUNNDDUUPP // R R OOUUNNDDDDOOWWNN

SSiinnttaaxxăă ==R R OOUUNNDDUUPP//R R OOUUNNDDDDOOWWNN((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ; ; + + / / - - c c o o n n s s t t a a n n t t ă ă ))

n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t reprezintă o constantă numerică cu zecimale, rezultatul unei

expresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente; + + / / - - c c o o n n s s t t a a n n t t ă ă d d e e r r o o t t u u n n j j i i r r e e semnifică o valoare poziŃională la care să se efectueze

rotunjirea.


O valoare numerică rotunjită prin adaos/lipsă la o anumită valoarepoziŃ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.

RRoottuunn j jiirreeaa uunneeii vvaalloorrii nnuummeerriiccee pprriinn aaddaaooss ssaauu lliippssăă..

FFuunnccŃŃiiaa R R OOUUNNDD

SSiinnttaaxxăă ==R R OOUUNNDD((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ; ; + + / / - - c c o o n n s s t t a a n n t t ă ă d d e e r r o o t t u u n n j j i i r r e e ))

n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t reprezintă o constantă numerică cu zecimale, rezultatul uneiexpresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente;

+ + / / - - c c o o n n s s t t a a n n t t ă ă d d e e r r o o t t u u n n j j i i r r e e semnifică o valoare poziŃională la care să se efectueze

rotunjirea.R R eezzuullttaatt 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

7/13/2019 Tao




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

AAf f iişşaarreeaa ppăărrţţiiii î î nnttrreeggii aa uunnuuii nnuummăărr rreeaall..

FFuunnccŃŃiiaa IINN T T

SSiinnttaaxxăă ==IINN T T((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ))

n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t reprezintă o constantă numerică, rezultatul unei expresii decalcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente.


O valoare numerică rotunjită prin lipsă până la cel mai apropiat întreg.

FFuunnccŃŃiiaa T TR R UUNNCC

SSiinnttaaxxăă == T TR R UUNNCC((n n u u m m ă ă r r _ _d d e e _ _t t r r u u n n c c h h i i a a t t ))

n n u u m m ă ă r r _ _d d e e _ _t t r r u u n n c c h h i i a a t t reprezintă o constantă numerică cu zecimale, rezultatul uneiexpresii de calcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente.

R R eezzuullttaatt

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.

7/13/2019 Tao




Figura 24 Exemplu de utilizare a funcţiilor INT şi TRUNC RRoottuunn j jiirreeaa pprriinn aaddaaooss aa uunnuuii nnuummăărr ccăăttrree cceell mmaaii aapprrooppiiaatt î î nnttrreegg ppaarr ssaauu iimmppaarr.

FFuunnccŃŃiiaa EE V V EENN

SSiinnttaaxxăă ==EE V V EENN((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ))



O valoare numerică rotunjită prin adaos către cel mai apropiat numărîntreg par .

FFuunnccŃŃiiaa OODDDD

SSiinnttaaxxăă ==OODDDD((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ))


R R eezzuullttaatt

rreettuurrnnaatt

O valoare numerică rotunjită prin adaos către cel mai apropiat număr

întreg impar .

RRoottuunn j jiirreeaa uunnuuii nnuummăărr pprriinn lliippssăă ssaauu aaddaaooss,, ccăăttrree zzeerroo,, ppeennttrruu cceell mmaaii aapprrooppiiaatt mmuullttiipplluu ssaauu ssuubbmmuullttiipplluu ddee rroottuunn j jiirree..

FFuunnccŃŃiiaa FFLLOOOOR R // CCEEIILLIINNGG

SSiinnttaaxxăă ==FFLLOOOOR R ((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ; ; m m u u l l t t i i p p l l u u / / s s u u b b m m u u l l t t i i p p l l u u _ _d d e e _ _r r o o t t u u n n j j i i r r e e ))

==CCEEIILLIINNGG((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ; ; m m u u l l t t i i p p l l u u / / s s u u b b m m u u l l t t i i p p l l u u _ _d d e e _ _r r o o t t u u n n j j i i r r e e ))

n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t reprezintă o constantă numerică, rezultatul unei expresii decalcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente;

m m u u l l t t i i p p l l u u _ _d d e e _ _r r o o t t u u n n j j i i r r e e reprezintă o constantă numerică întreagă ce semnifică unmultiplu de rotunjire.

s s u u b b m m u u l l t t i i p p l l u u _ _d d e e _ _r r o o t t u u n n j j i i r r e e reprezintă o constantă numerică zecimală ce inhibărotunjirea în mod poziŃional astfel: 0,001 se inhibă rotunjirea miilor; 0,01 seinhibă rotunjirea sutimilor; 0,1 se rotunjeşte la nivelul zecimilor.


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.

7/13/2019 Tao




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 RRoottuunn j jiirreeaa uunnuuii nnuummăărr llaa uunn mmuullttiipplluu ccee ccoorreessppuunnddee uunneeii vvaalloorrii nnuummeerriiccee iinnf f eerriiooaarree ssaauu

ssuuppeerriiooaarree nnuummăărruulluuii rreeaall..

FFuunnccŃŃiiaa MMR R OOUUNNDD

SSiinnttaaxxăă ==MMR R OOUUNNDD((n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t ; ; m m u u l l t t i i p p l l u u _ _d d e e _ _r r o o t t u u n n j j i i r r e e ))

n n u u m m ă ă r r _ _d d e e _ _r r o o t t u u n n j j i i t t reprezintă o constantă numerică, rezultatul unei expresii decalcul, referinŃa celulară sau numele celulei ce conŃine aceste elemente;

m m u u l l t t i i p p l l u u _ _d d e e _ _r r o o t t u u n n j j i i r r e e reprezintă o constantă numerică întreagă ce semnifică unmultiplu de rotunjire.


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 multipliconsecutivi de rotunjire aplicaŃi numărului, atunci rotunjirea va fi făcută prin lipsă către cel mai micmultiplu de rotunjire. În Excel, funcŃia MROUND reprezintă o componentă ce se instalează la cerere, prin comandaOffice 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 ladiferiŃ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.

7/13/2019 Tao




Figura 26 Exemplu de utilizare a funcţiei MROUNDExplicaţ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Ńuluieditat î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ă încâ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 rotunjireprin lipsă la partea zecimală a unei medii aritmetice.

Figura 27 Exemplu de rotunjire prin adaos sau lipsa la un multiplu de rotunjire

7/13/2019 Tao




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

7/13/2019 Tao




IIddeennttiif f iiccaarreeaa cceelleeii mmaaii mmaarrii // mmiiccii vvaalloorrii ddiinnttrr--oo sseerriiee ddee ddaattee..

FFuunnccŃŃiiaa MM A A XX

SSiinnttaaxxăă ==MM A A XX((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))


rezultate ale formulelor sau nume de câmpuri.R R eezzuullttaatt rreettuurrnnaatt

Valoarea cea mai mare dintr-o distribuŃie statistică de date.

FFuunnccŃŃiiaa MMIINN

SSiinnttaaxxăă ==MMIINN((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))



Valoarea cea mai mică dintr-o distribuŃie statistică de date.

CCaallccuulluull mmeeddiieeii aarriittmmeettiiccee aaf f eerreennttee uunneeii sseerriiii ssttaattiissttiiccee ddee ddaattee..

FFuunnccŃŃiiaa A A V V EER R A A GGEE

SSiinnttaaxxăă == A A V V EER R A A GGEE((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))



Media aritmetică dintr-o distribuŃie statistică de date. Media este calculatăca o sumă de elemente numerice raportată la numărul de elemente.


Dacă un câmp pe care se calculează media conŃine celule vide, şiruri de caractere, valori logice, acesteelemente nu sunt incluse la calcularea mediei. Dacă se doreşte includerea valorilor logice (TRUE sauFALSE) î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ă încalculul mediei.

Dacă lista de elemente pe care se calculează media este o mulŃime vidă, atunci funcŃia AVERAGE vareturna 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 ofuncţ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ă.

7/13/2019 Tao




Figura 29 Exemplu de utilizare a funcţiilor MAX, MIN şi AVERAGE

CCaallccuulluull ccoonnddiiţţiioonnaall aall mmeeddiieeii aarriittmmeettiiccee aaf f eerreennttee uunneeii sseerriiii ssttaattiissttiiccee ddee ddaattee..

FFuunnccŃŃiiaa A A V V EER R A A GGEEIIFF

SSiinnttaaxxăă == A A V V EER R A A GGEEIIFF((c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t ; ; c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c c Ń Ń i i e e

[ [ ; ; c c â â m m p p _ _ d d e e _ _ c c a a l l c c u u l l a a t t _ _ m m e e d d i i a a ] ] ))

c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t reprezintă o listă compusă din adrese de celule sau nume decâmpuri. Asupra acestui argument operează condiŃia specificată de argumentulurmător;

c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c c Ń Ń i i e e reprezintă o condiŃie impusă c c â â m m p pu u l l u u i i d d e e e e v v a a l l u u a a t t , care se specificăsub formă de şir de caractere (între ghilimele);

[ [ c c â â m m p p _ _ d d e e _ _ c c a a l l c c u u l l a a t t _ _ m m e e d d i i a a ] ] este argumentul care desemnează câmpul pe care se vacalcula media aritmetică, în funcŃie de evaluarea condiŃiei precizate de al doileaargument. Dacă acest argument lipseşte, c c â â m m p pu u l l d d e e c c a a l l c c u u l l a a t t m m e e d d i i a a va fi chiar c c â â m m p pu u l l d d e e e e v v a a l l u u a a t t .


Media aritmetică a elementelor unei liste în funcŃie de evaluarea unei condiŃii precizate.

CCaallccuullaarreeaa uunneeii mmeeddiiii aarriittmmeettiiccee î î nn f f uunnccţţiiee ddee eevvaalluuaarreeaa mmaaii mmuullttoorr ccoonnddiiţţiiii..

FFuunnccŃŃiiaa A A V V EER R A A GGEEIIFFSS

SSiinnttaaxxăă == A A V V EER R A A GGEEIIFFSS((c c â â m m p p _ _d d e e _ _c c a a l l c c u u l l a a t t _ _m m e e d d i i a a ; ; c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t _ _1 1 ; ;


c c â â m m p p _ _d d e e _ _c c a a l l c c u u l l a a t t _ _m m e e d d i i a a este argumentul care desemnează câmpul pe care va ficalculată media în funcŃie de evaluarea condiŃiei sau condiŃiilor precizate.

c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t _ _1 1 reprezintă o listă compusă din adrese de celule sau nume decâmpuri. Asupra acestui argument operează condiŃia specificată de argumentulurmător;

c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c cŃ i i e e _ _1 1 reprezintă o condiŃie impusă c c â â m m p pu u l l u u i i d d e e e e v v a a l l u u a a t t _ _ 11, care se

7/13/2019 Tao




specifică sub formă de egalitate sau restricŃie (între ghilimele);R R eezzuullttaatt 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 şiprenumele (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 aaf f eerreennttee uunneeii sseerriiii ssttaattiissttiiccee ddee ddaattee..

FFuunnccŃŃiiaa GGEEOOMMEE A A NN

SSiinnttaaxxăă ==GGEEOOMMEE A A NN((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))



Media geometrică dintr-o distribuŃie statistică de date.

FFuunnccŃŃiiaa HH A A R R MMEE A A NN

SSiinnttaaxxăă ==HH A A R R MMEE A A NN((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))



Media armonică dintr-o distribuŃie statistică de date.

IIddeennttiif f iiccaarreeaa uunneeii vvaalloorrii î î nn f f uunnccţţiiee ddee oorrddiinnuull ddee mmăărriimmee aall aacceesstteeiiaa..

7/13/2019 Tao




FFuunnccŃŃiiaa LL A A R R GGEE

SSiinnttaaxxăă ==LL A A R R GGEE((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ; ; n n u u m m ă ă r r ))

l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e reprezintă o listă compusă din constante numerice, celule,formule sau nume de câmpuri.

n n u u m m ă ă r r 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 caargument.


A n-a valoare ca mărime în sens descrescător, plecând de la valoareamaximală.

FFuunnccŃŃiiaa SSMM A A LLLL

SSiinnttaaxxăă ==SSMM A A LLLL((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ; ; n n u u m m ă ă r r ))


formule sau nume de câmpuri. n n u u m m ă ă r r reprezintă o valoare de clasare în sens descrescător.R R eezzuullttaatt 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 aaf f eerreennttee uunneeii sseerriiii ddee ddaattee ssttaattiissttiiccee..

FFuunnccŃŃiiaa MMEEDDII A A NN

SSiinnttaaxxăă ==MMEEDDII A A NN((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))

l l i i s s t t ă ă_d d e e_e e l l e e m m e e n n t t e e reprezintă o listă compusă din constante numerice, celule,

7/13/2019 Tao




formule sau nume de câmpuri.R R eezzuullttaatt rreettuurrnnaatt

Mediana distribuŃiei statistice a elementelor listei.


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 dateloreste 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 deelemente aferent distribuŃiei este par, atunci se calculează media între ultimul element al primeijumătăŃi şi primul element al ultimei jumătăŃi, altfel se ia ca referinŃă elementul situat la jumătateadistribuŃ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 f f rreeccvveennttee aappaarriiţţiiii ddiinnttrr--oo ddiissttrriibbuuţţiiee ssttaattiissttiiccăă ddee ddaattee..

FFuunnccŃŃiiaa MMOODDEE

SSiinnttaaxxăă ==MMOODDEE((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))


7/13/2019 Tao





Valoarea cea mai frecventă dintr-o distribuŃie statistică de date.


Dacă distribuŃia statistică de date nu conŃine valori duplicate, funcŃia MODE va returna un mesaj deeroare de tipul „Not Available” (#N/A!);Dacă distribuŃia statistică de date conŃine mai multe seturi de valori duplicate, egale ca frecvenŃă deapariŃ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, acesteanu vor fi incluse la calculul celei mai frecvente apariţii.

Dacă respectiva serie statistică conŃine valoarea zero, valorile acesteia intră în calculul pe careoperează funcŃia MODE;

b. Este de recomandat ca atunci când se calculează cea mai frecventă apariţie a unei valorinumerice într-o distribuţie statistică de date, aceasta să poată fi localizată vizual printr-oformatare 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 aaf f eerreennttee uunneeii ddiissttrriibbuuţţiiii ssttaattiissttiiccee..

FFuunnccŃŃiiiillee CCOOUUNN T T // CCOOUUNN T T A A

SSiinnttaaxxăă ==CCOOUUNN T T // CCOOUUNN T T A A ((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))



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.

7/13/2019 Tao




bb.. FuncŃia COUNTA ia în considerare alături de elementele numerice ale unei distribuŃii statistice şicelulele 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 suntintercalate între elementele nevide ale distribuŃiei statistice.

CCaallccuullaarreeaa nnuummăărruulluuii ddee eelleemmeennttee aaf f eerreennttee uunneeii ddiissttrriibbuuţţiiii ssttaattiissttiiccee,, î î nn f f uunnccţţiiee ddee eevvaalluuaarreeaa uunneeii ccoonnddiiţţiiii..

FFuunnccŃŃiiaa CCOOUUNN T TIIFF

SSiinnttaaxxăă ==CCOOUUNN T TIIFF((c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t ; ; c c r r i i t t e e r r i i u u _ _d d e e _ _s s e e l l e e c c Ń Ń i i e e ))

c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t reprezintă o listă compusă din constante numerice, celule,formule sau nume de câmpuri;

c c r r i i t t e e r r i i u u _ _d d e e _ _s s e e l l e e c c Ń Ń i i e e reprezintă o condiŃie care se specifică sub formă de şir decaractere (între ghilimele).


Numărul de elemente dintr-o serie de date în funcŃie de precizarea uneicondiŃii.

CCaallccuullaarreeaa nnuummăărruulluuii ddee eelleemmeennttee aaf f eerreennttee uunneeii ddiissttrriibbuuţţiiii ssttaattiissttiiccee,, î î nn f f uunnccţţiiee ddee eevvaalluuaarreeaa uunneeii mmaaii mmuullttoorr ccoonnddiiţţiiii..

FFuunnccŃŃiiaa CCOOUUNN T TIIFFSS

SSiinnttaaxxăă ==CCOOUUNN T TIIFFSS((c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t _ _1 1 ; ;


c c â â m m p p _ _d d e e _ _e e v v a a l l u u a a t t _ _1 1 reprezintă o listă compusă din adrese de celule sau nume decâmpuri. Asupra acestui argument operează condiŃia specificată de argumentulurmător;

c c r r i i t t e e r r i i u u l l _ _s s e e l l e e c c Ń Ń i i e e _ _1 1 reprezintă o condiŃie impusă c c â â m m p pu u l l u u i i d d e e e e v v a a l l u u a a t t _ _ 11, care sespecifică sub formă de egalitate sau restricŃie (între ghilimele);


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.

7/13/2019 Tao




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

7/13/2019 Tao




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 FFR R EEQQUUEENNCC Y Y

SSiinnttaaxxăă == FFR R EEQQUUEENNCC Y Y ((t t a a b b l l o o u u _ _d d e e _ _v v a a l l o o r r i i ; ; c c o o l l o o a a n n ă ă _ _i i n n t t e e r r v v a a l l e e _ _v v a a l l o o r r i i )) t t a a b b l l o o u u _ _d d e e _ _v v a a l l o o r r i i reprezintă seria de valori pentru care va fi calculată frecvenŃa pe

clase (grupuri). Datele numerice pot fi organizate pe mai multe coloane ceformează un tablou;

c c o o l l o o a a n n ă ă _ _i i n n t t e e r r v v a a l l e e _ _v v a a l l o o r r i i identifică pe coloană valorile limitelor intervalelor ceaparŃin claselor de valori. Prima valoare (V 1 ) aparŃine intervalului (-∞,V 1 ], a doua valoare (V 2 ) aparŃine intervalului (V 1,V 2 ] şi aşa mai departe, până la ultima valoare.


Numărul de valori ce partajează aceleaşi caracteristici de clasă (calculeazănumărul de apariŃii corespunzătoare unui interval de valori precizat).

OObbsseerr v v aaŃŃiiii.

aa.. FuncŃia FREQUENCY trebuie introdusă în forma matriceală completă (validarea formulei seface 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 rezervatinstrumentelor 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) esteprezentat un exemplu de analiză a calificativelor şi a distribuŃiei notelor ce rezultă în urma unuiexamen.

7/13/2019 Tao




Figura 37 Exemplu de utilizare a funcţiei FREQUENCY CCaallccuullaarreeaa qquuaarrttiilleelloorr llaa oo vvaallooaarree ccee rreeggrruuppeeaazzăă 2255%%,, 5500%%,, 7755%% ssaauu 110000%% ddiinn eef f eeccttiivvuull ddaatteelloorr

oobbsseerrvvaattee..

FFuunnccŃŃiiaa QQUU A A R R T TIILLEE

SSiinnttaaxxăă == QQUU A A R R T TIILLEE ((t t a a b b l l o o u u _ _d d e e _ _v v a a l l o o r r i i ; ; q q u u a a r r t t i i l l ă ă ))

t t a a b b l l o o u u _ _d d e e _ _v v a a l l o o r r i i reprezintă ansamblul datelor asupra cărora se va calcula quartila( Quart );

q q u u a a r r t t i i l l ă ă semnifică numărul de quartilă de calculat: 1=25% din efectiv, 2=50% dinefectiv, 3=75% din efectiv şi 4=100% din efectiv (valoarea zero reprezintă valoareaminimă a seriei de valori).


O valoare ce regrupează un anumit procent din efectivul datelor observate.

OObbsseerr v v aaŃŃiiee. FuncŃia QUARTILE se introduce matriceal.

Un exemplu de utilizare a funcŃiilor FREQUENCY şi QUARTILE este prezentat în Figura 38. Înexemplul 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âmpulB73: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 fiecarequartilă în parte.

Figura 38 Exemplu de utilizare a funcţiilor FREQUENCY şi QUARTILE CCaallccuullaarreeaa vvaarriiaannţţeeii ssaauu aa iinnddiicceelluuii ddee ddiissppeerrssiiee aaf f eerreenntt uunneeii ddiissttrriibbuuţţiiii..

FFuunnccŃŃiiiillee V V A A R R PP // V V A AR R PP A A // V V A A R R // V V A AR R A A

SSiinnttaaxxăă == V V A A R R PP // V V A AR R PP A A // V V A A R R // V V A AR R A A ((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))

7/13/2019 Tao






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Ńieignoră în argumentele sale, elementele de tip şir de caractere şi valorile logice. Dacă se doreşte caaceste 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 seutilizează 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 SS T TDDEE V V PP // SS T TDDEE V V PP A A // SS T TDDEE V V // SS T TDDEE V V A A

SSiinnttaaxxăă == SS T TDDEE V V PP // SS T TDDEE V V PP A A // SS T TDDEE V V //SS T TDDEE V V A A ((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ))



Abaterea standard înregistrată într-o distribuŃie statistică sub formă delistă.

ObservaŃii.

aa..

Indicatorul „Standard Deviation” calculează abaterea-standard (ecartul-tip) prin extragerearădăcinii pătrate din varianŃă. Altfel spus, funcŃia calculează pentru o populaŃie statistică de datemedia abaterilor înregistrate între datele unei serii statistice. În această accepŃiune, abatereastandard 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ă seutilizează funcŃia STDEVP. Această funcŃie ignoră valorile logice ( True, False ), şirurile decaractere şi celulele vide. Pentru ca acestea să fie luate în consideraŃie, se utilizează funcŃiaSTDEVPA , 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Ńieistatistice se utilizează funcŃia STDEV . Această funcŃie ignoră în argumentele sale, elementele detip şir de caractere şi valorile logice. Dacă se doreşte ca aceste elemente să nu fie ignorate, seutilizează funcŃia STDEVA .

Figura 39 ilustrează modul de utilizare a funcŃiilor VARP, VAR , STDEVP şi STDEV . Pentru caaceste funcŃii statistice să fie relevante, a fost necesară generarea unei populaŃii statistice pentru cadistribuŃ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 şi500, astfel încât distribuŃia statistică să fie aproximativ gauss-iană.

7/13/2019 Tao




Figura 39 Exemplu de utilizare a funcţiilor STDEVP, STDEV, VARP, VAR

CCaallccuullaarreeaa ccoovvaarriiaannţţeeii..

FFuunnccŃŃiiaa CCOO V V A A R R

SSiinnttaaxxăă == CCOO V V A A R R ((t t a a b b l l o o u u _ _1 1 ; ; t t a a b b l l o o u u _ _2 2 ))

t t a a b b l l o o u u _ _1 1 şi t t a a b b l l o o u u _ _2 2 reprezintă două serii statistice de date, valori numerice saunume de câmpuri ce conŃin valori numerice întregi.

R R eezzuullttaatt

rreettuurrnnaatt

Un coeficient ce semnifică media produselor abaterilor a două serii de

date.OObbsseerr v v aaŃŃiiee.. CovarianŃa calculează media produselor abaterilor pentru fiecare pereche de puncte dedate. FuncŃia se introduce ca formulă matriceală şi se utilizează pentru a determina relaŃia dintre 2seturi de date.

Exemplul din Figura 40 calculează covarianŃa pentru 2 serii de valori ce reprezintă notele studenŃilorla examenul de informatică.

Figura 40 Exemplu de utilizare a funcţiei COVAR

7/13/2019 Tao




MMăăssuurraarreeaa ggrraadduulluuii ddee ddeeppeennddeennţţăă î î nnttrree ddoouuăă vvaarriiaabbiillee pprriinn iinntteerrmmeeddiiuull ccooeef f iicciieennttuulluuii ddee ccoorreellaaţţiiee..

FFuunnccŃŃiiaa CCOOR R R R EELL

SSiinnttaaxxăă == CCOOR R R R EELL((m m a a t t r r i i c c e e _ _1 1 ; ; m m a a t t r r i i c c e e _ _2 2 ))

m m a a t t r r i i c c e e _ _1 1 reprezintă prima variabilă susceptibilă de a fi explicată prin intermediul valorilor celei de a doua variabile. Această variabilă reprezintă parametrul calitativ,de explicat;

m m a a t t r r i i c c e e _ _2 2 reprezintă valorile celei de a doua variabile susceptibile de a influenŃaprima variabilă. Această variabilă reprezintă parametrul cantitativ, explicativ.


Un coeficient ce semnifică gradul de dependenŃă între 2 variabile. Cu câtcoeficientul de corelaŃie se apropie de 1, cu atât există o mai bună corelareîntre cele două variabile.

OObbsseerr v v aaŃŃ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 coeficientde corelaŃie care se apropie de valoarea +1 indică o puternică dependenŃă între fenomeneleobservate. 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 decorelaŃie care se apropie de zero indică o slabă corelaŃie intre cele două variabile. Atunci când oecuaŃ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 încelula E6 indică o la fel de slabă dependenŃă între codul studentului şi nota obŃinută la examenul deinformatică (valoare = 0,0830).

7/13/2019 Tao




Figura 41 Exemplu de utilizare a funcţiei CORRELCoeficientul 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 comenzise 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 vacuprinde doar valorile numerice. Dacă se doreşte includerea capului de tabel în selecţie, se va bifaopţ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 instrumentelorData Analysis.

EExxttrraappoollăărrii şşii pprreevviizziiuunnii pprriinn f f uunnccţţiiii ssttaattiissttiiccee..

FFuunnccŃŃiiaa LLIINNEESS T T

SSiinnttaaxxăă == LLIINNEESS T T((Y Y _ _c c u u n n o o s s c c u u t t ; ; X X _ _c c u u n n o o s s c c u u t t ; ; c c o o n n s s t t a a n n t t ă ă ; ; s s t t a a t t i i s s t t i i c c i i ))

Y Y _ _c c u u n n o o s s c c u u t t reprezintă o serie de valori observate (cifra de afaceri). Această variabilă reprezintă parametrul calitativ, de explicat;

X X _ _c c u u n n o o s s c c u u t t reprezintă o serie de valori ce explică variaŃiile (de exemplu cheltuielilecu publicitatea). Această variabilă reprezintă parametrul cantitativ, explicativ;

c c o o n n s s t t a a n n t t ă ă semnifică o valoare logică TRUE dacă valoarea b a ecuaŃiei y=ax+btrebuie să fie egală cu zero. În caz contrar, constanta va identifica o valoare logică deFALSE;

s s t t a a t t i i s s t t i i c c i i indică prin valoarea logică TRUE dacă se vor calcula informaŃiisuplimentare, iar dacă nu se doreşte acest lucru, valoarea logică va fi FALSE.


CoeficienŃii a şi b aferenŃi ecuaŃiei dreptei y=ax+b prin care se poate stabiligradul de dependenŃă între 2 variabile.

7/13/2019 Tao




OObbsseerr v v aaŃŃiiii..

aa.. FuncŃia LLIINNEESS T T calculează coeficienŃii ecuaŃiei dreptei prin metoda celor mai mici pătrate. Oastfel 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 dedreaptă. 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 cereprezintă corelaŃia la nivelul unei întreprinderi între cifra de afaceri şi cheltuielile publicitare pentrugenerarea acesteia.

Figura 43 Exemplu de utilizare a funcţiei LINEST

FFuunnccŃŃiiaa T TR R EENNDD

SSiinnttaaxxăă == T TR R EENNDD((Y Y _ _c c u u n n o o s s c c u u t t ; ; X X _ _c c u u n n o o s s c c u u t t ; ; X X _ _n n o o u u [ [ ; ; c c oon n s s t t a a n n t t ă ă ] ] ))

Y Y _ _c c u u n n o o s s c c u u t t reprezintă o serie de valori ale variabilei de explicat (de exemplu, cifrade afaceri este explicată prin cheltuielile cu publicitatea);

X X _ _c c u u n n o o s s c c u u t t reprezintă o serie de valori ale variabilei explicative (cheltuielile cupublicitatea vor explica creşterea cifrei de afaceri);

X X _ _n n o o u u semnifică noile date plecând de la care se va construi extrapolarea; [ [ c c oon n s s t t a a n n t t ă ă ] ] 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ă deFALSE.


Valoarea extrapolată. Rezultatul calculat reprezintă o informaŃie brutădatorită faptului că mulŃi factori nu sunt luaŃi în consideraŃie la extrapolare.

OObbsseerr v v aaŃŃiiee.. FuncŃia TREND se introduce matriceal.

FFuunnccŃŃiiaa FFOOR R EECC A A SS T T

SSiinnttaaxxăă == FFOOR R EECC A A SS T T((X X _ _n n o o u u ; ; Y Y _ _c c u u n n o o s s c c u u t t ; ; X X _ _c c u u n n o o s s c c u u t t ))

X X _ _n n o o u u semnifică reperul de date pentru care se doreşte anticiparea unei valori

Y Y _ _c c u u n n o o s s c c u u t t reprezintă o serie de valori ale variabilei explicative sau dependente;

X X _ _c c u u n n o o s s c c u u t t reprezintă o serie de valori ale variabilei explicative sau independente.

R R eezzuullttaatt rreettuurrnnaatt Valoarea extrapolată.

OObbsseerr v v aaŃŃ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 peperioada 2009-2010 a cheltuielilor cu publicitatea (prin FORECAST ) şi a cifrei de afaceri.

7/13/2019 Tao




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((c c o o n n d d i i Ń Ń i i e e ; ; r r e e z z u u l l t t a a t t _ _1 1 ; ; r r e e z z u u l l t t a a t t _ _2 2 ))

c c o o n n d d i i Ń Ń i i e e este o expresie de comparaŃie ce utilizează unul dintre operatorii decomparaŃie: =, >, >=, <, <=, <>. Prin aceşti operatori se poate compara unelement cu o constantă, sau două elemente între ele. Elementele care fac obiectulunei 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 deevaluare.

r r e e z z u u l l t t a a t t _ _1 1 specifică o valoare numerică, un şir de caractere sau o formulă;

r r e e z z u u l l t t a a t t _ _2 2 specifică aceleaşi elemente ca şi r r e e z z u u l l t t a a t t _ _ 11.R R eezzuullttaatt rreettuurrnnaatt

R R e e z z u u l l t t a a t t _ _ 11 dacă expresia condiŃională este evaluată ca adevărată saur r e e z z u u l l t t a a t t _ _ 2 2 dacă expresia condiŃională este evaluată ca falsă.


FuncŃia IF testează argumentul c c oon n d d i i Ń Ń i i e e şi în funcŃie de rezultatul evaluării logice, generează r r e e z z u u l l t t a a t t _ _ 11 dacă condiŃia este adevărată sau r r e e z z u u l l t t a a t t _ _ 2 2 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 suntconstante de tip dată calendaristică, se recomandă ca acestea să se abordeze fie prin intermediul unorfuncŃii specifice ( DATE, DATEVALUE, etc.), fie prin accesarea lor printr-o referinŃă celulară sauprintr-un nume de câmp.Dacă r r e e z z u u l l t t a a t t e e l l e e ( 11 şi/sau 2 2 ) evaluării condiŃionale se doresc a fi prezentate în valori booleene (logice),se vor utiliza funcŃiile TRUE() şi/sau FALSE(). Dacă argumentul c c oon n d d i i Ń Ń i i e e testează o valoare logică, se va utiliza funcŃia ISLOGICAL ce interceptează o valoare booleană returnată de o funcŃie. Figura 45prezintă două exemple de utilizare a valorilor booleene într-o funcŃie condiŃională (primul exempluevaluează o condiŃie simplă şi returnează în celula D20 una dintre cele două valori booleene, iar al

7/13/2019 Tao




doilea exemplu testează în condiŃie o valoare booleană şi returnează în celula D21 un rezultat cereprezintă tot o valoare logică).

Figura 45 Utilizarea expresiilor booleene în funcţiile condiţionalea. În condiŃiile în care din sintaxa funcŃiei IF, lipsesc alternativ argumentele r r e e z z u u l l t t a a t t _ _ 11 sau r r e e z z u u l l t t a a t t _ _ 2 2

se returnează următoarele valori, conform exemplului prezentat în Figura 46.

Dacă unul dintre argumentele r r e e z z u u l l t t a a t t _ _ 11 sau r r e e z z u u l l t t a a t t _ _ 2 2 sunt omise şi înlocuite cuseparatorul de argumente, funcŃia IF va returna valoarea zero, în condiŃiile în care evaluareacondiŃiei conduce către unul dintre rezultate lipsă. Altfel spus, dacă evaluarea condiŃieiconduce către un rezultat „adevărat ” şi lipseşte din sintaxa funcŃiei IF, fie argumentulr r e e z z u u l l t t a a t t _ _ 2 2 , fie r r e e z z u u l l t t a a t t _ _ 11 (IF(condiŃie;r r e e z z u u l l t t a a t t _ _ 11; ) sau IF(condiŃie; ; ; ; r r e e z z u u l l t t a a t t _ _ 2 2 ;)), acestea fiindînlocuite cu separatorul de argumente (punct şi virgulă sau virgulă1 ), funcŃia de evaluare vareturna valoarea zero.Figura 46 ilustrează cazul în care celula C25 conŃine funcŃia ce returnează r r e e z z u u l l t t a a t t _ _ 2 2 care estesubstituit cu punct şi virgulă.

Celula C26 conŃine rezultatul returnat de funcŃia IF, corespunzător evaluării condiŃiei careangajează logic execuŃia argumentului r r e e z z u u l l t t a a t t _ _ 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 r r e e z z u u l l t t a a t t _ _ 11 sau r r e e z z u u l l t t a a t t _ _ 2 2 sunt omise (Figura 46, celulaC27), funcŃia IF va returna valoarea logică de „ fals ” în condiŃiile în care evaluarea condiŃieiconduce către unul dintre rezultate lipsă. În mod contrar, dacă evaluarea condiŃiei conduce laactivarea unui argument existent ( r r e e z z u u l l t t a a t t _ _ 11 sau r r e e z z u u l l t t a a t t _ _ 2 2 ), acesta va fi executat (celula C27).

În locul argumentului r r e e z z u u l l t t a a t t _ _ 2 2 pot fi imbricate alte structuri condiŃionale IF, generându-se potrivitcondiŃiilor specificate ulterior o substituire în „cascadă ”. Această substituire ilustrată în Figura 47, areca efect calcularea pe fiecare interval a argumentelor: r r e e z z u u l l t t a a t t _ _ 11..11;r r e e z z u u l l t t a a t t _ _ 11, …, r r e e z z u u l l t t a a t t _ _ 11..n n .. Potrivitunei analize logice, pot exista 3 demersuri ale imbricării funcŃiilor condiŃionale, în ceea ce priveşteabordarea intervalelor: logica crescătoare, descrescătoare şi între intervale.Logica crescătoare presupune testarea a valorilor condiŃiei cu valori de comparaŃie dispuse însuccesiune 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ă aurmătoarelor 2 figuri).Logica descrescătoare presupune testarea a valorilor condiŃiei cu valori de comparaŃie dispuse însuccesiune 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

7/13/2019 Tao




egal” (>=), iar valoarea de comparaŃie va fi din ce în ce mai mică (fapt ilustrat în partea inferioară aurmătoarelor 2 figuri).

=IF( B B 3 3 2 2<< 3 3;00;IF(( B B 3 3 2 2<< 5 5;CC3322**55%%;IF(( B B 3 3 2 2<<1100;CC3322**1100%%;IF(( B B 3 3 2 2<<11 5 5;CC3322**11

55%%;IF( B B 3 3 2 2<< 2 200;CC3322**2200%%;CC3322**2255%%))))))))

=IF( B B 3 3 3 3>> 2 200;CC3333**2255%%;IF( B B 3 3 3 3>>11 5 5;CC3333**2200%%;IF( B B 3 3 3 3>>1100;CC3333**1155%%;IF( B B 3 3

3 3>> 5 5;CC3333**1100%%;IF( B B 3 3 3 3>> 3 3;CC3333**55%%;00)))))

==IIFF(( c c o o n n d d ii ţ ţ iiee__11 ; ; r ree z zuul l t t a a t t__11 ; ;____________________________))))))))

==IIFF(( c c o o n n d d ii ţ ţ iiee__ 2 2 ; ; r ree z zuul l t t a a t t__11 . .11 ; ;__________________

==IIFF(( c c o o n n d d ii ţ ţ iiee__ 3 3 ; ; r ree z zuul l t t a a t t__11 . . 2 2 ; ;________________

==IIFF(( c c o o n n d d ii ţ ţ iiee__ n n ; ; r ree z zuul l t t a a t t__11 . . n n ; ; r ree z zuul l t t a a t t__ 2 2 . . n n

r ree z zuul l t t a a t t__ 2 2 Testare

crescătoare

Testare

descrescătoare

Figura 47 Imbricarea structurilor condiŃionale prin substituirea „în cascadă” a argumentuluirezultat_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 altestructuri condiŃionale

Logica testării explicite a intervalelor presupune verificarea pentru fiecare imbricare (a funcŃieiIF ), a valorilor condiŃiei pentru fiecare interval în parte. Această tehnică se implementează cu ajutorulfuncŃiei logice AND (bornă_minimă; bornă_maximă).

EEvvaalluuaarreeaa llooggiiccăă pprriinn f f uunnccţţiiiillee „„ŞŞII”” // „„SSAAUU””..

FFuunnccŃŃiiaa AANNDD

SSiinnttaaxxăă == A A NNDD((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ; ; …………))

7/13/2019 Tao




l l i i s s t t ă ă_d d e e_ e e l l e e m m e e n n t t e e reprezintă o înşiruire de elemente ce vor fi evaluate logic.R R eezzuullttaatt rreettuurrnnaatt

T TR R UUEE dacă toate elementele din lista de argumentele sunt adevărate.FF A A LLSSEE dacă cel puŃin un element din lista de argumentele nu se verifică(se evaluează ca fals).


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 elementnumeric î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<x x <nn, unde x x este o valoare strict maimare 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 defuncŃ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 Figura49.

Figura 49 Exemple de utilizare a funcţiei ANDFFuunnccŃŃiiaa OORR

SSiinnttaaxxăă ==OOR R ((l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e ; ; …………))

l l i i s s t t ă ă_d d e e_ e e l l e e m m e e n n t t e e reprezintă o înşiruire de elemente ce vor fi evaluate logic.


T TR R UUEE dacă cel puŃin un element din lista de argumentele este evaluat caadevărat.

FF A A LLSSEE dacă nici un element din lista de argumentele nu este evaluat caadevă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Ńinutuluiunor celule, cu ajutorul funcŃiilor de informare. Nu în ultimul rând, este de menŃionat că funcŃiaOR 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) alefuncŃiei AND.

IInnvveerrssaarreeaa uunneeii eevvaalluuăărrii llooggiiccee..

FFuunnccŃŃiiaa NNOOTT

SSiinnttaaxxăă ==NNOO T T((e e v v a a l l u u a a r r e e _ _l l o o g g i i c c ă ă ))

7/13/2019 Tao




e e v v a a l l u u a a r r e e _ _l l o o g g i i c c ă ă reprezintă rezultatul unei evaluări condiŃionale. De regulă,evaluarea logică îmbracă forma unei expresii ce conŃine o funcŃie logică.


Inversul valorii logice a argumentului.

VVaalloorrii llooggiiccee ddee „„aaddeevvăărraatt”” ssaauu „„f f aallss””..

FFuunnccŃŃiiaa TTRRUUEE

SSiinnttaaxxăă == T TR R UUEE(())


Valoarea logică de „a a d d e e v v ă ă r r ”.

FFuunnccŃŃiiaa FFAALLSSEE

SSiinnttaaxxăă ==FF A A LLSSEE(())


Valoarea logică de „ f f a a l l s s ”.

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 iinnf f oorrmmaarree ((IInnf f oorrmmaattiioonn))

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 iinnf f oorrmmaarree 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,

7/13/2019 Tao




False ), un şir de caractere , o valoare numerică , etc. De asemenea, argumentul unei funcŃii de informarepoate fi o expresie sau chiar o altă funcŃie de regulă una de consultare (de exempluISNA ( 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 IISSBBLL A A NNK K

SSiinnttaaxxăă == IISSBBLL A A NNK K ((X X ))

X X este referinŃa unei celule.R R eezzuullttaatt 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 IISSNNUUMMBBEER R

SSiinnttaaxxăă == IISSNNUUMMBBEER R ((X X )) X X este referinŃa unei celule.R R eezzuullttaatt 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 IISS T TEEXX T T

SSiinnttaaxxăă == IISS T TEEXX T T((X X ))

XX este referinŃa unei celule.R R eezzuullttaatt 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 IISSNNOONN T TEEXX T T

SSiinnttaaxxăă == IISSNNOONN T TEEXX T T((X X ))

X X este referinŃa unei celule.R R eezzuullttaatt

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 IISSLLOOGGIICC A A LL

SSiinnttaaxxăă == IISSLLOOGGIICC A A LL((X X ))

X X este referinŃa unei celule sau a unei expresii de evaluare.

7/13/2019 Tao





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((X X ))

X X este referinŃa unei celule sau o expresie de calcul.R R eezzuullttaatt 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 IISSEE V V EENN

SSiinnttaaxxăă == IISSEE V V EENN((X X ))

X X este referinŃa unei celule sau o expresie de calcul.


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 f f oorrmmuullee ccaarree rreettuurrnneeaazzăă vvaalloorrii ddee eerrooaarree..

FFuunnccŃŃiiaa IISSR R EEFF

SSiinnttaaxxăă == IISSR R EEFF((X X ))

X X este referinŃa unei celule sau o expresie de calcul.R R eezzuullttaatt rreettuurrnnaatt

True dacă în foaia de calcul există referinŃa testată;

False dacă în foaia de calcul nu există referinŃa testată.

FFuunnccŃŃiiaa IISSEER R R R OOR R verifică dacă referinŃa definită de argument conŃine o valoarede 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ăă == IISSEER R R R OOR R ((X X ))

X X este referinŃa unei celule sau o expresie de calcul sau evaluare.R R eezzuullttaatt 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 IISSEER R R R verifică dacă referinŃa definită de argument conŃine o valoare deeroare cu excepŃia valorii #N/A , ce a fost returnată de o formulă şi/sau ofuncŃie.

SSiinnttaaxxăă == IISSEER R R R ((X X ))

X X este referinŃa unei celule sau o expresie de calcul sau evaluare.R R eezzuullttaatt True dacă referinŃa testată conŃine o valoare de eroare;

7/13/2019 Tao




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 IISSNN A A verifică dacă referinŃa definită de argument conŃine o valoare

nedisponibilă returnată ca eroare a unei funcŃii de consultare.

SSiinnttaaxxăă == IISSNN A A ((X X ))

XX este referinŃa unei celule sau o expresie definită de o funcŃie de consultare.R R eezzuullttaatt 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 ofuncŃie de consultare verticală.

Utilizarea funcţiilor ISNA şi ISERROR este exemplificată la nivelul acestui capitol în cadrul funcţiilorde 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ŃiilecondiŃ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 carese 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

7/13/2019 Tao




FFuunnccţţiiii ddee iinnf f oorrmmaarree ccee aaf f iişşeeaazzăă iinnf f oorrmmaaţţiiii rreef f eerriittooaarree 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((v v a a l l o o a a r r e e ))

v v a a l l o o a a r r e e reprezintă valoarea căreia i se va face conversia.R R eezzuullttaatt 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 odată 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ŃaconŃine orice altceva.

OObbsseerr v v aaŃŃiiee.. Datorită faptului că Excel face în mod automat conversia automată a valorilor, nu estenecesar a se utiliza explicit funcŃia N.Majoritatea cazurilor de utilizare vizează conversia din şir de caractere în zero şi din valori logice în 0sau 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 NN A A

SSiinnttaaxxăă ==NN A A (())

f f ă ă r r ă ă a a r r g g u u m m e e n n t t .R R eezzuullttaatt rreettuurrnnaatt

Mesajul de eroare „Not Available” (#N/A ).

NNuummăărraarreeaa cceelluulleelloorr vviiddee aaf f eerreennttee uunnuuii ccââmmpp ssppeecciif f iiccaatt..

FFuunnccŃŃiiaa CCOOUUNN T TBBLL A A NNK K

SSiinnttaaxxăă ==CCOOUUNN T TBBLL A A NNK K ((c c â â m m p p ))

c c â â m m p p specifică referinŃa sau numele unui câmpR R eezzuullttaatt rreettuurrnnaatt

Numărul de celule vide.

7/13/2019 Tao




OObbsseerr v v aaŃŃ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

IIddeennttiif f iiccaarreeaa eerroorriiii ggeenneerraattee ddee oo f f oorrmmuullăă,, pprriinnttrr--oo vvaallooaarree nnuummeerriiccăă..

FFuunnccŃŃiiaa EER R R R OOR R .. T T Y Y PPEE

SSiinnttaaxxăă ==EER R R R OOR R .. T T Y Y PPEE((t t i i p p _ _e e r r o o a a r r e e ))

t t i i p p _ _e e r r o o a a r r e e este o valoare de eroare, o adresă sau un nume de celulă ce conŃine o valoare de eroare specifică Excel.


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

OObbsseerr v v aaŃŃiiee.. FuncŃia ERROR.TYPE prezentată în Figura 55 este utilizată cu precădere atunci cândse doreşte testarea unor formule ce pot returna diferite valori de eroare:=IF(OR(ERROR.TYPE=1;ERROR.TYPE=2;ERROR.TYPE=3);…;…)

7/13/2019 Tao




Figura 55 Exemple de valori returnate de funcţia de informare ERROR.TYPE IIddeennttiif f iiccaarreeaa pprriinnttrr--oo vvaallooaarree nnuummeerriiccăă aa uunnuuii ttiipp ddee ddaattăă..

FFuunnccŃŃiiaa T T Y Y PPEE

SSiinnttaaxxăă == T T Y Y PPEE((t t i i p p _ _d d a a t t ă ă ))

t t i i p p _ _d d a a t t ă ă 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).

R R eezzuullttaatt 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ă.

7/13/2019 Tao




Figura 56 Exemple de utilizare a funcţiei de informare TYPE IInnf f oorrmmaaţţii ii ssuupplliimmeennttaarree rreef f eerriittooaarree oo rreef f eerriinnţţăă cceelluullaarrăă..

FFuunnccŃŃiiaa CCEELLLL

SSiinnttaaxxăă ==CCEELLLL((t t i i p p _ _i i n n f f o o r r m m a a Ń Ń i i e e [ [ ; ; r r e e f f e e r r i i n n Ń Ń ă ă ] ] ))

t t i i p p _ _i i n n f f o o r r m m a a Ń Ń i i e e este o valoare de tip text, care specifică tipul de informaŃiei desprecelula referită. Valorile acestui argument pot fi următoarele:

o "address " adresa celulei specificate. Dacă argumentul r r e e f f e e r r i i n n Ń Ń ă ă este uncâ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 argumentuluieturnează 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) aferentcoloanei.

[ [ r r e e f f e e r r i i n n Ń Ń ă ă ] ] specifică adresa celulei, coordonatele sau numele unui câmp, pentru carese doreşte aflarea unui tip de informaŃie. În condiŃiile în care acest argument esteopŃional, funcŃia CELL returnează informaŃii corespunzătoare celulei curente (încare este editată funcŃia).


Diferite informaŃii asupra referinŃei celulare, în funcŃie de valorile primuluiargument specificat.

Figura 57 exemplifică posibilităţile de utilizare a argumentului tip_informaţie, aferent funcţiei CELL.

7/13/2019 Tao




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 T TOODD A A Y Y

SSiinnttaaxxăă == T TOODD A A Y Y (())

f f ă ă r r ă ă a a r r g g u u m m e e n n t t e e .R R eezzuullttaatt rreettuurrnnaatt

Data curentă a sistemului, într-un format implicit sau specificat deutilizator prin formatare.

FFuunnccŃŃiiaa NNOOWW

SSiinnttaaxxăă ==NNOOWW(())

f f ă ă r r ă ă a a r r g g u u m m e e n n t t e e .R R eezzuullttaatt rreettuurrnnaatt

Data şi ora curentă a sistemului, într-un format implicit sau explicitspecificat de utilizator prin operaŃiunea de formatare.

MMaanniippuullaarreeaa î î nn f f oorrmmuullee aa ccoonnssttaanntteelloorr ddee ttiipp ddaattăă ccaalleennddaarriissttiiccăă..

FFuunnccŃŃiiaa DD A A T TEE V V A A LLUUEE

SSiinnttaaxxăă ==DD A A T TEE V V A A LLUUEE((„„ ş ş i i r r _ _d d e e _ _c c a a r r a a c c t t e e r r e e ” ” ))

ş ş i i r r _ _d d e e _ _c c a a r r a a c c t t e e r r e e este un text ce prezintă similitudini cu formatul unei datecalendaristice. În mod obligatoriu, şirul de caractere trebuie plasat între ghilimele.


Data calendaristică specificată în argument (sub formă de text), prezentatăîntr-un format tip dată calendaristică.

FFuunnccŃŃiiaa DD A A T TEE

7/13/2019 Tao




SSiinnttaaxxăă ==DD A A T TEE ((a a n n ; ; l l u u n n ă ă ; ; z z i i ))

a a n n este o valoare numerică cuprinsă între 1900 şi 9999;

l l u u n n ă ă este o valoare numerică cuprinsă între 01 şi 12; z z i i este o valoare numerică cuprinsă între 01 şi 31.

R R

eezz

uu

llttaa

tt rreettuurrnnaatt

Data calendaristică ce corespunde argumentelor specificate.

OObbsseerr v v aaŃŃ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 fiabordată 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 fostprezentată anterior, în cadrul funcŃiei condiŃionale IF.Figura 58 reprezintă grafic câteva exemple de utilizare a funcŃiilor prezentate, împreună cu formatelepersonalizate aferente.

Figura 58 Exemplu de utilizare a funcţiilor NOW, TODAY, DATEVALUE, DATE EExxttrraaggeerreeaa aannuulluuii ddiinnttrr--oo ddaattăă ccaalleennddaarriissttiiccăă..

FFuunnccŃŃiiaa Y Y EE A A R R

SSiinnttaaxxăă == Y Y EE A A R R ((n n u u m m ă ă r r _ _d d a a t t ă ă ))

n n u u m m ă ă r r _ _d d a a t t ă ă poate fi un număr serial ce corespunde unei date calendaristice, oadresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce estetratată printr-o funcŃie.


Anul (între 1 şi 9999) ce corespunde argumentului specificat.

EExxttrraaggeerreeaa lluunniiii ddiinnttrr--oo ddaattăă ccaalleennddaarriissttiiccăă..

FFuunnccŃŃiiaa MMOONN T THH

SSiinnttaaxxăă ==MMOONN T THH((n n u u m m ă ă r r _ _d d a a t t ă ă ))


7/13/2019 Tao





Luna (între 1 şi 12) ce corespunde argumentului specificat.

EExxttrraaggeerreeaa zzii lleeii ddiinnttrr--oo lluunnăă,, aaf f eerreennttăă uunneeii ddaattee ccaalleennddaarriissttiiccee..

FFuunnccŃŃiiaa DD A A Y Y

SSiinnttaaxxăă ==DD A A Y Y ((n n u u m m ă ă r r _ _d d a a t t ă ă ))



Numărul zilei din lună (între 1 şi 31) ce corespunde argumentuluispecificat.

EExxttrraaggeerreeaa nnuummăărruulluuii zziilleeii ddiinn ssăăppttăămmâânnăă,, aaf f eerreenntt uunneeii ddaattee ccaalleennddaarriissttiiccee..

FFuunnccŃŃiiaa WWEEEEK K DD A A Y Y

SSiinnttaaxxăă ==WWEEEEK K DD A A Y Y ((n n u u m m ă ă r r _ _d d a a t t ă ă ; ; [ [ c c oon n s s t t a a n n t t ă ă ] ] )) n n u u m m ă ă r r _ _d d a a t t ă ă 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 estetratată printr-o funcŃie:

[ [ c c oon n s s t t a a n n t t ă ă ] ] reprezintă o constantă, plecând de la care se calculează începutulsăptămânii:

o 1 (tip implicit) prima zi a săptămânii este Duminică, iar ultima zieste 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.

R R eezzuullttaatt

rreettuu

rrnn

aa

tt

Numărul zilei din săptămână, potrivit semnificaŃiei parametrului opŃionalt t i i p p::

o 1 =Duminică; 7=sâmbătă pentru t t i i p p=1;

o 1=luni; 7=Duminică pentru t t i i p p = 2;o 1=marŃi; 6=Duminică; 7=luni pentru ti i p = 3.

EExxttrraaggeerreeaa nnuummăărruulluuii ssăăppttăămmâânniiii ddiinnttrr--uunn aann,, aaf f eerreenntt uunneeii ddaattee ccaalleennddaarriissttiiccee..

FFuunnccŃŃiiaa WWEEEEK K NNUUMM

SSiinnttaaxxăă ==WWEEEEK K NNUUMM((n n u u m m ă ă r r _ _d d a a t t ă ă ; ; [ [ c c oon n s s t t a a n n t t ă ă ] ] ))

n n u u m m ă ă r r _ _d d a a t t ă ă poate fi un număr serial ce corespunde unei date calendaristice, oadresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce este

tratată printr-o funcŃie: [ [ c c oon n s s t t a a n n t t ă ă ] ] 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ă;


Numărul săptămânii dintr-un an.

7/13/2019 Tao




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 semestruExcel 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 textsegmente din data agregată.

Figura 61 Exemplu de transformare prin funcţii concatenate a datei calendaristice în

7/13/2019 Tao




şir de caractereUn 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ârziereFigura 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ări lor, în condiţiile utilizării

adreselor de celule.

7/13/2019 Tao




Figura 64 Exemplu de calcul al majorărilor de întârziere, utilizând adrese de celuleUn 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 ddiif f eerreennţţeeii ddee zziillee ccee sseeppaarrăă ddoouuăă ddaattee ccaalleennddaarriissttiiccee..

FFuunnccŃŃiiaa DD A A Y Y SS336600

SSiinnttaaxxăă ==DD A A Y Y SS336600((d d a a t t ă ă _ _d d e e b b u u t t ; ; d d a a t t ă ă s s f f â â r r ş ş i i t t ))

d d a a t t ă ă _ _d d e e b b u u t t poate fi un număr serial ce corespunde unei date calendaristice, oadresă de celulă ce conŃine o dată calendaristică sau o constantă de tip dată ce estetratată printr-o funcŃie;

d d a a t t ă ă_s s f f â â r r ş ş i i t t idem.R R eezzuullttaatt

rreettuurrnnaatt

Numărul de zile ce separă data de debut de data de sfârşit.


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 impuneformatarea numerică a rezultatului diferenţei.

7/13/2019 Tao




CCaallccuullaarreeaa ddiif f eerreennţţeeii ((zziillee,, lluunnii,, aannii)) ccee sseeppaarrăă ddoouuăă ddaattee ccaalleennddaarriissttiiccee..

FFuunnccŃŃiiaa DD A A T TEEDDIIFF

SSiinnttaaxxăă ==DD A A T TEEDDIIFF((d d a a t t ă ă s s f f â â r r ş ş i i t t ; ; d d a a t t ă ă _ _d d e e b b u u t t ; ; „„c c o o d d _ _f f o o r r m m a a t t _ _d d a a t t ă ă ” ” ))

d d a a t t ă ă _ _d d e e b b u u t t 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 estetratată printr-o funcŃie;

d d a a t t ă ă _ _s s f f â â r r ş ş i i t t idem.

c c o o d d _ _f f o o r r m m a a t t _ _d d a a t t ă ă _ _c c a a l l e e n n d d a a r r i i s s t t i i c c ă ă reprezintă simbolul componentelor unui formattip dată calendaristică: („d” = număr de zile, „m” = număr de luni, „y” = număr deani.


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 prinDATEDIF

CCaallccuullaarreeaa nnuummăărruulluuii ddee zziillee lluuccrrăăttooaarree ccee sseeppaarrăă ddoouuăă ddaattee ccaalleennddaarriissttiiccee..

FFuunnccŃŃiiaa NNEE T TWWOOR R K K DD A A Y Y SS SSiinnttaaxxăă ==NNEE T TWWOOR R K K DD A A Y Y SS((d d a a t t ă ă _ _d d e e b b u u t t ; ; d d a a t t ă ă s s f f â â r r ş ş i i t t [ [ ; ; s s ă ă r r b b ă ă t t oor r i i ] ] ))


d d a a t t ă ă _ _s s f f â â r r ş ş i i t t idem;

[ [ s s ă ă r r b b ă ă t t oor r i i ] ] este un câmp ce conŃine date calendaristice ce semnifică în general operioadă de inactivitate (indisponibilitate) ce este reglementată în funcŃie decontextul activităŃii utilizatorului (sărbători legale, vacanŃe, deplasări, sesiuni deexamene, etc.).


Numărul de zile lucrătoare (fără a lua în considerare week-end-urile şisărbătorile legale) dintre două date calendaristice.


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

7/13/2019 Tao




Figura 67 Exemplu de utilizare a funcţiei NETWORKDAYS

CCaallccuullaarreeaa uunneeii ddaattee ccaalleennddaarriissttiiccee vviiiittooaarree ddeeccaallaattee ccuu uunn nnuummăărr ddee zzii llee lluuccrrăăttooaarree..

FFuunnccŃŃiiaa WWOOR R K K DD A A Y Y

SSiinnttaaxxăă ==WWOOR R K K DD A A Y Y ((d d a a t t ă ă _ _d d e e b b u u t t ; ; n n u u m m ă ă r r _ _z z i i l l e e [ [ ; ; s s ă ă r r b b ă ă t t oor r i i ] ] ))


n n u u m m ă ă r r _ _z z i i l l e e reprezintă o constantă numerică, o adresă (sau un nume) de celulă ceconŃine valoarea numerică ce decalează în timp d d a a t t a a _ _ d d e e b b u u t t ;

[ [ s s ă ă r r b b ă ă t t oor r i i ] ] este un câmp ce conŃine date calendaristice ce semnifică în general operioadă de inactivitate (indisponibilitate) ce este reglementată în funcŃie decontextul activităŃii utilizatorului (sărbători legale, vacanŃe, deplasări, sesiuni deexamene, etc.).


O dată calendaristică viitoare, decalată cu n zile lucrătoare faŃă de o dată deiniŃ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 dezile 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 1Un 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).

7/13/2019 Tao




Figura 69 Exemplu de utilizare a funcţiei WORKDAY: aplicaţia 2

CCaallccuullaarreeaa uullttiimmeeii zziillee ddiinn lluunnăă..

FFuunnccŃŃiiaa EEOOMMOONN T THH

SSiinnttaaxxăă ==EEOOMMOONN T THH((d d a a t t ă ă _ _d d e e b b u u t t ; ; + + / / f f a a c c t t o o r r _ _d d e e c c a a l l a a j j ))


f f a a c c t t o o r r _ _d d e e c c a a l l a a j j reprezintă numărul de luni (constantă numerică pozitivă/negativă)de decalare în avans/urmă, faŃă de o dată de referinŃă.


O dată calendaristică ce reprezintă ultima zi din lună.


În Excel, funcŃia EOMONTH (ca şi NETWORKDAYS sau WORKDAY ) nu este disponibilă înmod 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 carerezultatele returnate sunt formatate personalizat cu ziua săptămânii, deoarece este evident pentru toŃiutilizatorii 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 d d a a t t a a _ _ d d e e b b u u t t ca referinŃă absolută sau să fie identificată prin nume. Această observaŃie este utilă, deoarece în cazul

utilizării unei referinŃe relative pentru d d a a t t a a _ _ d d e e b b u u t t , aceasta se va decala cu câte o celulă la procesul decopiere 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ă).

7/13/2019 Tao




Figura 70 Exemplu de utilizare a funcţiei EOMONTH

CCaallccuullaarreeaa uunneeii ddaattee ccaalleennddaarriissttiiccee,, ddeeccaallaattee ccuu ++nn lluunnii ssaauu – –nn lluunnii,, f f aaţţăă ddee oo ddaattăă ddee rreef f eerriinnţţăă..

FFuunnccŃŃiiaa EEDD A A T TEE

SSiinnttaaxxăă ==EEDD A A T TEE((d d a a t t ă ă _ _d d e e b b u u t t ; ; + + / / - - f f a a c c t t o o r r _ _d d e e c c a a l l a a j j ))


f f a a c c t t o o r r _ _d d e e c c a a l l a a j j reprezintă numărul de luni (constantă numerică pozitivă/negativă)de decalare în avans/urmă, faŃă de o dată de referinŃă.


O dată calendaristică decalată cu un număr de luni (exprimate într-oaccepŃiune relativă) .


În Excel, funcŃia EDATE (ca şi EOMONTH, NETWORKDAYS sau WORKDAY ) este ocomponentă 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 zileaferente fiecărei luni, ci iau în calcul luna ca entitate întreagă. Deci, contextul de utilizare a funcŃieiEDATE 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 poatededuce cu uşurinŃă data zilei dintr-o lună ce este decalată (+/-), dar nu este evident şi în ce zi asă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.

7/13/2019 Tao




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 Y Y EE A A R R FFR R A A CC

SSiinnttaaxxăă == Y Y EE A A R R FFR R A A CC((d d a a t t ă ă _ _d d e e b b u u t t ; ; d d a a t t ă ă _ _s s f f â â r r ş ş i i t t ; ; c c o o n n s s t t a a n n t t ă ă ))


d d a a t t ă ă _ _s s f f â â r r ş ş i i t t idem d d a a t t ă ă _ _ d d e e b b u u t t ;

c c o o n n s s t t a a n n t t ă ă 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


Un număr cu zecimale ce reprezintă o fracŃiune dintr-un an în care secompară (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.

7/13/2019 Tao




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 f f oorrmmuullee aa ccoonnssttaanntteelloorr ddee ttiipp ttiimmpp..

FFuunnccŃŃiiaa T TIIMMEE V V A A LLUUEE

SSiinnttaaxxăă == T TIIMMEE V V A A LLUUEE((„„ ş ş i i r r _ _d d e e _ _c c a a r r a a c c t t e e r r e e ” ” ))

ş ş i i r r _ _d d e e _ _c c a a r r a a c c t t e e r r e e este un text ce prezintă similitudini cu formatul unei valori de tiptimp. În mod obligatoriu, şirul de caractere trebuie plasat între ghilimele.


Valoarea de tip timp specificată în argument, prezentată într-un format valoric adecvat.

FFuunnccŃŃiiaa T TIIMMEE

SSiinnttaaxxăă == T TIIMMEE ((o o r r ă ă ; ; m m i i n n u u t t ; ; s s e e c c u u n n d d ă ă ))

o o r r ă ă este o valoare numerică cuprinsă între 0 şi 23;

m m i i n n u u t t este o valoare numerică cuprinsă între 0 şi 60;

7/13/2019 Tao




s s e e c c u u n n d d ă ă este o valoare numerică cuprinsă între 0 şi 60. Acest ultim argument poatefi substituit cu separatorul punct şi virgulă.


Valoarea de tip timp ce corespunde argumentelor specificate.

OObbsseerr v v aaŃŃ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, serecomandă ca aceasta nu fie abordată printr-o constantă-timp (IF(B8>18:22:08);....;.....), ci prinintermediul unei adrese de celule ce conŃine constanta respectivă (IF(B8>G12);....;.....), sau prinintermediul 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 HHOOUUR R

SSiinnttaaxxăă ==HHOOUUR R ((n n u u m m ă ă r r _ _t t i i m m p p ))

n n u u m m ă ă r r _ _t t i i m m p p poate fi un număr serial zecimal ce corespunde unei valori de tiptimp, o adresă de celulă ce conŃine o valoare de tip timp sau o constantă de tip timpce este tratată printr-o funcŃie specializată.


Un număr întreg sub forma orei (între 0 şi 24) ce corespunde argumentuluispecificat.

EExxttrraaggeerreeaa mmiinnuuttuulluuii ddiinnttrr--uunn nnuummăărr ttiimmpp..

FFuunnccŃŃiiaa MMIINNUU T TEE

SSiinnttaaxxăă ==MMIINNUU T TEE((n n u u m m ă ă r r _ _t t i i m m p p ))

n n u u m m ă ă r r _ _t t i i m m p p idem HOUR .R R eezzuullttaatt

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((n n u u m m ă ă r r _ _t t i i m m p p ))

n n u u m m ă ă r r _ _t t i i m m p p idem HOUR ..R R eezzuullttaatt Un număr întreg sub forma secundei (între 0 şi 59) ce corespunde

7/13/2019 Tao




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 înminuscule şi invers ( LOWER, UPPER, PROPER ); caracter de tip şir (text) în valoare ( VALUE ) şiinvers ( TEXT, DOLLAR , FIXED ).

AAf f iişşaarreeaa ccaarraacctteerruulluuii ccee ccoorreessppuunnddee uunnuuii ccoodd nnuummeerriicc..

FFuunnccŃŃiiaa CCHH A A R R

SSiinnttaaxxăă ==CCHH A A R R ((c c o o d d _ _n n u u m m e e r r i i c c ))

c c o o d d _ _n n u u m m e e r r i i c c este, fie un număr întreg cuprins în intervalul [1,255], fie adresa uneicelule sau a unui nume de câmp ce conŃine caracterul respectiv.


Caracterul ce corespunde unui cod numeric ASCII2

OObbsseerr v v aaŃŃ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 setastează în timp ce tasta Alt a fost activată). De exemplu, Alt + 65 returnează caracterul A.

b.

Dacă argumentul c c ood d _ _ n n u u m m e e r r i i c c este în afara intervalului, funcŃia CHAR va returna valoarea deeroare # VALUE!.

AAf f iişş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 InformationInterchange)

7/13/2019 Tao




SSiinnttaaxxăă ==CCOODDEE((c c a a r r a a c c t t e e r r ))

c c a a r r a a c c t t e e r r este, fie unul dintre cele 255 de caractere ASCII, fie adresa unei celule saua unui nume de câmp ce conŃine caracterul respectiv .


Caracterul ce corespunde unui cod numeric ASCII


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

Figura 75 Exemplu de utilizare a funcţiilor CHAR şi CODE CCoonnvveerrttiirreeaa ccaarraacctteerreelloorr mmiinnuussccuullee î î nn mmaa j juussccuullee..

FFuunnccŃŃiiaa UUPPPPEER R

SSiinnttaaxxăă ==UUPPPPEER R ((t t e e x x t t ))

t t e e x x t t este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau aunui nume de câmp ce conŃine respectivul şir de caractere .

R R eezzuullttaatt

rreettuurrnnaatt

Caracterele argumentului convertite în majuscule .

CCoonnvveerrttiirreeaa ccaarraacctteerreelloorr mmaa j juussccuullee î î nn mmiinnuussccuullee..

FFuunnccŃŃiiaa LLOOWWEER R

SSiinnttaaxxăă ==LLOOWWEER R ((t t e e x x t t ))

t t e e x x t t este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau aunui nume de câmp ce conŃine respectivul şir de caractere.

7/13/2019 Tao





Caracterele argumentului convertite în minuscule .

CCoonnvveerrttiirreeaa î î nn mmaa j juussccuullăă aa f f iieeccăărruuii pprriimm ccaarraacctteerr aaf f eerreenntt uunnuuii şşiirr ddee ccaarraacctteerree..

FFuunnccŃŃiiaa PPR R OOPPEER R

SSiinnttaaxxăă ==PPR R OOPPEER R ((t t e e x x t t ))

t t e e x x t t este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau aunui nume de câmp ce conŃine respectivul şir de caractere .


Caracterele argumentului în care prima literă a f iecărui cuvânt este majusculă , iarrestul 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 V V A A LLUUEE

SSiinnttaaxxăă == V V A A LLUUEE((t t e e x x t t ))

t t e e x x t t este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule sau aunui nume de câmp ce conŃine respectivul şir de caractere.


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

7/13/2019 Tao




SSiinnttaaxxăă == T TEEXX T T((v v a a l l o o a a r r e e ; ; f f o o r r m m a a t t _ _t t e e x x t t ))

v v a a l l o o a a r r e e reprezintă, fie o valoare numerică, fie adresa unei celule sau a unui numede câmp ce conŃine respectiva valoare;

f f o o r r m m a a t t _ _t t e e x x t t reprezintă unul dintre formatele numerice disponibile din caseta dedialog Format Cells (tabul Number ), comanda HomeNumber (Number

Format).R R eezzuullttaatt 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 DDOOLLLL A A R R

SSiinnttaaxxăă ==DDOOLLLL A A R R ((v v a a l l o o a a r r e e ; ; + + / / - - c c o o n n s s t t a a n n t t ă ă p p o o z z i i Ń Ń i i o o n n a a l l ă ă d d e e r r o o t t u u n n j j i i r r e e ))

v v a a l l o o a a r r e e reprezintă, fie o valoare numerică / şir de caractere, fie adresa unei celulesau a unui nume de câmp ce conŃine respectiva valoare / şir;

+ + / / - - c c o o n n s s t t a a n n t t ă ă p p o o z z i i Ń Ń i i o o n n a a l l ă ă d d e e r r o o t t u u n n j j i i r r e e reprezintă expresia rotunjită a număruluide convertit în şir de caractere.


Un şir de caractere ce imită formatul monetar, ce corespunde unei valorinumerice.


Dacă argumentul „++//- - c c oon n s s t t a a n n t t ă ă p pooz z i i Ń Ń i i oon n a a l l ă ă d d e e r r oot t u u n n j j i i r r e e ” ” lipseşte, funcŃia DOLLAR va afişa cu 2zecimale (D18, A18) textul ce reprezenta valoarea numerică de convertit.În cazul în care c c oon n s s t t a a n n t t ă ă p pooz z i i Ń Ń i i oon n a a l l ă ă d d e e r r oot t u u n n j j i i r r e e este pozitivă, funcŃia DOLLAR operează o rotunjire lapartea zecimală (la dreapta separatorului zecimal). Dacă respectiva c c oon n s s t t a a n n t t ă ă d d e e r r oot t u u n n j j i i r r e e 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 delocaliză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((v v a a l l o o a a r r e e ; ; + + / / - - c c o o n n s s t t a a n n t t ă ă p p o o z z i i Ń Ń i i o o n n a a l l ă ă d d e e r r o o t t u u n n j j i i r r e e

7/13/2019 Tao




[ [ ; ; s s e e p pa a r r a a t t oor r _ _ d d e e _ _ m m i i i i ] ] ))

v v a a l l o o a a r r e e reprezintă, fie o valoare numerică / şir de caractere, fie adresa unei celulesau a unui nume de câmp ce conŃine respectiva valoare / şir de caractere;

+ + / / - - c c o o n n s s t t a a n n t t ă ă p p o o z z i i Ń Ń i i o o n n a a l l ă ă d d e e r r o o t t u u n n j j i i r r e e reprezintă expresia rotunjită a număruluide convertit în format text;

s s e e p pa a r r a a t t oor r _ _ d d e e _ _ m m i i i i este un argument opŃional care la valoarea FALSE, inhibă afişareaseparatorului de mii a numărului ce a fost transformat în şir de caractere. R R eezzuullttaatt 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 R R EEPP T T

SSiinnttaaxxăă ==R R EEPP T T((t t e e x x t t ; ; n n u u m m ă ă r r _ _d d e e _ _m m u u l l t t i i p p l l i i c c ă ă r r i i )) t t e e x x t t 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;

n n u u m m ă ă r r _ _d d e e _ _m m u u l l t t i i p p l l i i c c ă ă r r i i reprezintă numărul multiplicări ale textului.R R eezzuullttaatt 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” (baraverticală) 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.

7/13/2019 Tao




Figura 81 Exemplu de utilizare a funcţiilor REPT şi FREQUENCY

EElliimmiinnaarreeaa ssppaaţţiiiilloorr iinnuuttiillee aaf f eerreennttee uunnuuii şşiirr ddee ccaarraacctteerree..

FFuunnccŃŃiiaa T TR R IIMM

SSiinnttaaxxăă == T TR R IIMM((t t e e x x t t ))

t t e e x x t t este, fie un şir de caractere (cuprins între ghilimele), fie adresa unei celule saua unui nume de câmp ce conŃine respectivul şir de caractere.


Un şir de caractere căruia i s-au anulat spaŃiile inutile (cu excepŃia spaŃiilorcare separă cuvintele textului).

CCoonnccaatteennaarreeaa şşiirruurriilloorr ddee ccaarraacctteerree..

FFuunnccŃŃiiaa CCOONNCC A A T TEENN A A T TEE

SSiinnttaaxxăă ==CCOONNCC A A T TEENN A A T TEE((t t e e x x t t 1 1 ; ; t t e e x x t t 2 2 ; ; ……t t e e x x t t n n ))

t t e e x x t t 1 1 ; ; t t e e x x t t 2 2 ; ; ……t t e e x x t t n n este fie, o listă de şiruri de caractere (între ghilimele), fieadresa unor celule sau a unor nume de câmpuri ce conŃin respectivele şiruri decaractere.


Mai multe şiruri de caractere sau valori numerice concatenate (unite unelecu altele).

OObbsseerr v v aaŃŃiiee.. Concatenarea şirurilor de caractere, a valorilor numerice (cu excepŃia numerelor de tipdată 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 întreghilimele). 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..

7/13/2019 Tao




FFuunnccŃŃiiaa LLEEFF T T

SSiinnttaaxxăă ==LLEEFF T T((t t e e x x t t [ [ ; ; n n u u m m ă ă r r _ _ c c a a r r a a c c t t e e r r e e ] ] ))

t t e e x x t t este, fie un şir de caractere sau o valoare, fie adresa unei celule sau a unuinume de câmp ce conŃine valoarea sau respectivul şir de caractere;

[ [ n n u u m m ă ă r r _ _ c c a a r r a a c c t t e e r r e e ] ] reprezintă numărul de caractere de extras din stânga primuluiargument. Dacă argumentul lipseşte, se va extrage doar primul caracter din stângaacestuia.


Primul caracter sau un grup de caractere extrase din stânga unui alt şir decaractere sau aferent unei valori.

FFuunnccŃŃiiaa R R IIGGHH T T

SSiinnttaaxxăă ==R R IIGGHH T T((t t e e x x t t [ [ ; ; n n u u m m ă ă r r _ _ c c a a r r a a c c t t e e r r e e ] ] ))

t t e e x x t t 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; [ [ n n u u m m ă ă r r _ _ c c a a r r a a c c t t e e r r e e ] ] reprezintă numărul de caractere de extras din dreapta primului

argument. Dacă argumentul lipseşte, se va extrage doar primul caracter din dreaptaacestuia.


Primul caracter sau un grup de caractere extrase din dreapta unui alt şir decaractere sau aferent unei valori.

FFuunnccŃŃiiaa MMIIDD

SSiinnttaaxxăă ==MMIIDD((t t e e x x t t ; ; d d e e b b u u t t ; ; n n u u m m ă ă r r _ _c c a a r r a a c c t t e e r r e e ))

t t e e x x t t este, fie un şir de caractere sau o valoare, fie adresa unei celule sau a unuinume de câmp ce conŃine valoarea sau respectivul şir de caractere;

d d e e b b u u t t 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.

n n u u m m ă ă r r _ _ c c a a r r a a c c t t e e r r e e reprezintă numărul de caractere de extras începând cu valoareapoziŃională a argumentului d d e e b b u u t t .


Un număr de caractere extrase dintr-un şir de caractere în funcŃie decoordonatele dictate de o valoare poziŃională .


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 prelucrareaunor elemente ce fac parte dintr-un cod compozit. Acest cod conţine mai multe elementedisociabile în părţi sau componente care au o semnificaţie bine stabilită şi sunt compacte dinpunct 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.

7/13/2019 Tao




Figura 83 ilustrează posibilităŃile de disociere a unui element numeric ( Cod Produs ) în părŃile salecomponente ( 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 codcompozit

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

7/13/2019 Tao




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

7/13/2019 Tao




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 codcompozit

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 aaf f eerreenntt uunnuuii şşiirr ddee ccaarraacctteerree ssaauu vvaalloorrii nnuummeerriiccee..

FFuunnccŃŃiiaa LLEENN

SSiinnttaaxxăă ==LLEENN((t t e e x x t t ))

t t e e x x t t este, fie un şir de caractere sau o valoare numerică, fie adresa unei celule sau aunui nume de câmp ce conŃine respectivul şir sau valoare.


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.

7/13/2019 Tao




Figura 88 Exemplu de utilizare a funcţiei LEN CCoommppaarraarreeaa aa ddoouuăă şşiirruurrii ddee ccaarraacctteerree ssaauu vvaalloorrii nnuummeerriiccee..

FFuunnccŃŃiiaa EEXX A A CC T T

SSiinnttaaxxăă ==EEXX A A CC T T((t t e e x x t t 1 1 ; ; t t e e x x t t 2 2 ))

t t e e x x t t 1 1 şi t t e e x x t t 2 2 sunt, fie un două şiruri de caractere sau valori numerice, fie adresa a 2celule sau nume de câmpuri ce conŃin şirurile sau valorile.


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//SSEE A A R R CCHH

SSiinnttaaxxăă ==FFIINNDD//SSEE A A R R CCHH((t t e e x x t t _ _c c ă ă u u t t a a t t ; ; t t e e x x t t _ _b b a a z z ă ă [ [ ; ; n n u u m m ă ă r r ] ] ))

t t e e x x t t _ _c c ă ă u u t t a a t t este fie un şir de caractere sau o valoare ce se doreşte a se identifica; t t e e x x t t _ _b b a a z z ă ă specifică şirul de caractere sau valoarea în care se identifică textul

căutat; [ [ n n u u m m ă ă r r ] ] este un număr ce reprezintă valoarea poziŃională a caracterului de la care să

înceapă căutarea.


O valoare numerică ce reprezintă valoarea poziŃională a şirului de caracterecă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

7/13/2019 Tao




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-

7/13/2019 Tao




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 util izare:

- 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 cumajuscule (UPPER...).

Pentru editarea cu minuscule a prenumelui salariatului (în care primul caracter estemajusculă) se compară cu ajutorul funcţiei EXACT prenumele (extras cu RIGHT din agregatulcompozit „Nume Prenume”) cu acelaşi prenume editat cu prima literă majusculă, iarurmătoarele minuscule (PROPER...);

Dimensiunea agregatului text „Nume Prenume” poate fi declarată între 7 şi 30 de caractereprin 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)).

7/13/2019 Tao




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 R R EEPPLL A A CCEE SSiinnttaaxxăă ==R R EEPPLL A A CCEE((t t e e x x t t _ _v v e e c c h h i i ; ; v v a a l l o o a a r r e e _ _ p p o o z z i i Ń Ń i i o o n n a a l l ă ă ; ; n n u u m m ă ă r r _ _c c a a r r a a c c t t e e r r e e ; ;

t t e e x x t t _ _n n o o u u ))

t t e e x x t t _ _v v e e c c h h i i este fie un şir de caractere sau o valoare numerică sursă (de înlocuit), fieadresa celulei corespunzătoare;

v v a a l l o o a a r r e e _ _ p p o o z z i i Ń Ń i i o o n n a a l l ă ă specifică valoarea poziŃională din text vechi (de la stânga ladreapta), plecând de la care se doreşte înlocuirea cu t t e e x x t t _ _ n n oou u ;

n n u u m m ă ă r r _ _c c a a r r a a c c t t e e r r e e precizează numărul de caractere din textul vechi ce urmează a fiînlocuite cu textul nou.

t t e e x x t t _ _n n o o u u este fie un şir de caractere sau o valoare numerică destinaŃie (cu caret t e e x x t t u u l l _ _ v v e e c c h h i i urmează a fi înlocuit), fie adresa celulei corespunzătoare;

R R eezzuullttaatt rreettuurrnnaatt Un text în care un nou şir de caractere/valoare ( t t e e x x t t _ _ n n oou u ) a înlocuit un şirsau 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ă.

7/13/2019 Tao




Figura 92 Exemplu de utilizare a funcţiei REPLACE

Î Î nnllooccuuiirreeaa uunnuuii şşiirr ddee ccaarraacctteerree ccuu aallttuull..

FFuunnccŃŃiiaa SSUUBBSS T TII T TUU T TEE

SSiinnttaaxxăă ==SSUUBBSS T TII T TUU T TEE((t t e e x x t t ;; t t e e x x t t _ _v v e e c c h h i i ; ; t t e e x x t t _ _n n o o u u [ [ ; ; n n u u m m ă ă r r ] ] ))

t t e e x x t t 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Ńinerespectivul şir sau valoare, pentru care se doreşte înlocuirea caracterelor.

t t e e x x t t _ _v v e e c c h h i i este fie un şir de caractere sau o valoare numerică sursă (de înlocuit), fieadresa celulei corespunzătoare;

t t e e x x t t _ _n n o o u u este fie un şir de caractere sau o valoare numerică destinaŃie (cu caret t e e x x t t u u l l _ _ v v e e c c h h i i urmează a fi înlocuit), fie adresa celulei corespunzătoare;

[ [ n n u u m m ă ă r r ] ] precizează la a câta apariŃie a şirului specificat ca t t e e x x t t _ v v e e c c h h i i să se efectuezeînlocuirea cu t t e e x x t t _ n n oou u .


Un text în care un nou şir de caractere/valoare ( t t e e x x t t _ _ n n oou u ) a înlocuit un şirsau 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.

7/13/2019 Tao




Figura 93 Exemplu de utilizare a funcţiei SUBSTITUTEUn 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), prinintermediul 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, prinfuncţia SUBSTITUTE se înlocuieşte „ultima” apariţie a spaţiului (antecalculată anterior încoloana C) cu caracterul generic „*”.

Coloana E calculează prin funcţia FIND valoarea poziţională a caracterului „*” în cadrulagregatului 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 formatdintr-un număr variabil de elemente

2.7 Funcţii de căutare şi consultare

FFuunnccţţiiiillee ddee ccăăuuttaarree şşii ccoonnssuullttaarree ((LLooookkuupp && RReef f eerreennccee)) 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 iiddeennttiif f iiccaatt ppoozziiţţiioonnaall..

7/13/2019 Tao




FFuunnccŃŃiiaa CCHHOOOOSSEE

SSiinnttaaxxăă == CCHHOOOOSSEE( ( i i n n d d e e x x _n n u u m m e e r r i i c c ;; l l i i s s t t ă ă _d d e e _ _e e l l e e m m e e n n t t e e ......))

i i n n d d e e x x _ _n n u u m m e e r r i i c c precizează sub forma unui număr de ordine, care element (de tiptext, numeric sau referinŃă celulară) din lista de argumente va fi selectat. Indexuleste un număr cuprins între 0 şi 29.

l l i i s s t t ă ă _ _d d e e _ _e e l l e e m m e e n n t t e e reprezintă o enumerare de elemente (declarate ca şi constante)sau de celule ce conŃin elementele respective.


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

OObbsseerr v v aaŃŃiiee:: Argumentul l l i i s s t t ă ă _ _ d d e e _ _ e e l l e e m m e e n n t t e e 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 seutilizează ca argument al unei funcŃii matematice sau statistice, argumentul l l i i s s t t ă ă _ _ d d e e _ _ e e l l e e m m e e n n t t e e poateconŃ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 IIddeennttiif f iiccaarreeaa nnuummăărruulluuii ddee ccoollooaannăă ccee ccoorreessppuunnddee uunneeii rreef f eerriinnţţee ddiinnttrr--uunn ccââmmpp..

FFuunnccŃŃiiaa CCOOLLUUMMNN

SSiinnttaaxxăă == CCOOLLUUMMNN((r r e e f f e e r r i i n n Ńă ă _ _c c e e l l u u l l a a r r ă ă ssaauu c c â â m m p p ))

r r e e f f e e r r i i n n Ń Ń ă ă _ _c c e e l l u u l l a a r r ă ă s s a a u u c c â â m m p p precizează adresa unei celule sau a unui câmp ce vareferi numărul coloanei.


Numărul coloanei corespunzătoare primei referinŃe celulare aferentecoordonatelor unui câmp specificat.

FFuunnccŃŃiiaa CCOOLLUUMMNNSS

7/13/2019 Tao




SSiinnttaaxxăă == CCOOLLUUMMNNSS((c c â â m m p p ))

c c â â m m p p precizează adresa unui câmp ce va referi numărul coloanei.R R eezzuullttaatt rreettuurrnnaatt

Numărul de coloane aferent câmpului specificat ca argument.

OObbsseerr v v aaŃŃ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ărulde ordine a coloanei respectivului câmp specificat ca argument (număr ce începe întotdeauna cu 1 şise 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 Figura96.

Figura 96 Exemple de utilizare a funcţiilor COLUMN şi COLUMNS IIddeennttiif f iiccaarreeaa nnuummăărruulluuii ddee lliinniiee ccee ccoorreessppuunnddee uunneeii rreef f eerriinnţţee ddiinnttrr--uunn ccââmmpp ..

FFuunnccŃŃiiaa R R OOWW

SSiinnttaaxxăă == R R OOWW((r r e e f f e e r r i i n n Ń Ń ă ă _ _c c e e l l u u l l a a r r ă ă ssaauu c c â â m m p p ))

r r e e f f e e r r i i n n Ń Ń ă ă _ _c c e e l l u u l l a a r r ă ă s s a a u u c c â â m m p p precizează adresa unei celule sau a unui câmp ce vareferi numărul convenŃional al liniei.


Numărul liniei corespunzătoare primei referinŃe celulare aferentecoordonatelor unui câmp specificat.

FFuunnccŃŃiiaa R R OOWWSS SSiinnttaaxxăă == R R OOWWSS((c c â â m m p p ))

c c â â m m p p precizează adresa unui câmp ce va specifica numărul real al liniei.R R eezzuullttaatt rreettuurrnnaatt

Numărul de linii aferente câmpului specificat ca argument.

OObbsseerr v v aaŃŃiiee.. În timp ce funcŃia ROW returnează numărul de linie în mod convenŃional (linia 1=1, linia2=2,... ultima linie=1.048.576), funcŃia ROWS returnează numărul de linie în mod real (număr ce

7/13/2019 Tao




î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 IIddeennttiif f iiccaarreeaa nnuummăărruulluuii ddee zzoonnee aaddiiaacceennttee ddiinnttrr--uunn ccââmmpp((uurrii))..

FFuunnccŃŃiiaa A A R R EE A A SS

SSiinnttaaxxăă == A A R R EE A A SS((c c â â m m p p / / c c â â m m p p u u r r i i ))

c c

â â

m m

p p

( ( u u

r r i i ) ) precizează adresa unui câmp sau a mai multor câmpuri ce urmează a fi

identificate.R R eezzuullttaatt rreettuurrnnaatt

Numărul de zone contigue dintr-un câmp.

OObbsseerr v v aaŃŃiiee.. Dacă argumentul cuprinde multe zone contigue (mai multe câmpuri), atunci acesta semai închide într-o pereche de paranteze rotunde.

În Figura 98 este prezentat un exemplu de utilizare a funcŃiei AREAS.

7/13/2019 Tao




Figura 98 Exemplu de utilizare a funcţiei de consultare AREAS AAf f iişşaarreeaa aaddrreesseeii uunneeii cceelluullee..

FFuunnccŃŃiiaa A A DDDDR R EESSSS

SSiinnttaaxxăă == A A DDR R EESSSS((n n r r _l l i i n n i i e e ; ; n n r r _c c o o l l o o a a n n ă ă

[ [ ; ; t t i i p p _ _ r r e e f f e e r r i i n n Ń Ń ă ă , ,v v a a l l _ _ l l oo g g i i c c ă ă ; ; ” ” n n u u m m e e _ _ f f ooa a i i e e _ _ c c a a l l c c u u l l ” ” ] ]))

n n r r _ _l l i i n n i i e e specifică numărul de linie al referinŃei celulare;

n n r r _ _c c o o l l o o a a n n ă ă precizează numărul de coloană al celulei;

[ [ t t i i p p _ _ r r e e f f e e r r i i n n Ń Ń ă ă ] ] 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);

[ [ v v a a l l ooa a r r e e _ _ l l oo g g i i c c ă ă ] ] specifică prin opŃiunea False (implicit opŃiunea are valoarea True )adresele referinŃei celulare sub forma R n Cm - unde R n indică linia ( Row ) n , iar Cm returnează coloana ( Column ) m . Aceste coordonate se numesc referenŃiale;

[„n n u u m m e e _ _ f f ooa a i i e e _ _ c c a a l l c c u u l l ”] afişează numele foii de calcul sau un text explicativ referitor laadresa celulei în cauză.


O adresă de celulă sub formă de text.

În Figura 99 sunt ilustrate câteva exemple de utilizare a funcŃiei ADDRESS.

7/13/2019 Tao




Figura 99 Exemple de utilizare a funcţiei ADDRESS MMooddiif f iiccaarreeaa rreef f eerriinnţţeelloorr cceelluullaarree llaa ccaarree f f aaccee aappeell oo f f oorrmmuullăă,, f f ăărrăă ccaa rreessppeeccttiivvaa f f oorrmmuullăă ssăă f f iiee

aa j juussttaattăă llaa nnooiillee ccoooorrddoonnaattee..

FFuunnccŃŃiiaa IINNDDIIR R EECC T T

SSiinnttaaxxăă ==IINNDDIIR R EECC T T((r r e e f f e e r r i i n n Ńă ă _ _t t e e x x t t [ [ ; ; p pa a r r a a m m e e t t r r u u _ _ l l oo g g i i c c ] ] ))

r r e e f f e e r r i i n n Ń Ń ă ă _ _t t e e x x t t specifică o referinŃă către o celulă, un nume de câmp definit ca referinŃă sau oreferinŃă către o celulă specificată ca şir de caractere.

[ [ p pa a r r a a m m e e t t r r u u _ _ l l oo g g i i c c ] ] specifică o valoare logică de tip True sau False, prin care se face precizareatipului de referinŃă ce este conŃinut de primul argument:

True (parametru implicit): semnifică o referinŃă de tip A1 (referinŃă standard a foiide calcul);

False : semnifică o referinŃă de tip R1C1 (referinŃă a foii de calcul prin care seprecizează explicit linia-Row şi coloana-Column);R R eezzuullttaatt rreettuurrnnaatt

ReferinŃa specificată printr-un şir de caractere.

OObbsseerr v v aaŃŃiiee.. FuncŃia INDIRECT returnează o valoare de eroare, dacă referinŃa_text nu este oreferinŃă celulară validă. Necesitatea funcŃiei INDIRECT derivă din adresarea din interiorulformulelor 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ŃiaSUM, pentru Produsul1 pe domeniul C6:C16. Dacă se doreşte schimbarea lunilor pentru care esteoperaŃional calculul (de exemplu, pentru lunile martie –decembrie), formula de calcul a sumei trebuiereajustată 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 aleliniilor şi coloanelor care participă la calcul. Astfel, în celula D1 şi E1 se marchează numărul absolutal primei şi ultimei linii de însumat. Similar, în celulele câmpului C3:F3 se editează etichetele decoloană în expresie absolută (C, D, E, F).Formula din F19 aplică funcŃia SUM pe coordonatele rezultate din concatenarea adreselor celulelorce reprezintă numărul de linie şi adresa coloanei. Modificând numărul de linie din D1 sau E1, calcululsumei poate fi ajustat astfel încât să includă orice linie, fără a se modifica formula de calcul.

7/13/2019 Tao




Figura 100 Exemplu de utilizare a funcţiei INDIRECT

IIddeennttiif f iiccaarreeaa ppoozziiţţiieeii rreellaattiivvee aa uunnuuii eelleemmeenntt î î nn rraappoorrtt ddee ccoooorrddoonnaatteellee ssaallee î î nnttrr--uunn vveeccttoorr ddee ccoonnssuullttaarree..

FFuunnccŃŃiiaa MM A A T TCCHH

SSiinnttaaxxăă ==MM A A T TCCHH((c c h h e e i i e e _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e ; ; v v e e c c t t o o r r _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e [ [ ;; p pa a r r a a m m e e t t r r u u ] ]))

c c h h e e i i e e _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e 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ă;

v v e e c c t t o o r r _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e 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;

[ [ p pa a r r a a m m e e t t r r u u ] ] specifică un număr cu următoarea semnificaŃie:

0 (zero): identifică prima valoare (pot fi mai multe) egală cu valoarea cheii deconsultare. 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 cheiide consultare;

-1: caută cea mai mică valoare imediat mai mare sau egală decât valoarea cheii deconsultare. Tabelul de consultare trebuie să fie sortat în ordinea descrescătoare a cheii deconsultare.


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-uncâmp orizontal sau vertical.

OObbsseerr v v aaŃŃiiee.. FuncŃia MATCH are relevanŃă împreună cu alte funcŃii de consultare care referă prinnumărul de linie şi numărul de coloană coordonatele unei valori căutate (de exemplu, cu funcŃiileINDEX şi HLOOKUP ).În Figura 101 sunt ilustrate două exemple de utilizare a funcŃiei MATCH. Un prim exemplu caută ocheie de consultare cu valoarea 104 într-un câmp vertical numit Cod_Client (B3:B10). FuncŃiaMATCH va returna linia 5 din tabelul de consultare. Al doilea exemplu caută o cheie de consultarede 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

7/13/2019 Tao




ca ultim argument al funcŃiei, parametrul 0 (zero). În acest fel, funcŃia MATCH va returna coloana 6din 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((t t a a b b e e l l _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e ; ; n n u u m m ă ă r r _ _l l i i n n i i e e ; ; n n u u m m ă ă r r _ _c c o o l l o o a a n n ă ă ))

t t a a b b e e l l _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e este un câmp ce conŃine posibilele valori căutate;

n n u u m m ă ă r r _ _l l i i n n i i e e specifică un număr de linie de unde să fie extrasă valoarea căutată;

n n u u m m ă ă r r _ _c c o o l l o o a a n n ă ă specifică un număr de coloană de unde să fie extrasă valoareacăutată;


PoziŃia relativă a unui element într-un tablou de consultare, la intersecŃiaunei linii cu o coloană.

OObbsseerr v v aaŃŃ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 uneicelule 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ărorase 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Ńialiniei 5 cu coloana 6 din tabloul de consultare. Cele două funcŃii MATCH permit în acest contextidentificarea numărului de linie şi de coloană, iar funcŃia INDEX extrage valoarea găsită la intersecŃiacelor două coordonate ale tabelului de consultare ClienŃi .

Figura 101 Exemplu de utilizare a funcţiilor de consultare MATCH şi INDEX

7/13/2019 Tao



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

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 identificavaloarea 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 pelinie (opţiunea Copy the formula and lookup parameters).

7/13/2019 Tao




În varianta alegerii opţiunii Copy just a formula to a single cell, etapa Step 4 of 4 permitealegerea amplasamentului (adresa celulei) în care să se copieze rezultatul consultării şi implicitfuncţia INDEX; În varianta alegerii opţiunii Copy the formula and lookup parameters, etapele: Step 4/5/6of 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) princomanda 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 seva 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 returnavaloarea 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ţiacoordonatelor date numărul de linie (celula B45) şi numărul de coloană returnat deMATCH(„Cod Fiscal ”;B36:G36;0);

Coloana G37:G43 a fost formatată condiţional astfel încât să pună în evidenţă valoareacăutată (Codul fiscal ).

Figura 103 Exemplu de căutare a unei valori printr-o listă derulantă şi formatareacondiţională a rezultatului

IIddeennttiif f iiccaarreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee î î nnttrr--uunn ttaabblloouu ddee ccoonnssuullttaarree..

7/13/2019 Tao




FFuunnccŃŃiiaa LLOOOOK K UUPP

SSiinnttaaxxăă ==LLOOOOK K UUPP((c c h h e e i i e e _d d e e _c c o o n n s s u u l l t t a a r r e e ; ; v v e e c c t t o o r r _d d e e _ _c c o o n n s s u u l l t t a a r r e e ; ; v v e e c c t t o o r r _ _r r e e z z u u l l t t a a t t ))

c c h h e e i i e e _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e este o valoare căutată într-un vector. Cheia poate fi oconstantă o adresă absolută/relativă sau un nume de câmp;

v v e e c c t t o o r r _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e specifică un câmp ce conŃine fie o singură linie, fie osingură coloană. Elementele vectorului de consultare (valori numerice, şiruri decaractere, valori logice) trebuie să fie sortate crescător.

v v e e c c t t o o r r _ _r r e e z z u u l l t t a a t t specifică un câmp ce conŃine fie o singură linie, fie o singurăcoloană de unde să se extragă o valoare căutată;


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ŃiaLOOKUP 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 varecupera din vectorul rezultat (G4:G11) valoarea codului fiscal , în funcŃie de identificarea cheii deconsultare (B16) în vectorul de consultare (B4:B11).

Figura 104 Exemplu de utilizare a funcţiei de consultare LOOKUPOObbsseerr v v aaŃŃ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ă . Altfelspus, în momentul în care se caută o cheie de consultare ce nu este găsită în vectorul deconsultare, funcŃia LOOKUP returnează o valoare dintr-un vector rezultat ce corespunde celeimai apropiate valori a vectorului de căutare pentru cheia de consultare. De exemplu, dacă secaută 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

7/13/2019 Tao




clientului ce corespunde codului 102, existent în vectorul de consultare (deoarece codul 103 nuexistă, este recuperat numele clientului Client2 , pentru cea mai apropiată valoare mai mică decâtcodul 102 – care există).

Acest dezavantaj al funcŃiei LOOKUP poate fi înlăturat printr-un test de existenŃă a cheii deconsultare în vectorul de căutare. Potrivit acestui demers (ilustrat în Figura 105) se va testa (prinfuncŃ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ă valorilesunt 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 fostidentificată) 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 deconsultare într-un vector de căutareb. 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 acheii 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 unspaŃ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 deconsultare î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ŃiaLOOKUP.

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

7/13/2019 Tao




Figura 106 Exemplu de utilizare a funcţiei LOOKUP, cu test de eroare IIddeennttiif f iiccaarreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee î î nnttrr--uunn ttaabblloouu ddee ccoonnssuullttaarree vveerrttiiccaallăă..

FFuunnccŃŃiiaa V V LLOOOOK K UUPP

SSiinnttaaxxăă == V V LLOOOOK K UUPP((c c h h e e i i e e _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e ; ; t t a a b b e e l l _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e ; ; n n u u m m ă ă r r _ _c c o o l l o o a a n n ă ă _ _d d e e _ _r r e e c c u u p p e e r r a a t t [ [ ; ; v v a a l l ooa a r r e e _ _ l l oo g g i i c c ă ă ] ] ))

c c h h e e i i e e _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e 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; t t a a b b e e l l _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e este un câmp asupra căruia operează consultarea verticală

prin căutarea valorii cheii precizate anterior (observaŃia a);

n n u u m m ă ă r r _ _c c o o l l o o a a n n ă ă _ _d d e e _ _r r e e c c u u p p e e r r a a t t reprezintă numărul coloanei (numerotarea începecu 1) de unde va fi recuperată informaŃia găsită în tabelul de consultare,corespunzător valorii cheii de căutare;

[ [ v v a a l l ooa a r r e e _ _ l l oo g g i i c c ă ă ] ] 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 decăutare (observaŃiile b şi c).

R R eezzuullttaatt

rreettuurrnnaatt

Conţinutul unei celule ce figurează într-o anumită coloană dintr-un tablou

de consultare verticală, potrivit valorii unei chei de căutare.


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ă fiesortate 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 esteobligatorie editarea sa în funcŃia VLOOKUP. Contextul în care operează valoarea TRUE este

7/13/2019 Tao




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 deconsultare şi recuperează în consecinŃă o altă informaŃie. Altfel spus, în mod implicit, la tastarea uneichei 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 A vailable) în cazul în care cheia deconsultare 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 primeicoloane 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 sedoreşte ca la tastarea unui cod de identificare, existent într-un tablou să se recupereze automatanumite 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 (încelula 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 decheia 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 deconsultare. Odată copiată formula de consultare în I4, aceasta va fi editată numai pentru schimbareanumă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 coduluicu valoarea 30, funcŃia nu va identifica în prima coloană a tabelului valoarea căutată şi va returnaelementul din coloana „ Nume şi Prenume (sau „Salariul de încadrare ”) ce corespunde cheii cu valoareacea mai apropiată, imediat mai mică decât valoarea căutată (va returna elementele „Vasiliu Marcela” şi„3.000” ce corespund codului cu valoarea 16).

7/13/2019 Tao




„Cazul 2 b ” prezintă o primă posibilitate de înlăturare a acestui inconvenient, şi anume, utilizarea înfuncŃ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ă alteformule (ce conŃin îndeosebi calcule) care fac referinŃă la rezultatul returnat de VLOOKUP în acestcaz.

„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(....) sauIf ( 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ă atabelului, testul If ( IsError( Vlookup(....;FFaallssee)) va înlocui mesajul #N/A (generat automat înprezenŃ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 serecupera cursul valutar al monedei Euro în funcŃie de o dată calendaristică tastată de utilizator (şideclarată î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 desorginte numerică) şi reprezintă prin funcŃia TODAY() data curentă a sistemului. Potrivit valoriicheii de consultare (E8=data de 18.01009), funcŃia VLOOKUP editată în celula F8, returnează dincoloana 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 deparametrul opŃional FALSE (pentru a semnala prin #N/A o valoare neidentificată a cheii), iar înconsecinŃă, nici de un test de existenŃă a cheii în prima coloană a tabelului „CotaŃii ”. ExplicaŃia acestuidemers constă în faptul că în zilele de weekend BNR nu cotează principalele valute şi deci, din punctde vedere informatic, dacă o dată calendaristică căutată nu este identificată (de exemplu data de 18ianuarie 2009 - duminică) se va recupera cursul ce corespunde celei mai mari valori, imediat mai micidecâ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 fostformatată personalizat şi cu ziua din săptămână) a unei date calendaristice (12 ianuarie 2009). Apoi, încelula B9 se va edita o formulă ce conŃine funcŃia WORDAY ce conŃine ca argumente: data

7/13/2019 Tao




calendaristică anterioară – considerată ca bază şi numărul corespunzător zilei de cotaŃie (ce se vaprelua din coloana stângă). Formula astfel editată va fi copiată pe amplasamentul definit de câmpulB10: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) presupuneconstruirea a două formule de consultare verticală, ce vor fi implementate în caseta de dialog

Conditional Formatting (HomeConditional FormattingNew RuleUse a formula...).Formulele de modificare a formatului implicit pot fi editate la nivelul oricărei linii a tabelului deconsultare, iar apoi formatul poate fi copiat (fie prin butonul Format Painter, fie prin succesiuneade comenzi Copy, Paste SpecialFormats). 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 cecaută 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 casetade dialog aferentă formatării condiţionale. În mod analog, în celula C13 se procedează laformatarea condiţională a liniilor aferente coloanei „Curs Euro”, prin compararea cursului uneizile (C13=4,1390) cu valoarea corespunzătoare extrasă din tabel, potrivit cheii de căutare cuvaloarea 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.

7/13/2019 Tao




Figura 109 Exemplu de utilizare a funcţiei VLOOKUP. Calculul reducerilorcomerciale.

Esenţa calculului valorii din exemplul prezentat este dat de faptul că toate elementele ce compunvaloarea 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.

IIddeennttiif f iiccaarreeaa ccoonnţţiinnuuttuulluuii uunneeii cceelluullee î î nnttrr--uunn ttaabblloouu ddee ccoonnssuullttaarree oorriizzoonnttaallăă..

FFuunnccŃŃiiaa HHLLOOOOK K UUPP

SSiinnttaaxxăă ==HHLLOOOOK K UUPP((c c h h e e i i e e _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e ; ; t t a a b b e e l l _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e ; ; n n u u m m ă ă r r _ _l l i i n n i i e e _ _d d e e _ _r r e e c c u u p p e e r r a a t t [ [ ; ; v v a a l l ooa a r r e e _ _ l l oo g g i i c c ă ă ] ] ))

c c h h e e i i e e _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e este o valoare (o constantă, o adresă absolută/relativă sau unnume de câmp) după care are loc căutarea în prima linie a unui tablou de consultareorizontală;

t t a a b b e e l l _ _d d e e _ _c c o o n n s s u u l l t t a a r r e e este un câmp asupra căruia operează consultarea orizontalăprin căutarea valorii cheii precizate anterior (observaŃia b);

n n u u m m ă ă r r _ _l l i i n n i i e e _ _d d e e _ _r r e e c c u u p p e e r r a a t t reprezintă numărul implicit al liniei (prima va începecu 1) de unde va fi recuperată informaŃia găsită în tabelul de consultare,corespunzător valorii cheii de căutare (observaŃia b);

[ [ v v a a l l ooa a r r e e _ _ l l oo g g i i c c ă ă ] ] 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).


Conţinutul unei celule ce corespunde unei linii, dintr-un tablou de

consultare orizontală, potrivit valorii unei chei de consultare.


a) Tabelul de consultare orizontală este o transpunere (din orientarea pe coloane în orientarea pelinii) a unui tabel de consultare verticală. În prima linie a sa vor fi căutate valorile unei chei deconsultare;

7/13/2019 Tao




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ă valorilecheii 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 alegeopŃ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 estemomentul în care valoarea cheii de consultare nu este identificată în prima linie a tabelului . În acest caz, funcŃiaHLOOKUP localizează cea mai apropiată valoare (imediat mai mică) faŃă de cheia de consultare şirecuperează în consecinŃă o altă informaŃie;

Valoarea „de negaŃie ” a argumentului opŃional (valoarea logică FALSE ) în sintaxa funcŃieiHLOOKUP declanşează afişarea mesajului de eroare #N/A ( Not A vailable) în cazul în care cheiade consultare nu este găsită în prima linie a tabelului de consultare. Tot în condiŃiile precizăriiargumentului FALSE, la tastarea unei valori a cheii de consultare, mai mici decât limita inferioară adomeniului primei linii din tabelul de consultare, funcŃia HLOOKUP va returna tot valoarenedisponibilă (#N/A ). Dacă argumentul FALSE este precizat în mod explicit, tabelul de consultarenu 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ă serecupereze 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 areca 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 deprodus (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 întabelul 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 ) înmomentul în care se doreşte recuperarea unor elemente dependente funcŃional de un cod deidentificare inexistent în prima linie a unui tabel de consultare orizontală. Pentru a semnala

7/13/2019 Tao




inexistenŃa cheii de consultare, sintaxa funcŃiei HLOOKUP a fost completată cu al patruleaargument poziŃionat pe valoarea logică de FALSE.În „Cazul 4” funcŃia HLOOKUP a fost completată cu un test de existenŃă ce detectează o valoarenedisponibilă (după acelaşi raŃionament ca şi la VLOOKUP ) în cazul în care valoarea cheii deconsultare (B25=107) nu se regăseşte în prima linie a tabloului de consultare orizontală. Acest demersare 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 şiorizontale î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 ”) cecuprinde anumiŃi coeficienŃi (subunitari) de reevaluare a mijloacelor fixe, pe fiecare an în parte (primalinie 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 fiecaremijloc fix („Denumire mijloc fix ”, „Data punerii în funcŃiune ”, „Valoare de inventar ”), dependente funcŃionalde un cod de identificare („ Număr de ordine ”).

AplicaŃia extrage din tabelul de consultare „Indici ” în celulele câmpului C17:C20 coeficientul dereevaluare, în funcŃie de valorile codului compozit „ Număr de inventar ” (ce este format din categoriamijlocului 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 corespundecategoriei mijlocului fix. Anul punerii în funcŃiune este extras cu funcŃia YEAR din ultimele patrucaractere VALUE((RIGHT(A19,4))) ale numărului de inventar, localizat prin funcŃia VLOOKUP întabelul „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 dinstânga al numărului de inventar (VALUE(LEFT(A19)+1). Decalarea cu o unitate a rândului ceconŃine categoria este explicată prin faptul că prima linie este rezervată anilor, a 2-a linie corespundeprimei 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 derecuperat şi va avea valoarea incrementată cu o unitate pentru a exista o concordanŃă între valorileluate de aceasta şi numărul liniei de recuperat. Dacă nu s-ar fi operat acest artificiu, numărul liniei derecuperat 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 reevaluarecorespunzător liniei 7 din tabel, adică coeficientul de reevaluare corespunzător anului 2007, şi alcategoriei 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 dereevaluare, dacă anul de punere în funcŃiune (extras din tabelul „Imobilizări”) este anul curent, altfel

se menŃine aceeaşi valoare de inventar.

7/13/2019 Tao




Figura 111 Exemplu de utilizare a funcţiei HLOOKUP. Aplicaţia 2Un 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 l iste derulantedeclarate 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 serecuperează 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 deconsultare) î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

7/13/2019 Tao




vectorul de consultare A26:A31. În acest caz, nu mai este necesar un test de existenŃă a cheii deconsultare î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 consultarecheltuielile de transport în lei , prin extragere dintr-un tabel de consultare, în funcŃie de destinaŃie şi decantitatea livrată.O primă abordare de recuperare şi calcul a cheltuielilor de transport în celula C7, identifică prinHLOOKUP 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(......)) adestinaŃ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 consultareextins (şi declarat pe coordonatele A12:F33), corespunzător unui număr de coloană ce corespundedestinaŃ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 defuncŃ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 valoripoziŃionale date prin localizarea cu ajutorul funcŃiei MATCH a destinaŃiei în tabelul de consultare A12:F12.

7/13/2019 Tao




Figura 114 Exemplu recapitulativ de utilizare a funcţiilor HLOOKUP, VLOOKUP şiINDEX. 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 investitesau 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..

7/13/2019 Tao




FFuunnccŃŃiiaa PP V V

SSiinnttaaxxăă ==PP V V ((r r a a t t a a _ _d d o o b b â â n n z z i i i i ; ; n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e ; ; m m ă ă r r i i m m e e a a _ _ p p l l ă ă Ń Ń i i i i [ [ ; ; v v a a l l ooa a r r e e _ _ v v i i i i t t ooa a r r e e ; ; t t i i p p ] ] ))

r r a a t t a a _ _d d o o b b â â n n z z i i i i reprezintă procentul de dobândă perceput pentru o anumităperioadă;

n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e reprezintă numărul total de plăŃi periodice;

m m ă ă r r i i m m e e a a _ _ p p l l ă ă Ń Ń i i i i semnifică valoarea plăŃii făcute în fiecare perioadă;

[ [ v v a a l l ooa a r r e e _ _ v v i i i i t t ooa a r r e e ] ] reprezintă suma totală care se doreşte a fi realizată după ultimaplată (valoarea viitoare la care se ajunge după efectuarea ultimei plăŃi);

[ [ t t i i p p ] ] este un parametru care semnifică faptul că plata se face la începutul perioadei(valoarea 1) sau la sfârşitul perioadei (valoarea 0 – implicită).


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.


FuncŃia financiară PV calculează valoarea prezentă a unei sume investite, adică valoarea curentă aunei serii de plăŃi viitoare. FuncŃia se utilizează pentru a se determina dacă valoarea de revenire a uneianumite 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 caavând valori nule.

Argumentele r r a a t t a a _ _d d o o b b â â n n z z i i i i şi n n u u m m ă ă r r u u l l _ _d d e e _ _ p p e e r r i i o o a a d d e e 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 seechivala 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 – înfuncŃie de faptul dacă valoarea respectivă semnifică o cheltuială sau un venit. De exemplu, dacăargumentele m m ă ă r r i i m m e e a a _ _ p p l l ă ă Ń Ń i i i i , v v a a l l o o a a r r e e _ _ p p r r e e z z e e n n t t ă ă sau v v a a l l o o a a r r e e _ _v v i i i i t t o o a a r r e e sunt exprimate prin numerenegative, 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 m m ă ă r r i i m m e e a a _ _ p p l l ă ă Ń Ń i i i i , v v a a l l o o a a r r e e _ _ p p r r e e z z e e n n t t ă ă sau v v a a l l o o a a r r e e _ _v v i i i i t t o o a a r r e e sunt exprimate prin numerepozitive, acestea semnifică o încasare (o intrare de fonduri financiare), iar funcŃia care le utilizează vareturna 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 secalcula valoarea prezentă a anuităŃii. În Figura 115 s-a calculat valoarea prezentă a sumei investite cuajutorul funcŃiei PV .

7/13/2019 Tao




Figura 115 Exemplu de utilizare a funcţiei PVDin 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 FF V V

SSiinnttaaxxăă ==FF V V ((r r a a t t a a _ _d d o o b b â â n n z z i i i i ; ; n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e ; ; m m ă ă r r i i m m e e a a _ _ p p l l ă ă Ń Ń i i i i [ [ ; ; v v a a l l ooa a r r e e _ _ p pr r e e z z e e n n t t ă ă ; ; t t i i p p ] ] ))

……aceleaşi argumente;

[ [ v v a a l l ooa a r r e e _ _ p pr r e e z z e e n n t t ă ă ] ] reprezintă valoarea prezentă (actuală) a investiŃiei sau aplasamentului.


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 FVSe observă că argumentele mmăăr r i i mmeeaa _ _ p pl l ăăţ ţ i i i i şi v v aal l ooaar r ee _ _ p pr r eezzeennt t ăă, 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ăăr r i i mmeeaa _ _ p pl l ăăţ ţ i i i i şi v v aal l ooaar r ee _ _ p pr r eezzeennt t ăă, 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 PPMM T T

7/13/2019 Tao




SSiinnttaaxxăă ==PPMM T T((r r a a t t a a _ _d d o o b b â â n n z z i i i i ; ; n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e ; ; v v a a l l o o a a r r e e _ _ p p r r e e z z e e n n t t ă ă [ [ ; ; v v a a l l ooa a r r e e _ _ v v i i i i t t ooa a r r e e ; ; t t i i p p ] ] ))

……aceleaşi argumente.R R eezzuullttaatt 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 împrumutde la o bancă comercială către o persoană fizică pentru achiziŃionarea unui autoturism în valoare de76.500 lei. Rata dobânzii practicată de bancă este de 12% pe an, iar durata împrumutului a foststabilită la 5 ani. Mărimea lunară a plăŃii către bancă pentru creditul acordat a fost calculată prinfuncŃia PMT în celula E14.

Figura 117 Exemplu de utilizare a funcţiei PMTDe asemenea în exemplul prezentat s-a calculat şi costul total al împrumutului ca un produs întremărimea lunară a plăŃii şi numărul de perioade de plată, exprimat în luni. Valoarea totală a dobânzii s-acalculat ca diferenŃă între costul total al împrumutului şi suma împrumutată.

CCaallccuullaarreeaa rraatteeii ddoobbâânnzziiii lluunnaarree//aannuuaallee..

FFuunnccŃŃiiaa R R A A T TEE

SSiinnttaaxxăă ==R R A A T TEE((n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e ; ; m m ă ă r r i i m m e e a a _ _ p p l l ă ă Ń Ń i i i i ; ; v v a a l l o o a a r r e e _ _ p p r r e e z z e e n n t t ă ă ))


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

7/13/2019 Tao




FFuunnccŃŃiiaa NNPPEER R

SSiinnttaaxxăă ==NNPPEER R ((r r a a t t a a _ _d d o o b b â â n n z z i i i i ; ; m m ă ă r r i i m m e e a a _ _ p p l l ă ă Ń Ń i i i i ; ; v v a a l l o o a a r r e e _ _ p p r r e e z z e e n n t t ă ă ))


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 NPERUn 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 la6 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 restituitbă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 restulde 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ă, fieaplicând procentul dobânzii lunare la restul de rambursat, fie prin funcţia RATE. Mărimeadobâ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) şidobânda lunară. Rata de plată (lunară) reprezintă anuitatea împrumutului fără a se consideradobâ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ă (valoareegală cu PMT). Pentru cele 6 luni, mărimea plăţii este constantă, iar suma plăţilor lunarereprezintă costul împrumutului.

Împrumutul este complet rambursat la începutul celei de-a şaptea perioade, după ce a fost achitată aşasea rată.

7/13/2019 Tao




Figura 120 Tablou de rambursare a unui credit

Exemplul prezentat anterior este completat de cerinŃa plăŃii cu anticipaŃie a împrumutului, începândcu luna a 4-a.

Pentru a se calcula rambursarea cu anticipaŃie a împrumutului, începând cu luna a 4-a, se va folosifuncŃ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 3luni rămase: lunile 4,5 şi 6). Pentru exemplul ilustrat în Figura 121, suma de 5.161 lei (returnată defuncŃ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 NNPP V V

SSiinnttaaxxăă ==NNPP V V ((r r a a t t a a _ _a a c c t t u u a a l l i i z z a a r r e e ; ; v v a a l l o o a a r r e e _ _1 1 ; ; ……....; ; v v a a l l o o a a r r e e _ _2 2 9 9 ))

r r a a t t a a _ _a a c c t t u u a a l l i i z z a a r r e e este rata de actualizare pe parcursul unei perioade ;

v v a a l l o o a a r r e e_1 1 ; ; ……....; ; v v a a l l o o a a r r e e _ _2 2 9 9 reprezintă plăŃi sau încasări.R R eezzuullttaatt rreettuurrnnaatt

Valoarea actuală netă a unei investiţii bazate pe o serie periodică de

intrări de numerar (cash flows).


FuncŃia NPV diferă de funcŃia PV (Present Value), deoarece se bazează pe vărsăminte care nu auaceeaşi mărime. Astfel, se calculează valoarea actuală netă a unor intrări viitoare de fonduri, pentru ase 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 lasfârşitul fiecărei perioade. NPV foloseşte ordinea argumentelor valoare1, valoare2, ... pentru ainterpreta ordinea fluxurilor de numerar.

7/13/2019 Tao




aa.. Pentru a calcula valoarea actuală netă prin utilizarea unei rate de actualizare, se utilizează seriide 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ătordoreş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 vapune 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 (încelula B95) valoarea actuală netă a investiŃiei, care a fost de 128.901,16 lei dacă rata dobânzii a fost de15% (celula B84). A face o investiŃie de 170 mii lei, antrenează cheltuieli suplimentare de 41.098,84 leifaŃă 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 saudegresiv pentru imobilizări. Majoritatea funcŃiilor financiare pentru calculul amortismentului fac apella noŃiunea de valoare reziduală. Această noŃiune semnifică valoarea ce va putea fi recuperată larevî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((v v a a l l o o a a r r e e _d d e e _ _i i n n v v e e n n t t a a r r ; ; v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă ; ;

d d u u r r a a t t a a _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e )) v v a a l l o o a a r r e e _ _d d e e _ _i i n n v v e e n n t t a a r r reprezintă valoarea cu care a fost înregistrată în evidenŃa

contabilă imobilizarea;

v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă semnifică valoarea ce va putea fi recuperată la revînzareaimobilizării, după expirarea duratei normate de funcŃionare;

d d u u r r a a t t a a _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e reprezintă perioada amortizabilă, adică durata cât timpamortismentul va fi repartizat pe costurile produsului căruia i s-a afectat o cotă-parte a imobilizării.

7/13/2019 Tao





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ărilorpuse în funcţiune la începutul exerciţiului financiar. Amortizarea lineară aferentă unei imobilizărise face prin anuităţi constante (anuitatea se calculează raportând valoarea de inventar aimobiliză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 defuncŃ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 SLNDupă 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 A A MMOOR R LLIINNCC

SSiinnttaaxxăă == A A MMOOR R LLIINNCC((v v a a l l o o a a r r e e _d d e e _ _i i n n v v e e n n t t a a r r ; ; d d a a t t a a _ _ p p u u n n e e r r i i i i _î î n n _ _f f u u n n c c Ńi i u u n n e e ; ;

d d a a t t a a _ _s s f f â â r r ş ş i i t t u u l l u u i i _ _ p p r r i i m m e e i i _ _ p p e e r r i i o o a a d d e e _ _d d e e _ _a a m m o o r r t t i i z z a a r r e e ; ;

v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă ; ; n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e _ _d d e e _ _a a m m o o r r t t i i z z a a r r e e ; ;

r r a a t t a a _ _a a m m o o r r t t i i z z ă ă r r i i i i _ _l l i i n n e e a a r r e e ; ; c c o o m m u u t t a a t t o o r r ))

7/13/2019 Tao




n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e _ _d d e e _ _a a m m o o r r t t i i z z a a r r e e exprimă durata pentru care se calculeazăamortismentul. Numărul de perioadă este întotdeauna zero la sfârşitul primeiperioade;

r r a a t t a a _ _a a m m o o r r t t i i z z ă ă r r i i i i _ _l l i i n n e e a a r r e e reprezintă procentul de depreciere anuală a imobilizăriisupuse amortizării. Acest procent se regăseşte în “fişa mijlocului fix” sau se poatecalcula raportând valoarea unitară la durata normată de funcŃionare în ani

(1/DNFani); c c o o m m u u t t a a t t o o r r 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 ziledintr-un an calendaristic, 3 indică 365 de zile pe an, 4 indică 360 de zile pe an.


Valoarea amortismentului linear al unei imobilizări ţinând cont de data

reală a punerii în funcţiune, adică de prorata temporis.

OObbsseerr v v aaŃŃiiee.. FuncŃia AMORLINC este recomandată pentru calculul amortismentului imobilizărilorpuse î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 V V DDBB

SSiinnttaaxxăă == V V DDBB((v v a a l l o o a a r r e e _d d e e _i i n n v v e e n n t t a a r r ; ; v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă ; ;

d d u u r r a a t t a a _ _n n o o r r m m a a t t ă ă _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e ; ; d d e e b b u u t t u u l l _ _ p p e e r r i i o o a a d d e e i i ; ;

s s f f â â r r ş ş i i t t u u l l _ _ p p e e r r i i o o a a d d e e i i [ [ ; ; r r a a t t ă ă _ _ d d e e p pr r e e c c i i e e r r e e ; ; c c oom m u u t t a a t t oor r ] ] ))

d d e e b b u u t t u u l l _ _ p p e e r r i i o o a a d d e e i i ; ; s s f f â â r r ş ş i i t t u u l l _ _ p p e e r r i i o o a a d d e e i i sunt argumente utilizate pentru calculareaanuităŃilor incomplete de amortisment (plecând de la o perioadă când se începecalculul amortizării, către ultima perioadă pentru care se calculează amortizarea);

[ [ r r a a t t ă ă _ _ d d e e p pr r e e c c i i e e r r e e ] ] este un parametru care influenŃează amortizarea în sensul creşterii

gradului de depreciere al imobilizării. [ [ c c oom m u u t t a a t t oor r ] ] permite sau trecerea automată de la amortizarea degresivă la amortizarea

lineară (valoare zero – implicită) sau împiedicarea acestei treceri (valoare unu).R R eezzuullttaatt 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.

OObbsseerr v v aaŃŃiiee.. Amortismentul degresiv ajustat reprezintă amortismentul contabil descrescător(amortismentul este mai mare pentru primele anuităŃi) până ce anuitatea amortismentului este maimică decât anuitatea ce corespunde amortismentului linear, iar de aici încolo suma amortizabilă estecalculată 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 deprecierepentru 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şitulcelui 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 deamortizare a imobilizării începe la 1 ianuarie (valoare 0) şi durează până la 31 decembrie (valoare 1) şi

7/13/2019 Tao




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 procedeuldegresiv 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 şi0,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 afuncţiei VDB cu parametru

„comutator” = 0

Figura 125 Exemplu de utilizare afuncţiei VDB cu parametru

„comutator” = 1

FFuunnccŃŃiiaa A A MMOOR R DDEEGGR R CC

SSiinnttaaxxăă == A A MMOOR R DDEEGGR R CC((v v a a l l o o a a r r e e _ _d d e e _ _i i n n v v e e n n t t a a r r ; ;

d d a a t t a a _ _ p p u u n n e e r r i i i i _ _î î n n _ _f f u u n n c c Ń Ń i i u u n n e e ; ;

d d a a t t a a _ _s s f f â â r r ş ş i i t t u u l l u u i i _ _ p p r r i i m m e e i i _ _ p p e e r r i i o o a a d d e e _ _d d e e _ _a a m m o o r r t t i i z z a a r r e e ; ;

v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă ; ; n n u u m m ă ă r r _ _d d e e _ _ p p e e r r i i o o a a d d e e _ _d d e e _ _a a m m o o r r t t i i z z a a r r e e ; ;

r r a a t t a a _ _a a m m o o r r t t i i z z ă ă r r i i i i _ _l l i i n n e e a a r r e e ; ; c c o o m m u u t t a a t t o o r r ))

argumente identice (şi cu aceeaşi semnificaŃie) ca funcŃia AMORLINC.R R eezzuullttaatt rreettuurrnnaatt

Valoarea amortismentului degresiv al unei imobilizări ţinând cont de data

reală a punerii în funcţiune.

OObbsseerr v v aaŃŃiiee.. CoeficienŃii amortismentului degresiv, utilizaŃi de funcŃia AMORDEGRC variază înfuncŃie de numărul de perioade de amortizare astfel: 1,5 pentru o perioadă cuprinsă între 3 şi 4 ani, 2pentru 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 ladata 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).

7/13/2019 Tao




Figura 126 Exemplu de utilizare a funcţiilor AMORLINC şi AMORDEGRCFFuunnccŃŃiiaa SS Y Y DD

SSiinnttaaxxăă ==SS Y Y DD((v v a a l l o o a a r r e e _ _d d e e _ _i i n n v v e e n n t t a a r r ; ; v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă ; ;

d d u u r r a a t t ă ă _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e ; ; p p e e r r i i o o a a d d a a _ _d d e e _ _c c a a l l c c u u l l _ _a a _ _a a m m o o r r t t i i z z ă ă r r i i i i ))

argumente prezentate anterior.R R eezzuullttaatt 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).

7/13/2019 Tao




Figura 127 Exemplu de utilizare a funcţiei de amortizare degresivă SYDFFuunnccŃŃiiaa DDBB

SSiinnttaaxxăă ==DDBB((v v a a l l o o a a r r e e _ _d d e e _ _i i n n v v e e n n t t a a r r ; ; v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă ; ;

d d u u r r a a t t ă ă _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e ; ; p p e e r r i i o o a a d d a a _ _d d e e _ _c c a a l l c c u u l l _ _a a _ _a a m m o o r r t t i i z z ă ă r r i i i i ; ;

n n u u m m ă ă r r u u l l _ _d d e e _ _l l u u n n i i _ _ p p e e _ _a a n n _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e _ _a a _ _i i m m o o b b i i l l i i z z ă ă r r i i i i ))

argumente prezentate anterior.


Valoarea amortismentului degresiv absolut , ţinând cont de numărul deluni 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((v v a a l l o o a a r r e e _ _d d e e _ _i i n n v v e e n n t t a a r r ; ; v v a a l l o o a a r r e e _ _r r e e z z i i d d u u a a l l ă ă ; ;

d d u u r r a a t t ă ă _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e ; ; p p e e r r i i o o a a d d a a _ _d d e e _ _c c a a l l c c u u l l _ _a a _ _a a m m o o r r t t i i z z ă ă r r i i i i ; ;

n n u u m m ă ă r r u u l l _ _d d e e _ _l l u u n n i i _ _ p p e e _ _a a n n _ _d d e e _ _f f u u n n c c Ń Ń i i o o n n a a r r e e _ _a a _ _i i m m o o b b i i l l i i z z ă ă r r i i i i ; ;

f f a a c c t t o o r r _ _d d e e _ _m m u u l l t t i i p p l l i i c c a a r r e e ))

argumente prezentate anterior;

f f a a c c t t o o r r _ _d d e e _ _m m u u l l t t i i p p l l i i c c a a r r e e reprezintă un coeficient (3, 4, 5) care accelereazăamortizarea imobilizării.


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,

7/13/2019 Tao



antepenultima este 3n şi aşa mai departe).

Figura 128 ilustrează un exemplu edificator de utilizare a funcŃiilor DB şi DDB pentru o imobilizarecu o valoare de inventar de 80 mii lei, o valoare reziduală de 10 mii lei, o durată de funcŃionare de 8ani ş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 3Nr

Date post:	22-Jul-2015
Category:	Documents
Upload:	antoniavizireanu
View:	1,105 times
Download:	0 times

Tao

Documents