Home-theater-designers
Vlookup on olennainen toiminto Excelissä ja siitä on tullut tärkeä osa tietojenkäsittelyä. Se tarjoaa joitakin toimintoja, joita saatat tavallisesti liittää täysimittaiseen tietokantaan.
Mutta entä jos haluat automatisoida tämän toiminnon? Kyllä, se on mahdollista, varsinkin jos osaat käyttää Excelin alkuperäistä automaatiokieltä, VBA:ta. Näin voit automatisoida vlookup-toiminnon Excel VBA:ssa.
PÄIVÄN VIDEON TEKEMINEN
Vlookupin syntaksi
Vlookup-funktion syntaksi on melko yksinkertainen, mutta siinä on neljä näkökohtaa, joihin sinun on keskityttävä, jopa automatisoitaessa sitä Excel VBA:ssa.
=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)Funktion argumenteilla on seuraava merkitys:
- lookup_value: Tämä on ensisijainen arvo, jonka haluat etsiä hakutaulukosta.
- lookup_array: Tämä on lähdetieto, jota lookup_value käyttää viitteenä.
- column_index: Sarake, josta palautetaan arvo.
- tarkka_haku/osittainen_osuma: Määritä hakutyyppi käyttämällä joko 0 tai 1.
Tietojen valmistelu
Tässä tietojoukossa on kaksi osaa: hakutaulukko ja kohdetietopisteet.
miksi siri ei toimi
Hakutaulukko koostuu kolmesta sarakkeesta, joiden tiedot täyttävät alaluokka- ja tuotenimikentät:
Kohdetaulukossa on vastaavia sarakkeita, joissa ei ole alaluokka- tai tuotenimitietoja. Huomaa, että sen Tilaustunnus-sarakkeen arvot ovat myös samassa sarakkeessa hakutaulukossa:
Vlookupin käyttäminen suoraan Excel-laskentataulukossa
Voit täyttää kohdesarakkeet B ja C käytä Excelin vlookup-toimintoa .
Solussa B2 , syötä seuraava kaava:
=VLOOKUP($A2, $F:$H, 2, 0)Samoin solussa C2 , syötä tämä kaava:
=VLOOKUP($A2, $F:$H, 3, 0)Voit vetää kaavan alas solusta B2 sarakkeen loppuun, B17. Toista tämä prosessi myös sarakkeelle C. Jokaisen seuraavan merkinnän arvot päivittyvät automaattisesti molemmissa sarakkeissa.
Vlookup-funktioon välitettävät arvot ovat:
- A2 : Hakuarvo on tässä solussa.
- F3: H17 : Tämä koostuu hakualueesta.
- 23 : Viitesarakkeet, joista arvo haetaan.
- 0 : Tarkoittaa täsmällistä vastaavuutta.
Excel VBA:n Vlookup-funktio
Vlookup-toiminnolla on monia yhtäläisyyksiä riippumatta siitä, käytätkö sitä suoraan Excelissä tai VBA:n kautta. Oletpa sitten data-analyytikko, joka käsittelee datarivejä, tai varastopäällikkö, joka käsittelee uusia tuotteita säännöllisesti, voit hyötyä vlookupin käyttämisestä.
Kun työskentelet dynaamisen hakualueen kanssa, on aina parasta automatisoida kaavat, jotta kaavoja ei käytetä toistuvasti Excelissä. Automatisoimalla Vlookup-toiminnon VBA:ssa voit suorittaa usean sarakkeen laskelmia yhdellä napsautuksella.
1. Suorita vaaditut asetukset
Aloita avaamalla koodauseditori (paina Alt + F11 tai navigoi kohtaan Kehittäjä -välilehti) uuteen Excel-työkirjaan ja lisää moduuli koodin kirjoittamisen aloittamiseksi. Luo aliohjelma lisäämällä koodieditorissa seuraavat rivit koodausikkunan yläosaan:
Sub vlookup_fn1()End SubAlirutiini on VBA-koodisi säilö ja on kriittinen sen suorittamiseksi onnistuneesti. Vaihtoehtona on luo VBA-käyttäjälomake tehdä käyttöliittymästäsi interaktiivisempi.
2. Ilmoita muuttujasi ja luo viitealueesi
Ensin sinun on määritettävä muuttujan tietotyypit käyttämällä Himmeä lausunto:
Dim i as integer, lastrow as longLuo seuraavaksi a viimeinen rivi muuttuja, tallentaaksesi hakualueen viimeisen täytetyn rivin arvon. Viimeinen rivi -funktio käyttää end(xldown) toiminto laskea rivin loppuviittaus määritetyllä alueella.
Tässä tapauksessa viimeinen rivimuuttuja tallentaa hakualueen viimeksi täytetyn rivin arvon, 17.
lastrow = Sheets("Sheet2").Range("F3").End(xlDown).RowYllä olevassa esimerkissä kohdetaulukko ulottuu alkaen A2:C17 , kun taas lähdetaulukko ulottuu alkaen F2: H17 . Vlookup-kaava kiertää jokaisen sarakkeeseen A tallennetun arvon, etsii sen lähdetaulukosta ja liittää vastaavat merkinnät sarakkeisiin B ja C.
Ennen kuin hyppäät silmukkaan, sinun on kuitenkin tallennettava alueen arvot uuteen muuttujaan ( my_range ), seuraavasti:
my_range = Sheets("Sheet2").Range("F3:H" & lastrow)Sinun on määritettävä selvästi aloitussoluviittaus ( F3 ) ja loppusarakkeen viite ( H ). VBA lisää automaattisesti rivin arvon suorittaakseen hakutaulukon loppuun.
3. Kirjoita silmukka, joka kiertää jokaisen hakuarvon läpi
Ennen kuin kirjoitat silmukan, määritä aloitusrivin arvo, 2 . Silmukan aloitusarvon määrittäminen on välttämätöntä, kun käsittelet dynaamisia elementtejä. Kohdetaulukon rivi 2 on tietojen ensimmäinen rivi. Muuta tätä arvoa, jos tietosi alkavat eri riviltä.
i = 2Voit käyttää a tehdä, kun silmukka jokaisen rivin käsittelemiseksi, alkaen rivistä 2 ja päättyen riviin 17. Kuten muukin koodi, tämä aspekti on dynaaminen; silmukka jatkuu, kunnes ehto on epätosi. Käytä ehtoa tarkistaaksesi, onko nykyisen rivin ensimmäisessä solussa arvoa, joka ei ole tyhjä.
Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0Silmukan sisällä voit kutsua VLookup-funktiota täyttämään solut:
Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)Nämä lausekkeet määrittävät nykyisen rivin solujen arvon sarakkeissa 2 ja 3. He käyttävät Työtaulukkotoiminto vastustaa soittamista VHaku funktio välittää vastaavan arvon sarakkeesta 1 hakuun. Ne välittävät myös aiemmin määrittämäsi alueen ja vastaavan sarakkeen indeksin lopullisen arvon hakemiseksi.
Vlookup-koodi on valmis, mutta sinun on silti lisättävä rivimuuttujaa joka kerta silmukan ympärillä:
i = i + 1Joka kerta kun silmukka suoritetaan, se lisää arvoa i 1. Muista, että aloitusrivin arvo on 2 ja lisäys tapahtuu joka kerta, kun silmukka suoritetaan. Käytä silmukka avainsana asettaaksesi silmukan koodilohkon lopun.
Kun suoritat koko koodin, se täyttää tulokset molemmissa sarakkeissa lähdetaulukon arvojen perusteella.
miten muuntaa tiff pdf -muotoon Windows 10: ssä
Tässä koko koodi viitteeksi:
Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub4. Luo painike koodin suorittamista varten
Kun koodi on poissa tieltä, voit luoda Excel-työkirjaan painikkeen, jonka avulla voit suorittaa sen yhdellä napsautuksella. Lisää haluamasi muoto tyhjälle Excel-arkille, napsauta sitä hiiren kakkospainikkeella ja napsauta Määritä makro vaihtoehto.
Valitse valintaikkunassa aliohjelman nimi ja napsauta OK .
Kun haluat suorittaa koodin, napsauta painiketta nähdäksesi kuinka tiedot täyttyvät välittömästi.
Excel VBA:n käyttö hakutoimintojen automatisoimiseen
Excel VBA on joustava kieli, joka on hyvin viritetty helpottamaan automaatiota MS Excelin eri puolilla. MS Excel VBA:ssa on monia vaihtoehtoja aina Excel-toimintojen automatisoinnista monimutkaisten pivot-taulukoiden automaattiseen luomiseen.
Kun työskentelet eri Excel-segmenttien kanssa, voit kokeilla erilaisia vaihtoehtoja tehdäksesi elämästäsi helpompaa ja tehokkaampaa.