Prenesite primer delovnega zvezka
Ta vadnica prikazuje, kako uporabljati Excel VLOOKUP funkcija v Excelu poiščite vrednost.
Pregled funkcije VLOOKUP
Funkcija VLOOKUP Vlookup pomeni navpično iskanje. Išče vrednost v skrajnem levem stolpcu tabele. Nato vrne vrednost določeno število stolpcev desno od najdene vrednosti. Enako je kot hlookup, le da gleda vrednosti navpično namesto vodoravno.
(Upoštevajte, kako so prikazani vnosi formule)
Sintaksa in argumenti funkcije VLOOKUP:
| 1 | = VLOOKUP (iskalna_vrednost, matrika_mase, stolpec_indeksa_številka, obseg_ogled) |
lookup_value - Vrednost, ki jo želite iskati.
matrika_maza - Področje podatkov, ki vsebuje vrednost, ki jo želite poiskati, in vrednost, za katero želite, da vlookup vrne. Stolpec, ki vsebuje iskalne vrednosti, mora biti najbolj levi stolpec.
col_index_num - Številka stolpca obsega podatkov, iz katerega želite vrniti vrednost.
range_lookup - Pravilno ali napačno. FALSE išče natančno ujemanje. TRUE išče najbližje ujemanje, ki je manjše ali enako vrednosti lookup_value. Če izberete TRUE, mora biti levi stolpec (iskalni stolpec) razvrščen naraščajoče (od najnižjega do najvišjega).
Kaj je funkcija VLOOKUP?
Kot ena starejših funkcij v svetu preglednic se funkcija VLOOKUP uporablja Vetično Iskanje. Ima nekaj omejitev, ki jih pogosto premagamo z drugimi funkcijami, kot je INDEX/MATCH. Vendar pa ima prednost, ker je ena sama funkcija, ki lahko sama poišče natančno ujemanje. Prav tako je ponavadi ena prvih funkcij, o katerih se ljudje naučijo.
Osnovni primer
Poglejmo vzorec podatkov iz razredne knjige. Obravnavali bomo več primerov pridobivanja informacij za določene študente.
Če želimo ugotoviti, v katerem razredu je Bob, bi zapisali formulo:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Pomembno si zapomnite, da mora biti predmet, ki ga iščemo (Bob), v prvem stolpcu našega obsega iskanja (A2: C5). Funkciji smo povedali, da želimo vrniti vrednost iz 2nd stolpec, ki je v tem primeru stolpec B. Nazadnje smo navedli, da želimo narediti an natančno ujemanje tako da za zadnji argument umesti False. Tukaj bo odgovor "Branje".
Stranski nasvet: Kot zadnji argument lahko uporabite tudi številko 0 namesto False, saj imata enako vrednost. Nekaterim je to všeč, ker je hitreje pisati. Samo vedite, da je oboje sprejemljivo.
Premaknjeni podatki
Če želimo prvemu primeru dodati nekaj pojasnil, ni nujno, da je iskalni element v stolpcu A vaše preglednice, ampak le prvi stolpec vašega obsega iskanja. Uporabimo isti nabor podatkov:
Zdaj pa poiščimo oceno za razred Znanost. Naša formula bi bila
| 1 | = VLOOKUP ("Znanost", B2: C5, 2, FALSE) |
To je še vedno veljavna formula, saj je prvi stolpec našega obsega iskanja stolpec B, kjer bo naš iskalni izraz »Znanost«. Vrnemo vrednost iz 2nd stolpec obsega iskanja, ki je v tem primeru stolpec C. Odgovor je nato “A-“.
Uporaba nadomestnih znakov
Funkcija VLOOKUP podpira uporabo nadomestnih znakov "*" in "?" pri iskanju. Na primer, recimo, da smo pozabili, kako se piše Frankovo ime, in samo želeli smo poiskati ime, ki se začne s "F". Formulo bi lahko zapisali
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Tako bi lahko v 5. vrstici našli ime Frank in nato vrnili vrednost iz 2nd relativni stolpec. V tem primeru bo odgovor "znanost".
Nenatančno ujemanje
Večino časa se boste morali prepričati, da je zadnji argument v VLOOKUP napačen (ali 0), da dobite natančno ujemanje. Vendar pa lahko nekajkrat iščete nenatančno ujemanje. Če imate seznam razvrščenih podatkov, lahko uporabite tudi VLOOKUP, da vrnete rezultat za element, ki je enak ali naslednji najmanjši. To se pogosto uporablja pri obravnavi naraščajočih razponov številk, na primer v davčni tabeli ali provizijah.
Recimo, da želite najti davčno stopnjo za dohodek, vnesen v celico D2. Formula v D4 je lahko:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Razlika v tej formuli je, da je naš zadnji argument "res". V našem konkretnem primeru lahko vidimo, da bodo imeli, ko bodo naši posamezniki vnesli dohodek v višini 45.000 USD, davčno stopnjo 15%.
Opomba: Čeprav ponavadi želimo natančno ujemanje z False kot argumentom, pozabite navesti 4th Argument v VLOOKUP -u je privzeta vrednost True. To lahko povzroči nepričakovane rezultate, zlasti pri obravnavi besedilnih vrednosti.
Dinamični stolpec
VLOOKUP zahteva, da navedete argument, iz katerega stolpca želite vrniti vrednost, vendar se lahko pojavi priložnost, ko ne veste, kje bo stolpec, ali pa želite uporabniku omogočiti, da spremeni, iz katerega stolpca se vrne. V teh primerih je lahko koristno uporabiti funkcijo MATCH za določitev številke stolpca.
Ponovno razmislimo o našem primeru knjižice z nekaj vložki v E2 in E4. Da bi dobili številko stolpca, bi lahko napisali formulo
| 1 | = MATCH (E2, A1: C1, 0) |
S tem boste poskušali najti natančen položaj "stopnje" v območju A1: C1. Odgovor bo 3. Če to vemo, ga lahko priključimo v funkcijo VLOOKUP in formulo v E6 napišemo takole:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Torej bo funkcija MATCH ocenjena na 3, kar pove VLOOKUP -u, naj vrne rezultat iz 3rd stolpec v območju A2: C5. Na splošno dobimo želeni rezultat "C". Naša formula je zdaj dinamična, saj lahko spremenimo stolpec za ogled ali ime za iskanje.
Omejitve VLOOKUP
Kot je bilo omenjeno na začetku članka, je največji padec VLOOKUP -a ta, da zahteva iskalni izraz v skrajnem levem stolpcu iskalnega območja. Čeprav obstaja nekaj domiselnih trikov, ki jih lahko odpravite, je skupna alternativa uporaba INDEX in MATCH. Ta kombinacija vam daje večjo prilagodljivost, včasih pa je lahko celo hitrejši izračun.
VLOOKUP v Google Preglednicah
Funkcija VLOOKUP deluje v Google Preglednicah popolnoma enako kot v Excelu:
Dodatne opombe
S funkcijo VLOOKUP izvedite VERTICAL iskanje. VLOOKUP išče natančno ujemanje (range_lookup = FALSE) ali najbližje ujemanje, ki je enako ali manjše od lookup_value (alirange_lookup = TRUE, samo številčne vrednosti) v prvi vrstici matrike_tabel. Nato vrne ustrezno vrednost, n število vrstic pod ujemanjem.
Ko za iskanje natančnega ujemanja uporabite VLOOKUP, najprej določite identifikacijsko vrednost, ki jo želite poiskati kot lookup_value. Ta identifikacijska vrednost je lahko SSN, ID zaposlenega, ime ali kak drug edinstven identifikator.
Nato določite obseg (imenovan matrika_maza), ki vsebuje identifikatorje v zgornji vrstici in vse vrednosti, ki jih na koncu želite poiskati v vrsticah pod njo. POMEMBNO: Enolični identifikatorji morajo biti v zgornji vrstici. Če niso, morate vrstico premakniti na vrh ali namesto VLOOKUP uporabiti MATCH / INDEX.
Tretjič, določite številko vrstice (row_index) od matrika_maza ki se ga želite vrniti. Upoštevajte, da je prva vrstica z edinstvenimi identifikatorji vrstica 1. Druga vrstica je vrstica 2 itd.
Nazadnje morate v datoteki range_lookup. Če je izbrana možnost natančnega ujemanja in natančnega ujemanja ni, se vrne napaka (#N/A). Če želite, da formula vrne prazno ali "ni najdeno" ali katero koli drugo vrednost namesto vrednosti napake (#N/A), uporabite funkcijo IFERROR z VLOOKUP.
Če želite uporabiti funkcijo VLOOKUP za vrnitev približnega niza ujemanj: range_lookup = RES. Ta možnost je na voljo samo za številske vrednosti. Vrednosti je treba razvrstiti po naraščajočem vrstnem redu.
Za vodoravno iskanje uporabite funkcijo HLOOKUP.
VLOOKUP Primeri v VBA
V VBA lahko uporabite tudi funkcijo VLOOKUP. Vrsta:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Za argumente funkcije (lookup_value itd.) Jih lahko vnesete neposredno v funkcijo ali pa namesto tega definirate spremenljivke.
Vrnite se na seznam vseh funkcij v Excelu
