Vaadake videot - funktsiooni Excel XLOOKUP (10 XLOOKUPi näidet)
Excel XLOOKUP funktsioon on lõpuks kohale jõudnud.
Kui olete kasutanud funktsiooni VLOOKUP või INDEX/MATCH, olen kindel, et teile meeldib XLOOKUP -funktsiooni pakutav paindlikkus.
Selles õpetuses käsitlen kõike, mida on vaja teada funktsiooni XLOOKUP kohta, ja mõningaid näiteid, mis aitavad teil seda parimal viisil kasutada.
Nii et alustame!
Mis on XLOOKUP?
XLOOKUP on Office 365 uus funktsioon ja see on funktsiooni VLOOKUP/HLOOKUP uus ja täiustatud versioon.
See teeb kõike, mida VLOOKUP varem tegi, ja palju muud.
XLOOKUP on funktsioon, mis võimaldab kiiresti otsida andmestikust väärtust (vertikaalne või horisontaalne) ja tagastada vastava väärtuse mõnes teises reas/veerus.
Näiteks kui teil on eksamil õpilaste hinded, saate XLOOKUPi abil kiiresti kontrollida, kui palju õpilane on õpilase nime kasutades hinnanud.
Selle funktsiooni jõud muutub veelgi selgemaks, kui süvenen mõnda XLOOKUPi näited hiljem selles õpetuses.
Kuid enne näidete juurde asumist tekib suur küsimus - kuidas ma saan juurdepääsu XLOOKUPile?
Kuidas pääseda juurde XLOOKUPile?
Praeguse seisuga on XLOOKUP saadaval ainult Office 365 kasutajatele.
Seega, kui kasutate Exceli varasemaid versioone (2010/2013/2016/2019), ei saa te seda funktsiooni kasutada.
Samuti pole ma kindel, kas see avaldatakse kunagi varasemate versioonide jaoks või mitte (võib-olla saab Microsoft luua lisandmooduli Power Query jaoks). Kuid praegu saate seda kasutada ainult siis, kui kasutate Office 365.
Office 365 -le üleminekuks klõpsake siin
Kui kasutate juba Office 365 (kodu-, isiklik või ülikooli väljaanne) ja teil pole sellele juurdepääsu, võite minna vahekaardile Fail ja seejärel klõpsata valikul Konto.
Seal oleks Office Insideri programm ja saate klõpsata ja liituda Office Insider programmiga. See annab teile juurdepääsu funktsioonile XLOOKUP.
Eeldan, et XLOOKUP on peagi saadaval kõigis Office 365 versioonides.
Märkus. XLOOKUP on saadaval ka Office 365 for Mac ja Exceli veebi jaoks (Excel võrgus)Funktsiooni XLOOKUP süntaks
Allpool on funktsiooni XLOOKUP süntaks:
= XLOOKUP (otsinguväärtus, otsimismassiiv, tagastamismassiiv, [kui_ ei leitud], [vastamisrežiim], [otsingurežiim])
Kui olete rakendust VLOOKUP kasutanud, märkate, et süntaks on üsna sarnane, muidugi koos mõne suurepärase lisafunktsiooniga.
Ärge muretsege, kui süntaks ja argumendid tunduvad liiga palju. Ma katan need mõne lihtsa XLOOKUP näitega hiljem selles õpetuses, mis muudab selle kristallselgeks.Funktsioon XLOOKUP võib esitada 6 argumenti (3 kohustuslikku ja 3 valikulist):
- otsinguväärtus - väärtus, mida otsite
- lookup_array - massiiv, milles otsite otsinguväärtust
- return_array - massiiv, millest soovite väärtuse hankida ja tagastada (vastab positsioonile, kust otsinguväärtus leitakse)
- [if_not_found] - tagastatav väärtus juhuks, kui otsinguväärtust ei leita. Kui te seda argumenti ei määra, tagastatakse viga #N/A
- [match_mode] - Siin saate määrata soovitud vaste tüübi:
- 0 - Täpne vaste, kus otsingu_väärtus peaks täpselt vastama otsimismassiivi väärtusele. See on vaikevalik.
- -1 - Otsib täpset vastet, kuid kui see leitakse, tagastab järgmise väiksema üksuse/väärtuse
- 1 - Otsib täpset vastet, kuid kui see leitakse, tagastab järgmise suurema üksuse/väärtuse
- 2 - Osalise sobitamise tegemiseks, kasutades metamärke (* või ~)
- [otsingurežiim] - Siin saate määrata, kuidas funktsioon XLOOKUP otsimismassiivist otsima peaks
- 1 - See on vaikesuvand, kus funktsioon hakkab otsingu_väärtust otsima otsinguväärtust ülaosast (esimene üksus) kuni alumise (viimane element)
- -1 - Kas otsida alt üles. Kasulik, kui soovite otsimismassiivist leida viimase sobiva väärtuse
- 2 - Teostab binaarotsingu, kus andmed tuleb sortida kasvavas järjekorras. Kui seda pole sorteeritud, võib see anda tõrkeid või valesid tulemusi
- -2 - Teostab binaarotsingu, kus andmed tuleb sortida kahanevas järjekorras. Kui seda pole sorteeritud, võib see anda tõrkeid või valesid tulemusi
Funktsioonide XLOOKUP näited
Läheme nüüd huvitava osa juurde - mõned praktilised XLOOKUPi näited.
Need näited aitavad teil paremini mõista, kuidas XLOOKUP toimib, kuidas see erineb VLOOKUPist ja INDEX/MATCHist ning selle funktsiooni mõningaid täiustusi ja piiranguid.
Näidisfaili allalaadimiseks ja selle järgimiseks klõpsake siin
Näide 1: Tooge otsinguväärtus
Oletame, et teil on järgmine andmekogum ja soovite tuua Gregi matemaatika skoori (otsinguväärtus).
Allpool on valem, mis seda teeb:
= XLOOKUP (F2, A2: A15, B2: B15)
Ülaltoodud valemis kasutasin just kohustuslikke argumente, kus see otsib nime (ülevalt alla), leiab täpse vaste ja tagastab vastava väärtuse B2: B15.
Üks ilmne erinevus funktsioonidel XLOOKUP ja VLOOKUP on otsimismassiivi käsitlemise viis. VLOOKUPis on teil kogu massiiv, kus otsingu väärtus asub kõige vasakpoolses veerus, ja seejärel määrate veeru numbri, kust soovite tulemuse tuua. XLOOKUP seevastu võimaldab teil valida lookup_array ja return_array eraldiÜks otsene eelis, kui otsimismassiivi ja tagasiside massiiv on eraldi argumentidena, tähendab, et nüüd saate seda teha vaata vasakule. VLOOKUPil oli see piirang, kus saate ainult üles otsida ja leida väärtuse, mis on paremal. Kuid XLOOKUPiga on see piirang kadunud.
Siin on näide. Mul on sama andmestik, kus nimi on paremal ja return_range vasakul.
Allpool on valem, mille abil saan matemaatikas Gregi skoori (see tähendab otsimist otsinguväärtusest vasakule)
= XLOOKUP (F2, D2: D15, A2: A15)
XLOOKUP lahendab veel ühe olulise probleemi - kui sisestate uue veeru või liigutate veerge ringi, oleksid saadud andmed siiski õiged. VLOOKUP puruneb või annab vale tulemuse sellistel juhtudel, kui veeruindeksi väärtus on enamikul juhtudel kõvasti kodeeritud.
Näide 2: otsige ja tooge kogu kirje
Võtame näiteks samad andmed.
Sel juhul ei taha ma lihtsalt Gregi tulemust matemaatikas tuua. Ma tahan saada kõikide ainete hinded.
Sel juhul saan kasutada järgmist valemit:
= XLOOKUP (F2, A2: A15, B2: D15)
Ülaltoodud valem kasutab vahemikku return_array, mis on rohkem kui veerg (B2: D15). Nii et kui otsinguväärtus leitakse parameetrist A2: A15, tagastab valem tagastamismassiivi kogu rea.
Samuti ei saa kustutada ainult lahtreid, mis kuuluvad automaatselt massiivi. Selles näites ei saa te H2 või I2 kustutada. Kui proovite, ei juhtu midagi. Kui valite need lahtrid, on valemiriba valem hall, mis näitab, et seda ei saa muuta.
Saate kustutada valemi lahtris G2 (kuhu me selle algselt sisestasime), see kustutab kogu tulemuse.
See on kasulik täiustus, nagu varem VLOOKUPi puhul, peate veerunumbri iga valemi jaoks eraldi määrama.
Näide 3: kahesuunaline otsing XLOOKUPi abil (horisontaalne ja vertikaalne otsing)
Allpool on andmekogum, kus tahan teada Gregi tulemust matemaatikas (teema lahtris G2).
Seda saab teha kahesuunalise otsingu abil, kus otsin nime veerust A ja teema nime realt 1. Selle kahepoolse otsingu eeliseks on see, et tulemus ei sõltu õppeaine nime õpilase nimest. Kui ma muudan teema nimeks keemia, siis see kahesuunaline XLOOKUP valem töötaks ikkagi ja annaks mulle õige tulemuse.
Allpool on valem, mis teostab kahesuunalise otsingu ja annab õige tulemuse:
= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))
See valem kasutab pesastatud XLOOKUP -i, kus kõigepealt kasutan seda, et tuua kõik õpilase märgid lahtrisse F2.
Seega on XLOOKUPi (F2, A2: A15, B2: D15) tulemus {21,94,81}, mis on antud juhul Gregi skooritud märkide massiiv.
Seejärel kasutatakse seda uuesti välise XLOOKUP valemi tagastamismassiivina. Välises XLOOKUP valemis otsin teema nime (mis on lahtris G1) ja otsingumassiiv on B1: D1.
Kui teema nimi on matemaatika, tõmbab see välimine XLOOKUP -valem tagasivõtmismassiivi esimese väärtuse, mis on selles näites {21,94,81}.
See teeb sama, mida siiani saavutati kombinatsiooni INDEX ja MATCH abil
Näidisfaili allalaadimiseks ja selle järgimiseks klõpsake siin
Näide 4: Kui otsinguväärtust ei leita (veakäsitlus)
Vigade käsitlemine on nüüd XLOOKUP valemile lisatud.
Funktsiooni XLOOKUP neljas argument on [if_not_found], kus saate määrata, mida soovite juhuks, kui otsingut ei leita.
Oletame, et teil on allpool näidatud andmekogum, kust soovite matemaatika skoori saada juhuks, kui vaste, ja kui nime ei leita, soovite tagasi saata - "Ei ilmunud"
Seda teeb järgmine valem:
= XLOOKUP (F2, A2: A15, B2: B15, "Ei ilmunud")
Sel juhul olen kõvasti kodeerinud, mida tahan saada juhuks, kui vastet pole. Võite kasutada ka lahtri viite lahtrile või valemile.
Näide 5: pesastatud XLOOKUP (otsing mitmes vahemikus)
[If_not_found] argumendi geniaalne omadus on see, et see võimaldab teil seda kasutada pesastatud XLOOKUP valem.
Oletame näiteks, et teil on kaks eraldi loendit, nagu allpool näidatud. Kuigi mul on need kaks tabelit samal lehel, saate neid eraldi lehtedena või isegi töövihikuna.
Allpool on pesastatud XLOOKUP valem, mis kontrollib mõlema tabeli nime ja tagastab vastava väärtuse määratud veerust.
= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))
Ülaltoodud valemis olen kasutanud argumenti [if_not_found] teise XLOOKUP valemi kasutamiseks. See võimaldab teil lisada teise XLOOKUPi samasse valemisse ja skannida kahte tabelit ühe valemiga.
Ma pole kindel, mitu pesastatud XLOOKUP -i saate valemis kasutada. Proovisin kuni kümneni ja see töötas, siis loobusin 🙂
Näide 6: leidke viimane sobiv väärtus
Seda oli hädasti vaja ja XLOOKUP tegi selle võimalikuks. Nüüd ei pea te leidma keerukaid viise, kuidas saada vahemikku viimast sobivat väärtust.
Oletame, et teil on allpool näidatud andmekogum ja soovite kontrollida, millal võeti igas osakonnas viimati tööle töötaja ja milline oli rendikuupäev.
Allolev valem otsib iga osakonna viimase väärtuse ja annab viimase rendi nime:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)
Ja järgmine valem annab iga osakonna viimase rentimise kuupäeva:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)
Kuna XLOOKUPil on sisseehitatud funktsioon otsingu suuna määramiseks (esimesest viimaseks või viimasest esimeseks), tehakse seda lihtsa valemiga. Vertikaalsete andmete korral näevad VLOOKUP ja INDEX/MATCH alati ülevalt alla, kuid XLOOKUP -iga ja võivad määrata suuna ka alt üles.
Näide 7: ligikaudne vaste XLOOKUPiga (maksumäära leidmine)
Veel üks märkimisväärne täiustus XLOOKUPiga on see, et nüüd on neli vasturežiimi (VLOOKUPil on 2 ja MATCH -l 3).
Otsinguväärtuse sobitamiseks saate määrata ühe neljast argumendist.
- 0 - Täpne vaste, kus otsingu_väärtus peaks täpselt vastama otsimismassiivi väärtusele. See on vaikevalik.
- -1 - Otsib täpset vastet, kuid kui see leitakse, tagastab järgmise väiksema üksuse/väärtuse
- 1 - Otsib täpset vastet, kuid kui see leitakse, tagastab järgmise suurema üksuse/väärtuse
- 2 - Osalise sobitamise tegemiseks, kasutades metamärke (* või ~)
Allpool on mul andmestik, kust tahan leida iga inimese vahendustasu - ja komisjonitasu tuleb arvutada paremal oleva tabeli abil.
Allpool on valem, mis seda teeb:
= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2
See kasutab otsinguna lihtsalt müügiväärtust ja vaatab läbi paremal asuva otsingutabeli. Selle valemi puhul kasutasin viienda argumendina -1 ([match_mode]), mis tähendab, et see otsib täpset vastet ja kui seda ei leia, tagastab see otsingu väärtusest vaid väiksema väärtuse .
Ja nagu ma ütlesin, ei pea te muretsema, kas teie andmed pole sorteeritud.
Näidisfaili allalaadimiseks ja selle järgimiseks klõpsake siin
Näide 8: Horisontaalne otsing
XLOOKUP saab teha nii vertikaalse kui ka horisontaalse otsingu.
Allpool on mul andmestik, kus mul on ridades õpilaste nimed ja nende skoorid ning soovin tuua lahtrisse B7 antud nime skoori.
Seda teeb järgmine valem:
= XLOOKUP (B7, B1: O1, B2: O2)
See pole midagi muud kui lihtne otsing (sarnane näites 1 nähtuga), kuid horisontaalne.
Kõiki näiteid, mida ma vertikaalse otsingu kohta käsitlen, saab teha ka horisontaalse otsingu abil, kasutades funktsiooni XLOOKUP (hüvastijätt VLOOKUP ja HLOOKUP).
Näide 9: Tingimuslik otsing (XLOOKUPi kasutamine koos teiste valemitega)
See on pisut arenenud näide ja näitab ka XLOOKUPi võimsust, kui peate tegema keerulisi otsinguid.
Allpool on andmekogum, kus mul on õpilaste nimed ja nende hinded, ning ma tahan teada selle õpilase nime, kes on igas õppeaines maksimumpunkti saavutanud, ja õpilaste arvu, kes on igal õppeaines saanud rohkem kui 80 punkti.
Allpool on valem, mis annab iga aine kõrgeima hindega õpilase nime:
= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A 2 dollarit: 15 dollarit)
Kuna XLOOKUPi saab kasutada terve massiivi tagastamiseks, kasutasin seda kõigepealt vajaliku teema kõigi märkide saamiseks.
Näiteks matemaatika puhul, kui kasutan funktsiooni XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), annab see mulle kõik matemaatika hinded. Seejärel saan kasutada funktsiooni MAX, et leida selle vahemiku maksimaalne tulemus.
Sellest maksimaalsest skoorist saab siis minu otsinguväärtus ja otsinguvahemik oleks XLOOKUPi tagastatud massiiv (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)
Kasutan seda teises XLOOKUP valemis, et tuua maksimaalse hinde saanud õpilase nimi.
Ja selleks, et arvestada õpilaste arvu, kes on saanud rohkem kui 80, kasutage järgmist valemit:
= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")
See kasutab lihtsalt XLOOKUP valemit, et saada antud teema kõigi väärtuste vahemik. Seejärel ümbritseb see funktsiooni COUNTIF, et saada rohkem kui 80 skoori.
Näide 10: metamärgi kasutamine XLOOKUPis
Nii nagu saate rakendustes VLOOKUP ja MATCH kasutada metamärke, saate seda teha ka XLOOKUP -iga.
Kuid on vahe.
XLOOKUPis peate määrama, et kasutate metamärke (viiendas argumendis). Kui te seda ei määra, annab XLOOKUP teile vea.
Allpool on andmestik, kus mul on ettevõtete nimed ja nende turukapitalisatsioon.
Soovin otsida veerust D ettevõtte nime ja tuua turukapitalisatsiooni vasakult tabelist. Ja kuna veerus D olevad nimed ei vasta täpselt, pean kasutama metamärke.
Allpool on valem, mis seda teeb:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)
Ülaltoodud valemis olen varem kasutanud tärniga (*) metamärke nagu pärast D2 (see peab olema kahekordse jutumärgi sees ja liidetud tähega D2, kasutades tähemärki).
See käsib valemil kõik lahtrid läbi vaadata ja kui see sisaldab sõna lahtris D2 (mis on Apple), pidage seda täpseks vasteks. Pole tähtis, kui palju ja mis märke on lahtris D2 enne ja pärast teksti.
Ja veendumaks, et XLOOKUP aktsepteerib metamärke, on viiendaks argumendiks seatud 2 (metamärgi vaste).
Näide 11: leidke veerust viimane väärtus
Kuna XLOOKUP võimaldab teil otsida alt üles, saate hõlpsalt leida loendi viimase väärtuse ja tuua vastava väärtuse veerust.
Oletame, et teil on allpool näidatud andmekogum ja soovite teada, mis on viimane ettevõte ja milline on selle viimase ettevõtte turukapitalisatsioon.
Allolev valem annab teile viimase ettevõtte nime:
= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)
Ja järgmine valem annab loendis viimase ettevõtte turupiiri:
= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)
Need valemid kasutavad jällegi metamärke. Nendes olen kasutanud otsinguväärtusena tärni (*), mis tähendab, et see loeks esimese lahtri, mida see kohtab, täpseks vasteks (kuna tärn võib olla mis tahes märk ja arv tähemärke).
Ja kuna suund on alt üles (vertikaalselt paigutatud andmete puhul), tagastab see loendi viimase väärtuse.
Ja teine valem kasutab loendis perekonnanime turupiiri saamiseks eraldi return_range.
Näidisfaili allalaadimiseks ja selle järgimiseks klõpsake siin
Mis siis, kui teil pole XLOOKUPi?
Kuna XLOOKUP on tõenäoliselt saadaval ainult Office 365 kasutajatele, on üks viis selle hankimiseks Office 365 -le üle minna.
Kui teil on juba Office 365 kodu-, isiklik või ülikooliversioon, on teil juba juurdepääs teenusele XLOOKUP. Kõik, mida pead tegema, on liituda Office Insideri programmiga.
Selleks minge vahekaardile Fail, klõpsake nuppu Konto ja seejärel Office'i siseringi valikut. Oleks võimalus liituda siseringi programmiga.
Kui teil on muid Office 365 tellimusi (näiteks Enterprise), olen kindel, et XLOOKUP ja muud suurepärased funktsioonid (näiteks dünaamilised massiivid, valemid nagu SORT ja FILTER) muutuvad peagi kättesaadavaks.
Kui kasutate Excelit 2010/2013/2016/2019, pole teil XLOOKUP -i ja peate jätkuvalt kasutama kombinatsioone VLOOKUP, HLOOKUP ja INDEX/MATCH, et saada parimaid otsinguvalemeid.
XLOOKUP tagumine ühilduvus
See on üks asi, millega peate ettevaatlik olema - XLOOKUP on EI tahapoole ühilduv.
See tähendab, et kui loote faili ja kasutate valemit XLOOKUP ning avate selle versioonis, millel puudub XLOOKUP, kuvatakse selles vigu.
Kuna XLOOKUP on suur samm õiges suunas, usun, et sellest saab vaikimisi otsimisvalem, kuid selle laialdase kasutuselevõtmiseni kulub kindlasti paar aastat. Lõppude lõpuks näen ma endiselt mõnda inimest, kes kasutab Excel 2003.
Nii et need on 11 XLOOKUPi näidet, mis aitavad teil otsingu- ja viiteasju kiiremini teha ning hõlbustada ka kasutamist.
Loodan, et leidsite selle õpetuse kasulikuks!
