Selles andmepõhises maailmas on andmete haldamiseks vaja erinevaid tööriistu. Andmed reaalajas on tohutud ja detailide hankimine mõne konkreetse andmeosa kohta oleks kindlasti väsitav ülesanne, kuid VLOOKUP Excel , saab selle ülesande saavutada ühe käsurea abil. Selles artiklis saate teada ühest olulisest Exceli funktsioonid st funktsioon VLOOKUP.
Enne edasiliikumist vaatame kiiresti üle kõik siin käsitletud teemad:
- Mis on Exceli VLOOKUP?
- Kuidas see töötab?
- Täpne vaste
- Ligikaudne vaste
- Esimene matš
- Suur- ja väiketähtede tundlikkus
- Vead
- Kahesuunaline otsing
- Metamärkide kasutamine
- Mitu otsingu tabelit
Mis on Exceli VLOOKUP?
Excelis on VLOOKUP a sisseehitatud funktsioon mida kasutatakse Exceli lehelt konkreetsete andmete otsimiseks ja toomiseks. V tähistab vertikaalset ja funktsiooni VLOOKUP kasutamiseks Excelis peavad andmed olema paigutatud vertikaalselt. See funktsioon on väga mugav, kui teil on tohutult andmeid ja praktiliselt võimatu on konkreetseid andmeid käsitsi otsida.
Kuidas see töötab?
Funktsioon VLOOKUP võtab väärtuse, st otsingu väärtuse ja hakkab seda kõige vasakpoolsemas veerus otsima. Kui otsinguväärtus leitakse esimest korda, hakkab see selles reas paremale liikuma ja tagastab teie määratud veeru väärtuse. Seda funktsiooni saab kasutada nii täpse kui ka ligikaudse vaste tagastamiseks (vaikimisi vaste on ligikaudne vaste).
Süntaks:
Selle funktsiooni süntaks on järgmine:
VLOOKUP (otsinguväärtus, tabeli_värv, veeruindeks_num, [vahemiku_otsing])
kus
- otsingu_väärtus on antud tabeli esimeses veerus vaadatav väärtus
- tabeli_indeks on tabel, kust andmed tuleb hankida
- col_index_num on veerg, kust väärtus tuleb hankida
- vahemiku_otsing on loogiline väärtus, mis määrab, kas otsingu väärtus peaks olema ideaalne vaste või ligikaudne vaste ( TÕSI leiab lähima vaste VÄÄR kontrollib täpset vastet)
Täpne vaste:
Kui soovite, et funktsioon VLOOKUP otsiks otsingu väärtuse täpset vastet, peate määrama vahemiku_otsing väärtuseks VÄÄR. Vaadake järgmist näidet, mis on töötajate üksikasjadest koosnev tabel:
Kui soovite otsida mõne nimetatud töötaja määramist, saate teha järgmist.
- Valige lahter, kus soovite väljundit kuvada, ja sisestage seejärel märk '='
- Kasutage funktsiooni VLOOKUP ja sisestage otsingu_väärtus (Siin saab olema töötaja ID)
- Seejärel sisestage muud parameetrid, st table_array , col_index_num ja määrake vahemiku_otsing väärtuseks VÄÄR
- Seetõttu on funktsioon ja selle parameetrid järgmised: = VLOOKUP (104, A1: D8, 3, FALSE)
Funktsioon VLOOKUP hakkab otsima töötaja ID 104 ja liigub seejärel väärtuse leidmise real paremale. See kestab kuni col_index_num ja tagastab sellel positsioonil oleva väärtuse.
Ligikaudne vaste:
Funktsiooni VLOOKUP see funktsioon võimaldab teil väärtusi hankida ka siis, kui teil pole loopup_value täpset vastet. Nagu varem mainitud, peate VLOOKUP-i ligikaudse mängu otsimiseks panema vahemiku_otsing väärtus väärtusele TRUE. Heitke pilk järgmisele näitele, kus märgid kaardistatakse koos nende hinnete ja klassiga, kuhu nad kuuluvad.
- Täpselt samamoodi toimige nagu täpselt vaste tegemisel
- Väärtuse range_lookup asemel kasutage FALSE asemel TRUE
- Seetõttu on funktsioon koos selle parameetritega: = VLOOKUP (55, A12: C15, 3, TRUE)
Kasvavas järjekorras sorteeritud tabelis hakkab VLOOKUP otsima ligikaudset vastet ja peatub järgmise suurima väärtuse juures, mis on väiksem kui teie sisestatud otsinguväärtus. Seejärel liigub see selles reas paremale ja tagastab määratud veeru väärtuse. Ülaltoodud näites on otsingu väärtus 55 ja esimese veeru suuruselt järgmine otsingu väärtus 40. Seetõttu on väljundiks teine klass.
Esimene matš:
Kui teil on tabel, mis koosneb mitmest otsinguväärtusest, peatub VLOOKUP selle esimesel vastel ja võtab väärtuse sellest veerust rea real.
Heitke pilk allpool olevale pildile:
ID 105 korratakse ja kui otsingu väärtuseks on määratud 105, on VLOOKUP tagastanud rea rea, millel on otsingu väärtus esimest korda, väärtuse.
Suur- ja väiketähtede tundlikkus:
Funktsioon VLOOKUP ei erista suurtähti. Kui teil on otsinguväärtus, mis suurtähtedega ja tabelis olev väärtus on väike, tõmbab VLOOKUP ikkagi väärtuse realt, milles väärtus on. Heitke pilk allpool olevale pildile:
Nagu näete, on minu poolt parameetrina määratud väärtus „RAFA“, samas kui tabelis olev väärtus on „Rafa“, kuid VLOOKUP on ikkagi määratud väärtuse tagastanud. Kui teil on täpne vaste isegi juhtumiga, tagastab VLOOKUP ikkagi otsingu väärtuse esimese vaste, olenemata kasutatud juhtumist. Heitke pilk allpool olevale pildile:
Vead:
Funktsioonide kasutamisel on loomulik kohata vigu. Sarnaselt võite ka VLOOKUP-funktsiooni kasutamisel ilmneda tõrkeid ja mõned levinumad vead on järgmised:
- #NIMI
- # Puudub
- #REF
- # VÄÄRTUS
#NAME viga:
Selle vea eesmärk on teavitada teid sellest, et olete süntaksis mõne vea teinud. Süntaktiliste vigade vältimiseks on parem kasutada iga funktsiooni jaoks Exceli pakutavat funktsiooniviisardit. Funktsioonide viisard aitab teil teavet iga parameetri ja sisestatavate väärtuste tüübi kohta. Heitke pilk allpool olevale pildile:
Nagu näete, teavitab funktsiooniviisard sisestama parameetri lookup_value asemele mis tahes tüüpi väärtusi ja kirjeldab selle lühidalt. Samamoodi näete ka teiste parameetrite valimisel teavet nende kohta.
# Puudub viga:
See viga tagastatakse juhul, kui antud otsinguväärtusele vastet ei leita. Näiteks kui sisestan „RAFA“ asemel „AFA“, kuvatakse viga # N / A.
Kahe ülaltoodud vea jaoks mõne veateate määratlemiseks võite kasutada funktsiooni IFNA. Näiteks:
#REF viga:
Selle tõrega ilmneb viide veerule, mis pole tabelis saadaval.
Viga #VALUE:
See tõrge ilmneb siis, kui lisate parameetritele valed väärtused või jätate mõned kohustuslikud parameetrid vahele.
kuidas hõljukit CSS-is kasutada
Kahesuunaline otsing:
Kahesuunaline otsing viitab kahemõõtmelise tabeli väärtuse toomisele viidatud tabeli mis tahes lahtrist. VLOOKUPi abil kahesuunalise otsingu tegemiseks peate koos sellega kasutama funktsiooni MATCH.
MATCHi süntaks on järgmine:
MATCH (otsingu_väärtus, otsingu_joon, vaste tüüp)
- otsingu_väärtus on väärtus, mida tuleb otsida
- otsingu_vajutus on otsinguväärtustest koosnev lahtrivahemik
- match_type võib olla arv, st kas 0, 1 või -1, mis tähistab täpset vastet, vastavalt väiksem ja suurem
Selle asemel, et kasutada VLOOKUP-is kõvakodeeritud väärtusi, saate selle raku viidetes dünaamiliselt mööda hiilida. Vaatleme järgmist näidet:
Nagu ülaltoodud pildil näha, võtab funktsioon VLOOKUP otsingu väärtuse jaoks lahtriviite kui F6 ja veeruindeksi väärtus määratakse funktsiooni MATCH abil. Kui muudate mõnda neist väärtustest, muutub ka väljund vastavalt. Heitke pilk allpool olevale pildile, kus olen muutnud F6-s oleva väärtuse Chrisilt Leoks ja ka väljundit on vastavalt uuendatud:
Kui ma muudan G5 väärtust või nii F6 kui ka G5, töötab see valem vastavalt, kuvades vastavad tulemused.
Samuti saate luua rippmenüüid, et muuta väärtuste muutmine väga mugavaks. Ülaltoodud näites tuleks seda teha F6 ja G5 puhul. Rippmenüüde loomiseks toimige järgmiselt.
- Valige lindil vahekaardilt Andmed
- Valige rühmas Andmetööriistad andmete valideerimine
- Avage paan Seaded ja valige suvandist Luba loend
- Määrake allikaloendi massiiv
Kui rippmenüü on loodud, näeb see välja järgmine:
Metamärkide kasutamine:
Kui te ei tea otsingu täpset väärtust, vaid ainult osa sellest, võite kasutada metamärke. Excelis tähistab sümbol “*” metamärke. See sümbol teavitab Exceli, et enne, pärast või vahel olevat järjestust tuleb otsida ja nende ees või järel võib olla suvaline arv tähemärke. Näiteks kui minu loodud tabelis sisestatakse „erg” koos mõlemal küljel olevate wild-kaartidega, tagastab VLOOKUP väljundi „Sergio” jaoks, nagu allpool näidatud:
Mitu otsingu tabelit:
Kui teil on mitu otsingu tabelit, võite kasutada IF-funktsiooni koos sellega, et uurida mõnda tabelit mõne konkreetse tingimuse põhjal. Näiteks kui on olemas tabel, kus on kahe supermarketi andmed ja peate müügi põhjal välja selgitama kummagi teenitud kasumi, saate teha järgmist.
Looge põhitabel järgmiselt:
Seejärel looge kaks tabelit, kust tuleb kasumit hankida.
Kui see on tehtud, looge iga uue tabeli jaoks nimega vahemik. Nimetatud vahemiku loomiseks toimige järgmiselt.
- Valige tabelist kogu tabel, millele soovite nime määrata
- Valige vahekaardil Lindi valemid ja seejärel grupist Määratletud nimed Define Name
- Näete järgmist dialoogiboksi
- Pange oma valitud nimi
- Klõpsake nuppu OK
Kui see on mõlema tabeli jaoks tehtud, saate IF-funktsioonis kasutada neid nimetatud vahemikke järgmiselt:
Nagu näete, on VLOOKUP tagastanud veeru Kasum täitmiseks sobivad väärtused vastavalt sellele, millisesse supermarketti nad kuuluvad. Selle asemel, et valem kirjutada veeru Kasum igasse lahtrisse, on mul lihtsalt kopeeris valemi aja ja energia säästmiseks.
See viib meid Exceli VLOOKUPi artikli lõpuni. Loodan, et teil on selge kõigega, mida teiega on jagatud. Harjutage kindlasti nii palju kui võimalik ja pöörake oma kogemused tagasi.
Kas teil on meile küsimus? Palun mainige seda selle ajaveebi „VLOOKUP in Exceli“ kommentaaride jaotises ja võtame teiega ühendust niipea kui võimalik.
Põhjalike teadmiste saamiseks kõigi populaarsete tehnoloogiate ja nende erinevate rakenduste kohta saate registreeruda otseülekandeks 24/7 toe ja ligipääsuga kogu eluks.