Za računanje međuvrednosti u Excel-u, možete koristiti SUBTOTAL funkciju. U današnjem tekstu učimo kako koristiti SUBTOTAL funkciju u Excel-u, a navodimo i pet razloga zašto koristiti ovu funkciju.
Uz pomoć ove funkcije možete izračunati međuvrednosti samo za isfiltrirane ćelije. Funkcija zahteva opseg ćelija iz kojih želite dobiti međuvrednost, kao i vrsta međuvrednosti koju želite da dobijete - međuzbir (SUM funkcija), međuprosek (AVERAGE funkcija), prebrojavanje (COUNT funkcija) itd.
Kako se koristi SUBTOTAL funkcija?
Sintakse ove funkcije je:
=subtotal(ime_funkcije;opseg_podataka_1;[opseg_podataka_2];...)
Ime_funkcije - izaberite neku od ponuđenih funkcija, opis je u tabeli ispod
Opseg_podataka - Opseg podataka na koji želimo da primenimo prethodno izabranu funkciju
Opseg_podataka_2 - opciono, drugi opseg koji smo dodali
Ono što je bitno znati, jeste da svaka funkcija se može koristiti u dva režima, prvi gde se NE ignorišu sakriveni redovi, i drugi gde se IGNORIŠU sakriveni redovi, što znači da ovu drugu opciju možete koristiti za računanje zbira isfiltriranih redova i sl.
* Prednost korišćenje SUBTOTAL funkcije umesto SUM funkcije za računanje međuzbirova jeste što SUBTOTAL neće računati prethodne subtotal funkcije u koloni, dok sum hoće. Npr. ako su brojevi od A1 do A5 i od A7 do A10, ukoliko u A6 koristimo SUBTOTAL za međuzbir od A1 do A5 i u A11 isto SUBTOTAL, kada u A12 opet koristimo SUBTOTAL za ukupan zbir od A1 do A11, subtotal neće sabirati međuzbirove u A6 i A11 ćeliji. Međutim, da smo koristili SUM funkciju, ona bi računala sve, pa bi rezultat bio 3x veći.
Kako izračunati međuzbirove u tabeli?
Dodavanje međuzbirova u tabeli možete raditi ručno, korišćenjem subtotal funkcije, međutim to zna biti dosadno ukoliko imate veću tabelu u kojoj niste dodavali međuzbirove na vreme. Zbog toga je Excel automatizovao ovaj postupak kako bi se rešio ovaj problem.
Na primeru koji ćemo koristiti, a čiji Excel fajl možete preuzeti na kraju teksta, imamo agente prodaje, regione u kojima su prodavali i vrednost njihove prodaje. Želimo da dodamo međuzbir za svaki region (istok, jug i zapad), pa bismo za ručno dodavanje morali da ubacujemo nove redove gde bi ručno unosili SUBTOTAL funkciju.
Za automatsko dodavanje međuzbira u ovoj tabeli, potrebno je selektovati tabelu, uključujući i nazive kolona. Otvorimo tab DATA (2), pa u outline grupi alata kliknete na Subtotal (3).
Nakon toga, u prozoru koji se otvorio izaberite naziv kolone po kojoj želite izračunati međuzbir (1), funkciju koju želite koristiti (izabrali smo SUB, da bi sabirali (2)), i koju kolonu će sabirati (3).
Kada kliknete ok, dobijete grupisane redove, međuzbir i ukupni zbir.
Da biste uklonili proračun međuzbirova, opet kliknemo na SUBTOTAL (Data - Subtotal), pa kliknemo na dugme Remove ALL.
Kako sabirati isfiltrirane podatke iz opsega podataka (SUBTOTAL funkcija)
Ukoliko ste pročitali uvodni deo ovog teksta, i pogledali tabelu sa opisom funkcija, videli ste da je šifra funkcije za sabiranje, koja ne sabira skrivene redove 109, što znači da ćemo u SUBTOTAL funkciji, kada želimo da računamo zbir isfiltriranih redova, koristiti šifru 109.
4 razloga zašto koristiti SUBTOTAL funkciju
1. Možete koristiti SUBTOTAL za ignorisanje skrivenih redova
Često sam viđao greške u Excel-u, gde se dobio pogrešan rezultat jer je korišćena SUM funkcija, koja je sabirala i vrednosti skrivenih redova u kojima su ostale vrednosti iz prethodnog projekta.
Takođe, kada želimo da isključimo neke stavke iz računice, možemo jednostavno sakriti redove.
=SUBTOTAL(109;A1:A10) će sabirati samo vidljive ćelije dok bi =SUBTOTAL(9;A1:A10) sabirao sve ćelije, čak i one sakrivene.
Na početku teksta se vidi lista šifara koje se koriste u SUBTOTAL funkciji i njihovo značenje.
2. Za računanje vrednosti filtriranih ćelija
Filtriranjem neke kolone, nepotrebni redovi će biti sakriveni, a SUBTOTAL funkcija sa šifrom 109, neće računati te skrivene redove, tako da ćete dobiti rezultat samo za te filtrirane vrednosti.
Iznad u tekstu sam detaljno opisao kako se to koristi, a i u primeru čiji ću link za preuzimanje ostaviti na kraju teksta, možete videti kako to izgleda.
3. Ukoliko se SUBTOTAL već nalazi u opsegu koji sabirate, SUBTOTAL će to zanemariti
Imate tabelu u kojoj je potrebno izračunati nekoliko međuzbirova, a na kraju ukupan zbir. Korišćenjem SUM funkcije, dobićete pogrešan rezultat ukoliko se međuzbirovi nalaze u opsegu koji sabira SUM, međutim ukoliko ste koristili SUBTOTAL za međuzbirove i ukupan zbir, on će zanemariti sve međuzbirove u opsegu gde je korišćena SUBTOTAL formula.
4. Možete automatski dodati međuzbirove u tabeli
Excel ima opciju koja automatizuje korišćenje međuzbirova u Excel-u. Na početku teksta smo detaljnije opisali kako možete koristiti ovu funkciju.
0 Коментари