Subtotaluri în Microsoft Excel

În acest tutorial voi prezenta subtotalurile în Microsoft Excel.

Gruparea (schițarea) datelor într-un registru de lucru

Foile de lucru care au multe date pot deveni greu de citit. Din fericire, Excel poate organiza datele în grupuri, permițându-vă să afișați și să ascundeți cu ușurință diferite secțiuni ale foii de lucru.

Dacă aveţi o listă de date pe care doriţi să le grupaţi şi să le rezumaţi, aveţi posibilitatea să creaţi o schiţă (outline) cu până la opt nivele ierarhice, cu un nivel pentru fiecare grup.

Fiecare nivel interior, reprezentat de un număr mai mare în simbolurile ierarhice (simboluri care se utilizează pentru a modifica aspectul unei foi de lucru) afişează date detaliate pentru nivelul exterior precedent, reprezentat cu un număr mai mic în simbolurile din schiță.

Se pot afișa sau ascunde datele de detaliu apăsând butoanele de structură (semnul plus, semnul minus) precum şi numerele 1, 2, 3, …, care indică nivelul de detaliere.

Utilizaţi o schiţă pentru a afişa rapid rezumate de rânduri sau coloane ori pentru a afişa datele de detalii pentru fiecare grup.

Aveţi posibilitatea să creaţi o schiţă de rânduri (ca mai jos), o schiţă de coloane sau o schiţă de rânduri şi coloane.

Comenzile de grupare pe nivele ierarhice sunt disponibile din fila Data – grupul Outline.

Pentru gruparea manuală a liniilor/coloanelor: se selectează liniile/coloanele pe care le doriţi grupate și din fila Data – grupul Outline – apăsaţi butonul Group.

Din caseta de dialog Group selectaţi rânduri sau coloane în funcţie de ce anume doriţi să grupaţi; (dacă selectaţi un rând sau o coloană întreagă, nu va apare această casetă de dialog).

Pentru degruparea liniilor/coloanelor: se selectează liniile/coloanele grupate (pe care le doriţi degrupate) și din fila Data – grupul Outline – apăsaţi butonul Ungroup.

Pentru gruparea automată a liniilor/coloanelor, în foaia de lucru este necesară existența
totalurilor/ subtotalurilor.

Având selectată orice celulă din tabel, din fila Data – grupul Outline – Group – apăsaţi
butonul Auto Outline.

Pentru schimbarea poziției rândurilor rezumative și pentru aplicarea stilurilor de structură predefinite în Excel (RowLevel_1 (bold) and RowLevel_2 (italic)), din fila Data – grupul Outline, se face click pe lansatorul ferestrei de dialog și din fereastra Settings se bifează Summary rows below detail (liniile de rezumat să fie plasate sub detalii), Summary columns to right of detail (coloanele de rezumat în dreapta detaliilor), Automatic Styles (Stiluri automate).

Observație: Pentru a aplica stiluri unei grupări existente, selectați caseta Automatic Styles, dar faceți click pe butonul Apply Styles în loc de OK (pe care faceți click când aplicați stiluri unei grupări noi).

Subtotaluri

Excel poate foarte uşor să genereze subtotaluri dintr-o listă numerică de date.

Pentru a genera subtotaluri:

• Se sortează înregistrările după criteriul (coloana) care ne interesează (în exemplul nostru, coloana Luna)

• Se selectează orice celulă din tabel
• Din fila Data – grupul Outline – apăsaţi butonul Subtotal. Apare fereastra Subtotal.

• At Each Change in: se specifică pentru ce se doreşte să se facă subtotaluri. Acesta este motivul pentru care este necesară sortarea.
• Use Function: Se poate stabili ce funcţie să se utilizeze.
  După ce se fac subtotalurile se poate utiliza AutoFormat pentru a modifica înfăţişarea listei finale.
• Opţiunea Add Subtotal to: permite selectarea câmpurilor numerice pentru care se fac subtotaluri.
  • Dacă se selectează opţiunea Replace Current Subtotals, se vor înlocui subtotalurile curente. Dacă se introduce al doilea subtotal, se va debifa opţiunea Replace current subtotals (pentru a nu suprascrie subtotalurile existente) astfel încât subtotalul curent să fie adăugat subtotalului calculat anterior.
  • Se selectează opţiunea Page Break Between Groups, dacă se doreşte ca fiecare grup să fie afişat la imprimantă pe o pagină nouă.
  • Dacă se selectează Summary Below Data (opţiune implicită) liniile de totaluri apar sub liniile de detaliu (altfel, aceste rezultate sunt afişate deasupra datelor centralizate).

Subtotalurile nu se recalculează când se filtreză o listă. De aceea se recomandă să se facă filtrarea înaintea subtotalurilor.

Dacă după realizarea subtotalurilor, se dorește înlăturarea acestora (pentru reorganizarea datelor în alt fel, de exemplu), se selectează întreg tabelul, se apasă butonul Subtotal iar din fereastra Subtotal se apasă butonul Remove All. Această acțiune are ca efect înlăturarea tuturor grupurilor și subtotalurilor.

Dacă doriți doar eliminarea grupurilor fără a șterge subtotalurile, faceți click pe săgeata derulantă de lângă Ungroup, apoi alegeți Clear Outline.

Simbolurile de evidenţiere (outline) a subtotalurilor

După generarea subtotalurilor, Excel afişează o serie de simboluri sau butoane în partea stângă a foii de calcul. Aceste butoane se pot folosi pentru a ascunde sau a afişa diferitele nivele de detaliu în foaia de calcul.

Butoanele de nivel indică diferite valori de detaliu:

• Nivelul 1– afişează numai totalul general (grand total)
• Nivelul 2 – afişează subtotalurile
• Nivelul 3 – afişează întraga listă

La nivelul 2 ecranul poate arăta astfel:

Butonul de Expandare indică existenţa unor detalii şi apăsându-l ramura corespunzătoare se va detalia (expanda).

Butonul de Collapse poate fi utilizat pentru a ascunde liniile marcate din dreptul liniei corespunzătoare. În exemplul de mai sus va rămâne numai totalul general.

Sintetizarea foii de calcul

Sintetizarea vă oferă posibilitatea să extindeţi sau să micşoraţi foile de calcul sau rapoartele, astfel încât să le vedeţi mai mult sau mai puţin detaliat. În funcţie de persoana căreia îi este adresat raportul, este posibil să aveţi nevoie doar de 2 niveluri de detaliu sau de mai multe.

Figura de mai jos arată un raport cu vânzări de produse pe tip de produs şi pe luni.

Acestui raport i s-a aplicat o sintetizare automată. Raportul necesită un efort de derulare pentru a vedea cumulul pe rânduri pentru fiecare regiune sau cumulul pe coloane pentru fiecare trimestru.

Sintetizarea automată poate fi făcută dacă formulele de cumulare sunt consecvente în direcţia către care indică.

Toate formulele de cumulare din rânduri ar trebui să cumuleze celulele de deasupra
şi toate totalurile din coloane ar trebui să cumuleze celulele din stânga.

Sintetizarea automată funcţionează pe cumulările care se referă la celulele de dedesubt sau de la dreapta dacă toate cumulurile sunt consecvente în direcţia către care indică.

Puteţi schimba configurarea automată sau puteţi grupa manual rândurile sau coloanele.

Pentru a aplica pe aceeaşi foaie de calcul un subtotal şi o grupare Auto Outline ordinea esteurmătoarea: întâi aplicaţi subtotalurile şi apoi alegeţi din fila Data– grupul Outline – Group – butonul Auto Outline.

Pentru eliminarea subtotalurilor şi a grupării coloanelor, ordinea este aceeaşi (întâi subtotalurile şi apoi, dacă este cazul opţiunea Clear Outline din fila Data – grupul Outline – lista Ungroup.