Formule in funkcije

XLOOKUP

Funkcija XLOOKUP

Uporabite funkcijo XLOOKUP , da poiščete elemente v tabeli ali obsegu po vrsticah. Na primer, poiščite ceno avtomobilskega dela s številko dela ali pa poiščite ime delavca na podlagi svojega ID-ja zaposlenih. Z XLOOKUP lahko pogledate v en stolpec za iskalni izraz in vrnete rezultat iz iste vrstice v drugem stolpcu, ne glede na to, na kateri strani je vrnjeni stolpec.

Vaš brskalnik ne podpira tega videoposnetka.

Sintaksa

Funkcija XLOOKUP išče obseg ali matriko, nato pa vrne element, ki ustreza prvi tekmi, ki jo najde. Če ne obstaja nobena tekma, lahko 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 izpustite, XLOOKUP vrne prazne celice, ki jih najde v lookup_array.   

matrika_iskanja

Obvezno

Matrika ali obseg, ki ga želite poiskati

return_array

Obvezno

Matrika ali obseg, ki ga želite vrniti

[if_not_found]

Izbirno

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

Če ni mogoče najti veljavnega ujemanja in [if_not_found] manjka, se vrne #N/a .

[match_mode]

Izbirno

Določite vrsto ujemanja:

0 – natančno ujemanje. Če ga ni bilo mogoče najti, se vrnite #N/A. To je privzeta nastavitev.

-1-natančna ujemanja. Če je ni mogoče najti, vrnite naslednji manjši element.

1 – natančno ujemanje. Če je ni mogoče najti, vrnite naslednji večji element.

2 – nadomestni znak se ujema s tem, kje *,? in ~ imajo poseben pomen.

[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 – izvedete obratno iskanje, ki se začne pri zadnjem elementu.

2 – izvedite binarno iskanje, ki se opira na lookup_array, ki so razvrščene v naraščajočem vrstnem redu. Če niso razvrščeni, bodo vrnjeni neveljavni rezultati.

-2 – izvedite binarno iskanje, ki se opira na lookup_array razvrščene v padajočem vrstnem redu. Če niso razvrščeni, bodo vrnjeni neveljavni rezultati.

Primeri

Primer 1    uporablja XLOOKUP, če želite poiskati ime države v obsegu in nato vrniti kodo telefonske države. Vključuje lookup_value (celica F2), lookup_array (obseg B2: B11) in Return_array (obseg D2: D11). Ne vključuje argumenta» match_mode «, saj XLOOKUP samodejno ustvari popolno ujemanje.

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

Opomba: XLOOKUP uporablja polje za iskanje in polje za vračanje, medtem ko Funkcija 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)

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

Primer 2    poišče informacije o zaposlenem, ki temeljijo na ID-ju zaposlenega. V nasprotju s funkcijo VLOOKUP lahko XLOOKUP vrne polje z več elementi, tako da lahko ena formula vrne ime in oddelek za zaposlene iz celic C5: D14.

Primer funkcije XLOOKUP, ki se uporablja za vrnitev imena in službe delavca na podlagi IDt zaposlenih. Formula je: = XLOOKUP (B2, B5: B14; C5: D14; 0; 1)

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

Primer 3    doda argument» if_not_found «v prejšnji primer.

Primer funkcije XLOOKUP, ki se uporablja za vrnitev imena in službe delavca na podlagi ID-ja zaposlenega z argumentom» if_not_found «. Formula je = XLOOKUP (B2, B5: B14; C5: D14; 0; 1; "zaposlenega ni bilo mogoče najti")

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

Primer 4    je videti v stolpcu C za osebni dohodek, vnesen v celico E2, in poišče ujemanje davčne stopnje v stolpcu B. Nastavi argument» if_not_found «, da vrne 0 (nič), če se ne najde nič. Argument» match_mode «je nastavljen na 1, kar pomeni, da bo funkcija iskala natančno ujemanje in če je ne najde, vrne naslednji večji element. Na koncu je argument» search_mode «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 na podlagi največjega dohodka. To je približna tekma. Formula je: = XLOOKUP (E2; C2: C7; B2: B7; 1; 1)

Opomba: Lookup_array stolpca» XARRAY «je desno od stolpca» return_array «, Funkcija VLOOKUP pa je lahko videti le od leve proti desni.

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

5. primer    uporablja ugnezdeno funkcijo XLOOKUP za izvajanje navpične in horizontalne ujemanja. Najprej poišče bruto dobiček v stolpcu B, nato pa poišče Čtr1 v zgornji vrstici tabele (obseg C5: F5) in končno vrne vrednost na presečišču obeh. To je podobno uporabi funkcij index in Match skupaj.

Namig: Če želite zamenjati funkcijo HLOOKUP , lahko uporabite tudi XLOOKUP.

Slika funkcije XLOOKUP, ki se uporablja za vračanje 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    uporabi funkcijo sumin dve ugnezdeni funkciji XLOOKUP, da seštejeta vse vrednosti med dvema obsegoma. V tem primeru želimo sešteti vrednosti za grozdje, banane in vključujejo hruške, ki so med obema.

Uporaba XLOOKUP z VSOTo za skupno število vrednosti, ki sodijo med dva izbora

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 ob izračunavanju formula konča videti tako: = sum ($E $7: $E $9). To lahko vidite tako, da izberete celico s formulo XLOOKUP, ki je podobna tej, nato pa izberite formule > nadzor formul > ocenite formuloin nato izberite oceni , da se pomikate po izračunu.

Opomba: Hvala za Microsoft Excel MVP, Bill jelen, za predlaganje tega primera.

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

Potrebujete dodatno pomoč?

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

Glejte tudi

Funkcija XMATCH

Funkcije v Excelu (po abecedi)

Excelove funkcije (po kategoriji)

Ali potrebujete dodatno pomoč?

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije.

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×