excel - suport de curs

238
LECŢIA 1 LUCRUL ÎN MEDIUL EXCEL

Upload: patricia-ruxanda

Post on 30-Oct-2014

205 views

Category:

Documents


35 download

DESCRIPTION

Excel

TRANSCRIPT

Page 1: EXCEL - SUPORT DE CURS

LECŢIA 1

LUCRUL ÎN MEDIUL EXCEL

Page 2: EXCEL - SUPORT DE CURS
Page 3: EXCEL - SUPORT DE CURS

Componentele ferestrei Excel

Fereastra Excel – figura 1.1.- are numeroase elemente comune cu ferestrele Windows:

o bară de meniuri - de unde se pot selecta comenzi; o bară de stare – care indică starea activităţii curente; bare de instrumente – care conţin butoane şi liste derulante prin care se obţine acces

rapid la comenzile utilizate rapid.

Figura 1.1 – Fereastra Excel

În plus, o fereastră Excel conţine câteva elemente unice, care vor fi explicate pe parcursul lecţiei.

Configurarea unui nou registru de calcul

Când se lansează în execuţie programul Excel, pe ecran apare o fereastră care conţine un registru de calcul nou. Registrul (Workbook) este principalul document folosit în Excel pentru stocarea şi prelucrarea datelor. Un registru este format din foi de calcul

Bara de meniuriBare de instrumente

Celulă

Bara de stare Suprafaţa foii de calcul

Page 4: EXCEL - SUPORT DE CURS

individuale, fiecare din acestea putând conţine date. În configuraţia prestabilită fiecare registru de calcul creat conţine 3 foi de calcul (Sheet1, Sheet2, Sheet3), dar ulterior se pot adăuga mai multe foi (până la 255).

Foile de calcul pot conţine diverse tipuri de informaţii. În mod obişnuit foile dintr-un registru de calcul conţin informaţii legate între ele. De exemplu, la un buget, fiecare foaie poate conţine bugetul pentru un anumit departament din cadrul companiei.

În partea de jos a registrului există o bară de derulare a foilor de calcul (figura 1.2). În această bară sunt afişate numele foilor de calcul. (Dacă această bară nu apare, din meniul Tools se selectează comanda Options. În caseta de dialog Options se selectează butonul View şi se marchează caseta Sheet Tools). La un registru nou, foaia de calcul curentă este Sheet1. Se poate trece la o altă foaie făcând clic pe numele ei. De exemplu pentru a trece pe foaia Sheet 2 se face clic pe deasupra numelui ei.

Figura 1.2 – Bara de derulare a foilor de calcul

În bara de derulare a foilor de calcul, nu pot apare numele tuturor foilor de calcul. De aceea, în bara de derulare a foilor au mai fost prevăzute 4 butoane care permit deplasarea printre foile registrului. Aceste butoane nu realizează selectarea foilor de calcul, ele permit doar derularea numelor lor.

Tabelul de mai jos descrie operaţiile pe care le realizează aceste butoane:

Buton Operaţie Deplasarea la prima foaie de calcul a registrului Deplasarea la ultima foaie de calcul a registrului Deplasarea spre stânga cu o foaie Deplasarea spre dreapta cu o foaie

Dacă un registru conţine mai multe foi de calcul, numele acestora sunt importante pentru identificare. Chiar şi atunci când există o singură foaie în registrul de calcul, numele acesteia este important, deoarece poate fi folosit în formule şi furnizează antetul prestabilit pentru pagina tipărită. De aceea este recomandabil ca foilor de calcul să li se acorde nume mai sugestive decât Sheet 1, Sheet2 etc.

Pentru a denumi o foaie de calcul se execută următorii paşi:1. Se aplică un dublu clic pe eticheta foii de calcul (în bara de derulare a foilor de

calcul). Numele foii de calcul va fi afişat în video invers;2. Se introduce noul nume, după care se apasă Enter.În mod prestabilit un registru conţine 3 foi de calcul. Dacă este necesar se pot

adăuga noi foi de calcul. Pentru a introduce o nouă foaie de calcul trebuie parcurşi următorii paşi:

1. Se poziţionează cursorul mouse-ului pe eticheta unei foi de calcul (în bara de derulare a foilor de calcul);

2. Se aplică un clic folosind butonul drept al mouse-ului;3. Pe ecran apare un meniu din care se selectează comanda Insert.

Bara de derulare a foilor de calculEtichetele foilor de calcul

Page 5: EXCEL - SUPORT DE CURS

Foile de calcul suplimentare pot fi eliminate prin operaţia de ştergere astfel:1.Se poziţionează cursorul mouse-ului pe eticheta unei foi de calcul (în bara de

derulare a foilor de calcul);2.Se aplică un clic folosind butonul drept al mouseului;3.Pe ecran apare un meniu din care se selectează comanda Delete.

Deplasarea în cadrul foii de calcul

Într-o foaie de calcul informaţiile sunt prezentate în cadrul unei interfeţe grafice formate din linii şi coloane (figura 1.3). Coloanele au ataşate litere, iar liniile sunt numerotate cu cifre. O foaie de calcul conţine 256 coloane şi 65536 de linii. Coloanele au etichetele dispuse în partea superioară a ferestrei documentului şi sunt notate cu litere (A, B, ..., Z), apoi combinaţii de două litere (AA, AB până la IV). Liniile sunt numerotate de la 1 la 65536 în partea stângă a ferestrei documentului.

Figura 1.3

Figura 1.3 – Structura unei foi de calcul

Bara de derulare orizontală

etichetele liniei

etichetele coloanei celulă activă

Bara de derulare vericala

Casete de derulare

Page 6: EXCEL - SUPORT DE CURS

La intersecţia dintre o coloană şi o linie se găseşte o celulă. Celula este unitatea fundamentală pentru stocarea datelor. Ea poate fi referită folosind litera coloanei şi cifra liniei la intersecţia cărora se află (de exemplu A5, B7). Într-o celulă se pot introduce mai multe tipuri de informaţii:

Texte - capete de tabel, etichete, note, texte explicative; Valori – numere, date calendaristice şi ore, valori logice; Formule – formule de calcul cu ajutorul cărora este calculată o nouă valoare în

funcţie de informaţiile din alte celule.Celula activă este celula care este marcată. Informaţiile tastate apar în celula

activă. Pentru a introduce date în altă celulă mai întâi se activează celula respectivă. După introducerea datelor se iese din celulă cu Enter, altfel există posibilitatea ca următoarele comenzi să nu poată fi aplicate.

Foile de calcul pot avea uneori dimensiuni foarte mari. În aceste situaţii pentru a găsi sau introduce informaţii trebuie folosite metode speciale de deplasare prin foile de calcul. Deplasarea se poate efectua cu mouse-ul sau prin intermediul tastaturii. Pentru a efectua deplasarea (prin foaie) cu mouse-ul se folosesc barele de derulare verticală şi orizontală care se găsesc în partea dreaptă şi de jos a fiecărei foi de calcul (figura 1.3).

Fiecare bară conţine două săgeţi. Prin executarea unui clic pe o săgeată se pot realiza următoarele operaţii:

Derulare o linie în sus Derulare o linie în jos Derulare o coloană la stânga Derulare o coloană la dreapta

Pentru deplasare mai rapidă prin foaie se pot folosi casetele de derulare (prin tragerea lor în bara de derulare). Locul ocupat de caseta de derulare în cadrul barei de derulare indică poziţia relativă a ferestrei faţă de întreaga zonă a foii de calcul.

O celulă se poate activa rapid plasând cursorul mouse-ului pe celula respectivă şi executând un clic pe butonul din stânga al mouse-ului.

Deplasarea prin foaia de calcul cu ajutorul tastaturii se poate face cu următoarele taste:

Tastă Acţiune Deplasare o celulă la stânga Deplasare o celulă la dreapta Deplasare o celulă în sus Deplasare o celulă în josTab Deplasare o celulă la dreaptaHome Deplasare în celula din extrema

stângă a unui rândCtrl+Home Deplasare în celula A1 a foii de

calculCtrl+End Deplasare în ultima celulă folosită din

foaia de calcul (colţul din dreapta jos)Page Up Deplasare în sus cu un ecranPage Down Deplasare în jos cu un ecranCtrl+Page Up Deplasare la dreapta cu un ecranCtrl+Page Down Deplasare la stânga cu un ecran

Page 7: EXCEL - SUPORT DE CURS

Utilizarea barelor de instrumente

Barele de instrumente (afişate sub meniu) permit un acces mai rapid la procedurile şi comenzile des utilizate. Pentru a lucra cu barele de instrumente este necesar mouse-ul. Pentru utilizarea unei anumite comenzi se execută un clic pe butonul asociat comenzii sau funcţiei de care aveţi nevoie.

În Excel există mai multe bare de instrumente, dar nu trebuie folosite toate în acelaşi timp deoarece se încarcă prea mult ecranul. Este bine să fie vizualizate doar barele care conţin comenzi ce trebuie folosite. Există mai multe bare de instrumente predefinite care pot fi afişate selectând din meniul View comanda Toolbars. Cele mai folosite bare de instrumente sunt prezentate în continuare:

Bara Standard.Conţine butoane pentru comenzi de formatare, administrare fişiere şi tipărire.

Bara Formatting

Conţine butoane utilizate pentru formatarea fonturilor, comenzi de aliniere, formate numerice, formatarea marginilor, stabilire culori.

Bara Chart.Conţine butoane pentru formatarea graficelor.

Bara Pivot TableConţine butoane folosite pentru crearea, actualizarea şi organizarea tabelelor pivot

Bara Drawing.Conţine butoane pentru desenare.

Bara Forms.

Page 8: EXCEL - SUPORT DE CURS

Conţine butoane prin intermediul cărora se pot adăuga foii de calcul obiecte cum ar fi: casete de validare, butoane de opţiuni, liste de derulare.

Bara Stop Recording.

Conţine două butone care produc încheierea şi oprirea/repornirea înregistrării unei comenzi macro.

Bara Audit.

Conţine butoane cu comenzi pentru detectarea erorilor uzuale din foile de calcul Excel.

Bara Full Screen.

Această bară de instrumente apare atunci când Excel a fost configurat pentru lucrul pe întreg ecranul. Apăsarea butonului produce întoarcerea la modul de lucru anterior.

Afişarea/ascunderea barelor de instrumente

Pentru a avea acces la alte comenzi sau pentru a mări spaţiul de lucru, barele de instrumente pot fi afişate sau ascunse. Paşii care trebuiesc efectuaţi pentru afişarea/ascunderea barelor de instrumente sunt:1. Se aplică comanda View, Toolbars, Customize.2. Pe ecran este afişată caseta de dialog Customize (figura 1.4) care este alcătuită din

trei secţiuni: Toolbars, Commands şi Options. Aceste secţiuni sunt activate la apăsarea butoanelor corespunzătoare din partea superioară a ferestrei. Se selectează butonul Toolbars.

Page 9: EXCEL - SUPORT DE CURS

Figura 1.4 – caseta de dialog Customize - secţiunea Toolbars

3. În lista Toolbars sunt afişate numele tuturor barelor de instrumente din Excel. Vor fi afişate numai barele de instrumente care sunt marcate. Marcarea/demarcarea se face aplicând un clic în dreptul casetei din dreptul numelui barei.

4. Se aplică un clic pe butonul Close.

Adăugarea/ascunderea butoanelor dintr-o bară de instrumente

Componenţa unei bare de instrumente se poate modifica. Există situaţii în care la o bară de instrumente trebuie adăugat un buton nou sau trebuie şters un buton. Aceste modificări se pot face în secţiunea Commands a casetei Customize (figura 1.5).Această secţiune conţine:- lista Categories. În această listă apar toate titlurile de meniuri verticale.- lista Commands. La selectarea unui meniu din lista Categories, în lista Commands

apar toate comenzile care pot fi aplicate din meniul respectiv.

Page 10: EXCEL - SUPORT DE CURS

Figura 1.5 – fereastra de dialog Customize - secţiunea Commands

Pentru a adăuga un buton nou pe o bară de instrumente:1. Se aplică comanda View, Toolbars, Customize2. Se activează secţiunea Commands, prin selectarea butonului Commands3. Se selectează din lista Categories comanda asociată butonului4. Ţinând butonul stânga al mouse-ului apăsat, se trage mouse-ul în poziţia în care

trebuie inserat butonul. În poziţia în care butonul poate fi adăugat cursorul mouse-ului îşi modifică forma – apare un cursor în forma literei I

5. Se eliberează butonul mouse-ului6. Se selectează butonul Close

Pentru a şterge un buton de pe o bară de instrumente:1. Se aplică comanda View, Toolbars, Customize2. Se activează secţiunea Commands, prin selectarea butonului Commands3. Se aplică un clic pe butonul care trebuie şters dintr-o bară de instrumente4. Ţinând butonul stâng al mouse-ului apăsat, se trage de mouse în interiorul casetei

Customize (în orice poziţie !)5. Se eliberează butonul mouse-ului6. Se selectează butonul Close.

Pentru a reveni la structura standard a unei bare de instrumente, în secţiunea Toolbars a casetei de dialog Customize se selectează bara de instrumente respectivă se verifică dacă este marcată şi se aplică comanda Reset.

Este bine ca pentru început să fie afişate doar două bare de instrumente: Standard şi Formating.

Page 11: EXCEL - SUPORT DE CURS

Opţiuni suplimentare de afişare a butoanelor

Forma butoanelor din barele de instrumente poate fi stabilită în secţiunea Options a ferestrei Customize (figura 1.6).

Figura 1.6 - fereastra de dialog Customize - secţiunea Options

Această secţiune conţine:- opţiunea Show Standard and Formatting toolbars in two rows- opţiunea Always show full menus- opţiunea Show full menus after a short delay. Dacă opţiunea este marcată, opţiunea

Always show full menus este inactivă, iar la selectarea unui meniu sunt afişate toate comenzile componente abia după câteva secunde.

- opţiunea Large Icons. Dacă opţiunea este marcată butoanele vor fi mari, dacă nu este marcată butoanele vor fi mici

- opţiunea List font names in their font. Dacă această opţiune este marcată la apăsarea butonului Font fiecare font este afişat cu caracteristicile sale, altfel va fi afişată doar lista de fonturi.

- opţiunea Show Screen Tips on toolbars. Screen Tips reprezintă suprafaţa mică de culoare galbenă în care este afişat numele butonului deasupra căruia este poziţionat cursorul mouse-ului. Dacă opţiunea este marcată numele butonului va fi afişat, dacă nu este marcată numele butonului nu va fi afişat. Recomandăm ca această opţiune să fie marcată

- lista Menu animations. Din această listă se selectează modul de deschidere a meniurilor. Opţiunile sunt: None – modul normal de deschidere. Nu se aplică nici un efect Random – meniurile se deschid aleator (o parte oarecare mai întâi) Unfold – modul de deschidere seamănă cu o despăturire

Page 12: EXCEL - SUPORT DE CURS

Slide – deshiderea dă senzaţia de alunecare

Page 13: EXCEL - SUPORT DE CURS

LECŢIA 2

INTRODUCEREA ŞI EDITAREA DATELOR

Page 14: EXCEL - SUPORT DE CURS
Page 15: EXCEL - SUPORT DE CURS

Introducerea şi editarea datelor

Pe măsură ce datele sunt introduse de la tastatură ele apar în celula activă dar şi într-o zonă de deasupra foii de calcul, numită bară de formule (Figura 2.1). Dacă bara de formule nu este afişată din meniul View se selectează comanda Formula Bar.

Figura 2.1 – bara de formule

În bara de formule apare o bară clipitoare care indică locul unde va apărea următorul caracter tastat.

Introducerea datelor se termină fie apăsând tasta <Enter>, fie activănd o altă celulă (cu mouse-ul, cu săgeţi, cu „Tab” etc.). Dacă introducerea nu se termină corect următoarele comenzi nu vor mai putea fi aplicate.

Introducerea textelor

Datele de tip text includ caractere alfabetice, numere şi simboluri. Pentru a introduce text într-o celulă se selectează celula, se scrie textul şi apoi se validează prin apăsarea tastei „Enter”. Într-o celulă se pot introduce cel mult 255 caractere. Textul introdus este aliniat în celulă la stânga.

Dacă un număr uie introdus ca text (de exemplu un cod poştal) înaintea datelor trebuie introdus un apostrof.

Dacă textul este prea lung şi nu încape în celula activă partea în exces va fi afişată în celula adiacentă dreaptă dacă aceasta este liberă; dacă nu este liberă va fi afişat doar textul care încape în celula activă. Pentru a putea afişa textul în întregime trebuie mărită dimensiunea coloanei active.

Pentru a modifica dimensiunea unei coloane se poziţionează cursorul mouse-ului pe marginea din dreapta a etichetei coloanei. Forma cursorului se va modifica ( ). Se trage de acest cursor şi se eliberează butonul mouse-ului când se ajunge la dimensiunea dorită. O altă metodă este aplicarea unui dublu clic când apare acest cursor; în acest mod coloana va avea dimensiunea textului de lungime maximă din coloană.

Introducerea numerelor

Numerele sunt valori care conţin caracterele 1 2 3 5 6 7 8 9 0 - + /. E e.Pentru a introduce un număr, se selectează celula dorită se scrie numărul şi se

apasă <Enter>. Dacă într-o celulă se introduce un număr prea lung, Excel încearcă să-l afişeze în format exponenţial (1.53 E+08 sau 1.53 * 108). Dacă numărul nu poate fi afişat nici în format exponenţial atunci în celulă apare ####. În acest caz pentru a putea vizualiza numărul se va mări dimensiunea coloanei folosind metoda prezentată la introducerea textelor.

Page 16: EXCEL - SUPORT DE CURS

În Excel există mai multe formate numerice. Câteva din acestea sunt prezentate în tabelul de mai jos:

FormatAfişare

General 12345.6Number 12345.60Currency $1,234.78Comma 12,345.6Percent 123.454 %Scientific (Exponential) 1.23E+04Fraction 12 3/4Time/False True/False

Pentru introducerea unei fracţii, trebuie scris numărul întreg, urmat de un spaţiu şi fracţia respectivă. Dacă trebuie introdusă numai partea fracţionară se scrie zero, spaţiu şi fracţia. Astfel, Excel poate interpreta datele introduse ca fiind în format dată.

Într-o celulă numerele sunt aliniate la dreapta.

Introducerea datelor şi orelor

Valorile de tip oră pot fi introduse în următoarele formate:

Ora Format15:31 h:mm

15:31:45 h:mm:ss8:31 PM h:mm:AM/PM

3.31:45 PM h:mm:ss AM/PM

La introducerea datelor trebuie să fim atenţi ce sistem de introducere a datelor este folosit pe calculatorul pe care îl folosim. În continuare vom folosi sistemul american de introducere a datelor, marea majoritate a calculatoarelor fiind setate pe sistemul respectiv. Valorile introduse corect vor apare în bara de formule în formatul lună/zi/an, indiferent de stilul de formatare a celulei.

Alte formate de introducere a datelor sunt:

Date Format7/8/98 M/d/yy

8-Jul-98 d-mmm-yy8 -Jul d-mmm (va fi folosit anul din data sistemului de calcul)Jul-98 mmm-yy

7/8/98 09 09:45 M/d/yy h:mm

Page 17: EXCEL - SUPORT DE CURS

Excel înregistrează datele şi orele sub formă de numere. Fiecărei date şi ore îi este ataşat un număr. Aceste numere se numesc numere seriale. Numai formatul de afişare este de tip dată sau oră. Folosind această codificare, o dată reprezintă numărul de zile care despart data de 1.01.1900 de data specificată; ora reprezintă o fracţiune zecimală din 24 de ore.

Numerele seriale au proprietatea că permit calcularea zilelor scurse între două date specificate sau a duratelor dintre două momente de timp. Numerele seriale ataşate datelor sunt numere întregi, cele ataşate orelor sunt numere subunitare.

Exemple:

Data Număr serial Ora Număr serial1-01-1900 1 0:0:0 02-01-1900 2 12:00:00 0.51-08-1998 36008 23:59:59 0.99

Implicit datele şi orele se aliniază în celule la dreapta. Dacă o dată nu este introdusă corect ea este transformată în text şi informaţia din celulă va fi aliniată la stânga. Acest mod reprezintă o metodă foarte simplă de a verifica dacă datele sau orele au fost introduse corect.

În unele cazuri, deşi a fost utilizat un format corect pentru valori de tip dată sau oră rezultatul afişat apare sub formă de număr. Acest lucru se întâmplă când celula respectivă a fost formatată anterior cu un format altul decât General. În acest caz celulei respective trebuie să i se aplice formatul dată sau oră corespunzător (vezi lecţia 3).

Efectuarea modificărilor

Dacă datele dintr-o celulă au fost introduse greşit acestea se pot corecta în modul următor:

1. Se activează celula care conţine datele care trebuie modificate.2. Se apasă tasta <F2> sau se aplică un clic în linia de formule în dreptul locului

unde trebuie făcute modificări.3. Se fac operaţiile de ştergere şi inserare necesare. Pentru a muta punctul de inserţie

se folosesc tastele săgeţi. Pentru ştergere se pot folosi tastele <Backspace> - pentru a şterge un caracter la stânga - şi <Delete> - pentru a şterge un caracter la dreapta.

4. Se apasă tasta <Enter>.

Selectarea, copierea şi mutarea celulelor

Când o celulă este activă cursorul mouse-ului poate lua trei forme:

-

Acestă formă de cursor apare dacă cursorul mouse-ului este poziţionat în interiorul celulei. Cu acest cursor se realizează operaţia de selecţie a celulelor

Page 18: EXCEL - SUPORT DE CURS

-

Acestă formă de cursor apare dacă cursorul mouse-ului este poziţionat pe conturul celulei. Cu acest cursor se realizează operaţia de mutare a celulelor

-

Acestă formă de cursor apare dacă cursorul mouse-ului este poziţionat în colţul din dreapta-jos celulei. Cu acest cursor se realizează operaţia de copiere a celulelor

Selectarea celulelor

Celulele pot fi selectate în două moduri: cu tastatura şi cu mouse-ul.

Selectarea celulelor cu tastatura

Pentru a selecta un domeniu de celule cu ajutorul tastaturii, se vor efectua următorii paşi:1. Se activează celula din colţul din stânga-sus al domeniului.2. Celulele se selectează folosind combinaţiile de taste: <Shift>+<>,

<Shift>+<>, <Shift>+<>, <Shift>+<>. Celulele selectate apar în video-invers. Excepţie face prima celulă selectată.

Selectarea celulelor cu mouse-ul

Pentru a selecta un domeniu de celule alăturate, se vor efectua următorii paşi:1. Se aplică un clic pe celula din colţul din stânga-sus al domeniului.2. Ţinând butonul stâng al mouse-ului apăsat, se trage de mouse spre colţul din

dreapta-jos al domeniului. Celulele selectate apar în video-invers. Excepţie face prima celulă selectată.

3. Se eliberează butonul mouse-ului.

Pentru a selecta celule care nu sunt alăturate se ţine apăsată tasta Ctrl în timp ce se aplică un clic de mouse pe celulele individuale.

Pentru a selecta un rând întreg sau o coloană de celule se aplică un clic pe eticheta de rând sau de coloană.

Copierea datelor

Prin copiere, datele originale rămân la locul lor, copia lor fiind plasată în locul indicat de utilizator.Pentru a realiza operaţia de copiere, se vor efectua următorii paşi:

1. Se selectează celula sau domeniul de celule care trebuie copiate.2. Se aplică comanda Edit, Copy. 3. Se selectează prima celulă din zona unde se va plasa copia.4. Se aplică comanda Edit, Paste.

Pentru a copia date în celule alăturate, se poate utiliza şi o altă metodă mai rapidă:

Page 19: EXCEL - SUPORT DE CURS

1. Se selectează celula care trebuie copiată.2. Se poziţionează cursorul mouse-ului în colţul din dreapta-jos al celulei.3. Când cursorul mouse-ului ia forma unei cruci subţiri, se apasă butonul stâng al

mouse-ului şi se trage de mouse peste celulele în care se vor copia datele.

Mutarea datelor

Prin mutare datele sunt luate din poziţia iniţială şi sunt plasate în noua locaţie. Pentru a realiza operaţia de mutare, se vor efectua următorii paşi:

1. Se selectează celula sau domeniul de celule care trebuie mutate.2. Se aplică comanda Edit, Cut. 3. Se selectează prima celulă din zona unde se va plasa copia.4. Se aplică comanda Edit, Paste.

O altă metodă mai rapidă este următoarea:1. Se selectează celula care trebuie mutată.2. Se poziţionează cursorul mouse-ului pe conturul celulei.3. Când cursorul mouse-ului ia forma unei săgeţi, se apasă butonul stâng al

mouse-ului şi se trage de mouse în noua poziţie a datelor.

Page 20: EXCEL - SUPORT DE CURS

LECŢIA 3

FORMATAREA FOILOR DE CALCUL

Page 21: EXCEL - SUPORT DE CURS
Page 22: EXCEL - SUPORT DE CURS

Formatarea foilor de calcul

Aspectul informaţiilor din celulele unei foi de calcul poate fi modificat prin utilizarea comenzii Format, Cells. La lansarea acestei comenzi pe ecran apare caseta de dialog Format Cells (figura 3.1).

Figura 3.1 – caseta de dialog Format Cells

Conţinutul casetei variază în funcţie de butonul selectat în partea superioară a ferestrei. Opţiunile posibile sunt:

Number – pentru modificarea formatului numerelor Alignment – pentru modificarea tipului de aliniere din celulă Font – pentru modificarea fonturilor Borders – pentru adăugarea de chenare în jurul celulelor Patterns – pentru stabilirea culorii de fond în celulă Protection – pentru protejarea informaţiilor din celulă

Modificarea formatului numerelor

Excel oferă o paletă largă de formate numerice, care sunt prezentate în tabelul următor:

Format numeric Exemple DescriereGeneral 10.7

-10.7Excel afişează valoarea aşa cum este introdusă.

Page 23: EXCEL - SUPORT DE CURS

Format numeric Exemple DescriereNumber 3400.50

(-120.39)Formatul prestabilit Number are două zecimaleNumerele negative apar scrise cu roşu şi între paranteze, precedate de semnul minus.

Currency(Valută)

$3,400.50($3,400.50)

Formatul prestabilit Currency are două zecimale şi simbolul dolarului.Numerele negative apar scrise cu roşu şi între peranteze.

Accounting(Contabilitate)

$3,400.00$978.21

Acest format este folosit pentru a alinia simbolul dolarului şi zecimalele în coloană. Formatul Accounting prestabilit conţine două zecimale şi simbolul pentru dolar.

Date(Dată)

11/7 Formatul Date prestabilit cuprinde ora şi ziua separate de o bară oblică(/); se poate selecta însă şi un alt format din cele disponibile.

Time (Ora) 10:00 Formatul Time prestabilit conţine ora şi minutele separate de două puncte; se poate opta însă şi pentru afişarea secundelor, sau a indicatorilor AM şi PM.

Percentage(Procent)

99.50% Formatul Percentage prestabilit cuprinde două zecimale. Excel înmulţeşte valoarea din celulă cu 100 şi afişează rezultatul însoţit de simbolul pentru procent

Fraction(Fracţie)

½ Formatul Fraction permite afişarea numerelor sub formă de fracţie.

Scientific(Ştiinţific)

3.40E 03 Formatul Scientific prestabilit cuprinde două zecimale. Folosiţi acest format pentru afişarea numerelor în format ştiinţific.

Text 135RV90 Folosiţi formatul text pentru a afişa atât text cât şi numere, în aceeaşI celulă. Excel va afişa exact exact ceea ce introduceţi dumneavoastră.

Special 02110 Acest format este conceput special pentru afişarea codurilor poştale, a numerelor de telefon şi a codurilor personale, astfel încât să nu fie necesară folosirea unor caractere speciale, cum ar fi liniuţele

Custom(Personalizat)

00.0% Formatul Custom este folosit pentru a crea propriul format numeric. Se pot folosi codurile pentru formatare din lista Type, care pot fi modificate. Simbolul # reprezintă un marcaj de rezervare pentru un număr, 0 reprezintă un marcaj de rezervare pentru zero, ? pentru adăugarea unui spaţiu

După ce se stabileşte formatul numeric adecvat, se efectuează paşii următori:1. Se selectează celula sau domeniul de celule ce conţine valorile care trebuie formatate.2. Se aplică comanda Format, Cells. Pe ecran va fi afişată caseta de dialog Format

Cells figura 3.2).3. Se aplică un clic pe butonul Number.4. Din lista Category, se selectează formatul numerelor care va fi folosit. 5. În partea dreaptă a casetei se efectuează modificările dorite pe formatul selectat. De

exemplu se poate stabili numărul de zecimale cu care va fi afişat numărul (în lista

Page 24: EXCEL - SUPORT DE CURS

Decimal places), sau modul în care vor fi afişate numerele negative (în lista Negative numbers).

6. Se aplică un clic pe butonul OK, sau se apasă tasta <Enter>. Excel va reformata celulele selectate conform opţiunilor alese.

Figura 3.2 – caseta de dialog Format Cells - secţiunea Number

Dacă se introduce o dată calendaristică într-o celulă formatată cu formatul Number, data va apărea ca număr (numărul serial al datei). Pentru a rezolva problema, se modifică formatul celulei din Number în Date.

Eliminarea formatului numeric dintr-o celulă sau domeniu de celule se realizează aplicând formatul Genaral.

Utilizarea butoanelor pentru formatarea numerelor

Bara de instrumente de formatare conţine mai multe butoane pentru stabilirea formatelor numerice:

Buton Denumire ExempluCurrency Style $1200.90

Percent Style 20.90%Comma Style 1,200.90

Increase Decimal Adaugă o zecimală

Decrease Decimal Elimină o zecimală

Page 25: EXCEL - SUPORT DE CURS

Pentru a folosi unul dintre aceste butoane, se selectează celula sau domeniul care

trebuie formatate, apoi se aplică un clic pe butonul dorit.

Modificarea tipului de aliniere din celule

La introducerea datelor într-o foaie de calcul din Excel, acestea sunt aliniate în mod automat: textul este aliniat la stânga, iar numerele la dreapta. Atât textul cât şi numerele sunt plasate iniţial la baza celulelor. În Excel se poate modifica alinierea datelor din celule atât pe verticală cât şi pe orizontală. Modul de aliniere a informaţiilor se poate modifica din secţiunea Alignment a casetei de dialog Format Cell.

Pentru a schimba modul de aliniere a informaţiilor, trebuie efectuaţi următorii paşi:1. Se selectează celula sau domeniul de celule care conţine datele care trebuie aliniate2. Se aplică comanda Format, Cells. Pe ecran va apărea caseta de dialog Format Cells.3. Se aplică un clic pe butonul Alignment. Pe ecran vor apărea opţiunile pentru aliniere (figura 3.3):

Figura 3.3 – caseta de dialog Format Cells - secţiunea Alignment

Elementele acestei casete sunt:

secţiunea Text alignment care conţine: caseta Horizontal - în care se specifică modul de aliniere pe orizontală. Opţiunile

cele mai folosite sunt: General – modul de aliniere implicit

Page 26: EXCEL - SUPORT DE CURS

Left – aliniere la stânga în celulă Center – centrare în celulă Right – aliniere la dreapta în celulă Center Across Selection – centrare într-un domeniu de celule.

caseta Vertical - în care se specifică modul de aliniere pe verticală în celulă. Opţiunile cele mai folosite sunt:

Top – textul se aliniază faţă de marginea de sus a celulei. Bottom – textul este centrat pe vericală. Center - textul se aliniază faţă de marginea de jos a celulei.

secţiunea Orientation. În această secţiune se specifică unghiul sub care va fi afişat textul în celulă.

secţiunea Text control. Această secţiune conţine trei opţiuni: Wrap Text. În mod normal Excel afişează textul dintr-o celulă pe un singur

rând. Cu opţiunea Wrap Text, textul dintr-o celulă poate fi afişat pe mai multe rânduri, fără a modifica lăţimea celulei.

Shrink to Fit. Aceată opţiune adaptează fontul textului la lăţimea celulei curente. Dacă se modifică lăţimea celulei, mărimea fontului creşte sau se micşorează în mod corespunzător.

Merge Cells. Cu aceată opţiune se unesc mai multe celule. 4. Se aplică un clic pe butonul OK sau se apasă tasta <Enter>.

Utilizarea butoanelor pentru aliniere

O metodă rapidă pentru alinierea informaţiilor din celule presupune folosirea butonelor de aliniere de pe bara cu instrumente de formatare. Aceste butoane sunt:

- Aliniere la stânga

- Centrare

- Aliniere la dreapta

- Unire şi centrare

Modificarea fonturilor

Când se introduc date în Excel, acestea sunt formatate automat folosind fontul implicit (de obicei Arial). Aspectul textului poate fi modificat folosind caseta de dialog Format Cells.

Pentru a modifica fonturile în Excel, trebuie efectuaţi următorii paşi:1. Se selectează celula sau domeniul de celule ce conţine datele care trebuie formatate.2. Se aplică comanda Format, Cells.3. Se selectează butonul Font din partea superioară a casetei. Pe ecran apare următoarea casetă de dialog (figura 3.4):

Elementele acestei casete sunt:- caseta Preview. În această casetă pot fi observate modificările care se fac în

celelalte câmpuri ale casetei de dialog.

Page 27: EXCEL - SUPORT DE CURS

- lista Font. Această listă conţine toate fonturile disponibile. De aici se selectează fontul dorit.

- lista Font Style. Această listă conţine stilurile corpului de literă care pot fi aplicate caracterelor: Bold, Italic, Regular, Bold Italic.

- lista Size. În această listă se stabileşte înălţimea în puncte a caracterelor.- lista Underline. Această listă conţine mai multe stiluri de subliniere. Lista

conţine şi opţiunea None, a cărei selectare are ca efect anularea unei sublinieri.- lista Color. Din această listă se selectează culoarea de scriere a literelor.- zona Effect. În această zonă pot fi specificate o serie de efecte speciale, prin

marcarea uneia sau mai multor opţiuni propuse de Excel: Strikethrough - este trasată o linie la mijlocul literelor. Superscript - caracterele sunt scrise mai sus şi mai mici, ca un exponent. Subscript - caracterele sunt scrise mai jos şi mai mici, ca un indice..

Figura 3.4 – caseta de dialog Format Cells - secţiunea Font

4. Se selectează opţiunile dorite.5. Se aplică un clic pe butonul OK sau se apasă tasta <Enter>.

Modificarea fonturilor cu ajutorul butoanelor

O metodă mai rapidă de modificare a atributelor textului presupune folosirea butoanelor de pe bara de instrumente de formatare:Butonul Font: La apăsarea acestui buton apare lista fonturilor disponibile, din care se poate selecta fontul dorit.

Butonul Size:

Page 28: EXCEL - SUPORT DE CURS

La apăsarea acestui buton apare o listă din care se stabileşte înălţimea în puncte a caracterelor.

Butonul Bold: Dacă acest buton este apăsat are loc scrierea cu litere îngroşate, dacă nu textul este scris normal.

Butonul Italic: Dacă acest buton este apăsat are loc scrierea cu litere aplecate, dacă nu textul este scris normal.

Butonul Underline: Dacă acest buton este apăsat are loc scrierea cu litere subliniate, altfel textul este scris normal.Folosind butoanele Bold, Italic şi Underline se pot aplica mai multe stiluri de scriere (orice combinaţie dintre ele). Activarea/ dezactivarea unui stil se face aplicând un clic pe butonul respectiv.

Butonul Font Color : La apăsarea acestui buton apare o listă din care se selectează culoarea de scriere a literelor.

Pentru a modifica atributele textului cu ajutorul butoanelor se vor efectua paşii următori:1. Se selectează celula sau domeniul ce conţine textul al cărui aspect trebuie modificat.2. Se selectează butonul corespunzător.

Adăugarea de chenare la celule

În mod normal liniile de reţea din jurul celulelor nu sunt tipărite. Excel oferă facilitatea de a adăuga chenare celulelor selectate sau unui întreg domeniu de celule. Chenarul poate apărea pe cele patru laturi ale celulei sau numai pe laturile specificate.

Pentru a adăuga chenare la o celulă sau unui domeniu de celule, trebuie efectuaţi următorii paşi :1. Se selectează celula (celulele) în jurul căreia (cărora) se va trasa chenarul.2. Se aplică comanda Format, Cells. Pe ecran va apărea caseta de dialog Format Cells

(figura 3.5), din care se selectează butonul Borders.

Page 29: EXCEL - SUPORT DE CURS

Figura 3.5 – caseta de dialog Format Cells - secţiunea Border

Elementele din această secţiune sunt: lista Line. Din această listă se selectează tipul liniilor cu care va fi trasat

chenarul. lista Color. Din această listă se selectează culoarea liniilor cu care va fi trasat

chenarul. zona Presets. În această zonă există trei butoane:

None. Dacă în jurul unei celule sau a unui domeniu există un chenar, acesta este anulat cu opţiunea None.

Outline. În cazul în care a fost selectat un domeniu de celule, liniile de reţea vor fi trasate doar în jurul zonei selectate. Nu sunt trasate liniile din interiorul domeniului.

Inside. Acest buton este activ doar dacă este selectat un domeniu de celule. Efectul obţinut este trasarea liniilor de reţea în interiorul zonei selectate.

zona Border. În această zonă există mai multe butoane, cu ajutorul cărora se poate modifica aspectul chenarului. Simbolul fiecărui buton sugerează linia din chenar asupra căreia se vor efectua modificări. Dacă butonul este apăsat linia va fi trasată dacă nu este apăsat linia nu va fi trasată. Pentru a modificarea culorii sau a tipului de linie se selectează din listele Style şi Color aceste atribute, după care se foloseşte butonul corespunzător din zona Border.

3. Se aplică un clic pe butonul OK sau se apasă tasta <Enter>.

Adăugarea chenarelor cu ajutorul butoanelor

Pentru a adăuga rapid chenare în jurul celulelor, se poate utiliza butonul Borders -

de pe bara de instrumente de formatare. La apăsarea săgeţii din dreptul acestui

Page 30: EXCEL - SUPORT DE CURS

buton apare o listă din care se selectează tipul de chenar care trebuie aplicat. Dacă se apasă pe buton (nu pe săgeată) se va aplica ultimul tip de chenar selectat.

Stabilirea culorii de fundal din celule

Pentru a obţine diverse efecte, în Excel se pot adăuga culori de fundal celulelor din foile de calcul.

Figura 3.6 – caseta de dialog Format Cells - secţiunea Patterns

Pentru a aplica culori în fundalul celulelor, se vor efectua paşii următori: 1. Se selectează celula sau domeniul de celule căreia la care se va aplica o culoare de

fundal.2. Se aplică comanda Format, Cells. Pe ecran va fi afişată caseta de dialog Format

Cells (figura 2.6), din care se selectează butonul Patterns.Elementele din această secţiune sunt: lista Color. Din această listă se selectează culoarea pentru fundal. lista Pattern. Din această listă se selectează textura şi culoarea texturii care se

aplică deasupra culorii de fudal. zona Sample. În această zonă se poate observa efectul opţiunilor selectate.

3. Se aplică un clic pe butonul OK sau se apasă tasta <Enter>.

Stabilirea culorii de fundal cu ajutorul butoanelor

Page 31: EXCEL - SUPORT DE CURS

Pentru a adăuga rapid culoarea de fundal într-o celulă sau domeniu, se poate

utiliza butonul Fill Color - de pe bara de instrumente de formatare. La apăsarea săgeţii din dreptul acestui buton apare o listă din care se selectează culoarea de fundal care trebuie aplicată. Dacă se apasă pe buton (nu pe săgeată) se va aplica ultima culoare selectată.

Page 32: EXCEL - SUPORT DE CURS

LECŢIA 4

UTILIZAREA FORMULELOR ÎN EXCEL

Page 33: EXCEL - SUPORT DE CURS
Page 34: EXCEL - SUPORT DE CURS

Utilizarea formulelor în Excel

Microsoft Excel a fost creat special pentru efectuarea de calcule numerice. Pentru a indica programului modul de calcul trebuie scrise formule. Formulele sunt formate, de regulă din adrese de celule, valori şi operatori matematici. La efectuarea calculelor sunt respectate toate regulile algebrice: prioritatea operaţiilor de înmulţire şi împărţire, a parantezelor etc.

Toate formulele se introduc cu semnul = în faţă. Dacă se omite semnul =, Excel consideră că în celula respectivă a fost introdus un text.

În continuare sunt prezentaţi principalii operatori utilizaţi în Excel.

Operatori aritmetici

Operator Scop– scădere+ adunare* înmulţire/ împărţire

% procente^ ridicare la putere

Operatori de text

Folosind operatorii pentru text se pot concatena texte cuprinse între ghilimele (“”) sau texte din celule diferite. De exemplu dacă se scrie formula =”Total vânzări: ”$B4 rezultatul este Total vânzări: 28, dacă celula B4 are valoarea 28.

Operatori pentru comparaţiiPentru a compara rezultatele se pot folosi operatorii pentru

comparaţii:

Operator Scop

= egal cu mai mic decât mai mare decât= mai mic sau egal cu= mai mare sau egal cu

diferit de

Pentru introducerea unei formule se execută paşii următori:1. Se selectează celula în care trebuie să apară rezultatul formulei;2. Se scrie formula precedată de semnul .3. Se apasă tasta Enter

După apăsarea tastei Enter în celulă apare automat rezultatul calculelor. Dacă celula care conţine formula este selectată din nou, se constată:

în bara de formule este afişată formula de calcul; în celulă apare rezultatul.

Page 35: EXCEL - SUPORT DE CURS

Exemplu:O foaie de calcul conţine următoarele valori:

în celula A1 valoarea 7; în celula B1 valoarea 3.

Dacă în celula C1 se scrie formula A1B1, după apăsarea tastei Enter în celulă se va afişa rezultatul 10 (73).

Excel poate fi setat astfel încât şi în celule să fie afişate formulele. Pentru aceasta trebuie efectuaţi paşii următori:

1. Se aplică comanda Tools, Options.2. Se selectează butonul View (figura 4.1).

Figura 4.1 - caseta de dialog Options - secţiunea View

3. În zona Window Options se aplică un clic în câmpul Formulas, pentru a-l marca.4. Se selectează butonul OK.

Vizualizarea formulelor din celulele unei foi de calcul ar fi necesară doar în cazul în care trebuie vizualizate toate formulele deodată (pentru tipărire sau corecţii).

Pentru revenirea la situaţia în care în celule apar rezultatele formulelor se aplică procedura anterioară numai că se va demarca câmpul Formulas.

Copierea formulelor

La copierea unei formule aceasta este adaptată pentru a corespunde poziţiei celulei în care va fi copiată.

Page 36: EXCEL - SUPORT DE CURS

De exemplu, dacă se copiază formula A1B1 din celula C1 în celula D1, aceasta va fi adaptată pentru coloana D, devenind B1C1. La copierea în celula C2, aceasta devine A2B2.

Dacă se copiază o formulă pe aceeaşi linie, toţi indicatorii de linie din celulele formulei rămân neschimbaţi. Indicatorii de coloană se modifică. Peste o coloană la dreapta, indicatorii de coloană se modifică cu o literă peste două coloane cu două litere etc.

Dacă se copiază o formulă pe aceeaşi coloană prin copiere rămân neschimbaţi indicatorii de coloană. În schimb se modifică indicatorii de linie. Cu o linie mai jos indicatorii de linie cresc cu 1, peste două linii cu 2 etc.

Exemplu:În celula A5 se introduce formula B2. Excel ştie că în celula A5 este folosită o

celulă care se găseşte cu trei rânduri mai sus şi o coloană la dreapta.Prin copierea formulei din A5 în C8, Excel actualizează indicatorii de linie şi de

coloană astfel încât în celula C8 va fi referită o celulă care se găseşte tot cu trei rânduri mai sus şi o coloană la dreapta, dar faţă de C8. Deci formula din această celulă va fi D5.

Pentru a copia o formulă într-un domeniu de celule alăturat, se efectuează următorii paşi:

1. Se aplică un clic pe celula care conţine formula ce trebuie copiată.2. Se poziţionează cursorul mouse-ului în colţul din dreapta jos al celulei.3. Când cursorul îşi modifică forma (apare un cursor de forma unei cruci mici) se

apasă butonul stâng al mouse-ului şi se trage de cursor peste celulele în care trebuie copiată formula.

Formulele pot fi copiate şi în celule neadiacente, folosind comenzile Copy şi Paste:

1. Se aplică un clic pe celula care conţine formula;2. Se aplică comanda Edit, Copy;3. Se aplică un clic pe celula în care se face copierea;4. Se aplică comanda Edit, Paste.

Aplicaţie

Un fond mutual deţine acţiuni la mai multe societăţi comerciale. Pentru fiecare tip de acţiune se cunosc: valoarea de cumpărare, valoarea curentă de pe piaţă şi numărul de acţiuni cumpărate. Să se calculeze profitul (sau pierderea) pentru fiecare tip de acţiune.

Se va crea următoarea foaie de calcul (figura 4.2)

Figura 4.2

Foaia de calcul creată conţine următoarele informaţii:

Page 37: EXCEL - SUPORT DE CURS

în coloana A – numele acţiunilor; în coloana B – numărul de acţiuni cumpărate; în coloana C – valoarea la cumpărare a acţiunilor; în coloana D – valoarea curentă a acţiunilor; în coloana E – se va calcula profitul sau pierderea pentru fiecare acţiune.

În celula E2 se va introduce formula B2*(D2-C2).

Se copiază formula din E2 în domeniul E3:E4, astfel:1. Se aplică un clic în celula E2;2. Se poziţionează cursorul în colţul din dreapta jos al celulei;3. Când cursorul îşi modifică forma (apare un cursor de forma unei cruci mici

+) se apasă butonul stâng al mouse-ului şi se trage de cursor peste celulele E3 şi E4.

Utilizarea adreselor absolute

Aşa cum am văzut, la copierea unei formule Excel adaptează indicatorii de linie şi de coloană ai celulelor (referinţele celulei) în funcţie de noua poziţie din foaia de calcul.

Modul de adresare al celulelor folosit până în prezent (notaţia A7) foloseşte sistemul de adresare relativ.

Există multe situaţii în care, prin copierea unor formule, unele celule trebuie să rămână fixe (nu trebuie să se modifice indicatorii de linie/coloană). În acest caz se foloseşte sistemul de adresare absolut. În faţa indicatorilor care nu trebuie să se modifice se pune simbolul $. De exemplu $A$7.

Exemplu:În celula A5 se introduce formula =$B$2. După copierea formulei din A5 în C8, în celula C8 formula va fi tot =$B$2. Prin copiere nu s-a modificat nici indicatorul de linie, nici cel de coloană.

La copierea pe linie/coloană indicatorii de linie/coloană nu se modifică. În aceste cazuri formulele pot conţine referiri mixte:

A$7 – linia este adresată absolut, coloana relativ.

$A7 - linia este adresată relativ, coloana absolut.

Aplicaţie

La firma XYZ preţurile sunt stabilite în $. Datorită modificării cursului de schimb, trebuie să recalculeze destul de frecvent preţul în lei al produselor sale. Să se proiecteze o foaie de calcul astfel încât preţul în lei să se calculeze automat la modificarea cursului de schimb. Se va crea următoarea foaie de calcul (figura 4.3):

Page 38: EXCEL - SUPORT DE CURS

Figura 4.3

Dacă în celula C4 s-ar introduce formula =B4*B1 (preţul în lei * cursul de schimb) şi acestă formulă s-ar copia în C5 şi C6, rezultatele nu ar fi corecte. În C5 s-ar obţine valoarea 0, iar în C6 #N/A.

Dacă analizăm formulele din aceste celule constatăm că:

celula C5 conţine formula =B5*B2 (B4 s-a transformat în B5, iar B1 în B2); celula C6 conţine formula =B6*B3 (B4 s-a transformat în B6, iar B1 în B3).Formulele corecte din aceste celule ar trebui să fie =B5*B1, respectiv =B6*B1.Deci celula B1 nu trebuie să se modifice atunci când este copiată. Pentru a realiza

acest lucru celula B1 trebuie referită absolut. Deci trebuie introdus simbolul $ în faţa indicatorului de linie. Formula corectă care trebuie introdusă în celula C4 este =B4*B$1. La copierea acestei formule în C5 şi C6 se constată că formulele din aceste celule sunt corecte. Celula C5 conţine formula =B5*B$1, iar celula C6 conţine formula =B6*B$1.

Page 39: EXCEL - SUPORT DE CURS

LECŢIA 5

INTRODUCEREA ŞI EDITAREA DATELOR

Page 40: EXCEL - SUPORT DE CURS
Page 41: EXCEL - SUPORT DE CURS

Utilizarea funcţiilor în Excel

Excel oferă peste 200 de funcţii (formule predefinite), care permit crearea unor formule complexe pentru o mare diversitate de aplicaţii: ştiinţifice, inginereşti, de afaceri etc.

O funcţie este definită de numele şi argumentele ei.Argumentele unei funcţii se introduc între paranteze. În cazul în care se folosesc

mai multe argumente, acestea se separă prin virgulă. Funcţia SUM, de exemplu, adună toate numerele dintr-un domeniu de celule.

Adresa celulelor specificate formează argumentul funcţiei:

Dacă o funcţie nu are nici un argument, se scriu totuşi parantezele, numai că între ele nu se va mai nota nimic. De asemenea, funcţiile pot conţine atât argumente obligatorii cât şi argumente opţionale.

Cel mai folosit tip de argument este cel numeric, dar argumentele pot fi şi de tip text, dată oră sau matrice. Dacă un text este folosit ca argument într-o funcţie, el trebuie introdus între ghilimele.

Datorită numărului mare de funcţii încorporate în Excel acestea au fost grupate în mai multe categorii:

- Funcţii matematice- Funcţii financiare- Funcţii logice- Funcţii de căutare- Funcţii de lucru cu texte- Funcţii pentru lucrul cu date şi ore.- Funcţii statistice, etc.etc.

Pentru a introduce o funcţie în Excel se poate utiliza una din următoarele metode:1. Funcţia este scrisă de utilizator. În acest caz se presupune că utilizatorul ştie

sintaxa funcţiei.

Page 42: EXCEL - SUPORT DE CURS

Figura 5. 1 – caseta de dialog Insert Function

2. Funcţia este introdusă folosind aplicaţia Function Wizard, care se lansează la aplicarea comenzii Insert, Function. Pe ecran va apărea caseta de dialog Insert Function (figura 5.1).

În lista Or select a category sunt afişate toate categoriile de funcţii încorporate în Excel. La selectarea unei categorii în caseta Select a function sunt afişate în ordine alfabetică funcţiile existente în categoria selectată.

După selectarea unei funcţii, se aplică un clic pe butonul OK pentru a trece la următoarea casetă de dialog (figura 5.2).

Figura 5. 2

În caseta de dialog a funcţiei alese (figura 5.2), trebuie introduse argumentele necesare pentru funcţia respectivă. Casetele text pentru argumente trebuie să conţină valori sau referinţe de celule.

Funcţia se termină de introdus selectând butonul OK.

Page 43: EXCEL - SUPORT DE CURS

În continuare vor fi prezentate funcţiile Excel întâlnite mai frecvent, grupate pe categorii.

Funcţii matematice

Funcţiile matematice constituie infrastructura oricărei foi de calcul. Majoritatea funcţiilor ştiinţifice şi inginereşti pot fi regăsite în grupele funcţiilor matematice.

ABS (număr)Funcţia ABS returnează valoarea absolută a unui număr.Exemple: ABS (–5) va returna valoarea 5

ABS (5) va returna valoarea 5

EXP (număr)Funcţia EXP calculează exponenţiala unui număr (e ridicat la puterea specificată de argumentul număr).Exemplu: EXP (0) va returna valoarea 1

LN (număr)Funcţia LN calculează logaritmul natural al numărului specificat.Exemplu: LN (1) va returna valoarea 0

INT (număr)Funcţia INT rotunjeşte un număr până la cea mai apropiată valoare întreagă.Exemple: INT (7.6) va returna valoarea 7

INT (–7.6) va returna valoarea 8MOD (a, b)Funcţia MOD calculează restul (modulul) lui a împărţit la b. Dacă b este 0, se va afişa valoarea de eroare #DIV/0.Exemplu: MOD (7, 6) va returna valoarea 1

MOD (32, 15) va returna valoarea 2

POWER (a, b)Funcţia POWER efectuează ridicarea unui număr a la puterea b.Exemplu: POWER (2, 2) va returna valoarea 4

RAND ( )Funcţia RAND furnizează un număr aleator între 0 şi 1. Funcţia nu acceptă argumente. Apăsarea tastei F9 va produce generarea altor numere.

ROUND (număr, număr de zecimale)Funcţia ROUND rotunjeşte numărul specificat în primul argument la numărul de zecimale specificat în al doilea argument.Exemplu: ROUND (753.345, 2) va returna valoarea 753.35

ROUND (753.342, 2) va returna valoarea 753.34

ROUNDUP (număr, număr de zecimale)Funcţia ROUNDUP rotunjeşte în sus numărul specificat în primul argument, cu numărul de zecimale specificat în al doilea argument.

Page 44: EXCEL - SUPORT DE CURS

Exemplu: ROUNDUP (7.49, 1) va returna valoarea 7.5

ROUNDDOWN (număr, număr de zecimale)Funcţia ROUNDDOWN rotunjeşte în jos numărul specificat în primul argument, cu numărul de zecimale specificat în al doilea argument.Exemplu: ROUNDDOWN (7.49, 1) va returna valoarea 7.4

SQRT (număr)Funcţia SQRT extrage rădăcina pătrată din argumentul specificat.Exemplu: SQRT (4) va returna valoarea 2

SUM (număr1, număr2, …)Funcţia SUM calculează suma tuturor argumentelor. Argumentele pot fi valori, celule individuale sau domenii de celule, dar numărul lor este limitat la 30. Argumentele numerice sunt ignorate.

Un domeniu de celule este specificat prin celula din colţul stâng sus al domeniului, separatorul : şi celula din colţul drept jos al domeniului.

Exemplu: SUM (A1:B3) va calcula suma valorilor din celulele A1, A2, A3,B1,B2, B3

AVERAGE (număr1, număr2, …)Funcţiile AVERAGE calculează media aritmetică a tuturor argumentelor. Argumentele pot fi valori, celule sau domenii de celule, dar numărul lor este limitat la 30. Argumentele nenumerice sunt ignorate.Exemplu: AVERAGE (A1:B3) va calcula media aritmetică a valorilor din celulele A1, A2, A3, B1, B2, B3.

COUNT (număr1, număr2, …)Funcţia COUNT numără în argumentele specificate celulele care conţin numere. Funcţia poate avea între 1 şi 30 de argumente.Exemplu: COUNT (A2:A5) va returna valoarea 3 atunci când domeniul A2:A4 conţine numerele 2,3,4, iar celula A5 este goală.

MAX (număr1, număr2, …)Funcţia MAX returnează valoarea celui mai mare argument. Funcţia poate avea cel mult 30 de argumente. Celulele goale, valorile de tip text, logic sau de tip eroare vor fi ignorate.Exemplu: MAX (A1:A3) va returna valoarea 10, dacă numerele din acest domeniu sunt: 1,10, 7, 4.

MIN (număr1, număr2, …)Funcţia MIN returnează valoarea celui mai mic argument. Funcţia poate avea cel mult 30 de argumente. Celulele goale, valorile de tip text, logic sau de tip eroare vor fi ignorate.Exemplu: MIN (A1:A3) va returna valoarea 1, dacă numerele din acest domeniu sunt: 1,10, 7, 4.

IF (condiţie, valoare adevărată valoare falsă)

Page 45: EXCEL - SUPORT DE CURS

Funcţia IF evaluează o condiţie. Dacă condiţia este adevărată funcţia va returna al doilea aergument- valoarea adevărată. Dacă condiţia este falsă funcţia va returna al treilea argument - valoarea falsă.Exemplu: IF (A1”A2, “mai mic”, “mai mare”) va returna textul mai mic dacă celula A1 conţine valoarea 7 şi celula A2 conţine valoarea 10.

Funcţii logice

Funcţiile logice sunt folosite în cazurile în care trebuie evaluate mai multe condiţii. În general, aceste funcţii nu se folosesc singure, ele apar ca argumente la alte funcţii (de exemplu în funcţia IF).

AND (condiţia1, condiţia2, …)Funcţia AND returnează valoarea adevărată (TRUE) dacă toate condiţiile specificate în argumente sunt adevărate. Dacă cel puţin o condiţie nu este adevărată funcţia AND va returna valoarea fals (FALSE).Funcţia poate avea cel mult 30 de argumente.

OR (condiţia1, condiţia2, …)Funcţia OR returnează valoarea adevărată (TRUE) dacă cel puţin o condiţie din cele specificate în argumente este adevărată. Dacă nici o condiţie nu este adevărată funcţia OR va returna valoarea fals (FALSE).Funcţia poate avea cel mult 30 de argumente.

NOT (condiţie)Funcţia NOT returnează valoarea adevărată dacă condiţia este falsă şi dacă condiţia este adevărată.

Funcţii text

Funcţiile text permit manipularea informaţiilor de tip text. Datele din foile de calcul pot fi concatenate pentru a alcătui titluri, propoziţii, etichete.

CHAR (număr)Funcţiile CHAR returnează caracterul care corespunde codului ASCII specificat ca argument.Exemplu: CHAR (65) va returna caracterul A.

CONCATENATE (text1, text2, …)Funcţia CONCATENATE efectuează reuniunea tuturor argumentelor (cel mult

30).Exemplu: CONCATENATE ("Microsoft", "Excel") va returna textul Microsoft Excel.

EXACT (text1, text2)Funcţia EXACT compară textele text1 şi text2. Dacă acestea sunt identice funcţia va returna valoarea adevărată (TRUE), astfel se va reţine valoarea logică FALSE. Funcţia face distincţie între literele mici şi mari.

Page 46: EXCEL - SUPORT DE CURS

FIND (text-căutat, sursă start-num)Funcţia FIND caută primul argument, text-căutat în textul din al doilea argument sursă începând cu poziţia specificată de start-num. În cazul în care acesta este găsit, funcţia FIND returnează poziţia de început a textului căutat. Dacă argumentul start-num este în afara limitelor sau dacă nu este găsită o valoare, se va afişa codul de eroare #VALUE. Dacă argumentul start-num nu este specificat, se presupune că acesta are valoarea 1.Exemplu: FIND (B12, "ABCDE", 1) va returna valoarea 3 dacă celula B12 conţine caracterul C.

LEFT (text, num-car)Funcţia afişează primele num-car caractere din partea stângă a unui text.

Argumentul num-car trebuie să fie mai mare ca 0. Dacă se omite introducerea sa se va

presupune că este egal cu 1.

Exemplu: LEFT (A1, 5) va returna valoarea Micro dacă în celula A1 se găseşte textul Microsoft.

RIGHT (text, num-car)Funcţia afişează primele num-car caractere din partea dreaptă a unui text.

Argumentul num-car trebuie să fie mai mare ca 0. Dacă se omite introducerea sa se va

presupune că este egal cu 1.

Exemplu: RIGHT (A1, 4) va returna valoarea soft dacă în celula A1 se găseşte textul Microsoft.

LEN (text)Funcţia LEN calculează numărul de caractere din textul specificat de argument.Exemplu: LEN ("Microsoft") va returna valoarea 9.

MID (text, start-num, num-car)Funcţia MID extrage un număr de num-car caractere din text, începând cu poziţia

start-num.Exemplu: MID ("Microsoft Excel 7.0", 11, 5) va returna textul Excel.

LOWER (text)Funcţia LOWER converteşte eventualele majuscule din text în litere mici.Exemplu: LOWER ("Microsoft Excel") va returna microsoft excel

PROPER (text)Funcţia PROPER determină afişarea textului cu litere mici, începuturile de cuvinte fiind scrise cu majuscule.Exemplu: PROPER ("MICROSOFT EXCEL") va returna Microsoft Excel.

Page 47: EXCEL - SUPORT DE CURS

TRIM (text)Funcţia TRIM şterge toate blank-urile din text, astfel încât între cuvinte să rămână un singur spaţiu.Exemplu: TRIM ("Microsoft Excel") va returna Microsoft Excel.

TEXT (valoare, format-text)Funcţia TEXT converteşte o valoare numerică în text şi o afişează corespunzător formatului indicat prin al doilea argument. Rezultatul apare afişat ca un număr formatat, dar în realitate este de tip text. Se pot utiliza oricare din formatele numerice predefinite sau personalizate, prezentate în lecţia “Formatarea foilor de calcul”.Exemplu: TEXT (457989, "$#, ##0.00") va returna $4,579.89

Funcţii pentru date şi ore

Programul Excel ataşează fiecărei date calendaristice şi ore câte un număr serial. Numerele seriale ataşate datelor calendaristice sunt mai mari ca 1, cele ataşate orelor sunt subunitare. Când efectuează calcule cu date şi ore, Excel foloseşte aceste numere seriale, numai formatul de afişare este de tip dată sau oră.

Cele mai folosite funcţii de lucru cu date şi ore sunt:

DATE (an, lună zi)Funcţia DATE returnează numărul serial pentru data specificată.Exemplu: DATE (1900, 1, 1) va returna 1 (numărul serial al datei 1.1.1900)

NOW ( )Funcţia NOW calculează numărul serial al datei şi al orei extrase din ceasul intern al calculatorului. Excel actualizează data şi ora doar la deschiderea sau recalcularea foii. Această funcţie nu are argumente, însă este necesară introducerea parantezelor.Exemplu: NOW ( ) va returna 9/ 10/ 99 10:43, dacă aceasta este data curentă.Dacă rezultatul nu apare sub forma unei date, înseamnă că este afişat numărul

serial ataşat. Pentru afişarea sub formă de dată calendaristică celula respectivă trebuie

formatată de tip dată (vezi lecţia Formatarea foilor de calcul).

YEAR (dată calendaristică)Funcţia YEAR extrage anul din data specificată.Exemplu: YEAR ( 7/ 3/ 1999) va returna 1999.

MONTH (dată calendaristică)Funcţia MONTH extrage luna din data specificată.Exemplu: MONTH ( 7/ 3/ 1999) va returna 7 (se consideră că data este introdusă în formatul lună/ zi/ an)

Page 48: EXCEL - SUPORT DE CURS

DAY (dată calendaristică)Funcţia DAY extrage ziua din data specificată.Exemplu: DAY (7/ 3/ 1999) va returna 3.

TIME (oră minut, secundă)Funcţia TIME calculează numărul serial corespunzător numărului de ore, minute şi secunde indicate.Exemplu: TIME (18, 4, 19) furnizează valoarea 0,752998.

HOUR (oră)Funcţia HOUR returnează numărul de ore corespunzătoar orei specificate.Exemplu: HOUR (19:10:30) va returna valoarea 19.

MINUTE (oră)Funcţia MINUTE returnează numărul de minute corespunzătoare orei specificate.Exemplu: MINUTE (19:10:30) va returna valoarea 10.

SECOND (oră)Funcţia SECOND returnează numărul de secunde corespunzător orei specificate.Exemplu: SECOND (19:10:30) va returna valoarea 30.

Funcţii financiare

Programul Excel pune la dispoziţie şi o serie de funcţii financiare.

FV (dobândă reper, plată vp, tip)Funcţia FV calculează valoarea viitoare pentru o serie de încasări/ plăţi egale

(specificate în argumentul plată), făcute într-un număr de perioade reper, cu o anumită dobândă (primul argument). Dobânda trebuie să aibă aceeaşi unitate de măsură ca reper. De exemplu, dobânda anuală trebuie să se împartă la 12 dacă încasările/ plăţile se fac lunar.

Numărul vp reprezintă valoarea prezentă sau suma care se investeşte/ împrumută in momentul iniţial. Dacă vp este omis se consideră că este 0.

Tip poate lua valoarea 0 sau 1. Dacă are valoarea 0 se consideră că plăţile se fac la sfârşitul perioadei, dacă are valoarea 1, plăţile se fac la începutul perioadei. Dacă argumentul tip este omis se consideră că are valoarea 0.

Banii care sunt plătiţi sunt reprezentaţi prin numere negative, iar cei încasaţi sunt reprezentaţi prin numere pozitive.

Exemplu: Să presupunem că o persoană vrea să investească bani pentru un proiect care va fi realizat peste 1 an. De aceea, depune 1 000 $ într-un cont de economii cu o dobândă de 6% pe an (dobânda lunară va fi 6%/ 12, adică 0.5%). De asemenea, să presupunem că persoana respectivă va depune câte 100 $ la începutul fiecărei luni, în următoarele 12 luni. Câţi dolari vor fi în cont la sfârşitul celor 12 luni?

Aplicăm funcţia =FV(0.5%, 12, –100, –1000, 1) obţinem 2301.40 $.

PV (dobândă reper, plată vv, tip)Funcţia PV calculează valoarea prezentă a unui flux de încasări/ plăţi viitoare.Argumentele funcţiei au aceeaşi semnificaţie ca şi în funcţia FV.

Page 49: EXCEL - SUPORT DE CURS

Argumentul vv reprezintă valoarea viitoare, obţinută după efectuarea ultimei plăţi/ încasări. Dacă vv este omis, se consideră că este 0. De exemplu, dacă vreţi să economisiţi 100 000 000 lei pentru un proiect de 20 de ani, atunci 100 000 000 lei este valoarea viitoare.

Banii plătiţi sunt reprezentaţi prin numere negative, cei încasaţi prin numere pozitive.

Exemplu: O persoană ştie că îşi poate permite să plătească 220 $ pe lună în următorii 4 ani. Dobânda curentă de piaţă este de 9%. Cât de mare este împrumutul pe care şi-l permite persoana ?

Funcţia necesară pentru calcul este: =PV (0.09/12, 48, –220) care returnează valoarea 8840.65 $.

PMT (dobândă reper, vp, vv, tip)Funcţia PMT calculează suma care trebuie achitată periodic pentru un împrumut/

economie, dacă se indică dobânda, numărul perioadelor de plată (reper).Argumentele funcţiei au aceeaşi semnificaţie ca şi în funcţiile precedente.Pentru a determina suma totală de plătit pe durata împrumutului se înmulţeşte

valoarea returnată de funcţia PMT cu numărul de perioade.Exemple: 1. Ce sumă trebuie plătită lunar pentru un împrumut de 10 000 $ cu o dobândă

anuală de 8%, care trebuie achitat în 10 luni.Formula de calcul este:=PMT (8%/ 12, 10, 10000) care returnează valoarea –$ 1037.03 dacă plăţile se fac la sfârşitul lunii.sau =PMT (8%/ 12, 10, 10000, 0, 1) care returnează valoarea –$ 1,030.16 dacă plăţile se fac la începutul lunii.S-au obţinut valori negative pentru că sunt plăţi care trebuie efectuate.

2. Următoarea formulă returnează suma pe care cineva trebuie să o primească lunar, dacă a împrumutat 5 000 $ cu o dobândă anuală de 12% pe o perioadă de 5 luni.=PMT (12%/12, 5, –5000) returnează valoarea 1,030.20.S-au obţinut valori pozitive pentru că sunt sume ce trebuie încasate.

3. O persoană doreşte să strângă 50 000 $ în 18 ani prin economisirea unei sume lunare constante. Dobânda annuală este de 6%.Formula de calcul este:=PMT (6%/ 12, 18*12, 0, 50000) care returnează valoarea –129.08 $.

NPV (dobândă valoare1, valoare2, …)Funcţia NPV calculează valoarea prezentă actualizată a unui flux de venituri/

cheltuieli.Dacă n este numărul de argumente din şirul de valori ( n nu poate fi mai mare de

29), atunci valoarea netă actualizată se calculează cu formula:

Valorile trebuie să fie echidistante în timp şi să fie valori plătite/ încasate la sfârşitul fiecărei perioade.

Dobânda- reprezintă dobânda anuală.

Page 50: EXCEL - SUPORT DE CURS

Funcţia NPV este asemănătoare cu PV.Deosebirea constă în faptul că valorile utilizate de PV trebuie să fie constante, iar

PV acceptă valori fie la începutul, fie la sfârşitul perioadei.Exemplu: Pentru o investiţie trebuie plătiţi 10 000 $ timp de 1 an. În următorii trei

ani se obţin venituri anuale de 3 000 $, 4 200 $ şi 6 800 $. Dobânda anuală este de 10%. Să se calculeze valoarea netă actualizată a investiţiei.

Formula de calcul este:=NPV (10%, –10 000, 3 000, 4 200, 6 800) care returnează valoarea 1,188.44 $Al doilea argument este negativ pentru că reprezintă o cheltuială.

IRR (valori, aproximaţie)Funcţia IRR calculează rata internă de rentabilitate a unei proiect. Rata internă de

rentabilitate este valoarea coeficientului de actualizare (dobânzii) pentru care venitul net actualizat este 0.

Valori este o matrice sau un domeniu de celule care conţine numerele pentru care trebuie calculată rata internă de rentabilitate. Pentru a putea calcula IRR în domeniu trebuie să fie cel puţin o valoare negativă şi cel puţin una pozitivă.

Aproximaţie este un număr care se consideră a fi cel mai apropiat de rezultatul furnizat de către funcţia IRR.

Microsoft Excel foloseşte un algoritm iterativ pentru calcularea valorii IRR. Începând cu aproximaţia, funcţia IRR verifică toate posibilităţile de calcul până când rezultatul este dat cu o aproximaţie de 0.00001%. Dacă funcţia IRR nu găseşte un rezultat care să fie bun după 20 de încercări, se va returna valoarea de eroare #NUM!.

În cele mai multe cazuri nu este nevoie de acest argument. Dacă aproximaţie este omis, se va considera valoarea implicită 10%.

Dacă IRR returnează eroarea #NUM! Se va încerca din nou cu alte valori pentru aproximaţie.

Exemplu: O persoană vrea să facă o afacere. Pentru începerea afacerii are nevoie de 70 000 $. Veniturile estimate din primii cinci ani sunt: 12 000, 15 000, 18 000, 21 000 şi 26 000 de dolari.

În domeniul B1:B6 se introduc următoarele valori: -70 000, 12 000, 5 000, 18 000, 21 000, 26 000.

Pentru a calcula rata internă de rentabilitate a investiţiei se foloseşte formula:=IRR (B1:B6) care returnează valoarea 8.66%.

Funcţii de căutare

Două din cele mai utilizate funcţii de căutare din Excel sunt VLOOKUP şi HLOOKUP.

VLOOKUP (valoare, domeniu, index-linie, tip-căutare)HLOOKUP (valoare, domeniu, index-coloană tip-căutare)

Funcţiile VLOOKUP/ HLOOKUP caută valoarea specificată în primul argument în prima coloană/linie din domeniul specificat în al doilea argument. Apoi funcţia extrage din linia/coloana corespunzătoare valorii găsite elementul indicat în coloana/linia specificată în al treilea argument- index coloană/index linie.

Valorile din prima coloană/linie a domeniului trebuie să fie ordonata crescător sau alfabetic.

Page 51: EXCEL - SUPORT DE CURS

Argumentul tip-căutare are o valoare logică. El este opţional. Dacă lipseşte se consideră că are valoare TRUE (adevărată). Dacă acest argument are valoare TRUE este găsită valoarea cea mai mare care este mai mică sau egală cu valoarea căutată. Dacă are valoarea FALSE, este căutată valoarea exactă. Dacă această valoare nu este găsită în prima linie/coloană din domeniul specificat este returnată eroarea #N/A.

Aceste funcţii sunt folositoare în aplicaţii de calcul a impozitelor şi a comisioanelor.

Exemplu: Distribuitorii unei firme sunt plăţii în funcţie de valoarea vânzărilor. Dacă valoarea vânzărilor este mai mică de 5 000 000 comisionul este de 0%, între 5 000 000 şi 30 000 000 comisionul este de 4%, între 30 000 000 şi 70 000 000 comisionul este de 7%, peste 70 000 000 comisionul este de 10%.

Se va crea următoarea foaie de calcul (figura 5.3):

Figura 5.3

În B2 se introduce formula =VLOOKUP (B1, A5:B8, 2). Dacă în B1 se introduce valoarea 80000000, Excel caută această valoare în prima coloană din domeniul A5:B8, deci în celulele A5, A6, A7, A8, B5, B6, B7, B8. Cum această valoare nu este găsită funcţia găseşte cea mai mare valoare care este mai mică sau egală cu valoarea căutată deci 70000000. Această valoare se găseşte pe a patra linie din tabel (linia 8 din Excel). Din această linie Excel returnează valoarea găsită în coloana 2 (al treilea argument), deci 10%.

Introducerea referinţelor la alte foi de calcul

Într-o celulă se pot introduce şi referinţe la date din alte foi ale registrului de calcul. Pentru aceasta se introduce denumirea foii respective, un semn de exclamare şi referinţa la celulă. De exemplu: =Sheet1!A1 înseamnă că se face referire la celula A1 din foaia Sheet1.

Dacă denumirea foii de calcul conţine spaţii libere, numele acesteia trebuie încadrat între ghilimele. De exemplu: “Buget 2001”!A1.

Introducerea de referinţe la alte fişiere

Există situaţii în care sunt necesare date care se află în alt registru de calcul. Pentru a referi date din alt registru se introduce întâì numele registrului între paranteze drepte, numele foii de calcul, semnul exclamării şi referinţa la celulă. De exemplu:

Page 52: EXCEL - SUPORT DE CURS

formula =vanzari.XLS!Sheet1!A10, face referire la celula A10 din foaia de calcul Sheet1 conţinută în registrul vanzări.XLS.

Aplicaţii

1. Un întreprinzător vrea să facă o investiţie şi face câteva estimări privind valoarea investiţiei, cheltuielile anuale şi veniturile anuale. Să se determine anul în care investiţia devine profitabilă (anul în care venitul total depăşeşte cheltuielile totale).

Pentru rezolvarea problemei se va crea foaia de calcul din figura 5.4.

Celulele B1, B2, B3 vor conţine valorile pentru investiţie, cheltuielile anuale şi venitul anual.

Se generează în coloana A, începând cu celula A6 o serie de numere începând cu valoarea 0 şi pasul seriei 1.

În anul 0 cheltuielile totale sunt cele cu investiţia, iar venitul total este 0, deci în celula B6 se va introduce formula =B1, iar în celula C6 valoarea 0.

În anii următori la cheltuielile totale şi venitul total din anul precedent se adaugă cheltuielile anuale, respectiv venitul anual. Deci formulele din celulele B7 şi C7 sunt =B6‡B$2, respectiv =C6‡B$3. Pentru celulele B2 şi B3 s-a folosit referirea absolută deoarece aceste celule trebuie să rămână fixe la copiere (ele conţin cheltuielile anuale şi venitul anual).

Se copiază aceste formule pe coloană.

Figura 5. 4

În coloana D se calculează profitul total (venit total - cheltuieli totale). Se va introduce în celula D6 formula =C6-B6. Se copiază această formulă pe coloană.

Page 53: EXCEL - SUPORT DE CURS

Pentru a determina pragul de rentabilitate (anul în care venitul total depăşeşte cheltuielile totale), în celula E6 se introduce formula:

=if (D6=0, “=”, “”).Deci dacă veniturile totale sunt mai mari decât cheltuielile totale se va afişa o

săgeată.Dacă s-ar copia această formulă pe coloană săgeata s-ar afişa în dreptul tuturor

lunilor în care profitul este pozitiv. Pragul de rentabilitate se obţine în prima lună în care profitul este pozitiv. Deci, pentru a afişa săgeata doar în dreptul acestei luni în celula D7 se va introduce formula:

=if (AND(D7=0,D60), “=”, “”)Prima lună în care profitul este pozitiv este testată verificând profitul din luna

precedentă. Dacă acesta este negativ, înseamnă că avem prima lună cu profit, dacă este pozitiv înseamnă că şi în luna precedentă s-a obţinut profit.

Se copiază pe coloană formula din celula E7.

2. O firmă vinde televizoare în rate. Să se determine rata lunară totalul de plată şi să se construiască tabela amortizării în cazul în care un cumpărător achiziţionează un televizor în valoare de 5 000 000 lei. Se va considera că a fost plătit un avans de 500 000 lei şi că televizorul va fi plătit în 12 rate lunare cu o dobândă de 40%. Să se construiască o foaie de calcul cu ajutorul căreia să se calculeze automat aceste valori. La proiectarea foii de calcul se va avea în vedere că dobânda se poate modifica în timp.

Se va crea următoarea foaie de calcul (figura 5.5):

Page 54: EXCEL - SUPORT DE CURS

Figura 5. 5

Prima parte a foii de calcul conţine datele de intrare în problemă: numele cumpărătorului ( B1), valoarea obiectului cumpărat (B3), data de cumpărare (B4) dobânda anuală (B5) şi numărul de rate(B6).

În a doua parte a foii de calcul se va calcula rata lunară ce trebuie plătită în ideea că dobânda nu se va modifica. Tot aici se calculează totalul de plată şi valoarea totală a dobânzii. Se vor introduce următoarele formule:

B9:= -PMT(B5/12,B6,B3-B7) (rata lunară)B10:=B11–B3 (totalul de plată - valoarea iniţială)B11:=B6*B9 +B7 (numărul de rate*rata lunară + avansul)În a treia parte a foii de calcul se va crea un tabel care va conţine

pentru fiecare lună data la care trebuie plătită rata, rata dobânzii în luna respectivă restul de plată valoarea dobânzii şi rata lunară. Restul de plată în luna a doua este egal cu restul de plată în prima lună‡valoarea dobânzii -rata lunară.Se vor introduce următoarele formule:

B14:=DATE(year(B$4),MONTH(B$4)+A14,DAY(B$4))D14:=B3-B7D15:=D14+F14–E14E14:= -PMT(C14/12,B$6-A14+1,D14)F14:=D14*C14/12Se copiază pe coloană formulele din celulele D15, E14, F14, A14. În domeniul

A14:A25 se generează o serie numerică care începe de la valoarea 1 şi cu pasul seriei de 1.

Page 55: EXCEL - SUPORT DE CURS

3. Un agent economic îşi propune să-şi dezvolte activitatea şi are nevoie de un capital de 240000000 lei. Acest capital este împrumutat de la BRD cu o dobândă de 45% şi trebuie restituit în 5 ani. Care este suma lunară care trebuie plătită aici fiind inclusă atât dobânda compusă cât şi plata împrumutului?

Se va crea următoarea foaie de calcul (figura 5.6):

Figura 5.6

Celulele B1:B3 conţin datele de intrare în problemă: valoarea împrumutului, dobânda anuală şi perioada de restituire.

În celula B4 se calculează numărul de plăţi înmulţind perioada de restituire cu 12. Formula din B4 va fi =12*B3.

În B6 se calculează rata lunară cu formula = -PMT (B2/12, B4, B1)

4. Un proiect necesită un volum de investiţii de 45.000.000 lei. Durata de execuţie a proiectului este de doi ani, iar durata de viaţă economică este de 7 ani. Fluxul tranşelor anuale pentru investiţii, cheltuielile de exploatare şi încasările sunt cele din tabelul următor:

Anul 1 2 3 4 5 6 7 8 9Investiţii 15 30Cheltuieli de exploatare - - 20 25 25 25 24 24 22Încasări 40 45 45 47 47 48

Să se calculeze:1. Venitul net actualizat cumulat pentru o rată de actualizare de 20% 2. Rata internă de rentabilitate a proiectului.

Figura 5. 7

Page 56: EXCEL - SUPORT DE CURS

Se calculează pentru fiecare an fluxul de numerar scăzând din încasări cheltuielile de exploatare şi cheltuielile cu investiţii (figura 5.7). În celula B7 se introduce formula =B6–B5–B4. Se copiază formula la domeniul C7:J7.

În celula C9 se calculează venitul net actualizat cumulat cu formula =NPV(B1,B7:J7).

În celula C10 se calculează rata internă de rentabilitate cu formula =IRR(B7:J7).

5. Un întreprinzător care face o investiţie într-un obiectiv economic câştigă un venit net anual de 200000 lei, timp de 12 ani cât este durata de funcţionare a obiectivului. Care este valoarea prezentă a acestui flux de venituri în momentul investiţiei, la o valoare de discontare de 14%.

Se va crea următoarea foaie de calcul (figura 5.8):

Figura 5. 8

În celula B5 se va introduce formula: = PV (B3, B2, B1), unde B3 reprezintă dobânda, B2 perioada în care se obţin veniturile, B1 valoarea venitului anual.

6. O echipă de muncitori este plătită în funcţie de numărul de ore lucrate. Orele lucrate peste programul normal de lucru (8 ore) sunt plătite dublu. Să se calculeze cu cât este plătit zilnic fiecare muncitor, cunoscând tariful orar şi orele de intrare şi ieşire din tură.

Pentru rezolvarea problemei se va folosi foaia de calcul din figura 5.9.Pentru fiecare muncitor, se calculează în coloana E numărul de ore lucrate.

Formula utilizată în celula E4 este =D4-C4, formulă care se copiează în domeniul E5:E8.În coloana F se afişează salariul calculat pentru orele lucrate în fiecare zi. Formula

din celula F4 este:=IF(HOUR(E4)<8,HOUR(E4)*B$1+MINUTE(E4)*B$1/60,8*B$1+(HOUR(E4)-

8)*2*B$1+ MINUTE(E4)*2*B$1/60)Această formulă se copiează în domeniul F5:F8.Dacă în funcţia IF ar fi fost utilizată condiţia E4‹8, ar fi fost incorect. E4<8 este

întotdeauna adevărată deoarece în E4 avem o oră şi se ştie că numerele seriale ataşate orelor sunt mai mici decât 1, deci şi mai mici decât 8. Pentru a extrage numărul de ore lucrate s-a folosit funcţia HOUR.

Page 57: EXCEL - SUPORT DE CURS

Figura 5.9

7. Un registru Excel este alcătuit din două foi de calcul. O foaie de calcul – cursuri - conţine cursurile de schimb pentru mai multe valute, cealaltă foaie – casa – este folosită pentru a calcula echivalentul în lei al sumelor schimbate.

Foaia de calcul cursuri are structura din figura 5.10.

Figura 5.10

Foaia de calcul casa are structura din figura 5.11.

Figura 5.11

În coloana Nume se introduce numele persoanei care realizează o tranzacţie, iar în coloana Tranzacţie se introduce tipul tranzacţiei efectuate. Valorile permise în această coloană sunt C – pentru cumpărare şi V – pentru vânzare.

În coloana Tip valută se introduce numele monedei schimbate (atenţie, numele monedei trebuie să fie identic cu cel din foaia de calcul cursuri).

În coloana Suma schimbată se introduce suma care se schimbă.În coloana Echivalent lei se calculează echivalentul în lei al sumei schimbate (în

funcţie de valută suma schimbată şi tipul tranzacţiei). Formula utilizată în celula E2 este:=D2*VLOOKUP(C2, cursuri!A$2:C$9, IF(B2=”C”,2,3))

Page 58: EXCEL - SUPORT DE CURS

Se copiează formula pe coloană şi pe măsură ce se introduc date în foaia de calcul, echivalentul în lei al sumei schimbate se va calcula automat.

Page 59: EXCEL - SUPORT DE CURS

LECŢIA 6

CALCULE CU MATRICI

Page 60: EXCEL - SUPORT DE CURS
Page 61: EXCEL - SUPORT DE CURS

Calcule cu matrici

Matricile sunt domenii dreptunghiulare de formule sau valori pe care Excel le tratează ca fiind un grup. Unele funcţii şi formule care lucrează cu astfel de elemente returnează rezultate care sunt plasate în mai multe celule. Altele, deşi prelucrează matrici de valori, pot furniza un rezultat afişat într-o singură celulă.

Pentru a introduce o formulă de tip matrice se parcurg următoarele etape:

1. Se selectează domeniul care va conţine formula de tip matrice.2. Se introduce formula.3. După introducerea formulei se apasă combinaţia de taste <Shift> + <Ctrl> +

<Enter>.Foaia de calcul următoare (figura 6.1) conţine un exemplu cu datele care sunt introduse într-o factură:

Figura 6.1

Pentru a calcula totalul în coloana D se pot folosi şi metodele anterioare (în celula D4 se introduce formula =B4*C4, care apoi se copiază în celelalte celule din coloana D). În locul acestor formule se poate introduce o singură formulă valabilă în tot domeniul D4:D6, care va ocupa mai puţină memorie.

Pentru a calcula totalul utilizând formule de tip matice:

1. Se selectează domeniul D4:D6.2. Se introduce în linia de formulă formula ş B4:B6*C4:C63. Se tastează combinaţia de taste <Shift > + <Ctrl> + <Enter>.

În bara de formulă formula va fi afişată între acolade {}. Fiecare celulă a domeniului D4:D6 conţine aceeaşi formulă. Acoladele simbolizeză că formula este o matrice şi că domeniul matrice trebuie să fie tratat ca o entitate unică. Nu se pot introduce linii sau coloane în acest domeniu după cum nu este posibilă ştergerea componentelor sale sau editarea unei singure celule. Formula nu multiplică valorile din două celule, ci valorile din două matrici, B4:B6 şi C4:C6, făcând corespondenţa corectă a elementelor acestora B4*C4, B5*C5, B6*C6.

Rezultatele sunt plasate în celulele matricei D4:D6 selectată anterior.Domenii de tip matrice se pot utiliza şi ca argumente la funcţii. Funcţiile cu

argumente de tip matrice se introduc la fel ca şi formulele de acest tip.1. Se selectează un domeniu de dimensiuni corecte.2. Se introduce funcţia specificând domeniile de celule asupra cărora operează.3. Se apasă combinaţia <Shift> + <Ctrl> + <Enter>.

De exemplu, dacă în exemplul anterior trebuie calculat totalul general fără a calcula totalurile parţiale, în celula D7 trebuie introdusă formula {=SUM(B4:B6*C4:C6)}. Introducerea se termină cu <Shift> + <Ctrl> + <Enter> pentru ca formula să fie considerată ca formulă de tip matrice.

Page 62: EXCEL - SUPORT DE CURS

În mod uzual domeniul utilizat pentru introducerea formulelor sau funcţiilor de tip matrice trebuie să fie corect selectat. Dacă domeniul de tip matrice selectat este prea mic, nu se pot vedea rezultatele; dacă este prea mare, celulele care nu au fost utilizate vor conţine mesajul #N/A.

Funcţii pentru matrici

Funcţiile de lucru cu matrici cele mai utilizate sunt:

MDETERM (matrice)Funcţia MDETERM calculează determinantul unei matrici.

MINVERSE (matrice)Funcţia MINVERSE calculează inversa unei matrici.

MMULT (matrice1, matrice2)Funcţia MMULT efectuează înmulţirea a două matrici. Numărul de coloane din matrice1 trebuie să fie egal cu numărul de linii din matrice2.

TRANSPOSE (matrice)Funcţia TRANSPOSE efectuează operaţia de transpunere a unei matrici.

Dacă într-o formulă intervine o funcţie de tip matrice, introducerea formulei se termină tastând combinaţia de taste <Shift> + <Ctrl> + <Enter>.

Exemplul următor prezintă modul în care se poate calcula inversa unei matrici. Matricea iniţială este cuprinsă în domeniul A2:C4 (figura 6.2).

Figura 6.2Pentru a calcula inversa matricei se selectează domeniul A7:C9 (în acest domeniu se va afişa rezultatul), se introduce formula şi =MINVERSE(A2:C4) şi se apasă <Shift> + <Ctrl> + <Enter>.

Page 63: EXCEL - SUPORT DE CURS

Aplicaţie

Firma ABC vinde apă minerală sucuri şi vin în România şi Moldova. Foaia de calcul din figura 6.3 conţine situaţia vânzărilor din anii 1999 şi 2000.

Să se calculeze valoarea medie a vânzărilor pentru Romania şi Moldova pe ce doi ani.

Figura 6.3

Pentru a calcula valoarea medie a vânzărilor pentru România şi Moldova se introduc formulele:

Celula C18: = AVERAGE(IF($B5:$B14=”Romania”,C5:C14)). Se termină formula de introdus cu <Shift> + <Ctrl> + <Enter>. Se copiează formula în D18. Deci se va calcula media aritmetică doar a valorilor care în domeniul B5:B14 conţin textul Romania.

Celula C19: = AVERAGE(IF($B5:$B14=”Moldova”,C5:C14)). Se termină formula de introdus cu <Shift> + <Ctrl > + <Enter>. Se copiează formula în D19. Deci se va calcula media aritmetică doar a valorilor care în domeniul B5:B14 conţin textul Moldova.

Page 64: EXCEL - SUPORT DE CURS

LECŢIA 7

SISTEMUL DE GESTIUNE AL GRAFICELOR

Page 65: EXCEL - SUPORT DE CURS
Page 66: EXCEL - SUPORT DE CURS

Sistemul de gestiune al graficelor

Utilizând Excel, datele din tabele pot fi reprezentate sub formă grafică. Un grafic este un instrument eficient pentru prezentarea clară a datelor, într-un mod care oferă un impact vizual imediat. Cu alte cuvinte, graficele sunt mai uşor de înţeles “dintr-o privire” decât liniile şi coloanele de date. În Excel se pot crea mai multe tipuri de grafice. Unele din ele sunt bidimensionale (2D), altele tridimensionale (3D).Principalele tipuri de grafice sunt:

Arie - Area Bară - Bar Coloană - Column Linie - Line Circulare - Pie Inel - Doughnut Radar - Radar XY (Dispersate) – Scatter Suprafaţă - Surface Cilindru – Cylinder Cone – Con Piramid – Piramidă

Tipuri de grafice tridimensionale: Zonă Bară Coloană Linie Plăcintă Suprafaţă

Pentru majoritatea tipurilor de grafice, există cel puţin două variante sau subtipuri.Un grafic bidimensional este desfăşurat pe două axe:

axa orizontală - axa X, numită şi axa categoriilor axa valorilor – axa Y, pe care sunt reprezentate seriile de date. O serie de date este un

set de valori care descriu evoluţia unei mărimi.La graficele 3D este utilizată pentru a treia dimensiune axa Z.

Un grafic conţine mai multe obiecte care pot fi selectate şi modificate individual. Figura următoare (figura 7.1) prezintă unele din aceste obiecte.

Legenda

Titlu

Axa X

Axa Y

Grafic

Figura 7.1

Page 67: EXCEL - SUPORT DE CURS

În continuare este prezentată o descriere sumară a principalelor tipuri de grafice.

Graficele de tip arie - Area

Un grafic arie ilustrează continua schimbare în volum a unor serii de date. Acest tip de grafic însumează datele din toate seriile individuale pentru a crea linia de vârf care cuprinde zona, oferind privitorului o imagine asupra modului în care diferitele serii contribuie la volumul total. Utilizaţi graficul arie pentru cifrele referitoare la vânzări şi la producţie, pentru a arăta modul în care volumul se modifică în timp şi pentru a evidenţia cantitatea sau volumul schimbării.

Figura 7.2

Grafice bară - Bar

Un grafic bară este utilizat pentru compararea obiectelor neconectate în timp. Acest tip de grafic nu oferă o imagine prea bună a evoluţiei în timp, el utilizează bare orizontale pentru a arăta variaţia pozitivă sau negativă faţă de un punct de referinţă. Barele aflate la stânga punctului de referinţă arată o variaţie negativă iar cele din dreapta arată o variaţie pozitivă.

Figura 7.3

Grafice coloană - Column

Graficele coloană sunt în general folosite pentru a arăta variaţia în timp a unor mărimi necontinue. Acest tip de diagramă utilizează bare verticale pentru a da impresia de

Page 68: EXCEL - SUPORT DE CURS

măsurători făcute la intervale de timp diferite. Graficele coloană sunt folosite frecvent pentru comparea diferitelor elemente prin plasarea lor unele lângă altele.Asemănătoare cu graficele coloană sunt graficele de tip cilindru, con şi piramidă numai că la aceste grafice valorile nu mai sunt reprezentate prin coloane ci prin cilindre, conuri,

respectiv piramide.

Figura 7.4

Asemănătoare sunt graficele de tip cilindru, con, piramidă. Diferenţa este că valorile nu mai sunt reprezentate prin coloane, ci prin cilindrii, conuri, piramide.

Grafice linie - Line

Un grafic linie ilustrează evoluţia unei mărimi la care intervalele de variaţie sunt egale. Dacă intervalele de variaţie sunt neegale se va utiliza un grafic (dispersat) XY. Pentru fiecare serie de date se va obţine în grafic o linie.

Figura 7.5

Grafice circulare - Pie

Într-un grafic circular se evidenţiază mărimea părţilor în raport cu întregul. Într-un astfel de grafic se poate reprezenta o singură serie de date. Pentru a scoate mai bine în evidenţă valorile pe care le reprezintă sectoarele din grafic pot fi scoase în afara cercului.

Page 69: EXCEL - SUPORT DE CURS

Pentru a scoate în afară un sector dintr-un grafic circular, se aplică un clic pe suprafaţa sectorului pentru a selecta întregul grafic, apoi un al doilea clic pentru a selecta numai sectorul, după care se trage sectorul în afara cercului. Se eliberează butonul mouse-ului atunci când sectorul este poziţionat în locul dorit.

Figura 7.6

Grafice inel - Doughnut

La fel ca şi graficele circulare, graficele inel scot în evidenţă mărimea părţilor dintr-un întreg. Deosebirea este că structura diagramelor inel permite reprezentarea mai multor serii de date. Fiecare inel concentric conţine datele dintr-o serie de date.

Figura 7.7

Grafice radar - Radar

Fiecare categorie (fiecare etichetă care ar fi afişată pe axa X la un grafic în două dimensiuni) are propria axă. Punctele de date sunt plasate de-a lungul acestor axe. Un grafic radar rezultă prin unirea punctelor de date care au aceeaşi semnificaţie pe toate axele.

Figura 7.8

Page 70: EXCEL - SUPORT DE CURS

Grafice XY (Dispersate) - Scatter

Un grafic XY (Dispersat) este asemănător cu unul linie, numai că ilustrează evoluţia unor mărimi la care intervalele de variaţie nu sunt egale.

Figura 7.9

Crearea unui grafic

O metodă pentru realizarea automată pas cu pas, a unei diagrame pornind de la datele din foaia de calcul este folosirea aplicaţiei Chart Wizard. Graficele se pot crea în foaia de calcul curentă sau se pot plasa într-o nouă foaie de calcul din registrul curent.

Foaia de calcul următoare (figura 7.10) conţine vânzările dintr-o librărie pentru câteva articole, pe durata unei săptămâni.

Figura 7.10

Pe baza acestui tabel se pot construi două grafice

Page 71: EXCEL - SUPORT DE CURS

Primul grafic (figura 7.11) conţine trei serii de date: vânzările pentru creioane,

caiete şi pixuri. Pentru fiecare zi valorile fiecărei serii sunt reprezentate prin bare colorate în mod diferit. Barele de aceeaşi culoare fac parte din aceeaşi serie.

Figura 7.11Al doilea grafic (figura 7.12) conţine cinci serii de date: vânzările efectuate luni, marţi, miercuri, joi şi vineri. În grafic sunt reprezentate vânzările efectuate în fiecare zi pentru fiecare articol.

Figura 7.12

Dacă analizăm modul în care sunt generate seriile constatăm că la primul grafic seriile sunt generate pe linii, la al doilea grafic seriile sunt generate pe coloane. Deci, în funcţie de cum se generează seriile se obţin grafice diferite.

Dacă numărul de linii este mai mare decât numărul de coloane Excel va încerca să genereze seriile pe coloane, altfel va genera seriile pe linie. Acesta este modul în care Excel încearcă automat să genereze graficele, dar modul în care se se vor genera seriile poate fi specificat de către utilizator.

Primul pas în crearea unui grafic este selectarea datelor din foaia de calcul. Pentru selectarea datelor se vor respecta următoarele reguli:

Datele pentru grafic trebuie să fie introduse în linii şi coloane, care nu trebuie nepărat să fie adiacente(celulele nediacente se selectează ţinând apăsată tasta Ctrl).

Etichetele (textele) ce vor fi folosite în grafic vor fi plasate primele linii şi primele coloane ale tabelului.

Pentru exemplul nostru vom selecta domeniul A1:F4.

Page 72: EXCEL - SUPORT DE CURS

După selectarea datelor se face un clic pe butonul Chart Wizard. Pe ecran va apare prima fereastră din Chart Wizard – Step 1 of 4 (figura 7.13).

Din această fereastră se va selecta tipul graficului. În fereastră există două butoane: Standard Types şi Custom Types. Când este selectat butonul Standard Types apare o listă cu toate tipurile standard de grafice. Pentru fiecare tip de grafic există mai multe subtipuri. Dacă este selectat butonul Custom Types apare o listă de grafice predefinite. Din una din cele două liste se selectează tipul de grafic dorit.Pentru exemplul nostru vom selecta un grafic de tip coloană (Column).

În partea de jos a ferestrei apar trei butoane:Next – pentru a trece la pasul următor.Back – pentru a reveni la pasul anterior.Finish – se crează graficul folosind opţiunile selectate până la acel moment.Cancel – se renunţă la crearea graficului

figura 7.14 – caseta Chart Wizard – Step 1 of 4

Se selectează butonul Next pentru a trece la următoarea fereastră din ChartWizard – Step 2 of 4.

Această casetă de dialog are două butoane în partea de sus a casetei. Se selectează butonul Data range. Pe ecran apare următoarea casetă de dialog (figura 7.14):

Page 73: EXCEL - SUPORT DE CURS

Figura 7.14 - ChartWizard – Step 2 of 4

În caseta Data range este afişat domeniul de celule din care se va genera graficul. Dacă nu apare domeniul corect, se poate introduce alt domeniu (cu ˆ în faţă).Din lista de opţiuni Series in se selectează Rows dacă seriile de date se vor genera pe linii şi Columns dacă seriile de date se vor genera pe coloane.Pentru exemplul nostru în Data range trebuie să avem domeniul ş A1:F4.Vom genera seriile pe linie, deci se selectează opţiunea Rows.Se selectează butonul Series. Pe ecran apare următoarea casetă de dialog (figura 7.15):

Page 74: EXCEL - SUPORT DE CURS

Figura 7.15 – Step 2 of 4 – Chart Source Data

În lista Series sunt afişate seriile de date ale graficului. Dacă se selectează o serie în caseta text Name apare sau numele seriei, sau celula care conţine numele seriei, iar în caseta Values este afişat domeniul de valori din care se generează seria.Pentru exemplul nostru, dacă selectăm seria caiete, în caseta Name va apare = Sheet1!$A$3 (A3 este celula care conţine numele seriei), iar în caseta Values va apare = Sheet1!$B$3:$F$3 (setul de valori din care se generează seria).

În caseta text Category(x) axis labels se introduce domeniul care conţine informaţiile ce vor fi afişate pe axa x (a categoriilor).În cazul nostru în această casetă apare = Sheet1!$B$1:$F$1. Aceste celule conţin zilele săptămânii ce vor fi afişate pe axa x.

O serie poate fi ştearsă dacă atunci când este selectată se apasă pe butonul Remove.Pentru a adăuga o serie nouă se apasă pe butonul Add; în caseta Name se scrie numele seriei, iar în caseta Values valorile care generează seria.

În casetele Name, Values şi Category(x) axis labels celulele sau domeniile trebuie să aibă specificate în faţă şi numele foii de calcul (numele foii de calcul este separat de referinţele celulelor prin !).Se apasă pe butonul Next.

Pe ecran apare caseta de dialog Step 3 of 4 - Chart Options.Această casetă de dialog are în partea de sus mai multe butoane din care pot fi setate diferite caracteristici ale graficului.

Page 75: EXCEL - SUPORT DE CURS

Butonul AxesCând este apăsat acest buton apare următoarea casetă de dialog (figura 7.16):

Figura 7.16

Dacă este selectată opţiunea Category(x) axis în grafic vor fi afişate informaţiile de pe axa X, în caz contrar pe axa X nu se va afişa nimic.

Dacă este selectată opţiunea Value(y) axis în grafic vor fi afişate informaţiile de pe axa Y, în caz contrar pe axa Y nu se va afişa nimic.

Butonul TitlesCând este apăsat acest buton pe ecran apare următoarea casetă de dialog (figura 7.17):

Page 76: EXCEL - SUPORT DE CURS

Figura 7.17

În caseta Chart title se introduce titlul graficului.În caseta Category(x) axis se introduce titlul axei x.În caseta Category(y) axis se introduce titlul axei y.În cazul în care graficul are două axe X sau Y, în următoarele două casete text se introduc titlurile asociate acestora.

Butonul GridlinesCând acest buton este apăsat, pe ecran apare următoarea casetă de dialog (figura 7.18):

Figura 7.18

În această casetă se specifică dacă sunt afişate sau nu liniile de reţea. Liniile de reţea sunt linii orizontale şi verticale dispuse pe întreaga suprafaţă a graficului. Ele sunt folosite pentru a citi mai uşor valorile de pe grafic. Există două tipuri de linii de reţea: Principale (Major Gridlines)-pentru delimitarea intervalelor principale de pe axe. Secundare (Minor Gridlines)-pentru a insera linii de reţea între intervalele principale

de pe axe.Dacă trebuie afişate liniile de reţea principale se vor marca casetele Major Gridlines,

dacă trebuie afişate liniile de reţea secundare se vor marca casetele Minor Gridlines.

Butonul LegendLa apăsarea acestui buton pe ecran apare următoarea casetă de dialog (figura 7.19):

Page 77: EXCEL - SUPORT DE CURS

Figura 7.19

Dacă opţiunea Show Legend este setată graficul va avea ataşată o legendă. Poziţia în grafic a legendei se stabileşte cu una din opţiunile din domeniul Placement.

Opţiune Locul de plasare a legendeiBottom În partea de jos a graficuluiCorner În colţul din dreapta-sus al graficuluiTop În partea de sus a graficuluiRight În partea dreaptă a graficuluiLeft În partea stângă a graficului

Page 78: EXCEL - SUPORT DE CURS

Butonul Data labelsLa apăsarea acestui buton pe ecran apare următoarea casetă de dialog (figura 7.20):

Figura 7.20

Folosind una din opţiunile din domeniul Data labels, barelor din grafic li se pot asocia nişte etichete. Aceste etichete pot conţine fie valorile punctelor de date din grafic, fie categoria pe care o reprezintă.

Butonul Data TableCând acest buton este apăsat, pe ecran apare următoarea casetă de dialog (figura 7.21):

Page 79: EXCEL - SUPORT DE CURS

Figura 21

Dacă opţiunea Show Data Table este setată sub grafic va fi afişat tabelul pe baza căruia este construit graficul.Se apasă pe butonul Next.Pe ecran apare caseta de dialog Step 4 of 4 - Chart Location (figura 7.22).

Figura 7.22 – caseta de dialog Step 4 of 4 - Chart Location

În această casetă de dialog se specifică unde va fi plasat graficul.Dacă este selectată opţiunea As new sheet - graficul se va crea într-o nouă foaie de calcul al cărei nume este specificat în caseta text alăturată.Dacă este selectată opţiunea As object in - se va crea în foaia de calcul specificată în caseta text alăturată.Se apasă pe butonul Finish.

Mutarea şi dimensionarea unui grafic

După crearea unui grafic, acesta poate fi mărit sau micşorat sau poate fi mutat în altă poziţie în foaia de calcul.Pentru a dimensiona un grafic:

Page 80: EXCEL - SUPORT DE CURS

1. Se selectează graficul aplicând un clic deasupra lui. În jurul graficului vor apare mici pătrate negre, numite puncte de selectare.

2. Se plasează cursorul mouse-ului deasupra unui punct de selectare. Cursorul îşi va modifica forma – va căpăta forma unei săgeţi cu două vârfuri.

3. Se aplică un clic şi se trage de acest cursor pentru a mări sau micşora graficul. Pentru a modifica dimensiunile graficului în mod proporţional se va ţine apăsată tasta Shift în timp ce se trage de unul din punctele de selectare din colţuri.

Pentru a muta un grafic în altă poziţie a foii de calcul:1. Se selectează graficul aplicând un clic deasupra lui. În jurul graficului trebuie să apară

punctele de selectare.2. Se aplică un clic în interiorul graficului şi se trage graficul în noua poziţie. În timpul

acestei operaţii cursorul ia forma unei cruci cu săgeţi.

Modificarea tipului de grafic

La crearea unui grafic se selectează un anumit tip de grafic. După finalizarea graficului, tipul acestuia poate fi modificat în modul următor:1. Se selectează graficul.2. Din bara de instrumente Chart se selectează butonul Chart Type.3. Din lista care apare se selectează tipul de grafic dorit.

Tipul de grafic se poate modifica şi cu ajutorul opţiunilor din meniu.1. Se selectează graficul.2. Se aplică un clic pe butonul din dreapta al mouse-ului, şi din meniul care apare se

selectează opţiunea Chart Type.Pe ecran apare următoarea casetă de dialog (figura 7.23),

Page 81: EXCEL - SUPORT DE CURS

Figura 7.23

asemănătoare cu caseta de dialog din care se stabileşte tipul graficului la crearea lui. Se selectează tipul de grafic dorit.3. Se selectează butonul OK.

Opţiuni pentru afişarea tridimensională

Pentru a modifica unghiurile, înălţimea şi perspectiva tipului de grafic se foloseşte comanda 3-D View care apare în meniul rapid afişat la aplicarea uniu clic pe butonul din dreapta al mouse-ului.Pe ecran apare următoarea casetă de dialog (figura 7.24):

Figura 7.24 – caseta de dialog 3-D View

Butoanele pentru înălţime (Elevation) controlează nivelul relativ la care este văzută diagrama. Pentru a modifica valoarea existentă se pot folosi cele două butoane sau se poate introduce o nouă valoare în caseta Elevation.

Butoanele pentru rotaţie controlează unghiul de afişare al diagramei în jurul unei axe verticale. O altă probabilitate de modificare a valorii existente este introducerea noului unghi în caseta Rotation.

Dacă opţiunea Right Angle Axis nu este selectată în caseta de dialog apar şi butoanele pentru perspectivă. Aceste butoane sunt folosite pentru a stabili profunzimea în care este prezentat graficul. În acelaşi scop este folosită şi valoarea introdusă în caseta Perspective.

Dacă opţiunea Auto Scaling este selectată cele două dimensiuni ale graficului (baza şi înălţimea) se vor stabili automat de către Excel.

Dacă opţiunea Auto Scaling nu este selectată se activează caseta text Height în care se specifică raportul înălţime/ bază în procente.Se selectează butonul OK.

Modificarea caracteristicilor unui grafic

Aproape orice parte a unui grafic Excel poate fi formatată. Această flexibilitate oferă un control total asupra aspectului graficului creat. Se pot modifica culorile, haşurile, fonturile diferitelor elemente dintr-un grafic. Pentru a putea fi formatat, orice element din grafic trebuie întâi selectat. Selectarea se face foarte uşor cu ajutorul mouse-ului aplicând un clic peste elementul respectiv, după ce graficul a fost selectat. După selectarea

Page 82: EXCEL - SUPORT DE CURS

elementului dorit se apasă butonul din dreapta al mouse-ului şi se aplică comanda Format + numele elementului selectat. În funcţie de obiectul selectat pe ecran apare o casetă de dialog care, în general, în partea superioară are mai multe butoane. Din aceste casete de dialog se selectează caracteristicile graficului.

Aplicaţie – grafice tip plăcintă

Foaia de calcul următoare (figura 7.25) conţine cheltuielile pe care le face o firmă pentru a-şi face reclamă.

Figura 7.25

Să se reprezinte într-un grafic structura acestor cheltuieli.Cel mai potrivit tip de grafic pentru a reprezenta structura acestor cheltuieli este

graficul Pie.Să calculăm în coloana C cât la sută din valoarea totală reprezintă fiecare tip de

cheltuială. În celula C4 vom introduce formula =B4/B$8 (cheltuielile/cheltuielile totale) şi copiem formula în domeniul C5:C7. Pentru ca numerele să fie afişate sub formă de procent, le selectăm şi le formatăm de tip procent.Pentru construirea graficului:1. Se selectează zona A4:B7.2. Se apasă pe butonul Chart Wizard.3. Se alege un grafic de tip Pie. Se selectează un subtip de grafic care să afişeze şi

procentele.4. Avem o singură serie de date în domeniul B4:B7. Deci seria se va genera pe

coloană şi domeniul de valori este B4:B7.Din celulele specificate în Category(x) axis labels se vor extrage numele sectoarelor. Deci în cazul nostru completăm cu A4:A7.

Se obţine următorul grafic (figura 7.26):

Page 83: EXCEL - SUPORT DE CURS

Figura 7.26

După construirea graficului comparăm procentele din coloana C cu procentele din dreptul sectoarelor. Constatăm că sunt exact aceleaşi valori.Deci fiecare sector reprezintă un procent dintr-un întreg.

Aplicaţie – grafice de tip linie

Foaia de calcul următoare (figura 7.27) conţine valoarea vânzărilor efectuate în perioada 1994-1998 de o firmă distribuitoare de produse soft.

Figura 7.27

Pentru a ilustra tedinţele de variaţie în timp se va folosi un grafic de tip linie (nu XY pentru că intervalele de timp sunt egale).Pentru a crea graficul:1. Se selectează domeniul A3:F62. Se selectează butonul Chart Wizard.3. Se selectează un grafic de tip Line.4. În grafic vom avea trei serii de date, pentru vânzările de Excel, a doua pentru

vânzările de Word, a treia pentru Power Point.Deci seriile se vor genera pe linie.Avem trei serii:1. Excel - în domeniul B4:F42. Word - în domeniul B5:F53. Power Point - în domeniul B6:F6Pe axa x trebuie afişaţi anii. Deci în Category(x) axis labels completăm = B3:F3.Se va obţine următorul grafic (figura 7.28):

Page 84: EXCEL - SUPORT DE CURS

Figura 7.28

Aplicaţie – grafice Stacked Line

Foaia de calcul următoare (figura 7.29) conţine numărul de calculatoare asamblate într-o fabrică în trei intrvale de timp: de la ora 8 la ora 16 şi de la ora 16 la ora 24, de luni până vineri.

Figura 7.29

Să se reprezinte într-un grafic numărul total de calculatoare asamblate la sfârşitul fiecărei perioade.La sfârşitul perioadei 8-16 numărul total de calculatoare asamblate este egal cu suma dintre numărul de calculatoare asamblate în intervalul 0-8 cu cele asamblate în intervalul 8-16, iar la sfârşitul zilei trebuie însumate calculatoarele asamblate în cele trei perioade.Cel mai potrivit tip de grafic este Stacked Line. La acest tip de grafic linia pentru o serie se generează adunând şi valorile din seriile precedente.Pentru a crea graficul:1. Se selectează domeniul A1:F42. Se selectează butonul Chart Wizard.3. Selectăm un grafic de tip linie şi ca subtip un grafic Stacked Line.4. În grafic avem trei serii de date:

0 - 800 în B2:F2800 – 1600 în B3:F31600 - 2400 în B4:F4

Seriile sunt generate pe linie.Pe axa x vor fi afişate zilele săptămânii, deci în Category(x) axis labels vom introduce domeniul = B1:F1.Se va obţine următorul grafic (figura 7.30).

Figura 7.30

Page 85: EXCEL - SUPORT DE CURS

Aplicaţie –grafice de tip xy dispersate (Scater)

Foaia de calcul următoare (figura 7.31) conţine nivelul stocurilor pentru articolele realizate de o fabrică de încălţăminte pe durata unei linii.

Figura 7.31

Să se reprezinte sub formă grafică nivelul stocurilor pentru cele trei articole.Deoarece datele sunt distribuite inegal trebuie aleasă o diagramă xy şi nu una de tip linie.Pentru a crea graficul:1. Se selectează domeniul A1:I42. Se selectează un grafic de tip xy3. Seriile de date se vor genera pe linie.4. Vom avea 3 serii:

Sandale B2:I2Adidaşi B3:I3Pantofi B4:I4

Pe axa x vor fi afişate zilele, deci în Category(x)Axis Labels vom avea = B2:I1Se va obţine următorul grafic (figura 7.32):

Figura 7.32

Page 86: EXCEL - SUPORT DE CURS

LECŢIA 8

LISTE

Page 87: EXCEL - SUPORT DE CURS
Page 88: EXCEL - SUPORT DE CURS

Crearea şi editarea unei liste

În Excel datele pot fi gestionate cu uşurinţă dacă sunt organizate sub formă de liste. O listă reprezintă o zonă dintr-o foaie de calcul care conţine date similare. Fiecare coloană din listă reprezintă o anumită categorie de date (numită câmp) şi determină tipul de informaţie necesar pentru fiecare intrare în listă. Fiecare linie dintr-o listă constituie o înregistrare.

Pentru a crea o listă se introduce un titlu pentru fiecare coloană. Listele se pot crea în orice zonă din foaia de calcul, trebuie doar ca zona de sub listă să nu conţină date, astfel încât lista să se poată întinde fără să interfereze cu alte date din foaia de calcul.

Pentru a introduce înregistrări în listă se vor introduce date în liniile imediat de sub titlurile coloanelor. Fiecare înregistrare trebuie să conţină aceleaşi câmpuri. În figura 8.1 este prezentată o listă care conţine informaţii despre comenzile livrate de o firmă distribuitoare de produse electrocasnice la firme din diverse oraşe din ţară. Pentru fiecare comandă sunt furnizate informaţii despre data de livrare a comenzii, produsul vândut, cui i- a fost livrată comanda, numărul de bucăţi livrate şi valoarea comenzii.

Figura 8.1

Pentru a uşura introducerea şi editarea înregistrărilor dintr-o listă se pot utiliza facilităţile oferite de formulare. Un formular reprezintă o imagine organizată a datelor care cuprinde denumirile coloanelor, casete text pentru introducerea datelor şi butoane pentru adăugarea, ştergerea şi găsirea înregistrărilor.

Pentru a adăuga înregistrări cu ajutorul formularului de date:1. Se poziţionează cursorul în orice celulă din listă.2. Din meniul Data se selectează comanda Form. Pe ecran apare caseta de dialog

Data Form (figura 8.2).3. Pentru a adăuga o nouă înregistrare în listă se aplică un clic pe butonul New. Pe

ecran apare un formular gol în care se completează fiecare casetă text cu informaţiile corespunzătoare. Pentru deplasare în următoarea casetă se apasă tasta Tab, pentru deplasare în caseta text anterioară se apasă ShiftTab. După terminarea introducerii datelor se apasă tasta Enter pentru adăugarea de

Page 89: EXCEL - SUPORT DE CURS

înregistrări noi în listă. În cazul în care în anumite câmpuri au fost scrise informaţii şi se constată că înregistrarea respectivă nu trebuie adăugată în listă se aplică un clic pe butonul Restore pentru a elimina înregistrarea din formular. Clicul pe Restore trebuie aplicat înainte de a apăsa tasta Enter prin care se salvează înregistrarea.

4. Se aplică un clic pe butonul Close pentru a reveni în foaia de calcul.

Figura 8.2

Formularul de date poate fi folosit şi pentru vizualizarea înregistrărilor din listă. Există mai multe metode pentru a realiza acest lucru. O metodă ar fi folosirea barei de derulare din caseta de dialog Data Form. Se apasă pe săgeata în sus sau săgeata în jos pentru a vizualiza înregistrarea anterioară sau înregistrarea următoare. Pe măsură ce sunt vizualizate înregistrările din listă în colţul din dreapta sus al casetei de este indicat numarul curent al înregistrării. O altă metodă ar fi utilizarea butoanelor Find Next pentru a vizualiza următoarea înregistrare şi Find Previous pentru a vedea înregistrarea anterioară. Dacă la apăsarea acestor butoane Excel emite un semnal sonor înseamnă că sunteţi poziţionaţi pe ultima, respectiv prima înregistrare din listă.

Pentru a şterge o înregistrare cu ajutorul formularului de date:1. Se poziţionează cursorul în orice celulă din listă.2. Din meniul Data se selectează comanda Form. Pe ecran apare caseta de dialog

Data Form.3. Se vizualizează înregistrarea care trebuie ştearsă.4. Se aplică un clic pe butonul OK pentru a efectua ştergerea. Excel va solicita

confirmarea pentru ştergerea înregistrării. Se va răspunde cu OK pentru a şterge înregistrarea sau cu Cancel pentru a anula operaţia de ştergere.

5. Se aplică un clic pe butonul Close pentru revenirea în foaia de calcul.

Page 90: EXCEL - SUPORT DE CURS

Formularul de date poate fi folosit şi pentru a găsi înregistrările care satisfac unul sau mai multe criterii. Dacă se foloseşte formularul în acest scop nu se poate vizualiza decât câte o înregistrare găsită.Paşii pentru a găsi o înregistrare sunt:

1. Se selectează o celulă din listă.2. Din meniul Data se selectează comanda Form.3. Din caseta de dialog Data Form se selectează butonul Criteria.4. Se introduc criteriile pentru efectuarea căutării (figura 8.3). De exemplu, dacă

vrem să vizualizăm comenzile cu o valoare mai mare de 40000000 în câmpul Valoare Comandă introducem criteriul 40000000.

Figura 8.3

5. Se aplică un clic pe butonul Find Next sau se apasă pe săgeata în jos din bara de derulare a formularului. În formular va fi afişat rezultatul căutării.

Dacă nu există nici o înregistrare corespunzătoare se va auzi un semnal sonor.Dacă trebuie făcută căutarea în sens invers se aplică un clic pe butonul Find Prev

sau se apasă săgeata în sus din bara de derulare a formularului. Dacă nu există nici o înregistrare corespunzătoare se va auzi semnalul sonor.

Sortarea datelor din liste

Informaţiile organizate într-o listă pot fi sortate, filtrate sau se pot genera extrase pe totaluri parţiale.

Sortarea este operaţia de ordonare a înregistrărilor în funcţie de unul sau mai multe criterii. Excel sortează listele pe baza câmpurilor.Pentru a ordona o listă se efectuează următorii paşi:

1. Se selectează o celulă din listă. Dacă trebuie ordonate doar anumite înregistrări din listă acestea se selectează

2. Se aplică comanda Data, Sort. Pe ecran apare caseta de dialog Sort (figura 8.4).

Page 91: EXCEL - SUPORT DE CURS

3. Pentru a evita sortarea capului de tabel împreună cu restul listei, din secţiunea My List Has se selectează opţiunea Header Row.

4. În caseta Sort By se selectează numele coloanei în funcţie de care se va face sortarea. Dacă lista nu conţine nume de coloane, şi deci la pasul 3 a fost selectată opţiunea My List Has No Header Row, în loc de numele coloanei se va selecta litera coloanei din foaia de calcul.

5. Se selectează ordinea de sortare: Ascending pentru sortare în ordine crescătoare şi Descending pentru sortare în ordine descrescătoare.

6. Dacă este necesară o sortare pe două nivele (în cazul în care există duplicate în primul câmp de sortare) se completează a doua casetă Then By. Din lista derulantă se va selecta numele coloanei ce va fi folosită ca al doilea câmp de sortare. Se selectează ordinea de sortare pentru al doilea câmp.

7. Pentru a crea un al treilea nivel de sortare, în cazul în care există duplicate în primele două câmpuri de sortare se completează în mod similar a treia casetă Then By cu al treilea câmp de sortare şi se selectează ordinea de sortare şi pentru acest câmp.

8. Se selectează butonul OK.

Figura 8.4 – caseta de dialog Sort

O listă se poate sorta mai rapid cu ajutorul a două butoane din bara de instrumente standard: A

Z, pentru sortare în ordine crescătoare şi ZA, pentru sortare în ordine

descrescătoare.Pentru a sorta o listă cu butoanele de sortare:1. Se selecteză o celulă din coloana ce va fi folosită drept cheie de sortare.2. Se execută un clic pe unul din cele două butoane de sortare.

Butoanele de sortare efectuează această operaţie doar după un câmp cheie - câmpul selectat înainte de apăsarea butonului.Pentru sortare, Excel foloseşte următoarele reguli:

Datele sunt sortate în ordine ascendentă de la A la Z, de sus în jos pentru linii, sau de la stânga la dreapta pentru coloane. Opţiunea Descending inversează această ordine, de la Z la A, de sus în jos sau de la stânga la dreapta.

Spaţiile libere sunt puse la sfârşit.

Page 92: EXCEL - SUPORT DE CURS

Este folosită următoarea ordine de priorităţi:1. numerele de la cel mai mic număr negativ la cel mai mare număr pozitiv2. texte (de la A la Z)3. rezultatele FALSE4. rezultatele TRUE5. valori de eroare6. spaţii libere

Excel poate ignora diferenţele dintre literele mari şi mici sau poate ţine cont de acestea . Acest lucru se poate specifica dacă din caseta de dialog Sort se selecteză butonul Options. Pe ecran apare caseta de dialog Sort Options (figura 8.5):

Dacă este marcată opţiunea Case Sensitive se face distincţie între literele mici şi cele mari, dacă nu este marcată nu se face distincţie. Tot în această casetă de dialog se specifică în ce ordine se va face sortarea: - de jos în sus (Sort Top To Bottom), sau de la stânga la dreapta (Sort Left To Right).

Figura 8.5 - caseta de dialog Sort Options

În unele cazuri este posibil să fie necesară o sortare în care informaţiile nu trebuie să apară în ordine alfabetică normală. În acest caz din caseta de dialog Sort Options se selectează din lista derulantă First Key Sort Order modul în care se va face sortarea după prima cheie.

Aplicaţie

În foaia de calcul din lecţia precedentă să se grupeze comenzile în funcţie de oraşul în care au fost livrate:Rezolvare:1. Se poziţionează cursorul în orice celulă din listă.2. Se selectează comanda Sort din meniul Data.3. În secţiunea My List Has se selectează opţiunea Header Row (avem cap de tabel).4. În caseta Sort By se selectează Oraş - numele coloanei după care se face sortarea.5. Se selectează opţiunea Ascending - vom avea o sortare în ordine crescătoare după

numele oraşelor.6. Se selectează butonul OK.

Page 93: EXCEL - SUPORT DE CURS

În cazul în care trebuie să facem o sortare cu două niveluri de sortare, de exemplu după oraş şi după produs: se repetă paşii 1 - 5, prezentaţi mai sus (până acum datele sunt sortate doar după un

nivel de sortare)6. În a doua casetă Then By se selectează Produs - numele coloanei pentru al doilea

criteriu de sortare. Astfel se specifică ordinea şi pentru al doilea nivel de sortare.7. Se selectează butonul OK.

Sortarea după mai mult de trei câmpuri

Cu toate că în caseta de dialog Sort se pot specifica doar trei nivele de sortare, sortarea se poate face după oricâte câmpuri. Se poate face o resortare după câmpuri suplimentare oricât de des este necesar, fără a pierde rezultatul ordonării sortărilor precedente. Regula de sortare după mai mult de trei chei este de a sorta întâi după nivelurile inferioare mergând în sus către nivelul superior.

De exemplu, să presupunem că avem de făcut o sortare pe 6 nivele în care coloana A este folosită ca primă cheie de sortare, B ca a doua, C ca a treia, D ca a patra, E ca a cincea şi F a şasea cheie. Deşi Excel permite specificarea doar a trei chei de sortare se pot sorta toate cele şase coloane.

Se sortează mai întâi coloanele de nivel inferior: D, E ,F. Coloana D va fi prima cheie de sortare, E a doua, F a treia. O a doua sortare efectuează operaţia după coloanele de nivel superior A, B, C. Coloana A va fi prima cheie de sortare, B a doua şi C a treia.

Page 94: EXCEL - SUPORT DE CURS

LECŢIA 9

PRELUCRAREA DATELOR CU AJUTORUL SUBTOTALURILOR

Page 95: EXCEL - SUPORT DE CURS
Page 96: EXCEL - SUPORT DE CURS

Crearea subtotalurilor automate

Când sortează datele dintr-o listă Excel permite efectuarea unor sinteze a datelor pe baza totalurilor parţiale. Când se efectuează calcule parţiale, acestea sunt calculate pentru fiecare grup din listă. Un grup este alcătuit din mai multe linii (înregistrări) care au aceeaşi informaţie într-o coloană (câmp) specificată.

Pentru a putea crea subtotaluri trebuie ca datele să fie sortate. După sortarea datelor după câmpuri, se parcurg următorii paşi:1. Se selectează orice celulă din listă.2. Se aplică comanda Data, Subtotals. Pe ecran apare caseta de dialog Subtotal (figura

9..1).

Figura 9..1 – caseta de dialog Subtotal

3. Se specifică modul în care vor fi grupate datele pentru subtotaluri selectând din lista derulantă At Each Change(la fiecare schimbare) numele coloanei după care se face gruparea.

Dacă este prima dată când este selectată comanda, Excel selectează automat coloana cea mai din stânga. Dacă comanda a fost folosită şi mai înainte, atunci va fi selectată coloana folosită ultima dată.

4. Din lista derulantă Use Function se selectează funcţia care trebuie calculată. Funcţiile cel mai frecvent folosite sunt:SUM – adunareMAX – maximumMIN – minimumAVERAGE – mediePRODUCT – produsVAR - varianţaSTD DEVP - deviaţia standard;

5. Din lista Add Subtotal To se selectează datele cu care se vor efectua calculele. Această listă conţine numele coloanelor din lista de date. Se marchează coloanele pentru care se vor efectua calculele.

Page 97: EXCEL - SUPORT DE CURS

6. Pentru a înlocui totaluri existente se marchează opţiunea Replace Current Subtotals.

7. Pentru a insera un salt de pagină înaintea fiecărui grup se marchează opţiunea Page Break Between Groups.

8. În mod implicit subtotalurile şi totalurile generale apar la sfârşitul grupului de date (opţiunea Summary Below Data se marchează automat). Dacă ele trebuie afişate înaintea grupului de date se va demarca opţiunea Summary Below Data.

9. Se selectează butonul OK.

Pentru eliminarea subtotalurilor din caseta de dialog Subtotal se selectează butonul Remove All.

Aplicaţie

La datele din aplicaţia din lecţia 8 să se calculeze valoarea totală a comenzilor livrate în fiecare oraş.1. Se sortează comenzile după oraş.2. Se selectează o celulă din listă.3. Din meniul Data se aplică comanda Subtotals.4. Caseta de dialog Subtotal se va completa în modul următor (figura 9..2):

Din lista At Each Change se selctează Oraş. Se va genera câte un grup de înregistări pentru fiecare oraş. Din lista Use Function se va selecta funcţia SUM, iar în lista Add Subtotal To se va marca coloana Valoare comandă. Deci pentru fiecare grup se va calcula suma valorilor din coloana Valoare comandă.Se marchează opţiunile Replace Current Subtotals şi Summary Below Data.

5. Se selectează butonul OK.

Figura 9..2

Page 98: EXCEL - SUPORT DE CURS

Excel inserează rândurile de subtotal pentru fiecare grup şi realizează calculul specificat în coloanele alese. Excel etichetează fiecare rând inserat cu un titlu potrivit. Se inserează de asemenea un rând de total general.

Când se adaugă totaluri parţiale calculate automat într-o listă Excel afişează lista în modul Outline. Nivelul detaliilor din listă poate fi extins sau restrâns pentru a nu fi afişate decât totalurile parţiale şi totalurile generale pentru datele respective. În partea stângă a ferestrei (figura 9..3) apar câteva butoane care permit ascunderea şi afişarea rapidă a datelor de detaliu:

(-) Hide Detail - pentru a condensa sintetizarea datelor - apare în dreptul fiecărui subtotal calculat

(Ş) Display Detail - pentru a extinde sintetizarea datelor - apare în dreptul fiecărui subtotal calculat

1, 2, 3, ... - butoane de ierarhizare care indică modul cum sunt grupate datele.În exemplul prezentat, deoarece s-a efectuat o grupare pe un nivel, sunt afişate

doar trei butoane: 1, 2 şi 3. Dacă se apasă pe butonul 1 este afişat doar totalul general (figura 9.4), dacă se apasă pe butoanul 2 sunt afişate totalurile parţiale, iar dacă se apasa butonul 3 este vizualizată toată lista.

Pentru a ascunde un nivel de detaliere se selectează o celulă care conţine un total parţial şi se aplică un clic pe butonul Hide Detail Level (-) de pe nivelul 2. Excel va reduce dimensiunile listei astfel încât este afişat doar rândul care conţine totalul parţial corespunzător. Dacă se aplică un clic pe butonul Display Detail (+) vor fi afişate toate înregistrările pe baza cărora s-a calculat subtotalul corespunzător.

Butoanele Hide Detail (-) şi Display Detail (+) de pe nivelul 1 sunt folosite pentru a condensa sau extinde datele care generează totalul general.

Figura 9..3

Page 99: EXCEL - SUPORT DE CURS

Figura 9..4

Crearea de subtotaluri ierarhizate

Dacă în cadrul unui grup trebuie calculate subtotaluri suplimentare se vor calcula subtotaluri ierarhizate.

Pentru a crea un subtotal ierarhizat datele trebuie sortate şi după a doua cheie. Apoi se aplică comanda Data, Subtotals. Se vor selecta opţiunile pentru grupul principal (cel mai mare). Excel inserează subtotalurile pentru primul grup.

Se alege în continuare Data, Subtotals şi opţiunile pentru următoarele subseturi de grupe. Opţiunea Replace Current Subtotals trebuie să fie demarcată. Excel inserează câte un subtotal pentru următoarele subseturi de grupuri.

Aplicaţie

Pe lângă subtotalurile pe oraşe şi totalul general se mai pot calcula şi subtotaluri pe produse. Pentru a realiza acest lucru:

1. Se face o sortare a listei pe două niveluri: nivelul 1 - sortare după oraş; nivelul 2 - sortare după produs.

2. Ca în aplicaţia precedentă se calculează subtotalurile pe oraş.

Page 100: EXCEL - SUPORT DE CURS

Figura 9..53. Se aplică din nou comanda Data, Subtotals. Caseta de dialog Subtotal se

completează în modul următor:Din lista At Each Change In se selectează câmpul Produs (se vor genera subgrupe pentru fiecare produs).Din lista Use Function se selectează funcţia SUM, iar în lista Add Subtotal To se marchează câmpul Valoare comandă (se calculează suma valorilor comenzilor pentru fiecare subgrup).Se demarchează opţiunea Replace Current Subtotals.

4. Se selectează butonul OK.Se obţine rezultatul din figura 9.5.

În colţul din stânga sus a ferestrei apar butoanele de ierarhizare 1, 2, 3, 4. Acum apar mai multe butoane de ierarhizare pentru că se calculează subtotalurile pentru mai multe nivele (produs şi oraş). Pentru a afişa doar totalul general se aplică un clic pe butonul de nivel 1. Dacă trebuie afişate doar totalurile vânzărilor efectuate în fiecare oraş şi totalul general se aplică un clic pe butonul de nivel 2. Dacă trebuie afişate subtotalurile pe produs şi oraş se aplică un clic pe butonul 3, iar dacă trebuie afişată toată lista se aplică un clic pe butonul 4.

Page 101: EXCEL - SUPORT DE CURS

LECŢIA 10

FILTRAREA DATELOR

Page 102: EXCEL - SUPORT DE CURS
Page 103: EXCEL - SUPORT DE CURS

Filtrarea datelor dintr-o listă

Filtrarea datelor dintr-o listă este o operaţie prin care sunt afişate doar acele înregistrări din listă care corespund unor criterii specificate şi ascunderea celorlalte înregistrări care nu mai sunt afişate. În Excel există două metode de filtrare: comanda Auto Filter pentru a efectua o filtrare rapidă a datelor din listă şi comanda Advenced Filter pentru a filtra pe baza unor criterii suplimentare.

Utilizarea comenzii Auto Filter

Comanda Auto Filter oferă o putere deosebită de gestionare a listelor. Informaţiile care nu trebuie vizualizate sau tipărite pot fi filtrate rapid doar aplicând clicuri. Liniile de date (înregistrările) care nu îndeplinesc criteriile specificate sunt ascunse. Din această cauză atunci când se face o filtrare numerele de linie apar pe sărite şi sunt afişate cu culoare albastră.Pentru a filtra o listă cu comanda Auto Filter se execută următorii paşi:1. Se selectează o celulă din lista ce trebuie filtrată.2. Din meniul Data se aplică comenziile Filter, Auto Filter.

Excel va insera săgeţi de derulare în dreptul fiecărei celule din capul de tabel.3. Se aplică un clic pe săgeata de derulare din coloana în care se va introduce criteriul.

Se selectează criteriul pentru coloana respectivă. Sunt posibile următoarele opţiuni:All Permite afişarea tuturor înregistrărilor cu acest câmp.(Top 10) Permite selectarea unui subset de înregistrări pornind de sus sau de

jos într-o listă. Se poate specifica fie numărul de elemente, fie procentul de elemente ce vor fi filtrate. La selectarea acestei opţiuni apare caseta de dialog Top 10 Autofilter (figura 10.1).

Figura 10.1 – caseta de dialog Top 10 Autofilter

Din prima listă derulantă se selectează Top /Bottom, în funcţie de direcţia în care se va face filtrarea de sus (Top) sau de jos (Bottom). În a doua listă derulantă se introduce sau se selectează un număr care indică câte înregistrări (dacă în a treia listă derulantăeste selectată opţiunea Items) sau ce procent din numărul total de înregistrări (dacă în a treia listă derulantă este selectată opţiunea Percent) vor rezulta din filtrare.

Custom Permite crearea unor criterii definite cu condiţii şi/sau. La selectarea acestei opţiuni apare caseta de dialog Custom AutoFilter (figura 10.2).

Page 104: EXCEL - SUPORT DE CURS

Figura 10.2 – caseta de dialog Custom AutoFilter

Pentru a introduce criterii comparative se selectează un operator din prima listă derulantă apoi se introduce o valoare în caseta alăturată.

Dacă există un al doilea criteriu se selectează una din opţiunile AND (şi) sau OR (sau) şi se introduce în mod similar al doilea criteriu.

Blanks Afişează toate înregistrările cu spaţii în acest câmp.Non Blanks Afişează toate înregistrările care nu conţin spaţii în acest câmp

(înregistrări ce conţin date).În plus apare o listă cu toate valorile distincte din coloana curentă. La selectarea acestor valori vor fi afişate doar înregistrările care au exact această valoare în câmpul specificat. Numele coloanelor după care s-a făcut filtrarea apar cu albastru.

4. Se aplică paşii 1-3 pentru a filtra datele şi în funcţie de alte criterii. Pe măsură ce se selectează mai multe criterii ele sunt combinate şi cu criteriile anterioare. Pentru ca o înregistrare să fie afişată ea trebuie să îndeplinească criteriile pentru toate câmpurile.

Dacă trebuie anulat rezultatul filtrării după un anumit câmp se selectează săgeata de derulare pentru acel câmp şi se selectează opţiunea All. Pentru a afişa toate înregistrările şi a înlătura criteriile din toate câmpurile se aplică comanda Data, Filter, Show All. Atunci când comanda AutoFilter este activă în meniu, în dreptul comenzii apare un marcaj de validare. Pentru a dezactiva această facilitate se aplică din nou comanda Data, Filter, AutoFilter.

Aplicaţie

Foaia de calcul următoare (figura 10.3) conţine o listă cu facturile emise de o firmă. Pentru fiecare factură sunt specificate următoarele date:

codul facturii data emiterii facturii numele distribuitorului numele clientului produs vândut preţul unitar

Page 105: EXCEL - SUPORT DE CURS

cantitatea vândută valoarea totală (preţul unitar*cantitatea vândută).

Pentru a calcula valoarea totală se introduce în celula H2 formula şF2*G2. Această formulă se va copia pe coloană.

1. Se selectează o celulă din listă.

Figura 10.3

2. Din meniul Data se aplică comanda Filter, Autofilter. În dreptul fiecărei celule din capul de tabel se inserează o săgeată de derulare.

Prin filtrare se poate răspunde la întrebări de genul: Să se vizualizeze toate comenzile livrate de Popescu.

Pentru aceasta se aplică un clic pe săgeata de derulare din coloana Distribuitor şi se selectează din lista afişată Popescu.

Dacă trebuie vizualizate toate comenzile livrate de Popescu cu valoare mai mare de 500000 lei se mai face o filtrare după câmpul Total. Se aplică un clic pe săgeata de derulare din coloana Total şi se selectează opţiunea Custom. Din lista de operatori se selectează operatorul , iar în caseta alăturată se introduce valoarea 500000. Se selectează butonul OK.

Utilizarea comenzii Advanced Filter

O altă metodă de filtrare este folosirea comenzii Advanced Filter. Cu această comandă se pot efectua operaţii de filtrare bazate pe criterii complexe. Pentru folosirea comenzii trebuie creat un domeniu de criterii. Domeniul de criterii specifică condiţiile pe care datele filtrate trebuie să le îndeplinească. Prima linie din domeniul de criterii conţine numele câmpurilor pentru care se vor specifica criterii. Numele câmpurilor trebuie scrise exact la fel ca numele câmpurilor din listă. În liniile imediat următoare, sub numele câmpurilor, se introduc criteriile pentru câmpurile respective. Domeniul de criterii se va termina cu o linie goală. Criteriile care sunt pe aceeaşi linie în domeniul de criterii vor trebui să fie îndeplinite simultan. Între criteriile care se găsesc pe linii diferite aplică o relaţie de tip SAU.După pregătirea domeniului de criterii se execută următorii paşi:1. Se selectează domeniul de celule care conţine lista2. Se aplică comanda Data, Filter, Advanced Filter. Pe ecran apare caseta de dialog

Advanced Filter (figura 12.4):

Page 106: EXCEL - SUPORT DE CURS

3. Dacă lista filtrată va fi afişată în acelaşi loc ca şi lista iniţială se selectează opţiunea Filter the List, in place. Dacă datele trebuie plasate într-o altă zonă a foii de calcul pentru a fi prelucrate ulterior se selectează opţiunea Copy To Another Location. În acest caz în caseta Copy To se specifică domeniul de celule ce va conţine lista, dacă acesta nu a apărut automat.

4. În caseta List Range se introduce domeniul de celule care conţine lista, dacă acesta nu a apărut automat.

Figura 12.4 – caseta de dialog Advanced Filter

5. În caseta Criteria Range se introduce domeniul de celule care conţine domeniul de criterii.

6. Dacă prin filtrare trebuie înlăturate înregistrările duble se marchează opţiunea Unique Records Only.

7. Se selectează butonul OK.

Aplicaţie

În exemplul din aplicaţia precedentă să se realizeze o filtrare astfel încât să fie vizualizate doar comenzile în valoare de 500.000 de lei livrate de Popescu folosind comanda Advanced Filter.

Figura 12.5

La sfârşitul listei (figura 12.5) se lasă o linie liberă şi se introduce domeniul de criterii. Se vor introduce în foaia de calcul următoarele informaţii:

În A10 DistribuitorÎn B10 Val. TotalaÎn A11 PopescuÎn B11 500000

Page 107: EXCEL - SUPORT DE CURS

Rezolvare:1. Se selectează domeniul de celule care conţine lista A1:H8.2. Se aplică comanda Data, Filter, Advanced Filter.3. Caseta de dialog Advanced Filter se completează în modul următor:

se selectează opţiunea Filter the List, in place List Range A1:H8 Criteria Range A10:B11 se demarchează opţiunea Unique Records Only.

4. Se selectează butonul OK.

Page 108: EXCEL - SUPORT DE CURS

LECŢIA 11

COMPARAREA ALTERNATIVELOR ÎN VEDEREA LUĂRII DECIZIEI

OPTIME

Page 109: EXCEL - SUPORT DE CURS
Page 110: EXCEL - SUPORT DE CURS

Utilizarea facilităţii Goal Seek

Excel dispune de o serie de facilităţi pentru a putea răspunde la întrebări de genul “Ce se întâmplă dacă ?“. Presupunem că avem o foaie de calcul, cunoaştem răspunsul dorit, dar vrem să rezolvăm problema şi în sens invers, adică să găsim valoarea de intrare care conduce la un anumit răspuns. Pentru a putea rezolva probleme de acest tip se utilizează comanda Goal Seek.

Pentru a folosi comanda Goal Seek se formulează întâi problema, se introduc variabilele şi formulele în foaia de calcul. Celula cu rezultate trebuie să conţină neapărat o formulă iar formula respectivă trebuie să conţină referiri la alte celule din foaia de calcul, celule care conţin variabile de intrare.

Pentru găsirea valorii de intrare care să conducă la un anumit răspuns se vor parcurge următoarele etape:1. Se selectează celula rezultat, care trebuie să conţină o formulă şi în care vrem să

obţinem o anumită valoare.2. Se aplică comanda Tools, Goal Seek. Pe ecran apare caseta de dialog Goal Seek

(figura 11.1).

Figura 11.1 – caseta de dialog Goal Seek

3. Caseta Set Cell conţine celula selectată în etapa 1. Dacă s-a sărit peste etapa 1, se scrie în această casetă referinţa celulei rezultat. În caseta To value se introduce soluţia la care vreţi să ajungeţi. În caseta By changing Cell se scrie referinţa celulei de intrare. Această celulă trebuie să contribuie la valoarea formulei din celula rezultat, specificată în Set Cell.

4. Se selectează butonul OK.

Goal Seek înlocuieşte valoarea de intrare astfel încât soluţia să se apropie cât mai mult de soluţia cerută.

Aplicaţie – Goal Seek

O persoană depune o sumă la o bancă pe termen de o lună cu o rată a dobânzii de 50%. Să se calculeze, pentru un orizont de 12 luni suma din cont la începutul şi sfârşitul fiecărei luni. Să se calculeze valoarea din cont la sfârşitul perioadei pentru mai multe valori a sumei depuse. Să se determine ce sumă trebuie să fie depusă astfel încât la sfârşitul perioadei suma din cont să fie de 10000000 lei ?

Se va crea următoarea foaie de calcul (figura 11.2):

Page 111: EXCEL - SUPORT DE CURS

Figura 11.2

Suma la începutul lunii 1 este chiar suma depusă deci în B5 vom introduce formula =B1.

Suma la sfârşitul unei luni este suma de la începutul lunii la care se adaugă dobânda, deci formula din celula C5 va fi =B5+B5*B$2/12.

Suma la începutul lunii 2 este suma de la sfârşitul lunii 1, deci în B6 vom introduce formula =C5.

Se copiază pe coloană formulele din B6 şi B5. Suma de la sfârşitul perioadei este în celula C16. Valoarea din această celulă depinde în mod indirect de suma depusă din B1.

Dacă se modifică suma depusă automat se modifică şi valoarea din C16. De exemplu, pentru o sumă depusă de 3000000 se va obţine la sfârşitul perioadei o sumă de 4896282lei.

Să rezolvăm acum următoarea întrebare: Ce sumă trebuie depusă astfel încât la sfârşitul perioadei suma finală să fie de 10000000 lei ?.Rezolvare:1. Se selectează celula C16.2. Se aplică comanda Tools, Goal Seek3. Caseta Goal Seek se va completa în modul următor:

Set CellC16 Celula care conţine suma pe care vrem sã o obţinem

To Value 10000000 Suma pe care vrem sã o obţinem (suma depusã)By Changing Cell B1 Celula care variazã ca sã obţinem rezultatul

4. Se selectează butonul OK

Excel rezolvă problema în mod invers, suma care trebuie depusă fiind de 6127097 lei.

Calcularea tabelelor de răspunsuri

Page 112: EXCEL - SUPORT DE CURS

Facilităţile “What if“ - “Ce se întâmplă dacă“ oferite de Excel sunt foarte folositoare în afaceri. Foile de calcul pot da răspuns imediat la întrebări cum ar fi: “Ce s-ar întâmpla dacă s-ar reduce costurile cu 0,5% ?”, “Ce s-ar întâmpla dacă s-ar vinde mai mult cu 10% ?”, “Ce s-ar întâmpla dacă nu am primi împrumutul?”.

Atunci când se testează cât de mult afectează rezultatele o schimbare cât de mică a mărimilor de intrare, se realizează o analiză de senzitivitate. Pentru a realiza o analiză de senzitivitate pentru un domeniu mare de intrări se poate folosi comanda Table din meniul Data. Comanda poate fi utilizată în două moduri:1. Modificarea unei date de intrare pentru a vedea efectul produs asupra uneia sau mai

multor formule.2. Modificarea a două date de intrare pentru a vedea efectul produs asupra unei formule.

Pentru a înţelege mai bine ce face această comandă să considerăm următorul exemplu:Să se calculeze ratele lunare care trebuie plătite pentru a returna un credit, cu o anumită dobândă.Rezolvare:1. Primul pas este crearea foii de calcul (figura 11.3).

Figura 11.3

În celula B6 se introduce formula = -PMT(B2/12,B3*12,B1), formulă cu care se calculează ratele lunare ce trebuie plătite pentru a returna creditul care se găseşte în B1, pe durata specificată în B3 (B3*12 reprezintă numărul de luni), cu dobânda din celula B2 (B2/12 reprezintă dobânda lunară).2. Să facem o analiză de senzitivitate în care să analizăm cum sunt influenţate ratele

lunare de dobânzi.Se construieşte un tabel în care prima coloană sau prima linie conţine valorile care

trebuie testate. Pentru exemplul nostru în domeniul A9:A13 conţine ratele dobânzii care vor fi utilizate ca intrări în analiza de senzitivitate.3. În următoarele coloane (sau linii) din tabel, în celulele din capul de tabel se introduc

adresele formulelor care conţin răspunsul. În cazul nostru în celula B8 se introduce formula =B6 (B6 conţine formula pentru calculul ratelor lunare).

4. Se selectează celulele care conţin tabelul. Se aplică comanda Data, Table. Pe ecran apare caseta de dialog Table (figura 11.3).

Page 113: EXCEL - SUPORT DE CURS

5.

Figura 11.3 – caseta de dialog Table

6. Se introduce adresa celulei care conţine variabila de intrare în Row Input Cell (dacă valorile care sunt testate sunt desfăşurate pe linie ) sau Column Input Cell (dacă valorile care sunt testate sunt desfăşurate pe coloană). În cazul nostru, valorile testate sunt desfăşurate pe coloană (A9/A13), deci vom introduce în Column Input Cell B2 (adresa ratei dobânzii).

7. Se selectează butonul OK.

Rezultatul obţinut este un tabel care conţine ratele lunare corespunzătoare fiecărei dobânzi.

În continuare vom vedea cum poate fi folosită comanda Table pentru a vedea efectul produs asupra unei formule prin modificarea a două date de intrare.

Reluăm exemplul anterior numai că acum vom modifica două date de intrare: dobânda şi creditul. În foaia da calcul se va calcula rezultatul pentru mai multe combinaţii ale acestor valori.

Rezolvare:1. Ca şi în cazul precedent se creează foaia de calcul (figura 11.4).2. Se construieşte un tabel în care prima coloană şi prima linie conţin valorile celor două

variabile. Colţul din stânga sus trebuie să conţină o formulă sau o referinţă la o formulă

În exemplul nostru domeniul A9:A13 va conţine valorile dobânzilor, domeniul B8:F8

valorile creditului, iar celula A8 va conţine formula =B6 (o referinţă la o celulă care

conţine formula de calcul).

3. Se selectează tabelul şi se aplică comanda Table. În Row Input Cell se introduce adresa celulei care reprezintă variabila care are valorile desfăşurate pe linie, iar în Column Input Cell se introduce adresa celulei care reprezintă variabila care are desfăşurate valorile pe coloană. În cazul nostru: în Row Input Cell se introduce B1; în Column Input Cell se introduce B2.

4. Se selectează butonul OK.

Page 114: EXCEL - SUPORT DE CURS

Rezultatul este prezentat în tabelul din figura 11.4. Valorile din tabel reprezintă ratele lunare care trebuie plătite pentru diferite valori ale creditului şi diferite valori ale dobânzii.

Figura 11.4

Efectuarea de analize “What if” cu scenarii

Multe din analizele economice implică efectuarea de analize de tipul “Ce se întâmplă dacă?”. Pentru a răspunde la astfel de întrebări se modifică valorile din celulele care conţin datele iniţiale ale problemei. La schimbarea acestor valori se modifică şi rezultatele. Cu cât există mai multe scenarii, cu atât urmărirea diferenţelor dintre rezultatele acestora este mai dificilă. Excel oferă o facilitate care permite urmărirea acestor scenarii: “Scenario Manager” (managerul de scenarii).

Crearea unui scenariu

Un model cu scenarii trebuie să aibă un set de valori de intrare şi un set de valori rezultat (care se schimbă în funcţie de intrări).Pentru a crea un scenariu se vor efectua următorii paşi:1. Se aplică comanda Tools, Scenarios. Pe ecran apare caseta de dialog Scenario

Manager (figura 11.5).

Page 115: EXCEL - SUPORT DE CURS

Figura 11.5 - caseta de dialog Scenario Manager

2. Din caseta Scenario Manager se selectează butonul Add. Pe ecran apare caseta de dialog Add Scenario (figura 11.6).

Figura 11.6 – caseta de dialog Add Scenario

3. În caseta Scenario Name se specifică numele scenariului.În caseta Changing Cells se indică celulele sau domeniul de celule care vor fi modificate pentru fiecare scenariu.În caseta Comment se pot scrie informaţii suplimentare. Automat Excel introduce în această casetă numele utilizatorului şi data la care a fost creat scenariul.Pentru a evita efectuarea de modificări în celulele din foaia de calcul se selectează optiunea Prevent Changes din secţiunea Protection a casetei de dialog. Pentru a ascunde datele din celule se selectează opţiunea Hide.

Page 116: EXCEL - SUPORT DE CURS

4. Se aplică un clic pe butonul OK.Pe ecran apare caseta de dialog Scenario Values (figura 11.7), în care se introduc datele pentru fiecare celulă din scenariu.

Figura 11. 7 – caseta de dialog Scenario Values

După introducerea datelor se selectează butonul OK. Pe ecran apare caseta de dialog Scenario Manager. Denumirea noului scenariu creat apare în lista Scenarios. La selectarea unui scenariu din listă în câmpul Changing Cells vor fi afişate adresele celulelor din scenariu, iar în câmpul Comments comentariile introduse.

5. Pentru a vedea scenariul se selectează denumirea lui din listă şi se execută un clic pe butonul Show. Excel va afişa valorile din toate celulele din foaia de calcul. În cazul în care caseta de dialog acoperă o parte din date, se trage cu mouse-ul bara de titlu a casetei de dialog spre marginea ecranului.

6. Pentru a reveni în foaia de calcul se execută un clic pe butonul Close. Excel va afişa în foaia de calcul valorile stabilite în scenariu.

Este bine ca atunci când se lucrează cu scenarii fiecare celulă din scenariu să aibă un nume. Excel va folosi aceste nume în caseta de dialog Scenario Values şi în rapoartele pentru scenarii.

Figura 11. 8 – caseta de dialog Define Name

Pentru a atribui un nume unei celule se efectuează următorii paşi:1. Se selectează celula căreia trebuie să i se atribuie un nume.

Page 117: EXCEL - SUPORT DE CURS

2. Se aplică comanda Insert, Name, Define. Pe ecran apare caseta de dialog Define Name (figura 11.8).

3. Se scrie numele celulei în caseta Names.4. Se aplică un clic pe butonul OK.

Editarea şi ştergerea scenariilor

Un scenariu existent poate fi modificat sau poate fi şters. Pentru a şterge un scenariu se selectează numele acestuia din caseta de dialog

Scenario Manager şi se aplică un clic pe butonul Delete. Excel va elimina scenariul din lista cu scenarii.

Pentru a modifica un scenariu se selectează numele scenariului din caseta de dialog Scenario Manager şi se aplică un clic pe butonul Edit Scenario. Pe ecran apare caseta de dialog Edit Scenario (figura 11.9), asemănătoare cu caseta Add Scenario. Se efectuează toate modificările necesare şi se aplică un clic pe butonul OK. Pe ecran apare caseta de dialog Scenario Values în care se introduc noile valori.

Figura 11. 9 – caseta de dialog Edit Scenario

Sintetizarea scenariilor prin rapoarte

Pentru compararea rezultatelor din mai multe scenarii, Excel oferă două metode. La prima metodă se creează un raport simplu sub formă de tabel, în care sunt prezentate datele din celulele de intrare şi efectul lor asupra rezultatelor. La a doua metodă se generează un tabel pivot.

Page 118: EXCEL - SUPORT DE CURS

Crearea unui raport de sintetizare

Pentru a crea un raport de sintetizare se efectuează următorii paşi:1. Se aplică comanda Tools, Scenarios.2. Se aplică un clic pe butonul Summary. Pe ecran apare caseta de dialog Scenario

Summary (figura 11.10)

Figura 11. 10 – caseta de dialog Scenario Summary

3. Din zona Report Type se selectează opţiunea Scenario Summary. În caseta Result Cells se indică domeniul de celule rezultat (care conţin formulele bazate pe celule cu datele iniţiale).

4. Se aplică un clic pe butonul OK.

Excel va afişa o nouă foaie de calcul cu un tabel ce conţine pentru datele iniţiale şi rezultatele din fiecare scenariuscenariu.

Crearea unui raport de tip tabel pivot pentru scenarii

Tabelele pivot sunt tabele obţinute prin gruparea în diverse moduri a informaţiilor din rândurile şi coloanele unui tabel. Pentru a crea un tabel pivot plecând de la scenariile din foaia de calcul se vor efectua următorii paşi:

1. Se aplică comanda Tools, Scenarios.2. Se aplică un clic pe butonul Summary.3. Din caseta de dialog Scenario Summary se selectează opţiunea Scenario

Pivot Table. În caseta text Result Cells se indică domeniul de celule care conţine formulele bazate pe celulele cu datele iniţiale.

4. Se aplică un clic pe butonul OK.Excel va afişa o nouă foaie de calcul cu un tabel pivot ce conţine datele de pornire

şi rezultatele scenariului.

Aplicaţie - Elaborarea de alternative de buget

Pentru a echilibra un buget trebuie găsită cea mai bună modalitate de a repartiza departamentelor resursele disponibile. Atunci cănd prevederile iniţiale sunt depăşite

Page 119: EXCEL - SUPORT DE CURS

trebuie comparate strategiile de redistribuire. Cu facilităţile oferite de managerul de scenarii se pot modela diferite strategii pentru a analiza avantajele şi dezavantajele diferitelor moduri de abordare.

Se va crea următoarea foaie de calcul (figura 11.11):

Figura 11.11

În coloana Buget proiectat sunt introduse prevederile iniţiale ale bugetului.În coloana Buget repartizat sunt introduse bugetele repartizate fiecărui

departament. În coloana Diferenţă se va calcula diferenţa dintre bugetul repartizat şi bugetul

proiectat. În celula D2 se va introduce formula =B2-C2, care se va copia în domeniul D3:D5.

În coloana Procent se va calcula procentul cu care se depăşeşte bugetul proiectat. În celula E2 se va introduce formula =D2/C2. Această formulă se va copia în domeniul E3:E5.

În ultima linie din tabel se vor calcula totalurile: bugetul total repartizat, bugetul total proiectat, diferenţa totală şi procentul de depăşire total. Celulele din această linie vor conţine următoarele formule:

B6: Sum(B2:B5)C6: Sum(C2:C5)D6: B6-C6E6: D6/C6

Folosind facilitatea Goal Seek s-ar putea răspunde la întrebări de tipul: “Cât de mult ar putea să scadă bugetul repartizat pentru departamentul Desfacere astfel încât să se reducă depăşirea bugetului total?”.

Depăşirea bugetului total se găseşte în celula E6. Aici ar trebui să obţinem valoarea 0. Bugetul pentru departamentul Desfacere se găseşte în celula B5.Pentru rezolvarea problemei:1. Se aplică comanda Tools, Goal Seek.2. Caseta de dialog Goal Seek se completează în modul următor: Set Cell - E6, To

Value - 0, By Changing Cell - B5.3. Se selectează butonul OK.

După aplicarea comenzii celula B5 va conţine bugetul care trebuie repartizat departamentului Desfacere astfel încât bugetul total să nu fie depăşit.

Pentru a testa mai multe strategii de repartizare a bugetului se poate folosi managerul de Scenarii.Rezolvare:Se denumesc celulele B2:B5:1. Se selectează celula B2.2. Se aplică comanda Insert, Name, Define.3. În caseta Name din caseta de dialog Define Name se introduce Buget Marketing.

Page 120: EXCEL - SUPORT DE CURS

4. Se aplică un clic pe butonul OKFolosind aceeaşi metodă se vor denumi şi celulele B3:B5, B6:E6 în modul următor:B3 - Buget Aprovizionare,B4 - Buget Resurse Umane,B5 - Buget Desfacere.B6 – Total buget repartizatC6 – Total buget proiectatD6 – Diferenţa totalăE6 – Procent de depăşire total

Se vor crea mai multe scenarii care vor conţine diferite valori pentru bugetele repartizate pentru cele patru departamente:1. Se aplică comanda Tools, Scenarios.2. Din caseta de dialog Scenario Manager se selecatează butonul Add.3. În caseta Scenario Name se introduce numele scenariului: Estimări iniţiale. În caseta

Changing Cells se indică celulele care vor fi modificate: B2:B5. Se aplică un clic pe butonul OK.

4. În caseta de dialog Scenario Values se vor introduce datele specifice pentru fiecare scenariu.

B2: 956750000B3: 244120000B4: 370000000B5: 145188000

5. Se selectează butonul OK.

Folosind paşii 2-4 se vor mai crea următoarele scenarii:Valori specifice scenariilor

B2 B3 B4 B5Scenariu1 956750000 244120000 370000000 1252130000Scenariu2 956750000 23970000 320000000 1000000000

După crearea scenariilor, pentru compararea acestora se poate crea un raport.1. În caseta de dialog Scenario Manager se aplică un clic pe butonul Summary.2. În caseta de dialog Scenario Summary, se selectează opţiunea Scenario Summary,

iar în Result Cells se introduce domeniul B6:E6 (domeniul care conţine rezultatele).3. Se aplică un clic pe butonul OK.

Excel creează o nouă foaie de calcul cu un tabel în care sunt afişate pentru fiecare scenariu valorile de intrare şi valorile rezultat.

Page 121: EXCEL - SUPORT DE CURS

LECŢIA 12

UTILIZAREA SOLVER-ULUI

Page 122: EXCEL - SUPORT DE CURS
Page 123: EXCEL - SUPORT DE CURS

Solver - ul

Cu comada Goal Seek se poate analiza variaţia rezultatelor în funcţie de o singură celulă de pornire. Multe foi de calcul sunt prea complexe pentru comanda Goal Seek. La unele modele pot fi necesare intrări multiple sau se pot impune restricţii asupra unora dintre valorile de intrare sau asupra rezultatului. Analiza unui rezultat prin modificarea valorilor din una sau mai multe celule se poate face utilizând facilitatea Solver din Excel.

Pentru a putea fi folosită facilitatea Solver, ea trebuie mai întâi instalată. Pentru a realiza acest lucru se vor executa următorii paşi:1. Se aplică comanda Tools, Add-Ins. Pe ecran apare caseta Add-Ins (figura 12.1).

Figura 12.1 – caseta de dialog Add - Ins

2. Se marchează opţiunea Solver.3. Se selecteză butonul OK. Comanda Solver va aparea în meniul Tools.

Cu Solver-ul se pot rezolva tot felul de probleme de programare liniară şi neliniară: Probleme de maximizare a câştigurilor Probleme de transport Probleme de amestec Probleme de minimizare a costurilor

Aceste tipuri de probleme vor fi tratate în detaliu în partea a doua – Modelarea deciziilor utilizând foile de calcul.

Page 124: EXCEL - SUPORT DE CURS

În principiu problemele care pot fi rezolvate au un singur obiectiv, pentru unele variabile sunt specificate restricţii, iar variabilele de intrare influenţează direct sau indirect, atât restricţiile cât şi valorile care trebuie optimizate.

Pentru utilizarea Solver-ului se vor efectua următorii paşi:1. Se configurează foaia de calcul şi se stabileşte care sunt celulele care conţin

variabilele de intrare şi rezultatul.2. Se aplică comanda Tools, Solvers. Pe ecran apare caseta de dialog Solver

Parameters (figura 12.2).

Figura 12.2 – caseta de dialog Solver Parameters

În caseta text Set Target Cell se specifică celula care conţine formula cu rezultatul care trebuie analizat (funcţia obiectiv). În secţiunea Equal To se specifică ce se urmăreşte pentru funcţia obiectiv:

Dacă trebuie maximizată se selectează opţiunea Max. Dacă trebuie minimizată se selectează optiunea Min. Dacă funcţia obiectiv trebuie să atingă o anumită valoare se selectează opţiunea Value

of, iar în caseta text alăturată se introduce valoarea respectivă.În caseta text By Changing Cells se vor indica celulele sau domeniul de celule pe care Solver-ul le va modifica pentru a obţine valoarea optimă.Caseta Subject to the Constraints va conţine restricţiile problemei. Pentru a adăuga o restricţie se aplică un clic pe butonul Add. Pe ecran apare o nouă casetă de dialog, Add Constraint (figura 12.3) în care se pot introduce restricţiile problemei:

Figura 12.3 – caseta de dialog Add Constraint

Pentru a stabili o restricţie: În caseta text Cell Reference se va specifica celula care conţine formula pe care

se bazează restricţia.

Page 125: EXCEL - SUPORT DE CURS

Se aplică un clic pe săgeata de derulare pentru a vedea lista cu operatori pentru restricţii şi se selectează operatorul corespunzător.

În ultima casetă text se scrie valoarea restricţiei ce trebuie respectată. Se aplică un clic pe butonul Add pentru a introduce şi alte restricţii. Pentru a reveni în caseta de dialog Solver Parameters se aplică un clic pe butonul

OK. Restricţiile definite vor fi afişate în caseta Subject to the Constraints.4. Se aplică un clic pe butonul Solver. Solver-ul va începe efectuarea calculelor pentru

soluţiile optime. După ce găseşte o soluţie, pe ecran apare casetă de dialog Solver Results (figura 12.4).

Figura 12.4 – caseta de dialog Solver Results

Excel introduce soluţiile în foaia de calcul. Dacă se alege opţiunea Keep Solver Results Excel va păstra în foaia de calcul soluţia calculată.

Dacă se selectează opţiunea Restore Original Values, se va reveni la valorile iniţiale din foaia de calcul. Pentru sintetizarea rezultatelor găsite, Solver-ul permite generarea a trei tipuri de rapoarte:

Answer - în care sunt prezentate valorile iniţiale şi finale pentru celula rezultat şi celulele care conţin variabilele de intrare.

Sensitivity - în care este prezentată o analiză de senzitivitate a variabilelor de intrare (cum variază rezultatul la diferite modificări ale variabilelor de intrare).

Limits - se specifică între ce limite pot varia limitelele impuse în restricţii, astfel încât rezultatul final să nu se modifice.

Mai multe detalii despre aceste rapoarte sunt prezentate în partea a-II-a – Modelarea deciziilor utilizând foile de calcul.

Pentru a crea un raport se selectează denumirea lui din lista Reports din caseta Solver Results. Pentru a selecta mai multe rapoarte din listă se alege primul raport, se ţine apăsată tasta Ctrl şi se aplică un clic pe unul sau ambele rapoarte rămase. Se aplică un clic pe butonul OK. Excel va crea fiecare raport într-o foaie de calcul separată.

Modificarea configuraţiei Solver-ului

Utilizatorul poate specifica tehnica utilizată de programul Solver pentru găsirea răspunsurilor, precizia răspunsurilor şi perioada de lucru a programului Solver.

Pentru a realiza acest lucru din caseta de dialog Solver Parameters se selectează butonul Options. Pe ecran apare caseta de dialog Solver Options (figura 12.5). Folosind opţiunile din această casetă de dialog se poate stabili modulul în care va lucra Solver-ul.

Page 126: EXCEL - SUPORT DE CURS

În caseta Max Time se specifică timpul maxim (în secunde) pe care programul Solver poate să îl folosească pentru găsirea unei soluţii.

În caseta Iterations se specifică numărul maxim de iteraţii pe care le poate face Solver-ul.

În caseta Precision se specifică cât de apropiate trebuie să fie două încercări de soluţie, înainte de a declara găsită cea mai bună soluţie.

În caseta Tolerance se specifică (în procente) cât de aproape de cea mai bună soluţie trebuie să fie răspunsul, atunci când se lucrează cu probleme cu numere întregi. Stabilirea unei toleranţe mai mari poate mări considerabil viteza de calcul atunci când se lucrează cu probleme complexe cu numere întregi

Opţiunea Assume Linear Model configurează Solver-ul să utilizeze o metodă de programare liniară pentru găsirea soluţiei. Dacă foaia de calcul conţine o problemă neliniară apare un mesaj de avertisment.

Opţiunea Show Iterations Results permite afişarea soluţiilor intermediare. Continuarea se face apăsând pe butonul Continue, oprirea pe butonul Stop.

Opţiunile Tangent sau Quadratic sunt metode adiţionale folosite pentru găsirea soluţiei. Se recomandă utilizarea opţiunii Quadratic dacă foaia de calcul conţine formule complexe care sunt neliniare.

Opţiunile din secţiunea Derivatives permit specificarea metodei de derivare parţială folosite.

Opţiunile din secţiunea Search permit specificarea metodei de căutare folosită.

Figura 12.5 – caseta de dialog Solver Options

Aplicaţie

Page 127: EXCEL - SUPORT DE CURS

O companie are trei fabrici în localităţile A, B, C. Produsele realizate în aceste fabrici sunt distribuite din localităţile S şi P. Compania analizează posibilitatea de amplasare a unui nou depozit în localitatea R. Analizele efectuate au stabilit următoarele costuri de transport:

Costul Transportului în depozitele din:Fabrici S P RA 10 14 8B 12 10 12C 8 12 10

Capacităţile de producţie la fabricile din localităţile A, B, C sunt de 20, 30 şi 40 unităţi pe săptămână.

Se estimează că depozitul din R va absolvi 20 de unităţi pe săptămână iar cele din S şi P, 40 respectiv 30 de unităţi pe săptămână.

Să se determine modul de distribuţie a produselor la depozite astfel încât costurile de transport să fie minime.

Modelul va avea 9 variabile:x1 - numărul de produse transportate de la fabrica A la depozitul Sx2 - numărul de produse transportate de la fabrica A la depozitul Px3 - numărul de produse transportate de la fabrica A la depozitul Rx4 - numărul de produse transportate de la fabrica B la depozitul Sx5 - numărul de produse transportate de la fabrica B la depozitul Px6 - numărul de produse transportate de la fabrica B la depozitul Rx7 - numărul de produse transportate de la fabrica C la depozitul Sx8 - numărul de produse transportate de la fabrica C la depozitul Px9 - numărul de produse transportate de la fabrica C la depozitul R

Restricţiile modelului sunt:1. x1+x2+x3<=20 (Producţia din fabrica A sa nu depăşească capacitatea de productie)2. x4+x5+x6<=30 (Producţia din fabrica B sa nu depăşească capacitatea de productie)3. x7+x8+x9<=40 (Producţia din fabrica C sa nu depăşească capacitatea de productie) C)4. x1+x4+x7<=20 (numarul de produse transportate în depozitul A sã fie mai mare decât

cererea la depozitul respectiv)5. x2+x5+x8>=40 (numarul de produse transportate în depozitul B sã fie mai mare decât

cererea la depozitul respectiv )6. x5+x6+x9>=30 (numarul de produse transportate în depozitul C sã fie mai mare decât

cererea la depozitul respectiv )

Functia obiectiv a modelului este minimizarea cheltuielilor de transport. Cheltuielile de transport sunt egale cu 10x1+14x2+8x3+12x4+10x5+12x6+8x7+12x8+10x9

Pentru rezolvarea acestei probleme se va configura foaia de calcul în modul urmãtor (figura 12.6):

Page 128: EXCEL - SUPORT DE CURS

Figura 12.6

Celulele din meniul C3:E5 vor conţine variabilele modelului. La început aceste celule se vor completa cu date aleatoare, să presupunem că toate variabilele sunt egale cu 1. În urma rezolvării problemei în aceste celule se va obţine rezultatul.

În celulele B3, B4, B5 se va calcula producţia totală realizată în fiecare fabrică.În B3 se va introduce formula =SUM(C3:E3) şi se copiază formula în B4 şi B5.În celulele C6, D6, E6, se va calcula producţia depozitată în fiecare depozit. În

celula C6 se va introduce formula =SUM(C3:C5). Această formulă se copiază în D6 şi E6.

Celulele B10, B11, B12 vor conţine capacităţile de producţie la fabricile A, B, C.Celulele C13, D13, E13 vor conţine cererea la cele 3 depozite.În domeniul C10:E12 se vor introduce cheltuielile de transport pe unitatea de

produs de la fiecare fabrică la fiecare depozit.În celulele C15, D15, E15 se calculează cheltuielile de transport la fiecare depozit.

În celula C15 se introduce formula =C3*C10+C4*C11+C5*C12 (Numărul de produse transportate de la fabrica A la depozitul S * cheltuielile de transport + numărul de produse transportate de la fabrica B la depozitul S * cu cheltuielile de transport + numărul de produse transportate de la fabrica C la depozitul S * cheltuielile de transport.). Această formulă se copiază în celulele d15 şi E15.

În celula B15 se vor calcula cheltuielile cu transportul. În această celulă se va introduce formula = SUM( C15:E15).Restricţiile problemei pentru foaia de calcul proiectată vor fi:1. B3<=B10 Producţia din fabrica A să nu depăşească capacitatea de producţie.2. B4<=B11 Producţia din fabrica B să nu depăşească capacitatea de producţie 3. B5<=B12 Producţia din fabrica să nu depăşească capacitatea de producţie 4. C6<=C13 să nu existe rupere de stoc la depozitul S5. D6<=D13 să nu existe rupere de stoc la depozitul P6. E6<=E13 să nu existe rupere de stoc la depozitul R7. C3:E5<=0 Toate variabilele să fie numere negative

După ce foaia de calcul a fost configurată din meniul Tools se aplică comanda Solver.Obiectivul problemei este minimizarea cheltuielilor totale de transport. Această

valoare este calculată în celula B15, deci în Set Target Cell se introduce B15. Funcţia trebuie minimizată deci se va alege opţiunea Min.

Page 129: EXCEL - SUPORT DE CURS

Celulele care conţin variabilele de intrare sunt în domeniu C3:E5, deci în By Changing Cells se introduce C3:E5.

În caseta Subject to the Constrains sevor introduce restricţiile problemei: Se selectează butonul Add. În caseta Cell Reference se introduce B3. Din lista cu operatori se selectează „ş. În caseta Constraint se introduce B10. Se selectează butonul Add. În mod similar se introduc şi celelalte restrricţii.În final caseta Solver va fi completată ca în figura 12.7

Figura 12.7

Se selectează butonul Options. În caseta de dialog Solver Options se marchează opţiunile Assume Linear Model şi Assume Non Negative. Se selectează butonul OK.

În acest moment se poate selecta în caseta de dialog Solver Parameters butonul Solve.

În caseta Solver Results se selectează opţiunea Keep Solver Solutions şi cele trei rapoarte. Se selectează butonul OK.

Solver-ul rezolvă problema. Rezultatul obţinut este:se vor transporta 20 de produse de la fabrica A la depozitul R, 30 de produse de la fabrica B la depozitul P, 20 de produse de la fabrica C la depozitul S, 10 de produse de la fabrica C la depozitul P 10 de produse de la fabrica C la depozitul R.

Page 130: EXCEL - SUPORT DE CURS

MODELAREA DECIZIILOR UTILIZÂND FOILE DE CALCUL

Page 131: EXCEL - SUPORT DE CURS
Page 132: EXCEL - SUPORT DE CURS

REZOLVAREA PROBLEMELOR DE PROGRAMARE LINIARĂ

Page 133: EXCEL - SUPORT DE CURS
Page 134: EXCEL - SUPORT DE CURS

Rezolvarea problemelor şi procesul de luare a deciziilor

În general, prin „problemă” se înţelege o dificultate care nu poate fi depăşită în mod automat. Procesul de rezolvare a unei probleme poate fi definit ca procesul de identificare a diferenţelor dintre starea actuală şi starea dorită a unei afaceri şi stabilirea acţiunilor necesare pentru a rezolva această diferenţă. Pentru probleme destul de complicate care să justifice timpul şi efortul unei analize amănunţite, procesul de rezolvare a unei probleme implică parcurgerea următorilor paşi:

1. Identificarea şi definirea problemei.2. Determinarea unui set de soluţii alternative.3. Determinarea unui criteriu sau a unor criterii pentru evaluarea alternativelor.4. Evaluarea alternativelor.5. Alegerea unei alternative.6. Implementarea alternativei alese.7. Evaluarea rezultatelor şi verificarea dacă a fost selectată o soluţie satisfăcătoare.Luarea deciziilor este un termen în general asociat cu primele cinci etape ale

procesului de rezolvare a unei probleme. Astfel, prima etapă în luarea unei decizii este identificarea şi definirea problemei, iar ultima etapă este alegerea unei alternative, care de fapt este actul de luare a deciziei (figura II.1.1).

Să considerăm următoarea situaţie. Un absolvent de facultate îşi caută un serviciu. Ca urmare a cererilor depuse, absolventul primeşte mai multe oferte situate în localităţi diferite: Bucureşti, Timişoara, Constanţa, Braşov. Alternativele pentru acest caz de luare a deciziei sunt:

1. Acceptarea postului din Bucureşti.2. Acceptarea postului din Timişoara.3. Acceptarea postului din Constanţa.4. Acceptarea postului din Braşov.Următorul pas al procesului de luare a deciziei este stabilirea criteriilor ce vor fi

folosite în evaluarea alternativelor. Problemele decizionale în care obiectivul este de a găsi cea mai bună soluţie în raport cu un singur criteriu se numesc decizii cu un singur obiectiv. Desigur, un criteriu important este salariul, dar pot exista şi alte criterii: posibilitatea de avansare, localitatea, posibilitatea de a avea o locuinţă. Problemele decizionale în care decizia este luată în funcţie de mai multe criterii se numesc decizii multicriteriale.

Următorul pas este evaluarea fiecărei altenative în raport cu fiecare criteriu. Unele criterii sunt uşor de evaluat (cum ar fi salariul), altele pot fi evaluate pe baza unor factori subiectivi (potenţialul de avansare, localitatea). În general, la factorii subiectivi, pentru fiecare variantă se acordă un calificativ sau o notă. De foarte multe ori criteriile sunt contradictorii. O alternativă bună prin aplicarea unui criteriu poate să nu fie la fel de bună prin aplicarea celorlalte criterii.

Pentru evaluarea primului tip de criterii se folosesc metodele cantitative, pentru cel de al doilea tip, metodele calitative.

În abordarea cantitativă analistul se va concentra asupra datelor asociate problemei şi va dezvolta un model matematic care va descrie obiectivele, restricţiile sau alte relaţii care există în problemă. Ulterior, prin utilizarea metodelor cantitative, analistul va face o alegere în funcţie de datele problemei.

Analiza calitativă se bazează mai mult pe intuiţie şi experienţă. Dacă managerul a avut experienţe similare, problema este relativ simplă. Dacă managerul nu are experienţă

Page 135: EXCEL - SUPORT DE CURS

în probleme similare sau problema este prea complexă pentru luarea deciziei finale se recomandă metodele cantitative.

Figura II.1.1 – Procesul de luare a deciziei

Dezvoltarea modelelor

Modelele sunt reprezentări ale unor obiecte sau situaţii reale. Ele pot exista în mai multe forme. De exemplu, o machetă a unui avion este o reprezentare a unui avion adevărat. Similar, un camion de jucărie este modelul unui camion adevărat. Aceste două exemple de modele sunt replici fizice ale obiectelor reale. Folosind terminologia adecvată ele sunt modele fizice sau modele iconice.

O altă categorie de modele include obiectele care există în formă fizică dar nu au acelaşi aspect ca şi obiectul modelat. Acestea sunt modelele analogice. Cutia de viteze a unui automobil este un model analogic: poziţia acului indică viteza automobilului. Un termometru este un alt model analogic pentru reprezentarea temperaturii.

A treia categorie include acele modele care reprezintă o problemă sub forma unui set de relaţii matematice. Aceste modele se numesc modele matematice. De exemplu, profitul total obţinut prin vânzarea unui produs poate fi calculat înmulţind profitul unitar cu cantitatea vândută. Dacă x reprezintă numărul de unităţi vândute, P profitul total,

Definirea problemei

Identificarea alternativel

or

Determinarea criteriilor

Evaluarea alternativel

or

Analiza cantitativă

Analiza calitativă

Luarea deciziei

Page 136: EXCEL - SUPORT DE CURS

atunci pentru un profit unitar de 1000 lei, modelul matematic care stabileşte profitul total în funcţie de vânzări este P=1000*x.

Scopul utilizării modelelor este realizarea unei interfeţe cu situaţia reală prin studierea şi analizarea modelului. De exemplu, un constructor de avioane poate testa un model fizic pentru a verifica caracteristicile de zbor ale unui avion adevărat. Similar, un model matematic poate fi utilizat pentru a analiza ce profit va fi obţinut dacă un produs este vândut. Pentru cazul prezentat, dacă vor fi vândute 30 de bucăţi (x=30), profitul obţinut va fi de 30*1000=30000 lei.

Utilizarea modelelor matematice reduce cheltuielile şi timpul necesar pentru rezolvarea unei probleme reale. O machetă de avion se construieşte mai repede şi este mai ieftină decât un avion real. La fel, prin utilizarea modelului matematic, se poate calcula rapid profitul ce poate fi obţinut, fără ca managerul să producă şi să vândă cele x unităţi.

Modelele au şi avantajul reducerii riscului asociat, prin experimentarea unei situaţii reale. Pentru exemplele prezentate se pot evita greşelile de proiectare, care ar putea duce la prăbuşirea avionului, sau se pot evita deciziile greşite care ar duce la pierderi de milioane de lei.

Concluziile obţinute depind de cât de bine reprezintă modelul situaţia reală. Cu cât modelul se apropie mai mult de cazul real, cu atât rezultatele vor fi mai precise.

În continuare vor fi analizate numai modelele matematice. Principalele aspecte abordate se referă la utilizarea metodelor cantitative în procesul de luare a deciziei. Accentul este pus nu pe metodele propriu-zise, ci pe modul în care ele pot fi rezolvate utilizând foile de calcul.

Modele matematice

În majoritatea cazurilor în care se încearcă rezolvarea unor probleme manageriale se constată că modul în care este structurată problema conduce la obţinerea unui obiectiv specific (cum ar fi maximizarea unui profit sau minimizarea unui cost). De asemenea, se constată că de multe ori există o serie de restricţii sau constrângeri (cum ar fi capacitatea de producţie). Succesul folosirii analizei cantitative depinde de acurateţea cu care obiectivul şi restricţiile sunt exprimate sub formă de ecuaţii şi relaţii matematice.

Expresia matematică care descrie obiectivul problemei se numeşte funcţie obiectiv. De exemplu, ecuaţia P=10*x poate fi funcţia obiectiv a unei firme care încearcă să maximizeze profitul.

Relaţiile matematice care descriu constrângerile problemei se numesc restricţii. Dacă de exemplu pentru a produce o unitate de produs sunt necesare 5 ore şi într-o săptămână se lucrează doar 40 de ore, atunci relaţia 50*x>=40 este o restricţie de timp. 5*x reprezintă timpul total necesar pentru a produce x unităţi, care trebuie să fie mai mic sau egal cu cele 40 de ore disponibile.

Problema de decizie este următoarea: Câte unităţi trebuie produse într-o săptămână pentru a maximiza profitul? Modelul matematic al acestei probleme este:

Restricţia x>=0 este necesară deoarece nu se poate fabrica un număr negativ de produse.

Page 137: EXCEL - SUPORT DE CURS

Metoda programării liniare

Programarea liniară este o metodă de rezolvare a problemelor de luare a deciziei. Următoarele tipuri de aplicaţii sunt specifice pentru rezolvarea lor cu ajutorul programării liniare:

1. Un manager trebuie să stabilească pentru perioada următoare programul de producţie şi nivelul stocurilor astfel încât să fie satisfăcută cererea de pe piaţă şi în acelaşi timp vrea să minimizeze costul total de producţie şi costurile de stocare.

2. Un analist financiar trebuie să selecteze pentru un portofoliu de investiţii cea mai bună combinaţie de acţiuni şi obligaţiuni. Aceste investiţii trebuie selectate astfel încât să se maximizeze eficienţa investiţiei.

3. Un director de marketing trebuie să stabilească modul în care va distribui bugetul pentru publicitate în diverse medii: radio, televiziune, ziare şi reviste, astfel încât efectul reclamei făcute să fie maxim.

4. O companie are depozite în câteva oraşe din ţară şi primeşte comenzi de la clienţi din diverse localităţi. Se pune problema determinării cantităţilor care vor fi trimise de la depozite spre clienţi astfel încât costurile totale de transport să fie minimizate.

Acestea sunt doar câteva exemple în care programarea liniară a fost utilizată cu succes, dar lista poate continua. Ce au în comun aceste exemple este faptul că ele încearcă să minimizeze sau să maximizeze ceva. În primul exemplu managerul vrea să minimizeze costurile; în exemplul 2 analistul financiar vrea să maximizeze eficienţa investiţiei; în exemplul 3 directorul de marketing trebuie să maximizeze eficienţa reclamei; în exemplul 4 trebuie minimizate cheltuielile de transport. În toate problemele de programare liniară obiectivul este maximizarea sau minimizarea unor cantităţi.

Toate problemele de programare liniară au şi o a doua proprietate: restricţiile care limitează gradul în care obiectivul poate fi realizat. În exemplul 1 producţia este limitată de capacitatea de producţie şi în acelaşi timp trebuie să satisfacă cererea; în exemplul 2 analistul este limitat de suma disponibilă şi tipul acţiunilor existente; în exemplul 3 directorul de marketing este constrâns de bugetul fixat şi de disponibilitatea mediilor de reclama; în exemplul 4 cantităţile ce pot fi transportate sunt limitate la disponibilul din fiecare depozit. Deci, restricţiile sunt o altă trăsătură generală a fiecărei probleme de programare liniară.

Exemplu

Firma ABC produce o varietate de produse chimice. În cadrul unui proces de producţie, pentru a produce două produse (un aditiv şi un solvent) sunt necesare trei tipuri de materii prime. Aditivul este vândut fabricilor de ulei şi este folosit la producerea a diverse tipuri de combustibil. Solventul este vândut combinatelor chimice şi este utilizat la fabricarea detergenţilor. Pentru a fabrica aditivul şi solventul cele trei materii prime sunt amestecate în proporţiile indicate în tabelul II.1.1.

ProdusAditiv Solvent

Material 1 2/5 ½Material 2 0 1/5

Page 138: EXCEL - SUPORT DE CURS

Material 3 3/53/10

Tabelul II.1.1 – Necesarul de materii prime pentru obţinerea unei tone de adidiv/solventPentru a obţine o tonă de aditiv se amestecă 2/5 tone de material 1şi 3/5 tone de

material 3. O tonă de solvent poate fi obţinută prin amestecarea a ½ tone de material 1, 1/5

tone de material 2 şi 3/10 tone de material 3. Producţia este limitată de disponibilitatea celor trei materii prime. În prezent firma

dispune de 20 tone de material 1, 5 tone de material 2 şi 21 tone de material 3. Prin natura procesului de producţie, materiile prime care nu sunt utilizate în procesul de producţie curent sunt considerate deşeuri.

Fiecare tonă de aditiv aduce un profit de 40$ , iar fiecare tonă de solvent aduce un profit de 30$.

Managementul firmei ABC, după analiza cererii de pe piaţă a decis că preţurile stabilite vor determina vânzarea întregii cantităţii produse (aditiv şi sovent).

Formularea problemei

Formularea problemei sau modelarea reprezintă procesul de transpunere a problemei într-un model matematic. Modelarea problemei este o artă care poate fi stăpânită prin practică şi experienţă. Deşi fiecare problemă are caracteristici unice, multe probleme pot avea trăsături comune. Ca urmare, pentru începători pot fi utile o serie de reguli ce pot fi aplicate pentru formularea unui model, reguli ce vor fi ilustrate în dezvoltarea modelului matematic pentru firma ABC.

Acest exemplu a fost selectat pentru a introduce metoda programării liniare pentru că este uşor de înţeles. În practică apar probleme mai complicate, care necesită o analiză mai profundă pentru a identifica toate aspectele care trebuie incluse în model.

Primul pas este identificarea obiectivului şi a restricţiilor. În cazul nostru obiectivul este maximizarea profitului total. Restricţiile se referă la cantităţile de materii prime disponibile, care limitează cantităţile de aditiv şi solvent ce pot fi produse.

Restricţia 1: cantitatea de material 1 utilizată trebuie să fie mai mică sau egală cu cantitatea de material 1 disponibilă.

Restricţia 2: cantitatea de material 2 utilizată trebuie să fie mai mică sau egală cu cantitatea de material 2 disponibilă.

Restricţia 3: cantitatea de material 3 utilizată trebuie să fie mai mică sau egală cu cantitatea de material 3 disponibilă.

Următorul pas este definirea variabilelor de decizie. Cele două variabile de decizie sunt: numărul de tone de aditiv produse şi numărul de tone de solvent produse. Notăm cu:

A: cantitatea de aditiv produsă (tone)S: cantitatea de solvent produsă (tone)

A şi S sunt variabile de decizie.Se scrie obiectivul utilizând variabilele de decizie. Profitul total provine din două

surse: vânzările de aditiv şi vânzările de solvent. Dacă profitul obţinut prin vânzarea unei tone de aditiv este de 40$, atunci prin vânzarea a A tone profitul va fi 40*A. La fel, dacă profitul obţinut prin vânzarea unei tone de solvent este de 30$, atunci prin vânzarea a S tone profitul va fi 40*S.

Profitul total = 40A + 30S

Page 139: EXCEL - SUPORT DE CURS

Expresia matematică a obiectivului se numeşte funcţie obiectiv. În cazul nostru obiectivul este maximizarea profitului total, deci funcţia obiectiv va fi:

Max ( 40A + 30S )

Se scriu restricţiile utilizând variabilele de decizie.

Restricţia 1. Deoarece o tonă de aditiv este produsă folosind 2/5 tone de material 1, cantitatea de material 1 necesară pentru a produce A tone de aditiv este 2/5 * A. Pentru fiecare tonă de solvent se folosesc ½ tone de material 1, deci cantitatea de material 1 necesară pentru a produce S tone de solvent este ½ * S. Astfel, cantitatea totală de material 1 necesară este 2/5 * A + ½ * S. Cantitatea disponibilă de material 1 este de 20 tone, deci transpunerea sub formă de ecuaţie a restricţiei 1 este:

2/5 * A + ½ * S > =20Restricţia 2. Deoarece la fabricarea aditivului nu este necesar materialul 1 se va lua

în lua în calcul doar cantitatea de material 2 utilizată la fabricarea solventului. Pentru fiecare tonă de solvent se folosesc 1/5 tone de material 2, deci cantitatea de material 2 necesară pentru a produce S tone de solvent este 1/5 * S. Astfel, cantitatea totală de material 2 necesară este 1/5 * S. Cantitatea disponibilă de material 2 este de 5 tone, deci transpunerea sub formă de ecuaţie a restricţiei 2 este:

1/5 * S >=5Restricţia 3. Deoarece o tonă de aditiv este produsă folosind 3/5 tone de material 3,

cantitatea de material 3 necesară pentru a produce A tone de aditiv este 3/5 * A. Pentru fiecare tonă de solvent se folosesc 3/10 tone de material 3, deci cantitatea de material 3 necesară pentru a produce S tone de solvent este 3/10 * S. Astfel, cantitatea totală de material 3 necesară este 3/5 * A + 3/10 * S. Cantitatea disponibilă de material 3 este de 21 tone, deci transpunerea sub formă de ecuaţie a restricţiei 3 este:

3/5 * A + 3/10 * S >= 21

Până acum am specificat relaţiile matematice referitoare la constrângerile asociate celor trei materii prime. Mai trebuie oare alte restricţii? Poate firma ABC să producă un număr negativ de tone de aditiv şi solvent? Răspunsul este evident nu. Deci pentru ca variabilele de decizie să nu aibă valori negative mai sunt necesare două restricţii:

A >=0S >=0

Modelul matematic al problemei este acum complet. Atât obiectivul cât şi restricţiile au fost transformate într-un set de relaţii matematice, set de relaţii definit ca model matematic. Modelul matematic complet al problemei este:

Max ( 40A + 30S )2/5 * A + ½ * S>=201/5 * S>=53/5 * A + 3/10 * S>=21A>=0S>=0

Pentru rezolvarea problemei trebuie găsită combinaţia optimă (de A şi S) care să satisfacă toate restricţiile şi în acelaşi timp să conducă la o valoare a funcţiei obiectiv care să fie mai mare sau egală decât orice valoare calculată cu o altă soluţie posibilă.

Page 140: EXCEL - SUPORT DE CURS

Dacă funcţia obiectiv şi restricţiile sunt funcţii liniare în raport cu variabilele de decizie (variabilele de decizie apar numai la puterea I), atunci avem o problemă de programare liniară.

Pentru rezolvarea problemelor de programare liniară există mai multe metode analitice: metoda Simplex, metoda grafică. În continuare vom prezenta modul în care pot fi rezolvate problemele de programare liniară utilizând foile de calcul (Microsoft Excel).

Utilizarea foilor de calcul pentru rezolvarea problemelor de programare liniară

Foile de calcul sunt instrumente utilizate frecvent pentru prelucrarea datelor în multe organizaţii. Deoarece modelele matematice necesită de multe ori date care deja există în alte foi de calcul, este important a înţelege modul în care o problemă de programare liniară poate fi rezolvată cu ajutorul foilor de calcul. În continuare vom ilustra modul în care se poate rezolva problema precedentă folosind foile de calcul. În acest scop va fi folosit programul de calcul tabelar Microsoft Excel.

Un model de programare liniară transpus într-o foaie de calcul va conţine următoarele elemente:Celulele care conţin datele problemei.Celulele pentru variabilele de decizie.O celulă care conţine formula pentru calcularea funcţiei obiectiv.Celulele care conţin formulele pentru calcularea părţii stângi a restricţiilor.Celulele care conţin valorile părţii drepte a restricţiilor.

Transpunerea problemei într-o foaie de calcul presupune parcurgerea următoarelor etape:Introducerea datelor problemei în foaia de calcul.Definirea celulelor care vor conţine variabilele de decizie.Definirea celulei care conţine formula pentru funcţia obiectiv.Definirea celulelor care conţin formulele din partea stângă a resticţiilor.Definirea celulelor care conţin valorile din partea dreaptă a restricţiilor.

În figura II.1.2 este prezentată soluţia pentru problema prezentată anterior.

Page 141: EXCEL - SUPORT DE CURS

Figura II.1.2 – Foaia de calcul utilizată pentru rezolvarea problemei

Remarcaţi că foaia de calcul este alcătuită din două părţi: o parte conţine datele problemei şi alta conţine modelul. Un avantaj al separării datelor de model este că se poate studia efectul modificării mărimilor de intrare asupra modelului făcând modificări doar în zona care conţine date. Un alt avantaj este că analistul poate dezvolta modelul independent de datele disponibile.În continuare este prezentat fiecare pas al procedurii:

Pasul 1: Introducerea datelor problemei. Datele problemei apar în partea superioară a foii de calcul. Fracţiile care reprezintă compoziţia pentru obţinerea unei tone de solvent şi aditiv au fost convertite în valori zecimale şi introduse în domeniul B5:C7. Valoarea 0.4 din celula B5 arată că fiecare tonă de aditiv produsă utilizează 0.4 tone de material 1, valoarea 0.5 din celula C5 arată că fiecare tonă de solvent produsă utilizează 0.5 tone de material 1, etc. Celulele D5:D7 conţin cantitatea disponibilă din fiecare material, iar celulele B8 şi C8 conţin profitul obţinut prin vânzarea unei tone de aditiv (40$), respectiv solvent (30$).

Pasul 2: Definirea celulelor care vor conţine variabilele de decizie. Celulele B15 şi C15 conţin numărul de tone de aditiv şi solvent produse.

Pasul 3: Definirea celulei care conţine formula funcţiei obiectiv. Celula B17 conţine formula pentru calcularea funcţiei obiectiv: = B8*B15+ C8*C15 (profiul unitar pe tona de aditiv * producţia de aditiv + profiul unitar pe tona de solvent * producţia de solvent).

Pasul 4: Definirea celulelor care conţin formulele din partea stângă a restricţiilor. Celulele B20:B22 conţin formulele care indică cum se calculează partea stângă a restricţiilor. Pentru materialul 1, în celula B20 se introduce formula =B5*B15+C5*C15 (cantitatea de aditiv produsă*cantitatea de material 1 pentru a produce o tonă de aditv + cantitatea de solvent produsă*cantitatea de material 1 pentru a produce o tonă de solvent). În mod similar se vor introduce în celulele B21 şi B22 formulele pentru materialele 2 şi 3.

Pasul 5: Definirea celulelor care conţin valorile din partea dreaptă a restricţiilor. În problema analizată valorile din partea dreaptă a restricţiilor reprezintă cantităţile de material disponibile, valori care deja sunt introduse în domeniul D5:D7. Pentru materialul 1, în celula D20 se introduce formla =D5, pentru matrialul 2, în celula D21 se introduce formula =D6, iar pentru materialul 3 în celula D22 se introduce formula =D7.

Un avantaj al folosirii foilor de calcul este că dacă una din valorile din partea care conţine datele problemei se modifică valorile din model se modifică automat..Pentru a determina soluţia optimă a problemei se va folosi Solver-ul din Excel. Paşii următori arată modul în care poate fi folosit Solver-ul pentru obţinerea soluţiei optime pentru o problemă de programare liniară.Se selectează meniul Tools.Se aplică comanda Solver.Caseta Solver Parameters se completează în modul următor:Set Target Cell: B17Se selectează opţiunea Max.By Changing Cells: B15:C15.

Page 142: EXCEL - SUPORT DE CURS

Se selectează butonul Add.Caseta Add Constraint se completează astfel:Cell Reference: B20:B22Se selectează operatorul ‹ =Constraint: D20:D22Se selectează butonul OK.Când caseta Solver Parameters apare din nou se selectează butonul Options.În caseta Solver Options se selectează:Assume Linear Model.Assume Non- Negative.Butonul Ok.Când caseta Solver Parameters apare din nou se selectează butonul Solve.În caseta Solver Results se selectează Keep Solver Solution. Se selectează butonul Ok pentru a genera soluţia optimă afişată în celulele B15, C15.Soluţia optimă este 25 tone de aditiv şi 20 tone de solvent.

Analiza de senzitivitate şi interpretarea rezultatelor

Problemele din lumea reală au loc într-un mediu în continuă schimbare. Preţul materiilor prime, salariile, cererea, oferta, valoarea acţiunilor, etc. sunt valori care pot varia de la un moment la altul. Dacă o problemă de programare liniară este utilizată într-un astfel de mediu, ne putem aştepta ca anumiţi coeficienţi ai problemei să se modifice în timp. Deci va trebui să determinăm cum afectează aceste schimbări soluţia optimă a problemei de programare liniară iniţială.Cu analiza de senzitivitate se poate observa cum este afectată soluţia optimă de modificări ale coefiecienţilor dintr-o problemă de programare liniară. Utilizând analiza de senzitivitate se poate răspunde la întrebări de tipul:Cum este afectată soluţia optimă de o modificare a unui coeficient din funcţia obiectiv?Cum este afectată soluţia optimă de o modificare a valorii din partea dreaptă a restricţiilor?Deoarece obiectul analizei de senzitivitate este modul în care modificările specificate afectează soluţia optimă analiza nu poate începe până când nu se obţine soluţia problemei de programare liniară iniţială. Din această cauză analiza de senzitivitate este de multe ori numită şi analiză postoptimală.

Revenind la problema prezentată anterior:

Max ( 40A + 30S )2/5 * A + ½ * S>=20 Materialul 11/5 * S>=5 Materialul 23/5 * A + 3/10 * S>=21 Materialul 3A>=0S>=0

Soluţia optimă A=25 tone de aditiv şi S=20 tone de solvent s-a obţinut pentru cazul în care s-a considerat că profitul pe tonă pentru aditiv este 40$, iar profitul pe tonă pentru solvent este de 30$.

Presupunem că datorită unor factori exteriori are loc o reducere a preţurilor, ceea ce determină o scădere a profitului de la 30$ pe tonă la 25$ pe tonă pentru solvent. În acest caz programul de producţie de 25 de tone de aditiv şi 20 de tone de solvent este în

Page 143: EXCEL - SUPORT DE CURS

continuare cel mai bun? În mod normal ar trebui să rezolvăm o nouă problemă de programare liniară cu funcţia obiectiv modificată 40*A+25*S. Acest lucru nu este necesar, deoarece cu analiza de senzitivitate putem determina în ce limite poate varia profitul pe tona de aditiv fără ca soluţia optimă să se modifice. Dacă analiza de senzitivitate arată că 25 tone de aditiv şi 20 de tone de solvent rămâne soluţia optimă atâta timp profitul pe tona de solvent variază între 20$ şi 40$, agentul decizional poate considera că estimarea de 30$/tonă este bună. Dacă analiza de senzitivitate arată că 25 de tone de aditiv şi 20 de tone de solvent rămâne soluţia optimă atâta timp profitul pe tona de solvent variază între 29.90$ şi 32$, managementul va trebui să reanalizeze acurateţea estimării de 30$/tona de solvent.

Domeniul de optimalitate pentru fiecare coeficient al funcţiei obiectiv este domeniul de valori în care acest coeficient poate varia fară a modifica soluţia optimă. Managerul va trebui să analizeze cu atenţie acei coeficienţi din funcţia obiectiv care au un domeniu de optimalitate îngust, deoarece o mică modificare a acestora poate modifica soluţia optimă.

Un alt aspect al analizei de senzitivitate se referă la modificările valorilor din partea dreaptă a restricţiilor. Referindu-ne la aceeaşi problemă pentru soluţia optimă sunt utilizate în întregime stocurile de material 1 şi 3. Ce se întâmplă cu soluţia optimă şi profitul total dacă se măresc cantităţile disponibile de material 1 şi 3?

Cu analiza de senzitivitate se poate determina cu cât va creşte profitul total dacă cantitatea disponibilă de material 1 sau 3 creşte cu o tonă.

Pentru ca programul Excel să furnizeze un raport pentru realizarea analizei de senzitivitate, când se rezolvă problema cu Solver-ul, în fereastra de dialog Solver Results, secţiunea Reports, se selectează Sensitivity (vezi lecţia 12).

Interpretarea raportului Excel pentru analiza de senzitivitate

Raportul generat de Excel are structura prezentată în figura II.1.3.

Figura II.1.3 – Raportul de analiză de senzitivitate

Raportul are două secţiuni Adjustable Cells şi Constraints. În secţiunea Adjustable Cells se analizează coeficienţii variabilelor de decizie din funcţia obiectiv, iar în secţiunea Constraints sunt analizate valorile din partea dreaptă a restricţiilor.

Secţiunea Adjustable CellsÎn coloana Cell sunt afişate celulele care conţin coeficienţii variabilelor de decizie

din funcţia obiectiv, iar în coloana Name sunt afişate numele acestor celule.

Page 144: EXCEL - SUPORT DE CURS

Coloana Final Value conţine valorile optime pentru variabilele de decizie. Pentru problema analizată soluţia este 25 de tone de aditiv şi 20 tone de solvent.Coloana Reduced Cost. Pentru fiecare variabilă de decizie, valoarea absolută din Reduced Cost arată cât de mult trebuie să crească (pentru problemele de maximizare) sau să scadă (pentru problemele de minimizare) coeficientul variabilei de decizie din funcţia obiectiv astfel încât variabila de decizie respectivă să aibă valoare pozitivă. Dacă o variabilă de decizie este pozitivă în soluţia optimă costul redus este 0. Pentru problema analizată ambele variabile de decizie au valori pozitive şi costurile reduse sunt 0. Dacă de exemplu pentru cantitatea de solvent s-ar fi obţinut 0 în coloana Final Value şi –12.5 în coloana Reduced Cost, interpretarea ar fi următoarea: profitul pe tona de solvent ar trebui să crească la 30+12.50=42.50 pentru ca în soluţia optimă variabila de decizie ataşată cantităţii de solvent să aibă o valoare pozitivă. Altfel spus, pentru a produce solvent ar trebui ca profitul pe tona de solvent să fie 42.50$.

Coloana Objective Coefficient conţine valorile coeficienţilor variabilelor de decizie din funcţia obiectiv, iar coloanele Allowable Increase şi Allowable Decrease conţin valorile pe baza cărora se poate calcula domeniu de optimalitate pentru coeficientul respectiv (creşterea şi micşorarea permisă). De exemplu, pentru aditiv:

Deci dacă profitul pe tona de aditiv variază între 24 şi 60, soluţia optimă de 25 tone de aditiv şi 20 tone de solvent rămâne neschimbată.

Pentru solvent:

Deci dacă profitul pe tona de solvent variază între 20 şi 50, soluţia optimă de 25 tone de aditiv şi 20 tone de solvent rămâne neschimbată.

Secţiunea ConstraintsColoana Cell indică celulele care conţin valorile din partea dreaptă a restricţiilor,

iar coloana Name conţine numele acestor celule.Valorile din coloana Final Value sunt valorile restricţiilor (partea stângă)

calculate pentru soluţia optimă. Pentru problema analizată valorile din coloana Final Value indică cantităţile de material 1, 2 şi 3 necesare pentru a produce combinaţia optimă de 25 de tone de aditiv şi 20 tone de solvent. Deci pentru soluţia optimă sunt necesare 20 tone de material 1, 4 tone de material 2 şi 21 tone de material 3.

Valorile din coloana Constraint RH sunt valorile iniţiale ale problemei: 20 tone de material 1, 5 tone de material 2, 21 tone de material 3 (cantităţile disponibile). Pentru fiecare restricţie abaterea reprezintă diferenţa dintre valoarea din coloana Constraint RH şi valoarea din Final Value. Abaterea asociată materialului 1 este 20-20=0 tone, pentru materialul 2: 5-4=1 tonă iar pentru materialul 3: 21-21=0 tone. Deci materialele 1 şi 3 sunt utilizate în totalitate, iar din materialul 2 rămâne o tonă. Concluzia este că dacă ar exista cantităţi mai mari de material 1 sau 3 s-ar putea obţine un profit total mai mare. Modul în care modificarea acestor cantităţi influenţează profitul este indicat în coloana Shadow Price (preţuri umbră).

Preţurile umbră arată cu cât se modifică (creştere/micşorare) valoarea funcţiei obiectiv la creşterea/micşorarea cu o unitate a valorii din partea dreaptă a unei restricţii.

În cazul nostru, preţul umbră de 33.33 pentru materialul 1 arată că o tonă suplimentară de material 1 va creşte profitul cu 33.33$. Deci, dacă cantitatea disponibilă

Page 145: EXCEL - SUPORT DE CURS

de material 1 ar creşte de la 20 la 21, ceilalţi coeficienţi rămânând constanţi, profitul total ar creşte cu 33.33$, ceea ce înseamnă 1600+33.33=1633.33$.Similar, dacă cantitatea disponibilă de material 3 ar creşte de la 21 la 22, ceilalţi coeficienţi rămânând constanţi, profitul total ar creşte cu 44.44$, ceea ce înseamnă 1600+44.44=1644.44$.Valoarea 0 a preţului umbră pentru materialul 2 arată că dacă cantitatea disponibilă de material 2 ar creşte, valoarea funcţiei obiectiv (profitul total) nu s-ar modifica.Ultimele două coloane Allowable Increase şi Allowable Decrease determină domeniul în care poate varia termenul din dreapta al unei restricţii fără a se modifica preţurile umbră. De exemplu, considerând restricţia pentru materialul 1, termenul din partea dreaptă are valoarea 20, creşterea permisă este 1.5 şi micşorarea permisă este de 6. Ştim că cu un preţ umbră de 33.33$ , o tonă în plus de material 1 va creşte valoarea funcţiei obiectiv (profitul) cu 33.33$, iar reducerea cantităţii de material cu o tonă va micşora valoarea funcţiei obiectiv cu 33.33$. Valorile din Allowable Increase şi Allowable Decrease arată că preţul umbră de 33.33$ este valabil pentru creşteri de material 1 de până la 1.5 tone şi reduceri de panâ la 6 tone.Domeniul de valori în care preţul umbră este aplicabil se numeşte domeniu de fezabilitate. Deci pentru materialul 1 domeniul de fezabilitate este între 20-6=14 şi 20+1.5=21.5 tone. Pentru modificări în afara domeniului de fezabilitate problema trebuie rezolvată din nou pentru a găsi noul preţ umbră.Pentru restricţia materialului 2 creşterea permisă este 1E+30, deci 1030, un număr foarte mare. Putem interpreta această valoare ca o evidenţă a faptului că nu există limită superioară pentru domeniul de fezabilitate a materialului 2. Cu alte cuvinte, oricât material 2 ar fi disponibil, valoarea funcţiei obiectiv nu s-ar modifica. Descreşterea permisă (1) arată că limita minimă a domeniului de fezabilitate pentru materialul 2 este 5-1=4 tone. Deci dacă pentru producţie ar fi disponibile 4.5 tone de material 2, valoarea funcţiei obiectiv nu s-ar modifica. Dacă sunt disponibile mai puţin de 4 tone va trebui să rezolvăm problema din nou pentru a afla noua soluţie şi preţurile umbră.Pentru materialul 3, domeniul de fezabilitate este între 21-2.25=18.75 tone şi 21+9=30 tone. Deci preţul umbră de 44.44 este aplicabil dacă termenul din partea dreaptă a restricţiei (cantitatea de material disponibil) ia valori între 18.75 tone şi 30 tone.

Informaţiile din raportul de analiză de senzitivitate se bazează pe presupunerea că doar un coeficient se modifică toţi ceilalţi rămânând neschimbaţi.

Page 146: EXCEL - SUPORT DE CURS

REZOLVAREA PROBLEMELOR DE TRANSPORT

Page 147: EXCEL - SUPORT DE CURS
Page 148: EXCEL - SUPORT DE CURS
Page 149: EXCEL - SUPORT DE CURS

Probleme de transport

Problemele de transport apar frecvent în situaţiile în care trebuie planificat modul de distribuire al bunurilor de la producători la consumatori. Obiectivul obişnuit al acestor probleme este minimizarea costurilor de transport. Modelele de transport sunt o variaţie a problemelor de programare liniară şi presupun următoarele:Obiectivul este minimizarea costurior totale de transport.Costurile de transport sunt funcţii liniare în raport cu numărul de unităţi transportate.Cererea şi oferta sunt exprimate în unităţi omogene.Costurile de transport pe unitate nu variază cu cantitatea transportată.Pentru a ilustra modul în care se pot rezolva problemele de transport prezentăm următorul exemplu:O companie dispune de trei fabrici şi patru centre de distribuţie. Fabricile sunt plasate în Cluj, Bacău şi Craiova. Capacităţile de producţie ale fabricilor sunt:

Fabrica Capacitate de producţie (unităţi)Cluj 5000Bacău 6000Craiova 2500Total: 13.500

Centrele de distribuţie sunt plasate în Deva, Iaşi, Bucureşti, Braşov. Cererea pentru produsele companiei în aceste centre este:

Centre de distribuţie Cerere (unităţi)Deva 6000Iaşi 4000Bucureşti 2000Braşov 1500Total: 13.500

Managementul ar dori să determine cantitatea care ar trebui transportată de la fiecare fabrică la fiecare centru de distribuţie astfel încât costurile de transport să fie minime.

Figura II.2.1 prezintă graficul cu cele 12 rute posibile. Un astfel de graf este numit graf de reţea. Cercurile reprezintă nodurile reţelei. Liniile care unesc nodurile se numesc arcuri. Fiecare punct de plecare şi sosire este reprezentat printr-un nod, iar fiecare rută posibilă este reprezentată printr-un arc. În dreptul fiecărui nod este trecută valoarea ofertei (pentru capacităţile de producţie) sau a cererii (pentru centrele de distribuţie). Sensul de deplasare este indicat prin săgeţi. Costurile unitare de transport pentru fiecare rută sunt prezentate în tabelul II.2.1 şi pe fiecare arc din figura II.2.1.

DestinaţieOrigine 1. Deva 2. Iaşi 3. Bucureşti 4. Braşov1. Cluj 3 2 7 62. Bacău 7 5 2 33. Craiova 2 5 4 5

Tabelul II.2.1 – Costurile unitare de transport pe fiecare rută

Page 150: EXCEL - SUPORT DE CURS

Figura II.2.1 – Graful de reţea ataşat problemei

Pentru a rezolva problema de transport putem folosi programarea liniară. Vom utiliza variabile de decizie cu doi indici, primul indice indică nodul origine, al doilea nodul destinaţie. Astfel xij indică numărul de unităţi transportate de la fabrica i la centrul de distribuţie j.

Costul unităţilor transportate din Cluj este = 3*x11+2*x12+7*x13+6*x14

Costul unităţilor transportate din Bacău este = 7*x21+5*x22+2*x23+3*x24

Costul unităţilor transportate din Craiova este = 2*x31+5*x32+4*x33+5*x34

Suma acestor costuri este costul total de transport, valoare care trebuie minimizată deci funcţia obiectiv este:Min (3*x11+2*x12+7*x13+6*x14+7*x21+5*x22+2*x23+3*x24+2*x31+5*x32+4*x33+5*x34)În problemele de transport apar restricţii deoarece fiecare fabrică are o capacitate de producţie limitată şi fiecare centru de distribuţie are o anumită cerere. Fabrica din Cluj are o capacitate de producţie de 5000 unităţi. Numărul total de unităţi transportate din fabrica de la Cluj este x11+x12+x13+x14, deci restricţia asociată acestei fabrici este:x11+x12+x13+x14 5000În mod similar pentru celelalte fabrici avem:x21+x22+x23+x24 6000 - pentru fabrica de la Bacău.x31+x32+x33+x34 2500 - pentru fabrica de la Craiova.În cele patru centre de distribuţie, restricţia va fi dată de faptul că cererea la centrul respectiv trebuie să fie egală cu cantităţile transportate aici.x11+x21+x31+x41 =6000 - cererea la Devax12+x22+x32+x42 =4000 - cererea la Iaşix13+x23+x33+x43 =2000 - cererea la Bucureştix14+x24+x34+x44 =1500 - cererea la Braşov

Cluj

Craiova

Bac[u

Bra=ov

Bucure=ti

Ia=i

Deva

5000

6000

2500

1500

2000

4000

6000

7

2

6

3

3

2

57

5

45

2

Page 151: EXCEL - SUPORT DE CURS

Combinând funcţia obiectiv cu restricţiile obţinem modelul pentru problema de transport:

Min (3*x11+2*x12+7*x13+6*x14+7*x21+5*x22+2*x23+3*x24+2*x31+5*x32+4*x33+5*x34)x11+x12+x13+x14 5000x21+x22+x23+x24 6000x31+x32+x33+x34 2500x11+x21+x31+x41 =6000 x12+x22+x32+x42 =4000 x13+x23+x33+x43 =2000 x14+x24+x34+x44 =1500 xij0, i=1,2,3; j=1,2,3,4

Rezolvarea problemei în Excel

Foaia de calcul folosită pentru rezolvarea problemei este prezentată în figura II.2.2.

Figura II.2.2 – Foaia de calcul ataşată problemei

Datele problemei sunt introduse în domeniul A1:F8. Costurile de transport sunt conţinute în domaniul B5:E7, capacităţile de producţie (oferta) în F5:F7, iar cererea din centrele de distribuţie în celulele B8:E8.

Elementele cheie care trebuie introduse în Excel sunt variabilele de decizie, funcţia obiectiv, partea stângă şi partea dreaptă a restricţiilor.

Page 152: EXCEL - SUPORT DE CURS

Variabilele de decizie

Celulele B17:E19 conţin variabilele de decizie. Iniţial toate variabilele de decizie au valoarea 0.

Funcţia obiectiv

Pentru a calcula costul total, în celula C13 a fost introdusă formula =SUMPRODUCT(B5:E7,B17:E19).

Partea stângă a restricţiilor

Celulele F17:F19 conţin formulele pentru partea stângă a restricţiilor asociate capacităţilor de producţie, iar celulele B20:E20 conţin formulele pentru partea stângă a restricţiilor asociate cererii din centrele de distribuţie. Formulele utilizate sunt:Celula F17: =SUM(B17:E17). Se copiează F17 în F18:F19.Celula B20: =SUM(B17:B19). Se copiează B20 în C20:E20.

Partea dreaptă a restricţiilor

Celulele H17:H19 conţin partea dreaptă a restricţiilor asociate capacităţilor de producţie, iar celulele B22:E22 conţin partea dreaptă a restricţiilor asociate cererii din centrele de distribuţie. Aceste valori sunt introduse deja în datele iniţiale ale problemei, deci se vor utiliza formulele:Celula H17: =F5. Se copiează H17 în H18:H19.Celula B22: =B8. Se copiează B22 în C22:E22.

Se rezolvă problema utilizând Solver-ul. Caseta de dialog Solver Parameters se completează ca în figura II.2.3. Opţiunile selectate sunt Assume Linear Model şi Assume Non-Negative.

Figura II.2.3 – Caseta de dialog Solver

Soluţia optimă arată că costul minim de transport este de 39500 u.m., iar în domeniul B17:E19 sunt afişate cantităţile care trebuie transportate pe fiecare rută. Valoarea 0 indică că pe ruta respectivă nu se transportă nimic.

Variaţii ale problemelor de transport

Oferta totală nu este egală cu cererea totală

Page 153: EXCEL - SUPORT DE CURS

În multe cazuri oferta totală nu este egală cu cererea totală. Dacă oferta totală depăşeşte cererea totală nu este necesară nici o modificare în problema de programare liniară. Excesul de ofertă va apărea ca o abatere în soluţia problemei, iar aceste abateri pot fi interpretate ca ofertă neutilizată sau cantităţi netransportate.Dacă oferta totală este mai mică decât cererea totală modelul de programare liniară a problemei de transport nu are o soluţie fezabilă. Pentru rezolvarea problemei se creează o ofertă fictivă astfel încât excesul de cerere să fie satisfăcut şi se atribuie costurilor de transport din acest punct valoarea 0. În acest mod problema de programare liniară va avea soluţie.Maximizarea funcţiei obiectivÎn unele probleme obiectivul este găsirea unei soluţii care maximizează venitul sau profitul. Utilizând venitul sau profitul unitar în coeficienţii funcţiei obiectiv, se va rezolva o problemă de maximizare în locul uneia de minimizare. Modificările nu afectează restricţiile.

Rute neacceptateStabilirea unei rute de la fiecare nod origine la fiecare nod destinaţie nu este

întotdeauna posibilă. Pentru a rezolva aceste situaţii se elimină din graful de reţea arcele respective, iar din modelul de programare liniară variabilele de decizie corespunzătoare. Pentru a face cât mai puţine modificări în foaia de calcul, pentru aceste rute se stabilesc costuri foarte mari, astfel încât pe aceste rute se vor efectua transporuri doar dacă nu există alte soluţii fezabile.

Rute cu capacităţi limitatePentru rutele cu capacităţi limitate se introduc restricţii suplimentare. De exemplu,

dacă mijloacele de transport pe ruta Craiova – Deva nu pot transporta mai mult de 1000 de unităţi se va introduce restricţia x131000.

Modelul general de programare liniară al unei probleme de transport cu m puncte de origine şi n puncte de destinaţie este:

unde:i = index-ul pentru punctele de originej = index-ul pentru punctele de destinaţiexij = numărul de unităţi transportate de la originea i la destinaţia jcij = costul unitar de transport din originea i la destinaţia jsi = oferta sau capacitatea din originea idj = cererea la destinaţia j

Page 154: EXCEL - SUPORT DE CURS

REZOLVAREA PROBLEMELOR DE ALOCARE

Page 155: EXCEL - SUPORT DE CURS
Page 156: EXCEL - SUPORT DE CURS

Probleme de alocare

Problemele de alocare pot apărea în diverse situaţii de luare a deciziilor. Problemele tipice sunt: alocarea lucrărilor pe maşini, repartizarea personalului în diverse centre teritoriale, repartizarea agenţilor care să efectueze anumite activităţi. O caracteristică distinctă este că unui agent îi este asignată o singură activitate şi se încearcă optimizarea unui obiectiv, cum ar fi minimizarea costurilor, minimizarea timpului, maximizarea profitului, etc.

Pentru a ilustra modul de rezolvare a problemelor de alocare vom considera următorul exemplu: Firma ABC, specializată în studii de marketing are trei clienţi noi. Fiecărui proiect îi trebuie alocat un lider de proiect. Timpul necesar pentru realizarea proiectului depinde de experienţa şi abilitatea liderului de proiect. În prezent sunt disponibile doar trei persoane, proiectele au aproximativ aceeaşi prioritate şi nu pot fi realizate în acelaşi timp. Conducerea firmei trebuie să stabilească ce lider de proiect va coordona fiecare studiu astfel încât cele trei studii să se termine în timpul total cel mai scurt. Unui lider i se poate aloca doar un proiect. Cu trei clienţi şi trei studii sunt posibile 9 alternative. Timpii estimaţi pentru finalizarea fiecărui proiect sunt prezentaţi în tabelul II.3.1.

ClientLider de proiect 1 2 3Ionescu 10 15 9Popescu 9 18 5Georgescu 6 14 3

Tabelul II.3.1 – Timpii estimaţi pentru terminarea fiecărui proiect

Figura II.3.1 prezintă graful de reţea pentru problema analizată.

1Ionescu

2Popescu

3Georgesc

uu

Client 3

Client 2

Client 1

14

3

6

5

9

18

9

15

10 11

1

1 1

1

Page 157: EXCEL - SUPORT DE CURS

Figura II.3.1 – Graful de reţea ataşat problemei

Nodurile corespund liderilor de proiect şi clienţilor, iar arcurile reprezintă repartizările posibile ale liderilor de proiect clienţilor.

Oferta în fiecare nod origine este 1 şi cererea în fiecare nod destinaţie este 1. Costul repartizării unui lider de proiect la un client este timpul necesar pentru realizarea studiului. Observaţi asemănarea dintre problemele de alocare şi cele de transport, problemele de alocare fiind un caz special de probleme de transport în care toate ofertele şi cererile au valoarea 1, iar cantitatea transportată pe fiecare arc este 0 sau 1.

Problema poate fi rezolvată folosind metoda programării liniare. Avem nevoie de o variabilă pentru fiecare arc şi o restricţie pentru fiecare nod. Vom utiliza variabile de decizie cu doi indici xij - repartizarea liderului i la proiectul j.

Deci vom avea 9 variabile de decizie:

unde i=1,2,3 şi j=1,2,3.Utilizând aceste notaţii:

Timpul necesar pentru finalizarea proiectelor de către Ionescu este 10x11+15x12+9x13 (doar una din variabilele de decizie poate lua valoarea 0).Timpul necesar pentru finalizarea proiectelor de către Popescu este 9x21+18x22+5x23 (doar una din variabilele de decizie poate lua valoarea 0).Timpul necesar pentru finalizarea proiectelor de către Georgescu este 6x31+14x32+3x33 (doar una din variabilele de decizie poate lua valoarea 0).

Suma acestor timpi furnizează numărul total de zile pentru a finaliza cele trei studii de piaţă. Astfel, funcţia obiectiv este:

Min (10x11+15x12+9x13+9x21+18x22+5x23+6x31+14x32+3x33)

Restricţiile reflectă faptul că fiecare lider poate fi repartizat cel mult unui client şi fiecare client trebuie să aibă repartizat un lider. Aceste restricţii sunt:

Combinând funcţia obiectiv cu restricţiile obţinem următorul model:

Min (10x11+15x12+9x13+9x21+18x22+5x23+6x31+14x32+3x33)

Foaia de calcul folosită pentru rezolvarea problemei este prezentată în figura II.3.2.

Page 158: EXCEL - SUPORT DE CURS

Figura II.3.2 – Foaia de calcul ataşata problemei

Datele problemei sunt introduse în domeniul A1:D7.

Variabilele de decizie

Celulele D16:D18 sunt rezervate variabilelor de decizie. Iniţial toate variabilele de decizie au valoarea 0.

Funcţia obiectiv

Formula =SUMPRODUCT(B5:D7,B16:D18) a fost plasată în celula C12 pentru a calcula numărul necesar de zile pentru a termina toate proiectele.

Partea stângă a restricţiilor

Celulele E16:E18 conţin partea stângă a restricţiilor referitoare la numărul de clienţi la care poate fi repartizat un lider. Celulele B19:D19 conţin partea stângă a restricţiilor conform cărora unui proiect trebuie să-i fie repartizat un lider de proiect. Formulele utilizate sunt:Celula E16: =SUM(B16:D16). Se copiează E16 în E17:E18.Celula B19: =SUM(B16:B18). Se copiează B19 în C19:D19.

Partea dreaptă a restricţiilor

Celulele G16:G18 conţin partea dreaptă a restricţiilor pentru lideri, iar celulele B21:D21 conţin partea dreaptă a restricţiilor pentru clienţi. Toate valorile sunt egale cu 1.

Se rezolvă problema utilizând Solver-ul. Caseta de dialog Solver Parameters se completează ca în figura II.3.3. Opţiunile selectate sunt Assume Linear Model şi Assume

Page 159: EXCEL - SUPORT DE CURS

Non-Negative.

Figura II.3.3 – Caseta Solver Parameters

Soluţia optimă a problemei este: Ionescu este repartizat clientului 2, Popescu clientului 3 şi Georgescu clientului 1. Timpul de finalizare a celor trei proiecte este de 26 de zile.

Variaţii ale problemei

Deoarece problemele de alocare pot fi tratate ca fiind cazuri speciale de probleme de transport, variaţiile care pot apărea la problemele de alocare sunt aceleaşi ca şi la problemele de transport.Numărul total de agenţi (oferta) este diferit de numărul total de activităţi (cererea)

Dacă numărul de agenţi depăşeşte numărul de activităţi, agenţii suplimentari vor rămâne nealocaţi în modelul de programare liniară. Dacă numărul de activităţi este mai mare decât numărul de agenţi, modelul de programare liniară nu va avea o soluţie fezabilă. În această situaţie este suficient să adăugăm un număr suficient de „agenţi falşi“ pentru ca numărul de agenţi să fie egal cu numărul de activităţi. În funcţia obiectiv coeficienţii pentru agenţii falşi vor fi zero.

Dacă problemele de alocare sunt evaluate în termeni de venit sau profit vom avea de rezolvat o problemă de maximizare în loc de una de minimizare.

În plus, dacă una sau mai multe alocări nu pot fi acceptate, variabilele de decizie corespunzătoare vor fi eliminate din modelul de programare liniară. Pentru exemplul prezentat acest lucru poate apărea dacă la un agent nu are experienţa necesară să lucreze la un proiect. Pentru a nu face modificări în foaia de calcul, cea mai simplă soluţie ar fi ataşarea unor costuri foarte mari pentru variabilele de decizie ce corespund alocărilor ce nu pot fi acceptate.

Page 160: EXCEL - SUPORT DE CURS

Modelul general pentru problemele de alocare este:

Page 161: EXCEL - SUPORT DE CURS

MANAGEMENTUL PROIECTELOR

Page 162: EXCEL - SUPORT DE CURS
Page 163: EXCEL - SUPORT DE CURS
Page 164: EXCEL - SUPORT DE CURS

Managementul proiectelor

Multe din proiectele din viaţa reală sunt foarte complexe şi costisitoare. Realizarea acestora la timp şi în cadrul bugetului alocat nu este o sarcină uşoară. În mod tipic, anumite activităţi nu pot începe înainte ca altele să se termine. Iar dacă într-un proiect apar sute de astfel de dependenţe, problemele de planificare se complică foarte mult, iar managerii au nevoie de metode speciale de analiză.

Câteva din întrebările la care vom încerca să răspundem în continuare sunt:Care este termenul de terminare al proiectului?Care sunt momentele de început şi de terminare ale fiecărei activităţi?Care activităţi sunt critice, în sensul că ele trebuie să se termine exact în termenul planificat, astfel încât să nu fie depăşit termenul final de realizare al proiectului?Cât de mult pot fi întârziate activităţile necritice astfel încât să nu fie depăşit termenul final de realizare al proiectului?Cum pot fi alocate resursele diverselor activităţi astfel încât proiectul să se realizeze rapid şi cu costuri minime?Metodele PERT şi CPM, acronimele pentru Program Evaluation Review Technique şi Critical Path Method, graficele Gant, sunt metode de analiză utilizate pentru managemenul proiectelor. Indiferent de metodă primul pas în planificarea proiectelor este definirea activităţilor şi stabilirea relaţilor de precedenţă dintre acestea. Aceasta este partea cea mai importantă a unui proiect şi în mod normal în această etapă ar trebui implicate mai multe persoane, astfel încât să nu fie uitată nici o activitate importantă.

Exemplu

În prezent firma ABC are birouri doar în Bucureşti, şi doreşte să deschidă birouri noi în Braşov. În acest scop o parte din personalul din Bucureşti se va muta în Braşov şi se va angaja personal nou. În timp ce economiştii trebuie să se ocupe de partea financiară a afacerii, arhitecţii trebuie să se ocupe de proiectarea interioarelor.

Anumite părţi ale proiectului nu pot începe până când altele nu sunt terminate. De exemplu, nu pot fi amenajate birourile dacă acestea nu au fost încă proiectate, sau nu se poate angaja personal până nu se stabileşte personalul necesar. În tabelul II.4.1 sunt prezentate activităţile din care este alcătuit proiectul.

Activitatea Descriere Activităţi precedente

Durata de realizare(săptămâni)

A Selectarea birourilor - 3B Stabilirea planului de organizare şi a

celui financiar- 5

C Determinarea personalului necesar B 3D Proiectarea interioarelor A, C 4E Amenajarea birourilor D 8F Selectarea personalului care se va muta C 2G Angajarea de personal nou F 4H Mutarea propriu-zisă F 2I Stabilirea relaţiilor cu noii parteneri din

BraşovB 5

J Instruirea peronalului H, E, G 3Tabelul II.4.1 - Activităţile proiectului

Page 165: EXCEL - SUPORT DE CURS

Fiecare activitate este plasată într-un rând separat, iar în coloana Activităţi precedente sunt trecute activităţile care trebuie realizate înaintea începerii activităţii analizate. De exemplu activitatea C nu poate începe până nu se termină activitatea B. În coloana Durata de realizare este trecut timpul estimat pentru realizarea activităţilor.

Grafice Gant

Una din metodele cele mai populare folosite pentru planificarea proiectelor este utilizarea graficelor Gant. Fiecare activitate este desfăşurată pe axa verticală. Pe axa orizontală este reprezentat timpul. Activităţile sunt reprezentate prin bare de lungime egală cu timpul de realizare a activităţii. Graficul indică şi termenul cel mai devreme de începere a fiecărei activităţi. De exemplu, activitatea C nu poate începe înainte de sfârşitul săptămânii 5, deoarece activitatea B trebuie să se termine înainte ca C să înceapă. Pe măsură ce o activitate este realizată bara asociată este haşurată. Astfel, în orice moment de timp este foarte clar ce activităţi au fost realizate la timp şi care nu. Graficul din figura II.4.1 arată că în săptămâna 13 activităţile D, E şi H sunt în urma planului, iar activitatea G este înaintea planului.

Figura II.4.1 – Graficul Gant

În contextul graficelor Gant „în plan“ înseamnă că activitatea nu a fost finalizată mai târziu de cel mai devreme termen de terminare a activităţii. Astfel, în figura II.4.1 putem observa că activităţile D şi H ar trebui să se termine cel mai devreme în săptămâna 12. Deoarece nu sunt terminate în săptămâna 13 ele sunt în urma planului.Din graficele Gant nu se pot stabili predecesorii imediaţi ai unei activităţi. În figura II.4.1 poate părea că F şi I sunt activităţi precedente ale activităţii G, deoarece G poate începe în săptămâna 10, iar F şi I se pot termina atunci. Dar din tabelul II.4.1 ştim că doar F este „predecesor imediat“ a lui G. O întârziere a activităţii I nu ar afecta momentul de începere al activităţii G. Astfel de informaţii sunt importante pentru manager pentru că ar putea să stabilească ce activităţi ar putea fi întârziate fără a modifica termenul final de realizare al proiectului. Graficele Gant nu pot fi folosite pentru astfel de analize, în acest caz fiind recomandată metoda de reprezentare a proiectului printr-un graf.

Page 166: EXCEL - SUPORT DE CURS

Reprezentarea proiectelor prin grafuri

Fiecare activitate este reprezentată în graf printr-un arc. Începutul şi sfârşitul fiecărei activităţi sunt indicate printr-un cerc numit nod. Fiecărui nod i se atribuie un număr. Modul de atribuire a numerelor este arbitrar. Pe măsură ce se construieşte graful nodurile se pot renumerota, dar trebuie păstrate corect relaţiile de precedenţă între activităţi. Fiecare activitate trebuie să înceapă în nodul în care activitatea precedentă se termină. De exemplu, în figura II.4.2, activitatea C începe în nodul 3, deoarece activitatea precedentă B se termină aici.

Figura II.4.1 – Graful pentru activităţile de la A la C

Figura II.4.2 – Graful parţial

Complicaţii apar în momentul în care încercăm să adăugăm activitatea D în graf. Şi A şi C sunt activităţi precedente pentru D, şi cum vrem ca în graf activitatea D să apară o singură dată trebuie să combinăm nodurile 2 şi 4 din figura II.4.2 într-unul singur. Acest lucru este arătat în figura II.4.3. Nodul 2 (au fost renumerotate nodurile) reprezintă evenimentul în care activităţile A şi C au fost terminate. Activitatea E, care are ca activitate precedentă doar pe D poate fi adăugată fără dificultate. Când încercăm să adăugăm activitatea F apar din nou probleme. Cum F are activitate precedentă pe C, ar trebui ca activitatea F să înceapă în nodul 3. Dar acest lucru ar însemna că activitatea F are ca activitate precedentă şi pe A, ceea ce este incorect.

1

2

3

4

A

BC

1

2

3

4A

B

C

5D E

Page 167: EXCEL - SUPORT DE CURS

Această dilemă poate fi rezolvată prin introducerea unei activităţi fictive, reprezentă prin linie punctată în figura II.4.4. Această activitate nu necesită nici timp şi

nici resurse. Astfel, figura II.4.4 arată că activitatea D poate începe după ce şi A şi C s-au

terminat. Similar, F poate începe după ce activitatea C s-a terminat.Putem generaliza modul în care introducem o activitate fictivă în modul următor:

Presupunem că vrem să adăugăm o activitate A, în nodul de start N, dar nu toate activităţile care se termină în nodul N sunt activităţi precedente ale acestei activităţi. Pentru aceasta se creează un nou nod M, cu o activitate fictivă de la nodul M la nodul N. Toate activităţile care se termină în N şi sunt predecesoare ale activităţii A se vor termina în nodul M. Acum activitatea A poate începe în nodul M.

Figura II.4.5 prezintă graful asociat tabelului II.4.1.

Figura II.4.5 – Graful de reţea

Fiecare activitate este identificată printr-un nod de start şi unul de terminare. În graful din figura II.4.5 s-ar putea face confuzia că G şi H reprezintă aceeaşi activitate. Pentru a evita confuzia se introduce o nouă activitate fictivă (figura II.4.6).

Figura II.4.6 – Introducerea celei de a doua activităţi fictive

Astfel, graful final are forma din figura II.4.7.

1

2

3

5A

B C 7

DE

64F

8

G

H

I

J

6

G

8

7

H

1

2

3

5A

B C

7D E

64F

Figura II.4.4 – Introducerea unei activit[\i fictive

Page 168: EXCEL - SUPORT DE CURS

Figura II.4.7 – Graful finalDin tabelul 4.1. se poate calcula (adunând duratele de realizare ale activităţilor) că

timpul total de realizare al proiectului este de 39 de săptămâni. Termenul acesta poate fi mai mic deoarece unele activităţi se pot desfăşura simultan (de exemplu activităţile A şi B).

Pentru a afla termenul minim de realizare al proiectului trebuie să calculăm drumul critic. Un drum într-un graf este o succesiune de activităţi de la nodul iniţial (1) la nodul final (9). De exemplu secvenţa B-I necesită 10 săptămâni pentru a fi realizată secvenţa B-C-D-E-J 23 de săptămâni. Într-un graf pot fi identificate mai multe drumuri de la nodul iniţial la cel final, cu durate diferite. Se pune problema determinării celui mai lung drum de la nodul iniţial la cel final. Acest drum, numit drum critic, va determina timpul de realizare al proiectului, deoarece nici un alt drum nu este mai lung. Dacă activităţile de pe drumul critic sunt întârziate, întregul proiect va fi întârziat. Din această cauză activităţile care se găsesc pe drumul critic se numesc activităţi critice. Activităţile critice trebuie realizate „la termen“.

Problema se rezolvă în modul următor:Se calculează pentru fiecare activitate cel mai devreme termen de începere şi cel mai devreme termen de terminare. Vom nota cu:DI – cel mai devreme termen pentru începerea unei activităţiDT – cel mai devreme termen pentru terminarea unei activităţit – durata estimată a activităţii.Pentru o activitate, relaţia dintre aceste mărimi este: DT=DI+tTermenul DI pentru o activitate care pleacă dintr-un nod este cel mai mare DT al activităţilor care se termină în acel nod.Pentru fiecare activitate din reţea se calculează DI şi DT. Rezultatul este prezentat în figura II.4.8.

1

2

3

5A(3)

B(5) C(3) 7

D(4)E(8)

64F(2)

9

G(4)

H(2)

I(5)

J(3)8

1

2

3

5A(0,3)

B(0,5) C(5,8) 7

D(8,12) E(12,20)

64F(8,10)

9

G(10,14)

H(10,12)

I(5,10)

J(20,23)8

Page 169: EXCEL - SUPORT DE CURS

Figura II.4.8 – Termenele DI şi DT

Deci, cel mai devreme termen de terminare al proiectului este de 23 de săptămâni.

Se calculează cel mai târziu termen de începere şi terminare a activităţii. Pentru a identifica activităţile critice şi intervalele de timp cu care activităţile necritice pot fi întârziate fără a afecta termenul de finalizare al proiectului, se parcurge graful înapoi de la nodul final la nodul iniţial. Ideea este că odată ce se cunoaşte termenul de realizare al proiectului (23 de săptămâni), pornind de la această valoare putem calcula cel mai târziu termen la care se poate termina o activitate fără a întârzia întregul proiect. Evaluarea începe de la nodul final spre nodul iniţial.Vom nota cu:TI – cel mai târziu termen de începere a unei activităţiTT – cel mai târziu termen de terminare a unei activităţiRelaţia dintre aceste mărimi este: TI = TT – tTermenul TT pentru o activitate care se termină într-un nod este cel mai mic TI al activităţilor care pleacă din acel nod.Rezultatele sunt prezentate în figura II.4.9.

Figura II.4.9 – Calcularea TI şi TT

Determinarea rezervei de timp asociate fiecărei activităţi.Rezerva de timp este timpul cu care o activitate poate fi întârziată fără a afecta termenul de finalizare al proiectului. Rezerva de timp (RT) se calculează cu formula: RT = TI - DI = TT – DTDe exemplu, pentru activitatea G, rezerva de timp este:RTG = TIG – DIG = 16 - 10 = 6 sauRTG = TTG – DTG = 20 - 14 = 6 Aceasta înseamnă că activitatea G poate întârzia cu 6 săptămâni după cel mai devreme termen de începere a activităţii fără a întârzia proiectul.Pentru activitatea C:RTC = TIC – DIC =5 - 5 = 0Deci activitatea C nu are rezervă de timp şi trebuie să înceapă în săptămâna 5. Cum această activitate nu poate fi întârziată fără a afecta întregul proiect, înseamnă că această activitate este o activitate critică.

Activităţile care au rezerva de timp 0 sunt activităţi critice.

1

2

3

5A(5,8)

B(0,5) C(5,8) 7

D(8,12)E(12,20)

64F(14,16)

9

G(16,20)

H(18,20)

I(18,23)

J(20,23)8

Page 170: EXCEL - SUPORT DE CURS

Rezolvarea cu Excel

Rezolvarea problemelor de managementul proiectelor cu Excel se face folosind abordarea bazată pe grafuri. Foaia de calcul care conţine acest model este prezentată în figura II.4.10.

Datele şi formulele introduse sunt cele rezultate prin dezvoltarea grafului ataşat proiectului. De exemplu, deoarece cel mai târziu termen de terminare a activităţii F este cea mai mică valoare dintre cele mai târzii termene de începere ale activităţilor G, F şi K, formula din celula G7 va fi = MIN(F8, F9, F12). Deoarece cel mai devreme termen pentru începerea activităţii D este cea mai mare valoare din cele mai devreme termene de terminare ale activităţilor A şi C, formula din D5 este = MAX(E2,E4). În coloana activitate critică este trecut cuvântul DA pentru activităţile care au abaterea 0.

Figura II.4.10 – Foaia de calcul Excel

Formulele utilizate în foaia de calcul sunt:

Celula Formula Se copiează înD4 =MAX(E3) -D5 =MAX(E2,E4) -D6 =MAX(E5) -D7 =MAX(E4) -D8 =MAX(E7) -D9 =MAX(E7) -D10 =MAX(E3) -D11 =MAX(E6,E8,E9) -E2 =D2+C2 E3:E11F2 =G2-C2 F3:F11G2 =MIN(F5) -G3 =MIN(F4,F10) -G4 =MIN(F5,F7) -G5 =MIN(F6) -G6 =MIN(F11) -G7 =MIN(F8,F9) -G8 =MIN(F11) -G9 =MIN(F11) -G10 =E13 -G11 =E13 -H2 =F2-D2 H3:H11

Page 171: EXCEL - SUPORT DE CURS

I2 =IF(H2=0,“DA“,“NU“) I3:I1E13 =MAX(E2:E11) -

Reprezentarea grafică a graficelor Gant în Excel

În graficul Gant activităţile sunt afişate pe axa verticală iar pe axa orizontală este reprezentat timpul. Graficul indică cel mai devreme termen de începere a fiecărei activităţi şi durata activităţii. Vom ilustra modul de construire a graficelor Gant pentru exemplul din figura II.4.10.Se selectează datele care vor fi reprezentate în grafic: activităţile (A1:A11), durata activităţilor (C1:C11) şi cel mai devreme termen de începere a activităţilor (D1:D11).Se creează un grafic de tip Staked Bar.Se selectează seria DI. Se apasă butonul din dreapta al mouse-ului şi se selectează comanda Format Series. Se selectează butonul Series Order şi se stabileşte pentru afişarea seriilor ordinea DI, Durata. Se selectează butonul Patterns, şi în secţiunile Border şi Area se selectează opţiunile None. Deci barele ataşate termenelor de începere ale activităţilor vor fi transparente, iar barele care reprezintă durata activităţilor vor apărea în prelungirea lor.Se selectează seria Durata, se apasă butonul din dreapta al mouse-ului şi se selectează comanda Format Series. Se selectează butonul Data Labels, opţiunea Show Value. Astfel în dretul fiecărei bare va fi afişată durata activităţii.Se selectează axa Y, se apasă butonul din dreapta al mouse-ului şi se selectează comanda Format Axis. Se selectează butonul Scale, opţiunile Categories in reverse order şi Value (Y) axis crosses at maximum category. Astfel activităţile vor fi afişate începând din partea de sus a axei y.

Modelul de analiză a drumului critic/cost

Pentru reducerea timpului de realizare a unui proiect, analistul poate încerca reducerea duratei în care se efectuează anumite activităţi de pe drumul critic prin alocarea de resurse suplimentare. De exemplu, o activitate care durează în mod normal 2 săptămâni dacă se lucrează 8 ore pe zi, poate fi terminată mai repede dacă se lucrează peste program sau dacă se măreşte numărul de muncitori. Acest lucru, bineînţeles, se realizează cu preţul unor costuri crescute. Problema care se pune este: „Ce activităţi ar trebui urgentate astfel încât reducerea termenului final de realizare al proiectului să se facă cu costuri minime?“.

Acest model presupune că costul este o funcţie liniară de timp, descrescătoare, deoarece orice efort de urgentare este însoţit de creşterea cheltuielilor (figura II.4.11).

Timp

maxim

Timp

minim

Timp

Cost

Cost

maximCost

minim

Page 172: EXCEL - SUPORT DE CURS

Figura II.4.11 - Funcţia cost - durată

Pentru fiecare activitate se cunosc următoarele date:Timpul normal – timpul maxim de realizare a activităţiiCostul normal – costul necesar pentru realizarea activităţii în timpul normal

de lucruTimpul minim - timpul minim în care se poate realiza activitateaCost maxim - costul necesar pentru realizarea lucrării în timpul minimPentru prezentarea metodei vom folosi următorul exemplu:Un proiect, cu graful asociat prezentat în figura II.4.13, este alcătuit din 5

activităţi. Pentru fiecare activitate se cunosc timpul normal, timpul minim, costul normal şi costul maxim (prezentate în tabelul II.4.2).

Activitate Timp normal (ore)

Cost normal ($)

Timp minim (ore)

Cost maxim ($)

Costul urgentării/oră

A 32 640 20 800 13.3B 40 480 30 720 24C 50 1000 30 1200 10D 24 288 15 360 8E 120 4800 70 5600 16Total 7208

Tabelul II.4.2 - Activităţile proiectului

În ultima coloană din tabel s-a calculat pentru fiecare activitate costul urgentării pe oră egal cu (Costul maxim-Costul normal)/(Timpul normal-Timpul minim) .Figura II.4.12 ilustrează funcţia cost - durată pentru activitatea A.

Figura II.4.12 - Funcţia cost - durată pentru activitatea A

Graful asociat problemei este prezentat în figura II.4.13.

1

2

3

4 5

A

C

B

D

E32

50

40

120

24

Timp

Cost

800

640

3220

Page 173: EXCEL - SUPORT DE CURS

Figura II.4.13 – Graful asociat problemei

Utilizând duratele normale pentru fiecare activitate, cel mai devreme termen pentru finalizarea proiectului este 194 ore (pe drumul critic C-D-E).

Pentru a reduce termenul de finalizare al proiectului la 193 de ore o activitate de pe drumul critic trebuie urgentată cu o oră. Cum costul urgentării pe oră pentru activitatea D este mai mic decât costurile urgentării pe oră pentru activităţile C şi E (8‹10 şi 8‹16), se va urgenta activitatea D cu o oră. Astfel, proiectul se va termina în 193 de ore, drumul critic va fi C-D-E şi costul total 7208+8=7216.

Dacă termenul de finalizare mai trebuie redus cu o încă oră la 192 ore, aplicând un raţionament asemănător se urgentează activitatea D cu încă o oră şi costul marginal va creşte cu 8$.

Dacă termenul de finalizare trebuie redus mai mult, la 191 ore, problema se complică. Situaţia este ilustrată în figura II.4.4. Acum există două drumuri critce A-B-E şi C-D-E, ambele de 192 ore.

Figura II.4.4 – Graful pentru timpul de finalizare de 191 ore

Urgentarea uneia dintre activităţile A, B, C, D cu o oră va reduce un drum cu o oră dar drumul critic va rămâne tot de 192 ore. Un drum critic de 191 de ore se poate obţine dacă se urgentează activităţi de pe ambele drumuri, sau dacă se urgentează doar activitatea E. Deci există mai multe alternative, iar dintre acestea trebuie găsită soluţia care are costul minim. Pentru grafuri complexe rezolvarea în acest mod ar fi foarte greoaie. Problema poate fi rezolvată simplu cu ajutorul programării liniare. Figura II.4.5 conţine modelul ataşat problemei.

1

2

3

4 5

A

C

B

D

E32

50

40

120

22

Page 174: EXCEL - SUPORT DE CURS

Figura II.4.5 – Foaia de calcul utilizată pentru rezolvarea problemei

Formulele utilizate în foaia de calcul sunt:

Celula Formula Se copiează înF2 =B2-D2 F3:F6G2 =(E2-C2)/(B2-D2) G3:G6D9 0 -D10 =E9 -D11 0 -D12 =E11 -D13 =MAX(E10,E12) -E9 =D9+C9 E10:E13F9 =G9-C9 F10:F13G9 =F10 -G10 =F13 -G11 =F12 -G1 =F13 -G13 =E13 -H9 =F9-D9 H10:H13I9 =IF(H9=0,“***“,““) I10:I13D14 =E13 -C15 =SUMPRODUCT(B9,B13,G2:G6) -

În prima parte a foii de calcul se introduc timpul normal, costul normal, timpul minim şi costul maxim de realizare a fiecărei ativităţi. Pe baza acestor date, se calculează în coloana Durata maximă a urgentării durata maximă cu care poate fi urgentată fiecare activitate (diferenţa dintre timpul normal şi timpul minim), iar în coloana următoare costul urgentării pe oră (diferenţa dintre costul maxim şi costul normal raportată la durata maximă a urgentării).

Al doilea tabel din foaia de calcul conţine variabilele de decizie ale problemei – duratele cu care poate fi urgentată fiecare activitate (în domeniul B9:B13). Iniţial toate valorile vor avea valoarea 0.

În coloana Durata activităţii se calculează durata activităţii în cazul în care aceasta va fi urgentată cu valoarea din coloana Durata urgentării (diferenţa dintre durata normală şi durata urgentării).

În coloanele următoare se calculează cele mai devreme şi cele mai târzii termene de începere şi terminare al fiecărei activităţi, respectând succesiunea activităţilor (la fel ca în exemplul anterior). Apoi, se calculează pentru fiecare activitate abaterile şi se introduc formulele pentru determinarea activităţilor critice.

Funcţia obiectiv (celula C15) este minimizarea costului total de urgentare, calculat ca suma produselor dintre duratele cu care se urgentează fiecare activitate şi costul urgentării activităţii pe unitatea de timp - min(C15).

Restricţiile problemei sunt:Durata în care trebuie realizat proiectul (conţinută în celula D14). De exemplu dacă proiectul ar trebui terminat în 184 de ore, restricţia ar fi D14=184Durata cu care poate fi urgentată fiecare activitate nu poate depăşi durata maximă de urgentare, iar aceste durate sunt numere pozitive. Deci,

Page 175: EXCEL - SUPORT DE CURS

Se rezolvă problema cu ajutorul Solver-ului, iar rezultatele obţinute arată că pentru ca proiectul să se termine cu costuri minime în 184 de ore, trebuie urgentate lucrările D cu 2 ore şi E cu 8 ore. Costul suplimentar al urgentării ar fi în acest caz de 144$.

Page 176: EXCEL - SUPORT DE CURS

PROBLEME DE ANALIZĂ DECIZIONALĂ

Page 177: EXCEL - SUPORT DE CURS
Page 178: EXCEL - SUPORT DE CURS

Modele de analiză decizională

Principalele elemente ale unui proces decizional sunt:Agentul decizional.Tipul problemei decizionale. Dacă parametrii problemei analizate sunt cunoscuţi se spune că avem de a face cu decizii în condiţii de certitudine. În cazul unor evenimente ale căror probabilităţi de apariţie sunt cunoscute, se spune că procesul decizional are loc în condiţii de risc. Dacă probabilităţile de apariţie ale evenimentelor sunt aleatoare (nu sunt cunoscute), atunci decizia este adoptată în condiţii de incertitudine.Variantele posibile de acţiune, pe care decidenţii le au la dispoziţie.Strategiile posibile de acţiune ale managerilor. Aceste strategii constau în diverse reguli care permit alegerea unei variante din cele existente.Obiectivele procesului de decizie (scopul urmărit de manageri). Aceste obiective se concretizează fie în restricţii, fie în funcţii scop.Evenimentele care pot apărea, dar pe care agentul decizional nu le poate controla sunt numite stări ale naturii.Modelele de analiză decizională vor fi prezentate pe următorul exemplu:Firma PDC a cumpărat la Buşteni un teren pentru a construi un complex de vile. Preţurile de construcţie ale acestora variază între 300000$ şi 1200000$, în funcţie de numărul de camere. În urma studiilor efectuate au fost realizate trei proiecte de dimensiuni diferite: 6 vile cu 10 camere, 12 vile cu 20 de camere şi 18 vile cu 30 de camere. Factorul cheie în selectarea uneia din cele trei alternative este evaluarea corectă de către managementul firmei a cererii viitoare. Cu toate că piaţa poate fi influenţată prin publicitate, preţurile de cazare relativ mari fac ca cererea să depindă de o varietate de factori asupra cărora managementul nu are control.Managementul firmei crede că există două posibilităţi:acceptarea proiectului de către piaţă şi deci o cerere marecerere redusă.Deci, pentru exemplul analizat există două stări ale naturii:S1 – cerere mareS2 – cerere redusă

şi trei alternative:d1 – proiectul de dimensiune micăd2 – proiectul de dimensiune medied3 – proiectul de dimensiune mare

Utilizând cele mai bune informaţii disponibile, agentul decizional trebuie să evalueze pentru fiecare alternativă şi stare a naturii „câştigul“ ce va fi obţinut. În funcţie de problema analizată acest „câştig“ poate reprezenta un profit, un cost, un timp, o distanţă sau orice altă măsură care să reflecte „ieşirile“ problemei studiate.

În tabelul II.5.1 sunt prezentate profiturile evaluate pentru problema analizată (în termeni de milioane de dolari).

Alternativă Stări ale naturiiCerere mare (S1) Cerere redusă (S2)

Proiect de dimensiune mică - d1 8 7Proiect de dimensiune medie – d2 14 5Proiect de dimensiune mare – d3 20 -9

Tabelul II.5.1 – Profiturile obţinute pentru fiecare alternativă şi stare a naturii

Page 179: EXCEL - SUPORT DE CURS

V31=20 arată că se anticipează un profit de 20 de milioane dolari dacă se va selecta proiectul de dimensiune mare şi cererea va fi mare, V32= -9 arată că dacă se va selecta proiectul de dimensiune mare şi cererea este redusă se va obţine o pierdere de 9 milioane de dolari.

Decizii în condiţii de incertitudine

Problemele decizionale care conţin incertitudini apar atunci când nu se cunosc probabilităţile de apariţie ale stărilor naturii. Aceste probleme pot fi abordate din mai multe puncte de vedere, şi în mod corespunzător există mai multe criterii de decizie. Deoarece prin aplicarea diverselor criterii se pot obţine recomandări diferite este bine că agentul decizional să înţeleagă foarte bine toate criteriile existente şi să-l selecteze pe acela care i se potriveşte cel mai bine.

Criteriul optimist (criterium maxi-max)Pentru fiecare alternativă se determină cel mai bun „câştig“. Decizia recomandată

este cea cu „câştigul“ cel mai bun. Pentru problemele de maximizare „câştigul“ cel mai bun înseamnă cea mai mare valoare, pentru problemele de minimizare „câştigul“ cel mai bun înseamnă cea mai mică valoare.

Criteriul pesimist (criteriul maxi-min)Pentru fiecare alternativă se determină cel mai defavorabil „câştig“. Decizia

recomandată este cea cu cel mai bun „câştig“ defavorabil.

Criteriul regretelor (criteriul mini-max)Alternativa se alege luând în considerare diferenţa dintre rezultatul optim ce s-ar fi

putut obţine într-o anumită stare şi valoarea celorlalte rezultate. Această diferenţă este numită regret.

unde:Rij – „regretul“ asociat alternativei di şi stării naturii Sj

V*j - „câştigul“ corespunzător celei mai bune decizii pentru starea naturii Sj. Pentru

probleme de maximizare V*j este cea mai mare valoare pentru starea naturii Sj, pentru

probleme de minimizare V*j este valoarea cea mai mică.

Vij - „câştigul“ corespunzător alternativei di şi stării naturii Sj.

Următorul pas este determinarea regretului maxim pentru fiecare alternativă. În final va fi selectată alternativa cu cel mai mic „regret“ maxim.

Rezolvarea problemelor de analiză decizională în condiţii de incertitudine în Excel

Criteriul optimistÎn figura II.5.1 este prezentată foaia de calcul pentru rezolvarea problemei cu

criteriul optimist. Domeniul A4:C8 conţine datele problemei. În domeniul D6:D8 se calculează „câştigul“ maxim pentru fiecare alternativă. În celula D10 se calculează cel mai mare dintre „câştigurile“ maxime ale fiecărei alternative. În domeniul E6:E8 se afişează numele alternativei recomandate.Formulele utilizate sunt:

Celula Formula Se copiează în:D6 =MAX(B6:C6) D7:D8

Page 180: EXCEL - SUPORT DE CURS

D10 =MAX(D6:D8) -E6 =IF(D6=$D$10,A6,““) E7:E8

Figura II.5.1 – Criteriul optimist

Criteriul pesimistÎn figura II.5.2 este prezentată foaia de calcul pentru rezolvarea problemei cu

criteriul pesimist. Singura diferenţă dintre foile de calcul din figura II.5.1 şi II.5.2 este că la criteriul pesimist se determină „câştigul“ minim pentru fiecare alternativă. Astfel, celula D6 conţine formula =MIN(B6:C6), care este copiată în celulele D7 şi D8.

Figura II.5.2 – Criteriul pesimist

Criteriul regretelorÎn figura II.5.3 este prezentată foaia de calcul pentru rezolvarea problemei cu

criteriul regretelor. Domeniul A4:C8 conţine datele problemei.Această problemă presupune determinarea regretelor asociate fiecărei alternative

şi fiecărei stări a naturii. Formulele utilizate sunt:

Celula B14 - Se calculează „regretul“ faţă de cea mai bună valoare a stării naturii cerere mare. Formula utilizată este:=MAX($B$6:$B$8)-B6Se copiează B14 în B15 şi B16

Celula C14 - Se calculează „regretul“ faţă de cea mai bună valoare a stării naturii cerere redusă. Formula utilizată este:=MAX($C$6:$C$8)-C6

Page 181: EXCEL - SUPORT DE CURS

Se copiează C14 în C15 şi C16Celula D14 - Se calculează „regretul“ maxim. Formula utilizată este:

=MAX(B14:C14)Se copiează D14 în D15 şi D16

Celula D18 - Se calculează minimul „regretelor maxime“. Formula utilizată este:=MIN(D14:D16)

Celula E14 - În domeniul E14:E16 se afişează numele alternativei recomandate. În celula E14 se introduce formula:=IF(D14=$D$18,A14,““).Se copiează E14 în E15:E16.

Figura II.5.3 – Criteriul regretelor

Decizii în condiţii de risc

În multe probleme decizionale se poate estima probabilitatea cu care apar stările naturii. Principala caracteristică a deciziilor în condiţii de risc este că agentul decizional trebuie să aleagă o alternativă pe baza probabilităţilor de apariţie a stărilor naturii.

Notăm cu :N - numărul stărilor naturiiP(Sj) – probabilitatea de apariţie a stării SJ

La un moment dat poate apărea doar o stare a naturii, deci probabilităţile trebuie să îndeplinească condiţiile:

Valoarea aşteptată VA a unei alternative di este definită în moul următor:

Page 182: EXCEL - SUPORT DE CURS

Altfel spus, „valoarea aşteptată“ a unei alternative este suma ponderată a „câştigurilor“ alternativei analizate. Ponderea unui „câştig“ este probabilitatea asociată stării naturii j.

Pentru exemplul analizat, dacă probabilităţile de apariţie ale stărilor naturii sunt 0.8 pentr S1 şi 0.2 pentru S2, avem:VA(d1)=0.8*8+0.2*7=7.8VA(d2)=0.8*14+0.2*5=12.2VA(d3)=0.8*20+0.2*(-9)=14.2

În final va fi selectată alternativa cu „valoarea aşteptată“ cea mai bună (cea mai mare valoare pentru criterii de maximizare, cea mai mica valoare pentru criterii de minimizare). În exemplul analizat se încearcă maximizarea profitului, deci cea mai bună alternativă este d3 (cu VA=14.2)

Rezolvarea problemei utilizând Excel

În figura II.5.4 este prezentată foaia de calcul folosită pentru luarea deciziilor în condiţii de risc.

Figura II.5.4 – Luarea deciziei în condiţii de risc

Page 183: EXCEL - SUPORT DE CURS

Domeniul A4:C9 conţine datele problemei. Probabilităţile de apariţie ale celor două stări ale naturii sunt introduse în celulele B9 şi C9.

În domeniul D6:D8 se calculează „valoarea aşteptată“ pentru fiecare alternativă iar în domeniul E6:E8 se afişează numele alternativei recomandate.

Formulele utilizate sunt:

Celula D6 - Se calculează „valoarea aşteptată“ a alternativei d1. Formula utilizată este:=SUMPRODUCT($B$9:$C$9,B6:C6)Se copiează D6 în D7 şi D8.

Celula D11 - Se calculează „valoarea aşteptată“ maximă. Formula utilizată este:=MAX(D6:D8)

Celula E6 - Se determină alternativa recomandată. Formula utilizată este:=IF(D6=$D$11,A6,““)Se copiează E6 în E7:E8

Pentru o problemă de minimizare singura modificare în foaia de calcul ar fi schimbarea formulei din celula D11 în =MIN(D6:D8).