Formulės ir funkcijos

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ę "Excel 2016" arba "Excel 2019" su funkcija XLOOKUP, kurią sukūrė kažkas kitas naudodamas naujesnę "Excel" versiją.

Jūsų naršyklė nepalaiko vaizdo įrašo. Įdiekite „Microsoft Silverlight“, „Adobe Flash Player“ ar „Internet Explorer 9“.

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ė

Reikalingas*

Ieškomą reikšmę *Jei nenurodyta, funkcija XLOOKUP grąžins tuščius langelius, kurie bus rasti lookup_array.   

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į.

Funkcijos XLOOKUP, naudojamos grąžinti darbuotojo vardą ir skyrių pagal darbuotojo ID, pavyzdys. Formulė yra =XLOOKUP(B2,B5:B14,C5:C14)

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.

Funkcijos XLOOKUP, naudojamos grąžinti darbuotojo vardą ir skyrių pagal darbuotojų ID, pavyzdys. Formulė yra: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

3 pavyzdyje    prie ankstesnio pavyzdžio pridedamas if_not_found argumentas.

Funkcijos XLOOKUP, naudojamos pateikti darbuotojo vardą ir skyrių pagal darbuotojo ID su argumentu if_not_found, pavyzdys. Formulė yra =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Darbuotojas nerastas")

———————————————————————————

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.

XLOOKUP funkcijos, naudojamos grąžinti mokesčių tarifą pagal didžiausias pajamas, vaizdas. Tai apytikslis atitikmuo. Formulė yra: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

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 .

Funkcijos XLOOKUP, naudojamos pateikti horizontalūs duomenys iš lentelės įdėjimo 2 XLOOKUP, vaizdas. Formulė yra: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

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ų.

XLOOKUP naudojimas su SUM norint sumuoti reikšmių diapazoną, patenkantį tarp dviejų pasirinkimų

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.

Funkcija XMATCH

„Excel“ funkcijos (pagal abėcėlę)

„Excel“ funkcijos (pagal kategoriją)

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ų.