Pachetul software Microsoft Excel

În aceste seminarii vom aplica următoarele funcționalități ale pachetului Microsoft Excel:
 Formule cu referințe absolute şi relative
 Funcții: matematice, logice, financiare, matriceale şi de căutare, definite de utilizator și statistice
 Grafice
 Analize What-if (Scenarii şi Goal Seek)
 Solver
 Tabele pivot
 Macrocomenzi şi lucrul cu Macro Recorder
 Lucrul cu Visual Basic Editor

Contents
Exemplul 1 – Formule, funcții matematice și logice ......................................................................................................................... 2
Exemplul 2 – Funcții financiare .................................................................................................................................................................... 4
Exemplul 3 – Funcții matriceale și definite de utilizator ................................................................................................................ 6
Exemplul 4 – Funcții statistice și matematice ..................................................................................................................................... 8
Exemplul 5 – Realizarea unei foi de calcul de analiză şi Grafice ............................................................................................... 9
Exemplul 6 – Scenariu ..................................................................................................................................................................................... 13
Exemplul 6 – Goal Seek .................................................................................................................................................................................. 14
Exemplul 7 – Solver ........................................................................................................................................................................................... 15
Exemplul 8 – Tabele pivot .............................................................................................................................................................................. 18
Exemplul 8 – Macro Recorder ...................................................................................................................................................................... 20
Exemplul 9 – Visual Basic Editor ............................................................................................................................................................... 21

1
Exemplul 1 – Formule, funcții matematice și logice
Nr. Pas Mod de rezolvare Observații
1. Deschideți fișierul MS Excel denumit „Seminar Fișierul se găsește în arhiva cu date de seminar
Excel”, foia de calcul Achizitii. Datele se referă la disponibilă pe platforma [Link] la secțiunea de
achizițiile din ziua curentă ale unei firme importator- seminar.
distribuitor de produse de larg consum. Prețurile sunt
exprimate în RON.
2. Inserați o nouă coloană în fața tabelului, cu titlul Selectați coloana A
“[Link].” Insert->Columns
Scrieți “Nr. Crt.” în celula A1
3. Generați numere în prima coloană Scrieți “1” in celula A2
Selectați celulele A2:A11
Din secțiunea Editing se allege opțiunea Fill->
Series: Step Value: 1
Se apasă OK
4. Reformatați foaia de calcul, păstrând stilul inițial, Se poate folosi facilitatea Format Painter pentru a
pentru a include și coloana nou creată. copia formatul unei celule și a-l aplica ulterior
pentru alte celule.
5. Introduceți data curenta în coloana “Data” Funcţiile predefinite se pot selecta din meniul
principal, Formulas

sau selectând fx

Se deschide wizard-ul de funcţii, se selectează

categoria şi apoi funcţia

2
 Pentru informaţii
detaliate pentru fiecare
funcţie selectată
consultaţi

Meniul Formulas->Date&Time->Today( )

6. Formatați celulele care conțin data, aplicând Selectați celulele D2:D11, click dreapta Format
formatul zz/ll/aaaa. Cells. Selectați locația (Romanian) și tipul de format
corespunzător.
7. Calculați valoarea fără TVA a fiecărui produs Calculul valorii produselor (celulele G2:G10) Observați adresarea
importat. - Selectați celula G2 relativă!
- Scrieți formula de calcul, respectiv “=E2*F2”
- Copiați formula și în celelalte celule (trageți de
colțul din dreapta jos, când mouse-ul este sub forma
unei cruci negre)
8. În coloana H, calculați TVA-ul aferent. În celula H2 introduceți formula: H2= G2*M3
Copiați formula în celelalte celule (H2:H10)
9. Studiați formula pentru a vedea dacă TVA-ul aferent
a fost calculat corect.
10. Rescrieți formula utilizând adresarea absolută a H2=G2*$M$3 Folosiți tasta F4 pentru
celulelor a modifica referințele!
11. Calculați accizele aferente băuturilor alcoolice, în - Folosiți funcția logică IF (Formulas->Logical- Identificați corect
coloana I (Valoare accize) >IF), având următorii parametri: ultimii doi parametri ai
-Logical Test: testați dacă produsul face funcției!
parte din categoria “Bauturi alcoolice” Analizați rezultatele
(C2="Bauturi alcoolice") obținute!
-Value if true: valoarea accizei se obține ca
produs între valoarea fără TVA și nivelul de
0.2 al accizei

3
 -Value if false: nu se aplică acciză
- Copiați formula în jos pe verticala pentru toate
produsele
12. Calculați valoarea totală pentru fiecare tip de produs, Se calculează valorile din coloana J.
ca sumă dintre Valoarea fără TVA, TVA și Valoare
acciză.
13. Realizați suma valorilor calculate anterior, pentru - Selectați domeniul de celulele care trebuie
toate produsele importate, folosind funcția SUM. calculate: H12:J12
- Formulas-> AutoSUM-> SUM
16. Evidențiați, prin colorarea celulelor, produsele care - Scrieți domeniul de celule F2:F11.
au prețul de achiziție cuprins între 15 si 100 de - Selectați Home, Conditional Formatting ->
RON. Highlight Cell Rules -> Between, introduceți
valorile corespunzătoare și selectați culoarea dorită.
17. Salvați fișierul. Salvați folosind email-ul sau prin alte mijloace
fișierul cu rezolvarea exercițiilor.

Exemplul 2 – Funcții financiare

Nr. Pas Mod de rezolvare Observații
1. Deschideți fișierul MS Excel denumit „Seminar Fișierul se găsește în arhiva cu date de seminar In Wizard-ul funcției se
Excel”, foia de calcul Financiar. Datele se referă la disponibilă pe platforma [Link] la secțiunea de va folosi trimitere spre
trei scenarii de investiții financiare sau economisiri. seminar. celula care conţine
Funcţiile se pot selecta din fx sau meniul Formulas, valoare, nu se va
Financiar introduce valoarea în
wizard
2. O companie cumpără un utilaj de producţie, care se Funcţia PV(rate,nper,pmt,fv,type) –Returnează
estimează că va aduce în următorii 10 ani un venit valoarea prezentă a unei investiții (valoarea în
anual de 28.600€. Pentru finanţarea utilajului, firma prezent a unei serii de plăti viitoare).
are nevoie de un credit. Banca selectată acordă Are parametrii:
credite cu dobânda de 6, 75% pe an. - Rata reprezintă rata dobânzii. De exemplu, dacă se
Care ar trebui să fie preţul maxim al utilajului, dacă obține un împrumut cu o rată anuală a dobânzii de
ar trebui să fie plătit numai din veniturile pe care le 10%, iar plățile sunt lunare, rata lunară a dobânzii va
aduce. Cheltuielile de întreţinere nu sunt luate în fi de 0,1/12 adică 0,83%.
considerare. Plata către bancă se face în fiecare lună, - Nper reprezintă numărul total de perioade în care
la sfărșitul lunii. se efectuează plăți. Pentru un împrumut pe 4 ani cu
plăți lunare nper va fi 4*12=48 de perioade.

4
 - Pmt reprezintă plata efectuată în fiecare perioadă și
care rămâne fixă pe întreaga durată a anuității
- Fv reprezintă valoarea viitoare sau ce valoare se
vrea a se obține după efectuarea ultimei plăți. Este
un fel de valoare reziduală. Valoarea implicit a
acestui argument este 0.
- Tipul poate fi 0 sau 1 și indică momentul de
efectuare a plăților, respectiv sfârșitul sau începutul
perioadei.
3. Inserați formula de calcul în celula B8. - Selectați din meniu Formulas -> Financial -> PV Luați in considerare
- Introduceți valori pentru parametrii funcției, prin faptul că suma plătită
referirea celulelor în care au fost introduse date. și rata dobânzii au
valori anuale în
enunțul problemei, iar
plata se face lunar la
sfârșitul fiecărei luni.
4. Presupunem că dorim să economisim bani pentru un Funcția FV(rata,nper,pmt,pv,tip) –Returnează
proiect care va începe peste un an. Facem în acest valoarea viitoare a unei investiții bazate pe plăți
scop un depozit de 1000€, cu o rată anuală a periodice și constante și cu o rata a dobânzii
dobânzii de 6%, plătibilă lunar (rata lunara a constantă.
dobânzii va fi 6%/12=0.5%). Vrem să depunem - Argumentele rata, nper, pmt şi tip au aceeași
suma de 100€ la începutul fiecărei luni, timp de 1 an. semnificație ca în cazul funcției PV
Câți bani vom avea în cont la sfârșitul celor 12 luni? - Pv reprezintă valoarea prezentă sau cât valorează
în momentul prezent o serie de plăți viitoare
(implicit este 0)
5. Presupunem că se face o investiție astfel: se plătesc Funcția NPV(rata,valoare1,valoare2, ...) – Plăţile se introduc ca
10.000€ peste un an și se primesc anual venituri de Calculează valoarea prezentă netă unei investiții valori negative
3.000€, 4.000€ și 6.800€ în următorii 3 ani. Dacă utilizând rata inflație și o serie de plăți (valori
rata anuală a inflației este de 10%, care va fi negative) și venituri (valori pozitive) viitoare.
valoarea prezentă netă a investiției? - Rata reprezintă rata inflației pe parcursul unei
perioade.
- Valoare1, valoare2, ... argumente (de le 1 la 29) ce
reprezintă plățile sau veniturile.

5
Exemplul 3 – Funcții matriceale și definite de utilizator
Nr. Pas Mod de rezolvare Observații
1. Deschideți fișierul MS Excel denumit „Seminar 1 Fișierul se găsește în arhiva cu date de seminar
Excel”, foia de calcul Salarii. Datele se referă la disponibilă pe platforma [Link] la secțiunea de
veniturile lunare obținute de angajați. seminar.
2. Trebuie calculate veniturile totale lunare ale agenților unei companii de asigurări, știind că aceștia primesc un salariu fix, un comision
din cifra de afaceri pe care au realizat-o în acea lună și un spor de vechime.
3. Calculați comisionul agenților Inserați în coloana E funcția matriceală VLOOKUP Atenție la referințele
(Formulas-> Lookup&Reference -> VLOOKUP), absolute sau relative
având următorii parametri: ale parametrilor!
- Lookup_value: valoarea care trebuie căutată în
tabel (Cifra de afaceri)
- Table_array: tabelul de comision (atenție ca
celulele care referă tabelul (D14 și E19) să aibă
adrese absolute)
- Col_index_num: numarul coloanei din tabelul de
comision a cărei valoare se returnează (în acest caz,
coloana procentelor; se va introduce manual indexul
acestei coloane în cadrul Tabel de comision)
- Range_lookup: dacă se caută o valoare apropiată
sau una exactă (se lasă necompletat deoarece în acest
caz se face o căutare pe intervale de valori)
4. Funcţie definită de utilizator (utilizare cod VBA)
Definiți o funcție care, pe baza salariului fix și a vechimii unui angajat, să calculeze sporul de vechime ce i se cuvine acestuia.
Algoritmul de calculare a sporului de vechime este următorul:
- pentru o vechime sub 3 ani nu se acordă spor;
- pentru o vechime între 3 și 5 ani sporul reprezintă 5% din salariu;
- pentru o vechime între 5 și 10 ani sporul reprezintă 10% din salariu;
- pentru o vechime între 10 și 15 ani sporul este de 15% din salariu;
- pentru o vechime mai mare de 15 ani sporul este de 20% din salariu.
5. Deschideți editorul Visual Basic Selectați meniul Developer >Visual Basic
În cazul în care meniul Developer nu apare, se
selectează File > Options > Customize Ribbon.
Choose commands from > Main Tabs

6
 Se bifează opțiunea Developer și apoi se apasă Add
și OK.
6. Activați proiectul VBAProject (Seminar 1 Selectați Insert->Module
[Link]) și scrieți funcția Introduceţi (copiaţi) următorul cod sursă:

Function Spor(salariu, vechime)

If vechime < 3 Then
Spor = 0
Else
If vechime >= 3 And vechime < 5 Then
Spor = 0.05 * salariu
Else
If vechime >= 5 And vechime < 10 Then
Spor = 0.1 * salariu
Else
If vechime >= 10 And vechime < 15 Then
Spor = 0.15 * salariu
Else Spor = 0.2 * salariu
End If
End If
End If
End If
End Function
7. Salvați proiectul, închideți editorul VB și reveniți la Dacă securitatea fișierului nu permite crearea de
foaia de calcul din Excel module asociate, modificați opțiunile de securitate
folosind opțiunea Developer > Macro Security
8. Calculați sporul, utilizând funcția definită ca pe orice - Selectați celula F2
altă funcție Excel - Formulas > Insert Function Category: User
Defined > Spor
9. Calculați veniturile totale ale agenților ca sumă Se calculează coloana G.
dintre salariul fix, cifra de afaceri înmulțită cu
procentul din comision și sporul de vechime
10. Însumați cifra de afaceri şi veniturile totale, în Se calculează valorile celulelor D9 și G9.
celulele corespunzătoare.
11. Salvați și închideți fișierul

7
Exemplul 4 – Funcții statistice și matematice
Nr. Pas Mod de rezolvare Observații
1. Deschideți fișierul MS Excel denumit „Seminar Fișierul se găsește în arhiva cu date de seminar
Excel”, foia de calcul Evaluare. Datele se referă la disponibilă pe platforma [Link] la secțiunea de
un test pentru evaluarea unor cursanți. seminar.
2. Se dorește calcularea punctajelor mediu, maxim și minim obținute pentru fiecare categorie de gen, precum și câți cursanți din fiecare
categorie au participat la curs. De asemenea, se cere să se realizeze un clasament al cursanților în funcție de punctajele obținute.
3. Calculați punctajul maxim pe fiecare categorie de Inserați în celula H4 funcția statistică MAXIFS Pentru anumite
gen. (Formulas-> Statistical -> MAXIFS), având versiuni de Excel nu
următorii parametri: sunt suportate toate
- Max_range: domeniul de celule pentru care se va funcţiile, în acest caz în
calcula punctajul maxim (C2:C11) faţa funcţiei se oiate
- Criteria_range1: domeniul de celule pentru care se folosi prefixul _xlfn.
aplică condiția de evaluare (B2:B11)
- Criteria1: reprezintă criteriul sub formă de număr,
expresie sau text care definește ce celule vor fi Pentru o evaluare
evaluate pentru calculul maximului (“M”) multicriterială, funcția
În mod similar, repetați pașii anteriori pentru a permite adăugarea de
calcula valoarea din celula H5. noi domenii de celule
și criteriile asociate
acestora!
4. Calculați punctajul minim și mediu pe fiecare Se vor folosi funcțiile MINIFS pentru celulele I4 și Funcțiile MINIFS,
categorie de gen. I5 și AVERAGEIFS pentru celulele J4 și J5. SUMIFS și
Modul de lucru este similar celui folosit la punctul 3 AVERAGEIFS au
al exemplului. parametrii similari cu
MAXIFS!
5. Calculați câți cursanți din fiecare categorie au Inserați în celula K4 funcția statistică COUNTIF
participat la curs. (Formulas-> Statistical -> COUNTIF), având
următorii parametri:
- Range: domeniul de celule în care se vor număra
cursanții de gen masculin (B2:B11)
- Criteria: criteriul care controlează ce celule ar
trebui să fie numărate (“M”)
În mod similar, repetați pașii anteriori pentru a
calcula valoarea din celula K5.

8
 6. Realizați un clasament al cursanților în funcție de Inserați în celula D2 funcția statistică [Link]
punctajele obținute. (Formulas-> Statistical -> [Link]), având
următorii parametri:
- Number: numărul pentru care dorim să
determinăm poziția în clasament (C2)
- Ref: o listă de valori numerice pentru care se
calculează clasamentul ($C$2:$C$11)
Order: determină tipul de sortare (valoarea 0 pentru
sortare descendentă -implicit; orice altă valoare
pentru sortare ascendentă)
Copiați formula din celula D2 în jos, pe verticală,
pentru a calcula punctajul celorlalți cursanți.

Exemplul 5 – Realizarea unei foi de calcul de analiză şi Grafice

Nr. Pas Mod de rezolvare Observații
1 Datele se referă la o firma Deschideți fișierul MS Excel denumit „Seminar Excel”, foia de Atenţie la folosirea
importatoare de produse de calcul Analiza venituri si cheltuieli. adreselor absolute, de
birotica care a importat în Completati foaia de calcul astfel (formule de calcul şi nu valori): exemplu $K$2
primele șase luni ale anului trei  Pentru fiecare produs, in fiecare luna, valoarea fiind
categorii de produse: creioane, cantitate*pret
pixuri si markere. Firma a  Valoare totală produse este suma valorilor produselor (pentru
vândut în fiecare lună toate fiecare lună)
produsele importate.  Cheltuielile cu importul reprezintă valoarea totală a produselor +
Se dorește o analiză a veniturilor taxele vamale (valoarea totală a produselor * procent taxa
si cheltuielilor firmei in această vamală) (pentru fiecare lună)
perioadă, în condițiile în care  Se completează fondul de salarii, conform enunţului (în ian şi feb,
sunt cunoscute următoarele: 20000, în martie cu 15% mai mult decât în februarie
- cantitățile și prețurile la care (20000+20000*0.15) iar din aprile aceiasi formulă ca în martie
au fost importate produsele  Veniturile din vanzări reprezintă cheltuielile cu importul +
- taxa vamală care se aplică pe adaosul comercial (care este cheltuielile cu importul * procentul
valoarea produselor importate de adaos comercial) (se completează pentru fiecare lună)
- fondul de salariu pentru  Se calculează primele, comform algoritmului din enunţ, folosind
fiecare lună funcţia logică IF (se completează pentru fiecare lună)
- dacă veniturile din vânzări
depășesc un anumit plafon,

9
 atunci se acordă prime
reprezentând un anumit
procent din depășire
- fondul de salarii in lunile
ianuarie şi februarie au fost de
20000, in martie a crescut cu
15%, rămânând constant pana
la sfârşitul perioadei
- pe fondul de salarii si pe prime
se aplică impozitul pe salarii şi  Se calculează taxele şi impozitele conform formulei: (fond
CAS de salarii + prime)*(procent impozit salarii+procent CAS)
- firma a avut cheltuieli fixe de  Se calculează Cheltuieli salariale totale (fond de
10000 în fecare lună salarii+prime+taxe si impozite)
Se doreşte să se analizeze:  Se introduc cheltuielile fixe (10000 în fiecare lună)
- Evoluţia profitului net în  Se calculează Total cheltuieli (Cheltuieli cu importul +
perioada analizată Cheltuieli salariale totale + Cheltuieli fixe)
- Structura cantităților
 Se calculează Profitul brut ca Venituri din vânzări – Total
importate în luna ianuarie
- Care ar fi fost situaţia daca cheltuieli
taxele vamale ar fi fost de 3%?  Se calculează Profitul net dupa formula Prpfit brut –Profit
- Care ar fi fost situaţia daca brut * Procent impozit pe profit
taxele vamale ar fi fost de 7% Exemplu de completare a foii de calcul:
şi adaosul comercial ar fi fost
de 32%?
- Cât ar fi trebuit sa fie adaosul
comercial, astfel încât firma sa
obțină în luna iunie un profit
de 32.000 RON?

Foaia de calcul completetă:

10
2 Creați un grafic care să prezinte - Insert->Chart
structura cantităților importate - Selectaţi tipul de grafic (Pie)
în luna ianuarie, sub formă de - Select Data
procente.
- Chart Data Range, selectaţi celulele care conţin cantităţile
importate în Ianuarie (ţinând <Ctrl> apăsat, C3,C6 şi C9)
- Horizontal (Category) Axis Labels, selectaţi celulele care
conţin produsele importate în Ianuarie (ţinând <Ctrl> apăsat,
A3,A6 şi A9)
- Adaugati titlul graficului
- Formatati graficului prin Add Chart Elements, Data Labes,
More Data Label Options, Values Percentage
- Move Chart - New Sheet: Structura
5 Creați un grafic cu bare - Selectați datele pe baza cărora se va realiza graficul: ținând Folosiți opțiunile Quick
verticale pentru a evidenția <Ctrl> apăsat selectați domeniile de celule C4:H4; C7:H7; Layout si Change Colors
evoluția prețului celor trei C10:H10 pentru a personaliza graficul!
articole pe parcursul celor șase - Alegeți Insert și apoi pictograma corespunzătoarea graficelor de
luni. tipul Column și Bar. Alegeți opțiunea 3D Clustered Column.
- Modificați titlul în „Evoluția prețurilor”

11
 - Select Sata Source, selectati celulele care contin preturile
(tinand <Ctrl> apasat C4:H4; C7:H7; C10:H10
- Legend Entitys (Series), Edit selectati celulele care contin
denumirile produselor, pe rand cu ajutoril Edit A3, A6, A9
- Horizontal (Category) Axis Labels, selectaţi zona care contine
lunile C3:H3
- Pentru a specifica denumirea axelor graficului, selectați graficul.
Spoi apăsați butonul plus (+) care apare în dreapta, sus și bifați
opțiunea Axis Titles. Denumiți axa verticală Preț și axa
orizontală Luni.
7 Plasați graficul într-o foaie de Move Chart - New Sheet: Evolutie preturi
calcul nouă

Analize What-if
 Folosind instrumentele What-If Analysis în Excel, se pot utiliza mai multe seturi de valori distincte, sub
formă de intrări pentru una sau mai multe formule, în scopul de a explora și analiza rezultatele obținute.
 Spre exemplu, se poate folosi What-If Analysis pentru a construi două bugete, în care se presupune că
fiecare are un anumit nivel al veniturilor. Sau se poate specifica un rezultat pe care dorim să îl obțină o
formulă și apoi să determinăm ce seturi de valori vor produce acel rezultat.
 Excel oferă mai multe instrumente pentru acest tip de analize, în funcție de specificul problemei: Scenario
Manager (Scenariu), Goal Seek (Căutarea rezultatului) și Data Table (Tabele de date).

12
Exemplul 6 – Scenariu

Scenariul este un tip de analiză ce permite definirea unor simulări în care evidențiem modul în care
schimbarea uneia sau mai multor valori de intrare afectează rezultatul. Se obține un raport pe baza căruia se
pot analiza comparativ datele din diferite scenarii.
Nr. Pas Mod de rezolvare Observații
1. In foaia de calcul Analiza Venituri si
Cheltuieli
2. Dorim să analizăm situațiile în care taxele - Accesați meniul Data > What-If Analysis > Scenario După definirea unor
vamale se modifică. Se presupune ca taxele Manager scenarii, apăsarea
vamale scad, de la 5% la 3%. Cum se - Apăsați Add tastei Show in
modifică profitul net? Dar în situația în care - Adăugați următorul scenariu: fereastra Scenario
aceste taxe cresc la 7%, iar adaosul o Scenario name: Taxa vamala 3% Manager va duce la
comercial (AC) crește și el la 32%? o Changing cells: K3 modificarea valorilor
o Apăsați OK direct în foia de
o Enter value for each of the changing cells: 0.03 calcul. De cele mai
o Apăsați OK multe ori, acest lucru
- Adăugați următorul scenariu: este de evitat în
o Scenario name: Taxa vamala7%, AC 32% scopul menținerii
o Changing cells: ținând <CTRL> apăsat K3 și K4 datelor originale.
o Apăsați OK Pentru simulări, se
o Enter value for each of the changing cells: 0.07 recomandă opțiunea
și 0.32 Summary!
o Apăsați OK
- În fereastra Scenario Manager apăsați Summary
- Selectați tipul de raport Scenario Summary
- Selectați la Result cells: C26:H26
- Apăsați OK
3. Personalizați raportul generat în noua foaie - Modificați celulele B6 și B7 de sub Changing Cells,
de calcul Scenario Summary astfel încât să reflecte valorile care se schimbă: Taxa
vamala și Adaos comercial
- Modificați celulele de sub Result Cells pentru a include
lunile anului din problemă

13
Exemplul 6 – Goal Seek

Presupunem că celula D (celula rezultat) se calculează pe baza celulelor A, B si C (celule parametru), adică
D = f(A, B, C). Facilitatea Goal Seek ne permite ca, stabilind o valoare pentru celula rezultat, si cunoscând,
de exemplu valorile parametrilor B si C, sa se calculeze valoarea parametrului A. Prin Goal Seek putem spune
ca se realizează un “drum invers” față de modul obișnuit de calcul a valorii unei celule în MS Excel.
De precizat că pot exista oricâte celule parametru care să influențeze celula rezultat, dar că se poate determina
numai valoarea unei singure celule parametru.

Nr. Pas Mod de rezolvare Observații

1. In foaia de calcul Analiza Venituri si
Cheltuieli
2. Determinați cât ar fi trebuit sa fie adaosul - Accesați meniul Data > What-If Analysis >Goal Seek Prin Goal Seek se
comercial, astfel încât firma sa obțină în luna - Realizați următoarele setării: poate defini o
iunie un profit de 32.000 RON. o Set cell: selectați celula cu profitul net în iunie singură celula care
o To value: 32000 își modifică valoarea!
o By changing cell: selectați celula cu Adaos
comercial
o Apăsați OK
- Observați mesajul din fereastra Goal Seek Status care ne
informează că a fost găsită o soluție și analizați
rezultatele obținute în foia de calcul
- Apăsați OK daca vreți sa păstrați noile valori sau Cancel
în caz contrar
3. S-a decis ca un capital de 10,000 € sa fie Deschideți foia de calcul Goal Seek si analizați modul de
depus la bancă sub forma unui depozit având calcul al dobânzii compuse, prin cele doua variante.
dobânda anuală de 3%. Știind că dobânda este
compusă, s-a calculat (prin doua variante)
capitalul final care se obține după o perioadă
de 5 ani.

14
 6. Dorim ca după perioada de cinci ani sa avem - Pentru una dintre cele două variante de calcul, parcurgeți
în cont un capital final de 15.000 €. Cât de pașii de la punctul 2 al exemplului, realizând următoarele
mare trebuie sa fie capitalul inițial, dacă se setări:
acordă aceeași dobândă? o Set cell: capitalul final după 5 ani
o To value: 15000
o By changing cell: capitalul inițial la începutul
celor 5 ani

Exemplul 7 – Solver

Instrumentul Solver este folosit pentru a rezolva probleme de cercetări operaționale prin găsirea soluțiilor
optime în probleme decizionale, în special pentru probleme de programare liniară. Cea mai simplistă problemă
ar fi găsirea unui minim/maxim prin schimbarea valorilor în unele celule.
Când definiți o problemă pentru Solverul Excel, puteți alege una dintre următoarele metode în caseta derulantă
Select a Solving Method:
 GRG Nonlinear. Algoritmul neliniar cu gradient redus generalizat este utilizat pentru probleme care
sunt neliniare netede, adică în care cel puțin una dintre restricții este o funcție neliniară netedă a
variabilelor de decizie.
 LP Simplex. Metoda Simplex LP se bazează pe algoritmul Simplex, ce este utilizat pentru rezolvarea
problemelor de programare liniară - modele matematice ale căror cerințe sunt caracterizate de relații
liniare, adică constau dintr-un singur obiectiv reprezentat de o ecuație liniară care trebuie maximizată
sau minimizată.
 Evolutionary. Se folosește pentru probleme non-netede, care sunt cel mai dificil tip de probleme de
optimizare de rezolvat, deoarece unele dintre funcții sunt netede sau chiar discontinue și, prin urmare,
este dificil să se determine direcția în care o funcție crește sau descrește.

15
Nr. Pas Mod de rezolvare Observații
1. Deschideți foia de calcul Solver. Datele se referă la Fișierul se găsește în arhiva cu date de seminar disponibilă pe platforma
mărfurile care pot fi transportate din 2 depozite către [Link] la secțiunea de seminar.
4 clienți.
2. Acesta este un exemplu de problemă simplă de optimizare a transportului cu un obiectiv liniar.
Problemă: Doriți să reduceți la minimum costul expedierii mărfurilor de la 2 depozite diferite la 4 clienți diferiți. Fiecare depozit are o
capacitate de stocare limitată și fiecare client are o anumită cerere.
Obiectiv: Minimizați costul total de expediere, fără a depăși cantitatea disponibilă la fiecare depozit și satisfacerea cererii fiecărui client.
3. Calculați totalul de mărfuri transportate din fiecare Inserați în celula F8 funcția SUM(B8:F8). În mod similar, repetați pentru
depozit. celula F9.
4. Calculați totalul de mărfuri transportate către fiecare Inserați funcția SUM(B8,B9) în celula B10 și apoi copiați formula în
client. celulele C10, D10, E10.
5. Calculați costurile totale (funcția obiectiv). Inserați în celula B13 funcția matematică SUMPRODUCT (Formulas->
Math & Trig-> SUMPRODUCT) ce returnează suma produsurilor din
mai multe tablouri, având următorii parametri:
- Array1: B3:E4
- Array2: B8:E9
6. Denumiți valorile din celule pentru a fi mai ușor de - Selectează celulele B8:E9 -> Click dreapta -> Define Name -> se scrie
înțeles parametrii ce se vor aplica în Solver. Marfuri_transporate în Name -> OK
Parametru - Se repetă pentru celelalte 5 denumiri de celule
Nume Celule
Solver
Variable
Marfuri_transporate B8:E9
cells

Capacitate H8:H9 Constraint

Total_transp_depozit F8:F9 Constraint

Cerere B11:E11 Constraint

Total_transp_client B10:E10 Constraint

Costuri_totale B13 Objective

7. Adăugați Solver în Excel - File > Options

16
 - click Add-Ins pe bara din stânga, verifică că este selectat Excel Add-
ins în căsuța Manage din josul paginii, apoi click Go.
- bifează Solver Add-in și click OK
- opțiunea Solver apare acum în tabul Data, în gruparea Analysis.
8. Rezolvați problema cu Solver. Parametrii pentru Solver sunt:
- Objective: Costuri_totale
- To: Min
- Variable cells: Marfuri_transportate
- Constraints: Total_transport_client = Cerere și
Total_transport_depozit <= Capacitate
- Solving Method: Simplex LP
9. Rulați Solver și păstrați soluția obținută.

- Click Solve
- Bifați Keep Solver Solution
- OK
10. Salvați și închideți fișierul

17
Exemplul 8 – Tabele pivot

Tabelul pivot din Excel reprezintă un instrument puternic pentru gruparea seturilor mari de date și aplicarea
filtrelor, sortărilor, funcțiilor matematice asupra lor. Permite regruparea și rearanjarea datelor prin inversarea
liniilor cu coloanelor și vice-versa, precum și prin împărțirea datelor în funcție de diverse criterii.

Nr. Pas Mod de rezolvare Observații

1. Deschideți foaia de calcul Comenzi. Datele se referă la produsele comercializate şi furnizorii acestora, clienții firmei, furnizori şi vânzările
firmei. Se dorește evidențierea preferințele clienților în ceea ce privește furnizorii produselor, preferințele clienților în ceea ce privește
produsele precum şi vânzările grupate pe ani.
2. Se creează o nouă tabelă numită In celula T3 se introduce formula =VALUE(RIGHT(L3;4)) în
vânzări detaliate, în care pe baza care funcția RIGHT(L3;4) returnează ultimele patru caractere
datei comenzii se creează coloana din textul aflat in celula L3. Funcția VALUE convertește un text
An comandă care conţine numai cifre în valoare numerică. Se copiază
formula în celelalte rânduri.
3. Pe baza codului clientului şi al In celula V3 se introduce formula
tabelei Clienţi se creează coloana =VLOOKUP(M3;$E$3:$F$23;2;0). Se copiază formula în
Client celelalte rânduri.
4. Pe baza codului produsului şi al In celula X3 se introduce formula
tabelei Produse / Furnizori se =VLOOKUP(W3;$A$3:$C$742;2;0) şi se copiază în celelalte
creează coloana Denumire produs rânduri
5. Se creează coloana valoare Pret unitar vanzare * Cantitate comandată şi se copiază în
celelalte rânduri
6. Pe baza codului produsului şi al In celula AC3 se introduce formula
tabelei Produse / Furnizări se =VLOOKUP(W3;$A$3:$C$742;3;1) şi se copiază în celelalte
creează coloana Cod Furnizori rânduri
7. Pe baza codului furnizorului şi al In celula AD3 se introduce formula
tabelei Produse / Furnizori se =VLOOKUP(AC3;$H$3:$I$26;2;1) şi se copiază în celelalte
creează coloana Furnizor rânduri
8. Se dorește evidențierea preferințele - Insert -> Pivot Table
clienților în ceea ce privește
furnizorii produselor

18
 - Se selectează tabelul Vânzări Detaliate și se alege ca
tabelul pivot să fie amplasat într-o foaie de calcul nouă
(New Worksheet)
- Se aleg câmpurile: Furnizor la Columns, Client la Rows,
Valoare la Values
- Se selectează Sum of Valoare -> Value Field Settings
- Insert -> Recommended Charts – Clustered Column

3. Se dorește evidențierea preferințele Urmați pașii:

clienților în ceea ce privește - Insert -> Pivot Table
produsele - Se selectează tabelul Vânzări Detaliate și se alege ca
tabelul pivot să fie amplasat într-o foaie de calcul nouă
(New Worksheet)
- Se aleg câmpurile: Clienti la Columns, Denumire Produs la
Rows, Valoare la Values,
- Se selectează Sum of Valoare -> Value Field Settings
-
4. Evidenţierea vânzărilor grupate pe - Insert -> Pivot Table
ani - Se selectează tabelul Vânzări Detaliate și se alege ca
tabelul pivot să fie amplasat într-o foaie de calcul nouă
(New Worksheet)
- Se alege câmpul An vânzare la Rows, Valoare la Values,

5. Salvați și închideți fișierul

19
 Macrocomenzi
 O macrocomandă este un set de comenzi sau instrucțiuni stocate într-o foaie de calcul MS Excel sub formă
de cod VBA. Din punct de vedere tehnic, o macrocomandă este o bucată de cod, în timp ce Visual Basic
for Applications (VBA) este limbajul de programare creat de Microsoft pentru a scrie macrocomenzi.
 O macrocomandă poate fi percepută ca un program de dimensiuni reduse necesar pentru a efectua o
secvență predefinită de acțiuni. Odată create, macrocomenzile pot fi oricând refolosite. De obicei, sunt
utilizate pentru automatizarea sarcinilor repetitive și a activităților zilnice, de rutină. Totodată, cu
ajutorului codului VBA, se pot crea macrocomenzi de complexitate ridicată.
 Scopul principal este acela de a facilita utilizatorului realizarea mai multor activități în mai puțin timp.
Există două moduri de a crea macrocomenzi în Excel : folosind Macro Recorder sau Visual Basic Editor.

Exemplul 8 – Macro Recorder

Nr. Pas Mod de rezolvare Observații
1. Deschideți fișierul MS Excel denumit Fișierul se găsește în arhiva cu date de seminar disponibilă pe
„Seminar 2 Excel”, foia de calcul Macro. platforma [Link] la secțiunea de seminar.
2. Foia de calcul conține date referitoare la - Accesați meniul Developer > Record Macro Pașii realizați în
încasările zilnice ale unei companii. Datele se - Scrieți numele macrocomenzii: continuare de
referă la numărul facturii, data plății, codul Formateaza_incasari_zilnice utilizator vor fi
contului în care s-a plătit și suma plătită, - Apăsați OK înregistrați în această
exprimată în lei. Se cere ca în fiecare zi aceste macrocomandă.
date să fie formatele și agregate conform
cerințelor factorilor decizionali.
Se va crea o macrocomandă pentru a
automatiza aceste activității repetitive.

20
 3. Inserați înainte de date un rând nou care - Click dreapta pe primul rând, apoi selectați Insert
conține capul de tabel. - Definiți un cap de tabel în coloanele A1:D1 care conține
șirurile de caractere: Factura, Data, Cont, Valoare.
6. Calculați totalul încasărilor din ziua - În celula F2 introduceți textul Total incasari, iar în F3
respectivă, precum și numărul de vouchere Vouchere
care au fost oferite clienților, știind că acestea - În celula G2 introduceți o funcție care calculează suma
se oferă clienților care au realizat achiziții mai valorilor facturilor. Pentru această funcție, aveți în vedere
mari de 800 lei. Evidențiați cu o culoare un domeniu acoperitor de celule, compania estimând ca
diferită rândurile pentru care plățile au fost limită maximă zilnică 100 de facturi încasate. Suma
virate în contul 2, cont special pentru plăți calculată se va formata cu virgulă pentru mii și două
externe. zecimale.
- În celula G3 se va introduce funcția COUNTIF cu
următorii parametri:
o Range: celulele cu valoarea încasărilor zilnice.
Aveți din nou în vedere un domeniu acoperitor
de celule pentru maxim 100 de facturi.
o Criteria: ">800"
- Selectați coloana C
- Selectați Conditional Formatting > Highlight Cells
Rules > Equal To…
- Introduceți valoarea 2 apoi apăsați OK.
7. Opriți înregistrarea macrocomenzii - Accesați meniul Developer > Stop Recording
8. Rulați macrocomanda înregistrată pentru a - Deschideți foia de calcul Macro1.
formata facturile din altă zi. - Accesați meniul Developer > Macro
- Selectați macrocomanda Formateaza_incasari_zilnice
- Apăsați Run

Exemplul 9 – Visual Basic Editor

Nr. Pas Mod de rezolvare Observații
1. Deschideți fișierul MS Excel denumit Fișierul se găsește în arhiva cu date de seminar disponibilă pe
„Seminar 2 Excel” platforma [Link] la secțiunea de seminar.
2. Se dorește generarea unui fișier PDF din - Apăsați Developer > Visual Basic sau țineți apăsate Experimentați
fiecare foaie de calcul dintr-un workbook, simultan tastele ALT și F11. Aceasta deschide Editorul plasarea unor
excepție făcând foile de calcul care conțin VBA. butoane pe foile de

21
 grafice. De asemenea, se va genera un fișier - Accesați Insert -> Module. calcul, cărora să li
PDF care conține toate foile de calcul dintr-un - Copiați și lipiți codul de mai jos în fereastră: se asigneze
workbook. funcționalități ale
'Acest cod va salva fiecare foaie de calcul ca un fisier macrocomenzilor!
PDF
Sub SalveazaWorksheetPDF()
Dim ws As Worksheet
For Each ws In Worksheets
[Link] xlTypePDF, "E:\macro\Export"
& [Link] & ".pdf"
Next ws
End Sub

- Modificați calea directorului în care se salvează fișierele

pentru a corespunde cerințelor voastre
- Apăsați butonul Save
- Apăsați butonul de rulare ( )sau F5 pentru a executa
macrocomanda
- Verificați generarea fișierelor în directorul specificat
- Repetați pașii de mai sus pentru a crea o macrocomandă
ce conține următorul cod sursă:

'Acest cod va salva intreg workbook-ul ca un fisier PDF

Sub SalveazaWorkbookPDF()
[Link] xlTypePDF,
"E:\macro\" & [Link] & ".pdf"
End Sub
3. Se dorește ștergerea unor anumite înregistrări - Creați o foaie de calcul nouă cu denumirea “Viramente”
dintr-o foaie de calcul. Vom folosi date din - Deschideți fișierul text “date_vba” din arhiva de lucru și
exemplul 6 și creăm o macrocomandă care copiați datele din acest fișier în foia de calcul creată
evidențiază doar viramentele realizate în - Parcurgând pașii de la punctul 2 el exercițiului, creați o
contul 2 al companiei. macrocomandă care include următorul cod VBA:

Sub eliminare_val()
Dim cell As Range
For i = 102 To 1 Step -1

22
 Set cell = Range("C" & i)
If cell <> 2 Then
[Link]
End If
Next i
End Sub

- Apăsați butonul Save

- Accesați foia de calcul Viramente
- Rulați macrocomanda eliminare_val

Probleme propuse I
1. Utilizați, împreună cu funcția IF, și alte funcții logice precum AND sau OR pentru a rezolva următoarea problemă: un examen
constă dintr-o probă teoretică și una practică, ce au o pondere identică în punctajul final. Examenul este promovat atunci când,
din punctajul maxim de 100 de puncte per probă, la proba practică se obțin minim 75 de puncte, iar la proba teoretică minim 50
de puncte. Stabiliți care studenți au promovat sau nu.
2. Creați un enunț de problemă în care să folosiți funcția matriceală HLOOKUP.

23