01 od 03
Pronađite približne podudarnosti s podacima s Excelovim VLOOKUP-om
Kako funkcija VLOOKUP funkcionira
Excelova funkcija VLOOKUP, koja označava vertikalno pretraživanje , može se koristiti za traženje specifičnih podataka koji se nalaze u tablici podataka ili baze podataka.
VLOOKUP normalno vraća jedno polje podataka kao svoj izlaz. Kako to radi:
- Navodite ime ili vrijednost lookup_value koja govori VLOOKUP u kojem retku ili zapisu podatkovne tablice traži željene podatke
- Dostavite broj stupca - poznat kao col_index_num - podataka koje tražite
- Funkcija traži lookup_value u prvom stupcu podatkovne tablice
- VLOOKUP zatim smješta i vraća informacije koje tražite od drugog polja istog zapisa koristeći isporučeni broj stupca
Razvrstavanje podataka prvo
Iako nije uvijek potrebno, obično je najbolje najprije sortirati raspon podataka koje VLOOKUP traži uzlazno, koristeći prvi stupac raspona za sortiranje ključa.
Ako podaci nisu razvrstani, VLOOKUP može vratiti pogrešan rezultat.
Sintaksa i argumenti funkcije VLOOKUP
Sintaksa funkcije odnosi se na izgled funkcije i uključuje naziv funkcije, zagrade i argumente .
Sintaksa za funkciju VLOOKUP je:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
traženje _value - (potrebno) vrijednost za pretraživanje - kao što je količina prodana na gornjoj slici
table_array - (required) ovo je tablica podataka koje VLOOKUP pretražuje kako bi pronašli informacije koje posjedujete.
- Tablica tablice mora sadržavati najmanje dva stupca podataka
- Prvi stupac obično sadrži lookup_value
col_index_num - (potrebno) broj stupca koji želite pronaći.
- Brojčane oznake počinju sa stupcem " search_key " kao stupac 1
- Ako je col_index_num postavljen na broj veći od broja stupaca odabranih u argumentu table_array , #REF! funkcija vraća pogrešku
range_lookup - (opcionalno) označava je li raspon razvrstan po uzlaznom redoslijedu.
- Podaci u prvom stupcu koriste se kao ključ sortiranja
- Booleova vrijednost - TRUE ili FALSE su jedine prihvatljive vrijednosti
- Ako je izostavljeno, vrijednost je prema zadanim postavkama postavljena na TRUE
- Ako je postavljeno na TRUE ili izostavljeno, a prvi stupac raspona nije poredan poredanim redoslijedom, može se pojaviti netočan rezultat
- Ako je postavljeno na TRUE ili izostavljeno, a točan podudaranje za _value za pretraživanje nije pronađen, ključ za pretraživanje upotrijebljen je najbliži podudaraj koji je manji u veličini ili vrijednosti
- Ako je postavljeno na FALSE, VLOOKUP prihvaća točno podudaranje za _value pretraživanja . Ako postoji više vrijednosti podudaranja, vraća se prva podudarna vrijednost
- Ako je postavljeno na FALSE i nije pronađena odgovarajuća vrijednost za ključ za pretraživanje, ta funkcija vraća # N / A pogrešku
Primjer: Pronađite diskontnu stopu za kupljene količine
Primjer na gornjoj slici koristi funkciju VLOOKUP kako bi pronašao diskontnu stopu koja ovisi o količini kupljenih artikala.
Primjer pokazuje da je popust za kupnju 19 stavki 2%. To je stoga što stupac Količina sadrži raspone vrijednosti. Kao rezultat toga, VLOOKUP ne može pronaći točno podudaranje. Umjesto toga, mora se pronaći približan podudaraj da biste vratili ispravnu stopu popusta.
Da biste pronašli približne podudaranja:
- razvrstavanje podataka u tablici tablice uzlaznim redoslijedom;
- postavite argument field_lookup na TRUE
U primjeru, sljedeća formula sadrži funkciju VLOOKUP koja se koristi za pronalaženje popusta za količine kupljene robe.
= VLOOKUP (C2, $ C $ 5: 8,2 $ D $, TRUE)
Iako se ova formula može jednostavno upisati u listove radnih listova, druga opcija, kako se upotrebljava s koracima navedenim u nastavku, jest upotrijebiti dijaloški okvir funkcije za unos svojih argumenata.
- Korištenje dijaloškog okvira često olakšava ispravno unos argumenata funkcije.
Otvaranje dijaloškog okvira VLOOKUP
Koraci koji se koriste za unos funkcije VLOOKUP prikazani na slici iznad u ćeliju B2 su:
- Kliknite na ćeliju B2 da biste ga učinili aktivnom ćelijom - lokacijom na kojoj se prikazuju rezultati funkcije VLOOKUP
- Kliknite karticu Formulas .
- Odaberite traku i referencu s vrpce kako biste otvorili padajući popis funkcije
- Kliknite na VLOOKUP na popisu kako biste otvorili dijaloški okvir funkcije
02 od 03
Unos Excelovih argumenata funkcije VLOOKUP
Upućivanje na reference mobitela
Argumenti za funkciju VLOOKUP unose se u zasebne linije dijaloškog okvira kao što je prikazano na gornjoj slici.
Citatske reference koje se koriste kao argumenti mogu se upisivati u pravu liniju ili, kao što je učinjeno u koracima u nastavku, upućivanje, koje uključuje označavanje željenog raspona ćelija s pokazivačem miša, može se upotrijebiti za unos u dijaloški okvir ,
Prednosti korištenja pokazivača uključuju:
- Brže je od tipkanja;
- Manje grešaka se unose u ispravne reference stanica.
Korištenje referentnih i apsolutnih ćelija s argumentima
Nije neuobičajeno koristiti više kopija VLOOKUP-a za vraćanje različitih podataka iz iste tablice podataka. Da biste to olakšali, često se VLOOKUP može kopirati iz jedne ćelije u drugu. Kada se funkcije kopiraju na druge stanice, treba paziti da su rezultirajuće reference na mreži točne s obzirom na novu lokaciju funkcije.
Na gornjoj slici, dolarni znakovi ( $ ) okružuju reference stanice za argument table_array, što znači da su apsolutne reference stanica , što znači da se neće promijeniti ako je funkcija kopirana u drugu ćeliju. To je poželjno jer će se više kopija VLOOKUP-a odnositi na istu tablicu podataka kao izvor informacija.
S druge strane , referenca stanice koja se koristi za lookup_value nije okružena dolarskim znakovima, što ga čini relativnom referentnom ćelijom. Relativne reference stanica mijenjaju se kada se kopiraju kako bi odražavale novo mjesto u odnosu na položaj podataka na koje se odnose.
Unos funkcijskih argumenata
- Kliknite kvadratić _value Lookup u dijaloškom okviru VLOOKUP
- Kliknite na ćeliju C2 u radnom listu da biste unijeli ovu ćeliju kao argument za ključ za pretraživanje
- Kliknite tablicu Table_array dijaloškog okvira
- Istaknite ćelije C5 do D8 u radnom listu kako biste ušli u ovaj raspon kao argument table_array - zaglavlja tablice nisu uključena
- Pritisnite tipku F4 na tipkovnici da biste promijenili raspon prema apsolutnim referencama ćelija
- Kliknite liniju Col_index_num dijaloškog okvira
- Upišite 2 na ovoj retku kao argument Col_index_num , budući da se popustne stope nalaze u stupcu 2 tablice tabličnog raspona
- Kliknite liniju Range_lookup dijaloškog okvira
- Upišite riječ True kao argument Range_lookup
- Pritisnite tipku Enter na tipkovnici da biste zatvorili dijaloški okvir i vratili se na radni list
- Odgovor 2% (diskontna stopa za kupljenu količinu) trebao bi se pojaviti u ćeliji D2 radnog lista
- Kada kliknete na ćeliju D2, u baru formule iznad radnog lista pojavljuje se cjelovita funkcija = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Zašto je VLOOKUP vratio 2% kao rezultat
- U primjeru, stupac Količina ne sadrži točno podudaranje za vrijednost ključa pretraživanja 19.
- Budući da je is_sorted argument postavljen na TRUE, VLOOKUP će pronaći približnu podudarnost s vrijednošću search_key .
- Najbliža veličina koja je još uvijek manja od vrijednosti za pretraživanje_kola od 19 je 11.
- VLOOKUP, dakle, traži postotak popusta u retku koji sadrži 11, i kao rezultat toga vraća stopu popusta od 2%.
03 od 03
Excel VLOOKUP ne radi: # N / A i #REF pogreške
Poruke o pogrešci VLOOKUP
Sljedeće poruke o pogrešci povezane su s VLOOKUP.
# N / A ("vrijednost nije dostupna") Prikazuje se pogreška ako:
- Vrijednost pretraživanja nije pronađena u prvom stupcu argumenta raspona
- Argument Table_array je netočan. Na primjer, argument može sadržavati prazne stupce na lijevoj strani dometa
- Argument Range_lookup postavljen je na FALSE i točno se podudara s argumentom search_key ne može se pronaći u prvom stupcu raspona
- Argument Range_lookup postavljen je na TRUE, a sve vrijednosti u prvom stupcu dometa su veće od trake za pretraživanje
#REF! ("referenca izvan raspona") Prikazuje se pogreška ako:
- Argument Col_index_num veći je od broja stupaca u tablici tablice