Funkcija VLOOKUP - Įvadas
Funkcija VLOOKUP yra etalonas.
Jūs žinote kažką „Excel“, jei žinote, kaip naudoti funkciją VLOOKUP.
Jei to nepadarysite, geriau neįtraukite „Excel“ į savo stipriąsias gyvenimo aprašymo sritis.
Dalyvavau grupiniuose pokalbiuose, kuriuose, kai tik kandidatas paminėjo „Excel“ kaip savo kompetencijos sritį, pirmiausia buvo paklausta - jūs supratote - funkcija VLOOKUP.
Dabar, kai žinome, kokia svarbi ši „Excel“ funkcija, prasminga ją visiškai atsisakyti, kad galėtume išdidžiai pasakyti: „Aš žinau vieną ar du dalykus„ Excel ““.
Tai bus didžiulė VLOOKUP pamoka (pagal mano standartus).
Aptarsiu viską, ką reikia žinoti, ir parodysiu naudingus ir praktiškus VLOOKUP pavyzdžius.
Taigi užsisegti.
Atėjo laikas kilimui.
Kada naudoti „VLOOKUP“ funkciją „Excel“?
Funkcija VLOOKUP geriausiai tinka situacijoms, kai stulpelyje ieškote atitinkamo duomenų taško, o kai randamas atitinkantis duomenų taškas, einate į dešinę toje eilutėje ir paimate reikšmę iš langelio, kuris yra nurodytas skaičius stulpelius į dešinę.
Paimkime paprastą pavyzdį, kad suprastume, kada naudoti „Vlookup“ programoje „Excel“.
Prisiminkite, kai egzaminų balų sąrašas buvo išklijuotas ir įklijuotas skelbimų lentoje ir visi išprotėjo ieškodami savo vardų ir balų (bent jau taip būdavo, kai buvau mokykloje).
Štai kaip tai veikė:
- Jūs einate prie skelbimų lentos ir pradedate ieškoti savo vardo ar registracijos numerio (pirštu braukite iš viršaus į apačią sąraše).
- Kai tik pastebėsite savo vardą, perkelsite akis į dešinę nuo vardo/registracijos numerio, kad pamatytumėte savo balus.
Būtent tai jums daro „Excel VLOOKUP“ funkcija (nedvejodami naudokite šį pavyzdį kitame interviu).
Funkcija VLOOKUP ieško nurodytos vertės stulpelyje (aukščiau pateiktame pavyzdyje tai buvo jūsų vardas), o radusi nurodytą atitiktį grąžina vertę toje pačioje eilutėje (gautus ženklus).
Sintaksė
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Įvesties argumentai
- lookup_value - tai yra paieškos vertė, kurią bandote rasti kairiajame lentelės stulpelyje. Tai gali būti reikšmė, langelio nuoroda arba teksto eilutė. Rezultatų lapo pavyzdyje tai būtų jūsų vardas.
- table_array - tai yra lentelės masyvas, kuriame ieškote vertės. Tai gali būti nuoroda į ląstelių diapazoną arba pavadintą diapazoną. Rezultatų lapo pavyzdyje tai būtų visa lentelė, kurioje yra kiekvieno dalyko balas
- col_index - tai stulpelio indekso numeris, iš kurio norite gauti atitinkamą vertę. Rezultatų lapo pavyzdyje, jei norite matematikos balų (tai yra pirmasis lentelės stulpelis, kuriame yra balai), turėtumėte ieškoti 1 stulpelyje. Jei norite fizikos balų, ieškokite stulpelio 2.
- [range_lookup] - čia nurodote, ar norite tikslios, ar apytikslės atitikties. Jei praleista, numatytoji reikšmė yra TRUE - apytikslė atitiktis (žr. papildomas pastabas žemiau).
Papildomos pastabos (nuobodu, bet svarbu žinoti)
- Atitikimas gali būti tikslus (FALSE arba 0 in range_lookup) arba apytikslis (TRUE arba 1).
- Apytiksliai ieškodami įsitikinkite, kad sąrašas surūšiuotas didėjančia tvarka (iš viršaus į apačią), nes kitaip rezultatas gali būti netikslus.
- Kai range_lookup yra TRUE (apytikslė paieška) ir duomenys rūšiuojami didėjančia tvarka:
- Jei funkcija VLOOKUP neranda vertės, ji grąžina didžiausią reikšmę, kuri yra mažesnė už lookup_value.
- Ji pateikia #N/A klaidą, jei lookup_value yra mažesnė už mažiausią reikšmę.
- Jei „lookup_value“ yra tekstas, galima naudoti pakaitos simbolius (žr. Toliau pateiktą pavyzdį).
Dabar, tikėdamiesi, kad turite pagrindinį supratimą apie tai, ką gali atlikti funkcija VLOOKUP, nulupkite šį svogūną ir pažiūrėkite keletą praktinių funkcijos VLOOKUP pavyzdžių.
10 „Excel“ VLOOKUP pavyzdžių (pagrindinis ir išplėstinis)
Čia yra 10 naudingų „Excel Vlookup“ naudojimo pavyzdžių, kurie parodys, kaip jį naudoti kasdieniame darbe.
1 pavyzdys - rasti Brado matematikos balą
Žemiau esančiame VLOOKUP pavyzdyje aš turiu sąrašą, kuriame yra mokinių vardai kairiajame stulpelyje ir žymės skirtinguose dalykuose B – E stulpeliuose.
Dabar pradėkime dirbti ir naudokime funkciją VLOOKUP tam, kas jai sekasi geriausiai. Iš aukščiau pateiktų duomenų turiu žinoti, kiek Bradas surinko matematikoje.
Iš aukščiau pateiktų duomenų turiu žinoti, kiek Bradas surinko matematikoje.
Štai VLOOKUP formulė, kuri grąžins Brado matematikos balą:
= VLOOKUP („Bradas“, 3 USD: 10,2 USD)
Aukščiau pateikta formulė turi keturis argumentus:
- „Bradas: - tai yra paieškos vertė.
- 3 USD: 10 USD - tai yra ląstelių diapazonas, į kurį mes žiūrime. Atminkite, kad „Excel“ ieško paieškos vertės kairiajame stulpelyje. Šiame pavyzdyje jis ieškotų pavadinimo Brad A3: A10 (tai yra kairiausias nurodyto masyvo stulpelis).
- 2 - Kai funkcija pastebės Brado vardą, ji pateks į antrąjį masyvo stulpelį ir grąžins vertę toje pačioje eilutėje kaip ir Bradas. Čia reikšmė 2 nurodė, kad mes ieškome balo iš nurodyto masyvo antro stulpelio.
- 0 - tai nurodo VLOOKUP funkcijai ieškoti tik tikslių atitikčių.
Štai kaip veikia VLOOKUP formulė aukščiau pateiktame pavyzdyje.
Pirma, kairėje stulpelyje ieškoma vertės Brad. Jis eina iš viršaus į apačią ir randa vertę ląstelėje A6.
Kai tik suranda vertę, ji eina į dešinę antrame stulpelyje ir paima vertę.
Galite naudoti tą pačią formulės struktūrą, kad gautumėte bet kurio asmens pažymius bet kuriame dalyke.
Pavyzdžiui, norėdami rasti Marijos ženklus chemijoje, naudokite šią VLOOKUP formulę:
= VLOOKUP („Marija“, $ 3 USD: $ 10,4,0)
Pirmiau pateiktame pavyzdyje paieškos vertė (studento vardas) įvedama dvigubose kabutėse. Taip pat galite naudoti langelio nuorodą, kurioje yra paieškos vertė.
Naudojant langelio nuorodą, formulė tampa dinamiška.
Pvz., Jei turite langelį su studento vardu ir renkate matematikos rezultatą, rezultatas bus automatiškai atnaujinamas, kai pakeisite mokinio vardą (kaip parodyta žemiau):
Jei įvesite paieškos vertę, kuri nėra kairiajame stulpelyje, ji grąžina #N/A klaidą.
2 pavyzdys - dviejų krypčių paieška
Pirmiau pateiktame 1 pavyzdyje stulpelio reikšmę užkoduodavome. Taigi formulė visada grąžintų matematikos balą, nes stulpelio indekso numeriu naudojome 2.
Bet ką daryti, jei norite, kad VLOOKUP reikšmė ir stulpelio indekso numeris būtų dinamiški. Pavyzdžiui, kaip parodyta žemiau, galite pakeisti mokinio vardą arba dalyko pavadinimą, o VLOOKUP formulė pateikia teisingą balą. Tai yra dviejų krypčių VLOOKUP formulės pavyzdys.
Tai yra dvipusio VLOOKUP funkcijos pavyzdys.
Norėdami sukurti šią dvipusę paieškos formulę, turite stulpelį padaryti dinamišką. Taigi, kai vartotojas keičia temą, formulė automatiškai parenka teisingą stulpelį (2 matematikos atveju, 3 fizikos atveju ir pan.).
Norėdami tai padaryti, turite naudoti funkciją MATCH kaip stulpelio argumentą.
Čia yra VLOOKUP formulė, kuri tai padarys:
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Aukščiau pateikta formulė naudoja MATCH (H3, $ A $ 2: $ E $ 2,0) kaip stulpelio numerį. Funkcija MATCH paima objekto pavadinimą kaip paieškos vertę (H3) ir grąžina savo poziciją A2: E2. Taigi, jei naudosite matematiką, ji grąžins 2, nes matematika randama B2 (tai yra antrasis langelis nurodytame masyvo diapazone).
3 pavyzdys. Išskleidžiamųjų sąrašų naudojimas kaip paieškos vertės
Pirmiau pateiktame pavyzdyje turime rankiniu būdu įvesti duomenis. Tai gali užtrukti daug laiko ir daug klaidų, ypač jei turite didžiulį paieškos verčių sąrašą.
Tokiais atvejais gera idėja yra sukurti išskleidžiamąjį paieškos verčių sąrašą (šiuo atveju tai gali būti studentų vardai ir dalykai), o tada tiesiog pasirinkti iš sąrašo.
Remiantis pasirinkimu, formulė automatiškai atnaujins rezultatą.
Kažkas, kaip parodyta žemiau:
Tai yra geras prietaisų skydelio komponentas, nes galite turėti didžiulį duomenų rinkinį, kurio gale yra šimtai mokinių, tačiau galutinis vartotojas (tarkime, mokytojas) gali greitai gauti dalyko pažymius, tiesiog pasirinkęs iš išskleidžiamasis.
Kaip tai padaryti:
Šiuo atveju naudojama VLOOKUP formulė yra tokia pati kaip 2 pavyzdyje.
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Peržiūros vertės buvo konvertuotos į išskleidžiamuosius sąrašus.
Norėdami sukurti išskleidžiamąjį sąrašą, atlikite šiuos veiksmus:
- Pasirinkite langelį, kuriame norite išskleidžiamojo sąrašo. Šiame pavyzdyje, G4, norime mokinių vardų.
- Eikite į Duomenys -> Duomenų įrankiai -> Duomenų patvirtinimas.
- Lauke Duomenų patvirtinimo dialogas, nustatymų skirtuko lape, išskleidžiamajame meniu Leisti pasirinkite Sąrašas.
- Šaltinyje pasirinkite $ A $ 3: $ A $ 10
- Spustelėkite Gerai.
Dabar langelyje G4 turėsite išskleidžiamąjį sąrašą. Panašiai galite sukurti vieną H3 dalykams.
4 pavyzdys - trijų krypčių paieška
Kas yra trijų krypčių paieška?
2 pavyzdyje mes naudojome vieną paieškos lentelę su skirtingų dalykų studentų balais. Tai yra dvipusio paieškos pavyzdys, nes mes naudojame du kintamuosius, kad gautume balą (studento vardą ir dalyko vardą).
Dabar, tarkime, per metus studentas turi tris skirtingus egzaminų lygius: vieneto testą, vidurio kursą ir baigiamąjį egzaminą (tai aš turėjau būdamas studentas).
Trijų krypčių paieška būtų galimybė gauti studento pažymius už nurodytą dalyką iš nurodyto egzamino lygio.
Kažkas, kaip parodyta žemiau:
Anksčiau pateiktame pavyzdyje funkciją VLOOKUP galima rasti trijose skirtingose lentelėse (vieneto testas, vidurinis laikotarpis ir baigiamasis egzaminas) ir grąžinamas nurodyto dalyko nurodyto studento balas.
Čia yra formulė, naudojama ląstelėje H4:
= VLOOKUP (G4, PASIRINKITE (IF (H2 = "Vieneto testas", 1, IF (H2 = "vidurio laikotarpis", 2,3))), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Ši formulė naudoja funkciją PASIRINKTI, kad įsitikintumėte, jog nurodoma tinkama lentelė. Išanalizuokime formulės dalį PASIRINKTI:
PASIRINKITE (JEI (H2 = „Vieneto testas“, 1, JEI (H2 = „Vidurio laikotarpis“, 2,3))), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23 )
Pirmasis formulės argumentas yra IF (H2 = „Vieneto testas“, 1, IF (H2 = „Vidurio laikotarpis“, 2,3)), kuris patikrina langelį H2 ir mato, koks egzamino lygis yra nurodomas. Jei tai vieneto testas, grąžinamas $ 3 USD: $ 7 USD, kuris turi vieneto testo balus. Jei jis yra vidurio laikotarpis, grąžinamas 11 USD: 15 USD, kitu atveju - 19 USD: 23 USD.
Tai padarius „VLOOKUP“ lentelės masyvas tampa dinamiškas, taigi ir trijų krypčių paieška.
5 pavyzdys. Paskutinės vertės gavimas iš sąrašo
Galite sukurti VLOOKUP formulę, kad gautumėte paskutinę skaitinę vertę iš sąrašo.
Didžiausias teigiamas skaičius, kurį galite naudoti „Excel“ 9.99999999999999E+307. Tai taip pat reiškia, kad didžiausias paieškos skaičius VLOOKUP numeryje taip pat yra tas pats.
Nemanau, kad jums kada nors reikės skaičiavimų, susijusių su tokiu dideliu skaičiumi. Ir būtent tai mes galime naudoti norėdami gauti paskutinį sąrašą.
Tarkime, kad turite duomenų rinkinį (A1: A14) kaip parodyta žemiau ir norite gauti paskutinį sąrašą.
Štai formulė, kurią galite naudoti:
= VLOOKUP (9,99999999999999E+307, 1 USD: 1 USD: 14 USD,TIESA)
Atminkite, kad aukščiau pateikta formulė naudoja apytikslę atitiktį VLOOKUP (Formulės pabaigoje pažymėkite TRUE, o ne FALSE arba 0). Be to, atminkite, kad sąrašo nereikia rūšiuoti, kad ši VLOOKUP formulė veiktų.
Štai kaip veikia apytikslė VLOOKUP funkcija. Jis nuskaito kairįjį stulpelį iš viršaus į apačią.
- Jei randa tikslią atitiktį, ji grąžina tą vertę.
- Jei ji nustato vertę, kuri yra didesnė už paieškos vertę, ji grąžina vertę langelyje virš jo.
- Jei paieškos vertė yra didesnė už visas sąrašo reikšmes, ji grąžina paskutinę vertę.
Pirmiau pateiktame pavyzdyje veikia trečiasis scenarijus.
Nuo 9.99999999999999E+307 yra didžiausias skaičius, kurį galima naudoti „Excel“, kai jis naudojamas kaip paieškos vertė, jis grąžina paskutinį skaičių iš sąrašo.
Taip pat galite jį naudoti norėdami grąžinti paskutinį teksto elementą iš sąrašo. Čia yra formulė, kuri gali tai padaryti:
= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1,TIESA)
Ta pati logika. „Excel“ peržiūri visus pavadinimus ir kadangi zzz laikomas didesniu už bet kurį pavadinimą/tekstą, prasidedantį abėcėlėmis prieš zzz, tai grąžintų paskutinį elementą iš sąrašo.
6 pavyzdys - dalinė paieška naudojant pakaitos simbolius ir VLOOKUP
„Excel“ pakaitos simboliai gali būti tikrai naudingi daugelyje situacijų.
Tai tas stebuklingas gėrimas, suteikiantis jūsų formulėms super galių.
Dalinė paieška reikalinga, kai reikia ieškoti vertės sąraše ir nėra tikslios atitikties.
Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite ieškoti įmonės ABC sąraše, tačiau sąraše yra ABC Ltd, o ne ABC.
Negalite naudoti ABC kaip paieškos vertės, nes A stulpelyje nėra tikslios atitikties. Apytikslė atitiktis taip pat lemia klaidingus rezultatus ir reikalauja, kad sąrašas būtų rūšiuojamas didėjančia tvarka.
Tačiau, norėdami gauti atitiktį, VLOOKUP funkcijoje galite naudoti pakaitos simbolį.
Į langelį D2 įveskite šią formulę ir vilkite ją į kitas ląsteles:
= VLOOKUP ("*" ir C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Kaip veikia ši formulė?
Anksčiau pateiktoje formulėje vietoj to, kad būtų naudojama paieškos reikšmė, ji iš abiejų pusių papildoma pakaitos simbolio žvaigždute (*) - „*“ Ir C2 & “*“
Žvaigždutė yra „Excel“ pakaitos simbolis ir gali reikšti bet kokį simbolių skaičių.
Naudojant žvaigždutę abiejose paieškos vertės pusėse, „Excel“ nurodo, kad reikia ieškoti bet kokio teksto, kuriame yra žodis C2. Jame gali būti bet koks simbolių skaičius prieš arba po teksto C2.
Pavyzdžiui, ląstelė C2 turi ABC, todėl funkcija VLOOKUP peržiūri A2: A8 pavadinimus ir ieško ABC. Jis randa atitiktį langelyje A2, nes jame yra ABC ABC Ltd. Nesvarbu, ar kairėje, ar dešinėje ABC yra simbolių. Kol teksto eilutėje nėra ABC, ji bus laikoma atitiktimi.
Pastaba: funkcija VLOOKUP visada grąžina pirmąją atitinkančią vertę ir nustoja ieškoti toliau. Taigi, jei turite ABC Ltd., ir ABC Corporation sąraše, jis grąžins pirmąjį ir ignoruos likusius.
7 pavyzdys - VLOOKUP Grąžina klaidą, nepaisant paieškos vertės atitikties
Tai gali išvesti jus iš proto, kai pamatysite, kad yra atitinkanti paieškos vertė ir funkcija VLOOKUP grąžina klaidą.
Pavyzdžiui, žemiau pateiktu atveju yra atitiktis (Mattas), tačiau funkcija VLOOKUP vis tiek pateikia klaidą.
Dabar, kai matome, kad yra rungtynės, plika akimi nematome, kad gali būti priekinių ar galinių tarpų. Jei turite šių papildomų tarpų prieš, po arba tarp paieškos verčių, tai NĖRA tiksli atitiktis.
Tai dažnai atsitinka, kai importuojate duomenis iš duomenų bazės arba gaunate juos iš kito asmens. Šios priekinės/galinės erdvės turi tendenciją įsiskverbti.
Sprendimas čia yra funkcija TRIM. Tai pašalina visas priekines ar galines tarpus ar papildomas tarpus tarp žodžių.
Čia yra formulė, kuri suteiks jums teisingą rezultatą.
= VLOOKUP („Matt“, TRIM ($ A $ 2: $ A $ 9), 1,0)
Kadangi tai yra masyvo formulė, naudokite „Control“ + „Shift“ + „Enter“, o ne tik „Enter“.
Kitas būdas gali būti pirmiausia apdoroti savo paieškos masyvą naudojant funkciją TRIM, kad įsitikintumėte, jog nėra visų papildomų tarpų, ir tada naudoti funkciją VLOOKUP kaip įprasta.
8 pavyzdys - didžiųjų ir mažųjų raidžių paieška
Pagal numatytuosius nustatymus funkcijos VLOOKUP paieškos reikšmė neskiria didžiųjų ir mažųjų raidžių. Pvz., Jei jūsų paieškos vertė yra MATT, matinė arba matinė, tai VLOOKUP funkcijai viskas vienoda. Ji grąžins pirmąją atitinkančią vertę, neatsižvelgiant į atvejį.
Bet jei norite ieškoti didžiųjų ir mažųjų raidžių, turite naudoti funkciją TIKSLAS kartu su funkcija VLOOKUP.
Štai pavyzdys:
Kaip matote, yra trys langeliai tuo pačiu pavadinimu (A2, A4 ir A5), bet su skirtingomis abėcėlės raidėmis. Dešinėje pusėje yra trys vardai (Matt, MATT ir matt) kartu su jų rezultatais matematikoje.
Dabar funkcija VLOOKUP nėra pritaikyta didžiųjų ir mažųjų raidžių peržiūros vertėms tvarkyti. Šiame aukščiau pateiktame pavyzdyje jis visada grąžins 38, tai yra Mato balas A2.
Kad didžiosios ir mažosios raidės būtų jautrios, turime naudoti pagalbinę stulpelį (kaip parodyta žemiau):
Norėdami gauti pagalbinio stulpelio reikšmes, naudokite funkciją = ROW (). Tai tiesiog gaus eilutės numerį langelyje.
Kai turėsite pagalbinį stulpelį, čia yra formulė, kuri suteiks didžiųjų ir mažųjų raidžių paieškos rezultatą.
= VLOOKUP (MAX (TIKSAS (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))), $ B $ 2: $ C $ 9,2,0)
Dabar suskaidykime ir suprasime, ką tai daro:
- TIKSLAS (E2, $ A $ 2: $ A $ 9) - šioje dalyje būtų lyginama E2 paieškos vertė su visomis A2: A9 reikšmėmis. Grąžina masyvą TIESŲ/KLAIDŲ, kur grąžinama TIKRA, kai yra tiksli atitiktis. Tokiu atveju grąžinamas šis masyvas: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- TIKSLAS (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Ši dalis daugina TRUE/FALSE masyvą su eilutės numeriu. Visur, kur yra TRUE, jis pateikia eilutės numerį , kitaip jis duoda 0. Tokiu atveju jis grąžintų {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (TIKSLAS (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))) - ši dalis grąžina didžiausią skaičių masyvo vertę. Tokiu atveju jis grąžintų 2 (tai yra eilutės numeris, kuriame yra tiksli atitiktis).
- Dabar mes tiesiog naudojame šį skaičių kaip paieškos vertę ir naudojame paieškos masyvą kaip B2: C9
Pastaba: kadangi tai yra masyvo formulė, naudokite „Control“ + „Shift“ + „Enter“, o ne tik įveskite.
9 pavyzdys - VLOOKUP naudojimas su keliais kriterijais
Funkcija „Excel VLOOKUP“ savo pagrindine forma gali ieškoti vienos paieškos vertės ir grąžinti atitinkamą reikšmę iš nurodytos eilutės.
Tačiau dažnai „VLOOKUP“ programoje „Excel“ reikia naudoti su keliais kriterijais.
Tarkime, kad turite duomenų su mokinių vardu, egzamino tipu ir matematikos balais (kaip parodyta žemiau):
Naudoti funkciją VLOOKUP, norint gauti kiekvieno studento matematikos balą atitinkamiems egzaminų lygiams, gali būti iššūkis.
Pvz., Jei bandysite naudoti VLOOKUP su Matu kaip paieškos vertę, ji visada grąžins 91, tai yra pirmojo Mato įvykio sąraše balas. Kad gautumėte Matto balą kiekvienam egzamino tipui (vieneto testas, vidurio laikotarpis ir baigiamasis), turite sukurti unikalią paieškos vertę.
Tai galima padaryti naudojant pagalbinį stulpelį. Pirmasis žingsnis yra įterpti pagalbinį stulpelį į kairę nuo balų.
Dabar, norėdami sukurti unikalų kiekvieno vardo egzemplioriaus kvalifikatorių, naudokite šią formulę C2: = A2 & "|" & B2
Nukopijuokite šią formulę į visas pagalbinio stulpelio ląsteles. Taip kiekvienam vardo egzemplioriui bus sukurtos unikalios paieškos vertės (kaip parodyta žemiau):
Dabar, nors buvo kartojami vardai, kartojimo nėra, kai vardas derinamas su egzamino lygiu.
Tai palengvina, nes dabar kaip pagalbines stulpelio reikšmes galite naudoti kaip paieškos vertes.
Čia yra formulė, kuri suteiks jums rezultatą G3: I8.
= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Čia mes sujungėme mokinio vardą ir egzamino lygį, kad gautume paieškos vertę, ir mes naudojame šią paieškos vertę ir patikriname ją pagalbinėje stulpelyje, kad gautume atitinkamą įrašą.
Pastaba: pirmiau pateiktame pavyzdyje mes naudojome | kaip separatorius, kai sujungiate tekstą pagalbinio stulpelyje. Kai kuriomis ypač retomis (bet galimomis) sąlygomis galite turėti du skirtingus kriterijus, tačiau kartu duodami tą patį rezultatą. Štai pavyzdys:
Atminkite, kad nors A2 ir A3 yra skirtingi, o B2 ir B3 yra skirtingi, deriniai galiausiai yra vienodi. Bet jei naudojate separatorių, net ir derinys būtų kitoks (D2 ir D3).
Čia yra pamoka, kaip naudoti VLOOKUP su keliais kriterijais, nenaudojant pagalbinių stulpelių. Čia taip pat galite žiūrėti mano vaizdo įrašo pamoką.
10 pavyzdys. Klaidų tvarkymas naudojant VLOOKUP funkciją
„Excel VLOOKUP“ funkcija grąžina klaidą, kai neranda nurodytos peržiūros vertės. Galbūt nenorite, kad bjauri klaidos vertė trikdytų jūsų duomenų estetiką, jei VLOOKUP neranda vertės.
Galite lengvai pašalinti klaidų reikšmes, turinčias bet kokią reikšmę, visą tekstą, pvz., „Nėra“ arba „Nerasta“.
Pavyzdžiui, toliau pateiktame pavyzdyje, kai bandote rasti Brado balą sąraše, jis pateikia klaidą, nes Brado vardo sąraše nėra.
Norėdami pašalinti šią klaidą ir pakeisti ją kažkuo prasmingu, apvyniokite VLOOKUP funkciją IFERROR funkcijoje.
Čia yra formulė:
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0), "Nerasta")
Funkcija IFERROR patikrina, ar pirmojo argumento (šiuo atveju funkcija VLOOKUP) grąžinta vertė yra klaida. Jei tai ne klaida, ji grąžina vertę naudodami funkciją VLOOKUP, kitaip grąžina nerastą.
Funkcija IFERROR pasiekiama nuo „Excel 2007“. Jei prieš tai naudojate versijas, naudokite šią funkciją:
= IF (ISERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)), "Not Found", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))
Taip pat žiūrėkite: Kaip tvarkyti „VLOOKUP“ klaidas „Excel“.
Tai yra šioje „VLOOKUP“ pamokoje.
Bandžiau pateikti pagrindinius „Vlookup“ funkcijos naudojimo „Excel“ pavyzdžius. Jei norite, kad prie šio sąrašo būtų pridėta daugiau pavyzdžių, praneškite man komentarų skiltyje.
Pastaba: aš stengiausi perskaityti šią pamoką, bet jei radote klaidų ar rašybos klaidų, praneškite man 🙂
Naudojant VLOOKUP funkcija „Excel“ - vaizdo įrašas
- „Excel HLOOKUP“ funkcija.
- „Excel XLOOKUP“ funkcija
- „Excel INDEX“ funkcija.
- „Excel“ netiesioginė funkcija.
- „Excel MATCH“ funkcija.
- „Excel OFFSET“ funkcija.
