01 od 03
Pronađite približne rezultate sa podacima sa Excelovim VLOOKUP-om
Kako funkcioniše VLOOKUP funkcija
Excelova VLOOKUP funkcija , koja se odnosi na vertikalan pregled , može se koristiti za pretraživanje određenih informacija koje se nalaze u tablici podataka ili baze podataka.
VLOOKUP normalno vraća jedno polje podataka kao njegov izlaz. Kako to radi:
- Nudite ime ili lookup_value koji govori VLOOKUP u kojem redosledu ili zapisu tablice podataka traži željene podatke
- Isporučujete broj kolone - poznat kao col_index_num - podataka koje tražite
- Funkcija traži lookup_value u prvoj koloni tablice podataka
- VLOOKUP potom locira i vraća informacije koje tražite iz drugog polja istog zapisa pomoću dostavljenog broja kolone
Sortiranje podataka prvo
Iako nije uvek potrebno, obično je najbolje prvo sortirati opseg podataka koje VLOOKUP pretražuje u rastućem redosledu koristeći prvu kolonu opsega za ključ za sortiranje.
Ako podaci nisu sortirani, VLOOKUP može vratiti pogrešan rezultat.
Sintaksa i argumenti VLOOKUP funkcije
Sintaksa funkcije se odnosi na raspored funkcije i uključuje ime funkcije, zagrade i argumente .
Sintaksa za funkciju VLOOKUP-a je:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (obavezno) vrednost za traženje - kao što je količina prodata na gornjoj slici
table_array - (obavezno) ovo je tabela podataka koje VLOOKUP traži da pronađe informacije o kojima ste posle.
- Table_array mora da sadrži najmanje dve kolone podataka
- Prva kolona obično sadrži lookup_value
col_index_num - (potrebno) broj kolone željene vrijednosti.
- Numerisanje počinje sa kolonom search_key kao kolona 1
- Ako je col_index_num podešen na broj koji je veći od broja kolona izabranih u argumentu table_array a #REF! greška se vraća od funkcije
range_lookup - (opcionalno) označava da li se opseg sortira u rastućem redosledu.
- Podaci u prvoj koloni se koriste kao ključ za sortiranje
- Boolova vrijednost - TRUE ili FALSE su jedine prihvatljive vrijednosti
- Ako je izostavljeno, vrednost je podrazumevano postavljena na TRUE
- Ako je postavljeno na TRUE ili ispušteno i prva kolona opsega nije sortirana po rastućem redosledu, može doći do pogrešnog rezultata
- Ako je postavljeno na TRUE ili ispušteno i nije pronađeno tačno odgovara za vrijednost za traženje , najbliži rezultat koji je manji po veličini ili vrijednosti koristi se kao search_key
- Ako je postavljeno na FALSE, VLOOKUP prihvata samo tačan podudar za traženje _value . Ako postoje više odgovarajućih vrijednosti, vraća se prva odgovarajuća vrijednost
- Ako je podešeno na FALSE i ne pronađena odgovarajuća vrijednost za search_key , funkcija # N / A vraća funkcija
Primer: Nađite stopu popusta za količinu kupljenu
Primjer na gornjoj slici koristi funkciju VLOOKUP da pronađe diskontnu stopu koja se razlikuje u zavisnosti od količine kupljenih predmeta.
Primer pokazuje da je popust za kupovinu 19 stavki 2%. To je zato što kolona Količina sadrži opsege vrednosti. Kao rezultat, VLOOKUP ne može pronaći tačan podudar. Umesto toga, mora se pronaći približni poklapaj kako bi se vratila tačna diskontna stopa.
Da biste pronašli približne utakmice:
- sortirati podatke u table_array u rastućem redosledu;
- postavite argument range_lookup na TRUE
U ovom primeru, sledeća formula koja sadrži VLOOKUP funkciju koristi se za pronalaženje popusta za količinu kupljene robe.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Iako se ova formula može upisati u ćeliju radnog lista, druga opcija, koja se koristi s koracima navedenim u nastavku, je da koristite dijalog funkcije funkcije da unosite svoje argumente.
- Korišćenje dijaloga često olakšava pravilno unos argumenata funkcije.
Otvaranje dijaloga VLOOKUP
Koraci koji se koriste za unos VLOOKUP funkcije prikazanih na slici iznad u ćeliju B2 su:
- Kliknite na ćeliju B2 da biste postali aktivna ćelija - lokaciju na kojoj se prikazuju rezultati funkcije VLOOKUP
- Kliknite na karticu Formule .
- Odaberite traku i referencu sa trake da biste otvorili listu padajućih funkcija
- Kliknite na VLOOKUP na listi da biste prikazali dijaloški okvir funkcije
02 od 03
Unošenje argumenata Excelove VLOOKUP funkcije
Pokazujući na reference ćelija
Argumenti za VLOOKUP funkciju unose se u odvojene linije dijalog box-a kako je prikazano na slici iznad.
Referentne ćelije koje se koriste kao argument mogu se otkucati u tačnu liniju ili, kako je to učinjeno u koracima ispod, pokazivanje, što uključuje označavanje željenog opsega ćelija pomoću pokazivača miša, može se koristiti za unos u dijalog box .
Prednosti korišćenja pokazivača uključuju:
- Brže od pisanja;
- Manje greške se unose u ispravne reference ćelija.
Upotreba relativnih i apsolutnih referentnih ćelija sa Argumentima
Nije neuobičajeno da koristite više kopija VLOOKUP-a da biste vratili različite informacije iz iste tabele podataka. Da bi se to olakšalo, često se VLOOKUP može kopirati iz jedne ćelije u drugu. Kada se funkcije kopiraju u druge ćelije, mora se voditi računa da se referentne reference ćelija budu tačne s obzirom na novu lokaciju funkcije.
Na slici iznad, znakovi dolara ( $ ) okružuju reference ćelija za argument table_array koji ukazuju da su apsolutne reference ćelija , što znači da se neće menjati ako se funkcija kopira u drugu ćeliju. Ovo je poželjno jer će više kopija VLOOKUP-a sve referentirati istu tabelu podataka kao izvor informacija.
Referenca ćelije koja se koristi za lookup_value, s druge strane , nije okružena znakom dolara, što ga čini referentnim referentnim ćelijama. Relativne referentne ćelije se menjaju kada se kopiraju da bi odrazile njihovu novu lokaciju u odnosu na položaj podataka na koje se odnose.
Unošenje argumenata funkcije
- Kliknite na dugme Lookup _value u dijalogu VLOOKUP
- Kliknite na ćeliju C2 na radnom listu da biste uneli ovu referencu ćelije kao argument za pretragu
- Kliknite na liniju Table_array u dijalog box-u
- Istaknite ćelije C5 do D8 na radnom listu da biste uneli ovaj opseg kao argument table_array - zaglavlja tablice nisu uključene
- Pritisnite taster F4 na tastaturi da biste promenili opseg do apsolutnih referenci ćelije
- Kliknite na Col_index_num liniju dijalog box-a
- Ukucajte 2 na ovoj liniji kao argument Col_index_num , s obzirom da se diskontne stope nalaze u koloni 2 argumenata Table_array
- Kliknite na Range_lookup liniju dijaloga
- Upišite riječi True kao Range_lookup argument
- Pritisnite taster Enter na tastaturi da biste zatvorili dijalog i vratili se na radni list
- Odgovor 2% (diskontna stopa za kupljenu količinu) treba da se pojavi u ćeliji D2 radnog lista
- Kada kliknete na ćeliju D2, potpuna funkcija = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) se pojavljuje u traci formule iznad radnog lista
Zašto je VLOOKUP povratio 2% kao rezultat
- U primjeru kolona Količina ne sadrži točno podudaranje vrijednosti za pretragu 19.
- Pošto je argument is_sorted postavljen na TRUE, VLOOKUP će pronaći približnu vrijednost za vrijednost search_key .
- Najbliža vrednost u veličini koja je i dalje manja od vrednosti 19 za pretragu je 11.
- VLOOKUP, stoga, traži popust u procentima koji sadrže 11, i kao rezultat, vraća diskontnu stopu od 2%.
03 od 03
Excel VLOOKUP ne radi: # N / A i #REF greške
VLOOKUP Poruke o greškama
Sledeće greške su povezane sa VLOOKUP-om.
A # N / A ("vrijednost nije dostupna") Greška je prikazana Ako:
- Traži se _value u prvoj koloni argumenta raspona
- Argument Table_array je netačan. Na primer, argument može sadržati prazne kolone sa leve strane opsega
- Argument Range_lookup je postavljen na FALSE i tačna podudarnost za argument search_key ne može se naći u prvoj koloni opsega
- Argument Range_lookup je postavljen na TRUE i sve vrednosti u prvoj koloni opsega su veće od pretraživača
#REF! ("referenca izvan opsega") Greška je prikazana Ako:
- Argument Col_index_num je veći od broja kolona u Table_array