Microsoft Office Excel
uređuje Dragan Grbić

Zanimljiva upotreba funkcije SUBTOTAL

Nivo:
NIVO 3 - Prilozi na ovom nivou su namenjeni naprednim korisnicima, onima koji se već dobro snalaze u programima i onima koji već imaju iskustva u naprednim tehnikama. Ovom nivou pripadaju preporuke i trikovi koje bi trebalo da savlada svako ko teži dugotrajnom i efikasnom radu u Office programima.

SUBTOTAL je zanimljiva agregatna funkcija koju automatski koriste neki napredni alati u Excelu. Međutim, postoje dobri razlozi zašto biste poželeli da i sami upotrebite tu funkciju na radnom listu.

Ovaj prilog je napisan na osnovu priloga preuzetog sa sajta Daily Dose of Excel
 

Kada raspolažete složenim podacima u kojima je potrebno da vidite parcijalne zbirove, često ste u potrebi da umećete dodatne redove u kojima ćete postavljati funkcuju za sabiranje. Kada tih podataka ima relativno malo, nema osobitih problema da to napravite kao na ovoj slici:

Prvi zbirovi su izvedeni verovatno kao klik na polje AutoSum, program je ispravno označio podatke koje treba sabrati, a vi ste na kraju samo pripazil ida konačni zbir u redu SVE UKUPNO bude izveden kao prosti izraz tri međuzbira.

Naravno, taj zbir je moguće izvesti i kao funkciju: mogli ste napisati =SUM(B7;B13;B19) i dobili biste isti rezultat. Izraz u primeru je napisan čisto kao prosta ilustracija.

Međutim, situacija postaje znatno komplikovanija ukoliko postoji mnogo veći obim podataka, pa još i njihova višestruka hijerarhija, kao u sledećem slučaju sa slike:

Pripazite na to da u situaciji na slici ima više sakrivenih redova: reč je o prostoj, ali velikoj strukturi u kojoj postoje polazni podaci za tri kase u pet radnih dana tokom četiri nedelje, sa svim međuzbirovima za svaku nedelju, za svaku kasu i za sve kase.

Puni primer možete pregledati u zipovanoj datoteci "funkcija SUBTOTAL.XLS" koja je priložena kao ilustracija tehnike o kojoj govorimo.
 

U ovom času bi se već moglo govoriti o tome da li su polazni podaci oblikovani optimalno: bilo bi zgodnije da je okupljanje organizovano kao uređena lista, kako biste mogli da nagažujete alatke za međuzbirove ili izvedenu tabelu. Ali, praksa je nemilosrdna: odveć često biva da polazni podaci nisu tako sređeni.

Najveći problem sa ovakvim uređenjem podataka je u tome što je izuzetno podložan grešci. Ako baš hoćete: dok sam spremao ovaj primer, zaista sam napravio materijalnu grešku: u međuzbiru svih podataka za jednu kasu sam zaboravio da umetnem podatke iz četvrte nedelje! U času postavljanja formula, nije bilo ovih uskih praznih redova (koje sam naknadno ubacio zbog lakše preglednosti na slikama) i ispustio sam vrednost u ćeliji neposredno iznad ćelije sa zbirom koji sam trenutno uređivao...

Znatno olakšanje ovog posla bismo mogli da izvedemo ako bismo upotrebili funkciju SUBTOTAL. Ta funkcija nije baš omiljena i većina korisnika je izbegava zato što ima pomalo čudnu sintaksu; reč je o tome da ova agregatna funkcija preuzima na sebe poslove u raznim naprdnim alatima, pa je načinjena tako da samo promenom jednog argumenta može da iskazuje različite agregatne vrednosti (zbir, prosek, maksimum, minimum, ...), pa još i u dve varijacije pojavnog oblika rezultata, o čemu ću vam reći malo kasnije.

Glavni štos koji nas ovog časa interesuje, a koji ćemo iskoristiti u slučaju iz primera je sledeći: da li ste znali da funkcija SUBTOTAL ignoriše sve druge funkcije SUBTOTAL u naznačenom opsegu?

Samo trenutak, kažete vi: šta to znači?

Pogledajte još jednom prvu sliku (tri kase, tri dana): bilo bi najlakše da ste postavili funkciju =SUM(B3:B19), ali tako biste uhvatili i parcijalne sume. Neko bi se dosetio, pa bi napisao =SUM(B3:B19)/2 i tim trikom bi došao do tačnog rezultata... Ali, sve je to labavo: da li biste u slučaju trostepene hijerarhije delili to sa tri? Da li su svi međurezultati izvedeni valjano? Ama, kako bi bilo da svaki međurezultat, bez obzira na nivo u hijerarhiji, bude napravljen kao rezultat polaznih podataka? Ali, funkcija SUM bi onda morala da glasi ovako:

=SUM(B4:B6;B10:B12;B16:B18)

Sasvim u redu, valjano i tačno, ali šta će se desiti kada se tabela iskomplikuje? Šta će biti kada budemo dodali sve dane, sve radne nedelje, petnaest prodavnica? Preuređenje formula pešice bi dovelo do greške sa verovatnoćom od 99%, budite uvereni u to.

Ovde uskače pomenuta osobina funkcije SUBTOTAL. Pogledajte sliku:

Obratite pažnju na formulu u ćeliji B21. Sasvim je očigledno da i tri međuzbira ulaze u opseg, ali rezultat je valjan: funkcija SUBTOTAL je uradila svoj posao efikasnije od funkcije SUM. Dakle, isplatilo se da je primenimo.

Pazite: ovde sam namerno povukao da opseg vrednosti u funkciji bude do ćelije B20, a ne do B19. To sam učinio zato što u tom slučaju obezbeđujem kontekstualnu izmenu formule u slučaju da dodajem redove za sledeće kase!

A ako vam još uvek nije najjasnije, hajde da pogledamo izmenjenu situaciju sa druge slike:

Da li je potreban ikakav komentar? OK, i ovde sam, ilustracije radi, zadržao skrivene redove. U ovom slučaju, važno je da napomenem taj detalj. Uskoro ćete saznati zašto je tako.

Ovakva struktura međurezultata je daleko fleksibilnija, jer su sve formule direktno osetljive na promenu dimenzija polaznih podataka. Štaviše, ovde se oslanjam i na izvesnu inteligentnost programa kada dodam red (npr. podaci za subotu), gde ćete nakon ubacivanja reda i dodavanja podatka uočiti da se međurezultat prvog nivoa menja - zapravo, menja se referenca u formuli; to možete proveriti sami u datoteci sa primerom.

O alatki za dobijanje automatskih međuzbirova govorićemo drugom prigodom, a tada ćete videti da Excel koristi upravo funkciju SUBTOTAL da bi što fleksibilnije pružio međurezultate u željenom obliku.

A kad smo kod tog pojma "željeni oblik", hajde da vidimo šta znači prvi argument funkcije SUBTOTAL. Ako koristite Excel 2007, onda prilikom pisanja imate značajnu pomoć; ali, u svakom slučaju, u času pisanja možete kliknuti na dugme fx levo od polja za unos formula, što će vas odvesti u čarobnjak za pisanje funkcija; tu ćete pronaći i hipervezu ka sadržaju iz sistema pomoći...

Postoji 11 agregatnih funkcija u funkciji SUBTOTAL, koje prozivate određenim brojem: 1 - AVERAGE (prosek), 2 - COUNT (prebrojavanje numeričkih ćelija), 3 - COUNTA (prebrojavanje nepraznih ćelija), 4 - MAX (maksimum), 5 - MIN (minimum), 6 - PRODUCT (proizvod), 7 - STDEV (standardna devijacija uzorka), 8 - STDEVP (standardna devijacija ukupne populacije podataka), 9 - SUM (zbir), 10 - VAR (varijansa uzorka) i 11 - VARP (varijansa ukupne populacije podataka). Agregatne funkcije su pobrojane abecednim redom, što je malo blesavo sa stanovišta frekventnosti upotrebe (SUM se koristi u barem 90% slučajeva), ali tako je to, sad je kasno da se išta menja. Pa dobro, upamtite makar taj broj 9 za najčešću funkciju.

Kritično važan detalj: postoje dve varijacije prozivanja funkcija. To su brojevi 1-11 i brojevi 101-111. Naoko, rezultat je isti; međutim, funkcije prozvane brojem uvećanim za 100 su karakteristične po tome da će sadržaji u skrivenim ćelijama biti ignorisane. Nemojte se čuditi, to je veoma celishodna stvar. Recimo, agregatne funkcije koje se postavljaju na dnu eksplicitnih tabela (lista) se pišu baš tako. Kada filtrirate podatke u listi, videćete agregatne vrednosti samo za vidljivi opseg podataka.

U opštem slučaju, kada funkciju SUBTOTAL dobijate kao rezultat rada neke napredne operacije u programu, Excel će postaviti pravilan oblik za prozivanje funkcije; ako koristite funkciju sami, pripazite da ne pogrešite. Primera radi, situacija na četvrtoj slici sadrži skrivene redove. Sve je u redu baš zato što sam koristio broj 9 kao prvi argument; da sam upotrebio sabiranje prozivajući ga brojem 109, posle sakrivanja redova bih dobio netačne rezultate!

Da biste sami isprobali efekte primene funkcije SUBOTOTAL, preuzmite datoteku subtotal.zip koja sadrži radnu svesku sa primerima sa slike.
 

Ovaj prilog možete komentarisati ako pristupite najavi priloga na blogu Praktikuma na Webu