AutoFilter

Euler · 18/7/19

1. Cách viết cơ bản:
Trên bảng tính, biểu tượng autofilter có hình tam giác ngược đầu hướng xuống phía dưới.
Xác định xem bảng tính có đang sử dụng Autofilter không, ta có thuộc tính của sheet là AutoFilterModel.
Nó sẽ trả về TRUE nếu như bảng tính đang dùng Autofilter, trả về là FALSE nếu không dùng AutoFilter.

Mã:

Sub Macro1()
    If ActiveSheet.AutoFilterMode = True Then
        MsgBox "Dang dung AutoFilter"
    Else
        MsgBox "Khong dung AutoFilter"
    End If
End Sub

Bạn cần đăng nhập để thấy hình ảnh

Tôi hủy bỏ autofilter (sử dụng phím tắt ấn ALT+D+F+F)
Thử chạy lại macro:

Bạn cần đăng nhập để thấy hình ảnh

2. Có đang thực hiện bộ lọc hay không?
Khi thiết định Autofilter, nhưng có hai trạng thái, chúng ta cho điều kiện vào để lọc, hoặc chưa cho điều kiện vào để lọc. Thực tế có chỉ định điều kiện nào hay không, ta có thuộc tính FilterMode. Ở đây rất dễ nhầm lẫn, hãy cẩn thận.
Có dùng AutoFilter hay không ta dùng thuộc tính AutoFilterMode, thuộc tính này thuộc sheet. Tuy nhiên, hiện trên bảng tính có thể hiện kết quả nào đó thỏa mãn điều kiện lọc hay không thì ta dùng thuộc tính FilterMode. Đây là thuộc tính thuộc AutoFilter. Do đó cách viết là:
AutoFilter.FilterMode

Mã:

Sub Macro2()
    If ActiveSheet.AutoFilterMode = True Then
        If ActiveSheet.AutoFilter.FilterMode = True Then
            MsgBox "Dang dung bo loc"
        Else
            MsgBox "Khong dung bo loc"
        End If
    End If
End Sub

Bạn cần đăng nhập để thấy hình ảnh

Bây giờ tôi hủy bỏ điều kiện lọc, cho hiển thị hết data nhưng vẫn còn giữ AutoFilter.
Chạy code:

Bạn cần đăng nhập để thấy hình ảnh

3. Dòng nào thỏa mãn điều kiện lọc
Khi sử dụng bộ lọc và đưa điều kiện lọc vào, chúng ta muốn biết dòng dữ liệu nào thỏa mãn điều kiện, chúng ta sử dụng đối tượng Filter. Toàn bộ dòng dữ liệu là tập hợp các Filters. Hiểu nôm na mỗi dòng là một Filter.
Ta dùng thuộc tính ON của đối tượng Filter để xác nhận có thỏa mãn điều kiện lọc hay không.

Mã:

Sub Macro3()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            For i = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(i).On = True Then
                    msg = msg & "cot thu " & i & vbCrLf
                End If
            Next i
        End If
    End With
    MsgBox msg
End Sub

Ở đây tôi sử dụng điều kiện lọc là cột B chọn Kihieu="A" và cột C chọn So > mức trung bình trong cột C.
Kết quả:

Bạn cần đăng nhập để thấy hình ảnh

Bên trái là dữ liệu chưa cho điều kiện lọc vào. Bên phải là cho điều kiện lọc và chạy code.
Cột số 2 và cột số 3 là các cột đang đưa điều kiện lọc vào. (Chú ý, hình ảnh minh họa code trên có nhầm lẫn).

Việc tính toán như trên thật phiền phức, tóm lại là tôi đang sử dụng điều kiện lọc cho cột nào, tôi chạy code sau:

Mã:

Sub Macro4()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            For i = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(i).On = True Then
                    msg = msg & .AutoFilter.Range(i) & vbCrLf
                End If
            Next i
        End If
    End With
    MsgBox msg
End Sub

Bạn cần đăng nhập để thấy hình ảnh

Chúng ta có thể kiểm chứng bằng cách trỏ chuột vào các vị trí, excel sẽ hiển thị điều kiện lọc.

Bạn cần đăng nhập để thấy hình ảnh

4. Khi một điều kiện thỏa mãn:
Ở trên ta dùng hai điều kiện lọc đó là cột B=A, và cột C > trung bình. Tuy nhiên thực tế sẽ phức tạp hơn nhiều nếu như chúng ta phức hợp các điều kiện lọc bằng lệnh AND hoặc OR. Tôi sẽ trình bày mục số 4 này ở bài viết sau do tính chất phức tạp của nó.

Nguon tham khao:

Bạn cần đăng nhập để thấy link

Euler · 20/7/19

Tôi tiếp tục mục số 4.
4. Khi một điều kiện thỏa mãn:
Như ở hình ảnh dưới đây, các bạn thấy rằng tôi đang đưa điều kiện lọc vào là người có tên là Tam thì sẽ được thỏa mãn.

Bạn cần đăng nhập để thấy hình ảnh

Với mỗi thiết định Autofilter như thế ta có cấu trúc là:
Cells.AutoFilter Field, Criteria1, Operator, Criteria2
Tham số Field là vị trí của cột mà ta thiết định điều kiện. Do đó với điều kiện số 1 thì tham số tương ứng sẽ là Criteria1.
Thực tế đối với đối tượng Filter thì tham số Criteria1 trùng tên với thuộc tính của nó là Criteria1. Khi đi tìm thuộc tính này ta sẽ hiểu điều kiện lọc.

Mã:

Sub Macro4()
    MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1
End Sub

Bạn cần đăng nhập để thấy hình ảnh

Khi tôi đưa vào hai điều kiện lọc: Có số lượng > 40 Và <=80.

Bạn cần đăng nhập để thấy hình ảnh

Mã:

Sub Macro5()
    With ActiveSheet.AutoFilter.Filters(3)
        MsgBox .Criteria1 & vbCrLf & .Criteria2
    End With
End Sub

Kết quả:

Bạn cần đăng nhập để thấy hình ảnh

Điều kiện 1 như đã nói, chúng ta có thuộc tính Criteria1. Đồng thời điều kiện 2 sẽ là Criteria2.
Vấn đề ở đây là phép logic AND. Nếu đã hiểu được qui trình cho tới bước này rồi, có lẽ là có thể tưởng tượng được thuộc tính Operator cũng trùng tên với tham số Operator. Quả đúng như vậy. Chúng ta sẽ hiểu về Operator khi tìm hiểu về nó nếu chúng ta chỉ định Operator là AND hay OR. Hãy thử làm nhé.
Kết quả là:

Mã:

Sub Macro6()
    With ActiveSheet.AutoFilter.Filters(3)
        MsgBox .Operator
    End With
End Sub

Bạn cần đăng nhập để thấy hình ảnh

Số [1] ở đây có nghĩa là gì vậy nhỉ? Khi chúng ta thiết định AutoFilter bằng VBA, ví dụ chúng ta thiết định cột số 3 với điều kiện logic là >40 AND <80 thì code sẽ là:

Mã:

Range("A1").AutoFilter 3, ">40", xlAnd, "<80"

Lúc này, tham số Operator được chỉ định là 1, nó có nghĩa là xlAnd.

Hằng số	Giá trị	Ý nghĩa
xlAnd	1	AND
xlOr	2	OR
xlTop10Items	3	Top 10
xlBottom10Items	4	Top 10
xlTop10Percent	5	Top 10
xlBottom10Percent	6	Top 10
xlFilterValues	7	Giá trị
xlFilterCellColor	8	Màu cells
xlFilterFontColor	9	Màu font
xlFilterIcon	10	Icon
xlFilterDynamic	11	Filter tự động
xlFilterNoFill	12	Màu cells(không màu)
xlFilterAutomaticFontColor	13	Màu font(tự động)
xlFilterNoIcon	14	Icon(không có icon)

Giá trị trả về của thuộc tính Operator đối với đối tượng Filter được mô tả ở cột Giá trị bên trên.
Nói tóm lại, chúng ta lấy được giá trị Operator thì phải tham chiếu vào bảng bên trên thì mới hiểu được ý nghĩa.
Và như vậy, việc tìm hiểu điều kiện nào được thiết định quả thực rất vất vả.
Đối với cột 1 khi chúng ta thiết định điều kiện là Tên = Tam thì lọc, khi đó giá trị trả về của Operator = 0.
Bây giờ chúng ta sẽ thử lọc tự động bằng VBA nhé:

Mã:

Sub Macro7()
    Range("A1").AutoFilter 1, "Tam", xlOr, "Hoa"
    Range("A1").AutoFilter 3, ">40", xlAnd, "<80"
End Sub

Kết quả:

Bạn cần đăng nhập để thấy hình ảnh

Việc tiếp theo, chúng ta truy tìm xem điều kiện logic nào đã được thiết định. Đây quả là một công việc khó khăn.

Mã:

Sub Macro9()
    Dim i As Long, msg As String
    With ActiveSheet
        If .AutoFilterMode = True Then
            With .AutoFilter
                For i = 1 To .Filters.Count
                    If .Filters(i).On = True Then
                        msg = msg & "Cot thu：" & i & vbCrLf
                        Select Case .Filters(i).Operator
                        Case 0
                            msg = msg & .Filters(i).Criteria1 & vbCrLf
                        Case 1
                            msg = msg & .Filters(i).Criteria1 & " AND " & .Filters(i).Criteria2 & vbCrLf
                        Case 2
                            msg = msg & .Filters(i).Criteria1 & " OR " & .Filters(i).Criteria2 & vbCrLf
                        End Select
                    End If
                Next i
            End With
        End If
    End With
    MsgBox msg
End Sub

Bạn cần đăng nhập để thấy hình ảnh

5. Khi có nhiều hơn 3 điều kiện được thiết định?
Do yêu cầu khắt khe của diễn đàn, một bài viết chỉ nên có dưới 20 hình vẽ. Bài viết cũng đã dài. Để không gây mệt mỏi cho người đọc, mình dừng ở đây. Hẹn gặp lại các bạn trong các bài viết tiếp theo.

dkkx3a · 24/5/20

Các anh chị mình có vấn đề thắc mắc: Sheet1 trong bảng tính của mình đang có AutoFilter và đang có điều kiện lọc, giờ mình muốn hủy điều kiện lọc nhưng vẫn giữ nguyên AutoFilter thì làm thế nào ạ? Cảm ơn!

NhanSu · 24/5/20

@dkkx3a bạn dùng lệnh sau:

Mã:

If Sheet1.AutoFilterMode Then Sheet1.AutoFilter.ShowAllData

Điều kiện Sheet1.AutoFilterMode cần thiết để tránh lỗi xảy ra khi dùng Sheet1.AutoFilter ở sheet đang không có autofilter.

AutoFilter

Euler

Administrator

Euler

Administrator

dkkx3a

Yêu THVBA

NhanSu

SMod