Hàm Trích Lọc Filter - Tương Tự Hàm Filter Trong Office 365 | Tự Học VBA

Menu

• Trang chủ
• Diễn đàn Bài viết mới Tìm chủ đề
• Có gì mới Bài viết mới Hoạt động mới nhất

Đăng nhập Đăng ký Có gì mới? Tìm kiếm

Tìm kiếm

Everywhere Chủ đề This forum This thread Chỉ tìm trong tiêu đề Bởi: Tìm Tìm kiếm nâng cao…

• Bài viết mới
• Tìm chủ đề

Menu Đăng nhập Đăng ký

• Cách upload ảnh lên diễn đàn

• Trang chủ
• Diễn đàn
• Kỹ thuật xử lý Excel
• Lập trình VBA

Hàm trích lọc Filter - Tương tự hàm Filter trong Office 365

• Thread starter vothanhthu
• Ngày gửi 23/12/19

V

vothanhthu

Guest

1. Giới thiệu Trong mỗi lần chúng ta sử dụng Excel để trích lọc ra một dữ liệu nào đó, chúng ta sẽ thường phải dùng đến chức năng Advanced Filter, đây là một chức năng rất mạnh của Excel nhưng đôi khi nó lại khá khó áp dụng trong một số trường hợp. Mình ví dụ, chúng ta có một bảng dữ liệu lớn và một bảng tra cứu, mình muốn mỗi lần gõ từ tìm kiếm thôi thì bảng tra cứu sẽ hiện ra tất cả các thông tin mình cần tìm lấy từ bảng dữ liệu lớn, và Advanced Filter là không khả dụng mấy trong trường hợp này. Thấy được vấn đề đó, Microsoft đã cho ra đời hàm Filter nhưng nó lại chỉ có ở phiên bản Office 365, phiên bản cao cấp nhất của Microsoft. Nên hôm nay, mình sẽ giới thiệu với các bạn 2 hàm FilterAll và FilterColumn có thể bù đắp được một phần của hàm Filter còn thiếu trong các phiên bản Office không phải Office 365. 2. Code của Function và giải thích Mã: Function FilterAll(Ten As String, CSDL As Range) Dim Rws As Long, J As Long, W As Integer Dim Col As Long Dim a As Long Dim b As Long 'Đếm số dòng và cột của mảng cần lọc Rws = CSDL.Rows.Count: W = 0 Col = CSDL.Columns.Count 'Function sẽ thoát nếu Ten là rỗng If Ten = "" Then Exit Function 'Khai báo mảng động Arr ReDim Arr(1 To Rws, 1 To Col) As String For J = 1 To Rws 'Chạy tìm kiếm With CSDL(1).Offset(J) If InStr(.Value, Ten) Then 'Nếu tìm thấy Ten thì W = W + 1: Arr(W, 1) = .Value 'Điền giá trị cột đầu tiên tìm thấy được so với Ten For a = 2 To Col 'Chạy giá trị cho các cột tiếp theo Arr(W, a) = .Offset(, a-1).Value Next a End If End With Next J FilterAll = Arr() End Function ---------------------------------------------------------------------- Function FilterColumn(Ten As String, CSDL As Range, Cot As Long) Dim Rws As Long, J As Long, W As Integer Dim Col As Long Dim a As Long Rws = CSDL.Rows.Count: W = 0 Col = CSDL.Columns.Count 'Function sẽ thoát nếu Ten là rỗng If Ten = "" Then Exit Function 'Khai báo mảng động Arr ReDim Arr(1 To Rws, 1 To 2) As String For J = 1 To Rws 'Chạy tìm kiếm With CSDL(1).Offset(J) If InStr(.Value, Ten) Then 'Nếu tìm thấy Ten thì W = W + 1: Arr(W, 1) = .Offset(, Cot - 1).Value 'Giá trị cột cần tìm End If End With Next J FilterColumn = Arr() End Function 3. Ví dụ thực tế về hàm 3.1. Hàm FilterAll Hàm có dạng FilterAll(Giá trị tìm kiếm, Vùng tìm kiếm) Giá trị tìm kiếm: Là giá trị bạn cần tìm trong vùng dữ liệu Vùng tìm kiếm: Là vùng dữ liệu bạn cần lọc (bao gồm cả tiêu đề dữ liệu) Hàm sẽ lọc tất cả các giá trị tìm kiếm trong cột đầu tiên của vùng tìm kiếm, giá trị trả về sẽ làm toàn bộ các dòng có chứa giá trị tìm kiếm (toàn bộ dữ liệu hàng ngang) trong vùng tìm kiếm. Lưu ý: Đây là hàm mảng nên khi sử dụng, cần phải chọn tất cả các ô cần có công thức, điền công thức rồi nhấn Ctrl + Shift + Enter. Bạn cần đăng nhập để thấy đính kèm Ở ví dụ, Công thức tại vùng E4:G10 sẽ là kết quả trả về của giá trị tìm kiếm Ngô Hữu Thịnh ô F1 trong vùng tìm kiếm A1:C10. Công thức sẽ được đặt trong dấu {} là công thức mảng. 3.2. Hàm FilterColumn Hàm có dạng FilterColumn(Giá trị tìm kiếm, Vùng tìm kiếm, Cột kết quả) Giá trị tìm kiếm: Là giá trị bạn cần tìm trong vùng dữ liệu Vùng tìm kiếm: Là vùng dữ liệu bạn cần lọc (bao gồm cả tiêu đề dữ liệu) Cột kết quả: Cột cần ra kết quả sau khi lọc Hàm sẽ lọc tất cả các giá trị tìm kiếm trong cột đầu tiên của vùng tìm kiếm, giá trị trả về sẽ là dòng của cột kết quả có chứa giá trị tìm kiếm trong vùng tìm kiếm. Bạn cần đăng nhập để thấy đính kèm Ở ví dụ, khác với FilterAll, công thức tại vùng E4:E10 sẽ chỉ trả về cột Mã NV cho kết quả tìm kiếm Ngô Hữu Thịnh tại ô F1 trong vùng A1:C10. 4. Kết luận Hàm FilterAll và FilterColumn rất mạnh trong việc trích lọc dữ liệu, giúp cho việc trích lọc tìm kiếm không còn khó khăn như trước. Có thể thay thế cho sự thiếu xót của hàm Filter trong các phiên bản không phải Office 365 khi sử dụng các dữ liệu không quá lớn. Phát triển thêm: Hàm vẫn còn thiếu tính năng co giãn, tốc độ xử lý vẫn chưa nhanh như Filer trong Office 365. Còn lại khả năng cho ra kết quả là như nhau. Chúc các bạn có được những bài Excel tuyệt vời với hàm FilterAll và FilterColumn. Sửa lần cuối bởi điều hành viên: 23/12/19 G

Gaexcel

Yêu THVBA

liệu có thể sử dụng hàm mà không dùng công thức mảng không ạ?. vì khi thay đổi kích thước mảng rất phiền phức ạ D

Deleted member 1392

Guest

@Gaexcel Xin lỗi vì không liên quan lắm, không biết bạn có liên quan gì đến kênh Youtube Gà Excel không nhỉ. G

Gaexcel

Yêu THVBA

Ngày Mới nói: @Gaexcel Xin lỗi vì không liên quan lắm, không biết bạn có liên quan gì đến kênh Youtube Gà Excel không nhỉ. Nhấn để mở rộng...

Không Em là con gà excel chứ ko liên quan đến youtube đâu ạ D

Deleted member 1392

Guest

Hiện nếu máy bạn sử dụng Office 2019 trờ lên thì bạn có thể dùng trực tiếp hàm này mà không cần Ctrl + Shift + Enter, Excel tự co dãn vị trí dữ liệu K

kiban09

Yêu THVBA

Em tét thử cả 2 hàm OK tuy hơi chậm! Sửa lần cuối: 21/11/22 B

bdk20vn

Yêu THVBA

Bạn ơi bạn có hàm Sort cho mình xin luôn với. Cảm ơn bạn nhiều. N

nguyễn bảo khuyên

Yêu THVBA

vothanhthu nói: 1. Giới thiệu Trong mỗi lần chúng ta sử dụng Excel để trích lọc ra một dữ liệu nào đó, chúng ta sẽ thường phải dùng đến chức năng Advanced Filter, đây là một chức năng rất mạnh của Excel nhưng đôi khi nó lại khá khó áp dụng trong một số trường hợp. Mình ví dụ, chúng ta có một bảng dữ liệu lớn và một bảng tra cứu, mình muốn mỗi lần gõ từ tìm kiếm thôi thì bảng tra cứu sẽ hiện ra tất cả các thông tin mình cần tìm lấy từ bảng dữ liệu lớn, và Advanced Filter là không khả dụng mấy trong trường hợp này. Thấy được vấn đề đó, Microsoft đã cho ra đời hàm Filter nhưng nó lại chỉ có ở phiên bản Office 365, phiên bản cao cấp nhất của Microsoft. Nên hôm nay, mình sẽ giới thiệu với các bạn 2 hàm FilterAll và FilterColumn có thể bù đắp được một phần của hàm Filter còn thiếu trong các phiên bản Office không phải Office 365. 2. Code của Function và giải thích Mã: Function FilterAll(Ten As String, CSDL As Range) Dim Rws As Long, J As Long, W As Integer Dim Col As Long Dim a As Long Dim b As Long 'Đếm số dòng và cột của mảng cần lọc Rws = CSDL.Rows.Count: W = 0 Col = CSDL.Columns.Count 'Function sẽ thoát nếu Ten là rỗng If Ten = "" Then Exit Function 'Khai báo mảng động Arr ReDim Arr(1 To Rws, 1 To Col) As String For J = 1 To Rws 'Chạy tìm kiếm With CSDL(1).Offset(J) If InStr(.Value, Ten) Then 'Nếu tìm thấy Ten thì W = W + 1: Arr(W, 1) = .Value 'Điền giá trị cột đầu tiên tìm thấy được so với Ten For a = 2 To Col 'Chạy giá trị cho các cột tiếp theo Arr(W, a) = .Offset(, a-1).Value Next a End If End With Next J FilterAll = Arr() End Function ---------------------------------------------------------------------- Function FilterColumn(Ten As String, CSDL As Range, Cot As Long) Dim Rws As Long, J As Long, W As Integer Dim Col As Long Dim a As Long Rws = CSDL.Rows.Count: W = 0 Col = CSDL.Columns.Count 'Function sẽ thoát nếu Ten là rỗng If Ten = "" Then Exit Function 'Khai báo mảng động Arr ReDim Arr(1 To Rws, 1 To 2) As String For J = 1 To Rws 'Chạy tìm kiếm With CSDL(1).Offset(J) If InStr(.Value, Ten) Then 'Nếu tìm thấy Ten thì W = W + 1: Arr(W, 1) = .Offset(, Cot - 1).Value 'Giá trị cột cần tìm End If End With Next J FilterColumn = Arr() End Function 3. Ví dụ thực tế về hàm 3.1. Hàm FilterAll Hàm có dạng FilterAll(Giá trị tìm kiếm, Vùng tìm kiếm) Giá trị tìm kiếm: Là giá trị bạn cần tìm trong vùng dữ liệu Vùng tìm kiếm: Là vùng dữ liệu bạn cần lọc (bao gồm cả tiêu đề dữ liệu) Hàm sẽ lọc tất cả các giá trị tìm kiếm trong cột đầu tiên của vùng tìm kiếm, giá trị trả về sẽ làm toàn bộ các dòng có chứa giá trị tìm kiếm (toàn bộ dữ liệu hàng ngang) trong vùng tìm kiếm. Lưu ý: Đây là hàm mảng nên khi sử dụng, cần phải chọn tất cả các ô cần có công thức, điền công thức rồi nhấn Ctrl + Shift + Enter. Bạn cần đăng nhập để thấy đính kèm Ở ví dụ, Công thức tại vùng E4:G10 sẽ là kết quả trả về của giá trị tìm kiếm Ngô Hữu Thịnh ô F1 trong vùng tìm kiếm A1:C10. Công thức sẽ được đặt trong dấu {} là công thức mảng. 3.2. Hàm FilterColumn Hàm có dạng FilterColumn(Giá trị tìm kiếm, Vùng tìm kiếm, Cột kết quả) Giá trị tìm kiếm: Là giá trị bạn cần tìm trong vùng dữ liệu Vùng tìm kiếm: Là vùng dữ liệu bạn cần lọc (bao gồm cả tiêu đề dữ liệu) Cột kết quả: Cột cần ra kết quả sau khi lọc Hàm sẽ lọc tất cả các giá trị tìm kiếm trong cột đầu tiên của vùng tìm kiếm, giá trị trả về sẽ là dòng của cột kết quả có chứa giá trị tìm kiếm trong vùng tìm kiếm. Bạn cần đăng nhập để thấy đính kèm Ở ví dụ, khác với FilterAll, công thức tại vùng E4:E10 sẽ chỉ trả về cột Mã NV cho kết quả tìm kiếm Ngô Hữu Thịnh tại ô F1 trong vùng A1:C10. 4. Kết luận Hàm FilterAll và FilterColumn rất mạnh trong việc trích lọc dữ liệu, giúp cho việc trích lọc tìm kiếm không còn khó khăn như trước. Có thể thay thế cho sự thiếu xót của hàm Filter trong các phiên bản không phải Office 365 khi sử dụng các dữ liệu không quá lớn. Phát triển thêm: Hàm vẫn còn thiếu tính năng co giãn, tốc độ xử lý vẫn chưa nhanh như Filer trong Office 365. Còn lại khả năng cho ra kết quả là như nhau. Chúc các bạn có được những bài Excel tuyệt vời với hàm FilterAll và FilterColumn. Nhấn để mở rộng...

Cho mình hỏi là vùng dữ liệu trên sheet khác sao nó không chạy vậy bạn? Bạn phải đăng nhập hoặc đăng ký để bình luận. Chia sẻ: Facebook Twitter WhatsApp Email Link

• Trang chủ
• Diễn đàn
• Kỹ thuật xử lý Excel
• Lập trình VBA

Top