Pronađite više polja podataka s programom Excel VLOOKUP

Kombiniranjem Excel funkcije VLOOKUP s funkcijom COLUMN možemo stvoriti formulu za pretraživanje koja vam omogućuje vraćanje više vrijednosti iz jednog retka baze podataka ili tablice podataka.

U primjeru prikazanom na gornjoj slici, formula za pretraživanje olakšava vraćanje svih vrijednosti - kao što su cijena, broj dijelova i dobavljač - vezane za različite dijelove hardvera.

01 od 10

Vrati višestruke vrijednosti s programom Excel VLOOKUP

Sljedeći koraci navedeni u nastavku stvaraju formulu traženja vidljivu na gornjoj slici koja će vraćati više vrijednosti s jednog zapisa podataka.

Formula za traženje zahtijeva da funkcija COLUMN bude ugniježđena unutar VLOOKUP.

Gniježđenje funkcije uključuje unos druge funkcije kao jedan od argumenata za prvu funkciju.

U ovom vodiču funkcija COLUMN bit će unesena kao argument stupca indeksa za VLOOKUP.

Posljednji korak u vodiču uključuje kopiranje tražene formule na dodatne stupce kako bi se preuzeli dodatne vrijednosti za odabrani dio.

Sadržaj vodiča

• Unos podataka o vodiču
• Izrada nazvanog raspona za tablicu podataka
• Pokretanje funkcije VLOOKUP
• Unos argumente vrijednosti traženja pomoću referenci za apsolutne ćelije
• Unos tabličnog argumenta
• Unos Nested COLUMN funkcije
• Dovršavanje funkcije VLOOKUP
• Kopiranje tražene formule s ručkom za ispunjavanje
• Dohvaćanje podataka pomoću formule za traženje

02 od 10

Unesite podatke o vodiču

Prvi korak u vodiču je unos podataka u radni list programa Excel .

Da biste slijedili korake u vodiču, unesite podatke prikazane na gornjoj slici u sljedeće ćelije .

• Unesite najviši raspon podataka u ćelije D1 do G1
• Unesite drugi raspon u ćelijama D4 do G10

Kriteriji pretraživanja i formula za pretraživanje stvorene tijekom ovog tutoriala bit će upisani u redak 2 radnog lista.

Vodič ne uključuje oblikovanje na slici, ali to neće utjecati na funkcioniranje formule za pretraživanje.

Informacije o mogućnostima oblikovanja slične onima navedenim gore dostupne su u ovom Osnovnom alatu za oblikovanje formata Excel .

Koraci

1. Unesite podatke kao što je vidljivo na slici iznad u ćelijama D1 do G10

03 od 10

Izrada nazvanog raspona za tablicu podataka

Imenovani raspon je jednostavan način da se odnosi na raspon podataka u formuli. Umjesto upisivanja ćelije reference za podatke, možete upisati naziv raspona.

Druga prednost za upotrebu nazvanog raspona je da se reference stanica za ovaj raspon nikada ne mijenjaju čak i kada se formula kopira u druge stanice u radnom listu.

Nazivi raspona, stoga, predstavljaju alternativu upotrebi apsolutnih referenci za ćelije kako bi se spriječilo pogreške prilikom kopiranja formula.

Napomena: Naziv raspona ne uključuje nazive niti nazive polja za podatke (redak 4), ali samo one podatke.

Koraci

1. Istaknite ćelije D5 do G10 u radnom listu da biste ih odabrali
2. Kliknite na Name Box iznad gornjeg stupca A
3. Upišite "Table" (bez navodnika) u Name Box
4. Pritisnite tipku ENTER na tipkovnici
5. Stanice D5 do G10 sada imaju naziv raspona "Tablica". U nastavku ćemo upotrijebiti naziv za argument VLOOKUP stolnog polja

04 od 10

Otvaranje dijaloškog okvira VLOOKUP

Iako je moguće upisati našu formulu traženja izravno u ćeliju na radnom listu, mnogi ljudi teško mogu zadržati sintaksa ravno - posebno za složenu formulu kao što je ona koju koristimo u ovom vodiču.

Alternativa je, u ovom slučaju, koristiti dijaloški okvir VLOOKUP. Gotovo sve Excelove funkcije imaju dijaloški okvir koji vam omogućuje da unesete svaki od argumenata funkcije u zasebnu liniju.

Koraci

1. Kliknite na ćeliju E2 radnog lista - mjesto na kojem će se prikazati rezultati dvodimenzionalne formule za traženje
2. Kliknite karticu Formule na vrpci
3. Kliknite opciju Lookup & Reference (Traži i referentni) na vrpci kako biste otvorili padajući popis funkcije
4. Kliknite na VLOOKUP na popisu da biste otvorili dijaloški okvir funkcije

05 od 10

Unos argumente vrijednosti traženja pomoću referenci za apsolutne ćelije

Uobičajeno, vrijednost pretraživanja odgovara polju podataka u prvom stupcu podatkovne tablice.

U našem primjeru, vrijednost pretraživanja odnosi se na naziv hardverskog dijela o kojem želimo pronaći informacije.

Dopuštene vrste podataka za vrijednost pretraživanja su:

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

U ovom ćemo primjeru upisati referencu stanice na mjesto gdje će se nalaziti naziv dijela - ćeliju D2.

Referencije apsolutnih stanica

U kasnijem koraku u udžbeniku ćemo kopirati formulu traženja u ćeliji E2 u ćelije F2 i G2.

Uobičajeno, kada se formule kopiraju u programu Excel, reference na ćeliji mijenjaju se tako da odražavaju njihovu novu lokaciju.

Ako se to dogodi, D2 - referenca ćelije za traženu vrijednost - mijenja se kako se formula kopira stvarajući pogreške u ćelijama F2 i G2.

Da bismo spriječili pogreške, pretvoriti ćemo referencu stanice D2 u apsolutnu referencu stanica .

Referencije apsolutnih stanica ne mijenjaju se kada se formule kopiraju.

Apsolutne ćelije se izrađuju pritiskom tipke F4 na tipkovnici. Na taj način dodaje dolarske znakove oko ćelije kao što je $ D $ 2

Koraci

1. Kliknite okvir lookup_value u dijaloškom okviru
2. Kliknite na ćeliju D2 da biste dodali ovu referencu ćelije na liniju lookup_value . Ovo je stanica u kojoj ćemo upisati naziv dijela o kojem tražimo informacije
3. Bez premještanja točke umetanja, pritisnite tipku F4 na tipkovnici da biste pretvorili D2 u apsolutnu referencu stanica $ D $ 2
4. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sljedeći korak u vodiču

06 od 10

Unos tabličnog argumenta

Tablica tablica je tablica podataka koje tražilica traži kako bi pronašla željene informacije.

Tablični polje mora sadržavati najmanje dva stupca podataka .

• prvi stupac sadrži argument za vrijednost pretraživanja (prethodni korak u vodiču)
• drugi, i bilo koji dodatni stupac, tražit će se pomoću formule za traženje kako bismo pronašli navedene podatke.

Argument table array mora biti unesen kao raspon koji sadrži reference stanice za podatkovnu tablicu ili kao naziv raspona .

Za ovaj primjer koristit ćemo naziv raspona stvoren u 3. koraku udžbenika.

Koraci

1. Kliknite tablicu tablice u dijaloškom okviru
2. Unesite "Table" (bez navodnika) unesite naziv raspona za ovaj argument
3. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sljedeći korak u vodiču

07 od 10

Gniježđenje funkcije COLUMN

Uobičajeno, VLOOKUP vraća podatke samo iz jednog stupca podatkovne tablice i ovaj je stupac postavljen argumentom indeksnog broja stupca .

U ovom primjeru, međutim, imamo tri stupca kojima želimo vratiti podatke tako da nam je potreban način jednostavnog izmjena indeksnog broja stupaca bez uređivanja formule za traženje.

Ovo je mjesto gdje dolazi funkcija COLUMN. Upisivanjem ga kao argument argumenta stupca stupca , promijenit će se kako se formula za pretraživanje kopira iz ćelije D2 u ćelije E2 i F2 kasnije u tutorialu.

Nesting funkcije

Stoga, funkcija COLUMN djeluje kao argument za indeksni broj stupaca VLOOKUP.

To se postiže gniježđenjem funkcije COLUMN unutar VLOOKUP u liniji Col_index_num dijaloškog okvira.

Ulazak u COLUMN funkciju ručno

Kada funkcionira gniježđenje, Excel ne dopušta da otvorimo dijaloški okvir druge funkcije da unesemo njegove argumente.

Stoga se COLUMN funkcija mora unijeti ručno u liniji Col_index_num .

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

Odabir referentnog argumenata funkcije COLUMN

Zadatak COLUMN funkcije je vratiti broj stupca koji je naveden kao Referentni argument.

Drugim riječima, slovo stupca pretvara u broj u kojem je stupac A prvi stupac, stupac B drugi i tako dalje.

Budući da se prvo polje podataka želimo vratiti je cijena stavke - koja je u stupcu dvije tablice podataka - možemo odabrati referencu stanica za bilo koju ćeliju u stupcu B kao referentni argument kako bismo dobili broj 2 za argument Col_index_num .

Koraci

1. U dijaloškom okviru funkcije VLOOKUP kliknite na Col_index_num redak
2. Upišite stupac naziv funkcije, nakon kojeg slijedi otvoreni okrugli nosač " ( "
3. Kliknite na ćeliju B1 u radnom listu da biste unijeli tu ćeliju kao Referentni argument
4. Upišite kružni nosač zatvaranja " ) " za dovršavanje funkcije COLUMN
5. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sljedeći korak u vodiču

08 od 10

Unos argumenata traženja raspona VLOOKUP

VLOOKUP je argument Range_lookup logička vrijednost (samo TRUE ili FALSE) koja označava želite li da VLOOKUP pronađe točnu ili približnu utakmicu Lookup_value.

• Ako je TRUE ili ako je ovaj argument izostavljen, VLOOKUP vraća točno podudaranje s vrijednostom Lookup_value ili, ako se ne pronađe točno podudaranje, VLOOKUP vraća sljedeću najveću vrijednost. Za formulu za to, podaci u prvom stupcu Table_array moraju biti razvrstani po uzlaznom poretku .
• Ako je FALSE, VLOOKUP će koristiti točno podudaranje s vrijednostom Lookup_value. Ako u prvom stupcu tabličnog raspona postoje dvije ili više vrijednosti koje odgovaraju vrijednosti traženja, koristi se prva pronađena vrijednost. Ako se ne pronađe točno podudaranje, vratit će se pogreška # N / A.

U ovom udžbeniku, budući da tražimo određene informacije o određenoj stavci hardvera, postavit ćemo Range_lookup jednak False .

Koraci

1. Kliknite na liniju Range_lookup u dijaloškom okviru
2. U ovom retku upišite riječ Lažno da biste naznačili da želimo VLOOKUP vratiti točno podudaranje podataka koje tražimo
3. Kliknite U redu da biste dovršili formulu traženja i zatvorili dijaloški okvir
4. Budući da još nismo unijeli kriterij pretraživanja u ćeliju D2, pogreška # N / A bit će prisutno u ćeliji E2
5. Ta se pogreška ispravlja kad ćemo dodati kriterije pretraživanja u zadnjem koraku udžbenika

09 od 10

Kopiranje tražene formule s ručkom za ispunjavanje

Pretraživačka formula ima za cilj prikupljanje podataka iz više stupaca tablice podataka odjednom.

Da biste to učinili, formula za traženje mora se nalaziti u svim poljima iz kojih želimo informacije.

U ovom udžbeniku želimo da dohvatimo podatke iz stupaca 2, 3 i 4 podatkovne tablice - to je cijena, broj dijela i ime dobavljača kada unesemo naziv dijela kao Lookup_value.

Budući da su podaci prikazani u regularnom obrascu u radnom listu , možemo kopirati formulu traženja u ćeliji E2 u ćelije F2 i G2.

Kako se formula kopira, Excel će ažurirati relativnu referencu stanica u funkciji COLUMN (B1) kako bi odražavao novu lokaciju formule.

Isto tako, Excel ne mijenja apsolutnu referencu stanica $ D $ 2 i nazvani raspon tablice kao što je formula kopirana.

Postoji više načina kopiranja podataka u programu Excel, ali najvjerojatnije najlakši način je pomoću ručice za popunjavanje .

Koraci

1. Kliknite na ćeliju E2 - gdje se nalazi formula za pretraživanje - kako bi bila aktivna ćelija
2. Postavite pokazivač miša preko crnog kvadrata u donjem desnom kutu. Pokazivač će se promijeniti na plus znak " + " - ovo je ručica za punjenje
3. Pritisnite lijevu tipku miša i povucite ručicu za punjenje u ćeliju G2
4. Otpustite gumb miša i ćelija F3 trebala bi sadržavati formulu dvodimenzionalnog pretraživanja
5. Ako je to ispravno, stanice F2 i G2 sad bi trebale sadržavati i pogrešku # N / A koja je prisutna u ćeliji E2

10 od 10

Unos Kriterija za pretraživanje

Nakon što je formula za traženje kopirana u tražene stanice , može se upotrijebiti za preuzimanje podataka iz tablice podataka.

Da biste to učinili, upišite naziv stavke koju želite preuzeti u ćeliju Lookup_value (D2) i pritisnite tipku ENTER na tipkovnici.

Kada završite, svaka ćelija koja sadrži formulu za traženje trebala bi sadržavati različite podatke o hardverskoj stavci koju tražite.

Koraci

1. Kliknite na ćeliju D2 u radnom listu
2. Utipkajte Widget u ćeliju D2 i pritisnite tipku ENTER na tipkovnici
3. Sljedeće informacije trebaju biti prikazane u ćelijama E2 do G2:
   • E2 - 14,76 dolara - cijena widgeta
   • F2 - PN-98769 - broj dijela za widget
   • G2 - Widgets Inc. - naziv dobavljača za widgete
4. Testirajte formulu polja VLOOKUP dalje upisivanjem naziva drugih dijelova u ćeliju D2 i promatranjem rezultata u stanicama E2 do G2

Ako se pojavi poruka o pogrešci kao što je #REF! pojavljuje se u stanicama E2, F2 ili G2, ovaj popis VLOOKUP poruka o pogreškama može vam pomoći da odredite gdje se problem nalazi.