Excel 2020: XLOOKUPi kaksteist eelist - Exceli näpunäited

Uus funktsioon XLOOKUP on Office 365-s kasutusel alates novembrist 2019. Joe McDaid Exceli meeskonnast lõi XLOOKUPi, et ühendada VLOOKUP-i ja INDEX / MATCH-i kasutavaid inimesi. Selles jaotises käsitletakse XLOOKUPi 12 eelist:

  1. Täpne vaste on vaikimisi.
  2. VLOOKUPi täisarvupõhine kolmas argument on nüüd sobiv viide.
  3. IFNA on sisseehitatud puuduvate väärtuste haldamiseks.
  4. XLOOKUPil pole vasakule minnes probleeme.
  5. Leidke järgmine-väiksem või järgmine-suurem vaste tabelit sortimata.
  6. XLOOKUP saab teha HLOOKUPi.
  7. Leidke viimane vaste alt üles otsides.
  8. Metamärgid on vaikimisi välja lülitatud, kuid saate need uuesti sisse lülitada.
  9. Tagastage kõik 12 kuud ühes valemis.
  10. Lahtriviite saab tagastada, kui XLOOKUP on kooloni kõrval, näiteks XLOOKUP (); XLOOKUP ()
  11. Oskab teha kahesuunalist mängu nagu INDEX (, MATCH, MATCH).
  12. Oskab summeerida kõik otsingud ühes valemis, nagu saaks teha LOOKUP.

Siin on süntaks: = XLOOKUP (otsingu_väärtus, otsingu_massiiv, tulemuste_massiiv, (kui_ei_leitud), (mängu_režiim), (otsingu_režiim)).

XLOOKUPi eelis 1: täpne vaste vaikimisi

99% minu VLOOKUP-i valemitest lõpeb täpse vaste tähistamiseks väärtusega FALSE või 0. Kui kasutate alati VLOOKUP-i täpse vaste versiooni, võite hakata funktsiooni match_mode oma XLOOKUP-funktsioonist välja jätma.

Järgmisel joonisel otsite lahtrist A4 üles W25-6. Sa tahad seda üksust otsida L8: L35-st. Kui see on leitud, soovite veerust N. vastavat hinda. Vaste_režiimiks pole vaja määrata valet, kuna XLOOKUP on vaikimisi täpne vaste.

XLOOKUP väärtus A4 formaadis. Vaadake L8: L35. Tagastage vastav hind N8: N35-st.

XLOOKUPi eelis 2: Tulemuste massiiv on viide täisarvu asemel

Mõelge VLOOKUP-i valemile, mida kasutaksite enne XLOOKUP-i. Kolmas argument oleks olnud 3, mis näitab, et soovite tagastada 3. veeru. Alati oli oht, et abitu töökaaslane oleks teie tabelisse veeru lisanud (või kustutanud). Kui tabelis on lisaveeru, hakkab hinda tagastanud VLOOKUP kirjeldust tagastama. Kuna XLOOKUP osutas lahtriviidele, kirjutab valem ennast edasi, et näidata pidevalt veerus O olevat hinda.

Vana VLOOKUP nurjuks, kui keegi sisestaks otsingutabelisse uue veeru. XLOOKUP töötab edasi.

XLOOKUPi eelis 3: IFNA on sisse ehitatud valikulise argumendina

Kardetud viga # N / A tagastatakse, kui teie otsinguväärtust tabelist ei leita. Varem peate # N / A asendamiseks millegi muuga kasutama VLOOKUPi ümber keeratud IFERRORi või IFNA-d.

Kui üksust ei leita, tagastab see väärtuse VLOOKUP või XLOOKUP …

Tänu Rico ettepanekule minu YouTube'i kanalil lisas Exceli meeskond valikulise neljanda argumendi if_not_found. Kui soovite need # N / A vead asendada nulliga, lisage neljanda argumendina lihtsalt 0. Või võite kasutada mõnda teksti, näiteks "Väärtust ei leitud".

XLOOKUPi valikuline neljas argument on "kui ei leitud". Pange sinna 0 või "Ei leitud".

XLOOKUPi eelis 4: pole mingit probleemi vaadata võtmeväljast vasakule

VLOOKUP ei saa klahviväljast vasakule vaadata ilma VLOOKUP-i kasutamata (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Rakenduse XLOOKUP kasutamisel pole probleemi, kui Tulemite_diagramm jääb otsingutulemist vasakule.

XLOOKUPi kasutamisel pole veerust F kategooria tagastamisel probleeme, kui otsitakse veerust G. osade numbreid. See oli alati VLOOKUPi nõrkus: see ei saanud vasakule vaadata.

XLOOKUPi eelis 5: järgmine-väiksem või järgmine-suurem vaste sortimata

VLOOKUPil oli võimalus otsida täpne vaste või lihtsalt väiksem väärtus. Võite jätta neljanda argumendi VLOOKUPist välja või muuta vale väärtuseks Tõene. Selle toimimiseks tuli otsingu tabel järjestada kasvavas järjestuses.

VLOOKUPi ligikaudse vaste versiooni näide. Iga müük vahemikus 10 tuhat kuni 20 tuhat saab boonuse 12 dollarit.

Kuid VLOOKUPil polnud võimalust täpset vastet või järgmist suuremat üksust tagastada. Selleks tuli üle minna MATCH-i kasutamisele koos mängurežiimiga -1 ja pidid olema ettevaatlik, et otsingutabel oleks järjestatud kahanevalt.

XLOOKUPi valikuline viies argument match_mode saab otsida ainult täpset vastet, võrdne või lihtsalt väiksem, võrdne või lihtsalt suurem. Pange tähele, et XLOOKUPi väärtused on mõttekamad kui mängus MATCH:

  • -1 leiab väärtuse, mis on võrdne või lihtsalt väiksem
  • 0 leida täpne vaste
  • 1 leiab väärtuse, mis on võrdne või lihtsalt suurem.

Kuid kõige hämmastavam osa: otsingu tabelit ei pea sortima ja mis tahes match_mode töötab.

Allpool leiate mängu match_mode -1 järgmise väiksema üksuse.

XLOOKUPi viies argument on Match_Mode. 0 on täpse vaste jaoks. Negatiivset kasutatakse täpse vaste või järgmise väiksema üksuse jaoks. Positiivne 1 on täpse vaste või järgmise suurema üksuse jaoks. 2 tähistab metamärki. Selleks et peegeldada seda, mida neljandas argumendis toimiks VLOOKUP koos True väärtusega, pange XLOOKUP-i argumendiks match_mode negatiivne.

Siit leiab mängu_režiim 1, millist sõidukit on vaja, olenevalt peoliste arvust. Pange tähele, et otsingu tabelit ei sorteerita reisijate järgi ja sõiduki nimi asub võtmest vasakul.

XLOOKUP saab teha midagi, mida VLOOKUP ei suutnud: leida täpne vaste või lihtsalt suurem. Sellisel juhul on reisifirmal broneeringute nimekiri. Reisijate arvu põhjal näitab otsitabel, millist sõidukit nende inimeste jaoks vajate.

Tabelis on kirjas:

  • Buss mahutab 64 inimest
  • Autosse mahub 4 inimest
  • Motorcyle mahutab 1 inimese
  • Tour Van mahutab 12 inimest
  • Kaubik mahutab 6 inimest.

Boonusena sorteeritakse andmed sõiduki järgi (vanas lahenduses tuleks MATCHi abil tabel järjestada kahanevalt mahutavuse järgi. Samuti: sõiduk on mahust vasakul.

XLOOKUPi eelis 6: XLOOKUP asendab külgsuunas HLOOKUPi

Otsingu_juur ja tulemuste_raa võib olla XLOOKUP-iga horisontaalne, mis muudab HLOOKUP-i asendamise lihtsaks.

Siin on otsingu tabel horisontaalne. Varem nõudis see HLOOKUP-i, kuid XLOOKUP saab hakkama külgsuunas liikuva tabeliga.

XLOOKUPi eelis 7: otsige alt viimast mängu

Mul on YouTube'is vana video, mis vastab Briti hobusekasvanduse küsimusele. Neil oli sõidukipark. Iga kord, kui sõiduk tuli kütuse või hoolduse järele, logisid nad arvutustabelisse sõiduki, kuupäeva ja läbisõidu. Nad tahtsid leida iga sõiduki jaoks teadaoleva viimase läbisõidu. Ehkki Excel-2017 ajastu MAXIFS võib selle täna lahendada, oli paljude aastate taguseks lahenduseks arukas valem, mis kasutas funktsiooni LOOKUP ja hõlmas nulliga jagamist.

Täna võimaldab XLOOKUPi valikuline kuues argument määrata, et otsingut tuleks alustada andmekogumi alt.

Leidke loendist viimane vaste.

Märge

Kuigi see on suur edasiminek, võimaldab see teil leida ainult esimese või viimase mängu. Mõned inimesed lootsid, et see võimaldab teil leida teise või kolmanda vaste, kuid see pole argumendi search_mode eesmärk.

Ettevaatust

Ülaltoodud joonis näitab, et vanas binaarotsingus on otsingurežiime. Joe McDaid soovitab neid mitte kasutada. Esiteks on 2018. aastast pärit täiustatud otsimisalgoritm piisavalt kiire, et kiirusest märkimisväärset kasu pole. Teiseks on oht, et abitu töökaaslane sorteerib otsingu tabelit ja tutvustab valesid vastuseid.

XLOOKUPi eelis 8: metamärgid on vaikimisi välja lülitatud

Enamik inimesi ei saanud aru, et VLOOKUP käsitleb tärni, küsimärki ja tildet metamärkidena, nagu on kirjeldatud jaotises "# 51 Kasuta metamärke VLOOKUPis" lk 143. XLOOKUPi korral on metamärgid vaikimisi välja lülitatud. Kui soovite, et XLOOKUP käsitaks neid märke metamärgina, kasutage 2 mängu_režiimina.

Väga vähesed inimesed mõistsid, et VLOOKUP käsitleb otsingu väärtuses olevaid tärne kui metamärke. Vaikimisi ei kasuta XLOOKUP metamärke, kuid võite sundida teda käituma nagu VLOOKUP, kui kasutate vasterežiimi 2: metamärgi vaste.

XLOOKUPi eelis 9: tagastage kõik 12 kuud ühe vormelina!

See on tõesti dünaamiliste massiivide eelis, kuid see on minu lemmik põhjus XLOOKUPi armastamiseks. Kui peate otsingu korras tagasi saatma kõik 12 kuud, tagastab B6-sse sisestatud üks ristkülikukujulise tagastusega valemiga mitu valemit. Need tulemused valguvad külgnevatesse lahtritesse.

Alloleval joonisel tagastab üks B7-sse sisestatud valem kõik 12 vastust, mis on näidatud B7-s: M7.

Üks veerg Jaanuar XLOOKUP tagastab jaanuari kuni detsembri numbrid. Selleks määratakse tulemuste_kaart 12 veeruga.

XLOOKUPi eelis 10: suudab tagastada lahtriviite, kui see asub kooloniga

See on keeruline, kuid ilus. Varem oli seitse funktsiooni, mis muutusid raku väärtuse tagastamisest lahtriviite tagastamiseks, kui funktsioon puudutas käärsoole. Näite saamiseks vaadake jaotist A2: INDEX () kasutamine mittelenduva OFFSETina. XLOOKUP on selle käitumise pakkumiseks funktsioon kaheksas, liitudes CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET ja SWITCH.

Mõelge järgmisele joonisele. Keegi valib kirsi E4-s ja joonis E5-s. Soovite valemit, mis summeeriks kõik alates B6 kuni B9.

Joonisel on näidatud kaks XLOOKUP-valemit kahes lahtris. Esimene tagastab lahtrist B6 15. Teine proovib B9-st uuesti 30. Kuid siis on kolmandas lahtris valem, mis ühendab kaks XLOOKUP-i valemit kooloniga ja mähib selle seejärel funktsiooni SUM. Tulemuseks on summa B6: B9, sest XLOOKUP võib tagastada lahtriviite, kui funktsioon kuvatakse operaatori, näiteks kooloni kõrval. Selle tõestamiseks näitavad mitmed järgmised joonised seda valemit valemi hindamise dialoogis.

Ülaltoodud joonisel näete, et E4 XLOOKUP tagastab lahtrist B6 15. XLOOKUP E5 tagastab B9-lt 30. Kui aga võtate lahtritest D9 ja D10 kaks funktsiooni XLOOKUP ja panete need omavahel käärsoolega kokku, muutub XLOOKUPi käitumine. 15 tagastamise asemel tagastab esimene XLOOKUP lahtriaadressi B6!

Selle tõestamiseks olen valinud D7 ja kasutan valemeid, valemit hindama. Pärast kaks korda nuppu Hindamine nuppu on järgmine arvutatav osa XLOOKUP ("Cherry", A4: A29, B4: B29), nagu siin näidatud.

See näitab dialoogi Vormeli hindamine vahetult enne esimese XLOOKUPi hindamist. See XLOOKUP ilmub vahetult enne koolonit.

Vajutage uuesti nuppu Hindamine ja hämmastavalt, valem XLOOKUP tagastab B6-sse salvestatud 15 asemel $ B $ 6. See juhtub, kuna selle XLOOKUP-valemi järgi on kohe jämesool.

Klõpsake nuppu Hinda ja esimene XLOOKUP tagastab 15 B asemel 6 USD.

Vajutage Hinda veel kaks korda ja vahevalemiks on = SUM (B6: B9).

Pärast teise XLOOKUPi hindamist on vahevalem = SUM (B6: B9).

See on hämmastav käitumine, millest enamik inimesi ei tea. Exceli MVP Charles Williams ütleb mulle, et selle saab käivitada mis tahes neist kolmest operaatorist XLOOKUPi kõrval:

  • Käärsool
  • Kosmos (ristmikuoperaator)
  • Koma (liidu operaator)

XLOOKUPi eelis 11: kahesuunaline mäng nagu INDEX (, MATCH, MATCH)

Kõigi minu VLOOKUPi sõprade puhul on INDEX / MATCH inimesed oodanud, kas XLOOKUP saab hakkama kahesuunalise matšiga. Suurepärane uudis: sellega saab hakkama. Halb uudis: metoodika on veidi teistsugune, kui INDEX / MATCH fännid ootaksid. See võib olla veidi üle nende peade. Kuid olen kindel, et nad saavad selle meetodi juurde jõuda.

Kahepoolse mängu jaoks soovite leida, milline rida sisaldab J3-s näidatud kontonumbrit A621. Niisiis, XLOOKUP algab piisavalt lihtsalt: = XLOOKUP (J3, A5: A15. Kuid siis peate esitama result_array. Võite kasutada sama trikki nagu XLOOKUPi hüves 9: tagastage kõik 12 kuud ülaltoodud ühe valemiga, kuid kasutage seda vertikaalse vektori tagastamiseks. Sisemine XLOOKUP otsib J4 kuud kuu pealkirjades B4: G4. Tagasivõtte_arv on määratud kui B5: G15. Tulemuseks on see, et sisemine XLOOKUP tagastab massiivi, nagu on näidatud I10-s : I20 allpool. Kuna A621 on otsingu_suuruse viiendast lahtrist ja 104 on leitud tulemuse_jada viiendast lahtrist, saate valemilt õige vastuse. Allpool näitab J6 vanaviisi. J7 tagastab uue tee.

XLookup J3 kontode loendis A5: A15. Tulemuste massiivi jaoks kasutage funktsiooni XLOOKUP (J4, B4: G4, B5: G15). Selles valemis on B4: G4 kuude loend. B5: G15 on kõigi kontode kõigi kuude ristkülikukujuline väärtuste massiiv. Teises lahtris näitab lihtsalt sisemine XLOOKUP, kuidas see tagastab kogu mai väärtuste veeru.

XLOOKUPi eelis 12: summeerige kõik otsinguväärtused ühes valemis

Iidne funktsioon LOOKUP pakkus kahte kummalist trikki. Esiteks, kui proovite välja selgitada kogutavate boonuskulude kogusumma, võite paluda LOOKUPil otsida kõik väärtused ühest valemist. Alloleval pildil otsib LOOKUP (C4: C14) 11 otsingut. Kuid funktsioon LOOKUP ei pakkunud täpset vastet ja nõudis otsingutabeli sortimist.

Otsige 13 väärtust ja summeerige need. See töötas varem LOOKUP-iga, kuid töötab ka XLOOKUP-iga. Esimese argumendina määrake kõik otsinguväärtused C4: C14. Pakkige funktsioon XLOOKUP funktsiooni SUM.

Funktsiooniga XLOOKUP saate määrata vahemiku otsingu_väärtusena ja XLOOKUP tagastab kõik vastused. Eeliseks on see, et XLOOKUP suudab teha täpseid vasteid.

LOOKUPi kasutamine kõigi otsimistulemuste summeerimiseks kasutas trikki ainult otsingu ligikaudse vaste versiooniga. Siin teeb XLOOKUP täpse vaste kõigile L4: L14 nimedele ja saab kõigi tulemuste kokku.

Boonusenipp: kuidas on lood keerdunud otsimisega?

Exceli MVP Mike Girvin näitab sageli funktsiooni LOOKUP nippi, kus otsingu_vektor on vertikaalne ja tulemuse_vektor on horisontaalne. XLOOKUP ei toeta seda trikki loomupäraselt. Kuid kui te natuke petate ja funktsiooni TRANSPOSE ümber murrate tulemused_kaardiga, saate hallata keeratud otsingut.

Siin on otsingu massiiv vertikaalne ja tulemuste massiiv horisontaalne. Vana funktsioon LOOKUP saab sellega hakkama, kuid selleks, et seda teha rakendusega XLOOKUP, peate mistahes massiivi pakkima teksti TRANSPOSE.

Huvitavad Artiklid...