Home-theater-designers
Vlookup ir būtiska Excel funkcija, un tā ir kļuvusi par būtisku datu apstrādes sastāvdaļu. Tas nodrošina dažas funkcijas, kuras parasti var saistīt ar pilnu datubāzi.
Bet ko darīt, ja vēlaties automatizēt šo funkciju? Jā, tas ir iespējams, it īpaši, ja zināt, kā izmantot Excel vietējo automatizācijas valodu VBA. Lūk, kā jūs varat automatizēt vlookup funkciju programmā Excel VBA.
DIENAS VIDEO MAKEUSEOF
Vlookup sintakse
Vlookup funkcijas sintakse ir diezgan vienkārša, taču tai ir četri aspekti, uz kuriem jums jākoncentrējas, pat automatizējot to programmā Excel VBA.
=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)Funkcijas argumentiem ir šāda nozīme:
programmatūras labošanas rīks operētājsistēmai Windows 10
- lookup_value: Šī ir primārā vērtība, kuru vēlaties meklēt no uzmeklēšanas masīva.
- lookup_array: Šie ir avota dati, kurus lookup_value izmantos kā atsauci.
- column_index: Kolonna, no kuras atgriezt vērtību.
- precīza_atbilstība/daļēja_atbilstība: Izmantojiet 0 vai 1, lai noteiktu atbilstības veidu.
Datu sagatavošana
Šai datu kopai ir divas daļas: uzmeklēšanas tabula un galamērķa datu punkti.
Uzmeklēšanas tabula sastāv no trim kolonnām ar datiem, kas aizpilda laukus Apakškategorija un Produkta nosaukums.
Galamērķa tabulā ir atbilstošas kolonnas, bez apakškategorijas vai produkta nosaukuma datiem. Ņemiet vērā, ka vērtības slejā Pasūtījuma ID ir arī tajā pašā kolonnā uzmeklēšanas tabulā:
Vlookup izmantošana tieši Excel izklājlapā
Lai aizpildītu mērķa kolonnas B un C, varat izmantojiet Excel vlookup funkciju .
Šūnā B2 , ievadiet šādu formulu:
=VLOOKUP($A2, $F:$H, 2, 0)Tāpat arī šūnā C2 , ievadiet šo formulu:
=VLOOKUP($A2, $F:$H, 3, 0)Varat vilkt formulu uz leju no šūnas B2 līdz kolonnas beigām, B17. Atkārtojiet šo procesu arī C kolonnai. Katra nākamā ieraksta vērtības tiks automātiski atjauninātas abās kolonnās.
Vērtības, ko nododat vlookup funkcijai, ir šādas:
- A2 : uzmeklēšanas vērtība atrodas šajā šūnā.
- F3:H17 : tas sastāv no uzmeklēšanas diapazona.
- 23 : atsauces kolonnas, no kurām iegūt vērtību.
- 0 : apzīmē precīzu atbilstību.
Excel VBA Vlookup funkcija
Vlookup funkcijai ir daudz līdzību neatkarīgi no tā, vai to izmantojat tieši programmā Excel vai caur VBA. Neatkarīgi no tā, vai esat datu analītiķis, kas nodarbojas ar datu rindām, vai krājumu pārvaldnieks, kas regulāri nodarbojas ar jauniem produktiem, varat gūt labumu, izmantojot vlookup.
Strādājot ar dinamisku uzmeklēšanas diapazonu, vienmēr vislabāk ir automatizēt formulas, lai izvairītos no atkārtotas formulas lietošanas programmā Excel. Automatizējot Vlookup funkciju VBA, varat veikt vairāku kolonnu aprēķinus ar vienu klikšķi.
1. Veiciet nepieciešamo iestatīšanu
Sāciet, atverot kodēšanas redaktoru (nospiediet Alt + F11 vai dodieties uz Izstrādātājs cilne) jaunā Excel darbgrāmatā un pievienojiet moduli, lai sāktu rakstīt kodu. Lai izveidotu apakšprogrammu, koda redaktorā kodēšanas loga augšdaļā pievienojiet šādas rindiņas:
Sub vlookup_fn1()End SubApakšprogramma ir jūsu VBA koda konteiners, un tā ir ļoti svarīga, lai to veiksmīgi palaistu. Alternatīva ir izveidot VBA lietotāja veidlapu lai padarītu jūsu lietotāja saskarni interaktīvāku.
2. Deklarējiet savus mainīgos un izveidojiet atsauces diapazonus
Pirmkārt, jums ir jādeklarē mainīgo datu tipi, izmantojot Aptumšot paziņojums, apgalvojums:
Dim i as integer, lastrow as longPēc tam izveidojiet a pēdējā rinda mainīgais, lai saglabātu pēdējās aizpildītās rindas vērtību jūsu uzmeklēšanas diapazonā. Pēdējās rindas funkcija izmanto beigas (xldown) funkcija, lai aprēķinātu beigu rindas atsauci norādītajā diapazonā.
Šajā gadījumā pēdējās rindas mainīgais saglabā uzmeklēšanas diapazona pēdējo aizpildīto rindas vērtību, 17.
lastrow = Sheets("Sheet2").Range("F3").End(xlDown).RowIepriekš minētajā piemērā mērķa tabula stiepjas no A2:C17 , savukārt avota tabula stiepjas no F2:H17 . Vlookup formula apskatīs katru A kolonnā saglabāto vērtību, meklēs to avota tabulā un ielīmēs atbilstošos ierakstus kolonnās B un C.
Tomēr, pirms pāriet uz cilpu, diapazona vērtības ir jāsaglabā jaunā mainīgā ( mans_diapazons ), sekojoši:
my_range = Sheets("Sheet2").Range("F3:H" & lastrow)Jums ir skaidri jādefinē sākuma šūnas atsauce ( F3 ) un beigu kolonnas atsauce ( H ). VBA automātiski pievieno rindas vērtību, lai pabeigtu uzmeklēšanas masīvu.
3. Uzrakstiet cilpu, lai ciklētu cauri katrai uzmeklēšanas vērtībai
Pirms cilpas rakstīšanas definējiet sākuma rindas vērtību, 2 . Cilpas sākuma vērtības noteikšana ir ļoti svarīga, jo strādājat ar dinamiskiem elementiem. Galamērķa tabulā 2. rinda ir pirmā datu rinda. Mainiet šo vērtību, ja jūsu dati sākas citā rindā.
i = 2Jūs varat izmantot a darīt kamēr cilpa, lai apstrādātu katru rindu, sākot ar 2. rindu un beidzot ar 17. rindu. Tāpat kā pārējais kods, šis aspekts ir dinamisks; cilpa darbosies, līdz nosacījums ir nepatiess. Izmantojiet nosacījumu, lai pārbaudītu, vai pašreizējās rindas pirmajā šūnā nav tukšas vērtības.
Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0Cilpas iekšpusē varat izsaukt funkciju VLookup, lai aizpildītu šūnas:
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)Šie paziņojumi nosaka šūnu vērtību pašreizējā rindā attiecīgi 2. un 3. kolonnā. Viņi izmanto Darblapas funkcija iebilst, lai izsauktu VLookup funkciju, nododot atbilstošo vērtību no 1. kolonnas, lai meklētu. Viņi arī nodod iepriekš definēto diapazonu un attiecīgās kolonnas indeksu, no kura iegūst galīgo vērtību.
Vlookup kods ir gatavs, taču jums joprojām ir jāpalielina rindas mainīgais katru reizi ap cilpu:
i = i + 1Katru reizi, kad cilpa darbojas, tas palielina vērtību i ar 1. Atcerieties, ka sākuma rindas vērtība ir 2, un pieaugums notiek katru reizi, kad tiek izpildīta cilpa. Izmantojiet cilpa atslēgvārds, lai iestatītu cilpas koda bloka beigas.
Palaižot visu kodu, tas aizpildīs rezultātus abās kolonnās, pamatojoties uz vērtībām avota tabulā.
Šeit ir viss kods atsaucei:
kā padarīt Facebook fotoattēlus privātus
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. Izveidojiet pogu, lai palaistu kodu
Ja kods nav pieejams, varat izveidot pogu savā Excel darbgrāmatā, lai to palaistu ar vienu klikšķi. Ievietojiet vēlamo formu tukšā Excel lapā, ar peles labo pogu noklikšķiniet uz tās un noklikšķiniet uz Piešķirt makro opciju.
Dialoglodziņā atlasiet apakšprogrammas nosaukumu un noklikšķiniet uz labi .
Kad vēlaties palaist kodu, noklikšķiniet uz pogas, lai redzētu, kā dati tiek aizpildīti nekavējoties.
Excel VBA izmantošana, lai automatizētu uzmeklēšanas funkcijas
Excel VBA ir elastīga valoda, kas ir labi pielāgota, lai atvieglotu automatizāciju dažādos MS Excel aspektos. Programmā MS Excel VBA ir daudz iespēju, sākot no Excel funkciju automatizēšanas un beidzot ar automātisku sarežģītu rakurstabulu izveidi.
Strādājot ar dažādiem Excel segmentiem, varat eksperimentēt ar dažādām iespējām, lai padarītu savu dzīvi vieglāku un efektīvāku.