Kaip naudotis „Excel XLOOKUP“ funkcija -

Santrauka

„Excel XLOOKUP“ funkcija yra modernus ir lankstus pakeitimas senesnėms funkcijoms, tokioms kaip VLOOKUP, HLOOKUP ir LOOKUP. „XLOOKUP“ palaiko apytikslį ir tikslų atitikimą, pakaitos simbolius (*?) Dalinėms atitiktims ir vertikalių arba horizontalių diapazonų paieškas.

Tikslas

Paieškos vertės diapazone arba masyve

Grąžinimo vertė

Atitinkamos vertės (-ų) iš grąžinimo masyvo

Sintaksė

= XLOOKUP (lookup, look_array, return_array, (not_found), (match_mode), (search_mode))

Argumentai

  • lookup - paieškos vertė.
  • lookup_array - masyvas arba diapazonas ieškoti.
  • return_array - masyvas arba diapazonas, kurį norite grąžinti.
  • not_found - (neprivaloma) Vertė, kurią reikia grąžinti, jei nerandama atitikties.
  • match_mode - (neprivaloma) 0 = tiksli atitiktis (numatytasis nustatymas), -1 = tiksli atitiktis arba kita mažiausia, 1 = tiksli atitiktis arba kita didesnė, 2 = pakaitos žymė.
  • search_mode - (neprivaloma) 1 = paieška iš pirmo (numatytasis), -1 = paieška iš paskutinės, 2 = dvejetainė paieška didėjanti, -2 = dvejetainė paieška mažėjanti.

Versija

„Excel 365“

Naudojimo užrašai

„XLOOKUP“ yra modernus „VLOOKUP“ funkcijos pakaitalas. Tai lanksti ir universali funkcija, kurią galima naudoti įvairiausiose situacijose.

„XLOOKUP“ gali rasti vertikalių ar horizontalių diapazonų reikšmes, gali atlikti apytikslę ir tikslią atitiktį ir palaiko pakaitos simbolius (*?) Dalinėms rungtynėms. Be to, „XLOOKUP“ gali ieškoti duomenų pradedant nuo pirmosios ar paskutinės vertės (žr. Atitikties tipą ir paieškos režimo informaciją toliau). Lyginant su senesnėmis funkcijomis, tokiomis kaip VLOOKUP, HLOOKUP ir LOOKUP, XLOOKUP siūlo keletą pagrindinių privalumų.

Nerastas pranešimas

Kai „XLOOKUP“ negali rasti atitikties, ji pateikia klaidą # N / A, kaip ir kitos „Excel“ atitikties funkcijos. Skirtingai nuo kitų atitikimo funkcijų, „XLOOKUP“ palaiko pasirinktinį argumentą, pavadintą „not_found“, kuris gali būti naudojamas klaidai # N / A panaikinti, kai ji kitu atveju pasirodys. Tipiškos „not_found“ vertės gali būti „Nerasta“, „Nėra atitikties“, „Nėra rezultatų“ ir kt. Pateikdami „not_found“ vertę, tekstą pridėkite dvigubomis kabutėmis ("").

Pastaba: būkite atsargūs, jei pateikiate tuščią eilutę ("") not_found. Jei atitiktis nerasta, XLOOKUP vietoje # N / A nieko nerodys. Jei norite pamatyti # N / A klaidą, kai atitiktis nerandama, visiškai praleiskite argumentą.

Rungtynių tipas

Pagal numatytuosius nustatymus „XLOOKUP“ atliks tikslų atitikimą. Rungtynių elgesį kontroliuoja pasirinktinis argumentas, vadinamas „ match_type“, kuris turi šias parinktis:

Rungtynių tipas Elgesys
0 (numatytasis) Tikslus atitikimas. Grįš # N / A, jei nebus rungtynių.
-1 Tiksli atitiktis arba kitas mažesnis elementas.
1 Tiksli atitiktis arba kita didesnė prekė.
2 Pakaitos simbolių rungtynės (*,?, ~)

Paieškos režimas

Pagal numatytuosius nustatymus XLOOKUP pradės atitikti nuo pirmosios duomenų vertės. Paieškos elgesį kontroliuoja pasirinktinis argumentas, vadinamas paieškos_mode , kuris pateikia šias parinktis:

Paieškos režimas Elgesys
1 (numatytasis) Paieška pagal pirmąją vertę
-1 Paieška pagal paskutinę vertę (atvirkštinė)
2 Dvejetainės paieškos vertės surūšiuotos didėjimo tvarka
-2 Dvejetainės paieškos vertės surūšiuotos mažėjimo tvarka

Dvejetainės paieškos vyksta labai greitai, tačiau duomenys turi būti rūšiuojami pagal poreikį. Jei duomenys nėra rūšiuojami tinkamai, dvejetainė paieška gali pateikti netinkamus rezultatus, kurie atrodo visiškai normalūs.

1 pavyzdys - pagrindinė tiksli atitiktis

Pagal numatytuosius nustatymus „XLOOKUP“ atliks tikslią atitiktį. Toliau pateiktame pavyzdyje „XLOOKUP“ naudojamas norint gauti pardavimus pagal tikslią „Movie“ atitiktį. H5 formulė yra:

=XLOOKUP(H4,B5:B9,E5:E9)

Išsamesnis paaiškinimas čia.

2 pavyzdys - pagrindinė apytikslė atitiktis

Norėdami įgalinti apytikslę atitiktį, pateikite argumento „match_mode“ vertę. Žemiau pateiktame pavyzdyje XLOOKUP yra naudojamas apskaičiuojant nuolaidą pagal kiekį, kuriam reikia apytikslės atitikties. Formulė F5 pateikia -1 dėl match_mode, kad būtų įmanomas apytikslis atitikimas su tikslia atitiktimi arba kita mažiausia elgsena:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Išsamesnis paaiškinimas čia.

3 pavyzdys - kelios vertės

„XLOOKUP“ gali tuo pačiu metu pateikti daugiau nei vieną vertę tam pačiam mačui. Žemiau pateiktame pavyzdyje parodyta, kaip galima sukonfigūruoti XLOOKUP, kad būtų pateiktos trys atitikimo vertės pagal vieną formulę. C5 formulė yra:

=XLOOKUP(B5,B8:B15,C8:E15)

Atkreipkite dėmesį, kad grąžinimo masyve (C8: E15) yra 3 stulpeliai: Pirmasis, Paskutinis, Departamentas. Visos trys reikšmės grąžinamos ir patenka į C5: E5 diapazoną.

4 pavyzdys - abipusė paieška

XLOOKUP galima naudoti atliekant dvipusę paiešką, įterpiant vieną XLOOKUP į kitą. Žemiau pateiktame pavyzdyje „vidinis“ XLOOKUP gauna visą eilutę (visas „Glass“ vertes), kuri perduodama „išoriniam“ XLOOKUP kaip grįžtamam masyvui. Išorinis XLOOKUP suranda reikiamą grupę (B) ir grąžina atitinkamą reikšmę (17.25) kaip galutinį rezultatą.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Daugiau informacijos čia.

5 pavyzdys - pranešimas nerastas

Kaip ir kitos paieškos funkcijos, jei XLOOKUP neranda vertės, ji pateikia # N / A klaidą. Norėdami rodyti pasirinktinį pranešimą vietoj # N / A, pateikite pasirinktinio argumento „nerasta“ vertę, įterptą į dvigubas kabutes (""). Pvz., Norėdami rodyti „Nerasta“, kai nerandama atitinkamo filmo, remiantis toliau pateiktu darbalapiu, naudokite:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Galite tinkinti šį pranešimą, kaip jums patinka: „Nėra atitikties“, „Filmas nerastas“ ir kt.

6 pavyzdys - sudėtingi kriterijai

Turėdamas galimybę natūraliai tvarkyti masyvus, XLOOKUP gali būti naudojamas pagal sudėtingus kriterijus. Toliau pateiktame pavyzdyje XLOOKUP atitinka pirmąjį įrašą, kur: sąskaita prasideda „x“, o regionas yra „rytai“, o mėnuo nėra balandžio mėn .:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Išsami informacija: (1) paprastas pavyzdys, (2) sudėtingesnis pavyzdys.

XLOOKUP nauda

XLOOKUP siūlo keletą svarbių pranašumų, ypač palyginti su VLOOKUP:

  • „XLOOKUP“ gali ieškoti duomenų dešinėje arba kairėje nuo paieškos verčių
  • XLOOKUP gali pateikti kelis rezultatus (3 pavyzdys aukščiau)
  • „XLOOKUP“ pagal numatytuosius nustatymus yra tiksli atitiktis („VLOOKUP“ pagal numatytuosius nustatymus yra apytikslė)
  • XLOOKUP gali dirbti su vertikaliais ir horizontaliais duomenimis
  • „XLOOKUP“ gali atlikti atvirkštinę paiešką (paskutinė - pirma)
  • XLOOKUP gali grąžinti visas eilutes ar stulpelius, o ne tik vieną vertę
  • „XLOOKUP“ gali naudoti natūralius masyvus, kad būtų taikomi sudėtingi kriterijai

Pastabos

  1. „XLOOKUP“ gali veikti tiek su vertikaliais, tiek su horizontaliais matricomis.
  2. XLOOKUP grąžins # N / A, jei nerandama paieškos vertė.
  3. „ Lookup_array“ turi turėti dimensiją, suderinamą su argumentu „ return_array“ , kitaip XLOOKUP grąžins #VALUE!
  4. Jei XLOOKUP naudojamas tarp darbaknygių, abi darbaknygės turi būti atidarytos, kitaip XLOOKUP grąžins #REF !.
  5. Kaip ir „INDEX“ funkcija, taip ir XLOOKUP grąžina nuorodą.

Susiję vaizdo įrašai

Pagrindinis XLOOKUP pavyzdys Šiame vaizdo įraše mes nustatysime funkciją XLOOKUP su pagrindiniu pavyzdžiu. Pagal miesto pavadinimą surasime šalį ir gyventojus. Pagrindinė apytikslė „XLOOKUP“ atitiktis Šiame vaizdo įraše nustatysime funkciją „XLOOKUP“, kad atliktų apytikslę atitiktį, kad apskaičiuotų kiekiu pagrįstą nuolaidą. XLOOKUP su logine logika Šiame vaizdo įraše mes pažvelgsime, kaip naudoti XLOOKUP funkciją su Boolean logika, norint pritaikyti kelis kriterijus. „XLOOKUP“ su keliomis paieškos reikšmėmis Šiame vaizdo įraše nustatysime „XLOOKUP“, kad dinaminėje masyve būtų pateiktos kelios vertės, o vietoje vienos paieškos vertės pateikiame paieškos verčių diapazoną.

Įdomios straipsniai...