Funkcija VLOOKUP je bila vedno ena najmočnejših Excelovih funkcij. Omogoča iskanje vrednosti v prvem stolpcu tabele in vračanje vrednosti iz polj na desni. Toda Excel ima tudi funkcijo, imenovano XLOOKUP, ki vam omogoča iskanje vrednosti v katerem koli stolpcu ali vrstici in vrnitev podatkov iz katerega koli drugega stolpca.
Kako deluje XLOOKUP
Funkcija XLOOKUP je veliko enostavnejša za uporabo kot funkcija VLOOKUP, saj lahko namesto podajanja vrednosti za stolpec z rezultati določite celoten obseg.
Funkcija omogoča tudi iskanje po stolpcu in vrstici, pri čemer poiščete vrednost v celici, ki se seka.
Parametri funkcije XLOOKUP so naslednji:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: vrednost, ki jo želite iskati
- lookup_array: Niz (stolpec), ki ga želite iskati
- return_array: rezultat (stolpec), iz katerega želite pridobiti vrednost
- match_mode (neobvezno): Izberite natančno ujemanje (0), natančno ujemanje ali naslednjo najmanjšo vrednost (-1) ali ujemanje z nadomestnimi znaki (2).
- search_mode (neobvezno): Izberite, ali naj se iskanje začne s prvim elementom v stolpcu (1), zadnjim elementom v stolpcu (-1), binarno iskanje naraščajoče (2) ali binarno iskanje padajoče (-2).
Sledeče je nekaj najpogostejših iskanj, ki jih lahko izvedete s funkcijo XLOOKUP.
Kako poiskati en sam rezultat z XLOOKUP
Najlažji način za uporabo XLOOKUP je iskanje enega rezultata z uporabo podatkovne točke iz enega stolpca.
-
Ta primer preglednice je seznam naročil, ki so jih oddali prodajni predstavniki, vključno z artiklom, številom enot, ceno in skupno prodajo.
Image -
Če želite najti prvo prodajo na seznamu, ki ga je predložil določen prodajni zastopnik, lahko ustvarite funkcijo XLOOKUP, ki išče ime v stolpcu Rep. Funkcija bo vrnila rezultat iz stolpca Total. Funkcija XLOOKUP za to je:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: kaže na Rep Name iskalno celico
- C2:C44: To je stolpec Rep, ki je iskalni niz
- G2:G33: To je stolpec Total, ki je povratna matrika
- 0: izbere natančno ujemanje
- 1: izbere prvo ujemanje v rezultatih
-
Ko pritisnete Enter in vnesete ime prodajnega zastopnika, vam bo celica Skupni rezultat prikazala prvi rezultat v tabeli za tega prodajnega zastopnika.
Image -
Če želite poiskati najnovejšo prodajo (ker je tabela razvrščena po datumu v obratnem vrstnem redu), spremenite zadnji argument XLOOKUP v - 1, ki se bo začel iskanje iz zadnje celice v matriki za iskanje in vam namesto tega ponudi ta rezultat.
Image -
Ta primer prikazuje podobno iskanje, ki bi ga lahko izvedli s funkcijo VLOOKUP z uporabo stolpca Rep kot prvega stolpca iskalne tabele. Vendar vam XLOOKUP omogoča iskanje katerega koli stolpca v obe smeri. Na primer, če želite poiskati prodajnega predstavnika, ki je prodal prvo naročilo Binder v tem letu, bi uporabili naslednjo funkcijo XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: kaže na celico za iskanje predmeta
- D2:D44: To je stolpec Postavka, ki je iskalna matrika
- C2:C44: To je stolpec Rep, ki je povratna matrika na levi strani iskane matrike
- 0: izbere natančno ujemanje
- 1: izbere prvo ujemanje v rezultatih
-
Tokrat bo rezultat ime prodajnega zastopnika, ki je prodal prvo naročilo vezave v tem letu.
Image
Izvedite navpično in vodoravno ujemanje z XLOOKUP
Druga možnost XLOOKUP-a, ki je VLOOKUP ne zmore, je zmožnost izvajanja navpičnega in vodoravnega iskanja, kar pomeni, da lahko iščete element v stolpcu in po vrsti.
Ta funkcija dvojnega iskanja je učinkovita zamenjava za druge Excelove funkcije, kot so INDEX, MATCH ali HLOOKUP.
-
V naslednji vzorčni preglednici je prodaja za vsakega prodajnega predstavnika razdeljena na četrtine. Če bi želeli videti prodajo v tretjem četrtletju za določenega prodajnega predstavnika brez funkcije XLOOKUP, bi bilo takšno iskanje težavno.
Image -
S funkcijo XLOOKUP je takšno iskanje preprosto. Z naslednjo naslednjo funkcijo XLOOKUP lahko iščete prodajo v tretjem četrtletju za določenega prodajnega predstavnika:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Kaže na celico za iskanje predstavnikov
- B2:B42: To je stolpec Postavka, ki je matrika za iskanje stolpcev
- K2: Kaže na celico za iskanje četrtine
- C1:H1: To je niz za iskanje vrstic
- C2:H42: To je iskalna matrika za znesek v dolarjih v vsakem četrtletju
Ta ugnezdena funkcija XLOOKUP najprej identificira prodajnega predstavnika, naslednja funkcija XLOOKUP pa identificira želeno četrtletje. Vrnjena vrednost bo celica, kjer se ta dva prestrežeta.
-
Rezultat te formule je zaslužek v prvem četrtletju za predstavnika z imenom Thompson.
Image
Uporaba funkcije XLOOKUP
Funkcija XLOOKUP je na voljo samo naročnikom na Office Insider, vendar bo kmalu uvedena za vse naročnike Microsoft 365.
Če želite funkcijo preizkusiti sami, lahko postanete Office Insider. Izberite File > Account, nato izberite spustni meni Office Insider, da se naročite.
Ko se pridružite programu Office Insider, bo vaša nameščena različica Excela prejela vse najnovejše posodobitve in lahko začnete uporabljati funkcijo XLOOKUP.
