Selle Exceli funktsiooni rakendus ja määratlus
VLOOKUP on Exceli funktsioon, mille abil saab kasutaja otsida ja hinnata tabeli sisu. See funktsioon on saadaval Windowsi ja Maci Excel 2007 versioonides.
Mis on VLOOKUP?
VLOOKUPi võimalikke kasutusviise tuleb siin selgitada näite abil. Selles lehes olete suur kirjanduse fänn ja olete seetõttu loonud oma Exceli arvutustabeli, kuhu saate kogutud raamatuid hoolikalt sorteerida. Iga töö sisestatakse teabega järgmistesse kategooriatesse:
-
autor
-
tiitel
-
Lehekülje number
-
Avaldamise aasta
Nüüd tahaksite sõbrale anda raamatunäpunäite, mida järgmisel koosolekul kaasa võtta. Kahjuks võite mõelda ainult autorile, mitte raamatu pealkirjale. Siin tuleb mängu VLOOKUP, kuna see saab selle sisendväärtuse abil otsitava teabe ühe hoobiga välja visata.
Kuidas VLOOKUPi kasutatakse?
Enne valemite koostamisele mõtlemist tuleks kindlaks teha, kus sisendväli ja erinevad väljundväljad hiljem asuvad. Selleks on mõttekas luua eraldi tabel, mis on esialgu tühi ja võimaldab seega ruumi mainitud teabele. Kui kujundate selle uue tabeli olemasoleva tabeli näite põhjal, saate hiljem aja kokkuhoiu eelise.
Selle põhjal saab VLOOKUP valemi luua kas käsitsi või automaatselt Exceli abil. Algajatel tasub kasutada viimast lähenemist, et järk -järgult tundma õppida valemi struktuuri ja mõju. Selleks valitakse vahekaardil „Valemid” nupp „Lisa funktsioon”. VLOOKUP on peidetud avanevas aknas. Pärast kinnitamist avaneb uuesti aken, milles saab täita valemi neli parameetrit. Need on:
-
Otsingu kriteerium
-
maatriks
-
Veeruindeks
-
Area_reference
Seetõttu näeb valemi mustand välja selline:
= VLOOKUP (otsingukriteerium, maatriks, veeruindeks, vahemiku link)
ja ühes sellises võimalikus rakenduses:
= OTSING (H3; A3: E40; 5)
Otsingu kriteerium
Et funktsioon teaks, millist väärtust tuleks lähtepunktina kasutada, märgitakse väljale "Otsingukriteerium" rida, mis valiti sisestusväljaks kaks sammu varem. Meie näites sisestatakse sinna raamatu autori nimi “Phillip Pulmann”. See muudab valemi paindlikuks ja seda ei pea uuesti korrigeerima niipea, kui sisestatud väärtus muutub.
maatriks
Sisestusväli "Maatriks" kirjeldab tabelit, kus väljunditeavet võib leida. See spetsiaalne maatriks sisaldab seega ka raamatu pealkirja, lehe numbri ja ilmumisaasta veerge.
Maatriks valitakse täielikult üks kord ilma pealkirjadeta ülevalt vasakult paremasse alumisse serva. Nii teab Excel, millist sisu tuleb hindamisel arvesse võtta.
Veeruindeks
"Veeruindeksi" sisestusväli palub kasutajal määratleda maatriksi veeru, milles on loetletud ainult soovitud väärtus. Veergude määramine on nummerdatud kronoloogiliselt. See tähendab, et tabeli esimene veerg saab väärtuse 1, teine väärtuse 2 jne. Meie näites vastab see autori veeruindeksile 1, pealkirja veeruindeksile 2, lehe numbri veeruindeksile 3 ja veeruindeks 4 avaldamise aasta kohta.
Et tabel oleks võimalikult paindlik, saab veeru pealkirja numbri asemel lingida. Selle eeliseks on see, et valemit saab probleemideta üle kanda ka teistele ridadele, kuna veeru pealkirja saab iga kord paindlikult kohandada.
Tähelepanu: VLOOKUP loeb maatriksit vasakult paremale, mistõttu tuleb veergude indeks paigutada otsingukriteeriumi jaoks veerust paremale, et funktsioon seda arvesse võtaks!
Area_reference
Parameeter "Range_Lookup" täiendab VLOOKUP valemit, täpsustades tabeli hindamise täpsuse. Kuid see erineb valemi eelnevalt mainitud komponentidest, kuna see on valikuline. Kui väärtuseks 0 sisestatakse "vale", otsib Excel ainult otsingukriteeriumina määratud väärtust. Kui väärtuseks 1 on "tõene", jätkatakse ilmsete väärtuste otsimist, kui täpset väärtust ei leitud.
Selle parameetri määramine on valikuline, kuna väärtus 1 on vaikimisi määratud. See säte on kasulik hiljem mitme otsingukriteeriumiga täiustatud VLOOKUP -is.
Ühinemine
Niipea kui kõik vajalikud parameetrid on seadistatud, saab kasutada funktsiooni VLOOKUP. Pärast otsingukriteeriumi sisestamist ja funktsiooni kinnitamist ilmub otsitav väärtus reale, mis on määratletud väljundväljana.
Meie näites kuvatakse nüüd raamatu pealkiri “Kuldne kompass”, mis vastab sisestatud autorile. Lehekülje numbri ja avaldamisaasta kiireks väljaselgitamiseks pole vaja teha muud, kui olemasolev VLOOKUP valem järgmistesse lahtritesse lohistada. See on nii lihtne, sest VLOOKUPi veeruindeks on lingitud esimese tabeli veerupealkirjaga ja ka teine tabel on üles ehitatud samas järjekorras.
Juhul kui tabelid peaksid üksteisest erinema või kui kõigest hoolimata ilmneb tõrge, saab VLOOKUP valemit ka käsitsi muuta. Selleks tuleb veeruindeksi eelviimane number sobitada väljundis oleva uue väärtuse veergu.
VLOOKUP mitme otsingukriteeriumiga
Sageli juhtub, et ühest otsingukriteeriumist ei piisa suure Exceli tabeli täpseks hindamiseks. Siis on mõistlik käivitada VLOOKUP mitme otsingukriteeriumiga. Selleks tuleb olemasolevat valemit täiendada täiendava IF -funktsiooniga. Nii saab taotluse ajal arvesse võtta kuni kaheksat erinevat otsingukriteeriumi.
VLOOKUP mitmes Exceli arvutustabelis
Kui otsingukriteeriumi ei leidu mitte ainult ühes tabelis, vaid võib -olla ka teises tabelis, saab VLOOKUP valemit vastavalt kohandada. Selleks tuleb olemasoleva valemi ette panna nii funktsioon kui if kui ka funktsioon ISERROR. Selleks on vaja viit parameetrit:
-
Otsingu kriteerium
-
Maatriks1 ja maatriks 2
-
Veeruindeks1 ja veeruindeks2
Tulemus näeb välja selline:
= IF (ISERROR (VLOOKUP (otsingukriteerium, maatriks1, veerg-indeks1, 0));
VLOOKUP (otsingukriteerium; maatriks2; veeruindeks2,0); VLOOKUP (otsingukriteerium; maatriks1; veeruindeks1;))
ja ühes sellises võimalikus rakenduses:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Otsingukriteeriumi kasutatakse otsitava väärtuse lisamiseks kahte tabelisse. Maatriks1 ja maatriks2 määratlevad kahe tabeli vastavad lahtripiirkonnad. Veergude indeksit 1 ja veergude indeksit2 kasutatakse täpsemalt määratlemiseks, milliseid vastavate tabelite veerge tuleks otsida.
Kui otsitav väärtus esineb mõlemas tabelis, väljastab Excel esimese tabeli tulemuse. Kui aga väärtust ei leita kummaski tabelis, kuvatakse veateade. Valemi eeliseks on see, et need kaks loendit ei pea olema sama ülesehitusega ega ühesuurused.
Määrake kategooriatele väärtused, kasutades funktsiooni VLOOKUP
VLOOKUPi lisafunktsioon võimaldab loetletud väärtused automaatselt teie valitud tähtedeks ja predikaatideks jagada. Meie eelmises näites tuleks raamatu tüübi jaoks lisada täiendav tabeliveerg. Raamatud pikkusega kuni 50 lehekülge peaksid kuuluma novelli žanrisse, 51–150 leheküljelised raamatud on aga novellile ja 151 lehekülge romaani. Selle võimaldamiseks ei ole VLOOKUPis vaja täiendavat valemit, piisab lokkisulgude “{}” kasutamisest. Valmis valem näeb välja selline:
= VLOOKUP (B1; {1. "Novell"; 51. "Novella"; 151. "Romaan"}; 2)
Lokkisulgude sisu näitab maatriksit, mis määratleb vastava raamatutüübi ala. Küljepikkuse määramine sobivale perekonnale asub seetõttu lokkis sulgudes. Valem kasutab väärtuspaare, millest igaüks on eraldatud punktiga. Maatriksit {1. "Novell"; 51. "Novella"; 151. "Romaani"} loetakse järgmiselt:
"Alates 1 -st näidake lühijuttu, 51 -st näidake romaani, 151 -st näidake romaani."
Seda maatriksit saab hõlpsasti kohandada erinevate ülesannete jaoks. See puudutab ühelt poolt maatriksite suurust ja arvu ning nende määramist. Seega on võimalik üksikute tähtede asemel väljastada stringe või numbreid. Piisab, kui valemis tähti kohandada.
VLOOKUP mitmele töölehele
Teine VLOOKUPi funktsioon võimaldab kasutajatel linkida erinevatel arvutustabelitel asuvat sisu. Meie näites võib see suvand olla kasulik, kui teave sorteeritakse esmalt erinevatel töölehtedel ja seejärel värskendatakse kokkuvõtetabelis.
Kujutage ette, et lisaks raamatutele loetlete oma kogutud filmid ka Exceli tabelis. Seejärel ühendate mõlemad kogud üheks suureks tabeliks.
Selle protseduuri eelis ei seisne mitte ainult suurenenud järjekorras, vaid ka võimalike vigade vältimises. Kui soovite luua uue kirje või värskendada olemasolevat, ei pea te otsima suurest tabelist, vaid pääsete juurde väiksematele. Seejärel kantakse väärtused automaatselt Exceli kokkuvõtvasse tabelisse. See muudab suures tabelis ümberkirjutamise üleliigseks, mis heal juhul väldib kahetsusväärset käiku ja sellele järgnevat veateadete aheldamist.
Kuidas valem välja näeb?
See funktsioon on uuesti võimalik, lisades teise valemi. Kui mitme kriteeriumiga otsimine nõudis täiendavat IF -valemit, siis mitme töölehega töötamiseks on vaja kaudset valemit. See võimaldab VLOOKUP maatriksi jaoks määrata vahemiku teisest arvutustabelist.
= VLOOKUP (otsingukriteerium; KAUDNE (maatriks); veeruindeks; vahemiku link)
Tähelepanu: See valem töötab ainult siis, kui erinevate lehtede üksikutel tabelitel on samad nimed kui üldtabeli veerupäistel. Kogu tabeleid saab nimetada lahtrivõrgu kohal vasakus ülanurgas asuval väljal "Nimi". Tabeleid, millele on juba antud nimi, saab vaadata klahvikombinatsiooniga Ctrl + F3.
Tekkivate veateadetega tegelemine
Lingitud Exceli tabelitega töötamine võib põhjustada soovimatuid probleeme. See hõlmab eelkõige valede väärtuste väljastamist. Juhul, kui väljastatakse vale väärtus 0, on Exceli seadetes väike probleem, mille saab kiiresti parandada.
Tavaline veateade #NV on seevastu VLOOKUPi tahtlik funktsioon, mis näitab kasutajale, et nõutav väärtus pole saadaval. Selle märkme saab valemi abil erinevalt kujundada.
VLOOKUP - ülevaade
VLOOKUP on kasulik Exceli funktsioon, mida saab kasutada tabelite otsimiseks ja hindamiseks. Selle eelised ilmnevad kasutajasõbralikus ja paindlikus rakenduses. Sel viisil saavad sellest funktsioonist kasu kõik, kes regulaarselt Exceli tabelitega töötavad. Olgu see erakollektsionäär, kes loob oma väikesed tabelid, või suur ettevõte, mis töötleb oluliselt sisulisemaid andmekogumeid.
Kui aga teil on endiselt vastamata taotlusi, millele VLOOKUP ei suutnud vastata, võite oodata Exceli lisavõimalust: Microsoft on pakkunud Excel 365 kasutajatele uut XLOOKUPi alates 2022-2023. – 2022. See põhineb VLOOKUPi pädevustel ja täiendab neid täiendavate, mõnikord isegi lihtsamate funktsioonidega. Seetõttu avaneb siinkohal ka uus rutiin andmete hindamisel.
