Excelova nova funkcija XLOOKUP predstavlja značajan korak napred u odnosu na VLOOKUP, nudeći superiornu zamenu za jednu od najčešće korišćenih funkcija u Excelu. Ova unapređena funkcija adresira mnoga ograničenja VLOOKUP-a i uvodi novu funkcionalnost. Sledi detaljan pregled onoga što treba znati o XLOOKUP-u.
Šta je XLOOKUP?
XLOOKUP je dizajniran da prevaziđe nedostatke VLOOKUP-a, istovremeno zamenjujući i HLOOKUP. Za razliku od VLOOKUP-a, XLOOKUP omogućava pretraživanje ulevo, podrazumevano koristi tačno podudaranje, i koristi raspon ćelija umesto numerisanja kolona. Ova svojstva čine ga lakšim za upotrebu i mnogo svestranijim od VLOOKUP-a. Detaljnije ćemo opisati kako ove funkcije rade.
Trenutno, XLOOKUP je dostupan samo korisnicima programa Insider. Svi zainteresovani se mogu pridružiti Insider programu kako bi dobili pristup najnovijim Excel funkcijama čim budu objavljene. Microsoft planira da ga uskoro učini dostupnim svim korisnicima Office 365.
Kako koristiti funkciju XLOOKUP
Da bismo bolje razumeli kako XLOOKUP funkcioniše, pogledajmo konkretan primer. Pretpostavimo da imamo podatke prikazane na slici ispod, gde želimo da pronađemo odeljenje iz kolone F za svaki ID koji se nalazi u koloni A.
Ovo je tipičan primer koji zahteva pronalaženje tačnog podudaranja. Funkcija XLOOKUP zahteva samo tri osnovna ulazna podatka.
Iako XLOOKUP funkcija može imati do šest argumenata, za precizno podudaranje su nam neophodna samo prva tri. Hajde da se fokusiramo na njih:
lookup_value: Vrednost koju tražimo.
lookup_array: Raspon ćelija u kojem se vrši pretraga.
return_array: Raspon ćelija koji sadrži vrednost koju želimo vratiti.
Za navedeni primer, sledeća formula će dati željeni rezultat: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Sada ćemo analizirati prednosti koje XLOOKUP ima u odnosu na VLOOKUP.
Nema više potrebe za brojem indeksa kolone
Kod VLOOKUP-a, treći argument je bio broj kolone iz tabele iz koje se vraćaju informacije. Sa XLOOKUP-om, ovaj problem je rešen jer umesto broja kolone, birate opseg iz kojeg želite da vratite podatke (u ovom slučaju, kolona F).
Važno je napomenuti da XLOOKUP može da pretražuje podatke koji se nalaze levo od izabrane ćelije, što VLOOKUP ne može. O ovome ćemo detaljnije govoriti kasnije.
Takođe, više nećete imati problem sa pokvarenim formulama kada se ubacuju nove kolone. U takvim situacijama, raspon vraćanja će se automatski prilagoditi.
Tačno podudaranje je podrazumevano
Prilikom učenja VLOOKUP-a, mnogima je bilo nejasno zašto je potrebno eksplicitno navoditi da se traži tačno podudaranje.
Srećom, kod XLOOKUP-a je tačno podudaranje podrazumevana opcija – što je najčešći razlog za korišćenje formula za pretragu. Ovo smanjuje potrebu za odgovaranjem na peti argument i smanjuje šanse za greške, posebno kod početnika u korišćenju ovih formula.
Ukratko, XLOOKUP postavlja manje pitanja od VLOOKUP-a, jednostavniji je za korisnike i mnogo je pouzdaniji.
XLOOKUP može pretraživati ulevo
Mogućnost izbora raspona pretrage čini XLOOKUP znatno fleksibilnijim od VLOOKUP-a. Kod XLOOKUP-a, redosled kolona u tabeli nije bitan.
VLOOKUP je bio ograničen pretragom krajnje leve kolone u tabeli, sa mogućnošću vraćanja podataka iz određene kolone koja se nalazi desno.
U sledećem primeru, treba da pretražimo ID (kolona E) i vratimo ime osobe (kolona D).
Ovo se može postići sledećom formulom: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Šta učiniti ako vrednost nije pronađena
Korisnici funkcija za pretragu su dobro upoznati sa porukom o grešci #N/A, koja se pojavljuje kada VLOOKUP ili MATCH funkcija ne mogu pronaći traženu vrednost. Često postoji logičan razlog za ovo.
Zbog toga, korisnici brzo traže načine da sakriju ovu grešku, jer nije informativna niti korisna. Postoje različiti načini za to.
XLOOKUP dolazi sa ugrađenim argumentom „if not found“ za rešavanje ovakvih grešaka. Pogledajmo kako to radi na primeru koji smo ranije koristili, ali sa pogrešno unetim ID-om.
Sledeća formula će prikazati tekst „Netačan ID“ umesto poruke o grešci: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,“Netačan ID“)
Korišćenje XLOOKUP-a za pretragu raspona
Iako nije tako često kao traženje tačnog podudaranja, veoma efikasna upotreba formule za pretragu je traženje vrednosti u rasponu. Uzmimo sledeći primer. Želimo da pronađemo popust u zavisnosti od potrošenog iznosa.
Ovog puta ne tražimo specifičnu vrednost. Moramo da znamo gde vrednosti iz kolone B spadaju u rasponu vrednosti iz kolone E. To će odrediti iznos popusta koji se dobija.
XLOOKUP ima opcioni peti argument (podsetimo se, tačno podudaranje je podrazumevano) pod nazivom „match mode“.
Kao što se može videti, XLOOKUP ima naprednije opcije za približna podudaranja od VLOOKUP-a.
Možete odabrati najbliže podudaranje koje je manje (-1) ili veće (1) od tražene vrednosti. Takođe, postoji mogućnost korišćenja džoker znakova (2) kao što su ? ili *. Ovo podešavanje nije automatski uključeno kao što je bilo kod VLOOKUP-a.
Formula u ovom primeru će vratiti najbližu manju vrednost od tražene vrednosti ako tačno podudaranje nije pronađeno: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Međutim, u ćeliji C7 se javlja greška #N/A (nije korišćen argument „if not found“). Ovde je trebalo da se vrati popust od 0%, jer potrošnja od 64 ne ispunjava kriterijume za ostvarivanje bilo kakvog popusta.
Još jedna prednost XLOOKUP funkcije je da ne zahteva da raspon pretraživanja bude sortiran u rastućem redosledu kao što je to slučaj kod VLOOKUP-a.
Ubacite novi red na dnu tabele za pretragu i otvorite formulu. Proširite opseg koji koristite tako što ćete kliknuti i prevući uglove.
Formula odmah ispravlja grešku. Nije problem imati „0“ na dnu raspona.
Iako se preporučuje sortiranje tabele po koloni za pretragu radi preglednosti, činjenica da se formula nije pokvarila je zaista odlična.
XLOOKUP zamenjuje i funkciju HLOOKUP
Kao što je već pomenuto, XLOOKUP je tu da zameni i funkciju HLOOKUP. Dakle, jedna funkcija zamenjuje dve, što je sjajno!
HLOOKUP je funkcija za horizontalnu pretragu, koja se koristi za pretraživanje duž redova.
Iako nije tako poznata kao VLOOKUP, korisna je u situacijama kao što je prikazano ispod, gde su zaglavlja u koloni A, a podaci u redovima 4 i 5.
XLOOKUP može pretraživati u oba smera – kroz kolone i redove. Stoga, više nisu potrebne dve različite funkcije.
U ovom primeru, formula se koristi za vraćanje vrednosti prodaje koja odgovara imenu u ćeliji A2. Pretražuje se red 4 kako bi se pronašlo ime, a zatim se vraća vrednost iz reda 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP može pretraživati od dna prema vrhu
Obično je potrebno pretražiti listu da bi se pronašlo prvo (često jedino) pojavljivanje vrednosti. XLOOKUP ima šesti argument pod nazivom „search mode“. Ovo omogućava da se pretraga započne od dna i da se na listi traži poslednje pojavljivanje vrednosti.
U donjem primeru, želimo da pronađemo nivo zaliha za svaki proizvod iz kolone A.
Tabela za pretragu je sortirana po datumu i postoje više provera zaliha za svaki proizvod. Želimo da vratimo nivo zaliha sa poslednje provere (poslednje pojavljivanje ID-a proizvoda).
Šesti argument XLOOKUP funkcije pruža četiri opcije. U ovom slučaju nas zanima opcija „Pretraži od poslednjeg ka prvom“.
Kompletna formula je prikazana ovde: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
U ovoj formuli, četvrti i peti argument su izostavljeni, jer nisu obavezni i želimo da koristimo podrazumevano tačno podudaranje.
Zaključak
Funkcija XLOOKUP predstavlja dugo očekivanog naslednika funkcija VLOOKUP i HLOOKUP.
U ovom članku su prikazani razni primeri koji demonstriraju prednosti XLOOKUP-a. Jedna od prednosti je da se XLOOKUP može koristiti na radnim listovima, radnim sveskama i tabelama. Primere koje smo koristili su jednostavni kako bi nam pomogli da razumemo osnovnu funkcionalnost.
Zahvaljujući dinamičkim nizovima koji se uvode u Excel, XLOOKUP uskoro može da vrati i niz vrednosti. Ovo je definitivno nešto što vredi dalje istražiti.
Dani VLOOKUP-a su odbrojani. XLOOKUP je stigao i uskoro će postati standardna formula za pretragu.