Funcția SUBTOTAL: Formula și modul de utilizare a funcției SUBTOTAL în Excel

Ce este funcția SUBTOTAL în Excel ? Care este formula SUBTOTAL în Excel ? Să aflăm cu LuckyTemplates.com!

Microsoft Excel are multe funcții utilizate pentru calcularea totalurilor, iar SUBTOTAL este una dintre ele. Cu toate acestea, Subtotal este diferit de funcția SUM. Nu este o sumă normală. În schimb, este folosit pentru a calcula subtotaluri în liste sau baze de date, pentru a calcula medii și multe altele.

Conținut principal despre funcția SUBTOTAL

• Ce este funcția SUBTOTAL și pentru ce este folosită?
• Formula SUBTOTAL în Excel
• Cum se utilizează funcția SUBTOTAL în Excel
  • 1. Calculați totalul rândurilor filtrate 
  • 2. Numărați celulele filtrate care nu sunt goale
  • 3. Ignorați valorile în formulele de subtotal imbricate
• Lucruri de luat în considerare înainte de a utiliza funcția SUBTOTAL în Excel
• Cum să eroați atunci când efectuați funcția SUBTOTAL în Excel

Ce este funcția SUBTOTAL și pentru ce este folosită?

La prima vedere, vă puteți întreba: Care este scopul funcției SUBTOTAL? La urma urmei, este ușor să găsiți un subtotal dintr-o serie de celule folosind funcții existente, cum ar fi SUMIF sau MEDIE .

Deși este adevărat că nu trebuie neapărat să utilizați SUBTOTAL, aceasta face mult mai ușoară analiza datelor vizate. Puteți insera o formulă folosind SUBTOTAL într-un interval de celule fără a afecta totalul total, deoarece SUBTOTAL ignoră celelalte celule care conțin o formulă SUBTOTAL.

SUBTOTAL funcționează cu alte funcții Excel, cum ar fi filtrarea celulelor. Dacă filtrați un tabel după o valoare, formula SUBTOTAL se actualizează, recunoaște filtrul și exclude celulele corespunzătoare. SUBTOTAL este, de asemenea, util pentru ignorarea valorilor ascunse, ceea ce alte funcții (cum ar fi SUM) nu le pot face.

Din fericire, puteți utiliza alte funcții ca parte a unei formule SUBTOTAL. Există 11 metode diferite de subtotal disponibile, fiecare potrivită pentru o funcție Excel existentă. Dacă doriți o formulă SUM care funcționează cu celule filtrate și ignoră alte formule SUBTOTAL, cel mai bine este să utilizați SUBTOTAL, deși un tabel pivot poate funcționa în același mod.

Formula SUBTOTAL în Excel

Funcția SUBTOTAL funcționează prin calcularea valorii totale a unui interval de celule, pe baza unei alte funcții matematice. SUBTOTAL folosește până la 11 funcții matematice diferite (de la MEDIE la VAR.P) pentru a găsi valoarea totală adecvată, în timp ce lucrează în parametrii funcției (adică, ignorând alte formule SUBTOTAL).

Funcția SUBTOTAL are sintaxa =SUBTOTAL(num_funcție, ref1, [ref2],…) . Acolo:

• Function_num: numerele de la 1 la 11 și de la 101 la 111 specifică ce funcție va fi utilizată pentru a calcula în SUBTOTAL.
• Ref1, Ref2, ...: 1 sau mai multe celule sau interval de celule pentru a calcula subtotalurile, maxim 254.

Notă:

• Funcția SUBTOTAL este concepută pentru a calcula coloane verticale de date.
• Dacă argumentele ref1, ref2,... conțin funcția SUBTOTAL, acestea vor fi ignorate pentru a evita dubla numărare.
• Dacă funcția_num este de la 1 la 11, funcția SUBTOTAL calculează includerea valorilor ascunse în setul de date deoarece rândul care conține acel interval este ascuns. Dacă funcția_num este de la 101 la 111, funcția SUBTOTAL calculează doar valorile neascunse în setul de date (fără numărarea valorilor din rândurile ascunse).
• Pentru zonele de date ascunse de filtru, SUBTOTAL va fi ignorat.

Cum se utilizează funcția SUBTOTAL în Excel

1. Calculați totalul rândurilor filtrate 

Pentru a înțelege mai bine acest tip de sumă, vă rugăm să consultați articolul Calculați suma valorilor listei filtrate în Excel . Practic, funcția SUBTOTAL în acest caz va fi:

=SUBTOTAL(9,_interval)

pham_range aici este zona pe care doriți să o însumați după filtrarea datelor.

2. Numărați celulele filtrate care nu sunt goale

Vom folosi SUBTOTAL 3 sau SUBTOTAL 103. Cu toate acestea, în cazul în care există rânduri ascunse, trebuie să utilizați SUBTOTAL 103 pentru a număra cu precizie celulele vizibile care nu sunt goale.

De exemplu, tabelul de date de mai sus va ascunde 2 rânduri 4 și 5. Când utilizați SUBTOTAL 3 sau SUBTOTAL 103, va produce 2 rezultate diferite.

Introducem formula și apoi Excel va afișa automat funcția setată din care puteți alege fără a fi nevoie să vă amintiți.

Rezultatul la utilizarea SUBTOTAL 3 va fi 3, inclusiv celulele ascunse din rând.

În ceea ce privește SUBTOTAL 103, va afișa doar celulele negoale pe care le vedem, ignorând rândurile ascunse.

3. Ignorați valorile în formulele de subtotal imbricate

De exemplu, vom calcula media kilogramelor totale de țesătură din depozitele A1 și A2.

Formula medie pentru depozitul A2= SUBTOTAL(1,C2:C4) și dă rezultatul 19.

Formula medie pentru depozitul A1 = SUBTOTAL(1,C5:C7) are rezultatul 38.

Totuși, la calcularea mediei numărului total de țesături din 2 depozite, rezultatele medii din cele 2 depozite vor fi ignorate. Avem formula =SUBTOTAL(1,C2:C9) iar rezultatul va elimina automat rezultatul mediu calculat anterior.

Lucruri de luat în considerare înainte de a utiliza funcția SUBTOTAL în Excel

Deși SUBTOTAL are avantajele sale, există câteva lucruri pe care ar trebui să le luați în considerare înainte de a începe să utilizați această funcție, inclusiv următoarele:

• După cum sa menționat, utilizarea 1-11 pentru argumentul function_num va asigura că SUBTOTAL include valori ascunse, în timp ce 101-111 le ignoră.
• Utilizarea unei alte valori decât 1-11 sau 101-111 va face ca Excel să returneze eroarea #VALOARE!. Acest lucru se va întâmpla și pentru referințele de celule 3D (unde aceeași celulă, pe mai multe foi, este referită într-un interval).
• Când sunt utilizate intervale de celule orizontale, cum ar fi A1:D1, valorile ascunse sunt incluse automat (indiferent de valoarea argumentului function_num). Aceasta este o limitare a funcției SUBTOTAL și nu poate fi depășită.
• Utilizarea SUBTOTAL pe datele filtrate va asigura că valorile ascunse sunt întotdeauna ignorate, indiferent de argumentul function_num utilizat.
• SUBTOTAL poate fi folosit ca o funcție imbricată ca parte a altor funcții, inclusiv formule care conțin o funcție IF.
• Dacă o altă formulă SUBTOTAL se află în intervalul de date ref1, SUBTOTAL o ignoră și o exclude din calculul general.
• Excel permite utilizarea a până la 254 de intervale de celule într-o formulă SUBTOTAL, deși este nevoie de doar 1 interval de celule pentru ca formula să funcționeze.

Cum să eroați atunci când efectuați funcția SUBTOTAL în Excel

Când calculați funcția SUBTOTAL în Excel, veți întâlni adesea câteva erori de bază:

• #VALOARE!: identificatorul funcției nu este în intervalul 1-11 sau 101-111 sau are o referință 3D.
• #DIV/0!: Apare atunci când o anumită sumă trebuie împărțită la 0 (de exemplu, calculând media sau abaterea standard a unui interval de celule care nu conține valori numerice).
• #NUME? Numele funcției SUBTOTAL este scris greșit.

Câteva sfaturi despre cum să utilizați funcția SUBTOTAL în Microsoft Excel pe care este posibil să nu le cunoașteți

Sfat 1: Să presupunem că doriți să vă asigurați că toate culorile de tricouri sunt disponibile în orice mărime în oricare dintre depozite. Puteți urma acești pași:

Pasul 1: faceți clic pe Subtotal . Rețineți că acest pas este de a adăuga mai multe criterii la datele subtotale existente.

Pasul 2: Selectați COUNT din meniul derulant și Dimensiune din câmpul „ Adăugați câmpul subtotal la ”. Apoi, debifați Înlocuire subtotaluri curente . Când faceți clic pe OK , veți primi următorul tabel de date:

Acest lucru vă ajută să vă asigurați că aveți un număr suficient de dimensiuni diferite și că puteți clasifica datele într-un mod care nu necesită operații repetate.

Sfat 2 : este întotdeauna util să sortați datele după coloană.

Sfatul 3: vă ajută să includeți o etichetă pe primul rând.

Sfat 4: Dacă doriți să rezumați datele, debifați caseta „ Rezumat de sub date atunci când introduceți Subtotal ”. - Rezumatul datelor de mai jos la introducerea funcției SUBTOTAL.

Pe scurt, ce este funcția Subtotal în Excel?

După cum sugerează și numele, funcția SUBTOTAL din Excel este utilizată pentru a calcula subtotalurile. Funcția Excel SUBTOTAL returnează rezultate agregate pentru valorile furnizate. SUBTOTAL poate calcula suma, medie, numărare, maxim... Acest Excel poate include sau exclude și valori în rândurile ascunse.

• Scop: Calculați subtotalul într-o listă sau bază de date.
• Valoare returnată: un număr care reprezintă un tip de subtotal.
• Argument:
  • function_num- Un număr care specifică ce funcție să folosești pentru a calcula subtotalurile dintr-o listă.
  • ref1- Un interval numit sau o referință la un subtotal.
  • ref2- [opțional] Un interval numit sau o referință la un subtotal.

Utilizarea funcției SUBTOTAL vă poate ajuta să analizați rapid un set de date fără a vă baza pe tabele pivot mai complexe. Dacă rămâneți blocat, puteți utiliza și caracteristica Subtotal, aflată în fila Date de pe panglică, pentru a crea automat o formulă SUBTOTAL pe care apoi o puteți edita sau copia în altă parte.

Vezi mai mult:

• Funcția SUMPRODUS în Excel: Calculați produsul total al valorilor corespunzătoare
• Cum să combinați 2 coloane Nume complet și Prenume în Excel fără a pierde conținut