Šta treba znati
- Prvo kreirajte funkciju INDEX, a zatim pokrenite ugniježđenu funkciju MATCH unosom argumenta Lookup_value.
- Dalje, dodajte argument Lookup_array praćen argumentom Match_type, a zatim navedite raspon stupaca.
- Zatim, pretvorite ugniježđenu funkciju u formulu niza pritiskom na Ctrl+ Shift+ Enter. Konačno, dodajte pojmove za pretraživanje na radni list.
Ovaj članak objašnjava kako kreirati formulu za traženje koja koristi više kriterija u Excelu za pronalaženje informacija u bazi podataka ili tablici podataka korištenjem formule niza. Formula niza uključuje ugniježđenje funkcije MATCH unutar funkcije INDEX. Informacije pokrivaju Excel za Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 i Excel za Mac.
Pratite uz vodič
Da biste slijedili korake u ovom vodiču, unesite uzorke podataka u sljedeće ćelije, kao što je prikazano na slici ispod. Redovi 3 i 4 ostaju prazni da bi se prilagodila formula niza kreirana tokom ovog vodiča. (Imajte na umu da ovaj vodič ne uključuje formatiranje koje se vidi na slici.)
- Unesite gornji raspon podataka u ćelije D1 do F2.
- Unesite drugi raspon u ćelije D5 do F11.
Kreirajte funkciju INDEX u Excelu
Funkcija INDEX je jedna od rijetkih funkcija u Excelu koja ima više oblika. Funkcija ima formu polja i referentnu formu. Obrazac niza vraća podatke iz baze podataka ili tablice podataka. Referentni obrazac daje referencu ćelije ili lokaciju podataka u tabeli.
U ovom tutorijalu, obrazac niza se koristi za pronalaženje imena dobavljača za titanijumske widgete, umjesto reference ćelije za ovog dobavljača u bazi podataka.
Slijedite ove korake da kreirate funkciju INDEX:
- Odaberite ćeliju F3 da biste je učinili aktivnom ćelijom. U ovu ćeliju će se unijeti ugniježđena funkcija.
-
Idite na Formule.
Image - Odaberite Lookup & Reference da otvorite padajuću listu funkcija.
- Odaberite INDEX da otvorite dijaloški okvir Odaberite argumente.
- Odaberite array, row_num, column_num.
- Izaberite OK da otvorite Function Arguments dijaloški okvir. U programu Excel za Mac otvara se Formula Builder.
- Postavite kursor u okvir za tekst Array.
-
Označite ćelije D6 do F11 na radnom listu da unesete opseg u dijaloški okvir.
Ostavite dijalog Argumenti funkcije otvorenim. Formula nije gotova. Formulu ćete ispuniti u uputama ispod.
Image
Pokreni ugniježđenu funkciju MATCH
Kada ugniježdite jednu funkciju unutar druge, nije moguće otvoriti drugu ili ugniježđenu formulu funkcije za unos potrebnih argumenata. Ugniježđena funkcija se mora unijeti kao jedan od argumenata prve funkcije.
Kada se funkcije unose ručno, argumenti funkcije su odvojeni jedan od drugog zarezom.
Prvi korak za unos ugniježđene funkcije MATCH je unos argumenta Lookup_value. Lookup_value je lokacija ili referenca ćelije za pojam za pretraživanje koji se podudara u bazi podataka.
Lookup_value prihvata samo jedan kriterij pretraživanja ili termin. Da biste tražili više kriterija, proširite Lookup_value spajanjem ili spajanjem dvije ili više referenci ćelija pomoću simbola ampersanda (&).
- U dijaloškom okviru Function Arguments, postavite kursor u okvir za tekst Row_num.
-
Unesite MATCH(.
- Odaberite ćeliju D3 da unesete tu referencu ćelije u dijaloški okvir.
- Unesite & (ampersand) nakon reference ćelije D3 da dodate drugu referencu ćelije.
- Odaberite ćeliju E3 da unesete drugu referencu ćelije.
-
Enter , (zarez) nakon reference ćelije E3 da dovršite unos argumenta Lookup_value funkcije MATCH.
Image U posljednjem koraku tutorijala, Lookup_values će biti unesene 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.
Pošto su dva polja pretraživanja identificirana u argumentu Lookup_array, isto se mora učiniti i za Lookup_array. Funkcija MATCH pretražuje samo jedan niz za svaki specificirani termin. Da unesete više nizova, koristite ampersand da spojite nizove zajedno.
- Postavite kursor na kraj podataka u tekstualni okvir Row_num. Kursor se pojavljuje iza zareza na kraju trenutnog unosa.
- Označite ćelije D6 do D11 u radnom listu da unesete raspon. Ovaj raspon je prvi niz koji funkcija traži.
- Unesite & (ampersand) nakon referenci ćelije D6:D11. Ovaj simbol uzrokuje funkciju da traži dva niza.
- Označite ćelije E6 do E11 u radnom listu da unesete raspon. Ovaj raspon je drugi niz koji funkcija traži.
-
Unesite , (zarez) nakon reference ćelije E3 da dovršite unos argumenta Lookup_array funkcije MATCH.
Image - Ostavite dijaloški okvir otvoren 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 sa vrijednostima u Lookup_arrayu. 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 ili jednaka Lookup_value. Podaci Lookup_array moraju biti sortirani uzlaznim redoslijedom.
- Ako je tip_podudaranja=0, MATCH pronalazi prvu vrijednost koja je jednaka Lookup_value. Podaci Lookup_array se mogu sortirati bilo kojim redoslijedom.
- Ako je tip_podudaranja=-1, MATCH pronalazi najmanju vrijednost koja je veća ili jednaka Lookup_value. Podaci Lookup_array moraju biti sortirani u opadajućem redoslijedu.
Unesite ove korake nakon zareza unesenog u prethodnom koraku na liniji Row_num u funkciji INDEX:
- Unesite 0 (nula) iza zareza u Row_num okvir za tekst. Ovaj broj uzrokuje da ugniježđena funkcija vrati tačna podudaranja sa terminima unesenim u ćelije D3 i E3.
-
Unesite ) (završna okrugla zagrada) da završite funkciju MATCH.
Image - Ostavite dijaloški okvir otvoren za sljedeći korak u vodiču.
Završite funkciju INDEX
Funkcija MATCH je gotova. Vrijeme je da pređete na tekstni okvir Column_num u dijaloškom okviru i unesete posljednji argument za funkciju INDEX. Ovaj argument govori Excelu da je broj kolone u rasponu od D6 do F11. Ovaj raspon je mjesto gdje pronalazi informacije koje vraća funkcija. U ovom slučaju, dobavljač za titanijumske widgete.
- Postavite kursor u Column_num okvir za tekst.
-
Unesite 3 (broj tri). Ovaj broj govori formuli da traži podatke u trećoj koloni opsega D6 do F11.
Image - Ostavite dijaloški okvir otvoren za sljedeći korak u vodiču.
Kreirajte formulu niza
Prije zatvaranja dijaloškog okvira, pretvorite ugniježđenu funkciju u formulu niza. Ovaj niz omogućava funkciji da traži više pojmova u tabeli podataka. U ovom tutorijalu, dva termina se podudaraju: Widgeti iz kolone 1 i Titanium iz kolone 2.
Da kreirate formulu niza u Excelu, pritisnite CTRL, SHIFT i ENTERtastera istovremeno. Jednom pritisnuta, funkcija je okružena vitičastim zagradama, što ukazuje da je funkcija sada niz.
- Odaberite OK da zatvorite okvir za dijalog. U programu Excel za Mac odaberite Gotovo.
- Odaberite ćeliju F3 da vidite formulu, a zatim postavite kursor na kraj formule u traci formule.
- Da konvertujete formulu u niz, pritisnite CTRL+ SHIFT+ ENTER.
- A N/A greška se pojavljuje u ćeliji F3. Ovo je ćelija u koju je funkcija unesena.
-
Greška N/A pojavljuje se u ćeliji F3 jer su ćelije D3 i E3 prazne. D3 i E3 su ćelije u kojima funkcija traži da pronađe Lookup_value. Nakon što se podaci dodaju 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 na radni list. Ovaj korak odgovara pojmovima Widgeti iz kolone 1 i Titanium iz kolone 2.
Ako formula pronađe podudaranje za oba pojma u odgovarajućim kolonama u bazi podataka, vraća vrijednost iz treće kolone.
- Odaberite ćeliju D3.
- Unesite Widgets.
- Odaberite ć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.
-
Odaberite ć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 titanijumske widgete. Ako je postojalo više od jednog dobavljača, funkcija vraća dobavljača koji je prvi u bazi podataka.
Image
