Tere! Kujutage ette, et teil on Excelis tuhandeid ridu töötajate andmeid ja teie ülemus küsib teilt juhuslikult palka ja Ramesh. Või veel hullem, ta annab teile saja töötaja nimekirja ja palub teil saata nende töötajate andmed. Mida sa teeksid? Te ei leia seda käsitsi. See pole lihtsalt teostatav ja üldse mitte tark. Aga sa oled tark, sellepärast sa oled siin. Lubage mul tutvustada teile Exceli funktsiooni VLOOKUP.
Miks me vajame VLOOKUPi?
Mis on VLOOKUP? VLOOKUP tähistab vertikaalset otsingut.
VLOOKUP otsib tabeli esimesest veerust lihtsalt antud väärtuse rea ja tagastab küsitud veeru väärtuse selles reas.
VLOOKUPi süntaks:
= VLOOKUP (otsingu_väärtus, tabeli_massiiv, veeru_indeksi_arv, [vahemiku_vaade])
Otsinguväärtus: Väärtus, mille järgi soovite otsida tabelimassiivi esimeses veerus.
Tabelimassiiv: Tabel, kust soovite otsida/otsida
col_index_number: Tabeli massiivi veerunumber, millest soovite tulemusi tuua.
[vahemiku_vaade]: VÄÄR, kui soovite otsida täpset väärtust, TÕENE, kui soovite ligikaudset vastet.
Ok, piisab teooriast. Hüppame Exceli VLOOKUP näite juurde.
VLOOKUP Näide 1.
Exceli lehel on teil need andmed töötajatelt. Esimeses veerus (veerg A) on teil töötajate nimed. Järgmisena on teil nende nimetus ja nii edasi, nagu on näidatud alloleval pildil.
Lihtsuse huvides olen võtnud väikese laua.
Nüüd saatis teie ülemus teile selle nimekirja ja küsis üksikasju tühjades veergudes.
Peate ütlema Rameshile ja Divyale palgad ja nimetused. Kuidas te seda teeksite?
Selleks saate otsida käsitsi või… Või võite kasutada funktsiooni Excel VLOOKUP.
Lihtsalt kirjutage see VLOOKUP valem lahtrisse H2 ja kopeerige see allolevasse lahtrisse:
= VLOOKUP (G3, $ A $ 3: $ D $ 11,4, FALSE)
Saate tulemused, nagu allpool näidatud.
Selgitus:
Siin käskisime excelil Rameshi otsida (G3) esimeses veerus (A) tabelist $ A $ 3: $ D $ 11.
Kui antud väärtus leitakse, tagastage väärtus neljandas (4) veergu sellel tabelil.
Andes sellele vahemikus_vaade VÄÄR, ütlete VLOOKUP -ile, et see leiaks täpse vaste (ei ifs n buts).
Siin on Ramesh esimeses veerus A ja tema palk tagastatakse 105347 -na.
Sarnaselt otsib VLOOKUP veerust Divjat ja naaseb #Ei ole viga, kuna Divya rekordit pole teie tabelis.
Kas näete siin VLOOKUPi muud kasutamist? Funktsiooni VLOOKUP abil saate teada, kas loendis on väärtus või mitte. Samuti saate sobitada kaks loendit ja teada, millised väärtused on neis ühised.
Pro nõuanne: lukustage alati tabeli_massiivi viide, kui kasutate Excelis funktsiooni VLOOKUP. Vastasel juhul muutub teie tabelimassiiv, kui kopeerite valemi teistesse lahtritesse.
See on vaikimisi, kui VLOOKUP teiselt lehelt või töövihikust, kuid samal lehel, peate seda tegema käsitsi.
Kas saate Rameshi nimetuse saamiseks kirjutada VLOOKUP valemi? Kommentaaride jaotis on kõik teie.
Ülaltoodud näites nägime VLOOKUPi näidet, kus soovisime täpset vastet. Aga mis siis, kui tahame teada ligikaudset vastet?
Millal kasutada VLOOKUPis ligikaudset vastet kui Range_lookup?
Mõistame seda teise VLOOKUP näitega.
Näide 2: ligikaudse vaste kasutamine VLOOKUPis
Vastasite oma ülemuse päringule õigesti, kasutades VLOOKUP -i. Nüüd soovib teie "kallis" ülemus teada lähimat palka 150000 -ni (ainult alla).
Sellele vastamiseks kasutame taas VLOOKUP -i, kuid seekord ligikaudse vastega.
Oluline märkus: Ligikaudse vaste leidmiseks peate oma andmed sorteerima. Siin olen andmed palga järgi kasvavas järjekorras sorteerinud.
Lahtrisse H2 kirjutage see valem ja lohistage see allolevasse lahtrisse:
= VLOOKUP (G9, $ D $ 3: $ D $ 11,1,1)
Märkus: 1 tõlgendatakse kui TÕSI ja 0 nagu VALE Excelis.
Lõppkokkuvõttes saate tulemuse, mis näeb välja selline:
Selgitus.
Meie otsinguväärtus on 150000 (G9) ja me tahame sellele lähimat palka.
Meie valik on $ 3 $: $ 11 $ kuna tahame väärtuse tagastada samast veerust. Seetõttu on ka col_index_num 1. Ja kuna me soovime ligikaudset vastet, siis andsime vahemiku_vaade 1 (ÕIGE).
Ligikaudse vaste saamiseks tuleb teie andmed sorteerida. Vastasel juhul toimib Exceli VLOOKUP vale tulemusena.
Olulised märkused:
-
- VLOOKUP töötab alati vasakult paremale. Te ei saa väärtust tuua esimese veeru paremalt poolt.
- Veeru numeratsioon Alustab valitud vahemikust. Näiteks kui esitate tabelimassiivi D4: F10. Siis algab veergude arv D -ga, mitte tabeli tegelikust algusest.
- Alati lukustage viide tabelile, kasutades märki $, et vältida viga Microsoft Excel 2016 V-otsinguvalemi kopeerimisel.
- Otsinguväärtuses saate kasutada metamärki (*), kui mäletate ainult mõnda otsinguväärtuse osa.
- Seadistage Range_Lookup väärtusele FALSE (0), et saada oma väärtusele täpne vaste.
- Määrake Range_Lookup väärtuseks TRUE (1), et saada lähim väärtus, mis on väiksem kui antud väärtus.
- Ligikaudse vaste saamiseks sorteerige andmed alati kasvavas järjekorras.
Nüüd saate oma ülemuse päringule vastata sekunditega. Saate kahte loendit hõlpsalt võrrelda. Saate teha hulgiotsingu, kasutades rakendust VLOOKUP Excelis 2016, 2013, 2010 ja Exceli vanemates versioonides sekunditega.
Kas sellest oli siis abi? Kui ei, siis andke mulle oma täpne päring kommentaaride jaotises teada.
17 asja Exceli kohta VLOOKUP
Vlookup top 5 väärtust dubleerivate väärtustega, kasutades Excelis INDEX-MATCH
VLOOKUP Mitu väärtust
VLOOKUP koos dünaamilise värvide indeksiga
Osaline vaste funktsiooniga VLOOKUP
Kasutage kahe või enama otsingutabeli VLOOKUP -i
Vlookup Excelis kuupäeva järgi
VLOOKUP -valemi kasutamine väärtuse olemasolu kontrollimiseks
Populaarsed artiklid
Kuidas otsida erinevatelt Exceli lehtedelt
VLOOKUP numbrite ja tekstiga
Funktsioon IF, ISNA ja VLOOKUP
Funktsioon ISNA ja VLOOKUP
Funktsioon IFERROR ja VLOOKUP