În acest tutorial voi prezenta lucrul cu tabele Pivot în Microsoft Excel.
Tabelele pivot reprezintă o facilitate puternică a programului Excel de însumare și analiză a datelor în maniere diferite. Acestea permit organizarea datelor stocate în mai multe foi de lucru sau registre de lucru și au o mare calitate: interactivitatea. După ce creați un tabel pivot, puteți rearanja informațiile în aproape orice mod imaginabil și chiar insera formule speciale care efectuează noi calcule.
Avantajul tabelelor pivot este că ele permit diferitelor câmpuri şi categorii să fie aranjate şi manipulate uşor prin pivotare.
În exemplul următor datele pot fi însumate pe sucursale.
Crearea unui raport Pivot Table
Selectaţi o singură celulă a sursei de date şi aplicaţi una din următoarele metode:
- Click pe fila Insert – grupul Tables – Pivot Table.
- Dacă sursa de date e formatată ca tabel, în Table Tools fila Design – grupul Tools – selectaţi Summarize With Pivot Table.
După ce aţi aplicat oricare din cele două metode apare fereastra Create Pivot Table.
În mod implicit tabelul pivot va fi plasat într-un nou worksheet dar dacă doriţi să-l plasaţi într-o locaţie anume alegeţi locaţia după ce bifaţi opţiunea Existing Worksheet.
Excel generează un format de tabel necompletat în zona din stânga foii de calcul. Se deschide automat în dreapta fereastra PivotTable Fields.
Când creaţi un raport PivotTable sau PivotChart, utilizaţi lista de câmpuri afișate în partea superioară a ferestrei PivotTable Fields pentru a adăuga câmpuri.
Dacă doriţi să modificaţi un raport PivotTable sau PivotChart, utilizaţi opțiunile din ferestra PivotTable Fields pentru a rearanja şi elimina câmpuri.
În mod implicit, fereastra PivotTable Fields afişează două secţiuni: o secţiune în partea de sus pentru adăugarea şi eliminarea câmpurilor şi o secţiune de aspect în partea de jos pentru rearanjarea şi repoziţionarea lor.
Aveţi posibilitatea să deblocaţi fereastra PivotTable Fields (trăgând de bara de titlu pentru a o muta în orice loc), caz în care se poate redimensiona atât vertical, cât şi orizontal. De asemenea, dacă faceți click pe o celulă din afara tabelului pivot, panoul de activități este temporar ascuns.
Dacă nu îl vedeţi asiguraţi-vă că faceţi click pe raportul PivotTable sau PivotChart.
Dacă tot nu vedeţi fereastra PivotTable Fields, pentru un raport PivotTable, din PivotTable Tools – fila Analyze – grupul Show – selectați Field List, iar pentru un PivotChart, în fila Analyze, în grupul Show/Hide, faceţi click pe Field List.
Pentru stabilirea poziţiei câmpurilor în tabelul pivot trageţi numele câmpului în zona Filters, Columns, Rows sau Σ Values în funcţie de modul în care doriţi ca datele să fie prelucrate în raport. În zona Σ Values se introduc de obicei date cărora li se poate aplica o operaţie aritmetică (date numerice), situaţie în care Excel introduce implicit suma valorilor (subtotaluri) pentru fiecare schimbare în fiecare câmp din zona de tip linie – Row.
Pentru obținerea tabelului pivot din exemplul nostru, fereastra PivotTable Fields a fost completată așa:
În momentul în care dați click pe tabelul pivot se activează două file: Analyze şi Design.
În fila Analyze aveţi următoarele grupuri:
- Pivot Table: aici puteţi denumi tabelul în caseta Pivot Table Name sau puteţi stabili proprietăţile generale ale tabelului pivot, proprietăţi pe care le puteţi activa din Options – Options – fereastra PivotTable Options.
- Active Field în care puteţi denumi câmpul activ.
În cazul în care câmpul activ este Values (în care funcţia implicită pentru câmpuri numerice este SUM iar pentru celelalte COUNT) puteţi modifica metoda de calcul prin selectarea opţiunii Field Settings. Apare următoarea casetă de dialog:
În fila Summarize Values By puteţi alege o altă funcţie după care se va face calculul, iar în fila Show Values As puteţi personaliza metoda de calcul pentru a calcula procente din totalul general, din totalul pe rând sau coloană, pentru determinarea diferenţei faţă de un item, etc.
Pentru a realiza un calcul corect funcţia de bază trebuie să fie SUM şi o selectaţi iniţial în fila Summarize Values By.
Dacă niciuna din opţiunile din lista propusă nu satisface cerinţele de analiză pe care doriţi să le aplicaţi asupra datelor, Excel oferă posibilitatea adăugării unor câmpuri calculate.
Pentru aceasta, selectaţi o celulă oarecare din tabelul pivot pentru a activa fila Analyze.
În grupul Calculations – click pe Fields, Items & Sets – click pe butonul Calculated Field.
Apare următoarea fereastră:
În caseta Name completaţi numele câmpului calculat iar în caseta Formula scrieţi formula de calcul pe baza căreia se vor completa datele din acest câmp.
Pentru exemplificare, considerăm tabelul de mai jos:
Considerăm că avem realizat un tabel pivot pe baza tabelului de mai sus și vrem să introducem un nou câmp calculat “Valoare” obţinut prin înmulţirea câmpului Cantitate cu câmpul Pret unitar.
Pentru aceasta, în caseta Formula introducem numele câmpului Cantitate selectându-l din lista Fields şi apăsând butonul Insert Field. Scriem apoi de la tastatură operatorul ”*” şi introducem apoi numele câmpului Pret unitar prin aceeaşi metodă.
În tabelul pivot va apărea noul câmp calculat, Sum of Valoare:
Pentru a afişa lista câmpurilor calculate împreună cu formula utilizată din PivotTable Tools – fila Analyze – grupul Calculations – click pe Fields, Items & Sets – click pe butonul List Formulas.
Excel va afişa lista într-un nou worksheet astfel:
Group. Tabelul pivot grupează valorile câmpurilor în funcţie de ordinea în care acestea au fost introduse în zona de câmpuri. Dacă doriţi să realizaţi o grupare personalizată selectaţi itemii pe care doriţi să-i includeţi într-un grup şi apoi din fila Analyze – grupul Group – click pe Group Selection.
Anularea grupării pentru datele grupate se realizează prin click dreapta pe orice element din grup și acţionarea butonului Ungroup.
În cazul în care doriţi o grupare semiautomată a datelor (spre exemplu grupare pe trimestre a datelor calendaristice) din fila Analyze – grupul Group – click pe Group Field care permite gruparea unui număr exact de elemente, gruparea datelor calendaristice pe anumite intervale, etc., în funcţie de tipul de date conţinute de câmpul pe care se realizează gruparea.
Observație: Începând cu Excel 2016, cu gruparea după timp, relațiile dintre câmpurile temporale sunt detectate și grupate automat atunci când adăugați rânduri ce conțin câmpuri temporale la rapoartele PivotTable. După grupare, puteți să glisați grupul în raportul Pivot Table și să începeți analiza.
De exemplu, pentru tabelul de mai jos realizăm un tabel pivot cu produsele comandate și datele calendaristice când au fost comandate și dorim gruparea acestor date pe trimestre.
Completând în fereastra Grouping ca mai sus, obținem:
Grupurile create pot fi restrânse/ extinse prin acționarea butoanelor Collapse Field/ Expand Field.
Prin dublu click pe orice valoare de sumarizare din tabelul pivot, Excel afişează într-un nou registru de lucru datele din tabelul iniţial pe baza cărora s-a realizat subtotalul respectiv.
În grupul Data aveţi opţiunile de actualizare a datelor din tabelul pivot când datele din tabelul sursă sunt modificate sau de a modifica proprietăţile conexiunii în situaţia în care tabelul pivot a fost generat pe baza datelor din surse externe.
Puteţi, de asemenea, renunţa la afişarea antetului de câmp sau la butoanele de structură ierarhică (+ sau -).
Din PivotTable Tools – fila Design aveţi la dispoziţie câteva opţiuni de afişare a datelor din tabelul pivot din punctul de vedere al aspectului raportului în vederea tipăririi.