Lectia 7.1: Generarea formulelor utilizand referinte catre alte celule si operatori matematici (adunare, scadere, inmultire, impartire)

Preambul
Microsoft Excel dispune de toate operatiile matematice obisnuite si functii predefinite.
Cand construim o formula, pentru a informa aplicatia Excel ca dorim sa realizam acest lucru, trebuie sa introducem in primul rand simbolul = (egal), altfel informatiile introduse vor fi interpretate ca text, data, etc.
Inserarea unei formule intr-o celula se poate face in mai multe moduri:
– fie selectam celula dorita si introducem formula dorita;
– fie indicam valorile; dupa introducerea semnului = (egal), folosind mouse-ul, executam click in celula care contine informatia dorita;
– fie accesam meniul Insert function (Inserare functie) si in caseta aparuta introducem argumentele functiei alese.

Pasul 0
Descarcati arhiva care contine fisierul ajutator si dezarhivati-l.

Pasul 1
Putem scrie o formula inserand manual toate datele pe care dorim, de exemplu, sa le adunam.
Efectuati un click in celula D7. (1)
Apasati tasta Enter.
O alta modalitate de a construi formule presupune utilizarea mouse-ului.
Dupa inserarea semnului = (egal), putem specifica argumentele formulei printr-un singur click.
Efectuati un click in celula D5. (2)
Dupa ce am introdus operatorul de scadere (-), efectuati un click in celula D6. (3)
Fiecare celula selectata ca argument primeste o bordura colorata.
Acea culoare o au si argumentele introduse in cadrul formulei, astfel vom sti care celule intra in componenta acesteia.
Apasati tasta Enter.

Pasul 2
Atunci cand construim o formula, putem introduce ca argumente si celule care au in spate alte formule de calcul; in acest caz, Excel va prelua rezultatul acelei formule, si nu formula din spatele celulei.
Efectuati un click in celula D7. (1)
Dupa ce am introdus operatorul de inmultire (*), efectuati un click in celula D5. (2)
Apasati tasta Enter.
Se poate observa ca, desi celulele folosite ca argument contin formule in spate, acest lucru nu a influentat negativ rezultatul obtinut.
Efectuati un click in celula D9. (3)
Dupa ce am introdus operatorul de impartire (/), efectuati un click in celula D5. (4)
Apasati tasta Enter.
Indiferent daca am folosit in definirea formulei argumente sub forma celulelor ce au in spate o formula sau celule in care am introdus valori manual, rezultatul este cel corect.

Lectia 7.2: Cunoasterea erorilor standard asociate formulelor

Preambul
Formulele pot avea uneori ca rezultat erori.
Atunci cand in cadrul formulei se strecoara o greseala, Excel va afisa diferite avertismente, in functie de eroarea existenta.
Aceste avertismente sunt:
– #### – coloana nu este destul de lata pentru a afisa toata valoarea;
– #VALUE! – formula contine un argument eronat;
– #NAME? – formula contine text (un domeniu definit gresit, de exemplu);
– #REF! – formula se refera la o celula care nu exista;
– #DIV0! – formula are la numitorul impartirii valoarea 0;
– #N/A – o valoare nu este disponibila pentru o functie sau o formula;
– #NULL – apare atunci cand se specifica o intersectie a doua suprafete care nu se intersecteaza.

Pasul 0
Descarcati arhiva care contine fisierul ajutator si dezarhivati-l.

Pasul 1
Efectuati un click in celula I6. (1)
Atunci cand rezultatul unei formule este afisat sub forma unui mesaj de eroare, in dreptul celulei respective regasim un buton.
Efectuati un click pe butonul indicat. (2)
Efectuati un click pe optiunea Show Calculation Steps (Afiseaza pasii de calcul). (3)
Efectuati un click pe butonul Evaluate (Evalueaza). (4)
Se poate observa imediat ca eroarea consta in scrierea gresita a numelui functiei.
Efectuati un click pe butonul Close (inchidere). (5)

Pasul 2
Efectuati un click pe tab-ul Formulas (Formule). (1)
in acest tab, in cadrul grupului Formula Auditing (Auditul formulei), regasim un set de instrumente care ne ajuta sa depistam si sa remediem erorile aparute intr-o foaie de calcul.
Efectuati un click pe butonul Error Checking (Verificare erori). (2)
Cu ajutorul acestui utilitar putem afla mai multe detalii despre eroarea existenta, putem urmari pasii parcursi pentru realizarea acelui calcul, putem ignora eroarea sau o putem edita.
Efectuati un click pe butonul Edit in Formula Bar (Editare in bara de formule). (3)
Dupa ce am corectat eroarea, vom valida noua functie.
Pentru a verifica din nou registrul in cautarea altor erori, efectuati un click pe butonul Resume (Reluare). (4)
Efectuati un click pe butonul Close (inchidere). (5)

Pasul 3
Efectuati un click pe butonul indicat. (1)
Efectuati un click pe optiunea Trace Error (Indicare eroare). (2)
Optiunea selectata anterior a trasat o sageata dinspre celula care este folosita in cadrul formulei catre celula ce contine eroarea.
Astfel putem observa care celule intra in componenta formulei celulei al carei rezultat este o eroare.

Lectia 7.3: Intelegerea si utilizarea referintelor celulelor

Preambul
in cadrul formulelor, aveti posibilitatea sa utilizati referinte de celule pentru a face referire la:
– datele dintr-o celula din foaia de lucru;
– datele care sunt continute in diferite zone dintr-o foaie de lucru;
– date din celule din alte foi de lucru ale aceluiasi registru de calcul sau care fac parte din alte registre de calcul.
O referinta de celula se refera la o celula sau o zona de celule dintr-o foaie de lucru si poate fi utilizata intr-o formula, astfel incat Microsoft Excel sa gaseasca valori sau date pe care doriti sa le calculeze formula.

Referintele celulelor sunt de 3 feluri:
– referinte relative;
– referinte absolute;
– referinte mixte.

Referintele relative:
intr-o formula, o referinta relativa la o celula, cum ar fi A1, se bazeaza pe pozitia relativa a celulei care contine formula fata de celula la care se face referire.
Daca pozitia celulei care contine formula se modifica, se modifica si referinta.
Daca se copiaza formula de-a lungul randurilor sau coloanelor, referinta se ajusteaza automat.
Implicit, formulele noi utilizeaza referinte relative.
De exemplu, daca se copiaza o referinta relativa din celula C3, care are legatura cu celula A1, in celula D4 (cu un rand mai jos si o coloana la dreapta), referinta se ajusteaza automat de la A1 la B2 (cu un rand mai jos si o coloana la dreapta).

Referintele absolute:
O referinta absoluta intr-o formula, cum ar fi $A$1, face referire intotdeauna la o celula cu o amplasare anume.
Daca pozitia celulei care contine formula se modifica, referinta absoluta ramane aceeasi.
Daca se copiaza formula de-a lungul randurilor sau coloanelor, referinta absoluta nu se ajusteaza.
Implicit, formulele noi utilizeaza referinte relative si este necesar ca in unele cazuri sa fie comutate la referinte absolute.
De exemplu, daca celula C3 contine o referinta absoluta la celula A1, de forma $A$1, si copiem formula in celula D4, referinta va ramane aceeasi in ambele formule, si anume $A$1.

Referintele mixte:
O referinta mixta are fie o coloana absoluta si un rand relativ, fie o coloana relativa si un rand absolut.
O referinta coloana absoluta si rand relativ este de forma $A1, $A2, $B1 s.a.m.d.
O referinta coloana relativa si rand absolut este de forma A$1, B$1, A$2 s.a.m.d.
Daca pozitia celulei care contine formula se modifica, referinta relativa se modifica, pe cand cea absoluta ramane aceeasi.
Daca se copiaza formula de-a lungul randurilor sau coloanelor, referinta relativa se ajusteaza automat iar cea absoluta ramane aceeasi.
De exemplu, daca celula B3 contine o referinta relativa la celula A1 de forma A$1, si se copiaza formula in celula C3, referinta se ajusteaza de la A$1 la B$1.

Pasul 0
Descarcati arhiva care contine fisierul ajutator si dezarhivati-l.

Pasul 1
in cadrul exemplului din aceasta lectie, vom face conversia EURO-RON pentru cele trei produse din lista.
Efectuati un click in celula E10. (1)
Efectuati un click in celula D10. (2)
Dupa ce am introdus operatorul de inmultire (*), efectuati un click in celula F5. (3)
Apasati tasta Enter.

Pasul 2
in acest moment, am calculat pretul unui singur produs.
Efectuati un click in celula E10. (1)
Efectuati dublu click pe manerul de umplere. (2)
Se poate observa ca pentru ultimele doua produse, valoarea afisata este eronata.
De ce?
Pentru ca in cadrul formulei pe care am extins-o, existau adrese relative.
Cand formula a fost copiata, referintele s-au modificat, atat pentru pretul in EURO, cat si pentru cursul de schimb.
Efectuati dublu click in celula E11. (3)
Se poate observa ca s-a modificat adresa celulei care contine pretul in EURO, ceea ce este bine, dar s-a modificat si adresa celulei ce contine cursul de schimb, ceea ce nu este bine deloc.
Apasati tasta Esc (Escape).

Pasul 3
Efectuati un click in celula F10. (1)
Efectuati un click in celula D10. (2)
Dupa ce am introdus operatorul de inmultire (*), efectuati un click in celula F5. (3)
in celula F5 se regaseste cursul de schimb, si am dori ca adresa acestei celule sa se propage si la celelalate celule atunci cand extindem formula.
Cum se adauga o adresa mixta sau absoluta?
Se poate apasa tasta F4 in mod repetat, pana obtinem tipul de adresa dorit, sau putem introduce manual simbolul $.
Daca apasam o data tasta F4, atunci adresa va deveni absoluta ($F$5).
Aceasta inseamna ca, daca extindem formula si la alte celule, aceasta adresa va fi blocata si nu se va modifica.
Daca se apasa de doua ori tasta F4, atunci adresa va deveni relativa ($F5).
Aceasta inseamna ca, daca extindem formula si la alte celule, coloana F va fi blocata, dar se va modifica identificatorul randului.
Daca se apasa de trei ori tasta F4, atunci adresa va deveni relativa (F$5).
Aceasta inseamna ca, daca extindem formula si la alte celule, se va schimba identificatorul coloanei, dar randul va ramane acelasi.
Daca se apasa de patru ori tasta F4, atunci adresa va redeveni F5.
Aceasta inseamna ca, daca extindem formula si la alte celule, atat identificatorul randului, cat si al coloanei, se vor modifica.
Noi vom bloca atat identificatorul coloanei, cat si pe cel al liniei pentru celula care contine cursul de schimb.
Tot ce trebuie sa facem este sa apasam o data tasta F4.
Apasati tasta Enter.

Pasul 4
Efectuati un click in celula F10. (1)
Efectuati dublu click pe manerul de umplere. (2)
Spre deosebire de situatia anterioara, acum este calculat pretul in RON si pentru celelalte doua produse.
Efectuati dublu click in celula F11. (3)
Se observa ca adresa celulei ce contine pretul in EURO s-a modificat, dar adresa celulei ce contine cursul de schimb a ramas nemodificata.
Apasati tasta Esc (Escape).
Astfel se pot folosi adresele relative, absolute si mixte ale celulelor in cadrul formulelor.

Lectia 7.4: Crearea formulelor folosind suma, medie, minim, maxim, numarare

Preambul
Pentru a crea anumite registre de calcul, se folosesc functiile agregate.
Acestea sunt: SUM (Suma), COUNT (Numarare), AVERAGE (Medie), MIN (Minim), MAX (Maxim).

Pasul 0
Descarcati arhiva care contine fisierul ajutator si dezarhivati-l.

Pasul 1
Efectuati un click in celula J6. (1)
Sintaxa functiei SUM (Suma) este simpla: sum(Argument1;Argument2;…).
Dupa ce vom scrie numele functiei urmat de o paranteza rotunda, vom specifica argumentele.
Efectuati un click in celula G2. (2)
Efectuati combinatia de taste Ctrl + Shift + Sageata jos.
Apasati tasta Enter.

Pasul 2
Efectuati un click pe bara de derulare verticala. (1)
Functia COUNT (Numarare) contorizeaza numarul de celule care contin numere.
Pentru a obtine numarul de intrari dintr-o matrice de numere, putem utiliza aceasta functie.
Apasati tasta Enter.

Pasul 3
Functia AVERAGE (Medie) returneaza media aritmetica a argumentelor introduse in cadrul acesteia.
Apasati tasta Enter.

Pasul 4
Se pare ca atunci cand introducem aceste functii, trebuie sa selectam mereu aceeasi coloana cu date.
Pentru a nu repeta acest proces mereu, vom atribui un nume coloanei ce contine salariile.
Efectuati un click pe bara de derulare verticala. (1)
Efectuati un click pe antetul coloanei G. (2)
Efectuati un click pe tab-ul Formulas (Formule). (3)
Efectuati un click pe comanda Create from Selection (Creare din selectie). (4)
Atribuirea unui nume pentru un set de celule creste foarte mult ritmul de lucru, deoarece nu mai pierdem timp cu selectarea datelor, mai ales daca tabelele cu care lucram sunt voluminoase.
De aceea, vom folosi antetul tabelului ca denumire pentru domeniul definit pentru toate informatiile de pe respectiva coloana.
Efectuati un click pe butonul OK. (5)

Pasul 5
Efectuati un click in celula J9. (1)
Efectuati un click pe comanda Use in Formula (Utilizare in formula). (2)
Efectuati un click pe domeniul definit Salariul. (3)
Apasati tasta Enter.

Pasul 6
Efectuati dublu click pe domeniul definit Salariul. (1)
Apasati tasta Enter.
Astfel putem utiliza functiile agregate pentru a realiza diverse calcule.

Lectia 7.5: Functia logica IF (pentru una din doua valori specifice)

Preambul
Functiile conditionale sunt folosite atunci cand dorim sa luam decizii in functie de anumite conditii.
Functia IF este o functie care returneaza o valoare pe baza unui criteriu stabilit de utilizator.
Sintaxa functiei este: IF(logical_test;value_if_true;value_if_false).
– logical test este orice valoare sau expresie care poate fi evaluata ca fiind adevarata sau falsa. Acest argument poate utiliza orice operator de comparatie;
– value_if_true este valoarea returnata in cazul in care logical_test=TRUE (ADEVĂRAT). value_if_true poate fi o alta functie;
– value_if_false este valoarea returnata in cazul in care logical_test=FALSE (FALS). value_if_false poate fi o alta formula.

Pasul 0
Descarcati arhiva care contine fisierul ajutator si dezarhivati-l.

Pasul 1
Efectuati un click in celula E2. (1)
Efectuati un click in celula D2. (2)
Functia inserata se interpreteaza astfel: daca valoarea din coloana Salariul este mai mare decat 1900, afiseaza textul Da, in caz contrar afiseaza textul Nu.
Apasati tasta Enter.

Pasul 2
Dat fiind faptul ca acest tabel este unul dinamic, o proprietate a sa este aceea de a extinde automat o formula inserata intr-o coloana, fara a mai trage de manerul de umplere.
in continuare, vom modifica salariul unei persoane pentru a vedea ce se intampla cu valorile din coloana Peste prag?
Efectuati un click in celula D2. (1)
Apasati tasta Enter.
Dupa validarea noii valori, formula si-a modificat rezultatul, semn ca a fost introdusa corect.

Lectia 7.6: Despre bara de formule si caseta de functii

Preambul
Atunci cand spunem Excel, ne gandim la diagrame si calcule.
Cei care incep acum sa invete Excel si vor sa calculeze diferite formule folosind functii, trebuie sa cunoasca un lucru: Excel are un instrument ce ne ghideaza pe parcursul introducerii argumentelor in cadrul unei functii.
Persoanele ce au un nivel mai ridicat in lucrul cu functii nu fac altceva decat sa insereze functiile si sa aleaga argumentele.

Pasul 0
Descarcati arhiva care contine fisierul ajutator si dezarhivati-l.

Pasul 1
Efectuati un click pe bara de derulare verticala. (1)
Efectuati un click in celula D22. (2)
Efectuati un click pe tab-ul Formulas (Formule). (3)
in tab-ul Formulas (Formule) din Excel 2016, gasim o grupare a functiilor pe categoriile din care fac parte: financiare, logice, text, data si timp, cautare si referinte, matematice si trigonometrice, etc.
Efectuati un click pe butonul Insert Function (Inserare functie). (4)
in fereastra de dialog Insert Function (Inserare functie), regasim toate functiile din Excel, grupate de asemenea pe categorii.
Efectuati un click pe butonul Cancel (Anulare). (5)

Pasul 2
Atunci cand ribbon-ul este minimizat, cea mai rapida cale de a deschise caseta de functii este sa apasam butonul de functii din bara de formule.
Acesta este echivalent cu butonul Insert Function (Inserare functie) din tab-ul Formulas (Formule).
Efectuati un click pe butonul indicat. (1)
Efectuati un click pe butonul indicat. (2)
Din acest meniu putem alege o functie dintr-o anumita categorie. Aceste categorii se regasesc si in ribbon.
Apasati tasta Esc (Escape).
Efectuati un click pe butonul OK. (3)
Atunci cand alegem sa lucram cu o functie in fereastra de functii, va aparea un asistent (wizard), care ne indica ce argumente trebuie sa contina functia.
in acelasi timp putem urmari si care va fi rezultatul acelei operatii.
Efectuati un click pe butonul OK. (4)

Pasul 3
in acest moment, in celula activa a fost inserata functia SUM (Suma).
Dat fiind faptul ca tabelul este formatat folosind instrumentul Table (Tabel), dispunem de o optiune suplimentara.
Efectuati un click pe butonul indicat. (1)
in acest meniu avem la dispozitie trei optiuni:
– sa plasam formula pe randul Total Row (Rand de total);
– sa plasam formula in cadrul tabelului;
– sa plasam formula sub tabel.
Efectuati un click pe optiunea indicata. (2)
in aceasta maniera putem insera functii in cadrul formulelor, folosind instrumentele specializate din cadrul Excel 2016.