1. Programinė įrangaMicrosoft OfficeExcelHow naudoti XLOOKUP funkciją „Excel 2016“

Gregas Harvey

„Excel 2016“, skirta „Office 365“ abonentams „Windows“ ir „Mac“, dabar palaiko naują „XLOOKUP“ funkciją, pavadintą žymiai paprastesniu ir įvairiapusiškesniu labai populiarios (vis dar dažnai netinkamos) vertikalios paieškos funkcijos „VLOOKUP“ pakeitimu (nežinau, kas yra „XLOOKUP X“). reiškia; galbūt, gausus?).

Tiems iš jūsų, dar nesusipažinusiems su „VLOOKUP“ (laikoma trečiąja dažniausiai naudojama funkcija iškart po SUM ir AVERAGE), ši funkcija vertikaliai ieško eilutės kairiajame paskirtos paieškos lentelės stulpelyje iš viršaus į apačią, kol randa reikšmę peržiūros stulpelis, pažymėtas ofsetiniu numeriu, kuris atitinka arba viršija jūsų ieškomą. Nors funkcija „VLOOKUP“ yra labai naudinga ieškant tam tikrų elementų ilgame duomenų lentelės sąraše ar darbalapio stulpelyje, ji turi keletą apribojimų, kurių neteikia ši nauja paieškos funkcija, kaip „XLOOKUP“:

  • Numatoma, kad ieškos diapazone nustatomos tikslios jūsų paieškos vertės atitiktys Gali ieškoti tiek vertikaliai (pagal eilutes), tiek horizontaliai (pagal stulpelį) lentelėje, taip pakeisdami poreikį naudoti funkciją HLOOKUP, kai ieškokite horizontaliai pagal stulpelį Galima ieškoti į kairę arba į dešinę, kad jūsų paieškos lentelė neturėtų būti stulpelyje, esančiame kairėje pusėje, nurodytoje kaip grįžimo sritis, kad funkcija veiktų Kai naudojamas tikslios atitikties numatytasis nustatymas, veikia net tada, kai paieškos diapazono vertės nėra rūšiuojamos tam tikra tvarka Galima ieškoti iš apatinės eilutės į viršų peržvalgos masyvo diapazone, naudojant pasirinktinį paieškos režimo argumentą

Funkcija „XLOOKUP“ turi penkis galimus argumentus, iš kurių pirmieji trys yra būtini, o du paskutiniai neprivalomi, naudojant tokią sintaksę:

„XLOOKUP“ (peržvalgos_vertė, peržiūros_masyvas, grąžinimo_mašina, [atitikties_modas], [paieškos_modelis))

Būtinas „lookup_value“ argumentas nurodo vertę arba elementą, kurio ieškote. Reikiamas „look_up“ masyvo argumentas nurodo langelių diapazoną, kurio reikia ieškoti pagal šią paieškos reikšmę, o „return_array“ argumentas nurodo langelių, kuriuose yra norima reikšmė, diapazoną, kai „Excel“ nustato tikslią atitiktį.

* Atminkite, kai „XLOOKUP“ funkcijoje nurodote „lookup_array“ ir „return_array“ argumentus, abu diapazonai turi būti vienodo ilgio, kitaip „Excel“ grąžins #VALUE! klaida jūsų formulėje. Tai yra dar labiau priežastis, kodėl apibrėždami šiuos argumentus, o ne nurodydami juos ar įvesdami savo langelių nuorodas, turite naudoti nurodytos duomenų lentelės diapazonų ar stulpelių pavadinimus.

Neprivalomame „match_mode“ argumente gali būti bet kuri iš šių keturių verčių:

  • 0 - tiksliam atitikimui (numatytasis, tas pats, kaip tada, kai nepaskirtas joks atitikties_modelis argumentas) -1 tiksliam atitikimui ar kitai mažesnei vertei 1 - tiksli atitiktis arba kita didesnė vertė 2, skirtas daliniam atitikimui, naudojant pakaitos ženklus, sujungtus su langelio nuoroda, pateikiant argumentą lookup_value

Neprivalomame „search_mode“ argumente gali būti bet kuri iš šių keturių verčių:

  • 1 - ieškoti nuo pirmos iki paskutinės, tai yra, nuo viršaus iki apačios (numatytoji, tokia pati, kaip tada, kai nepaskirtas joks paieškos modus argumentas) -1, kad galėtumėte ieškoti paskutinis prieš pirmą, tai yra, iš apačios į viršų 2 dvejetainėje paieškoje didėjimo tvarka -2 dvejetainėms paieškoms mažėjimo tvarka

Geriausias būdas suprasti naujos „XLOOKUP“ funkcijos galingumą ir universalumą yra pamatyti, kaip ji veikia „Excel“ darbalapyje. Toliau pateiktame paveikslėlyje turiu darbalapį su paprasta 2019 metų pardavimo duomenų lentele, išdėstyta pagal šalis. Norėdami naudoti „XLOOKUP“ norėdami grąžinti bendrą pardavimą iš šios lentelės E4 langelyje pagal šalį, kurią įvedėte darbalapio D4 langelyje, atlikite šiuos veiksmus:

  1. Padėkite langelio žymeklį darbalapio E4 langelyje Spustelėkite parinktį „Ieškoti“ ir nuorodą skirtuke „Formos“, tada išskleidžiamojo meniu apačioje esančią „XLOOKUP“, tada atidarykite dialogo langą Funkcijų argumentai. Spustelėkite langelį D4 darbalapyje, kad įveskite langelio nuorodą į teksto lauką „Lookup_value“. Paspauskite „Tab“, kad pasirinktumėte argumentų teksto lauką „Lookup_array“, tada spustelėkite langelį A4 ir laikykite nuspaudę „Shift“, kai paspaudžiate rodyklę žemyn, kad pasirinktumėte diapazoną A4: A8 kaip ieškomą diapazoną (nes diapazonas A3: B8 yra apibrėžtas kaip „Excel“ duomenų lentelė, 1 lentelė [Šalis] rodoma teksto laukelyje vietoj diapazono A4: A8). Paspauskite „Tab“, kad pasirinktumėte teksto laukelį „Return_array“, tada spustelėkite langelį B4 ir laikykite nuspaudę klavišą „Shift“, kai paspausite klavišą „Ctrl“ žemyn, kad B4: B8 pasirinktumėte kaip diapazoną, kuriame yra vertės, kurios turi būti grąžintos remiantis paieškos rezultatais (rodomas kaip 1 lentelė [Bendri pardavimai] teksto laukelyje).

Spustelėkite Gerai, jei norite įvesti XLOOKUP formulę E4 langelyje.

XLOOKUP formulės kūrimas

„Excel“ įveda „XLOOKUP“ formulę į darbalapio E4 langelį ir kaip rezultatą grąžina 4900, nes Kosta Rika šiuo metu yra įrašyta į paieškos langelį D4 ir, kaip matote 2019 metų pardavimų lentelėje, tai iš tiesų yra bendras šios šalies pardavimas.

Kadangi XLOOKUP veikia iš dešinės į kairę taip pat gerai, kaip iš kairės į dešinę, šią funkciją taip pat galite naudoti norėdami grąžinti šalį iš šios pardavimo lentelės, remdamiesi tam tikru pardavimų skaičiumi. Šis paveikslėlis parodo, kaip tai darote. Šį kartą sukuriate XLOOKUP formulę D4 langelyje ir kaip „lookup_value“ argumentą nurodote E4 langelyje įvestą vertę (šiuo atveju 11 000).

Be to, kaip „match_mode“ argumentą įvedate -1, kad nepaisytumėte funkcijos tiksliosios numatytosios atitikties, kad „Excel“ grąžintų šalį su tikslia atitiktimi pardavimo vertės, įvestos paieškos langelio E4, arba šaliai, kurios bendras pardavimų skaičius yra mažesnis (Meksika su Šiuo atveju 10 000 USD, nes šioje lentelėje nėra šalies, kuriai būtų parduota 11 000 USD). Nepaskyręs šios formulės „match_mode“ argumento, „Excel“ gautų #NA rezultatą, nes šioje pardavimo lentelėje nėra tikslios atitikties 11 000 USD.

„XLOOKUP“ formulė D4

Kadangi „XLOOKUP“ funkcija yra vienodai patogi ieškant horizontaliai pagal stulpelį, kaip ir ieškant vertikaliai pagal eilutes, galite ją naudoti kurdami formulę, kuri atlieka dvipusį paiešką (pakeisiantį poreikį sukurti formulę, apimančią INDEX ir MATCH funkcijas kaip praeityje). Šis paveikslėlis, kuriame yra 2019 m. Gamybos tvarkaraščio lentelė su numeriais nuo AB iki 100 iki AB – 103 balandžio – gruodžio mėnesiams, parodo, kaip tai daroma.

įdėtos „XLOOKUP“ funkcijos

Ląstelėje B12 aš sukūriau šią formulę:

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

Ši formulė prasideda apibrėžiant XLOOKUP funkciją, kuri vertikaliai pagal eilutes ieško tikslios atitikties dalelės įrašui, padarytam langelyje pavadinimu part_lookup (šiuo atveju langelis B10), ląstelių diapazone $ A $ 3: $ A $ 6 gamybos lentelėje. . Tačiau atminkite, kad šios originalios funkcijos LOOKUP argumentas return_array yra pati antra XLOOKUP funkcija.

Ši antra, įdėta „XLOOKUP“ funkcija ląstelių diapazone $ B $ 2: $ J $ 2 horizontaliai pagal stulpelį ieško tikslios datos įvesties, padarytos langelyje, pavadinta date_lookup (šiuo atveju langelis B11). Šios sekundės, įdėtos „XLOOKUP“ funkcijos, „return_array“ argumentas yra $ B $ 3: $ J $ 6, visų lentelės gamybos verčių langelių diapazonas.

Ši formulė veikia taip, kad „Excel“ pirmiausia apskaičiuoja antros, įdėtos „XLOOKUP“ funkcijos rezultatą, atlikdama horizontalią paiešką, kuri šiuo atveju grąžina masyvą stulpelių juostoje D3: D6 iš „Jun-19“ stulpelio (su reikšmėmis: 438, 153, 306 ir 779). Šis rezultatas, savo ruožtu, tampa originalios „XLOOKUP“ funkcijos, atliekančios vertikalią paiešką pagal eilę, tikslios atitikties detalės numerio įrašui, esančiam langelyje B11 (pavadinta part_lookup), „return_array“ argumentu. Kadangi šiame pavyzdyje šioje part_lookup ląstelėje yra AB-102, formulė grąžina tik Jun-19 produkcijos vertę, 306, iš antros, kitos XLOOKUP funkcijos rezultato.

Ten tu tai turi! Pirmasis žvilgsnis į „XLOOKUP“ - galingą, universalų ir gana lengvai naudojamą naują paieškos funkciją, galinčią atlikti ne tik vienos vertės paieškas, kurias atlieka VLOOKUP ir HLOOKUP funkcijos, bet ir abipusės vertės paieškas, atliekamas derinant INDEX ir MATCH funkcijos taip pat.

* Deja, „XLOOKUP“ funkcija nėra suderinama su ankstesnėmis „Microsoft Excel“ versijomis, kurios palaiko tik „VLOOKUP“ ir „HLOOKUP“ funkcijas, arba suderinamos su dabartinėmis versijomis, kurios dar neįtraukia jos į vieną iš savo paieškos funkcijų, pavyzdžiui, „Excel 2019“ ir „Excel Online“. Tai reiškia, kad jei bendrinsite darbo knygą su „XLOOKUP“ formulėmis su bendradarbiais ar klientais, kurie naudojasi „Excel“ versija, kurioje nėra šios naujos paieškos funkcijos, visos šios formulės grįš #NAME? klaidų vertės atidarant jos darbalapį.