Kada vaš Excel radni list sadrži skrivene redove, filtrirane podatke ili grupisane podatke, koristite Excel funkciju SUBTOTAL. Funkcija SUBTOTAL može uključiti ili isključiti skrivene vrijednosti u proračunima. Osim pronalaženja ukupne grupe podataka, Excel može izračunati prosjek, maksimum, minimum, standardnu devijaciju i varijansu vaših podataka. Evo kako umetnuti međuzbrojeve u Excel.
Upute u ovom članku odnose se na Excel za Microsoft 365, Excel 2019 i Excel 2016.
Sintaksa funkcije SUBTOTAL
Koristite funkciju SUBTOTAL u Excel-u da sumirate vrijednosti na radnom listu na različite načine. Posebno je korisno kada vaš radni list sadrži skrivene redove koje želite da uključite u izračun.
Sintaksa funkcije SUBTOTAL je: SUBTOTAL(funkcija_broj, ref1, ref2, …)
Argument function_num je obavezan i specificira vrstu matematičke operacije koja će se koristiti za međuzbroj. Funkcija SUBTOTAL može sabirati brojeve, izračunati prosječnu vrijednost odabranih brojeva, pronaći maksimalne i minimalne vrijednosti u rasponu, prebrojati broj vrijednosti u odabranom rasponu i još mnogo toga.
Funkcija SUBTOTAL zanemaruje ćelije koje ne sadrže podatke i ćelije s nenumeričkim vrijednostima.
Ovaj argument je broj i zavisi od toga da li želite da uključite skrivene redove u rezultat ili da isključite skrivene redove iz rezultata. Ovi redovi mogu biti ručno skriveni ili skriveni filterom.
Argumenti function_num uključuju:
| Funkcionalni zadatak | function_num | function_num |
|---|---|---|
| (uključuje skrivene vrijednosti) | (isključuje skrivene vrijednosti) | |
| PROSJEČNO | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PROIZVOD | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Referentni argumenti function_num 1 do 11 uključuju samo vrijednosti u skrivenim redovima kada koristite naredbu Sakrij za skrivanje redova. Kada koristite naredbu Filter, proračuni SUBTOTAL ne uključuju skrivene rezultate filtera.
Potreban je argument ref1. Ovo su ćelije koje se koriste za izračunavanje rezultata odabranog argumenta broj_funkcije. Ovaj argument može biti vrijednost, jedna ćelija ili raspon ćelija.
Argumenti ref2, … nisu obavezni. Ovo su dodatne ćelije koje su uključene u izračun.
Koristite funkciju SUBTOTAL sa skrivenim redovima
Excel funkcije se mogu unijeti ručno ili uz pomoć dijaloga Function Arguments. Da bi se ilustrovalo kako ručno uneti funkciju pomoću trake formule, sljedeći primjer koristi argument COUNT function_num za prebrojavanje broja vrijednosti u vidljivim redovima iu vidljivim i skrivenim redovima.
Da biste koristili funkciju SUBTOTAL za prebrojavanje broja redova na radnom listu:
- Počnite s radnim listom koji sadrži više redova podataka.
- Odaberite ćeliju koja će sadržavati broj vidljivih redova.
-
U funkcijskoj traci unesite =SUBTOTAL. Dok kucate, Excel predlaže funkciju. Dvaput kliknite na funkciju SUBTOTAL.
Da biste koristili dijaloški okvir Function Arguments za ulazak u funkciju SUBTOTAL, idite na Formule i odaberite Math & Trig >SUBTOTAL.
Image -
U padajućem meniju koji se pojavi, dvaput kliknite na argument 102 – COUNT function_num.
Image -
Upišite zarez (,).
Image -
U radnom listu odaberite ćelije koje želite uključiti u formulu.
Image -
Pritisnite Enter da vidite rezultat u ćeliji koju ste odabrali u koraku 2.
Image - Odaberite ćeliju koja će sadržavati broj vidljivih i skrivenih redova.
- U funkcijskoj traci unesite =SUBTOTAL. Dok kucate, Excel predlaže funkciju. Dvaput kliknite na funkciju SUBTOTAL.
-
U padajućem meniju koji se pojavi, dvaput kliknite na argument 2 – COUNT function_num, zatim upišite zarez (,).
-
U radnom listu odaberite ćelije koje želite uključiti u formulu, a zatim pritisnite Enter.
Image -
Sakrij nekoliko redova podataka. U ovom primjeru, sakriveni su redovi sa samo prodajom ispod 100.000 USD.
Image
Koristite funkciju SUBTOTAL sa filtriranim podacima
Upotreba funkcije SUBTOTAL na filtriranim podacima zanemaruje podatke u redovima koje je filter uklonio. Svaki put kada se kriterijumi filtera promene, funkcija se ponovo izračunava kako bi prikazala međuzbroj za vidljive redove.
Da biste koristili funkciju SUBTOTAL da vidite razlike u rezultatima proračuna tokom filtriranja podataka:
-
Kreirajte formule SUBTOTAL. Na primjer, kreirajte formule da odredite međuzbroj i prosječne vrijednosti filtriranih podataka.
Nije važno da li koristite argument function_num za vidljive ili skrivene redove. Oba argumenta daju isti rezultat u filtriranim podacima.
Image - Izaberite bilo koju ćeliju u skupu podataka.
-
Idite na Početnu, zatim odaberite Sort & Filter > Filter.
Image -
Koristite padajuće strelice za filtriranje podataka radnog lista.
Image -
Primijetite kako se vrijednosti mijenjaju svaki put kada odaberete različite kriterije filtriranja.
Image
Koristite funkciju SUBTOTAL sa grupisanim podacima
Kada su podaci grupisani, postoji način da primenite funkciju SUBTOTAL na svaku pojedinačnu grupu, a zatim izračunate ukupni zbroj za ceo skup podataka.
- Izaberite bilo koju ćeliju u skupu podataka.
-
Odaberite Podaci > Subtotal da otvorite dijaloški okvir Subtotal..
Image - Odaberite Pri svakoj promjeni u strelicu padajućeg izbornika i odaberite grupisanje u koje će se izračunati svaki međuzbroj.
- Odaberite Koristi funkciju padajuću strelicu i odaberite funkciju_num.
- U Dodaj međuzbroj listi, odaberite kolonu na koju će se formula primijeniti.
-
Odaberite OK.
Image -
Podzbirovi se ubacuju za svaku grupu podataka, a ukupni zbroj se ubacuje na dno skupa podataka.
Image - Da promijenite funkciju_num, označite bilo koju ćeliju u skupu podataka i odaberite Data > Subtotal. Zatim napravite svoje izbore u dijaloškom okviru Subtotal.
