IZBORNIK HOME FORUM ACCESS EXCEL WORD KAZALO
  HOME
  POPIS zadataka
   
- Excel FORMULE
- Excel FUNKCIJE
 
.

MICROSOFT EXCEL - primjeri zadataka za ECDL

Primjeri zadataka u excelu
(Kako funkcionira INDEX-MATCH formula)







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)

Pretraživanje stupca uz uvjet, Indirect, Address i Match funkcije u Excelu

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