Funkcija XLOOKUP ir ekskluzīva biroja 365. iekšējās informācijas programmai. Funkcijai LOOKUP ir daudzas funkcijas, kas pārvar daudzas VLOOKUP un HLOOKUP funkcijas vājās vietas, bet diemžēl tā mums pagaidām nav pieejama. Bet neuztraucieties, mēs varam izveidot funkciju XLOOKUP, kas darbojas tieši tāpat kā gaidāmā XLOOKUP funkcija MS Excel. Mēs pievienosim tam funkcionalitāti pa vienam.
Funkcijas XLOOKUP VBA kods
Zemāk redzamā UDF uzmeklēšanas funkcija atrisinās daudzas problēmas. Nokopējiet to vai lejupielādējiet zemāk esošo xl pievienojumprogrammu.
Funkcija XLOOKUP (lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Beigu funkcija
Paskaidrojums:
Iepriekš minētais kods ir tikai pamata INDEX-MATCH, ko izmanto VBA. Tas vienkāršo daudzas lietas, ar kurām saskaras jauns lietotājs. Ja atrisina funkcijas INDEX-MATCH sarežģītību un izmanto tikai trīs argumentus. Varat to kopēt savā Excel failā vai lejupielādēt zemāk esošo .xlam failu un instalēt to kā Excel pievienojumprogrammu. Ja jūs nezināt, kā izveidot un izmantot pievienojumprogrammu, noklikšķiniet šeit, tas jums palīdzēs.
XLOOKUP pievienojumprogramma
redzēsim, kā tas darbojas Excel darblapā.
XLOOKUP sintakse
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Šī ir vērtība, kuru vēlaties meklēt lookup_array.
lookup_array: Tas ir viendimensiju diapazons, kurā vēlaties veikt meklēšanu lookup_value.
result_array: Tas ir arī viendimensiju diapazons. Šis ir diapazons, no kura vēlaties izgūt vērtību.
Apskatīsim šo funkciju XLOOKUP darbībā.
XLOOKUP piemēri:
Šeit man ir Excel tabula. Izpētīsim dažas funkcijas, izmantojot šo datu tabulu.
Funkcionalitāte 1. Precīzi Meklēt uzmeklēšanas vērtības kreisajā un labajā pusē.
Kā mēs zinām, Excel funkcija VLOOKUP nevar izgūt vērtības no meklēšanas vērtības kreisās puses. Lai to izdarītu, jums jāizmanto sarežģītā INDEX-MATCH kombinācija. Bet vairs ne.
Pieņemot, ka mums ir jāiegūst visa informācija, kas pieejama dažu ruļļu skaitļu tabulā. Tādā gadījumā jums būs jāiegūst arī reģions, kas atrodas ruļļa numura slejas kreisajā pusē.
Uzrakstiet šo formulu, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Mēs iegūstam rezultātu uz ziemeļiem ruļļa numuram 112. Nokopējiet vai velciet uz leju formulu zemāk esošajās šūnās, lai aizpildītu tās ar attiecīgajiem reģioniem.
Kā tas darbojas?
Mehānisms ir vienkāršs. Šī funkcija meklē lookup_value iekšā lookup_array un atgriež pirmās precīzās atbilstības indeksu. Pēc tam izmanto šo indeksu, lai izgūtu vērtību no rezultāts_masīvs. Šī funkcija lieliski darbojas ar nosauktajiem diapazoniem.
Līdzīgi izmantojiet šo formulu, lai izgūtu vērtību no katras kolonnas.
Funkcionalitāte 2. Tieši tā Horizontāli Uzmeklēt virs un zem uzmeklēšanas vērtības.
XLOOKUP darbojas arī kā precīza HLOOKUP funkcija. Funkcijai HLOOKUP ir tāds pats ierobežojums kā VLOOKUP. Tas nevar iegūt vērtību no augšējās uzmeklēšanas vērtības. Bet XLOOKUP ne tikai darbojas kā HLOOKUP, bet arī pārvar šo vājumu. Redzēsim, kā.
Hipotētiski, ja vēlaties salīdzināt divus ierakstus. Uzmeklēšanas ieraksts, kas jums jau ir. Ieraksts, ar kuru vēlaties salīdzināt, atrodas virs lookup_range. Tādā gadījumā izmantojiet šo formulu.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
velciet uz leju formulu, un jums ir viss salīdzināšanas rindas ieraksts.
Funkcionalitāte 3. Nav nepieciešams slejas numurs un noklusējuma precīzā atbilstība.
Izmantojot funkciju VLOOKUP, jums jāpasaka kolonnas numurs, no kura vēlaties iegūt vērtības. Lai to izdarītu, jums ir jāskaita kolonnas vai jāizmanto daži triki, jāizmanto citu funkciju palīdzība. Izmantojot šo UDF XLOOKUP, jums tas nav jādara.
Ja jūs izmantojat VLOOKUP, lai tikai iegūtu kādu vērtību no vienas kolonnas vai pārbaudītu, vai kolonnā ir vērtība, tas ir labākais risinājums, kā es uzskatu.
Funkcionalitāte 4. Aizstāj funkciju INDEX-MATCH, VLOOKUP, HLOOKUP
Vienkāršiem uzdevumiem mūsu funkcija XLOOKUP aizstāj iepriekš minētās funkcijas.
XLOOKUP ierobežojumi:
Runājot par sarežģītām formulām, piemēram, VLOOKUP ar Dynamic Col Index, kur mēs VLOOKUP identificējam uzmeklēšanas kolonnu ar galvenēm, šī XLOOKUP neizdosies.
Vēl viens ierobežojums ir tāds, ka, ja no tabulas ir jāmeklē vairākas nejaušas kolonnas vai rindas, šī funkcija būs bezjēdzīga, jo šī formula ir jāraksta atkal un atkal. To var pārvarēt, izmantojot nosauktos diapazonus.
Pagaidām mēs neesam pievienojuši aptuveno funkcionalitāti, tāpēc, protams, jūs nevarat iegūt aptuveno atbilstību. Mēs to piebildīsim pārāk drīz.
Ja funkcijai XLOOKUP neizdodas atrast uzmeklēšanas vērtību, tā atgriezīs #VALUE kļūdu, nevis #N/A.
Jā, puiši, šādi jūs izmantojat XLOOKUP, lai izgūtu, meklētu un apstiprinātu vērtības Excel tabulās. Šo lietotāja definēto funkciju varat izmantot, lai bez problēmām meklētu uzmeklēšanas vērtību pa kreisi vai uz augšu. Ja jums joprojām ir šaubas vai kādas īpašas prasības saistībā ar šo funkciju vai EXCEL 2010/2013/2016/2019/365 vai ar VBA saistīto vaicājumu, jautājiet to komentāru sadaļā zemāk. Jūs noteikti saņemsit atbildi.
Izveidojiet VBA funkciju masīva atgriešanai | Lai atgrieztu masīvu no lietotāja definētas funkcijas, mums tas ir jādeklarē, nosaucot UDF.
Masīvi programmā Excel Formul | Uzziniet, kādi masīvi ir pieejami programmā Excel.
Kā izveidot lietotāja definētu funkciju, izmantojot VBA | Uzziniet, kā programmā Excel izveidot lietotāja definētas funkcijas
Lietotāja definētas funkcijas (UDF) izmantošana no citas darbgrāmatas, izmantojot VBA programmā Microsoft Excel | Izmantojiet lietotāja definētu funkciju citā Excel darbgrāmatā
Atgriež kļūdas vērtības no lietotāja definētām funkcijām, izmantojot Microsoft Excel VBA Uzziniet, kā atgriezt kļūdu vērtības no lietotāja definētas funkcijas
Populāri raksti:
Sadaliet Excel lapu vairākos failos, pamatojoties uz kolonnu, izmantojot VBA | Šī VBA koda sadalījuma Excel lapas pamatā ir unikālas vērtības noteiktā slejā. Lejupielādējiet darba failu.
Izslēdziet brīdinājuma ziņojumus, izmantojot VBA programmā Microsoft Excel 2016 | Lai izslēgtu brīdinājuma ziņojumus, kas pārtrauc palaisto VBA kodu, mēs izmantojam lietojumprogrammu klasi.
Pievienojiet un saglabājiet jaunu darbgrāmatu, izmantojot VBA programmā Microsoft Excel 2016 | Lai pievienotu un saglabātu darbgrāmatas, izmantojot VBA, mēs izmantojam darbgrāmatu klasi. Workbooks.Add viegli pievieno jaunu darbgrāmatu, tomēr…