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.
Desi o asemenea operatiune este posibila prin procedura de
filtrare a unei liste Excel, o imagine mai completa a realitatii
se poate obtine formānd un tabel pivotant bazat pe aceste date. Un exemplu
este dat īn figura urmatoare.
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.
Ultimul dialog al wizard-ului determina locul unde se creeaza
tabelul si diverse optiuni privind structura acestuia.
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.
Īn grupul de butoane este afisat īn acest dialog butonul Options care deschide un nou dialog
pentru configurarea tabelului. Dialogul este prezentat īn figura
urmatoare.
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.
Īn afara de liniile de totalizare create īn mod automat se pot introduce
linii suplimentare care sa contina date calculate dupa
formule proprii. Īn aceasta operatiune se face distinctia īntre
un cāmp calculat, situat īn nivelul
superior al ierarhiei de cāmpuri linie sau coloana, si un item calculat, care reprezinta un
cāmp inserat pe un nivel inferior al ierarhiei de cāmpuri.
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.
3. Se da comanda Formulas din
meniul PivotTable (sau bara de
unelte PivotTable) si se
activeaza Calculated Item. Este
afisat dialogul Insert Calculated
Item, prezentat īn figura urmatoare. Este de remarcat informatia
suplimentara din linia de titlu privind cāmpul īn care se introduce noul
subcāmp.
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)
Īn figura urmatoare este prezentata aceasta bara de unelte.
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.
|
