Formule si functii - Excel

Formule si functii - Excel

Ø     Formule

Formulele sunt instructiuni prin care programul Excel calculeaza, procesand datele. Intr-o formula Excel variabilele sunt valori (numite si operanti), iar instructiunile reprezenta operatorii. Variabile pot fi si constante sau argumente (atunci cind se afla in paranteza).

Excelul are mai multe categorii de operatori:

aritmetici: adunare, scadere, inmultire si impartire;

de comparare: =, >, <, <= (mai mic sau egal), >= si <> (diferite de);

logici: TRUE (adevarat) sau FALSE (fals);

de referinta: doua puncte (:); cand se utilizeaza la definirea unui domeniu se numeste operator de domeniu; de exemplu, A1 si D4 sunt referinte de celule, iar A1:D2 desemneaza domeniul ce include toate celulele dintre A1 si D4. Operatorul de domeniu este doar unul dintr-o serie de operatori de referinta. Operatorii de referinte sunt utilizati pentru a extrage valorile din celule sau domenii si a le folosi in formule;

de uniune: virgula (,) uneste doua sau mai multe referinte de celule sau domenii.

Referintele pe intelesul tuturor

Calea cea mai sigura si mai scurta pentru a pune referintele corect si in locul corect din formula se face utilizind mouse-ul astfel:

selectam celula in care dorim sa punem formula;

incepem formula introducind =;

selectam celula pe care dorim s-o introducem in formula. Daca este vorba de un domeniu, selectam celula sa de inceput si tragem pina la cea de sfirsit. In jurul celulei sau in domeniul pe care il introducem in formula apare marcajul;

introducem operatorul (de comparare, aritmetic sau de orice alt tip) pe care dorim sa-l introducem in formula;

selectam o alta celula sau un alt domeniu. Daca este ultima celula sau ultimul domeniu din formula, apasam tasta Enter sau executam click pe caseta Enter a barei pentru formule.

Referintele celulelor intr-o formula se pot schimba automat copiind formula si lipind-o in alta parte. In cazul cind dorim ca formula sa ramina acolo unde este, dar cu referintele schimbate, pur si simplu o editam.

Pentru a modifica o formula apasam tasta F2 sau executam dublu click pe celula ce contine formule. Deplasam cursorul de inserare acolo unde dorim sa facem modificarea si utilizam tastele Delete sau Backspace pentru a sterge vechea referinta.

Referinte absolute si referinte relative

Formulele cu referinte absolute de celule se refera la aceleasi celule, indiferent unde mutam aceste formule. Atunci cind introducem in formule referinte de celule prin indicare si executare de click cu mouse-ul, ele sunt referinte relative (pina cand le modificam). Daca mutam formula intr-o alta celula, referintele sale se modifica relativ la noua pozitie. Aceasta e bine, atit timp cit formula nu trebuie sa se refere mereu la aceleasi celule. Dar daca ea are nevoie de valoarea dintr-o anumita celula, va trebui sa va referiti la celula respectiva printr-o referinta absoluta.

Atunci cand nici referintele absolute, nici cele relative nu sunt bune, le vom folosi pe cele mixte. Referintele de domenii nu trebuie sa fie in totalitate absolute, si nici in totalitate relative. S-ar putea sa dorim sa tinem coloanele fixe, dar sa modificam randurile atunci cand deplasam formulele.

Pentru a introduce referinte mixte utilizam tasta referintelor - F4. Intai executam click pe referinta de celula din bara pentru formule, in asa fel incat cursorul de inserare sa fie plasat in interiorul referintei. Daca, de exemplu, referinta este C5, plasati cursorul intre C si 5, apoi apasati F4. Apasarea repetata a acestei taste parcurge circular cele patru combinatii posibile: rand absolut si coloana relativa , coloana absoluta si rand relativ, absolut in totalitate si relativ in totalitate.

Pentru usurarea calcului se utilizeaza o formula matriciala care opereaza asupra unor domenii intregi de valori.

Ca sa operam cu formule tip matrice trebuie sa stim urmatoarele trei lucruri:

Forma si marimea domeniului in care vor fi depuse rezultatele trebuie sa fie identice cu cele ale domeniilor cu care operam.

O formula matriciala trebuie inclusa intre acolade - - dar nu trebuie sa le introducem efectiv pentru ca Excel le insereaza automat.

Formulele matriciale nu sunt valabile prin tasta Enter, ci prin combinatia Shift+Ctrl+Enter.

Dupa cele mentionate mai sus, calculul matricial se face astfel:

Se selecteaza domeniul in care vor fi puse rezultatele;

Introducem "=" pentru a incepe formula;

Selectam primul domeniul al formulei si apoi scriem un operator;

selectam al doilea domeniu;

apasam Ctrl+Shift+Enter, pentru a atrage atentia programului Excel ca nu introducem o formula obisnuita, ci una matriciala; efectul este obtinerea rezultatelor pe foaia de calcul.

Ø     Functii

Formulele sunt alcatuite din doua parti importante: valorile si instrumentele care lureaza cu aceste valori. La fel este si in cazul functiilor.

Exista, in primul rand, functia in sine, care "spune" programului Excel ce sa faca. Ea este asemenea operatorilor de formule ( + , - , * , /), cu observatia ca o singura functie poate contine mai multi operatori. Si ceea ce este si mai bine, ea executa totdeauna operatiunile in ordinea corecta si nu trebuie sa ne batem capul cu scrierea semnelor.

In al doilea rand exista argumentele. Acesta este unul dintre numele sub care se ascund valorile, referintele sau textul cu care operam. Argumentele sunt datele pe care le dam functiei pentru a obtine rezultatele. Functiile au diferite argumente. Pentru unele functii argumentele sunt numere si text; pentru altele, ore si date calendaristice. Unele functii nu au un argument. Altele au oricat de multe argumente le dam.

Functiile si formulele au un lucru in comun: ambele incep cu semnul =. Daca nu-l punem functia nu va functiona. Operatiile efectuate de multe dintre functii pot fi realizate prin formule simple. Insa e mult mai usor si rapid sa utilizati functiile in loc de formule, ori de cite ori puteti.

Functia AutoSum (comanda de totalizare) opereaza la fel ca tasta TOTAL a unei casete de marcat. Ea se afla pe bara de instrumente, folosind-o pentru a aduna rapid coloane sau randuri de numere, astfel:

Selectam celula de dedesuptul unei coloane sau din dreapta unui rind de numere pe care dorim sa le insumam .

Executam click pe butonul AutoSum.

Comanda AutoSum cauta in sus sau in stanga numerele de insumat si sugereaza un domeniu pentru aceste numere, punand un chenar in jurul lui.

Daca AutoSum a ghicit bine, executam din nou click pe butonul AutoSum. Daca dorim un alt domeniu, selectam intii, apoi executam click pe butonul AutoSum. Putem insuma chiar si mai multe randuri sau coloane simultan.

AutoSum scrie functia Sum (suma) in locul nostru, insumind intregul domeniu. Totalul fiecarei coloane este afisat frumos la baza coloanei respective sau la dreapta randurilor.

Observatii: Comanda de totalizare se poate aplica si domeniilor neadiacente. Se selecteaza fiecare domeniu, executam click pe butonul AutoSum, iar totalurile apar dedesuptul coloanelor sau la dreapta rindurilor.

Functia Wizard ("vrajitorul" functiilor) usureaza foarte mult operatia de scriere a functiilor. El ne conduce, pas cu pas, prin labirintul celor mai complexe functii. Daca executam click pe butonul Function Wizard, "vrajitorul" face urmatoarele :

Listeaza functiile si spune la ce folosesc.

La fiecare pas, va cere tipul si numarul corect de argumente.

Da scurte descrieri pentru fiecare functie si pentru fiecare argument dintr-o functie.

Ne ajuta sa editam functii care se afla deja pe foaia de calcul.

Scrie functia si argumentele sale, apoi o pune in celula selectata.

Functii uzuale

Functia AVERAGE (medie)

Selectam un domeniu de valori, executam clic-dreapta si executam Average (medie); in caseta AutoCalculate (autocalculare) a barei de stare este afisata media aritmetica a valorilor selectate.

Functia MEDIAN

Pentru a gasi mediana unei serii de valori utilizam Function Wizard:

Selectam celula in care dorim sa avem rezultatul.

Executam click pe butonul Function Wizard si selectam categoria Statical din lista Function Categories. Derulam lista Function, Names si alegem MEDIAN.

Executam click pe butonul Next si apare caseta de dialog Function Wizard Setup 2 of 2 MEDIAN ("vrajitorul" functiilor pasul 2 din 2, functia MEDIAN).

Cursorul ar trebui sa se afle in caseta number 1 (primul numar), iar daca nu este acolo, executam click pe caseta. Selectam cu mouse-ul un domeniu a carei mediana dorim sa o calculam. De asemenea, puteti introduce valorile individual, in caseta number1, number 2, . (Excel va adauga mai multe daca este nevoie), dar este mai rapid sa selectati un domeniu.

Rezultatul apare in caseta Value (valoare). Acum executam click pe butonul Finish si valoarea medianei este pusa in celula selectata.

Observatie: In caseta Function Categories selectam All (toate). Obtinem astfel o lista alfabetica a tuturor functiilor. Executam click pe caseta cu lista Function Names, apoi introducem prima litera a functiei pe care o cautam; lista va fi derulata automat pina la litera respectiva.

Functia IF ( daca )

Functia IF se utilizeaza pentru a trata orice propozitie de tip adevarat / fals. Dar nu este neaparat ca in dreptul valorilor despre care dorim sa se faca comparatia sa se scrie adevarat sau fals, se pot scrie orice alte consideratii pe care le vrem. "Vrajitorul" functiilor pune automat ghilimelele din casetele Value_if_true (valoarea luata pentru adevarat) si Value_if_false (valoarea luata pentru fals).

Functia Count nums (contorizare numere) din Excel numara celulele dintr-o foaie de calcul in care se afla numere.

Pentru aceasta selectam un domeniu, executam click dreapta pe bara de stare, selectam din meniul rapid optiunea Count nums, iar comanda AutoCalculate afiseaza numarul de numere din domeniul selectat. Count nums mai poate contoriza date calendaristice, ore sau valori logice - pe scurt, tot ceea ce poate fi convertit la un numar. Count nums nu ia in seama textul sau celulele albe, care nu pot fi convertite in numere.

Functia Count contorizeaza orice.

Pentru a evalua numarul total de celule operate dintr-un domeniu, utilizam functia Count. Ea contorizeaza numerele si textul, dar nu ia in seama celulele albe. Executam click pe butonul Function Wizard si dublu click pe functia Count Executam click pe caseta Value 1 si selectam domeniul de valori pe care dorim sa le contorizam.

Functia DATE ne invita sa introducem anul, luna si ziua, dupa care returneaza data corespunzatoare in format Excel.

Felul in care data este afisata pe foaia de calcul depinde de formatarea celulei respective.Numarul de zile dintre doua date se poate calcula utilizind functia DAYS360. Data de incheiere a unei faze va fi folosita ca data de incepere a fazei urmatoare. Data finala este data programata pentru incheierea proiectului. In acest fel e usor sa construim o diagrama de timp cu functia DAYS360.

Functia NOW (acum) se utilizeaza pentru a introduce data si ora curenta pe foaia de calcul.

Ora si data vor fi actualizate la fiecare deschidere a foii de calcul. Pentru a opri actualizarea prin functia NOW, transformam continutul celulei din functie in valoare. Executam click pe butonul Copy, alegem comanda Edit, Paste Special si apoi executam click pe Values si pe OK. In acest fel lipim o valoare fixa deasupra functiei, anuland-o.

Crearea de functii noi

Functia IF ne permite sa aplicam celulelor teste logice si sa returnam valorile pe care le dorim. Excel ne permite sa inseram o functie in interiorul alteia pentru a lua decizii mai complete. IF este deseori insotit de operatorii AND si OR pentru a crea functii imbricate (nested functions), in care o functie este argumentul alteia. Functia AND combinata cu functia IF ne ajuta sa luam o decizie clara. Incepem cu definirea primei functii, tip IF.

Selectam celula in care dorim sa apara valorile value_if_ true si value_if_false si executam click pe butonul Function Wizard. Din lista Function Category, selectam Logical, apoi executam dublu click pe IF in caseta Function Name. Apare caseta de dialog Function Wizard Step 2, asa cum se vede in figura alaturata.

Executam click pe micul buton din dreapta textului logical_test. Acesta face sa apara caseta de dialog Function Wizard Step 1 of 2 (Nested). Din lista Function Category, selectam Logical , apoi selectam AND (SI) in caseta Function Name. Daca privim bara pentru formule, vom vedea cum functia AND a devenit un argument al functiei IF.

Executam click pe butonul Next pentru a ajunge la caseta de dialog Function Wizard Step 2 of 2 (Nested) si introducem conditiile care trebuie sa fie indeplinite de functia noastra imbricata.

Executam click pe butonul OK pentru a ne intoarce la caseta de dialog Function Wizard Step 2. De exemplu, introducem textul Buy (cumpara) in caseta value_if_true si Don't buy (nu cumpara) in caseta value_if_false.

Bara de formule arata functia imbricata in forma finala (vezi figura alaturata). Executam click pe butonul Finish si am terminat.