Phương thức Find trong VBA thay thế Vlookup chạy chậm, nguyên nhân và giải pháp

[Cá ngừ F1]

Xin chào các anh chị trên GPE
- Căn cứ Topic "Thắc mắc về VBA tìm kiếm thay Vlookup" của tác giả: gianghoxaotra

Tôi xin phép lấy File dữ liệu của tác giả để mở Topic này.

Tôi có dùng phương thức Find trong VBA để tìm kiếm thay cho Vlookup trên bảng tính. Tuy nhiên code chạy ì ạch (mất gần 2 phút, dùng DIC thì rẹt phát xong). Vậy em hỏi các anh/chị có biết nguyên nhân và giải pháp khắc phục.

Sub Find_Test()


    Dim i&, Rng As Range, Arr(), KQ(), t As Double
    
    t = Timer
    
    Arr = Range(Sheet7.[AK2], [AK100000].End(3))
    
    ReDim KQ(1 To UBound(Arr), 1 To 1)
    
    For i = 1 To UBound(Arr)
        
        Set Rng = Sheet9.[A:A].Find(Arr(i, 1), , , 1)
        
        If Not Rng Is Nothing Then
        
            KQ(i, 1) = Rng.Offset(, 1)
            
        End If


    Next
    
    Sheet7.[AP2].Resize(i - 1, 1) = KQ
    
    MsgBox Timer - t
    
End Sub

Em xin cảm ơn.

 

[huuthang_bd]

Xin chào các anh chị trên GPE
- Căn cứ Topic "Thắc mắc về VBA tìm kiếm thay Vlookup" của tác giả: gianghoxaotra

Tôi xin phép lấy File dữ liệu của tác giả để mở Topic này.

Tôi có dùng phương thức Find trong VBA để tìm kiếm thay cho Vlookup trên bảng tính. Tuy nhiên code chạy ì ạch (mất gần 2 phút, dùng DIC thì rẹt phát xong). Vậy em hỏi các anh/chị có biết nguyên nhân và giải pháp khắc phục.

Sub Find_Test()


    Dim i&, Rng As Range, Arr(), KQ(), t As Double
    
    t = Timer
    
    Arr = Range(Sheet7.[AK2], [AK100000].End(3))
    
    ReDim KQ(1 To UBound(Arr), 1 To 1)
    
    For i = 1 To UBound(Arr)
        
        Set Rng = Sheet9.[A:A].Find(Arr(i, 1), , , 1)
        
        If Not Rng Is Nothing Then
        
            KQ(i, 1) = Rng.Offset(, 1)
            
        End If


    Next
    
    Sheet7.[AP2].Resize(i - 1, 1) = KQ
    
    MsgBox Timer - t
    
End Sub

Em xin cảm ơn.

Tôi nghĩ về nguyên tắc thì Find cũng phải duyệt qua từng ô của dữ liệu, duyệt đến khi nào thỏa điều kiện thì dừng. Như vậy, nếu bạn tìm kiếm 1000 giá trị thì Excel phải duyệt qua bảng dữ liệu 1000 lần.

Còn dùng Dictionary thì khác, bạn chỉ duyệt qua dữ liệu 1 lần và lưu giá trị đó lại. Cần thì chỉ lấy ra xài thôi.

 

[ndu96081631]

Xin chào các anh chị trên GPE
- Căn cứ Topic "Thắc mắc về VBA tìm kiếm thay Vlookup" của tác giả: gianghoxaotra

Tôi xin phép lấy File dữ liệu của tác giả để mở Topic này.

Tôi có dùng phương thức Find trong VBA để tìm kiếm thay cho Vlookup trên bảng tính. Tuy nhiên code chạy ì ạch (mất gần 2 phút, dùng DIC thì rẹt phát xong). Vậy em hỏi các anh/chị có biết nguyên nhân và giải pháp khắc phục.

Sub Find_Test()


    Dim i&, Rng As Range, Arr(), KQ(), t As Double
    
    t = Timer
    
    Arr = Range(Sheet7.[AK2], [AK100000].End(3))
    
    ReDim KQ(1 To UBound(Arr), 1 To 1)
    
    For i = 1 To UBound(Arr)
        
        Set Rng = Sheet9.[A:A].Find(Arr(i, 1), , , 1)
        
        If Not Rng Is Nothing Then
        
            KQ(i, 1) = Rng.Offset(, 1)
            
        End If


    Next
    
    Sheet7.[AP2].Resize(i - 1, 1) = KQ
    
    MsgBox Timer - t
    
End Sub

Em xin cảm ơn.

Lưu ý chỗ này

Set Rng = Sheet9.[COLOR=#ff0000][A:A][/COLOR].Find(Arr(i, 1), , , 1)

Tìm kiếm trên Range phải biết "tiết kiệm". Chơi nguyên 1 cột 1,048,576 dòng thì làm sao Excel nó chịu nỗi chứ

 

[Cá ngừ F1]

Lưu ý chỗ này

Set Rng = Sheet9.[COLOR=#ff0000][A:A][/COLOR].Find(Arr(i, 1), , , 1)

Tìm kiếm trên Range phải biết "tiết kiệm". Chơi nguyên 1 cột 1,048,576 dòng thì làm sao Excel nó chịu nỗi chứ

Kể cả cho nhỏ thôi mà vẫn ko ăn thua ạh

 

[ndu96081631]

Kể cả cho nhỏ thôi mà vẫn ko ăn thua ạh

Sao không ăn thua chứ. Nếu cho Range giới hạn trong 5000 dòng thì thời giản sẽ giảm đáng kể. Tôi test kết quả trong 13s

 

[Cá ngừ F1]

Sao không ăn thua chứ. Nếu cho Range giới hạn trong 5000 dòng thì thời giản sẽ giảm đáng kể. Tôi test kết quả trong 13s

Hí hí. 13s với bài toán này thì quá ... tồi tệ.
2s có thể chấp nhận được.
e đang lăn tăn có phải do lookupvalue đang là dạng chuỗi dài nên ảnh hưởng đến khả năng duyệt code.

 

[ndu96081631]

Hí hí. 13s với bài toán này thì quá ... tồi tệ.
2s có thể chấp nhận được.
e đang lăn tăn có phải do lookupvalue đang là dạng chuỗi dài nên ảnh hưởng đến khả năng duyệt code.

Duyệt trên Range thì nó chỉ vậy thôi
Với bài toán phải xử lý trên dữ liệu lớn, chỉ nên nghĩ đến Array và nên quên đi những gì có liên quan đến Range
Vậy thôi!

 

[Let'GâuGâu]

Hí hí. 13s với bài toán này thì quá ... tồi tệ.
2s có thể chấp nhận được.
e đang lăn tăn có phải do lookupvalue đang là dạng chuỗi dài nên ảnh hưởng đến khả năng duyệt code.

nói chung chơi trên range thì ko ăn thua, ngay cả chơi cặp này dzô

With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

thử dùng hàm match, tốc độ tăng đáng kể

Sub Find_Test()

    Dim i&, FRng, Rng As Range, Arr(), KQ(), FArr(), t As Double, k
  
    t = Timer
    
    Arr = Range(Sheet7.[AK2], [AK100000].End(3))
    
    ReDim KQ(1 To UBound(Arr), 1 To 1)
     With Sheet9
        Set FRng = .Range(.[A2], .[A2].end(4))
        FArr = FRng.Resize(, 2).Value
    End With
    For i = 1 To UBound(Arr)
      k = Application.Match(Arr(i, 1), FRng, 0)
        If TypeName(k) <> "Error" Then
            KQ(i, 1) = FArr(k, 2)
        End If

    Next
    
    Sheet7.[AP2].Resize(i - 1, 1) = KQ
    
    MsgBox Timer - t
   
    
End Sub

 

[Lê Duy Thương]

Thật ra nếu muốn học code thì cứ thử sử dụng thôi .chứ hỏng thay thế được vlookup đâu.

 

[ndu96081631]

Thật ra nếu muốn học code thì cứ thử sử dụng thôi .chứ hỏng thay thế được vlookup đâu.

Thật ra thì không ai có ý định viết "cái gì đó" thay VLOOKUP đâu (lúc trước cũng từng tranh luận vụ này rồi)
Bài toán thực tế là: Người ta muốn dò tìm gì đó trên bảng tính và nếu dùng hàm có sẵn thì người ta sẽ dùng VLOOKUP. Vấn đề là với dữ liệu lớn, kéo fill quá nhiều dòng sẽ khiến cho bảng tính ì ạch. Vậy là người ta nghĩ đến code VBA, dò tìm sao đó như VLOOKUP nhưng sẽ biến mọi kết quả thành Values hết để bảng tính không nặng
Chỉ vậy thôi

 

[ChanhTQ@]

Em đang lăn tăn có phải do lookupvalue đang là dạng chuỗi dài nên ảnh hưởng đến khả năng duyệt code.

2ủa là chuỗi tìm kiếm mà dài cũng có ảnh hưởng đến tốc độ tìm kiếm;

Nếu cùng số lượng dữ liệu cần tìm & lượng dòng nơi fải tìm thì tìm các mã chỉ 5 kí tự & kí số như dạng NVT00. . . . NVTZZ (độ dài giảm từ 12 -> 5) thì thời gian chỉ tiêu tốn fân nữa.

(Chuyện này làm cho vui thôi & chưa thử trong cách xài mảng. Nhưng nói 1 điều rằng: Tạo mã nên cân nhắc đến tính "khoa học" của nó)

 

[Cá ngừ F1]

Thật ra nếu muốn học code thì cứ thử sử dụng thôi .chứ hỏng thay thế được vlookup đâu.

Dạ, em đâu có ý định thay thế hàm vlookup trên bảng tính đâu anh. Chỉ là ko muốn dùng hàm số trên bảng tính để dữ liệu mượt mà hơn thôi. Hì

Xin cảm ơn mọi người, em rút ra kết luận, phương thức find chỉ nên thực hiện với những bảng tính nhỏ, dữ liệu cỡ 5000 dòng thì tạm ổn. Còn không cứ chơi anh đít to cho khoẻ.

p/s: xin cảm ơn, chúc cả nhà cuối tuần vui vẻ...