PRELUCRAREA DATELOR CU AJUTORUL SUBTOTALURILOR

Crearea subtotalurilor automate

Cand sorteaza datele dintr-o lista Excel permite efectuarea unor sinteze a datelor pe baza totalurilor partiale. Cand se efectueaza calcule partiale, acestea sunt calculate pentru fiecare grup din lista. Un grup este alcatuit din mai multe linii (inregistrari) care au aceeasi informatie intr-o coloana (camp) specificata.

Pentru a putea crea subtotaluri trebuie ca datele sa fie sortate. Dupa sortarea datelor dupa campuri, se parcurg urmatorii pasi:

Se selecteaza orice celula din lista.

Se aplica comanda Data, Subtotals. Pe ecran apare caseta de dialog Subtotal (figura 9 ).

Figura 9 - caseta de dialog Subtotal

Se specifica modul in care vor fi grupate datele pentru subtotaluri selectand din lista derulanta At Each Change(la fiecare schimbare) numele coloanei dupa care se face gruparea.

Daca este prima data cand este selectata comanda, Excel selecteaza automat coloana cea mai din stanga. Daca comanda a fost folosita si mai inainte, atunci va fi selectata coloana folosita ultima data.

Din lista derulanta Use Function se selecteaza functia care trebuie calculata. Functiile cel mai frecvent folosite sunt:

SUM - adunare

MAX - maximum

MIN - minimum

AVERAGE - medie

PRODUCT - produs

VAR - varianta

STD DEVP - deviatia standard;

Din lista Add Subtotal To se selecteaza datele cu care se vor efectua calculele. Aceasta lista contine numele coloanelor din lista de date. Se marcheaza coloanele pentru care se vor efectua calculele.

Pentru a inlocui totaluri existente se marcheaza optiunea Replace Current Subtotals.

Pentru a insera un salt de pagina inaintea fiecarui grup se marcheaza optiunea Page Break Between Groups.

In mod implicit subtotalurile si totalurile generale apar la sfarsitul grupului de date (optiunea Summary Below Data se marcheaza automat). Daca ele trebuie afisate inaintea grupului de date se va demarca optiunea Summary Below Data.

Se selecteaza butonul OK.

Pentru eliminarea subtotalurilor din caseta de dialog Subtotal se selecteaza butonul Remove All.

Aplicatie

La datele din aplicatia din lectia 8 sa se calculeze valoarea totala a comenzilor livrate in fiecare oras.

Se sorteaza comenzile dupa oras.

Se selecteaza o celula din lista.

Din meniul Data se aplica comanda Subtotals.

Caseta de dialog Subtotal se va completa in modul urmator (figura 9..2):

Din lista At Each Change se selcteaza Oras. Se va genera cate un grup de inregistari pentru fiecare oras. Din lista Use Function se va selecta functia SUM, iar in lista Add Subtotal To se va marca coloana Valoare comanda. Deci pentru fiecare grup se va calcula suma valorilor din coloana Valoare comanda.

Se marcheaza optiunile Replace Current Subtotals si Summary Below Data.

Se selecteaza butonul OK.

Figura 9

Excel insereaza randurile de subtotal pentru fiecare grup si realizeaza calculul specificat in coloanele alese. Excel eticheteaza fiecare rand inserat cu un titlu potrivit. Se insereaza de asemenea un rand de total general.

Cand se adauga totaluri partiale calculate automat intr-o lista Excel afiseaza lista in modul Outline. Nivelul detaliilor din lista poate fi extins sau restrans pentru a nu fi afisate decat totalurile partiale si totalurile generale pentru datele respective. In partea stanga a ferestrei (figura 9 ) apar cateva butoane care permit ascunderea si afisarea rapida a datelor de detaliu:

(-) Hide Detail - pentru a condensa sintetizarea datelor - apare in dreptul fiecarui subtotal calculat

(S) Display Detail - pentru a extinde sintetizarea datelor - apare in dreptul fiecarui subtotal calculat

1, 2, - butoane de ierarhizare care indica modul cum sunt grupate datele.

In exemplul prezentat, deoarece s-a efectuat o grupare pe un nivel, sunt afisate doar trei butoane: 1, 2 si 3. Daca se apasa pe butonul 1 este afisat doar totalul general (figura 9.4), daca se apasa pe butoanul 2 sunt afisate totalurile partiale, iar daca se apasa butonul 3 este vizualizata toata lista.

Pentru a ascunde un nivel de detaliere se selecteaza o celula care contine un total partial si se aplica un clic pe butonul Hide Detail Level (-) de pe nivelul 2. Excel va reduce dimensiunile listei astfel incat este afisat doar randul care contine totalul partial corespunzator. Daca se aplica un clic pe butonul Display Detail (+) vor fi afisate toate inregistrarile pe baza carora s-a calculat subtotalul corespunzator.

Butoanele Hide Detail (-) si Display Detail (+) de pe nivelul 1 sunt folosite pentru a condensa sau extinde datele care genereaza totalul general.

Figura 9..3

Figura 9..4

Crearea de subtotaluri ierarhizate

Daca in cadrul unui grup trebuie calculate subtotaluri suplimentare se vor calcula subtotaluri ierarhizate.

Pentru a crea un subtotal ierarhizat datele trebuie sortate si dupa a doua cheie. Apoi se aplica comanda Data, Subtotals. Se vor selecta optiunile pentru grupul principal (cel mai mare). Excel insereaza subtotalurile pentru primul grup.

Se alege in continuare Data, Subtotals si optiunile pentru urmatoarele subseturi de grupe. Optiunea Replace Current Subtotals trebuie sa fie demarcata. Excel insereaza cate un subtotal pentru urmatoarele subseturi de grupuri.

Aplicatie

Pe langa subtotalurile pe orase si totalul general se mai pot calcula si subtotaluri pe produse. Pentru a realiza acest lucru:

Se face o sortare a listei pe doua niveluri: nivelul 1 - sortare dupa oras; nivelul 2 - sortare dupa produs.

Ca in aplicatia precedenta se calculeaza subtotalurile pe oras.

Figura 9

Se aplica din nou comanda Data, Subtotals. Caseta de dialog Subtotal se completeaza in modul urmator:

Din lista At Each Change In se selecteaza campul Produs (se vor genera subgrupe pentru fiecare produs).

Din lista Use Function se selecteaza functia SUM, iar in lista Add Subtotal To se marcheaza campul Valoare comanda (se calculeaza suma valorilor comenzilor pentru fiecare subgrup).

Se demarcheaza optiunea Replace Current Subtotals.

Se selecteaza butonul OK.

Se obtine rezultatul din figura 9.5.

In coltul din stanga sus a ferestrei apar butoanele de ierarhizare 1, 2, 3, 4. Acum apar mai multe butoane de ierarhizare pentru ca se calculeaza subtotalurile pentru mai multe nivele (produs si oras). Pentru a afisa doar totalul general se aplica un clic pe butonul de nivel 1. Daca trebuie afisate doar totalurile vanzarilor efectuate in fiecare oras si totalul general se aplica un clic pe butonul de nivel 2. Daca trebuie afisate subtotalurile pe produs si oras se aplica un clic pe butonul 3, iar daca trebuie afisata toata lista se aplica un clic pe butonul 4.