Funkcija XLOOKUP

Naudokite funkciją Xlookup , kad rastumėte dalykus lentelėje arba 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.

Sintaksė

Funkcija XLOOKUP ieško intervalo arba masyvo ir grąžina elementą, atitinkantį pirmą aptiktą atitikmens elementą. Jei atitikmenų nėra, 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ūtina	Reikšmė, kurią reikia ieškoti * Jei nenurodyta, XLOOKUP pateikia tuščius langelius, kuriuos randa lookup_array.
ieškos_masyvas Būtinas	Ieškos masyvas arba sritis
return_array Būtinas	Norėdami grįžti masyvas arba intervalas
[if_not_found] Pasirenkamas	Kai tinkamas atitikmuo nerastas, grąžinkite [if_not_found] jūsų tiekiamą tekstą. Jei tinkamas atitikmuo nerastas ir nėra [if_not_found], grąžinamas #N/a .
[match_mode] Pasirenkamas	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] Pasirenkamas	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.

Argumentas

Aprašas

ieškos_reikšmė

Būtina

Reikšmė, kurią reikia ieškoti

* Jei nenurodyta, XLOOKUP pateikia tuščius langelius, kuriuos randa lookup_array.

ieškos_masyvas

Būtinas

Ieškos masyvas arba sritis

return_array

Būtinas

Norėdami grįžti masyvas arba intervalas

[if_not_found]

Pasirenkamas

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

Jei tinkamas atitikmuo nerastas ir nėra [if_not_found], grąžinamas #N/a .

[match_mode]

Pasirenkamas

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]

Pasirenkamas

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 pavyzdyje funkcija xlookup turi ieškoti šalies pavadinimo diapazonu, tada grąžinti jos telefono šalies kodą. Tai yra lookup_value (langelio F2), Lookup_array (diapazonas B2: B11) ir Return_array (diapazonas D2: D11) argumentai. Ji neapima match_mode argumento, nes pagal NUMATYTUOSIUS "xlookup" pateikia tikslią atitikį.

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

Pastaba: XLOOKUP naudoja peržvalgos masyvą ir grąžinimo masyvą, o VLOOKUP naudoja vieną lentelės masyvą, o po to – stulpelio indekso numerį. Šiuo atveju lygiavertė VLOOKUP formulė būtų: = VLOOKUP (F2, B2: D11, 3, klaidingas)

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

2 pavyzdyje rodoma darbuotojo informacija pagal darbuotojo ID numerį. Skirtingai nei VLOOKUP, XLOOKUP gali pateikti masyvą su keliais elementais, todėl viena formulė gali pateikti darbuotojo vardą ir skyrių iš langelių 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 pavyzdyje į ankstesnį pavyzdį įtraukiamas if_not_found argumentas.

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")

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

4 pavyzdyje ieškoma asmeninių pajamų, įvestų langelyje E2, stulpelyje C ir randa sutampantį mokesčio tarifą stulpelyje B. Jis nustato if_not_found argumentą, kad grąžintų 0 (nulį), jei nieko nerado. Argumentas match_mode nustatytas kaip 1, o tai reiškia, kad funkcija ieškos tikslaus atitikmens ir jei ji negali rasti vieno, ji pateikia 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: XARRAY lookup_array stulpelis yra į dešinę nuo return_array stulpelio, o VLOOKUP gali ieškoti tik iš kairės į dešinę.

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

5 pavyzdys naudojama įdėtoji XLOOKUP funkcija, kad būtų galima atlikti vertikalųjį ir horizontalųjį atitikmens. Jis pirmiausia ieško bendrojo pelno stulpelyje B, tada ieško Qtr1 viršutinėje eilutėje (intervalas C5: F5), ir galiausiai grąžina dviejų sankirtos reikšmę. Tai panašu į funkcijų index ir Match naudojimą.

Patarimas: 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))

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ą SUMir dvi ĮDĖTĄSIAS xlookup funkcijas, kad susumuotų 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 pasirinkite formulės > formulės tikrinimą > įvertinti formulęir pasirinkite įvertinti , kad būtų atliktas skaičiavimas.

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

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