Laadige alla töövihiku näide
See õpetus näitab, kuidas Funktsioon Excel VLOOKUP Excelis väärtuse otsimiseks.
Funktsiooni VLOOKUP ülevaade
Funktsioon VLOOKUP Vlookup tähistab vertikaalset otsingut. See otsib väärtust tabeli vasakpoolses veerus. Seejärel tagastab leitud väärtusest paremale määratud arvu veerge. See on sama mis hlookup, välja arvatud see, et see otsib väärtusi horisontaalse asemel vertikaalselt.
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsiooni VLOOKUP süntaks ja argumendid:
| 1 | = VLOOKUP (otsingu_väärtus, tabeli_massiiv, veeru_indeksi_arv, vahemiku_vaade) |
lookup_value - Väärtus, mida soovite otsida.
tabel_massiiv - Andmevahemik, mis sisaldab nii väärtust, mida soovite otsida, kui ka väärtust, mida soovite, et vlookup tagastaks. Otsinguväärtusi sisaldav veerg peab olema kõige vasakpoolsem.
col_index_num - Andmevahemiku veerunumber, kust soovite väärtuse tagastada.
vahemiku_vaade - Õige või vale. FALSE otsib täpset vastet. TRUE otsib lähimat vastet, mis on väiksem või võrdne otsinguväärtusega. Kui valite TRUE, tuleb kõige vasakpoolsem veerg (otsinguveerg) sortida kasvavalt (madalaimast kõrgeimani).
Mis on funktsioon VLOOKUP?
Arvutustabelite maailma ühe vanema funktsioonina kasutatakse funktsiooni VLOOKUP Vertiline Otsingud. Sellel on mõned piirangud, mis sageli ületatakse muude funktsioonidega, näiteks INDEX/MATCH. Selle eeliseks on aga see, et see on üks funktsioon, mis suudab iseenesest teha täpse vaste otsingu. Samuti kipub see olema üks esimesi funktsioone, mida inimesed õpivad.
Põhinäide
Vaatame hinderaamatu andmete näidist. Me käsitleme mitmeid näiteid teabe hankimiseks konkreetsete õpilaste jaoks.
Kui tahame teada, mis klassi Bob kuulub, kirjutaksime järgmise valemi:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Oluline on meeles pidada, et üksus, mida otsime (Bob), peab olema meie otsinguvahemiku esimeses veerus (A2: C5). Oleme funktsioonile öelnud, et tahame väärtuse 2 -st tagastadand veergu, mis antud juhul on veerg B. Lõpuks märkisime, et tahame teha an täpne vaste pannes viimaseks argumendiks False. Siin on vastus "lugemine".
Küljeots: Viimase argumendina võite kasutada ka väärtuse Väärtuse asemel numbrit 0, kuna neil on sama väärtus. Mõned inimesed eelistavad seda, sest kirjutamine on kiirem. Lihtsalt teadke, et mõlemad on vastuvõetavad.
Nihutatud andmed
Meie esimesele näitele selgituse lisamiseks ei pea otsinguüksus olema arvutustabeli veerus A, vaid teie otsinguvahemiku esimeses veerus. Kasutame sama andmekogumit:
Leiame nüüd loodusteaduste klassi hinde. Meie valem oleks
| 1 | = VLOOKUP ("Teadus", B2: C5, 2, VÄÄR) |
See on endiselt kehtiv valem, kuna meie otsinguvahemiku esimene veerg on veerg B, kust leitakse meie otsingusõna „Teadus”. Anname tagasi väärtuse 2 -stnd otsinguvahemiku veergu, mis antud juhul on veerg C. Vastus on siis „A-“.
Metamärkide kasutamine
Funktsioon VLOOKUP toetab metamärkide “*” ja “?” Kasutamist otsinguid tehes. Oletame näiteks, et olime unustanud Franki nime kirjutamise ja tahtsime lihtsalt otsida nime, mis algab tähega "F". Võiksime valemi kirjutada
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Nii oleks võimalik leida nimi Frank realt 5 ja seejärel väärtus 2 tagasi andand suhteline veerg. Sel juhul on vastus "Teadus".
Mitte täpne vaste
Enamasti soovite täpse vaste saamiseks veenduda, et VLOOKUPi viimane argument on vale (või 0). Siiski on mõni kord, kui otsite ebatäpset vastet. Kui teil on sorteeritud andmete loend, saate funktsiooni VLOOKUP abil ka tagastada sama või järgmise väikseima üksuse tulemuse. Seda kasutatakse sageli suurenevate numbrivahemikega tegelemisel, näiteks maksutabelis või vahendustasudes.
Oletame, et soovite leida tulu lahtrisse D2 tulumaksumäära. D4 valem võib olla järgmine:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Selle valemi erinevus seisneb selles, et meie viimane argument on “tõene”. Meie konkreetses näites näeme, et kui meie isik sisendab tulu 45 000 dollarit, on neil maksumäär 15%.
Märge: Kuigi tavaliselt tahame täpset vastet, mille argument on vale, unustate 4th argumendis VLOOKUPis, on vaikimisi True. See võib põhjustada ootamatuid tulemusi, eriti tekstiväärtuste käsitlemisel.
Dünaamiline veerg
VLOOKUP nõuab argumendi esitamist, millisest veerust soovite väärtuse tagastada, kuid mõnikord võib juhtuda, kui te ei tea, kus veerg asub, või soovite lubada kasutajal muuta, millisest veerust naasta. Sellistel juhtudel võib olla kasulik veeru numbri määramiseks kasutada funktsiooni MATCH.
Mõelgem uuesti oma näiteraamatu näitele, mõned sisendid on E2 ja E4. Veeru numbri saamiseks võiksime kirjutada valemi
| 1 | = MATCH (E2, A1: C1, 0) |
See püüab leida täpset „Hinne” positsiooni vahemikus A1: C1. Vastus on 3. Seda teades saame selle ühendada funktsiooni VLOOKUP ja kirjutada valemi E6 järgmiselt:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Seega hindab funktsioon MATCH väärtuseks 3 ja see käsib VLOOKUP -il saada tulemuse kolmestrd veerus vahemikus A2: C5. Üldiselt saame soovitud tulemuse "C". Meie valem on nüüd dünaamiline, kuna saame muuta kas vaadatavat veergu või otsitavat nime.
VLOOKUPi piirangud
Nagu artikli alguses mainitud, on VLOOKUPi suurim langus see, et see nõuab otsingutermini leidmist otsinguvahemiku kõige vasakpoolses veerus. Kuigi selle ületamiseks saate teha mõningaid väljamõeldud trikke, on tavaline alternatiiv kasutada INDEX ja MATCH. See kombinatsioon annab teile rohkem paindlikkust ja mõnikord võib see olla isegi kiirem arvutus.
VLOOKUP Google'i arvutustabelites
Funktsioon VLOOKUP töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
lisamärkmed
Kasutage funktsiooni VLOOKUP, et teha vertikaalne otsing. VLOOKUP otsib täpset vastet (vahemiku_vaade = FALSE) või lähim vaste, mis on võrdne või väiksem kui lookup_value (vahemiku_vaade = TRUE, ainult numbrilised väärtused) tabeli_massiivi esimesel real. Seejärel tagastab see vastava väärtuse, n ridade arvu vaste all.
Kui kasutate VLOOKUP -i täpse vaste leidmiseks, määratlege esmalt identifitseeriv väärtus, mida soovite otsida lookup_value. See identifitseeriv väärtus võib olla SSN, töötaja ID, nimi või mõni muu kordumatu tunnus.
Järgmisena määrate vahemiku (nn tabel_massiiv), mis sisaldab ülemise rea identifikaatoreid ja väärtusi, mida soovite selle all olevatelt ridadelt otsida. TÄHTIS: unikaalsed identifikaatorid peavad olema ülemisel real. Kui neid pole, peate rea kas üles viima või VLOOKUPi asemel kasutama MATCH / INDEX.
Kolmandaks määrake rea number (rida_indeks) selle tabel_massiiv et soovite tagasi tulla. Pidage meeles, et esimene rida, mis sisaldab kordumatuid tunnuseid, on rida 1. Teine rida on rida 2 jne.
Lõpuks peate kaustast märkima, kas otsida täpset vastet (FALSE) või lähimat vastet (TRUE) vahemiku_vaade. Kui valitud on täpne vaste ja täpset vastet ei leita, tagastatakse tõrge (#N/A). Kui soovite, et valem sisestaks vea väärtuse (#N/A) asemel tühja või „ei leitud” või mõne muu väärtuse, kasutage funktsiooni IFERROR koos VLOOKUP -iga.
Funktsiooni VLOOKUP kasutamine ligikaudse vaste komplekti tagastamiseks: vahemiku_vaade = ÕIGE. See valik on saadaval ainult numbriliste väärtuste puhul. Väärtused tuleb sortida kasvavas järjekorras.
Horisontaalse otsingu tegemiseks kasutage selle asemel funktsiooni HLOOKUP.
VLOOKUP Näited VBA -s
Funktsiooni VLOOKUP saate kasutada ka VBA -s. Tüüp:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Funktsiooniargumentide (lookup_value jne) jaoks saate need kas otse funktsiooni sisestada või määrata selle asemel kasutatavad muutujad.
Tagasi kõigi Exceli funktsioonide loendisse
