EFECTUAREA CALCULELOR SIMPLE ÎN MS EXCEL

7.1. Utilizarea butonului AutoSum

Butonul AutoSum este folosit pentru însumarea rapida a unui sir de date, astfel:

a)      Executam click pe o celula aflata în partea de jos a unei coloane sau la capatul unei linii

b)      Click pe butonul  din bara de instrumente standard

c)      Daca celulele selectate automat sunt diferite de cele 20520m129u dorite de noi, tragem mouse-ul peste celulele dorite

d)      Apasam Enter.

7.2. Utilizarea optiunii AutoCalculate pentru calcule rapide

Daca dorim sa efectuam calcule rapide, fara ajutorul formulelor, putem utiliza optiunea AutoCalculate astfel:

a)      Selectam celulele pentru care dorim sa aflam suma, sau media, sau pe care vrem sa le numaram (functia folosita este afisata în caseta AutoCalculate aflata pe bara de stare).

b)      Daca dorim sa modificam functia utilizata executam click dreapta pe caseta AutoCalculate si selectam o alta functie.

In figura puteti observa modul de utilizare a casetei AutoCalculate.

7.3. Scrierea formulelor

Pe lânga facilitatile rapide de calcul oferite de MS EXCEL, avem posibilitatea editarii propriilor formule.

Formulele simple implica operatii aritmetice simple precum: adunarea +, scaderea - , înmultirea *, împartirea celulelor /, ridicarea la putere ^ s.a.

Formulele complicate implica, pe lânga operatiile simple si utilizarea de functii matematice (medie aritmetica, geometrica, deviatie standard s.a.)

Formulele pot fi aplicate celulelor din aceeasi foaie de calcul, din foi diferite sau chiar din registre de lucru diferite.

Important: toate formulele MS EXCEL încep cu semnul =

Pentru scrierea unei formule procedam astfel:

a)      In celula dorita tastam semnul =

b)      Pentru fiecare celula ce intra în componenta formulei efectuam click, inserând de la tastatura si operatorii necesari (+, - , *, / etc)

c)      Daca în formula sunt necesare celule din alte foi de calcul sau registre de lucru (fisiere Excel) atunci efectuam click pe acestea.

7.4. Editarea unei formule

Pentru aceasta se executa:

a)      Dublu click pe celula; adresele si numele celulelor folosite în formula se vor colora diferit, putându-se usor observa care sunt celulele la care se refera formula

b)      Selectam si înlocuim ce dorim sa modificam astfel

Pentru înlocuirea adresei unei celule cu a alteia o selectam în formula si apoi executam click pe celula înlocuitoare

Pentru înlocuirea unui domeniu executam dublu click si tragem mouse-ul peste ambele adrese ca sa le selectam (adresa de început si cea de sfârsit) apoi, în foaia de calcul, tragem mouse-ul peste domeniul înlocuitor

Pentru înlocuirea unei valori constante sau a unui operator aritmetic le selectam si tastam din nou valorile sau operatorii înlocuitori

In figura puteti observa ca celulele incluse în formula sunt marcate diferit

7.5. Utilizarea AutoFill pentru copierea unei formule

Pentru aceasta procedam dupa cum urmeaza:

a)      Scriem doua rânduri de date; spre exemplu cantitate si pret unitar

b)      In cea de-a treia coloana vom calcula pretul total dupa formula cantitate x pret unitar = pret total. Scriem aceasta formula doar în prima linie

c)      Cu mouse-ul tragem în jos semnul +

d)      Eliberam butonul mouse-ului când am terminat de introdus formula.

Observatie: Când scriem o formula care utilizeaza adrese relative, aceste adrese se modifica singure la copierea formulei, dupa cum se observa si din figura ce urmeaza.

Daca, în formula, trebuie ca adresa uneia din celulele folosite sa nu se modifice, atunci folosim adresa ei absoluta. Spre exemplu, în loc de = A1*C1, vom scrie $A$1*C1 - celula A1 va ramâne fixa la utilizarea AutoFill, adica la tragerea în jos cu mouse-ul în loc de A2*C2, pentru linia a doua, formula va fi A1*C2.

7.6. Formule ce includ date din mai multe registre de calcul

Când avem nevoie sa includem în formule adrese de celule din alte registre de calcul, cel mai usor este sa deschidem aceste registre în aceeasi fereastra.

Când registrul de lucru contine formule ce includ adrese de celule din alte registre de lucru (fisiere Excel), acesta se va numi registru de calcul dependent, iar la deschiderea ulterioara a lui, vom fi întrebati daca dorim sa actualizam informatiile legate.

Avem doua optiuni:

a)      Yes - si formula va fi actualizata cu valorile din celelalte registre, chiar daca acestea s-au modificat

b)      No - si formula va afisa vechile valori, deci nu va fi actualizata.

c)      Daca deschidem registrele sursa în paralel cu registrul dependent, modificarea valorilor din acestea va conduce automat la actualizarea valorilor formulei, fara a mai fi interogati despre aceasta.

In figura se poate observa modul de scriere a unei formule ce utilizeaza adrese de celule din registre diferite de lucru.

7.7. Auditul unei formule

In cazul formulelor complicate scrise mai demult, s-ar putea sa fie nevoie sa ne reamintim modul în care au fost create. Pentru aceasta MS EXCEL pune la dispozitia noastra procedura de Audit a unei formule, care ne arata care sunt celulele folosite în formula si care sunt celulele dependente de aceasta formula.

Pentru afisarea barei de instrumente pentru audit, în meniul Tools selectam Auditing si apoi efectuam click pe Show Auditing.

Pentru identificarea celulelor sursa ale unei formule procedam astfel:

a)      Click pe celula care contine celula

b)     
în meniul Tools selectam Auditing si apoi efectuam click pe Trace Precedents. Vom vedea pe foia de calcul liniile care leaga celulele sursa de formula, ca în figura de mai jos:

Pentru identificarea celulelor dependente de o formula procedam astfel:

c)      Click pe celula care contine celula

d)      în meniul Tools selectam Auditing si apoi efectuam click pe Trace Dependents. Vom vedea pe foia de calcul liniile care leaga celulele dependente de formula, ca în figura de mai jos:

8. UTILIZAREA FUNCŢIILOR MS EXCEL

8.1. Scrierea unei formule care include functii

Procedam astfel:

a)      Click pe celula care va contine functia

b)      Click pe butonul

c)      Click pe o categorie de functii si dublu click pe functia dorita

d)      In caseta Formula Palette vom executa click în caseta pentru argument si citim informatiile ajutatoare afisate

e)      Selectam celula sau celulele ce vor forma argumentul functiei

f)        Click pe OK

In figurile urmatoare sunt ilustrate casetele de dialog utilizate

8.2. Functii uzuale

In tabelul urmator sunt prezentate câteva functii utile:

8.3. Functia COUNTIF

Functia COUNTIF numara câte articole sunt dintr-un anumit produs. Spre exemplu, dorim ca într-un tabel sa afisam de câte ori s-a achizitionat un anumit reper

Pasii pentru aplicarea acestei functii sunt:

a)      Click pe celula unde dorim sa plasam rezultatul

b)      Click pe si din lista de functii alegem COUNTIF

c)      In caseta de dialog, în câmpul Range selectam domeniul unde se va face numararea

d)      In caseta de dialog, în câmpul Criteria selectam celula al carei continut este identic cu cel pentru care dorim sa facem numararea

e)      Apasam OK

In figura este prezentat un exemplu privind numararea reperului "planetare", la baza coloanei Reper.

8.4. Functia SUMIF

Aceasta functie însumeaza doar valorile care satisfac o conditie impusa. Lucreaza asemanator cu COUNTIF. Spre exemplu, dorim sa însumam toate valorile din câmpul Pret total pentru valoarea câmpului Reper= "planetare".

Din figura de mai sus retinem ca:

Range = domeniul caruia îi apartine criteriul dupa care se face însumarea

Criteria = criteriul dupa care se face însumarea

Sum_range = domeniul pe care se executa însumarea

Observatie: Criteriul de însumare este obligatoriu inclus în domeniul Range

In cazul nostru Range este domeniul ce include toate reperele (de la D2 la D16),

Criteria este "planetare", adica D2, iar Sum_range este intervalul ce include valorile din câmpul Pret total, adica de la G2 la G16.

In cazul în care avem nevoie de mai multe criterii, putem folosi Conditional Sum

Wizard, astfel:

a)      Din meniul Tools selectam Wizard si executam click pe Conditional Sum

b)      Selectam domeniul pe care dorim sa facem însumarea conditionata si facem click pe Next

c)      Stabilim coloana de adunat si criteriul; daca sunt mai multe criterii, executam click pe Add Condition si indicam si pe cel de-al doilea criteriu; executam click pe Next

d)      La pasul 3 putem alege sa afisam numai suma sau suma si o eticheta

e)      La pasii 4 si 5 putem stabili unde sa plasam eticheta si suma în foaia de calcul (se urmeaza instructiunile din caseta de dialog); click Next

f)        Click Finish

In figura de mai jos este ilustrata caseta de dialog de la Conditional Sum Wizard

8.5. Functia IF

Aceasta functie determina valoarea unei celule în functie de un criteriu.

Functia IF lucreaza astfel: daca (IF) o afirmatie este adevarata, atunci (THEN) celula ia prima valoare, daca nu (OTHERWISE) celula capata cea de-a doua valoare.

Functia IF poate fi inclusa (îmbricata) într-o alta functie IF. Ea suporta pâna la 7 niveluri de îmbricare. Aceasta functie este ilustrata în figura care urmeaza.

8.6. Functiile VLOOKUP si HLOOKUP

In practica s-ar putea sa avem nevoie ca, într-o coloana (sau rând) sa scriem calificative, dependente de valori numerice dintr-un tabel aflat pe o alta foaie sau registru de calcul.

In acest caz vom utiliza functiile VLOOKUP sau HLOOKUP (pentru scrieri într-un rând).

In continuare vom arata modul de utilizare al functiei VLOOKUP (HLOOKUP folosindu-se similar). Sa presupunem ca, într-un tabel unde avem calculate abaterile de la dimensiunea standard trebuie sa completam o coloana cu urmatoarele observatii (Rebut - piesa are abateri > 100, Necesita rectificare - piesa are abateri >50 si <100 si Buna - piesa are abateri < 50. Pentru aceasta procedam astfel:

a)      Intr-o foaie de lucru cream un tabel simplu de cautare, ce contine valorile pe care le cautam. Conditia pe care trebuie sa o îndeplineasca tabelul este ca valorile numerice sa fie în stânga si sa fie sortate în ordine crescatoare; putem defini tabelul printr-un nume (Vezi cap.2.5[l1] .) si sa ne referim la el prin formula folosindu-i numele

b)      Executam click pe celula unde vrem sa apara rezultatul

c)      Click pe butonul

d)      Selectam functia VLOOKUP

e)      In caseta de dialog, la optiunea Lookup_value executam click pe prima celula unde se afla valoarea pentru care va fi scrisa observatia

f)        La optiunea Table_array scriem numele tabelului de cautare, pe care l-am numit STAS; acest tabel îl mai puteam selecta cu mouse-ul, daca nu dorim sa-i dam un nume

g)      In caseta Col_index_num tastam cifra 2, care corespunde numarului coloanei din tabelul STAS unde exista observatia ce trebuie inserata în tabelul principal

h)      La Range_lookup scriem TRUE (adevarat) daca dorim ca evaluarea pieselor sa se faca pe intervale de valori sau False (fals) daca evaluarea trebuie facuta exact pe valoare

i)        Apasam butonul OK

j)        Pentru completarea tuturor celulelor din tabelul principal folosim optiunea AutoFill

In figura urmatoare este prezentat modul de utilizare a functiei VLOOKUP

8.7. Functiile LEFT si RIGHT

Aceste functii returneaza primele x caractere din extremitatea stânga (sau dreapta) a unei celule sau domeniu de celule. Sunt utile în cazul în care dorim sa cream un câmp numit Cod Reper, spre exemplu, care sa preia primele x caractere din denumirea unui reper. Modul de completare este ilustrat în figura urmatoare.

8.8. Functia CONCATENATE ( & )

Aceasta functie alatura valorile afisate în doua sau mai multe celule. Este necesara, spre exemplu, când avem doua câmpuri - Nume si Prenume pe care dorim sa le unim într-unul singur Numele si Prenumele. Pentru aceasta se procedeaza în modul urmator:

a)      Inseram o coloana care va contine numele întreg

b)      Tasam = si executam click pe o primul nume de persoana

c)      Tastam semnul & si un mic spatiu între ghilimele

d)      Tastam din nou semnul & si executam click si pe prenumele corespondent

e)      Tastam Enter

Formula o completam pentru toate înregistrarile cu AutoFill.

Figura de mai jos ne arata modul de efectuare a concatenarii.