Funkcija Excel XLOOKUP: Vse, kar morate vedeti (10 primerov)

Oglejte si video - funkcija Excel XLOOKUP (10 primerov XLOOKUP)

Excel Funkcija XLOOKUP je končno prišel.

Če ste uporabljali VLOOKUP ali INDEX/MATCH, sem prepričan, da vam bo všeč prilagodljivost, ki jo ponuja funkcija XLOOKUP.

V tej vadnici bom obravnaval vse, kar je treba vedeti o funkciji XLOOKUP, in nekaj primerov, ki vam bodo pomagali vedeti, kako jo najbolje uporabiti.

Pa začnimo!

Kaj je XLOOKUP?

XLOOKUP je nova funkcija Office 365 in je nova in izboljšana različica funkcije VLOOKUP/HLOOKUP.

Dela vse, kar je nekoč storil VLOOKUP, in še veliko več.

XLOOKUP je funkcija, ki vam omogoča hitro iskanje vrednosti v nizu podatkov (navpično ali vodoravno) in vrnitev ustrezne vrednosti v drugo vrstico/stolpec.

Če imate na primer ocene za študente na izpitu, lahko s XLOOKUP -om hitro preverite, koliko študent je dosegel z imenom študenta.

Moč te funkcije bo postala še bolj jasna, ko se poglobim v nekatere Primeri XLOOKUP kasneje v tej vadnici.

Preden se lotim primerov, pa se postavlja veliko vprašanje - kako pridem do XLOOKUP -a?

Kako do dostopa do XLOOKUP?

Zaenkrat je XLOOKUP na voljo samo za uporabnike sistema Office 365.

Če torej uporabljate prejšnje različice Excela (2010/2013/2016/2019), te funkcije ne boste mogli uporabljati.

Prav tako nisem prepričan, ali bo to kdaj izdano za prejšnje različice ali ne (morda lahko Microsoft ustvari dodatek, kot so to storili za Power Query). Zaenkrat ga lahko uporabljate le, če uporabljate Office 365.

Kliknite tukaj za nadgradnjo na Office 365

Če že uporabljate Office 365 (domača, osebna ali univerzitetna izdaja) in nimate dostopa do njega, pojdite na zavihek Datoteka in kliknite Račun.

Na voljo bi bil program Office Insider, ki ga lahko kliknete in se mu pridružite. Tako boste imeli dostop do funkcije XLOOKUP.

Pričakujem, da bo XLOOKUP kmalu na voljo v vseh različicah sistema Office 365.

Opomba: XLOOKUP je na voljo tudi za Office 365 za Mac in Excel za splet (Excel na spletu)

Sintaksa funkcije XLOOKUP

Spodaj je sintaksa funkcije XLOOKUP:

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

Če ste uporabljali VLOOKUP, boste opazili, da je skladnja precej podobna, seveda z nekaj odličnimi dodatnimi funkcijami.

Ne skrbite, če sta sintaksa in argument videti preveč. Kasneje v tej vadnici jih zajemam z nekaj enostavnimi primeri XLOOKUP, ki bodo kristalno jasni.

Funkcija XLOOKUP lahko pove 6 argumentov (3 obvezne in 3 neobvezne):

1. lookup_value - vrednost, ki jo iščete
2. lookup_array - matriko, v kateri iščete iskalno vrednost
3. return_array - matriko, iz katere želite pridobiti in vrniti vrednost (ustreza položaju, kjer je najdena iskalna vrednost)
4. [if_not_found] - vrednost, ki se vrne, če iskalne vrednosti ni mogoče najti. Če tega argumenta ne navedete, bi se vrnila napaka #N/A
5. [match_mode] - Tu lahko določite želeno vrsto ujemanja:
   • 0 - Natančno ujemanje, pri katerem se mora lookup_value natančno ujemati z vrednostjo v lookup_array. To je privzeta možnost.
   • -1 - Išče natančno ujemanje, če pa ga najde, vrne naslednji manjši element/vrednost
   • 1 - Išče natančno ujemanje, če pa ga najde, vrne naslednji večji element/vrednost
   • 2 - Za delno ujemanje z nadomestnimi znaki (* ali ~)
6. [iskalni_način] - Tu določite, kako naj funkcija XLOOKUP išče po matriki lookup_array
   • 1 - To je privzeta možnost, kjer funkcija začne iskati lookup_value od vrha (prvi element) do dna (zadnji element) v lookup_array
   • -1 - Ali išče od spodaj navzgor. Uporabno, če želite najti zadnjo ujemajočo se vrednost v polju lookup_array
   • 2 - Izvede binarno iskanje, kjer je treba podatke razvrstiti po naraščajočem vrstnem redu. Če ne razvrstite, lahko to povzroči napake ali napačne rezultate
   • -2 - Izvede binarno iskanje, kjer je treba podatke razvrstiti po padajočem vrstnem redu. Če ne razvrstite, lahko to povzroči napake ali napačne rezultate

Primeri funkcij XLOOKUP

Zdaj pa pojdimo na zanimiv del - nekaj praktičnih primerov XLOOKUP.

Ti primeri vam bodo pomagali bolje razumeti, kako deluje XLOOKUP, kako se razlikuje od VLOOKUP in INDEX/MATCH ter nekatere izboljšave in omejitve te funkcije.

Kliknite tukaj, če želite prenesti primer datoteke in nadaljevati

Primer 1: Pridobite iskalno vrednost

Recimo, da imate naslednji nabor podatkov in želite pridobiti matematični rezultat za Grega (iskalna vrednost).

Spodaj je formula, ki to naredi:

= XLOOKUP (F2, A2: A15, B2: B15)

V zgornji formuli sem pravkar uporabil obvezne argumente, kjer išče ime (od zgoraj navzdol), najde natančno ujemanje in vrne ustrezno vrednost iz B2: B15.

Ena očitna razlika, ki jo imata funkciji XLOOKUP in VLOOKUP, je v načinu ravnanja z matriko iskanja. V VLOOKUP-u imate celotno polje, kjer je iskalna vrednost v najbolj levem stolpcu, nato pa podate številko stolpca, od koder želite pridobiti rezultat. XLOOKUP pa omogoča ločeno izbiro lookup_array in return_array

Ena takojšnja prednost lookup_array in return_array kot ločenih argumentov pomeni, da lahko zdaj poglej na levo. VLOOKUP je imel to omejitev, kjer lahko samo pogledate navzgor in poiščete vrednost, ki je na desni. Toda pri XLOOKUP je ta omejitev izginila.

Tukaj je primer. Imam isti nabor podatkov, kjer je ime na desni, vračalni obseg pa na levi.

Spodaj je formula, s katero lahko dobim rezultat za Grega v matematiki (kar pomeni, da gledam levo od lookup_value)

= XLOOKUP (F2, D2: D15, A2: A15)

XLOOKUP rešuje še eno pomembno težavo - če vstavite nov stolpec ali premaknete stolpce, bodo nastali podatki še vedno pravilni. VLOOKUP bi se verjetno zlomil ali dal napačen rezultat v primerih, ko je vrednost indeksa stolpca večinoma trdo kodirana.

Primer 2: Poiščite in pridobite celoten zapis

Za primer vzemimo iste podatke.

V tem primeru ne želim samo pridobiti Gregove ocene pri matematiki. Želim doseči ocene pri vseh predmetih.

V tem primeru lahko uporabim naslednjo formulo:

= XLOOKUP (F2, A2: A15, B2: D15)

Zgornja formula uporablja obseg return_array, ki je več kot stolpec (B2: D15). Torej, ko je iskalna vrednost najdena v A2: A15, formula vrne celotno vrstico iz return_array.

Prav tako ne morete izbrisati samo celic, ki so del matrike, ki so bile samodejno poseljene. V tem primeru ne morete izbrisati H2 ali I2. Če poskusite, se ne bo nič zgodilo. Če izberete te celice, bo formula v vrstici s formulami zatemnjena (kar pomeni, da je ni mogoče spremeniti)

Formulo lahko izbrišete v celici G2 (kjer smo jo prvotno vnesli), izbrisala bo celoten rezultat.

To je koristna izboljšava, saj ste pri VLOOKUP -u prej morali določiti številko stolpca posebej za vsako formulo.

Primer 3: Dvosmerno iskanje z uporabo XLOOKUP (vodoravno in navpično iskanje)

Spodaj je niz podatkov, kjer želim izvedeti oceno Grega iz matematike (predmet v celici G2).

To je mogoče storiti z dvosmernim iskanjem, kjer iščem ime v stolpcu A in ime predmeta v vrstici 1. Prednost tega dvosmernega iskanja je, da je rezultat neodvisen od imena študenta imena predmeta. Če spremenim ime predmeta v Kemija, bi ta dvosmerna formula XLOOKUP še vedno delovala in mi dala pravilen rezultat.

Spodaj je formula, ki bo izvedla dvosmerno iskanje in dala pravilen rezultat:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Ta formula uporablja ugnezdeni XLOOKUP, kjer jo najprej uporabim za pridobivanje vseh ocen učenca v celici F2.

Tako je rezultat XLOOKUP (F2, A2: A15, B2: D15) {21,94,81}, kar je niz ocen, ki jih je v tem primeru dosegel Greg.

To se nato ponovno uporabi v zunanji formuli XLOOKUP kot vrnitev matrike. V zunanji formuli XLOOKUP iščem ime predmeta (ki je v celici G1) in iskalna matrika je B1: D1.

Če je ime predmeta Math, ta zunanja formula XLOOKUP pridobi prvo vrednost iz vrnilne matrike - kar je v tem primeru {21,94,81}.

To naredi enako, kar je bilo doslej doseženo s kombinacijo INDEX in MATCH

Kliknite tukaj, če želite prenesti primer datoteke in nadaljevati

Primer 4: Ko iskalne vrednosti ni mogoče najti (ravnanje z napakami)

Formuli XLOOKUP je bilo dodano obravnavanje napak.

Četrti argument v funkciji XLOOKUP je [if_not_found], kjer lahko določite, kaj želite, če iskanja ni mogoče najti.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, kjer želite dobiti oceno matematike v primeru, če se ujema, in če imena ni mogoče najti, se želite vrniti - 'Ni prikazano'

Spodnja formula bo to naredila:

= XLOOKUP (F2, A2: A15, B2: B15, "Ni prikazano")

V tem primeru sem težko kodiral, kaj želim dobiti, če ni ujemanja. Uporabite lahko tudi sklic celice na celico ali formulo.

Primer 5: ugnezden XLOOKUP (iskanje v več razponih)

Genij argumenta [if_not_found] je, da vam omogoča uporabo ugnezdena formula XLOOKUP.

Recimo, da imate dva ločena seznama, kot je prikazano spodaj. Medtem ko imam ti dve tabeli na istem listu, jih lahko imate na ločenih listih ali celo v delovnih zvezkih.

Spodaj je ugnezdena formula XLOOKUP, ki bo preverila ime v obeh tabelah in vrnila ustrezno vrednost iz podanega stolpca.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

V zgornji formuli sem uporabil argument [if_not_found] za uporabo druge formule XLOOKUP. To vam omogoča, da v isto formulo dodate drugi XLOOKUP in skenirate dve tabeli z eno samo formulo.

Nisem prepričan, koliko ugnezdenih XLOOKUP -ov lahko uporabite v formuli. Poskušal sem do 10 in je uspelo, potem sem obupal 🙂

Primer 6: Poiščite zadnjo ujemajočo se vrednost

Ta je bil zelo potreben in XLOOKUP je to omogočil. Zdaj vam ni treba iskati zapletenih načinov, da bi dobili zadnjo ujemajočo se vrednost v obsegu.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite preveriti, kdaj je bila zadnja oseba zaposlena v vsakem oddelku in kateri je bil datum zaposlitve.

Spodnja formula bo poiskala zadnjo vrednost za vsak oddelek in dala ime zadnjega najema:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)

Spodnja formula bo dala datum zadnjega najema za vsak oddelek:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)

Ker ima XLOOKUP vgrajeno funkcijo za določanje smeri iskanja (od prvega do zadnjega ali od zadnjega do prvega), se to naredi s preprosto formulo. Pri navpičnih podatkih sta VLOOKUP in INDEX/MATCH vedno gledana od zgoraj navzdol, pri XLOOKUP pa lahko določita tudi smer od spodaj navzgor.

Primer 7: Približno ujemanje s XLOOKUP (najdi davčno stopnjo)

Druga pomembna izboljšava pri XLOOKUP -u je, da zdaj obstajajo štirje načini ujemanja (VLOOKUP ima 2, MATCH pa 3).

Določite lahko katerega koli od štirih argumentov, da se odločite, kako naj se ujema vrednost iskanja:

• 0 - Natančno ujemanje, pri katerem se mora lookup_value natančno ujemati z vrednostjo v lookup_array. To je privzeta možnost.
• -1 - Išče natančno ujemanje, če pa ga najde, vrne naslednji manjši element/vrednost
• 1 - Išče natančno ujemanje, če pa ga najde, vrne naslednji večji element/vrednost
• 2 - Za delno ujemanje z nadomestnimi znaki (* ali ~)

Najboljši del pa je, da vam ni treba skrbeti, ali so vaši podatki razvrščeni v naraščajočem ali padajočem vrstnem redu. Tudi če podatki niso razvrščeni, bo za to poskrbel XLOOKUP.

Spodaj imam niz podatkov, kjer želim najti provizijo vsake osebe - provizijo pa je treba izračunati s pomočjo tabele na desni.

Spodaj je formula, ki bo to naredila:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2

Ta preprosto uporabi prodajno vrednost kot iskanje in pogleda skozi iskalno tabelo na desni. V tej formuli sem uporabil -1 kot peti argument ([match_mode]), kar pomeni, da bo iskal natančno ujemanje, in ko ga ne najde, bo vrnil vrednost, ki je le manjša od iskalne vrednosti .

In kot sem rekel, vam ni treba skrbeti, ali so vaši podatki razvrščeni.

Kliknite tukaj, če želite prenesti primer datoteke in nadaljevati

Primer 8: Vodoravno iskanje

XLOOKUP lahko izvaja navpično in vodoravno iskanje.

Spodaj imam nabor podatkov, v katerem so imena učencev in njihovi rezultati v vrsticah, zato želim pridobiti rezultat za ime v celici B7.

Spodnja formula bo to naredila:

= XLOOKUP (B7, B1: O1, B2: O2)

To ni nič drugega kot preprosto iskanje (podobno kot smo videli v primeru 1), ampak vodoravno.

Vse primere, ki jih obravnavam o vertikalnem iskanju, lahko naredimo tudi z vodoravnim iskanjem z uporabo XLOOKUP (slovo od VLOOKUP in HLOOKUP).

Primer 9: Pogojno iskanje (uporaba XLOOKUP z drugimi formulami)

Ta je nekoliko napreden primer in kaže tudi moč XLOOKUP -a, ko morate opraviti zapletene poizvedbe.

Spodaj je niz podatkov, kjer imam imena učencev in njihove rezultate ter želim vedeti ime študenta, ki je dosegel največ pri vsakem predmetu, in število študentov, ki so pri vsakem predmetu dosegli več kot 80 točk.

Spodaj je formula, ki bo dala ime študenta z najvišjimi ocenami pri vsakem predmetu:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)

Ker je XLOOKUP mogoče uporabiti za vrnitev celotnega niza, sem ga uporabil, da sem najprej dobil vse ocene za zahtevano temo.

Na primer, za matematiko, ko uporabljam XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), dobim vse ocene iz matematike. Nato lahko s funkcijo MAX poiščem največjo oceno v tem razponu.

Ta največja ocena nato postane moja iskalna vrednost, obseg iskanja pa je matrika, ki jo vrne XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

To uporabljam v drugi formuli XLOOKUP za pridobitev imena študenta, ki je dosegel največ točk.

Če želite šteti število študentov, ki so dosegli več kot 80, uporabite naslednjo formulo:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Ta preprosto uporablja formulo XLOOKUP, da dobi obseg vseh vrednosti za dano temo. Nato ga zavije v funkcijo COUNTIF, da dobi več kot 80 točk.

Primer 10: Uporaba nadomestnega znaka v XLOOKUP -u

Tako kot lahko uporabite nadomestne znake v VLOOKUP in MATCH, lahko to storite tudi z XLOOKUP.

Je pa razlika.

V XLOOKUP -u morate določiti, da uporabljate nadomestne znake (v petem argumentu). Če tega ne določite, vam bo XLOOKUP prikazal napako.

Spodaj je niz podatkov, kjer imam imena podjetij in njihovo tržno kapitalizacijo.

Želim poiskati ime podjetja v stolpcu D in iz tabele na levi strani pridobiti tržno kapitalizacijo. Ker se imena v stolpcu D ne ujemajo natančno, bom morala uporabiti nadomestne znake.

Spodaj je formula, ki bo to naredila:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

V zgornji formuli sem prej kot D2 uporabil nadomestni znak zvezdico (*) (mora biti v dvojnih narekovajih in se z D2 povezati z znakom črke).

To pove formuli, da poišče vse celice, in če vsebuje besedo v celici D2 (to je Apple), naj bo to natančno ujemanje. Ne glede na to, koliko znakov je pred in za besedilom v celici D2.

Za zagotovitev, da XLOOKUP sprejema nadomestne znake, je peti argument nastavljen na 2 (ujemanje nadomestnih znakov).

Primer 11: Poiščite zadnjo vrednost v stolpcu

Ker XLOOKUP omogoča iskanje od spodaj navzgor, lahko preprosto najdete zadnjo vrednost na seznamu in iz stolpca pridobite ustrezno vrednost.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite vedeti, kaj je zadnje podjetje in kakšna je tržna kapitalizacija tega zadnjega podjetja.

Spodnja formula vam bo dala ime zadnjega podjetja:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

Spodnja formula bo pokazala tržno vrednost zadnjega podjetja na seznamu:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Te formule spet uporabljajo nadomestne znake. V teh primerih sem kot iskalno vrednost uporabil zvezdico (*), kar pomeni, da bo prva celica, na katero naleti, upoštevana kot natančno ujemanje (zvezdica je lahko kateri koli znak in poljubno število znakov).

In ker je smer od spodaj navzgor (za navpično razporejene podatke), bo vrnila zadnjo vrednost na seznamu.

In druga formula, ker uporablja ločen return_range za pridobitev tržne vrednosti priimka na seznamu.

Kliknite tukaj, če želite prenesti primer datoteke in nadaljevati

Kaj pa, če nimate XLOOKUP -a?

Ker bo XLOOKUP verjetno na voljo samo uporabnikom Office 365, je eden od načinov za to nadgradnja na Office 365.

Če že imate Office 365 Home, Personal ali University edition, že imate dostop do XLOOKUP. Vse kar morate storiti je, da se pridružite programu Office Insider.

To storite tako, da odprete zavihek Datoteka, kliknete Račun in nato možnost možnosti Insider Office. Obstaja možnost, da se pridružite notranjemu programu.

Če imate druge naročnine na Office 365 (na primer Enterprise), sem prepričan, da bodo kmalu na voljo XLOOKUP in druge odlične funkcije (kot so dinamične matrike, formule, kot sta SORT in FILTER).

Če uporabljate Excel 2010/2013/2016/2019, ne boste imeli XLOOKUP -a, zato boste morali še naprej uporabljati kombinacijo VLOOKUP, HLOOKUP in INDEX/MATCH, da kar najbolje izkoristite iskalne formule.

XLOOKUP združljivost nazaj

To je ena stvar, na katero morate biti previdni - XLOOKUP je NI združljiv z nazaj.

To pomeni, da če ustvarite datoteko in uporabite formulo XLOOKUP, nato pa jo odprete v različici, ki nima XLOOKUP, bodo prikazane napake.

Ker je XLOOKUP velik korak naprej v pravo smer, verjamem, da bo to postala privzeta iskalna formula, vendar bo vsekakor trajalo nekaj let, preden bo postala široko sprejeta. Navsezadnje še vedno vidim nekaj ljudi, ki uporabljajo Excel 2003.

To je torej 11 primerov XLOOKUP, ki vam lahko pomagajo pri hitrejšem iskanju in referenčnih opravilih ter olajšajo uporabo.

Upam, da vam je bila ta vadnica koristna!