Što treba znati
- Prvo stvorite funkciju INDEX, zatim pokrenite ugniježđenu funkciju MATCH unosom argumenta Lookup_value.
- Zatim dodajte argument Lookup_array nakon čega slijedi argument Match_type, zatim navedite raspon stupaca.
- Zatim pretvorite ugniježđenu funkciju u formulu polja pritiskom na Ctrl+ Shift+ Enter JPY. Na kraju dodajte pojmove za pretraživanje na radni list.
Ovaj članak objašnjava kako stvoriti formulu pretraživanja koja koristi više kriterija u Excelu za pronalaženje informacija u bazi podataka ili tablici podataka pomoću formule polja. Formula polja uključuje ugniježđivanje funkcije MATCH unutar funkcije INDEX. Informacije pokrivaju Excel za Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 i Excel za Mac.
Pratite upute
Da biste slijedili korake u ovom vodiču, unesite ogledne podatke u sljedeće ćelije, kao što je prikazano na slici ispod. Redovi 3 i 4 ostavljeni su prazni kako bi se smjestila formula polja stvorena tijekom ovog vodiča. (Imajte na umu da ovaj vodič ne uključuje oblikovanje koje se vidi na slici.)
- Unesite gornji raspon podataka u ćelije D1 do F2.
- Unesite drugi raspon u ćelije D5 do F11.
Stvorite funkciju INDEX u programu Excel
Funkcija INDEX jedna je od rijetkih funkcija u Excelu koja ima više oblika. Funkcija ima obrazac polja i obrazac reference. Array Form vraća podatke iz baze podataka ili tablice podataka. Referentni obrazac daje referencu ćelije ili lokaciju podataka u tablici.
U ovom vodiču, Array Form se koristi za pronalaženje imena dobavljača za titanium widgete, umjesto reference ćelije na ovog dobavljača u bazi podataka.
Slijedite ove korake za izradu funkcije INDEX:
- Odaberite ćeliju F3 kako biste je učinili aktivnom ćelijom. Ova ćelija je mjesto gdje će se unijeti ugniježđena funkcija.
-
Idite na Formule.
Image - Odaberite Traženje i referenca za otvaranje padajućeg popisa funkcija.
- Odaberite INDEX za otvaranje Odaberite argumente dijaloški okvir.
- Odaberite niz, broj_reda, broj_stupca.
- Odaberite OK za otvaranje dijaloškog okvira Argumenti funkcije. U programu Excel za Mac otvara se alat za sastavljanje formula.
- Postavite kursor u Array tekstualni okvir.
-
Označite ćelije D6 do F11 na radnom listu za unos raspona u dijaloški okvir.
Ostavite dijaloški okvir Argumenti funkcije otvorenim. Formula nije gotova. Formulu ćete dovršiti prema uputama u nastavku.
Image
Pokretanje ugniježđene funkcije MATCH
Prilikom ugniježđivanja jedne funkcije u drugu, nije moguće otvoriti alat za izgradnju formule druge ili ugniježđene funkcije za unos potrebnih argumenata. Ugniježđena funkcija mora se unijeti kao jedan od argumenata prve funkcije.
Prilikom ručnog unosa funkcija, argumenti funkcije međusobno su odvojeni zarezom.
Prvi korak za unos ugniježđene funkcije MATCH je unos argumenta Lookup_value. Lookup_value je lokacija ili referenca ćelije za traženi pojam koji se podudara u bazi podataka.
Lookup_value prihvaća samo jedan kriterij ili pojam pretraživanja. Za traženje više kriterija, proširite Lookup_value ulančavanjem ili spajanjem dviju ili više referenci ćelija pomoću simbola ampersend (&).
- U dijaloškom okviru Argumenti funkcije, postavite kursor u Row_num tekstualni okvir.
-
Unesite MATCH(.
- Odaberite ćeliju D3 za unos reference te ćelije u dijaloški okvir.
- Unesite & (ampersand) iza reference ćelije D3 da biste dodali drugu referencu ćelije.
- Odaberite ćeliju E3 za unos druge reference ćelije.
-
Unesite , (zarez) nakon reference ćelije E3 da dovršite unos argumenta Lookup_value funkcije MATCH.
Image U posljednjem koraku vodiča, Lookup_values će se unijeti u ćelije D3 i E3 radnog lista.
Dovršite ugniježđenu funkciju MATCH
Ovaj korak pokriva dodavanje argumenta Lookup_array za ugniježđenu funkciju MATCH. Lookup_array je raspon ćelija koje funkcija MATCH pretražuje kako bi pronašla argument Lookup_value dodan u prethodnom koraku vodiča.
Budući da su dva polja za pretraživanje identificirana u argumentu Lookup_array, isto se mora učiniti za Lookup_array. Funkcija MATCH pretražuje samo jedno polje za svaki navedeni izraz. Za unos višestrukih nizova upotrijebite ampersand za spajanje nizova zajedno.
- Postavite kursor na kraj podataka u Row_num tekstnom okviru. Kursor se pojavljuje nakon zareza na kraju trenutnog unosa.
- Označite ćelije D6 do D11 na radnom listu za unos raspona. Ovaj raspon je prvi niz koji funkcija pretražuje.
- Unesite & (znak &) nakon reference ćelije D6:D11. Ovaj simbol uzrokuje da funkcija pretražuje dva niza.
- Označite ćelije E6 do E11 na radnom listu za unos raspona. Ovaj raspon je drugi niz koji funkcija pretražuje.
-
Unesite , (zarez) nakon reference ćelije E3 kako biste dovršili unos argumenta Lookup_array funkcije MATCH.
Image - Ostavite dijaloški okvir otvorenim za sljedeći korak u vodiču.
Dodajte argument tipa MATCH
Treći i posljednji argument funkcije MATCH je argument Match_type. Ovaj argument govori Excelu kako uskladiti Lookup_value s vrijednostima u Lookup_array. Dostupni izbori su 1, 0 ili -1.
Ovaj argument nije obavezan. Ako je izostavljen, funkcija koristi zadanu vrijednost 1.
- Ako je Match_type=1 ili je izostavljen, MATCH pronalazi najveću vrijednost koja je manja od ili jednaka Lookup_value. Podaci Lookup_array moraju biti poredani uzlaznim redoslijedom.
- Ako je Match_type=0, MATCH pronalazi prvu vrijednost koja je jednaka Lookup_value. Podaci Lookup_array mogu se poredati bilo kojim redoslijedom.
- Ako je Match_type=-1, MATCH pronalazi najmanju vrijednost koja je veća ili jednaka Lookup_value. Podaci Lookup_array moraju biti poredani silaznim redoslijedom.
Unesite ove korake nakon zareza unesenog u prethodnom koraku u retku Row_num u funkciji INDEX:
- Unesite 0 (nula) nakon zareza u Row_num tekstualni okvir. Ovaj broj uzrokuje da ugniježđena funkcija vrati točna podudaranja s pojmovima unesenim u ćelije D3 i E3.
-
Unesite ) (zatvarajuća okrugla zagrada) da dovršite funkciju MATCH.
Image - Ostavite dijaloški okvir otvorenim za sljedeći korak u vodiču.
Dovršite funkciju INDEX
Funkcija MATCH je gotova. Vrijeme je da prijeđete na tekstni okvir Column_num dijaloškog okvira i unesete posljednji argument za funkciju INDEX. Ovaj argument govori Excelu da je broj stupca u rasponu od D6 do F11. Ovaj raspon je mjesto gdje se pronalaze informacije koje vraća funkcija. U ovom slučaju, dobavljač za widgete od titana.
- Postavite kursor u Column_num tekstualni okvir.
-
Unesite 3 (broj tri). Ovaj broj govori formuli da traži podatke u trećem stupcu raspona od D6 do F11.
Image - Ostavite dijaloški okvir otvorenim za sljedeći korak u vodiču.
Stvorite formulu polja
Prije zatvaranja dijaloškog okvira pretvorite ugniježđenu funkciju u formulu polja. Ovaj niz omogućuje funkciji traženje više pojmova u tablici podataka. U ovom vodiču se podudaraju dva pojma: Widgeti iz stupca 1 i Titanium iz stupca 2.
Da biste stvorili formulu polja u Excelu, pritisnite CTRL, SHIFT i ENTERtipke istovremeno. Nakon pritiska, funkcija je okružena vitičastim zagradama, što znači da je funkcija sada niz.
- Odaberite OK za zatvaranje dijaloškog okvira. U programu Excel za Mac odaberite Gotovo.
- Odaberite ćeliju F3 za prikaz formule, zatim postavite pokazivač na kraj formule u traci formule.
- Za pretvaranje formule u polje pritisnite CTRL+ SHIFT+ ENTER.
- A N/A pogreška se pojavljuje u ćeliji F3. Ovo je ćelija u koju je unesena funkcija.
-
Pogreška N/A pojavljuje se u ćeliji F3 jer su ćelije D3 i E3 prazne. D3 i E3 ćelije su u kojima funkcija traži Lookup_value. Nakon dodavanja podataka u ove dvije ćelije, greška se zamjenjuje informacijama iz baze podataka.
Image
Dodajte kriterije pretraživanja
Posljednji korak je dodavanje pojmova za pretraživanje u radni list. Ovaj korak odgovara pojmovima Widgets iz stupca 1 i Titanium iz stupca 2.
Ako formula pronađe podudaranje za oba pojma u odgovarajućim stupcima u bazi podataka, vraća vrijednost iz trećeg stupca.
- Odaberi ćeliju D3.
- Unesite Widgeti.
- Odaberi ćeliju E3.
- Upišite Titanium i pritisnite Enter.
- Ime dobavljača, Widgets Inc., pojavljuje se u ćeliji F3. Ovo je jedini navedeni dobavljač koji prodaje Titanium Widgete.
-
Odaberi ćeliju F3. Funkcija se pojavljuje u traci formule iznad radnog lista.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
U ovom primjeru postoji samo jedan dobavljač za widgete od titana. Ako je bilo više od jednog dobavljača, funkcija vraća dobavljača koji je prvi naveden u bazi podataka.
Image
