Procesorul de tabele Excel

Procesorul de tabele Excel

1. Concepte generale

Procesorul de tabele reprezinta un program generator de aplicatii care gestioneaza date numerice prezentate sub forma matriceala (organizata pe linii si coloane).

Principalele caracteristici ale procesorului de tabele Excell sunt:

a)     ofera o gama larga de functii simple sau complexe capabile sa permita utilizatorului sa-si construiasca modele proprii de analiza si diagnoza ;

b)     genereaza un spatiu de lucru prin ansamblul foilor de calcul ;

c)     ofera posibilitatea formatarii spatiului alocat foilor de calcul, cu facilitati de vizualizare si protectie, conferind astfel siguranta in exploatarea aplicatiilor ;

d)     dispune de o baza proprie de modele (bugete, scenarii etc) care pot fi personalizate in functie de necesitatile utilizatorilor ;

e)     posibilitati avansate de de gestionare si de interogare a bazelor de date (construirea de criterii, filtre proprii, extragere rezultate in locatii distincte, etc) ;

f)      ofera un ansamblu de instrumente de asistare a deciziei care permit utilizatorilor :

sa realizeze calculul automat al unor serii de date prin intermediul subtotalurilor, informatiile fiind grupate pe baza unor criterii distincte ;

sa centralizeze informatiile aferente tabelelor pe baza unor bugete consolidate ;

sa atinga o valoare obiectiv prin modificarea unui singur parametru ;

optimizarea solutiilor avand drept punct de pornire o anumita variabila sau prin intermediul solverului de probleme;

sa modifice ordinea de regrupare a campurilor bazelor de date cu ajutorul tabelelor pivot;

sa gestioneze un set de scenarii (bugete) predefinite.

g)     dispune de o interfata Visual Basic care confera utilizatorului posibilitatea construirii unor functii proprii carora li se asociaza proceduri automate aferente unui set de instructiuni cu un grad ridicat de repetabilitate

Elemente conceptuale de baza.

Unitatea structurala a unui processor de tabele este foaia de calcul. Aceasta se defineste ca un spatiu plan bine delimitat si organizat pe linii si coloane sub forma de matrice (coloanele sunt identificate prin literele mari ale alfabetului si combinatii, iar liniile prin cifre intre 1 si 65536). La intersectia dintre linii si coloane se regasesc celulele ale caror coordonate sunt identificate prin mai multe tipuri de adrese (referinte) :

a)              relative (ale caror coordonate se modifica pe parcursul unei aplicatii, spre exemplu D5, D8, E14 etc) ;

b)             absolute (recunoscute prin prezenta semnului $ inaintea coordonatelor celulei respective, spre exemplu $D$5) ;

c)              mixte : D$7 (varuaza coloana), $D7 (variaza linia).

Transformarea unei adrese relative intr-o adresa absoluta se realizeaza prin selectarea celulei respective si actionarea tastei F4.

In cadrul unei cellule se pot regasi mai multe tipuri de informatii:

siruri de caractere (texte),

numere,

date calendaristice,

simboluri speciale,

expresii de calcul sau formule care sunt precedate intotdeauna de semnul =.

In vederea construirii expresiilor de calcul, in cele mai multe cazuri, se apeleaza la anumite functii Excell, sintaxa fenerala fiind urmatoarea :

=denumire functie (argumente)

In raport cu modalitatea de amplasare a celulelor in structura unui tabel, sintaxa functiilor variaza:

a) Celule alaturate (plaja de valori)

=denumire functie (arg1 : argn)

Exemplu: =sum(E9:E15)

b) Celule izolate

=denumire functie (arg1,arg2, . ,argn) sau

=denumire functie (arg1;arg2; . ,argn)

2. Utilizarea functiilor Excell

Procesorul de tabele Excell regrupeaza functiile in doua mari categorii :

a)     functii predefinite: matematice si trigonometrice, statistice, de informare, logice, de cautare si consultare, data si ora, text sau sir de caractere, frinaciare, baza de date.

b)     functii proprii, definite de catre utilizatori.

Functiile matematice si trigonometrice permit efectuarea diferitelor tipuri de calcule pentru rezolvarea unor aplicatii care solicita instrumente matematice si trigonometrice de uz curent.

Un exemplu de aplicatie simpla este prezentat in tabelul urmator (Tab.1). Sopul este a se determina cantiatea de produse vandute, valoarea totala a vanzarilor si comisioanele catre distribuitori.

Principalele functii matematice si trigonometrice:

=ROMAN(numar): converteste numerele din format numeric arab in format roman.

=POWER(numar;putere):returneaza rezultatul ridicarii unui numar la o putere.

=SQRT(numar): calculeaza radacina patrata dintr-un numar.

=FACT(numar): calculeaza factorialul unui numar pozitiv

=ROUND(X;numar zecimale): rotunjeste argumentul X la un numar specificat de zecimale

SUMPRODUCT
SUM
SUMIF
AUTOSUM

SUM(lista): aceasta functie aduna valorile dintr-o lista precizata drept argument.

Aceasta este singura functie care are echivalent un buton de comanda AUTOSUM ( ) care permite insumarea valorilor aferente celulelor dispuse pe veriticala/orizontala pana in punctul in care se intalneste primul semn de discontinuitate.

PRODUCT (Arg1,Arg2, . .Argn) este functia care multiplica valorile continute iantr-o lista si care va asigura calculul valorii la nivelul fiecarui produs.

Unul dintre avantajele Excell consta in posibiliatatea copierii unei formule dintr-o zona sursa intr-una sau mai multe locatii destinatie, datele fiind actualizate in mod automat Acest lucru se realizeaza prin pozitionarea cursorului in celula sursa (in exmplul de mai sus in celula E10, dupa care din punctul situat in coltul din dreapta jos al celulei sursa se extinde formula pe orizontala sau pe verticala).

SUMPRODUCT(lista) este functia care multiplica valorile situate in celulele corespondente si aduna rezultatele. Un mod de aplicare pentru exemplul de mai sus ar fi calculul sumei valorice a produselor apeland la campurile cantitate si pret.

SUMIF(camp de evaluat;criteriu;camp de insumat) este functia care aduna continutul aferent celulelor care indeplinesc o anumita conditie.

Functii statistice

a)     max(lista) :extrage valoarea maxima dintr-o lista;.

b)     min(lista): extrage valoarea minima dintr-o lista;

c)     average(lista): caluleaza valoarea medie aferenta argumentelor unei liste.

d)     Count (Lista) este functia cu care dintr-o anumita lista se numara elementele unei liste formata din valori numerice.

e)     CountA(lista), este functia cu care numara elementele unei serii alfanumerice sau de tip text.

f)      Countif(camp de evaluat, criteriu):numara valorile unei liste care indeplinesc un anumit criteriu precizat de catre utilizator.

Functiile de informare si functiile logice

isblank(*) : evalueaza daca argumentul "*" este sau nu o celula vida.

istext(*): se numeste test de sir de caractere si verifica daca argumentul "*" este sau nu un sir de caractere si returneaza expresia "true" sau "false" in functie de rezultatul evaluarii.

isnumber(*): se numeste test numeric si evalueaza daca argumentul "*" este un numar si returneaza "true" sau "false" in functie de rezultatul evaluarii.

=ISNONTEXT(X) verifica daca argumentul X nu este un sir de caractere, returnand TRUE in caz de adevar sau FALSE in caz contrar

Functii logice

=IF(conditie;X;Y) testeaza argumentul conditie si in functie de rezultatul evaluarii logice genereaza argumentul X, altfel genereaza argumentul Y.

=AND(ev.log1;ev.log2; . ) returneaza TRUE daca toate argumentele functiei sunt adevarate, iar in caz contrar, returneaza FALSE.

=OR(ev.log1;ev.log2; . ) returneaza TRUE daca oricare dintre argumentele este adevarat, iar FALSE, in cazul in care toate argumentele sunt false.

=NOT(ev.log1;ev.log2; . ) inverseaza valoarea argumentului returnand dupa caz TRUE sau FALSE.

=TRUE() returneaza valoarea logica de adevar TRUE.

=FALSE() returneaza valoarea logica de adevar FALSE.

Exemplul 1.

Se considera un tabel cu structura urmatoare

Sa se calculeze suma valorica a produselor in celula E18, apeland in mod distinct la campurile cantitate si pret.

=PRODUCT(C9,D9)

Sa se calculeze suma comisioanelor in celula F18: =SUM(Comision)

Sa se calculeze suma comisioanelor numai pentru acele produse a caror valoare depaseste 7000000 lei.

E20: =SUMIF (E9:E15,'>7000000',F9:F15)

Sa se afle valoarea maxima obtinuta pe produs

E23: =MAX(E9:E15)

Sa se afle valoarea minima obtinuta pe produs

E24: =MIN(E9:E15)

Sa se afle valoarea medie a valorii produselor vandute

E25: =AVERAGE(E9:E15)

Sa se afle numarul de produse vandute

E26: =COUNT(E9:E15)

Sa se afle numarul de produse a caror valoare este mai mica de 500000 lei

E27: =COUNTIF (E9:E15,'<5000000')

Exemplul 2.

C39: =IF(OR(ISTEXT(B39), ISBLANK(B39),B39<=0), FALSE(),TRUE())

C40: =IF(OR(ISTEXT(B40), ISBLANK(B40),B40<=0), FALSE(),TRUE())

Pentru impozit

=IF(NOT(C39=FALSE()), IF(B39<=500000,B39*10%, IF(AND(B39>500000,B39 <=1500000),50000+(B39-500000)*20%, IF(AND(B39>1500000,B39<=2500000), 250000+(B39-1500000)*30%, 500000+(B39-2500000)*40%))),0).

Exemplu pentru salarii in tabelul 2

!. Argementele x si y reprezinta mesaje, deci se trec intre "", →"expresie"

Salariu brut<=2800000 → " se acorda o prima de 2000000"  

Salariu brut<=5000000 → »se acorda o prima de 1000000 »

Salariu brut>5000000 → »se acorda o prima de 500000 »

Celula E37 :

=if(B37<=2800000,  " se acorda o prima de 2000000", if(B37)<=5000000, " se acorda o prima de 1000000", " se acorda o prima de 500000"))

2. Argumentele x si y reprezinta expresii de calcul.

Exemplu : Impozitul pe salarii se calculeaza diferential pe transe avand in vedere urmatoarele conditii :

A)   daca salariul brut este mai mic de 2800000 lei, impozitul reprezinta 10% din salariul brut.

B)    altfel, daca salariul brut este mai mic de 4000000 lei, impozitul reprezinta 15% din salariul brut.

C)    altfel, daca salariul brut este mai mic de 7000000 lei, impozitul reprezinta 18% din salariul brut.

D)   altfel, impozitul reprezinta 25% din salariul brut.

Celula D37 :

+if(B37<=2800000;B37* 10%;if(B37<=4000000;B37* 15%; if(B37<=7000000;B37* 18%;B37*20%)));

=and(evaluare_logica1, evaluare_logica2, . ..): returneaza expresia TRUE daca toate argumentele sunt adevarate;in caz contrar afiseaza FALSE;

or(evaluare_logica1, evaluare_logica2, . ..): returneaza expresia TRUE daca oricare dintre argumenteeste adevarat, FALSE in rest;

Exemple

Daca salariul brut se plateste in dolari ($), atunci pentru obtinerea rezultatului final se va tine cont de cursul dolarului la momentul respectiv

USD : 28000

EUR : 36000

LEI :valoarea ramane aceeasi cu valorile trecute in coloana B.

In aceasta situatie impozitul se calculeaza avand in vedere urmatoarele restrictii :

a)     pentru salarii platite in $ : daca valoarea e mai mica sau egala cu 300$, impzitul este de 10% :altfel, valoarea este de 10% :

b)     pentru salarii platite in euro : daca valoarea este mai mica sau egala cu 200 euro, impozitul este de 12 » : altfel, valoarea este de 15% ;

c)     pentru salarii in lei : daca valoarea este mai mica sau egala cu 2800000, impozitul este 0% ;altfel, valoarea este de 11%.

Pentru curs

Pentru determinare impozit :

Celula D37 :

=if(and(F37="USD";B37<=300); B37*10%*$C$43;if(and(F37="USD";B37>300); B37*20%*$C$43; if(and(F37="EUR"; B37>200);B37*15%*$C$42; if(and(F37="EUR"; B37<=200);B37*12%*$C$42; if(and (F37="LEI";B37<=2800000);0; B37*11%)))));

Functii de cautare si consultare

Aceste functii asigura identificarea si recuperarea unor informatii dintr-un table de consultare intr-o baza de date finala in raport cu un element cheie.

A) Consultare pe verticala.

=VLOOKUP(cheie de consultare;camp sau tabel de consultare;numar coloana de recuperat)

cheie : valoarea dupa care are loc cautarea (adresa absoluta/relativa sau nume de camp)

camp (tabel) de consultare: campul asupra caruia opereaza consultarea prin cautarea valorii cheii precizate anterior.

Coloana de recuperate> numarul coloanei (numerotarea incepe cu 1) de unde va fi recuperate informatia gasita in tabelul de consultare.

Exemplu : Consideram o factura pe 8 coloane (tabelul 3)

Se solicita recuperarea rubricilor denumire produs, prêt, prin consultare verticala, in functie de codul produsului, cautarea fiind realizata intr-un nomenclator al produselor cu urmatoarea structura (tabelul 4):

Tabelul 4

Etape de lucru :

Tabelul sursa se impune a fi ordonat in functie de campul cheie (cod produs ) :

selectie tabel (A20 :C28)

-Data/Sort/Sort By/Cod_produs

denumirea tabelului sursa:

Insert/Name/Define:

TCV (Tabel de Cautare pe Verticala) sau Produse

introducerea codurilor produselor in prima coloana din baza de date (A12:A16)

4 Pentru rubrica denumire produs din celula B22 se introduce formula

=VLOOKUP(A12;TCV;2), unde A12=camp cheie, TCV=tabelul in care se realizeaza cautarea, iar 2=numarul de ordine al coloanei cu denumirea produsului.

asau

VLOOKUP(A12;$A$20:$C$;2)

Nota: pentru prêt se va introduce aceeasi formula in celula E12, numai ca pe ultima pozitie se va trece valoarea 5 (numarul de ordine al coloanei cu pret)

Astfel, in cadrul foii de calcul care contine factura, campul Denumire_produs se va recupera prin consultare verticala, in functie de codul fiecarui produs asupra caruia vor opera urmatoarele categorii de teste :

A)   test de vid : se realizeaza prin intermediul functie de informare ISBLANK

B)    test de sir de caractere :ISTEXT

C)    test de nul sau de numere negative (<=0 →comp[aratie cu 0)

D)   test de existenta, prin intermediul caruia se verifica prezenta campului cheie in prima coloana a tabelului de consultare verticala.

Astfel, recuperarea campului Denumire_produs in cadrul facturii din tabelul 3, se obtine prin introducerea urmatoarei formule de calcul in celula B12

=if(or(isblank(A12) ;istext (A12) ;A12<=0) ;'Eroare' ;if(A12<> VLOOKUP(A12;TCV;1); 'Produs_Inexistent'; VLOOKUP(A12;TCV;2)))

B) Consultarea pe orizontala

Se realizeaza asemanator cu consultarea pe verticala cu precizarea ca se aplica HLOOKUP si deci formula

+HLOOKUP(camp_date;table_de_ consultare_orizontala(TCO); numar_linie_de_recuperat)

Functii data si ora

=NOW() returneaza data si ora curenta

=TODAY() returneaza data curenta

=DATEVALUE('sir de caractere in format data') calculeaza numarul de data corespunzator sirului de caractere

=DATE(an;luna;zi) calculeaza numarul de data pentru data calendaristica specificata ca argument

ex :

=NETORKDAYS(data debut;data sfarsit;sarbatori) calculeaza numarul de zile lucratoare intre doua date calendaristice, fara a tine cont de weekend-uri si sarbatori legale. Functia este utila in calculul penalizarilor si in stabilirea zilelor de cotatie bursiera.

Ex : =NETWORKDAYS *(D16:E16:F9:F12)

=DAYS360(data debut;data sfarsit) calculeaza numarul de zile intre doua date calendaristice, considerand anul ca avand 360 de zile

=YEAR(numar data) returneaza numarul anului corespunzator unui numar data

ex: =YEAR(DATEVALUE ('15-jan-2005'))

=MONTH(numar data) returneaza numarul lunii dintr-un numar data

=DAY(numar data) returneaza numarul zilei dintr-un numar data

=TIME(ora;minut;secunda) calculeaza un numar timp corespunzator orei, minutului, secundei specificate

=HOUR(numar-timp) extrage ora dintr-un numar-timp

Functii similare :

=MINUTE(numar timp)

=SECOND(numar timp)

Functii concatenate: Permit concatenarea intre informatii de natura diferita (sir de caractere, numere, functii), prin caracterul '&'.

Exemplu:

='Astazi este '&DAY(TODAY()) &', luna a '&MONTH(TODAY()) &'-a'&' din anul '&YEAR(TODAY())

Functii de cautare si consultare*1

Exemplu

D8 : Validare de tip lista derulanta (Data/Validation/list, source

G8 : Validare conditionala de tip lista

B12 : Consultare verticala simpla ; =VLOOKUP (A12,$A$20:$C$28,2)

C12: format numeric personalizat

D12, Consultare orizontala simpla; =HLOOKUP ($G$8,$F$20:$J$25,C12+1)

E12: Pret

=IF(OR(A12<=0,ISTEXT(A12), ISBLANK(A12)),'',IF(A12=VLOOKUP (A12,$A$20:$C$28,1),VLOOKUP (A12,$A$20:$C$28,3),'Eroare'))

F12:Valoare; =C12*E12 +C12*E12*$D$9+D12

G12: TVA;=F12*19%

H12: Protejarea celulelor ce contin formule de calcul.Ascunderea formulelor de calcul

=F12+G12

ABC16 : Concatenare intre un sir de caractere si valoarea informationala a unei celule.

='Total factura '&D5

D16 : =SUM(D12:D15)

E16 : =AVERAGE(E12:E15)

F17 : Structura conditionala imbricata 

=IF(H16<=1000000,0,IF (AND(H16>1000000,H16<=1500000), H16*5%,IF(AND(H16>1500000, H16<=2000000),H16*10%, H16*15%)))

H17: Calculul reducerii comerciale printr-o functie definita de utilizator in Visual Basic.

=ROUND(DISCOUNT(H16),0)

I16: Realizarea unei legaturi dinamice intre 2 celule de calcul; =$H$16

Filtre:

Majorari K3:

=IF(OR(J3='DA',TODAY()<H3), 0,IF((TODAY()-H3)<=30,(TODAY()-H3) *I3*0.3%,IF((TODAY()-H3) <=90,(I3*30*0.3%)+(TODAY() -H3-30)*I3*0.5%,IF ((TODAY()-H3)<=180,(I3*30*0.3%) +(I3*60*0.5%)+(TODAY()-H3-90) *I3*0.7%,(I3*30*0.3%)+ (I3*60*0.5%)+(I3*90*0.7%)+ (TODAY()-H3-180)*I3*1%))))

E24: =DSUM(A2:M13,9,B21:D22)

F24: =DSUM(A2:M13,11,B21:D22)

G24: =DCOUNT(A2:M13,5,B21:D22)

Functii tip DB

DSUM: Returneaza suma valorilor unei rubrici aferente unei baze de date, care raspunde unui criteriu de selectie.

=DSUM(baza de date;rubrica/nr.rubrica;camp de criterii)

DMAX : Returneaza cea mai mare valoare dintr-o rubrica aferenta unei baze de date, corespunzator unui criteriu de selectie.

=DMAX(baza de date;rubrica/nr.rubrica;camp de criterii)

DMIN : Returneaza cea mai mica valoare dintr-o rubrica aferenta unei baze de date, corespunzator unui criteriu de selectie.

=DMIN(baza de date;rubrica/nr.rubrica;camp de criterii)

DAVERAGE

Calculeaza media valorilor unei rubrici aferente unei baze de date, potrivit criteriului de selectie specificat.

=DAVERAGE(baza de date;rubrica/nr.rubrica;camp de criterii)

DGET : Returneaza continutul unei rubrici pentru o baza de date, corespunzator unui criteriu specificat.

=DGET(baza de date;rubrica/nr.rubrica;camp de criterii)

DCOUNT : Numara celulele ocupate intr-o baza de date, conform unui criteriu specificat.

=DCOUNT(baza de date;rubrica/nr.rubrica;camp de criterii)

Exemplu :

Formule necesare :

H13 : =F13+G13

I13: =+I15+I17+I20+I21+I22

K13:

=IF(OR(J13='DA',TODAY() <H13),0,IF((TODAY()-H13)<=30, (TODAY()-H13)*I13*0.3%,IF((TODAY() -H13)<=90,(I13*30*0.3%)+ (TODAY()-H13-30)*I13*0.5%, IF((TODAY()-H13)<=180,(I13*30*0.3%) +(I13*60*0.5%)+(TODAY()-H13-90) *I13*0.7%,(I13*30*0.3%)+(I13*60*0.5%) +(I13*90*0.7%)+(TODAY()-H13-180) *I13*1%))))

L13: =(I13+K13)*19%

M3: =I3+K3+L3

K13 Majorari: =IF(OR(J13='DA', TODAY()<H13),0,IF((TODAY()-H13) <=30,(TODAY()-H13)*I13* 0.3%,IF((TODAY()-H13) <=90,(I13*30*0.3%)+(TODAY() -H13-30)*I13*0.5%,IF((TODAY()-H13) <=180,(I13*30*0.3%)+(I13*60*0.5%) +(TODAY()-H13-90)*I13*0.7%, (I13*30*0.3%)+(I13*60*0.5%)+ (I13*90*0.7%)+(TODAY()-H13-180) *I13*1%))))

Instrumente de sintetizare a informatiei

Teorie TBLP

Tabele de ipoteze

Comanda Subtotals (Data) ofera posibilitatea sintetizarii informatiei prin organizarea ei pe niveluri de grupare, dar si operarea unor calcule diverse pe aceste grupuri sau informatii centralizate.

Tabela pivot permite crearea unui tablou de sinteza in care datele dintr-o foaie de calcul pot fi permutate pe linie sau pe coloana pentru a scoate in evidenta anumite informatii.

Astfel, asupra datelor pot opera agregari si calcule sub forma de totaluri, medii, minim, maxim etc, cu alte cuvinte, se pot opera calcule diferite asupra acelorasi elemente supuse sintetizarii.

Tehnica valorii scop : Permite stabilirea unei valori finale (valoare obiectiv) pentru o formula prin modificarea valorii uneia dintre celulele care participa la formula respectiva.

Scenarii : Sunt utilizate pentru a compara anumite valori care genereaza rezultate diferite; astfel se pot modela diverse strategii pentru a analiza avantajele si dezavantajele diferitelor moduri de abordare a unor probleme date.