Funktsioon XLOOKUP on eksklusiivne kontori 365 siseringiprogrammile. Funktsioonil LOOKUP on palju funktsioone, mis ületavad paljusid funktsiooni VLOOKUP ja HLOOKUP nõrkusi, kuid kahjuks pole see meile praegu saadaval. Kuid ärge muretsege, saame luua funktsiooni XLOOKUP, mis töötab täpselt samamoodi nagu eelseisev funktsioon XLOOKUP MS Excel. Lisame sellele ükshaaval funktsioone.
Funktsiooni XLOOKUP VBA kood
Allpool olev UDF -i otsingufunktsioon lahendab paljusid probleeme. Kopeerige see või laadige alla allpool olev xl-lisandmoodul.
Funktsioon XLOOKUP (lk Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Lõppfunktsioon
Selgitus:
Ülaltoodud kood on lihtsalt VBA-s kasutatav põhiline INDEX-MATCH. See lihtsustab paljusid asju, millega uus kasutaja silmitsi seisab. Kui lahendab funktsiooni INDEX-MATCH keerukuse ja kasutab ainult kolme argumenti. Saate selle oma Exceli faili kopeerida või alla laadida .xlam-faili ja installida selle Exceli lisandmoodulina. Kui te ei tea, kuidas lisandmoodulit luua ja kasutada, klõpsake siin, see aitab teid.
XLOOKUP-i lisandmoodul
vaatame, kuidas see Exceli töölehel töötab.
XLOOKUPi süntaks
| = XLOOKUP (otsinguväärtus, otsimismassiiv, tulemuste_massiiv) |
lookup_value: See on väärtus, mida soovite otsida lookup_array.
lookup_array: See on ühemõõtmeline vahemik, milles soovite otsida lookup_value.
result_array: See on ka ühemõõtmeline vahemik. See on vahemik, millest soovite väärtuse hankida.
Vaatame seda funktsiooni XLOOKUP töös.
XLOOKUPi näited:
Siin on mul Exceli andmetabel. Uurime selle andmetabeli abil mõningaid funktsioone.
Funktsionaalsus 1. Täpne Otsige otsinguväärtuse vasakult ja paremalt küljelt.
Nagu me teame, ei saa funktsiooni Excel VLOOKUP otsingu väärtusest vasakult väärtusi alla laadida. Selleks peate kasutama keerulist INDEX-MATCH kombinatsiooni. Aga enam mitte.
Eeldades, et peame leidma kogu teabe, mis on saadaval mõne rullinumbri tabelis. Sellisel juhul peate hankima ka piirkonna, mis asub veerunumbri veerus vasakul.
Kirjutage see valem, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Rulli numbri 112 puhul saame tulemuse Põhja. Kopeerige või lohistage allolevates lahtrites olev valem, et täita need vastavate piirkondadega.
Kuidas see töötab?
Mehhanism on lihtne. See funktsioon otsib üles lookup_value sisse lookup_array ja tagastab esimese täpse vaste indeksi. Seejärel kasutab seda indeksit väärtuse toomiseks tulemus_massiiv. See funktsioon töötab ideaalselt nimega vahemikega.
Sarnaselt kasutage seda valemit igast veerust väärtuse hankimiseks.
Funktsionaalsus 2. Täpne Horisontaalne Otsige otsinguväärtuse kohal ja all.
XLOOKUP töötab ka täpse HLOOKUP funktsioonina. Funktsioonil HLOOKUP on samad piirangud, mis VLOOKUPil. See ei saa väärtust otsimisväärtusest kõrgemale tuua. Kuid XLOOKUP mitte ainult ei tööta HLOOKUPina, vaid ka ületab selle nõrkuse. Vaatame, kuidas.
Hüpoteetiliselt, kui soovite võrrelda kahte kirjet. Otsingurekord, mis teil juba on. Kirje, millega soovite võrrelda, on otsinguvahemiku kohal. Sel juhul kasutage seda valemit.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
lohistage valem alla ja teil on kogu võrdlusrida.
Funktsionaalsus 3. Pole vaja veeru numbrit ja vaikimisi täpset vastet.
Funktsiooni VLOOKUP kasutamisel peate ütlema veeru numbri, millest soovite väärtused hankida. Selleks peate veerge kokku lugema või kasutama mõningaid nippe, kasutama teisi funktsioone. Selle UDF XLOOKUP -iga ei pea te seda tegema.
Kui kasutate funktsiooni VLOOKUP lihtsalt ühest veerust mõne väärtuse toomiseks või veeru väärtuse kontrollimiseks, on see minu arvates parim lahendus.
Funktsionaalsus 4. Asendab funktsiooni INDEX-MATCH, VLOOKUP, HLOOKUP
Lihtsate ülesannete korral asendab meie funktsioon XLOOKUP eespool nimetatud funktsioonid.
XLOOKUPi piirangud:
Mis puutub keerukatesse valemitesse, nagu VLOOKUP koos dünaamilise kolviindeksiga, kus me VLOOKUP tuvastab otsinguveeru päistega, siis see XLOOKUP ebaõnnestub.
Teine piirang on see, et kui peate tabelist otsima mitu juhuslikku veergu või rida, on see funktsioon kasutu, kuna peate selle valemi uuesti ja uuesti kirjutama. Seda saab ületada, kasutades nimega vahemikke.
Praegu pole me lisanud ligikaudset funktsionaalsust, nii et loomulikult ei saa te ligikaudset vastet. Lisame selle liiga kiiresti.
Kui funktsioon XLOOKUP ei leia otsinguväärtust, tagastab see vea #VALUE, mitte numbrit #N/A.
Nii et jah, poisid, nii kasutate XLOOKUP -i Exceli tabelite väärtuste hankimiseks, otsimiseks ja kinnitamiseks. Seda kasutaja määratud funktsiooni saate kasutada probleemivabaks otsimiseks otsinguväärtuse vasakult või ülespoole. Kui teil on selle funktsiooni või EXCEL 2010/2013/2016/2019/365 või VBA -ga seotud päringuga seoses endiselt kahtlusi või konkreetseid nõudeid, küsige seda allpool kommentaaride osas. Kindlasti saate vastuse.
Looge massiivi tagastamiseks VBA funktsioon | Massiivi tagastamiseks kasutaja määratud funktsioonist peame selle UDF -i nimetamisel deklareerima.
Massiivid Excel Formulis | Lugege, millised massiivid on Excelis.
Kuidas luua kasutaja määratud funktsiooni VBA kaudu | Siit saate teada, kuidas Excelis kasutaja määratud funktsioone luua
Kasutaja määratud funktsiooni (UDF) kasutamine teisest töövihikust, kasutades Microsoft Excelis VBA -d | Kasutaja määratud funktsiooni saate kasutada mõnes teises Exceli töövihikus
Tagastab vigade väärtused kasutaja määratud funktsioonidest, kasutades Microsoft Excelis VBA-d | Siit saate teada, kuidas saate kasutaja määratud funktsiooni veaväärtusi tagastada
Populaarsed artiklid:
Jagage Exceli leht mitmeks failiks VBA abil | See VBA -kood jagab Exceli lehe baasi unikaalsete väärtuste jaoks määratud veerus. Laadige tööfail alla.
Hoiatusteadete väljalülitamine Microsoft Excel 2016 VBA abil | Jooksva VBA koodi katkestavate hoiatusteadete väljalülitamiseks kasutame rakendusklassi.
Uue töövihiku lisamine ja salvestamine Microsoft Excel 2016 VBA abil | Töövihikute lisamiseks ja salvestamiseks VBA abil kasutame klassi Töövihikud. Töövihikud.Add lisab uue töövihiku lihtsalt, kuid…