Când foaia de lucru Excel conține rânduri ascunse, date filtrate sau date grupate, utilizați funcția Excel SUBTOTAL. Funcția SUBTOTAL poate include sau exclude valori ascunse în calcule. Pe lângă găsirea totalului unui grup de date, Excel poate calcula media, maxima, minimă, abaterea standard și varianța datelor dvs. Iată cum să inserați subtotaluri în Excel.
Instrucțiunile din acest articol se aplică Excel pentru Microsoft 365, Excel 2019 și Excel 2016.
Sintaxa funcției SUBTOTAL
Utilizați funcția SUBTOTAL în Excel pentru a rezuma valorile dintr-o foaie de lucru în moduri diferite. Este deosebit de util atunci când foaia de lucru conține rânduri ascunse pe care doriți să le includeți în calcul.
Sintaxa funcției SUBTOTAL este: SUBTOTAL(function_num, ref1, ref2, …)
Argumentul function_num este necesar și specifică tipul de operație matematică de utilizat pentru subtotal. Funcția SUBTOTAL poate adăuga numere, poate calcula valoarea medie a numerelor selectate, poate găsi valorile maxime și minime dintr-un interval, numără numărul de valori dintr-un interval selectat și multe altele.
Funcția SUBTOTAL ignoră celulele care nu conțin date și celulele cu valori nenumerice.
Acest argument este un număr și depinde dacă doriți să includeți rânduri ascunse în rezultat sau să excludeți rândurile ascunse din rezultat. Aceste rânduri pot fi ascunse manual sau ascunse de un filtru.
Argumentele function_num includ:
| Sarcina funcției | function_num | function_num |
|---|---|---|
| (include valori ascunse) | (exclude valorile ascunse) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUS | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Argumentele de referință function_num de la 1 la 11 includ numai valori în rândurile ascunse atunci când utilizați comanda Ascundere pentru a ascunde rândurile. Când utilizați comanda Filtrare, calculele SUBTOTAL nu includ rezultate de filtrare ascunse.
Argumentul ref1 este necesar. Acestea sunt celulele utilizate pentru a calcula rezultatele argumentului function_num selectat. Acest argument poate fi o valoare, o singură celulă sau un interval de celule.
Argumentele ref2, … sunt opționale. Acestea sunt celule suplimentare care sunt incluse în calcul.
Utilizați funcția SUBTOTAL cu rânduri ascunse
Funcțiile Excel pot fi introduse manual sau cu ajutorul casetei de dialog Argumente ale funcției. Pentru a ilustra modul de introducere manuală a funcției folosind bara de formule, următorul exemplu utilizează argumentul COUNT function_num pentru a număra numărul de valori din rândurile vizibile și din rândurile vizibile și ascunse.
Pentru a utiliza funcția SUBTOTAL pentru a număra numărul de rânduri dintr-o foaie de lucru:
- Începeți cu o foaie de lucru care conține mai multe rânduri de date.
-
Selectați celula care va conține numărul rândurilor vizibile.
-
În bara de funcții, introduceți =SUBTOTAL. Pe măsură ce tastați, Excel vă sugerează o funcție. Faceți dublu clic pe funcția SUBTOTAL.
Pentru a utiliza caseta de dialog Argumente ale funcției pentru a intra în funcția SUBTOTAL, accesați Formule și selectați Math & Trig >SUBTOTAL.
Image -
În meniul drop-down care apare, faceți dublu clic pe 102 – COUNT argumentul function_num.
Image -
Tastați o virgulă (,).
Image -
În foaia de lucru, selectați celulele de inclus în formulă.
Image -
Apăsați Enter pentru a vedea rezultatul în celula pe care ați selectat-o la pasul 2.
Image - Selectați celula care va conține numărul rândurilor vizibile și ascunse.
- În bara de funcții, introduceți =SUBTOTAL. Pe măsură ce tastați, Excel vă sugerează o funcție. Faceți dublu clic pe funcția SUBTOTAL.
- În meniul drop-down care apare, faceți dublu clic pe 2 – COUNT argumentul function_num, apoi introduceți o virgulă (,).
-
În foaia de lucru, selectați celulele de inclus în formulă, apoi apăsați Enter.
Image -
Ascunde mai multe rânduri de date. În acest exemplu, rândurile cu vânzări sub 100.000 USD au fost ascunse.
Image
Utilizați funcția SUBTOTAL cu date filtrate
Folosirea funcției SUBTOTAL pentru datele filtrate ignoră datele din rândurile care au fost eliminate de filtru. De fiecare dată când criteriile de filtrare se schimbă, funcția se recalculează pentru a afișa subtotalul pentru rândurile vizibile.
Pentru a utiliza funcția SUBTOTAL pentru a vedea diferențele dintre rezultatele calculelor la filtrarea datelor:
-
Creează formule SUBTOTAL. De exemplu, creați formule pentru a determina subtotalul și valorile medii ale datelor filtrate.
Nu contează dacă utilizați argumentul function_num pentru rândurile vizibile sau ascunse. Ambele argumente oferă același rezultat în datele filtrate.
Image - Selectați orice celulă din setul de date.
-
Accesați Acasă, apoi selectați Sortare și filtrare > Filtru.
Image -
Utilizați săgețile drop-down pentru a filtra datele din foaia de lucru.
Image -
Observați cum se schimbă valorile de fiecare dată când selectați diferite criterii de filtrare.
Image
Utilizați funcția SUBTOTAL cu date grupate
Când datele sunt grupate, există o modalitate de a aplica funcția SUBTOTAL fiecărui grup individual și apoi de a calcula totalul general pentru întregul set de date.
- Selectați orice celulă din setul de date.
-
Selectați Date > Subtotal pentru a deschide caseta de dialog Subtotal.
Image - Selectați La fiecare modificare asăgeata drop-down și alegeți gruparea la care va fi calculat fiecare subtotal.
- Selectați Folosiți funcția săgeata drop-down și alegeți o funcție_num.
- În lista Adăugați subtotal la, selectați coloana în care va fi aplicată formula.
-
Selectați OK.
Image -
Subtotaluri sunt inserate pentru fiecare grup de date, iar un total general este inserat în partea de jos a setului de date.
Image - Pentru a schimba funcția_num, evidențiați orice celulă din setul de date și selectați Date > Subtotal. Apoi, alegeți-vă în caseta de dialog Subtotal.
