Pretraživanje raspona podataka uz dva uvjeta
U ovom zadatku se traži rezultat na
osnovu uvjeta koji postavimo. Ovdje je opisano dva uvjeta;
"prvi uvjet" i "drugi uvjet". Zadatak možemo riješiti
na više načina i to pomoći slijedećih funkcija:
- pomoću funkcije
SUMPRODUCT
- pomoću funkcije
INDEX -
MATCH
- pomoću funkcije
VLOOKUP i dva
pomoćna stupca
- pomoću funkcije
IF -
ISERROR -
VLOOKUP i pomoćnog
stupca
Situacija je kao na slici ispod: dakle u stupcu F tražimo
rezultat (iz C) uz prvi i drugi uvjet (D i E) koji
odgovara stupcima A i B
Kao prvo idemo imenovati naše
određene raspone podataka radi lakšeg rada i čitljivijih
formula. Nadam se da znate
kako se imenuju rasponi
podataka.
dakle
- dio podataka u stupcu "A" imenovat ćemo nazivom "prvi" =>
to je raspon ćelija A2:A4
- dio podataka u stupcu "B" imenovat ćemo nazivom "drugi" =>
to je raspon ćelija B2:B4
- dio podataka u stupcu "C" imenovat ćemo nazivom "broj" =>
to je raspon ćelija C2:C4
Inače ako ne želite imenovati
raspone podataka tada ćete morati za određene raspone
podataka u formuli kreirati apsolutne raspone podataka (apsolutna
adresa).
Za primjer uočite slijedeće: Raspon "broj" je isto što i
$C$2:$C$4. Ovo je važno kod kopiranja formule u ostale
ćelije, tj. da se ne remeti adresni raspon podataka.
Pogledajte sliku ispod.
Pretraživanje raspona podataka uz dva uvjeta pomoću
funkcije
SUMPRODUCT
U prvom rješavanju zadatka koristit ćemo Excelovu
funkciju SUMPRODUCT. Formule za ovo rješenje glase:
=SUMPRODUCT((E2=$A$2:$A$4)*(D2=$B$2:$B$4)*($C$2:$C$4))
=SUMPRODUCT((E3=prvi)*(D3=drugi)*(broj))
Mali dodatak, zašto je u formuli E2=$A$2:$A$4? Zato što
drugi uvjet traži podatke u A stupcu (identične oznake)
Pretraživanje raspona podataka uz dva uvjeta pomoću
funkcije
INDEX i MATCH
U ovom rješavanju zadatka koristit ćemo Excelove funkcije
INDEX i MATCH. Formule za ovo rješenje glase:
=INDEX($C$2:$C$4;MATCH(E2;$A$2:$A$4;0);MATCH($D$2;$B$2:$B$4;0))
=INDEX(broj;MATCH(E2;prvi;0);MATCH($D$2;drugi;0))
Pretraživanje raspona podataka uz dva uvjeta pomoću
funkcije VLOOKUP i dva
pomoćna stupca
U ovom rješavanju zadatka koristit ćemo Excelovu
funkciju VLOOKUP. Osim funkcije Vlookup u pomoćnim stupcima
iskoristiti ćemo znak &
za spajanje dviju ćelija koje su bitne za rezultat i
usporedbu. Uz to imenovat ćemo raspon podataka C2:D4 nazivom
"podaci".
Formule za ovo rješenje glase:
- pomoćni stupac "C" => =A2&B2
- pomoćni stupac "G" => =F2&E2
- rezultat u stupcu H => =VLOOKUP(G2;$C$2:$D$4;2;FALSE)
ili =VLOOKUP(G2;podaci;2;FALSE)
Uočite da je u formuli uvjet stupac G koji se uspoređuje
sa stupcom C i vraća rezultat stupca D a stupac D je broj 2
u formuli jer je to drugi stupac u rasponu podataka "podaci"
Pretraživanje raspona podataka uz dva uvjeta pomoću
funkcije
IF - ISERROR - VLOOKUP i jednim pomoćnim stupcem
U ovom rješavanju zadatka koristit ćemo Excelovu
funkciju IF u koju ćemo ugraditi ISERROR i VLOOKUP uz jedan
pomoćni stupac.
Formule za ovo rješenje glase:
- pomoćni stupac "C" => =A2&B2
=IF(ISERROR(VLOOKUP(F2&E2;$C$2:$D$4;2;FALSE));"";VLOOKUP(F2&E2;$C$2:$D$4;2;FALSE))
=IF(ISERROR(VLOOKUP(F2&E2;podaci;2;FALSE));"";VLOOKUP(F2&E2;podaci;2;FALSE))
I za kraj rezime, U ovim primjerima mogli ste uočiti kako
se jedan zadatak može riješiti na više načina, naravno vi
možete kombinirati po svojoj volji i kreirati samostalno
redoslijed uvjeta, stupaca itd... Be creative
Pretraživanje raspona podataka uz dva uvjeta pomoću
funkcije
SUM i IF
i
SUMIFS
U ovom primjeru koristit ćemo funkcije SUM i IF a formulu
ćemo završiti kao formulu polja (ARRAY)
sa CTRL+SHIFT+ENTER
Cilj je na osnovu uvjeta u stupcima A i B tablice 1,
pronaći odgovarajuću vrijednost iz stupca H tablice 2
Formula za ovo rješenje glasi:
=SUM(IF($F$1:$F$7=A1;IF($G$1:$G$7=B1;$H$1:$H$7;0)))
=> Ctrl+Shift+Enter
Ovaj zadatak možemo riješiti i sa funkcijom SUMIFS
(Excel 2007). U ovom
slučaju će funkcija pronaći u odnosu na uvjete podatak koji
im odgovara u trećem stupcu. Ovu formulu možemo upotrijebiti
i za zbrajanje više vrijednosti u trećem stupcu, kada bi se
poklopili uvjeti u više redova)
=SUMIFS($H$1:$H$7;$F$1:$F$7;A1;$G$1:$G$7;B1)
=SUMIFS(sum_range;criteria_range1;criteria1;criteria_range2;criteria2) |
|
Ova web stranica koristi COOKIES - COPYRIGHT 2008 - 2021 - IvanC - ic.ims.hr |