Excel Two Way Lookup Korištenje VLOOKUP Part 2

01 od 06

Pokretanje Nested MATCH funkcije

Vratite se na 1. dio

Unos funkcije MATCH kao Argument broj indeksa stupca

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

Međutim, u ovom primjeru imamo tri stupca da želimo pronaći podatke tako da nam je potreban način jednostavnog izmjena indeksnog broja stupaca bez uređivanja naše formule za traženje.

Ovo je mjesto gdje funkcija MATCH dolazi u igru. Omogućit će nam da podudarimo broj stupca s nazivom polja - siječnja, veljača ili ožujka - koji upisujemo u ćeliju E2 radnog lista.

Nesting funkcije

Zbog toga funkcija MATCH funkcionira kao argument indeksa stupca VLOOKUP.

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

Ručno unos funkcije MATCH

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

Zbog toga se funkcija MATCH mora unijeti ručno u liniji Col_index_num .

Kada ručno unosite funkcije, svaki argument funkcije mora biti odvojen zarezom "," .

Koraci

Unos argumenata Lookup_value funkcije MATCH

Prvi korak u unosu u ugrađenu funkciju MATCH jest unijeti argument Lookup_value .

Vrijednost " Lookup_value" bit će lokacija ili referentna stanica za pojam za pretraživanje koji želimo podudarati u bazi podataka.

1. U dijaloškom okviru funkcije VLOOKUP kliknite na Col_index_num redak.
2. Upišite podudarnost imena funkcija i slijedi otvoreni okrugli nosač " ( "
3. Kliknite na ćeliju E2 da biste upisali referencu ćelije u dijaloški okvir.
4. Upišite zarez "," nakon referentne ćelije E3 da biste dovršili unos argumenta Lookup_value funkcije MATCH.
5. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sljedeći korak u vodiču.

U posljednjem koraku vodiča Lookup_values ​​će se unijeti u ćelije D2 i E2 na radnom listu .

02 od 06

Dodavanje Lookup_array za MATCH funkciju

Dodavanje Lookup_array za MATCH funkciju

Ovaj korak obuhvaća dodavanje argumenata Lookup_array za ugniježđenu funkciju MATCH.

Lookup_array je raspon ćelija koje će funkcija MATCH pretražiti kako bi pronašla argument Lookup_value dodan u prethodnom koraku udžbenika.

U ovom primjeru želimo da funkcija MATCH pretražuje ćelije D5 do G5 za utakmicu s nazivom mjeseca koji će biti upisan u ćeliju E2.

Koraci

Te se korake unosi nakon zareza unesenog u prethodnom koraku na liniji Col_index_num u dijaloškom okviru funkcije VLOOKUP.

1. Ako je potrebno, kliknite zarez Col_index_num nakon zarezom da biste stavili točku umetanja na kraj trenutnog unosa.
2. Istaknite ćelije D5 do G5 u radnom listu kako biste unijeli ove reference stanica kao raspon funkcije za pretraživanje.
3. Pritisnite tipku F4 na tipkovnici da biste promijenili ovaj raspon u apsolutne reference o ćeliji . Ako to učinite, moguće je kopirati ispunjenu formulu za pretraživanje na druge lokacije u radnom listu u zadnjem koraku udžbenika
4. Unesite zarez "," nakon referentne ćelije E3 da biste dovršili unos matrice Lookup_array funkcije MATCH.

03 od 06

Dodavanje vrste podudaranja i Dovršavanje funkcije MATCH

Dodavanje vrste podudaranja i Dovršavanje funkcije MATCH

Treći i konačni argument funkcije MATCH je argument Match_type.

Ovaj argument kaže Excelu kako uskladiti vrijednost Lookup_value s vrijednostima u Lookup_array. Izbori su: -1, 0 ili 1.

Ovaj argument nije obavezan. Ako je izostavljena, funkcija koristi zadanu vrijednost od 1.

• ako je Match_type = 1 ili izostavljen: MATCH pronalazi najveću vrijednost koja je manja ili jednaka vrijednosti Lookup_value. Ako je ova vrijednost odabrana, podaci Lookup_array moraju biti razvrstani po uzlaznom redoslijedu.
• ako Match_type = 0: MATCH pronalazi prvu vrijednost koja je točno jednaka Lookup_value. Podaci Lookup_array mogu se razvrstati u bilo kojem redoslijedu.
• ako Match_type = 1: MATCH pronalazi najmanju vrijednost koja je veća ili jednaka vrijednosti Lookup_value. Ako je ova vrijednost odabrana, podaci Lookup_array moraju biti razvrstani u silaznom redoslijedu.

Koraci

Ove se korake unose nakon zareza unesenog u prethodnom koraku na retku Row_num u dijaloškom okviru funkcije VLOOKUP.

1. Slijedeći drugu zarez na liniji Col_index_num , upišite nulu " 0 " budući da želimo da ugniježđena funkcija vrati točan podudaranje s mjesečnim unosom u ćeliju E2.
2. Upišite kružni nosač zatvaranja " ) kako biste dovršili funkciju MATCH.
3. Ostavite dijaloški okvir funkcije VLOOKUP otvoren za sljedeći korak u vodiču.

04 od 06

Unos argumenata traženja raspona VLOOKUP

Argument pretraživanja raspona

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 vodiču, budući da tražimo podatke o prodaji za određeni mjesec, 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 dvodimenzionalnog pretraživanja i zatvorili dijaloški okvir
4. Budući da još nismo unijeli kriterij pretraživanja u ćelije D2 i E2, pogreška # N / A bit će prisutno u ćeliji F2
5. Ta će se pogreška ispraviti u sljedećem koraku u vodiču kada ćemo dodati kriterij pretraživanja u sljedeći korak vodiča.

05 od 06

Testiranje formule za traženje dvosmjernih pretraživanja

Testiranje formule za traženje dvosmjernih pretraživanja

Da biste koristili formulu za traženje dvosmjernog pretraživanja kako biste pronašli mjesečne podatke o prodaji za različite kolačiće navedene u tabličnom nizu, upišite naziv kolačića u ćeliju D2, mjesec u ćeliju E2 i pritisnite tipku ENTER na tipkovnici.

Podaci o prodaji bit će prikazani u ćeliji F2.

Koraci

1. Kliknite na ćeliju D2 u svojem radnom listu
2. Upišite zobene pahuljice u ćeliju D2 i pritisnite tipku ENTER na tipkovnici
3. Kliknite na ćeliju E2
4. Utipkajte veljaču u ćeliju E2 i pritisnite tipku ENTER na tipkovnici
5. Vrijednost 1,345 USD - prodajna količina za kolačiće zobenih pahuljica u mjesecu veljači - trebala bi biti prikazana u ćeliji F2
6. U ovom trenutku, vaš radni list treba odgovarati primjeru na stranici 1 ovog vodiča
7. Testirajte formulu za pretraživanje dodatno upisivanjem bilo koje kombinacije tipova kolačića i mjeseci koji su prisutni u Tablici tablice i brojke prodaje trebaju biti prikazane u ćeliji F2
8. Posljednji korak u vodiču obuhvaća kopiranje tražene formule pomoću ručice za popunjavanje .

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

06 od 06

Kopiranje Formule za traženje dimenzije s ručkom za ispunjavanje

Kopiranje Formule za traženje dimenzije s ručkom za ispunjavanje

Da bi pojednostavnili uspoređivanje podataka za različite mjesece ili različite kolačiće, formula za pretraživanje može se kopirati u druge stanice tako da se istodobno mogu prikazati više iznosa.

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

Kako se formula kopira, Excel će ažurirati relativne reference stanica kako bi odražavao novu lokaciju formule. U ovom slučaju D2 postaje D3 i E2 postaje E3,

Isto tako, Excel zadržava apsolutnu referencu ćelija isto tako da apsolutni raspon $ D $ 5: $ G $ 5 ostaje isti kada se formula kopira.

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 D3 u radnom listu
2. Upišite zobene pahuljice u ćeliju D3 i pritisnite tipku ENTER na tipkovnici
3. Kliknite na ćeliju E3
4. Upišite Ožujak u ćeliju E3 i pritisnite tipku ENTER na tipkovnici
5. Kliknite na ćeliju F2 kako biste ga učinili aktivnom ćelijom
6. Postavite pokazivač miša preko crnog kvadrata u donjem desnom kutu. Pokazivač će se promijeniti na plus znak "+" - ovo je ručica za popunjavanje
7. Pritisnite lijevu tipku miša i povucite ručicu za punjenje do ćelije F3
8. Otpustite gumb miša i ćelija F3 trebala bi sadržavati formulu dvodimenzionalnog pretraživanja
9. Vrijednost 1.287 USD - iznos prodaje za kolačiće zobenih pahuljica u mjesecu ožujku - trebao bi biti prikazan u ćeliji F3