01 03
Otsige Exceli VLOOKUPi andmetega ligipääsu
Kuidas VLOOKUPi funktsioon töötab?
Excel'i VLOOKUP-i funktsiooni , mis tähistab vertikaalset otsingut , saab konkreetse teabe otsimiseks kasutada andmete või andmebaasi tabelis.
VLOOKUP tavaliselt tagastab väljundina ühe andmevälja. Kuidas see nii on?
- Annate nime või lookup_value, mis annab VLOOKUPile teada , millises andmeside tabelis olevas reas või salvestuses soovitud andmed otsida
- Pakute veetulemust - nimega col_index_num - soovitud andmete hulgast
- Funktsioon otsib andmelehe esimeses veerus lookup_value
- VLOOKUP otsib seejärel ja tagastab teabe, mida otsite samast rekordist teise välja, kasutades kaasasolevat veeru numbrit
Andmete sortimine kõigepealt
Kuigi seda pole alati vaja, on kõige parem kõigepealt sorteerida andmete hulk, mille VLOOKUP otsib kasvavas järjekorras, kasutades sortimisvõtme vahemiku esimest veergu.
Kui andmeid ei sorteerita, võib VLOOKUP tagastada vale tulemuse.
VLOOKUPi funktsiooni süntaks ja argumendid
Funktsiooni süntaks viitab funktsiooni kujule ja sisaldab funktsiooni nime, sulgudes ja argumente .
VLOOKUP-i funktsiooni süntaks on:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (vajalik) otsinguväärtus - näiteks ülaltoodud pildil müüdud kogus
table_array - (nõutav) see on andmete tabel, mille VLOOKUP otsib, et leida teie kohta käivat teavet.
- Tabelisarray peab sisaldama vähemalt kaht veeru andmeid
- Esimene veerg sisaldab tavaliselt lookup_value
col_index_num - (vajalik) soovitud väärtuse veeru number.
- Numeratsioon algab veergu 1 oleva otsing_key veeru
- Kui col_index_num on seatud arvule, mis on suurem kui lahtrite arv, mis on valitud table_array argument, #REF! funktsioon tagastab viga
range_lookup - (valikuline) näitab, kas vahemik on sorteeritud kasvavas järjekorras.
- Esimeses veerus olevaid andmeid kasutatakse sortimisvõtmetega
- Boolean väärtus - TRUE või FALSE on ainsad vastuvõetavad väärtused
- Kui see on välja jäetud, on vaikimisi väärtus TRUE
- Kui seade on TRUE või välja jäetud ning vahemiku esimene veerg ei ole järjestatud järjestuses, võib ilmneda vale tulemus
- Kui seade on TRUE või välja jäetud ja otsingu _value täpset vastet ei leita, kasutatakse otsing_key-na lähima vaste, mis on väiksema suuruse või väärtusega.
- Kui seadeks on FALSE, võtab VLOOKUP vastu otsingu _valu täpse vaste. Kui on mitu sobivat väärtust, tagastatakse esimene sobituv väärtus
- Kui seadeks on FALSE ja otsing_key jaoks ei leidu ühtegi sobivat väärtust, siis tagastatakse funktsioon # N / V
Näide: leiate ostetud koguse allahindluse määra
Ülaltoodud näites kasutatakse VLOOKUP-i funktsiooni diskontomäära leidmiseks, mis sõltub ostetud osade arvust.
Näide näitab, et allahindlus 19 toote ostmiseks on 2%. Seda seetõttu, et veeru Kogus sisaldab väärtuste vahemikku. Selle tulemusena ei leia VLOOKUP täpset vastet. Selle asemel tuleb leida õige diskontomäära tagastamiseks ligikaudne vaste.
Ligikaudsete vaste leidmiseks:
- sorteerige tabelisarjas olevad andmed kasvavas järjekorras;
- määrake range_lookup argument TRUE
Näites kasutatakse VLOOKUP-i funktsiooni sisaldavat järgmist valemit ostetud kaupade koguse allahindluse leidmiseks.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Isegi kui seda valemit saab lihtsalt sisestada töölehe lahtrisse, on allpool loetletud etappide abil kasutatav muu võimalus kasutada oma argumentide sisestamiseks funktsiooni dialoogi.
- Dialoogiboksi kasutamine lihtsustab sageli funktsiooni argumentide sisestamist õigesti.
VLOOKUP-i dialoogiboksi avamine
Täiendavad sammud VLOOKUP-i funktsioonide sisestamiseks lahtrisse B2 on järgmised:
- Klõpsake lahtris B2, et muuta aktiivne lahter - asukoht, kus kuvatakse VLOOKUPi tulemusi
- Klikkige vahekaardil Vormid .
- Funktsiooni rippmenüü avamiseks valige rippmenüüst käsk Otsing ja viide
- Klõpsake loendis VLOOKUP , et avada funktsioonide dialoogiboks
02 03
Exceli VLOOKUPi funktsiooni argumentide sisestamine
Viidates raku viidetele
VLOOKUP-i funktsiooni argumendid sisestatakse dialoogiboksis eraldi ridadesse, nagu on näidatud ülaltoodud pildil.
Argumentideks kasutatavaid raamistiku viiteid saab sisestada õigesse rida või, nagu on tehtud alljärgnevates etappides, saab suunata dialoogiboksi sisestamiseks suunaga, mis hõlmab soovitud lahtrite valimist hiirekursoriga .
Näpunäitete kasutamise eelised on järgmised:
- See on kiirem kui kirjutades;
- Korrektsete raku viidete sisestamisel tehakse vähem vigu.
Suhteliste ja absoluutsete rakuliinide kasutamine argumentidega
Mitmekordne VLOOKUPi koopiate kasutamine ühe ja sama andmete tabelisse tagasipöördumiseks. Selle lihtsamaks tegemiseks võib sageli VLOOKUPi kopeerida ühest lahtrist teisele. Kui funktsioone kopeeritakse teistesse rakkudesse, tuleb hoolitseda selle eest, et saadud rakupinnad oleksid õiged, arvestades funktsiooni uut asukohta.
Ülaltoodud pildil ümbritsevad tabeliarvuti argumendi rakuliinid , mis näitavad, et nad on absoluutsed raku viited , mis tähendab, et need ei muutu, kui funktsioon kopeeritakse teisele lahtrile. See on soovitav, kuna VLOOKUPi mitme koopia puhul oleks teabeallikas viidatud samale andmekaardile.
Teisest küljest ei kasutata lookup_value jaoks kasutatavat raamistiku viidet dollarite tähistega, mistõttu on see suhteline raku viide. Suhtelised raku viited muutuvad, kui need kopeeritakse, et kajastada nende uut asukohta nende andmete positsiooni suhtes, millele need viitavad.
Funktsioonide argumentide sisestamine
- VLOOKUP-i dialoogiboksis klõpsake rea Lookup _value väärtust
- Klõpsake töölehel raami C2, et sisestada selle lahtri viide otsing_key argumendina
- Klõpsake dialoogiboksis Tabelarvuti joon
- Tõstke esile töölaual C5 kuni D8, et sisestada sellesse vahemikku tabeliarvuti argumendina - tabeli pealkirju ei kaasata
- Vajutage klaviatuuril olevat klahvi F4, et muuta vahemik absoluutseks raku viideteks
- Klõpsake dialoogiboksi rida Col_index_num
- Tüüp 2 sellel joonel nagu Col_index_num argument, kuna diskontomäärad paiknevad tabeli_raami argumendi veerus 2
- Klõpsake dialoogiboksi Range_lookup rida
- Sisestage Range_lookup argumendiks sõna True
- Dialoogi sulgemiseks vajutage klaviatuuril Enter ja töölehele tagasi
- Vastus 2% (ostetud koguse diskontomäär) peaks ilmnema töölehe lahtris D2
- Kui klõpsate lahtris D2, kuvatakse töölehe kohal olevas valemiribal täielik funktsioon = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Miks VLOOKUP tagastab tulemusena 2%
- Näites pole veeru Kogus täpse vaste otsing_key väärtuseks 19.
- Kuna is_sorted argument on seatud TRUE, näeb VLOOKUP otsing_key väärtuse ligikaudse vastavusse.
- Lähim väärtus, mis on veel väiksem kui search_key väärtuseks 19, on 11.
- VLOOKUP otsib seetõttu allahindluse protsenti reas, mis sisaldab 11, ja selle tulemusel tagastatakse diskontomäär 2%.
03 03
Excel VLOOKUP ei tööta: # N / A ja #REF vead
VLOOKUP veateated
VLOOKUP-iga on seotud järgmised veateated.
A # N / A ("väärtus pole saadaval") Tõrge kuvatakse, kui:
- Vaatlus _value ei leidu vahemiku argumendi esimeses veerus
- Table_array argument on ebatäpne. Näiteks võib argument sisaldada vahemiku vasakpoolses osas tühje veerge
- Range_lookupi argumendiks on FALSE ja otsing_key argumendi täpne vaste ei leidu vahemiku esimeses veerus
- Range_lookup argument on seatud TRUE ja kõik vahemiku esimeses veerus olevad väärtused on suuremad kui search_key
A # REF! ("võrdlus väljapoole vahemikku") Viga kuvatakse, kui:
- Col_index_num argument on suurem kui tabelisarjas olevate veergude arv