Nađite više polja podataka sa Excel VLOOKUP

Kombinovanjem Excelove VLOOKUP funkcije sa funkcijom COLUMN možemo napraviti formulu za pretraživanje koja vam omogućava da vratite više vrijednosti iz jednog reda baze podataka ili tablice podataka.

U primjeru prikazanom na slici iznad, formula za pretraživanje omogućava lako vratiti sve vrijednosti - kao što su cijena, broj parcele i dobavljač - koji se odnose na različite dijelove hardvera.

01 od 10

Vraćanje više vrijednosti sa Excel VLOOKUP

Sledeci koraci navedeni dole stvaraju formulu za pretragu koja se vidi na gornjoj slici koja vraca vise vrednosti iz jednog zapisa podataka.

Formula za traženje zahteva da se COLUMN funkcija ugnezdi unutar VLOOKUP-a.

Nesting funkcija podrazumeva ulazak u drugu funkciju kao jedan od argumenata za prvu funkciju.

U ovom tutorijalu, COLUMN funkcija će se uneti kao argument broja indeksa kolona za VLOOKUP.

Poslednji korak u tutorijalu podrazumeva kopiranje formulare za pretragu na dodatne stupce kako bi preuzeo dodatne vrijednosti za odabrani dio.

Sadržaj tutoriala

• Unos podataka o tutorijalu
• Kreiranje imenovanog opsega za tablicu podataka
• Započinjanje funkcije VLOOKUP
• Unošenje argumenata vrijednosti traženja pomoću referenci Absolute Cell
• Unošenje argumenta Array table
• Ulazak u Ugrađenu funkciju COLUMN
• Završetak funkcije VLOOKUP
• Kopiranje Formule za traženje pomoću ručice za popunjavanje
• Preuzimanje podataka pomoću Formule za traženje

02 od 10

Unesite tutorski podaci

Prvi korak u tutorijalu je unos podataka u Excel radni list .

Da biste pratili korake u tutorijalu, unesite podatke prikazane na slici iznad u sledeće ćelije .

• Unesite najviše opsega podataka u ćelije D1 do G1
• Unesite drugi opseg u ćelije D4 do G10

Kriterijumi pretraživanja i formul za pronalaženje kreirani tokom ovog tutorijala unose se u red 2 radnog lista.

Ovaj tutorijal ne uključuje oblikovanje prikazano na slici, ali to neće uticati na način na koji formula za pretraživanje funkcioniše.

Informacije o opcijama za oblikovanje slične onima koje su gore vidljive dostupne su u ovom Basic Excel formatting tutorijalu .

Uputstvo Koraci

1. Unesite podatke kao što vidite na slici iznad u ćelije D1 do G10

03 od 10

Kreiranje imenovanog opsega za tablicu podataka

Nazivni opseg je jednostavan način za upućivanje na niz podataka u formuli. Umjesto da unosite reference ćelija za podatke, možete samo upisati naziv opsega.

Druga prednost za korištenje naznačenog opsega je to što se reference ćelija za ovaj opseg nikada ne mijenjaju čak i kada se formula kopira u druge ćelije na radnom listu.

Dakle, imena opsega su alternativa korišćenju apsolutnih referenci ćelija kako bi se sprečile greške prilikom kopiranja formulara.

Napomena: Naziv područja ne uključuje nazive naslova ili polja za podatke (red 4), već samo podatke.

Uputstvo Koraci

1. Označite ćelije D5 do G10 na radnom listu kako biste ih izabrali
2. Kliknite na polje za izbor imena iznad stupca A
3. Upišite "Tabela" (bez citata) u polje Ime
4. Pritisnite tipku ENTER na tastaturi
5. Ćelije D5 do G10 sada imaju naziv raspona "Tabela". Mi ćemo koristiti naziv za argument VLAOKUP table array kasnije u tutorijalu

04 od 10

Otvaranje dijaloga VLOOKUP

Iako je moguće upisati našu formulu za pretragu direktno u ćeliju na radnom listu, mnogim ljudima je teško držati sintaksu prave - posebno za kompleksnu formulu kao što je ona koju koristimo u ovom priručniku.

Alternativa, u ovom slučaju, je korišćenje dijaloga VLOOKUP. Skoro sve funkcije Excel- a imaju dijaloški okvir koji vam omogućava da unosite svaki od argumenata funkcije na zasebnu liniju.

Uputstvo Koraci

1. Kliknite na ćeliju E2 radnog lista - lokaciju na kojoj će se prikazati rezultati dvodimenzionalne formula za pretraživanje
2. Kliknite na karticu Formule na traci
3. Kliknite na opciju Lookup & Reference u traci da biste otvorili listu padajućih funkcija
4. Kliknite na VLOOKUP na listi da biste otvorili dijaloški okvir funkcije

05 od 10

Unošenje argumenata vrijednosti traženja pomoću referenci Absolute Cell

Normalno, vrijednost traženja odgovara polju podataka u prvoj koloni tablice podataka.

U našem primeru, vrijednost traženja odnosi se na ime hardverskog dela o kojem želimo pronaći informacije.

Dozvoljeni tipovi podataka za tražnju su:

• tekstualni podaci
• logička vrednost (samo TRUE ili FALSE)
• broj
• referenca ćelije na vrijednost na radnom listu

U ovom primjeru ćemo uneti referencu ćelije na gdje će se naziv partije naći - ćelija D2.

Apsolutne reference ćelija

U kasnijom koraku u tutorijalu kopiramo formulu za traženje u ćeliju E2 u ćelije F2 i G2.

Obično, kada se formule kopiraju u Excel-u, reference ćelija se menjaju da odražavaju njihovu novu lokaciju.

Ukoliko se ovo desi, D2 - referenca ćelije za vrijednost traženja - će se promijeniti kada se formula kopira, stvarajući greške u ćelijama F2 i G2.

Da bismo sprečili greške pretvorićemo reference D2 ćelije u apsolutnu referentnu ćeliju .

Apsolutne reference ćelija se ne menjaju kada se kopiraju formule.

Apsolutne reference ćelija se stvaraju pritiskom na taster F4 na tastaturi. Na taj način se dodaju znaci dolara oko reference ćelije, kao što je $ D $ 2

Uputstvo Koraci

1. Kliknite na liniju lookup_value u dijaloškom okviru
2. Kliknite na ćeliju D2 da dodate ovu referencu ćeliju na liniju lookup_value . Ovo je ćelija u kojoj ćemo uneti ime dela o kojem tražimo informacije
3. Bez pomeranja tačke ubacivanja, pritisnite taster F4 na tastaturi da biste pretvorili D2 u apsolutnu referentnu ćeliju $ D $ 2
4. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sledeći korak u tutorijalu

06 od 10

Unošenje argumenta Array table

Niz tablica je tabela podataka koju formula za pretraživanje traži da pronađe željene informacije.

Niz tablica mora sadržavati najmanje dvije kolone podataka .

• prva kolona sadrži argument lookup value (prethodni korak u tutorijalu)
• drugi, i bilo koji dodatni stupac, pretražit će se pomoću formula za pronalaženje kako bi se pronašle informacije koje smo naveli.

Array table array se mora uneti kao opseg koji sadrži reference ćelija za tablicu podataka ili kao naziv opsega .

Za ovaj primjer koristićemo ime opsega kreiranog u koraku 3 tutoriala.

Uputstvo Koraci

1. Kliknite na liniju table_array u dijaloškom okviru
2. Ukucajte "Tabela" (bez navodnika) da unesete naziv raspona za ovaj argument
3. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sledeći korak u tutorijalu

07 od 10

Vređanje funkcije COLUMN

Obično VLOOKUP vraća samo podatke iz jedne kolone tablice podataka i ova kolona je postavljena argumentom brojeva indeksnog broja .

U ovom primeru, međutim, imamo tri kolone za koje želimo vratiti podatke, tako da nam je potreban način da lako promenimo indeksni broj stupca bez izmjene naše formule za pretraživanje.

Ovde ulazi funkcija COLUMN. Unošenjem je kao argument broja indeksa stupca , ona će se promeniti kako se formula za pretragu kopira iz ćelije D2 u ćelije E2 i F2 kasnije u tutorialu.

Nesting Funkcije

Funkcija COLUMN, dakle, deluje kao VLOOKUP-ov argument broja brojeva indeksa .

Ovo se postiže gneženjem funkcije COLUMN unutar VLOOKUP-a u Col_index_num liniji dijalog box-a.

Ručno unošenje funkcije COLUMN

Kada gnežite funkcije, Excel nam ne dozvoljava da otvorimo dijalog box druge funkcije da unosimo njegove argumente.

Funkcija COLUMN, prema tome, mora se uneti ručno u Col_index_num liniju.

Funkcija COLUMN ima samo jedan argument - Referentni argument koji je referenca ćelije.

Izbor referentnog argumenta COLUMN funkcije

Zadatak funkcije COLUMN je vratiti broj kolone datog kao Referentni argument.

Drugim rečima, pretvara se slovo kolone u broj sa kolonom A koja je prva kolona, ​​kolona B druga i tako dalje.

Pošto prvo polje podataka koje želimo vratiti je cena stavke - koja je u koloni dva iz tabele podataka - možemo odabrati referencu ćelije za bilo koju ćeliju u koloni B kao referentni argument kako bi dobili broj 2 za argument Col_index_num .

Uputstvo Koraci

1. U dijalogu funkcije VLOOKUP kliknite na liniju Col_index_num
2. Ukucajte stupac imena funkcije, a zatim otvoreni okrugli nosač " ( "
3. Kliknite na ćeliju B1 na radnom listu da biste uneli referencu ćelije kao referentni argument
4. Ukucajte zaključnu konzolu " ) " da biste dovršili funkciju COLUMN
5. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sledeći korak u tutorijalu

08 od 10

Ulazak u VLOOKUP Range Argument

VLOOKUP-ov argument Range_lookup je logična vrijednost (samo TRUE ili FALSE) koja ukazuje na to da li želite da VLOOKUP pronađe tačnu ili približnu podudarnost sa Lookup_value.

• Ako je TRUE ili ako je ovaj argument ispušten, VLOOKUP vraća ili tačno usklađivanje sa Lookup_value, ili ako nije pronađeno tačno usklađivanje, VLOOKUP vraća sledeću najveću vrijednost. Za formulu koja to radi, podaci u prvoj koloni Table_array moraju biti sortirani po rastućem redosledu .
• Ako je FALSE, VLOOKUP će koristiti samo tačnu podudarnost sa Lookup_value. Ako u prvoj koloni Table_array postoje dve ili više vrijednosti koje odgovaraju pokrivnoj vrijednosti, koristi se prva vrijednost. Ako se ne pronađe tačno podudaranje, greška # N / A se vraća.

U ovom vodiču, pošto tražimo specifične informacije o određenom hardverskom stavku, postavićemo Range_lookup jednako False .

Uputstvo Koraci

1. Kliknite na Range_lookup liniju u dijalogu
2. Upišite riječ False u ovoj liniji da biste naznačili da želimo da VLOOKUP vrati tačan podudar za podatke koje tražimo
3. Kliknite na OK da biste dovršili formulu za pretragu i zatvorili dijaloški okvir
4. Pošto još nismo upisali kriterijume za pretraživanje u ćeliju D2, greška # N / A će biti prisutna u ćeliji E2
5. Ova greška će biti ispravljena kada ćemo dodati kriterijume za pretraživanje u poslednjem koraku tutoriala

09 od 10

Kopiranje Formule za traženje pomoću ručice za popunjavanje

Formula za pretraživanje ima za cilj da preuzme podatke iz više kolona tablice podataka u isto vrijeme.

Da bi to učinili, formula za traženje mora da se nalazi u svim poljima iz kojih želimo informaciju.

U ovom vodiču želimo da dobije podatke iz kolona 2, 3 i 4 tabele podataka - to je cena, broj dela i ime dobavljača kada unesemo naziv dela kao Lookup_value.

Pošto su podaci postavljeni u redovnom obrazcu na radnom listu , možemo kopirati formu za pretraživanje u ćeliji E2 u ćelije F2 i G2.

Kako je formula kopirana, Excel će ažurirati relativnu referentnu ćeliju u funkciji COLUMN (B1) kako bi odrazila novu lokaciju formule.

Takođe, Excel ne menja apsolutnu referentnu ćeliju $ D $ 2 i nazvanu tablicu raspona jer se formula kopira.

Postoji više načina za kopiranje podataka u programu Excel, ali najverovatnije najlakši način je korištenje ručice za popunjavanje .

Uputstvo Koraci

1. Kliknite na ćeliju E2 - gde se nalazi formula za pretragu - da bi ona postala aktivna ćelija
2. Postavite pokazivač miša preko crnog kvadrata u donjem desnom uglu. Pokazivač će se promeniti na plus znak " + " - ovo je ručica za popunjavanje
3. Kliknite na levi taster miša i prevucite ručicu za punjenje preko ćelije G2
4. Oslobodite dugme miša i ćelija F3 treba da sadrži dvodimenzionalnu formulu za pretraživanje
5. Ako se ispravno izvrši, ćelije F2 i G2 sada treba da sadrže i # N / A grešku koja je prisutna u ćeliji E2

10 od 10

Upisivanje Kriterijuma za traženje

Kada je formula za pretragu kopirana u željene ćelije , može se koristiti za preuzimanje podataka iz tabele podataka.

Da biste to učinili, upišite naziv stavke koju želite da preuzmete u Cell Lookup_value (D2) i pritisnite taster ENTER na tastaturi.

Kada jednom završite, svaka ćelija koja sadrži formu za pretraživanje treba da sadrži različite podatke o hardveru koji tražite.

Uputstvo Koraci

1. Kliknite na ćeliju D2 na radnom listu
2. Ukucajte Widget u ćeliju D2 i pritisnite taster ENTER na tastaturi
3. Sledeće informacije trebaju biti prikazane u ćelijama E2 do G2:
   • E2 - 14,76 dolara - cena widget-a
   • F2 - PN-98769 - broj dela za vidžet
   • G2 - Widgets Inc. - ime dobavljača za vidžete
4. Ispitajte formulu VLOOKUP niza tako što ćete ukucati ime drugih delova u ćeliju D2 i posmatrati rezultate u ćelijama E2 do G2

Ako se poruka o grešci, kao što je #REF! pojavljuje se u ćelijama E2, F2 ili G2, ova lista VLOOKUP poruka o greškama može vam pomoći da odredite gdje se nalazi problem.