Cách tự động hóa Vlookup bằng Excel VBA

Vlookup là một hàm thiết yếu trong Excel và đã trở thành một phần quan trọng trong xử lý dữ liệu. Nó cung cấp một số chức năng mà bạn có thể kết hợp với database toàn diện.

Thế nhưng nếu muốn tự động hóa hàm này, bạn có thể, nhất là khi biết cách dùng ngôn ngữ tự động hóa có sẵn trong Excel, VBA. Dưới đây là cách bạn có thể tự động hóa hàm vlookup trong Excel VBA.

Công thức hàm Vlookup trong Excel

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

Ý nghĩa các đối số trong công thức:

• lookup_value: Giá trị cơ bản mà bạn muốn tra cứu trong mảng.
• lookup_array: Dữ liệu nguồn, lookup_value sẽ dùng dưới dạng tham chiếu.
• column_index: Cột trả về giá trị.
• exact_match/partial_match: Dùng 0 hoặc 1 để xác định kiểu kết hợp.

Hàm Vlookup của VBA trong Excel

Hàm Vlookup có nhiều điểm tương đồng dùng bạn đang dùng trực tiếp nó trong Excel hoặc qua VBA. Dù là nhà phân tích dữ liệu đang xử lý các hàng thông tin hay quản lý kho thường xuyên phải làm việc với các sản phẩm mới, hàm vlookup đều hữu ích.

Khi làm việc với một phạm vi tra cứu động, tự động hóa công thức là tốt nhất, để tránh phải thao tác nhiều lần một công thức trong Excel. Bằng cách tự động hóa hàm Vlookup trong VBA,bạn có thể thực hiện các phép tính nhiều cột bằng một click.

Ví dụ minh họa

Bộ dữ liệu này có hai phần: một bảng tra cứu và các điểm dữ liệu đích. Bảng tra cứu chứa 3 cột, với dữ liệu trong hai trường Sub-Category và Product Name:

Bảng đích có các cột phù hợp, không thư mục phụ hay dữ liệu tên sản phẩm. Lưu ý rằng các giá trị trong cột Order ID cũng hiện ở cùng cột trong bảng tra cứu:

1. Thực hiện thiết lập cần thiết

Bắt đầu bằng cách mở trình chỉnh sửa code (nhấn Alt + F11 hoặc điều hướng tới tab Developer) trong một workbook Excel mới và thêm mô đun để bắt đầu viết code. Trong trình chỉnh sửa code, thêm các dòng sau vào phía trên cùng cửa cửa sổ lập trình để tạo một sub-routine:

Sub vlookup_fn1()End Sub

sub-routine là một container cho code VBA và cần thiết để chạy nó thành công. Lựa chọn thay thế khác là tạo một biểu mẫu người dùng VBA để làm UI tương tác hơn.

2. Khai báo biến và tạo các phạm vi tham chiếu

Đầu tiên, bạn cần khai báo các kiểu dữ liệu của biến bằng cách dùng lệnh Dim:

Dim i as integer, lastrow as long

Tiếp theo, tạo một biến lastrow, để lưu trữ giá trị của hàng được điền cuối cùng trong phạm vi tra cứu. Hàm lastrow dùng hàm end(xldown) để tính tham chiếu hàng cuối cùng trong phạm vi cụ thể.

Ở ví dụ này, biến lastrow chứa giá trị hàng được điền cuối cùng trong phạm vi lookup, cụ thể ở đây là 17.

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

Ở ví dụ trên, bảng đích kéo dài từ A2:C17, còn bảng nguồn mở rộng từ F2:H17. Công thức vlookup sẽ lặp qua từng giá trị trong cột A, tra cứu nó trong bảng nguồn và dán các mục kết hợp ở cột B và C.

Tuy nhiên, trước khi vào vòng lặp, bạn phải lưu trữ các giá trị phạm vi trong một biến mới (my_range) như sau:

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

Bạn phải xác định rõ ràng tham chiếu ô bắt đầu (F3) và tham chiếu cột kết thúc (H). VBA tự động thêm giá trị hàng để hoàn thiện mảng tra cứu.

3. Viết vòng lặp để chuyển qua từng giá trị tra cứu

Trước khi viết vòng lặp, xác định giá trị hàng bắt đầu, 2. Xác định một giá trị bắt đầu cho vòng lặp là cần thiết khi bạn xử lý các thành phần động. Ở bảng đích, hàng 2 là hàng dữ liệu đầu tiên. Thay đổi giá trị này nếu dữ liệu bắt đầu ở hàng khác.

i = 2

Bạn có thể dùng vòng lặp do-while để xử lý từng hàng, bắt đầu ở hàng 2 và kết thúc ở hàng 17. Giống như phần code còn lại, khía cạnh này linh động; vòng lặp sẽ chạy cho tới khi điều kiện sai. Dùng điều kiện để kiểm tra một giá trị không trống trong ô đầu tiên của hàng hiện tại.

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

Bên trong vòng lặp, bạn có thể gọi hàm Vlookup để điền vào các ô:

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)

Những lệnh này đặt giá trị các ô trong hàng hiện tại, lần lượt ở cột 2 và 3. Chúng dùng đối tượng WorksheetFunction để gọi hàm Vlookup, chuyển giá trị tương ứng từ cột 1 để tìm kiếm. Chúng cũng chuyển phạm vi bạn đã xác định ban đầu và chỉ mục của cột liên quan để tìm nạp giá trị cuối cùng.

Code vlookup đã sẵn sàng, nhưng bạn vẫn cần tăng biến hàng mỗi lần trong vòng lặp:

i = i + 1

Mỗi lần vòng lặ chay, nó tăng thêm 1 cho giá trị I. Nhớ rằng, giá trị hàng bắt đầu là 2, mức tăng xảy ra mỗi lần khi vòng lặp chạy. Dùng từ khóa loop để thiết lập kết thúc của khối code này.

Khi chạy toàn bộ code, nó sẽ điền kết quả vào cả hai cột, dựa trên các giá trị trong bảng nguồn.

Dưới đây là toàn bộ code tham chiếu:

 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. Tạo nút bấm để chạy code

Bạn có thể tạo một nút bấm trong bảng Excel để chạy nó bằng một click. Chèn hình dạng mong muốn trên bảng Excel trống, click chuột phải vào nó và click tùy chọn Assign Macro.

Trong box hội thoại, chọn tên của sub-routine và click OK.

Khi muốn chạy code, click nút bấm để thấy cách dữ liệu được điền ngay lập tức.

Trên đây là cách dùng Excel VBA để tự động hóa các hàm tra cứu. Hi vọng bài viết hữu ích với các bạn.