Funkcija XLOOKUP ekskluzivna je za unutarnji program ureda 365. Funkcija LOOKUP ima mnoge funkcionalnosti koje prevladavaju mnoge slabosti funkcija VLOOKUP i HLOOKUP, ali nažalost za sada nam nisu dostupne. Ali ne brinite, možemo stvoriti XLOOKUP funkciju koja radi potpuno isto kao i nadolazeća XLOOKUP funkcija MS Excel. Dodavat ćemo mu funkcionalnosti jednu po jednu.
VBA kôd funkcije XLOOKUP
Dolje prikazana UDF funkcija pretraživanja riješit će mnoge probleme. Kopirajte ga ili preuzmite datoteku ispod u nastavku.
Funkcija XLOOKUP (lk kao varijanta, lCol kao raspon, rCol kao raspon) XLOOKUP = Funkcija radnog lista.Index (rCol, Radni listFunction.Match (lk, lCol, 0)) Kraj funkcija
Obrazloženje:
Gornji kôd je samo osnovni INDEX-MATCH koji se koristi u VBA-i. To pojednostavljuje mnoge stvari s kojima se novi korisnik suočava. Ako riješi složenost funkcije INDEX-MATCH i koristi samo tri argumenta. Možete ga kopirati u Excel datoteku ili preuzeti .xlam datoteku u nastavku i instalirati je kao dodatak excelu. Ako ne znate stvoriti i koristiti dodatak, kliknite ovdje, pomoći će vam.
Dodatak XLOOKUP
da vidimo kako to radi na excel radnom listu.
Sintaksa XLOOKUP -a
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Ovo je vrijednost koju želite pretraživati u lookup_array.
lookup_array: To je jednodimenzionalni raspon u kojem želite pretraživati lookup_value.
niz_rezulata: To je također jednodimenzionalni raspon. Ovo je raspon iz kojeg želite dohvatiti vrijednost.
Pogledajmo ovu XLOOKUP funkciju na djelu.
Primjeri XLOOKUP -a:
Evo, imam tablicu podataka u excelu. Istražimo neke funkcionalnosti pomoću ove tablice podataka.
Funkcionalnost 1. Točno Pretraživanje s lijeve i desne strane vrijednosti traženja.
Kao što znamo da funkcija Excel VLOOKUP ne može dohvatiti vrijednosti s lijeve strane vrijednosti pretraživanja. Za to morate koristiti složenu kombinaciju INDEX-MATCH. Ali ne više.
Pod pretpostavkom da moramo dohvatiti sve podatke dostupne u tablici nekih brojeva rola. U tom slučaju morat ćete dohvatiti i regiju koja se nalazi s lijeve strane stupca s brojevima rola.
Napišite ovu formulu, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Dobivamo rezultat sjever za broj role 112. Kopirajte ili povucite formulu u donje ćelije kako biste ih ispunili odgovarajućim regijama.
Kako radi?
Mehanizam je jednostavan. Ova funkcija traži lookup_value u lookup_array i vraća indeks prvog točnog podudaranja. Zatim koristi taj indeks za dohvaćanje vrijednosti iz matrica rezultata. Ova funkcija savršeno funkcionira s imenovanim rasponima.
Slično koristite ovu formulu za dohvaćanje vrijednosti iz svakog stupca.
Funkcionalnost 2. Točna Vodoravno Traži iznad i ispod vrijednosti traženja.
XLOOKUP također radi kao točna funkcija HLOOKUP -a. Funkcija HLOOKUP ima isto ograničenje kao i VLOOKUP. Ne može dohvatiti vrijednost iznad vrijednosti traženja. Ali XLOOKUP ne radi samo kao HLOOKUP, već i prevladava tu slabost. Da vidimo kako.
Hipotetski, ako želite usporediti dva zapisa. Zapis pretraživanja koji već imate. Zapis s kojim želite usporediti nalazi se iznad raspona lookup_. U tom slučaju upotrijebite ovu formulu.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
povucite formulu prema dolje i imate cijeli zapis usporedbe redaka.
Funkcionalnost 3. Nema potrebe za brojem stupca i zadanim točnim podudaranjem.
Kada koristite funkciju VLOOKUP, morate reći broj stupca iz kojeg želite dohvatiti vrijednosti. Za to morate izbrojati stupce ili upotrijebiti neke trikove, iskoristiti druge funkcije. S ovim UDF XLOOKUP -om to ne trebate učiniti.
Ako koristite VLOOKUP samo za dohvaćanje neke vrijednosti iz jednog stupca ili za provjeru postoji li vrijednost u stupcu, ovo je po meni najbolje rješenje.
Funkcionalnost 4. Zamjenjuje INDEX-MATCH, VLOOKUP, HLOOKUP funkciju
Za jednostavne zadatke naša funkcija XLOOKUP zamjenjuje gore navedene funkcije.
Ograničenja XLOOKUP -a:
Kada su u pitanju složene formule, poput VLOOKUP -a s indeksom dinamičkog stupnja gdje VLOOKUP identificira stupac za pretraživanje s zaglavljima, ovaj XLOOKUP neće uspjeti.
Drugo ograničenje je da ako morate potražiti više slučajnih stupaca ili redaka iz tablice, ova funkcija bit će beskorisna jer ćete morati pisati ovu formulu iznova i iznova. To se može prevladati korištenjem imenovanih raspona.
Za sada nismo dodali približnu funkcionalnost, pa naravno, ne možete dobiti približno podudaranje. To ćemo dodati prerano.
Ako funkcija XLOOKUP ne uspije pronaći vrijednost pretraživanja, vratit će grešku #VALUE, a ne #N/A.
Pa da, dečki, ovako koristite XLOOKUP za dohvaćanje, pretraživanje i provjeru vrijednosti u Excel tablicama. Ovu korisnički definiranu funkciju možete koristiti za pretraživanje bez problema s lijeve strane ili gore od vrijednosti traženja. Ako i dalje imate bilo kakvih nedoumica ili bilo kakvih posebnih zahtjeva vezanih uz ovu funkciju ili EXCEL 2010/2013/2016/2019/365 ili upit vezan uz VBA, pitajte to u odjeljku komentara u nastavku. Odgovor ćete sigurno dobiti.
Izradite VBA funkciju za vraćanje niza | Da bismo vratili niz iz korisnički definirane funkcije, moramo ga deklarirati kada imenujemo UDF.
Nizovi u Excelu Formul | Saznajte koji su nizovi u Excelu.
Kako stvoriti korisnički definiranu funkciju putem VBA | Saznajte kako stvoriti korisnički definirane funkcije u Excelu
Korištenje korisnički definirane funkcije (UDF) iz druge radne knjige pomoću VBA u programu Microsoft Excel | Upotrijebite korisnički definiranu funkciju u drugoj radnoj knjizi programa Excel
Vraćanje vrijednosti pogrešaka iz korisnički definiranih funkcija pomoću VBA u Microsoft Excelu | Saznajte kako možete vratiti vrijednosti pogreške iz korisnički definirane funkcije
Popularni članci:
Podijelite Excel list u više datoteka na temelju stupca pomoću VBA | Ovaj VBA kôd dijeli excel tablicu na jedinstvenim vrijednostima u navedenom stupcu. Preuzmite radnu datoteku.
Isključite poruke upozorenja pomoću VBA u programu Microsoft Excel 2016 | Za isključivanje poruka upozorenja koje ometaju rad VBA koda, koristimo klasu Application.
Dodajte i spremite novu radnu knjigu pomoću VBA -a u programu Microsoft Excel 2016 | Za dodavanje i spremanje radnih knjiga koristeći VBA koristimo klasu Radne knjige. Radne knjige. Dodavanje lako dodaje novu radnu knjigu, međutim …