Kaip naudoti „XLOOKUP“ funkciją „Microsoft Excel“

Naujasis „Excel“ XLOOKUP pakeis „VLOOKUP“, suteikdamas galingą vienos populiariausių „Excel“ funkcijų pakaitalą. Ši nauja funkcija pašalina kai kuriuos „VLOOKUP“ apribojimus ir turi papildomų funkcijų. Štai ką jūs turite žinoti.

Kas yra XLOOKUP?

Naujoji XLOOKUP funkcija turi sprendimus dėl kai kurių didžiausių „VLOOKUP“ apribojimų. Be to, jis taip pat pakeičia HLOOKUP. Pavyzdžiui, „XLOOKUP“ gali žiūrėti į kairę, pagal numatytuosius nustatymus tiksli atitiktis ir leidžia vietoj stulpelio numerio nurodyti langelių diapazoną. „VLOOKUP“ nėra taip paprasta naudoti ar universalus. Parodysime, kaip visa tai veikia.

Šiuo metu „XLOOKUP“ yra prieinama tik „Insider“ programos vartotojams. Kiekvienas gali prisijungti prie „Insider“ programos, kad pasiektų naujausias „Excel“ funkcijas, kai tik jos bus pasiekiamos. „Microsoft“ netrukus pradės ją naudoti visiems „Office 365“ vartotojams.

Kaip naudotis funkcija XLOOKUP

Pasinerkime tiesiai, naudodami „XLOOKUP“ pavyzdį. Paimkite toliau pateiktus duomenų pavyzdžius. Mes norime grąžinti skyrių iš F stulpelio kiekvienam A stulpelio ID.

Tai yra klasikinis tikslios atitikties paieškos pavyzdys. XLOOKUP funkcija reikalauja tik trijų dalių.

Žemiau pateiktame paveikslėlyje pateikiama „XLOOKUP“ su šešiais argumentais, tačiau tiksliai atitikčiai būtini tik trys pirmieji. Taigi sutelkime dėmesį į juos:

  • „Lookup_value“:  ko ieškote.
  • Lookup_array:  Kur ieškoti.
  • Return_array:  diapazonas, kuriame yra grąžintina vertė.

Šiame pavyzdyje veiks ši formulė: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Panagrinėkime keletą privalumų, kuriuos čia turi XLOOKUP, palyginti su VLOOKUP.

Nebėra stulpelio indekso numerio

Liūdnai pagarsėjęs trečiasis „VLOOKUP“ argumentas buvo nurodyti informacijos, kurią reikia grąžinti iš lentelių masyvo, stulpelio numerį. Tai nebėra problema, nes „XLOOKUP“ leidžia pasirinkti diapazoną, iš kurio norite grįžti (šio pavyzdžio F stulpelis).

Nepamirškite, kad XLOOKUP gali peržiūrėti duomenis, likusius pasirinktoje langelyje, skirtingai nei VLOOKUP. Daugiau apie tai žemiau.

Įterpus naujus stulpelius, jūs taip pat nebeturite sugadintos formulės problemos. Jei taip atsitiko jūsų skaičiuoklėje, grąžinimo diapazonas bus automatiškai sureguliuotas.

Tikslus atitikimas yra numatytasis

Mokantis „VLOOKUP“ visada buvo painu, kodėl reikėjo nurodyti tikslią atitiktį.

Laimei, XLOOKUP pagal numatytuosius nustatymus yra tiksli atitiktis - kur kas dažnesnė priežastis naudoti paieškos formulę). Tai sumažina poreikį atsakyti į penktąjį argumentą ir užtikrina mažiau klaidų vartotojams, naujiems formulėje.

Taigi trumpai tariant, „XLOOKUP“ užduoda mažiau klausimų nei „VLOOKUP“, yra patogesnis vartotojui ir yra patvaresnis.

„XLOOKUP“ gali žiūrėti į kairę

Galimybė pasirinkti paieškos diapazoną daro XLOOKUP universalesnį nei VLOOKUP. Naudojant „XLOOKUP“, lentelių stulpelių tvarka nėra svarbi.

VLOOKUP buvo suvaržytas ieškant kairiausio lentelės stulpelio ir grįžus iš nurodyto stulpelių skaičiaus į dešinę.

Toliau pateiktame pavyzdyje turime ieškoti ID (E stulpelis) ir grąžinti asmens vardą (D stulpelis).

Ši formulė gali tai pasiekti: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Ką daryti, jei nerandama

Paieškos funkcijų vartotojai yra gerai susipažinę su klaidos pranešimu # N / A, kuris juos pasitinka, kai jų funkcija „VLOOKUP“ arba „MATCH“ negali rasti to, ko jai reikia. Dažnai tam yra logiška priežastis.

Todėl vartotojai greitai ištiria, kaip paslėpti šią klaidą, nes ji nėra teisinga ar naudinga. Ir, žinoma, yra tam būdų.

„XLOOKUP“ pateikia savo įmontuotą argumentą „jei nerasta“, kad būtų galima pašalinti tokias klaidas. Pažiūrėkime, kaip tai veikia su ankstesniu pavyzdžiu, bet su neteisingai įvestu ID.

Šioje formulėje vietoj klaidos pranešimo bus rodomas tekstas „Neteisingas ID“: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

XLOOKUP naudojimas diapazono paieškai

Nors tai nėra tokia įprasta kaip tiksli atitiktis, labai efektyvus paieškos formulės naudojimas yra vertės ieškojimas diapazonuose. Paimkite šį pavyzdį. Mes norime grąžinti nuolaidą priklausomai nuo išleistos sumos.

Šį kartą mes neieškome konkrečios vertės. Turime žinoti, kur B stulpelio vertės patenka į E stulpelio ribas. Tai nulems uždirbtą nuolaidą.

„XLOOKUP“ turi pasirinktinį penktąjį argumentą (atminkite, kad pagal numatytuosius nustatymus yra tiksli atitiktis), pavadintą atitikties režimu.

Galite pamatyti, kad „XLOOKUP“ turi daugiau galimybių su apytikslėmis atitiktimis nei „VLOOKUP“.

Yra galimybė rasti artimiausią atitikmenį, mažesnį nei (-1) arba didžiausią už (1) ieškomą vertę. Taip pat yra galimybė naudoti pakaitos simbolius (2), tokius kaip? arba *. Šis nustatymas pagal numatytuosius nustatymus nėra įjungtas, kaip buvo naudojant „VLOOKUP“.

Šiame pavyzdyje pateikta formulė pateikia arčiausiai mažesnę nei ieškoma vertė, jei tiksli atitiktis nerasta: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Tačiau C7 langelyje yra klaida, kur pateikiama klaida # N / A (argumentas „jei nerasta“ nebuvo naudojamas). Tai turėjo suteikti 0% nuolaidą, nes išleidus 64, neatitinka jokios nuolaidos kriterijų.

Kitas funkcijos XLOOKUP pranašumas yra tas, kad nereikalaujama, kad paieškos diapazonas būtų didėjimo tvarka, kaip tai daro VLOOKUP.

Paieškos lentelės apačioje įveskite naują eilutę ir atidarykite formulę. Išplėskite naudojamą diapazoną spustelėdami ir vilkdami kampus.

Formulė nedelsdama ištaiso klaidą. Tai nėra problema, kai diapazono apačioje yra „0“.

Asmeniškai aš vis tiek rūšiuosiu lentelę pagal paieškos stulpelį. Jei apačioje būtų „0“, tai išprotėčiau. Bet tai, kad formulė nesugedo, yra puiku.

XLOOKUP taip pat pakeičia HLOOKUP funkciją

Kaip minėta, funkcija XLOOKUP taip pat yra skirta pakeisti HLOOKUP. Viena funkcija pakeis dvi. Puiku!

Funkcija HLOOKUP yra horizontali paieška, naudojama paieškai palei eilutes.

Ne taip gerai žinomas kaip jo brolis VLOOKUP, bet naudingas tokiems pavyzdžiams kaip žemiau, kur antraštės yra A stulpelyje, o duomenys yra palei 4 ir 5 eilutes.

„XLOOKUP“ gali atrodyti abiem kryptimis - stulpeliais žemyn ir išilgai eilučių. Nebereikia dviejų skirtingų funkcijų.

Šiame pavyzdyje formulė naudojama grąžinti pardavimo vertę, susijusią su vardu langelyje A2. Atrodo 4 eilutėje, kad surastų pavadinimą, ir grąžina vertę iš 5 eilutės:=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP gali žiūrėti iš apačios į viršų

Paprastai reikia ieškoti sąrašo, kad surastumėte pirmą (dažnai tik vienintelį) vertės įvykį. XLOOKUP turi šeštąjį argumentą, pavadintą paieškos režimu. Tai leidžia mums perjungti paiešką pradėti nuo apačios ir ieškoti sąrašo, kad vietoj to rastume paskutinį vertės įvykį.

Toliau pateiktame pavyzdyje norėtume rasti kiekvieno produkto A stulpelio atsargų lygį.

Paieškos lentelė pateikiama datų tvarka ir kiekvienam produktui yra keli patikrinimai. Norime grąžinti atsargų lygį nuo paskutinio patikrinimo (paskutinio produkto ID atvejo).

Šeštasis funkcijos XLOOKUP argumentas pateikia keturias parinktis. Mums įdomu naudoti parinktį „Ieškoti paskutinio iki pirmo“.

Užpildyta formulė rodoma čia: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

Šioje formulėje ketvirtasis ir penktasis argumentai buvo ignoruojami. Tai neprivaloma, ir mes norėjome, kad būtų nustatyta tiksli atitiktis.

Apvalumas

XLOOKUP funkcija yra labai laukiama VLOOKUP ir HLOOKUP funkcijų perėmėja.

Šiame straipsnyje buvo naudojami įvairūs pavyzdžiai, siekiant parodyti XLOOKUP pranašumus. Vienas iš jų yra tai, kad „XLOOKUP“ gali būti naudojamas lapuose, darbaknygėse ir kartu su lentelėmis. Straipsnyje pavyzdžiai buvo nesudėtingi, kad padėtų mums geriau suprasti.

Dėl dinaminių masyvų, kurie netrukus bus įvesti į „Excel“, jie taip pat gali grąžinti reikšmių diapazoną. Tai tikrai verta toliau tyrinėti.

VLOOKUP dienos yra sunumeruotos. „XLOOKUP“ yra čia ir netrukus bus de facto paieškos formulė.