Bagaimana untuk mengautomasikan Vlookup menggunakan Excel VBA

Vlookup ialah fungsi penting dalam Excel dan telah menjadi bahagian penting dalam pemprosesan data. Ia menyediakan beberapa fungsi yang boleh anda gabungkan dengan pangkalan data yang komprehensif.

Walau bagaimanapun, jika anda ingin mengautomasikan fungsi ini, anda boleh, terutamanya jika anda tahu cara menggunakan bahasa automasi yang tersedia dalam Excel dan VBA. Begini cara anda boleh mengautomasikan fungsi vlookup dalam Excel VBA .

Formula fungsi Vlookup dalam Excel

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

Maksud hujah dalam formula:

• lookup_value: Nilai asas yang anda ingin cari dalam tatasusunan.
• lookup_array: Data sumber, lookup_value akan digunakan sebagai rujukan.
• column_index: Lajur mengembalikan nilai.
• exact_match/partial_match: Gunakan 0 atau 1 untuk menentukan jenis gabungan.

Fungsi VBA Vlookup dalam Excel

Penggunaan fungsi Vlookup tidak jauh berbeza apabila anda menggunakannya secara langsung dalam Excel atau melalui VBA. Sama ada penganalisis data sedang memproses baris maklumat atau pengurus gudang sentiasa bekerja dengan produk baharu, fungsi vlookup berguna.

Apabila bekerja dengan julat carian dinamik, mengautomasikan formula adalah yang terbaik, untuk mengelakkan daripada memanipulasi formula yang sama beberapa kali dalam Excel. Dengan mengautomasikan fungsi Vlookup dalam VBA, anda boleh melakukan pengiraan berbilang lajur dengan satu klik.

Ilustrasi

Set data ini mempunyai dua bahagian: jadual carian dan titik data sasaran. Jadual carian mengandungi 3 lajur, dengan data dalam dua medan Sub-Kategori dan Nama Produk:

Jadual sasaran mempunyai lajur yang sesuai, tiada subfolder atau data nama produk. Ambil perhatian bahawa nilai dalam lajur ID Pesanan juga muncul dalam lajur yang sama dalam jadual carian:

1. Buat tetapan yang diperlukan

Mulakan dengan membuka editor kod (tekan Alt + F11 atau navigasi ke tab Pembangun ) dalam buku kerja Excel baharu dan tambahkan modul untuk memulakan pengekodan. Dalam editor kod, tambahkan baris berikut ke bahagian atas tetingkap pengaturcaraan untuk mencipta sub-rutin:

Sub vlookup_fn1()End Sub

sub-rutin ialah bekas untuk kod VBA dan diperlukan untuk menjalankannya dengan jayanya. Alternatif lain ialah mencipta borang pengguna VBA untuk menjadikan UI lebih interaktif.

2. Isytiharkan pembolehubah dan cipta skop rujukan

Pertama, anda perlu mengisytiharkan jenis data pembolehubah menggunakan arahan Dim:

Dim i as integer, lastrow as long

Seterusnya, buat lastrow pembolehubah , untuk menyimpan nilai baris terakhir yang diisi dalam julat carian. Fungsi lastrow menggunakan fungsi end(xldown) untuk mengira rujukan baris terakhir dalam julat tertentu.

Dalam contoh ini, pembolehubah lastrow mengandungi nilai baris terakhir yang diisi dalam julat carian, khususnya 17.

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

Dalam contoh di atas, jadual destinasi memanjang dari A2:C17, dan jadual sumber memanjang dari F2:H17. Formula vlookup akan melingkari setiap nilai dalam lajur A, mencarinya dalam jadual sumber dan menampal entri gabungan dalam lajur B dan C.

Walau bagaimanapun, sebelum memasuki gelung, anda mesti menyimpan nilai julat dalam pembolehubah baharu ( my_range ) seperti berikut:

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

Anda mesti menyatakan secara eksplisit rujukan sel permulaan (F3) dan rujukan lajur penamat (H). VBA secara automatik menambah nilai baris untuk melengkapkan tatasusunan carian.

3. Tulis gelung untuk mengitar setiap nilai carian

Sebelum menulis gelung, tentukan nilai baris permulaan, 2. Menentukan nilai permulaan untuk gelung adalah perlu apabila anda berurusan dengan komponen dinamik. Dalam jadual destinasi, baris 2 ialah baris pertama data. Tukar nilai ini jika data bermula dalam baris yang berbeza.

i = 2

Anda boleh menggunakan gelung do-while untuk memproses setiap baris, bermula pada baris 2 dan berakhir pada baris 17. Seperti kod yang lain, aspek ini adalah fleksibel; Gelung akan berjalan sehingga syarat adalah palsu. Gunakan syarat untuk menguji nilai bukan kosong dalam sel pertama baris semasa.

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

Di dalam gelung, anda boleh memanggil fungsi Vlookup untuk mengisi sel:

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)

Perintah ini menetapkan nilai sel dalam baris semasa, masing-masing dalam lajur 2 dan 3. Mereka menggunakan objek WorksheetFunction untuk memanggil fungsi Vlookup, menghantar nilai yang sepadan dari lajur 1 untuk mencari. Mereka juga melepasi julat yang anda tetapkan pada mulanya dan indeks lajur yang berkaitan untuk mengambil nilai akhir.

Kod vlookup sudah sedia, tetapi anda masih perlu menambah pembolehubah baris setiap kali dalam gelung:

i = i + 1

Setiap kali gelung berjalan, ia menambah nilai i sebanyak 1 . Ingat, nilai baris permulaan ialah 2, kenaikan berlaku setiap kali gelung berjalan. Gunakan kata kunci gelung untuk menetapkan penghujung blok kod ini.

Apabila keseluruhan kod dijalankan, ia akan mengisi kedua-dua lajur dengan hasil, berdasarkan nilai dalam jadual sumber.

Di bawah ialah keseluruhan kod rujukan:

 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. Buat butang untuk menjalankan kod

Anda boleh membuat butang dalam jadual Excel untuk menjalankannya dengan satu klik. Masukkan bentuk yang dikehendaki pada helaian Excel kosong, klik kanan padanya dan klik pilihan Berikan Makro .

Dalam kotak dialog, pilih nama sub-rutin dan klik OK .

Apabila anda ingin menjalankan kod, klik butang untuk melihat cara data diisi dengan segera.

Di atas ialah cara menggunakan Excel VBA untuk mengautomasikan fungsi carian . Semoga artikel itu berguna kepada anda.