Pretraživanje raspona podataka uz uvjet - criteria (kako vratiti
podatak iz ćelije drugog stupca u odnosu na uvjet
U ovom zadatku se traži rezultat
na osnovu uvjeta koji postavimo. Ovdje je prikazan jedan
uvjet. Zadatak možemo riješiti na više načina no ja ću
pokazati dva načina, koje ću malo
podrobnije razložiti na dijelove i pojasniti što koji dio
formule radi. Problem zadataka rješavat ću:
- pomoću funkcije
INDIRECT
- pomoću funkcije
ADDRESS
-
pomoću funkcije
MATCH
Situacija je kao na slikama ispod: dakle imamo dva stupca
sa određenim rasponima (range) podataka i dvije varijante
rasporeda naslova stupaca. Formulu možemo kreirati tako da
koristimo raspon podataka sa
apsolutnim adresama
ili
imenovanim rasponom
A = student
B = bodovi
A = bodovi
B = student
u ćeliji G2 tražimo rezultat uz uvjet iz F2. Dakle
tražimo ime studenta koji ima 14 bodova.
Prva situacija
|
Druga situacija
(obrnut redoslijed stupaca)
|
Kao prvo idemo imenovati naše
određene raspone podataka radi lakšeg rada i čitljivijih
formula. Nadam se da znate, ako ne pogledajte
kako se imenuju rasponi
podataka.
prva situacija (lijeva
slika iznad)
- sve podatke u stupcu "B" uključujući i naslov imenovat ćemo nazivom "bodovi"
=> to je raspon ćelija B1:B9
druga situacija (desna
slika iznad)
- sve podatke u stupcu "A" uključujući i naslov imenovat ćemo nazivom "bodovi"
=> to je raspon ćelija A1:A9
Postavljate pitanje, zašto sam
imenovao samo stupac u kojem su "bodovi"? Zato što su nam
podaci koji se nalaze u njemu UVJET a podatak iz drugog
stupca bilo da je ispred ili iza njega ćemo dobiti funkcijom INDIRECT i ADDRESS.
Pretraživanje dva stupca
uz uvjet kada je rezultat u stupcu ispred stupca u kojem se
nalazi uvjet (criteria)
Za rješavanje prve
situacije kada nam je traženi rezultat u stupcu
ispred stupca u kojem se nalazi uvjet upotrijebiti ćemo
formulu
=INDIRECT(ADDRESS(MATCH(F2;$B$1:$B$9;0);1;4))
Ako raščlanimo ovu formulu iznad
na tri formule (tj. tri funkcije) tada možemo uočiti
slijedeće rezultate.
=MATCH(F2;$B$1:$B$9;0) => rezultat je 6
Ovu formulu mogli smo pisati i
kao =MATCH(F2;bodovi;0) jer za raspon B1:B9
definirali smo ime "bodovi"
Funkcija
MATCH ime
slijedeću sintaxu:
MATCH(lookup_value;lookup_array;match_type)
- prvi parametar (argument) je podatak koji tražimo (naš
uvjet) ili "lookup_value",
- slijedeći drugi parametar (argument) je
"lookup_array" ili raspon (range)
koji pretražujemo
- i na kraju je treći parametar (argument) "match_type",
koji može biti -1, 0, 1
Ako je "match_type" 0, MATCH
nalazi prvu vrijednost koja je identična
"lookup_value". Lookup_array
može biti u bilo kojem poretku, za ostale pogledajte link
MATCH.
Ova funkcija tj. formula daje
nam rezultat broj šest (6). Ovaj broj 6 označava broj
retka (počevši od jedan (1) pa nadalje) u kojem se nalazi
naš uvjet stupca "bodovi".
Zašto broj 6? Zato što je to
redni broj retka raspona podataka (range) koji sam imenovao
nazivom "bodovi" (B1:B9). Dakle ja sam u obzir uzeo i naslov
stupca što i nije baš najsretnije rješenje. Da sam koristio
raspon podataka bez naslova stupca tada bi ovdje bio
rezultat broj retka 5. Ovo je malo pojašnjeno u drugom
načinu rješavanja ovog zadatka.
=ADDRESS(MATCH(F2;$B$1:$B$9;0);1;4)
=> rezultat je
A6
Funkcija
ADDRESS ime
slijedeću sintaxu: ADDRESS(row_num;column_num;abs_num;a1;sheet_text)
ili
=ADDRESS(broj_reda;redniBroj_stupca_u_adresi_ćelije;rezultat=relativna_adresa_ćelije)
- prvi parametar (argument) "row_num"
je podatak koji je dala funkcija MATCH, Dakle umjesto
parametra "row_num" možemo
upisati rezultat koji nam je dala funkcija MATCH (broj retka
6)
- drugi parametar (argument) "column_num"
je nađen pomoću broja 1
u formuli jer je to redni broj stupca počevši brojati od
prvog stupca a to je u ovom slučaju stupac A. Zašto sam
upisao broj 1? Pa zato što rezultat tražim u prvom stupcu
koji ima redni broj 1. Nisam mogao upisati broj 2 jer je to
stupac B a u drugom stupcu nalazi naš uvjet a također ni
broj 3 jer je to stupac C i u njemu nema ništa.
- treći parametar (argument) "abs_num"
je po pod-parametar (pod-argument) koji možete vidjeti u
tablici na linku
ADDRESS,
a u ovom slučaju je to broj 4 koji označava da je u pitanju
relativna adresa ćelije.
Za ostale parametre (argumente) funkcije koje ovdje
ne koristimo pogledajte link
ADDRESS
Ova funkcija tj. formula daje
nam rezultat relativnu adresu ćelije a to je A6. To
znači da se naš rezultat koji tražimo nalazi u ćelji A6.
=INDIRECT(ADDRESS(MATCH(F2;$B$1:$B$9;0);1;4))
=> rezultat je
IvanC
Funkcija
INDIRECT ime
slijedeću sintaxu: =ADDRESS(ref_text;A1)
ili =ADDRESS(adresa_ćelije)
- parametar (argument) "adresa_ćelije"
je podatak koji je kao rezultat dala funkcija ADDRESS, a
Addres funkcija je dala rezultat A6 što je
relativna adresa ćelije
u kojoj se nalazi naš podatak. Dakle ova funkcija INDIRECT
vraća kao rezultat, podatak iz dotične ćelije A6 što je u
ovom slučaju tekstni podatak IvanC. Mogli smo pisati i =INDIRECT(A6)
i rezultat je opet isti.
Pretraživanje dva stupca
uz uvjet kada je rezultat u stupcu iza stupca u kojem se
nalazi uvjet (criteria)
Za rješavanje druge
situacije kada nam je traženi rezultat u stupcu iza
stupca u kojem se nalazi uvjet upotrijebiti ćemo formulu
=INDIRECT(ADDRESS(MATCH(F2;$B$1:$B$9;0);2;4))
U situaciji kada nam se uvjet
nalazi ispred traženog rezultata, tada možemo upotrijebiti
istu formulu ali sa razlikom vrijednosti parametra
(argumenta) "column_num" u
formuli funkcije ADDRESS. Dakle upisat ćemo broj 2 (dva) jer
je to drugi stupac po redu u radnom listu, od početka A=1
B=2 i u tom stupcu 2 se nalazi naš traženi podatak tj.
rezultat.
ZAKLJUČAK:
Funkcija MATCH nam je poslužila
za pronalazak reda (retka) u kojem se nalazi naš uvjet =>
6
Funkcija ADDRESS nam je poslužila da pronađemo adresu ćelije
na osnovu retka 6 u stupcu 1 (u drugoj situaciji stupac 2)
=> A6
Funkcija INDIRECT nam je pomogla da na osnovu ćelije A6
"izvučemo" podatak koji se u njoj nalazi. => IvanC
I na kraju pogledajte kako se
raščlanjena formula može primijeniti po dijelovima funkcija
za svaku ćeliju posebno
DRUGI NAČIN RJEŠAVANJA
OVOG ZADATKA
U ovom drugom načinu problem zadataka rješavat ću:
- pomoću funkcije
INDEX
-
pomoću funkcije
MATCH
U ovom drugom načinu imenovao sam tri raspona podataka (range)
da bi bilo jasnije što se uzima u obzir za izračun zadatka. Za razliku od prvog primjera uočite da
sam ovdje izostavio adrese ćelija (red prvi) u kojima se
nalazi naslov stupca podataka što će se kasnije odraziti
na broj reda prilikom izračuna.
- student => A2:A9 => $A$2:$A$9
- bodovi => B2:B9 => $B$2:$B$9
- uvjet => F2 => $F$2
Naravno, ako radite sa rasponima podataka koje niste
imenovali trebate raspone podataka pisati sa
apsolutnim adresama ćelija
u rasponu podataka. (dakle ugraditi znakove $ (dolar) u
raspon podataka) |
|
U ovom slučaju naša formula glasi:
=INDEX($A$2:$A$9;MATCH(F2;$B$2:$B$9;0))
=INDEX(student;MATCH(uvjet;bodovi;0))
MATCH(F2;$B$2:$B$9;0)
=> rezultat je 5
MATCH(uvjet;bodovi;0)
Ovu formulu mogli smo pisati i
kao =MATCH(F2;bodovi;0) jer za raspon B2:B9
definirali smo ime "bodovi"
Funkcija
MATCH ime
slijedeću sintaxu:
MATCH(lookup_value;lookup_array;match_type)
- prvi parametar (argument) je podatak koji tražimo (naš
uvjet) ili "lookup_value",
- slijedeći drugi parametar (argument) je
"lookup_array" ili raspon (range)
koji pretražujemo
- i na kraju je treći parametar (argument) "match_type",
koji može biti -1, 0, 1
Ako je "match_type" 0, MATCH
nalazi prvu vrijednost koja je identična
"lookup_value". Lookup_array
može biti u bilo kojem poretku, za ostale pogledajte link
MATCH.
Ova funkcija tj. formula daje
nam rezultat broj pet (5). Ovaj broj 5 označava broj
retka (počevši od jedan (1) u rasponu podataka pa nadalje) u kojem se nalazi
naš uvjet stupca "bodovi".
B2 => 1 red
B3 => 2 red
B4 => 3 red
B5 => 4 red
B6 => 5 red
...
Zašto broj 5? Zato što je
to redni broj retka u rasponu podataka (range) koji sam
imenovao nazivom "bodovi" (B2:B9). Dakle u ovom slučaju sam
izostavio naslov stupca pa ako brojite redove od B2 prema
dolje dolazite do broja reda 5 u kojem se nalazi naš uvjet.
=INDEX($A$2:$A$9;MATCH)
=> rezultat je
IvanC
=INDEX(student;MATCH)
=INDEX(student;5)
Funkcija
INDEX ime
slijedeću sintaxu: (za više detalja pogledajte link
funkcije)
=INDEX(array;
row_num)
ili =INDEX(raspon podataka
student;broj reda koji je dala funkcija Match)
- parametar (argument) "array"
je raspon podataka stupca "student" i u njemu funkcija Index
traži podatak koji koji se nalazi u istom redu (broju reda)
koji je kao rezultat dala funkcija MATCH (a ona nam je
vratila razultat reda broj 5 (pet). Uočite da se u
petom redu stupca "student" nalazi naš traženi rezultat.
U ovom slučaju izostavljamo "column_num"
jer imamo "row_num"
U koliko zamijenimo redoslijed
stupaca, ništa se posebno neće promijeniti, dovoljno je samo
prilagoditi raspone podataka u formuli. Ako smo radili
formulu sa imenovanim opsegom tada ni to ne moramo
mijenjati. U ovom slučaju kada su stupci zamijenjeni naša
formula bi glasila ovako: =INDEX($B$2:$B$9;MATCH(F2;$A$2:$A$9;0))
U koliko želite, ova dva primjera možete downloadati u
datoteci
OVDJE koju
raspakirajte na svoj HDD
Kako pronaći jedan uvjet u
Excel tablici i vratiti sve pripadajuće vrijednosti iz
ostalih redova za zadani uvjet
U ovom primjeru imamo tablicu podataka kao bazu sa nekoliko
stupaca. Naš uvjet se ponavlja u prvom stupcu a u drugom i
trećem stupcu za isti uvjet ima više pripadajućih
vrijednosti. Potrebno je zadati uvjet a formula da nam vrati
sve pripadajuće vrijednosti ili podatke iz ostalih stupaca
po redovima i sve to izlistati bez praznih redova.
Pogledajte sliku ispod
Formula u F2 ćeliji za ovaj primjer je
ARRAY a izgleda
ovako.
=IF(ISERROR(INDEX($A$1:$C$8;SMALL(IF($A$1:$A$8=$E$1;ROW($A$1:$A$8));ROW(1:1));COLUMN(B1)));"";INDEX($A$1:$C$8;SMALL(IF($A$1:$A$8=$E$1;ROW($A$1:$A$8));ROW(1:1));COLUMN(B1)))
Kopiraj se u desnu stranu i prema dolje.
Formula u J2 izgleda ovako:
=IF(ISERROR(INDEX($A$1:$C$8;SMALL(IF($A$1:$A$8=$I$1;ROW($A$1:$A$8));ROW(1:1));COLUMN(B1)));"";INDEX($A$1:$C$8;SMALL(IF($A$1:$A$8=$I$1;ROW($A$1:$A$8));ROW(1:1));COLUMN(B1)))
Također pogledajte tutorijale:
- Jedna formula INDEX-MATCH za kopiranje u desnu stranu koja pronalazi sjecište za više stupaca i redova
- Automatsko označavanje reda i stupca bojom nakon selektiranja ćelije
- Kako prikazati zaglavlje retka i stupca ako nam je poznat podatak iz sjecišta
- Dva uvjeta, zaglavlje retka i stupca vrati podatak ili vrijednost iz sjecišta
- Kako pronaći podatak u tablici uz 2 uvjeta
- Kako pronaći jedan uvjet u Excel tablici i vratiti sve pripadajuće vrijednosti iz ostalih redova za zadani uvj
Ova web stranica koristi COOKIES - COPYRIGHT 2008 - 2021 - IvanC - ic.ims.hr |