Zbrajanje
vrijednosti sa drugog radnog lista uz tri uvjeta, jedan uvjet
određuje koji je to Sheet
Ako imate potrebu zbrojiti vrijednosti koje
se nalaze na ostalim radnim listovima (Sheets) a uz to imate
tri uvjeta. Jedna od uvjeta je naziv radnog lista.
Uzmimo za primjer da su nam 3 slijedeća
uvjeta: Naziv Sheet, nekakav redni broj i šifra. Naši radni
listovi imaju nazive mjeseci u godini i ima ih 12.
Potrebno je u sumarnom radnom listu u odnosu na tri uvjeta
imati brzi pregled koliko je zbroj vrijednosti uz uvjet
imena mjeseca rednog broja i šifre. Pogledajte slike ispod
Dakle na jednom Sheetu (u ovom primjeru SUM)
želimo brzo imati informacije sa ostalih radnih listova
pojedinačno uz određena tri uvjeta. Za rješavanje ovog
problema koristit ćemo nekoliko funkcija gdje ćemo određene
funkcije ugnijezditi u ostale. Prva funkcija koju možemo
koristiti (za Excel 2007) je
SUMIFS pomoću koje
možemo brzo doći do rezultata.
SUMIFS funkciju ugnijezdimo u
funkciju IF radi
prikaza rezultata kada nemamo upisan podataka za naziv
mjeseca u godini.
PRVA FORMULA:
Funkcija SUMIFS ima slijedeću sintaksu
SUMIFS(sum_range; criteria_range1;
criteria1; [criteria_range2;criteria2]; …)
Uočimo u formuli funkciju
INDIRECT koja je
vezana uz naziv radnog lista iz ćelije A2 a ona nam daje
rezultat kao dio formule koji bi inače pisali kao
siječanj!$E$6:$E$17
čime određujemo raspon podataka na Sheetu
siječanj.
Dakle umjesto prethodnog izraza u
formuli mi možemo pisati kao
INDIRECT($A$2&"!"&"$E$6:$E$17").
U ćeliji A2 nalazi se naziv mjeseca, npr:
siječanj.
Obratite pažnju na slijedeće.
Ako ne koristite funkciju INDIRECT nećete moći promjenom
naziva mjeseca u ćeliji A2 dobiti ispravan rezultat jer je u
formuli strogo definiran radni list. Primjer formule bez
INDIRECT funkcije dajem samo da bi bolje razumjeli zbog čega
sam koristio Indirect funkciju.
=SUMIFS(INDIRECT($A$2&"!"&"$E$6:$E$17");INDIRECT($A$2&"!"&"$A$6:$A$17");A7;INDIRECT($A$2&"!"&"$C$6:$C$17");B7)
ili ako strogo definiramo naziv radnog lista
na kojem tražimo podatke
=SUMIFS(siječanj!$E$6:$E$17);siječanj!$A$6:$A$17);A7;siječanj!$C$6:$C$17);B7)
DRUGA FORMULA:
Druga formula koju možemo koristiti u novijim
verzijama Excela je
SUMPRODUCT. Opet
umjesto klasične veze prema nazivu radnog lista možemo
koristiti INDIRECT.
=SUMPRODUCT((INDIRECT($A$2&"!"&"$E$6:$E$17"))*(INDIRECT($A$2&"!"&"$A$6:$A$17")=A7)*(INDIRECT($A$2&"!"&"$C$6:$C$17")=B7))
Dakle za primjer ova formula može izgledati i
ovako ali se vežemo u samoj formuli za dotični radni list
"siječanj"
=SUMPRODUCT(siječanj!$E$6:$E$17))*(siječanj!$A$6:$A$17)=A7)*(siječanj!$C$6:$C$17)=B7))
TREĆA FORMULA:
formula može izgledati i ovako
=SUMPRODUCT(--(INDIRECT($A$2&"!"&"$E$6:$E$17"));--(INDIRECT($A$2&"!"&"$A$6:$A$17")=A7);--(INDIRECT($A$2&"!"&"$C$6:$C$17")=B7))
Dakle za primjer ova formula može izgledati i
ovako ali se vežemo u samoj formuli za dotični radni list
"siječanj"
=SUMPRODUCT(--(siječanj!$E$6:$E$17));--(siječanj!$A$6:$A$17)=A7);--(siječanj!$C$6:$C$17)=B7))
Na vama ostaje samo da dotičnu formulu
ugnijezdite u IF funkciju ako želite. Ovdje je
DOWNLOAD datoteke
iz primjera
|