Preuzmite primjer radne knjige
Ovaj vodič prikazuje kako se koristi Excel SUBTOTAL funkcija u Excelu za izračun zbirne statistike.
SUBTOTAL Pregled funkcije
Funkcija SUBTOTAL Izračunava zbirnu statistiku za niz podataka. Dostupni statistički podaci uključuju, ali nisu ograničeni na prosjek, standardnu devijaciju, broj, min i maks. Cijeli popis dolje pogledajte u odjeljku ulaza funkcija:
Da biste koristili SUBTOTAL Excel radnu tablicu, odaberite ćeliju i upišite:
(Obratite pažnju na to kako se pojavljuju unosi formule)
SUBTOTAL Sintaksa funkcije i ulazi:
| 1 | = SUBTOTAL (broj_funkcije, REF1) |
broj_funkcije - Broj koji predstavlja koju operaciju izvršiti.
REF1 - Rasponi ili reference koji sadrže podatke za izračunavanje.
Što je funkcija SUBTOTAL?
SUBTOTAL je jedna od jedinstvenih funkcija unutar proračunskih tablica jer može razlikovati skrivene ćelije od onih koje nisu skrivene. To se može pokazati vrlo korisnim kada se bavite filtriranim rasponima ili kada trebate postaviti izračune na temelju različitih odabira korisnika. Budući da također zna zanemariti druge SUBTOTAL funkcije iz svojih izračuna, možemo ga koristiti i u velikim sažetim podacima bez straha od dvostrukog brojanja.
Osnovni sažetak sa SUBTOTAL
Recimo da ste imali tablicu razvrstanih prodaja proizvoda i htjeli ste stvoriti ukupne vrijednosti za svaki proizvod, kao i ukupnu ukupnu vrijednost. Možete upotrijebiti zaokretnu tablicu ili umetnuti neke formule. Razmotrite ovaj raspored:
Postavio sam neke SUBTOTALNE funkcije u ćelije B5 i B8 koje izgledaju
| 1 | = SUBTOTAL (9, B2: B4) |
Iz sintakse možete koristiti različite brojeve za prvi argument. U našem konkretnom slučaju koristimo 9 da označimo da želimo uložiti iznos.
Usredotočimo se na ćeliju B9. Ima ovu formulu koja uključuje cijeli raspon podataka stupca B, ali ne uključuje ostale međuzbrojeve.
| 1 | = SUBTOTAL (9, B2: B8) |
NAPOMENA: Ako ne želite sami napisati sve formule sažetka, možete otići na vrpcu Podaci i upotrijebiti čarobnjaka Outline - međuzbroj. Automatski će umetnuti retke i postaviti formule umjesto vas.
Razlika u prvim argumentima
U prvom primjeru koristili smo 9 da označimo da želimo napraviti iznos. Razlika između korištenja 9 i 109 bila bi u tome kako želimo da funkcija obrađuje skrivene retke. Ako koristite oznake 1XX, funkcija neće uključivati retke koji su ručno skriveni ili filtrirani.
Evo našeg stola od prije. Prebacili smo funkcije tako da možemo vidjeti razliku između 9 i 109 argumenata. Sa svim vidljivim, rezultati su isti.
Primijenimo li filter za filtriranje vrijednosti 6 u stupcu B, dvije funkcije ostaju iste.
Ako ručno sakrijemo retke, vidimo razliku. Funkcija 109 mogla je zanemariti skriveni redak, dok funkcija 9 nije.
Promijenite matematičku operaciju s SUBTOTAL
Možda biste ponekad željeli svom korisniku dati mogućnost da promijeni vrstu izračuna. Na primjer, žele li dobiti zbroj ili prosjek. Budući da SUBTOTAL kontrolira matematičku operaciju brojem argumenta, to možete zapisati u jednu formulu. Evo našeg postavljanja:
Napravili smo padajući izbornik u D2 gdje korisnik može odabrati "Zbroj" ili "Prosjek". Formula u E2 je:
| 1 | = SUBTOTAL (IF (D2 = "Prosjek", 1, IF (D2 = "Zbir", 9)), B2: B4) |
Ovdje će funkcija IF odrediti koji numerički argument dati SUBTOTAL -u. Ako je A5 "Prosjek", tada će ispisati 1, a SUBTOTAL će dati prosjek B2: B4. Ili, ako je A5 jednako "Zbroj", IF daje vrijednost 9 i dobivamo drugačiji rezultat.
Ovu mogućnost možete proširiti pomoću tablice za pretraživanje da biste naveli još više vrsta operacija koje želite izvesti. Tablica za pretraživanje mogla bi izgledati ovako
Zatim biste mogli promijeniti formulu u E2 u
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Uvjetne formule s SUBTOTAL
Iako SUBTOTAL ima mnogo operacija koje može obaviti, ne može sam provjeriti kriterije. Međutim, možemo ga koristiti u pomoćnom stupcu za izvođenje ove operacije. Kad budete imali stupac podataka za koji znate da hoće stalno ako imate dio podataka, možete koristiti SUBTOTALs za otkrivanje skrivenih redaka.
Evo tablice s kojom ćemo raditi u ovom primjeru. Na kraju bismo željeli zbrojiti vrijednosti za "Apple", ali i dopustiti korisniku da filtrira stupac Količina.
Prvo, stvorite pomoćni stupac u kojem će se nalaziti funkcija SUBTOTAL. U C2, formula je:
| 1 | = SUBTOTAL (103, A2) |
Upamtite da 103 znači da želimo napraviti COUNTA. Preporučujem korištenje COUNTA jer tada možete popuniti svoju referentnu ćeliju A2 ili brojeve ili tekst. Sada ćete imati tablicu koja izgleda ovako:
Ovo se na prvu ne čini korisnim jer su sve vrijednosti samo 1. Međutim, ako sakrijemo redak 3, to će se "1" u C3 promijeniti u 0 jer pokazuje na skriveni redak. Iako je nemoguće imati sliku koja prikazuje vrijednost određene skrivene ćelije, mogli biste to provjeriti skrivanjem retka, a zatim napisati osnovnu formulu poput ove za provjeru.
| 1 | = C3 |
Sada kada imamo stupac koji će promijeniti vrijednost ovisno o tome je li skriven ili ne, spremni smo za pisanje konačne jednadžbe. Naši SUMIFS će izgledati ovako
U ovoj formuli zbrajat ćemo vrijednosti iz stupca B samo ako je stupac A jednak "Apple", i vrijednost u stupcu C je 1 (također poznat, redak nije skriven). Recimo da naš korisnik želi filtrirati 600, jer se čini nenormalno visokim. Možemo vidjeti da naša formula daje točan rezultat.
S tom sposobnošću možete primijeniti ček na COUNTIFS, SUMIFS ili čak na SUMPRODUCT. Dodajete mogućnost dopuštanja korisnicima da kontroliraju neke rezače stolova i spremni ste za stvaranje izvrsne nadzorne ploče.
SUBTOTAL u Google tablicama
SUBTOTAL funkcija radi potpuno isto u Google tablicama kao i u Excelu:
SUBTOTALNI Primjeri u VBA
Također možete koristiti funkciju SUBTOTAL u VBA. Tip:application.worksheetfunction.subtotal (broj_funkcije, reh1)
Izvršavanje sljedećih VBA izraza
| 1234567891011121314151617 | Raspon ("C7") = Application.WorksheetFunction.Subtotal (1, Raspon ("C2: C5"))Raspon ("C8") = Application.WorksheetFunction.Subtotal (2, Raspon ("C2: C5"))Raspon ("C9") = Aplikacija.Funkcija radnog lista.Pubtotal (4, Raspon ("C2: C5"))Raspon ("C10") = Aplikacija.Funkcija radnog lista.Pubtotal (5, Raspon ("C2: C5"))Raspon ("C11") = Application.WorksheetFunction.Subtotal (9, Raspon ("C2: CE5"))Raspon ("D7") = Aplikacija.Funkcija radnog lista.Pubtotal (1, Raspon ("D2: D5"))Raspon ("D8") = Aplikacija.Funkcija radnog lista.Pubtotal (2, Raspon ("D2: D5"))Raspon ("D9") = Aplikacija.Funkcija radnog lista.Pubtotal (4, Raspon ("D2: D5"))Raspon ("D10") = Aplikacija.Funkcija radnog lista.Pubtotal (5, Raspon ("D2: D5"))Raspon ("D11") = Application.WorksheetFunction.Subtotal (9, Raspon ("D2: D5"))Raspon ("E7") = Application.WorksheetFunction.Subtotal (1, Raspon ("E2: E5"))Raspon ("E8") = Application.WorksheetFunction.Subtotal (2, Raspon ("E2: E5"))Raspon ("E9") = Application.WorksheetFunction.Subtotal (4, Raspon ("E2: E5"))Raspon ("E10") = Application.WorksheetFunction.Subtotal (5, Raspon ("E2: E5"))Raspon ("E11") = Application.WorksheetFunction.Subtotal (9, Raspon ("E2: E5")) |
će proizvesti sljedeće rezultate
Za argumente funkcije (broj_funkcije itd.) Možete ih unijeti izravno u funkciju ili definirati varijable koje ćete umjesto toga koristiti.
Povratak na popis svih funkcija u Excelu
