Xin viết dùng mã vba thay thế cho hàm vlookup

ndu96081631 · 14/4/12

tiendo1988 đã viết:
BÁc ndu96081631 Hướng dẫn cụ thể cách thêm For Each Clls in Target như thế nào?
“...có thể dùng Array để tăng tốc bảng tính” : bác gúp em và mọi người đoạn code để học hỏi thêm.
Nhờ bác cùng các Pro tùy biến giúp bài của em bên Topic Cần hướng dẫn và trợ giúp về sử dụng Vlookup trong VBA excel!

Xin cảm ơn!

Làm thử trên file của bạn nhé:
Mô tả:
- Nhập liệu tại cột C
- Cột D, E, G, H, I và N là những cột cần lookup
- Vậy, nếu nhập liệu 1 hoặc nhiều cell trên cột C thì những cột D, E, G, H, I và N với dòng tương ứng sẽ lấy dữ liệu từ sheet LLNV gán vào
- Nếu 1 hoặc nhiều cell trên 1 C bị xóa thì thì những cột D, E, G, H, I và N với dòng tương ứng cũng sẽ bị xóa theo
Mô tả đúng chứ?
Nếu là vậy thì tôi để xuất code thế này:
1> Nạp Dictionary

PHP:

Public Chk As Boolean, Dic As Object, aResult()
Sub Auto_Open()
  Dim wks As Worksheet, SrcRng As Range, sArray
  Dim lR As Long, i As Long, n As Long, tmp
  On Error Resume Next
  Set wks = Sheets("LLNV")
  Set SrcRng = wks.Range("B6:R1000")
  sArray = SrcRng.Value
  ReDim aResult(1 To UBound(sArray, 1), 1 To UBound(sArray, 2))
  Set Dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(sArray, 1)
    If CStr(sArray(i, 1)) <> "" Then
      tmp = sArray(i, 1)
      If Not Dic.Exists(tmp) Then
        lR = lR + 1
        Dic.Add tmp, lR
        aResult(lR, 1) = tmp
        aResult(lR, 2) = sArray(i, 2)
        aResult(lR, 3) = sArray(i, 3)
        aResult(lR, 5) = sArray(i, 5)
        aResult(lR, 6) = sArray(i, 6)
        aResult(lR, 14) = sArray(i, 14)
        aResult(lR, 13) = sArray(i, 13)
      End If
    End If
  Next
End Sub

2> Theo dỏi những thay đổi tại Sheet LLNV (để cập nhật lại Dictionary)

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
  Chk = True
End Sub

PHP:

Private Sub Worksheet_Deactivate()
  If Chk Then
    Auto_Open
    Chk = False
  End If
End Sub

3> Nhập liệu và fill dữ liệu tại sheet ChiTiet

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rTarget As Range, aTarget, i As Long, n As Long
  Dim Arr1(), Arr2(), Arr3(), tmp
  On Error Resume Next
  If Dic Is Nothing Then Auto_Open
  If Not Intersect(Range("C6:C1000"), Target) Is Nothing Then
    Set rTarget = Intersect(Range("C6:C1000"), Target)
    If IsArray(rTarget.Value) Then
      aTarget = rTarget.Value
    Else
      ReDim aTarget(1 To 1, 1 To 1)
      aTarget(1, 1) = rTarget.Value
    End If
    ReDim Arr1(1 To UBound(aTarget, 1), 1 To 2)
    ReDim Arr2(1 To UBound(aTarget, 1), 1 To 3)
    ReDim Arr3(1 To UBound(aTarget, 1), 1 To 1)
    For i = 1 To UBound(aTarget, 1)
      If aTarget(i, 1) <> "" Then
        tmp = aTarget(i, 1)
        If Dic.Exists(tmp) Then
          Arr1(i, 1) = aResult(Dic.Item(tmp), 2)
          Arr1(i, 2) = aResult(Dic.Item(tmp), 3)
          Arr2(i, 1) = aResult(Dic.Item(tmp), 5)
          Arr2(i, 2) = aResult(Dic.Item(tmp), 6)
          Arr2(i, 3) = aResult(Dic.Item(tmp), 14)
          Arr3(i, 1) = aResult(Dic.Item(tmp), 13)
        End If
      End If
    Next
    rTarget.Offset(, 1).Resize(, 2).Value = Arr1
    rTarget.Offset(, 4).Resize(, 3).Value = Arr2
    rTarget.Offset(, 11).Resize(, 1).Value = Arr3
  End If
End Sub

Xem file đính kèm và thí nghiệm nhé ---> Có gì sơ sót, ta bàn tiếp
(Nói thiệt, làm mấy bài này chán bỏ xừ... lại hại não)

tiendo1988 · 14/4/12

Cám ơn bác ndu96081631, em d
em đang xem, nếu có gì em không hiểu hay cần giúp đỡ thêm mong bác và các AE giúp đỡ.
Chúc bác và mọi người sức khỏe!

siwtom · 14/4/12

ndu96081631 đã viết:
Nguyên văn bởi siwtom Tôi thấy có vẻ nhiều người hơi lạm dụng Dictionary. Dictionary mạnh và không dễ thay thế được trong một vài trường hợp không có nghĩa là nó tốt cho mọi trường hợp. Tỏi nếu nấu với món "này" thì tuyệt nhưng không có nghĩa là nấu món nào cũng cho tỏi. Không phải mổ trâu, lợn, gà, bóc tỏi, gọt táo đều dùng dao mổ trâu. Lợi thì chắc không mà hại thì nhiều.
Sửa một chút code trên thành
.
Đây chính là lúc dùng đến Dictionary nè!
Tuy nhiên, nếu khéo hơn thì ta chỉ tạo và nạp Dictionary 1 lần duy nhất (nếu Dictionary chưa được tạo) ---> Những lần sau đó của sự kiện Change, chỉ việc "vào" Dic "moi" ra xài thôi

Bạn có để ý là tôi góp ý cho bài của bạn concogia chứ không phải trả lời bạn chủ topic?
Nếu tôi trả lời trực tiếp chủ topic thì dĩ nhiên code đề xuất là của tôi, nhưng tôi chỉ muốn góp ý với bạn concogia mà thôi.
Bạn concogia có viết: "Code trên là viết theo đề bài của bạn hoangvinh_tb". Tức chỉ viết cho vấn đề của chủ topic. Tôi thấy là để làm cái việc mà bạn concogia định làm thì không cần Dictionary. Tôi không khẳng định là để làm cái việc mà chủ topic định làm thì không cần dùng Dictionary. Nếu bạn concogia dùng Dictionary nhưng với code khác thì đã chắc gì tôi góp ý. Tôi không đề xuất cách giải quyết vấn đề của chủ topic, tôi góp ý cho người cụ thể, cho code cụ thể đáp ứng yêu cầu cụ thể bạn ạ. Rõ ràng là để làm cái việc mà code của concogia làm cho yêu cầu cụ thể thì chả cần gì tới Dictionary. Bạn trả lời người khác và dùng Dictionary nhưng tôi không có ý định góp ý vì tôi thấy nó hợp lý. Còn concogia dùng Dictionary trong code cụ thể ấy không hợp lý nên tôi góp ý. Không ai phê phán chuyện dùng Dictonary nhưng sau khi viết code thì nên xem lại xem liệu với cách làm việc như thế thì Dictionary có cần không. Vì rất có thể để làm y nguyên việc như thế không làm ÍT hơn và cũng không NHIỀU hơn thì có thể thay bằng code mới không dùng dictionary.

concogia · 14/4/12

siwtom đã viết:
Tôi thấy có vẻ nhiều người hơi lạm dụng Dictionary. Dictionary mạnh và không dễ thay thế được trong một vài trường hợp không có nghĩa là nó tốt cho mọi trường hợp. Tỏi nếu nấu với món "này" thì tuyệt nhưng không có nghĩa là nấu món nào cũng cho tỏi. Không phải mổ trâu, lợn, gà, bóc tỏi, gọt táo đều dùng dao mổ trâu. Lợi thì chắc không mà hại thì nhiều.
Sửa một chút code trên thành

Mã:

Private Sub Worksheet_Change(ByVal Target As Range) Dim I, Vung, Ws Set Ws = Sheets("MA") Vung = Ws.Range(Ws.[B3], Ws.[B10000].End(xlUp)).Resize(, 4) If Not Intersect(Target, Range("B4:B1000")) Is Nothing Then If Target.Count = 1 Then For I = 1 To UBound(Vung) If Vung(I, 1) = Ucase(Target.Value) Then Target.Offset(, 1) = Vung(I, 2) Target.Offset(, 2) = Vung(I, 3) Target.Offset(, 5) = Vung(I, 4) Exit For End If Next I End If End If End Sub

nếu tôi không lầm thì code tốt hơn. Trong trường hợp xấu nhất thì cũng chỉ phải duyệt (FOR) tất cả các dòng của Vung, còn trong trường hợp tốt nhất thì chỉ duyệt có 1 dòng. Dùng Dictionary như trên luôn phải duyệt tất cả các dòng, rồi với mỗi dòng đó làm "động tác" d.Add ... (thừa)
Nếu số dòng không phải là "vài" mà là "mấy trăm" (mã không phải là A --> Z mà là vd. wxyz) thì chắc chắn code dùng Dictionary như trên sẽ làm nhiều việc hơn, lâu hơn.

Híc, hôm nay mình mới đọc được bài này. Ý của bạn siwtom cũng đúng thôi, thật ra bài này mình muốn thử Item của Dictionary là một Array xem nó như thế nào, xử lý nó có linh hoạt không thôi (trước kia mình hay gom vào một cục, khi xử lý thì dùng Split tách nó ra, sau khi đọc bài của bạn Kyo mình mới biết Item có thể là một Array _ vì toàn mò mẫm tự học_ ) chứ với đề bài trên & dữ liệu không thật lớn thì dùng Find ( bài của chị Hải Yến) hoặc chơi cùi bắp hơn thì dùng Match thì khỏi phải " Pho với Phiếc", "Đít-to với Đít-bé" chi cho rách việc
Thân ái

siwtom · 14/4/12

concogia đã viết:
Híc, hôm nay mình mới đọc được bài này. Ý của bạn siwtom cũng đúng thôi, thật ra bài này mình muốn thử Item của Dictionary là một Array xem nó như thế nào, xử lý nó có linh hoạt không thôi (trước kia mình hay gom vào một cục, khi xử lý thì dùng Split tách nó ra, sau khi đọc bài của bạn Kyo mình mới biết Item có thể là một Array _ vì toàn mò mẫm tự học_ ) chứ với đề bài trên & dữ liệu không thật lớn thì dùng Find ( bài của chị Hải Yến) hoặc chơi cùi bắp hơn thì dùng Match thì khỏi phải " Pho với Phiếc", "Đít-to với Đít-bé" chi cho rách việc
Thân ái

Góp ý cũng chỉ là để lưu ý thôi.
Bạn nói rất đúng. Nhiều khi có thể viết một code tổng quát cho việc của mình rồi bất cứ trường hợp cụ thể nào cũng gọi cái code tổng quát kia nhưng như thế không tối ưu. Nếu nhu cầu của mình cụ thể thì có thể viết code cho trường hợp đó thì sẽ hay hơn. Tôi góp ý cho code cụ thể của bạn nhưng thực chất tôi muốn một điều khác. Có những người chập chững bước vào lập trình. Nếu ta biết thì ta giúp họ phân tích code để hiểu và có thể tối ưu nó. Hoặc nói về một trường hợp khác. Ai đó có một code và hỏi: sao em chạy code mà nó không làm theo ý muốn? Anh A trả lời: Bạn dùng code ở dưới của tôi nhé, chạy vù vù, tối ưu. Anh B nói: Bạn sai ở chỗ này, chỗ này. Anh A đưa ra được code tối ưu, giải quyết được vấn đề cần làm của người hỏi nhưng chỉ thế thôi. Anh B có thể biết viết code tối ưu hơn của người hỏi nhưng anh ta đặt cho mình mục đích khác với mục đích của anh A. Anh ta phân tích code của người hỏi và chỉ ra những chỗ sai. Như thế người hỏi sẽ biết mình sai chỗ nào để trong tương lai không mắc phải nữa. Mục đích của A và B là khác nhau. Rất có thể tôi sẽ là B vì mục đích tôi chọn khác với A

tiendo1988 · 14/4/12

Rất cám ơn anh ndu96081631 đã giúp đỡ về code.
Do kiến thức của em về Code VBA quá hạn chế nên từ chiều đến giờ em ngồi xem code của bác và tìm cách để lookup những cột còn lại (K, L, M, O) mà em chưa làm được. mong anh chỉ cho.
ở trong Sheet ChiTiet, cột N (cột THƯỜNG TRÚ) hình như anh lookup nhầm cột "Nơi cấp" thì phải. anh sửa lại code giúp em nhé.

Nếu em có nhiều Sheet tương tự cần Lookup từ Sheet LLNV, mỗi Sheet có số cột cần lookup khác nhau và chỉ số cột cần lookup cũng khác nhau. Để có thể vận dụng code của anh vào những sheet này thì em cần lưu ý và thay đổi những chỗ nào trong code để đạt được yêu cầu lookup của các Sheet.

Rất mong anh ndu96081631 cùng các Pro trên GPE hướng dẫn thêm.

siwtom · 14/4/12

tiendo1988 đã viết:
Rất cám ơn anh ndu96081631 đã giúp đỡ về code.
Do kiến thức của em về Code VBA quá hạn chế nên từ chiều đến giờ em ngồi xem code của bác và tìm cách để lookup những cột còn lại (K, L, M, O) mà em chưa làm được. mong anh chỉ cho.
ở trong Sheet ChiTiet, cột N (cột THƯỜNG TRÚ) hình như anh lookup nhầm cột "Nơi cấp" thì phải. anh sửa lại code giúp em nhé.

Nếu em có nhiều Sheet tương tự cần Lookup từ Sheet LLNV, mỗi Sheet có số cột cần lookup khác nhau và chỉ số cột cần lookup cũng khác nhau. Để có thể vận dụng code của anh vào những sheet này thì em cần lưu ý và thay đổi những chỗ nào trong code để đạt được yêu cầu lookup của các Sheet.

Rất mong anh ndu96081631 cùng các Pro trên GPE hướng dẫn thêm.

Kiến thức của tôi cũng vô cùng hạn chế nhưng tôi mạo muội có ý kiến. Tôi thấy đoạn

Mã:

If IsArray(rTarget.Value) Then
      aTarget = rTarget.Value
Else
      ReDim aTarget(1 To 1, 1 To 1)
      aTarget(1, 1) = rTarget.Value
End If

có vẻ chưa ổn lắm. Đểm kiểm nghiệm ta thử như sau:
Nếu vd. ND chọn xóa 1 ô ở cột C hoặc một loạt ô ở các dòng liên tiếp thì không sao. Nhưng nếu chọn vài ô không ở các dòng lên tiếp - tức từ "ô đầu" tới "ô cuối" có ô không được chọn để xóa thì code làm không theo ý muốn.
Nếu tôi nhầm lẫn thì xin thứ lỗi cho. Tôi chỉ muốn là nếu cảm nhận của tôi là đúng thì ai dùng cứ dùng nhưng phải ý thức được điều đó.

ndu96081631 · 15/4/12

siwtom đã viết:
Kiến thức của tôi cũng vô cùng hạn chế nhưng tôi mạo muội có ý kiến. Tôi thấy đoạn

Mã:

If IsArray(rTarget.Value) Then aTarget = rTarget.Value Else ReDim aTarget(1 To 1, 1 To 1) aTarget(1, 1) = rTarget.Value End If

có vẻ chưa ổn lắm. Đểm kiểm nghiệm ta thử như sau:
Nếu vd. ND chọn xóa 1 ô ở cột C hoặc một loạt ô ở các dòng liên tiếp thì không sao. Nhưng nếu chọn vài ô không ở các dòng lên tiếp - tức từ "ô đầu" tới "ô cuối" có ô không được chọn để xóa thì code làm không theo ý muốn.
Nếu tôi nhầm lẫn thì xin thứ lỗi cho. Tôi chỉ muốn là nếu cảm nhận của tôi là đúng thì ai dùng cứ dùng nhưng phải ý thức được điều đó.

Ah... cái đoạn code đó không liên quan gì đến vấn đề bạn nói cả... Chẳng qua nếu chuyển toàn bộ sang Array thì phải lưu ý xem Target có phải là 1 cell hay không (nếu Target gồm nhiều cell thì Target.Value là 1 Array nhưng nếu Target là 1 cell thì Target.Value không phải là Array)
Còn vấn đề bạn vừa nói thật ra giải quyết nó cũng không có vấn đề gì... Có điều nếu chuyển mọi thứ sang xử lý mảng thì hơi rắc rối chút ----> Dạng Array trong Array đấy mà
Để từ từ tôi nghiên cứu thêm việc này
Cảm ơn bạn đã nhắc nhở!

ndu96081631 · 15/4/12

tiendo1988 đã viết:
Rất cám ơn anh ndu96081631 đã giúp đỡ về code.
Do kiến thức của em về Code VBA quá hạn chế nên từ chiều đến giờ em ngồi xem code của bác và tìm cách để lookup những cột còn lại (K, L, M, O) mà em chưa làm được. mong anh chỉ cho.
ở trong Sheet ChiTiet, cột N (cột THƯỜNG TRÚ) hình như anh lookup nhầm cột "Nơi cấp" thì phải. anh sửa lại code giúp em nhé.

Nếu em có nhiều Sheet tương tự cần Lookup từ Sheet LLNV, mỗi Sheet có số cột cần lookup khác nhau và chỉ số cột cần lookup cũng khác nhau. Để có thể vận dụng code của anh vào những sheet này thì em cần lưu ý và thay đổi những chỗ nào trong code để đạt được yêu cầu lookup của các Sheet.

Rất mong anh ndu96081631 cùng các Pro trên GPE hướng dẫn thêm.

Thật ra nếu bạn để ý các chỉ số trong code sẽ đoán được đang lookup ở cột nào
Ví dụ:
Arr3(i, 3) = aResult(Dic.Item(tmp), 11) ---> Lookup cột 11
Tại sao lại có Arr1, Arr2 và Arr3 ---> Vì Sheet ChiTiet các vùng cần lookup nằm không liền nhau: Cột D và E là 1 vùng, cột G, H, I là 1 vùng, cột N là 1 vùng (vị chi có 3 vùng, tương ứng với 3 Array sẽ gán vào)
Cái thằng Arr3 lúc trước chỉ gán cho 1 cột (cột N)... Giờ bạn thêm cột K, L, M, O ---> Các cột này cùng với cọt N tạo thành 1 vùng liên tục, vậy cho nó vào Arr3 luôn (5 cột)
Code sửa lại như sau:

PHP:

Public Chk As Boolean, Dic As Object, aResult()
Sub Auto_Open()
  Dim wks As Worksheet, SrcRng As Range, sArray
  Dim lR As Long, i As Long, j As Long, n As Long, tmp
  On Error Resume Next
  Set wks = Sheets("LLNV")
  Set SrcRng = wks.Range("B6:R1000")
  sArray = SrcRng.Value
  ReDim aResult(1 To UBound(sArray, 1), 1 To UBound(sArray, 2))
  Set Dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(sArray, 1)
    If CStr(sArray(i, 1)) <> "" Then
      tmp = sArray(i, 1)
      If Not Dic.Exists(tmp) Then
        lR = lR + 1
        Dic.Add tmp, lR
        For j = 1 To 17
          aResult(lR, j) = sArray(i, j)
        Next
      End If
    End If
  Next
End Sub

và:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rTarget As Range, aTarget, i As Long, n As Long
  Dim Arr1(), Arr2(), Arr3(), tmp
  On Error Resume Next
  If Dic Is Nothing Then Auto_Open
  If Not Intersect(Range("C6:C1000"), Target) Is Nothing Then
    Set rTarget = Intersect(Range("C6:C1000"), Target)
    If IsArray(rTarget.Value) Then
      aTarget = rTarget.Value
    Else
      ReDim aTarget(1 To 1, 1 To 1)
      aTarget(1, 1) = rTarget.Value
    End If
    ReDim Arr1(1 To UBound(aTarget, 1), 1 To 2)
    ReDim Arr2(1 To UBound(aTarget, 1), 1 To 3)
    ReDim Arr3(1 To UBound(aTarget, 1), 1 To 5)
    For i = 1 To UBound(aTarget, 1)
      If aTarget(i, 1) <> "" Then
        tmp = aTarget(i, 1)
        If Dic.Exists(tmp) Then
          Arr1(i, 1) = aResult(Dic.Item(tmp), 2)
          Arr1(i, 2) = aResult(Dic.Item(tmp), 3)
          Arr2(i, 1) = aResult(Dic.Item(tmp), 5)
          Arr2(i, 2) = aResult(Dic.Item(tmp), 6)
          Arr2(i, 3) = aResult(Dic.Item(tmp), 14)
          Arr3(i, 1) = aResult(Dic.Item(tmp), 7)
          Arr3(i, 2) = aResult(Dic.Item(tmp), 8)
          Arr3(i, 3) = aResult(Dic.Item(tmp), 11)
          Arr3(i, 4) = aResult(Dic.Item(tmp), 15)
          Arr3(i, 5) = aResult(Dic.Item(tmp), 4)
        End If
      End If
    Next
    rTarget.Offset(, 1).Resize(, 2).Value = Arr1
    rTarget.Offset(, 4).Resize(, 3).Value = Arr2
    rTarget.Offset(, 8).Resize(, 5).Value = Arr3
  End If
End Sub

Các code khác giữ nguyên
Kiểm tra lại, trục trặc gì ta sẽ bàn tiếp

siwtom · 15/4/12

ndu96081631 đã viết:
Ah... cái đoạn code đó không liên quan gì đến vấn đề bạn nói cả... Chẳng qua nếu chuyển toàn bộ sang Array thì phải lưu ý xem Target có phải là 1 cell hay không (nếu Target gồm nhiều cell thì Target.Value là 1 Array nhưng nếu Target là 1 cell thì Target.Value không phải là Array)
Còn vấn đề bạn vừa nói thật ra giải quyết nó cũng không có vấn đề gì... Có điều nếu chuyển mọi thứ sang xử lý mảng thì hơi rắc rối chút ----> Dạng Array trong Array đấy mà
Để từ từ tôi nghiên cứu thêm việc này
Cảm ơn bạn đã nhắc nhở!

Tôi nghĩ là có liên quan. Với nghĩa là có thể phải thay nó bằng đoạn code khác, vì kiểm tra như thế dẫn tới việc đoạn code tiếp theo làm việc không đúng.
Không biết bạn có đọc kỹ bài của tôi không.
Thứ nhất tôi đọc code (của bạn hay code nào khác) thì tôi hiểu ý đồ của người lập trình và tại sao lại muốn làm thế. Thứ hai: tôi thấy các code còn lại nhìn có vẻ đúng nên tôi nghi ngờ

Mã:

If IsArray(rTarget.Value) Then
      aTarget = rTarget.Value
Else
      ReDim aTarget(1 To 1, 1 To 1)
      aTarget(1, 1) = rTarget.Value
End If

Mà nghi cũng phải thôi, tiếp theo tôi sẽ viết về nó. Chỗ này tôi muốn lưu ý. Bạn viết: "nếu Target gồm nhiều cell thì Target.Value là 1 Array". Chỗ này không chính xác (tôi không nói là không đúng, chỉ nói là không chính xác, hay nói khác đi thì "cũng còn tùy"). Ở dưới tôi sẽ viết.
Thứ ba tôi viết: "Nhưng nếu chọn vài ô không ở các dòng lên tiếp - tức từ "ô đầu" tới "ô cuối" có ô không được chọn để xóa thì code làm không theo ý muốn".
Bạn hãy chọn trong cột C 2 ô liên tiếp, bỏ 1 ô sau đó chọn 99 ô nữa (liên tiếp hay không không cần), tổng cộng 101 ô. Lúc này IsArray(rTarget.Value) = TRUE, và UBound(aTarget, 1) (dùng trong FOR) bằng ........... 2. Tôi hiểu là chỉ 2 ô đầu liên tiếp được tính, 99 ô sau coi như là mất. Nhưng thậm chí 2 dòng có 2 ô đầu này không được xóa, chứng tỏ code

Mã:

rTarget.Offset(, 1).Resize(, 2).Value = Arr1
rTarget.Offset(, 4).Resize(, 3).Value = Arr2
rTarget.Offset(, 8).Resize(, 5).Value = Arr3

không thực hiện.
Trong trường hợp này

Mã:

If aTarget(i, 1) <> "" Then
End If

không được thực hiện vì các ô đã xóa là rỗng, chứng tỏ Arr1, Arr2, Arr3 chứa các giá trị rỗng. Nhưng 2 dòng đầu không được xóa điều này chứng tỏ cụm "rTarget.Offset ... " không thực hiện đúng.
Bây giờ ta chỉ chọn 1 ô, sau đó bỏ 1 ô, tiếp theo chọn 100 ô rồi xóa. Có 101 ô chọn nhưng IsArray(rTarget.Value) = FALSE. Tôi hiểu do chỉ tính ô đầu. Cái tôi viết "cũng còn tùy" chính là đây - so sánh 2 trường hợp chọn 101 ô

tiendo1988 · 15/4/12

Cám ơn Bác ndu96081631. Cả buổi sáng nay em ngồi ở nhà nghiên cứu đoạn code hôm qua anh viết cho. Em đã hiểu được chút ít và biết cách sửa thông tin để lookup được cho các cột khác. Giờ em mới online thấy bác sửa lại một chút trong code và có những cập nhật thiệt hay: ở phần Nạp Dictionary theo hướng linh hoạt hơn không cần phải sửa trong đó nữa khi thay đổi cột cần lookup bên Sheet ChiTiet.

Cho em hỏi mở rộng ra ngoài bài hiện tại của em một chút: trường hợp như sau:

Nếu ở Sheet LLNV phần SỐ THẺ (hay còn gọi là mã số Nhân Viên) nằm ở cột F chẳng hạn, và ở các cột A, B, C, D, E vẫn có dữ liệu liên quan.

- Đặt ra trường hợp: trên Sheet ChiTiet nếu cần lookup những giá trị trên một trong các cột A, B, C, D, E của LLNV theo SỐ THẺ thì có thể Lookup được không bác? Nếu được thì code sẽ thay đổi như thế nào?

Nếu cột cần lookup ở Sheet Chitiet nằm ở bên trái của cột SỐ THẺ thì cần thay đổi code thế nào cho phù hợp ạ?

Trong file đính kèm em có thay đổi thứ tự một số cột để phù hợp với nội dung em hỏi thêm. Sheet LLNV vẫn là DATA gốc.

Cám ơn Bác ndu96081631 cùng các AE GPE đã quan tâm, giúp đỡ và chia sẻ.

Mong bác cùng mọi người dành thêm chút thời gian cho phần em hỏi thêm.

vodoi2x · 15/4/12

Bài này cần xem lại ý tưởng ngay từ đầu: không thể viết hàm VBA mà nhanh hơn Vlookup được, Chắc chắn là chậm hơn hàm gốc, có chăng là cái "chậm nhiều" này được chia đều ra và đẩy vào ngay giai đoạn nhập liệu.

Nếu xem xét ý tưởng dưới góc độ thế, thì khỏi cần phải VBA code dài dòng vậy, mà hãy dùng ngay Vlookup chuẩn sẵn có trong Excel và biến thành "giá trị" (value) là đủ

Thấy file của người hỏi đều là "thuc hanh" -- chứng tỏ người hỏi đang muốn học hỏi lập trình, vậy hãy chúng ta phải nhìn nhận lại ý tưởng ngay từ đâu

Nếu ý kiến tôi có không hợp, thì có thể do tiêu đề topic là chưa chuẩn sát

ndu96081631 · 15/4/12

vodoi2x đã viết:
Bài này cần xem lại ý tưởng ngay từ đầu: không thể viết hàm VBA mà nhanh hơn Vlookup được, Chắc chắn là chậm hơn hàm gốc, có chăng là cái "chậm nhiều" này được chia đều ra và đẩy vào ngay giai đoạn nhập liệu.

Dám cá với bạn là nhanh gấp 100 lần (nếu không thì người ta xài công thức cho rồi)
Còn dùng VBA mà "mượn tạm" thằng VLOOKUP để làm cũng chậm luôn (được cái code gọn thôi)... Chỉ có cách dùng Array mới là nhanh nhất thôi
Không tin bạn có thể thí nghiệm với dữ liệu tùy ý
---------------------
Với siwtom: Bạn chỉ mô tả sơ qua là tôi đã hiểu rồi ---> Nói chung là tôi biết phải sửa chổ nào, thêm bớt chổ nào... nhưng vấn đề này tôi sẽ viết tiếp trong dịp khác
(dù sao thì cũng không khoái mấy vụ này lắm vì nó không.. MỚI... Ẹc... Ẹc...)

vodoi2x · 15/4/12

ndu96081631 đã viết:
Dám cá với bạn là nhanh gấp 100 lần (nếu không thì người ta xài công thức cho rồi)
Còn dùng VBA mà "mượn tạm" thằng VLOOKUP để làm cũng chậm luôn... Chỉ có cách dùng Array mới là nhanh nhất thôi
Không tin bạn có thể thí nghiệm với dữ liệu tùy ý

Vấn đề là anh nhận định sai rồi, Hàm của anh không nhanh hơn Vlookup được, cái này là chắc chắn

Còn "mượn tạm" thì tôi không nhận xét, vì thí nghiệm kiểu dữ liệu này không chuẩn, và hơn nữa trình VBA của tôi cũng có hạn.

tiendo1988 · 15/4/12

vodoi2x đã viết:
Bài này cần xem lại ý tưởng ngay từ đầu: không thể viết hàm VBA mà nhanh hơn Vlookup được, Chắc chắn là chậm hơn hàm gốc, có chăng là cái "chậm nhiều" này được chia đều ra và đẩy vào ngay giai đoạn nhập liệu.

Nếu xem xét ý tưởng dưới góc độ thế, thì khỏi cần phải VBA code dài dòng vậy, mà hãy dùng ngay Vlookup chuẩn sẵn có trong Excel và biến thành "giá trị" (value) là đủ

Thấy file của người hỏi đều là "thuc hanh" -- chứng tỏ người hỏi đang muốn học hỏi lập trình, vậy hãy chúng ta phải nhìn nhận lại ý tưởng ngay từ đâu

Nếu ý kiến tôi có không hợp, thì có thể do tiêu đề topic là chưa chuẩn sát

- Mình thiết nghĩ nếu ý tưởng viết code VBA để thay cho công thức và hàm (ở đây là thay cho hàm Vlookup) sử dụng phải nhập ở trên các Cells để đáp ứng cho việc Dữ liệu bảng tính nhiều hàng nhiều cột sử dụng hàm Vlookup nhiều là rất tiện và cần thiết.
- Mình thì chưa hiều gì về lập trình nên việc sử dụng code vào file chạy nhanh hay chậm thì mình không rõ. song đọc một số bài trên GPE thì việc file chạy nhanh hay chậm hơn khi sử dụng hàm Vlookup cũng như các hàm khác trên các cells, các cột trong bảng tính còn phụ thuộc vào phương pháp của người viết đoạn code đó nữa.
- Không dấu gì bạn và mọi người, mình làm ở bộ phận nhân sự, chỗ mình làm vẫn quản lý nhân viên trên Excel và toàn bộ là nhập liệu bằng tay hết, rất mất thời gian. để tiện cho việc quản lý nhân viên mình đang mò mẫm hoàn chỉnh lại các mẫu biểu quản lý và các hàm, công thức sử dụng trong đó để thuận tiện hơn cho công việc quản lý nhân viên của mình. Vì vậy mình để tên file là Thực hành. Và mình cũng muốn tìm hiểu thêm một chút VBA để vận dụng cho công việc hiện tại của mình.

vodoi2x · 15/4/12

tiendo1988 đã viết:
- Không dấu gì bạn và mọi người, mình làm ở bộ phận nhân sự, chỗ mình làm vẫn quản lý nhân viên trên Excel và toàn bộ là nhập liệu bằng tay hết, rất mất thời gian. để tiện cho việc quản lý nhân viên mình đang mò mẫm hoàn chỉnh lại các mẫu biểu quản lý và các hàm, công thức sử dụng trong đó để thuận tiện hơn cho công việc quản lý nhân viên của mình. Vì vậy mình để tên file là Thực hành. Và mình cũng muốn tìm hiểu thêm một chút VBA để vận dụng cho công việc hiện tại của mình.

Vậy bạn cứ tiếp tục ứng dụng trường hợp cụ thể này, Có chăng ở đây tôi ý kiến về tên topic nó không chuẩn sát với nội dung yêu cầu thực sự, Điều này quan trọng cho việc người sau đọc và ứng dụng, nếu vẫn thế họ sẽ hiểu là không nên sử dụng Vlookup

còn trường hợp riêng như của bạn thì là đặc thù rồi -- ở đây tôi đang nói về việc quan điểm lập trình, và phương pháp đó.

ndu96081631 · 15/4/12

vodoi2x đã viết:
Vấn đề là anh nhận định sai rồi, Hàm của anh không nhanh hơn Vlookup được, cái này là chắc chắn
.

Thật ra tôi rất thích tranh luận để chứng minh vấn đề
Vậy thay vì nói suông ta làm cuộc thí nghiệm với 10000 dòng dữ liệu giữa code của tôi VS với VLOOKUP nhé (xem file)
Tại sheet ChiTiet, điền dữ liệu vào cột C rồi lookup 16 cột còn lại bên phải
Code của tôi như sau:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rTarget As Range, aTarget, i As Long, j As Long, n As Long, TG As Double
  Dim Arr(), tmp
  On Error Resume Next
  TG = Timer
  If Dic Is Nothing Then Auto_Open
  If Not Intersect(Range("C6:C65536"), Target) Is Nothing Then
    Set rTarget = Intersect(Range("C6:C65536"), Target)
    If IsArray(rTarget.Value) Then
      aTarget = rTarget.Value
    Else
      ReDim aTarget(1 To 1, 1 To 1)
      aTarget(1, 1) = rTarget.Value
    End If
    ReDim Arr(1 To UBound(aTarget, 1), 1 To 17)
    For i = 1 To UBound(aTarget, 1)
      If aTarget(i, 1) <> "" Then
        tmp = aTarget(i, 1)
        If Dic.Exists(tmp) Then
          For j = 2 To 17
            Arr(i, j - 1) = aResult(Dic.Item(tmp), j)
          Next
        End If
      End If
    Next
    rTarget.Offset(, 1).Resize(, 16).Value = Arr
    MsgBox Timer - TG
  End If
End Sub

- Còn code "mượn" VLOOKUP như sau:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rTarget As Range, aTarget, i As Long, j As Long, n As Long, TG As Double
  Dim Arr(), tmp
  On Error Resume Next
  TG = Timer
  If Not Intersect(Range("C6:C65536"), Target) Is Nothing Then
    Set rTarget = Intersect(Range("C6:C65536"), Target)
    With rTarget.Offset(, 1).Resize(, 16)
      .Value = "=IF(RC3="""","""",VLOOKUP(RC3,LLNV!R5C2:R10000C18,2,0))"
      .Value = .Value
    End With
    MsgBox Timer - TG
  End If
End Sub

Code này tương đương bạn tự tay gõ hàm VLOOKUP vào rồi copy/paste value thôi (tôi nghĩ không khó hiểu đối với bạn)
----------------
Giờ so sánh khi copy paste 10000 dòng dữ liệu vào cột C của sheet ChiTiet (dữ liệu tôi đã làm sẵn tại sheet1)
- Code tôi viết trên nền tảng xử lý mảng cho kết quả trong vòng 1.1 giây
- Code dùng VLOOKUP cho kết quả không vòng 25 giây
Đó là chưa nói code dùng VLOOKUP chỉ tìm duy nhất trên cột 2 ---> Nếu tìm 1 lần 16 cột như code của tôi dùng Array chắc là cách dùng VLOOKUP sẽ... đói luôn
Nếu thay đoạn "VLOOKUP(RC3,LLNV!R5C2:R10000C18, 2,0)" thành "VLOOKUP(RC3,LLNV!R5C2:R10000C18, COLUMNS(RC3:RC),0)" để lookup luôn 16 cột thì... Ẹc.. Ẹc... tôi không kiên nhẩn để chờ (lâu quá, treo máy luôn)
Đương nhiên khi làm cuộc thí nghiệm này tôi đã thử bằng rất nhiều cách với VLOOKUP... Chẳng hạn dùng WorksheetFunction.Vlookup ---> Kết quả còn tệ hơn rất nhiều

vodoi2x đã viết:
Còn "mượn tạm" thì tôi không nhận xét, vì thí nghiệm kiểu dữ liệu này không chuẩn, và hơn nữa trình VBA của tôi cũng có hạn.

Bạn muốn dữ liệu "chuẩn" thế nào, hoặc muốn sửa VLOOKUP như thế nào, cứ đưa lên đây, chúng ta sẽ cùng thí nghiệm để bạn tâm phục khẩu phục về tốc độ của xử lý Array

Dauthivan · 15/4/12

Dĩ nhiên là Code của thày Ndu nhanh hơn rất nhiều, nếu dùng VBA mà mượn Vlookup của Excel thì chẳng thà sài Vlookup của Excel cho xong cần VBA làm gì.

vodoi2x: Từ hôm nọ tôi đọc rất nhiều bài của bác, phải nói bác rất chịu khó tranh luận, tuy vậy một số bài tranh luận của bác (không phải tất cả) dường như có cảm giác thiếu cơ sở gì đó (có lẽ nhiều vấn đề bác hiểu không sâu như các thày trên diễn đàn).

vodoi2x · 15/4/12

ndu96081631 đã viết:
Thật ra tôi rất thích tranh luận để chứng minh vấn đề
Vậy thay vì nói suông ta làm cuộc thí nghiệm với 10000 dòng dữ liệu giữa code của tôi VS với VLOOKUP nhé (xem file)

Bạn muốn dữ liệu "chuẩn" thế nào, hoặc muốn sửa VLOOKUP như thế nào, cứ đưa lên đây, chúng ta sẽ cùng thí nghiệm để bạn tâm phục khẩu phục về tốc độ của xử lý Array

Anh tính thời gian thế này thì sai rồi, anh phải tính cả thời gian mà load cho diction ary ban đầu nữa, Còn anh làm thế này thì phải so sánh với dấu = của excel thôi, vì lúc đó dic sẵn sàng cả rồi
Song nhờ đó tôi mới hiểu anh đang hiểu sai, mỗi người nghĩ theo 1 hướng.

Tôi đã viết là với hàm lập VBA so sánh cùng hàm chuẩn Vlookup trong excel thì sẽ chậm hơn là cái chắc chắn, NÊN tôi mới nói chủ topic xem lại tiêu đề cho hợp lý,

Còn so sánh kiểu anh thì là so sánh thật khập khiễng còn nếu muốn đọ thì anh phải viết 1 hàm cùng lúc enter vào với hàm có sẵn VLOOKUP thì sẽ biết tay ngay.

vodoi2x · 15/4/12

Dauthivan đã viết:
vodoi2x: Từ hôm nọ tôi đọc rất nhiều bài của bác, phải nói bác rất chịu khó tranh luận, tuy vậy một số bài tranh luận của bác (không phải tất cả) dường như có cảm giác thiếu cơ sở gì đó (có lẽ nhiều vấn đề bác hiểu không sâu như các thày trên diễn đàn).

cám ơn,
bạn nên đọc kỹ điều tôi viết nhé, tôi cũng mới sơ sơ VBA chắc cũng không sâu như bạn đâu, nhưng cái gì đúng thì là nó phải đúng, chúng ta không the a dua theo 1 chiều, mà không theo cái chuẩn ah,

Xin viết dùng mã vba thay thế cho hàm vlookup

Huyền thoại GPE

File đính kèm

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

Thành viên gắn bó

Gội rồi mới Cạo

Thành viên gắn bó

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

Thành viên gắn bó

Huyền thoại GPE

Huyền thoại GPE

File đính kèm

Thành viên gắn bó

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

File đính kèm

Nothing & Nothing

Huyền thoại GPE

Nothing & Nothing

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

Nothing & Nothing

Huyền thoại GPE

File đính kèm

Thành viên tiêu biểu

Nothing & Nothing

Nothing & Nothing

Facebook

Group