XLOOKUP -toiminto on yksinomaan Office 365: n sisäpiiriohjelmassa. LOOKUP -toiminnolla on monia toimintoja, jotka voittavat monet VLOOKUP- ja HLOOKUP -toiminnon heikkouksista, mutta valitettavasti se ei ole tällä hetkellä käytettävissä. Mutta älä huoli, voimme luoda XLOOKUP -toiminnon, joka toimii täsmälleen samalla tavalla kuin tuleva XLOOKUP -toiminto MS Excel. Lisäämme siihen toimintoja yksi kerrallaan.
XLOOKUP -toiminnon VBA -koodi
Alla oleva UDF -hakutoiminto ratkaisee monia ongelmia. Kopioi se tai lataa alla oleva xl-apuohjelma.
Toiminto XLOOKUP (lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Lopputoiminto
Selitys:
Yllä oleva koodi on vain perusindeksi, jota käytetään VBA: ssa. Tämä yksinkertaistaa monia asioita, joita uusi käyttäjä kohtaa. If ratkaisee INDEX-MATCH-funktion monimutkaisuuden ja käyttää vain kolmea argumenttia. Voit kopioida sen Excel-tiedostoosi tai ladata alla olevan .xlam-tiedoston ja asentaa sen Excel-apuohjelmana. Jos et tiedä kuinka luoda ja käyttää apuohjelmaa, napsauta tätä, se auttaa sinua.
XLOOKUP-apuohjelma
Katsotaanpa miten se toimii Excel -laskentataulukossa.
XLOOKUPin syntaksi
| = XLOOKUP (haun_arvo, haun_taulukko, tulos_taulukko) |
lookup_value: Tämä on arvo, jota haluat hakea lookup_array.
lookup_array: Se on yksiulotteinen alue, josta haluat hakea haku_arvo.
result_array: Se on myös yksiulotteinen alue. Tämä on alue, josta haluat hakea arvon.
Katsotaanpa tätä XLOOKUP -toimintoa toiminnassa.
XLOOKUP -esimerkkejä:
Tässä minulla on Excel -taulukko. Tutkitaan joitakin toimintoja tämän tietotaulukon avulla.
Toiminnallisuus 1. Tarkka Hae hakuarvon vasemmalta ja oikealta puolelta.
Kuten tiedämme, Excel VLOOKUP -toiminto ei voi noutaa arvoja hakuarvon vasemmalta puolelta. Tätä varten sinun on käytettävä monimutkaista INDEX-MATCH-yhdistelmää. Mutta ei enää.
Olettaen, että meidän on haettava kaikki saatavilla olevat tiedot joidenkin rullanumeroiden taulukosta. Siinä tapauksessa sinun on myös haettava alue, joka on rullanumerosarakkeen vasemmalla puolella.
Kirjoita tämä kaava, I2:
| = HAKU (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Saamme tuloksen pohjoiseen rullanumerolle 112. Kopioi tai vedä alas alla olevien solujen kaava täyttääksesi ne vastaavilla alueilla.
Kuinka se toimii?
Mekanismi on yksinkertainen. Tämä toiminto etsii haku_arvo sisään lookup_array ja palauttaa ensimmäisen tarkan haun indeksin. Käytä sitten kyseistä indeksiä arvon noutamiseen result_array. Tämä toiminto toimii täydellisesti nimettyjen alueiden kanssa.
Samoin käytä tätä kaavaa noutaaksesi arvon jokaisesta sarakkeesta.
Toiminnallisuus 2. Tarkka Vaakasuora Etsi hakuarvon ylä- ja alapuolelta.
XLOOKUP toimii myös tarkana HLOOKUP -funktiona. HLOOKUP -toiminnolla on sama rajoitus kuin VLOOKUP -toiminnolla. Se ei voi hakea arvoa hakuarvon yläpuolelta. Mutta XLOOKUP ei ainoastaan toimi HLOOKUPina, vaan myös voittaa heikkouden. Katsotaanpa kuinka.
Hypoteettisesti, jos haluat verrata kahta tietuetta. Hakutietue, joka sinulla jo on. Tietue, johon haluat verrata, on lookup_range -alueen yläpuolella. Käytä tässä tapauksessa tätä kaavaa.
| = HAKU (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
vedä kaava alas, ja sinulla on koko vertailutietue.
Toiminnallisuus 3. Ei tarvetta sarakkeen numerolle ja oletukselle.
Kun käytät VLOOKUP -toimintoa, sinun on kerrottava sarakkeen numero, josta haluat hakea arvot. Tätä varten sinun on laskettava sarakkeet tai käytettävä temppuja, otettava muiden toimintojen apu käyttöön. Tämän UDF XLOOKUPin avulla sinun ei tarvitse tehdä sitä.
Jos käytät VLOOKUPia vain jonkin arvon noutamiseen yhdestä sarakkeesta tai tarkistaaksesi, onko sarakkeessa arvoa, tämä on paras ratkaisu minun mielestäni.
Toiminnallisuus 4. Korvaa INDEX-MATCH-, VLOOKUP- ja HLOOKUP-toiminnot
Yksinkertaisissa tehtävissä XLOOKUP-toiminto korvaa edellä mainitut toiminnot.
XLOOKUPin rajoitukset:
Kun kyse on monimutkaisista kaavoista, kuten VLOOKUP ja Dynamic Col Index, jossa VLOOKUP tunnistaa hakusarakkeen otsikoilla, tämä XLOOKUP epäonnistuu.
Toinen rajoitus on, että jos sinun on etsittävä taulukosta useita satunnaisia sarakkeita tai rivejä, tämä toiminto on hyödytön, koska sinun on kirjoitettava tämä kaava uudestaan ja uudestaan. Tämä voidaan voittaa käyttämällä nimettyjä alueita.
Toistaiseksi emme ole lisänneet likimääräisiä toimintoja, joten et tietenkään voi saada likimääräistä vastaavuutta. Lisäämme sen liian pian.
Jos XLOOKUP -toiminto ei löydä hakuarvoa, se palauttaa #VALUE -virheen, ei #N/A.
Joten kyllä kaverit, tällä tavalla käytät XLOOKUPia noutaaksesi, etsiäksesi ja validoidaksesi arvot Excel -taulukoissa. Voit käyttää tätä käyttäjän määrittämää toimintoa vaivattomalle haulle hakuarvon vasemmalta tai ylöspäin. Jos sinulla on vielä epäilyksiä tai erityisiä vaatimuksia, jotka liittyvät tähän toimintoon tai EXCEL 2010/2013/2016/2019/365 tai VBA -kyselyyn, kysy sitä alla olevasta kommenttiosasta. Saat varmasti vastauksen.
Luo VBA -toiminto palautusryhmään | Jos haluat palauttaa taulukon käyttäjän määrittämästä funktiosta, meidän on ilmoitettava se, kun nimeämme UDF: n.
Taulukot Excel Formulissa | Opi mitä taulukot ovat Excelissä.
Käyttäjän määrittämän toiminnon luominen VBA: n kautta | Opi luomaan käyttäjän määrittämiä toimintoja Excelissä
Käyttäjän määrittämän toiminnon (UDF) käyttäminen toisesta työkirjasta käyttämällä VBA: ta Microsoft Excelissä | Käytä käyttäjän määrittämää toimintoa toisessa Excelin työkirjassa
Palauta virhearvot käyttäjän määrittämistä funktioista VBA: n avulla Microsoft Excelissä | Opi kuinka voit palauttaa virhearvot käyttäjän määrittämästä funktiosta
Suosittuja artikkeleita:
Jaa Excel -taulukko useisiin tiedostoihin sarakkeen perusteella VBA: n avulla | Tämä VBA -koodijako Excel -taulukko perustuu yksilöllisiin arvoihin tietyssä sarakkeessa. Lataa työtiedosto.
Poista varoitusviestit käytöstä VBA: n avulla Microsoft Excel 2016 | Sammuttaaksemme varoitusviestit, jotka keskeyttävät käynnissä olevan VBA -koodin, käytämme sovellusluokkaa.
Lisää ja tallenna uusi työkirja VBA: n avulla Microsoft Excel 2016: ssa | Työkirjojen lisäämiseen ja tallentamiseen VBA: n avulla käytämme Työkirjat -luokkaa. Työkirjat.Add lisää uuden työkirjan helposti…