unitatea de invatare 4

Unitatea de invatare 4 Instrumente Excel pentru asistarea deciziei economice _________________________________________________________________________________________________________________

Tehnologia aplicaŃiilor Office 1

Unitatea de invatare 4 INSTRUMENTE EXCEL PENTRU ASISTAREA DECIZIEI ECONOMICE

__________________________________________________________________________________

Cuprins

Obiective 4.1 Instrumente pentru gruparea şi centralizare informaŃiei economice Tehnica valorii scop (Goal Seek) Gruparea datelor prin Subtotaluri Centralizarea datelor prin Consolidare Centralizarea datelor prin Tabela pivot Întrebări şi teste 4.2 Instrumente de simulare şi optimizare a informaŃiei Tehnica simularii prin controlul de tip Spinner Simularea prin Scenarii Optimizarea prin metoda Solver Folosirea Tabelei de ipoteze Întrebări şi teste Răspunsuri şi indicatii la întrebări şi teste

Bibliografie


�� OBIECTIVE

• Dobândirea unor cunoştinŃe necesare pentru efectuarea unei analize economico-financiare pe baza datelor dintr-un tabel EXCEL

• Exemplificarea modalităŃilor prin care se realizează gruparea informaŃiei din tabelul Excel.

• PosibilităŃi Excel pentru realizarea unor previziuni. • Utilizarea unor funcŃii Excel pentru analize dinamice

4.1 Instrumente pentru gruparea şi centralizare informaŃiei economice

Instrumentele de sintetizare a informaŃiei oferă tehnici şi facilităŃi avansate de regrupare şi centralizare a datelor conŃinute în tabele, obŃinându-se în urma prelucrări, noi informaŃii agregate şi sintetizate.

Tehnica valorii scop (Goal Seek) Tehnica valorii scop permite calcularea unei valori finale (numite scop sau obiectiv) ce este returnată de o formulă, pentru care se modifică un parametru de care depinde valoarea formulei respective. Identificăm, astfel, două celule a căror definire este absolut necesară pentru aplicarea tehnicii valorii scop: celula scop (Target Cell - celula rezultat pe care instrumentul Goal Seek va căuta să o aducă la o valoare precizată) şi celula care trebuie modificată (Changing Cell) pentru obŃinerea rezultatului dorit. Utilizând căutarea tip “rezultat” se poate ajusta o estimare pentru a se ajunge la o concluzie referitoare la o expresie relativă sau absolută (procentaj sau valoare) pentru un buget sau o variantă de simulat. Căutarea valorii scop are loc prin comanda Data �� Data Tools �� What-If Analysis

�� Goal Seek

Figura 4. 1 Utilizarea instrumentului Goal

Seek

Se stabilesc: celula ce conŃine scopul sau obiectivul

(rubrica Set cell:). Valoarea acestei celule este dată de o formulă care va ajusta automat valoarea iniŃială la o valoare finală (scop) precizată de utilizator printr-un parametru;

un parametru care desemnează mărimea valorii finale care se doreşte a se obŃine în urma simulării (rubrica To value:);

celula ce conŃine o valoare constantă ce trebuie modificată (rubrica By changing cell:) pentru a se atinge obiectivul propus în rubrica Set cell. Formularea unei cereri pentru a identifica o valoare scop sau obiectiv, ar fi următoarea: „cu cât (sau la cât) ar trebui modificată o valoare iniŃială (By changing cell) pentru ca o valoare scop (Set cell) să atingă un prag specificat (To value)?”

Valoarea scop

Valoarea modificată



Se observă că, în urma introducerii datelor de calcul pentru instrumentul Goal Seek, se caută valoarea celulei modificabile, ce poate conduce la obŃinerea valorii scop propuse şi se afişează fereastra Goal Seek Status ce anunŃă dacă operaŃiunea de găsire a unei valori corespunzătoare s-a încheiat cu succes sau cu un eşec (nicio valoare nu poate fi găsită). Prin tehnica valorii scop noile valori calculate ale formulelor vor înlocui vechile valori corespunzătoare modelul de simulare. Aceste valori noi vor fi ilustrate şi în caseta de dialog Goal Seek Status. La activarea butonului de comandă OK celula modificabilă a modelului de simulare va conŃine o nouă valoare, plecând de la care se propagă prin intermediul formulelor noile valori calculate pentru obiectivul fixat. Dacă se activează butonul Cancel, vechile valori ale modelului vor fi restaurate. Celula al cărei conŃinut va fi ajustat la o nouă valoare (By changing cell) este supusă anumitor restricŃii: trebuie să conŃină o valoare numerică constantă, şi să participe în mod nemijlocit la formarea rezultatului; de asemenea, celula ce conŃine valoarea scop sau obiectiv (Set cell) trebuie să conŃină în mod obligatoriu o formulă ale cărei argumente depind funcŃional direct sau indirect (prin intermediul unor alte formule) de celula ce conŃine obiectivul de simulat.

Gruparea datelor prin Subtotaluri Excel oferă posibilitatea sintetizării informaŃiei prin organizarea ei pe niveluri de grupare, iar apoi poate executa diferite calcule la nivelul acestor grupuri de informaŃii centralizate. Pentru a realiza gruparea prin totalizate acestea trebuie să fie sortate în prealabil pe rubricile de regrupare ale informaŃiei. Ordinea sortării se efectuează potrivit gradului de consistenŃă a datelor, în sensul în care primul nivel de sortare îl cuprinde pe al doilea s.a.m.d. Agregarea datelor prin subtotalizare este un demers operaŃional prin poziŃionarea cursorului pe una dintre celulele bazei de date (sau prin selectarea explicită a acesteia) şi activarea comenzii Subtotal de pe tabul Data. Prin caseta de dialog Subtotal (ilustrată în Figura 4. 3) se declară elementele necesare regrupării informaŃiei. Baza de date numită „Personal” definită pe coordonatele A4:F16 (ca în Figura 4. 3) se sortează crescător pe rubricile "Departament" şi "FuncŃia"

Figura 4. 2 Baza de date Personal

Se doreşte aflarea totalului “Salariului de încadrare” pe fiecare departament astfel:



� se precizează rubrica pe baza căreia se grupează înregistrările. Astfel, din lista derulantă At each change in se alege rubrica pe baza căreia se vor forma grupuri de înregistrări prin identificarea aceleaşi valori în câmpul de grupare "Departament"; � se alege din caseta Use Function tipul de calcul care va aplica datelor grupate (Sum; Max; Min; Avarage; Count; Product); � în final se selectează (simplu sau multiplu) casetele de validare aferente elementelor listei derulante Add subtotal to: ce corespund rubricii(lor) de calculat ( în cazul de faŃă "Salariul de încadrare"); � după precizarea acestor elemente se validează operaŃia prin butonul OK.

Figura 4. 3 Caseta de dialog Subtotal

Rezultatul grupării datelor potrivit valorilor rubricii „Departament”, precum şi rezultatul aplicării funcŃiei SUM asupra fiecărui grup în parte este prezentat în Figura 4. 4. În urma agregării datelor foaia de calul şi-a schimbat aspectul în sensul apariŃiei la stânga barei de identificare a liniilor a unor mici butoane de expandare (+) şi restrângere (-) a nivelului de grupare a datelor. Vizualizarea ierarhizată a elementelor regrupate este permisă prin intermediul a 3 mici butoane plasate în linie cu bara de identificare a coloanelor (numerotate 1, 2, 3).

Figura 4. 4 Rezultat al subtotalizării salariului de încadrare

Revenirea afişării din forma ierarhizată în forma normală este posibilă prin selectarea tabelului sau poziŃionarea cursorului într-o celulă oarecare a acestuia şi activarea comenzii Data → Subtotal, iar din caseta Subtotal va fi acŃionat butonul Remove All.

Centralizarea datelor prin Consolidare Consolidarea datelor din foile de calcul semnifică gruparea datelor într-o foaie centralizatoare după anumite reguli. Această grupare nu înseamnă obligatoriu însumarea acestor date – operaŃie cel mai des folosită – putând fi folosite şi alte funcŃii cum ar fi numărări, medii, minim, maxim şi alte funcŃii utilizate în statistică. Datele ce urmează a fi



consolidate se vor găsi în aceiaşi foaie de calcul cu rezultatul consolidării, în foi de calcul separate din acelaşi document sau în foi de calcul din documente diferite.

Consolidarea datelor se poate realiza în trei moduri:

�� consolidarea prin poziŃie;

�� consolidarea pe categorii;

�� folosind formule de calcul cu referinŃe 3-D. Fiecare din aceste tipuri de consolidări se folosesc în funcŃie de modul de aranjare a datelor în tabelele sursă. Pentru exemplificarea operaŃiei de consolidare, se va urmări calculul mediei vânzărilor obŃinute în trei filiale: Alba, IalomiŃa şi Timiş. Vânzările pentru fiecare judeŃ se vor găsi în foi de calcul diferite. Este utilizată consolidarea prin poziŃie când datele din toate zonele (foile de calcul) care participă la consolidare, sunt organizate într-o ordine şi poziŃie identică în cadrul zonei de selecŃie.

Figura 4. 5 Consolidarea prin poziŃie

Pentru a realiza o consolidare prin poziŃie se parcurgi paşii: 1. Se selectează zona în care va fi depus rezultatul consolidării. Această zonă se poate

menŃiona în patru moduri: • dacă se selectează o celulă, rezultatul consolidării va fi depus într-un spaŃiu

suficient pentru a afişa tot rezultatul consolidării, la dreapta şi în partea de jos a selecŃiei.

• dacă se selectează un rând de celule, rezultatul va fi depus în partea de jos a selecŃiei, într-o zonă ce nu va depăşi lăŃimea selecŃiei;

• dacă se selectează o coloană de celule, înălŃimea zonei în care va fi depus rezultatul nu va putea depăşi înălŃimea selecŃiei;

• dacă se selectează o zonă de celule, rezultatul consolidării va fi limitat la zona selectată. 2. Se copiază etichetele liniilor şi a coloanelor din tabelele sursă. Această operaŃie poate fi executată la sfârşitul consolidării; 3. Se apelează din tabul Data grupul Data Tools şi apoi opŃiunea Consolidate (Figura 4. 6); 4. În caseta Function se alege una dintre funcŃiile de totalizare disponibile: SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT NUMBERS STDEV(P), VAR(P) pentru realizarea consolidării; 5. În caseta Reference se introduce sursa de date ce va participa la consolidare.

Figura 4. 6 Caseta de dialog Consolidate

Sursa de date poate fi introdusă fie prin selectarea directă a acesteia în foaia de calcul, fie prin introducerea de la tastatură. Precizarea sursei de date se face în mai multe moduri în



funcŃie de poziŃia pe care o are faŃă de zona destinaŃie: • când zonele sursă şi zona destinaŃie sunt în aceeaşi foaie de calcul, se utilizează fie referinŃe de zonă, fie nume de zonă; • când sursele şi destinaŃia se află pe foi de calcul diferite, se utilizează referinŃe de foaie şi referinŃe de zonă sau numele acestora (ex. Alba!B6:B9); • când sursele şi destinaŃiile sunt în registre de lucru diferite, se utilizează referinŃe de registru, de foaie şi de zonă, în cazul în care registrele se află în acelaşi director (ex: '[Vanzari2009.xls]'Alba!B6:B9), în cazul în care se află în directoare diferite se mai adaugă şi calea de fişiere (ex. '[C:\CIG\Vanzari2009.xls]'Alba!B6:B9). Pentru a selecta un document Excel se apelează opŃiunea Browse. 6. Se acŃionează butonul de comandă Add, zona selectată anterior fiind înregistrată în caseta All References; 7. Se vor repeta paşii 4 şi 5 pentru fiecare zonă sursă ce participă la consolidare; 8. Pentru a actualiza automat rezultatul consolidării în momentul în care se modifică valori în

celule ce aparŃin de sursa consolidării, se activează caseta de selectare Create links to source data. Această casetă poate fi activată doar în cazul în care rezultatul consolidării se află pe o foaie de calcul diferită faŃă de sursa de date;

9. Se efectuează consolidarea validând operaŃiile efectuate cu ajutorul butonului OK.

Consolidarea pe categorii (Figura 4. 7) presupune gruparea datelor după etichetele coloanelor sau a liniilor din tabelele sursă a consolidării. Nu este necesar ca datele să fie aranjate într-o ordine identică, informaŃiile fiind regăsite pentru consolidare după etichete. Figura 4. 7 Consolidarea pe categorii

Ştergerea unui element care participă la consolidare se face prin selectarea acestuia din lista All references şi acŃionarea butonului Delete. Modificarea unui element se face doar prin ştergerea acestuia şi prin adăugarea unui nou element. După ce a fost realizată consolidarea modificarea sursei consolidării (adăugarea de zone sursă, modificarea referinŃei la o zonă sursă sau ştergerea referinŃei la o zonă sursă) şi recentralizarea datelor nu poate fi efectuată decât dacă nu au fost create legături către datele sursă. Această modificare se realizează urmând paşii: 1. Se selectează celula din stânga sus a tabelului centralizator;

2. Se apelează din tabul Data grupul Data Tools şi apoi opŃiunea Consolidate;

3. Se efectuează ştergerile şi adăugările surselor de date ce se doresc a fi modificate;

4. Pentru a recentraliza datele se acŃionează butonul OK. În cazul în care au fost create legături la zonele sursă, se şterge tabelul centralizator, iar dacă foaia de calcul are o ierarhie, aceasta trebuie eliminată. Pentru a elimina o ierarhie se selectează o celulă din foaia de calcul ce conŃine ierarhia şi din tabul Data, grupul Outline se alege opŃiunea Ungroup se acŃionează opŃiunea Clear Outline. Pentru a modifica sursa consolidării din acest moment se parcurg paşii descrişi anterior. Consolidarea folosind formule de calcul cu referinŃe 3-D vizează utilizarea formulelor cu incidenŃă tridimensională. Acest tip de consolidare este folosit de obicei în situaŃia în care datele din zonele sursă nu au o poziŃionare identică pentru a fi folosită



consolidarea prin poziŃie şi nici etichetele din zonele sursă nu sunt identice fiind imposibilă identificarea datelor de centralizat prin consolidarea pe categorii. În cazul consolidării folosind referinŃe 3D nu există restricŃii în ceea ce priveşte zonele sursă. Consolidarea datelor ce provin din fişiere distincte presupune utilizarea în cadrul formulei de consolidare şi a specificatorului de fişier (eventual şi calea către respectivul fişier) de unde provin datele de centralizat.

Centralizarea datelor prin Tabela pivot Tabela pivot este un instrument de asistare a deciziei ce permite analiza multidimensională a datelor regrupate în tabelele foii de calcul Excel. Tabela pivot reprezintă o facilitate prin care datele conŃinute de o foaie de calcul pot fi permutate, pe linie sau pe coloană, în vederea punerii în evidenŃă a unor informaŃii necesare procesului decizional. O tabelă pivot utilizează datele organizate în două dimensiuni: coloană, linie asupra datelor operându-se agregări şi calcule sub formă de totaluri, medii, minim, maxim etc. Aceste operaŃii pot fi selectate dintr-o listă de funcŃii predefinite, tabela pivot permiŃând operarea de calcule diferite asupra aceloraşi elemente supuse sintetizării. Din punct de vedere operaŃional, tabela pivot este un instrument care permite o foarte elastică asociere a unor câmpuri, într-o manieră interactivă, fapt ce duce la regruparea datelor şi prezentarea acestora într-un mod sintetic. Ea posedă un asemenea grad de interactivitate încât, odată ce a fost creată, orice rubrică a tabelei poate fi permutată (folosind tehnica Drag&Drop) obŃinându-se astfel o reorientare a structurii tabelei în raport de necesităŃile de informare ale utilizatorului. Tabela pivot se generează selectând sursa datelor de sintetizat şi executând comanda

Insert → Pivot Table → PivotTable Pornind de la baza de date „Editură” prezentată în Figura 4. 1, se doreşte afişarea numărului de cărŃi şi suma valorică a acestora, pe titluri de carte şi pe agenŃi de vânzare, restricŃionând interactiv tabela pe ani şi oraşe de distribuŃie.

Figura 4. 8 Baza de date „Editură”

Într-o prima etapă de generare a tabelei pivot, în urma execuŃiei comenzii Insert → Pivot Table → PivotTable se alege sursa de date pentru sintetizarea informaŃiei, precum şi amplasarea rezultatului acestei sintetizări (caseta de dialog Create Pivot Table - Figura 4. 9):



Sursa de date a unei sintetizări poate să o constituie:

un tabel Excel -Table sau un câmp -Range (Select a table or range);

o bază de date externă (Use an external data source) În situaŃia utilizării unei surse de date interne se introduce (sau se selectează dinamic) adresa tabelei sursă pe baza căreia se va construi tabela pivot.

Figura 4. 9 Alegerea sursei de date pentru sintetizare

Caseta de dialog Create Pivot Table oferă două opŃiuni de amplasare a tabelei pivot: • într-o nouă foaie de calcul (opŃiunea New Worksheet); • sau în foaia de calcul curentă (opŃiunea Existing worksheet).

În cazul selectării locaŃiei tabelei pivot în foaia de calcul curentă este necesar a se furniza în caseta Location adresa celulei de la care să se genereze tabela cu datele sintetizate. Odată validate opŃiunile de alegere a surselor de date şi de amplasare a tabelei pivot se afişează macheta tabelei pivot pe baza căreia se realizează operaŃia de sintetizare a datelor (Figura 4. 10). Din punct de vedere tehnic, tabela pivot se generează plecând de la elementele casetei de dialog Pivot Table Field List. Această casetă de dialog conŃine în partea superioară toate rubricile bazei de date organizate în coloană, fiecare rubrică având în faŃa sa o casetă de validare. În partea inferioară există patru secŃiuni rectangulare ce reprezintă opŃiunile de sintetizare. Avantajul incontestabil al utilizării instrumentului de sintetizare de tip „tabelă pivot” constă în posibilitatea de permutare a rubricilor bazei de date prin tehnica Drag&Drop din partea superioară a casetei de dialog Pivot Table Field List către cele patru cadrane sau secŃiuni din partea inferioară. Astfel, baza de date va fi sintetizată potrivit orientării rubricilor pe linie sau pe coloană, precum şi la nivel de raport, ceea ce conferă un aspect dinamic în prezentarea datelor supuse operaŃiilor de centralizare şi agregare.

Figura 4. 10 Macheta tabelei pivot

Cele patru secŃiuni de sintetizare care formează structura tabelei pivot sunt: �� Row Labels aplică un format vertical tabelei pivot, rezumând datele de sus în jos,

elementele rubricii de sintetizare fiind afişate în prima coloană a tabelei;



�� Column Labels aplică un format orizontal tabelei pivot, rezumând datele de la stânga la dreapta. În acest caz, elementele rubricii de sintetizare vor fi plasate pe prima linie a tabelei;

�� Report Filter generează un meniu derulant în partea superioară a tabelei care permite selectarea elementelor ce vor restricŃiona în raport de anumite rubrici ale bazei de date;

�� ΣΣΣΣ Values permite definirea rubricilor asupra cărora se vor opera agregări şi calcule sub formă de totaluri, medii, număr de elemente, minim, maxim etc., cu specificaŃia că funcŃia implicit setată este cea de totalizare (funcŃia SUM).

Gruparea informaŃiilor pe mai multe niveluri de centralizare permite obŃinerea unei viziuni mai sintetice, existând posibilitatea grupării mai multor rubrici din baza de date, pe linie sau pe coloană, pentru a obŃine o agregare în trepte a informaŃiilor. FaŃă de tabela de ipoteze tabela pivot permite agregarea „în cascadă” a mai multor sintetizări la nivel de linie, coloană sau raport. De exemplul în Figura 4. 11 se realizează o sintetizare însumând valorile rubricii „Valoare vânzări” potrivit criteriilor de grupare „Titlu Carte” şi „Agent vânzare” (potrivit demersului logic prin care un titlu de carte este distribuit de către mai mulŃi agenŃi de vânzare, maximul de generalitate fiind „Titlu Carte”).

Figura 4. 11 Tabel pivot cu distribuŃia pe titlu carte şi pe agent vânzare

Fiecare rubrică de sintetizare poate fi restricŃionată la nivelul valorilor selective ale domeniului de definiŃie aferent rubricii respective, acŃionând în partea superioară a casetei de dialog Pivot Table Field List se activează o casetă de dialog prin care se pot activa opŃiuni de sortare şi de restricŃionare a valorilor domeniului ca în Figura 4. 12.

Figura 4. 12 Casetă de opŃiuni de restricŃionare a tabelului pivot

Un alt mod de generare a tabelei pivot respectă demersul clasic, compatibil cu versiunile de Excel 2003 şi 2000. O astfel de generare a tabelei pivot pleacă tot de la validarea casetei de dialog Create Pivot Table, dar modul de agregare a rubricilor de sintetizare se face direct într-o machetă a foii de calcul. Afişarea acestei machete în procesul de creare a sintetizării de date este rezultatul succesiunii comenzilor: tabul principal Pivot Table → tabul contextual Options → grupul de opŃiuni Options → butonul Options →


caseta de dialog Pivot table options → fişa Display → caseta de validare Clasic Pivot Table Layout. Rubricile de sintetizare vor fi plasate în macheta tabelei, folosind tehnica Drag&Drop, care constă în deplasarea cu mouse-ul a butoanelor de validare (din caseta Pivot Table Field List) reprezentând câmpurile bazei de date, către macheta de sintetizare. Macheta conceptuală a tabelei pivot în format clasic este ilustrată în Figura 4. 13.

Figura 4. 13 Structura conceptuală a machetei pivot în format clasic Din punct de vedere al structurii machetei pivot pot fi identificate tot cele patru zone de date: �� Drop Column Fields Here aplică un format orizontal tabelei; �� Drop Row Fields Here aplică un format vertical tabelei; �� Drop Page Fields Here generează un meniu derulant ca va restricŃiona afişarea tabelei pivot, în raport de anumite elemente selectate din rubrica de sintetizare; �� Drop Data Items Here permite definirea câmpurilor asupra cărora se vor opera agregări şi calcule. Ştergerea unei rubrici de sintetizare se execută prin dezactivarea casetei de validare din dreptul rubricii de şters. O altă modalitate de anulare a unei rubrici de sintetizare constă în eliminarea acesteia din secŃiunea corespunzătoare (Column Labels, Row Labels sau Report Filter). Eliminarea din această secŃiune se face fie prin glisare cu mouse-ul în afara secŃiunii, fie prin activarea rubricii de sintetizare şi validarea acŃiunii prin comanda Remove Field. În modul clasic de afişare a tabelei pivot dacă se doreşte eliminarea unor rubrici, acestea vor fi glisate din zona machetei în afara acesteia, alături de celelalte rubrici care nu participă la sintetizarea informaŃiei. Principalele opŃiuni de afişare ale unei tabele pivot sunt disponibile la nivelul tabului contextual Design – grupurile de opŃiuni Layout, Pivot Table Style Options şi Pivot Table Styles şi vizează opŃiuni de afişare, inhibare a vizualizării subtotalurilor sau totalului general, de organizare şi prezentare a ierarhiei grupurilor.

�� Întrebări 1. Care sunt modalităŃile de consolidare a datelor? 2. Care este funcŃia cea mai utilizată în cadrul sintetizării de tip Subtotal? 3. Care sunt funcŃiile utilizate în cadrul sintetizării de tip Consolidate? 4. Cum trebuie pregatite datele pentru a realiza sintetizarea de tip Subtotal?

Drag&Drop



�� Teste

Test A Fiind date următoarele informaŃii (Figura 4. 14) despre vânzările de cărŃi în oraşele: Bucureşti, Cluj şi Iaşi, să se realizeze o consolidare pentru aflarea numărului mediu de exemplare vândute din fiecare titlu de carte pentru cele 3 oraşe:

Figura 4. 14 Vânzările de cărți pe orașe

Test B Realizându-se o calculaŃie a costurilor ilustrat în Figura 4. 15 se doreşte ca să se ajungă la un cost de 2300 ron.

Figura 4. 15 CalculaŃia de cost

Test C Fiind dată Baza de date ”Editura” A3:I60 să se afişeze numărul de cărŃi (total cantitate vândută) şi media valorică a acestora, pe titluri de carte şi pe agenŃi de distribuŃie.

4.2 Instrumente de simulare şi optimizare a informaŃiei


Tehnica simularii prin controlul de tip Spinner O tehnică de simulare presupune incrementarea secvenŃială a salariului de încadrare prin intermediul unui control de tip spinner, pentru a se urmări mărimea salariului datorat. Controlul de tip spinner este un control de tip formular regăsit la nivelul grupului de opŃiuni Developer. Implicit, acest tab nu este afişat de către aplicaŃia Excel 2007. Pentru vizualizarea sa se apelează opŃiunile aplicaŃiei (butonul Excel Options din meniul Office), unde în categoria de proprietăŃi Popular se bifează opŃiunea Show Developer tab in the Ribbon.

Figura 4. 16 Alegerea butonului Spinner Pentru utilizarea controlului Spinner se va alege din butonul Insert al grupului Controls opŃiunea Spin Button (Form control) - Figura 4. 16, care permite desenarea acestuia pe suprafaŃa foii de calcul. Acest control, la fiecare activare a părŃii superioare (triunghi cu vârful în sus), incrementează valoarea (salariului de încadrare) cu o raŃie de 100 lei, iar la fiecare activare a părŃii inferioare (triunghi cu vârful în jos), decrementează valoarea cu aceeaşi sumă. Simularea efectuată prin butonul (controlul) de tip spinner se propagă prin intermediul formulelor până la nivelul salariului net datorat care reprezintă obiectivul simulării. Pentru mai buna vizualizare a efectului simulării, s-a generat o reprezentare grafică a salariului de încadrare, totalului salariului brut, venitului impozabil, impozitului, salariului net şi a salariului datorat, elemente ce îşi vor modifica dinamic valorile şi mărimile histogramelor în funcŃie de acŃionarea progresivă sau degresivă a butonului spinner.

Figura 4. 17 Simulare cu ajutorul unui buton spinner

Legătura dintre celula care trebuie modificată şi controlul Spinner se va realiza prin proprietăŃile acestuia, prin efectuarea unui clic dreapta, apoi alegerea opŃiunii Properties. În caseta de dialog Format Control, fişa Control se stabilesc (Figura 4. 18): • valoarea minimă a parametrului simulării (Minimum value); • valoarea maximă până la care poate ajunge parametrul simulat (Maximum value); • valoarea incrementală (Incremental change), adică valoarea care se adaugă succesiv parametrului iniŃial (salariul de încadrare), până la atingerea plafonului maxim; • adresa celulei pentru care valoarea iniŃială din model se va modifica cu valoarea incrementală (Cell link).

Control spinner Celula modificată: D4



Figura 4. 18 ProprietăŃile controlului Spinner

Simularea unor valori incrementale este operaŃională prin caseta de dialog Format Control până la limita maximă de 30.000 de unităŃi (Minimum value). Pentru simularea unor valori ce depăşesc limita maximă, se operează următorul artificiu: Se desemnează prin caseta de text Cell link o adresă de celulă (în afara modelului) ce conŃine o valoare numerică (care va conŃine rezultatul intermediar al incrementării). Pentru a masca la afişare această valoare intermediară, se recomandă formatarea caracterelor cu culoarea alb.

Valoarea numerică intermediară, se înmulŃeşte cu o constantă ce reprezintă un factor multiplicator, printr-o formulă în celula ale cărei valori se doresc a fi schimbate pentru simulare (salariul de încadrare). (De exemplu: celula D2 ar fi fost stabilită ca celulă de legătură cu controlul Spinner, iar în celula D4, aferentă salariului de încadrare, s-ar fi introdus formula: =D2*100, prin aceasta, la fiecare modificare cu o unitate a valorii generate de Spinner în celula D2, s-ar fi modificat cu 100 de unităŃi valoarea celulei D4 – Salariul de încadrare).

Simularea prin Scenarii

Scenariile reprezintă instrumente de asistare a deciziei care se utilizează pentru a compara anumite valori care generează rezultate diferite. Astfel, se pot modela diverse strategii pentru a analiza avantajele şi dezavantajele diferitelor moduri de abordare pentru anumite probleme date. Scenariile sunt, de fapt, diferite versiuni ale aceleiaşi situaŃii de calcul, fiecare dintre acestea conŃinând valori schimbate ale unor date de intrare pentru situaŃia dată. Fiecare set de valori care se presupune a constitui un anumit scenariu poate fi stocat separat în cadrul registrului de lucru, astfel încât să permită, la un moment dat, generarea de rapoarte comparative. Pentru adăugarea, modificarea, ştergerea scenariilor definite şi afişarea de rapoarte concluzive, aplicaŃia Microsoft Excel deŃine „Gestionarul de Scenarii” (Scenario Manager). Modelul de simulare conŃine mai multe ipoteze ce conduc la rezultate diferite prin intermediul valorilor luate de anumiŃi parametrii. Pentru exemplificare, să considerăm următoarea situaŃie:


Figura 4. 19 SituaŃia cheltuielilor de producŃie şi a vânzărilor lunare

Din structura observăm că pot fi considerate variabile de intrare (relative cu modul de calcul al profitului) adaosul comercial şi cantităŃile vândute din fiecare produs, celelalte date având la bază formule de calcul. Tehnica scenariilor presupune ca una sau mai multe celule de intrare (Changing Cells) să prezinte valori schimbate faŃă de situaŃia iniŃială, pentru a observa impactul acestor modificări asupra rezultatului. De aceea, vom defini două scenarii privind cele două variabile de intrare pentru primele două produse comercializate:

Tabel 4. 1 Variante de scenarii Denumire variabilă Scenariu optimist Scenariu pesimist

Adaos comercial

Produs 1 48% 43%

Produs 2 53% 44%

Cantitate vândută

Produs 1 120 110

Produs 2 110 80

Cum ar putea afecta aceste modificări profitul obŃinut? Care ar fi noile valori privind cheltuielile şi veniturile totale? Celulele modificabile nu trebuie să conŃină formule, ci doar constante numerice, care să conducă nemijlocit la un rezultat simulat. Generarea scenariilor este posibilă prin intermediul comenzii Data�� Data Tools �� What-If Analysis �� Scenario Manager…

Figura 4. 20 Apelarea gestionarului de scenarii Fereastra Scenario Manager afişează, la un

moment dat, toate scenariile definite până în momentul respectiv, permiŃând operaŃii de adăugare, modificare şi ştergere a fiecărui scenariu în parte, dar şi operaŃii de vizualizare a datelor modificate


(Show), de fuzionare (Merge) sau de raportare a rezultatelor (Summary). Pentru construirea efectivă a unui scenariu se selectează celulele care conŃin valori modificabile (Changing Cells), ce participă la formarea unui rezultat final şi care, pe parcursul simulării, admit valori diferite, care generează variante de rezultate. Apăsarea butonului Add din cadrul casetei Managerului de Scenarii permite definirea scenariului.

După introducerea numelui scenariului şi a celulelor ce se vor modifica (Figura 4. 22Error! Reference source not found.), se va deschide (la apăsarea butonului OK) fereastra de introducere a valorilor (cele din Error! Reference source not found.) pe care scenariul le presupune (Figura 4. 21). Aceeaşi paşi se vor parcurge şi pentru introducerea informaŃiilor cu

privire la cel de-al doilea scenariu:

Figura 4. 22 Fereastra de definire a scenariului pesimist

Figura 4. 23 Introducerea valorilor aferente scenariului Pesimist

Revenirea la managerul de scenarii se face prin activarea butonului OK, iar adăugarea unor noi variante de buget se operează prin butonul Add. După specificarea condiŃiilor necesare pentru afişarea rezultatelor scenariilor, se va apăsa butonul Summary din cadrul ferestrei principale Scenario Manager. AplicaŃia Excel va afişa o fereastră prin care va întreba utilizatorul care este celula (celulele) rezultat, precum şi prin ce tip de raport se vor afişa soluŃiile găsite.

Figura 4. 24 Precizarea celulelor rezultat şi a tipului de raport de analiză ce va fi generat

În caseta de dialog Scenario Summary utilizatorul poate preciza un tip de raport ce se doreşte a fi generat (Report type): tip sinteză a scenariilor (Scenario summary) sau tip

tabelă pivot (Scenario Pivot Table report). Managerul de scenarii este capabil să afişeze o analiză de sinteză în cadrul unei noi foi de calcul, prin care se poate vizualiza impactul pe care aceste modificări prezumtive pot afecta rezultatele specificate (Venitul total, Cheltuielile totale şi Profitul – celulele G21:G23 (Figura 4. 25). În plus, raportul de sinteză a scenariilor (Scenario Summary) afişează în prima sa coloană cu date, valorile iniŃiale ale modelului de simulare (Current Value). Pentru ca valorile iniŃiale ale parametrilor simulării să se păstreze, este necesar a nu se valida opŃiunea Prevent Changes (aferentă casetei de dialog Add / Edit Scenario) în momentul definirii unui scenariu.

Noile valori

ale

variabilelor

de intrare



Figura 4. 25 Raportul de sinteză generat de managerul de scenarii

În noua foaie de calcul apar, în stânga raportului, mici butoane de afişare (+) sau de inhibare (-) a unor detalii: • butonul “+” (plus) în linia de antet (Scenario Summary) determină afişarea comentariilor declarate în caseta de dialog Add Scenario, numele autorului scenariului şi data sistem când a fost creat scenariul; • butonul “-“ (minus) în zona Changing Cells inhibă afişarea celulelor modificabile, adică a parametrilor simulării; • butonul “-“ în zona de rezultate (Results Cells) inhibă afişarea rezultatelor simulării. Pentru a identifica diferitele scenarii aflate în foile de calcul tabelar se foloseşte metoda căutării acestora prin caseta de dialog Scenario Manager, butonul Merge… Caseta de dialog Merge Scenarious (Figura 4. 26Error! Reference source not found.) permite identificarea scenariilor în mai multe documente Excel (rubrica Book:) şi în mai multe foi de calcul, prin lista derulantă a rubricii Sheet.

Figura 4. 26 Căutarea scenariilor în foile de calcul

După ce a fost construit un scenariu, acesta poate fi exploatat şi actualizat oricând prin comanda Data ��Data Tools �� What-If Analysis��Scenario Manager…

Optimizarea prin metoda Solver

Solverul sau “rezolvătorul de probleme” este un instrument informatic de optimizare care generalizează tehnica valorii scop, oferind mai multe posibilităŃi de simulare a unor parametrii ce generează o situaŃie de optim. Prin solver se pot face simulări mai complexe asupra unor informaŃii conŃinute în celule şi se pot lua în consideraŃie mai multe restricŃii impuse unui model de optimizat. Ca principiu, o problemă de optimizare rezolvată prin Excel presupune automatizarea unei aplicaŃii de programare lineară (algoritmul SIMPLEX) şi anume ajungerea la o soluŃie optimă în sensul maximizării unor rezultate, minimizării unor eforturi sau de atingere a unei valori-scop considerate ca fiind de referinŃă. Atingerea situaŃiei de optim se face prin modificarea automată a unor parametrii ce conduc la atingerea scopului propus, în condiŃiile



precizării unor restricŃii impuse modelului, astfel încât situaŃia optimală să ia în considerare aceste constrângeri sau restricŃii. Pentru transpunerea unei probleme de programare lineară într-un model apt a fi rezolvat prin tehnica solver-ului în Excel, se impune: � stabilirea informaŃiei ce urmează a fi optimizată; � stabilirea tipului de optim (maximizare, minimizare, atingerea unei valori); � stabilirea parametrilor modificabili, care prin variaŃia lor să producă optimul; � stabilirea unor restricŃii aplicate parametrilor modificabili sau oricăror informaŃii legate de generarea stării de optim. Pentru înŃelegerea modului de rezolvare a problemelor cu ajutorul solver-ului, furnizăm următoarea aplicaŃie: Utilizând situaŃia cheltuielilor de producŃie şi a structurii vânzărilor de produse aferente unei întreprinderi, să se determine care este varianta optimă pentru realizarea obiectivului de maximizare a profitului prin modificarea cheltuielilor auxiliare, cheltuielilor materiale, cheltuielilor cu personalul şi a numărului de produse obŃinute pentru fiecare sortiment în parte, Ńinând cont de următoarele restricŃii: • Cheltuielile materiale pentru un anumit produs trebuie să se încadreze între 100 şi 420 u.m.; • Cheltuielile cu personalul stabilite pentru realizarea unui anumit produs sunt evaluate ca fiind situate în intervalul 100 şi 300 u.m.; • Numărul de unităŃi realizate din fiecare produs trebuie să fie minim 100 şi maxim 400; • Cantitatea vândută din fiecare produs nu trebuie să depăşească numărul de unităŃi realizate din produsul respectiv. • Cuantumul cheltuielilor auxiliare nu pot depăşi valoarea totală a cheltuielilor directe, dar nici nu pot fi mai mici decât o treime din valoarea acestora. În Figura 4. 27 se prezintă situaŃia iniŃială a cheltuielilor de producŃie repartizate pe fiecare produs şi a desfacerilor, afişând pe fundal gri acele celule care pot fi modificate de către instrumentul de optimizare, pentru aflarea profitului maxim prin respectarea restricŃiilor enunŃate mai sus. Cele două situaŃii tabelare expuse sunt legate prin denumirile de produse, dar şi prin preŃurile unitare aferente acestora, ce vor trebui preluate din tabelul ProducŃie.

Figura 4. 27 Structura cheltuielilor de producŃie şi a

desfacerilor (situaŃie iniŃială)

Cheltuielile de producŃie se calculează pentru fiecare produs în parte prin însumarea cheltuielilor directe cu cele indirecte: de conducere şi auxiliare (repartizate procentual în funcŃie de ponderea lor în costurile directe pe fiecare produs). Cheltuielile unitare pentru fiecare produs se calculează prin împărŃirea cheltuielilor de producŃie totale la numărul de unităŃi produse din fiecare sortiment şi ele vor constitui preŃul de producŃie luat în calcul la stabilirea preŃului de vânzare pentru activitatea de desfacere a produselor. În Figura 4. 28 se expune modul de calcul pentru situaŃia descrisă, prin afişarea formulelor aferente fiecărei celule ce prezintă o valoare calculată.



Figura 4. 28 Formulele utilizate în modelul de optimizare

Modificarea valorilor unor parametri, precum Cheltuieli materiale, Cheltuieli de

personal, Nr unităŃi produse, va afecta, pe de o parte, totalul cheltuielilor, cu influenŃe directe asupra profitului (calculat după formula TotalVenituri - TotalCheltuieli), iar pe de altă parte, cheltuiala unitară pe produs, care va reprezenta preŃul de producŃie, ce influenŃează profitul obŃinut, prin preŃul de vânzare stabilit la nivel de produs. Pentru o mai bună înŃelegere a problemei de optimizat, este bine ca, în prealabil, să atribuim nume zonelor utilizate ca parametri din cadrul situaŃiei iniŃiale. Vom redenumi foaia de calcul curentă „Solver”, iar celulele pe care modelul de optimizare le va utiliza, vor primi denumiri sugestive, fie individual, fie în grup (Figura 4. 29). În se afişează fereastra Name Manager ce prezintă denumirile folosite la nivelul foii de calcul Solver pentru celulele identificate pe coloana Refers To.

Figura 4. 29 Vizualizarea denumirilor atribuite zonelor de celule în fereastra Name Manager

Optimizarea modelului presupune în primul rând stabilirea unui obiectiv. În studiul de caz prezentat, obiectivul propus vizează maximizarea profitului (celula G23). Pentru rezolvarea problemei de optimizare, în sensul maximizării valorii celulei rezultat, se poziŃionează cursorul pe celula care conŃine obiectivul (G23) şi se activează comanda de deschidere a instrumentului Solver. (Data �� Analysis �� Solver). Dacă opŃiunea Solver nu este afişată în caseta Analysis, poate fi adăugată prin parcurgerea următorilor paşi: • Se apasă butonul Office Button; • Se apelează opŃiunile Excel prin apăsarea butonului Excel Options; • Se selectează grupul de opŃiuni Add-Ins; • În partea de jos a casetei se apasă butonul Go… dacă în caseta adiacentă lui este selectată opŃiunea Excel Add-Ins; • În fereastra care apare se bifează opŃiunea Solver Add-In, validându-se prin apăsarea butonului OK.



SemnificaŃia rubricilor şi butoanelor casetei de dialog Solver Parameters, ce se deschide imediat după apelarea instrumentului Solver, este următoarea: � Set Target Cell: specifică adresa celulei care conŃine scopul sau obiectivul optimizării. Celula ce conŃine scopul trebuie să fie în mod obligatoriu o formulă; � Equal To: stabileşte sensul optimizării potrivit scopului propus, şi anume: maximizare (Max), minimizare (Min) sau atingere a unei valori obiectiv (Value of:); � By Changing Cells declară toate celulele care nu conŃin formule şi au legătură cu scopul optimizării, astfel se selectează toate celulele ce conŃin parametrii numerici la care formula din celula ce defineşte obiectivul (Target Cell) face referire; � Guess propune ca celule modificabile toate celulele care nu conŃin formule de calcul şi plasează referinŃele acestora în zona celulelor variabile (By changing Cells); � Subject to the Constraints: � conŃine restricŃiile impuse modelului de optimizare; � Butonul Add � permite adăugarea restricŃiilor; � Butonul Change �� permite modificarea restricŃiilor; � Butonul Delete ��permite ştergerea restricŃiilor; � Butonul Solve �� lansează procesul de rezolvare a problemei de optimizare; � Butonul Options �� afişează o casetă de dialog prin care se pot controla caracteristici avansate ale procesului de rezolvare şi de schimbare sau înregistrare a specificaŃiilor pentru o problemă particulară; � Reset All � anulează parametrii atribuiŃi şi restabileşte opŃiunile implicite. În caseta de dialog Solver Parameters se stabilesc următoarele elemente: celula ce conŃine obiectivul sau scopul (G23 denumită Profit_Obtinut), sensul optimizării (Max) în rubrica Equal To: celulele modificabile sau ajustabile şi restricŃiile impuse modelului.

Figura 4. 30 Elementele casetei Solver

Adresele celulelor modificabile se pot introduce de la tastatură, prin precizarea numelor definite anterior, separate prin semnul ; sau pot fi selectate prin apăsarea concomitentă a tastei Control (selecŃie multiplă). Prin opŃiunea By Changing Cells utilizatorul selectează toate celulele ce conŃin parametrii numerici la care formula din celula care defineşte obiectivul (Target Cell) face referire: Cheltuieli_Materiale;

Cheltuieli_Personal; Unitati_Produse; Cheltuieli_auxiliare (Figura 4. 30). RestricŃiile modelului de optimizare pot influenŃa atât celule modificabile, cât şi alte celule ce conŃin formule cu incidenŃă directă sau indirectă asupra scopului sau obiectivului. Declararea restricŃiilor presupune deci, compararea celulelor ce influenŃează rezultatul cu parametrii constanŃi sau cu formule. RestricŃiile impuse asupra sistemului trebuie adăugate pe rând, prin apăsarea butonului Add şi specificarea criteriilor corespunzătoare în cadrul casetei Add Constraint.



Fereastra pentru adăugarea restricŃiilor

Următorul tabel explică cum vor fi stabilite condiŃiile impuse pentru problema de optimizare expusă:

Tabel 4. 2 RestricŃiile modelului de optimizare pornind de la cerinŃele problemei CondiŃii enunŃate RestricŃii Solver

Cheltuielile materiale pentru un anumit produs trebuie să se încadreze între 1.000 şi 400 u.m.

Cheltuieli_materiale<=400 Cheltuieli_materiale>=1.000

Cheltuielile cu personalul stabilite pentru realizarea unui anumit produs sunt evaluate ca fiind situate în intervalul 1.000 şi 3.000 u.m.

Cheltuieli_de_personal<=3.000 Cheltuieli_de_personal>=1.000

Numărul de unităŃi realizate din fiecare produs trebuie să fie minim 100 şi maxim 400

UnităŃi_produse>=100 UnităŃi_produse<=400

Cantitatea vândută din fiecare produs nu trebuie să depăşească numărul de unităŃi realizate din produsul respectiv

$C$12>=$F$16 $D$12>=$F$17 $E$12>=$F$18 $F$12>=$F$19 $G$12>=$F$20

Cuantumul cheltuielilor auxiliare nu pot depăşi valoarea totală a cheltuielilor directe, dar nici nu pot fi mai mici decât o treime din valoarea acestora

Cheltuieli_auxiliare<=$H$8 Cheltuieli_auxiliare>=1/3*$H$8

Dacă nu se precizează un sistem corect de restricŃii, solver-ul calculează algoritmul optim prin maximizarea „către infinit” a valorilor aditive ale modelului (resursele) şi prin minimizarea „către infinit” a valorilor substractive (consumurile). După completarea rubricilor casetei de dialog Solver Parameters şi acŃionarea butonului Solve, se declanşează iterativ procedura de optimizare. La final, când problemei i s-a găsit (sau nu i s-a găsit soluŃia) se afişează caseta de dialog cu rezultatele optimizării (Solver Results). Rezultatul optimizării (Figura 4. 31) presupune fie înlocuirea vechilor parametrii cu alŃii noi, găsiŃi în procesul de optimizare şi implicit transformarea valorii scop existente într-una optimizată (Keep Solver Solution), fie restaurarea parametrilor originali şi păstrarea vechiului rezultat (Restore Original Values). SoluŃia găsită se poate salva într-un scenariu (alături de cele existente), activând butonul Save Scenario…

Figura 4. 32 Caseta de dialog Solver Results

Rezultatele optimizării sunt prezentate în Figura 4. 33, putându-se observa că instrumentul Solver a identificat o valoare maximă a profitului obŃinut de 22.788 u.m., la care se poate ajunge prin respectarea condiŃiilor impuse şi, totodată, a ajustat şi valorile celulelor modificabile în sensul în care acestea vor conduce la obŃinerea rezultatului identificat.



Figura 4. 33 Rezultatele optimizării prin Solver pentru maximizarea profitului

Valorile optimale ale rezultatului solver-ului pot fi sintetizate, dacă se alege un tip special de raport din caseta de dialog Solver Results, rubrica Reports, astfel: • Raportul de tip “Answer”: afişează valoarea celulei obiectiv, celulele variabile cu valorile lor iniŃiale şi finale, restricŃiile modelului de optimizat, precum şi informaŃiile legate de aceste restricŃii (Figura 4. 34); • Raportul de tip “Sensitivity”: furnizează informaŃii asupra sensibilităŃii sau elasticităŃii modelului de optimizare, adică variaŃia soluŃiei faŃă de cele mai mici modificări aduse formulei din zona Set Target Cell. Altfel spus, cu cât se modifică soluŃia la schimbarea cu o unitate a parametrilor ce conduc la obiectivul fixat; • Raportul tip “Limits”: afişează şi limitele superioare şi inferioare ale modelului.

Figura 4. 34 Raportul Solver-ului privind optimizarea

Folosirea Tabelei de ipoteze

Tabela de ipoteze reprezintă acea facilitate de sintetizare a informaŃiilor ce permite obŃinerea mai multor serii de rezultate, prin calcule aplicate unor serii de valori, dispuse



monodimensional sau bidimensional, de unde şi de numirea de tabel de ipoteze cu o singură variabilă(dispusă pe linie sau pe coloană) sau cu două variabile. În fapt, tabelele de ipoteze permit crearea de rapoarte de sinteză pentru bazele de date. O tabelă de ipoteze se compune din următoarele elemente: una sau două celule de intrare; una sau mai multe formule care au incidenŃă asupra celulelor de intrare; un câmp numit “câmp de ipoteze” cu una sau două dimensiuni în care prima coloană sau prima linie conŃine valorile asupra cărora se aplică efectiv formulele de calcul. Procedura de sintetizare a informaŃiei prin tabelele de ipoteze se declanşează prin execuŃia comenzii What-If Analysis → Data Table din tabul Data.

O tabela de ipoteze cu o singură variabilă permite diseminarea rezultatului uneia sau a mai multor formule, în funcŃie de diferite valori luate de un parametru. Tabela de ipoteze monodimensională este compusă din următoarele elemente: � Celula de intrare, situată oriunde în foaia de calcul (dar mai puŃin în câmpul de ipoteze), definită şi referită de utilizator în caseta de dialog Data Table, corespunzător rubricilor Row Input Cell sau Column Input Cell, după cum se doreşte a se returna rezultatul sintetizării pe linie sau pe coloană. Celula de intrare poate fi vidă, aceasta fiind situată într-un câmp de criterii, atunci când formula generatoare de rezultate este tip bază de date (funcŃie agregată DataBase). În cazul utilizării altor funcŃii decât cele de tip baze de date, celula de intrare poate conŃine unul din argumentele funcŃiei respective.

� Câmpul de ipoteze conŃine rezultatele calculate prin aplicarea unei formule pentru o serie de date. Prima coloană a tabelei sau prima linie a acesteia poate conŃine valorile testate prin formulă(e). Respectivul câmp va fi selectat de către utilizator înainte de activarea comenzii Data → What-If Analysis → Data Table. � Formula generatoare de rezultat(e) sintetizat(e) este plasată pe prima linie (sau prima coloană) a câmpului de ipoteze. Formula poate fi de tip DataBase sau se poate baza pe un interval de variaŃie (cum ar fi funcŃiile financiare: PMT, SYD, SLN, DB, DDB etc). O tabelă de ipoteze cu o singură variabilă poate admite mai multe formule generatoare de rezultate sintetice. Celula din colŃul superior stâng al câmpului de ipoteze nu intervine în calcule, conŃinutul său poate fi lăsat la libera iniŃiativă a utilizatorului. Comanda Data → What-If Analysis → Data Table permite completarea automată a tabloului cu valori calculate ca şi cum formula sau formulele situate pe prima linie ar fi aplicate succesiv pentru fiecare valoare găsită în prima coloană sau în prima linie (aceste valori reprezintă parametrii sintetizării informaŃiei). Din punctul de vedere al organizării datelor în foaia de calcul, parametrii sintetizării pot fi dispuşi pe linie sau pe coloană. Schema de principiu a unei tabele de ipoteze cu o singură variabilă, cu parametrii dispuşi pe coloană, este prezentată în Figura 4. 35.



Figura 4. 35 Schema conceptuală a tabelei de ipoteze cu o singură variabilă Pentru înŃelegerea mecanismului de sintetizare a informaŃiei prin tabela de ipoteze cu o variabilă, se furnizează următorul exemplu: Plecând de la o bază de date pentru evidenŃa personalului unei societăŃi comerciale, se doreşte a se calcula (printr-o tehnică de sintetizare) suma şi media salariilor pe departamente funcŃionale. Tabelul ce conŃine baza de date – ilustrat în Figura 4. 36 - este numit „Personal” şi este definit pe coordonatele A4:F30. Pentru a răspunde cererii de sintetizare, este necesar a se construi în prealabil o interogare asupra bazei de date prin care să se extragă toate departamentele (cu valoare unică). Astfel, în câmpul de criterii va figura rubrica „Departament” şi o celulă vidă. Înregistrările care vor fi filtrate potrivit restricŃiei specificate în câmpul de criterii, vor fi extrase ca elemente unice într-un câmp de rezultate. Acesta va fi situat într-o altă locaŃie a foii de calcul şi va conŃine rubrica „Departament”, necesară constituirii parametrului sintetizării pentru tabela de ipoteze (extragerea se face prin comanda Data�� Advanced marcând în caseta de dialog Advanced Filter caseta de validare Unique Records Only). Câmpul de criterii (B34:B35), aferent operaŃiei de interogare, precum şi câmpul de rezultate, sunt ilustrate în Figura 4. 37. După ce comanda de filtrare a înregistrărilor a fost executată, iar departamentele au fost extrase din baza de date, se procedează la construirea „câmpului de ipoteze”, prin editarea formulelor de sintetizare (funcŃiile DSUM şi DAVERAGE) în celulele situate în prima linie a acestui câmp (C39 şi D39).



Figura 4. 36 Baza de date "Personal” Formulele se vor aplica succesiv pentru fiecare departament în parte, însumând şi calculând media salariile corespunzătoare acestora. Rezultatele sintetizării se generează prin selectarea „câmpului de ipoteze” (B39:D47) şi activarea comenzii Data→What-If Analysis→Data Table.

Figura 4. 37 Extragere de date pe baza valorilor unice

Figura 4. 38 Editarea funcŃiilor agregate

În caseta de dialog Data Table (Figura 4. 39) se completează, în rubrica Column input cell, adresa celulei de intrare (celula vidă – B35) aferentă câmpului de criterii (B34:B35). Rubrica Column input cell admite celula de intrare ce corespunde unui parametru desfăşurat (orientat) pe coloană. Prin validarea operaŃiei de sintetizare cu butonul OK, calculele se vor efectua automat pentru fiecare departament în parte. Figura 4. 39 ilustrează modul de obŃinere a rezultatelor sintetizate prin tabela de ipoteze cu o variabilă.



Figura 4. 39 Sintetizare prin tabela de ipoteze cu o variabilă

Un alt exemplu (ilustrat în Figura 4. 40 Error! Reference source not found.), derivat din primul, calculează numărul de salariaŃi pe fiecare compartiment în parte. Datele vor fi organizate pe linie în „câmpul de ipoteze” (orizontal). Datorită acestui fapt, în caseta de dialog Data Table va fi completată adresa celulei de intrare în rubrica Row input cell. FuncŃia de sintetizare utilizată pentru generarea rezultatelor este DCOUNT.

Figura 4. 40 Tabela de ipoteze dispusă orizontal Un alt gen de aplicaŃii pentru sintetizarea informaŃiei prin tabela de ipoteze cu o variabilă, vizează generarea de rezultate prin funcŃiile financiare. De exemplu, dacă s-ar dori calcularea sumelor rambursabile lunar, aferente unui credit de 80.000 lei, contractat pe 5 ani, pentru mai multe variante a ratei a dobânzii, ar fi utilizată funcŃia financiară PMT, a cărei valori ar fi ventilate prin tabela de ipoteze cu o variabilă. Comanda Data→What-If Analysis→Data Table aplicată asupra câmpului de ipoteze A105:B118, prin intermediul unei celule de intrare ce reprezintă rata dobânzii (B98), pentru valorile unui parametru dispus pe coloană (A106:A118) va genera o serie de rezultate ce reprezintă mărimea plăŃii lunare pentru mai multe variante ale ratei dobânzii (Figura 4. 41).



Figura 4. 41 Calculul funcŃiei financiare PMT prin tabela de ipoteze cu o variabilă În contextul utilizării tabelelor de ipoteze, există şi posibilitatea de a aplica funcŃii generatoare de rezultate sintetizate, care să solicite câmpuri de criterii diferite. În această situaŃie, criteriile vor fi plasate de o parte şi de alta a criteriului de bază, cel care furnizează denumirea rubricii pe care se construieşte sintetizarea. O altă formă de aplicare a tabelei de ipoteze constă în generarea, prin alternanŃă, a rezultatelor sintetizării, în acelaşi plan (câmp de rezultate), în raport de funcŃia DataBase aplicată. Modalitatea de rezolvare a acestei cerinŃe se rezumă la plasarea opŃiunilor de sintetizare într-o listă derulantă, din care utilizatorul selectează operaŃia de sintetizare pe care doreşte să o aplice asupra datelor conŃinute de tabelul iniŃial. Pe de altă parte, formula generatoare de rezultate sintetizate va conŃine o structură condiŃională care să permită aplicarea unei anumite funcŃii agregate, în raport de opŃiunea selectată din lista derulată. Figura 4. 42 ilustrează modul de calcul sintetic al salariului minim, maxim şi mediu, pe funcŃii de încadrare, datele sintetizate fiind afişate, în mod dinamic, în acelaşi câmp de rezultate.

Figura 4. 42 Afişarea în acelaşi plan a mai multor serii de rezultate Tabela de ipoteze cu două variabile funcŃionează potrivit aceloraşi principii ca şi tabela cu o singură variabilă, cu deosebirea că permite variaŃia simultană a doi parametri, deci



necesită existenŃa a două celule de intrare în câmpul de criterii, la care dac referire funcŃiile Database. Structura unei tabele de ipoteze cu două variabile este ilustrată în Figura 4. 43 şi cuprinde: � Valorile primului parametru, ce corespunde primei celule de intrare se află în coloana din stânga a câmpului de ipoteze, iar valorile celui de-al doilea parametru, ce corespunde celei de-a doua celule de intrare se află pe prima linie; � Formula pe care este construită sintetizarea se introduce în celula colŃului superior stâng al câmpului de ipoteze, la intersecŃia primului parametru (pe coloană) cu cel de-al doilea parametru (pe linie). În urma execuŃiei comenzii Data Table, câmpul de ipoteze este completat, în mod automat, cu rezultatele calculelor efectuate pentru fiecare linie şi coloană a tabloului de sintetizare. Fiecare intersecŃie a unei linii cu o coloană furnizează rezultatul formulei aplicate respectivelor valori.

Figura 4. 43 Schema conceptuală a tabelei de ipoteze cu două variabile

�� Întrebări 1. Cum se realizează în cazul scenariilor pastrarea valorilor iniŃiale ale parametrilor simulării? 2. Cum poate fi exploatat şi actualizat un scenariu? 3. Cum se gestionează mai bine variabilele în cadrul Solver-ului?

�� Teste Test A



Să se calculeaze pornind de la baza de date din Figura 4. 36 pe departamente funcŃionale, suma salariilor pentru economişti şi numărul total de salariaŃi care au fost angajaŃi în ultimii 10 ani la societatea respectivă. Test B Să se calculează prin sintetizarea informaŃiei din Figura 4. 36 suma salariilor pe funcŃii de încadrare şi pe departamente funcŃionale. Test C O societate comercială fabrică trei produse: Model A, Model B şi model C dispunând de două materii prime: mătase naturală şi mătase artificială. Pentru procesul de fabricaŃie se aprovizionează cu materii rpime astfel: mătase naturală în cantitate de 800 m şi mătase artificială în cantitate de 1200 m. Pentru a produce Modelul A sunt necesari 2 m mătase naturală şi 1 m mătase artificială, iar pentru a produce Modelul B sunt necesari 2,5 m mătase naturală şi 2 m mătase artificială şi pentru a produce modelul C sunt necesari 1 m mătase naturală şi 1,5 m mătase artificială. Se aşteaptă ca de la modelul A să se obŃină un profit de 25 u.m., de la de la modelul B să se obŃină un profit de 18 u.m., de la de la modelul C să se obŃină un profit de 21 u.m. Să se realizeze maximizarea profitului total pe societate, pentru cele trei produse.



�� Răspunsuri şi indicatii la întrebări şi teste

IndicaŃii la Testul A de la Tabele de ipoteze

Plecând de la principiul enunŃat anterior, funcŃiile agregate DSUM şi DCOUNT vor funcŃiona pe restricŃii diferite, astfel: operaŃia de însumare se va realiza numai pentru funcŃia de încadrare economist (sau pentru o altă funcŃie de încadrare, aleasă dintr-o listă derulantă (de tip Data Validation → List), iar calculul numărului de salariaŃi se va baza pe un criteriu calculat, pornind de la data angajării salariatului.

Figura 4. 44 Tabela de ipoteze construită pe criterii selective Aşa cum se observă în Figura 4. 44Error! Reference source not found., asupra rezultatelor sintetizării (câmpul D147:E155) s-a aplicat o formatare personalizată, prin care s-a utilizat ca separator de mii „punctul”, iar valoarea nul a fost înlocuită cu „cratima” (Home � Number � Format Cells � Number � Custom).

Figura 4. 45 Tip de formatare personalizată

IndicaŃii la Testul A de la Tabele de ipoteze

Pentru rezolvarea aplicaŃiei se parcurg următoarele etape:



� se construieşte tabela de ipoteze, completând pe prima linie funcŃiile salariaŃilor (cu valoare unică) pentru care se face sintetizarea prin însumare, iar pe prima coloană, departamentele funcŃionale pe care se realizează regruparea datelor;

� se editează câmpul de criterii pe coordonatele B200:C201, câmp ce va conŃine rubricile: “Departament” şi “FuncŃie”;

� se introduce funcŃia de însumare condiŃională DSUM(Personal;6;B200:C201), în colŃul superior stâng al tabelei de ipoteze (la adresa B208). Formula va genera ulterior rezultate centralizate, pentru fiecare funcŃie şi departament în parte;

� se selectează tabela de ipoteze (plaja de celule B208:L217);

� se activează comanda Data Table din grupul What-If Analysis, tabul Data, iar în caseta de dialog Data Table (ilustrată în Figura 4. 46) se editează adresele celulelor de intrare, corespunzător celor doi parametri care variază, astfel:

Figura 4. 46 Construirea tabelei de ipoteze cu două variabile � în rubrica Column input cell se introduce adresa celulei vide corespunzătoare parametrului dispus pe coloană: „Departament” (B201); � în rubrica Row input cell se editează adresa celulei corespunzătoare parametrului dispus pe linie: „FuncŃie” (C201). Prin validarea acŃiunii cu butonul OK, sintetizarea informaŃiei prin însumare se va efectua automat pentru fiecare departament şi funcŃie în parte. Figura 4. 47 prezintă rezultatele agregării datelor, în raport de cei doi parametri.

Figura 4. 47 Rezultatele sintetizării prin tabela de ipoteze cu două variabile Datorită modului în care sunt dispuse valorile parametrilor care variază, formula de calcul se introduce, în mod invariabil, în celula aflată la intersecŃia primei linii cu prima coloană a câmpului de ipoteze, fapt ce restrânge considerabil posibilitatea de a aplica mai multe funcŃii agregate, în acelaşi câmp de ipoteze. Practic, efectuarea mai multor operaŃii de sintetizare asupra aceluiaşi câmp de ipoteze, ar însemna editarea, în mod succesiv, a funcŃiilor agregate ce se doresc a se aplica, în raport de cerinŃele informaŃionale ale utilizatorului, la un moment dat.

Eliminarea acestui inconvenient se poate realiza prin concatenarea într-o celulă a foii de calcul, a mai multor funcŃii care generează rezultate sintetizate diferite. Pentru



exemplificare, se furnizează următorul exemplu ilustrat în Figura 4. 48. „Se va calcula şi afişa, în acelaşi câmp de ipoteze, salariul mediu şi numărul de salariaŃi, în anul în care s-au făcut cele mai multe angajări. Rezultatele sintetizării vor fi afişate respectând următorul format: salariu mediu / număr angajaŃi, iar mesajul de eroare #DIV/! (afişat în cazul în care nu există date pentru calculul mediei) va fi înlocuit cu valoarea nul. În plus, salariul mediu va fi rotunjit la un număr de două zecimale.”

Figura 4. 48 Câmp de ipoteze cu concatenare de funcŃii Database

IndicaŃii la Testul C de la Solver

Se realizează transpunerea modelului în foaia de calcul ca în Figura 4. 49.

Figura 4. 49 Transpunerea modelului în foaia de calcul Se completează caseta de dialog conform Figura 4. 50 având restricŃiile “nu pot produce negativ şi nu pot să consum mai mult decât s-a aprovizionat o societate.”

Figura 4. 50 Casetă Solver Parameters



� Bibliografie

Nr. Crt.

Nume Prenume Titlu Editura

1. Cox Joyce, Preppernau Joan

Microsoft Certified Application Specialist Study Guide 2007 Microsoft Office System

Ed. Microsoft Press, 2008

2. Curtis Frye Microsoft® Office Excel® 2007 Step by Step

Ed. Prentice Hall India, 2007

3. Dodge Mark, Stinson Craig

Microsoft Office Excel 2007 Inside Out

Ed. Microsoft Press, 2007

4. Ionescu Bogdan şi al. Informatica utilizatorului Ed. Infomega, Bucureşti, 2004 pag. 518-573

5. Ionescu Iuliana şi. al. Office 2003 AplicaŃii şi teste rezolvate de WORD şi EXCEL

Ed. Infomega, Bucureşti, 2005, pag. 28-34, 43, 82, 107-108, 119-, 12-125, 130-139, 161, 173-174, 184, 215, 230-231, 242, 254-255,270-274, 286-293.

6. Walkenbach John Microsoft Office Excel 2007 Bible

Ed. Wiley Publishing Inc., 2007

unitatea de invatare 4

Documents