Subtotaluri, tabele pivot

Enuntul temei

În cadrul unui document Excel ce contine doua foi de calcul identice se vor efectua urmatoarele operatii:

se va crea un subtotal într-una din cele doua foi;

se va genera un tabel pivot plecând de la datele din foaia de calcul nefolosita pentru subtotaluri 222k1010c .

Datele de la care se pleaca reprezinta o situatie pe durata a doua trimestre a salariului, primelor si unor eventuale retineri, pentru un numar de cinci persoane.

Pentru usurinta operarii, tabelul de date (zona C4:H44) a fost denumita Date_de_intrare - vezi figura 1.

Figura 1

Dupa cum s-a mai precizat, documentul date_subtot_pivot.xls are initial doua foi de calcul, una numita Date si alta numita Subtotaluri. Prima foaie va fi folosita ca sursa pentru crearea tabelului pivot (care se va gasi pe o alta foaie, creata în mod automat la generarea tabelului pivot), iar a doua [foaie] va fi folosita la obtinerea de subtotaluri (care vor aparea pe aceeasi foaie cu datele initiale).

Subtotalurile vor reprezenta sumele salariilor, primelor si retinerilor pentru cele cinci persoane. Se va obtine un rezultat de tipul celui din figura 2 (aici s-a prezentat numai situatia pentru doua persoane).

În ceea ce priveste tabelul pivot, acesta este un tabel centralizator care realizeaza sintetizarea sau rezumarea unor date initiale.

În figura 4 se poate vedea o varianta de tabel pivot care s-ar putea obtine pornind de la datele continute în foaia de calcul Date.

Dupa cum se poate observa în figura 4, tabelul pivot permite structurarea datelor astfel încât sa fie posibila analizarea lor mult mai usoara. În exemplul considerat, pentru fiecare persoana sunt realizate totaluri sau numarari la nivel de trimestru, dar si la nivel global. Se fac sume atât pe orizontala (la nivel de persoana), cât si pe verticala (la nivel de luna, dar si trimestru).

Tabelele pivot sunt deci mai "puternice" decât subtotalurile.

Se va observa ca si în cazul tabelelor pivot, operatiile ce se executa asupra datelor initiale pot fi si altceva decât simple sumari.

Figura 2

Figura 3

Mod de lucru

1. Pentru realizarea de subtotaluri, se vor parcurge urmatoarele etape:

Se selecteaza zona de tabel pentru care se vor realiza subtotaluri. Daca, asa cum s-a aratat în figura 1, zona de date a primit un nume, selectia se face foarte simplu prin executarea unui clic stânga pe numele zonei asa cum apare el în lista derulabila din figura 1;

Figura 4

Se foloseste câmpul Date Subtotaluri si se ajunge astfel la caseta de dialog prezentata în figura 5; Subtotalurile se vor insera dupa fiecare modificare a continutului celulelor de pe coloana precizata în La fiecare modificare în. Pentru ca s-a vorbit despre subtotaluri, se va folosi functia Suma (Utilizare functie). Fireste, se pot folosi si alte functii (vezi figura 5), caz în care termenul de subtotal nu mai este adecvat. În zona Adaugă subtotal la, se specifica coloanele pe care se vor face subtotaluri. La apasarea butonului OK, datele initiale vor fi înlocuite de tabelul ce contine subtotalurile.

Daca se doreste condensarea datelor, se face clic stânga pe oricare din butoanele ce contin un semn minus (-), evidentiate prin încadrarea cu elipse. Se va ajunge la o situatie de tipul celei din figura 3.

Expandarea pentru orice nume se poate face prin apasarea pe butonul ce contine un plus (+) corespunzator.

 

Figura 5

2. Pentru generarea unui tabel pivot, se va proceda astfel:

Se selecteaza datele de pornire (ca si în cazul subtotalurilor). Se face precizarea ca în cazul tabelelor pivot selectia zonei de date se poate face si în timpul generarii tabelului;

Se foloseste câmpul Date Raport Pivot Table si Pivot Chart. Se va intra astfel în asa-numitul expert pentru generarea tabelelor pivot. Procesul se desfasoara pe întinderea a 3 pasi:

o        În primul pas - vezi figura 6 - se stabileste sursa de date pe baza careia se va crea tabelul: poate fi o lista Excel, o sursa externa de date, sau chiar un alt tabel pivot. Tot acum se stabileste daca se genereaza un tabel pivot, sau un grafic de tip pivot;

Figura 6

o        În al doilea pas - vezi figura 7 -  se stabileste sau se modifica zona de date. Apasarea butonului Rasfoire, permite chiar accesarea unui alt document Excel decât cel în care se lucreaza;

Figura 7

o        În al treilea pas - vezi figura 8 - se stabileste daca tabelul pivot se creeaza în foaia de calcul curenta sau într-una noua. Daca se alege cea de a doua varianta, se poate preciza prin completarea câmpului de adrese (sau interactiv la apasarea butonului ) care va fi zona din foaia de calcul unde se va dezvolta tabelul pivot. Tot acum, prin apasarea butonului Aspect, se stabileste structura tabelului pivot.

Figura 8

Acest al treilea pas este o etapa deosebit de importanta, pentru ca  stabileste formatul pe care-l vor lua datele în tabel. Se lucreaza cu caseta de dialog prezentata în figura 9. Aici, folosind tehnica drag & drop, se muta etichetele câmpurilor ce apar în partea dreapta a casetei în cele trei zone ale spatiului de definire a structurii tabelului. Pentru o mai buna întelegere se recomanda coroborarea figurii 9 cu figura 4.

 

Figura 9

La mutarea numelor câmpurilor în zona de lucru, acestea sunt prefixate de cuvintele Suma sau Contor. Aceste prefixe indica de fapt functia ce se va aplica datelor. Modificarea functiei sau a numelui ce va aparea în tabelul pivot se poate face executând un dublu clic pe numele butonului din zona de lucru. Se va ajunge astfel la caseta de dialog din figura 10.

Figura 10

Apasarea butonului Număr permite controlul formatului numeric al datelor din tabelul pivot. Apasarea butonului Optiuni, va conduce la extinderea casetei de dialog cu o zona plasata în partea ei inferioara - vezi figura 11. De aici se poate alege daca datele din tabel vor fi valori absolute (Normal), sau procente, având diverse termene de comparatie.

 

Figura 11

Daca s-ar fi ales de exemplu pentru câmpul Suma de Salarii, %din, trebuie precizat fata de cine se exprima procentual valorile - vezi figura 11. Rezultatul obtinut este prezentat în figura 12.

Dupa apasarea butonului OK al casetei de dialog Câmp Pivot Table, se revine în pasul 3 al expertului (figura 8). Aici se poate apasa butonul Terminare, ceea ce ar determina generarea tabelului pivot, sau se poate apasa butonul Optiuni - vezi figura 13 - ceea ce va permite realizarea unor setari legate de aspectul general al tabelului pivot.

Figura 12

Se vor comenta aici numai câmpurile Totaluri generale pentru coloane si Totaluri generale pentru rânduri. Cu ajutorul lor se poate activa sau inhiba prezenta totalurilor generale pe rânduri sau pe coloane.

Figura 13