Funkcija XLOOKUP

Naudokite funkciją Xlookup , kai reikia surasti dalykus lentelėje ar diapazonui pagal eilutę. Pvz., peržiūrėkite automobilių dalies kainą pagal dalies numerį arba raskite darbuotojo vardą pagal darbuotojo ID. Naudodami XLOOKUP galite ieškoti viename ieškos termino stulpelyje ir grąžinti rezultatą iš tos pačios eilutės kitame stulpelyje, neatsižvelgiant į tai, kurioje pusėje yra grįžimo stulpelis.

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

Funkcija XLOOKUP ieško intervalo arba masyvo ir grąžina elementą, atitinkantį pirmą aptiktą atitikimą. Jei nėra atitikmens, XLOOKUP gali pateikti artimiausią (apytikslį) atitikimą. 

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Argumentas

Aprašas

ieškos_reikšmė

Būtinas

Peržvalgos reikšmė

ieškos_masyvas

Būtinas

Ieškos masyvas arba sritis

return_array

Būtinas

Norėdami grįžti masyvas arba intervalas

[if_not_found]

Pasirinktinai

Kai tinkamas atitikmuo nerastas, grąžinkite [if_not_found] jūsų tiekiamą tekstą.

Jei netinkamas atitikimas nerastas ir [if_not_found] nėra, bus grąžintas #N/A.

[match_mode]

Pasirinktinai

Apibrėžti atitikties tipą:

0 – tikslios atitikties. Jei nieko neradote, grįžkite #N/A. Tai yra numatytasis nustatymas.

-1-tikslus atitikimas. Jei nėra rasta, grąžinkite paskesnį mažesnį elementą.

1 – tiksli atitiktis. Jei nėra rasta, grąžinkite paskesnį elementą.

2 – pakaitos simbolį atitinka kur *,? ir ~ turi ypatingą reikšmę.

[search_mode]

Pasirinktinai

Nustatykite norimą naudoti ieškos režimą:

1 – atlikite iešką nuo pirmo elemento. Tai yra numatytasis nustatymas.

-1 – atlikite atvirkštinį iešką nuo paskutinio elemento.

2 – atlikite dvejetainę iešką, kuri remiasi lookup_array Surikiuota didėjimo tvarka. Jei nesurūšiuota, bus pateikti neleistini rezultatai.

-2 – atlikite dvejetainę iešką, kuri remiasi lookup_array rūšiuojama mažėjančia tvarka. Jei nesurūšiuota, bus pateikti neleistini rezultatai.

Pavyzdžiai

1 pavyzdys

Šis pavyzdys yra aukščiau pateiktame vaizdo įraše ir naudoja paprastą XLOOKUP, kad galėtų ieškoti šalies pavadinimo, tada grąžinti telefono šalies kodą. Jame yra tik lookup_value (langelio F2), lookup_array (diapazonas B2: B11) ir return_array (diapazonas D2: D11) argumentai. Ji neapima match_mode argumento, nes XLOOKUP pagal nutylėjimą yra tiksliai atitinka.

Funkcijos XLOOKUP, naudojamos darbuotojo pavadinimui ir padaliniui pagal darbuotojo ID, pavyzdys. Formulė yra = XLOOKUP (B2, B5: B14, C5: C14).

Pastaba: XLOOKUP skiriasi nuo VLOOKUP, nes naudoja atskirą peržvalgą ir grąžina masyvus, kur VLOOKUP naudoja vieną lentelės masyvą ir stulpelio indekso numerį. Šiuo atveju lygiavertė VLOOKUP formulė būtų: = VLOOKUP (F2, B2: D11, 3, klaidingas)

2 pavyzdys

Šiame pavyzdyje mes ieškome darbuotojų informacijos pagal darbuotojo ID numerį. Skirtingai nei VLOOKUP, XLOOKUP gali pateikti masyvą su keliais elementais, kuris leidžia vieną formulę pateikti ir darbuotojo vardą, ir padalinį iš ląstelių C5: D14.

XLOOKUP funkcijos, naudojamos darbuotojui pavadinimui ir padaliniui, pagrįstam darbuotojo IDt, pavyzdys. Formulė yra: = XLOOKUP (B2, B5: B14, C5: D14; 0; 1)

3 pavyzdys

Šiame pavyzdyje prie aukščiau pateikto pavyzdžio pridedami if_not_found argumentai.

XLOOKUP funkcijos, naudojamos darbuotojo pavadinimui ir padaliniui pagal darbuotojo ID, su if_not_found argumentu, pavyzdys. Formulė yra = XLOOKUP (B2, B5: B14, C5: D14, 0, 1, "darbuotojas nerastas")

Pavyzdys 4

Toliau pateiktame pavyzdyje parodyta, kaip C stulpelyje yra asmeninės pajamos, įvestos į langelį E2, ir randa atitikimo mokesčio tarifą B stulpelyje. Jame nustatomas if-not_found argumentas, kad būtų grąžinta 0, jei nieko nėra rasta. Argumentas match_mode nustatytas kaip 1, o tai reiškia, kad funkcija ieškos tikslaus atitikmens, o jei ji negali rasti, ji pateiks kitą didesnį elementą. Galiausiai search_mode argumentas nustatytas kaip 1, o tai reiškia, kad funkcija ieškos iš pirmojo elemento į paskutinį.

XLOOKUP funkcijos, naudojamos norint pateikti mokesčio tarifą pagal maksimalias pajamas, vaizdas. Tai apytikslis atitikmuo. Formulė yra: = XLOOKUP (E2, C2: C7, B2: B7, 1; 1)

Pastaba: Skirtingai nei VLOOKUP, lookup_array stulpelis yra į dešinę nuo return_array stulpelio, kur VLOOKUP gali ieškoti tik iš kairės į dešinę.

5 pavyzdys

Be to, naudosime įdėtąją XLOOKUP funkciją, kad atliktumėte tiek vertikalų, tiek horizontalų atitikmens. Šiuo atveju pirmiausia bus rodomas Bendrasis pelnas stulpelyje B, tada ieškokite Qtr1 lentelės viršutinėje eilutėje (intervalas C5: F5) ir grąžinkite reikšmę iš dviejų sankirtos. Tai panašu į funkcijų index ir Match naudojimą kartu. Taip pat galite naudoti XLOOKUP, kad pakeistumėte funkciją HLOOKUP .

Funkcija XLOOKUP, naudojama horizontaliesiems duomenims iš lentelės pateikti naudojant lizdus 2 XLOOKUPs. Formulė yra: = XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17))

Formulė langeliuose D3: F3 yra: = XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17)).

6 pavyzdys

Šiame pavyzdyje naudojama funkcija SUM, o dvi "xlookup" funkcijos įdėtos kartu sudėjus visas reikšmes tarp dviejų diapazonų. Šiuo atveju norime susumuoti vynuoges, bananus ir įtraukti kriaušes, kurios yra tarp dviejų.

"XLOOKUP" naudojimas su suma, kad suminis reikšmių, kurios patenka tarp dviejų pasirinkimų, intervalas

Formulė langelyje E3 yra: = SUM (XLOOKUP (B3, B6: B10, E6: E10): XLOOKUP (C3, B6: B10, E6: E10))

Kaip tai veikia? XLOOKUP pateikia intervalą, todėl apskaičiavus formulė baigiasi taip: = SUM ($E $7: $E $9). Galite matyti, kaip tai veikia savo nuožiūra, pažymėdami langelį, kurio XLOOKUP formulė panaši į šį, tada eikite į formulės > formulės tikrinimą > Įvertinkite formulęir paspauskite mygtuką įvertinti , kad pereitumėte per skaičiavimą.

Pastaba: Dėkojame, kad "Microsoft Excel" MVP, Bill Jelen, rodo šį pavyzdį.

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Taip pat žr.

Funkcija XMATCH

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

„Excel“ funkcijos (pagal kategoriją)

Pastaba:  Šis puslapis išverstas automatiškai, todėl gali būti gramatikos klaidų ar netikslumų. Mūsų tikslas – padaryti, kad šis turinys būtų jums naudingas. Prašome mus informuoti, ar radote reikiamos informacijos. Čia yra straipsnis anglų kalba, kuriuo galite pasinaudoti kaip nuoroda.

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×