INSTRUMENTE INFORMATICE DE SINTETIZARE A

INFORMAŢIILOR ÎN EXCEL 2010

Procesorul de tabele Excel dispune de o gamă de instrumente care oferă tehnici şi

facilităţi avansate de regrupare şi centralizare a datelor, asigurând decidentului o imagine

sintetică pe baza căreia se pot fundamenta ulterior soluţii optime în procesul decizional.

TABELA DE IPOTEZE

Tabela de ipoteze reprezintă un instrument de sintetizare a informaţiilor care oferă

facilităţi suplimentare de generare a unor rezultate diferite prin aplicarea uneia sau a mai

multor funcţii asupra unor serii de date dispuse monodimensional sau bidimensional.

A. Tabela de ipoteze cu o singură variabilă presupune disocierea rezultatului

unor formule în condiţiile în care se urmăreşte variaţia unui singur parametru.

Tabela de ipoteze monodimensională impune existenţa următoarelor elemente distincte:

Câmpul de criterii – se compune, la rândul său din:

o celulă care conţine denumirea parametrului

care variază;

o celulă de intrare care va fi referită ulterior, în

momentul generării rezultatelor finale, în funcţie de

modul în care sunt organizate datele pe linie sau pe

coloană.

Observaţie: în cazul în care funcţiile aplicate pentru

obţinerea rezultatelor finale sunt de tip „bază de

date”, celula de intrare rămâne vidă; dacă se apelează

funcţii din alte categorii, celula de intrare va fi

completată cu unul dintre argumentele funcţiei

respective. Fig. nr. 1 Structura câmpului de criterii

Câmpul de ipoteze – în cadrul căruia se pot identifica:

o celulă situată în colţul din stânga-sus care, de regulă, este completată

prin numele parametrului care variază (celula respectivă poate să

rămână vidă sau poate fi completată printr-o listă derulantă simplă);

celule care vor fi completate prin valorile parametrului studiat;

celule completate prin funcţia/funcţiile pe baza cărora se generează

rezultatele sintetizate.

rezultatele finale actualizate în funcţie de valorile parametrului care

variază.

Macheta câmpului de ipoteze este organizată diferit, în funcţie de modul de dispunere a

valorilor parametrului de sintetizare – pe linie sau pe coloană.

a). pentru tabela de ipoteze cu o singură variabilă organizată pe verticală - câmpul de

ipoteze este structurat ca în figura 2:

Fig. nr. 2 Structura tabelei de ipoteze organizată pe verticală

Observaţie:

Exemplificarea tabelei de ipoteze cu o singură variabilă se va realiza pe o bază de date

Editura care gestionează vânzările de carte ale unei edituri (o carte poate fi distribuită în

unul sau mai multe oraşe, de către unul sau mai mulţi agenţi de vânzare - baza de date se

identifică prin domeniul A5:J23):

Fig. nr.3 Baza de date Editura

Exemplul 1: Se doreşte sintetizarea informaţiilor prin intermediul unei tabele de ipoteze

cu o singură variabilă organizată pe verticală astfel încât să se obţină suma valorică a

vânzarilor realizate de editură pe fiecare titlu de carte în ultimii 2 ani:

Fig. nr. 4 Câmpul de criterii şi câmpul de ipoteze pentru exemplul 1

Observaţie:

pentru extragerea valorilor parametrului care variază în câmpul de ipoteze

(Titlurile de carte) se recomandă utilizarea filtrării bazei de date prin metoda filtrului

avansat astfel încât fiecare valoare a câmpului respectiv să fie extrasă o singură dată.

rezultatele sintetizării (figura nr. 5) se generează prin selectarea tabelului din

câmpul de ipoteze şi alegerea din meniul Data a grupului What If Analysis – Data

Table şi completarea rubricii Column input cell cu adresa celulei de intrare din câmpul

de criterii (A32).

Fig. nr. 5 Generarea rezultatelor sintetizării prin Data Table

b). pentru tabela de ipoteze cu o singură variabilă organizată pe orizontală - câmpul de

ipoteze este prezentat în figura 6:

Fig. nr. 6 Structura tabelei de ipoteze organizată pe orizontală

Exemplul 2: Să se calculeze şi să se afişeze printr-o tabelă de ipoteze cu o singură

variabilă organizată pe orizontală nr. de exemplare vândute şi comisionul total încasat

de fiecare agent de vanzare:

Fig. nr.7 Câmpul de criterii şi câmpul de ipoteze pentru exemplul 2

În cazul tabelei de ipoteze cu o singură variabilă organizată pe orizontală, pentru

diseminarea rezultatelor pe fiecare agent de vânzare se selectează câmpul de ipoteze şi se

completează caseta Data table la rubrica Row input cell cu adresa celulei de intrare din

câmpul de criterii (A60) – figura nr. 8:

Fig. nr. 8 Generarea rezultatelor sintetizării prin Data Table

Observaţie:

În contextul utilizării tabelelor de ipoteze, există şi posibilitatea de a aplica funcţii

generatoare de rezultate sintetizate care să solicite câmpuri de criterii diferite. În aceste

cazuri, criteriile vor fi plasate de o parte şi de alta a domeniului care conţine parametrul

care variază (mai exact, denumirea rubricii pe care se realizează sintetizarea) şi celula de

intrare.

Exemplul 3: Să se calculeze numărul de exemplare vândute de fiecare agent de

vânzare către societăţile Papirus şi Coresi în ultimele 6 luni şi comisionul mediu

încasat de fiecare agent de vânzare în anul precedent.

Fig. nr. 9 Câmpul de criterii şi câmpul de ipoteze pentru exemplul 3

Observaţie:

O alta formă de aplicare a tabelei de ipoteze constă în generarea, prin alternanţă, a

rezultatelor sintetizării în acelaşi plan al câmpului de ipoteze, în raport cu funcţia aleasă

de utilizator dintr-o listă derulantă.

Exemplul 4: Să se calculeze şi să se afişeze în mod interactiv în acelaşi domeniu al

cîmpului de ipoteze vânzările minime, maxime şi medii realizate de editură pe fiecare

titlu de carte.

Fig. nr. 10 Câmpul de criterii şi câmpul de ipoteze pentru exemplul 4

Observaţie: Un caz particular de aplicare a tabelei de ipoteze cu o singură variabilă îl

constituie generarea rezultatelor de sinteză prin intermediul funcţiilor financiare. În

această variantă, fereastra Data Table va fi completată la una dintre rubricile

Column/Row input cell cu celula care conţine acel argument al funcţiei financiare a cărei

variaţie se urmăreşte.

Exemplul 5. O societate cumpără un echipament în leasing pe o perioadă de 3 ani, în

valoare de 10.000 de lei (ratele se plătesc la sfârşitul fiecărei perioade). Care ar trebui să

fie rata lunară a împrumutului în condiţiile în care dobânda anuală variază între 15% şi

20%?

Fig. nr. 11 Aplicarea tabelei de ipoteze cu o singură variabilă pe baza funcţiei financiare PMT

Funcţia PMT (PayeMenT) calculează mărimea plăţii lunare sau anuale pentru un

împrumut bazat pe plăţi constante şi o rată constantă a dobânzii. Sintaxa funcţiei este

următoarea:

=PMT(rate; nper; pv;[fv];[type])

unde:

rate (rata dobânzii) reprezintă procentul de dobândă perceput pe o anumită

perioadă exprimat procentual;

nper (număr de perioade) constituie numărul total de perioade de plată;

pv reprezintă suma împrumutată la începutul tranzacţiei;

[fv] (valoarea reziduală) este un parametru opţional care exprimă valoarea care

rămâne la sfârşitul plăţii (dacă acest parametru nu se precizează se consideră că

are valoarea 0);

[type] (tip) este un parametru care semnifică faptul că plata se efectuează la

începutul perioadei (valoarea 1) sau la sfârşitul perioadei (valoarea 0 – implicită).

Argumentele rata dobânzii şi numărul de perioade trebuie exprimate în aceeaşi unitate de

timp (luni sau ani); valoarea contractului de leasing generează o cheltuială periodică, ceea

ce justifică faptul că valoarea respectivă este trecută su semnul minus în sintaxa funcţiei

PMT (dacă suma se trece ca valoare pozitivă, rezultatul final se afişează cu roşu).

B. Tabela de ipoteze cu două variabile funcţionează pe aceleaşi principii ca

şi tabela cu o singură variabilă, cu precizarea că permite variaţia simultană a doi

parametri; în consecinţă, în câmpul de criterii se impune existenţa a două celule de intrare

la care se va face referire prin funcţiile DataBase (figura nr. 12).

Fig. nr. 12 Structura câmpului de criterii şi a câmpului de ipoteze pentru tabela cu două variabile

Exemplul 6. Să se sintetizeze informaţiile bazei de date astfel încât să se obţină volumul

total al vânzărilor realizate de editură pe fiecare titlu de carte şi pe fiecare agent de

vânzare.

Fig. nr. 13 Sintetizarea volumului total al vânzărilor realizate de editură pe fiecare titlu de carte şi pe

fiecare agent de vânzare prin tabela de ipoteze cu două variabile

Observaţie:

Datorită modului în care sunt dispuse valorile parametrilor care variază, formula de

calcul se introduce în mod invariabil în celula aflată la intersecţia primei linii cu prima

coloană a câmpului de ipoteze, fapt care restrânge considerabil posibilitatea de a aplica

mai multe funcţii agregate în acelaşi câmp de ipoteze. Eliminarea acestui inconvenient se

poate realiza prin concatenarea mai multor funcţii în celula destinată introducerii

formulelor pe baza cărora vor fi generate rezultatele finale.

Exemplul 7.

Să se afişeze în acelaşi spaţiu al câmpului de ipoteze vânzările medii realizate de editură

şi nr. total de exemplare distribuite de fiecare agent din fiecare titlu de carte în anul în

care s-au efectuat cele mai multe vânzări (anul cu cea mai frecv. apariţie).

Nota: rezultatele vor fi afişate sub forma Vanzari medii / Nr total de exemplare cu

precizarea ca mesajele de eroare vor fi înlocuite prin cratimă, iar valoarea medie a

vânzărilor va fi rotunjită la 0 zecimale.

Fig. nr.14 Câmpul de criterii şi câmpul de ipoteze pentru exemplul 7