agwebrunner.com

Exceli uus XLOOKUP asendab VLOOKUPi, pakkudes võimsa asenduse ühele Exceli populaarseimale funktsioonile. See uus funktsioon lahendab mõned VLOOKUPi piirangud ja sellel on lisafunktsionaalsus. Siin on, mida peate teadma.

Mis on XLOOKUP?

Uuel funktsioonil XLOOKUP on lahendusi VLOOKUPi suurimatele piirangutele. Lisaks asendab see ka HLOOKUPi. Näiteks saab XLOOKUP vaadata vasakule, vaikimisi täpse vaste ja võimaldab teil veerunumbri asemel määrata lahtrivahemiku. VLOOKUP pole nii lihtne kasutada ega sama mitmekülgne. Näitame teile, kuidas see kõik töötab.

Praegu on XLOOKUP saadaval ainult Insidersi programmi kasutajatele. Igaüks võib liituda Insidersi programmiga, et pääseda juurde uusimatele Exceli funktsioonidele kohe, kui need kättesaadavaks saavad. Varsti hakkab Microsoft seda kõigile Office 365 kasutajatele levitama.

Funktsiooni XLOOKUP kasutamine

Sukeldume otse XLOOKUPi näite abil. Võtke allpool toodud näidisandmed. Soovime tagastada osakonna veerust F iga veeru A ID kohta.

See on klassikaline täpse vaste otsimise näide. Funktsioon XLOOKUP nõuab vaid kolme teavet.

Alloleval pildil on XLOOKUP kuue argumendiga, kuid täpse vaste saamiseks on vajalikud ainult kolm esimest. Nii et keskendume neile:

• Otsinguväärtus: Mida otsite.
• Otsingupilt: Kust otsida.
• Return_array: vahemik, mis sisaldab tagastatavat väärtust.

Selle näite puhul töötab järgmine valem: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ F $ 2: $ F $ 8)

Uurime nüüd paari eelist, mida XLOOKUPil siin VLOOKUPi ees on.

Veeru indeksinumbrit pole enam

VLOOKUPi kurikuulus kolmas argument oli täpsustada tabeli massiivi tagastatava teabe veeru number. See pole enam probleem, kuna XLOOKUP võimaldab teil valida vahemiku, kust naasta (selle näite veerg F).

Ja ärge unustage, et XLOOKUP saab erinevalt VLOOKUPist vaadata valitud lahtrist vasakule jäävaid andmeid. Lisateavet selle kohta allpool.

Uute veergude lisamisel pole teil enam katkise valemi probleemi. Kui see juhtuks teie arvutustabelis, kohanduks tagastusvahemik automaatselt.

Täpne vaste on vaikimisi

VLOOKUPi õppimisel oli alati segane, miks taheti täpset vastet määrata.

Õnneks on XLOOKUP vaikimisi täpne vaste - palju tavalisem põhjus otsinguvalemi kasutamiseks). See vähendab vajadust sellele viiendale argumendile vastata ja tagab vähem valemiga kasutajate vigu.

Nii et lühidalt öeldes esitab XLOOKUP vähem küsimusi kui VLOOKUP, on kasutajasõbralikum ja ka vastupidavam.

XLOOKUP saab vaadata vasakule

Otsinguvälja valimise võimalus muudab XLOOKUPi mitmekülgsemaks kui VLOOKUP. XLOOKUPi korral pole tabeliveergude järjekord oluline.

VLOOKUP oli piiratud, otsides tabeli kõige vasakpoolsest veerust ja naastes seejärel määratud arvu veergude juurest paremale.

Allpool toodud näites peame otsima ID (veerg E) ja tagastama inimese nime (veerg D).

Selle saavutamiseks on järgmine valem: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8)

Mida teha, kui seda ei leita

Otsimisfunktsioonide kasutajad tunnevad väga hästi # N / A tõrketeadet, mis tervitab neid, kui funktsioon VLOOKUP või funktsioon MATCH ei leia vajalikku. Ja sageli on selleks loogiline põhjus.

Seetõttu uurivad kasutajad kiiresti, kuidas seda viga varjata, kuna see pole õige ega kasulik. Ja muidugi on selleks ka viise.

XLOOKUPil on oma sisseehitatud argument "kui ei leitud" selliste vigade käsitlemiseks. Vaatame seda toimimas eelmise näite korral, kuid valesti sisestatud ID-ga.

Järgmine valem kuvab tõrketeate asemel teksti „Vale ID”:= XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8, "Vale ID")

Funktsiooni XLOOKUP kasutamine vahemiku otsimiseks

Kuigi see pole nii levinud kui täpne vaste, on otsinguvalemi väga tõhus kasutamine väärtuste otsimine vahemikest. Võtke järgmine näide. Soovime tagastada allahindluse sõltuvalt kulutatud summast.

Seekord me konkreetset väärtust ei otsi. Peame teadma, kus veeru B väärtused jäävad veeru E vahemikku. See määrab teenitud allahindluse.

Rakendusel XLOOKUP on valikuline viies argument (pidage meeles, et see on vaikimisi täpne vaste) nimega vaste režiim.

Näete, et XLOOKUPil on ligilähedaste vastetega suuremad võimalused kui VLOOKUPil.

Võimalik on leida lähim vaste, mis on väiksem kui (-1) või lähim suurem kui (1) otsitud väärtus. Samuti on võimalus kasutada metamärke (2), näiteks? või *. See seade pole vaikimisi sisse lülitatud, nagu see oli VLOOKUP-i puhul.

Selle näite valem tagastab otsitava väärtuse lähima vähem, kui täpset vastet ei leita: = XLOOKUP (B2, $ E $ 3: $ E $ 7, $ F $ 3: $ F $ 7 ,, - 1)

Lahtris C7 on aga viga, kus tagastatakse tõrge # N / A (argumenti „kui ei leitud” ei kasutatud). See oleks pidanud tagastama 0% allahindluse, sest 64 kulutamine ei vasta ühegi allahindluse kriteeriumidele.

Funktsiooni XLOOKUP teine ​​eelis on see, et see ei nõua otsimisvahemiku kasvavas järjekorras nagu VLOOKUP.

Sisestage otsingutabeli alaossa uus rida ja seejärel avage valem. Laiendage kasutatud vahemikku, klõpsates ja lohistades nurki.

Valem parandab vea kohe. See ei ole probleem, kui vahemiku lõpus on „0“.

Isiklikult sorteeriksin tabeli ikkagi otsingu veeru järgi. Kui allservas oleks „0“, läheks see hulluks. Kuid tõsiasi, et valem ei purunenud, on hiilgav.

XLOOKUP Asendab ka funktsiooni HLOOKUP

Nagu mainitud, on funktsioon HLOOKUP ka siin, et asendada HLOOKUP. Üks funktsioon kahe asendamiseks. Suurepärane!

Funktsioon HLOOKUP on horisontaalne otsing, mida kasutatakse ridade järgi otsimiseks.

Mitte nii tuntud kui selle vend-vend VLOOKUP, kuid kasulik näiteks allpool toodud näidete jaoks, kus päised asuvad veerus A ja andmed asuvad 4. ja 5. rida.

XLOOKUP võib vaadata mõlemas suunas - alla veergude ja ka mööda ridu. Enam pole meil vaja kahte erinevat funktsiooni.

Selles näites kasutatakse valemiga lahtris A2 nimega seotud müügiväärtuse tagastamist. Nime leidmiseks vaadatakse 4. rida ja tagastatakse 5. rea väärtus: = XLOOKUP (A2, B4: E4, B5: E5)

XLOOKUP saab vaadata alt üles

Tavaliselt peate loendi jahtima, et leida väärtus esimest (sageli ainult) esinemist. XLOOKUPil on kuues argument nimega otsingurežiim. See võimaldab meil vahetada otsingu alustamiseks alt ja otsida loendit, et leida selle asemel väärtus viimase esinemise kohta.

Allpool toodud näites tahaksime leida veeru A iga toote varude taseme.

Otsimistabel on kuupäevade järjekorras ja toote kohta on mitu laokontrolli. Soovime tagastada laovarude taseme eelmisest kontrollimiskorrast (toote ID viimane esinemine).

Funktsiooni XLOOKUP kuues argument pakub nelja võimalust. Oleme huvitatud valiku „Otsi viimasest esimeseni” kasutamisest.

Täidetud valemit kuvatakse siin: = XLOOKUP (A2, $ E $ 2: $ E $ 9, $ F $ 2: $ F $ 9 ,,, - 1)

Selles valemis eirati neljandat ja viiendat argumenti. See on valikuline ja soovisime täpse vaste vaikeväärtust.

Kokku võtma

Funktsioon XLOOKUP on pikisilmi oodatud funktsioonide VLOOKUP ja HLOOKUP järeltulija.

Selles artiklis kasutati erinevaid näiteid, et näidata XLOOKUPi eeliseid. Üks neist on see, et XLOOKUPi saab kasutada nii lehtede, töövihikute kui ka laudade vahel. Näited jäid artiklis arusaamiseks lihtsamaks.

Dünaamiliste massiivide peagi Excelisse sissetoomise tõttu võib see tagastada ka väärtuste vahemiku. See on kindlasti midagi, mida tasub edasi uurida.

VLOOKUPi päevad on nummerdatud. XLOOKUP on kohal ja on varsti de facto otsingu valem.