Utilizarea formulelor Excel
Formulele sunt elemente de bază ale foii de calcul şi permit efectuarea de operaţii simple (adunare, scădere etc.) dar şi calcule complexe de tip financiar, statistic sau ştiinţific. În plus, cu ajutorul lor se pot realiza comparaţii şi se pot lansa operaţii asupra şirurilor de caractere. Formulele programului Excel încep întotdeauna cu semnul = şi pot include valori numerice sau de tip text (constante), operatori aritmetici, de comparare, funcţii, paranteze, referinţe şi nume.
Implicit, în foaia de calcul este afişat rezultatul evaluării formulelor, Excel memorând însă formula stabilită de utilizator. Dacă se doreşte afişarea propriu-zisă a formulei /formulelor se activează caseta de validareFormulas din etichetaView a comenziiOptions din meniulTools. Acelaşi efect se obţine sau se anulează şi prin comanda rapidăCtrl + ` (accent grav). În paragraful Utilizarea funcţiilor Excel sunt mai multe foi de calcul pentru care s-a ales afişarea formulelor. Pentru a afişa doar formula din celula curentă se foloseşte comanda rapidăCtrl + ‘ (apostrof). Introducerea formulelor se poate realiza fie în bara de formule, fie în celula curentă. Formulele conţin valori şi operatori. Valorile sunt nume, date calendaristice, ore, texte etc. şi se introduc în celulele foilor de calcul. Valorile se mai numesc şi operanzi. Operatorii sunt instrucţiunile prin care se stabileşte ce trebuie făcut cu valorile. De regulă pentru operatori se folosesc simboluri. La rândul lor operatorii sunt: aritmetici (+, -, *, /, %, ^), de comparaţie (<, >, >=, <=, <>), de referire (două puncte, virgula).
Un caz particular de formulă complexă îl reprezintă formula matriceală. O astfel de formulă utilizează un domeniu de valori şi produce atâtea rezultate câte valori sunt în domeniul respectiv. La crearea unei matrici trebuie să se ţină seama de următoarele aspecte: forma şi dimensiunea rezultatului trebuie să fie la fel cu forma şi dimensiunea domeniilor asupra cărora se aplică formula, o formulă matriceală este inclusă între acolade, introduse automat de către Excel, execuţia unei formule matriceale se realizează cu ajutorul combinaţiei de taste:Shift +Ctr l +Enter.Matricea reprezintă o metodă de a realiza mai multe calcule cu o singură formulă. Ea poate fi editată ca orice formulă, dar nu individual, ci trebuie selectat întregul domeniu matriceal. În plus, Excel tratează matricea ca o entitate ceea ce face imposibilă ştergerea sau adăugarea de linii. În figura 6.16 este prezentat un exemplu: „Lista de produse” (achiziţionate pentru o Casă de copii) utilizând o formulă matriceale pentru calculul valorii fiecărui produs cunoscând cantităţile şi preţul unitar al fiecăruia dintre ele. În bara de formule se poate observa formula matriceală inclusă între acolade.
Pentru înlocuirea adreselor cu nume se foloseşteName din meniulInsert. Numele poate fi atribuit de utilizator (prin comandaDefine) sau poate fi conţinutul unei linii sau coloane din foaia de calcul (prin comandaCr eate). Domeniul de celule referit într-o formulă poate primi ulterior un nume care să apară în formulă dacă se foloseşte comandaApply din opţiuneaName a meniuluiIns er t. Pentru a atribui rapid un nume unei celule sau unui domeniu se poate folosi şi comanda rapidă Ctrl + F3 care deschide fereastra Define Name.
În figura 6.17 este prezentat un exemplu în care au fost definite pentru domenii de celule, nume care ulterior sunt referite în formule.
În acest exemplu au fost definite nume pentru fiecare secţie şi trimestru în parte (Ex. Ch_TrI, Ch-TrII, etc.), iar pentru stabilirea totalului pe trimestre şi pe semestru s-au utilizat tocmai aceste nume, aşa cum se observă din bara de formule a ferestrei din figura 6.18.
Pentru a elimina ulterior un nume se utilizează meniuInsert şi din fereastraDefine Name, după selectarea numelui dorit, se selectează butonul Delete. Ştergerea unui nume nu are ca efect eliminarea lui şi din formulele in care a fost folosit, ci doar din lista de nume. Dacă formulele sau funcţiile care au folosit nume şterse nu au fost modificate, Excel afişează mesajul de eroare #NAME.
Utilizarea funcţiilor Excel
Programul Excel foloseşte funcţii predefinite pentru a efectua calcule matematice, financiare, statistice şi logice, prelucrări de texte sau căutări de informaţii în foile de calcul. Funcţiile sunt mai uşor de editat şi permit o executare mai rapidă a diverselor operaţii.
Funcţia este o formulă memorată de Excel şi este formată din două părţi:numele (cuvânt rezervat precedat de semnul=) şi argumentele (incluse între paranteze rotunde). Argumentele pot fi adrese de celule, nume de domenii, valori numerice, şiruri de caractere sau chiar alte funcţii etc. Argumentele de tip şir de caractere sunt încadrate de ghilimele. În general argumentele sunt separate prin virgulă. Rezultatele pot fi valori calculate, valori de tip text, referinţe, valori logice etc.
Editarea funcţiilor se poate realiza fie în bara de formule sau celula curentă, fie apelându-se generatorul de funcţii (Function Wizard) activat din meniulInsert cu opţiunea Function sau prin intermediul pictogrameif(x) din bara de instrumente Standard. În ambele cazuri se deschide fereastra Insert Function (figura 6.19), în care sunt disponibile funcţiile organizate pe categorii, în ordine alfabetică. În Excel există aproape două sute de funcţii grupate după tipul operaţiilor pe care le realizează: financiare, statistice, de lucru cu liste şi baze de date, logice, de căutare şi referire, matematice şi trigonometrice, pentru date calendaristice şi timp, pentru şiruri de caractere, pentru informare, folosite în inginerie.
Pentru fiecare funcţie activată se deschide o fereastră în care sunt afişate formatul funcţiei, o succintă descriere a funcţiei şi zone distincte de editare pentru fiecare argument din format. În plus, este afişat şi rezultatul ce se va obţine prin executarea funcţiei. Ca exemplificare prezentăm în figura nr. 6.20 fereastra asociată funcţiei IF() în care prin condiţia specificată se stabileşte un procent de adaos de 15% sau 20%, după cum preţul de achiziţie este mai mare sau mai mic decât 15000 de lei.
Prezentăm în continuare doar principalele funcţii: statistice, matematice şi logice folosite în calculele economice din administraţia publică.
Funcţii statistice. Excel pune la dispoziţia utilizatorului un pachet de funcţii statistice dintre care cele mai utilizate sunt cele care permit stabilirea mediei aritmetice, extragerea valorii maxime sau minime. Funcţia Average( ) - calculează media argumentelor care pot fi valori sau domenii. Domeniile, la rândul lor, pot conţine numere, referinţe de celule sau matrici de valori. Funcţia Count( ) – returnează numărul de celule care conţin cifre sau formule, ignorându-le pe cele care conţin şiruri de caractere, valori logice, erori sau blank.
Funcţia CountA( ) - stabileşte numărul de celule al căror conţinut este nenul.
Funcţia Max( ) – afişează cel mai mare număr dintr-un domeniu de celule.
Funcţia Min( ) – afişează cel mai mic număr dintr-un domeniu de celule.
Funcţia Stdev( ) – calculează deviaţia standard de populare a unui domeniu de celule.
Funcţia Var( )- calculează variaţia de populare pentru un domeniu de celule.
În figura 6.17 sunt prezentate funcţiile statistice aplicate asupra datelor din foaia de calcul ce conţine Realizarea încasărilor pe unităţi fiscale.
Funcţiile logice. Aceste funcţii sunt printre cele mai folosite funcţii Excel, ele oferind posibilitatea de a adăuga noi facilităţi logice şi de decizie foilor de calcul. Cele mai utilizate funcţii din această categorie sunt: =IF(), =AND(), =OR(), =NOT(), =FALSE() şi =TRUE().
Funcţia IF( ) – este cea mai simplă funcţie logică Excel având totodată şi cele mai multe aplicaţii practice. Ea este folosită pentru testarea unor condiţii şi pentru luarea unor decizii. Formatul general este=IF(condiţie,val_adevăr,val_fals), rezultând faptul că funcţia IF( ) foloseşte trei argumente: testul logic şi valorile pentru adevărat şi pentru fals. În cazul în carecondiţia evaluată este adevărată, funcţia returnează rezultatul acţiuniival_adevăr. În caz contrar este returnat rezultatul acţiuniival_fals. Dacăval_fals lipseşte, iar condiţia este evaluată ca fiind falsă se va returna valoareaFALSE. Pentru testarea unor condiţii complexe /multiple se pot construi IF-uri imbricate prin plasarea altor funcţii IF() în val_adevăr şi /sau în val_fals, sau pot fi folosite funcţiile AND( ) şi OR( ).
FuncţiaAND( ) – are rolul de a reuni condiţii în care toate argumentele logice trebuie să respecte condiţia stabilită de utilizator. Formatul general este=AND(argument1, argument2,…), cu precizarea că argumentele trebuie să fie valori logice unice sau matrici de valori logice (într-un număr maxim de 30). Funcţia returneazăTRUE dacă toate argumentele logice sunt adevărate şiFALSE dacă cel puţin un argument este fals.
FuncţiaOR ( ) – are acelaşi format cu funcţia AND() şi este folosită pentru obţinerea condiţiilor multiple în care este suficient ca măcar unul dintre argumentele logice să respecte condiţia specificată. Astfel, funcţia va returna valoarea FALSE dacă nici unul dintre argumentele folosite nu respectă condiţia.
Funcţii matematice. Acest tip de funcţii oferă facilităţi pentru efectuarea unei game deosebit de variate de calcule matematice, ştiinţifice, inginereşti etc. Ne oprim doar asupra funcţiilor folosite curent în calculele economice: =SUM(), =SUMIF(), =PRODUCT(), =SUMPRODUCT(), =ROUND().
Funcţia SUM( ) – este cea mai utilizată funcţie, motiv pentru care în bara de instrumenteStandard este disponibilă şi pictograma AutoSum -Σ specializată în însumarea conţinutului celulelor unei foi de calcul. Prin folosirea pictogrameiAutoSum, implicit, este însumat conţinutul numeric al celulelor plasate la stânga sau deasupra celulei curente. Pentru o totalizare rapidă a fiecărei coloane dintr-un tabel, se selectează tot tabelul (cu mouse-ul sau cu combinaţia Ctrl+Shift+* (de pe tasta 8)), după care se execută clic pe AutoSum. Procedura funcţionează şi pentru domenii neadiacente. După selectarea acestora, executând clic pe
AutoSum, totalurile vor apărea la baza coloanelor sau în dreapta liniilor selectate. Funcţia 126 Tehnologii informaţionale pentru Administraţie Publică SUM() are formatul general=SUM(argment1,argument2,…) şi calculează suma tuturor argumentelor specificate (maxim 30). Argumentele care nu pot fi convertite din text în numere, sau mesajele de eroare sunt ignorate. Funcţia SUMIF( ) – calculează suma conţinutului tuturor celulelor dintr-un domeniu care verifică o condiţie stabilită. Formatul general este=SUMIF(domeniu, condiţie, domeniu_sumă). Trebuie specificat faptul că întotdeauna condiţia este precizată sub forma unui şir de caractere inclus între ghilimele. Funcţia PRODUCT( ) – are acelaşi format cu funcţiaSUM() şi realizează înmulţirea argumentelor specificate (maxim 14). Argumentele care sunt valori logice, text sau celule vide sunt ignorate.
Funcţia SUMPRODUCT( ) – calculează suma înmulţirii celulelor corespondente din două/mai multe zone de celule (linii sau coloane). Format general:= SUMPRODUCT( aria1,aria2,aria3..) FuncţiaROUND( ) –rotunjeşte rezultatul în funcţie de numărul de zecimale precizat.
Funcţii financiare Excel pune la dispoziţia utilizatorilor o serie de funcţii financiare.Dintre acestea cele mai folosite în lucrările din administraţia publică sunt:
Funcţii pentru calculul amortizării: SLN(), DDB() şiSY D() O primărie achiziţionează un utilaj la preţul de 50000000 lei. Durata de viaţă 5 ani. Să se calculeze amortizarea corespunzătore fiecăruia din cei 5 ani până la amortizarea completă. Valoarea reziduală este egală cu zero. În figurile 6.21 şi 6.22 sunt prezentate formatele funcţiilor şi respectiv rezultatele aplicării acestora.
Funcţii pentru calculul anuităţilor: FV(), PV(). Funcţia FV(). O primărie depune o sumă de 1000 USD în contul său curent. Timp de patru ani ea depune anual câte o sumă de 500 USD. Să se calculeze soldul contului după această perioadă ştiind că dobânda anuală va fi constantă în procent de 5%. Depunerile pot fi făcute la sfârşitul sau începutul fiecărei perioade. Se face precizarea că valoarea prezentă şi plăţile periodice vor fi preluate cu semnul minus deoarece ele reprezintă imobilizări pentru depunător. Rezultatul este prezentat în figura 6.23. După cum se observă în bara de formule, în celula D25 s-a calculat valoarea viitoare în condiţiile plăţilor la sfârşitul perioadei (valoarea Programe de calcul tabelar ultimului argument este zero). În celula D26, pentru cazul plăţilor efectuate la începutul perioadei, argumentul tip va fi egal cu 1.
Funcţii de căutare şi referire
Funcţiile din această categorie asigură căutarea şi returnarea unor valori dintr-un domeniu dat de celule. În aplicaţiile economice cele mai utilizate funcţii de acest tip sunt : =VLOOKUP()şi =HLOOKUP(). Funcţia HLOOKUP( ) – permite realizarea de căutări pe orizontală, pe rândul din partea de sus a unui tabel, până la găsirea unei valori de comparare adecvate. Formatul general este:=HLOO KUP(val_căutată,matri ce_tabel,nr_linie_ index,domeniu_căutare ).
Funcţia efectuează căutarea unei valori specificate în linia superioară a domeniului definit printr-o matrice, după care căutarea continuă în linia specificată prinnr_linie_index. Domeniul de căutare este o valoare logică opţională (TRUE sau FALSE) şi permite returnarea exactă a valorii căutate sau a unei valori aproximative.
Exemplu. Din situaţia centralizatoare pe anii 2000-2005 a absolvenţilor, pe specializări, să se afle care a fost numărul absolvenţilor de la secţia Administraţie Publică în anul 2004 (figura 6.25).
Funcţia VLOOKUP( ) – efectuează căutarea pe verticală a unui element în coloana cea mai din stânga a unei matrici dintr-o tabelă indexată. Dacă nu este găsită valoarea dorită, funcţia va căuta, din prima coloana, următoarea valoare mai mare, decât cea căutată. Format: =VLOOKUP (cheie-de-căutare,tabel,număr-coloană,tip-căutare)
Cheia de căutare specifică valoarea de căutat,tabelul de căutare este zona în care trebuie realizată căutarea şi se specifică prin adrese sau un nume asociat ei,număr-coloană indică poziţia coloanei pe care se găseşte data căutată (coloanele sunt numerotate de la 1 la n), tip-căutare este un argument de tip logic, care arată dacă se face sau nu o căutare exactă. CunoscândMarca să se stabilească datele privind salariatul folosind funcţia =VLOOKUP (figura 6.26)
