Ở bài viết trước chúng ta đã đi tìm hiểu về cách sử dụng cũng như ví dụ về hàm Vlookup trong excel rồi, và trong bài viết đó cũng đã đề cập tới sự hạn chế của hàm Vlookup, do vậy ở bài viết này sẽ giới thiệu đến với các bạn một hàm tự định nghĩa bằng cách sử dụng code VBA để bổ sung điểm hạn chế của hàm Vlookup và tên của nó là MyVlookup.
Như các bạn thấy ở đây, ở bên Bảng Tính thì có các Mã Hàng, và cột Hóa Đơn, còn phía bên Giá Trị Dò có Mã Hàng và số Hóa Đơn. Có rất nhiều mã hàng có nhiều hơn hai số hóa đơn.
Nếu các bạn dò theo cách thông thường là dùng hàm Vlookup như ví dụ minh họa bên dưới cho các bạn thấy ở đây thì kết quả sẽ trả về đúng một giá trị mà nó tìm thấy đầu tiên.
Trong khi đó bạn thấy Mã Hàng A01 xuất hiện tới 4 lần với 4 số hóa đơn khác nhau. Nên nếu dò như vậy thì sẽ cho kết quả không chính xác.
Chính vì lý do đó nên Thủ Thuật Excel sẽ giới thiệu với các bạn một hàm tự định nghĩa sẽ thay thế Vlookup làm điều này.
Vì sẽ dùng VBA nên việc đầu tiên các bạn kích hoạt VBA lên bằng cách vào Developer/Visual Basic.
Hoặc nhấn phím tắt Alt + F11
Khi chương trình VBA chạy các bạn chọn Insert/Module
Sau đó thì copy đoạn code bên dưới và dán vào khung soạn thảo của VBA
Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
If r = lookupval Then
result = result & " " & r.Offset(0, indexcol - 1)
End If
Next r
MYVLOOKUP = result
End Function
Sau đó click vào biểu tượng hình tam giác màu xanh như hình hoặc nhấn F5
Đặt tên cho macros khi được hỏi, các bạn nhớ đặt tên cho chính xác là MYVLOOKUP, viết hoa như vậy luôn, không thì nó không chạy được đâu. Sau đó chọn Create
Nhấn OK khi xuất hiện thông báo này.
Quay trở lại file excel lúc nãy, bây giờ ở ô C3 các bạn gõ công thức như sau:
=MYVLOOKUP(B3;F3:F21;2)
Giải thích:
➤ MYVLOOKUP: Công thức tự định nghĩa dựa vào code VBA
➤ B3: Giá trị Mã Hàng cần dò
➤ F3:F21: Giá trị dùng để dò
➤ 2: Chính là cột thứ 2 chứa số Hóa Đơn cần để dò, các bạn cứ đếm từ vị trí F3:F21 qua bao nhiêu cột thì chọn bấy nhiêu nhé.
Và đây chính là kết quả tìm được sau khi sử dụng hàm tự tạo nêu trên. Bạn thấy Mã Hàng A01 (bôi xanh) xuất hiện tới 4 số hóa đơn và được dò tìm đầy đủ rồi nhé.
Copy công thức xuống các ô còn lại ta được kết quả như sau
Nếu các bạn muốn sử dụng công thức này cho các bảng tính khác như một hàm có sẵn trong excel thì hãy tạo Add-Ins cho nó mà sử dụng nhé và chú ý khi gửi qua máy khác thì hãy copy và dán value nhé.
Vậy là chúng ta vừa đi tìm hiểu về một hàm tự định nghĩa trong excel dựa vào vba rồi, qua đây cũng cho các bạn thấy sức mạnh của vba trong excel, nếu muốn làm chủ excel thì hãy dành thời gian học vba các bạn nhé. Chúc các bạn thành công.
