Uporaba funkcije XLOOKUP

S funkcijo XLOOKUP lahko poiščete elemente v tabeli ali obsegu po vrsticah. Poiščite na primer ceno avtomobilskega dela po številki dela ali pa poiščite ime zaposlenega glede na ID zaposlenega. S funkcijo XLOOKUP lahko iskani izraz poiščete v enem stolpcu in dobite rezultat iz iste vrstice v drugem stolpcu, ne glede na to, na kateri strani je vrnjen stolpec.

Opomba: Funkcija XLOOKUP ni na voljo v sistemih Excel 2016 in Excel 2019, vendar pa lahko naletite na položaj uporabe delovnega zvezka v programu Excel 2016 ali Excel 2019 s funkcijo XLOOKUP v njej, ki jo je ustvaril nekdo drug, ki uporablja novejšo različico Excela.

Vaš brskalnik ne podpira tega videoposnetka. Namestite Microsoft Silverlight, Adobe Flash Player ali Internet Explorer 9.

Sintaksa

Funkcija XLOOKUP išče v obsegu ali matriki in nato vrne element, ki ustreza prvemu najdenega ujemanja. Če ujemanje ne obstaja, lahko funkcija XLOOKUP vrne najbližje (približno) ujemanje.

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

Argument	Opis
iskana_vrednost Zahteva*	Vrednost, ki jo želite poiskati Če ga izpustite, vrne XLOOKUP prazne celice, ki jih najde v lookup_array*.
matrika_iskanja Obvezno	Matrika ali obseg za iskanje
return_array Obvezno	Matrika ali obseg, ki ga želite vrniti
[if_not_found] Izbirno	Če veljavnega ujemanja ni mogoče najti, vrnite besedilo [if_not_found], ki ga vrnete. Če veljavnega ujemanja ni mogoče najti in če manjka [if_not_found], funkcija #N/V .
[match_mode] Izbirno	Navedite vrsto ujemanja: 0 – Natančno ujemanje. Če ga ni mogoče najti, #N/V. To je privzeta nastavitev. -1- Natančno ujemanje. Če ga ni bilo mogoče najti, vrnite naslednji manjši element. 1 – Natančno ujemanje. Če ni najdeno noben element, vrni naslednji večji element. 2 – Nadomestni znak se ujema s posebnim pomenom *, ? in ~ .
[search_mode] Izbirno	Določite način iskanja, ki ga želite uporabiti: 1 – Izvedite iskanje, ki se začne pri prvem elementu. To je privzeta nastavitev. -1 – Izvedite obratno iskanje z začetkom pri zadnjem elementu. 2 – Izvedite dvojiško iskanje, ki temelji na lookup_array v naraščajočem vrstnem redu. Če ni razvrščeno, bodo vrnjeni neveljavni rezultati. -2 – Izvedite dvojiško iskanje, ki temelji na lookup_array v padajočem vrstnem redu. Če ni razvrščeno, bodo vrnjeni neveljavni rezultati.

Argument

Opis

iskana_vrednost

Zahteva*

Vrednost, ki jo želite poiskati

*Če ga izpustite, vrne XLOOKUP prazne celice, ki jih najde v lookup_array.

matrika_iskanja

Obvezno

Matrika ali obseg za iskanje

return_array

Obvezno

Matrika ali obseg, ki ga želite vrniti

[if_not_found]

Izbirno

Če veljavnega ujemanja ni mogoče najti, vrnite besedilo [if_not_found], ki ga vrnete.

Če veljavnega ujemanja ni mogoče najti in če manjka [if_not_found], funkcija #N/V .

[match_mode]

Izbirno

Navedite vrsto ujemanja:

0 – Natančno ujemanje. Če ga ni mogoče najti, #N/V. To je privzeta nastavitev.

-1- Natančno ujemanje. Če ga ni bilo mogoče najti, vrnite naslednji manjši element.

1 – Natančno ujemanje. Če ni najdeno noben element, vrni naslednji večji element.

2 – Nadomestni znak se ujema s posebnim pomenom *, ? in ~ .

[search_mode]

Izbirno

Določite način iskanja, ki ga želite uporabiti:

1 – Izvedite iskanje, ki se začne pri prvem elementu. To je privzeta nastavitev.

-1 – Izvedite obratno iskanje z začetkom pri zadnjem elementu.

2 – Izvedite dvojiško iskanje, ki temelji na lookup_array v naraščajočem vrstnem redu. Če ni razvrščeno, bodo vrnjeni neveljavni rezultati.

-2 – Izvedite dvojiško iskanje, ki temelji na lookup_array v padajočem vrstnem redu. Če ni razvrščeno, bodo vrnjeni neveljavni rezultati.

Primeri

1 . primer uporablja funkcijo XLOOKUP za iskanje imena države v obsegu in vrnitev telefonske kode države. Vključuje argumente lookup_value (celica F2), lookup_array (obseg B2:B11) in argumente return_array (obseg D2:D11). Ne vključuje argumenta match_mode, saj funkcija XLOOKUP privzeto ustvari natančno ujemanje.

Primer funkcije XLOOKUP, ki se uporablja za vrnitev imena zaposlenega in oddelka na podlagi ID-ja zaposlenega. Formula je =XLOOKUP(B2;B5:B14;C5:C14).

Opomba: XLOOKUP uporablja polje za iskanje in vrnjeno matriko, medtem ko VLOOKUP uporablja eno matriko tabele, ki ji sledi številka indeksa stolpca. Enakovredna formula VLOOKUP v tem primeru bi bila: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

2. primer poišče podatke o zaposlenih na podlagi številke ID zaposlenega. Za razliko od funkcije VLOOKUP lahko XLOOKUP vrne matriko z več elementi, tako da lahko ena formula vrne tako ime zaposlenega kot tudi oddelek iz celic C5:D14.

Primer funkcije XLOOKUP, ki se uporablja za vrnitev imena zaposlenega in oddelka, ki temelji na funkciji IDt zaposlenega. Formula je: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

3 . primer doda if_not_found v prejšnji primer.

Primer funkcije XLOOKUP, ki se uporablja za vrnitev imena zaposlenega in oddelka na podlagi ID-ja zaposlenega z if_not_found zaposlenim. Formula je =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Zaposlenega ni bilo mogoče najti")

———————————————————————————

4. primer v stolpcu C poišče osebne dohodke, vnesene v celico E2, in poišče ujemajočo se davčno stopnjo v stolpcu B. Nastavi, da if_not_found argument vrne 0 (nič), če ni mogoče najti ničesar. Argument match_mode nastavljen na 1, kar pomeni, da bo funkcija poiskala natančno ujemanje. Če ga ne najde, vrne naslednji večji element. Na koncu je search_mode argument nastavljen na 1, kar pomeni, da bo funkcija iskala od prvega elementa do zadnjega.

Slika funkcije XLOOKUP, ki se uporablja za vrnitev davčne stopnje, ki temelji na največjem dohodku. Gre za približno ujemanje. Formula je: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Opomba: Stolpec funkcije XARRAY lookup_array na desni strani stolpca z return_array, medtem ko lahko funkcija VLOOKUP išče le od leve proti desni.

———————————————————————————

5. primer uporablja ugnezdeno funkcijo XLOOKUP za izvajanje navpičnega in vodoravnega ujemanja. Najprej poišče bruto dobiček v stolpcu B, nato pa poišče »Četa1« v zgornji vrstici tabele (obseg C5:F5), in na koncu vrne vrednost v preseku obeh. To je podobno uporabi funkcij INDEX in MATCH skupaj.

Namig: Funkcijo HLOOKUP lahko zamenjate tudi s funkcijo XLOOKUP .

Slika funkcije XLOOKUP, ki se uporablja za vrnitev vodoravnih podatkov iz tabele z gnezdenjem 2 XLOOKUPs. Formula je: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

Opomba: Formula v celicah D3:F3 je: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3;$C 5:$G 5,$C 6:$G 17))).

———————————————————————————

6. primer uporablja funkcijo SUM in dve ugnezdeni funkciji XLOOKUP, da sešteje vse vrednosti med dvema obsegoma. V tem primeru želimo sešteti vrednosti za grozdje, banane in vključiti hruške, ki so med njima.

Uporaba funkcije XLOOKUP s funkcijo SUM za seštevanje obsega vrednosti, ki so med dvema izboroma

Formula v celici E3 je: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Kako to deluje? XLOOKUP vrne obseg, tako da pri izračunu formula na koncu izgleda tako: =SUM($E$7:$E$9). Postopek si lahko ogledate sami, tako da izberete celico s formulo XLOOKUP, podobno tej, nato pa izberete Formule > Nadzor formul >Ovrednoti formulo, nato pa izberete Ovrednoti, da izvedete korak med izračunom.

Opomba: Zahvaljujoč MVP-ju za Microsoft Excel, Billu Jelenu, ki je predlagal ta primer.

———————————————————————————

Glejte tudi

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pa pridobite podporo v skupnostih.

Funkcija MATCH

Funkcije v Excelu (po abecedi)

Excelove funkcije (po kategoriji)

XLOOKUP

Sintaksa

Primeri

Glejte tudi

Ali potrebujete dodatno pomoč?

Ali želite več možnosti?

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.