„Excel 2020“: dvylika „XLOOKUP“ pranašumų - „Excel“ patarimai

Naujoji „XLOOKUP“ funkcija pradedama naudoti „Office 365“ nuo 2019 m. Lapkričio mėn. Joe McDaid iš „Excel“ komandos sukūrė „XLOOKUP“, kad suvienytų žmones, naudojančius „VLOOKUP“, ir žmones, naudojančius „INDEX / MATCH“. Šiame skyriuje bus aptarti 12 „XLOOKUP“ pranašumų:

  1. Tiksli atitiktis yra numatytoji.
  2. Sveikasis skaičius pagrįstas trečiasis VLOOKUP argumentas dabar yra tinkama nuoroda.
  3. IFNA yra įmontuota tvarkyti trūkstamas reikšmes.
  4. XLOOKUP nėra jokių problemų einant į kairę.
  5. Suraskite kitą, mažesnę ar kitą didesnę atitiktį nerūšiuodami lentelės.
  6. XLOOKUP gali atlikti HLOOKUP.
  7. Suraskite paskutinę atitiktį ieškodami iš apačios.
  8. Pakaitės yra „išjungtos“ pagal numatytuosius nustatymus, tačiau jas galite vėl įjungti.
  9. Grąžinkite visus 12 mėnesių pagal vieną formulę.
  10. Gali grąžinti langelio nuorodą, jei XLOOKUP yra šalia dvitaškio, pvz., XLOOKUP (); XLOOKUP ()
  11. Gali atlikti dviejų krypčių rungtynes, tokias kaip INDEX (, MATCH, MATCH).
  12. Gali susumuoti visas paieškas vienoje formulėje, pavyzdžiui, LOOKUP.

Čia yra sintaksė: = XLOOKUP (look_Value, Look_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP 1 nauda: tikslus atitikimas pagal numatytuosius nustatymus

99% mano VLOOKUP formulių baigiasi reikšme FALSE arba 0, kad būtų nurodyta tiksli atitiktis. Jei visada naudojate tikslią „VLOOKUP“ atitikties versiją, galite pradėti „match_mode“ išjungti iš savo funkcijos „XLOOKUP“.

Šiame paveikslėlyje ieškote W25-6 iš A4 langelio. To daikto norite ieškoti L8: L35. Kai jis bus rastas, norėsite nurodyti atitinkamą kainą iš N stulpelio. Nereikia nurodyti „False“ kaip „match_mode“, nes „XLOOKUP“ pagal numatytuosius nustatymus yra tiksli atitiktis.

XLOOKUP vertė A4. Pažvelkite į L8: L35. Grąžinkite atitinkamą kainą iš N8: N35.

XLOOKUP 2 nauda: Rezultatų masyvas yra nuoroda, o ne sveikasis skaičius

Pagalvokite apie VLOOKUP formulę, kurią naudotumėte prieš XLOOKUP. Trečiasis argumentas būtų 3, nurodantis, kad norite grąžinti 3-iąjį stulpelį. Visada buvo pavojus, kad nenaudingas bendradarbis į jūsų lentelę įterpė (arba ištrynė) stulpelį. Lentelėje esant papildomam stulpeliui, VLOOKUP, kuris grąžino kainą, pradėtų grąžinti aprašą. Kadangi „XLOOKUP“ nukreipė į langelio nuorodą, formulė perrašo save, kad toliau rodytų kainą, kuri dabar yra O stulpelyje.

Senas VLOOKUP nepavyktų, jei kas nors į paieškos lentelę įterptų naują stulpelį. XLOOKUP dirba toliau.

XLOOKUP 3 nauda: IFNA yra integruota kaip pasirenkamas argumentas

Bijota klaida # N / A grąžinama, kai jūsų paieškos vertė nerandama lentelėje. Anksčiau, norėdami pakeisti # N / A kažkuo kitu, turėtumėte naudoti IFERROR arba IFNA, apvyniotą VLOOKUP.

Kai elementas nerandamas, jis pateikia # N / A iš VLOOKUP arba XLOOKUP…

Dėka Rico pasiūlymo mano „YouTube“ kanale, „Excel“ komanda įtraukė neprivalomą ketvirtąjį argumentą if_not_found. Jei norite šias # N / A klaidas pakeisti nuliu, tiesiog pridėkite 0 kaip ketvirtą argumentą. Arba galite naudoti tekstą, pvz., „Reikšmė nerasta“.

XLOOKUP neprivalomas ketvirtasis argumentas yra „jei nerastas“. Įdėkite 0 arba „Not found“.

XLOOKUP 4 nauda: nekyla problemų žiūrint į kairę nuo raktų lauko

VLOOKUP negali ieškoti kairėje klavišo lauko, nenaudodamas VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Naudojant „XLOOKUP“, nėra jokių problemų, jei „Rezultatų_dabalys“ yra kairėje „Paieškos_daryklė“.

Naudojant „XLOOKUP“, nekyla jokių problemų grąžinant kategoriją iš F stulpelio ieškant dalių numerių G stulpelyje. Tai visada buvo „VLOOKUP“ silpnybė: ji negalėjo atrodyti kairėn.

XLOOKUP 5 nauda: kita-mažesnė arba kita-didesnė rungtis nerūšiuojant

„VLOOKUP“ turėjo galimybę ieškoti tikslios atitikties arba tiesiog mažesnės vertės. Galite palikti ketvirtąjį argumentą iš „VLOOKUP“ arba pakeisti „False“ į „True“. Kad tai veiktų, paieškos lentelę reikėjo surikiuoti didėjimo tvarka.

Apytikslės atitikties „VLOOKUP“ versijos pavyzdys. Bet koks pardavimas nuo 10 iki 20 tūkstančių gauna 12 USD premiją.

Bet VLOOKUP neturėjo galimybės grąžinti tikslios atitikties ar kito didesnio elemento. Tam turėjote pereiti prie MATCH naudojimo su -1 kaip „match_mode“ ir turėjote būti atsargūs, kad paieškos lentelė būtų surikiuota mažėjančia tvarka.

„XLOOKUP“ pasirinktinis penktasis argumentas „match_mode“ gali ieškoti tik tikslios atitikties, lygi arba tiesiog mažesnė, lygi arba tiesiog didesnė. Atminkite, kad XLOOKUP reikšmės yra prasmingesnės nei MATCH:

  • -1 randa reikšmę, lygią arba tiesiog mažesnę
  • 0 rasti tikslią atitiktį
  • 1 randa reikšmę, lygią arba tiesiog didesnę.

Bet pati nuostabiausia dalis: paieškos lentelės nereikia rūšiuoti ir veiks bet koks „match_mode“.

Žemiau „match_mode“ –1 raskite kitą mažesnį elementą.

Penktasis XLOOKUP argumentas yra „Match_Mode“. 0 yra tiksli atitiktis. Neigiamas yra naudojamas tiksliajam atitikimui arba kitam mažesniam elementui. 1 teigiamas yra tiksli atitiktis arba kitas didesnis elementas. 2 skirta pakaitos rungtynėms. Norėdami atspindėti tai, ką darytų ketvirtojo argumento „VLOOKUP“ su „True“, XLOOKUP naudokite argumentą „match_mode“ kaip neigiamą argumentą.

Čia „match_mode 1“ randa, kokia transporto priemonė reikalinga, atsižvelgiant į žmonių skaičių vakarėlyje. Atkreipkite dėmesį, kad paieškos lentelė nėra rūšiuojama pagal keleivius, o transporto priemonės pavadinimas yra kairėje klavišo.

„XLOOKUP“ gali padaryti tai, ko „VLOOKUP“ negalėjo: rasti tikslią atitiktį arba tiesiog didesnę. Tokiu atveju kelionių kompanija turi rezervacijų sąrašą. Pagal keleivių skaičių, paieškos lentelėje parodyta, kokia transporto priemonė jums reikalinga tiems žmonėms.

Lentelėje sakoma:

  • Autobusas talpina 64 žmones
  • Automobilyje telpa 4 žmonės
  • „Motorcyle“ telpa 1 žmogus
  • „Tour Van“ telpa 12 žmonių
  • Furgone telpa 6 žmonės.

Kaip premiją, duomenys yra rūšiuojami pagal transporto priemonę (senajame sprendime, naudojant MATCH, lentelę reikėtų surūšiuoti mažėjant pagal talpą. Taip pat: transporto priemonė yra kairėje nuo talpos.

„XLOOKUP 6“ nauda: „XLOOKUP“ į šoną pakeičia „HLOOKUP“

Paieškos_dabarymas ir rezultatų_dabarymas gali būti horizontalūs naudojant XLOOKUP, todėl HLOOKUP pakeisti paprasta.

Čia paieškos lentelė yra horizontali. Anksčiau tam reikėjo HLOOKUP, tačiau XLOOKUP gali susidoroti su lentele, einančia į šoną.

XLOOKUP 7 nauda: ieškokite naujausių rungtynių iš apačios

„YouTube“ turiu seną vaizdo įrašą, kuriame atsakau į klausimą iš Didžiosios Britanijos arklių ūkio. Jie turėjo transporto priemonių parką. Kiekvieną kartą, kai transporto priemonė atvažiuodavo į degalus ar aptarnavo, jie skaičiuoklėje registruodavo transporto priemonę, datą ir ridą. Jie norėjo rasti naujausią žinomą kiekvienos transporto priemonės ridą. Nors „Excel-2017“ era MAXIFS gali tai išspręsti šiandien, sprendimas prieš daugelį metų buvo paslaptinga formulė, naudojant LOOKUP ir įtraukiant padalijimą į nulį.

Šiandien pagal pasirinktą šeštą argumentą „XLOOKUP“ galite nurodyti, kad paieška turėtų prasidėti nuo duomenų rinkinio apačios.

Sąraše raskite paskutinę atitiktį.

Pastaba

Nors tai yra puikus patobulinimas, jis leidžia rasti tik pirmąsias ar paskutines rungtynes. Kai kurie žmonės tikėjosi, kad tai leis rasti antrą ar trečią atitiktį, tačiau tai nėra argumentas „search_mode“.

Atsargiai

Aukščiau pateiktame paveikslėlyje parodyta, kad yra paieškos režimai, naudojant senąją dvejetainę paiešką. Joe McDaidas nepataria jų naudoti. Pirma, patobulintas paieškos algoritmas nuo 2018 m. Yra pakankamai greitas, kad nebūtų reikšmingos greičio naudos. Antra, rizikuojate, kad nenuoseklus bendradarbis rūšiuos paieškos lentelę ir pateiks neteisingus atsakymus.

XLOOKUP 8 nauda: Pakaitalai yra „išjungti“ pagal numatytuosius nustatymus

Daugelis žmonių nesuprato, kad VLOOKUP žvaigždutę, klaustuką ir tildę traktuoja kaip pakaitos simbolius, kaip aprašyta skyriuje „# 51 Naudokite pakaitos simbolį VLOOKUP“ “, 143 psl. Naudojant„ XLOOKUP “, pakaitos simboliai išjungiami pagal numatytuosius nustatymus. Jei norite, kad XLOOKUP šiuos simbolius traktuotų kaip pakaitos simbolius, naudokite 2 kaip „Match_Mode“.

Labai nedaug žmonių suprato, kad VLOOKUP paieškos reikšmės žvaigždutes laiko pakaitomis. Pagal numatytuosius nustatymus „XLOOKUP“ nenaudoja pakaitinių simbolių, tačiau galite priversti elgtis kaip „VLOOKUP“, jei naudojate „Match Mode 2“: „Wildcard Character Match“.

„XLOOKUP“ 9 nauda: grąžinkite visus 12 mėnesių į vieną formulę!

Tai iš tikrųjų yra „Dynamic Arrays“ pranašumas, tačiau tai yra mano mėgstamiausia priežastis pamilti „XLOOKUP“. Kai turėsite grąžinti visus 12 mėnesių, atlikdami paiešką, viena formulė, įvesta B6 su stačiakampiu grąžinimo rodikliu, pateiks kelis rezultatus. Šie rezultatai pasklis gretimose ląstelėse.

Žemiau esančiame paveikslėlyje viena formulė, įvesta į B7, pateikia visus 12 atsakymų, parodytų B7: M7.

Sausio stulpelyje pateiktas vienas XLOOKUP pateikia sausio – gruodžio mėnesių skaičius. Tai daroma nurodant rezultatų_dabarį su 12 stulpelių.

XLOOKUP 10 nauda: gali grąžinti langelio nuorodą, jei ji yra šalia dvitaškio

Šis yra sudėtingas, bet gražus. Anksčiau buvo septynios funkcijos, kurios pasikeis iš langelio vertės grąžinimo į ląstelės nuorodos grąžinimą, jei funkcija paliečia dvitaškį. Pavyzdžiui, žr. Naudokite A2: INDEX () kaip nepastovią OFFSET. „XLOOKUP“ yra aštuonių funkcijų funkcija, siūlanti tokį elgesį, prisijungiant prie PASIRINKTI, IF, IFS, INDEKSO, NETIESIOGINIO, OFFSETO ir SWITCH.

Apsvarstykite šį paveikslą. Kažkas pasirenka vyšnią E4 ir figą E5. Norite formulės, kuri susumuotų viską nuo B6 iki B9.

Paveikslėlyje parodytos dvi XLOOKUP formulės dviem langeliais. Pirmasis grąžina 15 iš langelio B6. Antrasis pakartoja 30 iš B9. Bet tada trečioje langelyje yra formulė, kuri sujungia dvi XLOOKUP formules su dvitaškiu ir tada ją suvynioja į SUM funkciją. Rezultatas yra B6: B9 SUM, nes XLOOKUP gali pateikti langelio nuorodą, jei funkcija rodoma šalia operatoriaus, pavyzdžiui, dvitaškio. Norėdami įrodyti, kad tai veikia, keli kiti skaičiai parodys šią formulę dialogo lange Įvertinti formulę.

Aukščiau pateiktame paveikslėlyje matote, kad E4 XLOOKUP grąžins 15 iš langelio B6. XLOOKUP iš E5 grąžins 30 iš B9. Tačiau jei paimsite dvi XLOOKUP funkcijas iš D9 ir D10 langelių ir sujungsite jas su dvitaškiu, XLOOKUP elgsena pasikeis. Užuot grąžinęs 15, pirmasis XLOOKUP grąžina langelio adresą B6!

Norėdami tai įrodyti, aš pasirinkau D7 ir naudoju „Formules“, „Evaluate Formula“. Du kartus paspaudus „Įvertinti“, kita apskaičiuojama dalis yra „XLOOKUP“ („Vyšnia“, A4: A29, B4: B29), kaip parodyta čia.

Tai rodo dialogo langą „Įvertinti formulę“ prieš pat įvertinant pirmąjį „XLOOKUP“. Šis XLOOKUP pasirodo prieš pat dvitaškį.

Paspauskite „Įvertinti“ dar kartą ir nuostabiai, XLOOKUP formulė grąžina $ B $ 6, o ne 15, saugomų B6. Taip atsitinka todėl, kad iškart po šios XLOOKUP formulės yra dvitaškis.

Spustelėkite Įvertinti ir pirmasis XLOOKUP grąžina $ 15, o ne $ 15.

Paspauskite „Vertinti“ dar du kartus, o tarpinė formulė bus = SUM (B6: B9).

Įvertinus antrąjį XLOOKUP, tarpinė formulė yra = SUM (B6: B9).

Tai nuostabus elgesys, apie kurį dauguma žmonių nežino. „Excel“ MVP Charlesas Williamsas man sako, kad jį galima suaktyvinti naudojant bet kurį iš šių trijų operatorių šalia „XLOOKUP“:

  • Dvitaškis
  • Kosmosas (sankryžos operatorius)
  • Kablelis (Sąjungos operatorius)

„XLOOKUP 11“ nauda: dvipusės rungtynės, pvz., INDEX (, MATCH, MATCH)

Visiems mano „VLOOKUP“ draugams „INDEX“ / „MATCH“ žmonės laukė, ar „XLOOKUP“ gali tvarkyti dvipusį mačą. Puiki žinia: ji gali tai padaryti. Bloga žinia: metodika yra šiek tiek kitokia nei tikėtųsi INDEX / MATCH gerbėjai. Tai gali būti šiek tiek per galvą. Tačiau esu tikras, kad jie gali pritaikyti šį metodą.

Dviejų krypčių rungtynėms norite sužinoti, kurioje eilutėje yra sąskaitos numeris A621, nurodytas J3. Taigi, „XLOOKUP“ prasideda pakankamai lengvai: = XLOOKUP (J3, A5: A15. Bet tada jūs turite pateikti rezultatų_dydį. Galite naudoti tą patį triuką kaip ir „XLOOKUP 9 nauda“: visus 12 mėnesių grąžinkite viena aukščiau pateikta forma, bet naudokite jį, jei norite grąžinti vertikalųjį vektorių. Vidinis XLOOKUP ieško J4 mėnesio mėnesio antraštėse B4: G4. Return_array nurodomas kaip B5: G15. Rezultatas yra tas, kad vidinis XLOOKUP pateikia masyvą, panašų į parodytą I10 : I20 žemiau. Kadangi A621 yra penktame paieškos_dailos langelyje, o 104 rastas penktame rezultatų_dydžio langelyje, teisingą atsakymą gausite iš formulės. Žemiau J6 rodo senąjį kelią. J7 grąžina naują kelią.

XLookup J3 sąskaitų sąraše A5: A15. Rezultatų masyvui naudokite XLOOKUP (J4, B4: G4, B5: G15). Šioje formulėje B4: G4 yra mėnesių sąrašas. B5: G15 yra stačiakampis visų sąskaitų visų mėnesių visų verčių masyvas. Kitoje langelyje tik vidinis XLOOKUP parodo, kaip jis grąžina visą gegužės mėnesio reikšmių stulpelį.

XLOOKUP 12 nauda: susumuokite visas paieškos reikšmes vienoje formulėje

Senovės funkcija LOOKUP pasiūlė du keistus triukus. Pirma, jei bandote išsiaiškinti bendrą kaupiamų premijos išlaidų sumą, galite paprašyti LOOKUP ieškoti visų reikšmių vienoje formulėje. Žemiau esančiame paveikslėlyje LOOKUP (C4: C14 atlieka 11 paieškų. Tačiau funkcija LOOKUP nepateikė tikslaus atitikimo ir reikalavo surūšiuoti paieškos lentelę.

Ieškokite 13 verčių ir jas susumuokite. Anksčiau tai veikė su LOOKUP, bet taip pat ir su XLOOKUP. Pirmąjį argumentą nurodykite visas paieškos reikšmes C4: C14. Apvyniokite „XLOOKUP“ funkciją SUM.

Naudodami „XLOOKUP“, galite nurodyti diapazoną, nes „lookup_value“, o „XLOOKUP“ pateiks visus atsakymus. Privalumas yra tas, kad XLOOKUP gali atlikti tikslius atitikmenis.

Apgaulė, naudojant LOOKUP, norint susumuoti visus paieškos rezultatus, pasiteisino tik apytikslėje „Lookup“ atitikties versijoje. Čia „XLOOKUP“ tiksliai atitinka visus L4: L14 vardus ir gauna visus rezultatus.

Premijos patarimas: O kaip su „Twisted LOOKUP“?

„Excel MVP“ žaidėjas Mike'as Girvinas dažnai parodo funkcijos LOOKUP triuką, kai „Lookup_Vector“ yra vertikalus, o „Result_Vector“ - horizontalus. „XLOOKUP“ natūraliai nepalaikys šio triuko. Bet, jei šiek tiek apgaunate ir rezultatus_paveikslą įtraukiate į TRANSPOSE funkciją, galite valdyti susuktą paiešką.

Čia paieškos masyvas yra vertikalus, o rezultatų masyvas - horizontalus. Senoji funkcija LOOKUP gali tai išspręsti, tačiau norint tai padaryti su XLOOKUP, turite bet kurį masyvą suvynioti į TRANSPOSE.

Įdomios straipsniai...