Foarte multe dintre situatiile analizate în lumea reala depind de parametri specifici. De exemplu, beneficiile depind de, printre altele, de valoarea dobânzii cerute de banca creditoare, de termenul de punere în functiune a unei investitii etc. Analiza activitatii trebuie atunci sa considere diferite valori anticipate ale dobânzii, termene optimiste si pesimiste etc.
Într-o asemenea structura de date este important ca, atât pentru analiza, cât si pentru prezentari, sa se poata efectua rapid trecerea de la o configuratie de valori ale parametrilor la alta configuratie. Acest lucru se poate realiza, cu multa pierdere de viteza si posibilitate de eroare, prin completarea valorilor parametrilor de fiecare data când se analizeaza o situatie specifica. Microsoft Excel dispune însa de un mecanism adecvat pentru schimbarea instantanee a seturilor de valori si recalcularea corespunzatoare a foii de calcul.
Numim scenariu (scenario) un set de valori date parametrilor. Microsoft Excel gestioneaza o multime de scenarii prin crearea lor, adaugarea de noi scenarii, eliminarea, crearea unui raport etc. Fiecare scenariu este caracterizat, pe lânga valorile specifice ale parametrilor, de un nume propriu. Pentru usurinta utilizarii, se recomanda alegerea unor denumiri sugestive, de genul "Dobânda45%", "Dobânda60%" etc. Activarea unui scenariu produce atribuirea valorilor corespunzatoare la parametrii considerati si recalcularea foii de calcul.
Comanda principala este Scenarios din meniul Tools, care produce afisarea dialogului Scenario Manager. Dialogul contine în partea din stânga o lista a tuturor scenariilor deja definite (sau este vida în lipsa acestora), zone explicative pentru scenariul selectat în lista si o serie de butoane de control prin intermediul carora se realizeaza gestiunea scenariilor.
Prin selectarea unui scenariu în lista si actionarea butonului Show se realizeaza prezentarea scenariului respectiv prin actualizarea parametrilor si recalcularea foii de calcul potrivit acestor valori.
Prin selectarea unui scenariu în lista Scenarios si actionarea butonului Delete se va elimina scenariul selectat.
Pentru adaugarea unui nou scenariu se actioneaza butonul Add. Se afiseaza dialogul Add Scenario.
În caseta de editare Scenario name se va trece numele scenariului.
În zona Changing cells se vor trece referintele la celulele care contin parametrii foii de calcul (sau doar acei parametri care se modifica în scenariu). Referintele se dau prin denumiri create, prin referinte de zone (de tipul A1:A4) sau ca însiruire de referinte separate prin virgule.
În grupul Protection se alege tipul de protejare a scenariului.
Prin actionarea butonului OK se afiseaza dialogul Scenario Values. Pentru fiecare celula parametru exista câte o zona de editare în care se trece valoarea specifica scenariului în curs de definire.
Prin actionarea butonului OK se termina operatiunea de definire a scenariului si se trece în Scenario Manager.
Prin actionarea butonului Add se termina definirea scenariului curent si se revine în dialogul Add Scenario pentru a adauga un nou scenariu.
Editarea unui scenariu se realizeaza prin
- afisarea dialogului Scenario Manager prin Scenarios din meniul Tools;
- selectarea scenariului în lista Scenarios;
- actionarea butonuluide comanda Edit.
Se afiseaza dialogul Edit Scenario, structurat similar cu dialogul de definire (adaugare) a unui scenariu (Add Scenario) si se modifica atributele dorite.
Prin actionarea butonului OK se trece în dialogul Scenario Values unde se pot modifica valorile parametrilor scenariului.
În cazul în care se doreste definirea pentru foaia curenta a unor scenarii care au mai fost utilizate în alte caiete, operatiunea este posibila prin
- se deschid caietele care au scenariile dorite;
- se da comanda Scenarios din meniul Tools si se actioneaza butonul Merge din dialogul Scenario Manager.
Se afiseaza dialogul Merge Scenarios în care selecteaza, pe rând, caietul sursa în zona Book, foaia din acest caiet în Sheet.
Toate scenariile din foile selectate sunt adaugate foii curente. Este evident ca pentru ca operatiunea sa fie corecta trebuie ca celule parametri din foile sursa sa corespunda unor celule parametri din foaia curenta.
Se poate obtine în mod automat un rezumat al scenariilor definite în foaia de calcul. Raportul obtinut contine valorile parametrilor si rezultatele specificate de utilizator.
Operatiunea se realizeaza prin
- se da comanda Scenarios din meniul Tools si se actioneaza butonul Summary din dialogul Scenario Manager.
- se afiseaza dialogul Scenario Summary.
În grupul Report type se alege tipul de raport solicitat:
Scenario summary - se obtine un raport sub forma de tabel pe o foaie separata,
Scenario PivotTable - se obtine un tabel pivotant. Pentru explicatii suplimentare se va citi sectiunea dedicata tabelelor pivotante.
În Result cells se vor specifica referinte la celule care contin rezultatele dorite în raport.
Procedura Goal Seek este utila atunci când se doreste aflarea unei valori necunoscute de intrare care determina un rezultat cunoscut. Un exemplu poate fi rezolvarea unei ecuatii matematice, f(x)=0, în care rezultatul cunoscut este 0 (valoarea functiei) si trebuie sa se afle valoarea lui x pentru care functia se anuleaza. Alt exemplu este: se depune o suma de bani, sa zicem 10 milioane de lei, la o banca si se estimeaza dobânda lunara (valoarea necunoscuta) astfel încât peste 10 luni sa se acumuleze 16 milioane de lei (valoarea cunoscuta).
Analiza
exemplelor precedente arata ca în foaia de calcul trebuie sa
existe o celula rezervata pentru solutie (valoarea x) si o
celula care contine functia a carei valoare este
cunoscuta (f(x)). Este de retinut totusi ca functia nu
trebuie în mod necesar sa se refere direct la celula cu x ci poate sa
fie obtinuta în urma unor calcule intermediare (care implica
totusi, în cel putin un pas, pe x).
Pentru activarea procedurii Excel de rezolvare a problemei expuse, se da comanda Goal Seek din meniul Tools. Este afisat astfel dialogul Goal Seek prezentat în figura alaturata.
În Set cell se da referinta la celula care contine functia operata.
În To value se trece valoarea pe care trebuie sa o atinga functia.
În By changing cell se da referinta la celula care se modifica (valoarea x).
Prin actionarea butonului OK se executa procedura corespunzatoare Excel si sunt posibile doua situatii:
1)
se determina o solutie. Este afisat dialogul Goal Seek Status în care se dau
informatii despre rezultatul calculelor. Se observa specificarea
celulei care se modifica (aici C6), mesajul ca s-a gasit o
solutie (found a solution),
valoarea urmarita (Target value)
si valoarea efectiva gasita (Current value).
În cazul când solutia determinata este convenabila se da OK si valoarea este trecuta în celula care contine variabila (indicata în By changing cell). Daca se actioneaza Cancel nu se trece valoarea determinata.
2) nu se determina o solutie. În acest caz dialogul Goal Seek Status contine mesajul may not have found o solution (în locul mesajului found a solution) si valoarea din Current value nu este utilizabila desi este afisata.
Observatii. 1. Pentru stabilirea preciziei cu care se efectueaza calculele se va revedea sectiunea dedicata acestui subiect.
2. Pentru rezolvarea unor probleme de optimizare se va activa procedura Solver.
Prin unificarea datelor se întelege procedura prin care date similare, aflate pe foi de calcul diferite, sunt aduse pe o aceeasi foaie. Sa ne imaginam situatia în care filiale ale unei aceleeasi societati comerciale îsi prezinta activitatea în rapoarte aflate pe câte o foaie de calcul. Unitatea centrala trebuie sa centralizeze aceste date pentru a obtine un raport general. Procedura pe care o ofera Microsoft Excel pentru facilitarea acestei operatiuni este aceea de unificare a datelor.
În functie de structura fiecarei foi de calcul care se centralizeaza se poate alege unul dintre cele patru moduri permise si posibile de centralizare. Ca denumiri retinem
- regiunile sursa - sunt zonele din foile de calcul unde se afla datele individuale, detaliile prelucrarii (source areas);
- regiunea destinatie - zona unde se vor centraliza datele; mai este referita ca tabelul centralizator (consolidation table).
Unificarea datelor se poate efectua în mai multe moduri:
- prin referinte 3-D;
- prin pozitie;
- prin categorii;
- prin crearea unei tabele pivotante - procedura este discutata în sectiunea dedicata tabelelor pivotante.
Nu toate aceste moduri sunt asistate complet de mediul Excel, dar atunci când exista asistenta aceasta este data prin comanda Consolidate din meniul Data. Ca urmare a comenzii se afiseaza dialogul Consolidate care permite declararea regiunilor surse si functia dupa care se efectueaza centralizarea.
Pentru specificarea regiunilor sursa reamintim câteva moduri de referinta:
- este util sa se denumeasca zonele sursa pentru pastrarea controlului informatiilor centralizate;
- referirea unei zone din alta foaie a aceluiasi caiet se realizeaza prin NumeFoaie!NumeZona; de exemplu Flora!Vanzari. Daca denumirea foii contine spatii, atunci se include denumirea între apostrofuri: 'Mag Flora'!Vanzari.
- referirea unei zone din alt caiet se realizeaza prin [NumeCaiet]NumeFoaie!NumeZona; de exemplu '[Vanzari.xls]Mag Flora'!Chimicale. Numele caietului poate fi însotit de cale, daca se afla în alt folder.
- referintele pot fi date si dinamic prin selectarea cu mouse-ul în timpul editarii formulelor sau zonelor de editare necesare.
În aceasta metoda, bazata prin completarea zonei de destinatie cu formule create de utilizator, nu exista restrictii asupra structurilor de date din regiunile sursa. Se recomanda parcurgerea etapelor
- în regiunea de consolidare (zona destinatie) se copie sau se scriu etichetele necesare
- în fiecare celula care trebuie sa contina date centralizate se tasteaza formula dorita, utilizând referinte la regiunile sursa.
În acest mod de unificare se pastreaza întotdeauna legatura între zonele sursa si cele de destinatie: modificarile efectuate în surse se regasesc la recalcularea foii în destinatie.
Este procedura utila atunci când foile de calcul surse au aceeasi structura (sunt create, eventual, dupa acelasi template).
Se urmeaza urmatorii pasi.
- activeaza coltul din stânga-sus al regiunii de destinatie;
- se da comanda Consolidate din meniul Data.
Ca rezultat se afiseaza dialogul Consolidate.
- în lista derulanta Function se alege functia de centralizare dorita (suma, minimul etc.).
- în zona Reference se scrie sau se selecteaza dinamic o referinta la o regiune sursa.
- se actioneaza butonul Add.
- se repeta ultimii doi pasi pentru toate regiunile sursa care se centralizeaza.
Referintele utilizate se trec pe rând în lista All references.
- se selecteaza caseta de control Create links to source data daca se doreste actualizarea automata a centralizarii atunci când se modifica datele sursa. În acest caz sursa trebuie sa fie pe alta foaie de calcul decât destinatia si, o data create legaturile prin OK, nu se pot adauga noi surse sau modifica referintele la sursele incluse deja.
Este de notat ca etichetele existente în regiunile sursanu sunt copiate automat în zona de destinatie.
- actionarea butonului OK produce realizarea centralizarii.
Acest tip de centralizare este util atunci când datele regiunilor sursa nu sunt situate în mod identic în foile lor dar se utilizeaza aceleasi etichete.
Procesul decurge în mod similar unificarii prin pozitie, cu mici deosebiri:
- se activeaza coltul din stânga-sus al regiunii de destinatie;
- se da comanda Consolidate din meniul Data.
- în lista derulanta Function se alege functia de centralizare dorita (suma, minimul etc.).
- în zona Reference se scrie sau se selecteaza dinamic o referinta la o regiune sursa. Pentru acest mod de unificare zona referita trebuie sa includa si etichetele (capetele de tabel)
- se actioneaza butonul Add.
- se repeta ultimii doi pasi pentru toate regiunile sursa care se centralizeaza.
- în grupul Use labels in se va selecta optiunea privitoare la pozitia etichetelor: top row - linia de sus, left column - coloana din stânga, sau ambele. În acest mod se va aplica functia centralizatoare datelor care sunt etichetate identic. Daca o regiune sursa contine o eticheta care nu se regaseste în celelalte regiuni se va produce o linie/coloana separata pentru datele cu aceasta eticheta.
- se poate marca Create links to source data pentru actualizarea automata a centralizarii.
O centralizare de date efectuata manual (prin referinte 3-D) se poate modifica în mod uzual prin editarea formulelor utilizate pentru crearea tabelului destinatie.
Centralizarile efectuate prin comanda Consolidate pot fi modificate prin operarea în dialogul Consolidate.
Procedeul este permis doar daca nu s-au stabilit legaturi la regiunile sursa ale unificarii. În cazul existentei legaturilor, se elimina centralizarea (cu tot cu outline-ul eventual) si se reia procedura.
Adaugarea unei noi surse se realizeaza prin
- se activeaza coltul din stânga-sus al regiunii de destinatie;
- se da comanda Consolidate din meniul Data.
- în zona Reference se scrie sau se selecteaza dinamic noua referinta la o regiune sursa.
- se actioneaza butonul Add.
Prin actionarea butonului OK se realizeaza centralizarea care include si regiunea adaugata.
si aceasta procedura se poate efectua doar în lipsa legaturilor stabilite catre regiunile sursa (ca si adaugarea unei noi referinte).
- se activeaza coltul din stânga-sus al regiunii de destinatie;
- se da comanda Consolidate din meniul Data.
- sub All references se selecteaza referinta care se modifica;
- în zona editabila Reference se modifica referinta;
- se actioneaza butonul Add.
- se selecteaza din nou vechea referinta în lista All references si se actioneaza butonul Delete, în caz contrar ramânând si noua referinta si cea veche.
Prin OK se realizeaza centralizarea datelor potrivit referintelor actualizate.
- se activeaza coltul din stânga-sus al regiunii de destinatie;
- se da comanda Consolidate din meniul Data.
- în zona All references se selecteaza referinta la regiunea care se elimina;
- se actioneaza butonul Delete.
Prin OK se realizeaza centralizarea fara a mai considera regiunea eliminata.
Tabelele pivotante sunt cel mai puternic si mai elaborat procedeu de analiza a datelor. Prin crearea unui tabel pivotant se realizeaza un tabel de cu mai multe intrari în care se rezuma date provenite din diverse surse: liste Excel, baze de date, surse de date externe etc. Tabelul poate fi exploatat interactiv.
Puterea unui tabel pivotant consta în aceea ca imaginea grafica formata în foaia de calcul este dotata cu o serie de elemente de control si cu o serie de proceduri care permit rearanjarea automata a tabelului (pentru a vizualiza diferite aspecte), inserarea de linii/coloane de totaluri si subtotaluri, includerea de functii utilizator. În cazul în care numarul de dimensiuni este mai mare, tabelul este organizat pe pagini, fiecare dintre acestea devenind vizibila printr-o simpla selectare.
Pentru
a avea o imagine intuitiva sa consideram urmatoarea
lista Excel. Este evident ca analiza datelor este dificila în
cazul existentei unui mare numar de înregistrari. Ar trebuie un
mijloc de selectare a unui anumit reprezentant, anumit oras, anumit
produs, totaluri pe luni/produse etc.
 |
Se poate observa aranjarea pe pagini a rezultatelor, fiecare pagina fiind determinata de optiunea selectata în lista derulanta de lânga butonul reprezentant (în figura este data pagina care cuprinde date pentru toti reprezentantii). Structura de linii si coloane este determinata de numarul câmpurilor de linii sau coloane (în figura se observa doua câmpuri care determina linii - luna si produs - si un câmp care determina coloanele - localitate). Datele interioare sunt obtinute prin prelucrarea automata a câmpului valoare, functia totalizatoare fiind suma (mesajul din coltul stânga sus al tabelului). Tabelul mai contine subtotaluri pentru fiecare luna si totalurile generale pe linii si coloane.
În utilizarea unui tabel pivotant, structura acestuia se poate modifica interactiv prin dragarea butoanelor câmpuri în alte zone ale tabelului: de exemplu, daca se trage butonul luna peste zona de pagina, lânga reprezentant, atunci o pagina este definita de o optiune pentru reprezentant si o optiune pentru luna (aceasta nu va mai figura pe linii). Restructurarea dinamica produce recalcularea tabelului. Principalele operatiuni asupra unui tabel pivotant sunt prezentate în continuare.
Un tabel pivotant contine câmpuri de mai multe tipuri. Un câmp corespunde, în general, unui câmp din baza de date sau lista de baza, dar poate fi creat si de catre utilizator pe baza unei formule. Fiecare câmp are corespondent un buton care poate fi agatat si tras în alta zona a tabelului, producând restructurarea acestuia. Datorita posibilitatii de modificare a structurii tabelului pivotant, clasificarea data câmpurilor este relativa.
Câmp de pagina (page field) - este un câmp din datele sursa care produce clasificarea informatiilor pe pagini. În exemplul anterior câmpul reprezentant este câmp de pagina. Câmpul este însotit de un control de tip ComboBox din care se selecteaza pagina vizibila. În lista derulanta sunt afisate toate valorile acestui câmp care se întâlnesc în datele sursa.
Câmp de linie (row field) - este un câmp din date care a primit o orientare orizontala, pe linie. În exemplu, câmpurile luna si produs sunt câmpuri linie. Daca sunt mai multe câmpuri linie, acestea formeaza în prezentarea vizuala o structura ierarhica, doar nivelul exterior fiind listat o singura data.
Câmp de coloana (column field) - este un câmp din date care a primit o orientare verticala, pe coloana. În exemplu, câmpul localitate este un câmp coloana. Daca sunt mai multe câmpuri coloane, acestea formeaza în prezentarea vizuala o structura ierarhica, doar nivelul exterior fiind listat o singura data.
Câmp de date (data field) - este un câmp din datele sursa care contin informatia rezumata. În exemplu este câmpul valoare. Pentru datele numerice functia implicita de rezumare este sumarea (sum), pentru datele text functia implicita este cea de numarare (count).
Intrari (items) - sunt valorile (categoriile) unui câmp. De exemplu, "ian" si "feb" sunt intrari ale câmpului luna. Aceste intrari sunt utilizate în tabelul pivotant drept etichete de linii/coloane/pagini.
Regiunea datelor (data area) - este partea tabelului pivotant care contine rezumatele datelor, cu alte cuvinte zona din tabel fara liniile si coloanele de câmpuri si etichete.
Microsoft Excel dispune de un wizard specializat pentru crearea unui tabel pivotant. Cei patru pasi pe care trebuie sa-i parcurga utilizatorul în mod asistat corespund celor patru dialoguri ale wizard-ului.
Comanda PivotTable Report din meniul Data afiseaza primul dialog al wizard-ului.
Din
dialog este reprezentata doar zona de optiuni, butoanele de navigare
între ecranele dialogurilor fiind uzuale si sunt prezentate într-un
dialog ulterior.
Ca sursa de date se poate indica:
Microsoft Excel list or database - este cazul frecvent al operarii în Excel si fixeaza ca sursa o lista Excel. Este util în acest caz sa se selecteze lista înainte de a se da comanda PivotTable Report.
External data source - se arata ca datele sursa se afla într-un fisier extern (baza de date Access etc.).
Multiple consolidation range - permite realizarea unei unificari de date sub forma de tabel pivotant.
Another PivotTable - sursa de date este un tabel pivotant creat anterior.
Se actioneaza butonul Next pentru a se trece la urmatorul dialog.
În al doilea dialog al wizard-ului se precizeaza locul exact, adresa, de unde se preiau datele sursa. Daca acestea sunt într-o lista Excel dialogul este cel din figura urmatoare.
În
partea inferioara se observa butoanele de navigare între ecranele
wizard-ului:
Cancel - anuleaza operatiunea de creare a tabelului pivotant.
Back - se revine la dialogul precedent.
Next - se accepta optiunile din dialogul curent si se trece la pasul urmator.
Finish - termina faza de preluare a optiunilor si creeaza tabelul pivotant fara a mai afisa celelalte dialoguri.
Primul buton permite obtinerea de ajutor.
În zona Range se da referinta la domeniul datelor sursa, aceasta poate fi stabilita dinamica prin utilizarea butonului de restrângere a dialogului sau prin cautare actionând Browse.
Se afiseaza un dialog, prezentat în continuare, care permite stabilirea interactiva a câmpurilor tabelului. Dialogul contine o schema a tabelului cu figurarea zonelor acestuia si, în partea dreapta, o lista cu toate câmpurile definite în datele sursa.
Prin
tragerea butonului corespunzator unui câmp din lista sursa în
regiunea dorita a tabelului (Page
- pagini, Row - linii, Column - coloane, Data - regiunea datelor) se creeaza în tabel un câmp de
pagina, de linie, de coloana sau, respectiv, de date. În timpul cât
dialogul este vizibil se poate efectua orice operatiune de drag-and-drop
cu butoanele reprezentând câmpurile.
La crearea unui câmp de date se stabileste implicit functia de centralizare, dar aceasta se poate modifica prin click dublu pe câmpul de date, caz în care se poate selecta o alta functie predefinita.
 |
New worksheet - tabelul se creeaza pe o foaie noua,
Existing workshet - tabelul se creeaza într-o zona a unei foi existente. Zona poate fi selectata dinamic.
Prin actionarea butonului Finish se termina wizard-ul si se creeaza tabelul pivotant pe baza optiunilor selectate.
 |
Optiunile, suficient de sugestive prin denumirile lor, se refera în principal la includerea în tabel a liniilor/coloanelor de total (Grand totals for columns, Grand totals for rows), la formatarea automata a tabelului rezultat (AutoFormat table), la modul de afisare a celulelor vide sau cu erori (For error values, show., For empty cells, show.). Alte optiuni sunt explicate în sectiunile care urmeaza.
Grupul Data options fixeaza relatiile dintre tabel si datele sursa (salvare, actualizare etc.).
Prin OK se revine la pasul 4 al wizard-ului.
Observatie. Dialogul poate fi afisat si independent de wizard-ul de creare a unui tabel pivotant prin bara de unelte PivotTable sau meniul contextual.
 |
Pentru crearea unui câmp calculat se urmeaza algoritmul urmator.
1. Se activeaza o celula din tabelul pivotant.
2. Se da comanda Formulas din meniul PivotTable (sau bara de unelte PivotTable prezentata într-o sectiune separata) si se activeaza Calculated Field. Este afisat dialogul Insert Calculated Field, prezentat în figura urmatoare.
3. În zona de editare Name se tasteaza un nume pentru câmpul care se creeaza.
4. În zona Formula se creeaza formula dupa care se calculeaza noul câmp. Atunci când este necesara o referinta la datele dintr-un alt câmp, operatiunea se realizeaza prin selectarea în lista Fields si actionarea butonului Insert Field (rezultatul se vede în zona Formula).
5. Operatiunea se termina prin actionarea butonului Add. Noul câmp poate fi pozitionat în tabelul pivotant (pe linie sau pe coloana) în mod uzual.
Pentru crearea unui item calculat (similar unui câmp calculat, dar situat pe un nivel inferior al ierarhiei de câmpuri) se urmeaza pasii urmatori.
1. Daca tabelul pivotant este structurat ca un outline (total sau doar pentru câmpul unde se insereaza noul item), se elimina gruparea intrarilor câmpului unde are loc inserarea.
2. Se selecteaza câmpul sau o intrare a câmpului unde se adauga itemul calculat.
 |
4. În zona editabila Name se da un nume noii intrari.
5. În zona Formula se creeaza formula de calcul a itemului. Referinta la un câmp sau o alta intrare (doar din câmpul curent însa) se obtine utilizând listele Fields si Items (selectarea elementului si actionarea butonului de la baza listei respective).
6. Prin butonul Add se termina definirea unei noi intrari.
7. Pasii 2-6 se pot repeta pentru alte intrari calculate (ale aceluiasi câmp) si procesul se termina prin OK.
Observatii. - Functiile utilizate pentru rezumare nu pot fi modificate pentru câmpurile care au itemuri calculate.
- Într-o celula aflata în tabelul pivotant la intersectia unui item calculat si a unui câmp calculat, formula prioritara este aceea a câmpului.
Formulele care pot fi create în zonele Formula din dialogurile de definire a câmpurilor si subcâmpurilor calculate sunt asemanatoare celor utilizate în foaia de calcul dar nu pot contine referiri la
- celule sau zone denumite din foaia de calcul,
- functii Excel care necesita referiri la celule sau zone denumite,
- functii având ca rezultat tablouri.
Operanzii pot fi constante si elemente ale tabelului pivotant (referite prin denumirile lor, create în mod automat de procedura Excel de formare a unui tabel pivotant - corespund denumirilor de câmpuri din foaia de calcul sau denumirilor date câmpurilor calculate).
În evaluarea formulelor se urmeaza, totusi, cai diferite pentru câmpurile calculate - formulele opereaza pe rezultatele rezumate - si pentru itemurile calculate - formulele opereaza pe înregistrari si apoi se efectueaza rezumarea.
Recomandarile Excel pentru denumirile utilizabile în formule sunt:
- formulele pentru câmpurile calculate se pot referi la orice câmp sau item din datele sursa ale tabelului pivotant, inclusiv itemuri ascunse si câmpuri care nu au fost utilizate (prin dragare) la definirea tabelului;
- formulele pentru câmpurile calculate se pot referi la mai multe denumiri de câmpuri;
- formulele pentru itemurile calculate se pot referi doar la itemurile din câmpul în care se calculeaza noua intrare;
- denumirile care contin mai multe cuvinte sau includ numere sau simboluri se vor închide între apostrofuri;
- formulele nu se pot referi la totalurile din tabelul pivotant;
- referirea la un item trebuie sa contina câmpul de baza dupa modelul câmp[item] sau indexul itemului în aranjarea curenta a tabelului, de exemplu câmp[2]; exista si posibilitatea de referire a unui item prin pozitia relativa câmp[+1]; în aceasta situatie se reseteaza optiunile alese sub AutoShow si AutoSort din dialogul PivotTable Field Advanced Options la optiunea Manual.
- Formulele pentru câmpurile calculate opereaza întotdeauna pe toate datele sursa si nu exista posibilitatea de a restrânge domeniul de aplicare al unei formule doar la anumite înregistrari.
Pentru câmpurile calculate inserate de utilizator sau create de Excel ca linii/coloane de totaluri se poate alege un mod de afisare implicând calcule suplimentare asupra tabelului pivotant. Pentru a selecta un asemenea mod de afisare a rezultatelor:
- se deschide meniul contextual (prin click dreapta pe câmpul respectiv)
- se selecteaza comanda Field si, daca nu este vizibila lista derulanta Show data as, se actioneaza Options>> din dialogul afisat.
În lista derulanta Show data as sunt disponibile optiunile urmatoare, selectarea unei optiuni producând calculele si afisarea corespunzatoare a tuturor rezultatelor câmpului respectiv.
|
Function |
Result |
|
Difference From |
Afiseaza diferentele dintre valoarea câmpului selectat în lista Base field si valoarea câmpului selectat în lista Base item. |
|
% Of |
Afiseaza procentaje raportate la valoarea selectata în Base item. |
|
% Difference From |
Este o combinatie între optiunile precedente: afiseaza diferentele dintre valoarea câmpului si valoarea fixa din Base item, afisarea fiind în procente. |
|
Running Total In |
Afiseaza datele ca totaluri succesive. Câmpul pentru care se alege aceasta optiune trebuie sa fie selectat în prealabil. |
|
% of row |
Afiseaza datele din fiecare linie ca procentaj din totalul liniei. |
|
% of column |
Afiseaza datele din fiecare coloana ca procentaj din totalul coloanei. |
|
% of total |
Afiseaza datele ca procentaje din totalul general. |
|
Index |
Afiseaza datele utilizând formula: ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total)). Este proportia realizata în celula din ceea ce este asteptat în cazul unei distributii uniforme a datelor. |
Pentru operatiuni asupra elementelor unui tabel pivotant este disponibila o bara de unelte specifica. Afisarea ei se face în mod uzual: View - Toolbars - PivotTable.
_files/image001.gif) |
Meniul
PivotTable, deschis din prima
unealta a barei PivotTable,
contine comenzi dintre care unele sunt acoperite de unelte ale barei PivotTable iar altele de comenzi ale
meniului contextual asociat unui tabel pivotant.
Dintre operatiunile permise într-un tabel pivotant sunt explicate în continuare doar cele mai importante. Pentru cele omise, în special cele care sunt specifice unor surse externe de date, se va studia intrarea corespunzatoare din Microsoft Excel - Help. Multe dintre operatiuni necesita, pentru un acces mai rapid, afisarea barei de unelte PivotTable.
Prin operatiunea de actualizare întelegem recalcularea tabelului pentru a include eventualele modificari efectuate asupra datelor sursa, operatiunea fiind importanta mai ales atunci când se considera o sursa externa de date.
Pentru actualizare se urmeaza:
se selecteaza o celula a tabelului,
se actioneaza Refresh Data din bara PivotTable (sau Refresh Data din meniul PivotTable).
Observatii
a) Pentru actualizarea tabelului la deschiderea caietului se da comanda Options din meniul PivotTable si sub Data source options se marcheaza Refresh on open. În lipsa acestei marcari nu se realizeaza o actualizare a datelor la deschiderea caietului.
b) Daca sursa de date este o lista Excel si s-au adaugat noi linii si câmpuri acesteia, dupa actualizarea tabelului se poate utiliza PivotTable Wizard pentru adaugarea noilor câmpuri la structura tabelului.
c) Pentru actualizarea tuturor tabelelor pivotante din caiet se da comanda Refresh All din meniul PivotTable a barei de unelte sinonime.
Atunci când se modifica structura listei Excel considerata sursa de date a unui tabel pivotant, modificarea poate fi reflectata în tabel prin:
1. Selectarea unei celule a tabelului.
2. Comanda PivotTable Wizard.
3. În dialogul PivotTable Wizard - Step 3 of 4 se actioneaza Back.
4. În dialogul PivotTable Wizard - Step 2 of 4 se selecteaza noul domeniu al datelor sursa prin includerea liniilor/coloanelor adaugate.
5. Clic pe Next.
6. În dialogul PivotTable Wizard - Step 3 of 4 se modifica dupa necesitati structura tabelului.
7. Clic pe Finish.
Un tabel pivotant poate fi completat prin includerea de linii si coloane de totaluri, calculate în prima instanta prin functiile de sumar utilizate pentru zona de date. Aceste linii/coloane suporta însa operatia de modificare a afisarii similar unui câmp calculat.
Microsoft Excel afiseaza în mod automat linii/coloane de total doar pentru nivelul superior al ierarhiei de câmpuri. Pentru alte nivele se afiseaza subtotaluri doar la cerere.
Operatiunile implicate de liniile/coloanele de totaluri sunt descrise în continuare.
1. Se selecteaza o celula din tabelul pivotant.
2. Se da comanda Options din meniul PivotTable (deschis din bara de unelte PivotTable).
3. Pentru afisarea totalurilor generale se marcheaza casetele de control Grand totals for columns, Grand totals for rows (una sau ambele dupa necesitate). Pentru ascunderea totalurilor generale se anumeaza marcajele.
Includerea/eliminarea
subtotalurilor1. Dublu clic pe butonul câmpului pentru care se executa operatiunea. Se afiseaza dialogul PivotTable Field, aratat, partial, în figura alaturata.
2. Pentru afisarea subtotalurilor se marcheaza, când câmpul este situat pe nivelul superior (este exterior), caseta de control Automatic din grupul Subtotals. În cazul unui câmp situat pe un nivel inferior (este interior) se marcheaza Custom si se alege apoi o functie de totalizare din lista situata în dreapta grupului Subtotals.
Pentru eliminarea subtotalurilor se marcheaza None.
3. Pentru utilizarea unei functii diferite de rezumare, sau afisarea a mai multor tipuri de subtotaluri se selecteaza functiile dorite în lista din dreapta dialogului. Microsoft Excel adauga câte o linie de subtotaluri pentru fiecare functie selectata.
4. Pentru a include itemurile ascunse în subtotaluri se actioneaza în secventa: meniul PivotTable (de pe bara de unelte PivotTable) - selectarea optiunii Subtotal hidden page items din grupul Format.
1. Dublu click pe butonul câmpului din tabelul pivotant.
2. În dialogul PivotTable Field, prezentat la subiectul anterior, se selecteaza noua functie în lista din Subtotals (si se deselecteaza eventual functia anterioara).
Eliminarea unui tabel pivotant din foaia de calcul nu afecteaza datele sursa si se poate efectua prin:
A. Prin selectarea unei zone din foaia de calcul care include în totalitate tabelul si comanda Clear - All din meniul Edit. Acest mod este accesibil atunci când tabelul este înconjurat de o zona de celule libere care permite selectia prealabila stergerii.
B. Utilizând comenzile din bara de unelte PivotTable:
1. În meniul PivotTable se da Select si se asigura ca butonul Enable Selection este activ.
2. Se activeaza prin click o celula a tabelului.
3. În meniul PivotTable se da Select si apoi Entire Table.
4. În meniul Edit se da comanda Clear si se alege All.
În formatarea unui tabel pivotant trebuie retinut ca pentru pastrarea formatarii efectuate si dupa o operatiunea de actualizare (refresh) a tabelului se va începe prin verificarea ca butonul Enable Selection (dupa succesiunea bara PivotTable - meniul PivotTable - Select) este activ, ceea ce permite selectarea unor zone ale tabelului pivotant.
Se urmeaza urmatoarele etape:
1. Se selecteaza partea dorita din tabel.
2. Se formateaza (chenar, font) în mod uzual, cu unelte sau comenzi din meniul Format.
3. Pentru stabilirea optiunilor care afecteaza formatarea tabelului (cum ar fi AutoFormat) se da comanda Options (din meniul PivotTable de pe bara sinonima) sau din pasul 4 al wizard-ului PivotTable.
Deoarece datele continute într-un tabel pivotant sunt o sinteza a informatiilor, este utila reprezentarea lor într-o diagrama grafica. Prin natura tabelului pivotant acest lucru se realizeaza într-un mod specific:
1. Se permite selectarea datelor prin activarea optiunii Enable Selection la care se ajunge prin meniul PivotTable de pe bara de unelte sinonima si comanda Select a meniului.
2. Se elimina (ascund) subtotalurile.
3. Se selecteaza datele care se reprezinta grafic, incluzând câmpurile linii si coloane, dar nu totalurile generale sau câmpurile pagina. Prin dragare, selectia se va initia din coltul dreapta jos, astfel încât sa se poata selecta câmpurile linie/coloana (altfel se intra în procedura de restructurare a tabelului).
4. Se actioneaza unealta Chart Wizard si se urmeaza procedura de trasare a unei diagrame (a se vedea sectiunea dedicata acestui subiect).
Observatii. O diagrama creata dintr-un tabel pivotant ramâne legata de datele vizibile în tabel: aspectul reflecta rearanjarea câmpurilor, trecerea la o alta pagina etc.
Pentru salvarea si tiparirea diagramelor pentru toate paginile tabelului, se actioneaza Show Pages de pe bara de unelte PivotTable. Fiecare pagina apare atunci pe o foaie separata si se poate reprezenta si tipari independent de celelalte.
Se poate obtine o lista a tuturor formulelor utilizate într-un tabel pivotant prin
1. Se activeaza o celula a tabelului.
2. Se da comanda Formulas din meniul PivotTable si apoi List Formulas.
|
