INSTRUMENTE DE SINTETIZARE A INFORMAŢIEI sI DE ASISTARE A DECIZIEI
Procesorul de tabele Excel, alaturi de facilitatile cunoscute de calcul tabelar, de generare de grafice si de exploatare a bazelor de date, poseda mai multe tehnici de prelucrare a datelor prin care informatia poate fi agregata, centralizata, regrupata, simulata sau optimizata.
Aceste facilitati sunt cunoscute sub numele de instrumente de sintetizare a informatiei si de asistare a deciziei si regrupeaza: instrumente de sintetizare (tabelele de ipoteze, tabele pivot, subtotalizari, consolidari), instrumente de simulare si optimizare (scenariile, tehnica valorii scop si solver-ul) si instrumente de previziune (functiile Trend si Forecast, previziuni prin grafice).
1.6.1 Instrumente de sintetizare a informatiei
Procesorul de tabele Excel ofera tehnici si facilitati avansate de regrupare si centralizare a datelor continute în tabele, obtinându-se în urma prelucrarii, noi informatii agregate si sintetizate.
1.6.1.1 Tabelele de ipoteze permit obtinerea mai multor rezultate prin calcule aplicabile mai multor serii de valori, dispuse monodimensional sau bidimensional.
Astfel, prin aplicarea unei singure formule unor serii de valori dispuse pe linie sau pe coloana se poate obtine un numar mare de rezultate care sintetizeaza prin regrupare informatiile dintr-un tablou sau o baza de date. De fapt, tabelele de ipoteze permit crearea de rapoarte de sinteza pentru bazele de date.
O tabela de ipoteze se compune din urmatoarele elemente: una sau 2 celule de intrare; una sau mai multe formule care vizeaza celulele de intrare; un câmp special numit "câmp de ipoteze" cu una sau 2 dimensiuni în care prima coloana sau linie contine valori asupra carora se aplica efectiv formulele.
Atunci când mai multi parametrii participa la elaborarea unui rezultat, câmpurile tabelelor de ipoteze permit evaluarea importantei relative a fiecarui parametru (doi sau mai multi parametrii concura la rezultatul sintetizarii).
Procedura de sintetizare a informatiei prin tabelele de ipoteze se declanseaza prin executia comenzii Data Table.
Fig. 1.122 Tabela de ipoteze cu osingura variabila
Tabela de ipoteze cu o singura variabila sau studiul variatiei unui singur parametru.
O tabela de ipoteze cu o singura variabila permite exploatarea rezultatului uneia sau a mai multor formule, urmarind diferite valori luate de un parametru. Tabela de ipoteze este compusa din:
o celula de intrare, situata oriunde în foaia de calcul (dar mai putin în câmpul de ipoteze), definita si referita de utilizator în caseta de dialog Table, corespunzator rubricilor Row Input Cell sau Column Input Cell dupa cum se doreste a se organiza rezultatul pe linie sau pe coloana. Celula de intrare poate fi vida, ea fiind situata într-un câmp de criterii atunci când formula generatoare de rezultate este tip baza de date (=D). În cazul utilizarii altor functii, altele decât cele baze de date, celula de intrare poate contine unul din argumentele functiei respective.
câmpul de ipoteze contine rezultatele calculate prin aplicarea unei formule pentru o serie de date. Prima coloana a tabelei sau prima linie a acesteia poate contine valorile testate prin formula(e). Respectivul câmp va fi selectat de catre utilizator înainte de activarea comenzii Data Table
formula(formulele) este plasata pe prima linie a câmpului de ipoteze; acestea pot fi de tip baze de date sau formule bazate pe un interval de variatie (de tip PMT, SYD, SLN, DB, DDB etc).
Celula din coltul superior stâng al câmpului de ipoteze nu intervine în calcule, continutul sau poate fi lasat la libera initiativa a utilizatorului.
Comanda Data Table permite completarea automata a tabloului ca si cum formula sau formulele situate pe prima linie ar fi aplicate succesiv pentru fiecare valoare gasita în prima coloana sau în prima linie, aceste valori reprezentand parametrii sintetizarii informatiei.
Pentru întelegerea mecanismului de sintetizare a informatiei se furnizeaza urmatorul exemplu:
Plecând de la o baza de date pentru evidenta personalului unei societati comerciale, se calculeaza suma salariilor pe compartimente functionale. Tabelul ce contine baza de date va fi definit pe coordonatele A1:F14, figura 1.122.
Plecând de la aceeasi baza de date, ne propunem sa calculam suma salariilor pe compartimente functionale numai pentru personalul angajat în cursul ultimului an si care are salariile peste media salariilor, figura 1.123.
Fig. 1.123 Baza de date pentru tabela de ipoteze
Pentru a raspunde acestor restrictii este necesara construirea unei interogari a bazei de date care sa contina în câmpul de criterii conditiile enuntate (cele referitoare la data angajarii si la salariu);
Înregistrarile care vor îndeplini cumulativ cele 2 restrictii ale câmpului de criterii vor fi extrase ca valori unice într-un câmp de rezultate care va fi situat într-o alta locatie a foii de calcul si care va contine acele rubrici necesare pentru definirea t 444d323e abelei de ipoteze (în cazul de fata rubrica "Compartiment";
Extragerea din baza de date se va face prin intermediul functiei DSUM care are ca argumente: baza de date, rubrica pe care se opereaza calculul si câmpul de criterii (în care sunt precizate cele 2 restrictii; Acest exemplu îmbina facilitatile de interogare a bazelor de date prin criterii calculate cu facilitati de sintetizare a informatiei prin regrupare potrivit criteriilor enuntate.
Pentru rezolvarea aplicatiei se parcurg urmatoarele etape:
se construieste câmpul de criterii pentru interogare care poate contine una (compartimentul) sau toate rubricile bazei de date + criteriul calculat (=Data angajarii >TODAY() - 360 si =Salariul >AVERAGE(F2:F14) );
Într-un criteriu calculat, rubrica de comparat este reprezentata de prima celula plasata sub rubrica respectiva. Astfel, în loc de "Data angajarii" se va scrie prima adresa a domeniului, adica C2, iar pentru "Salariul" se va introduce adresa F2.
se vor extrage din baza de date potrivit criteriului specificat acele înregistrari unice care îndeplinesc cumulativ restrictiile enuntate; câmpul de rezultate va fi definit într-o alta locatie a foii de calcul si va deveni ulterior parametrul câmpului de ipoteze pentru care o formula va calcula serii diferite de rezultate (extragerea se face prin Data- Filter- Advanced Filter, marcând caseta se selectare Unique Record Only);
se scrie formula de calcul (=DSUM()) în coltul superior drept al câmpului de ipoteze- formula se va aplica pentru fiecare compartiment în parte însumând salariile pe respectivul criteriu de regrupare;
se selecteaza câmpul de ipoteze
se activeaza comanda Data Table si în caseta Table se completeaza adresa celulei care contine criteriul în rubrica Column Input Cell- tabela de ipoteze fiind orientata în acest caz pe coloana ( în cazul organizarii tabelei pe linie.)
validând cu butonul OK, calculele se vor efectua automat pentru fiecare compartiment în parte, figura 1.124.
Fig. 1.124 Tabela de ipoteze cu o singura variabila
Un alt exemplu derivat din primul, vizeaza calculul mediei salariilor pe compartimente. De aceasta data, datele vor fi organizate orizontal- pe linie- si în consecinta se va completa adresa celulei care contine criteriul în rubrica Row Input Cell aferenta casetei de dialog Table. Figura 1.125 ilustreaza acest exemplu.
Fig. 1.125 Tabela de ipoteze cu osingura variabila (II)
Rezultatele calculului mediei salariilor se gasesc reflectate în figura 1.126.
Fig. 1.126 Rezultatul final (I)
Tabela de ipoteze cu o singura variabila poate permite calcularea mai multor serii de rezultate, utilizând mai multe formule.
Astfel, daca s-ar fi dorit calcularea sumei si mediei salariilor economistilor, tabela de ipoteze ar contine trei coloane, una pentru marcarea valorilor studiate, adica parametrii, a doua pentru rezultatele calculului sumei salariilor si a treia pentru calculul mediei salariilor. Câmpul de criterii (B17:C18) a fost completat cu rubrica "Functia" pentru a sintetiza informatia referitoare la economisti. Tabelul ce contine sursa de date pentru sintetizare a fost declarat la adresa A3:E15. Schema de organizare a informatiilor în tabela de ipoteze este prezentata în figura 1.127.
Tabela de ipoteze -declarata la adresa B20:D25 - va calcula suma si media (prin formulele DSUM si DAVERAGE) salariilor corespunzatoare functiei de economist. În urma comenzii Data Table si precizarea adresei celulei vide (B18) în rubrica Column input cell, se vor genera automat rezultatele (figura 1.128).
Fig. 1.127 Tabela de ipoteze (III)
Un alt gen de aplicatii pentru sintetizarea informatiei prin tabela de ipoteze cu o variabila, vizeaza functiile financiare, de exemplu:
sa se calculeze sumele rambursabile lunar (functia PMT) aferente unui
Fig. 1.128 Rezultatele finale (III)
credit de 80 de milioane lei, contractat pe 3 ani, pentru mai multe variante de rata a dobânzii (aplicatie exemplificata în figura urmatoare), sau sa se calculeze aceleasi sume rambursabile lunar, aferente aceluiasi credit, cu o dobânda fixa de 60%, pentru mai multe variante de perioade de rambursare:
Alte aplicatii economice de sintetizarea informatiei, ce pot fi rezolvate cu tabela de ipoteze cu o variabila, ar putea fi:
se calculeaza prin sintetizarea informatiei maximul, minimul si media salariilor angajatilor cu functia "economist" din compartimentele "financiar" si "contabilitate" (începând din anul 1990) si se reprezinta grafic printr-o diagrama de amplitudine variatia salariilor pe cele doua compartimente;
se reprezinta grafic numarul total de angajati pe fiecare compartiment functional si suma salariilor acestora;
Fig. 1.129 Tabela de ipoteze (IV)
Tabela de ipoteze cu doua variabile
Tabela de ipoteze cu doua variabile functioneaza dupa aceleasi principii ca si tabela cu o variabila, dar permite variatia simultana a doi parametrii, necesitând în acest caz doua celule de intrare.
Structura unei tabele de ipoteze cu doua variabile este urmatoarea:
valorile primului parametru (prima celula de intrare) se afla în coloana stânga a câmpului de ipoteze, iar valorile celui de-al doilea parametru (a doua celula de intrare) se afla pe prima linie;
formula pe care este construita sintetizarea se introduce în celula coltului superior stâng al câmpului de ipoteze ca si când s-ar aplica celor doua celule de intrare.
Atunci când comanda Data Table este executata, câmpul de ipoteze este completat în mod automat cu rezultatele calculelor efectuate pentru fiecare linie si coloana a tabloului. Fiecare intersectie a unei linii cu o coloana furnizeaza rezultatul formulei aplicate valorilor liniei si coloanei.
Pentru exemplificare, vom lua urmatoarea aplicatie:
Se va calcula prin sintetizarea informatiei suma salariilor pe functii si pe compartimente functionale.
Pentru rezolvarea aplicatiei se parcurg urmatoarele etape:
se construieste tabela de ipoteze, completând pe prima linie functiile salariatilor pentru care se face sintetizarea prin însumare si pe prima coloana compartimentele functionale pentru care sa se faca respectiva regrupare;
se editeaza câmpul de criterii pe coordonatele B30:C31, câmp ce va contine rubricile: "Compartiment" si "Functia";
se introduce formula de însumare =SUM(A3:E15;5;B30:C31) în coltul superior stâng al tabelei de ipoteze (la adresa B35). Formula va genera rezultate centralizate pentru fiecare functie si compartiment în parte;
se selecteaza tabela de ipoteze (B35:F40);
se activeaza comanda Data Table si în caseta Table (figura 1.130) se completeaza adresele celulelor de intrare astfel:
în rubrica Column input cell se marcheaza adresa celulei vide corespunzatoare compartimentului, adica B31;
în rubrica Row input cell se marcheaza adresa celulei corespunzatoare functiei, adica C31;
Fig. 1.130 Tabela de ipoteze cu doua variabile
Prin validarea actiunii cu butonul OK, sintetizarea informatiei prin însumare se va efectua automat pentru fiecare compartiment si functie în parte. Figura 1.131 prezinta rezultatele gruparii datelor.
O a doua aplicatie a tabelei de ipoteze cu doua variabile vizeaza calculul amortizarii accelerate: Se considera o investitie (un mijloc fix) în valoare de 12mil de lei (un calculator); valoarea reziduala estimata a imobilizarii este de 3 mil lei dupa expirarea duratei normate de functionare care este de 6 ani; nr de perioade pentru care se calculeaza amortizarea este de 6 ani.
Fig. 1.131 Rezultatul final (I)
Fig. 1.132 Tabel de ipoteze cu doua variabile
Fig. 1.133 Rezultatul final (II)
Se va genera un tablou de amortizare accelerata pe ani de functionare si pe durata pentru care se calculeaza amortizarea. Modul de construire al tabelei cu doua variabile este prezentat în figura 1.132.
Diferenta fata de exemplul anterior consta în faptul ca, în cazul de fata celulele de intrare nu sunt vide si neprotejate, ci sunt chiar argumentele functiei financiare DB. Rezultatul sintetizarii informatiei -prezentat în figura 1.133- reprezinta chiar tabloul de amortizare al respectivei imobilizari.
1.6.1.2 Tabele pivot
Tabela pivot, ca instrument de asistare a deciziei reprezinta o facilitate prin care datele dintr-o foaie de calcul pot fi permutate pentru a se pune în evidenta noi informatii.
Tabela pivot permite crearea unui tablou de sinteza în care rubricile unui tabel sau unei baze de date pot fi permutate pe linie sau pe coloana, asupra datelor operându-se agregari si calcule sub forma de totaluri, medii, min, max. etc. Aceste operatii sunt alese dintr-o lista de functii predefinite si se pot opera calcule diferite asupra acelorasi elemente supuse sintetizarii.
Fig. 1.134 Baza de date pentru tabela pivot
Deci tabela pivot este un instrument care permite o foarte elastica asociere a unor câmpuri într-o maniera interactiva, fapt ce duce la regruparea datelor si prezentarea acestora într-un mod sintetic.
Tabela pivot se creeaza selectând sursa datelor de sintetizat si operând comanda Data - Pivot Table Report, dupa care un asistent Pivot Table Wizard îndruma utilizatorul în 4 pasi.
Un exemplu edificator de construire si utilizare a tabelei pivot ar consta în sintetizarea informatiei pentru o editura, referitoare la vânzarile de carte;Fie o tabela (figura 1.134) aferenta unei baze de date care regrupeaza informatii referitoare la vânzarile de carte de informatica pe orase (Bucuresti, Ploiesti, Bacau, Iasi), pe doi ani (1997/1998), pe agenti de vânzare (Radulescu S, Ionescu L, Popescu M, Vasilescu D, Moisescu A.), pe cantitati vândute (fiecare carte poate fi vânduta în mai multe orase si acelasi agent de vânzare poate opera vânzari pentru un titlu de carte sau mai multe, în acelasi oras sau în mai multe) si pe valori reiesite din vânzari. Pretul cartii se poate extrage (cu functia VLOOKUP) dintr-un tablou de consultare verticala. Volumul vânzarilor realizate de catre fiecare agent de vânzare este rezultatul înmultirii cantitatii vândute cu pretul cartilor. În rezolvarea aplicatiei, asistentul tabelei pivot (prin comanda Data - Pivot Table Report) parcurge urmatoarele patru etape (prin apasarea butonului Next> pentru fiecare etapa în parte):
În prima etapa Pivot Table Wizard -Step 1 of 4 (figura 1.135) se alege sursa de date pentru sintetizarea informatiei:
- dintr-un tabel Excel sau o baza de date (Microsoft Excel List or Database);
- dintr-o baza de date externa (External Data Source);
- din câmpuri multiple consolidate (Multiple Consolidation Ranges);
din alta tabela pivot (Another Pivot Table).
Fig. 1.135 Tabela pivot, pasul 1
În prealabil sursa de date poate fi selectata sau se poate pozitiona cursorul pe prima celula a sa (de exemplu A3).
Într-o a doua etapa PivotTable Wizard - Step 2 of 4, fig.1.136, se valideaza tabela sursa pe baza careia se va construi tabela pivot sau daca sursa de date nu a fost selectata anterior, aceasta se poate selecta în aceasta etapa prin completarea în rubrica Range.
Fig. 1.136 Tabela pivot, pasul 2
Într-o a treia etapa Pivot Table Wizard- Step 3 of 4 (fig. 1.137) are loc selectarea datelor, prin care se precizeaza care rubrica(rubricii) va(vor) fi plasata(e) pe linie, si care pe coloana. Plasarea rubricilor pe linie sau pe coloana se face prin glisarea rubricii respective într-o caseta de sintetizare, existând posibilitatea agregarii datelor prin plasarea mai multor rubrici (nivel descrescator de centralizare) pe linie sau pe coloana. În plus exista si posibilitatea declararii mai multor restrictii prin glisarea unuia sau mai multor elemente centralizatoare pe pagina (Page).
Caseta de dialog aferenta celei de a treia etapa, contine o macheta-sablon unde vor fi glisate rubricile datelor de sintetizat astfel:
ROW : pozitioneaza elementele câmpului (rubricii) pe linie;
COLUMN: pozitioneaza elementele câmpului (rubricii) pe coloana;
DATA sintetizeaza elementele rubricii prin calcul, la nivelul întregii baze de date, la intersectia valorilor rubricilor dispuse pe linie sau pe coloana;
PAGE: regrupeaza elemente de centralizare ale bazei sau sursei de date pentru care informatia este sintetizata pe linie, pe coloana, pe linie si pe coloana.
În cazul de fata, pentru rezolvarea sintetizarii "Volumul vânzarilor de carte de informatica pe titluri si pe ani", se va glisa rubrica "Denumire carte" pe linie (ROW) si rubrica "Anul" pe coloana (COLUMN), iar rubrica "Valoarea vânzarilor" va fi glisata în centrul machetei (DATA) pentru totalizare.
Fig. 1.137 Tabela pivot, pasul 3
În a patra etapa PivotTable Wizard - Step 4 of 4, (fig. 1.138) se stabileste adresa tabelei pivot, si anume daca aceasta se va plasa într-o noua foaie de calcul (New worksheet), sau în foaia de calcul existenta, la o anumita adresa (Existing worksheet).
Fig. 1.138 Tabela pivot, pasul 4
Tot în aceasta etapa se pot stabili si anumite optiuni ale tabelei pivot, prin apasarea butonului Options, prin caseta de dialog PivotTable Options (fig. 1.139):
- stabilirea numelui tabelei pivot (rubrica Name);
- efectuarea unor calcule de total general pe coloane (Grand totals for columns);
Fig. 1.139 Tabela pivot, pasul 4, Options
- efectuarea unor calcule de total general pe linii (Grand totals for rows);
Fig. 1.140 Tabela pivot, rezultatul final (I)
- salvarea datelor împreuna cu pagina tabelei pivot (Save data with table layout);
- aplicarea unui format predefinit automat pentru tabela pivot (AutoFormat table).
Procedura de generare a tabelei pivot se încheie prin apasarea butonului Finish.
Tabela pivot astfel construita este prezentata în figura 1.140.
Tabela pivot, ca instrument de asistare a deciziei permite o sintetizare în trepte a informatiei, grupând-o pe niveluri descrescatoare de centralizare. Din punct de vedere tehnic, acest lucru se poate realiza grupând mai multe rubrici pe linie sau pe coloana si efectuând mai multe tipuri de calcule pentru aceeasi rubrica sau pentru rubrici diferite.
Conditia pentru sintetizarea în trepte pe linie sau pe coloana este aceea ca prima rubrica plasata pe linie sau pe coloana sa contina mai multe elemente (ce vor fi regrupate) aferente celei de a doua rubrici plasate pe linie sau pe coloana, iar a doua rubrica plasata pe linie sau pe coloana sa contina elemente aferente celei de a treia rubrici, s.a.md. Altfel spus, elementele sunt grupate pe linie sau pe coloana dupa gradul lor de sintetizare, de la cel mai cuprinzatoe element la cel mai putin cuprinzator.
Agregarea în trepte a informatiilor permite deci obtinerea unei viziuni mai sintetice în ceea ce priveste analiza si reprezentarea datelor.
Un exemplu de agregare în trepte este generarea unui raport, privind cantitatea de carte vânduta de catre agentii comerciali pe titluri de carte, pe ani si pe orase de distributie.
Fig. 1.141 Tabela pivot, pasul 3 (II)
Modificarea dispunerii rubricilor în macheta-sablon se face plecând de la tabela pivot generata în cele patru etape, astfel:
se pozitioneaza cursorul oriunde în interiorul tabelei pivot;
se activeaza comanda Data - PivotTable Report, ajungându-se în etapa a 3-a de construire a tabelei pivot (PivotTable Wizard 3 of 4), figura 1.141;
se reconfigureaza rubricile prin glisare în macheta-sablon pe linie, pe coloana, pe pagina sau în zona de calcule DATA, dupa care se apasa butonul NEXT;
se parcurge etapa a 4-a, adica se alege amplasamentul tabelei pivot si anumite optiuni aferente acesteia (figura 1.142).
Anularea unei rubrici se poate face urmând primii doi pasi de la procedura de modificare, dupa care (în pasul PivotTable Wizard 3 of 4) rubrica de anulat va fi glisata din macheta-sablon în afara acesteia (în partea dreapta), alaturi de celelalte rubrici care nu participa la sintetizarea informatiei.
Fig. 1.142 Tabela pivot, rezultatul final (II)
Plecând de la tabela pivot definita anterior, s-a dorit reconfigurarea acesteia prin modificare pentru a obtine, în mod dinamic pe ani si pe orase de distributie, suma cantitatilor de carte vânduta, suma valorica a cartilor vândute pe titluri de carte si pe agenti de distributie.
Sintetizarea dinamica presupune precizarea de restrictii la nivel de pagina, prin alegerea unui element din lista de valori aferente rubricii, de exemplu "Oras distributie" = Bucuresti si "Anul" = 1997.
Reconfigurarea rubricilor este prezentata în figura 1.143.
Fig. 1.143 Tabela pivot, reconfigurarea rubricilor
Tabela pivot modificata si reconfigurata dinamic este prezentata în figura 1.144
Fig. 1.144 Tabela pivot, reconfigurata dinamic
Un alt procedeu de modificare-anulare a rubricilor ce participa la sintetizarea informatiei este urmatorul:
se selecteaza din tabela pivot rubrica de modificat sau anulat (nu elementele acesteia);
se executa un dublu-click pe rubrica respectiva;
în caseta de dialog PivotTable Field (figura 1.145);
se poate anula rubrica prin apasarea butonului Delete;
se poate schimba numele rubricii, în caseta Name;
se poate schimba orientarea (Orientation) rubricii "Agent vânzare" -de exemplu- pe linie (Row), pe coloana (Column), pe pagina (Page);
se pot redefini sau anula elemente de calcul în rubrica Subtotals. În mod implicit-Automatic- se calculeaza numai suma, dar se pot realiza si alte calcule - Custom- cum ar fi medie, produs, maxim, minim, etc, sau prin optiunea None calculele vor fi inhibate;
se pot ascunde anumite elemente ale rubricii respective ("Agent vânzare"), prin marcarea acestora în caseta Hide items (de exemplu, daca se marcheaza elementul Ionescu L. acesta nu va mai apare în tabela pivot ca element sintetizat.
se poate anula afisarea elementelor sintetizabile ce au valori nule, prin marcarea casetei de selectare Show items with no data.
Fig. 1.145 Tabela pivot, modificarea rubricilor
Pentru a schimba operatiile de calcul facute asupra rubricilor, de exemplu modificarea valorii vânzarilor din suma în medie (sau în produs, maxim, minim, etc.), se activeaza prin dublu-clik în procedura de modificare (PivotTable Wizard 3 of 4) câmpul de calcul (în caseta DATA - Sum of Valoare vânzari) asupra caruia opereaza respectiva modificare a operatiei de calcul si în caseta de dialog PivotTable Field, la rubrica Summarize by se schimba din operatia din Sum în Average, validându-se operatia cu OK, fig. 1.146 si 1.14.
Fig. 1.146-1.147 Tabela pivot, modificare rubrici
Caseta de dialog PivotTable Field, prin butonul Options>> permite efectuarea si altor operatii ce permit modalitati diferite de prezentare a datelor sintetizate. Astfel, din lista derulanta Show data as se pot alege operatii ca: "diferenta fata de ., % fata de." etc. Aceste operatii se refera la rubricile tabelei pivot (Base field) si se aplica elementelor acestor rubrici (Base item). De exemplu se poate construi urmatoarea sintetizare: suma vânzarilor pe ani, ca diferenta fata de anul 1998.
Tabela pivot permite ascunderea sau afisarea unor nivele de sintetizare. De regula nivelul de sintetizare ce urmeaza a fi inhibat (sau ascuns) trebuie sa fie în mod obligatoriu ierarhic inferior ca agregare, fata de primul nivel care este mai cuprinzator. În exemplul luat (fig. 1.148), rubrica "Agent vânzare" este pe un plan ierarhic secundar fata de rubrica "Titlu carte" (un titlu de carte este vândut de mai multi agenti de vânzare). Pentru a ascunde un nivel de sintetizare, se selecteaza din tabela pivot, rubrica superioara în ierarhie fata de rubrica de inhibat (de exemplu rubricile "Titlu carte" si "Anul") si se activeaza meniul Data, optiunea Grup and Outline, suboptiunea Hide detail. Pentru a reafisa respectivul nivel ierarhic inhibat, se procedeaza asemanator, cu deosebirea ca se activeaza suboptiunea Show Detail.
Tabela
pivot permite sintetizarea unor elemente disparate, prin gruparea acestora.
Astfel daca se selecteaza din tabela pivot doua orase
(Bucuresti,
Disocierea elementelor grupate se face selectând grupul si actionând comanda inversa: Data - Group and Outline - Ungroup.
Fig. 1.148 Tabela pivot, rezultatul ascunderii datelor (I)
Modificarea tabelei pivot, afisarea sau ascunderea unor detalii, gruparea
Fig. 1.149 Tabela pivot, rezultatul ascunderii datelor (II)
sau disocierea unor elemente disparate sunt operatii ce se pot realiza si cu ajutorul unei bare de butoane aferente tabelei pivot. Bara de butoane se activeaza prin comanda View - Toolbars - Pivot Table. Semnificatia butoanelor este prezentata în figur 1.150.
a) b) c) d) e) f) g) h) i) j) k)
Fig. 1.150 Bara de butoane pentrutabela pivot
a) PivotTable Wizard - permite modificarea tabelei pivot prin intermediul "asistentului" acesteia;
b) PivotTable Field - permite modificarea unei rubrici prin permutarea acesteia pe linie, coloana, pagina sau daca este vorba de o operatie de calcul, aceasta se poate modifica;
c) Show Pages - permite vizualizarea mai multor pagini ale tabelei pivot;
d) Ungroup - permite disocierea elementelor grupate;
e) Group - permite gruparea mai multor elemente într-o tabela pivot;
f) Hide Detail - inhiba afisarea unui nivel agregat;
g) Show Detail - afiseaza un nivel agregat care a fost anterior inhibat;
h) Refresh Data - actualizeaza datele din tabela pivot, daca sursa de date pe care aceasta a fost construita s-a modificat;
i) Select Label - selecteaza o rubrica;
j) Select Data - selecteaza datele unei rubrici;
k) Select Label and Data - selecteaza o rubrica cu elementele aferente.
O ultima facilitate a tabelei pivot, ar fi aceea de extragere în alta foaie de calcul a unor informatii, potrivit unui criteriu. Acest lucru este posibil printr-un dublu-clik executat pe un element aferent unei rubrici sau aflat la intersectia a doua rubrici.
Fig. 1.151 Rezultatul extragerii
De exemplu plasând cursorul la intersectia a doua rubrici, anume Oras distributie = Bucuresti si Birotica Total, rezulta în urma unui dublu-click o extragere tip baza de date, pentru vânzarile totale de carte cu titlul "Birotica" în Bucuresti (fig. 1.151).
1.6.1.3 Gruparea informatiei prin generarea de subtotaluri
Sub Excel exista posibilitatea sintetizarii informatiei prin organizarea ei pe niveluri de grupare, iar apoi pot opera diferite calcule pe aceste grupuri sau informatii centralizate. Altfel spus Excel poate organiza datele prin grupare pentru generarea de totaluri si subtotaluri.
Fig. 1.152 Rezultatul filtrarii datelor
Fig. 1.153 Caseta de dialog SUBTOTAL
Pentru gruparea datelor care se doresc a fi totalizate este necesara sortarea acestora pe rubricile de grupare. De exemplu, baza de date definita anterior (la tabela de ipoteze cu o variabila) pe coordonatele A3:E17 va fi sortata ascendent pe rubricile "Compartiment", "Functie" (un compartiment contine salariati cu functii diferite) si descendent pe rubrica "Marca".
Sortarea se face cu ajutorul comenzii Data Sort (baza de date nu trebuie neaparat selectata, fiind suficienta pozitionarea cursorului pe una din rubrici). În caseta de dialog Sort se precizeaza cheia (cheile) de sortare Sort By (Then By) si ordinea sortarii: crescatoare (Ascending) sau descrescatoare (Descending).
Figura 1.152 ilustraza rezultatul sortarii datelor dupa rubricile "Compartiment" (Sort By); "Functia" (Then By); "Marca" (Then By).
Dupa ce datele au fost sortate dupa cele trei chei de sortare, acestea pot fi totalizate pe câmpurile de grupare. Subtotalizarea se face prin pozitionarea pe prima celula a bazei de date (sau prin selectarea acesteia) si activarea comenzii Data - Subtotals. Prin caseta de dialog Subtotal se desfasoara tehnica de regrupare a informatiei, astfel: se precizeaza rubrica pentru care se face gruparea. Astfel, din lista derulanta At Each Change in (în cazul de fata) se alege rubrica pe care se face gruparea sau unde are loc "ruptura de secventa" "Compartiment";-se alege tipul de operatie care va aplica datelor regrupate (Sum; Max; Min; Avarage; Count; Product) din caseta Use Function;-în final se alege rubrica de calculat prin selectarea acesteia - în cazul de fata "Salariu";-dupa precizarea acestor elemente se valideaza cu butonul OK. Caseta de dialog Subtotal este prezentata în figura 1.153.
Rezultatul gruparii si totalizarii datelor este prezentat în figura 1.153 În urma totalizarii, foaia de calul si-a schimbat aspectul în sensul aparitiei unor elemente (+ si -) care semnifica gradul de grupare a datelor. Altfel spus, tabelul pentru care s-a facut totalizarea a fost ierarhizat.
Ierarhizarea elementelor regrupate permite alegerea pentru vizualizare sau pentru prelucrari ulterioare (de exemplu reprezentari grafice de structura) a nivelului dorit astfel încât sa fie relevata informatia de care utilizatorul are nevoie.
Revenirea afisarii din forma ierarhizata în forma normala se face prin selectarea tabelului sau pozitionarea cursorului pe prima celula a acestuia si activarea comenzii Data Subtotals, iar din caseta Subtotal va fi apasat butonul Remove All (fig. 1.155).
Fig. 1.154 Rezultatul gruparii datelor
Fig. 1.155 Prezentarea ierahizata a datelor
Consolidarea datelor din foile de calcul semnifica regruparea acestora prin utilizarea acelorasi coordonate într-o foaie de calcul centralizatoare.
Consolidarea datelor implica facilitati de exploatare tridimensionala pentru foile de calcul. Datele ce urmeaza a fi consolidate se pot gasi în foi de calcul separate sau în fisiere (documente de calcul tabelar) distincte.
Procedura de consolidare implica participarea fiecarei foi de calcul sau fisier la o operatiune de centralizare. Rezultatul acestei operatii se poate gasi fie în acelasi fisier, dar într-o foaie de calcul distincta, fie în alt fisier. Este foarte important ca datele ce urmeaza a fi centralizate sa aiba o schema unica de organizare în foile de calul ce constituie sursa a consolidarii. Altfel spus, datele de centralizat trebuie sa fie editate la niste adrese fixe, pe baza sablonarii acestora, astfel încât coordonatele celulare ale informatiilor de consolidat sa fie aceleasi din punct de vedere al exploatarii tridimensionale a foilor de calcul.
Din punctul de vedere al surselor de date ce participa la gruparea informatiei, consolidarea poate fi interna sau externa.
Consolidarea interna a datelor presupune participarea mai multor foi de calcul ale aceluiasi fisier, organizate identic din punct de vedere al referintelor celulare, la centralizarea datelor într-o foaie de calcul distincta, în cadrul aceluiasi document de calcul tabelar.
Consolidarea interna a datelor poate fi facuta în doua moduri:
printr-o procedura speciala, generata prin comanda Data Consolidate;
prin utlilizarea formulelor de calcul cu referinte 3-D.
Pentru exemplificarea operatiunii de consolidare, propunem urmatoarea aplicatie:
Un institut de formare doreste sa-si centralizeze informatiile legate de bugetele de cheltuieli aferente cursurilor de instruire pe care le face. Fiecare buget de curs se gestioneaza individual de catre un responsabil pedagogic. Presupunem ca respectivul institut gestioneaza trei cursuri, elaborând câte un buget pentru fiecare : Contabilitate, Informatica si Management. Fiecare buget de curs are aceleasi capitole: Cheltuieli salariale, Cheltuieli publicitare, Cheltuieli cu logistica, Cheltuieli administrative. Cele trei bugete au fost construite pe trei foi de calcul ce poarta numele fiecarui buget. Bugetele au fost sablonate, în sensul marcarii elementelor de centralizat în aceeasi ordine si pe aceleasi coordonate.
În figura 1.156 sunt prezentate cele trei variante de buget pentru cursurile de contabilitate, informatica si management:
Pentru centralizarea datelor din cele trei foi de calcul, se pot urmari doua procedee de consolidare. Primul procedeu vizeaza consolidarea automata prin intermediul comenzii Data - Consolidate, astfel:
se deschide noua foaie de calcul care va contine rezultatele consolidarii;
Fig. 1.156 Variante de buget
se pozitioneaza cursorul în prima celula din stânga-sus (de regula) sau într-o celula ce desemneaza destinatia consolidarii datelor;
se activeaza comanda Data - Consolidate, figura 1.157;
în caseta de dialog Consolidate se executa urmatoarele operatii:
Fig. 1.157 Caseta de dialog pentru consolidare
a) se alege tipul de operatie (Sum, Max, Min, Product, Average, etc.) care se va aplica datelor de consolidat, din lista derulanta Function (de regula se alege suma);
b) în caseta Reference se introduce sursa de date ce va participa la consolidare. La precizarea sursei de date se va avea în vedere atât selectarea datelor de consolidat propriu-zise, cât si selectarea etichetelor sau explicatiilor aferente acestora;
c) se apasa butonul Add, zona selectata anterior fiind înregistrata automat în caseta All References;
d) se repeta succesiv ultimele doua proceduri pâna la epuizarea zonelor sursa ce participa la consolidare;
e) se precizeaza modelul de organizare a datelor urmarit prin consolidare, prin caseta Use labels in. Consolidarea datelor pe categorii (niveluri ierarhizate de organizare a datelor) presupune si precizarea etichetelor si explicatiilor aferente datelor. Astfel, pot exista trei modele de consolidare : pe linie (caseta de selectare Top row), pe coloana (Left column) sau amândoua (cele doua casete de selectare sunt activate), dupa cum datele de consolidat sunt dispuse. Daca niciuna din cele doua casete de selectare nu este activata, consolidarea este considerata a fi facuta dupa pozitia datelor de consolidat, fara a urmari o eventuala regrupare a acestora.
f) se poate actualiza rezultatul consolidarii la modificarea valorilor unor celule ce apartin de sursa consolidarii, prin activarea casetei de selectare Create links to source data;
g) în final procedura de consolidare se valideaza cu OK.
Adaugarea de noi foi de calcul de sintetizat care sa participe la elaborarea unui rezultat consolidat se face prin includerea acestora în caseta Reference si apasarea butonului Add. stergerea unui element care participa la consolidare se face prin selectarea acestuia din lista All references si apasarea butonului Delete.
În momentul crearii de legaturi (Create links to source data) între sursa si destinatie, nu se mai pot adauga noi surse de date ce participa la consolidare, nu se pot modifica sau sterge surse existente.
Rezultatele consolidarii celor trei foi de calcul sunt prezentate în figura 1.158.
Daca datele de consolidat sunt externe documentului în care se face consolidarea, operatiunea poate fi facuta precizând adresa fizica de pe disc a surselor de date. Acest lucru este posibil prin marcarea completa în rubrica References a specificatorului de fisier si a foii de calcul din care provin sursele de date sau prin apasarea butonului Browse si alegerea interactiva a coordonatelor datelor de consolidat.
Fig. 1.158 Rezultatul consolidarii (I)
Al doilea procedeu de consolidare vizeaza utilizarea formulelor cu incidenta tridimensionala. Astfel, urmarind exemplul precedent se pot sintetiza urmatoarele etape de urmat:
se deschide noua foaie de calcul care va contine rezultatele consolidarii;
se pozitioneaza cursorul în prima celula din stânga-sus (de regula) sau într-o celula ce desemneaza destinatia consolidarii datelor;
se copiaza (Copy/Paste) etichetele datelor ce participa la consolidare
se genereaza o formula de calcul de regrupare utilizând referinte tridimensionale. Referintele 3-D includ pe lânga coordonatele coloanelor si liniilor, si numele foilor de calcul din care provin datele.
De exemplu, pentru a calcula suma salariilor cadrelor didactice de seminarizare, în celula B5 a foii de calcul de consolidare ("Buget CONSOLIDAT 2) s-a editat formula 3-D :
=SUM(Contabilitate:Management!B5).
Fig. 1.159 Rezultatul cosolidarii (II)
Formula semnifica efectuarea unei sume pe coordonata celulei B5 aferenta foilor de calcul cuprinse între foaia Contabilitate si Management.
O alta formula 3-D ar putea viza referintele individuale ale celulelor care participa la consolidare. De exemplu în celula B7 a foii de calcul de consolidare s-a editat formula :
=Contabilitate!B7+Informatica!B7+Management!B7.
Consolidarea datelor dupa aceasta procedura este prezentata în figura 1.159.
În utilizarea adreselor tridimensionale în procesul de consolidare a datelor, este foarte important ca datele de centralizat sa aiba acelasi model de organizare pe coordonate celulare.
formula 3-D poate fi copiata la nivelul celorlalte celule, iar daca cerinta anterioara de pastrare a acelorasi coordonate tridimensionale este îndeplinita, tabloul de consolidare va fi generat corect.
Consolidarea datelor ce provin din fisiere distincte prin acest procedeu presupune ca în formula de consolidare sa intervina si specificatorul de fisier de unde provin datele de consolidat.
Astfel de exemplu daca cele trei bugete ar fi fost construite pe fisiere separate: Contabilitate.XLS, Informatica.XLS, Management. XLS, formula de consolidare a datelor pentru Cheltulielile publicitare (celula B7) ar fi :
=[Contabilitate.xls]Sheet1!B7+[Informatica.xls]Sheet1!B7+[Management.xls]Sheet1!B7 (fig. 1.160)
Fig. 1.160 Buget consolidat
Instrumentele de simulare si optimizare cuprind facilitati puternice de modelare a unor probleme formulate în sensul gasirii unor solutii care sa raspunda la un ansamblu de restrictii, în sensul simularii si optimizarii acestora.
1.6.2.1 Tehnica valorii scop sau de cautare a rezultatului
Tehnica cautarii rezultatului permite stabilirea unei valori finale (scop sau obiectiv) pentru o formula pentru ca apoi sa modifice valoarea uneia din celulele utilizate în formula pentru a calcula valoarea finala.
Utilizând cautarea tip "rezultat" se poate ajusta o estimare pentru a se ajunge la o concluzie referitoare la o expresie relativa sau absoluta (procentaj sau valoare) pentru un buget sau o varianta de simulat. Cautarea valorii scop are loc prin comanda Tools - Goal Seek... unde prin caseta de dialog Goal Seek se stabilesc: . celula scop sau obiectiv (rubrica Set cell:) care va fi ajustata automat la o valoare corespunzatoare; . o valoare care desemneaza rezultatul care se doreste a se obtine (rubrica To value:); . celula ce contine valoarea care trebuie modificata (rubrica By changing cell:)
Pe scurt, pentru a gasi o valoare scop sau obiectiv, formularea ar fi urmatoarea : cu cât (sau la cât) ar trebui modificat un parametru (By changing cell) pentru ca o valoare scop (Set cell) sa atinga un prag specificat (To value).
Fig. 1.161 Aplicatie Goal Seek
Celula al carei continut va fi modificat (By changing cell) trebuie sa contina o valoare (care participa în mod nemijlocit la formarea rezultatului) si nu o formula, în timp ce valoarea scop sau obiectiv (Set cell) trebuie sa contina în mod obligatoriu o formula. Pentru cautarea simultana a rezultatelor dupa mai multe valori, nu se foloseste Goal Seek ci se modeleaza o problema de optimizare folosind Solver-ul. Exemplul din fig.1.161 ilustreaza un buget previzional simplificat al cursurilor postuniversitare.
Bugetul calculeaza veniturile, cheltuileile, precum si marja bruta ce se degaja din aceste cursuri. Marja bruta calculata la un numar de 30 de cursanti este de 23%.
Fig. 1.162 Utilizarea tehnicii Goal Seek
Daca s-ar dori calcularea eficientei cursurilor pentru un prag estimat la 30% marja bruta si s-ar pune întrebarea "de câti cursanti ar fi nevoie pentru a atinge o marja de 30%", problema s-ar rezolva prin tehnica valorii scop astfel:
se pozitioneaza (de regula) cursorul pe formula ce contine valoarea scop si se activeaza comanda Tools - Goal Seek;
Fig. 1.163 Utilizarea tehnicii valorii-scop
- se seteaza în rubrica Set cell: celula C15 care contine valoarea scop - adica marja bruta (daca în prealabil nu s-a pozitionat cursorul pe aceasta valoare);
- se stabileste valoarea obiectiv în rubrica To value: la care sa trebuie sa ajunga valoarea scop - în cazul de fata 30%;
se stabileste ce valoare sa se schimbe în rubrica By changing cell: (celula C5) - în cazul prezentat numarul de cursanti- pentru a se ajunge la obiectivul propus;
- se valideaza cu OK (fig. 1.162). Calculul este facut automat pentru a se atinge valoarea scop si în celulele respective vor apare noile valori (34 cursanti pentru o marja bruta de 30%). Daca se valideaza cu butonul OK vechile valori vor fi înlocuite cu noile valori calculate pentru obiectivul fixat, iar daca se activeaza Cancel, vechile valori vor fi restaurate (fig. 1.163).
Scenariile se folosesc pentru a compara anumite valori care genereaza rezultate diferite. Astfel se pot modela diverse stategii pentru a analiza avantajele si dezavantajele diferitelor moduri de abordare pentru probleme date.
Scenariile constituie simulari ale mai multor variante de proiect, care prin ipotezele propuse prin valori diferite ale unor parametrii conduc la rezultate diferite. Cu ajutorul acestor instrumente se pot vizualiza si analiza ipotezele luate în calcul. Generarea scenariilor este posibila prin intermediul meniului Tools - Scenarious.
Pentru construirea efectiva a unui scenariu se selecteaza anumite celule care contin valori modificabile (Changing Cells) care participa la formarea unui rezultat final si care pe parcursul simularii admit valori diferite care genereaza variante de rezultate.
Este de preferat ca înainte de a construi scenariul, celulele modificabile sa fie numite. Celulele modificabile nu trebuie sa contina formule, ci doar constante numerice care sa conduca nemijlocit la un rezultat de simulat.
Gestiunea scenariilor se realizeaza prin intermediului "managerului de scenarii" (Scenario Manager).
Generarea scenariilor va fi exemplificata prin urmatoarea aplicatie: un institut care organizeaza cursuri de perfectionare doreste sa elaboreze mai multe variante de buget pentru un singur curs, pentru a simula rentabilitatea bruta a acestuia în conditiile unei evolutii optimiste si pesimiste a petei de formare profesionala. Informatiile care conduc la formarea rezultatului (marja bruta) sunt: numarul de cursanti ce participa la formare, tariful cursului, numarul total de ore afectate programului de instruire si remuneratia personalului didactic implicat în formare.
Pentru rezolvarea acestei aplicatii se parcurg urmatoarele etape:
Se numesc în prealabil celulele modificabile care participa la simulare (comanda Insert - Name - Define);
Se selecteaza celulele modificabile care prin valorile continute conduc la un rezultat pertinent în simulare (celulele:C5, C6, C7, C9);
Se activeaza managerul de scenarii prin comanda Tools - Scenarious.(fig. 1.164)
Fig. 1.164 Aplicatie tip scenariu
Se introduc variantele simularii (buget optimist si buget pesimist) prin apasarea butonului Add din caseta de dialog Scenario Manager;
În caseta Add Scenario se introduce numele scenariului în caseta rubricii Scenario name (Optimist - în cazul studiat), se pot schimba (daca
Fig. 1.165a Scenariul optimist
este nevoie) adresele celulelor modificabile în caseta rubricii Changing cells sau se pot insera comentarii referitoare la varianta studiata, dupa care se valideaza operatiunea prin butonul OK.
Se introduc valorile corespunzatoare scenariului optimist în caseta Scenario Values. În exemplul luat se va introduce valoarea 60 pentru numarul de cursanti, valoarea 2000000 pentru taxa curs, valoarea 150 pentru numar de ore si valoarea 150000 pentru salarii orare.
Fig. 1.165b Rezultatul optimist
Se apasa butonul Add pentru a introduce un alt scenariu (pesimist) sau se apasa OK si se revine la managerul de scenarii.
Se introduce cel de-al doilea scenariu (pesimist) tastând numele acestuia în caseta Add Scenario si completând valoarile celulelor modificabile în caseta Scenario Values.
Fig. 1.166a Scenariul pesimist
Revenirea în managerul de scenarii se face prin butonul OK, iar adaugarea unor noi variante de buget se opereaza prin butonul Add.
Prin Scenario Manager se pot vizualiza alternativ variantele simulate prin selectarea scenariului respectiv din lista de optiuni a rubricii Scenarious si activarea butonului Show (actiune exemplificata în figurile 1.164-1.166). Tot la acest nivel, scenariile se pot modifica sau sterge, selectându-se varianta respectiva si apasând dupa caz Edit. sau Delete.
Fig. 1.166b Rezultatul pesimist
Daca se doreste efectuarea unei sinteze a variantelor de buget simulate, se poate genera un raport al scenariilor. Acest lucru este posibil activând din managerul de scenarii butonul Summary. În caseta de dialog Scenario Summary utilizatorul poate preciza ce tip de raport doreste a fi generat (Report type): tip sinteza a scenariilor (Scenario summary) sau tip tabela pivot (Pivot în zona de rezultate a simularii Table).
Fig. 1.167 Meniul sumarului de scenariu
De asemenea tot la acest nivel se precizeaza în rubrica Result cells care sunt celulele care contin rezultatul final al simularii (în cazul de fata C14 - Rezultatul si C15 - Marja bruta).
Validându-se prin OK se obtine raportul scenariilor într-o foaie de calcul generata automat si numita Scenario Summary, fapt exemplificat si în figura 1.167
Se observa ca în zona de rezultate (Result Cells) aferenta raportului Scenario Summary, celula ce contine Rezultatul nu a fost în prealabil numita si de aceea ea apare indentificata în raportul de sinteza prin coordonatele sale.
Fig. 1.168 Rezultatul scenariului (Sumarul scenariului)
În noua foaie de calcul (fig. 1.168), apar în stânga raportului, butoane de afisare (+) sau de inhibare (-) a unor detalii:
butonul "+" (plus) în linia de antet (Scenario Summary) determina afisarea comentariilor declarate în caseta de dialog Add Scenario, numele autorului scenariului si data sistem când a fost creat scenariul;
butonul "-" (minus) în zona Changing Cells inhiba afisarea celulelor modificabile, adica a parametrilor simularii;
butonul "-" în zona de rezultate (Results Cells) inhiba afisarea rezultatelor simularii.
Pentru a identifica scenarii aflate în foile de calcul tabelar, se foloseste metoda cautaruii acestora în caseta de dialog Scenario Manager prin butonul Merge.
Caseta de dialog Merge Scenarious (fig. 1.169) permite identificarea scenariilor în mai multe documente Excel (rubrica Book:) si în mai multe foi de calcul, prin lista rubricii Sheet. De exemplu în fisierul "Instrumente de asistare a deciziei.XLS" exista doua scenarii în foaia de calcul numita "Tehnica valorii scop".
Fig. 1.169 Caseta de dialog Merge Scenarious
Dupa ce a fost construit un scenariu, acesta poate fi exploatat si actualizat oricînd prin comanda Tools - Scenarious.
Solverul sau "rezolvatorul de probleme" este un instrument informatic de optimizare care generalizeaza tehnica valorii scop, oferind mai multe posibilitati de simulare a unor parametrii ce dau nastere unei situatii de optim. Prin solver se pot face simulari mai complexe asupra unor informatii continute în celule si se pot lua în consideratie mai multe restrictii impuse unui model de optimizat.
Ca principiu, o problema de optimizare tratata sub Excel vizeaza automatizarea unei aplicatii de programare lineara (algoritmul SIMPLEX) si anume de ajungere la o solutie optimala în sensul maximizarii unor rezultate, minimizarii unor eforturi sau de atingere a unei valori-scop considerate de utilizator. Atigerea situatiei de optim se face prin modificarea automata a unor parametrii ce conduc la atingerea scopului propus, în conditiile precizarii unor restrictii impuse modelului, astfel încât situatia optimala sa ia în considerare aceste constrângeri sau restrictii.
Pentru transpunerea unei probleme de programare lineara într-un model apt a fi rezolvat prin tehnica solver-ului în Excel, se impune:
stabilirea informatiei ce urmeaza a fi optimizata;
stabilirea tipului de optim (maximizare, minimizare, atingerea unei valori);
stabilirea parametrilor modificabili care prin variatia lor sa produca optimul;
stabilirea unor restrictii aplicate parametrilor modificabili sau oricaror informatii legate de generarea starii de optim.
Pentru întelegerea modului de rezolvare a problemelor cu ajutorul solver-ului, furnizam urmatoarea aplicatie:
Un institut de formare si perfectionare profesionala doreste sa-si optimizeze rentabilitatea globala în sensul maximizarii acesteia. Institutul gestioneaza bugetele a trei cursuri si anume : Managementul Afacerilor, Contabilitate si Management financiar. Fiecare curs are propriul sau buget previzional, estimându-se veniturile, cheltuielile, eventualul rezultat brut si marja bruta a rezultatului. Paralel, se centralizeaza datele previzionate la nivel de institut, calculându-se rentabilitatea totala. Modelul economic ce va fi optimizat prin maximizarea rezultatelor este prezentat în figura 1.170.
Fig. 1.170 Model supus maximizarii rezultatelor
Modificând anumiti parametrii (Numarul de cursanti, Salariu pe ora, Pretul pe fila de suport de curs si numarul de file al suportului de curs), prin intermediul formulelor (prezentate în figura 1.171) se pot genera rezultate diferite atât pentru rentabilitatea fiecarui curs, cât si pentru rentabilitatea globala a institutului.
Optimizarea modelului presupune stabilirea în primul rând a obiectivului acestuia, în cazul luat este vorba de celula care contine formula rentabilitatii globale (Target Cell), adica E154 (rentabilitate globala totala = rata marjei brute / cifra de afaceri).
Pentru rezolvarea problemei de optimizare, în sensul maximizarii rentabilitatii (rata marjei brute), se pozitioneaza cursorul pe celula care contine obiectivul (E154) si se activeaza comanda Tools - Solver, iar în caseta de dialog Solver Parameters se stabilesc : celula obiectiv, sensul optimizarii, celulele modificabile sau ajustabile (prezentate în figura anterioara: B143:D145) si restrictiile impuse modelului.
Semnificatia rubricilor casetei de dialog Solver Parameters este urmatoarea:
Set Target Cell: specifica adresa celulei care contine scopul sau obiectivul optimizarii;
Fig. 1.171 Formulele de optimizare
Equal To: stabileste sensul optimizarii potrivit scopului propus, ce anume: maximizare (Max), de minimizare (Min) sau de atingere a unei valori (Value of:)
Fig. 1.172 Parametrii Solver-ului
By Changing Cells: propune toate celulele care nu contin formule si au legatura cu scopul optimizarii. Altfel spus selecteaza automat toate celulele ce
contin parametrii numerici la care formula din câmpul ce defineste obiectivul (Target Cell) face referire;
Subject to the Constraints: contine restrictiile impuse modelului de optimizare;
Butonul Add permite adaugarea restrictiilor;
Butonul Change permite modificarea restrictiilor;
Butonul Delete permite stergerea restrictiilor;
Butonul Solve lanseaza procesul de rezolvare a problemei de optimizare;
Fig. 1.173 Rezultatul optimizarii-caseta de dialog
Butonul Options afiseaza o caseta de dialog prin care se pot controla caracteristici avansate ale procesului de rezolvare si de schimbare sau înregistrare a specificatiilor pentru o problema particulara; Reset All anuleaza parametrii atribuiti si restabileste optiunile implicite
Dupa completarea elementelor casetei de dialog Solver Parameters (fig. 1.172) si apasarea butonului Solve, se declanseaza iterativ procedura de optimizare, generându-se în final când problemei i s-a gasit (sau nu i s-a gasit solutia) caseta de dialog cu rezultatele optimizarii (Solver Resultats-fig. 1.173).
Fig. 1.174 Salvarea scenariului
Rezultatul optimizarii presupune sau înlocuirea vechilor parametrii cu altii noi, gasiti în procesul de optimizare, implicit transformarea solutiei existente într-una noua (Keep Solver Solution), sau restaurarea parametrilor originali si pastrarea vechiului rezultat (Restore Original Values). Solutia gasita se poate salva într-un scenariu (alaturi de cele existente), apasând butonul Save Scenario.fig. 1.174
Rezultele optimizarii sunt prezentate în figura 1.175.
Fig. 1.175 Rezultatele optimizarii
Fig. 1.176 Sintetizarea rezultatelor optimizarii
Valorile optimale ale rezultatului solver-ului pot fi sintetizate, alegându-se un tip special de raport din caseta de dialog Solver Resultats, rubrica Reports, astfel:
-raport tip "Answer" : afiseaza celula obiectiv de definit, celulele variabile cu valorile lor initiale si finale, restrictiile modelului de optimizat, precum si informatiile legate de aceste restrictii (figura 1.176);
-raport tip "Sensitivity": furnizeaza informatii asupra sensibilitatii si elasticitatii modelului de optimizare, adica variatia solutiei fata de cele mai mici modificari aduse formulei din zona Set Target Cell. Altfel spus, cu cât se modifica solutia la schimbarea cu o unitate a parametrilor ce conduc la obiectivul fixat.
Raport tip "Limits" : afiseaza si limitele superioare si inferioare ale modelului.
Excel poseda câteva instrumente de previziune, care aplicate seriilor de date, ofera tehnici sofisticate de analiza statistica, ce pot crea proiectii despre evolutia fenomenelor supuse studiului. Previziunile facute de Excel se bazeaza pe tendintele de evolutie ale unor fenomene al caror trecut este cunoscut.
Excel are posibilitatea de analiza a unui set de valori si de determinare a unei tendinte de evolutie pentru seria respectiva de valori, printr-o analiza de regresie. Analiza de regresie presupune determinarea unui grad de corelatie între mai multe fenomene studiate. De regula regresiile presupun stabilirea legaturii dintre o variabila dependenta si una (regresie simpla) sau mai multe (regresie multipla) variabile independente.
Sub Excel previziunile pot fi facute:
cu ajutorul functiilor statistice predefinite:
LINEST, TREND, FORECAST pentru regresiile lineare;
LOGEST, GROWTH pentru regresiile exponentiale;
LOGREG pentru regresiile logaritmice
printr-o procedura rapida de auto-umplere (fill-in) a celulelor cu rezultatele prognozei.
prin grafice;
Previziuni cu ajutorul functiilor statistice predefinite
Functia FORECAST efectueaza o prognoza a fenomenului studiat pentru perioada imediat urmatoare (luna sau an). Previziunea efectuata cu aceasta functie este de tip linear.
Exemplificarea functiei FORECAST pleaca de la urmatorul caz: o societate comerciala doreste sa-si prognozeze vânzarile pentru un produs (P1), pentru luna urmatoare (a 7-a), cunoscând evolutia precedenta a vânzarilor (lunile 1-6).
Pentru previziunea dorita, se va utiliza functia FORECAST, care va admite urmatoarele argumente:
adresa/numele celulei care contine perioada pentru care se face previziunea (A16);
adresa/ numele câmpului ce contine valorile pe baza carora se face previziunea (B10:B15);
adresa/numele câmpului ce contine valorile perioadei anterioare pe baza carora se face estimarea valorii viitoare (A10:A15).
Sintaxa functiei FORECAST, precum si rezultatul previziunii sunt elemente prezentate grafic în figura 1.177.
Fig. 1.177 Functia de previziune FORECAST (I)
Daca s-ar fi dorit previzionarea vânzarilor pentru urmatoarele 6 luni, formula FORECAST ar fi introdusa matriceal. Recopierea clasica a formulei pentru lunile urmatoare (7-12) ar conduce la rezultate eronate.
O formula matriceala se introduce:
selectând câmpul destinatie pentru care se vor calcula valorile previzionate;
tastând formula ce calculeaza mai multe valori deodata;
confirmând cu ajutorul combinatiei de taste Ctrl+Shift+Enter
Astfel, pentru introducerea matriceala a formulei FORECAST, s-a selectat plaja de celule pentru care se face previziunea (B16:B21), s-a editat formula (extinzând primul argument la nivelul valorilor lunilor pentru care se previzioneaza vânzarile) si s-a validat formula cu Ctrl+Shift+Enter.
În figura 1.178 sunt prezentate: atât sintaxa functiei FORECAST, editata matricial, cât si rezultatele acesteia.
Functia TREND furnizeaza pe mai multe perioade tendinta lineara a unui fenomen studiat plecând de la serii de date cunoscute.
Functia TREND se poate calcula printr-o formula matriciala, adica pe baza unei formule care genereaza într-un câmp anterior selectat mai multe valori deodata.
Fig. 1.178 Functia de previziune FORECAST (II)
Fig. 1.179 Functia de previziune TREND (I)
Formula ce contine functia TREND are ca argumente :
câmpul care contine valorile existente pentru care se construieste prognoza; numere distincte aferente pozitiei elementelor pentru valorile prognozateDe exemplu, pentru previzionarea evolutiei vânzarilor pentru perioada august-decembrie, cunoscând valorile din perioada trecuta (ianuarie - iulie), se poate introduce matriceal functia TREND cu urmatoarele argumente:
primul argument vizeaza câmpul B45:B51 ce contine valorile cunoscute pe care se construieste previziunea;
al doilea argument evidentiaza specificarea (printr-un numar de ordine atribuit) elementelor necunoscute, ale caror valori trebuiesc aflate (8;9;10;11;12) - corespunzator lunilor august-decembrie (fig. 1.179)
A doua metoda de previzionare vizeaza auto-umplerea celulelor cu rezultatele prognozei.
Rezultatele functiilor de previziune, bazate pe regresia lineara si exponentiala se genereaza automat prin selectarea datelor existente pe care se bazeaza prognoza si glisarea catre noul amplasament cu ajutorul butonului drept al mouse-ului apasat.
În
urma realizarii acestei operatii, se afiseaza un meniu
contextual, de unde se poate alege tipul tendintei seriilor de sate pe
care se construieste prognoza: tendinta lineara (Linear Trend) sau
exponentiala (Growth Trend).
Acest procedeu de auto-umplere a celulelor cu rezultatele prognozate, poate fi vizualizat în figura 1.180.
Fig. 1.180 Functia de previziune TREND (II)
Un alt mod de estimare pentru o tendinta este reprezentarea grafica printr-o diagrama "trendline".
Graficul "de tendinta" se construieste în mod obisnuit, selectând seriile de date (pe linie sau pe coloana). În selectie se vor cuprinde atât datele existente (pe care se construieste prognoza), cât si câmpul care se va estima prin previziune.
Fig. 1.181 Graficul de tendinta
În procesul de construire a graficului de tendinta, se selecteaza tipul de
Fig. 1.182 Alegerea graficului de tendinte
reprezentarea grafica (numai tip linear sau histograma), iar din meniul contextual Chart se alege optiunea Add Trendline (fig. 1.182).
Reprezentarile grafice ce ilustreaza tendinte de evolutie pentru datele prognozate pot fi : lineare (Linear), logaritmice (Logarithmic), polinomiale (Polynomial), putere (Power), exponentiale (Exponential), medie mobila (Moving average).
Figura 1.183 ilustreaza o previziune pentru datele analizate, din punct de vedere al evolutiilor lineare, exponentiale, logaritmice, etc.
Fig. 1.183 Graficul de previziune a tendintelor
|
