Kombiniranjem Excelove funkcije VLOOKUP s funkcijom COLUMN možete stvoriti formulu pretraživanja koja vraća više vrijednosti iz jednog retka baze podataka ili tablice podataka. Naučite kako izraditi formulu pretraživanja koja vraća više vrijednosti iz jednog zapisa podataka.
Upute u ovom članku odnose se na Excel 2019, 2016, 2013, 2010; i Excel za Microsoft 365.
Donja crta
Formula pretraživanja zahtijeva da funkcija COLUMN bude ugniježđena unutar VLOOKUP-a. Ugniježđivanje funkcije uključuje unos druge funkcije kao jednog od argumenata za prvu funkciju.
Unesite podatke vodiča
U ovom vodiču funkcija COLUMN unesena je kao argument broja indeksa stupca za VLOOKUP. Posljednji korak u vodiču uključuje kopiranje formule pretraživanja 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. Kako biste slijedili 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.
Kriteriji pretraživanja i formula traženja stvoreni u ovom vodiču unose se u redak 2 radnog lista.
Ovaj vodič ne uključuje osnovno Excel formatiranje prikazano na slici, ali to ne utječe na funkcioniranje formule pretraživanja.
Stvorite imenovani raspon za podatkovnu tablicu
Imenovani raspon jednostavan je način pozivanja na raspon podataka u formuli. Umjesto da upisujete reference ćelije za podatke, upišite naziv raspona.
Druga prednost korištenja imenovanog raspona je ta što se reference ćelija za ovaj raspon nikada ne mijenjaju čak ni kada se formula kopira u druge ćelije na radnom listu. Nazivi raspona alternativa su korištenju apsolutnih referenci ćelija kako bi se spriječile pogreške prilikom kopiranja formula.
Naziv raspona ne uključuje naslove ili nazive polja za podatke (kao što je prikazano u retku 4), samo podatke.
-
Označite ćelije D5 do G10 na radnom listu.
Image -
Postavite pokazivač u okvir za naziv koji se nalazi iznad stupca A, upišite Tablica, zatim pritisnite Enter. Ćelije D5 do G10 imaju naziv raspona tablice.
Image - Naziv raspona za argument polja VLOOKUP tablice koristi se kasnije u ovom vodiču.
Otvorite dijaloški okvir VLOOKUP
Iako je moguće upisati formulu traženja izravno u ćeliju na radnom listu, mnogima je teško držati sintaksu čistom - posebno za složenu formulu poput one korištene 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 zasebnom retku.
-
Odaberite ćeliju E2 radnog lista. Ovo je mjesto gdje će se prikazati rezultati dvodimenzionalne formule pretraživanja.
Image -
Na vrpci idite na karticu Formulas i odaberite Traženje i referenca.
Image -
Odaberite VLOOKUP za otvaranje dijaloškog okvira Function Arguments.
Image - Dijaloški okvir Argumenti funkcije mjesto je gdje se unose parametri funkcije VLOOKUP.
Unesite argument tražene vrijednosti
Normalno, tražena vrijednost odgovara polju podataka u prvom stupcu podatkovne tablice. U ovom primjeru, vrijednost pretraživanja odnosi se na naziv dijela za koji želite pronaći informacije. Dopuštene vrste podataka za traženu vrijednost su tekstualni podaci, logičke vrijednosti, brojevi i reference ćelija.
Apsolutne reference ćelija
Kada se formule kopiraju u Excelu, 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 pogreške u ćelijama F2 i G2.
Apsolutne reference ćelija ne mijenjaju se kada se formule kopiraju.
Kako biste spriječili pogreške, pretvorite referencu ćelije D2 u apsolutnu referencu ćelije. Da biste stvorili apsolutnu referencu ćelije, pritisnite tipku F4. Ovo dodaje znakove dolara oko reference ćelije kao što je $D$2.
-
U dijaloškom okviru Argumenti funkcije, postavite kursor u lookup_value tekstualni okvir. Zatim na radnom listu odaberite cell D2 da biste dodali referencu ove ćelije u lookup_value. Ćelija D2 mjesto je gdje će se unijeti naziv dijela.
Image -
Bez pomicanja točke umetanja, pritisnite tipku F4 za pretvaranje 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 tablice
Niz tablice je tablica podataka koju formula traženja traži kako bi pronašla informacije koje želite. Niz tablice mora sadržavati najmanje dva stupca podataka.
Prvi stupac sadrži argument vrijednosti traženja (koji je postavljen u prethodnom odjeljku), dok se drugi stupac pretražuje formulom pretraživanja kako bi se pronašle informacije koje navedete.
Argument polja tablice mora se unijeti ili kao raspon koji sadrži reference ćelija za podatkovnu tablicu ili kao naziv raspona.
Da biste dodali tablicu podataka funkciji VLOOKUP, postavite kursor u table_array tekstualni okvir u dijaloškom okviru i upišite Tableza unos naziva raspona za ovaj argument.
Ugniježdite funkciju COLUMN
Normalno, VLOOKUP vraća podatke samo iz jednog stupca podatkovne tablice. Ovaj stupac je postavljen argumentom broja indeksa stupca. Međutim, u ovom primjeru postoje tri stupca, a broj indeksa stupca treba promijeniti bez uređivanja formule traženja. Da biste to postigli, ugniježdite funkciju COLUMN unutar funkcije VLOOKUP kao argument Col_index_num.
Prilikom ugniježđivanja funkcija, Excel ne otvara dijaloški okvir druge funkcije za unos njenih argumenata. Funkcija COLUMN mora se unijeti ručno. Funkcija COLUMN ima samo jedan argument, argument Referenca, koji je referenca ćelije.
Funkcija COLUMN vraća broj stupca navedenog kao argument reference. Pretvara slovo stupca u broj.
Da biste pronašli cijenu artikla, koristite podatke u stupcu 2 podatkovne tablice. Ovaj primjer koristi stupac B kao referencu za umetanje 2 u argument Col_index_num.
-
U dijaloškom okviru Argumenti funkcije, postavite kursor u Col_index_num tekstualni okvir i upišite COLUMN(. (Obavezno uključite otvorenu okruglu zagradu.)
Image -
Na radnom listu odaberite ćeliju B1 da unesete tu referencu ćelije kao argument Referenca.
Image - Upišite zatvorenu okruglu zagradu da dovršite funkciju COLUMN.
Unesite VLOOKUP argument traženja raspona
VLOOKUP-ov argument Range_lookup je logična vrijednost (TRUE ili FALSE) koja označava treba li VLOOKUP pronaći točno ili približno podudaranje s Lookup_value.
- TRUE ili izostavljeno: VLOOKUP vraća blisko podudaranje s Lookup_value. Ako nije pronađeno točno podudaranje, VLOOKUP vraća sljedeću najveću vrijednost. Podaci u prvom stupcu Table_array moraju biti poredani uzlaznim redoslijedom.
- FALSE: VLOOKUP koristi točno podudaranje s 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 točno podudaranje ne pronađe, vraća se pogreška N/A.
U ovom vodiču će se tražiti određene informacije o određenoj hardverskoj stavci, tako da je Range_lookup postavljen na FALSE.
U dijaloškom okviru Argumenti funkcije, postavite kursor u tekstni okvir Range_lookup i upišite False kako biste VLOOKUP-u rekli da vrati točno podudaranje za podatke.
Odaberite OK za dovršetak formule traženja i zatvaranje dijaloškog okvira. Ćelija E2 sadržavat će pogrešku N/A jer kriterij traženja nije unesen u ćeliju D2. Ova greška je privremena. Bit će ispravljeno kada se dodaju kriteriji pretraživanja u zadnjem koraku ovog vodiča.
Kopirajte formulu pretraživanja i unesite kriterije
Formula pretraživanja dohvaća podatke iz više stupaca podatkovne tablice odjednom. Da biste to učinili, formula traženja mora se nalaziti u svim poljima iz kojih želite informacije.
Za dohvaćanje podataka iz stupaca 2, 3 i 4 podatkovne tablice (cijena, broj dijela i naziv dobavljača), unesite djelomični naziv kao Lookup_value.
Budući da su podaci raspoređeni u uobičajenom obrascu na radnom listu, kopirajte formulu pretraživanja u ćeliju E2 u ćelije F2 i G2 Kako se formula kopira, Excel ažurira relativnu referencu ćelije u funkciji COLUMN (ćelija B1) kako bi odražavala novu lokaciju formule. Excel ne mijenja apsolutnu referencu ćelije (kao što je $D$2) i imenovani raspon (tablica) dok se formula kopira.
Postoji više od jednog načina za kopiranje podataka u Excelu, ali najlakši način je korištenje Ručke za popunjavanje.
-
Odaberite ćeliju E2, gdje se nalazi formula traženja, kako biste je učinili aktivnom ćelijom.
Image -
Povucite ručicu za popunjavanje do ćelije G2. Ćelije F2 i G2 prikazuju pogrešku N/A koja je prisutna u ćeliji E2.
Image -
Da biste koristili formule traženja za dohvaćanje informacija iz podatkovne tablice, na radnom listu odaberite cell D2, upišite Widget i pritisnite Unesi.
Image Sljedeće informacije prikazuju se 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
-
Za testiranje formule VLOOKUP polja, upišite nazive ostalih dijelova u ćeliju D2 i promatrajte rezultate u ćelijama 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 s ugniježđenim funkcijama kao što je COLUMN pruža moćnu metodu za traženje podataka unutar tablice, koristeći druge podatke kao referencu za pretraživanje.
