Giúp code lọc nhanh Siêu tốc độ

[minhtuan55]

Mình muốn lọc theo điều kiện Từ ngày , đến ngày và theo nhân viên ( tất cả,ca1,c2)
Mình muốn lọc theo dạng Mảng để xữ lý cho nhanh. Ví dụ như Để tài ở đây
https://www.giaiphapexcel.com/forum/showthread.php?122365-Gi%C3%BAp-code-L%E1%BB%8Dc-t%C3%AAn-h%C3%A0ng-N%E1%BA%BFu-tr%C3%B9ng-C%E1%BB%99ng-d%E1%BB%93n-S%E1%BB%91-L%C6%B0%E1%BB%A3ng
Code cực kỳ nhanh của tác giả hoamattroicoi'
Hiện tại mình đang dùng code AdvancedFilter Khi dữ liệu lớn code chạy rất Chậm.Xin chân thành cảm ơn !

 

[SA_DQ]

Trước khi xài dao fẩu thuật, bạn xài thử lưỡi lam này xem sao:

Option Explicit
Sub LocKhoanNgayVaCa()
 Dim Arr()
 Dim Rws As Long, J As Long, Tmr As Double, Col As Byte, W As Long
 Dim fDat As Date, lDat As Date
 Dim NV As String
 
 Tmr = Timer()
 Rws = [b4].CurrentRegion.Rows.Count
 Arr() = [A4].Resize(Rws, 5).Value
 fDat = [i3].Value:     lDat = [i4].Value
 NV = [i5].Value
 ReDim dArr(1 To UBound(Arr()), 1 To 5)
 [n4].CurrentRegion.Offset(2).ClearContents
 For J = 1 To UBound(Arr())
    If (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) And Arr(J, 5) = NV Then
        W = W + 1
        For Col = 1 To 5
            dArr(W, Col) = Arr(J, Col)
        Next Col
    End If
 Next J
 If W Then
    [m4].Resize(W, 5).Value = dArr()
    [i1].Value = Timer() - Tmr
 End If
End Sub

 

[minhtuan55]

code của anh nhanh chóng mặt luôn. Chổ nhân viên có 3 lựa chọn anh nhé ( Tất cả,ca1, ca2) khi em chọn Tất cả thì ca 1 ca 2 đều lấy hết. Anh sữa lại Code giúp em. Cảm ơn anh

Trước khi xài dao fẩu thuật, bạn xài thử lưỡi lam này xem sao:

Option Explicit
Sub LocKhoanNgayVaCa()
 Dim Arr()
 Dim Rws As Long, J As Long, Tmr As Double, Col As Byte, W As Long
 Dim fDat As Date, lDat As Date
 Dim NV As String
 
 Tmr = Timer()
 Rws = [b4].CurrentRegion.Rows.Count
 Arr() = [A4].Resize(Rws, 5).Value
 fDat = [i3].Value:     lDat = [i4].Value
 NV = [i5].Value
 ReDim dArr(1 To UBound(Arr()), 1 To 5)
 [n4].CurrentRegion.Offset(2).ClearContents
 For J = 1 To UBound(Arr())
    If (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) And Arr(J, 5) = NV Then
        W = W + 1
        For Col = 1 To 5
            dArr(W, Col) = Arr(J, Col)
        Next Col
    End If
 Next J
 If W Then
    [m4].Resize(W, 5).Value = dArr()
    [i1].Value = Timer() - Tmr
 End If
End Sub

 

[ChanhTQ@]

(1) Trong cột [E:E] (nhân viên có C2 & Ca2; Vậy thì không bao giờ lọc được C2 (vì không có trong DS lọc)

(2) Tao macro sự kiện tại [I5] mà mần thôi.

Thay vì câu lệnh:

[COLOR=#000000][COLOR=#007700]If ([/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]J[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) >= [/COLOR][COLOR=#0000BB]fDat [/COLOR][COLOR=#007700]And [/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]J[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) <= [/COLOR][COLOR=#0000BB]lDat[/COLOR][COLOR=#007700]) And [/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]J[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]5[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]NV Then

Ta chuyển thành

If (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) And Arr(J, 5) = Target.Value Then

Chúc thành công![/COLOR][/COLOR]

 

[le_vis]

(1) Trong cột [E:E] (nhân viên có C2 & Ca2; Vậy thì không bao giờ lọc được C2 (vì không có trong DS lọc)

(2) Tao macro sự kiện tại [I5] mà mần thôi.

Thay vì câu lệnh:

[COLOR=#000000][COLOR=#007700]If ([/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]J[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) >= [/COLOR][COLOR=#0000BB]fDat [/COLOR][COLOR=#007700]And [/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]J[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) <= [/COLOR][COLOR=#0000BB]lDat[/COLOR][COLOR=#007700]) And [/COLOR][COLOR=#0000BB]Arr[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]J[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]5[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]NV Then [/COLOR][/COLOR]

Ta chuyển thành

If (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) And Arr(J, 5) = Target.Value Then

Chúc thành công!

Nếu sửa lại NV Then thành Target.Value Then thì lỗi luôn Đại ka ChanhTQ@ ơi ???

 

[ChanhTQ@]

Không báo lỗi, nhưng kết quả chưa thỏa hết các iêu cầu;

Chuyện này dành cho tác giả bài viết thử sức thôi;

 

[minhtuan55]

Không báo lỗi, nhưng kết quả chưa thỏa hết các iêu cầu;

Chuyện này dành cho tác giả bài viết thử sức thôi;

cảm ơn các bạn giúp mình. Tại vì mính biết VBA sơ sơ nên cũng không biết viết làm sao. Nhưng mà mình Nói ý tưởng cho các bạn viết như thế này

Nếu NV= Tất cả thì And ( tu ngay, den ngay )
Ngược lai thì And ( Tu ngay, den ngay , Nhan vien )

 

[minhtuan55]

Không báo lỗi, nhưng kết quả chưa thỏa hết các iêu cầu;

Chuyện này dành cho tác giả bài viết thử sức thôi;

Em mò như mò Cua giờ code ok rối . mấy anh chị xem giúp em như vậy ok chưa

Sub LocKhoanNgayVaCa()
Dim Arr()
Dim J As Long, Col As Byte, W As Long
Dim fDat As Date, lDat As Date
Dim NV As String

Arr() = Range("A4:E32").Value ' input
fDat = [i3].Value: lDat = [i4].Value
NV = [i5].Value
ReDim dArr(1 To UBound(Arr()), 1 To 5)
[n4].CurrentRegion.Offset(2).ClearContents

For J = 1 To UBound(Arr())

If Len(NV) <> 6 Then
If (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) And Arr(J, 5) = NV Then
W = W + 1
For Col = 1 To 5
dArr(W, Col) = Arr(J, Col)
Next Col
End If
Else
If (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) Then
W = W + 1
For Col = 1 To 5
dArr(W, Col) = Arr(J, Col)
Next Col
End If

End If
Next J

Range("m4").Resize(W, 5).Value = dArr() ' output
End Sub

 

[ChanhTQ@]

2úa được ấy chứ!

; Ta có thể căn cứ vô chữ cái đầu của từ được chọn.

 

[HieuCD]

Em mò như mò Cua giờ code ok rối . mấy anh chị xem giúp em như vậy ok chưa

bạn kha báo 1 biếnK As Boolean
sau đó gán biến DK

If Len(NV) <> 6 Then
  dk=(Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) And Arr(J, 5) = NV
else
  dk=(Arr(J, 1) >= fDat And Arr(J, 1) <= lDat)
end if

sau đó chỉ cần 1 if

If dk Then
...

 

[befaint]

@minhtuan55,

Ông tướng này viết gần 200 bài rồi còn ném code ra bài viết. Cho vào thẻ CODE hoặc PHP.

 

[VetMini]

@minhtuan55,

Ông tướng này viết gần 200 bài rồi còn ném code ra bài viết. Cho vào thẻ CODE hoặc PHP.

Ông ta ném ra cho bạn đọc chứ ông ta đâu có đọc đâu mà cần phải cải tiến. Tại bạn muốn "nâng cao tinh thần học hỏi nhau" thì phải chấp nhận vậy.
(hồi đó tôi cũng từng bảo người viết "sửa chỗ... rồi nói chuyện tiếp" thì bị một số thành viên khác chửi là chảnh. Thực sự tôi thấy bài nào dùng font lớn và bôi đậm toàn thể là khong thèm đọc đến dòng thứ 2, cố đọc loạn thị mất)

 

[befaint]

Ông ta ném ra cho bạn đọc chứ ông ta đâu có đọc đâu mà cần phải cải tiến. Tại bạn muốn "nâng cao tinh thần học hỏi nhau" thì phải chấp nhận vậy.
(hồi đó tôi cũng từng bảo người viết "sửa chỗ... rồi nói chuyện tiếp" thì bị một số thành viên khác chửi là chảnh. Thực sự tôi thấy bài nào dùng font lớn và bôi đậm toàn thể là khong thèm đọc đến dòng thứ 2, cố đọc loạn thị mất)

Em cảm ơn anh.

Em thì bị chửi là "...kẻ cả" các kiểu luôn nhưng mà hổng có sao, hổng "ngán" ạ.

Chúc anh một tối vui và ngon giấc!

 

[VetMini]

If Len(NV) <> 6 Then
  dk=(Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) And Arr(J, 5) = NV
else
  dk=(Arr(J, 1) >= fDat And Arr(J, 1) <= lDat)
end if

Viết như vầy đọc rõ hơn

dk = (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) [COLOR=#008000]' điều kiện này chính[/COLOR]
If Len(NV) <> 6 Then dk = dk And (Arr(J, 5) = NV) [COLOR=#008000]' nếu len <> 6 thì thêm điều kiện phụ[/COLOR]

 

[HieuCD]

Viết như vầy đọc rõ hơn

dk = (Arr(J, 1) >= fDat And Arr(J, 1) <= lDat) [COLOR=#008000]' điều kiện này chính[/COLOR]
If Len(NV) <> 6 Then dk = dk And (Arr(J, 5) = NV) [COLOR=#008000]' nếu len <> 6 thì thêm điều kiện phụ[/COLOR]

cám ơn bạn, vừa gọn vừa dể hiểu

 

[langtuchungtinh360]

tiện thể có chủ đề này mọi người giúp đỡ em vấn đề trong file này nhé
vấn đề là mình cần 1 hàm lọc dữ liệu (trả về mảng dữ liệu sau khi lọc) hơi bị đa năng nên mong mọi người giúp đỡ
trong file chỉ là 1 góc trong vô vàn cách lọc, theo em thấy thì cái góc này chắc là lọc chậm nhất trong các cách nên nêu ra
bỏ qua AutoFilter của excel nhé
trong file là cách em tự binh biến
nếu mọi người có tuyệt chiêu gì mong được chỉ giáo
link Media

 

[Gió Đông]

tiện thể có chủ đề này mọi người giúp đỡ em vấn đề trong file này nhé
vấn đề là mình cần 1 hàm lọc dữ liệu (trả về mảng dữ liệu sau khi lọc) hơi bị đa năng nên mong mọi người giúp đỡ
trong file chỉ là 1 góc trong vô vàn cách lọc, theo em thấy thì cái góc này chắc là lọc chậm nhất trong các cách nên nêu ra
bỏ qua AutoFilter của excel nhé
trong file là cách em tự binh biến
nếu mọi người có tuyệt chiêu gì mong được chỉ giáo
link Media

tạm liếc qua thế này

1) VBA:chấp nhận dùng class là chậm so với module thường (đối với dạng vấn đề đang xử lý của bạn) - chỉ thuận lợi cho người viết code (khi viết và chỉnh sửa) mà thôi - cái này phải suy nghĩ đánh đổi tốc độ và thuận lợi
2) Dictionary trong class của bạn là vô ích làm chậm đi quá trình (vì Dic.Add STT..., mà STT thì luôn khác nhau rui, đầu cần Dic làm chi??)
3) khi tìm với Tất cả (col Tìm=0) thì thuật toán cần xem lại: nên phân biệt number và Text (cái này chưa cần với ví dụ của bạn - vì toàn Text)
4) Việc tìm ra rồi lại list lại STT sau đó mới cho vào mảng, cũng làm chậm đi (cái này chắc không đáng kể)

Cải thiện được 4 điều trên thì code sẽ nhanh lên nhiều

 

[langtuchungtinh360]

1) chấp nhận dùng class là chậm so với module thường - chỉ thuật lợi cho người viết code (khi viết và chỉnh sửa) mà thôi - cái này phải suy nghĩ đánh đổi tốc độ và thuận lợi

cái này thì mình không biết, chủ yếu để trong class vì sau này cho vô Addins rồi mình thấy nếu viết function trong module thì lúc mình test code thì nó hay chạy mấy cái function trong module (không biết nguyên nhân)

2) Dictionary trong class của bạn là vô ích làm chậm đi quá trình (vì Dic.Add STT..., mà STT thì luôn khác nhau rui, đầu cần Dic làm chi??)

4) Việc tìm ra rồi lại list lại STT sau đó mới cho vào mảng, cũng làm chậm đi (cái này chắc không đáng kể)

Dictionary đó mình dùng để lấy dòng thỏa điều kiện để sau đó thêm vào mảng, vì không biết được kích thước mảng kết quả nên chỉ khi hoàn thành mới khai báo và gán lại mới được, nếu có cách khác hay hơn không làm ảnh hưởng đến tốc độ thì ok, mình cứ tưởng sử dụng Dic là tốc độ "bàn thờ" rồi chứ

3) khi tìm với Tất cả (col Tìm=0) thì thuật toán cần xem lại: nên phân biệt number và Text (cái này chưa cần với ví dụ của bạn - vì toàn Text)

cái này mình không biết làm sao cho tổng quát hơn được nên chỉ mới nghĩ ra như thế

 

[Gió Đông]

cái này thì mình không biết, chủ yếu để trong class vì sau này cho vô Addins rồi mình thấy nếu viết function trong module thì lúc mình test code thì nó hay chạy mấy cái function trong module (không biết nguyên nhân)

Khó hiểu,
Nếu cần thì đặt Private Sub/Function - để hạn chế phạm vi sử dụng hàm hay sub đó, cần nữa thì chỉ rõ tên module trong khi gọi sub/fun

Dictionary đó mình dùng để lấy dòng thỏa điều kiện để sau đó thêm vào mảng, vì không biết được kích thước mảng kết quả nên chỉ khi hoàn thành mới khai báo và gán lại mới được, nếu có cách khác hay hơn không làm ảnh hưởng đến tốc độ thì ok, mình cứ tưởng sử dụng Dic là tốc độ "bàn thờ" rồi chứ

Khi dùng Dic.Add key, ... --> Dic phải dò tìm xem key đó đã trùng hay chưa (trong Add method sẽ phải làm việc đó) - khi số lương keys lớn thì việc dò tìm dĩ nhiên là chậm hơn là không dò tìm rồi (dù DIC có siêu tốc hay không siêu tốc thì cũng chi phí thời gian đáng kể bị lãng phí),
Trong bài của bạn nên đổi thành dùng Array 1 chiều lưu chỉ số i

cái này mình không biết làm sao cho tổng quát hơn được nên chỉ mới nghĩ ra như thế

trên tôi đã gợi ý bạn nên phân tách ra number và text là 1 cách ... Nhưng bài ví dụ hiện tại của bạn toàn text nên không ảnh hường
------------

1 lưu ý mới nữa là

5) đối với sub LOAD Data:

+ có những biến và dòng code thừa - như ArrDataU vô ích chú ý này xem lại thì thấy ArrDataU có cần dùng không phải vô ích - tuy thế có cần upcase trước thế hay không?

+ có nên chăng phải lọc trước dữ liệu của Data gốc không?, sao không nghĩ phương án chuyển thẳng Data gốc cùng ArrCotDuLieu vào thẳng code Filter (hiện trong class) để xử lý (vì trước sau ta cũng phải nhặt dữ liệu theo điều kiện Filter) -- như thế bỏ hẳn được đoạn For i for j ngốn nhiều resource (time and memory) này (khi dữ liêu lớn (số dòng ở đây) thì mỗi FOR duyệt sẽ làm tăng chi phí thời gian là đáng kể)

-----------
Suy nghĩ cải thiện 5 điều đó thì code mới dần nhanh được, có thể còn nhiều cái khác nữa - vì tôi mới đọc lướt lướt qua.

 

[langtuchungtinh360]

Khi dùng Dic.Add key, ... --> Dic phải dò tìm xem key đó đã trùng hay chưa (trong Add method sẽ phải làm việc đó) - khi số lương keys lớn thì việc dò tìm dĩ nhiên là chậm hơn là không dò tìm rồi (dù DIC có siêu tốc hay không siêu tốc thì cũng chi phí thời gian đáng kể bị lãng phí),
Trong bài của bạn nên đổi thành dùng Array 1 chiều lưu chỉ số i

Cái này thì có thể sửa được, thì ra Dic phải tìm nữa mới Add vô (mất thời gian chỗ này), thế mà cứ tưởng nó ok rồi.

trên tôi đã gợi ý bạn nên phân tách ra number và text là 1 cách ... Nhưng bài ví dụ hiện tại của bạn toàn text nên không ảnh hường

cái này thì chưa biết giải quyết thế nào rồi, vì nếu tách text với Nunber ra thì phải thêm 1 bước kiểm tra nữa, liệu như thế có nhanh hơn không, vì dự tính là hàm tổng quát nên dữ liệu nó không ổn định như thế

5) đối với sub LOAD Data:
+ có những biến và dòng code thừa - như ArrDataU vô ích
+ có nên chăng phải lọc trước dữ liệu của Data gốc không, sao không nghĩ phương án chuyển thẳng Data gốc cùng ArrCotDuLieu vào thẳng code Filter (hiện trong class) để xử lý (vì trước sau ta cũng phải nhặt dữ liệu theo điều kiện Filter) -- như thế bỏ hẳn được đoạn For i for j ngốn nhiều resource (time and memory) này (khi dữ liêu lớn (số dòng ở đây) thì mỗi FOR duyệt sẽ làm tăng chi phí thời gian là đáng kể)

ArrDataU đó mình để phuc vụ cho việc tìm kiếm mà không phân biệt chữ hoa chữ thường ấy.
code LoadData mình dự định là chỉ chạy 1 lần đầu, những lần sau đó thì ko phải chạy lại (nên sẽ đỡ thời gian cho những lần chạy sau)- sẽ đặt điều kiện để nhận biết khi nào vùng dữ liệu thay đổi để Load lại, còn không thì không phải load
còn ý phía sau mình không hiểu
ps: Cảm ơn bạn đã quan tâm, vì đây là code ban đầu viết nên chưa lường hết các vấn đề nên vẫn còn chậm như thế, mong được sự hỗ trợ từ bạn để cải thiện tốc độ hơn