Funkcija XLOOKUP je izključna za notranji program pisarne 365. Funkcija LOOKUP ima številne funkcionalnosti, ki premagajo številne pomanjkljivosti funkcij VLOOKUP in HLOOKUP, vendar nam zaenkrat žal niso na voljo. Ampak ne skrbite, lahko ustvarimo funkcijo XLOOKUP, ki deluje popolnoma enako kot prihajajoča funkcija XLOOKUP MS Excel. Dodali bomo funkcionalnosti eno za drugo.
VBA koda funkcije XLOOKUP
Spodnja funkcija iskanja UDF bo rešila številne težave. Kopirajte ali prenesite spodnjo datoteko dodatka xl.
Funkcija XLOOKUP (lk kot varianta, lCol kot obseg, rCol kot obseg) XLOOKUP = Funkcija delovnega lista.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Končna funkcija
Pojasnilo:
Zgornja koda je le osnovni INDEX-MATCH, ki se uporablja v VBA. To poenostavi veliko stvari, s katerimi se sooča nov uporabnik. Če reši kompleksnost funkcije INDEX-MATCH in uporabi le tri argumente. Lahko ga kopirate v datoteko excel ali spodaj prenesete datoteko .xlam in jo namestite kot dodatek excel. Če ne veste, kako ustvariti in uporabiti dodatek, kliknite tukaj, vam bo v pomoč.
Dodatek XLOOKUP
poglejmo, kako deluje na Excelovem delovnem listu.
Sintaksa XLOOKUP
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: To je vrednost, ki jo želite iskati v lookup_array.
lookup_array: To je enodimenzionalno območje, v katerem želite iskati lookup_value.
matrika rezultatov: To je tudi enodimenzionalno območje. To je obseg, iz katerega želite pridobiti vrednost.
Poglejmo to funkcijo XLOOKUP v akciji.
Primeri XLOOKUP:
Tukaj imam podatkovno tabelo v Excelu. Raziščimo nekaj funkcij s to podatkovno tabelo.
Funkcionalnost 1. Natančno Iskanje na levi in desni strani iskalne vrednosti.
Kot vemo, da funkcija Excel VLOOKUP ne more pridobiti vrednosti z leve strani iskalne vrednosti. Za to morate uporabiti kompleksno kombinacijo INDEX-MATCH. Ampak ne več.
Ob predpostavki, da moramo pridobiti vse informacije, ki so na voljo v tabeli nekaterih številk zvitkov. V tem primeru boste morali pridobiti tudi regijo, ki je na levi strani stolpca s številko zvitka.
Napiši to formulo, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Za številko zvitka 112. dobimo rezultat sever. Kopirajte ali povlecite formulo v spodnje celice, da jih zapolnite z ustreznimi regijami.
Kako deluje?
Mehanizem je preprost. Ta funkcija išče lookup_value v lookup_array in vrne indeks prvega natančnega ujemanja. Nato ta indeks uporabi za pridobivanje vrednosti iz matrika rezultatov. Ta funkcija odlično deluje z imenovanimi območji.
Podobno uporabite to formulo za pridobivanje vrednosti iz vsakega stolpca.
Funkcionalnost 2. Natančno Vodoravno Iskanje nad in pod iskalno vrednostjo.
XLOOKUP deluje tudi kot natančna funkcija HLOOKUP. Funkcija HLOOKUP ima enako omejitev kot VLOOKUP. Ne more pridobiti vrednosti nad vrednostjo iskanja. Vendar XLOOKUP ne deluje samo kot HLOOKUP, ampak tudi premaga to slabost. Poglejmo, kako.
Hipotetično, če želite primerjati dva zapisa. Iskalni zapis, ki ga že imate. Zapis, s katerim želite primerjati, je nad lookup_range. V tem primeru uporabite to formulo.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
povlecite formulo navzdol in imate celoten zapis primerjave vrstice.
Funkcionalnost 3. Ni potrebe po številki stolpca in privzetem natančnem ujemanju.
Ko uporabljate funkcijo VLOOKUP, morate povedati številko stolpca, iz katerega želite pridobiti vrednosti. Za to morate prešteti stolpce ali uporabiti nekaj trikov, uporabiti druge funkcije. S tem UDF XLOOKUP vam tega ni treba storiti.
Če uporabljate VLOOKUP samo za pridobivanje vrednosti iz enega stolpca ali za preverjanje, ali v stolpcu obstaja vrednost, je to zame najboljša rešitev.
Funkcionalnost 4. Nadomešča funkcijo INDEX-MATCH, VLOOKUP, HLOOKUP
Za preprosta opravila naša funkcija XLOOKUP nadomesti zgoraj omenjene funkcije.
Omejitve XLOOKUP -a:
Ko gre za kompleksne formule, kot je VLOOKUP z indeksom dinamičnega stolpca, kjer VLOOKUP identificira iskalni stolpec z glavami, ta XLOOKUP ne bo uspel.
Druga omejitev je, da če morate iz tabele poiskati več naključnih stolpcev ali vrstic, bo ta funkcija neuporabna, saj boste morali to formulo vedno znova pisati. To je mogoče premagati z uporabo imenovanih obsegov.
Zaenkrat še nismo dodali približne funkcionalnosti, zato seveda ne morete dobiti približnega ujemanja. To bomo dodali prehitro.
Če funkciji XLOOKUP ne uspe najti iskalne vrednosti, bo vrnila napako #VALUE in ne #N/A.
Torej, fantje, tako uporabljate XLOOKUP za pridobivanje, iskanje in preverjanje vrednosti v tabelah Excel. To uporabniško določeno funkcijo lahko uporabite za iskanje brez težav na levi ali navzgor od iskalne vrednosti. Če imate še vedno kakršne koli dvome ali posebne zahteve v zvezi s to funkcijo ali poizvedbo EXCEL 2010/2013/2016/2019/365 ali VBA, jo vprašajte v spodnjem razdelku za komentarje. Zagotovo boste dobili odgovor.
Ustvari funkcijo VBA za vrnitev matrike | Če želimo vrniti matriko iz uporabniško določene funkcije, jo moramo razglasiti, ko poimenujemo UDF.
Nizovi v Excelu Formul | Preberite, kaj so matrike v Excelu.
Kako ustvariti uporabniško določeno funkcijo prek VBA | Naučite se ustvarjati uporabniško določene funkcije v Excelu
Uporaba uporabniško določene funkcije (UDF) iz drugega delovnega zvezka z uporabo VBA v programu Microsoft Excel | Uporabite uporabniško določeno funkcijo v drugem delovnem zvezku programa Excel
Vrnite vrednosti napak iz uporabniško določenih funkcij z uporabo VBA v Microsoft Excelu | Preberite, kako lahko vrnete vrednosti napak iz uporabniško določene funkcije
Priljubljeni članki:
Razdelite list Excel v več datotek na podlagi stolpca z uporabo VBA | Ta koda VBA za razdelitev kode excel na edinstvene vrednosti v določenem stolpcu. Prenesite delovno datoteko.
Izklopite opozorilna sporočila z uporabo VBA v programu Microsoft Excel 2016 | Za izklop opozorilnih sporočil, ki prekinejo delovanje kode VBA, uporabljamo razred Application.
Dodajte in shranite nov delovni zvezek z uporabo VBA v programu Microsoft Excel 2016 | Za dodajanje in shranjevanje delovnih zvezkov z uporabo VBA uporabljamo razred Delovni zvezki. Delovni zvezki.Dodajanje zlahka doda nov delovni zvezek …