excel6-pivoti

8/14/2019 Excel6-pivoti

1/33

1.6 INSTRUMENTE DE SINTETIZARE A INFORMAIEI I DE

ASISTARE A DECIZIEI

Procesorul de tabele Excel, alturi de facilitile cunoscute de calcul tabelar, de

generare de grafice i de exploatare a bazelor de date, posed mai multe tehnici de

prelucrare a datelor prin care informaia poate fi agregat, centralizat, regrupat,

simulat sau optimizat.

Aceste faciliti sunt cunoscute sub numele de instrumente de sintetizare a

informaiei i de asistare a deciziei i regrupeaz: instrumente de sintetizare (tabelele de

ipoteze, tabele pivot, subtotalizri, consolidri), instrumente de simulare i optimizare

(scenariile, tehnica valorii scop i solver-ul) i instrumente de previziune (funciile Trend

i Forecast, previziuni prin grafice).

1.6.1 Instrumente de sintetizare a informaiei

Procesorul de tabele Excel ofer tehnici i faciliti avansate de regrupare i

centralizare a datelor coninute n tabele, obinndu-se n urma prelucrrii, noi informaii

agregate i sintetizate.

1.6.1.1 Tabelele de ipoteze permit obinerea 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 coloan se poate obine un numr mare de rezultate care sintetizeaz prin regrupare

informaiile dintr-un tablou sau o baz de date. De fapt, tabelele de ipoteze permit crearea

de rapoarte de sintez pentru bazele de date.
http://www.ase.ro/ciedd/birotica/CAP16B.htmhttp://www.ase.ro/ciedd/birotica/excel-cuprins.htmhttp://www.ase.ro/ciedd/birotica/CAP15.htm


2/33

O tabel de ipoteze se compune din urmtoarele elemente: una sau 2 celule de

intrare; una sau mai multe formule care vizeaz celulele de intrare; un cmp special numit

cmp de ipoteze cu una sau 2 dimensiuni n care prima coloan sau linie conine valori

asupra crora se aplic efectiv formulele.

Atunci c

nd mai muli parametrii particip

la elaborarea unui rezultat, c

mpuriletabelelor de ipoteze permit evaluarea importanei relative a fiecrui parametru (doi sau

mai muli parametrii concur la rezultatul sintetizrii).

Procedura de sintetizare a informaiei prin tabelele de ipoteze se declaneaz prin

execuia comenzii Data Table.

Fig. 1.122 Tabel de ipoteze cu osingura variabil

Tabela de ipoteze cu o singur variabil sau studiul variaiei unui singur

parametru.

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

sau a mai multor formule, urmrind diferite valori luate de un parametru. Tabela de

ipoteze este compus din:

- - o celul de intrare, situat oriunde n foaia de calcul (dar mai puin n

cmpul de ipoteze), definit i referit de utilizator n caseta de dialog

Table, corespunztor rubricilor Row Input Cell sau Column Input Cell

dup cum se dorete a se organiza rezultatul pe linie sau pe coloan.

Celula de intrare poate fi vid, ea fiind situat ntr-un cmp de criterii

Cmp de criterii B17:B18

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

Zon unde se afieazrezultatele n urmacomenzii Data Table

Tabela de i oteze B20:C25


3/33

atunci cnd formula generatoare de rezultate este tip baz de date (=D). n

cazul utilizarii altor funcii, altele dect cele baze de date, celula de intrare

poate conine unul din argumentele funciei respective.

- - cmpul de ipoteze conine rezultatele calculate prin aplicarea unei

formule pentru o serie de date. Prima coloan

a tabelei sau prima linie aacesteia poate conine valorile testate prin formul(e). Respectivul cmp

va fi selectat de ctre utilizator nainte de activarea comenzii Data Table

- - formula(formulele) este plasat pe prima linie a cmpului de ipoteze;

acestea pot fi de tip baze de date sau formule bazate pe un interval de

variaie (de tip PMT, SYD, SLN, DB, DDB etc).

Celula din colul superior stng al cmpului de ipoteze nu intervine n calcule,

coninutul su poate fi lsat la libera iniiativ a utilizatorului.

Comanda Data Table permite completarea automat a tabloului ca i cum

formula sau formulele situate pe prima linie ar fi aplicate succesiv pentru fiecare valoaregsitn prima coloan sau n prima linie, aceste valori reprezentnd parametrii

sintetizrii informaiei.

Pentru nelegerea mecanismului de sintetizare a informaiei se furnizeaz

urmtorul exemplu:

Plecnd de la o baz de date pentru evidena personalului unei societi

comerciale, se calculeaz suma salariilor pe compartimente funcionale. Tabelul ce

conine baza de date va fi definit pe coordonatele A1:F14, figura 1.122.

Plecnd de la aceeai baz de date, ne propunem s calculm suma salariilor pe

compartimente funcionale numai pentru personalul angajat n cursul ultimului an i care

are salariile peste media salariilor, figura 1.123.


4/33

Fig. 1.123 Baza de date pentru tabela de ipoteze

Pentru a rspunde acestor restricii este necesar construirea unei interogri a

bazei de date care s coninn cmpul de criterii condiiile enunate (cele referitoare la

data angajrii i la salariu);

nregistrrile care vor ndeplini cumulativ cele 2 restricii ale cmpului de criterii

vor fi extrase ca valori unice ntr-un cmp de rezultate care va fi situat ntr-o alt locaie a

foii de calcul i care va conine acele rubrici necesare pentru definirea tabelei de ipoteze

(n cazul de fa rubrica Compartiment;

Extragerea din baza de date se va face prin intermediul funciei DSUM care are ca

argumente: baza de date, rubrica pe care se opereaz calculul i cmpul de criterii (n

care sunt precizate cele 2 restricii;

Acest exemplu mbin facilitile de interogare a bazelor de date prin criterii

calculate cu faciliti de sintetizare a informaiei prin regrupare potrivit criteriilor

enunate.

Pentru rezolvarea aplicaiei se parcurg urmtoarele etape:

1. 1. se construiete cmpul de criterii pentru interogare care poate conine

una (compartimentul) sau toate rubricile bazei de date + criteriul calculat

(=Data angajarii >TODAY() 360 i =Salariul >AVERAGE(F2:F14) );

ntr-un criteriu calculat, rubrica de comparat este reprezentat de prima celulplasat sub rubrica respectiv. Astfel, n loc de Data angajrii se va scrie prima adres a

domeniului, adic C2, iar pentru Salariul se va introduce adresa F2.

2. 2. se vor extrage din baza de date potrivit criteriului specificat acele

nregistrri unice care ndeplinesc cumulativ restriciile enunate; cmpul de

rezultate va fi definit ntr-o alt locaie a foii de calcul si va deveni ulterior

parametrul cmpului de ipoteze pentru care o formula va calcula serii diferite de

Tabela bazei de date A1:F14


5/33

rezultate (extragerea se face prin Data- Filter- Advanced Filter, marcnd

caseta se selectare Unique Record Only);

3. 3. se scrie formula de calcul (=DSUM()) n colul superior drept al

cmpului de ipoteze- formula se va aplica pentru fiecare compartiment n parte

nsumnd salariile pe respectivul criteriu de regrupare;4. 4. se selecteaz cmpul de ipoteze

5. 5. se activeaz comanda Data Table i n caseta Table se completeaz

adresa celulei care conine criteriul n rubrica Column Input Cell- tabela de

ipoteze fiind orientatn acest caz pe coloan ( n cazul organizrii tabelei pe

linie)

6. 6. validnd 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

variabil

Un alt exemplu derivat din primul, vizeaz calculul mediei salariilor pe

compartimente. De aceast dat, datele vor fi organizate orizontal- pe linie- i n

consecin se va completa adresa celulei care conine criteriul n rubrica Row Input Cell

aferent casetei de dialog Table. Figura 1.125 ilustreaz acest exemplu.

Fig. 1.125 Tabel de ipoteze cu osingura

variabil (II)

=DAVERAGE A1:F14 Salariu B23:B2

Tabela deipoteze dispusorizontal

Tabel de ipotezedispus vertical


6/33

Rezultatele calculului mediei salariilor se gsesc reflectate n figura 1.126.

Fig. 1.126 Rezultatul final (I)

Tabela de ipoteze cu o singur variabil poate permite calcularea mai multor serii

de rezultate, utiliznd mai multe formule.

Astfel, dac s-ar fi dorit calcularea sumei i mediei salariilor economitilor, tabela

de ipoteze ar conine trei coloane, una pentru marcarea valorilor studiate, adic

parametrii, a doua pentru rezultatele calculului sumei salariilor i a treia pentru calculul

mediei salariilor. Cmpul de criterii (B17:C18) a fost completat cu rubrica Funcia

pentru a sintetiza informaia referitoare la economiti. Tabelul ce conine sursa de date

pentru sintetizare a fost declarat la adresa A3:E15. Schema de organizare a informaiilor

n tabela de ipoteze este prezentatn figura 1.127.

Tabela de ipoteze declarat la adresa B20:D25 - va calcula suma i media (prin

formulele DSUM i DAVERAGE) salariilor corespunztoare funciei de economist. n

urma comenzii Data Table i precizarea adresei celulei vide (B18) n rubrica Column

input cell, se vor genera automat rezultatele (figura 1.128).

Fig. 1.127 Tabel de ipoteze (III)

Un alt gen de aplicaii pentru sintetizarea informaiei prin tabela de ipoteze cu o

variabil, vizeaz funciile financiare, de exemplu:

s se calculeze sumele rambursabile lunar (funcia PMT) aferente unui

Cmpde

criterii


7/33

Fig. 1.128 Rezultatele finale (III)

credit de 80 de milioane lei, contractat pe 3 ani, pentru mai multe variante de rat

a dobnzii (aplicaie exemplificatn figura urmtoare), sau s se calculeze aceleai sume

rambursabile lunar, aferente aceluiai credit, cu o dobnd fix de 60%, pentru mai multe

variante de perioade de rambursare:

Alte aplicaii economice de sintetizarea informaiei, ce pot fi rezolvate cu tabela

de ipoteze cu o variabil, ar putea fi:

- - se calculeaz prin sintetizarea informaiei maximul, minimul i media

salariilor angajailor cu funcia economist din compartimentele

financiar i contabilitate (ncepnd din anul 1990) i se reprezint

grafic printr-o diagram de amplitudine variaia salariilor pe cele dou

compartimente;

- - se reprezint grafic numrul total de angajai pe fiecare compartiment

funcional i suma salariilor acestora;

Fig. 1.129 Tabel de ipoteze (IV)

Tabela de ipoteze cu dou variabile


8/33

Tabela de ipoteze cu dou variabile funcioneaz dupa aceleai principii ca i

tabela cu o variabil, dar permite variaia simultan a doi parametrii, necesitnd 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 afln coloana stng

a cmpului de ipoteze, iar valorile celui de-al doilea parametru (a doua celul de

intrare) se afl pe prima linie;

- - formula pe care este construit sintetizarea se introduce n celula colului

superior stng al cmpului de ipoteze ca i cnd s-ar aplica celor dou celule de

intrare.

Atunci cnd comanda Data Table este executat, cmpul de ipoteze este

completat n mod automat cu rezultatele calculelor efectuate pentru fiecare linie i

coloan a tabloului. Fiecare intersecie a unei linii cu o coloan furnizeaz rezultatul

formulei aplicate valorilor liniei i coloanei.

Pentru exemplificare, vom lua urmtoarea aplicaie:

Se va calcula prin sintetizarea informaiei suma salariilor pe funcii i pe

compartimente funcionale.

Pentru rezolvarea aplicaiei se parcurg urmtoarele etape:

- - se construiete tabela de ipoteze, completnd pe prima linie funciile

salariailor pentru care se face sintetizarea prin nsumare i pe prima

coloan compartimentele funcionale pentru care s se fac respectiva

regrupare;

- - se editeaz cmpul de criterii pe coordonatele B30:C31, cmp ce va

conine rubricile: Compartiment i Funcia;

- - se introduce formula de nsumare =SUM(A3:E15;5;B30:C31) n colul

superior stng al tabelei de ipoteze (la adresa B35). Formula va genera

rezultate centralizate pentru fiecare funcie i compartiment n parte;

- - se selecteaz tabela de ipoteze (B35:F40);

- - se activeaz comanda Data Table i n caseta Table (figura 1.130) se

completeaz adresele celulelor de intrare astfel:

- - n rubrica Column input cell se marcheaz adresa celulei vide

corespunztoare compartimentului, adic B31;

- - n rubrica Row input cell se marcheaz adresa celulei

corespunztoare funciei, adic C31;


9/33

Fig. 1.130 Tabel de ipoteze cu dou variabile

Prin validarea aciunii cu butonul OK, sintetizarea informaiei prin nsumare se va

efectua automat pentru fiecare compartiment i funcie n parte. Figura 1.131 prezint

rezultatele gruprii datelor.

O a doua aplicaie a tabelei de ipoteze cu dou variabile vizeaz calculul

amortizrii accelerate: Se consider o investiie (un mijloc fix) n valoare de 12mil de lei

(un calculator); valoarea rezidual estimat a imobilizrii este de 3 mil lei dup expirarea

duratei normate de funcionare care este de 6 ani; nr de perioade pentru care se calculeaz

amortizarea este de 6 ani.

Fig. 1.131 Rezultatul final (I)


10/33

Fig. 1.132 Tabel de ipoteze cu dou variabile

Fig. 1.133 Rezultatul final (II)

Se va genera un tablou de amortizare accelerat pe ani de funcionare i pe durata

pentru care se calculeaz amortizarea.

Modul de construire al tabelei cu dou variabile este prezentat n figura 1.132.Diferena fa de exemplul anterior constn faptul c, n cazul de fa celulele de

intrare nu sunt vide i neprotejate, ci sunt chiar argumentele funciei financiare DB.

Rezultatul sintetizrii informaiei prezentat n figura 1.133- reprezint chiar

tabloul de amortizare al respectivei imobilizri.

1.6.1.2 Tabele pivot

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

datele dintr-o foaie de calcul pot fi permutate pentru a se pune n eviden noi informaii.Tabela pivot permite crearea unui tablou de sintezn care rubricile unui tabel sau

unei baze de date pot fi permutate pe linie sau pe coloan, asupra datelor operndu-se

agregri i calcule sub form de totaluri, medii, min, max. etc. Aceste operaii sunt alese

dintr-o list de funcii predefinite i se pot opera calcule diferite asupra acelorai

elemente supuse sintetizrii.


11/33

Fig. 1.134 Baza de date pentru tabela pivot

Deci tabela pivot este un instrument care permite o foarte elastic asociere a unor

cmpuri ntr-o manier interactiv, fapt ce duce la regruparea datelor i prezentarea

acestora ntr-un mod sintetic.

Tabela pivot se creeaz selectnd sursa datelor de sintetizat i opernd comanda

Data - Pivot Table Report, dup care un asistent Pivot Table Wizard ndrum

utilizatorul n 4 pai.

Un exemplu edificator de construire i utilizare a tabelei pivot ar consta n

sintetizarea informaiei pentru o editur, referitoare la vnzrile de carte;Fie o tabel

(figura 1.134) aferent unei baze de date care regrupeaz informaii referitoare la

vnzrile de carte de informatic pe orae (Bucureti, Ploieti, Bacu, Iai), pe doi ani

(1997/1998), pe ageni de vnzare (Rdulescu S, Ionescu L, Popescu M, Vasilescu D,

Moisescu A.), pe cantiti vndute (fiecare carte poate fi vndutn mai multe orae i

acelai agent de vnzare poate opera vnzri pentru un titlu de carte sau mai multe, n

acelai ora sau n mai multe) i pe valori reieite din vnzri. Preul crii se poate

extrage (cu funcia VLOOKUP) dintr-un tablou de consultare vertical. Volumul

vnzrilor realizate de ctre fiecare agent de vnzare este rezultatul nmulirii cantitii

vndute cu preul crilor. n rezolvarea aplicaiei, asistentul tabelei pivot (prin comanda

Data - Pivot Table Report) parcurge urmtoarele patru etape (prin apsarea butonului

Next> pentru fiecare etapn parte):

1. n prima etap Pivot Table Wizard -Step 1 of 4 (figura 1.135) se alege

sursa de date pentru sintetizarea informaiei:

- dintr-un tabel Excel sau o baz de date (Microsoft Excel List or Database);


12/33

- dintr-o baz de date extern (External Data Source);

- din cmpuri multiple consolidate (Multiple Consolidation Ranges);

- din alt tabel pivot (Another Pivot Table).

Fig. 1.135 Tabela pivot, pasul 1

n prealabil sursa de date poate fi selectat sau se poate poziiona cursorul pe

prima celul a sa (de exemplu A3).

2. ntr-o a doua etap PivotTable Wizard - Step 2 of 4, fig.1.136, se

valideaz tabela surs pe baza creia se va construi tabela pivot sau dac sursa de date nu

a fost selectat anterior, aceasta se poate selecta n aceast etap prin completarea n

rubrica Range.


3. ntr-o a treia etap Pivot Table Wizard- Step 3 of 4 (fig. 1.137) are loc

selectarea datelor, prin care se precizeaz care rubric(rubricii) va(vor) fi plasat(e) pe

linie, i care pe coloan. Plasarea rubricilor pe linie sau pe coloan se face prin glisarea

rubricii respective ntr-o caset de sintetizare, existnd posibilitatea agregarii datelor prin

plasarea mai multor rubrici (nivel descresctor de centralizare) pe linie sau pe coloan. n

plus exist i posibilitatea declarrii mai multor restricii prin glisarea unuia sau mai

multor elemente centralizatoare pe pagin (Page).


13/33

Caseta de dialog aferent celei de a treia etap, conine o machet-ablon unde vor

fi glisate rubricile datelor de sintetizat astfel:

ROW : poziioneaz elementele cmpului (rubricii) pe linie;

COLUMN: poziioneaz elementele cmpului (rubricii) pe coloan;

DATA : sintetizeaz elementele rubricii prin calcul, la nivelul ntregii baze dedate, la intersecia valorilor rubricilor dispuse pe linie sau pe coloan;

PAGE: regrupeaz elemente de centralizare ale bazei sau sursei de date pentru

care informaia este sintetizat pe linie, pe coloan, pe linie i pe coloan.

n cazul de fa, pentru rezolvarea sintetizrii "Volumul vnzrilor de carte de

informatic pe titluri i pe ani", se va glisa rubrica "Denumire carte" pe linie (ROW) i

rubrica "Anul" pe coloan (COLUMN), iar rubrica "Valoarea vnzrilor" va fi glisatn

centrul machetei (DATA) pentru totalizare.


4. n a patra etapPivotTable Wizard - Step 4 of 4, (fig. 1.138) se stabilete

adresa tabelei pivot, i anume dac aceasta se va plasa ntr-o nou foaie de calcul (New

worksheet), sau n foaia de calcul existent, la o anumit adres (Existing worksheet).

Rubric

plasat pelinie

RubricPlasat pe

coloan

Rubric desintetizareprin calcul

Centralizare


14/33


Tot n aceast etap se pot stabili i anumite opiuni ale tabelei pivot, prin

apsarea 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);


15/33

Fig. 1.140 Tabela pivot, rezultatul

final (I)

- salvarea datelor mpreun 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 apsarea butonului Finish.

Tabela pivot astfel construit este prezentatn figura 1.140.

Tabela pivot, ca instrument de asistare a deciziei permite o sintetizare n trepte a

informaiei, grupnd-o pe niveluri descresctoare de centralizare. Din punct de vedere

tehnic, acest lucru se poate realiza grupnd mai multe rubrici pe linie sau pe coloan i

efectund mai multe tipuri de calcule pentru aceeai rubric sau pentru rubrici diferite.

Condiia pentru sintetizarea n trepte pe linie sau pe coloan este aceea ca prima

rubric plasat pe linie sau pe coloan s conin mai multe elemente (ce vor fi regrupate)

aferente celei de a doua rubrici plasate pe linie sau pe coloan

, iar a doua rubric

plasat

pe linie sau pe coloan s conin elemente aferente celei de a treia rubrici, .a.md. Altfel

spus, elementele sunt grupate pe linie sau pe coloan dup gradul lor de sintetizare, de la

cel mai cuprinztoe element la cel mai puin cuprinztor.

Agregarea n trepte a informaiilor permite deci obinerea unei viziuni mai

sintetice n ceea ce privete analiza i reprezentarea datelor.

Un exemplu de agregare n trepte este generarea unui raport, privind cantitatea de

carte vndut de ctre agenii comerciali pe titluri de carte, pe ani i pe orae de

distribuie.


16/33

Fig. 1.141 Tabela pivot, pasul 3 (II)

Modificarea dispunerii rubricilor n macheta-ablon se face plecnd de la tabela

pivot generatn cele patru etape, astfel:

- - se poziioneaz cursorul oriunde n interiorul tabelei pivot;

- - se activeaz comanda Data PivotTable Report, ajungndu-se n

etapa a 3-a de construire a tabelei pivot (PivotTable Wizard 3 of 4),figura 1.141;

- - se reconfigureaz rubricile prin glisare n macheta-ablon 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 i

anumite opiuni aferente acesteia (figura 1.142).

Anularea unei rubrici se poate face urmnd primii doi pai de la procedura de

modificare, dup care (n pasul PivotTable Wizard 3 of 4) rubrica de anulat va fi glisat

din macheta-ablon n afara acesteia (n partea dreapt), alturi de celelalte rubrici carenu particip la sintetizarea informaiei.


17/33

Fig. 1.142 Tabela pivot, rezultatul final

(II)

Plecnd de la tabela pivot definit anterior, s-a dorit reconfigurarea acesteia prin

modificare pentru a obine, n mod dinamic pe ani i pe orae de distribuie, suma

cantitilor de carte vndut, suma valoric a crilor vndute pe titluri de carte i pe

ageni de distribuie.

Sintetizarea dinamic presupune precizarea de restricii la nivel de pagin, prin

alegerea unui element din lista de valori aferente rubricii, de exemplu Ora distribuie =

Bucureti i Anul = 1997.

Reconfigurarea rubricilor este prezentatn figura 1.143.

Fig. 1.143 Tabela pivot, reconfigurarea rubricilor

Tabela pivot modificat i reconfigurat dinamic este prezentatn figura 1.144


18/33

Fig. 1.144 Tabela pivot, reconfigurat

dinamic

Un alt procedeu de modificare-anulare a rubricilor ce particip la sintetizareainformaiei este urmtorul:

- - se selecteaz din tabela pivot rubrica de modificat sau anulat (nu

elementele acesteia);

- - se execut un dublu-click pe rubrica respectiv;

- - n caseta de dialog PivotTable Field (figura 1.145);

- - se poate anula rubrica prin apsarea butonului Delete;

- - se poate schimba numele rubricii, n caseta Name;

- - se poate schimba orientarea (Orientation) rubricii Agent

vnzare de exemplu- pe linie (Row), pe coloan (Column), pe

pagin (Page);

- - se pot redefini sau anula elemente de calcul n rubrica

Subtotals. n mod implicitAutomatic- se calculeaz numai suma,

dar se pot realiza i alte calcule Custom- cum ar fi medie, produs,

maxim, minim, etc, sau prin opiunea None calculele vor fi inhibate;

- - se pot ascunde anumite elemente ale rubricii respective

(Agent vnzare), 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 afiarea elementelor sintetizabile ce au valori

nule, prin marcarea casetei de selectare Show items with no data.


19/33

Fig. 1.145 Tabela pivot, modificarea

rubricilor

Pentru a schimba operaiile de calcul fcute asupra rubricilor, de exemplu

modificarea valorii vnzrilor din sumn medie (sau n produs, maxim, minim, etc.), se

activeaz prin dublu-clikn procedura de modificare (PivotTable Wizard 3 of 4) cmpul

de calcul (n caseta DATA Sum of Valoare vnzri) asupra cruia opereaz respectiva

modificare a operaiei de calcul i n caseta de dialog PivotTable Field, la rubrica

Summarize by se schimb din operaia din Sumn Average, validndu-se operaia cu

OK, fig. 1.146 i 1.14.


20/33

Fig. 1.146-1.147 Tabela pivot, modificare rubriciCaseta de dialog PivotTable Field, prin butonul Options>> permite efectuarea i

altor operaii ce permit modaliti diferite de prezentare a datelor sintetizate. Astfel, din

lista derulantShow data as se pot alege operaii ca: diferen fa de , % fa de"

etc. Aceste operaii se refer la rubricile tabelei pivot (Base field) i se aplic elementelor

acestor rubrici (Base item). De exemplu se poate construi urmtoarea sintetizare: suma

vnzrilor pe ani, ca diferen fa de anul 1998.

Tabela pivot permite ascunderea sau afiarea unor nivele de sintetizare. De regul

nivelul de sintetizare ce urmeaz a fi inhibat (sau ascuns) trebuie s fie n mod obligatoriu

ierarhic inferior ca agregare, fa de primul nivel care este mai cuprinztor. n exemplulluat (fig. 1.148), rubrica Agent vnzare este pe un plan ierarhic secundar fa de rubrica

Titlu carte (un titlu de carte este vndut de mai muli ageni de vnzare). Pentru a

ascunde un nivel de sintetizare, se selecteaz din tabela pivot, rubrica superioar n

ierarhie fa de rubrica de inhibat (de exemplu rubricile Titlu carte i Anul) i se

activeaz meniul Data, opiunea Grup and Outline, subopiunea Hide detail. Pentru a

reafia respectivul nivel ierarhic inhibat, se procedeaz asemntor, cu deosebirea c se

activeaz subopiunea Show Detail.

Tabela pivot permite sintetizarea unor elemente disparate, prin gruparea acestora.

Astfel dac

se selecteaz

din tabela pivot dou

orae (Bucureti, Ploieti) i se activeaz

comanda Data Group and Outline Group, cele dou elemente vor conta n

sintetizarea informaiei ca fiind grupate (Group1), fig.1.149.

Disocierea elementelor grupate se face selectnd grupul i acionnd comanda

invers: Data Group and Outline Ungroup.


21/33

Fig. 1.148 Tabela pivot, rezultatul ascunderii

datelor (I)

Modificarea tabelei pivot, afiarea sau ascunderea unor detalii, gruparea

Fig. 1.149 Tabela pivot, rezultatul ascunderii

datelor (II)

sau disocierea unor elemente disparate sunt operaii ce se pot realiza i cu ajutorul unei

bare de butoane aferente tabelei pivot. Bara de butoane se activeaz prin comanda View

Toolbars Pivot Table. Semnificaia butoanelor este prezentatn figur 1.150.

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

Fig. 1.150 Bara de butoane pentrutabela pivot

a) a) PivotTable Wizard permite modificarea tabelei pivot prin

intermediul asistentului acesteia;


22/33

b) b) PivotTable Field permite modificarea unei rubrici prin permutarea

acesteia pe linie, coloan, pagin sau dac este vorba de o operaie de

calcul, aceasta se poate modifica;

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) f) Hide Detail inhib afiarea unui nivel agregat;

g) g) Show Detail afieaz un nivel agregat care a fost anterior inhibat;

h) h) Refresh Data actualizeaz datele din tabela pivot, dac sursa de

date pe care aceasta a fost construit s-a modificat;

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 aferente.

O ultim facilitate a tabelei pivot, ar fi aceea de extragere n alt foaie de calcul a

unor informaii, potrivit unui criteriu. Acest lucru este posibil printr-un dublu-clik

executat pe un element aferent unei rubrici sau aflat la intersecia a dou rubrici.

Fig. 1.151 Rezultatul extragerii

De exemplu plasnd cursorul la intersecia a dou rubrici, anume Ora distribuie

= Bucureti i Birotica Total, rezultn urma unui dublu-click o extragere tip baz de

date, pentru vnzrile totale de carte cu titlul Birotic n Bucureti (fig. 1.151).

1.6.1.3 Gruparea informaiei prin generarea de subtotaluri

Sub Excel exist posibilitatea sintetizrii informaiei prin organizarea ei pe

niveluri de grupare, iar apoi pot opera diferite calcule pe aceste grupuri sau informaii

centralizate. Altfel spus Excel poate organiza datele prin grupare pentru generarea de

totaluri i subtotaluri.


23/33

Fig. 1.152 Rezultatul filtrrii datelor

Pentru gruparea datelor care se doresc a fi totalizate este necesar sortarea

acestora pe rubricile de grupare. De exemplu, baza de date definit anterior (la tabela de

ipoteze cu o variabil) pe coordonatele A3:E17 va fi sortat ascendent pe rubricile

"Compartiment", "Funcie" (un compartiment conine salariai cu funcii diferite) i

descendent pe rubrica "Marca".

Sortarea se face cu ajutorul comenzii Data Sort (baza de date nu trebuie neaprat

selectat, fiind suficient poziionarea cursorului pe una din rubrici). n caseta de dialog

Sort se precizeaz cheia (cheile) de sortare Sort By (Then By) i ordinea sortrii:

cresctoare (Ascending) sau descresctoare (Descending).

Figura 1.152 ilustraz rezultatul sortrii datelor dup rubricile "Compartiment"

(Sort By); "Funcia" (Then By); "Marca" (Then By).

Dup ce datele au fost sortate dup cele trei chei de sortare, acestea pot fi

totalizate pe cmpurile de grupare. Subtotalizarea se face prin poziionarea pe prima

celul a bazei de date (sau prin selectarea acesteia) i activarea comenzii Data

Subtotals. Prin caseta de dialog Subtotal se desfoar tehnica de regrupare a

informaiei, astfel:

- - se precizeaz rubrica pentru care se face gruparea. Astfel, din lista derulant

At Each Change in (n cazul de fa) se alege rubrica pe care se face gruparea

sau unde are loc ruptura de secven

"Compartiment";- - se alege tipul de operaie care va aplica datelor regrupate (Sum; Max; Min;

Avarage; Count; Product) din caseta Use Function;


24/33

Fig. 1.153 Caseta de dialog SUBTOTAL

- - n final se alege rubrica de calculat prin selectarea acesteia - n cazul de fa

"Salariu";

- - dup precizarea acestor elemente se valideaz cu butonul OK.

Caseta de dialog Subtotal este prezentatn figura 1.153.

Fig. 1.154 Rezultatul gruprii datelor

Rezultatul gruprii i totalizrii datelor este prezentat n figura 1.153.


25/33

n urma totalizrii, foaia de calul i-a schimbat aspectul n sensul apariiei unor

elemente (+ i -) care semnific gradul de grupare a datelor. Altfel spus, tabelul pentru

care s-a fcut totalizarea a fost ierarhizat.

Ierarhizarea elementelor regrupate permite alegerea pentru vizualizare sau pentru

prelucr

ri ulterioare (de exemplu reprezent

ri grafice de structur

) a nivelului dorit astfelnct s fie relevat informaia de care utilizatorul are nevoie.

Fig. 1.155 Prezentarea ierahizat a datelor

Revenirea afiarii din forma ierarhizatn forma normal se face prin selectarea

tabelului sau poziionarea cursorului pe prima celul a acestuia i activarea comenzii

Data Subtotals, iar din caseta Subtotal va fi apsat butonul Remove All (fig. 1.155).

1.6.1.4 Sintetizarea datelor prin consolidare

Consolidarea datelor din foile de calcul semnific regruparea acestora prin

utilizarea acelorai coordonate ntr-o foaie de calcul centralizatoare.

Consolidarea datelor implic faciliti de exploatare tridimensional pentru foile

de calcul. Datele ce urmeaz a fi consolidate se pot gsi n foi de calcul separate sau n

fiiere (documente de calcul tabelar) distincte.

Procedura de consolidare implic participarea fiecrei foi de calcul sau fiier la o

operaiune de centralizare. Rezultatul acestei operaii se poate gsi fie n acelai fiier,dar ntr-o foaie de calcul distinct, fie n alt fiier. Este foarte important ca datele ce

urmeaz a fi centralizate s aib o schem unic de organizare n foile de calul ce

constituie surs a consolidrii. Altfel spus, datele de centralizat trebuie s fie editate la

nite adrese fixe, pe baza ablonrii acestora, astfel nct coordonatele celulare ale

informaiilor de consolidat s fie aceleai din punct de vedere al exploatrii

tridimensionale a foilor de calcul.

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

- - restrnge ierarhia- + expandeaz ierarhia


26/33

Din punctul de vedere al surselor de date ce particip la gruparea informaiei,

consolidarea poate fi intern sau extern.

Consolidarea intern a datelor presupune participarea mai multor foi de calcul ale

aceluiai fiier, organizate identic din punct de vedere al referinelor celulare, la

centralizarea datelor

ntr-o foaie de calcul distinct

,

n cadrul aceluiai document decalcul tabelar.

Consolidarea intern a datelor poate fi fcutn dou moduri:

- - printr-o procedur special, generat prin comanda Data Consolidate;

- - prin utlilizarea formulelor de calcul cu referine 3-D.

Fig. 1.156 Variante de buget

Pentru exemplificarea operaiunii de consolidare, propunem urmtoarea aplicaie:

Un institut de formare dorete s-i centralizeze informaiile legate de bugetele de

cheltuieli aferente cursurilor de instruire pe care le face. Fiecare buget de curs se

gestioneaz individual de ctre un responsabil pedagogic. Presupunem c respectivul

institut gestioneaz trei cursuri, elabornd cte un buget pentru fiecare : Contabilitate,

Informatic i Management. Fiecare buget de curs are aceleai capitole: Cheltuielisalariale, Cheltuieli publicitare, Cheltuieli cu logistica, Cheltuieli administrative. Cele trei

bugete au fost construite pe trei foi de calcul ce poart numele fiecrui buget. Bugetele au

fost ablonate, n sensul marcrii elementelor de centralizat n aceeai ordine i pe

aceleai coordonate.

n figura 1.156 sunt prezentate cele trei variante de buget pentru cursurile de

contabilitate, informatic i management:


27/33

Pentru centralizarea datelor din cele trei foi de calcul, se pot urmri dou procedee

de consolidare.

Primul procedeu vizeaz consolidarea automat prin intermediul comenzii Data

Consolidate, astfel:

1. 1. se deschide noua foaie de calcul care va conine rezultateleconsolidrii;

2. 2. se poziioneaz cursorul n prima celul din stnga-sus (de regul) sau

ntr-o celul ce desemneaz destinaia consolidrii datelor;

3. 3. se activeaz comanda Data Consolidate, figura 1.157;

4. 4. n caseta de dialog Consolidate se execut urmtoarele operaii:

Fig. 1.157 Caseta de dialog pentru

consolidare

a) a) se alege tipul de operaie (Sum, Max, Min, Product, Average, etc.)

care se va aplica datelor de consolidat, din lista derulantFunction (de regul

se alege suma);

b) b) n caseta Reference se introduce sursa de date ce va participa la

consolidare. La precizarea sursei de date se va avea n vedere att selectarea

datelor de consolidat propriu-zise, ct i selectarea etichetelor sau explicaiilor

aferente acestora;

c) c) se apas butonul Add, zona selectat anterior fiind nregistrat

automat n caseta All References;

d) d) se repet succesiv ultimele dou proceduri pn la epuizarea zonelor

surs ce particip la consolidare;

e) e) se precizeaz modelul de organizare a datelor urmrit prin

consolidare, prin caseta Use labels in. Consolidarea datelor pe categorii

(niveluri ierarhizate de organizare a datelor) presupune i precizarea

etichetelor i explicaiilor aferente datelor. Astfel, pot exista trei modele de

consolidare : pe linie (caseta de selectare Top row), pe coloan (Left column)

sau amndou (cele dou casete de selectare sunt activate), dup cum datele


28/33

de consolidat sunt dispuse. Dac niciuna din cele dou casete de selectare nu

este activat, consolidarea este considerat a fi fcut dup poziia datelor de

consolidat, fr a urmri o eventual regrupare a acestora.

f) f) se poate actualiza rezultatul consolidrii la modificarea valorilor unor

celule ce aparin de sursa consolid

rii, prin activarea casetei de selectareCreate links to source data;

g) g) n final procedura de consolidare se valideaz cu OK.

Adugarea de noi foi de calcul de sintetizat care s participe la elaborarea unui

rezultat consolidat se face prin includerea acestora n caseta Reference i apsarea

butonului Add. tergerea unui element care particip la consolidare se face prin selectarea

acestuia din lista All references i apsarea butonului Delete.

n momentul crerii de legturi (Create links to source data) ntre surs i

destinaie, nu se mai pot aduga noi surse de date ce particip la consolidare, nu se pot

modifica sau terge surse existente.

Fig. 1.158 Rezultatul consolidrii (I)

Rezultatele consolidrii celor trei foi de calcul sunt prezentate n figura 1.158.

Dac datele de consolidat sunt externe documentului n care se face consolidarea,

operaiunea poate fi fcut preciznd adresa fizic de pe disc a surselor de date. Acestlucru este posibil prin marcarea completn rubrica References a specificatorului de

fiier i a foii de calcul din care provin sursele de date sau prin apsarea butonului

Browse i alegerea interactiv a coordonatelor datelor de consolidat.

Al doilea procedeu de consolidare vizeaz utilizarea formulelor cu inciden

tridimensional. Astfel, urmrind exemplul precedent se pot sintetiza urmtoarele etape

de urmat:


29/33

- - se deschide noua foaie de calcul care va conine rezultatele consolidrii;

- - se poziioneaz cursorul n prima celul din stnga-sus (de regul) sau ntr-o

celul ce desemneaz destinaia consolidrii datelor;

- - se copiaz (Copy/Paste) etichetele datelor ce particip la consolidare

- - se genereaz o formul de calcul de regrupare utiliznd referinetridimensionale. Referinele 3-D includ pe lng coordonatele coloanelor i

liniilor, i 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 cosolidrii (II)

Formula semnific efectuarea unei sume pe coordonata celulei B5 aferent foilor

de calcul cuprinse ntre foaia Contabilitate i Management.

O alt formul 3-D ar putea viza referinele individuale ale celulelor care particip

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 dup aceast procedur este prezentatn figura 1.159.

n utilizarea adreselor tridimensionale n procesul de consolidare a datelor, este

foarte important ca datele de centralizat s aib acelai model de organizare pe

coordonate celulare.

- - formula 3-D poate fi copiat la nivelul celorlalte celule, iar dac

cerina anterioar de pstrare a acelorai coordonate tridimensionale este

ndeplinit, tabloul de consolidare va fi generat corect.


30/33

Consolidarea datelor ce provin din fiiere distincte prin acest procedeu presupune

ca n formula de consolidare s intervin i specificatorul de fiier de unde provin datele

de consolidat.

Astfel de exemplu dac cele trei bugete ar fi fost construite pe fiiere separate:

Contabilitate.XLS, Informatica.XLS, Management. XLS, formula de consolidare adatelor pentru Cheltulielile publicitare (celula B7) ar fi :

=[Contabilitate.xls]Sheet1!B7+[Informatica.xls]Sheet1!B7+[Management.xls]She

et1!B7 (fig. 1.160)

Fig. 1.160 Buget consolidat

1.6.2 Instrumentele de simulare i optimizare

Instrumentele de simulare i optimizare cuprind faciliti puternice de modelare a

unor probleme formulate n sensul gsirii unor soluii care s rspund la un ansamblu de

restricii, n sensul simulrii i optimizrii acestora.

1.6.2.1 Tehnica valorii scop sau de cutare a rezultatului

Tehnica cutrii rezultatului permite stabilirea unei valori finale (scop sau

obiectiv) pentru o formul pentru ca apoi s modifice valoarea uneia din celulele utilizate

n formul pentru a calcula valoarea final.

Utiliznd cutarea tip rezultat se poate ajusta o estimare pentru a se ajunge la o

concluzie referitoare la o expresie relativ sau absolut (procentaj sau valoare) pentru un

buget sau o variant de simulat.


31/33

Cutarea 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 ajustat automat la o

valoare corespunztoare;

o valoare care desemneaz rezultatul care se dorete a se obine (rubrica Tovalue:);

celula ce conine valoarea care trebuie modificat (rubrica By changing cell:)

Pe scurt, pentru a gsi o valoare scop sau obiectiv, formularea ar fi urmtoarea :

cu ct (sau la ct) ar trebui modificat un parametru (By changing cell) pentru ca o

valoare scop (Set cell) s ating un prag specificat (To value).

Fig. 1.161 Aplicaie Goal Seek

Celula al crei coninut va fi modificat (By changing cell) trebuie s conin o

valoare (care participn mod nemijlocit la formarea rezultatului) i nu o formul, n timp

ce valoarea scop sau obiectiv (Set cell) trebuie s coninn mod obligatoriu o formul.

Pentru cutarea simultan a rezultatelor dup mai multe valori, nu se folosete

Goal Seek ci se modeleaz o problem de optimizare folosind Solver-ul. Exemplul din

fig.1.161 ilustreaz un buget previzional simplificat al cursurilor postuniversitare.

Bugetul calculeaz veniturile, cheltuileile, precum i marja brut ce se degaj din

aceste cursuri. Marja brut calculat la un numr de 30 de cursani este de 23%.


32/33

Fig. 1.162 Utilizarea tehnicii Goal Seek

Dac s-ar dori calcularea eficienei cursurilor pentru un prag estimat la 30% marj

brut i s-ar pune ntrebarea "de ci cursani ar fi nevoie pentru a atinge o marj de

30%, problema s-ar rezolva prin tehnica valorii scop astfel:

- se poziioneaz (de regul) cursorul pe formula ce conine valoarea scop i seactiveaz comanda Tools Goal Seek;

Fig. 1.163 Utilizarea tehnicii valorii-scop

- se seteazn rubrica Set cell: celula C15 care conine valoarea scop - adic marja

brut (dacn prealabil nu s-a poziionat cursorul pe aceast valoare);

- se stabilete valoarea obiectiv n rubrica To value: la care s trebuie s ajung

valoarea scop - n cazul de fa 30%;

- - se stabilete ce valoare s se schimbe n rubrica By changing cell: (celula C5)

- n cazul prezentat numrul de cursani- pentru a se ajunge la obiectivul

propus;- - - se valideaz cu OK (fig. 1.162). Calculul este fcut automat pentru a se

atinge valoarea scop i n celulele respective vor apare noile valori (34 cursani

pentru o marj brut de 30%). Dac se valideaz cu butonul OK vechile valori

vor fi nlocuite cu noile valori calculate pentru obiectivul fixat, iar dac se

activeazCancel, vechile valori vor fi restaurate (fig. 1.163).


33/33
http://www.ase.ro/ciedd/birotica/CAP16B.htmhttp://www.ase.ro/ciedd/birotica/excel-cuprins.htmhttp://www.ase.ro/ciedd/birotica/CAP15.htm

excel6-pivoti

Documents