Kako koristiti funkciju XLOOKUP u programu Excel 2016

Excel 2016 za pretplatnike na Office 365 na Windows i Mac sada podržava novu funkciju XLOOKUP, reklamiranu kao znatno jednostavniju i svestraniju zamjenu za vrlo popularnu (ali često zlonamjernu) funkciju vertikalnog pretraživanja, VLOOKUP (ne znam što je X u XLOOKUP-u znači; opsežan, možda?).

Za one od vas koji još niste upoznati s VLOOKUP-om (smatra se trećom najčešće korištenom funkcijom odmah nakon SUM i AVERAGE), ova funkcija pretražuje okomito po retku u krajnjem lijevom stupcu određene tablice pretraživanja od vrha do dna dok ne pronađe vrijednost u stupac pretraživanja označen brojem pomaka koji odgovara ili premašuje onaj koji tražite. Iako je izuzetno korisna za lociranje određenih stavki na dugom popisu ili stupcu tablice podataka u vašem radnom listu, funkcija VLOOKUP ima nekoliko ograničenja koja ova nova funkcija pretraživanja ne dijeli, kao XLOOKUP:

• Zadano je traženje točnih podudaranja za vašu vrijednost pretraživanja u rasponu pretraživanja
• Može pretraživati ​​i okomito (po retku) i vodoravno (po stupcu) u tablici, čime se zamjenjuje potreba za korištenjem funkcije HLOOKUP pri vodoravnom pretraživanju po stupcu
• Može pretraživati ​​lijevo ili desno tako da raspon pretraživanja u vašoj tablici pretraživanja ne mora biti smješten u stupcu lijevo od onoga koji je označen kao raspon povrata kako bi funkcija radila
• Kada se koristi zadano točno podudaranje, radi čak i kada vrijednosti u rasponu pretraživanja nisu sortirane određenim redoslijedom
• Može pretraživati ​​od donjeg retka do vrha u rasponu niza pretraživanja, koristeći neobavezni argument načina pretraživanja

Funkcija XLOOKUP ima pet mogućih argumenata, od kojih su prva tri obavezna, a posljednja dva neobavezna, koristeći sljedeću sintaksu:

XLOOKUP ( tražena_vrijednost , polje_pregleda , return_array [ match_mode ], [ search_mode ])

Potrebni tražena_vrijednost argument određuje vrijednost ili predmet za koji ste u potrazi. Potrebni look_up niz tvrdnja označava raspon ćelija koje se traže za tu pregledna vrijednosti, a return_array argument označava raspon ćelija koji sadrži vrijednost koju želite vratiti kada Excel pronađe točno podudaranje.

* Imajte na umu kada označavate argumente lookup_array i return_array u svojoj funkciji XLOOKUP, oba raspona moraju biti jednake duljine, inače će Excel vratiti #VALUE! greška u vašoj formuli. Ovo je više razlog da koristite nazive raspona ili nazive stupaca određene tablice podataka kada definirate ove argumente, umjesto da ih ističete ili upisujete njihove reference na ćelije .

Dodatni match_mode argument može sadržavati bilo koju od sljedećih četiri vrijednosti:

• 0 za točno podudaranje (zadano, isto kao kada nije određen argument match_mode )
• -1 za točno podudaranje ili sljedeću manju vrijednost
• 1 za točno podudaranje ili sljedeću veću vrijednost
• 2 za djelomično podudaranje pomoću zamjenskih znakova spojenih na referencu ćelije u argumentu lookup_value

Dodatni search_mode argument može sadržavati bilo koju od sljedećih četiri vrijednosti:

• 1 za pretraživanje od prvog do zadnjeg, odnosno od vrha do dna (zadano, isto kao kada nije određen argument search_mode )
• -1 za pretraživanje od posljednjeg do prvog, odnosno od dna prema vrhu
• 2 za binarno pretraživanje uzlaznim redoslijedom
• -2 za binarno pretraživanje u silaznom redoslijedu

Najbolji način da shvatite snagu i svestranost nove funkcije XLOOKUP je da je vidite na djelu na radnom listu programa Excel. Na sljedećoj slici imam radni list s jednostavnom tablicom podataka o prodaji za 2019. poredanu po zemljama. Da biste koristili XLOOKUP za vraćanje ukupne prodaje iz ove tablice u ćeliju E4 na temelju zemlje koju unesete u ćeliju D4 radnog lista, poduzmite ove korake:

Postavite pokazivač ćelije u ćeliju E4 radnog lista

Kliknite opciju Lookup & Reference na kartici Formule, a zatim XLOOKUP pri dnu padajućeg izbornika da biste otvorili dijaloški okvir Argumenti funkcije.

Kliknite ćeliju D4 na radnom listu da biste unijeli njenu referencu ćelije u tekstualni okvir argumenta Lookup_value.

Pritisnite Tab za odabir tekstualnog okvira argumenta Lookup_array, zatim kliknite ćeliju A4 i držite Shift dok pritiskate Ctrl-strelicu prema dolje da odaberete A4:A8 kao raspon za pretraživanje (jer je raspon A3:B8 definiran kao tablica podataka Excel, Tablica1[Zemlja] pojavljuje se u tekstualnom okviru umjesto raspona A4:A8).

Pritisnite Tab za odabir tekstualnog okvira argumenta Return_array, a zatim kliknite ćeliju B4 i držite Shift dok pritiskate Ctrl-strelicu prema dolje da odaberete B4:B8 kao raspon koji sadrži vrijednosti koje treba vratiti na temelju rezultata pretraživanja (koji se pojavljuje kao Tablica1 [Ukupna prodaja] u tekstualnom okviru).

Kliknite OK za unos formule XLOOKUP u ćeliju E4.

Izrada formule s XLOOKUP u ćeliji E4 koja vraća prodaju na temelju zemlje unesene u ćeliju D4.

Excel unosi formulu XLOOKUP u ćeliju E4 radnog lista i vraća 4900 kao rezultat jer je Kostarika trenutno unesena u ćeliju za pretraživanje D4 i kao što možete vidjeti u tablici prodaje za 2019., ovo je doista ukupna prodaja ostvarena za ovu zemlju.

Budući da XLOOKUP radi zdesna nalijevo jednako dobro kao i slijeva nadesno, ovu funkciju možete koristiti jednako dobro za vraćanje zemlje iz ove tablice prodaje na temelju određene brojke prodaje. Sljedeća slika pokazuje kako to radite. Ovaj put stvarate formulu XLOOKUP u ćeliji D4 i označavate vrijednost unesenu u ćeliju E4 (11.000, u ovom slučaju) kao argument lookup_value.

Osim toga, unosite -1 kao argument match_mode da biste nadjačali zadanu zadanu vrijednost točnog podudaranja funkcije tako da Excel vraća zemlju s točnim podudaranjem s vrijednošću prodaje unesenoj u ćeliju za traženje E4 ili onu sa sljedećom nižom ukupnom prodajom (Meksiko s 10.000 USD u ovom slučaju jer u ovoj tablici nema zemlje s 11.000 USD ukupne prodaje). Bez označavanja argumenta match_mode za ovu formulu, Excel bi kao rezultat vratio #NA, jer u ovoj tablici prodaje nema točnog podudaranja s 11.000 USD.

Izrada formule s XLOOKUP-om u ćeliji D4 koja vraća zemlju na temelju prodaje unesene u ćeliju E4

Budući da je funkcija XLOOKUP jednako udobna za pretraživanje vodoravno po stupcu kao i okomito po redak, možete je koristiti za stvaranje formule koja obavlja dvosmjerno traženje (zamjenjujući potrebu za stvaranjem formule koja kombinira funkcije INDEX i MATCH kao u prošlosti). Sljedeća slika, koja sadrži tablicu rasporeda proizvodnje za 2019. za brojeve dijelova, AB-100 do AB-103 za mjesece od travnja do prosinca, pokazuje kako se to radi.

Izrada formule s ugniježđenim funkcijama XLOOKUP za vraćanje broja jedinica proizvedenih za dio u određenom mjesecu

U ćeliji B12 stvorio sam sljedeću formulu:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

Ova formula počinje definiranjem funkcije XLOOKUP koja okomito traži po retku točno podudaranje s unosom dijela napravljenim u ćeliji pod nazivom part_lookup (ćelija B10, u ovom slučaju) u rasponu ćelija $A$3:$A$6 proizvodne tablice . Međutim, imajte na umu da je argument return_array za ovu izvornu funkciju LOOKUP sama po sebi druga funkcija XLOOKUP.

Ova druga, ugniježđena funkcija XLOOKUP pretražuje raspon ćelija $B$2:$J$2 vodoravno po stupcu za točno podudaranje s unosom datuma u ćeliji pod nazivom date_lookup (u ovom slučaju ćelija B11). Argument return_array za ovu drugu, ugniježđenu funkciju XLOOKUP je $B$3:$J$6, raspon ćelija svih proizvodnih vrijednosti u tablici.

Način na koji ova formula funkcionira je da Excel prvo izračuna rezultat druge, ugniježđene funkcije XLOOKUP izvodeći horizontalno pretraživanje koje, u ovom slučaju, vraća niz u rasponu ćelija D3: D6 stupca 19. lipnja (s vrijednostima: 438, 153, 306 i 779) kao rezultat. Ovaj rezultat, zauzvrat, postaje argument return_array za izvornu funkciju XLOOKUP koja izvodi okomito pretraživanje po retku za točno podudaranje s unosom broja dijela napravljenim u ćeliji B11 (nazvanom part_lookup). Budući da u ovom primjeru ova ćelija part_lookup sadrži AB-102, formula vraća samo proizvodnu vrijednost 19. lipnja, 306, iz rezultata druge, sljedeće funkcije XLOOKUP.

Evo ga! Prvi pogled na XLOOKUP, moćnu, svestranu i prilično jednostavnu za korištenje novu funkciju pretraživanja koja ne samo da može izvršiti traženja s jednom vrijednošću koju obavljaju funkcije VLOOKUP i HLOOKUP, već i dvosmjerna pretraživanja vrijednosti koja se izvode kombiniranjem Funkcije INDEX i MATCH također.

* Nažalost, funkcija XLOOKUP nije kompatibilna unatrag s ranijim verzijama Microsoft Excela koje podržavaju samo funkcije VLOOKUP i HLOOKUP ili je kompatibilna s trenutnim verzijama koje je još ne uključuju kao jednu od svojih funkcija pretraživanja, kao što su Excel 2019 i Excel Online . To znači da ako podijelite radnu knjigu koja sadrži formule XLOOKUP sa suradnicima ili klijentima koji koriste verziju Excela koja ne uključuje ovu novu funkciju pretraživanja, sve će te formule vratiti #NAME? vrijednosti pogreške kada otvore radni list.