Tặng hàm NewAutoFilter LỌC 2 CỘT, tốc độ cao và cấu trúc hàm đơn giản! (1 người xem)

Hoàng Trọng Nghĩa · 28/1/13

Như chúng ta đã biết nói về hàm lọc, chúng ta không khỏi không nhắc đến bậc Thầy ndu96081631 với hàm Filter2DArray (bản gốc), với hàm này tôi đã học được ở Thầy rất nhiều!

Tuy nhiên, với nguồn là dữ liệu lớn, kiểu lọc là dạng Số, dạng Ngày tháng, dạng giờ v.v... thì hàm Filter2DArray chưa đáp ứng được tốc độ.

Cấu trúc:

Filter2DArray (Vùng cần lọc, Cột cần lọc, Điều kiện lọc, Tiêu đề (True/False))

Thêm nữa nếu lọc thời gian phải làm thêm động tác phụ. Chẳng hạn, tôi muốn lọc 02/08/2012 05:30:00 thì cấu trúc hàm sẽ như sau:

1) Dùng hàm của EXCEL:

PHP:

sArray = Filter2DArray([A2:F65536], 6, "=DATE(2012, 8, 2) + TIME(5,30,0)", True)

2) Dùng thêm 1 biến Double để cụ thể hóa điều kiện: (nên dùng cái này để cải thiện tốc độ)

PHP:

Dim DateAndTime As Double
DateAndTime = DateSerial(2012, 8, 2) + TimeSerial(5, 30, 0)
sArray = Filter2DArray([A2:F65536], 6, "=" & DateAndTime, True)

Lý do mà hàm này chạy chậm khi so sánh dạng số là dùng hàm Evaluate. Đây là một hàm hữu dụng nhằm lắp ghép chuỗi để chuyển thành hàm. Nó rất hay và tiện dụng, tuy nhiên nó gặp rắc rối với dữ liệu lớn vì xử lý nhiều nên cho tốc độ rất chậm.

Tôi đã thử lọc dạng số với 65536 dòng, với hàm Filter2DArray cho tốc độ 2.3120 giây.

Chính vì thế, để cải thiện tốc độ tôi đã thay thế hàm Evaluate đó, bằng toán tử so sánh (cũng vì điều này mà thủ tục đã rất nhiều để loại trừ tất cả các điều kiện). Và hàm của tôi đã cho tốc độ nhanh gấp đôi cũng với dữ liệu trên: 1.078 giây.

Đây là hàm mới dựa vào ý tưởng của hàm Filter2DArray của Thầy ndu96081631, không những nó cải thiện về tốc độ, nó được nâng cao hơn là lọc 2 điều kiện ở 2 cột với điều kiện AND hoặc OR.

Hàm NewAutoFilter:

PHP:

Function NewAutoFilter(ByVal Expression As Variant, _
                       ByVal Header As HeaderType, _
                       ByVal Field1 As Long, _
                       ByVal Criteria1 As Variant, _
              Optional ByVal Operator As OperatorType = xlAnd, _
              Optional ByVal Criteria2 As Variant, _
              Optional ByVal Field2 As Variant) As Variant

    ''Yeu cau khi filter dang ngay thang, dau phan cach cua Criteria bat buoc _
    ''phai la dau (/) - VD: ">=14/9/1976" hoac "14/9/1976 05:30" v.v...
   
    FilterDataType1 = "": FilterDataType2 = ""
    FilterColumn1 = Field1: FilterColumn2 = Field2
    FilterCompare1 = 0: FilterCompare2 = 0: FilterSeries = 0
   
    SourceFilterArray = Expression: RowNumArray = Array(0)
    Urow = UBound(SourceFilterArray, 1)
    Lrow = LBound(SourceFilterArray, 1) - Header
    Ucol = UBound(SourceFilterArray, 2)
    Lcol = LBound(SourceFilterArray, 2)
   
    Call Check_Type_Of_Criteria1(Criteria1)
    Criteria1 = FilterCrit1
   
    If IsMissing(Criteria2) Then
        Call MissingCriteria2(Criteria1)
    Else
       
        If IsMissing(Field2) Then FilterColumn2 = FilterColumn1
       
        Call Check_Type_Of_Criteria2(Criteria2)
        Criteria2 = FilterCrit2
       
        Select Case FilterCompare1
        Case 1
            Call FilterCompare1_Case_1(Criteria1, Criteria2, Operator)
        Case 2
            Call FilterCompare1_Case_2(Criteria1, Criteria2, Operator)
        Case 3
            Call FilterCompare1_Case_3(Criteria1, Criteria2, Operator)
        Case 4
            Call FilterCompare1_Case_4(Criteria1, Criteria2, Operator)
        Case 5
            Call FilterCompare1_Case_5(Criteria1, Criteria2, Operator)
        Case Else
            Call FilterCompare1_Case_Else(Criteria1, Criteria2, Operator)
        End Select
    End If
   
    If FilterSeries Or Header Then
        Lrow = Lrow + Header
        Dim FilterArray As Variant
        ReDim FilterArray(Lrow To FilterSeries - Header, Lcol To Ucol) As Variant
       
        If Header Then
            For FilterColumn = Lcol To Ucol
                FilterArray(Lrow, FilterColumn) = SourceFilterArray(Lrow, FilterColumn)
            Next
        End If
       
        For FilterRow = Lrow To FilterSeries
            For FilterColumn = Lcol To Ucol
                FilterArray(FilterRow - Header, FilterColumn) = _
                SourceFilterArray(RowNumArray(FilterRow), FilterColumn)
            Next
        Next
       
        NewAutoFilter = FilterArray
       
        Erase FilterArray
        Erase RowNumArray
        Erase SourceFilterArray
    End If
End Function

Cấu trúc:

NewAutoFilter(Vùng cần lọc, Tiêu đề (xlYes, xlNo), Cột lọc 1, Điều kiện lọc 1, [Kiểu AND/OR (xlAnd, xlOr)], [Điều kiện lọc 2], [Cột lọc 2])

Tôi sẽ cụ thể file ở bài viết sau!

Hoàng Trọng Nghĩa · 28/1/13

Như đã nói ở bài trước hàm có cấu trúc:

NewAutoFilter(Vùng cần lọc, Tiêu đề (xlYes, xlNo), Cột lọc 1, Điều kiện lọc 1, [Kiểu AND/OR (xlAnd, xlOr)], [Điều kiện lọc 2], [Cột lọc 2])

LƯU Ý: Các dấu trong ngoặc móc [] thì không cần phải ghi vào nếu không cần thiết!

Lọc dạng số:

Các phép so sánh: >, <, >=, <=, =, <>

Nếu ta cần lọc ở 1 cột, và điều kiện là >=3 và <=8 thì cấu trúc sẽ như sau:

PHP:

sArray = NewAutoFilter([A2:F65536], xlYes, 1, ">=3", xlAnd, "<=8")

Khi ta lọc ở 1 cột thì không cần thêm số cột ở [Cột lọc 2]

Lọc dạng ngày tháng năm và giờ:

Cũng dùng các phép so sánh, ta cần lọc chính xác ở 1 cột thời gian: 02/08/2012 05:30:00 và 1 cột lấy rỗng, ta làm như sau:

Ta có thể ghi điều kiện đầy đủ như trên, hoặc "làm biếng" ta chỉ cần ghi 2/8/12 5:30 thậm chí nếu là năm hiện hành thì ta cũng chẳng thèm ghi số năm chi cho mệt!

PHP:

sArray = NewAutoFilter([A2:F65536], xlYes, 6, "03:05 24/11/11", xlAnd, "=", 1)

Với điều kiện rỗng, ta có thể đặt dấu bằng ("=") hoặc 2 dấu ngoặc kép ("").

Lọc dạng chuỗi thì ta sử dụng như phép so sánh của hàm Like

NGHĨA Like NGH* hoặc NGHĨA Like NGH??

Đối với dạng chuỗi hoặc dạng số, ta muốn lọc rỗng hoặc khác rỗng như sau:

Lọc rỗng: "=" hoặc ""

Lọc khác rỗng "<>"

Lọc loại trừ điều kiện (khác đk): "<>ĐK loại trừ"

==================================================================

Các bạn xem file và cho ý kiến để chỉnh sửa, cải tiến tốt hơn!

NẾU THẤY HAY, TẠI SAO KHÔNG BẤM CÁM ƠN NHỈ!!!

Hoàng Trọng Nghĩa · 29/1/13

Lúc đầu để giảm vòng lặp khi dò tìm số hàng đúng điều kiện, tôi đã thử với hàm TRANSPOSE, nhưng với dữ liệu lớn và phải dùng hàm đó đầu vào cũng như đầu ra thì thời gian tăng gấp đôi, nên tôi không dùng nữa!

Mã:

Sub TEST()

    Dim Arr As Variant, ArrTrans As Variant, t As Double
    t = Timer
    ArrTrans = [COLOR=#ff0000][B]WorksheetFunction.Transpose[/B][/COLOR](Sheet1.[A2:C65536])
    Sheet1.Columns("F:H").Clear
    
    Dim r As Long, c As Long, v As Long, _
        lr As Long, lc As Long, _
        ur As Long, uc As Long
        
    lr = LBound(ArrTrans, 1) '1
    ur = UBound(ArrTrans, 1) '3
    
    lc = LBound(ArrTrans, 2) '1
    uc = UBound(ArrTrans, 2) '65535
    
    ReDim Arr(lr To ur, lc To lc)
    Arr(lc, lc) = "abc"
    
    For c = lc To uc
        If ArrTrans(1, c) Like "*2*" Then
            v = v + 1
            ReDim Preserve Arr(lr To ur, lc To v)
            For r = lr To ur
                Arr(r, v) = ArrTrans(r, c)
            Next
        End If
    Next
    
    Sheet1.[F1].Resize(UBound(Arr, 2), UBound(Arr, 1)) = [COLOR=#ff0000][B]WorksheetFunction.Transpose[/B][/COLOR](Arr)
    Sheet1.[K65536].End(xlUp).Offset(1).Value = Timer - t
    
End Sub

hoangvn79 · 17/4/16

Cho mình hỏi về ham filter2Darray khi trích lọc dữ liệu có thể lấy k cột cần thiết chứ không phải toàn vùng dữ liệu, ví dụ dữ liệu có 10 cột khi trích lọc minh chỉ cần lấy cột 3,5,8 chẳng hạn thì có đc ko? Nếu có thể thì hướng dẫn cho mình cách lấy. Thanks!

Hoàng Trọng Nghĩa · 17/4/16

hoangvn79 đã viết:
Cho mình hỏi về ham filter2Darray khi trích lọc dữ liệu có thể lấy k cột cần thiết chứ không phải toàn vùng dữ liệu, ví dụ dữ liệu có 10 cột khi trích lọc minh chỉ cần lấy cột 3,5,8 chẳng hạn thì có đc ko? Nếu có thể thì hướng dẫn cho mình cách lấy. Thanks!

Bạn muốn hàm lọc từ 1 cột trong một bảng nhiều cột và xuất ra nhiều cột?

Dạng của bạn cần lọc là dạng gì? Số, ngày tháng hay chuỗi?

hoangvn79 · 17/4/16

Hi! Dạng lọc thì như hàm lọc của bạn có thể là chuỗi, số,ngày tháng, có thể lọc 2 cột. Nhưng khi xuất dữ liệu ra thì chỉ xuất những cột dữ liệu cần mà không xuất tất cả cột dữ liệu. Ví dụ: dữ liệu có 5 cot: 1,2,3,4,5 sau khi lọc chỉ cần xuất dữ liệu cột 1,3,4 chẳng hạn. Minh thấy hàm newautofilter của bạn rất hay nhưng khi lọc xong nó xuất hiện tất cả các cột của dữ liệu. Mà minh chỉ muốn sau khi lọc xong thì xuất ra một vài cột mình cần xem thôi. Thank!

Hoàng Trọng Nghĩa · 24/4/16

hoangvn79 đã viết:
Hi! Dạng lọc thì như hàm lọc của bạn có thể là chuỗi, số,ngày tháng, có thể lọc 2 cột. Nhưng khi xuất dữ liệu ra thì chỉ xuất những cột dữ liệu cần mà không xuất tất cả cột dữ liệu. Ví dụ: dữ liệu có 5 cot: 1,2,3,4,5 sau khi lọc chỉ cần xuất dữ liệu cột 1,3,4 chẳng hạn. Minh thấy hàm newautofilter của bạn rất hay nhưng khi lọc xong nó xuất hiện tất cả các cột của dữ liệu. Mà minh chỉ muốn sau khi lọc xong thì xuất ra một vài cột mình cần xem thôi. Thank!

Xem bài này nhé:

http://www.giaiphapexcel.com/forum/...er-dành-cho-mảng-2-chiều)&p=715728#post715728

Tặng hàm NewAutoFilter LỌC 2 CỘT, tốc độ cao và cấu trúc hàm đơn giản! (1 người xem)

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

Hoàng Trọng Nghĩa

Chuyên gia GPE

Hoàng Trọng Nghĩa

Chuyên gia GPE

File đính kèm

Hoàng Trọng Nghĩa

Chuyên gia GPE

hoangvn79

Thành viên mới

Hoàng Trọng Nghĩa

Chuyên gia GPE

hoangvn79

Thành viên mới

Hoàng Trọng Nghĩa

Chuyên gia GPE

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian