SUBTOTAL ( Podzbrojevi )
Vraća podzbroj u popisu ili bazi podataka. Lakše je
stvoriti popis sa podzbrojevima koristeći naredbu
Podzbrojevi (izbornik Podaci).
Kad jednom stvorite popis s podzbrojevima, možete ga
mijenjati koristeći funkciju SUBTOTAL.
Sintaksa
SUBTOTAL(function_num,
ref1, ref2, ...)
Function_num je broj od 1 do 11 (uključuje skrivene
vrijednosti) ili 101 do 111 (zanemaruje skrivene
vrijednosti) koji određuje koja će se funkcija koristiti za
izračun podzbrojeva unutar popisa.
Function_num
(uključuje skrivene vrijednosti) |
Function_num
(zanemaruje skrivene vrijednosti) |
Funkcija |
1 |
101 |
AVERAGE |
2 |
102 |
COUNT |
3 |
103 |
COUNTA |
4 |
104 |
MAX |
5 |
105 |
MIN |
6 |
106 |
PRODUCT |
7 |
107 |
STDEV |
8 |
108 |
STDEVP |
9 |
109 |
SUM |
10 |
110 |
VAR |
11 |
111 |
VARP |
Ref1, ref2, su 1 do 29 raspona ili referenci za koje
želite izračunati podzbroj.
Napomene
- Ako postoje drugi podzbrojevi unutar ref1, ref2,…
(ili ugniježđeni podzbrojevi), oni se ignoriraju zbog
izbjegavanja dvostrukog uključivanja.
- Za konstante function_num od 1 do 11, funkcija SUBTOTAL uključuje vrijednosti redaka skrivenih naredbom
Sakrij na podizborniku
Redak izobrnika
Oblikovanje). Koristite ove konstante kada želite
napraviti podzbroj skrivenih i neskrivenih brojeva na
popisu. Za konstante function_Num od 101 do 111,
funkcija SUBTOTAL zanemaruje vrijednosti redaka
sakrivenih naredbom Sakrij na
podizborniku Redak izbornika
Oblikovanje). Koristite ove
konstante kada želite napraviti podzbroj samo
neskrivenih brojeva na popisu.
- Funkcija SUBTOTAL zanemaruje sve retke koji nisu
uključeni u rezultatu filtra, bez obzira koju vrijednost
function_num koristite.
- Funkcija SUBTOTAL dizajnirana je za stupce podataka
ili okomite raspone. Nije dizajnirana za retke podataka
ili vodoravne raspone. Na primjer, kada napravite
podzbroj vodoravnog raspona koristeći function_num od
101 ili veće, kao što je SUBTOTAL(109,B2:G2), skrivanje
stupca ne utječe na podzbroj. Međutim, skrivanje retka u
podzbroju okomitog raspona utječe na podzbroj.
- Ako je bilo koja adresa 3D adresa, SUBTOTAL vraća
pogrešku #VRIJ!.
Primjer
Za primjer:
- Ručno skrivanje redova (ćelija)
SUBTOTAL(9;range)
zbraja sve redove, skrivene i vidljive (ćelije).
SUBTOTAL(109;range)
zbraja samo vidljive redove (ćelije).
- Skrivanje redova (ćelija) koristeći Autofilter
SUBTOTAL(9;range)
zbraja samo vidljive redove (ćelije).
SUBTOTAL(109;range)
zbraja samo vidljive redove (ćelije).
PRIMJER ZBRAJANJA SAMO
VIDLJIVIH REDOVA korištenjem funkcije Subtotal
Kada imamo neke podatke koje trebamo zbrojiti a želimo
ponekada ručno sakriti neke redove (hide row) i ti skriveni redovi ne bi
trebali ući u zbroj, iskoristiti ćemo funkciju Subtotal sa
Function_num brojem ( u ovom slučaju za zbrajanje SUM =>
109
)
Primjer SUBTOTAL (
podzbrajanja ) i printanja podzbroja na svaki list posebno
Da se odmah razumijemo. Ovaj primjer može se
odraditi i na drugi način, ovo je samo jedan od primjera.
Sve ovisi o vrsti zadatka, vašoj želji i načinu rada kao i o
doišljatosti.
U ovom primjeru pokazat ću kako možemo neku
veliku tablicu koja sadrži više listova prilikom ispisa na
papir prikazati kao zasebne cjeline na svakom listu papira
sa zbrojem nekakvih vrijednosti za svaku cjelinu ( grupu ).
Ovaj termin "cjelina" ili "grupa" Vi možete primijeniti na
način kako Vama odgovara a ja ću prikazati na jednostavnom
primjeru.
Dakle imamo tablicu sa puno redova i prilikom
ispisa imamo tri lista na kojem će se ispisati tablica. Kao
prvo želimo da na svakom listu na vrhu tablice budu nazivi
naših stupaca. Uz to želimo na svakom listu da ispod tablice
imamo podzbroj ( zbroj vidljivih vrijednosti ) a na zadnjem
listu ukupan zbroj svih vrijednosti.
Postavljanje naziva zaglavlja stupaca na
svaki list:
Nazive stupaca ( zaglavlje ) na svaki list
postaviti ćemo u dijalog prozoru Datoteka (File) =>
Postavljanje stranice (Page Setup) => kartica List (Sheet) i
na njoj postaviti raspon ćelija u kojem se nalazi zaglavlje
stupaca "Reci koje treba ponoviti na vrhu:" (Row to repeat
to top). Da bi postavili raspon ćelija trebate ga upisai ili
kliknuti na gumb sa strelicom koji se nalazi u desnoj strani
dotičnog polja a potom selektirati dotični raspon. Potom
klik na U redu (OK).
Postavljanje broja stranice od ukupnog
broja stranica za svaki list:
Na kartici (tabu) Podnožje/Zaglavlje (Header/Footer) u
sekciji za Prilagođeno Podnožje (Custom Footer) postavimo
broj stranice od broja stranica. To izgleda kao na slici
ispod. Ovime smo postavili numeraciju stranica.
Slijedeći korak je rad na samim podacima i postavljanje
SUBTOTAL-a
Ovo je naša tablica sa podacima
AKo odemo u pregled prije ispisa (print preview) uočit ćemo
da imamo tri lista za isprintati ali zbroj vrijednosti
nalazi se amo na zadnjem listu. Pogledajte sliku ispod
Kao što vidite na slikama podaci su raznoliki i nisu
grupirani. Za uspješno rješenje ovog problema nama bi
trebale GRUPE podataka. Zbog toga ja sam dodao stupac
"grupe" pomoću kojega ću moći odrediti da mi se određena
grupa pojavljuje na listu a ispod i zbroj za dotičnu grupu.
Ako radite nešto slično tada imate mogućnost da te grupe
sortirate. Meni je najjednostavnije bilo ovako. Naravno ovaj
stupac A sa grupama mogu postaviti da font bude bijele boje
i tako da se ne vidi kada sve isprintam.
Kada smo sve pripremili idemo kreirati SUBTOTAL sa određenim
parametrima.
1. Selektirajte sve Vaše podatke
2. Podaci (Data)
3. Podzbrojevi (Subtotal)
Potom na dijalog prozoru postavite parametre
- grupa
- Ukupni zbroj ( sum )
- Za subtotal izaberite opciju "Vrijednost"
- Uključite opcije kao na slici ( Page break betwen groups i
Summary below data )
Kliknite na gumb U redu (OK)
Sada imate drugačiji izgled radnog lista sa dodatnim
elementima ispred brojeva redova. To izgleda kao na slici
ispod. Ovaj prikaz dobijete klikom na gumbić sa brojem 3.
Ako kliknete na gumbić sa brojem 1 tada imate ovakav prikaz
Ako kliknete na gumbić sa brojem 2 tada imate ovakav prikaz
Ovime smo postavili opcije za Subtotal i sada kada odemo u
Pretpregled stranice (Print Preview) imamo ovakav izgled
stranice
Ako
sakrijemo stupac
"A" tada se neće vidjeti nazivi grupa a
li ni tekst "Ukupan Zbroj". Naravno možemo čak i obojati
nazive grupa u BIJELI font pa se neće vidjeti i stupac A
maximalno skupiti ali poravnanje postaviti u LIJEVO.
Ako postavimo FONT grupa kao BIJELI tada se
on neće vidjeti. Pogledajte sliku ispod
Download ovog primjer
OVDJE.
|
|
Još jedan primjer korištenja funkcije
SUBTOTAL
pogledajte OVDJE
|