„Excel 2016“, skirta „Office 365“ prenumeratoriams „Windows“ ir „Mac“ sistemose, dabar palaiko naują XLOOKUP funkciją, reklamuojamą kaip daug paprastesnį ir universalesnį labai populiarios (bet dažnai piktinančios) vertikalios paieškos funkcijos VLOOKUP pakaitalą (nežinau, kas yra X sistemoje XLOOKUP reiškia; eXtensive, galbūt?).
Tiems iš jūsų, kurie dar nėra susipažinę su VLOOKUP (laikoma trečiąja dažniausiai naudojama funkcija iškart po SUM ir AVERAGE), ši funkcija ieško vertikaliai pagal eilutę kairiajame nurodytos paieškos lentelės stulpelyje iš viršaus į apačią, kol randa reikšmę peržvalgos stulpelis, pažymėtas poslinkio skaičiumi, kuris atitinka arba viršija tą, kurį ieškote. Nors funkcija VLOOKUP yra nepaprastai naudinga ieškant konkrečių elementų ilgame duomenų lentelės sąraše arba stulpelyje jūsų darbalapyje, funkcija VLOOKUP turi keletą apribojimų, kurių ši nauja paieškos funkcija nedarantuoja, kaip XLOOKUP:
Funkcija XLOOKUP turi penkis galimus argumentus, iš kurių pirmieji trys yra būtini, o du paskutiniai pasirenkami, naudojant šią sintaksę:
XLOOKUP ( lookup_value , tablica_wyszukiwania , return_array , [ match_mode ], [ search_mode ])
Reikalingas argumentas lookup_value nurodo reikšmę arba elementą, kurio ieškote. Reikalingas look_up masyvo argumentas nurodo langelių diapazoną, kuriame reikia ieškoti šios peržvalgos reikšmės, o argumentas return_array nurodo langelių diapazoną, kuriame yra reikšmė, kurią norite grąžinti, kai "Excel" randa tikslią atitiktį.
* Atminkite, kai funkcijoje XLOOKUP nurodote argumentus lookup_array ir return_array, abu diapazonai turi būti vienodo ilgio, kitaip Excel pateiks #VALUE! jūsų formulės klaida. Dėl šios priežasties apibrėžiant šiuos argumentus reikia naudoti tam tikros duomenų lentelės diapazonų arba stulpelių pavadinimus, o ne nurodyti juos arba įvesti jų langelių nuorodas .
Pasirenkamame argumente match_mode gali būti bet kuri iš šių keturių reikšmių:
Pasirenkamame argumente search_mode gali būti bet kuri iš šių keturių reikšmių:
Geriausias būdas suprasti naujosios XLOOKUP funkcijos galią ir universalumą – pamatyti ją veikiant „Excel“ darbalapyje. Tolesniame paveikslėlyje turiu darbalapį su paprasta 2019 m. pardavimo duomenų lentele, sutvarkyta pagal šalį. Norėdami naudoti XLOOKUP, kad grąžintumėte visą pardavimą iš šios lentelės E4 langelyje pagal šalį, kurią įvedėte darbalapio D4 langelyje, atlikite šiuos veiksmus:
Padėkite langelio žymeklį darbalapio E4 langelyje
Spustelėkite parinktį Lookup & Reference skirtuke Formulės, po to XLOOKUP šalia išskleidžiamojo meniu apačios, kad atidarytumėte dialogo langą Funkcijų argumentai.
Spustelėkite langelį D4 darbalapyje, kad įvestumėte jo langelio nuorodą į argumento Lookup_value teksto laukelį.
Paspauskite Tab, kad pasirinktumėte argumento „Lookup_masyvo“ teksto laukelį, tada spustelėkite langelį A4 ir laikykite nuspaudę klavišą „Shift“, kol paspausite Ctrl ir rodyklę žemyn, kad pasirinktumėte A4:A8 kaip paieškos diapazoną (nes diapazonas A3:B8 yra apibrėžtas kaip „Excel“ duomenų lentelė, 1 lentelė [Šalis] rodoma teksto laukelyje vietoje diapazono A4:A8).
Paspauskite Tab, kad pasirinktumėte argumento „Return_array“ teksto laukelį, tada spustelėkite langelį B4 ir laikykite nuspaudę „Shift“, kol paspausite „Ctrl“ ir rodyklę žemyn, kad pasirinktumėte B4:B8 kaip diapazoną, kuriame yra reikšmės, kurios turi būti grąžinamos pagal paieškos rezultatus (kuri rodoma kaip 1 lentelė [Bendras pardavimas] teksto laukelyje).
Spustelėkite Gerai, kad įvestumėte XLOOKUP formulę langelyje E4.
Formulės sukūrimas su XLOOKUP langelyje E4, kuri grąžina pardavimą pagal D4 langelyje įvestą šalį.
„Excel“ įveda XLOOKUP formulę į darbalapio E4 langelį ir kaip rezultatą pateikia 4900, nes Kosta Rika šiuo metu įvesta į D4 paieškos langelį ir, kaip matote 2019 m. pardavimo lentelėje, tai iš tikrųjų yra bendras pardavimas šioje šalyje.
Kadangi XLOOKUP veikia tiek iš dešinės į kairę, tiek iš kairės į dešinę, šią funkciją taip pat galite naudoti norėdami grąžinti šalį iš šios pardavimo lentelės pagal tam tikrą pardavimo skaičių. Toliau pateiktame paveikslėlyje parodyta, kaip tai padaryti. Šį kartą D4 langelyje sukuriate formulę XLOOKUP ir E4 langelyje įvestą reikšmę (šiuo atveju 11 000) nurodote kaip argumentą lookup_value.
Be to, įvedate -1 kaip match_mode argumentą, kad nepaisytumėte funkcijos tikslios atitikties numatytosios vertės, kad „Excel“ grąžintų šalį, tiksliai atitinkančią pardavimo vertę, įvestą paieškos langelyje E4, arba tą, kurios bendras pardavimas yra kitas mažesnis (Meksika su 10 000 USD šiuo atveju, nes šioje lentelėje nėra šalies, kurios bendras pardavimas 11 000 USD). Nenurodžius šios formulės argumento match_mode, „Excel“ kaip rezultatą pateiks #NA, nes šioje pardavimo lentelėje nėra tikslios 11 000 USD atitikties.
Formulės sukūrimas su XLOOKUP langelyje D4, kuri pateikia šalį pagal E4 langelyje įvestus pardavimus
Kadangi funkcija XLOOKUP yra vienodai patogi ieškoti horizontaliai pagal stulpelį ir ieškoti vertikaliai pagal eilutę, galite ją naudoti kurdami formulę ir atlieka dvipusę paiešką (pakeičiant poreikį sukurti formulę, kuri apjungtų funkcijas INDEX ir MATCH kaip praeityje). Toliau pateiktame paveikslėlyje, kuriame yra 2019 m. gamybos grafiko lentelė su dalių numeriais AB-100–AB-103 nuo balandžio iki gruodžio mėn., parodyta, kaip tai daroma.
Formulės su įdėtomis XLOOKUP funkcijomis sukūrimas, kad būtų grąžintas dalies pagamintų vienetų skaičius per tam tikrą mėnesį
Langelyje B12 sukūriau šią formulę:
=XLOOKUP(dalies_peržvalga,$A$3:$A$6,XLOOKUP(datos_žvalga,$B$2:$J$2,$B$3:$J$6))
Ši formulė prasideda apibrėžiant funkciją XLOOKUP, kuri vertikaliai pagal eilutę ieško tikslios dalies įrašo atitikties langelyje, pavadintame part_lookup (šiuo atveju langelis B10), gamybos lentelės langelių diapazone $A$3:$A$6. . Tačiau atminkite, kad šios pradinės LOOKUP funkcijos argumentas return_array yra antroji XLOOKUP funkcija.
Ši antroji, įdėtoji XLOOKUP funkcija ieško langelių diapazono $B$2:$J$2 horizontaliai pagal stulpelį, kad tiksliai atitiktų datos įrašą, įvestą langelyje pavadinimu date_lookup (šiuo atveju langelis B11). Šios antrosios į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 antrosios, įdėtos XLOOKUP funkcijos rezultatą, atlikdama horizontalią paiešką, kuri šiuo atveju grąžina masyvą birželio 19 stulpelio langelių diapazone D3: D6 (su reikšmėmis: 438, 153, 306 ir 779), kaip rezultatas. Šis rezultatas, savo ruožtu, tampa argumentu return_array originaliai XLOOKUP funkcijai, kuri atlieka vertikalią paiešką pagal eilutę, kad tiksliai atitiktų dalies numerio įrašą, padarytą langelyje B11 (pavadintas part_lookup). Kadangi šiame pavyzdyje šioje part_lookup langelyje yra AB-102, formulė grąžina tik birželio 19 d. gamybos vertę 306 iš antrosios, kitos funkcijos XLOOKUP rezultato.
Štai jūs tai turite! Pirmas žvilgsnis į XLOOKUP – galingą, universalią ir gana lengvai naudojamą naują paieškos funkciją, kuri gali atlikti ne tik vienos reikšmės paieškas, atliekamas naudojant VLOOKUP ir HLOOKUP funkcijas, bet ir dvipuses verčių paieškas, atliekamas derinant INDEX ir MATCH funkcijos.
* Deja, funkcija XLOOKUP nėra suderinama su ankstesnėmis „Microsoft Excel“ versijomis, kurios palaiko tik funkcijas VLOOKUP ir HLOOKUP, arba suderinama su dabartinėmis versijomis, kuriose ji dar neįtraukta kaip viena iš paieškos funkcijų, pvz., „Excel 2019“ ir „Excel Online“ . Tai reiškia, kad jei darbaknygę su XLOOKUP formulėmis bendrinate su bendradarbiais arba klientais, kurie naudoja „Excel“ versiją, kurioje nėra šios naujos paieškos funkcijos, visos šios formulės pateiks #NAME? klaidų vertes, kai jie atidaro darbalapį.
Funkcija XLOOKUP ieško diapazono arba masyvo, tada grąžina elementą, atitinkantį pirmąją rastą atitiktį. Jei atitikties nėra, XLOOKUP gali pateikti artimiausią (apytikslę) atitiktį.
=XLOOKUP(paieškos_vertė, paieškos_masyvas, grįžimo_masyvas, [jei_nerasta], [atitikties_režimas], [paieškos_režimas])
|
Argumentas |
apibūdinimas |
|---|---|
|
lookup_value Privaloma* |
Ieškoma reikšmė |
|
lookup_masyvas Privaloma |
Masyvas arba diapazonas, kurio reikia ieškoti |
|
return_masyvas Privaloma |
Masyvas arba diapazonas, kurį norite grąžinti |
|
[jei_nerasta] Neprivaloma |
Jei tinkamos atitikties nerasta, grąžinkite pateiktą [if_not_found] tekstą. Jei tinkama atitiktis nerasta ir trūksta [if_not_found], #N/A pateikiamas. |
|
[match_mode] Neprivaloma |
Nurodykite atitikties tipą: 0 – tiksli atitiktis. Jei nerasta, grąžinkite #N/A. Tai yra numatytasis. -1 - Tiksli atitiktis. Jei nerasta, grąžinkite kitą mažesnę prekę. 1 – Tiksli atitiktis. Jei neradote, grąžinkite kitą didesnę prekę. 2 – pakaitos simbolio atitiktis, kur *, ? ir ~ turi ypatingą reikšmę. |
|
[paieškos_režimas] Neprivaloma |
Nurodykite naudotiną paieškos režimą: 1 – Atlikite paiešką, pradedant nuo pirmojo elemento. Tai yra numatytasis. -1 - Atlikite atvirkštinę paiešką, pradedant nuo paskutinio elemento. 2 – Atlikite dvejetainę paiešką, kuri priklauso nuo lookup_masyvo rūšiavimo didėjimo tvarka. Jei nerūšiuosite, bus grąžinti neteisingi rezultatai. -2 - Atlikite dvejetainę paiešką, kuri remiasi lookup_masyvo rūšiavimu mažėjančia tvarka. Jei nerūšiuosite, bus grąžinti neteisingi rezultatai. |
1 pavyzdys naudoja XLOOKUP, kad surastų šalies pavadinimą diapazone ir grąžintų telefono šalies kodą. Tai apima lookup_value (ląstelė F2), lookup_masyvas (diapazonas B2: B11) ir return_array (diapazonas D2:D11) argumentus. Jame nėra match_mode argumento, nes XLOOKUP pagal numatytuosius nustatymus pateikia tikslią atitiktį.
Pastaba: XLOOKUP naudoja paieškos masyvą ir grąžinimo masyvą, o VLOOKUP naudoja vieną lentelės masyvą, po kurio nurodomas stulpelio indekso numeris. Lygiavertė VLOOKUP formulė šiuo atveju būtų: =VLOOKUP(F2,B2:D11,3,FALSE)
————————————————————————————
2 pavyzdys ieško darbuotojo informacijos pagal darbuotojo ID numerį. Kitaip nei VLOOKUP, XLOOKUP gali pateikti masyvą su keliais elementais, todėl viena formulė gali grąžinti ir darbuotojo vardą, ir skyrių iš langelių C5:D14.
————————————————————————————
3 pavyzdys prie ankstesnio pavyzdžio prideda if_not_found argumentą.
————————————————————————————
4 pavyzdys C stulpelyje ieško asmeninių pajamų, įvestų E2 langelyje, o B stulpelyje randa atitinkamą mokesčio tarifą. Jis nustato if_not_found argumentas grąžinti 0 (nulis), jei nieko nerasta. match_mode argumentas nustatytas į 1, o tai reiškia, kad funkcija ieškos tikslios atitikties, o jei jos neras, ji grąžins kitas didesnis elementas. Galiausiai paieškos_režimas argumentas nustatytas į 1, o tai reiškia funkciją ieškos nuo pirmo iki paskutinio.
Pastaba: XARRAY's lookup_array stulpelis yra dešinėje nuo stulpelis, o VLOOKUP gali žiūrėti tik iš kairės į dešinę.return_array
————————————————————————————
5 pavyzdys naudoja įdėtą XLOOKUP funkciją, kad atliktų vertikalią ir horizontalią atitiktį. Pirmiausia jis ieško Bendrojo pelno B stulpelyje, tada ieško Qtr1 viršutinėje lentelės eilutėje (diapazonas C5:F5) ir galiausiai grąžina reikšmę dviejų sankirtoje. Tai panašu į funkcijų INDEX ir MATCH naudojimą kartu.
Patarimas: taip pat galite naudoti XLOOKUP, kad pakeistumėte funkciją HLOOKUP.
Pastaba: Formulė langeliuose D3:F3 yra: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3, $C5:$G5, $C6:$G17)).
————————————————————————————
6 pavyzdyje naudoja funkciją SUM ir dvi įdėtas XLOOKUP funkcijas, kad susumuotų visas dviejų diapazonų vertes. Šiuo atveju norime susumuoti vynuogių, bananų vertes ir įtraukti kriaušes, kurios yra tarp šių dviejų.
E3 langelio formulė yra: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Kaip tai veikia? XLOOKUP grąžina diapazoną, todėl skaičiuojant formulė atrodo taip: =SUM($E$7:$E$9). Galite patys pamatyti, kaip tai veikia, pasirinkę langelį su XLOOKUP formule, panašia į šią, tada pasirinkite Formulės > Formulės auditas > Įvertinkite formulę, tada pasirinkite Įvertinti , kad atliktumėte skaičiavimą.
Kaip neleisti Microsoft Word atidaryti failų tik skaitymo režimu sistemoje Windows. Microsoft Word atidaro failus tik skaitymo režimu, todėl jų redaguoti neįmanoma? Nesijaudinkite, metodai pateikiami žemiau
Kaip ištaisyti klaidas spausdinant neteisingus Microsoft Word dokumentus.Klaidos spausdinant Word dokumentus su pakeistais šriftais, netvarkingomis pastraipomis, trūkstamo teksto ar prarasto turinio yra gana dažnos. Tačiau nereikia
Jei naudodami rašiklį ar žymeklį piešėte ant savo PowerPoint skaidrės pristatymo metu, galite išsaugoti brėžinius kitam pristatymui arba ištrinti juos, kad kitą kartą rodydami pradėtumėte nuo švarių PowerPoint skaidrių. Vykdykite šias instrukcijas, kad ištrintumėte rašiklio ir žymeklio piešinius: Trinti linijas po vieną […]
Stilių bibliotekoje yra CSS failų, XSL (Extensible Stylesheet Language) failų ir vaizdų, naudojamų iš anksto nustatytų puslapių puslapiuose, puslapių išdėstymuose ir valdikliuose „SharePoint 2010“. Norėdami rasti CSS failus publikavimo svetainės stilių bibliotekoje: pasirinkite Svetainės veiksmai → Peržiūrėti Visas svetainės turinys. Pasirodo svetainės turinys. Stiliaus biblioteka yra […]
Neužkraukite savo auditorijos milžiniškais skaičiais. Programoje „Microsoft Excel“ galite pagerinti informacijos suvestinių ir ataskaitų skaitomumą formatuodami skaičius, kad jie būtų rodomi tūkstančiais arba milijonais.
Sužinokite, kaip naudoti „SharePoints“ socialinių tinklų įrankius, leidžiančius asmenims ir grupėms bendrauti, bendradarbiauti, bendrinti ir prisijungti.
Julijaus datos dažnai naudojamos gamybos aplinkoje kaip laiko žyma ir greita partijos numerio nuoroda. Šio tipo datos kodavimas leidžia mažmenininkams, vartotojams ir paslaugų agentams nustatyti, kada produktas buvo pagamintas, taigi ir produkto amžių. Julijaus datos taip pat naudojamos programavimo, kariuomenės ir astronomijos srityse. Skirtingi […]
Galite sukurti žiniatinklio programą naudodami „Access 2016“. Taigi, kas vis dėlto yra žiniatinklio programa? Na, žiniatinklis reiškia, kad jis yra internete, o programa yra tik „programos“ santrumpa. Pasirinktinė žiniatinklio programa yra internetinė duomenų bazės programa, pasiekiama iš debesies naudojant naršyklę. Kuriate ir prižiūrite žiniatinklio programą darbalaukio versijoje […]
Daugumoje „SharePoint 2010“ puslapių sparčiojo paleidimo juostoje, esančioje kairėje puslapio pusėje, pateikiamas naršymo nuorodų sąrašas. Greitosios paleidimo juostoje rodomos nuorodos į siūlomą svetainės turinį, pvz., sąrašus, bibliotekas, svetaines ir publikavimo puslapius. Greitojo paleidimo juostoje yra dvi labai svarbios nuorodos: Visas svetainės turinys: […]
Paprastoms problemoms spręsti „Excel“ sprendėjas paprastai greitai suranda optimalias tikslinės funkcijos „Solver“ kintamųjų reikšmes. Tačiau kai kuriais atvejais „Solver“ turi problemų ieškant „Solver“ kintamųjų reikšmių, optimizuojančių tikslo funkciją. Tokiais atvejais „Solver“ paprastai rodo pranešimą arba klaidos pranešimą, kuriame aprašoma arba aptariama problema, kurią […]
