Chào các bạn, mình tham gia diễn đàn đã lâu, học hỏi được nhiều thứ từ các anh chị và các bạn. Hôm nay nhân lúc uống tý rượu, mình xin chia sẻ những tìm hiểu của mình về mảng động, một tính năng sắp áp dụng trên excel. Các công thức mới có thể dễ dàng tìm trên Google nên mình chỉ viết về hàm tự tạo (UDF) trong VBA.
Như chúng ta đã biết, khi viết hàm tự tạo và sử dụng trên sheet, Excel từ trước tới nay không cho phép thay đổi giá trị của các ô trên sheet trừ ô gọi hàm (không tính cách sử dụng công thức mảng, công thức mảng cũng không linh hoạt vì ta không biết có bao nhiêu giá trị trả về). Để “lách luật”, có lẽ cách đơn giản nhất là sử dụng Evaluate, hàm này sẽ gọi một thủ tục phụ để trả về giá trị. Ví dụ sau đây là hàm MultiVlookup tương tự Vlookup nhưng trả về nhiều giá trị nếu trùng khớp, hàm nhận đối số thứ nhất là giá trị dò, đối số thứ 2 là một cột, đối số thứ 3 có thể âm hay dương là vị trí tương đối của cột kết quả so với cột dò tìm tương tự như tham số của OFFSET, áp dụng cho mọi phiên bản Excel (hàm này mình tự viết chỉ để làm ví dụ nên không test kỹ, có thể còn một số lỗi, cái mà các bác hay gọi là "hàm co giãn" cũng tương tự):
Ta thấy xây dựng hàm trên khá phức tạp, mặc dù nếu là Sub thì quá đơn giản. Tuy nhiên tới đây, chức năng mảng động sẽ thay đổi căn bản các công thức Excel, 1 hàm có sẵn hoặc UDF có thể trả về mảng mà không cần dùng công thức mảng. Nếu là UDF thì chỉ việc gán kết quả là mảng, Excel sẽ xuất toàn bộ kết quả. Hiện tính năng này mới áp dụng cho Excel 365 đã đăng ký Office insider, bạn nào đang sử dụng phiên bản này có thể thử ví dụ sau đây MultiVlookup365 đơn giản hơn rất nhiều:
Mong nhận được ý kiến đóng góp của các bạn.
Như chúng ta đã biết, khi viết hàm tự tạo và sử dụng trên sheet, Excel từ trước tới nay không cho phép thay đổi giá trị của các ô trên sheet trừ ô gọi hàm (không tính cách sử dụng công thức mảng, công thức mảng cũng không linh hoạt vì ta không biết có bao nhiêu giá trị trả về). Để “lách luật”, có lẽ cách đơn giản nhất là sử dụng Evaluate, hàm này sẽ gọi một thủ tục phụ để trả về giá trị. Ví dụ sau đây là hàm MultiVlookup tương tự Vlookup nhưng trả về nhiều giá trị nếu trùng khớp, hàm nhận đối số thứ nhất là giá trị dò, đối số thứ 2 là một cột, đối số thứ 3 có thể âm hay dương là vị trí tương đối của cột kết quả so với cột dò tìm tương tự như tham số của OFFSET, áp dụng cho mọi phiên bản Excel (hàm này mình tự viết chỉ để làm ví dụ nên không test kỹ, có thể còn một số lỗi, cái mà các bác hay gọi là "hàm co giãn" cũng tương tự):
Mã:
Option Explicit
Private KQ()
Function MultiVlookup(LookupValue, LookupCol As Range, Col&)
Dim arr1(), arr2(), i&, k&, n&
n = LookupCol.Rows.Count
arr1 = LookupCol.Value
arr2 = LookupCol.Offset(, Col).Value
ReDim KQ(1 To n, 1 To 1)
Application.Volatile
For i = 1 To n
If arr1(i, 1) = LookupValue Then
If k = 0 Then
MultiVlookup = arr2(i, 1)
Else
KQ(k, 1) = arr2(i, 1)
End If
k = k + 1
End If
Next
If k > 1 Then Evaluate "xxx(" & Application.Caller.Offset(1).Resize(k - 1).Address(0, 0) & ")"
End Function
Sub xxx(Cl As Range)
Cl = KQ
End Sub
Mã:
Function MultiVlookup365(LookupValue, LookupCol As Range, Col&)
Dim arr1(), arr2(), arrKQ(), i&, k&, n&
n = LookupCol.Rows.Count
arr1 = LookupCol.Value
arr2 = LookupCol.Offset(, Col).Value
ReDim arrKQ(1 To n)
For i = 1 To n
If arr1(i, 1) = LookupValue Then
k = k + 1
arrKQ(k) = arr2(i, 1)
End If
Next
If k = 0 Then Exit Function
ReDim Preserve arrKQ(1 To k)
MultiVlookup365 = Application.WorksheetFunction.Transpose(arrKQ)
End Function
Lần chỉnh sửa cuối:
