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.

Introducerea formulelor de tip matrice

Matricile sunt domenii dreptunghiulare de formule sau valori pe care Excel le trateaza ca fiind un grup. Unele functii si formule care lucreaza cu matrici returneaza rezultate care sunt tot matrici sau pot furniza un rezultat afisat intr-o singura celula. Atunci cand sunt folosite pentru inlocuirea formulelor repetitive, memoria utilizata este mai mica. Chiar in cazul unei matrice de formule care implica mai multe celule, programul Excel va pastra in memorie doar o singura formula. Pentru a introduce o formula de tip matrice se procedeaza astfel:

Se selecteaza domeniul care va contine formula de tip matrice (atentie la ce urmeaza sa fie acest rezultat: o matrice sau o singura valoare!).

Se introduce formula prin scriere propriu-zisa sau prin indicare cu mouse-ul.

Se apasa combinatia Shift+Ctrl+Enter pentru a introduce formula ca fiind de tip matrice. Aceasta apare intre acolade . De exemplu, in foaia prezentata mai jos calculam costurile prin inmultirea preturilor cu cantitatile, folosind o formula de tip matrice. Acoladele simbolizeaza ca formula este o matrice si ca domeniul matrice trebuie sa fie tratat ca o entitate unica. Nu se pot insera linii sau coloane in acest domeniu si nu este posibila stergerea componentelor sale sau editarea unei singure celule. Pentru a modifica o matrice, ea trebuie selectata mai intai.

Se pot introduce functii care opereaza cu valori din matrice matematice. Ele folosesc ca date de intrare matrici de valori si furnizeaza o celula sau o matrice de rezultate.

Sa presupunem ca in celula D13 dorim sa afisam totalul general, fara a mai calcula separat valorile. Se poate face acest lucru, intr-o singura celula cu o formula de tip matrice, astfel:

=SUM(B2:B11*C2:C11) Formula se introduce apasind combinatia de taste Shift+Ctrl+Enter.

Calculul matricilor de rezultate

Prezentam mai jos o singura formula de tip matrice, care poate inlocui mai multe formule obisnuite. Formulele din celulele C17 si D17 realizeaza completarea datelor, conform cu elementul din A17. De exemplu, formula din C17 insumeaza cantitatile corespunzatoare elementului 'Carti' aflat in A17, din domeniul C2:C12. Functia IF compara continutul celulei A17 cu valorile din fiecare celula a domeniului A2:A12. Cand este gasit un element care verifica aceasta conditie, la totalul facut de functia SUM, se adauga elementul corespondent din domeniul C2:C12. Aceasta formula nu va functiona corect, daca nu va fi introdusa ca o formula de tip matrice! In celula D17 a fost introdusa urmatoarea formula:

E simplu ce calculeaza, nu

Afisarea formulei in celula

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

Copierea formulei in alte celule

Daca celulele sunt alaturate, se gliseaza butonul de umplere 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 formulei.

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 celulele din zona dreptunghiulara definita de cele doua colturi.

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

spatiu SUM(A12 : A24 A16 : B20) Intersectie Evalueaza celulele comune celor doua 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.

Aplicarea numelor

In cazul in care formulele sunt create inaintea numelor si se doreste ca aceste nume sa apara in formule, va fi necesara o operatie de aplicare a numelor. Pentru a aplica nume existente formulelor care contin referinte de celule, se parcurg urmatoarele etape:

Se selecteaza celula sau domeniul de celule.

Se alege comanda Insert, Name, Apply, care va fisa caseta de dialog Apply Names.

Se selecteaza numele dorite din caseta de listare Apply Names, folosind tastele Shift sau Ctrl in combinatie cu un clic de mouse pentru a selecta mai multe nume adiacente sau neadiacente.

Se marcheaza caseta de validare Ignore Relative/Absolute pentru ca numele sa inlocuiasca referintele relative si absolute. In mod normal, aceasta caseta trebuie sa fie selectata; deselectarea ei face ca numele absolute sa fie aplicate referintelor absolute si numele relative celor relative.

Se marcheaza casetele de validare Use Row si Column Names daca se doreste ca Excel sa denumeasca referintele celulelor situate la intersectia dintre o linie si o coloana de nume.

Se selecteaza Options care pune la dispozitie urmatoarele optiuni:

Omit Column Name if Same Column (omitere nume de coloana in cazul coincidentei de coloana)

Omit Row Name if Same Row (omitere nume de linie in cazul coincidentei de linie)

Se alege OK.

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