Kombinovanjem Excelove funkcije VLOOKUP sa funkcijom COLUMN možete kreirati formulu pretraživanja koja vraća više vrijednosti iz jednog reda baze podataka ili tablice podataka. Naučite kako kreirati formulu pretraživanja koja vraća više vrijednosti iz jednog zapisa podataka.
Uputstva u ovom članku odnose se na Excel 2019, 2016, 2013, 2010; i Excel za Microsoft 365.
Donja linija
Formula za traženje zahtijeva da funkcija COLUMN bude ugniježđena unutar VLOOKUP-a. Ugniježđenje funkcije uključuje unošenje druge funkcije kao jednog od argumenata za prvu funkciju.
Unesite podatke za tutorial
U ovom vodiču, funkcija COLUMN se unosi kao argument broja indeksa kolone za VLOOKUP. Posljednji korak u tutorijalu uključuje kopiranje formule za traženje u dodatne stupce kako bi se dohvatile dodatne vrijednosti za odabrani dio.
Prvi korak u ovom vodiču je unos podataka u Excel radni list. Da biste pratili korake u ovom vodiču, unesite podatke prikazane na slici ispod u sljedeće ćelije:
- Unesite gornji raspon podataka u ćelije D1 do G1.
- Unesite drugi raspon u ćelije D4 do G10.
Kriterijumi pretraživanja i formula za traženje kreirana u ovom vodiču se unose u red 2 radnog lista.
Ovaj vodič ne uključuje osnovno Excel formatiranje prikazano na slici, ali to ne utiče na način na koji formula za traženje funkcionira.
Kreirajte imenovani raspon za tablicu podataka
Imenovani opseg je jednostavan način da se pozovete na opseg podataka u formuli. Umjesto da kucate reference ćelije za podatke, upišite naziv raspona.
Druga prednost korištenja imenovanog raspona je da se reference ćelija za ovaj raspon nikada ne mijenjaju čak ni kada se formula kopira u druge ćelije na radnom listu. Nazivi raspona su alternativa korištenju apsolutnih referenci ćelija kako bi se spriječile greške prilikom kopiranja formula.
Naziv raspona ne uključuje naslove ili nazive polja za podatke (kao što je prikazano u redu 4), samo podatke.
-
Označite ćelije D5 do G10 na radnom listu.
Image -
Postavite kursor u polje za naziv koji se nalazi iznad kolone A, otkucajte Table, zatim pritisnite Enter. Ćelije D5 do G10 imaju naziv raspona Table.
Image - Ime raspona za argument niza tablice VLOOKUP se koristi kasnije u ovom vodiču.
Otvorite dijaloški okvir VLOOKUP
Iako je moguće ukucati formulu za traženje direktno u ćeliju na radnom listu, mnogim ljudima je teško da sintaksu održe ravnom - posebno za složenu formulu kao što je ona koja se koristi u ovom vodiču.
Kao alternativu, koristite dijaloški okvir Argumenti funkcije VLOOKUP. Gotovo sve Excelove funkcije imaju dijaloški okvir u koji se svaki od argumenata funkcije unosi u poseban red.
-
Izaberite ćeliju E2 radnog lista. Ovo je lokacija na kojoj će se prikazati rezultati dvodimenzionalne formule pretraživanja.
Image -
Na traci idite na karticu Formule i odaberite Lookup & Reference.
Image -
Izaberite VLOOKUP da otvorite dijaloški okvir Function Arguments.
Image - Dialoški okvir Function Arguments je mjesto gdje se unose parametri funkcije VLOOKUP.
Unesite argument vrijednosti traženja
Uobičajeno, tražena vrijednost odgovara polju podataka u prvoj koloni tabele podataka. U ovom primjeru, vrijednost traženja se odnosi na ime dijela u kojem želite pronaći informaciju. Dozvoljeni tipovi podataka za vrijednost pretraživanja su tekstualni podaci, logičke vrijednosti, brojevi i reference ćelije.
Apsolutne reference ćelije
Kada se formule kopiraju u Excel, reference ćelija se mijenjaju kako bi odražavale novu lokaciju. Ako se to dogodi, D2, referenca ćelije za traženu vrijednost, mijenja se i stvara greške u ćelijama F2 i G2.
Apsolutne reference ćelije se ne mijenjaju kada se formule kopiraju.
Da biste spriječili greške, pretvorite referencu ćelije D2 u apsolutnu referencu ćelije. Da biste kreirali apsolutnu referencu ćelije, pritisnite tipku F4. Ovo dodaje znakove dolara oko reference ćelije kao što je $D$2.
-
U dijalogu Function Arguments, postavite kursor u okvir za tekst lookup_value. Zatim, na radnom listu, izaberite cell D2 da dodate ovu referencu ćelije lookup_value. Ćelija D2 je mjesto gdje će se unijeti naziv dijela.
Image -
Bez pomeranja tačke umetanja, pritisnite taster F4 da konvertujete D2 u apsolutnu referencu ćelije $D$2.
Image - Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sljedeći korak u vodiču.
Unesite argument polja tabele
Tabelarni niz je tabela podataka koju formula za pretraživanje pretražuje kako bi pronašla informacije koje želite. Niz tablice mora sadržavati najmanje dvije kolone podataka.
Prva kolona sadrži argument vrijednosti traženja (koji je postavljen u prethodnom odjeljku), dok se druga kolona pretražuje po formuli pretraživanja kako bi se pronašle informacije koje navedete.
Argument polja tabele se mora uneti ili kao opseg koji sadrži reference ćelija za tabelu podataka ili kao ime opsega.
Da dodate tabelu podataka funkciji VLOOKUP, postavite kursor u table_array tekstualni okvir u dijaloškom okviru i upišite Tableda unesete naziv raspona za ovaj argument.
Ugniježdite funkciju COLUMN
Uobičajeno, VLOOKUP vraća podatke samo iz jedne kolone tabele podataka. Ovaj stupac je postavljen argumentom broj indeksa stupca. U ovom primjeru, međutim, postoje tri kolone, a broj indeksa stupca treba promijeniti bez uređivanja formule za traženje. Da biste to postigli, ugniježdite funkciju COLUMN unutar funkcije VLOOKUP kao argument Col_index_num.
Kada ugnežđuje funkcije, Excel ne otvara dijaloški okvir druge funkcije da bi uneo njene argumente. Funkcija COLUMN se mora unijeti ručno. Funkcija COLUMN ima samo jedan argument, Referentni argument, koji je referenca ćelije.
Funkcija COLUMN vraća broj kolone koja je data kao referentni argument. Konvertuje slovo kolone u broj.
Da biste pronašli cijenu artikla, koristite podatke u koloni 2 tabele sa podacima. Ovaj primjer koristi kolonu B kao referencu za umetanje 2 u argument Col_index_num.
-
U dijaloškom okviru Function Arguments, postavite kursor u okvir za tekst Col_index_num i upišite COLUMN(. (Obavezno uključite otvorenu okruglu zagradu.)
Image -
U radnom listu izaberite ćelija B1 da unesete tu referencu ćelije kao argument reference.
Image - Upišite zatvornu okruglu zagradu da dovršite funkciju COLUMN.
Unesite argument za traženje opsega VLOOKUP
VLOOKUP-ov argument Range_lookup je logička vrijednost (TRUE ili FALSE) koja ukazuje da li VLOOKUP treba pronaći tačno ili približno podudaranje sa Lookup_value.
- TRUE ili Izostavljeno: VLOOKUP vraća blisko podudaranje sa Lookup_value. Ako nije pronađeno tačno podudaranje, VLOOKUP vraća sljedeću najveću vrijednost. Podaci u prvoj koloni Table_array moraju biti sortirani uzlaznim redoslijedom.
- FALSE: VLOOKUP koristi tačno podudaranje sa Lookup_value. Ako postoje dvije ili više vrijednosti u prvom stupcu Table_array koje odgovaraju vrijednosti pretraživanja, koristi se prva pronađena vrijednost. Ako se ne pronađe tačno podudaranje, vraća se greška N/A.
U ovom vodiču će se tražiti specifične informacije o određenoj hardverskoj stavci, tako da je Range_lookup postavljen na FALSE.
U dijalogu Function Arguments, postavite kursor u okvir za tekst Range_lookup i upišite False da kažete VLOOKUP-u da vrati tačno podudaranje za podatke.
Odaberite OK da dovršite formulu za traženje i zatvorite okvir za dijalog. Ćelija E2 će sadržavati grešku N/A jer kriteriji traženja nisu uneseni u ćeliju D2. Ova greška je privremena. Biće ispravljeno kada se kriterijumi traženja dodaju u poslednjem koraku ovog uputstva.
Kopiraj formulu za traženje i unesi kriterije
Formula za traženje preuzima podatke iz više kolona tabele podataka u isto vrijeme. Da biste to učinili, formula za traženje mora se nalaziti u svim poljima iz kojih želite informacije.
Da biste preuzeli podatke iz kolona 2, 3 i 4 tabele podataka (cijena, broj dijela i ime dobavljača), unesite djelomično ime kao Lookup_value.
Pošto su podaci postavljeni u regularnom uzorku na radnom listu, kopirajte formulu za traženje u ćeliju E2 u ćelije F2 i G2 Kako se formula kopira, Excel ažurira relativnu referencu ćelije u funkciji COLUMN (ćelija B1) da odražava novu lokaciju formule. Excel ne mijenja apsolutnu referencu ćelije (kao što je $D$2) i imenovani raspon (Tabela) dok se formula kopira.
Postoji više od jednog načina za kopiranje podataka u Excel, ali najlakši način je korištenje ručke za popunjavanje.
-
Selektujte ćeliju E2, gde se nalazi formula za traženje, da biste je učinili aktivnom ćelijom.
Image -
Prevucite ručicu za popunjavanje do ćelija G2. Ćelije F2 i G2 prikazuju grešku N/A koja je prisutna u ćeliji E2.
Image -
Da biste koristili formule za pronalaženje informacija iz tabele podataka, na radnom listu izaberite ćelija D2, otkucajte Widget i pritisnite Enter.
Image Sljedeće informacije se prikazuju u ćelijama E2 do G2.
- E2: $14,76 - cijena widgeta
- F2: PN-98769 - broj dijela za widget
- G2: Widgets Inc. - naziv dobavljača za widgete
-
Da biste testirali formulu VLOOKUP niza, upišite naziv drugih dijelova u ćeliju D2 i promatrajte rezultate u ćelijama od E2 do G2.
Image - Svaka ćelija koja sadrži formulu pretraživanja sadrži različite podatke o hardverskoj stavci koju ste tražili.
Funkcija VLOOKUP sa ugniježđenim funkcijama kao što je COLUMN pruža moćnu metodu za traženje podataka unutar tabele, koristeći druge podatke kao referencu za traženje.
