Funkcija XLOOKUP
Naudokite funkciją XLOOKUP norėdami rasti elementus lentelėje arba diapazone pagal eilutę. Pavyzdžiui, ieškokite automobilio dalies kainos pagal dalies numerį arba raskite darbuotojo vardą pagal jo darbuotojo ID. Naudodami XLOOKUP galite ieškoti ieškos termino viename stulpelyje ir pateikti rezultatą iš tos pačios eilutės kitame stulpelyje, neatsižvelgiant į tai, kurioje pusėje yra grąžinamas stulpelis.
Pastaba: XLOOKUP nėra Excel 2016 ir "Excel 2019", tačiau gali būti, kad naudojate darbaknygę programoje "Excel 2016" arba "Excel 2019" su funkcija XLOOKUP, kurią sukūrė kažkas kitas naudodamas naujesnę "Excel" versiją.
Sintaksė
Funkcija XLOOKUP ieško diapazone arba masyve, tada grąžina elementą, atitinkantį rastą pirmą atitikmenį. Jei atitikmens nėra, XLOOKUP gali grąžinti artimiausią (apytikslį) atitikmenį.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
|
Argumentas |
Aprašas |
|---|---|
|
ieškos_reikšmė Reikia* |
Ieškomą |
|
ieškos_masyvas Būtinas |
Ieškomą masyvą arba diapazoną |
|
return_array Būtinas |
Grąžinamas masyvas arba diapazonas |
|
[if_not_found] Pasirenkamas |
Jei tinkamo atitikmens nerandama, pateikite [if_not_found] tekstą. Jei nerastas galiojantis atitikmuo ir trūksta [if_not_found], grąžinama #N/A . |
|
[match_mode] Pasirenkamas |
Nurodykite atitikmens tipą: 0 – Tikslus atitikmuo. Jei nerasta, pateikti #N/A. Tai yra numatytasis nustatymas. -1 - Tikslus atitikmuo. Jei nerasta, pateikti kitą mažesnį elementą. 1 – Tikslus atitikmuo. Jei nieko nerandama, grąžinkite kitą didesnį elementą. 2 – pakaitos simbolis atitinka, kur *, ?, ir ~ turi specialią reikšmę. |
|
[search_mode] Pasirenkamas |
Nurodykite, kurį ieškos režimą naudoti: 1 . Atlikite iešką nuo pirmojo elemento. Tai yra numatytasis nustatymas. -1 – atlikti atvirkštinę iešką pradedant nuo paskutinio elemento. 2 – Atlikti dvejetainę iešką, kuri priklauso nuo lookup_array rikiavimo didėjimo tvarka. Jei nesurūšiuota, bus pateikti neleistini rezultatai. -2 – atlikti dvejetainę iešką, kuri priklauso nuo lookup_array rūšiuojama mažėjimo tvarka. Jei nesurūšiuota, bus pateikti neleistini rezultatai. |
Pavyzdžiai
1 pavyzdyje naudojamas XLOOKUP norint ieškoti šalies pavadinimo diapazone, tada pateikti telefono šalies kodą. Jis apima argumentus lookup_value (langelis F2), lookup_array (diapazonas B2:B11) ir return_array (diapazonas D2:D11). Jame nėra match_mode argumento, nes XLOOKUP pagal numatytuosius nustatymus sukuria tikslų atitikmenį.
Pastaba: XLOOKUP naudoja peržvalgos masyvą ir grąžinamą masyvą, o VLOOKUP naudoja vieną lentelės masyvą, po kurio eina stulpelių indekso numeris. Atitinkama VLOOKUP formulė šiuo atveju būtų: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
2 pavyzdyje ieškome darbuotojo informacijos pagal darbuotojo ID numerį. Skirtingai nei VLOOKUP, XLOOKUP gali grąžinti masyvą su keliais elementais, kad viena formulė galėtų grąžinti darbuotojo vardą ir skyrių iš langelių C5:D14.
———————————————————————————
3 pavyzdyje prie ankstesnio pavyzdžio pridedamas if_not_found argumentas.
———————————————————————————
4 pavyzdys C stulpelyje ieško langelyje E2 įvestų asmeninių pajamų ir stulpelyje B randa atitinkamą mokesčio tarifą. Ji nustato , kad if_not_found argumentas pateiktų 0 (nulį), jei nieko nerasta. Argumentas match_mode nustatytas į 1, o tai reiškia, kad funkcija ieškos tikslaus atitikmens, o jei neranda, ji grąžins kitą didesnį elementą. Galiausiai, search_mode argumentas nustatytas kaip 1, o tai reiškia, kad funkcija ieškos nuo pirmojo elemento iki paskutinio.
Pastaba: XARRAY lookup_array stulpelis yra return_array stulpelio dešinėje, o VLOOKUP gali atrodyti tik iš kairės į dešinę.
———————————————————————————
5 pavyzdys naudoja įdėtąją XLOOKUP funkciją vertikaliam ir horizontaliam atitikmenims atlikti. Pirmiausia stulpelyje B ieškomas bendrasis pelnas , tada lentelės viršutinėje eilutėje (diapazone C5:F5) ieškomas 1 ketv . ir galiausiai dviejų reikšmių sankirtoje grąžinama reikšmė. Tai panašu į INDEX ir MATCH funkcijų naudojimą kartu.
Patarimas: Taip pat galite naudoti XLOOKUP, kad pakeistumėte funkciją HLOOKUP .
Pastaba: Formulė langeliuose D3:F3 yra: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17)).
———————————————————————————
6 pavyzdys naudoja funkciją SUM ir dvi įdėtąsias XLOOKUP funkcijas, kad susumuotų visas reikšmes tarp dviejų diapazonų. Šiuo atveju norime sumuoti vynuogių, bananų vertes ir įtraukti kriaušės, kurios yra tarp jų.
Formulė langelyje E3 yra: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Kaip tai veikia? XLOOKUP grąžina diapazoną, todėl skaičiuojant formulė atrodo taip: =SUM($E$7:$E$9). Kaip tai veikia, galite patys pasirinkti langelį, kuriame yra panaši į šią formulę XLOOKUP formulė, tada pasirinkite Formulės > Formulės tikrinimas > Įvertinti formulę, tada pasirinkite Įvertinti ir atlikti skaičiavimą.
Pastaba: Dėkojame, kad "Microsoft Excel" MVP Bill Jelen siūlo šį pavyzdį.
———————————————————————————
Taip pat žr.
Visada galite paklausti „Excel“ technologijų bendruomenės specialisto arba gauti palaikymo bendruomenėse.
Reikia daugiau pagalbos?
Norite daugiau parinkčių?
Sužinokite apie prenumeratos pranašumus, peržiūrėkite mokymo kursus, sužinokite, kaip apsaugoti savo įrenginį ir kt.
Bendruomenės padeda užduoti klausimus ir į juos atsakyti, pateikti atsiliepimų ir išgirsti iš ekspertų, turinčių daug žinių.
