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

  • Thread starter vothanhthu
  • Ngày gửi
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 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 FilterColumn.
 
Sửa lần cuối bởi điều hành viên:

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ỉ.
 
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
 
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 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 FilterColumn.
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?
 
Top