Analiza datelor în Microsoft Excel 2010 Tema 7 Suport de curs, anul I Pag. 1 Unităţi de învăţare: Caracteristici ale rapoartelor şi diagramelor pivot. Construirea tabelelor pivot Actualizarea tabelelor pivot. Diagrame pivot Obiectivele temei: pivotarea şi principalele caracteristici ale tabelelor şi diagramelor pivot; crearea unei tabele pivot pornind de la foi de calcul ce conţin liste Excel; editarea tabelelor pivot prin: adăugarea de noi câmpuri; ştergerea de câmpuri; schimbarea ordinii câmpurilor şi a înregistrărilor în cadrul aceleiaşi zone şi mutarea câmpurilor dintr-o zonă în alta; actualizarea datelor din tabelele pivot atunci când apar modificări în listele pe baza cărora sunt construite; utilizarea formatelor predefinite pentru tabelele pivot; crearea şi actualizarea diagramelor pivot. Bibliografie recomandată: Abbot Katz, Beginning Microsoft Excel 2010, Apress, New York, 2010 John Walkenbach, Microsoft Excel 2010, Wiley Publishing, Inc., Indianapolis, 2010 Silvia Curteanu, Excel prin exemple, Polirom, Iaşi, 2004 Curtis D. Frye, Microsoft Excel 2010 – Step by step, Microsoft Press, Washington, 2010 Wayne L. Winston, Microsoft Excel 2010 – Data Analysis and Business Modeling, Microsoft Press, Washington, 2011 Andre Odnoha, Excel 2010 Financials Cookbook, Packt Publishing, Birmingham, 2011 Liţoiu V., Buşe R., Buligiu I., Dănciulescu D., Informatică economică aplicată : manual pentru învăţământ la distanţă, Ed. Universitaria, 2010 Tema 7 ANALIZA DATELOR ÎN MICROSOFT EXCEL 2010
Transcript
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 1
Unităţi de învăţare:
Caracteristici ale rapoartelor şi diagramelor pivot. Construirea tabelelor pivot
Actualizarea tabelelor pivot. Diagrame pivot
Obiectivele temei:
pivotarea şi principalele caracteristici ale tabelelor şi diagramelor pivot;
crearea unei tabele pivot pornind de la foi de calcul ce conţin liste Excel;
editarea tabelelor pivot prin: adăugarea de noi câmpuri; ştergerea de câmpuri; schimbarea ordinii câmpurilor şi a înregistrărilor în cadrul aceleiaşi zone şi mutarea câmpurilor dintr-o zonă în alta;
actualizarea datelor din tabelele pivot atunci când apar modificări în listele pe baza cărora sunt construite;
utilizarea formatelor predefinite pentru tabelele pivot;
crearea şi actualizarea diagramelor pivot.
Bibliografie recomandată:
Abbot Katz, Beginning Microsoft Excel 2010, Apress, New York, 2010
John Walkenbach, Microsoft Excel 2010, Wiley Publishing, Inc., Indianapolis, 2010
Silvia Curteanu, Excel prin exemple, Polirom, Iaşi, 2004
Curtis D. Frye, Microsoft Excel 2010 – Step by step, Microsoft Press, Washington, 2010
Wayne L. Winston, Microsoft Excel 2010 – Data Analysis and Business Modeling, Microsoft Press, Washington, 2011
Andre Odnoha, Excel 2010 Financials Cookbook, Packt Publishing, Birmingham, 2011
Liţoiu V., Buşe R., Buligiu I., Dănciulescu D., Informatică economică aplicată : manual pentru învăţământ la distanţă, Ed. Universitaria, 2010
Tema 7 ANALIZA DATELOR ÎN MICROSOFT EXCEL 2010
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 2
7.1 CARACTERISTICI ALE RAPOARTELOR ŞI DIAGRAMELOR PIVOT. CONSTRUIREA TABELELOR 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, prin sintetizarea 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 creează î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.
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.
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 3
De exemplu, într-un registru de calcul numit Ex_PivotT_01 avem o foaie de calcul Vânzări, care are foarte multe înregistrări, cu structura din tabelul nr. 7.1:
Tabelul nr. 7.1 Lista vânzărilor pe agenţi de vânzare şi ţări
Ţară Agent de vânzări Valoare comenzi
ROM Florescu 2.542,00 lei
ROM Ionescu 3.124,00 lei
ROM Constantin 1.514,00 lei
USA Florescu 4.512,00 lei
USA Ionescu 3.211,00 lei
USA Marin 424,00 lei
USA Marin 545,00 lei
USA Florescu 627,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 (în exemplul nostru, bun specialist în marketing), 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. Pentru a putea urmări practic operaţiunile descrise, copiaţi link-ul din
referinţa de mai jos1 în browser-ul dvs. de Internet, downloadaţi-l şi deschideţi-l în Excel.
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 tab-ul Data, precum Sort, Filter, Subtotals ...) determină implicit utilizarea tuturor datelor din listă pentru comanda care va fi dată în continuare.
Dacă se doreşte realizarea unui raport PivotTable (sau o sortare, o filtrare sau un subtotal etc.) dintr-un set de date din listă, se selectează domeniul care conţine datele.
2. Se apelează tab-ul Insert, din grupul de butoane Tables, se apasă
butonul PivotTable , având ca rezultat afişarea ferestrei Create PivotTable, aşa cum arată în figura 7.1, care pune la dispoziţie opţiuni referitoare la sursa de date ce se doreşte a fi analizată, precum şi locaţia unde va fi plasat raportul de pivotare.
Figura 7.1 Fereastra principală a utilitarului PivotTable
3. O primă setare se referă la specificarea sursei de date ce urmează a fi
analizate, aceasta putând fi o tabelă sau un domeniu de celule (opţiunea Select a table or range), respectiv utilizarea unei surse de date externă (opţiunea Use an external data source).
Pentru prima opţiune, completarea casetei se face fie scriind de la tastatură numele tabelei sau adresa domeniului sau, mai uşor, prin apăsarea
butonului şi selectarea din foaia de calcul a tabelei sau domeniului dorit. Implicit, dacă anterior acestui pas, s-a efectuat poziţionarea în listă, aşa cum am descris în pasul 1, adresa de domeniu sau a listei este trecută automat, utilizatorul putând eventual să verifice corectitudinea adresei.
În cazul în care se doreşte stabilirea unei surse externe, cum e cazul preluării dintr-o bază de date, se selectează opţiunea Use an external data source şi se apasă butonul Choose Connection…, având ca rezultat deschiderea unei ferestre cu conexiuni deja existente (fereastra Existing Connections – figura 7.2 a). Dacă în lista conexiunilor nu se află cea pe care o doriţi, se apasă butonul Browse for more, apărând fereastra Select Data Source – vezi – figura 7.2 b. În această fereastră se poate selecta una din conexiunile existente, dacă vreuna realizează conexiune la sursa de date dorită, în caz contrar, se va trece la crearea unei noi conexiuni ODBC, prin apăsarea butonului New Source… Pasul următor este selectarea tipului de conexiune dorit din fereastra Data Connection Wizard – figura 7.2 c, urmând să se stabilească parametrii de conectare la serverul bazei de date, tip de acces, numele bazei de date etc.
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 5
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ă.
Paşii sunt reflectaţi în figura compusă de mai jos:
7.2.a. Pasul 1 7.2.b. Pasul 2
7.2.c. Pasul 3
Figura 7.2 Paşii parcurşi pentru definirea unei noi conexiuni la o sursă externă de date
4. Cea de-a doua setare vă întreabă unde să se plaseze raportul
PivotTable. 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.
Dacă se alege opţiunea Existing Worksheet, va trebui să alegeţi adresa unde va fi poziţionat tabelul pivot.
După precizarea acestor setări, se apasă butonul Ok, sistemul Excel creând o nouă foaie de calcul în care veţi găsi structura raportului de pivotare, aşa cum este prezentat în figura 7.3.
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 6
Figura 7.3. Structura raportului PivotTable
În cadrul ribonului, se vor activa două noi tab-uri referitoare la lucrul cu
tabele pivot, derivate din PivotTable Tools şi anume Options şi Design, primul tab conţine comenzi de configurare a raportului de pivotare, de grupare a datelor, sortare şi filtrare, refreshing de date, efectuarea de calcule, generarea de grafic, activarea componentelor de configurare (figura următoare):
Cel de-al doilea tab răspunde de partea de aspect al raportului de
pivotare şi anume activarea/dezactivarea liniilor de subtotal sau total, structura raportului de pivotare, afişarea sau ascunderea elementelor de antet linie sau coloană, stiluri de formatare (figura de mai jos):
Odată afişată structura raportului pivot în foaia de calcul, mai puteţi
stabili şi anumite opţiuni asupra tabelului cu ajutorul comenzii Options... din cadrul butonului PivotTable, accesibilă din apăsarea săgeţii de derulare de sub acesta, care va deschide fereastra Pivot Table Options, figura 7.4:
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 7
Figura 7.4 – Stabilirea opţiunilor de realizare a tabelei pivot
o În cadrul secţiunii Layout & Format se poate stabili ordinea de
afişare a câmpurilor prin intermediul Display fields in report filter area, For error values show gestionează erorile prin înlocuirea valorilor greşite cu un caracter sau cu un spaţiu, iar For empty cells show face înlocuirea cu caracterul precizat pentru celulele goale.
o În secţiunea Total & Filters, opţiunile Show grand totals for rows , respectiv Show grand totals for columns permit activarea totalurilor generale pe linii, respectiv pe coloane.
o Opţiunea Repeat row labels on each printed page din secţiunea Printing determină imprimarea pe fiecare pagină a etichetelor de câmpuri.
o Refresh data when opening the file din secţiunea Data actualizează la deschidere tabela pivot pe baza modificărilor efectuate în lista (baza de date) ce a constituit sursa de date
Pentru un studiu analitic al opţiunilor raportului de pivotare, se poate consulta resursa electronică http://office.microsoft.com/en-us/help/pivottable-options-
HA010087022.aspx După stabilirea opţiunilor de realizare şi, eventual, după stabilirea
modului de afişare a tabelei, se trece la ecranul de proiectare al raportului de pivotare structurat în patru zone, în cadrul noii foi de calcul (vezi figura 7.5):
o Zona de linie, Row (Drop Row Fields Here) – câmpul introdus aici va fi amplasat în capul fiecărei linii a tabelei pivot, iar pentru fiecare valoare a câmpului se va genera o linie în tabelă.
o Zona de coloană, Column (Drop Column Fields Here) – câmpul introdus aici va fi amplasat în capul fiecărei coloane a tabelei pivot, iar pentru fiecare valoare a câmpului se va genera o coloană în tabelă.
o Zona de pagină, Page (Drop Report Filter Fields Here) – câmpul care se introduce aici creează un meniu derulant care va permite filtrarea înregistrărilor din tabel.
o Zona de date, Data (Drop Value Fields Here) – asupra câmpului amplasat aici se vor realiza diverse funcţii sub formă de sumă, minim, maxim etc. Funcţia implicită este SUM.
Figura 7.5 Proiectarea structurii tabelei pivot
În fiecare din cele patru zone se pot plasa mai multe câmpuri. Între
câmpurile introduse în aceeaşi zonă va exista o relaţie ierarhică: al doilea câmp este inclus în primul câmp, al treilea câmp este inclus în al doilea câmp etc.
Pentru 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, sau în cele patru casete corespunzătoare aceloraşi zone de mai sus, dar situate în partea dreaptă a ecranului, sub lista de câmpuri.
b. În fereastra Pivot Table Field List selectaţi câmpul pe care doriţi să îl plasaţi în tabel. Cu click dreapta al mouse-ului, puteţi selecta destinaţia plasării câmpului selectat (a se vedea figura alăturată).
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 9
Figura 7.6 Finalizarea raportului PivotTable În figura 7.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. Pentru configurarea ulterioară a funcţiilor aplicate asupra datelor, câmpuri selectate în raport, filtrare, afişarea într-un anumit format, vom discuta în subcapitolul următor.
TEST DE EVALUARE
1. Ce sunt tabelele pivot şi diagramele pivot? Răspuns: Tabelele pivot (rapoartele PivotTable) şi diagramele pivot
(rapoartele PivotChart) reprezintă facilităţi deosebite oferite de Excel pentru sinteza şi analiza datelor. Ele 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.
2. În câte zone este structurat ecranul de proiectare al unei tabele pivot? Ce se întâmplă cu câmpurile plasate în fiecare zonă?
Răspuns:
Întrebări tip grilă Exemplu rezolvat:
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 10
Se apelează tab-ul Insert, butonul PivotTable, în fereastra de opţiuni apărută se cere să se specifice sursa de date (Choose the data that you want to analyze). Ce opţiune alegeți dacă sursa este o bază de date Access?
a) Microsoft Office Excel list or database; b) Use external data source, butonul Choose Connection; c) Multiple consolidation ranges; d) Another PivotTable report or PivotTable chart.
Răspuns: ○●○○ De rezolvat: Am generat un tabel pivot şi trebuie să personalizăm anumite opţiuni.
Deschidem fereastra Pivot Table Options. Care din casetele de validare de mai jos trebuie bifată, pentru ca tabela pivot să se actualizeze de fiecare dată la deschiderea fişierului, pe baza modificărilor efectuate în lista (baza de date) ce a constituit sursa de date?
a) Refresh data when opening the file; b) Save data with table layout; c) Repeat item labels on each printed page; d) Refresh every n minutes.
Răspuns: ○○○○○
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 11
7.2 ACTUALIZAREA TABELELOR PIVOT. DIAGRAME 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ție 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 7.7 sau deschideți lista ascunsă (lista derulantă) Pivot Table din bara de instrumente cu acelaşi nume şi apare meniul din figura 7.8.
Figura 7.7 – Opţiuni pentru modificarea unei tabele pivot (meniu contextual)
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 12
Figura 7.8 – Opţiuni pentru modificarea unei tabele pivot (bara de instrumente Pivot Table)
Pentru a modifica lista sau sursa de date pe care se bazează raportul Pivot Table procedaţi astfel:
o apelaţi, din meniul contextual, 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 7.1.
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 categoriile 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.
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 13
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ă il 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 settings. Se deschide caseta de dialog PivotTable Field, prezentată în figura 7.9.
Figura 7.9 – Modificarea funcţiei implicite de însumare din zona de date Din 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. 2. Efectuaţi click pe butonul Advanced, pentru a afişa caseta de dialog
Pivot Table Fields Advanced Options, figura 7.10. 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
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).
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 14
Figura 7.10 – 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 7.11.
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.
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 15
Figura 7.11 – Setarea subtotalurilor
Comutarea între rapoartele de tip tabelar şi rapoartele de tip schiţă. 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. Tabelul 7.2
Lista vânzărilor pe agenţi de vânzări, ţări şi oraşe
Ţ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 4.534,00 lei
ROM Constantin Arad 1.514,00 lei
USA Florescu Dalas 4.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
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 16
În figura 7.12 el este prezentat sub formă de tabel, iar în figura 7.13 este prezentat sub formă de schiţă.
Figura 7.12 – 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 7.14.
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.
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 7.13 – Un raport PivotTable prezentat sub formă de schiţă
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 17
Figura 7.14 – Comutarea între macheta de tip tabel şi macheta de tip schiţă
Pentru a face ca vederea tabelară din figura 7.12 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.
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 18
7.2.1 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 profesionaist, 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 prezentată în tabelul 7.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 (funcţia de sumarizare este average). Dacă anumiţi agenţi de vânzări nu au vândut în unele ţări, atunci vor apărea mesaje de eroare, figura 7.15.
Figura 7.15 – Mesaje de eroare într-un raport PivotTable Pentru a configura aspectul celulelor necompletate şi al mesajelor de
eroare se procedează astfel: 1. Deschideţi fereastra pentru modificarea unei tabele pivot (vezi
figurile 7.7 şi 7.8 de la paragraful 7.1). 2. Selectaţi caseta de validare For error values, show (pentru valorile
eronate, se va fiş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).
3. 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).
4. 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.
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 19
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 trebuie manuală (reîmprospătarea datelor) din tabel. Pentru aceasta, trebuie apelată comanda Refrech 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 7.4) se bifează căsuţa de validare Refrech 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 Refrech 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.
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 formatepre stabilite. 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
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 20
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.
7.2.2 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 7.7 şi 7.8 de la paragraful 2.3, se alege opţiunea PivotChart. Drept rezultat, pe baza tabelului pivot existent se creează, î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 7.1), la întrebarea What kind of report do yu want to create? se alege a doua opţiune, PivotChart report (with PivotTable report). Atenţie: şi în acest caz se crează un tabel pivot; o diagramă pivot nu poate există fără un tabel pivot.
b. După parcurgerea celui de al treilea pas din programul de tip wizard, macheta de proiectare este cea prezentată în figura 7.14. 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 altă 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.
Analiza datelor în Microsoft Excel 2010 Tema 7
Suport de curs, anul I Pag. 21
Figura 7.16 – Macheta de proiectare a unei diagrame PivotChart
TEST DE EVALUARE
1. Cum se procedează pentru schimbarea automată a ordinii de sortare într-un tabelele pivot?
Răspuns: 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. 2. Efectuaţi click pe butonul Advanced, pentru a afişa caseta de
dialog Pivot Table Fields Advanced Options. 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 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).
.
2. Prezentaţi paşii ce trebuie parcurşi pentru a comuta un tabel pivot între formatele de tip tabel şi schiţă.
Răspuns:
1. 2. 3. 4.
Analiza datelor în Microsoft Excel 2010 Tema 1
Pag. 22
Întrebări tip grilă Exemplu rezolvat:
Care este prima etapă pe care o parcurgeţi pentru a modifica lista sau sursa de date pe care se bazează raportul Pivot Table?
a) apelaţi, din meniul contextual, opţiunea Table Options; b) alegeţi opţiunea Field Setings din meniul contextual; c) apelaţi, din meniul contextual, opţiunea Field Settings; d) apelaţi, din meniul contextual, opţiunea Pivot Table Wizard.
Răspuns: ○○○● De rezolvat:
Care este prima etapă pe care o parcurgeţi pentru a schimba funcţia implicită de centralizare?
a) apelaţi, din meniul contextual, opţiunea Table Options; b) alegeţi opţiunea Field Setings din meniul contextual; c) apelaţi, din meniul contextual, opţiunea Field Settings; d) apelaţi, din meniul contextual, opţiunea Pivot Table Wizard.
