XLOOKUP (funkcija)

Koristite funkciju Xlookup kada morate pronaći stavke u tablici ili rasponu prema retku. Primjerice, potražite cijenu automobilskog dijela po broju dijela ili Pronađite naziv zaposlenika na temelju ID-a zaposlenika. Uz XLOOKUP možete pogledati u jedan stupac za pojam za pretraživanje i vratiti rezultat iz istog retka u drugom stupcu, bez obzira na koju je stranu postavljen stupac za vraćanje.

Vaš preglednik ne podržava videozapise. Instalirajte Microsoft Silverlight, Adobe Flash Player ili Internet Explorer 9.

Napomena: Studeni 25, 2019: XLOOKUP trenutno je beta značajka, a dostupna je samo za dio sustava Office Insider u ovom trenutku. Nastavit ćemo ga optimizirati u idućih nekoliko mjeseci. Kada je XLOOKUP spreman, objavit ćemo ga svim sudionicima programa Office Insider i pretplatnicima sustava office 365.

Funkcija XLOOKUP pretražuje raspon ili polje i vraća stavku koja odgovara prvom podudaranju koje pronađe. Ako podudarnost ne postoji, XLOOKUP može vratiti najbližu (približnu) podudaranje. 

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Argument

Opis

vrijednost_pretraživanja

Obavezno

Vrijednost za pretraživanje

polje_pretraživanja

Obavezno

Polje ili raspon za pretraživanje

return_array

Obavezno

Polje ili raspon koji će se vratiti

[if_not_found]

Neobavezno

Ako se ne pronađe valjana podudarnost, vratite tekst [if_not_found] koji ste dobavili.

Ako se ne pronađe valjana podudarnost, a [if_not_found] nema, #N/A bit će vraćena.

[match_mode]

Neobavezno

Navedite vrstu podudaranja:

0 – točno podudaranje. Ako ništa nije pronađeno, vratite #N/A. To je zadana mogućnost.

-1 – točno podudaranje. Ako ništa nije pronađeno, vratite sljedeću manju stavku.

1 – točno podudaranje. Ako ništa nije pronađeno, vratite sljedeću veću stavku.

2 – zamjenski znak podudaranja gdje *,? i ~ imaju posebno značenje.

[search_mode]

Neobavezno

Odredite način pretraživanja koji će se koristiti:

1 – izvedite pretraživanje Počevši od prve stavke. To je zadana mogućnost.

-1 – izvršite obrnuto pretraživanje Počevši od zadnje stavke.

2 – izvršite binarni pretraživanje koji se oslanja na lookup_array sortiran uzlaznim redoslijedom. Ako nije sortirano, prikazat će se nevaljani rezultati.

-2 – izvršite binarni pretraživanje koji se oslanja na lookup_array sortiran silaznim redoslijedom. Ako nije sortirano, prikazat će se nevaljani rezultati.

Primjeri

Primjer 1

Ovaj je primjer iz iznad videozapisa i koristi jednostavnu XLOOKUP da bi vam se prikazala naziv zemlje, a zatim vratite njegov pozivni broj države. Sadrži samo lookup_value (ćelija F2), lookup_array (Range B2: B11) i return_array (Range D2: D11) argumente. To ne uključuje argument match_mode, kao što je zadano za XLOOKUP na točno podudaranje.

Primjer funkcije XLOOKUP koja se koristi za vraćanje naziva zaposlenika i odjela na temelju ID-a zaposlenika. Formula je = XLOOKUP (B2, B5: B14, C5: C14).

Napomena: XLOOKUP se razlikuje od funkcije VLOOKUP u tome da koristi zasebne polja za pretraživanje i povrat, gdje VLOOKUP koristi jedno polje tablice, a zatim broj indeksa stupca. Ekvivalentna formula VLOOKUP u ovom slučaju bila bi: = VLOOKUP (F2, B2: D11; 3; FALSE)

Primjer 2

U ovom primjeru provjeravamo informacije o zaposlenicima na temelju ID-a zaposlenika. Za razliku od VLOOKUP-a, XLOOKUP može vratiti polje s više stavki, što omogućuje jednoj formuli da vrati naziv zaposlenika i odjel iz ćelija C5: D14.

Primjer funkcije XLOOKUP koja se koristi za vraćanje naziva zaposlenika i odjela na temelju IDt-a zaposlenika. Formula je: = XLOOKUP (B2, B5: B14, C5: D14, 0, 1)

Primjer 3

U ovom se primjeru zbraja argument if_not_found u gornjem primjeru.

Primjer funkcije XLOOKUP koja se koristi za vraćanje naziva zaposlenika i odjela na temelju ID-a zaposlenika s argumentom if_not_found. Formula je = XLOOKUP (B2, B5: B14, C5: D14, 0, 1, "zaposlenik nije pronađen")

Primjer 4

Sljedeći primjer izgleda u stupcu C za osobne prihode unesene u ćeliju E2 i pronalazi odgovarajuću poreznu stopu u stupcu B. Postavlja argument if-not_found da bi vratio 0 ako se ništa ne pronađe. Argument match_mode postavljen je na 1, što znači da će funkcija tražiti točno podudaranje, a ako ne može pronaći neku, vratit će sljedeću veću stavku. Na kraju, argument search_mode postavljen je na 1, što znači da će funkcija pretraživati od prve stavke do posljednjeg.

Slika funkcije XLOOKUP koja se koristi za povrat porezne stope na temelju maksimalnog dohotka. Ovo je približno podudaranje. Formula je: = XLOOKUP (E2; C2: C7; B2: B7; 1; 1)

Napomena: Za razliku od značajke VLOOKUP, stupac lookup_array je desno od stupca return_array, gdje se VLOOKUP može pogledati samo slijeva nadesno.

Primjer 5

Zatim ćemo koristiti ugniježđenu funkciju XLOOKUP da bismo izvršili okomiti i vodoravni podudaranje. U tom će slučaju prvi put tražiti bruto profit u stupcu B, a zatim potražite Kv1 u gornjem retku tablice (raspon C5: F5) i vratite vrijednost na sjecištu dva. To je slično korištenju funkcija index i Match u kombinaciji. Možete koristiti i XLOOKUP da biste zamijenili funkciju HLOOKUP .

Slika funkcije XLOOKUP koja se koristi za vraćanje vodoravnih podataka iz tablice gniježđivanjem 2 XLOOKUPs. Formula je: = XLOOKUP (D2, $B 6: $B sedamnaest, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17))

Formula u ćelijama D3: F3 je: = XLOOKUP (D2, $B šest: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17)).

Primjer 6

U ovom se primjeru koristi funkcija SUMi dvije funkcije xlookup ugniježđene zajedno za zbrajanje svih vrijednosti između dvaju raspona. U ovom slučaju želimo zbrajati vrijednosti za grožđe, banane i uvrštavanje krušaka koje se nalaze između njih dvojice.

Korištenje značajke XLOOKUP sa ZBRAJANJA za ukupan raspon vrijednosti koje padaju između dvaju odabira

Formula u ćeliji E3 je: = SUM (XLOOKUP (B3; B6:,, E6: E10): XLOOKUP (C3, B6:,, E6: E10))

Način funkcioniranja XLOOKUP vraća raspon, pa kada izračunava, formula će završiti tako da izgleda ovako: = SUM ($E $7: $E $9). Možete vidjeti kako to funkcionira samostalno tako da odaberete ćeliju s formulom XLOOKUP koja je slična ovoj, a zatim prijeđite na formule > Nadzor formule > Vrednovanje formule, a zatim pritisnite gumb Vrednovanje da biste prelazili kroz izračun.

Napomena: Zahvaljujući programu Microsoft Excel MVP, Bill jelen, za predložu ovaj primjer.

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Dodatne informacije

XMATCH (Opis funkcije)

Funkcije programa Excel (abecednim redoslijedom)

Funkcije programa Excel (po kategorijama)

Proširite svoje vještine korištenja sustava Office
Istražite osposobljavanje

Jesu li vam ove informacije bile korisne?

Hvala vam na povratnim informacijama!

Hvala vam na povratnim informacijama! Čini se da bi vam pomoglo kad bismo vas povezali s nekim od naših agenata podrške za Office.

×