Exceli VLOOKUPi õpetus algajatele

Mis on VLOOKUP?

VLOOKUP (V tähistab Vertical) on Exceli sisseehitatud funktsioon, mis loob seose arvutustabeli veergude vahel. See võimaldab teil otsida väärtust ühest veerust ja tagastada vastava väärtuse sama rea ​​teisest veerust.

VLOOKUPi süntaks ja argumendid

Enne VLOOKUP-i rakendamist on kasulik mõista valemi struktuuri. Funktsioon võtab vastu neli argumenti ja järgib ühtset mustrit kõigis Exceli versioonides.

• lookup_value — väärtus, mida soovite leida (lahtriviide või literaal).
• table_array — otsingu- ja tagastusveergu sisaldavate lahtrite vahemik.
• col_index_num — veeru number tabeli_massiivis, millest väärtus tagastada (1 on vasakpoolne).
• vahemiku_otsing — FALSE täpse vaste korral, TRUE (või puudub) ligikaudse vaste korral sorteeritud andmetel.

NB! Otsitav väärtus peab asuma tabeli_massiivi vasakpoolseimas veerus ja VLOOKUP otsib ainult vasakult paremale.

VLOOKUPi kasutamine

Kui teil on vaja leida kindlat teavet suurest arvutustabelist või hankida sama tüüpi väärtust korduvalt, säästab VLOOKUP käsitsi filtreerimisega võrreldes märkimisväärselt aega.

Mõtle a Ettevõtte palgatabel finantsmeeskonna hallatav. Alustate teadaoleva teabega – indeksiga – ja kasutate tundmatu väärtuse toomiseks funktsiooni VLOOKUP.

Näiteks teate juba töötaja nime:

Ja sa tahad otsida töötaja palka:

Exceli tabel ülaltoodud näite jaoks:

Laadige alla ülaltoodud Exceli fail

Tundmatu töötaja palga leidmiseks sisestame töötaja Code mis on juba saadaval.

VLOOKUP-i rakendades saadakse sellele töötajale vastav palgaväärtus. Code ilmub automaatselt.

Kuidas kasutada Excelis funktsiooni VLOOKUP

VLOOKUP-funktsiooni rakendamiseks Excelis järgige seda samm-sammult juhendit:

1. samm) Liikuge sihtlahtrisse

Klõpsake lahtrit, kuhu soovite valitud töötaja palga kuvada – selles näites lahtrit H3.

2. samm) Sisestage VLOOKUP funktsioon =VLOOKUP()

Tippige lahtrisse funktsioon. Alustage võrdusmärgiga (mis annab Excelile teada, et järgneb valem) ja seejärel märksõnaga VLOOKUP: =VLOOKUP().

Sulgudes on argumentide hulk (andmed, mida funktsioon vajab).

VLOOKUP nõuab nelja argumenti:

3. samm) Esimene argument — otsinguväärtus

Esimene argument on otsitava väärtuse lahtriviide. Antud juhul on töötaja Code on otsinguväärtus, seega on esimene argument H2 – lahter, mille sisule Excel peaks vaste leidma.

4. samm) Teine argument — tabeli massiiv

See viitab otsitavate väärtuste plokile, mida Excelis nimetatakse tabeli massiiv või otsingutabel. Meie näites töötab otsingutabel B2-st E25-ni.

MÄRKUS: Otsinguveerg peab olema teie tabeli massiivi vasakpoolseim veerg.

5. samm) Kolmas argument — veeru_indeksi_number

See annab VLOOKUP-ile teada, millises tabeli massiivi veerus tagastusväärtus asub. Töötaja palk asub neljandas veerus, seega on veeruindeks 4.

6. samm) Neljas argument — täpne või ligikaudne vaste

Viimane argument on vahemiku otsingu lipp. See kontrollib, kas VLOOKUP tagastab täpse või ligikaudse vaste. Siin soovime täpset vastet (FALSE).

1. FALSE — täpne vaste.
2. TRUE — ligikaudne vaste.

Samm 7) Vajutage sisestusklahvi

Valemi täitmiseks vajutage sisestusklahvi (Enter). Alguses näete veateadet, kuna töötajat pole. Code pole veel H2-sse kantud.

Kui olete sisestanud kehtiva töötaja Code Lahtris H2 tagastab lahter vastava töötaja palga.

Lühidalt öeldes ütleb valem Excelile, et teadaolevad väärtused asuvad andmete vasakpoolseimas veerus (Töötaja Code). Seejärel skannib VLOOKUP tabelit ja tagastab vastava rea ​​neljanda veeru väärtuse – töötaja palga.

See näide hõlmas täpseid vasteid (märksõna FALSE). Järgmises osas selgitatakse ligikaudseid vasteid.

VLOOKUP ligikaudsete vastete jaoks (tÕene märksõna viimase parameetrina)

Kujutage ette stsenaariumi, kus tabel arvutab allahindlusi klientidele, kes ei osta täpselt kümneid või sadu esemeid.

Nagu allpool näidatud, rakendab ettevõte allahindlusi kogustele vahemikus 1 kuni 10 000:

Laadige alla ülaltoodud Exceli fail

Klient ostab harva täpselt 100 või 1,000 ühikut. Ligikaudse vaste režiim võimaldab VLOOKUP-il leida lähima madalama väärtuse, selle asemel et nõuda täpset arvu. Toimingud:

Step 1) Klõpsake lahtrit, kuhu VLOOKUP-funktsioon läheb – lahtriviide I2.

Step 2) Sisesta lahtrisse =VLOOKUP() ja lisa sulgudes olevad argumendid.

3. samm) Argument 1: Sisesta lahtriviide, mille väärtust tuleks otsingutabeliga võrrelda.

4. samm) Argument 2: Valige otsingutabel – siin veerud Kogus ja Allahindlus.

5. samm) Argument 3: Sisestage otsingutabeli veeruindeks, kust vastav väärtus tagastada.

6. samm) Argument 4: Määra viimaseks argumendiks TRUE ligikaudsete vastete saamiseks.

Step 7) Vajutage sisestusklahvi (Enter). Valem rakendub nüüd lahtrile. Koguse sisestamisel tagastab Excel allahindlusvahemiku ligikaudse vaste põhjal.

MÄRKUS: Kui jätate neljanda argumendi tühjaks, määrab Excel vaikimisi väärtuseks TRUE (ligikaudne vaste). Ligikaudsete vastete korral tuleb otsinguveerg sortida kasvavas järjekorras.

Vlookup-funktsiooni rakendatakse samasse töövihikusse paigutatud kahe erineva lehe vahel

Nüüd vaatleme töövihikut, millel on kaks lehte. 1. lehel on loetletud töötaja Code, Nimi ja ametinimetus; 2. lehel on loetletud töötaja Code ja töötaja palk.

LEHT 1:

LEHT 2:

Laadige alla ülaltoodud Exceli fail

Eesmärk on koondada kõik andmed lehele 1, nagu allpool näidatud:

VLOOKUP saab andmeid koondada, et töötaja saaks Code, Nimi ja Palk kuvatakse koos ühel lehel.

Alustame 2. lehelt, sest see annab kaks argumenti – siin on töötaja palga veerg ja veeruindeks on 2.

Me tahame leida palga, mis sobib igale töötajale. Code.

Andmed jooksevad lahtrisse A2 kuni lahtrisse B25 – see on meie tabelimassiiv.

Step 1) Lülitu lehele 1 ja sisesta kuvatud pealkirjad.

Step 2) Klõpsake lahtrit töötaja palga kõrval – lahtrit F3 –, kuhu VLOOKUP valem läheb.

Sisestage VLOOKUP funktsioon: =VLOOKUP().

3. samm) Argument 1: Sisestage F2 – lahter, mis sisaldab töötajat Code otsingutabelis vaste leidmiseks.

4. samm) Argument 2: Otsingutabel asub teisel lehel, seega viidake sellele lehe nimega: Leht2!A2:B25.

5. samm) Argument 3: Sisesta otsingutabeli veeruindeks, mis sisaldab tagastusväärtust.

6. samm) Argument 4: Täpse vaste saamiseks kasutage FALSE väärtust, sest me tahame iga töötaja palka, mis vastab täpselt temale. Code.

Step 7) Vajutage sisestusklahvi. Töötaja sisestamisel Code, tagastab lahter vastava palga, mis on võetud 2. lehelt.

Levinud VLOOKUP-i vead ja parandused

Isegi kogenud kasutajad satuvad VLOOKUP-i vigadesse. Kõige levinumad vead ja kiired lahendused:

• # N / A — VLOOKUP ei leia otsinguväärtust. Kontrollige, kas seal on liigseid tühikuid, mittevastavaid andmetüüpe (arvud, mis on salvestatud tekstina) või kas väärtus on tabeli_massiivi esimeses veerus tõepoolest olemas.
• #REF! — col_index_num on suurem kui veergude arv tabeli_massiivis. Vähendage veeruindeksit või laiendage vahemikku.
• #VALUE! — col_index_num on väiksem kui 1 või argument on sobimatu. Kontrollige valemi süntaksit.
• Vale tulemus tagastati — neljas argument on TRUE või puudub, aga otsinguveerg on sortimata. Valige FALSE või sorteerige veerg kasvavalt.
• Lukustatud viited — valemi kopeerimisel kasutage absoluutviiteid (näiteks $B$2:$E$25), et table_array ei triiviks.

VLOOKUP vs XLOOKUP: Kumba peaksite kasutama?

Microsoft XLOOKUP võeti kasutusele aastal Microsoft 365 ja Excel 2021 VLOOKUP-i moodsa asendajana. See eemaldab mitu VLOOKUP-i piirangut ja on nüüd toetatud versioonides soovitatav valik.

tunnusjoon	VLOOKUP	XLOOKUP
Otsingu suund	Ainult vasakult paremale	Mis tahes suunas (vasakule, paremale, üles, alla)
Vaikimisi vaste tüüp	Ligikaudne (TÕENE)	Täpne
Kui ei leitud, tegutsemine	Tagastab #N/A	Sisseehitatud argument if_not_found
Veeruindeks	Kõvakodeeritud number	Viidatakse tagastusveeru vahemikule
Kättesaadavus	Kõik Exceli versioonid	Microsoft 365, Excel 2021, Exceli veebiversioon

Millal valida VLOOKUP: töövihik peab töötama Excel 2019-s või varasemas versioonis või säilitate pärandvalemeid. Millal valida XLOOKUP: Loote uusi töövihikuid moodsas Excelis ja soovite vaikimisi vasakpoolseid otsinguid, selgemat veakäsitlust ja täpset vastet. Lisateavet otsingufunktsioonide kohta leiate teemast Exceli õpetused seeria.

Järeldus

Ülaltoodud kolm stsenaariumi selgitavad, kuidas VLOOKUP töötab täpsete vastete, ligikaudsete vastete ja ristviidete puhul. Harjutage oma andmekogumite peal, et omandada sujuvus. VLOOKUP on endiselt oluline funktsioon MS-Excel andmete tõhusaks haldamiseks ja XLOOKUP laiendab seda tööriistakomplekti tänapäevases Excelis.

KKK