Tận Dụng Autofilter trong VBA (Phần 2)

Bài viết này sẽ cung cấp cho bạn một số ví dụ về cách sử dụng Autofilter trong VBA. Nếu bạn muốn khám phá thêm, hãy đọc các bài viết khác trên Mytour để hiểu rõ hơn về các hàm như TRIM, DIR trong VBA.

1. Thực Hành với Autofilter trong VBA

Dưới đây là một số ví dụ về cách sử dụng Autofilter trong VBA:

1.1 Ví dụ 1: Lọc dữ liệu dựa trên điều kiện Văn Bản

Giả sử bạn đang làm việc với một tập tin dữ liệu như dưới đây và muốn áp dụng lọc dữ liệu dựa trên cột Mục:

Đoạn mã sau sẽ thực hiện việc lọc tất cả các hàng có mục là Printer:

Chức năng LọcHàng()

BảngTính('Sheet1').Dải('A1').Tự Động Lọc Trường:=2, Tiêu Chí1:='Printer'

Kết Thúc Chức Năng

Trong đoạn mã này, nó liên kết với bảng tính 1 và trong bảng tính đó, nó tham chiếu đến ô A1 (là ô trong tập dữ liệu).

Chú ý rằng trong ví dụ này, chúng ta sử dụng Field:=2, vì cột Item là cột thứ 2 trong tập dữ liệu, tính từ bên trái.

1.2 Ví dụ 2: Lọc dữ liệu theo nhiều tiêu chí (AND / OR) trong cùng một cột

Giả sử chúng ta có cùng một bảng dữ liệu và muốn lọc tất cả các bản ghi trong đó các mục là Printer hoặc Projector:

Để thực hiện điều này, chúng ta có thể sử dụng đoạn mã sau đây:

Chức Năng Lọc Theo Điều Kiện HOẶC()

Bảng Tính('Sheet1').Dải('A1').Tự Động Lọc Trường:=2, Tiêu Chí1:='Printer', Toán Tử:=xlHoặc, Tiêu Chí2:='Projector'

Kết Thúc Chức Năng

Chú ý rằng, trong đoạn mã trên, chúng ta sử dụng toán tử xlOR. Điều này cho phép VBA sử dụng cả 2 tiêu chí và lọc dữ liệu nếu bất kỳ một trong hai tiêu chí được đáp ứng.

Tương tự, chúng ta cũng có thể sử dụng tiêu chí VÀ.

Ví dụ, nếu muốn lọc tất cả các bản ghi có số lượng lớn hơn 10 nhưng không quá 20, chúng ta có thể sử dụng đoạn mã dưới đây:

Chức Năng Lọc Theo Điều Kiện VÀ()

BảngTính('Sheet1').Dải('A1').TựĐộngLọc Trường:=4, TiêuChí1:='>10', _

ToánTử:=xlAnd, TiêuChí2:='<>

KếtThúc Sub

1.3 Ví dụ 3: Lọc dữ liệu theo nhiều tiêu chí nhưng khác cột

Giả sử tại đây có một tập tin dữ liệu như sau:

Với Autofilter, chúng ta có khả năng lọc nhiều cột đồng thời.

Ví dụ, để lọc bản ghi với điều kiện mục Printer và Sales Rep là Mark, bạn có thể sử dụng mã sau:

Sub LọcHàng()

Với Worksheets('Sheet1').Range('A1')

.AutoFilter trường:=2, TiêuChuẩn1:='Printer'

.AutoFilter trường:=3, TiêuChuẩn1:='Mark'

KếtThúc Với

KếtThúc Sub

1.4 Ví dụ 4: Sử dụng Autofilter để lọc dữ liệu Top 10

Giả sử chúng ta có bảng dữ liệu sau đây:

Sử dụng đoạn mã dưới đây để lọc 10 bản ghi hàng đầu (dựa trên cột Số Lượng):

Sub LọcTop10Hàng()

BảngTính.Range('A1').AutoFilter Field:=4, Criteria1:='10', Operator:=xlTop10Items

End Sub

Trong ví dụ này, bảng tính có tên là BảngTính. Bạn có thể thay thế bằng tên bảng tính của mình.

Lưu ý, trong ví dụ này nếu muốn top 5 mục hàng đầu, bạn chỉ cần thay đổi số trong Criteria1:='5'' từ 10 thành 5.

Mã sử dụng để lấy top 5 mục hàng đầu như sau:

Sub LọcTop5Hàng()

BảngTính.Range('A1').AutoFilter Field:=4, Criteria1:='5', Operator:=xlTop10Items

End Sub

Dù muốn lấy bao nhiêu mục hàng đầu, giá trị của toán tử (Operator) luôn là xlTop10Items.

Tương tự, để lấy top 10 mục thấp nhất, bạn có thể sử dụng đoạn mã sau:

Sub LọcTop10DướiCùng()

ActiveSheet.Range('A1').AutoFilter Field:=4, Criteria1:='10', Operator:=xlBottom10Items

End Sub

1.5 Ví dụ 5: Lọc 10% dữ liệu bằng phương thức AutoFilter

Chúng ta tiếp tục sử dụng bảng dữ liệu từ phần ví dụ trước.

Sử dụng đoạn mã sau để lấy 10% các bản ghi hàng đầu (dựa trên cột Quantity):

Sub FilterRowsTop10Percent()

ActiveSheet.Range('A1').AutoFilter Field:=4, Criteria1:='10', Operator:=xlTop10Percent

End Sub

Với bảng dữ liệu 20 bản ghi, đoạn mã sẽ trả về 2 bản ghi hàng đầu (chiếm 10% tổng số bản ghi).

1.6 Ví dụ 6: Sử dụng ký tự đại diện trong Autofilter

Cho bảng dữ liệu sau:

Để lọc các hàng có tên chứa từ 'Board', sử dụng đoạn mã sau:

Sub LọcCácHàngKýTựĐạiDiện()

Worksheets('Sheet1').Range('A1').AutoFilter Field:=2, Criteria1:='*Board*'

End Sub

Trong đoạn mã này, chúng ta sử dụng ký tự đại diện * (dấu hoa thị) trước và sau từ 'Board' (làm tiêu chí).

Một dấu hoa thị có thể đại diện cho bất kỳ số lượng ký tự nào. Vì vậy, đoạn mã trên sẽ lọc bất kỳ mục nào chứa từ 'Board'.

1.7 Ví dụ 7: Sao chép các hàng đã lọc vào bảng tính mới

Nếu muốn lọc các bản ghi dựa trên các tiêu chí và sao chép các hàng đã lọc, chúng ta có thể sử dụng macro dưới đây. Macro sẽ sao chép các hàng đã được lọc, thêm một bảng tính mới, sau đó dán các hàng đã lọc vào bảng tính mới:

Sub SaoChepHangDaLoc()

Dim vung As Range

Dim bs As BảngTính

Nếu BảngTính('Sheet1').ChếĐộTựLọc = Sai Thì

MsgBox 'Không có hàng nào được lọc'

Kết Thúc Sub

End If

Đặt vung = BảngTính('Sheet1').PhạmViTựLọc

Đặt bs = BảngTính.Thêm

vung.SaoChép PhạmVi('A1')

Kết Thúc Sub

Đoạn mã trên sẽ kiểm tra xem có hàng nào được lọc trong Sheet1 hay không. Nếu không có hàng nào được lọc, nó sẽ hiển thị hộp thoại thông báo.

Nếu có các hàng được lọc, nó sẽ sao chép các hàng đó, chèn một bảng tính mới và dán các hàng đã lọc vào bảng tính mới được chèn.

1.8 Ví dụ 8: Sao chép các cột được lọc vào bảng tính mới

Nếu muốn lọc các bản ghi dựa trên các tiêu chí và sao chép các cột đã lọc, chúng ta có thể sử dụng macro dưới đây. Macro sẽ sao chép các cột đã được lọc, thêm một bảng tính mới, sau đó dán các cột này vào bảng tính mới:

Sub SaoChepCacCotDaLoc()

Dim vung As Range

Dim bts As BảngTính

If BảngTính('Sheet1').ChếĐộTựLọc = False Then

MsgBox 'Không có cột nào được lọc.'

Exit Sub

Kết thúc Nếu

Đặt rng = Worksheets('Bảng1').PhạmViTựĐộngLọc

Đặt ws = Worksheets.Thêm

rng.SaoChép PhạmVi('A1')

Kết Thúc Thủ tục

Đoạn mã này sẽ kiểm tra xem có cột nào đang được lọc trong Sheet1 không. Nếu không có cột nào được lọc, nó sẽ hiển thị một hộp thoại thông báo.

Trong trường hợp có cột đang được lọc, nó sẽ sao chép những cột đó, thêm một bảng tính mới và dán những cột đã lọc vào bảng tính mới vừa thêm.

1.9 Ví dụ 9: Lọc dữ liệu theo giá trị ô

Sử dụng Autofilter trong VBA kết hợp với menu thả xuống, chúng ta có thể tạo một hàm trong đó khi chọn một mục trong menu, tất cả các bản ghi của mục đó sẽ được lọc.

Loại cấu trúc này có thể hữu ích khi muốn lọc dữ liệu nhanh chóng và sau đó sử dụng cho các tác vụ khác.

Để làm điều này, chúng ta có thể sử dụng mã nguồn dưới đây:

Thủ tục riêng Worksheet_Change(ByVal Target As Range)

Nếu Địa chỉ của Target = '$B$2' Thì

Nếu Phạm vi('B2') = 'Tất cả' Thì

Phạm vi('A5').TựĐộngLọc

Ngược Lại

Phạm vi('A5').TựĐộngLọc Trường:=2, TiêuChuẩn1:=Phạm vi('B2')

Kết Thúc Nếu

Kết Thúc Nếu

Kết Thúc Thủ tục

Đây là mã sự kiện của bảng tính, chỉ thực thi khi có sự thay đổi trong bảng tính và ô đích là B2 (nơi chúng ta có menu thả xuống).

Ngoài ra, câu lệnh If Then Else được sử dụng để kiểm tra xem người dùng đã chọn Tất cả từ menu hay chưa. Nếu Tất cả được chọn, toàn bộ dữ liệu sẽ được hiển thị.

Chú ý rằng mã này không nằm trong module. Thay vào đó, chúng ta đặt nó trong phần backend của bảng tính chứa dữ liệu.

Thực hiện theo các bước dưới đây để đặt mã trong cửa sổ mã của bảng tính:

Bước 1: Mở Trình Biên tập VB (bằng cách nhấn tổ hợp phím Alt + F11).

Bước 2: Trong Khu vực Dự án, nhấn đôi vào tên bảng tính mà bạn muốn áp dụng đoạn mã lọc này.

Bước 3: Trên cửa sổ mã của bảng tính, sao chép và dán đoạn mã ở trên vào.

Bước 4: Đóng cửa sổ Trình Biên tập VB.

Kể từ bây giờ, khi sử dụng menu thả xuống, dữ liệu sẽ tự động được lọc.

Trong bài viết Sử dụng Autofilter trong VBA (Phần 2) này, Mytour đã giới thiệu một số ví dụ về cách sử dụng Autofilter trong VBA. Nếu có bất kỳ thắc mắc hoặc câu hỏi nào, độc giả có thể để lại ý kiến của mình trong phần bình luận bên dưới bài viết.