Tìm hiểu cách tự động hóa Vlookup của bạn với Excel VBA

Tìm hiểu cách tự động hóa Vlookup của bạn với Excel VBA
Độc giả như bạn giúp hỗ trợ MUO. Khi bạn mua hàng bằng các liên kết trên trang web của chúng tôi, chúng tôi có thể kiếm được hoa hồng liên kết. Đọc thêm.

Vlookup là một chức năng 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ể thường kết hợp với cơ sở dữ liệu toàn diện.





Nhưng nếu bạn muốn tự động hóa chức năng này thì sao? Có, điều đó là có thể, đặc biệt nếu bạn biết cách sử dụng ngôn ngữ tự động hóa riêng của Excel, VBA. Đây là cách bạn có thể tự động hóa chức năng vlookup trong Excel VBA.



LÀM VIDEO TRONG NGÀY

Cú pháp Vlookup

Cú pháp của hàm vlookup khá đơn giản, nhưng nó có bốn khía cạnh mà bạn phải tập trung vào, ngay cả khi tự động hóa nó trong Excel VBA.





cách xóa anbom trong google ảnh 
 =vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Các đối số của hàm có ý nghĩa như sau:

  • tra cứu_giá trị: Đây là giá trị chính mà bạn muốn tra cứu từ mảng tra cứu.
  • tra cứu_mảng: Đây là dữ liệu nguồn mà lookup_value sẽ sử dụng làm tham chiếu.
  • cột_chỉ mục: Cột để trả về giá trị từ đó.
  • chính xác_match/partial_match: Sử dụng 0 hoặc 1 để xác định loại đối sánh.

Chuẩn bị dữ liệu

Tập dữ liệu này có hai phần: bảng tra cứu và điểm dữ liệu đích.



Bảng tra cứu bao gồm ba cột, với dữ liệu điền vào các trường Danh mục phụ và Tên sản phẩm:

Bảng dữ liệu trong MS Excel với các hàng được điền sẵn

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



Tra cứu giá trị trong bảng tính excel

Sử dụng Vlookup trực tiếp trong Bảng tính Excel

Để điền vào các cột đích, B và C, bạn có thể sử dụng hàm vlookup của Excel .

Trong phòng giam B2 , nhập công thức sau:



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

Tương tự, trong ô C2 , hãy nhập công thức này:

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

Bạn có thể kéo công thức xuống từ ô B2 cho đến hết cột, B17. Lặp lại quá trình này cho cả cột C. Các giá trị cho mỗi mục nhập tiếp theo sẽ tự động cập nhật trong cả hai cột.

Các giá trị bạn chuyển đến hàm vlookup là:

  • A2 : Giá trị tra cứu nằm trong ô này.
  • F3:H17 : Điều này bao gồm phạm vi tra cứu.
  • 23 : Các cột tham chiếu để lấy giá trị từ đó.
  • 0 : Biểu thị một đối sánh chính xác.

Hàm Vlookup của Excel VBA

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

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

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

Bắt đầu bằng cách mở trình soạn thảo mã hóa (nhấn Tổ hợp phím + F11 hoặc điều hướng đến nhà phát triển tab) trong sổ làm việc Excel mới và thêm một mô-đun để bắt đầu viết mã của bạn. Trong trình chỉnh sửa mã, hãy thêm các dòng sau vào đầu cửa sổ viết mã để tạo quy trình phụ:

  Sub vlookup_fn1()End Sub

Quy trình phụ là nơi chứa mã VBA của bạn và rất quan trọng để chạy thành công. Một cách khác là tạo biểu mẫu người dùng VBA để làm cho giao diện người dùng của bạn tương tác hơn.

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

Đầ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 sử dụng lệnh Lờ mờ tuyên bố:

  Dim i as integer, lastrow as long

Tiếp theo, tạo một hàng cuối cùng biến, để lưu trữ giá trị của hàng được điền cuối cùng trong phạm vi tra cứu của bạn. Chức năng lastrow sử dụng kết thúc (xldown) để tính toán tham chiếu hàng cuối trong phạm vi đã chỉ định.

cách chọn tất cả các bài hát trên Spotify

Trong trường hợp này, biến lastrow lưu trữ giá trị hàng được điền cuối cùng của phạm vi tra cứu, 17. 

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

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

Tuy nhiên, trước khi nhảy 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 thành mảng tra cứu.

Mã VBA trên cửa sổ soạn thảo VBA

3. Viết một vòng lặp để duyệt qua từng giá trị tra cứu

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

  i = 2

Bạn có thể sử dụng một làm trong khi vòng lặp để xử lý từng hàng, bắt đầu từ hàng 2 và kết thúc ở hàng 17. Giống như phần còn lại của mã, khía cạnh này là động; vòng lặp sẽ chạy cho đến khi điều kiện sai. Sử dụng điều kiện để kiểm tra 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

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)

Các câu lệnh này đặt giá trị của các ô trong hàng hiện tại, trong các cột 2 và 3 tương ứng. Họ sử dụng Bảng tínhChức năng đối tượng để gọi 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 mà bạn đã xác định trước đó và chỉ mục của cột có liên quan để lấy giá trị cuối cùng từ đó.

Mã 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 khi vòng lặp chạy, nó làm tăng giá trị của Tôi bằng 1. Hãy nhớ rằng, giá trị của hàng bắt đầu là 2 và giá trị gia tăng xảy ra mỗi khi vòng lặp chạy. Sử dụng vòng từ khóa để đặt phần cuối của khối mã của vòng lặp.

Khi bạn chạy toàn bộ mã, 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.

Đầu ra dạng bảng trong bảng tính Excel

Đây là toàn bộ mã để tham khảo:

dừng mã quản lý bộ nhớ win 10 
  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 một nút để chạy mã

Khi mã không hoạt động, bạn có thể tạo một nút trong sổ làm việc Excel của mình để chạy nó chỉ bằng một cú nhấp chuột. Chèn hình dạng ưa thích của bạn vào một trang tính Excel trống, nhấp chuột phải vào nó và nhấp vào biểu tượng Gán Macro lựa chọn.

Nút hình chữ nhật trên bảng tính Excel có danh sách các tùy chọn

Trong hộp thoại, chọn tên của quy trình phụ của bạn và nhấp vào ĐƯỢC RỒI .

Hộp macro mở trên bảng tính excel

Khi bạn muốn chạy mã của mình, hãy nhấp vào nút để xem cách dữ liệu được điền ngay lập tức.

Sử dụng Excel VBA để tự động hóa các chức năng tra cứu

Excel VBA là một ngôn ngữ linh hoạt, được điều chỉnh tốt để tự động hóa dễ dàng hơn trong các khía cạnh khác nhau của MS Excel. Nhiều tùy chọn tồn tại trong MS Excel VBA, ngay từ việc tự động hóa các hàm Excel để tự động tạo các bảng tổng hợp phức tạp.

Khi làm việc với các phân đoạn Excel khác nhau, bạn có thể thử nghiệm nhiều tùy chọn khác nhau để giúp cuộc sống của bạn dễ dàng và hiệu quả hơn.