Giúp đỡ hàm tìm kiếm Lookup bằng VBA (6 người xem)

gianghoxaotra · 13/10/15

Hiện tại em đang viết một code VBA thay thế cho hàm VLookup vì hàm Vlookup chạy nhiều chậm máy quá, nhờ các cao thủ trên đây giúp đỡ vì hàm viết không chạy được.
Em mời vào nghề nên cần các cao thủ chỉ giáo dùm thêm. Cảm ơn mọi người.

Đây là Code VBA:
Function MTlookup(Source As Range, VTCV As Integer, Data As Range, ValueCell As Range)

Dim KQ As Object

ReDim KQ(1 To UBound(Data), 1 To 1)

Set Dic = CreateObject("scripting.dictionary")

For i = 1 To UBound(Source)
If Not Dic.exists(CStr(Source(i, VTCV))) Then

Dic.Add CStr(Source(i, VTCV)), i

End If

'Dic(CStr(Source(i, 1))) = i

Next

For i = 1 To UBound(Data)
Itm = CStr(Data(i, 1))
If Dic.exists(Itm) Then

KQ(i, 1) = Source(Dic.Item(Itm), 1)

End If

Next

ValueCell.Resize(i - 1, 1) = KQ

End Function

Cá ngừ F1 · 13/10/15

bạn đính lèm file xem sao. Chứ tôi nhìn cái code này phải là 1 thủ tục chứ sao là hàm số được !$@!!

gianghoxaotra · 13/10/15

Mình gửi file lên nhé, vì mục đích là muốn viết cái hàm thay cho Vlookup để nó chạy nhanh hơn Vlookup.

Cá ngừ F1 · 13/10/15

gianghoxaotra đã viết:
Mình gửi file lên nhé, vì mục đích là muốn viết cái hàm thay cho Vlookup để nó chạy nhanh hơn Vlookup.

Thường thì hay dùng phương thức nào đó thay hàm số để excel không phải tính toán trên bảng tính thôi. chứ Không có hàm nào tốt hơn hàm Vlookup đâu bạn

gianghoxaotra · 17/10/15

Mình viết cái Function này nó chạy mà sao ra kết quả toàn là #Value!, nhờ anh em trên đây xem dùm giúp với. Cảm ơn.

Function Tlookup(Source As Range, VTCV As Integer, Data As Range)

Dim N As Integer, M As Integer

N = Sourse.Rows.Count
M = Data.Rows.Count

ReDim KQ(1 To M, 1 To 1)

Set Dic = CreateObject("scripting.dictionary")

For i = 1 To N
If Not Dic.exists(CStr(Source(i, VTCV))) Then

Dic.Add CStr(Source(i, VTCV)), i

End If

Next

For i = 1 To M
Itm = CStr(Data(i, 1))
If Dic.exists(Itm) Then

KQ(i, 1) = Source(Dic.Item(Itm), 1)

End If

Next

Tlookup = KQ

End Function

mhung12005 · 17/10/15

gianghoxaotra đã viết:
Mình viết cái Function này nó chạy mà sao ra kết quả toàn là #Value!, nhờ anh em trên đây xem dùm giúp với. Cảm ơn.

Function Tlookup(Source As Range, VTCV As Integer, Data As Range)

Dim N As Integer, M As Integer

N = Sourse.Rows.Count
M = Data.Rows.Count

ReDim KQ(1 To M, 1 To 1)

Set Dic = CreateObject("scripting.dictionary")

For i = 1 To N
If Not Dic.exists(CStr(Source(i, VTCV))) Then

Dic.Add CStr(Source(i, VTCV)), i

End If

Next

For i = 1 To M
Itm = CStr(Data(i, 1))
If Dic.exists(Itm) Then

KQ(i, 1) = Source(Dic.Item(Itm), 1)

End If

Next

Tlookup = KQ

End Function

Bạn thử sửa chỗ màu đỏ xem sao. Ngoài ra bạn chưa nên khai báo tất cả các biiến.
Mà mỉnh đang thắc mắc không biết bạn dùng cái này vận dụng vào việc gì nhỉ ?

langtuchungtinh360 · 17/10/15

gianghoxaotra đã viết:
Mình gửi file lên nhé, vì mục đích là muốn viết cái hàm thay cho Vlookup để nó chạy nhanh hơn Vlookup.

anh Bill làm ra hàm vlookup thì cũng đã thử nghiệm tốc độ hết rồi bác à! cái đầu mình sao bằng đầu của mấy cha làm cho anh Bill được mà muốn nhanh hơn.

gianghoxaotra · 17/10/15

mhung12005 đã viết:
Bạn thử sửa chỗ màu đỏ xem sao. Ngoài ra bạn chưa nên khai báo tất cả các biiến.
Mà mỉnh đang thắc mắc không biết bạn dùng cái này vận dụng vào việc gì nhỉ ?

Hic, vẫn không được.
Nếu hàm này chạy được, chỉ cần đánh 1 câu lệnh có thể ra cả trăm nghìn kết quả bên dưới. Nhanh hơn rất nhiều.

giangleloi · 17/10/15

gianghoxaotra đã viết:
Hic, vẫn không được.
Nếu hàm này chạy được, chỉ cần đánh 1 câu lệnh có thể ra cả trăm nghìn kết quả bên dưới. Nhanh hơn rất nhiều.

Có lẽ do bạn chưa khai báo hết biến, bởi bạn nói xuông không có nói lỗi là lỗi như thế nào, không chạy được là không chạy được chỗ nào nên đến #10 vẫn chưa đi đến đâu.

gianghoxaotra · 17/10/15

giangleloi đã viết:
Có lẽ do bạn chưa khai báo hết biến, bởi bạn nói xuông không có nói lỗi là lỗi như thế nào, không chạy được là không chạy được chỗ nào nên đến #10 vẫn chưa đi đến đâu.

Mình gửi file lên đây, bạn xem thử. Cảm ơn nhé.

Ba Tê · 17/10/15

gianghoxaotra đã viết:
Hic, vẫn không được.
Nếu hàm này chạy được, chỉ cần đánh 1 câu lệnh có thể ra cả trăm nghìn kết quả bên dưới. Nhanh hơn rất nhiều.

Có lẽ bạn nhầm cái Function() với Sub.
Tôi nghĩ câu bạn nói "chỉ cần đánh 1 câu lệnh có thể ra cả trăm nghìn kết quả bên dưới" chỉ có thể dùng Sub, Nếu viết 1 Function() thay thế Vlookup() của anh Bill thì quá "siêng".

VetMini · 17/10/15

Không phải là không được. Vì hàm Vlookup phải chạy tổng quát. Nếu bạn viết hàm đăc biệt cho đúng tình huống của mình thì có thể nhanh hơn thật. Ví dụ trường hợp cái bảng lookup của bạn có rất nhiều trị lặp lại. Vlookup phải duyệt qua các trị này. Bạn thâu nó về Dictionary thì giảm thiểu số lookup.

Tuy nhiên code trên người viết hoàn toàn không biết gì về phạm vi đời sống của biến. Mỗi lần gọi hàm, lại phải lập lại Dictionary và do đó phải đọc nguyên range lại từ đầu. Chả tiết kiệm được gì cả. Nếu muốn tiết kiệm thì phải có cách giữ biến Dictionary không bị tiêu huỷ khi thoát khỏi hàm. Đó là nhiệm vụ của biến static.

Nếu dùng sub thì nói như bạn Ba Tê trên là đúng rồi. Lập Dictionary 1 lần dùng cho cả range kết quả. Tiết kiệm được nhiều.

doveandrose · 17/10/15

VetMini đã viết:
Không phải là không được. Vì hàm Vlookup phải chạy tổng quát. Nếu bạn viết hàm đăc biệt cho đúng tình huống của mình thì có thể nhanh hơn thật. Ví dụ trường hợp cái bảng lookup của bạn có rất nhiều trị lặp lại. Vlookup phải duyệt qua các trị này. Bạn thâu nó về Dictionary thì giảm thiểu số lookup.

Tuy nhiên code trên người viết hoàn toàn không biết gì về phạm vi đời sống của biến. Mỗi lần gọi hàm, lại phải lập lại Dictionary và do đó phải đọc nguyên range lại từ đầu. Chả tiết kiệm được gì cả. Nếu muốn tiết kiệm thì phải có cách giữ biến Dictionary không bị tiêu huỷ khi thoát khỏi hàm. Đó là nhiệm vụ của biến static.

Nếu dùng sub thì nói như bạn Ba Tê trên là đúng rồi. Lập Dictionary 1 lần dùng cho cả range kết quả. Tiết kiệm được nhiều.

bài này nếu dùng hàm thì điểm hấp dẫn không nằm ở chỗ làm sao lập Dictionary 1 lần . Mà hấp dẫn ở chỗ làm sao khi thay đổi giá trị bảng dò thì giá trị hàm lookUpChe cũng thay đổi theo . hi hi

VetMini · 17/10/15

Tôi có nói từ đầu là Vlookup làm việc tổng quát. Hàm viết gọn lại chỉ hiệu nghiệm với trường hợp đặc thù của mình thôi.

gianghoxaotra · 19/10/15

VetMini đã viết:
Không phải là không được. Vì hàm Vlookup phải chạy tổng quát. Nếu bạn viết hàm đăc biệt cho đúng tình huống của mình thì có thể nhanh hơn thật. Ví dụ trường hợp cái bảng lookup của bạn có rất nhiều trị lặp lại. Vlookup phải duyệt qua các trị này. Bạn thâu nó về Dictionary thì giảm thiểu số lookup.

Tuy nhiên code trên người viết hoàn toàn không biết gì về phạm vi đời sống của biến. Mỗi lần gọi hàm, lại phải lập lại Dictionary và do đó phải đọc nguyên range lại từ đầu. Chả tiết kiệm được gì cả. Nếu muốn tiết kiệm thì phải có cách giữ biến Dictionary không bị tiêu huỷ khi thoát khỏi hàm. Đó là nhiệm vụ của biến static.

Nếu dùng sub thì nói như bạn Ba Tê trên là đúng rồi. Lập Dictionary 1 lần dùng cho cả range kết quả. Tiết kiệm được nhiều.

Biến Static là sao vậy bạn, bạn cụ thể chút được không? Bạn có thể chia sẻ tài liệu nào nói về cái này ko? Cảm ơn bạn.

gtri · 19/10/15

gianghoxaotra đã viết:
Mình viết cái Function này nó chạy mà sao ra kết quả toàn là #Value!, nhờ anh em trên đây xem dùm giúp với. Cảm ơn.

Function Tlookup(Source As Range, VTCV As Integer, Data As Range)

Dim N As Integer, M As Integer

N = Sourse.Rows.Count
M = Data.Rows.Count

ReDim KQ(1 To M, 1 To 1)

Set Dic = CreateObject("scripting.dictionary")

For i = 1 To N
If Not Dic.exists(CStr(Source(i, VTCV))) Then

Dic.Add CStr(Source(i, VTCV)), i

End If

Next

For i = 1 To M
Itm = CStr(Data(i, 1))
If Dic.exists(Itm) Then

KQ(i, 1) = Source(Dic.Item(Itm), 1)

End If

Next

Tlookup = KQ

End Function

Sửa khai báo integer thành long hoặc double xem sao

gianghoxaotra · 23/10/15

gtri đã viết:
Sửa khai báo integer thành long hoặc double xem sao

Mình sửa thì nó ra kết quả rồi, nhưng chỉ ra kết quả tại dòng đó thôi chứ không ra được cả mảng kết quả như mong muốn.
Và mình thử dùng theo chức năng Ctrl + Shift + Enter theo công thức mảng thì kết quả ra cả mảng luôn, nhanh bất ngờ. -+*/

VetMini · 23/10/15

gianghoxaotra đã viết:
Mình sửa thì nó ra kết quả rồi, nhưng chỉ ra kết quả tại dòng đó thôi chứ không ra được cả mảng kết quả như mong muốn.
Và mình thử dùng theo chức năng Ctrl + Shift + Enter theo công thức mảng thì kết quả ra cả mảng luôn, nhanh bất ngờ.

Nạp công thức cho một vùng thì dùng Ctrl+Enter. Dùng Ctrl+Shift+Enter sẽ làm cho cả vùng nối với nhau thành 1 mảng.

Cá ngừ F1 · 23/10/15

gianghoxaotra đã viết:
Mình sửa thì nó ra kết quả rồi, nhưng chỉ ra kết quả tại dòng đó thôi chứ không ra được cả mảng kết quả như mong muốn.
Và mình thử dùng theo chức năng Ctrl + Shift + Enter theo công thức mảng thì kết quả ra cả mảng luôn, nhanh bất ngờ.

Hình như bạn chưa phân biệt được đâu là hàm đâu là thủ tục ?

1 hàm sẵn có hay tự tạo thì cũng chỉ cho ra kết quả ở ô đặt hàm số (tôi cũng chưa thấy hàm nào mà nhập hàm ở 1 ô mà cho kết quả ở nhiều ô, hoặc có nhưng tôi chưa biết)

việc bạn muốn nói cho kết quả ở cả vùng tôi hiểu là chạy 1 thủ tục để cho ra kết quả mà thôi.

gianghoxaotra · 23/10/15

Cảm ơn các bạn cao thủ đã giúp đỡ. __--__

Giúp đỡ hàm tìm kiếm Lookup bằng VBA (6 người xem)

Người dùng đang xem chủ đề này

Thành viên chính thức

( ͡° ͜ʖ ͡°)

Thành viên chính thức

( ͡° ͜ʖ ͡°)

Thành viên chính thức

Thành viên chậm chạm

Thành viên đã dừng hoạt động

Thành viên chính thức

Thành viên gắn bó

Thành viên chính thức

Cạo Rồi Khỏi Gội

Ăn cùng góc phố

hello

Ăn cùng góc phố

Thành viên chính thức

Thành viên tích cực

Thành viên chính thức

Ăn cùng góc phố

( ͡° ͜ʖ ͡°)

Thành viên chính thức

Đếm ngược thời gian