Kako uporabljati funkcijo XLOOKUP v Excelu 2016

Excel 2016 za naročnike na Office 365 v operacijskih sistemih Windows in Mac zdaj podpira novo funkcijo XLOOKUP, ki se omenja kot bistveno enostavnejša in bolj vsestranska zamenjava za zelo priljubljeno (a še pogosto zlonamerno) funkcijo navpičnega iskanja, VLOOKUP (ne vem, kaj je X v XLOOKUP). pomeni; morda obsežno?).

Za tiste, ki še niste seznanjeni z VLOOKUP (ki velja za tretjo najpogosteje uporabljeno funkcijo takoj za SUM in AVERAGE), ta funkcija išče navpično po vrstici v skrajnem levem stolpcu določene iskalne tabele od zgoraj navzdol, dokler ne najde vrednosti v stolpec za iskanje, označen s številko odmika, ki se ujema ali presega tisto, ki jo iščete. Čeprav je izredno uporabna za iskanje določenih elementov na dolgem seznamu ali stolpcu podatkovne tabele na vašem delovnem listu, ima funkcija VLOOKUP več omejitev, ki jih ta nova funkcija iskanja ne deli, kot je XLOOKUP:

• Privzeto za iskanje natančnih ujemanj za vašo vrednost iskanja v obsegu iskanja
• Lahko išče navpično (po vrstici) in vodoravno (po stolpcu) v tabeli, s čimer nadomesti potrebo po uporabi funkcije HLOOKUP pri vodoravnem iskanju po stolpcu
• Lahko išče levo ali desno, tako da ni treba, da se iskalni obseg v vaši iskalni tabeli nahaja v stolpcu levo od tistega, ki je določen kot povratni obseg, da funkcija deluje
• Ko je uporabljeno privzeto natančno ujemanje, deluje tudi, če vrednosti v iskalnem območju niso razvrščene v določenem vrstnem redu
• Lahko išče od spodnje vrstice do vrha v obsegu iskalnega niza z uporabo izbirnega argumenta načina iskanja

Funkcija XLOOKUP ima pet možnih argumentov, od katerih so prvi trije obvezni, zadnja dva pa izbirna, z uporabo naslednje sintakse:

XLOOKUP ( lookup_value , vpogledna_matrika , return_array [ match_mode ], [ search_mode ])

Zahtevani argument lookup_value označuje vrednost ali postavko, ki jo iščete. Zahtevani argument matrike look_up označuje obseg celic, ki jih je treba iskati za to iskalno vrednost, argument return_array pa obseg celic, ki vsebujejo vrednost, ki jo želite vrniti, ko Excel najde natančno ujemanje.

* Pri določanju argumentov lookup_array in return_array v vaši funkciji XLOOKUP ne pozabite, da morata biti oba obsega enake dolžine, sicer bo Excel vrnil #VALUE! napaka v vaši formuli. To je bolj razlog, da pri definiranju teh argumentov uporabljate imena obsegov ali stolpcev določene podatkovne tabele, namesto da jih izpostavljate ali vnašate v njihove reference celic .

Opcijski match_mode argument lahko vsebujejo katerega koli od naslednjih štirih vrednosti:

• 0 za natančno ujemanje (privzeto, enako kot, če ni določen argument match_mode )
• -1 za natančno ujemanje ali naslednjo manjšo vrednost
• 1 za natančno ujemanje ali naslednjo večjo vrednost
• 2 za delno ujemanje z uporabo nadomestnih znakov, združenih s sklicevanjem na celico v argumentu lookup_value

Opcijski search_mode argument lahko vsebujejo katerega koli od naslednjih štirih vrednosti:

• 1 za iskanje od prvega do zadnjega, to je od vrha do dna (privzeto, enako kot, če ni določen argument search_mode )
• -1 za iskanje od zadnjega do prvega, torej od spodaj navzgor
• 2 za binarno iskanje v naraščajočem vrstnem redu
• -2 za binarno iskanje v padajočem vrstnem redu

Najboljši način za razumevanje moči in vsestranskosti nove funkcije XLOOKUP je, da jo vidite v akciji na Excelovem delovnem listu. Na naslednji sliki imam delovni list s preprosto tabelo s podatki o prodaji za leto 2019, urejeno po državah. Če želite uporabiti XLOOKUP za vrnitev celotne prodaje iz te tabele v celici E4 na podlagi države, ki jo vnesete v celico D4 delovnega lista, naredite naslednje:

Postavite kazalec celice v celico E4 delovnega lista

Kliknite možnost Iskanje in sklic na zavihku Formule, ki ji sledi XLOOKUP na dnu spustnega menija, da odprete pogovorno okno Argumenti funkcije.

Kliknite celico D4 na delovnem listu, da vnesete njeno sklic na celico v besedilno polje argumenta Lookup_value.

Pritisnite tabulatorko, da izberete besedilno polje argumenta Lookup_array, nato kliknite celico A4 in držite tipko Shift, ko pritiskate Ctrl-puščica navzdol, da izberete A4:A8 kot obseg za iskanje (ker je obseg A3:B8 definiran kot podatkovna tabela Excel, Tabela1[Država] se prikaže v besedilnem polju namesto obsega A4:A8).

Pritisnite tabulatorko, da izberete besedilno polje argumenta Return_array, nato kliknite celico B4 in držite tipko Shift, ko pritiskate Ctrl-puščica navzdol, da izberete B4:B8 kot obseg, ki vsebuje vrednosti, ki jih je treba vrniti na podlagi rezultatov iskanja (ki je prikazan kot Tabela 1 [Skupna prodaja] v besedilnem polju).

Kliknite V redu, da vnesete formulo XLOOKUP v celico E4.

Ustvarjanje formule z XLOOKUP v celici E4, ki vrne prodajo na podlagi države, vnesene v celico D4.

Excel vnese formulo XLOOKUP v celico E4 delovnega lista in kot rezultat vrne 4900, ker je Kostarika trenutno vnesena v iskalno celico D4 in kot lahko vidite v tabeli prodaje za leto 2019, je to dejansko celotna prodaja za to državo.

Ker XLOOKUP deluje tako od desne proti levi kot od leve proti desni, lahko to funkcijo uporabite enako dobro, da vrnete državo iz te prodajne tabele na podlagi določene številke prodaje. Naslednja slika vam prikazuje, kako to storite. Tokrat ustvarite formulo XLOOKUP v celici D4 in določite vrednost, vneseno v celico E4 (v tem primeru 11.000), kot argument lookup_value.

Poleg tega vnesete -1 kot argument match_mode, da preglasite privzeto natančno ujemanje funkcije, tako da Excel vrne državo z natančnim ujemanjem z vrednostjo prodaje, vneseno v iskalno celico E4, ali tisto z naslednjo nižjo skupno prodajo (Mehika z 10.000 USD v tem primeru, ker v tej tabeli ni države z 11.000 USD celotne prodaje). Brez določitve argumenta match_mode za to formulo bi Excel vrnil #NA kot rezultat, ker v tej prodajni tabeli ni natančnega ujemanja z 11.000 $.

Ustvarjanje formule z XLOOKUP v celici D4, ki vrne državo na podlagi prodaje, vnesene v celico E4

Ker je funkcija XLOOKUP enako udobna pri iskanju vodoravno po stolpcih kot navpično po vrstici, jo lahko uporabite za ustvarjanje formule, ki izvede dvosmerno iskanje (nadomešča potrebo po ustvarjanju formule, ki združuje funkcije INDEX in MATCH kot v preteklosti). Naslednja slika, ki vsebuje tabelo razporeda proizvodnje za leto 2019 za številke delov, od AB-100 do AB-103 za mesece od aprila do decembra, prikazuje, kako se to naredi.

Ustvarjanje formule z ugnezdenimi funkcijami XLOOKUP za vrnitev števila proizvedenih enot za del v določenem mesecu

V celici B12 sem ustvaril naslednjo formulo:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

Ta formula se začne z definiranjem funkcije XLOOKUP, ki navpično išče po vrstici za natančno ujemanje z vnosom dela v celici z imenom part_lookup (v tem primeru celica B10) v obsegu celic $A$3:$A$6 proizvodne tabele . Upoštevajte pa, da je argument return_array za to izvirno funkcijo LOOKUP sama druga funkcija XLOOKUP.

Ta druga, ugnezdena funkcija XLOOKUP išče obseg celic $B$2:$J$2 vodoravno po stolpcu za natančno ujemanje z vnosom datuma v celici z imenom date_lookup (v tem primeru celica B11). Argument return_array za to drugo, ugnezdeno funkcijo XLOOKUP je $B$3:$J$6, obseg celic vseh produkcijskih vrednosti v tabeli.

Ta formula deluje tako, da Excel najprej izračuna rezultat druge, ugnezdene funkcije XLOOKUP z izvajanjem vodoravnega iskanja, ki v tem primeru vrne matriko v obsegu celic D3: D6 stolpca 19. junija (z vrednostmi: 438, 153, 306 in 779) kot rezultat. Ta rezultat pa postane argument return_array za izvirno funkcijo XLOOKUP, ki izvede navpično iskanje po vrstici za natančno ujemanje z vnosom številke dela, ki je vnesena v celico B11 (imenovano part_lookup). Ker v tem primeru ta celica part_lookup vsebuje AB-102, formula vrne samo produkcijsko vrednost 19. junija, 306, iz rezultata druge, naslednje funkcije XLOOKUP.

Tukaj imaš! Prvi pogled na XLOOKUP, zmogljivo, vsestransko in dokaj enostavno novo funkcijo iskanja, ki ne more izvajati samo iskanja ene vrednosti, ki ga izvajata funkciji VLOOKUP in HLOOKUP, temveč tudi dvosmerna iskanja vrednosti, ki se izvajajo s kombinacijo Funkcije INDEX in MATCH.

* Na žalost funkcija XLOOKUP ni združljiva nazaj s prejšnjimi različicami programa Microsoft Excel, ki podpirajo samo funkciji VLOOKUP in HLOOKUP, ali združljiva s trenutnimi različicami, ki je še ne vključujejo kot eno od funkcij iskanja, kot sta Excel 2019 in Excel Online . To pomeni, da če delite delovni zvezek, ki vsebuje formule XLOOKUP, s sodelavci ali strankami, ki uporabljajo različico Excela, ki ne vključuje te nove funkcije iskanja, bodo vse te formule vrnile #NAME? vrednosti napak, ko odprejo delovni list.