Utilizarea formulelor

Utilizarea formulelor

Dupa ce am introdus date in celulele unei foi de calcul este foarte probabil sa dorim sa folosim intr-un anumit fel aceste date. Multi dintre noi folosesc programul Excel doar pentru stocarea datelor la fel ca intr-un program de baze de date. Dar cea mai importanta caracteristica a Excel-ului este capacitatea lui de a efectua calcule cu aceste date folosind functiile si formulele. Formulele pot fi simple ecuatii care aduna sau inmultesc doua valori sau pot fi foarte complexe incluzand in corpul lor functii predefinite in Excel.

O functie este, in Excel, o formula predefinita, ajutand utilizatorul sa economiseasca timp prin folosirea ei. De exemplu pentru adunarea a doua valori stocate in celulele A1 si A2 folosim formula =A1+A2. Dar daca avem de adunat 20 de valori stocate in celulele A1 pana la A20 economisim timp folosind functia Excel =SUM(A1:A20).

O formula este un enunt la fel ca orice expresie matematica. La fel ca valorile numerice, formulele sunt memorate in celule individuale. Orice formula Excel incepe cu semnu =, care indica programului ca urmeaza o operatie matematica. De exemplu daca se introduce formula =4+7 in orice celula libera dintr-o foaie de calcul, Excel va afisa rezultatul 13. Formulele sunt compuse din operatori si operanzi.

Operatorii sunt simbolurile care precizeaza actiunea pe care o va efectua Excel asupra operanzilor. Unii operatori pot opera asupra unui singur operand si se numesc operatori unari, iar altii asupra doi operanzi si se numesc operatori binari.

In Excel gasim patru categorii de operatori:

1. Operatori aritmetici: + (adunare), - (scadere), / (impartire), * (inmultire), % (transformarea in procent), ^ (ridicarea la putere) care au efect asupra valorilor numerice si au ca rezultat tot valori numerice. Pentru stabilirea ordinii operatiilor se pot introduce paranteze.
2. Operatori pentru texte: & (concatenare) care uneste (concateneaza) doua sau mai multe siruri de caractere.
3. Operatori relationali: = (egal), > (mai mare), < (mai mic), <> (diferit), >= (mai mare sau egal), <= (mai mic sau egal), care compara doua valori de acelasi tip si au ca rezultat o valoare logica: TRUE (adevarat) sau FALSE (fals).
4. Operatori pentru referinte la celule sau domenii de celule: : (doua puncte), , (virgula), care combina doua referinte la celule intr-o referinta compusa. De exemplu =SUM(A1, A2) va aduna valorile din celulele A1 si A2 sau =SUM(A1:A20) va aduna valorile din celulele A1 pana la A20.

Formula va fi afisata in bara de formule de deasupra grilei foii de calcul.  In celula se afiseaza rezultatul produs de formula, nu formula in sine. Daca se doreste afisarea formulei in celula se selecteaza optiunea (Options) Optiuni din meniul Tools (Instrumente) si din subsectiunea View (Vizualizare) se bifeaza casuta de validare Formule.

Programul Excel foloseste reguluile matematice standard in privinta ordinii de efectuare a operatiilor. Toate operatiile din paranteze sunt efectuate primele. Obtinerea unui numar negativ, calculul procentajelor si ridicarea la putere trebuie efectuate primele, in aceasta ordine, pentru a fi folosite valorile obtinute in celelalte calcule.

Apoi ordinea de efectuare a operatiilor este: operatiile din paranteze, inmultirea, impartirea, adunarea si scaderea.

O formula se introduce prin selectarea celulei, tastarea semnului = si scrierea formulei propriu-zise. De exemplu "=B1+B2" va aduna valorile din celulele adiacente B1 Si B2.

Programul Excel genereaza un mesaj de eroare daca formula nu respecta regulile matematice pentru formule.

Aceasta tehnica se foloseste pentru a crea orice formula simpla. In lipsa unei recomandari explicite, Excel efectueaza inmultirea si impartirea inaintea adunarii si scaderii. Daca doriti ca unele parti ale formulei sa fie calculate in alta ordine, folositi parantesele pentru a modifica ordinea de calcul implicit. Se folosesc doar parantezele rotunde "()" si nu alte tipuri ca la matematica.

Pentru a modifica o celula puteti sa faceti clic pe celula in care e scrisa formula si apoi sa o editati in bara de celule sau sa faceti dublul clic pe celula si sa o editati direct in celula. Apoi apasati Enter si va fi afisat noul rezultat. Pentru a parasi o formula fara a efectua nici o modificare sau pentru a anula modificarile efectuate, apasati tasta Esc sau butonul Revocare (in stanga barei de formule, este afisat atunci cand sunteti pe formula).

Copierea formulelor cu comanda AutoFill (Umplere automata)

Copierea formulelor cu AutoFill este utila atunci cand aveti o baza de date cu mai multe valori, de exemplu pe 2 coloane, iar in coloana a treia doriti sa scrieti suma valorilor din primele doua coloane.

Atunci cand copiati o formula folosind Umplere automata ea isi ajusteaza automat referintele astfel incat calculul sa fie corect. De exemplu, cand scrieti o formula care insumeaza valorile din patru celule din stanga fiecare copie a formulei va insuma valorile celor patru celule din stanga sa. Acest lucru functioneaza deoarece formula utilizeaza referinte relative. Puteti folosi caracteristica Umplere automata pentru a copia formule in toate cele patru directii (sus, jos, stanga, dreapta).

Pentru a copia cu Umplere automata o formula,parcurgeti pasii urmatori:

1. Introduceti formula in prima celula.
2. Selectati celula cu formula. Celula (sau domeniul) activa are in coltul din dreapta jos o caseta mica denumita ghidaj de umplere.
3. Plasati indicatorul mouse-ului pe ghidajul de umplere. Atunci cand acesta devine o cruce neagra executati clic  si trageti in jos pentru a umple celulele cu copii ale formulei.
4. La sfarsitul domeniului, eliberati butonul mouse-ului. Celulele peste care ati tras vor fi umplute cu copii ale formulei.

Verificarea unei foi de lucru

Instrumentele de audit din Excel ajuta la urmarirea problemelor pe foile de lucru. De exemplu, valoarea care se vede intr-o celula ar putea fi rezultatul unei formule sau ar putea fi utilizata de o formula care sa produca un rezultat neasteptat. Comenzile de audit permit urmarirea relatiilor dintre celule si formule. Bara de instrumente de audit se deschide din meniul Tools (Instrumente), optiunea Formula Auditing (Formula de audit) si se bifeaza Show formula Auditing Tools (Afisare bara de instrumente de audit).

Evaluarea formulei se realizeaza urmand pasii:

1. Selectati celula in care este scrisa formula.
2. Din meniul Tools, alegeti optiunea Formula Auditing din care selectati butonul Evaluate Formula sau butonul Evaluate Formula din bara de instrumente de audit.
3. Apare fereastra de evaluare in care se evalueaza treptat formula prin apasarea succesiva a butonului Evaluate.

Trasarea erorilor permite localizarea tuturor celulelor care furnizeaza date unei formule afisand o valoare de eroare (de exemplu #DIV/0!). Trasarea erorilor se gaseste in meniul Tools, optiunea Formula Audit, se selecteaza butonul Trace Errors.

Din celula care cauzeaza eroarea se indreapta sageti rosii spre celula care detine eroarea si sageti albastre se indreapta spre celulele precedente celulei care cauzeaza eroarea. In tabelul urmator sunt prezentate mesajele de eroare si cauzele lor probabile. Trasarea erorilor se gaseste in meniul Tools, optiunea Formula Audit, se selecteaza butonul Trace Errors.

Daca eroarea provine dintr-o celula sursa si nu din celula activa, vor aparea sageti indicatoare care va ghideaza vizual spre posibilele surse de eroare. Liniile albastre indica celulele referite, iar liniile roaii va conduc la celula care a provocat eroarea. Daca nu apar sageti de indicare a erorii, sursa acesteia se gaseste in formula. Chiar daca nu exista erori, puteti trasa celulele dependente si precedente dintr-o formula executand clic pe butoanele Trace Precedents Trasare precedente si Traces DependentsTrasare dependente. De fiecare data cand executati clic pe un buton Trasare vor aparea sageti indicatoare pentru urmatorul nivel de precedenta sau dependenta. Butoanele Remove all Arrows (Eliminarea tuturor sagetilor) din bara de instrumente Formula Auditing (Formula de audit) elimina sagetile indicatoare, cate un nivel o data.

Valori de eroare

Trasarea precedentelor permite localizarea tuturor celulelor care furnizeaza date unei formule.

Trasarea precedentelor se gaseste in meniul Tools, optiunea Formula Audit, se selecteaza butonul Trace Precedents.

Sagetile de depistare albastre indica celulele de pe foaia de lucru activa care furnizeaza date unei formule si este posibil sa se mearg in adancime cu inca un nivel, trasandu-se toate celulele la care se refera celulele precedente. Daca o celula de pe o alta foaie de lucru sau dintr-un alt registru de lucru se refera la celula selectata, atunci apare o pictograma speciala pentru a indica acest fapt (celalalt registru de lucru trebuie sa fie deschis pentru a fi trasat).

Trasarea dependentelor permite st se arate care celule sau formule de pe foaia de lucru activa depind de o celula anume. Daca o celula de pe o alta foaie de lucru sau dintr-un alt registru de lucru se refera la celula selectata, atunci apare o pictograma speciala pentru a indica acest fapt (celalalt registru de lucru trebuie sa fie deschis pentru a fi trasat).

 Trasarea dependentelor se gaseste in meniul Tools, optiunea Formula Audit, se selecteaza butonul Trace Dependents.

Utilizarea referintelor

Referintele sunt constructii care permit identificarea celulelor individuale sau a grupurilor de celule. Ele sunt de fapt adresele celulelor in care Excel cauta valori. Referinta la o celula sau adresa celulei este formata din antetul coloanei si antetul randului (de exemplu A1, CF567).

In Excel sunt acceptate urmatoarele tipuri de referinte:

1. Referintele la celule (adrese relative) permit referirea la zone de celule. De exemplu, se face referire la toate celulele de la A1 la A7 tastand A1:A7. De exemplu, daca ati introdus valoarea 1 in celula A1 si valoarea 2 in celula B1, puteti scrie formula =A1+B1 in orice alta celula si rezultatul este 3. Daca veti modifica valorile din celulele A1 si B1, formula va continua sa le adune si sa ofere rezultatul corect.
2. Referintele absolute permit copierea de formule in celule noi si ajustarea referintelor pentru a corespunde cu mutarea in noua celula. De exemplu, daca formula "=A1+A2" a fost in celula A3 si aceasta formula a fost mutata in B3, atunci Excel rescrie formula astfel incat sa se citeasca "=B1+B2". Daca nu se doreste ca Excel sa modifice formulele atunci cand sunt copiate, se va pune semnul dolarului ($) inaintea oricarei referinte care se doreste sa nu fie modificata. De exemplu, daca formula de mai sus ar fi fost "=$A$1+$A$2" si formula ar fi fost copiata din A3 in B3 fara a modifica valoarea din A1, atunci noua formula ar fi fost "$A$1+B2". Practic o referinta absoluta este fixa si nu se modifica chiar daca mutati sau copiati formula.

Puteti combina intr-o formula adresele absolute cu cele relative. De exemplu, $A4 specifica programului Excel sa pastreze fixa coloana A, dar sa adapteze adresa randului la noua pozitie.

In imaginea urmatoare in celula O12 a fost introdusa o formula complexa (vezi bara de formule) care contine referinte relative si absolute.

3. Referintele nominale permit referirea la celule, zone de celule, nume de coloane sau formule utilizand nume descriptive. Toate celulele importante din foaia de calcul, in special cele in care aveti de gand sa schimbati datele pentru a rezolva probleme de genul "Ce-ar fi daca?", ar trebui sa aiba un nume, nu doar un numar. De asemenea, este posibila crearea de referinte nominale la constante numerice.
4. Referintele externe se refera la formule care au date din alt registru sau foaie de calcul decat cel/cea curent(a). O referinta la o foaie de calcul externa este compusa din numele registrului de calcul inclus intre paranteze drepte, urmat de numele foii de calcul separat de referinta la celula (care poate fi relativa sau absoluta) printr-un semn de exclamatie: [nume_registru]nume_foaie!referinta_celula. De exemplu "[Registrul1]Foaia1!A1" se va referi la celula A1 din foaia de lucru Foaia1 din registrul de lucru Registrul1.

Pentru a referi intr-o formula date din alte foi/registre de calcul se procedeaza astfel:

1. Incepeti sa construiti formula.
2. Cand ati ajuns la momentul introducerii referintei dintr-o alta foaie sau registru, executati clic pe eticheta acelei foi de calcul (este bine ca registrul din care preluati datele sa apara pe ecran intr-o fereastra vizibila).
3. Localizati celula la care doriti sa va referiti si executati clic pe ea. Daca veti referi un domeniu, trageti indicatorul peste el pentru a-l selecta. In bara de formule vor aparea numele registrului (daca e cazul), numele foii si referinta de celula.
4. Continuati construirea formulei prin introducerea restului de operatori. Daca formula mai necesita celule din alte foi (sau din foaia originala), repetati pasii b si c pentru a le adauga.
5. Cand formula este completa apasati tasta Enter.

In figura este prezentata o referinta externa: in celula B18 se insumeaza datele din domeniul C5:C7 din foaia de calcul "Buget LIS".

Utilizarea functiilor
O mare parte din capacitatea programului Excel de a rezolva aproape orice problema este determinata de lista sa extinsa de functii. Functiile oferite de Excel acopera domeniile statisticii, finantelor, ingineriei si matematicii. Functiile trebuie privite ca formule predefinite care preiau informatiile introduse si ofera un rezultat. Deci, ele reprezinta o metoda rapida de a introduce formule complexe.

Ca si in cazul formulelor, functiile incep cu semnul (=). Urmeaza apoi numele functiei, de obicei un cuvant scurt, o abreviere care indica ce face functia respectiva. Dupa numele functiei urmeaza un set de paranteze si in interiorul parantezelor se observa argumentele:

=Nume_functie(lista_argumente)

Argumentele pot fi numere, adrese, texte, valori logice, matrici, valori de eroare sau referinte de celule. Valorile folosite in cadrul argumentelor pot fi: o singura valoare, o referinta la o singura celula, o serie de referinte la celule, o serie de valori sau un domeniu. Intre argumente pot fi incluse valori constante sau formule. In cazul unei formule, aceasta poate contine alte functii. Daca argumentul unei functii contine alta functie, el se numeste imbricat. Separatorul de argumente este in general virgula, dar el poate fi diferit in functie de particularizarile de tara ale sistemului de operare.

Exista insa si functii care nu au nevoie de argumente (de exemplu functia NOW(), care returneaza data si ora curente). Functii diferite necesita argumente diferite. Unele argumente sunt obligatorii, altele sunt optionale.

Observatii:

• Utilizarea parantezelor este obligatorie, chiar daca nu exista nici un argument si nu trebuie lasat nici un spatiu intre numele functiei si prima paranteza. 
• Cand introduceti in bara de formule un nume de functie (de exemplu SUM), nu este nevoie sa-l scrieti cu majuscule. In momentul cand confirmati intrarea, Excel transforma automat literele mici din numele functtiei in litere mari. Daca programul nu raspunde in acest mod inseamna ca ati introdus probabil incorect numele functiei.

Pentru introducerea functiilor in foaia de lucru Excel puteti proceda astfel:

1. Tastati semnul (=) in bara de formule dupa care se scrie acronimul functiei. In momentul scrierii dupa prima paranteza vor aparea in fereastra paletei argumentele functiei respective. In acest caz trebuie sa fiti familiarizati cu sintaxa functiilor Excel.

2. Utilizati butonul Insert Function (Inserare functie) de pe bara de formule.
3. Folositi caseta de dialog Insert Function (Inserare functie) pe care o afisati prin derularea meniului butonului AutoSum (Insumare automata) de pe bara de instrumente Standard si alegand comanda More Functions (Mai multe functii).

4. sau alegeti din meniul Insert (Inserare) optiunea (Function Functie).

In cazul in care folositi caseta de dialog Insert Function (Inserare functie) primul lucru pe care trebuie sa-l faceti este sa alegeti categoria (din lista Selectati o categorie) care va cuprinde functia dorita. Fiecare categorie de functii rezolva un anumit tip de probleme. Excel include multe functii pentru operatii financiare si economice uzuale, unele dintre acestea fiind deosebit de complexe. Daca doriti afisarea argumentelor functiei si o scurta descriere a acestora, executati clic pe Ajutor pentru aceasta functie. Daca doriti insa si mai multe informatii despre functia selectata apelati la informatiile de ajutor ale programului prin intermediul butonului Asistentului Office.