Lær å automatisere Vlookups med Excel VBA

Lær å automatisere Vlookups med Excel VBA
Lesere som deg er med på å støtte MUO. Når du foretar et kjøp ved å bruke lenker på nettstedet vårt, kan vi tjene en tilknyttet provisjon. Les mer.

Vlookup er en viktig funksjon i Excel og har blitt en viktig del av databehandling. Det gir noe av funksjonaliteten du vanligvis forbinder med en fullverdig database.





Men hva om du vil automatisere denne funksjonen? Ja, det er mulig, spesielt hvis du vet hvordan du bruker Excels opprinnelige automatiseringsspråk, VBA. Slik kan du automatisere vlookup-funksjonen i Excel VBA.



MAKEUSE AV DAGENS VIDEO

Syntaksen til Vlookup

Syntaksen til vlookup-funksjonen er ganske enkel, men den har fire aspekter du må fokusere på, selv mens du automatiserer den i Excel VBA.





 =vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Funksjonens argumenter har følgende betydning:

  • oppslagsverdi: Dette er den primære verdien du vil slå opp fra oppslagsmatrisen.
  • lookup_array: Dette er kildedataene som lookup_value vil bruke som referanse.
  • kolonneindeks: Kolonne for å returnere verdien fra.
  • eksakt_samsvar/delvis_samsvar: Bruk enten 0 eller 1 for å bestemme samsvarstypen.

Dataforberedelse

Dette datasettet har to deler: en oppslagstabell og destinasjonsdatapunktene.



hvordan får jeg skriveren tilbake på nettet

Oppslagstabellen består av tre kolonner, med data som fyller ut feltene underkategori og produktnavn:

Datatabell i MS Excel med forhåndsutfylte rader

Destinasjonstabellen har samsvarende kolonner, uten data om underkategori eller produktnavn. Merk at verdiene i ordre-ID-kolonnen også finnes i samme kolonne i oppslagstabellen:



Slå opp verdier i et excel-regneark

Bruke Vlookup direkte i et Excel-regneark

For å fylle destinasjonskolonnene, B og C, kan du bruk Excels vlookup-funksjon .

I celle B2 , skriv inn følgende formel:



 =VLOOKUP($A2, $F:$H, 2, 0)

Tilsvarende i celle C2 , skriv inn denne formelen:

 =VLOOKUP($A2, $F:$H, 3, 0)

Du kan dra formelen ned fra celle B2 til slutten av kolonnen, B17. Gjenta denne prosessen for kolonne C også. Verdiene for hver påfølgende oppføring oppdateres automatisk i begge kolonnene.

Verdiene du sender til vlookup-funksjonen er:

  • A2 : Oppslagsverdien er i denne cellen.
  • F3:H17 : Dette består av oppslagsområdet.
  • 23 : Referansekolonnene for å hente verdien fra.
  • 0 : Angir en eksakt match.

Excel VBAs Vlookup-funksjon

Vlookup-funksjonen har mange likheter enten du bruker den direkte i Excel eller via VBA. Enten du er en dataanalytiker som arbeider med rader med data, eller en lagerbehandler som regelmessig arbeider med nye produkter, kan du dra nytte av å bruke vlookup.

Når du arbeider med et dynamisk oppslagsområde, er det alltid best å automatisere formlene dine, for å unngå å bruke formler gjentatte ganger i Excel. Ved å automatisere Vlookup-funksjonen din i VBA, kan du utføre flerkolonneberegninger med et enkelt klikk.

1. Utfør nødvendig oppsett

Start med å åpne kodingsredigeringsprogrammet (trykk Alt + F11 eller naviger til Utvikler fanen) i en ny Excel-arbeidsbok og legg til en modul for å begynne å skrive koden. I koderedigeringsprogrammet legger du til følgende linjer øverst i kodevinduet for å lage en underrutine:

  Sub vlookup_fn1()End Sub

Underrutinen er en beholder for VBA-koden din og er avgjørende for å kunne kjøre den. Et alternativ er å opprette et VBA-brukerskjema for å gjøre brukergrensesnittet ditt mer interaktivt.

2. Deklarer variablene dine og lag referanseområder

Først må du deklarere variabeldatatypene ved å bruke Dim uttalelse:

254D23C88C5E06FBC9663FFFFC3C5896BC413B18

Deretter oppretter du en siste rad variabel, for å lagre verdien av den siste fylte raden innenfor oppslagsområdet ditt. Lastrow-funksjonen bruker end(xldown) funksjon for å beregne sluttradreferansen innenfor det angitte området.

I dette tilfellet lagrer lastrow-variabelen oppslagsområdets siste utfylte radverdi, 17. 

  lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

I eksemplet ovenfor strekker destinasjonstabellen seg fra A2:C17 , mens kildetabellen strekker seg fra F2:H17 . Vlookup-formelen vil gå gjennom hver verdi som er lagret i kolonne A, slå den opp i kildetabellen og lime inn de samsvarende oppføringene i kolonnene B og C.

Men før du hopper inn i loopen, må du lagre områdeverdiene i en ny variabel ( mitt_område ), følgende:

  my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Du må eksplisitt definere startcellereferansen ( F3 ) og sluttkolonnereferansen ( H ). VBA legger automatisk til radverdien for å fullføre oppslagsmatrisen.

VBA-kode i et VBA-redigeringsvindu

3. Skriv en sløyfe for å gå gjennom hver oppslagsverdi

Før du skriver løkken, definer startradverdien, 2 . Å definere en startverdi for løkken din er viktig når du arbeider med dynamiske elementer. I måltabellen er rad 2 den første raden med dataene. Endre denne verdien hvis dataene dine starter i en annen rad.

  i = 2

Du kan bruke en gjør mens løkke for å behandle hver rad, som starter ved rad 2 og slutter ved rad 17. Som resten av koden er dette aspektet dynamisk; løkken vil kjøre til betingelsen er falsk. Bruk betingelsen for å se etter en ikke-tom verdi i den første cellen i gjeldende rad.

  Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Inne i løkken kan du kalle opp VLookup-funksjonen for å fylle ut cellene:

  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)

Disse setningene angir verdien til cellene i gjeldende rad, i henholdsvis kolonne 2 og 3. De bruker ArbeidsarkFunksjon objekt å kalle VOppslag funksjon, sender den tilsvarende verdien fra kolonne 1 for å søke etter. De passerer også området du definerte tidligere og indeksen til den relevante kolonnen for å hente den endelige verdien fra.

Vlookup-koden er klar, men du må fortsatt øke radvariabelen hver gang rundt loopen:

  i = i + 1

Hver gang løkken kjører, øker den verdien av Jeg med 1. Husk at startradverdien er 2, og økningen skjer hver gang sløyfen kjøres. Bruke Løkke nøkkelord for å angi slutten av løkkens kodeblokk.

Når du kjører hele koden, vil den fylle ut resultatene i begge kolonnene, basert på verdiene i kildetabellen.

Tabellutdata i Excel-regneark

Her er hele koden for referanse:

  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 Sub

4. Lag en knapp for å kjøre koden

Med koden ute av veien, kan du opprette en knapp i Excel-arbeidsboken for å kjøre den med et enkelt klikk. Sett inn din foretrukne form på et tomt Excel-ark, høyreklikk på det og klikk på Tilordne makro alternativ.

Rektangelknapp på et Excel-regneark med en liste over alternativer

I dialogboksen velger du navnet på underrutinen din og klikker på OK .

Makroboks åpen på et excel-regneark

Når du vil kjøre koden, klikker du på knappen for å se hvordan dataene fylles ut umiddelbart.

Bruke Excel VBA til å automatisere oppslagsfunksjoner

Excel VBA er et fleksibelt språk, som er godt innstilt for å gjøre automatisering enklere innenfor de forskjellige fasettene til MS Excel. Det finnes mange alternativer i MS Excel VBA, fra automatisering av Excel-funksjoner til å lage forseggjorte pivottabeller automatisk.

Når du arbeider med forskjellige Excel-segmenter, kan du eksperimentere med ulike alternativer for å gjøre livet ditt enklere og mer effektivt.