UTILIZAREA PROGRAMULUI EXCEL ÎN SINTEZA ªI ANALIZA DATELOR

2010/2011 Lucrarea de laborator 2 SIE

II. UTILIZAREA PROGRAMULUI EXCEL ÎN SINTEZA ŞI ANALIZA DATELOR: CREAREA TABELELOR PIVOT ŞI A DIAGRAMELOR PIVOT.

2.1 CE SUNT TABELELE PIVOT ŞI DIAGRAMELE PIVOT?

Tabelele pivot (rapoartele PivotTable) şi diagramele pivot (rapoartele PivotChart) reprezintă facilităţi deosebite oferite de Excel pentru sinteza şi analiza datelor. Extrem de uşor de folosit de către utilizatorii domestici (nu este necesară adăugarea unor formule sau funcţii), ele se pot constitui în instrumente utile procesului managerial.

Aceste rapoarte permit crearea de noi vizualizări ale datelor din foile de calcul. În câteva secunde, se pot asambla sute sau mii de informații, punând în evidență semnificațiile din spatele datelor.

Să presupunem că avem o foaie de lucru Excel cu lista vânzărilor dintr-o firmă, care conţine mii rânduri de date cu detalii privind vânzările pe agenţi, produse, perioade de timp, locuri de desfacere etc. Un manager îşi pune întrebări legate de semnificaţia acestor date. Cum se obţin răspunsurile? Rapoartele PivotTable şi PivotChart organizează şi sintetizează datele, oferă comparaţii, pun în evidenţă modele, relaţii şi analizează tendinţe. În această lucrare vom afla cât de uşor pot aceste rapoarte să realizeze diverse vizualizări ale datelor, comparând, punănd în evidenţă şi analizând aceste date, transformându-le în informaţii pline de semnificaţii.

Decideţi ce date doriţi să se analizeze şi cum vor fi organizate acestea. În locul unui singur formular arbitrar care nu corespunde pe deplin necesităţilor, fiecare raport PivotTable oferă o viziune diferită a datelor, răspunzând imediat la interogări şi fiind particularizat în funcţie de scopurile propuse.

Pentru a compara fapte şi a le afla semnificaţia, începeţi prin a pune întrebări. Este necesar să cunoaşteţi care sunt vânzările totale în funcţie de regiune, agent de vânzări, trimestru sau lună? N-ar merge mai bine afacerea dacă cei mai buni oameni ar vinde numai produsele de top? Sau ar trebui eliminate produsele care nu aduc venituri? Odată întrebările formulate, cu Excel obțineți foarte ușor răspunsurile.

Spre deosebire de facilitatea de realizare a subtotalurilor, care modifică structura listei (bazei de date) prin ordonarea datelor după un criteriu, tabelele pivot şi diagramele pivot crează în registru de lucru elemente noi, independente.

Rapoartele PivotTable se pot folosi atunci când toate datele se află într-o listă sau într-o bază de date externă pe care o puteţi interoga cu programul Excel. Ele nu se pot folosi pentru foile de calcul structurate care cuprind subtotaluri şi rânduri de total. De exemplu, un raport PivotTable nu poate fi folosit în cadrul unei foi de lucru ce conţine un buget anual sau un bilanţ contabil etc., deoarece în aceste cazuri există deja rânduri şi coloane în care sunt realizate subtotaluri. Dar, dacă se introduc în foaia de calcul (sau se importă dintr-o bază de date externă) datele primare pentru calculul bugetului anual sau pentru bilanţul contabil, atunci se pot crea uşor asemenea rapoarte, sub formă de PivotTable, având astfel la dispoziţie mai multe opţiuni analitice.

2.2 CREAREA UNUI RAPORT PIVOT TABLE

Pentru a crea un raport PivotTable dintr-o listă existentă se deschide registrul (fişierul) care conţine lista pe care doriţi să se bazeze acesta.

De exemplu, într-un registru de calcul numit Ex_PivotT_01 avem o foaie de calcu Vânzări, care are foarte multe înregistrări, cu structura de mai jos:

Ţară Agent de vânzări Valoare comenzi ROM Florescu 2.542,00 lei ROM Ionescu 3.124,00 lei ROM Constantin 4.132,00 lei ROM Marin 6.534,00 lei ROM Constantin 1.514,00 lei USA Florescu 6.512,00 lei USA Ionescu 3.211,00 lei

1


Ţară Agent de vânzări Valoare comenzi USA Marin 424,00 lei USA Marin 545,00 lei USA Florescu 627,00 lei ESP Ionescu 1.239,00 lei ESP Constantin 414,00 lei ESP Marin 9.843,00 lei ESP Ionescu 1.278,00 lei .... ........ .....ESP Florescu 1.456,00 lei

Atenţie: realizarea unei tabele pivot, înainte de a fi o problemă tehnică (de cunoaştere a ceea ce poate oferi programul Excel), este una funcţională. Utilizatorul, bun cunoscător al domeniului în care se încadrează problema, trebuie să definească bine cerinţele. Altfel zis: trebuie să stabilească exact ce informaţii vor apărea în raport şi cum vor fi ele grupate.

Să presupunem că în lista de mai sus sunt peste 2000 de înregistrări. Cum trebuie grupate datele pentru a servi procesului decizional? Pentru a afla cum, începem prin a pune întrebări despre ce este necesar să aflăm:

o cât a vândut fiecare agent de vânzări?o care este volumul vânzărilor după ţară?După ce am rezolvat problema din punct de vedere funcţional, parcurgem etapele de mai

jos.1. Se efectuează un click de mouse oriunde în listă. Programul Excel consideră ca fiind o

bază de date (listă) toate datele aflate în tabel într-o zonă continuă. Plasarea cursorului în interiorul listei şi trecerea la pasul următor (ca de altfel şi utilizarea altor comenzi din meniul Data, pecum Sort, Filter, Subtotals ...) determină inplicit utilizarea tuturor datelor din listă pentru comanda care va fi dată în contiunuare.

Dacă se doreşte realizarea unui raport PivotTable (sau o sortare sau o filtrare, sau un subtotal etc.) dintr-un set de date din listă, se selectează domeniul care conţine datele.

2. Se apelează meniul Data – PivotTable and PivotChart Reports..., iar pe ecran apare programul utilitar Pivot Table and PivotChart Wizard (vrăjitorul), aşa cum arată în figura 1.1, care ne ajută să construim tabela pivot.

Figura 1.1 – Fereastra principală a utilitarului PivotTable and PivotChart Wizard

2


3. Programul utilitar cere să se specifice sursa de date (Where is the data that you analyze?), care poate fi:

o Microsoft Office Excel or database - o listă Excel, respectiv lista curentă sau alta ce va fi ulterior precizată.

o External data source – se utilizează atunci când raportul PivotTable se va baza pe rezultatul unei interogări a unei baze de date externe, de exemplu o tabelă realizată în VisualFox care conţine înregistrările cu privire la situaţia vânzărilor. Dacă se alege această opţiune, se porneşte programul utilitar Query Wizard şi solicită opţiuni pentru formatul şi locaţia bazei de date. Apoi programul Excel utilizează această interogare ca sursă pentru raportul PivotTable sau PivotChart. De fiecare dată când se reîmprospătează datele din tabela pivot, Excel-ul rulează interogarea salvată şi reactualizează raportul cu modificările intervenite în baza de date externă utilizată ca sursă.

o Multiple consolidation ranges – dacă se utilizează această opţiune, programul Excel permite, ca pentru realizarea tabelei pivot, să se selecteze domenii din una sau mai multe foi de calcul.

Tot în cadrul acestui pas, se precizează şi tipul raportului ce va fi obţinut (What kind of report do you want to create?):

o o tabelă pivot;o o diagramă pivot însoţită de o tabelă pivot.În exemplul nostru, în ambele secţiuni alegem prima opţiune şi apoi efectuăm click pe

butonul Next.4. Programul vă cere să specificaţi domeniul în care sunt localizate datele (figura 1.2).

Selecţia implicită este lista curentă (este opţiunea pe care o facem în exemplul nostru) sau orice domeniu pe care a fost selectat anterior pornirii vrăjitorului. Dacă este necesar se modifică zona selectată şi se efectuează click pe butonul Next.

Figura 1.2 – Selectarea domeniului în care sunt localizate datele5. Apoi programul utilitar vă întreabă unde să plaseze raportul PivotTable, figura 1.3. Este

recomandată alegerea opţiunii implicite New Worksheet, deoarece prin adăugarea unui raport PivotTable în propria sa foaie vă expuneţi riscului ca modificările efectuate în listă să afecteze raportul PivotTable şi invers.

Figura 1.3 – Alegerea amplasamentului raportului şi a opţiunilor de realizare

3


Dacă se alege opţiunea Existing Worksheet, va trebui să alegeţi adresa unde va fi poziţionat tabelul pivot.

Pentru a controla, încă din această etapă, modul de afişare al tabelei pivot apăsaţi butonul Layout... În cadrul ferestrei Layout vor fi afişate structurile de date pe baza cărora se va construi tabelul pivot. Datele (câmpurile) vor fi introduse în cadrul tabelului cu ajutorul metodei Drag & Drop. Vă recomandăm să treceţi peste apelarea butonului Layout... şi să configuraţi modul de prezentare a informaţiilor în etapa finală (unde veţi vedea că există ... tot două posibilităţi de configurare!).

La acest pas, mai puteţi stabili şi anumite opţiuni asupra tabelului cu ajutorul butonului Options..., care va deschide fereastra Pivot Table Options, figura 1.4:

Figura 1.4 – Stabilirea opţiunilor de realizare a tabelei pivoto În cadrul meniului Name se poate stabili numele tabelei pivot.o Grand totals for columns / Grand totals for rows permit activarea totalurilor

generale pe coloane, respectiv pe linii.o AutoFormat table determină aplicarea unui format predefinit pentru tabel.o For error values show gestionează erorile prin înlocuirea valorilor greşite cu un

caracter sau cu un spaţiu.o For empty cells show permite aceleaşi setări ca şi opţiunea anterioară, dar pentru

celulele goale.o Repeat item labels on each printed page determină imprimarea pe fiecare pagină

a etichetelor de câmpuri.o Save data with table layout salvează datele împreună cu paginile tabelei pivot.o Refresh to open actualizează la deschidere tabela pivot pe baza modificărilor

efectuate în lista (baza de date) ce a constituit sursa de dateDupă stabilirea opţiunilor de realizare şi, eventual, după stabilirea modului de afişare a

tabelei, se revine la fereastra din figura 1.3 şi se alege butonul Finish.5. Programul utilitar Pivot Table Wizard se închide şi se crează o nouă foaie de calcul în

care se afişează bara de instrumente PivotTable, fereastra Pivot table field list şi ecranul de proiectare structurat în patru zone (vezi figura 1.5):

o Row (Drop Row Fields Here) – câmpurile care se introduc aici vor fi amplasate în capul primei coloane a tabelei pivot.

4


o Column (Drop Column Fields Here) – câmpurile care se introduc aici vor fi amplasate pe prima linie a tabelei pivot.

o Page (Drop Page Fields Here) – câmpurile care se introduc aici crează un meniu derulant care va permite filtrarea înregistrărilor din tabel.

o Data (Drop Page Fiefds Here) – permite definirea câmpurilor asupra cărora se vor realiza diverse funcţii sub formă de sumă, minim, maxim etc. Funcţia implicită este SUM.

Figura 1.5 – Proiectarea structurii tabelei pivotPentru plasarea câmpurilor în spaţiu de proiectare aveţi (cel puţin) două metode:a. Prin metoda Drag & Drop, plasaţi câmpuri din lista de câmpuri a tabelei pivot (Pivot

Table Field List) în cele patru zone din fereastra de proiectare (row, column, page, data) în funcţie de ceea ce aţi prevăzut să rezolve tabela pivot.

b. În fereastra Pivot Table Field List selectaţi câmpul pe care doriţi să îl plasaţi în tabel. Din lista ascunsă, aflată în colţul din dreapta jos al ferestrei, selectaţi zona în care vreţi să plasaţi câmpul şi apoi apelaţi butonul Add To.

Figura 1.6 – Finalizarea raportului PivotTableÎn figura 1.6 este prezentată ultima etapă de generare a tabelei pivot pentru exemplul avut

în vedere.Nu fiţi surprinşi dacă raportul PivotTable nu este afişat corespunzător de prima dată. În

particular, câmpurile de centralizare din zona de date sunt prestabilite pentru funcţia SUM. Dacă

5


doriţi să utilizaţi o altă funcţie sau să modificaţi structura şi formatul tabelei pivot, parcurgeţi paragraful următor.

2.3 EDITAREA ŞI ACTUALIZAREA TABELELOR PIVOT

După ce creaţi un raport PivotTable, este uşor să rearanjaţi câmpurile şi articolele de date. Astfel, trageţi câmpurile dintr-un loc în altul pentru a schimba afişarea datelor, de exemplu

dintr-un câmp de linii într-un câmp de coloane, dacă doriţi să vizualizaţi valorile unele lângă altele, în loc de una deasupra alteia.

Pentru a avea la dispoziţe comenzile necesare modificării unei tabele pivot, efectuaţi click din butonul drept al mouse-ului în interiorul tabelei pivot şi va fi afişat meniul contextual prezentat în figura 1.7 sau deschideţi lista ascunsă Pivot Table din bara de instrumente cu acelaşi nume şi apare meniul din figura 1.8.

Figura 1.7 – Opţiuni pentru modificarea unei tabele pivot (meniu contextual)

Figura 1.8 – Opţiuni pentru modificarea unei tabele pivot (bara de instrumente Pivot Table)

6


Pentru a modifica lista sau sursa de date pe care se bazează raportul Pivot Table procedaţi astfel:

o apelaţi opţiunea Pivot Table Wizard;o daţi de două ori Back pentru a reveni la începutul programului utilitar;o efectuaţi modificările dorite;o apelaţi butonul Finish. Pentru a adăuga la machetă un nou câmp alegeţi una din cele două metode descrise la

punctul 5 din paragraful 2.2. Dacă înlocuiţi un câmp existent, este bine ca, mai întâi să îl ştergeţi pe cel vechi, pentru a

nu efectua calcule inutile. Atunci când plasaţi un nou câmp în zona de rânduri sau de coloane, programul Excel îl

adaugă ca parte a ierarhiei de câmpuri care se află deja acolo şi grupează automat articolele în ordinea în care apar. De exemplu, dacă aveţi o listă de categorii de produse, în care fiecare categoriie conţine mai multe produse, plasaţi câmpul de categorii la stânga câmpului cu numele produsului, în caz contrar rezultatele raportului nu vor avea sens. Sau dacă aveţi o listă de conturi analitice, în care fiecare cont analitic se dezvoltă în mai multe conturi sintetice, plasaţi câmpul de conturi sintetice la stânga câmpului cu numele conturilor analitice. Dacă lista conţine două câmpuri care se află într-o corespondenţă de unu-la-unu, cum ar fi numele persoanei si CNP-ul, atunci puteţi să le adăugaţi în zona de rânduri în orice ordine, lista fiind corectă în ambele cazuri.

Pentru a şterge un câmp din orice parte a machetei PivotTable, trageţi butonul de câmp în afara machetei, iar atunci când pictograma indicatorului se modifică, astfel încât să devină un X de culoare roşie, eliberaţi butonul mouse-ului.

Pentru a schimba ordinea câmpurilor din rânduri, coloane sau zona de date, trageţi butonul de câmp şi plasaţi-l în noua locaţie. Asiguraţi-vă că indicaţi butonul de câmp nu eticheta acestuia, Veţi şti că este corect dacă indicatorul mouse-ului se transformă în patru săgeţi.

Utilizarea mouse-ului pentru a rearanja ordinea articolelor de date dintr-un raport PivotTable poate fi incomodă. Este mai simplu dacă efectuaţi click de dreapta pe butonul de câmp pe care doriţi să îl mutaţi şi apoi alegeţi oricare din opţiunile meniului Order.

Pentru a schimba funcţia de centralizare implicită utilizată în zona de date (SUM) efectuaţi click de dreapta pe butonul de câmp din raportul PivotTable şi alegeţi, din meniul contextual, opţiunea Field setings. Se deschide caseta de dialog PivotTable Field, prezentată în figura 1.9.

Figura 1.8 – Modificarea funcţiei implicite de însumare din zona de dateDin lista Summarize se alege funcţia dorită, al cărui nume prestabilit se poate schimba în

caseta Name.

Ordinea de sortare prestabilită pentru rânduri şi coloane este alfanumerică. Se poate schimba ordinea unor articole prin tragerea lor în sus sau în jos (în cazul rândurilor), la stânga sau la dreapta (în cazul coloanelor).

Pentru a schimba automat ordinea de sortare, procedaţi astfel:1. Efectuaţi click dreapta pe butonul pentru rânduri sau coloane şi alegeţi oţiunea Field

Setings.

7


2. Efectuaţi click pe butonul Advanced, pentru a afişa caseta de dialog Pivot Table Fields Advanced Options, figura 1.9.

3. Alegeţi ordinea de sortare şi coloana după care veţi efectua sortarea. 4. Pentru a afişa un anumit număr de înregistrări alegeţi opţiunea On din din secţiunea

Top 10 AutoShow. De fapt veţi putea afişa primele sau ultimele n înregistrări, folosind butonul de incrementare/decrementare Show (n poate lua valori până la 255).

Figura 1.9 – Sortarea în tabelele pivot

Într-o listă se pot adăuga subtotaluri ale rândurilor, coloanelor sau ambelor. În multe cazuri, programul Excel le adaugă automat, chiar dacă nu sunt adecvate. Subtotalurile pot reprezenta o modalitate utilă de prezentare a informaţiilor sau pot adăuga dezordine între rânduri şi coloane.

În funcţie de concepţia raportului PivotTable şi de ceea ce a adăugat automat programul Excel, este, de multe ori, nevoie să adăugaţi sau să ştergeţi aceste subtotaluri.

În unele cazuri, puteţi şterge subtotaluri cu ajutorul meniurilor contextuale. Efectuaţi click de dreapta pe oricare din subtotaluri şi alegeţi opţiunea Hide.

Pentru a utiliza subtotaluri procedaţi astfel:1. Efectuaţi click dreapta pe butonul Pivot Table corespunzător titlului rândului sau

coloanei care conţine subtotalul şi alegeţi opţiunea Field Setings din meniul contextual. Programul Excel afişează caseta de dialog prezentată în figura 1.10.

2. În secţiunea Subtotals, alegeţi opţiunea Automatic, pentru a lăsa programul Excel să creeze subtotaluri pentru toate articolele. Alegeţi opţiunea Custom pentru a adăuga unul sau mai multe tipuri specifice de subtotaluri, cum ar fi Count sau Average. Selectaţi None pentru a şterge toate subtotalurile.

3. Selectaţi butonul OK pentru a ieşi din caseta de dialog şi a salva modificările efectuate.

Figura 1.10 – Setarea subtotalurilor

8


După cum indică şi denumirea, macheta prestabilită pentru un raport PivotTable este în format tabelar. Dar acest aranjament nu reprezintă întotdeauna cea mai eficientă modalitate de a prezenta datele.

Atunci când grupaţi un raport PivotTable după un câmp de rânduri şi afişaţi datele după al doilea câmp de rânduri, probabil este mai bun formatul de tip schiţă, în care câmpul de rânduri de pe nivelul de sus apare în primul rând urmat de fiecare grup de articole.

Care varianta este mai bună: tabel sau schiţă? Nu există un răspuns întotdeauna corect. El este în funcţie de datele din tabel şi de considerente estetice.

Pe baza tabelului cu structura de mai jos, s-a realizat un tabel pivot.

Ţară Agent de vânzări Oraş Valoare comenzi ESP Ionescu Madrid 1.239,00 lei ESP Constantin Valencia 414,00 lei ESP Marin Madrid 9.843,00 lei ESP Ionescu Madrid 1.278,00 lei ESP Florescu Valencia 1.456,00 lei ROM Florescu Cluj 2.542,00 lei ROM Ionescu Cluj 3.124,00 lei ROM Constantin Arad 4.132,00 lei ROM Marin Arad 6.534,00 lei ROM Constantin Arad 1.514,00 lei USA Florescu Dalas 6.512,00 lei USA Ionescu Boston 3.211,00 lei USA Marin Boston 424,00 lei USA Marin Boston 545,00 lei USA Florescu Dalas 627,00 lei

În figura 1.11 el este prezentat sub formă de tabel, iar în figura 1.12 este prezentat sub formă de schiţă.

Figura 1.11 – Un raport PivotTable prezentat sub formă de tabel

Pentru a comuta între formatele de tip tabel şi schiţă, efectuaţi următoarele:1. Executaţi click de dreapta pe butonul PivotTable corespunzător câmpului de rânduri

care se află cel mai în stânga şi alegeţi opţiunea Field Setings din meniul contextual.2. În caseta de dialog PivotTable Field, efectuaţi click pe butonul Layout pentru a afişa

caseta de dialog PivotTable Fields Layout, figura 1.13.3. Pentru a utiliza o machetă tabelară selectaţi opţiunea Show Items in Tabulator Form.

Pentru a o machetă în stil schiţă selectaţi opţiunea Show Items in Outline Form.

9


4. Efectuaţi orice alte modificări, de exemplu pentru a adăuga o linie necompletată sau un salt la pagină nouă după fiecare grup şi apoi selectaţi butonul OK.

Figura 1.12 – Un raport PivotTable prezentat sub formă de schiţă

Figura 1.13 – Comutarea între macheta de tip tabel şi macheta de tip schiţă

Pentru a face ca vederea tabelară din figura 1.11 să fie mai uşor de citit, se poate utiliză o opţiune: se efectuează click dreapta oriunde în tabel, se alege Table Options şi apoi se bifează opţiunea Merge Labels. Efectul constă în îmbinarea tuturor celulelor corespunzătoare etichetelor rândurilor şi coloanelor exterioare.

2.4 ALTE ACTUALIZĂRI PENTRU RAPOARTELE PIVOTTABLE

Ştergerea celulelor necompletate şi configurarea mesajelor de eroare. Pentru a crea un raport PivotTable uşor de citit, cu aspect profesionist, este bine să acordăm atenţie celulelor necompletate şi mesajelor de eroare. Aceasta deoarece rapoartele PivotTable centralizează automat toate datele, iar în zona de date pot să apară celule necompletate şi mesaje de eroare. Erorile de tipul #DIV/0 sunt obişnuite mai ales când se calculează mediile dintr-o listă lungă, deoarece este aproape sigur că unele articole nu vor avea corespondent la intersecţia dintre un anumit rând şi o coloană.

De exemplu, pe baza listei de la începutul paragrafului 2.2, se generează un raport PivotTable care are în zona row ţara, în zona column agentul de vânzări, iar în zona data valoare comenzi şi dorim să analizăm media vânzărilor. Dacă anumiţi agenţi de vânzări nu au vândut în unele ţări, atunci vor apărea mesaje de eroare, figura 2.14.

10


Figura 2.14 – Mesaje de eroare într-un raport PivotTable

Pentru a configura aspectul celulelor necompletate şi al mesajelor de eroare se procedează astfel:

5. Deschideţi fereastra pentru modificarea unei tabele pivot (vezi figurile 2.7 şi 2.8 de la paragraful 2.3).

6. Selectaţi caseta de validare For error values, show (pentru valorile eronate, se va afişa) şi modificaţi conţinutul casetei de text din dreapta cu mesajul care doriţi să apară în cazul unor valori eronate (nc – nu se calculează, de exemplu).

7. Selectaţi caseta de validare For empty cells, show (pentru celulele necompletate, se va afişa) şi modificaţi conţinutul casetei de text din dreapta cu mesajul care doriţi să apară în cazul unor celule necompletate (de exemplu: puteţi să afişaţi 0, în cazul în care câmpul conţine date numerice sau ld – lipsă date – în rest).

8. Apelaţi butonul OK pentru a salva modificările făcute.

Actualizarea datelor într/un raport PivotTable. Atunci când se schimbă macheta unui raport PivotTable, Excel-ul actualizează automat datele din tabel. De exemplu, dacă aţi generat iniţial un tabel pe agenţi de vânzări (zona row) şi ţări (zona column), iar apoi, în zona de coloană se înlocuieşte câmpul ţări cu câmpul oraşe, el recalculează automat afişarea datelor.

Dar dacă se modifică lista de bază (în foaia de calcul după care a fost generat raportul se schimbă valoarea unor celule şi/sau apar linii sau coloane noi), modificările nu apar automat în tabelul pivot. Este necesară actualizarea (manuală) (reîmprospătarea datelor) din tabel. Pentru aceasta, trebuie apelată comanda Refresh Data (din bara de instrumente PivotTable sau din lista ascunsă de pe aceeaşi bară sau din meniul contextual sau din meniul Data).

Se poate opta şi pentru varianta reîmprospătării datelor din tabela pivot ori de câte ori se deschide registru de lucru. Pentru aceasta, din lista PivotTable de pe bara de instrumente PivotTable sau din meniul contextual ce apare la un click dreapta de mouse în suprafaţa tabelului pivot, se apelează comanda Table Options, iar fereastra Pivot Table Options care apare (figura 2.4) se bifează căsuţa de validare Refresh on open (reîmprospătare la deschidere).

Atenţie: 1. Opţiunea Refresh every n minutes nu este activă decât în cazul în care pentru

generarea raportului PivotTable s-a folosit o sursă externă de date (de exemplu, o bază de date VisualFox).

2. Dacă, în lista sursă (de bază), se adaugă noi linii sau noi coloane în afara cadrului iniţial al tabelului (altfel spus: se adaugă o linii mai jos de ultima linie sau se adaugă coloane la dreapta ultimei coloane ), pentru actualizarea tabelei pivot apelarea comenzii Refresh Data nu produce nici un efect. În acest caz, procedura de actualizare este următoarea:

a. se plasează cursorul în interiorul tabelei pivot şi, din meniul contextual ce apare prin apăsarea butonului drept al mouse-ului, se alege opţiunea Pivot Table Wizard, care lansează programul utilitar pentru generarea tabelelor pivot, dar (atenţie!) programul este lansat în cel de al treilea pas;

b. se apelează butonul Back şi se revine la pasul doi şi se selectează întreaga listă.

Formatarea rapoartelor PivotTable. Atunci când se crează un raport PivotTable, acesta preia formatările prestabilite pentru registru de lucru din care face parte.

11


Se pot aplica formatări noi, atât textului cât şi numerelor de pe cuprinsul unui raport PivotTable, prin folosirea opţiunilor de formatare la fel ca la o foaie de calcul obişnuită. Dar, dacă se redefineşte ulterior raportul pivot, se vor pierde aceste formatări.

Pentru a face ca rapoartele PivotTable sa arate cât mai bine, se poate profita de capacitatea AutoFormat din programul Excel. Astfel, după crearea raportului, se efectuează click cu butonul stâng al mouse-ului pe butonul Format Reports din bara de instrumente PivotTable. Apare caseta de dialog AutoFormat, care conţine 21 de formate prestabilite. Se alege unul dintre aceste formate şi se selectează butonul OK pentru a aplica modificările în raportul PivotTable.

Formatarea de mai sus se poate anula astfel: din fereastra Pivot Table Options (care se deschide apelând comanda Table Options... din meniul contextual rezultat în urma unui click dreapta de mouse în suprafaţa tabelei pivot sau din meniul ce se deschide apelând lista ascunsă Pivot Table din bara de instrumente cu acelaşi nume) se şterge semnul de validare aferent casetei AutoFormat table. Apoi se efectuează click pe butonul Format Reports, pentru a deschide din nou caseta de dialog AutoFormat şi se alege opţiunea None.

2.5 CREAREA ŞI EDITAREA DE DIAGRAME (GRAFICE) PIVOTCHART

Un raport PivotChart este o diagramă bazată pe datele din PivotTable. Fiecare diagramă PivotChart necesită deci un tabel PivotTable, pe care îl utilizează ca

sursă de date.Sunt două variante pentru crearea unei diagrame PivotChart:1. Există un raport PivotTable creat anterior. În acest caz, se plasează cursorul în

interiorul tabelei pivot şi, din unul din meniurile descrise în figurile 2.7 şi 2.8 de la paragraful 2.3, se apege opţiunea PivotChart. Drept rezultat, pe baza tabelului pivot existent se crează, într-o nouă foaie de calcul denumită Chart n, un grafic, care apoi se poate modifica în mod obişnuit.

2. Nu există un raport PivotTable anterior creat. a. După apelarea programului utilitar Pivot Table and PivotChart Wizard (figura

2.1), la întrebarea What kind of report do yu want to create? se alege a doua opţiune, PivotChart report (with PivotTable report).

b. După parcurgerea celui de al treilea pas din programul de tip wizard, macheta de proiectare este cea prezentată în figura 2.15. Se observă anumite diferenţe faţă de proiectarea unei tabele pivot:

i. Zona pentru câmpurile de rânduri s-a transformat în zonă pentru câmpurile de categorii;

ii. Zona pentru câmpurile de coloană s-a transformat în zonă pentru câmpuri de seri.

Plasarea şi actualizarea câmpurilor şi a datelor câmpurilor în spaţiul de proiectare nu diferă, în cazul diagramelor PivotChart faţă de tabelele pivot.

Pe de ată parte, pentru diagramele PivotChart se pot utiliza aceleaşi opţiuni de formatare şi editare ca şi în cazul diagramelor (graficelor) convenţionale. Se pot apela meniurile contextuale pentru a alege alt tip de grafic, a formata seriile de date etc.

12


Figura 2.14 – Macheta de proiectare a unei diagrame PivotChart

Exerciţii propuse1. Într-un registru de calcul Excel, TabelPivot_02.xls, se află o singură foaie de calcul Vînzări_Produse, care conţine datele structurate după modelul de mai jos.Agent_Vinzare Anul Produs Oras Cantitate Pret ValoareC 2005 P1 O2 7 15 105A 2005 P3 O1 10 13 130A 2005 P2 O1 11 22 242C 2006 P3 O3 11 13 143A 2006 P1 O1 12 15 180

Generaţi (scrieţi) înregistrări în această foaie de calcul în aşa fel încât:- sunt minim trei agenţi de vânzare;- fiecare agent de vânzare a desfăşurat activitate în trei ani (2005, 2006 şi 2007);- în fiecare an, agentul a vândut cel puţin trei produse;- fiecare produs a fost vândut de fiecare agent în cel puţin trei oraşe.

2. Generaţi o tabelă pivot cu numele Analiza_01 în care să sintetizaţi vânzările cantitative pe fiecare agent, an şi produs. Copiaţi foaia de calcul Analiza_01 în foaia de calcul Analiza_02. În Analiza_02 comutaţi tabela pivot din tabel în schiţă. Reveniţi. Ştergeţi câmpul ce reprezintă anul. Inseraţi câmpul ce reprezintă oraşul.3. Generaţi o tabelă pivot cu numele Analiza_03 în care să sintetizaţi vânzările valorice pe fiecare an, produs şi oraş. Sortaţi tabelul pivot descrescător după valoarea vânzărilor din fiecare oraş.4. Analizaţi,în tabela pivot Analiza_04, numărul de produse vândute de fiecare agent în diferiţi ani şi pe oraşe.5. Pentru datele din tabelul aflat la exerciţiul nr. 1, realizaţi o diagramă PivotChart care să pună în evidenţă vânările cantitative pe produse şi pe agenţi de vânzări. Realizaţi diagrama în două variante:

- mai întâi creaţi un tabel pivot şi apoi, din acesta, generaţi diagrama;- creaţi în acelaşi timp diagrama PivotChart şi tabelul PivotTable.

6. Modificaţi diagramele create la punctul 1 prin plasarea în zona de pagină a câmpului Anul.

13

UTILIZAREA PROGRAMULUI EXCEL ÎN SINTEZA ªI ANALIZA DATELOR

Documents

Transcript of UTILIZAREA PROGRAMULUI EXCEL ÎN SINTEZA ªI ANALIZA DATELOR