Funkcija „XLOOKUP“ yra skirta tik „Office 365“ viešai neatskleistajai programai. Funkcija LOOKUP turi daug funkcijų, kurios įveikia daugelį VLOOKUP ir HLOOKUP funkcijų trūkumų, tačiau, deja, kol kas ji mums nepasiekiama. Tačiau nesijaudinkite, galime sukurti funkciją „XLOOKUP“, kuri veikia lygiai taip pat, kaip ir būsimoji „XLOOKUP“ funkcija „MS Excel“. Mes pridėsime prie jo funkcijas po vieną.
Funkcijos XLOOKUP VBA kodas
Žemiau pateikta UDF paieškos funkcija išspręs daugybę problemų. Nukopijuokite jį arba atsisiųskite xl priedą žemiau esančiame faile.
Funkcija XLOOKUP (lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Pabaigos funkcija
Paaiškinimas:
Aukščiau pateiktas kodas yra tik pagrindinis INDEX-MATCH, naudojamas VBA. Tai supaprastina daugelį dalykų, su kuriais susiduria naujas vartotojas. Jei išsprendžiama INDEX-MATCH funkcijos sudėtingumas ir naudojami tik trys argumentai. Galite nukopijuoti jį į „Excel“ failą arba atsisiųsti žemiau esantį .xlam failą ir įdiegti kaip „Excel“ priedą. Jei nežinote, kaip sukurti ir naudoti priedą, spustelėkite čia, tai jums padės.
„XLOOKUP“ priedas
pažiūrėkime, kaip tai veikia „Excel“ darbalapyje.
XLOOKUP sintaksė
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Tai vertė, kurios norite ieškoti lookup_array.
lookup_array: Tai yra vienmatis diapazonas, kuriame norite ieškoti lookup_value.
result_array: Tai taip pat yra vienpusis diapazonas. Tai yra diapazonas, iš kurio norite gauti vertę.
Pažiūrėkime, kaip ši XLOOKUP funkcija veikia.
XLOOKUP pavyzdžiai:
Čia turiu „Excel“ duomenų lentelę. Panagrinėkime kai kurias funkcijas naudodami šią duomenų lentelę.
Funkcionalumas 1. Tiksliai Ieškokite kairėje ir dešinėje paieškos vertės pusėse.
Kaip žinome, „Excel VLOOKUP“ funkcija negali nuskaityti reikšmių iš paieškos vertės kairės pusės. Norėdami tai padaryti, turite naudoti sudėtingą INDEX-MATCH derinį. Bet ne daugiau.
Darant prielaidą, kad turime gauti visą kai kurių ritinių skaičių lentelėje esančią informaciją. Tokiu atveju taip pat turėsite gauti regioną, kuris yra ritinio numerio stulpelio kairėje.
Parašykite šią formulę, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Gauname rezultatą į šiaurę ritinio numeriui 112. Nukopijuokite arba nuvilkite formulę žemiau esančiuose langeliuose, kad užpildytumėte atitinkamus regionus.
Kaip tai veikia?
Mechanizmas yra paprastas. Ši funkcija ieško lookup_value į lookup_array ir grąžina pirmosios tikslios atitikties indeksą. Tada naudoja tą indeksą, kad gautų vertę iš rezultatas_masyvas. Ši funkcija puikiai veikia su pavadintais diapazonais.
Panašiai naudokite šią formulę, kad gautumėte vertę iš kiekvieno stulpelio.
Funkcionalumas 2. Tiksliai Horizontalus Ieškokite aukščiau ir žemiau paieškos vertės.
XLOOKUP taip pat veikia kaip tiksli HLOOKUP funkcija. Funkcija HLOOKUP turi tą patį apribojimą kaip ir VLOOKUP. Jis negali gauti vertės iš viršaus paieškos vertės. Tačiau XLOOKUP veikia ne tik kaip „HLOOKUP“, bet ir įveikia tą silpnumą. Pažiūrėkime, kaip.
Hipotetiškai, jei norite palyginti du įrašus. Peržiūros įrašas, kurį jau turite. Įrašas, su kuriuo norite palyginti, yra virš lookup_range. Tokiu atveju naudokite šią formulę.
| = XLOOKUP (H7, 9 USD: 9 USD, 2 USD: 2 USD) |
vilkite žemyn formulę ir turėsite visą palyginimo eilutės įrašą.
Funkcionalumas 3. Nereikia stulpelio numerio ir numatytosios tikslios atitikties.
Kai naudojate funkciją VLOOKUP, turite pasakyti stulpelio numerį, iš kurio norite gauti reikšmes. Norėdami tai padaryti, turite suskaičiuoti stulpelius arba pasinaudoti gudrybėmis, pasinaudoti kitų funkcijų pagalba. Naudodami šį UDF XLOOKUP jums to daryti nereikia.
Jei naudojate „VLOOKUP“ tam, kad tik gautumėte tam tikrą vertę iš vieno stulpelio arba patikrintumėte, ar stulpelyje yra vertė, tai yra geriausias mano sprendimas.
Funkcionalumas 4. Pakeičia INDEX-MATCH, VLOOKUP, HLOOKUP funkciją
Paprastoms užduotims atlikti mūsų funkcija XLOOKUP pakeičia aukščiau paminėtas funkcijas.
XLOOKUP apribojimai:
Kalbant apie sudėtingas formules, tokias kaip VLOOKUP su dinaminiu kolonų indeksu, kur mes VLOOKUP identifikuojame paieškos stulpelį su antraštėmis, šis XLOOKUP nepavyks.
Kitas apribojimas yra tas, kad jei iš lentelės turite ieškoti kelių atsitiktinių stulpelių ar eilučių, ši funkcija bus nenaudinga, nes šią formulę turite rašyti vėl ir vėl. Tai galima įveikti naudojant pavadintus diapazonus.
Kol kas mes nepridėjome apytikslių funkcijų, todėl, žinoma, negalite gauti apytikslės atitikties. Mes pridėsime tai per anksti.
Jei funkcijai XLOOKUP nepavyksta rasti paieškos vertės, ji grąžins #VALUE klaidą, o ne #N/A.
Taigi taip, vaikinai, taip naudojate XLOOKUP, kad gautumėte, ieškotumėte ir patvirtintumėte vertes „Excel“ lentelėse. Šią vartotojo apibrėžtą funkciją galite naudoti norėdami be rūpesčių ieškoti kairėje arba aukštyn paieškos vertės. Jei vis dar turite abejonių ar kokių nors konkrečių reikalavimų, susijusių su šia funkcija arba su EXCEL 2010/2013/2016/2019/365 ar VBA susijusia užklausa, paklauskite to komentarų skiltyje žemiau. Tikrai sulauksite atsakymo.
Sukurkite VBA funkciją grąžinti masyvą | Norėdami grąžinti masyvą iš vartotojo apibrėžtos funkcijos, turime jį deklaruoti, kai įvardijame UDF.
Masyvai „Excel Formul“ | Sužinokite, kokie masyvai yra „Excel“.
Kaip sukurti vartotojo apibrėžtą funkciją per VBA | Sužinokite, kaip „Excel“ sukurti vartotojo apibrėžtas funkcijas
Naudotojo apibrėžtos funkcijos (UDF) naudojimas iš kitos darbaknygės naudojant VBA programoje „Microsoft Excel“ | Naudokite vartotojo apibrėžtą funkciją kitoje „Excel“ darbaknygėje
Pateikite klaidų reikšmes iš vartotojo apibrėžtų funkcijų naudodami VBA programoje „Microsoft Excel“ | Sužinokite, kaip galite grąžinti klaidų vertes iš vartotojo nustatytos funkcijos
Populiarūs straipsniai:
Padalinkite „Excel“ lapą į kelis failus pagal stulpelį naudodami VBA | Šis VBA kodas padalija „Excel“ lapą pagal unikalias reikšmes nurodytame stulpelyje. Atsisiųskite darbo failą.
Išjunkite įspėjamuosius pranešimus naudodami VBA programoje „Microsoft Excel 2016“ | Norėdami išjungti įspėjamuosius pranešimus, kurie nutraukia veikiantį VBA kodą, naudojame taikymo klasę.
Pridėkite ir išsaugokite naują darbaknygę naudodami „Microsoft Excel 2016“ VBA | Norėdami pridėti ir išsaugoti darbaknyges naudodami VBA, naudojame darbo knygų klasę. Workbooks.Add lengvai prideda naują darbaknygę, tačiau…