Utilizarea formulelor

Utilizarea formulelor

Notiunea de formula

Formulele sunt elementele de baza ale unei foi de calcul Excel si pot fi folosite pentru a efectua operatii simple de adunare, scadere, inmultire, impartire, dar si calcule complexe de tip financiar, statistic ori stiintific. De asemenea, prin intermediul lor, se pot face comparatii si se poate opera asupra sirurilor de caractere.

Introducerea formulelor

In bara de formule:

Se selecteaza celula care va contine formula.

Se scrie semnul egal (=).

Se scrie o valoare, o referinta de celula, o functie sau un nume.

Daca formula este completa, se apasa Enter sau se executa un clic pe caseta Enter (cea marcata) din bara de formule. Daca formula nu este completa, se continua cu pasul urmator.

Se introduce un operator (+, - ,* etc.)

Se reia de la pasul 3.

Intr-o celula

Se executa dublu clic in celula respectiva sau se selecteaza celula si se apasa tasta F2.

Se continua cu pasii 2, 3, 4, 5, 6 ca mai sus.

Afisarea formulei in celula

Se executa dublu clic pe celula sau se apasa tasta F2.

Copierea formulei in alte celule

Daca celulele sunt alatu­rate, se glisea­za butonul de umple­re in celulele de destinatie.

Daca celulele nu sunt alaturate, se folosesc instructiunile Copy si Paste sau se trage conturul celulei avand tasta CTRL apasata.

Excel copiaza formula in celelalte celule si adapteaza referintele relative ale for­mulei.

Introducerea referintelor de celule prin indicare

Intotdeauna o celula este referita prin titlul liniei si al coloanei.

Se selecteaza celula in care se va introduce formula.

Se scrie semnul = (egal).

Se indica celula (adica se executa clic in celula) sau domeniul de celule (adica se selecteaza domeniul de celule) care se refera.

Se valideaza formula apasand tasta Enter.

Utilizarea referintelor relative in cadrul formulelor

Implicit, Excel utilizeaza referinte relative pentru adresarea celulelor, astfel ca, atunci cand se copiaza formula in noua celula, referintele se vor schimba conform noii pozitii.

Formulele pot fi afisate in format R1C1 (format linie coloana) prin alegerea comenzii Options din meniul Tools, prin selectarea etichetei General si marcarea casetei de validare R1C1. In aceste format, referintele se refera la celula curenta.

De exemplu, formula din celula C5, care este =B5+B5*C16, devine =RC[-1]+RC[-1]*R[11]C, in care C[-1] inseamna o coloana mai la stanga si R[11] inseamna 11 linii mai jos.

Utilizarea referintelor absolute in cadrul formulelor

Pentru a preveni modificarea referintelor de celule atunci cand se copiaza o celula intr-o noua pozitie, se folosesc referinte absolute. Acestea se indica prin plasarea simbolului $ (dolar) inaintea literei coloanei sau numarului liniei pe care dorim sa le 'inghetam'. In formatul R1C1, o referinta absoluta se obtine prin scrierea explicita a numarului liniei sau coloanei, fara a le incadra in paranteze patrate.

De exemplu, prin copierea formulei =B5*$B$18, singurul termen care se va modifica va fi primul.

Introducerea de referinte absolute se poate face in doua moduri:

Se pune semnul $ inaintea liniei sau coloanei care nu trebuie sa se modifice. In formatul R1C1 se elimina parantezele patrate.

Se muta cursorul de inserare in bara de formule astfel incat sa fie plasat in interiorul referintei de celula si se apasa tasta F4 (tasta de referinte absolute). La fiecare apasare a acestei taste, tipul de referinta se schimba.

Referirea altor foi din registrul de calcul

Se scrie in formula, inaintea referintei celulei, numele foii din care face parte, urmat de semnul ! (semnul exclamarii).

De exemplu, celula A1 din foaia Sheet 3 poate fi referita astfel: Sheet3!A1

Introducerea referintelor 3-D

Referintele 3-D refera un domeniu de celule cuprins in doua sau mai multe foi ale registrului.

De exemplu, formula

=SUM(Sheet2:Sheet4!$A$1:$A$9

realizeaza insumarea tuturor valorilor celulelor din domeniul $A$1:A$9 din fiecare foaie de calcul Sheet2. Sheet4 si creeaza un total general.

Operatori de referinta

Operator Exemplu Tip Rezultat

: SUM(A12 : C24) Domeniu Evalueaza ca o referinta unica celu­lele din zona dreptunghiula­ra definita de cele doua colturi.

, sau ; SUM(A12 : A24, B36) Uniune Evalueaza doua referinte ca fiind una singura.

spatiu SUM(A12 : A24 A16 : B20) Intersectie Evalueaza celulele comune celor do­ua referinte (daca nu exista celule comune se va intoarce rezultatul #NULL).

Denumirea domeniilor de celule

Domeniile cu nume au proprietatea de se redimensiona atunci cand le sunt adaugate sau sterse linii sau coloane.

Existenta numelor de domenii sau celule usureaza referirea la foi de calcul din alte registre, neavand nevoie de cunoasterea referintei de celule; o formula de tipul =BILANT.XLS!Vanzari va aduce in celula activa a foii de calcul curente valoarea din celula Vanzari din registrul BILANT.XLS.

Numele trebuie sa inceapa cu o litera sau cu simbolul de subliniere (_), iar in interior nu sunt permise caracterele spatiu si liniuta de unire.

Pentru a denumi celule sau domenii de celule, se selecteaza celula sau domeniul de celule si se atribuie denumiri alegand una din urmatoarele modalitati:

prin comanda Insert, Name, Define;

prin intermediul casetei de nume (plasata in stanga barei de formule);

prin comanda Insert, Name, Create, folosind text din foaia de calcul, text care de obicei consta in titluri de linii si coloane.

Observatie: Implicit, numele definite prin comanda Insert, Name, Define sau folosind caseta de nume vor fi aplicate intregului registru.

Pentru a putea aloca acelasi nume care sa refere mai multe domenii de celule din foi de calcul diferite, va trebui sa se creeze nume la nivel de foaie de calcul prin introducerea in caseta Names in Workbook a numelui foii inainte de numele domeniului de celule. Ex. Sheet2!Venit_Net. In cazul existentei a doua nume identice, unul definit la nivel de foaie si altul la nivel de registru, are prioritate cel definit la nivel de foaie.

Crearea de nume 3D

In caseta de text Refers to din caseta de dialog Define Name, se include o referinta 3D. De ex. pentru definirea unui nume care sa refere celulele B1:B12 din Foaia1 pana la Foaia 6 dintr-un registru, se scrie :

=Foaia1:Foaia6!B1:B12

Observatie: Daca se copiaza in alt registru o formula care foloseste un nume care exista deja in registrul respectiv, va fi afisata o caseta care intreaba daca se doreste pastrarea definitiei (Yes) sau inlocuirea ei cu cea din registrul sursa (No).

Denumirea formulelor si valorilor

Se poate denumi orice numar sau formula, nefiind necesar ca acestea sa se gaseasca in foaia de calcul. Pentru aceasta, se procedeaza astfel:

In meniul Insert se selecteaza Name, apoi Define.

Se selecteaza caseta Names in workbook si se introduce numele.

Se selecteaza caseta de text Refers to.

Se scrie numarul sau formula, editarea putandu-se face la fel ca in bara de formule. Pentru a utiliza tastele sageti la deplasarea in interiorul formulei, se apasa tasta F2 pentru a trece in modul editare; altfel, tastele sageti vor indica anumite celule din foaia de calcul.

Se alege OK.

Observatie: Daca in caseta de text Refers to se creeaza formule utilizand indicarea cu mouse-ul a referintelor de celule, Excel va lua in considerare doar referintele absolute, pentru ca in mod obisnuit, un nume este aplicat unei anumite celule din foaia de calcul. Se pot scrie si referinte relative, apasand succesiv tasta F4, care schimba modul de prezentare a referintelor.

Solutionarea erorilor din formule

Detectarea erorilor din formule prin evaluarea unei functii sau a unui termen

In bara de formule se selecteaza pe rand elementele componente ale formulei care pot produce eroarea respectiva.

Se apasa tasta F9, fapt care duce la inlocuirea portiunii selectate cu rezultatul calculat.

Se repeta procedeul cu diferitele parti ale formulei pana se gaseste eroarea.

Se apasa Esc sau Cancel pentru a reveni la forma initiala a formulei sau Enter daca se doreste ca rezultatul sa inlocuiasca formula.

Insumarea unui do­meniu folosind instrumentul AutoSum

Se executa clic pe celula in care dorim sa inseram formula SUM.

Se executa clic pe butonul AutoSum.

Se defineste domeniul de insumat prin tragere cu mouse-ul, dupa care se apasa ENTER.

Crearea numelor de celule folosind etichetele din celulele adiacente.

Se selecteaza un domeniu care include celulele ce urmeaza a fi denumite si celulele continand etichetele.

In meniul Insert, se indica Name, apoi se executa clic pe Create.

In caseta Create names in se selecteaza pozitia celulelor care contin etichetele ce vor da numele celulelor: sus, stanga, jos, dreapta.

Se executa clic pe OK.

Inserarea numelor in formule

Prin indicare:

Se executa clic pe celula nominalizata pe care dorim sa o includem in formula.

Cu ajutorul meniurilor:

Se muta cursorul de inserare in bara de formule (sau in celula) in pozitia in care trebuie inserat numele.

Se activeaza foaia sau registrul de calcul care contine numele referintei.

Se alege Insert, Name, Paste care afiseaza caseta Paste Name (lipire nume). Daca nu a fost atasat nume celulelor, domeniilor, formulelor sau valorilor, comanda Paste nu este accesibila.

Se selecteaza numele dorit din lista derulanta de nume.

Se executa clic pe OK, se completeaza formula si se apasa tasta Enter.

Inserarea functiilor in formule

Se pozitioneaza cursorul de inserare in bara de formule (sau in celula) in pozitia unde va fi inserata functia.

Se alege comanda Function din meniul Insert sau se executa clic pe butonul Paste Function.

Se alege functia dorita.

Utilizarea etichetelor in formule

Intr-o formula se pot introduce etichete de date, text intre ghilimele si valori de tip data si ora.

De ex. daca celula cu numele TOTAL_BUGET contine valoarea 34500, formula:

='Bugetul total este '&TOTAL_BUGET

va afisa urmatoarea informatie: Bugetul total este 34500

Pentru a efectua operatii aritmetice cu valori de tip data explicite, care nu se gasesc in celule, se folosesc formule ca cea de mai jos:

care calculeaza si afiseaza numarul de zile dintre cele doua date.

Schimbarea formulelor in valori

Se selecteaza celula care contine formula si se intra in modul editare prin apasarea tastei F2 sau prin clic in bara de formule sau dublu clic pe celula.

Se apasa tasta F9, apoi se apasa tasta Enter.