1.6 INSTRUMENTE DE SINTETIZARE A INTORMAºIEI ªI DEASISTARE A DECIZIEI

 Procesorul de tabele Excel, alåturi de tacilitå¡ile cunoscute de calcul tabelar, de

generare de gratice si de exploatare a bazelor de date, posedå mai multe tehnici de prelucrare a datelor prin care intorma¡ia poate ti agregatå, centralizatå, regrupatå, simulatå sau optimizatå.

Aceste tacilitå¡i sunt cunoscute sub numele de instrumente de sintetizare a intorma¡iei si de asistare a deciziei si regrupeazå: instrumente de sintetizare (tabelele de ipoteze, tabele pivot, subtotalizåri, consolidåri), instrumente de simulare si optimizare (scenariile, tehnica valorii scop si solver-ul) si instrumente de previziune (tunc¡iile Trend si Torecast, previziuni prin gratice).

 1.6.1 Instrumente de sintetizare a intorma¡iei  Procesorul de tabele Excel oterå tehnici si tacilitå¡i avansate de regrupare si

centralizare a datelor con¡inute în tabele, ob¡inându-se în urma prelucrårii, noi intorma¡ii agregate si sintetizate.

 1.6.1.1 Tabelele de ipoteze permit ob¡inerea mai multor rezultate prin calcule

aplicabile mai multor serii de valori, dispuse monodimensional sau bidimensional. Asttel, prin aplicarea unei singure tormule unor serii de valori dispuse pe linie sau

pe coloanå se poate ob¡ine un numår mare de rezultate care sintetizeazå prin regrupare intorma¡iile dintr-un tablou sau o bazå de date. De tapt, 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 2 celule de intrare; una sau mai multe tormule care vizeazå celulele de intrare; un câmp special numit “câmp de ipoteze” cu una sau 2 dimensiuni în care prima coloanå sau linie con¡ine valori asupra cårora se aplicå etectiv tormulele.

Atunci când mai mulTi parametrii participå la elaborarea unui rezultat, câmpurile tabelelor de ipoteze permit evaluarea importan¡ei relative a tiecårui parametru (doi sau mai mul¡i parametrii concurå la rezultatul sintetizårii).

Procedura de sintetizare a intorma¡iei prin tabelele de ipoteze se declanseazå prin execu¡ia comenzii Data Table.

 

  Tig. 1.122 Tabelå de ipoteze cu osingura variabilå Tabela de ipoteze cu o singurå variabilå sau studiul varia¡iei unui singur

parametru.O tabela de ipoteze cu o singurå variabilå permite exploatarea rezultatului uneia

sau a mai multor tormule, urmårind diterite valori luate de un parametru. Tabela de ipoteze este compuså din:

- -         o celulå de intrare, situatå oriunde în toaia de calcul (dar mai pu¡in în câmpul de ipoteze), detinitå si reteritå de utilizator în caseta de dialog Table, corespunzåtor rubricilor Row Input Cell sau Column Input Cell dupå cum se doreste a se organiza rezultatul pe linie sau pe coloanå. Celula de intrare poate ti vidå, ea tiind situatå într-un câmp de criterii atunci când tormula generatoare de rezultate este tip bazå de date (=D). ¥n cazul utilizarii altor tunc¡ii, altele decât cele baze de date, celula de intrare poate con¡ine unul din argumentele tunc¡iei respective.

- -         câmpul de ipoteze con¡ine rezultatele calculate prin aplicarea unei tormule pentru o serie de date. Prima coloanå a tabelei sau prima linie a acesteia poate con¡ine valorile testate prin tormulå(e). Respectivul câmp va ti selectat de cåtre utilizator înainte de activarea comenzii Data Table

- -         tormula(tormulele) este plasatå pe prima linie a câmpului de ipoteze; acestea pot ti de tip baze de date sau tormule bazate pe un interval de varia¡ie (de tip PMT, SYD, SLN, DB, DDB etc).

Celula din col¡ul superior stâng al câmpului de ipoteze nu intervine în calcule, con¡inutul såu poate ti låsat la libera ini¡iativå a utilizatorului.

Comanda Data Table permite completarea automatå a tabloului ca si cum tormula sau tormulele situate pe prima linie ar ti aplicate succesiv pentru tiecare valoare gåsitå în prima coloanå sau în prima linie, aceste valori reprezentånd parametrii sintetizårii intorma¡iei.

Câmp de criterii B17:B18

Formulå:=DSUM(A1:F14;”Salarii;B17:B18)

Zonå unde se afi¿eazårezultatele în urmacomenzii Data Table

Tabela de ipoteze B20:C25

Pentru în¡elegerea mecanismului de sintetizare a intorma¡iei se turnizeazå urmåtorul exemplu:

Plecând de la o bazå de date pentru eviden¡a personalului unei societå¡i comerciale, se calculeazå suma salariilor pe compartimente tunc¡ionale. Tabelul ce con¡ine baza de date va ti detinit pe coordonatele A1:T14, tigura 1.122.

Plecând de la aceeasi bazå de date, ne propunem så calculåm suma salariilor pe compartimente tunc¡ionale numai pentru personalul angajat în cursul ultimului an si care are salariile peste media salariilor, tigura 1.123.

Tig. 1.123 Baza de date pentru tabela de ipotezePentru a råspunde acestor restric¡ii este necesarå construirea unei interogåri a

bazei de date care så con¡inå în câmpul de criterii condi¡iile enun¡ate (cele reteritoare la data angajårii si la salariu);

¥nregistrårile care vor îndeplini cumulativ cele 2 restric¡ii ale câmpului de criterii vor ti extrase ca valori unice într-un câmp de rezultate care va ti situat într-o altå loca¡ie a toii de calcul si care va con¡ine acele rubrici necesare pentru detinirea tabelei de ipoteze (în cazul de ta¡å rubrica “Compartiment”;

Extragerea din baza de date se va tace prin intermediul tunc¡iei DSUM care are ca argumente: baza de date, rubrica pe care se opereazå calculul si câmpul de criterii (în care sunt precizate cele 2 restric¡ii;

Acest exemplu îmbinå tacilitå¡ile de interogare a bazelor de date prin criterii calculate cu tacilitå¡i de sintetizare a intorma¡iei prin regrupare potrivit criteriilor enun¡ate.

Pentru rezolvarea aplica¡iei se parcurg urmåtoarele etape:1. 1.                       se construieste câmpul de criterii pentru interogare care poate

con¡ine una (compartimentul) sau toate rubricile bazei de date + criteriul calculat (=Data angajarii >TODAY() – 360 si =Salariul >AVERAGE(T2:T14) );

¥ntr-un criteriu calculat, rubrica de comparat este reprezentatå de prima celulå plasatå sub rubrica respectivå. Asttel, în loc de “Data angajårii” se va scrie prima adreså a domeniului, adicå C2, iar pentru “Salariul” se va introduce adresa T2.

Tabela bazei de date A1:F14

2. 2.                       se vor extrage din baza de date potrivit criteriului speciticat acele înregistråri unice care îndeplinesc cumulativ restric¡iile enun¡ate; câmpul de rezultate va ti detinit într-o altå loca¡ie a toii de calcul si va deveni ulterior parametrul câmpului de ipoteze pentru care o tormula va calcula serii diterite de rezultate (extragerea se tace prin Data- Tilter- Advanced Tilter, marcând caseta se selectare Unique Record Only);

3. 3.                       se scrie tormula de calcul (=DSUM()) în col¡ul superior drept al câmpului de ipoteze- tormula se va aplica pentru tiecare compartiment în parte însumând salariile pe respectivul criteriu de regrupare;

4. 4.                       se selecteazå câmpul de ipoteze5. 5.                       se activeazå comanda Data Table si în caseta Table se

completeazå adresa celulei care con¡ine criteriul în rubrica Column Input Cell- tabela de ipoteze tiind orientatå în acest caz pe coloanå ( în cazul organizårii tabelei pe linie…)

6. 6.                       validând cu butonul OK, calculele se vor etectua automat pentru tiecare compartiment în parte, tigura 1.124.

Tig. 1.124 Tabela de ipoteze cu o singura variabilå

Un alt exemplu derivat din primul, vizeazå calculul mediei salariilor pe compartimente. De aceastå datå, datele vor ti organizate orizontal- pe linie- si în consecin¡å se va completa adresa celulei care con¡ine criteriul în rubrica Row Input Cell aterentå casetei de dialog Table. Tigura 1.125 ilustreazå acest exemplu.

Tig. 1.125 Tabelå de ipoteze cu osingura variabilå (II)

 Rezultatele calculului mediei salariilor se gåsesc retlectate în tigura 1.126.

=DAVERAGE(A1:F14;”Salariu”;B23:B24)

Tabela de ipoteze dispuså orizontal

Tabelå de ipotezedispuså vertical

Tig. 1.126 Rezultatul tinal (I)Tabela de ipoteze cu o singurå variabilå poate permite calcularea mai multor serii

de rezultate, utilizând mai multe tormule. Asttel, dacå s-ar ti dorit calcularea sumei si mediei salariilor economistilor, tabela

de ipoteze ar con¡ine trei coloane, una pentru marcarea valorilor studiate, adicå parametrii, a doua pentru rezultatele calculului sumei salariilor si a treia pentru calculul mediei salariilor. Câmpul de criterii (B17:C18) a tost completat cu rubrica “Tunc¡ia” pentru a sintetiza intorma¡ia reteritoare la economisti. Tabelul ce con¡ine sursa de date pentru sintetizare a tost declarat la adresa A3:E15. Schema de organizare a intorma¡iilor în tabela de ipoteze este prezentatå în tigura 1.127.

Tabela de ipoteze –declaratå la adresa B20:D25 - va calcula suma si media (prin tormulele DSUM si DAVERAGE) salariilor corespunzåtoare tunc¡iei de economist. ¥n urma comenzii Data Table si precizarea adresei celulei vide (B18) în rubrica Column input cell, se vor genera automat rezultatele (tigura 1.128).

 

Tig. 1.127 Tabelå de ipoteze (III)Un alt gen de aplica¡ii pentru sintetizarea intorma¡iei prin tabela de ipoteze cu o

variabilå, vizeazå tunc¡iile tinanciare, de exemplu:så se calculeze sumele rambursabile lunar (tunc¡ia PMT) aterente unui

Tig. 1.128 Rezultatele tinale (III)credit de 80 de milioane lei, contractat pe 3 ani, pentru mai multe variante de ratå

a dobânzii (aplica¡ie exempliticatå în tigura urmåtoare), sau så se calculeze aceleasi sume

Câmpde

criterii

rambursabile lunar, aterente aceluiasi credit, cu o dobândå tixå de 60%, pentru mai multe variante de perioade de rambursare:

Alte aplica¡ii economice de sintetizarea intorma¡iei, ce pot ti rezolvate cu tabela de ipoteze cu o variabilå, ar putea ti:

- -         se calculeazå prin sintetizarea intorma¡iei maximul, minimul si media salariilor angaja¡ilor cu tunc¡ia “economist” din compartimentele “tinanciar” si “contabilitate” (începând din anul 1990) si se reprezintå gratic printr-o diagramå de amplitudine varia¡ia salariilor pe cele douå compartimente;

- -         se reprezintå gratic numårul total de angaja¡i pe tiecare compartiment tunc¡ional si suma salariilor acestora;

Tig. 1.129 Tabelå de ipoteze (IV) Tabela de ipoteze cu douå variabile  Tabela de ipoteze cu douå variabile tunc¡ioneazå dupa aceleasi principii ca si

tabela cu o variabilå, dar permite varia¡ia simultanå a doi parametrii, necesitând în acest caz douå celule de intrare. Structura unei tabele de ipoteze cu douå variabile este urmåtoarea:

- -         valorile primului parametru (prima celulå de intrare) se atlå în coloana stângå a câmpului de ipoteze, iar valorile celui de-al doilea parametru (a doua celulå de intrare) se atlå pe prima linie;- -         tormula pe care este construitå sintetizarea se introduce în celula col¡ului superior stâng al câmpului de ipoteze ca si când s-ar aplica celor douå celule de intrare.

Atunci când comanda Data Table este executatå, câmpul de ipoteze este completat în mod automat cu rezultatele calculelor etectuate pentru tiecare linie si coloanå a tabloului. Tiecare intersec¡ie a unei linii cu o coloanå turnizeazå rezultatul tormulei aplicate valorilor liniei si coloanei.Pentru exempliticare, vom lua urmåtoarea aplica¡ie:

Se va calcula prin sintetizarea intorma¡iei suma salariilor pe tunc¡ii si pe compartimente tunc¡ionale.

Pentru rezolvarea aplica¡iei se parcurg urmåtoarele etape:- -         se construieste tabela de ipoteze, completând pe prima linie tunc¡iile

salaria¡ilor pentru care se tace sintetizarea prin însumare si pe prima coloanå compartimentele tunc¡ionale pentru care så se tacå respectiva regrupare;

- -         se editeazå câmpul de criterii pe coordonatele B30:C31, câmp ce va con¡ine rubricile: “Compartiment” si “Tunc¡ia”;

- -         se introduce tormula de însumare =SUM(A3:E15;5;B30:C31) în col¡ul superior stâng al tabelei de ipoteze (la adresa B35). Tormula va genera rezultate centralizate pentru tiecare tunc¡ie si compartiment în parte;

- -         se selecteazå tabela de ipoteze (B35:T40);- -         se activeazå comanda Data Table si în caseta Table (tigura 1.130) se

completeazå adresele celulelor de intrare asttel:- -                     în rubrica Column input cell se marcheazå adresa celulei

vide corespunzåtoare compartimentului, adicå B31;- -                     în rubrica Row input cell se marcheazå adresa celulei

corespunzåtoare tunc¡iei, adicå C31;

Tig. 1.130 Tabelå de ipoteze cu douå variabilePrin validarea ac¡iunii cu butonul OK, sintetizarea intorma¡iei prin însumare se va

etectua automat pentru tiecare compartiment si tunc¡ie în parte. Tigura 1.131 prezintå rezultatele grupårii datelor.

O a doua aplica¡ie a tabelei de ipoteze cu douå variabile vizeazå calculul amortizårii accelerate: Se considerå o investi¡ie (un mijloc tix) în valoare de 12mil de lei (un calculator); valoarea rezidualå estimatå a imobilizårii este de 3 mil lei dupå expirarea duratei normate de tunc¡ionare care este de 6 ani; nr de perioade pentru care se calculeazå amortizarea este de 6 ani.

Tig. 1.131 Rezultatul tinal (I)

Tig. 1.132 Tabel de ipoteze cu douå variabile

Tig. 1.133 Rezultatul tinal (II)Se va genera un tablou de amortizare acceleratå pe ani de tunc¡ionare si pe durata

pentru care se calculeazå amortizarea.Modul de construire al tabelei cu douå variabile este prezentat în tigura 1.132. Diteren¡a ta¡å de exemplul anterior constå în taptul cå, în cazul de ta¡å celulele de

intrare nu sunt vide si neprotejate, ci sunt chiar argumentele tunc¡iei tinanciare DB.Rezultatul sintetizårii intorma¡iei –prezentat în tigura 1.133- reprezintå chiar

tabloul de amortizare al respectivei imobilizåri. 1.6.1.2 Tabele pivot

 Tabela pivot, ca instrument de asistare a deciziei reprezintå o tacilitate prin care

datele dintr-o toaie de calcul pot ti permutate pentru a se pune în eviden¡å noi intorma¡ii. Tabela pivot permite crearea unui tablou de sintezå în care rubricile unui tabel sau

unei baze de date pot ti permutate pe linie sau pe coloanå, asupra datelor operându-se agregåri si calcule sub tormå de totaluri, medii, min, max. etc. Aceste opera¡ii sunt alese dintr-o listå de tunc¡ii predetinite si se pot opera calcule diterite asupra acelorasi elemente supuse sintetizårii.

Tig. 1.134 Baza de date pentru tabela pivotDeci tabela pivot este un instrument care permite o toarte elasticå asociere a unor

câmpuri într-o manierå interactivå, tapt ce duce la regruparea datelor si prezentarea acestora într-un mod sintetic.

Tabela pivot se creeazå selectând sursa datelor de sintetizat si operând comanda Data - Pivot Table Report, dupå care un asistent Pivot Table Wizard îndrumå utilizatorul în 4 pasi.

Un exemplu editicator de construire si utilizare a tabelei pivot ar consta în sintetizarea intorma¡iei pentru o editurå, reteritoare la vânzårile de carte;Tie o tabelå (tigura 1.134) aterentå unei baze de date care regrupeazå intorma¡ii reteritoare la vânzårile de carte de intormaticå pe orase (Bucuresti, Ploiesti, Bacåu, Iasi), pe doi ani (1997/1998), pe agen¡i de vânzare (Rådulescu S, Ionescu L, Popescu M, Vasilescu D, Moisescu A.), pe cantitå¡i vândute (tiecare carte poate ti vândutå în mai multe orase si acelasi agent de vânzare poate opera vânzåri pentru un titlu de carte sau mai multe, în acelasi oras sau în mai multe) si pe valori reiesite din vânzåri. Pre¡ul cår¡ii se poate extrage (cu tunc¡ia VLOOKUP) dintr-un tablou de consultare verticalå. Volumul vânzårilor realizate de cåtre tiecare agent de vânzare este rezultatul înmul¡irii cantitå¡ii vândute cu pre¡ul cår¡ilor. ¥n rezolvarea aplica¡iei, asistentul tabelei pivot (prin comanda Data - Pivot Table Report) parcurge urmåtoarele patru etape (prin apåsarea butonului Next> pentru tiecare etapå în parte):

1. ¥n prima etapå Pivot Table Wizard -Step 1 ot 4 (tigura 1.135) se alege sursa de date pentru sintetizarea intorma¡iei:- dintr-un tabel Excel sau o bazå de date (Microsott Excel List or Database);- dintr-o bazå de date externå (External Data Source);- din câmpuri multiple consolidate (Multiple Consolidation Ranges);- din altå tabelå pivot (Another Pivot Table).

Tig. 1.135 Tabela pivot, pasul 1¥n prealabil sursa de date poate ti selectatå sau se poate pozi¡iona cursorul pe

prima celulå a sa (de exemplu A3).2. ¥ntr-o a doua etapå PivotTable Wizard - Step 2 ot 4, tig.1.136, se

valideazå tabela surså pe baza cåreia se va construi tabela pivot sau dacå sursa de date nu a tost selectatå anterior, aceasta se poate selecta în aceastå etapå prin completarea în rubrica Range.

Tig. 1.136 Tabela pivot, pasul 23. ¥ntr-o a treia etapå Pivot Table Wizard- Step 3 ot 4 (tig. 1.137) are loc

selectarea datelor, prin care se precizeazå care rubricå(rubricii) va(vor) ti plasatå(e) pe linie, si care pe coloanå. Plasarea rubricilor pe linie sau pe coloanå se tace prin glisarea rubricii respective într-o casetå de sintetizare, existând posibilitatea agregarii datelor prin plasarea mai multor rubrici (nivel descrescåtor de centralizare) pe linie sau pe coloanå. ¥n plus existå si posibilitatea declarårii mai multor restric¡ii prin glisarea unuia sau mai multor elemente centralizatoare pe paginå (Page).

Caseta de dialog aterentå celei de a treia etapå, con¡ine o machetå-sablon unde vor ti glisate rubricile datelor de sintetizat asttel:

ROW : pozi¡ioneazå elementele câmpului (rubricii) pe linie;COLUMN: pozi¡ioneazå elementele câmpului (rubricii) pe coloanå;DATA : sintetizeazå elementele rubricii prin calcul, la nivelul întregii baze de

date, la intersec¡ia valorilor rubricilor dispuse pe linie sau pe coloanå;PAGE: regrupeazå elemente de centralizare ale bazei sau sursei de date pentru

care intorma¡ia este sintetizatå pe linie, pe coloanå, pe linie si pe coloanå.

¥n cazul de ta¡å, pentru rezolvarea sintetizårii "Volumul vânzårilor de carte de intormaticå pe titluri si pe ani", se va glisa rubrica "Denumire carte" pe linie (ROW) si rubrica "Anul" pe coloanå (COLUMN), iar rubrica "Valoarea vânzårilor" va ti glisatå în centrul machetei (DATA) pentru totalizare.

Tig. 1.137 Tabela pivot, pasul 34. ¥n a patra etapå PivotTable Wizard - Step 4 ot 4, (tig. 1.138) se stabileste

adresa tabelei pivot, si anume dacå aceasta se va plasa într-o nouå toaie de calcul (New worksheet), sau în toaia de calcul existentå, la o anumitå adreså (Existing worksheet).

Tig. 1.138 Tabela pivot, pasul 4Tot în aceastå etapå se pot stabili si anumite op¡iuni ale tabelei pivot, prin

apåsarea butonului Options, prin caseta de dialog PivotTable Options (tig. 1.139):- stabilirea numelui tabelei pivot (rubrica Name);- etectuarea unor calcule de total general pe coloane (Grand totals tor columns);

Rubricåplasatå pelinie

RubricåPlasatå pecoloanå

Rubricå desintetizareprin calcul

Centralizare

Tig. 1.139 Tabela pivot, pasul 4, Options- -         etectuarea unor calcule de total general pe linii (Grand totals tor rows);

Tig. 1.140 Tabela pivot, rezultatul tinal (I)

- salvarea datelor împreunå cu pagina tabelei pivot (Save data with table layout);- aplicarea unui tormat predetinit automat pentru tabela pivot (AutoTormat

table).Procedura de generare a tabelei pivot se încheie prin apåsarea butonului Tinish.Tabela pivot asttel construitå este prezentatå în tigura 1.140.Tabela pivot, ca instrument de asistare a deciziei permite o sintetizare în trepte a

intorma¡iei, grupând-o pe niveluri descrescåtoare de centralizare. Din punct de vedere tehnic, acest lucru se poate realiza grupând mai multe rubrici pe linie sau pe coloanå si etectuând mai multe tipuri de calcule pentru aceeasi rubricå sau pentru rubrici diterite.

Condi¡ia pentru sintetizarea în trepte pe linie sau pe coloanå este aceea ca prima rubricå plasatå pe linie sau pe coloanå så con¡inå mai multe elemente (ce vor ti regrupate) aterente celei de a doua rubrici plasate pe linie sau pe coloanå, iar a doua rubricå plasatå pe linie sau pe coloanå så con¡inå elemente aterente celei de a treia rubrici, s.a.md. Alttel spus, elementele sunt grupate pe linie sau pe coloanå dupå gradul lor de sintetizare, de la cel mai cuprinzåtoe element la cel mai pu¡in cuprinzåtor.

Agregarea în trepte a intorma¡iilor permite deci ob¡inerea 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ândutå de cåtre agen¡ii comerciali pe titluri de carte, pe ani si pe orase de distribu¡ie.

Tig. 1.141 Tabela pivot, pasul 3 (II)Moditicarea dispunerii rubricilor în macheta-sablon se tace plecând de la tabela

pivot generatå în cele patru etape, asttel:- -         se pozi¡ioneazå cursorul oriunde în interiorul tabelei pivot;- -         se activeazå comanda Data – PivotTable Report, ajungându-se în

etapa a 3-a de construire a tabelei pivot (PivotTable Wizard 3 ot 4), tigura 1.141;- -         se recontigureazå rubricile prin glisare în macheta-sablon pe linie, pe

coloanå, pe paginå sau în zona de calcule DATA, dupå care se apaså butonul NEXT;- -         se parcurge etapa a 4-a, adicå se alege amplasamentul tabelei pivot si

anumite op¡iuni aterente acesteia (tigura 1.142).Anularea unei rubrici se poate tace urmând primii doi pasi de la procedura de

moditicare, dupå care (în pasul PivotTable Wizard 3 ot 4) rubrica de anulat va ti glisatå din macheta-sablon în atara acesteia (în partea dreaptå), alåturi de celelalte rubrici care nu participå la sintetizarea intorma¡iei.

Tig. 1.142 Tabela pivot, rezultatul tinal (II)

Plecând de la tabela pivot detinitå anterior, s-a dorit recontigurarea acesteia prin moditicare pentru a ob¡ine, în mod dinamic pe ani si pe orase de distribu¡ie, suma cantitå¡ilor de carte vândutå, suma valoricå a cår¡ilor vândute pe titluri de carte si pe agen¡i de distribu¡ie.

Sintetizarea dinamicå presupune precizarea de restric¡ii la nivel de paginå, prin alegerea unui element din lista de valori aterente rubricii, de exemplu “Oras distribu¡ie” = Bucuresti si “Anul” = 1997.

 Recontigurarea rubricilor este prezentatå în tigura 1.143.

Tig. 1.143 Tabela pivot, recontigurarea rubricilorTabela pivot moditicatå si recontiguratå dinamic este prezentatå în tigura 1.144

Tig. 1.144 Tabela pivot, recontiguratå dinamic

Un alt procedeu de moditicare-anulare a rubricilor ce participå la sintetizarea intorma¡iei este urmåtorul:

- -         se selecteazå din tabela pivot rubrica de moditicat sau anulat (nu elementele acesteia);

- -         se executå un dublu-click pe rubrica respectivå;- -         în caseta de dialog PivotTable Tield (tigura 1.145);

- -                     se poate anula rubrica prin apåsarea 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 coloanå (Column), pe paginå (Page);- -                     se pot redetini sau anula elemente de calcul în rubrica

Subtotals. ¥n mod implicit–Automatic- se calculeazå numai suma, dar se pot realiza si alte calcule – Custom- cum ar ti medie, produs, maxim, minim, etc, sau prin op¡iunea None calculele vor ti inhibate;

- -                     se pot ascunde anumite elemente ale rubricii respective (“Agent vânzare”), prin marcarea acestora în caseta Hide items (de exemplu, dacå se marcheazå elementul Ionescu L. acesta nu va mai apare în tabela pivot ca element sintetizat.

- -                     se poate anula atisarea elementelor sintetizabile ce au valori nule, prin marcarea casetei de selectare Show items with no data.

Tig. 1.145 Tabela pivot, moditicarea rubricilor

Pentru a schimba opera¡iile de calcul tåcute asupra rubricilor, de exemplu moditicarea valorii vânzårilor din sumå în medie (sau în produs, maxim, minim, etc.), se activeazå prin dublu-clik în procedura de moditicare (PivotTable Wizard 3 ot 4) câmpul de calcul (în caseta DATA – Sum ot Valoare vânzåri) asupra cåruia opereazå respectiva moditicare a opera¡iei de calcul si în caseta de dialog PivotTable Tield, la rubrica Summarize by se schimbå din opera¡ia din Sum în Average, validându-se opera¡ia cu OK, tig. 1.146 si 1.14.

 

Tig. 1.146-1.147 Tabela pivot, moditicare rubriciCaseta de dialog PivotTable Tield, prin butonul Options>> permite etectuarea si

altor opera¡ii ce permit modalitå¡i diterite de prezentare a datelor sintetizate. Asttel, din

lista derulantå Show data as se pot alege opera¡ii ca: “diteren¡å ta¡å de …, % ta¡å de…" etc. Aceste opera¡ii se reterå la rubricile tabelei pivot (Base tield) si se aplicå elementelor acestor rubrici (Base item). De exemplu se poate construi urmåtoarea sintetizare: suma vânzårilor pe ani, ca diteren¡å ta¡å de anul 1998.

Tabela pivot permite ascunderea sau atisarea unor nivele de sintetizare. De regulå nivelul de sintetizare ce urmeazå a ti inhibat (sau ascuns) trebuie så tie în mod obligatoriu ierarhic interior ca agregare, ta¡å de primul nivel care este mai cuprinzåtor. ¥n exemplul luat (tig. 1.148), rubrica “Agent vânzare” este pe un plan ierarhic secundar ta¡å de rubrica “Titlu carte” (un titlu de carte este vândut de mai mul¡i agen¡i de vânzare). Pentru a ascunde un nivel de sintetizare, se selecteazå din tabela pivot, rubrica superioarå în ierarhie ta¡å de rubrica de inhibat (de exemplu rubricile “Titlu carte” si “Anul”) si se activeazå meniul Data, op¡iunea Grup and Outline, subop¡iunea Hide detail. Pentru a reatisa respectivul nivel ierarhic inhibat, se procedeazå asemånåtor, cu deosebirea cå se activeazå subop¡iunea Show Detail.

Tabela pivot permite sintetizarea unor elemente disparate, prin gruparea acestora. Asttel dacå se selecteazå din tabela pivot douå orase (Bucuresti, Ploiesti) si se activeazå comanda Data – Group and Outline – Group, cele douå elemente vor conta în sintetizarea intorma¡iei ca tiind grupate (Group1), tig.1.149.

Disocierea elementelor grupate se tace selectând grupul si ac¡ionând comanda inverså: Data – Group and Outline – Ungroup.

Tig. 1.148 Tabela pivot, rezultatul ascunderii datelor (I)

Moditicarea tabelei pivot, atisarea sau ascunderea unor detalii, gruparea

Tig. 1.149 Tabela pivot, rezultatul ascunderii datelor (II)

 sau disocierea unor elemente disparate sunt opera¡ii ce se pot realiza si cu ajutorul unei bare de butoane aterente tabelei pivot. Bara de butoane se activeazå prin comanda View – Toolbars – Pivot Table. Semnitica¡ia butoanelor este prezentatå în tigur 1.150.

a) b) c) d) e) t) g) h) i) j) k)

Tig. 1.150 Bara de butoane pentrutabela pivota) a)      PivotTable Wizard – permite moditicarea tabelei pivot prin

intermediul “asistentului” acesteia;b) b)      PivotTable Tield – permite moditicarea unei rubrici prin permutarea

acesteia pe linie, coloanå, paginå sau dacå este vorba de o opera¡ie de calcul, aceasta se poate moditica;

c) c)      Show Pages – permite vizualizarea mai multor pagini ale tabelei pivot;

d) d)      Ungroup – permite disocierea elementelor grupate;e) e)      Group – permite gruparea mai multor elemente într-o tabelå pivot;f) t)       Hide Detail – inhibå atisarea unui nivel agregat;g) g)      Show Detail – atiseazå un nivel agregat care a tost anterior inhibat;h) h)      Retresh Data – actualizeazå datele din tabela pivot, dacå sursa de date

pe care aceasta a tost construitå s-a moditicat;i) i)        Select Label – selecteazå o rubricå;j) j)        Select Data – selecteazå datele unei rubrici;k) k)      Select Label and Data – selecteazå o rubricå cu elementele aterente.

O ultimå tacilitate a tabelei pivot, ar ti aceea de extragere în altå toaie de calcul a unor intorma¡ii, potrivit unui criteriu. Acest lucru este posibil printr-un dublu-clik executat pe un element aterent unei rubrici sau atlat la intersec¡ia a douå rubrici.

Tig. 1.151 Rezultatul extragerii De exemplu plasând cursorul la intersec¡ia a douå rubrici, anume Oras distribu¡ie

= Bucuresti si Birotica Total, rezultå în urma unui dublu-click o extragere tip bazå de date, pentru vânzårile totale de carte cu titlul “Biroticå” în Bucuresti (tig. 1.151).

 1.6.1.3 Gruparea intorma¡iei prin generarea de subtotaluri

 Sub Excel existå posibilitatea sintetizårii intorma¡iei prin organizarea ei pe

niveluri de grupare, iar apoi pot opera diterite calcule pe aceste grupuri sau intorma¡ii centralizate. Alttel spus Excel poate organiza datele prin grupare pentru generarea de totaluri si subtotaluri.

Tig. 1.152 Rezultatul tiltrårii datelorPentru gruparea datelor care se doresc a ti totalizate este necesarå sortarea

acestora pe rubricile de grupare. De exemplu, baza de date detinitå anterior (la tabela de ipoteze cu o variabilå) pe coordonatele A3:E17 va ti sortatå ascendent pe rubricile "Compartiment", "Tunc¡ie" (un compartiment con¡ine salaria¡i cu tunc¡ii diterite) si descendent pe rubrica "Marca".

Sortarea se tace cu ajutorul comenzii Data Sort (baza de date nu trebuie neapårat selectatå, tiind suticientå pozi¡ionarea cursorului pe una din rubrici). ¥n caseta de dialog Sort se precizeazå cheia (cheile) de sortare Sort By (Then By) si ordinea sortårii: crescåtoare (Ascending) sau descrescåtoare (Descending).

Tigura 1.152 ilustrazå rezultatul sortårii datelor dupå rubricile "Compartiment" (Sort By); "Tunc¡ia" (Then By); "Marca" (Then By).

Dupå ce datele au tost sortate dupå cele trei chei de sortare, acestea pot ti totalizate pe câmpurile de grupare. Subtotalizarea se tace prin pozi¡ionarea pe prima celulå a bazei de date (sau prin selectarea acesteia) si activarea comenzii Data – Subtotals. Prin caseta de dialog Subtotal se deståsoarå tehnica de regrupare a intorma¡iei, asttel:

- -  se precizeazå rubrica pentru care se tace gruparea. Asttel, din lista derulantå At Each Change in (în cazul de ta¡å) se alege rubrica pe care se tace gruparea sau unde are loc “ruptura de secven¡å” "Compartiment";

- -  se alege tipul de opera¡ie care va aplica datelor regrupate (Sum; Max; Min; Avarage; Count; Product) din caseta Use Tunction;

Tig. 1.153 Caseta de dialog SUBTOTAL

- -  în tinal se alege rubrica de calculat prin selectarea acesteia - în cazul de ta¡å "Salariu";

- -  dupå precizarea acestor elemente se valideazå cu butonul OK.Caseta de dialog Subtotal este prezentatå în tigura 1.153.

Tig. 1.154 Rezultatul grupårii datelorRezultatul grupårii si totalizårii datelor este prezentat în tigura 1.153.

¥n urma totalizårii, toaia de calul si-a schimbat aspectul în sensul apari¡iei unor elemente (+ si -) care semniticå gradul de grupare a datelor. Alttel spus, tabelul pentru care s-a tåcut totalizarea a tost ierarhizat.

Ierarhizarea elementelor regrupate permite alegerea pentru vizualizare sau pentru prelucråri ulterioare (de exemplu reprezentåri gratice de structurå) a nivelului dorit asttel încât så tie relevatå intorma¡ia de care utilizatorul are nevoie.

Tig. 1.155 Prezentarea ierahizatå a datelorRevenirea atisarii din torma ierarhizatå în torma normalå se tace prin selectarea

tabelului sau pozi¡ionarea cursorului pe prima celulå a acestuia si activarea comenzii Data Subtotals, iar din caseta Subtotal va ti apåsat butonul Remove All (tig. 1.155).

Niveluri de ierarhizare:1 afi¿eazå numai totalul general (Grand Total)2 afi¿eazå subtotaluri (totaluri intermediare) ¿i totalul general3 afi¿eazå elementele de totalizat, subtotalurile ¿i totalul genetal

- - restrânge ierarhia- + expandeazå ierarhia

 1.6.1.4 Sintetizarea datelor prin consolidare 

Consolidarea datelor din toile de calcul semniticå regruparea acestora prin utilizarea acelorasi coordonate într-o toaie de calcul centralizatoare.

Consolidarea datelor implicå tacilitå¡i de exploatare tridimensionalå pentru toile de calcul. Datele ce urmeazå a ti consolidate se pot gåsi în toi de calcul separate sau în tisiere (documente de calcul tabelar) distincte.

Procedura de consolidare implicå participarea tiecårei toi de calcul sau tisier la o opera¡iune de centralizare. Rezultatul acestei opera¡ii se poate gåsi tie în acelasi tisier, dar într-o toaie de calcul distinctå, tie în alt tisier. Este toarte important ca datele ce urmeazå a ti centralizate så aibå o schemå unicå de organizare în toile de calul ce constituie surså a consolidårii. Alttel spus, datele de centralizat trebuie så tie editate la niste adrese tixe, pe baza sablonårii acestora, asttel încât coordonatele celulare ale intorma¡iilor de consolidat så tie aceleasi din punct de vedere al exploatårii tridimensionale a toilor de calcul.

Din punctul de vedere al surselor de date ce participå la gruparea intorma¡iei, consolidarea poate ti internå sau externå.

Consolidarea internå a datelor presupune participarea mai multor toi de calcul ale aceluiasi tisier, organizate identic din punct de vedere al reterin¡elor celulare, la centralizarea datelor într-o toaie de calcul distinctå, în cadrul aceluiasi document de calcul tabelar.

Consolidarea internå a datelor poate ti tåcutå în douå moduri:- -         printr-o procedurå specialå, generatå prin comanda Data Consolidate;- -         prin utlilizarea tormulelor de calcul cu reterin¡e 3-D.

 

Tig. 1.156 Variante de bugetPentru exempliticarea opera¡iunii de consolidare, propunem urmåtoarea aplica¡ie:

Un institut de tormare doreste så-si centralizeze intorma¡iile legate de bugetele de cheltuieli aterente cursurilor de instruire pe care le tace. Tiecare buget de curs se gestioneazå individual de cåtre un responsabil pedagogic. Presupunem cå respectivul institut gestioneazå trei cursuri, elaborând câte un buget pentru tiecare : Contabilitate, Intormaticå si Management. Tiecare buget de curs are aceleasi capitole: Cheltuieli salariale, Cheltuieli publicitare, Cheltuieli cu logistica, Cheltuieli administrative. Cele trei bugete au tost construite pe trei toi de calcul ce poartå numele tiecårui buget. Bugetele au tost sablonate, în sensul marcårii elementelor de centralizat în aceeasi ordine si pe aceleasi coordonate.

¥n tigura 1.156 sunt prezentate cele trei variante de buget pentru cursurile de contabilitate, intormaticå si management:

Pentru centralizarea datelor din cele trei toi de calcul, se pot urmåri douå procedee de consolidare.

Primul procedeu vizeazå consolidarea automatå prin intermediul comenzii Data – Consolidate, asttel:

1. 1.      se deschide noua toaie de calcul care va con¡ine rezultatele consolidårii;

1. 2.      se pozi¡ioneazå cursorul în prima celulå din stânga-sus (de regulå) sau într-o celulå ce desemneazå destina¡ia consolidårii datelor;

2. 3.      se activeazå comanda Data – Consolidate, tigura 1.157;3. 4.      în caseta de dialog Consolidate se executå urmåtoarele opera¡ii:

Tig. 1.157 Caseta de dialog pentru consolidare

a) a)                  se alege tipul de opera¡ie (Sum, Max, Min, Product, Average, etc.) care se va aplica datelor de consolidat, din lista derulantå Tunction (de regulå se alege suma);

b) b)                  în caseta Reterence 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 explica¡iilor aterente acestora;

c) c)                  se apaså butonul Add, zona selectatå anterior tiind înregistratå automat în caseta All Reterences;

d) d)                  se repetå succesiv ultimele douå proceduri pânå la epuizarea zonelor surså ce participå la consolidare;

e) e)                  se precizeazå modelul de organizare a datelor urmårit prin consolidare, prin caseta Use labels in. Consolidarea datelor pe categorii (niveluri ierarhizate de organizare a datelor) presupune si precizarea etichetelor si explica¡iilor

aterente datelor. Asttel, pot exista trei modele de consolidare : pe linie (caseta de selectare Top row), pe coloanå (Lett column) sau amândouå (cele douå casete de selectare sunt activate), dupå cum datele de consolidat sunt dispuse. Dacå niciuna din cele douå casete de selectare nu este activatå, consolidarea este consideratå a ti tåcutå dupå pozi¡ia datelor de consolidat, tårå a urmåri o eventualå regrupare a acestora.

f) t)                   se poate actualiza rezultatul consolidårii la moditicarea valorilor unor celule ce apar¡in de sursa consolidårii, prin activarea casetei de selectare Create links to source data;

g) g)                  în tinal procedura de consolidare se valideazå cu OK.Adåugarea de noi toi de calcul de sintetizat care så participe la elaborarea unui

rezultat consolidat se tace prin includerea acestora în caseta Reterence si apåsarea butonului Add. ªtergerea unui element care participå la consolidare se tace prin selectarea acestuia din lista All reterences si apåsarea butonului Delete.

¥n momentul creårii de legåturi (Create links to source data) între surså si destina¡ie, nu se mai pot adåuga noi surse de date ce participå la consolidare, nu se pot moditica sau sterge surse existente.

Tig. 1.158 Rezultatul consolidårii (I)Rezultatele consolidårii celor trei toi de calcul sunt prezentate în tigura 1.158.Dacå datele de consolidat sunt externe documentului în care se tace consolidarea,

opera¡iunea poate ti tåcutå precizând adresa tizicå de pe disc a surselor de date. Acest lucru este posibil prin marcarea completå în rubrica Reterences a speciticatorului de tisier si a toii de calcul din care provin sursele de date sau prin apåsarea butonului Browse si alegerea interactivå a coordonatelor datelor de consolidat.

Al doilea procedeu de consolidare vizeazå utilizarea tormulelor cu inciden¡å tridimensionalå. Asttel, urmårind exemplul precedent se pot sintetiza urmåtoarele etape de urmat:

- -  se deschide noua toaie de calcul care va con¡ine rezultatele consolidårii;- -  se pozi¡ioneazå cursorul în prima celulå din stânga-sus (de regulå) sau într-o

celulå ce desemneazå destina¡ia consolidårii datelor;

- -  se copiazå (Copy/Paste) etichetele datelor ce participå la consolidare- -  se genereazå o tormulå de calcul de regrupare utilizând reterin¡e tridimensionale.

Reterin¡ele 3-D includ pe lângå coordonatele coloanelor si liniilor, si numele toilor de calcul din care provin datele.

De exemplu, pentru a calcula suma salariilor cadrelor didactice de seminarizare, în celula B5 a toii de calcul de consolidare (“Buget CONSOLIDAT 2) s-a editat tormula 3-D :

=SUM(Contabilitate:Management!B5).

Tig. 1.159 Rezultatul cosolidårii (II) Tormula semniticå etectuarea unei sume pe coordonata celulei B5 aterentå toilor

de calcul cuprinse între toaia Contabilitate si Management. O altå tormulå 3-D ar putea viza reterin¡ele individuale ale celulelor care participå

la consolidare. De exemplu în celula B7 a toii de calcul de consolidare s-a editat tormula :=Contabilitate!B7+Intormatica!B7+Management!B7. Consolidarea datelor dupå aceastå procedurå este prezentatå în tigura 1.159.¥n utilizarea adreselor tridimensionale în procesul de consolidare a datelor, este

toarte important ca datele de centralizat så aibå acelasi model de organizare pe coordonate celulare.

- -         tormula 3-D poate ti copiatå la nivelul celorlalte celule, iar dacå cerin¡a anterioarå de påstrare a acelorasi coordonate tridimensionale este îndeplinitå, tabloul de consolidare va ti generat corect.

Consolidarea datelor ce provin din tisiere distincte prin acest procedeu presupune ca în tormula de consolidare så intervinå si speciticatorul de tisier de unde provin datele de consolidat.

Asttel de exemplu dacå cele trei bugete ar ti tost construite pe tisiere separate: Contabilitate.XLS, Intormatica.XLS, Management. XLS, tormula de consolidare a datelor pentru Cheltulielile publicitare (celula B7) ar ti :

=[Contabilitate.xls]Sheet1!B7+[Intormatica.xls]Sheet1!B7+[Management.xls]Sheet1!B7 (tig. 1.160)

 

Tig. 1.160 Buget consolidat 1.6.2 Instrumentele de simulare si optimizare 

Instrumentele de simulare si optimizare cuprind tacilitå¡i puternice de modelare a unor probleme tormulate în sensul gåsirii unor solu¡ii care så råspundå la un ansamblu de restric¡ii, în sensul simulårii si optimizårii acestora.

 1.6.2.1 Tehnica valorii scop sau de cåutare a rezultatului 

Tehnica cåutårii rezultatului permite stabilirea unei valori tinale (scop sau obiectiv) pentru o tormulå pentru ca apoi så moditice valoarea uneia din celulele utilizate în tormulå pentru a calcula valoarea tinalå.

Utilizând cåutarea tip “rezultat” se poate ajusta o estimare pentru a se ajunge la o concluzie reteritoare 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 Tools - Goal Seek... unde prin caseta de dialog Goal Seek se stabilesc:

• celula scop sau obiectiv (rubrica Set cell:) care va ti ajustatå automat la o valoare corespunzåtoare;

• o valoare care desemneazå rezultatul care se doreste a se ob¡ine (rubrica To value:);

• celula ce con¡ine valoarea care trebuie moditicatå (rubrica By changing cell:)Pe scurt, pentru a gåsi o valoare scop sau obiectiv, tormularea ar ti urmåtoarea :

cu cât (sau la cât) ar trebui moditicat un parametru (By changing cell) pentru ca o valoare scop (Set cell) så atingå un prag speciticat (To value).

Tig. 1.161 Aplica¡ie Goal SeekCelula al cårei con¡inut va ti moditicat (By changing cell) trebuie så con¡inå o

valoare (care participå în mod nemijlocit la tormarea rezultatului) si nu o tormulå, în timp ce valoarea scop sau obiectiv (Set cell) trebuie så con¡inå în mod obligatoriu o tormulå.

Pentru cåutarea simultanå a rezultatelor dupå mai multe valori, nu se toloseste Goal Seek ci se modeleazå o problemå de optimizare tolosind Solver-ul. Exemplul din tig.1.161 ilustreazå un buget previzional simpliticat al cursurilor postuniversitare.

Bugetul calculeazå veniturile, cheltuileile, precum si marja brutå ce se degajå din aceste cursuri. Marja brutå calculatå la un numår de 30 de cursan¡i este de 23%.

Tig. 1.162 Utilizarea tehnicii Goal SeekDacå s-ar dori calcularea eticien¡ei cursurilor pentru un prag estimat la 30% marjå

brutå si s-ar pune întrebarea "de câ¡i cursan¡i ar ti nevoie pentru a atinge o marjå de 30%”, problema s-ar rezolva prin tehnica valorii scop asttel:

- se pozi¡ioneazå (de regulå) cursorul pe tormula ce con¡ine valoarea scop si se activeazå comanda Tools – Goal Seek;

Tig. 1.163 Utilizarea tehnicii valorii-scop- se seteazå în rubrica Set cell: celula C15 care con¡ine valoarea scop - adicå marja

brutå (dacå în prealabil nu s-a pozi¡ionat cursorul pe aceastå valoare);- se stabileste valoarea obiectiv în rubrica To value: la care så trebuie så ajungå

valoarea scop - în cazul de ta¡å 30%;- -  se stabileste ce valoare så se schimbe în rubrica By changing cell: (celula C5) -

în cazul prezentat numårul de cursan¡i- pentru a se ajunge la obiectivul propus; - -  - se valideazå cu OK (tig. 1.162). Calculul este tåcut automat pentru a se atinge

valoarea scop si în celulele respective vor apare noile valori (34 cursan¡i pentru o marjå brutå de 30%). Dacå se valideazå cu butonul OK vechile valori vor ti înlocuite cu noile valori calculate pentru obiectivul tixat, iar dacå se activeazå Cancel, vechile valori vor ti restaurate (tig. 1.163).