1. SoftwareMicrosoft OfficeExcelKako koristiti funkciju XLOOKUP u Excelu 2016

Greg Harvey

Excel 2016 za pretplatnike Office 365 na Windows i Mac sada podržava novu XLOOKUP funkciju, nastupu kao znatno jednostavniju i svestraniju zamjenu za vrlo popularnu (ali često zlostavljanu) funkciju vertikalnog pretraživanja, VLOOKUP (ne znam što X u XLOOKUP zalaže se za, eXtensive, možda?).

Za one koji još nisu upoznati s VLOOKUP-om (koji se smatra trećom najčešće korištenom funkcijom odmah nakon SUM-a i AVERAGE-a), ova funkcija pretražuje vertikalno po redak u krajnjem lijevom stupcu određene tablice pretraživanja od vrha do dna dok ne nađe vrijednost u stupac za pretraživanje označen pomaknutim brojem koji odgovara ili premašuje onaj koji tražite. Iako je izuzetno korisno za smještanje određenih stavki na dugi popis ili stupac tablice podataka na vašem radnom listu, funkcija VLOOKUP ima nekoliko ograničenja koja ova nova funkcija pretraživanja, kao XLOOKUP, ne dijeli:

  • Neuspješno pronalazi točno podudaranje vrijednosti pretraživanja u rasponu pretraživanja Može pretraživati ​​i vertikalno (po redovima) i vodoravno (po stupcu) u tablici, čime zamjenjuje potrebu za korištenjem funkcije HLOOKUP pri horizontalnom pretraživanju po stupcu Možete pretraživati ​​lijevo ili desno tako da se raspon pretraživanja u vašoj pretraživačkoj tablici ne mora nalaziti u stupcu s lijeve strane od onog koji je određen kao povratni raspon da bi funkcija radila Kada se koristi zadana vrijednost podudaranja, funkcionira čak i kad se vrijednosti u rasponu pretraživanja ne razvrstavaju po posebnom redoslijedu Možete pretraživati ​​od donjeg retka do vrha u rasponu polja pretraživanja, koristeći neobavezni argument načina pretraživanja

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

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

Potrebni argument lookup_value označava vrijednost ili stavku za kojom tražite. Potrebni argument array look_up označava raspon ćelija koje se traže za ovu vrijednost pretraživanja, a argument return_array označava raspon ćelija koji sadrže vrijednost koju želite vratiti kad Excel pronađe točno podudaranje.

* Imajte na umu prilikom određivanja argumenata lookup_array i return_array u vašoj XLOOKUP funkciji, oba raspona moraju biti jednake duljine, u protivnom Excel će vratiti #VALUE! greška u vašoj formuli. Ovo je još više razlog za to da koristite nazive raspona ili nazive stupaca određene tablice podataka kada definirate te argumente, a ne da ih upućujete ili kucate u njihovim referencama ćelija.

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

  • 0 za točno podudaranje (zadano isto kao kad nije dodijeljen 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 korištenjem zamjenskih znakova pridruženih referenci ćelije u argumentu lookup_value

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

  • 1 za pretraživanje od prvog do posljednjeg, to jest od vrha do dna (zadano, isto kao kad nije dodijeljen argument search_mode) -1 za pretraživanje od posljednjeg do prvog, to jest odozdo prema gore 2 za binarno pretraživanje uzlaznim redoslijedom -2 za binarno pretraživanje silaznim redoslijedom

Najbolji način za razumijevanje snage i svestranosti nove XLOOKUP funkcije jest vidjeti je na djelu na radnom listu u Excelu. Na sljedećoj slici imam radni list s jednostavnom tablicom podataka o prodaji za 2019. godinu, poredanu po zemljama. Da biste pomoću XLOOKUP-a vratili ukupnu prodaju iz ove tablice u ćeliji E4 na temelju zemlje koju unesete u ćeliju D4 radnog lista, poduzmite ove korake:

  1. Postavite kursor ćelije u ćeliju E4 radnog lista Kliknite opciju Pretraživanje i referenca na kartici Formule, a zatim XLOOKUP pri dnu padajućeg izbornika kako biste otvorili dijaloški okvir Argumenti funkcija. Kliknite ćeliju D4 na radnom listu da biste unijeli njenu referencu ćelije u tekstni okvir argumenata Lookup_value. Pritisnite Tab za odabir tekstualnog okvira argumenta Lookup_array, zatim pritisnite ćeliju A4 i držite Shift dok pritisnete strelicu Ctrl prema dolje da odaberete A4: A8 kao raspon za pretraživanje (jer je raspon A3: B8 definiran kao tablica podataka Excela, Tablica1 [Zemlja] pojavljuje se u tekstnom polju umjesto raspona A4: A8). Pritisnite Tab za odabir tekstualnog okvira argumenta Return_array, zatim pritisnite ćeliju B4 i držite pritisnutu tipku Shift dok pritisnete strelicu Ctrl prema dolje da biste odabrali B4: B8 kao raspon koji sadrži vrijednosti koje se vraćaju na temelju rezultata pretraživanja (koji se prikazuje kao Tablica1 [Ukupna prodaja] u tekstnom polju).

Kliknite U redu za unos formule XLOOKUP u ćeliju E4.

Izrada XLOOKUP formule

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

Budući da XLOOKUP djeluje s desna na lijevo jednako dobro s lijeva na desno, ovu funkciju možete dobro koristiti i za vraćanje zemlje iz tablice prodaje na temelju određene prodajne brojke. Sljedeća slika prikazuje vam kako se to događa. Ovog puta kreirate formulu XLOOKUP u ćeliji D4 i označite vrijednost upisanu u ćeliju E4 (u ovom slučaju 11.000) kao argument lookup_value.

Uz to unosite -1 kao argument match_mode za poništavanje zadanih vrijednosti točnog podudaranja funkcije, tako da Excel vraća zemlju s točno podudaranjem s prodajnom vrijednosti unesenom u ćeliju za pretragu 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 imenovanja argumenta match_mode za ovu formulu, Excel bi vratio #NA kao rezultat, jer u ovoj prodajnoj tablici nema tačno podudaranje sa 11.000 USD.

XLOOKUP formula u D4

Budući da je funkcija XLOOKUP jednako ugodna u horizontalnom pretraživanju po stupcima, dok se vertikalno pretražuje po redovima, možete je koristiti za kreiranje formule koja vrši dvosmjernu pretragu (zamjenjujući potrebu za stvaranjem formule koja kombinira funkcije INDEX i MATCH kao u prošlosti). Sljedeća slika, koja sadrži tablicu proizvodnog rasporeda za 2019. za brojeve dijelova, AB-100 do AB-103 za mjesece od travnja do prosinca, pokazuje vam kako se to radi.

ugniježđene XLOOKUP funkcije

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 započinje definiranjem XLOOKUP funkcije koja vertikalno traži po retcima točno podudaranje s unosom dijela koji je napravljen u ćeliji nazvanoj part_lookup (u ovom slučaju ćelija B10) u rasponu ćelije $ A $ 3: $ A $ 6 proizvodne tablice , No, imajte na umu da je argument return_array za ovu originalnu LOOKUP funkciju sam po sebi druga XLOOKUP funkcija.

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 nazvanom date_lookup (u ovom slučaju ćelija B11). Argument return_array za ovu sekundu, ugniježđena funkcija XLOOKUP je $ B $ 3: $ J $ 6, raspon ćelija svih proizvodnih vrijednosti u tablici.

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

Eno ga! Prvi pogled na XLOOKUP, snažnu, svestranu i prilično jednostavnu novu funkciju pretraživanja, koja ne može samo pretraživati ​​pojedinačne vrijednosti koje izvode VLOOKUP i HLOOKUP funkcije, već i dvosmjerne pretrage vrijednosti kombinirane INDEX i MATCH funkcioniraju.

* Nažalost, funkcija XLOOKUP nije nazadno kompatibilna s starijim verzijama Microsoft Excel-a koje podržavaju samo funkcije VLOOKUP i HLOOKUP ili kompatibilne s trenutnim verzijama koje je još ne uključuju kao jednu od svojih pretraživačkih funkcija, kao što su Excel 2019 i Excel Online. To znači da ako dijelite radnu knjižicu koja sadrži formule XLOOKUP s suradnicima ili klijentima koji koriste verziju programa Excel koja ne uključuje ovu novu funkciju pretraživanja, sve ove formule vraćaju #NAME? vrijednosti pogreške kada otvore radni list.