=FILTER(A2:C7,B2:B7="North") trả về mọi hàng của A2:C7 có khu vực trong cột B là North. Bạn gõ công thức vào một ô và các hàng khớp sẽ tràn (spill) xuống các ô bên dưới và bên cạnh. Đổi một khu vực trong cột B thành North, hoặc một North thành South, danh sách sẽ tự cập nhật.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Cara | North | 200 | |
| 4 | Cara | North | 200 | Finn | North | 60 | |
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Chỉ E2 chứa công thức. Các ô có dữ liệu còn lại trong E:G là kết quả tràn ra của nó: nhấp vào F3 và bạn sẽ thấy ô đó thuộc về công thức trong E2. Nếu có gì đó được gõ vào vùng này, FILTER sẽ hiện #SPILL! thay cho các hàng (xem lỗi #SPILL!).
Cú pháp FILTER
=FILTER(array, include, [if_empty])
arraylà thứ bạn muốn lấy về: một cột, vài cột hoặc cả bảng.includelà một điều kiện cho ra một giá trị TRUE hoặc FALSE trên mỗi hàng củaarray, chẳng hạnB2:B7="North". Nó phải có đúng bằng số hàng củaarray. (Để lọc theo cột, hãy cho nó một giá trị trên mỗi cột.)if_emptylà thứ hiển thị khi không có hàng nào khớp. Nếu thiếu, kết quả rỗng sẽ là lỗi #CALC!.
FILTER cần Excel 2021, Excel 2024 hoặc Microsoft 365. Trong Excel 2019 trở về trước, hàm hiện #NAME?, và ở đó bạn lọc bằng nút Filter trên thẻ Data. Google Sheets cũng có FILTER, và ở đó mỗi điều kiện còn có thể được truyền thành một tham số riêng.
Phép so sánh văn bản không phân biệt chữ hoa chữ thường: B2:B7="north" khớp với North. FILTER giữ các hàng theo thứ tự ban đầu; sắp xếp kết quả là một bước riêng, có ở phần dưới.
Lọc theo giá trị của một ô
Viết cứng "North" trong công thức nghĩa là mỗi lần đổi phải sửa công thức. Hãy đặt giá trị vào một ô và so sánh với ô đó. Chọn khu vực khác ở F1 thì kết quả thay đổi theo:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Region | North | ||
| 2 | Ann | North | 120 | ||||
| 3 | Ben | South | 80 | Ann | North | 120 | |
| 4 | Cara | North | 200 | Cara | North | 200 | |
| 5 | Dan | East | 150 | Finn | North | 60 | |
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
West không có hàng nào, nên khi chọn nó, ô sẽ hiện văn bản if_empty là No sales.
Với số cũng vậy. C2:C7>=F1 với 100 trong F1 giữ mọi hàng có doanh số ít nhất 100, còn C2:C7>F1 thì yêu cầu lớn hơn hẳn.
FILTER với nhiều điều kiện (AND)
Để chỉ giữ một hàng khi cả hai điều kiện cùng đúng, hãy nhân chúng với nhau. Công thức này trả về các hàng North có doanh số lớn hơn 100:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Cara | North | 200 | |
| 4 | Cara | North | 200 | ||||
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Ann (120) và Cara (200) thỏa điều kiện. Finn thuộc North nhưng 60 không lớn hơn 100, nên bị loại.
Vì sao lại nhân: mỗi điều kiện là một cột TRUE và FALSE, và trong phép tính TRUE được tính là 1, FALSE là 0. Một hàng chỉ nhận 1 khi mọi thừa số đều bằng 1, nên * hoạt động như AND. Mỗi điều kiện cần có cặp ngoặc riêng, và bạn có thể nối bao nhiêu điều kiện tùy thích: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).
AND() không dùng được ở đây. AND(B2:B7="North",C2:C7>100) gộp cả vùng thành một giá trị TRUE hoặc FALSE duy nhất thay vì một giá trị cho mỗi hàng, nên FILTER nhận sai dạng dữ liệu.
FILTER với OR
Cộng các điều kiện để giữ một hàng khi ít nhất một điều kiện đúng. Công thức này trả về các hàng North và East:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Cara | North | 200 | |
| 4 | Cara | North | 200 | Dan | East | 150 | |
| 5 | Dan | East | 150 | Finn | North | 60 | |
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Một hàng thỏa cả hai điều kiện sẽ cộng lại thành 2, và FILTER giữ mọi hàng có kết quả khác 0, nên phép cộng hoạt động như OR. Bạn có thể kết hợp cả hai: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100) nghĩa là (North hoặc East) và lớn hơn 100. Ở đây công thức trả về Ann, Cara và Dan.
FILTER trả về #CALC! khi không có gì khớp
Khi không có hàng nào thỏa điều kiện, FILTER không có gì để trả về. Không có tham số thứ ba thì đó là lỗi #CALC!; có tham số đó thì bạn nhận được văn bản của riêng mình:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | No if_empty | With if_empty | |
| 2 | Ann | North | 120 | #CALC! | No match | |
| 3 | Ben | South | 80 | |||
| 4 | Cara | North | 200 | |||
| 5 | Dan | East | 150 | |||
| 6 | Eve | South | 95 | |||
| 7 | Finn | North | 60 |
#CALC! Phép tính không có kết quả, ví dụ một hàm FILTER không khớp gì.E2 hiện #CALC! còn F2 hiện No match. Đổi B3 từ South thành West thì cả hai công thức đều trả về Ben. Để không hiện gì cả, dùng chuỗi rỗng: =FILTER(A2:A7,B2:B7="West","").
Trang tính này cũng cho thấy cách lọc một cột duy nhất: array là A2:A7, nên chỉ có tên được trả về. Để lấy một số cột của bảng chứ không phải tất cả, bọc kết quả trong CHOOSECOLS: =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) trả về tên và doanh số mà không có khu vực. CHOOSECOLS cần Microsoft 365 hoặc Excel 2024.
Sắp xếp kết quả của FILTER
FILTER trả về các hàng theo thứ tự chúng xuất hiện trong bảng. Bọc nó trong SORT để sắp xếp kết quả: ở đây là các hàng North sắp theo doanh số, lớn nhất trước. Số 3 là cột của kết quả dùng để sắp xếp, còn -1 nghĩa là giảm dần.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Cara | North | 200 | |
| 3 | Ben | South | 80 | Ann | North | 120 | |
| 4 | Cara | North | 200 | Finn | North | 60 | |
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Cara (200) đứng đầu, tiếp theo là Ann (120) và Finn (60). Để chỉ lấy các hàng đầu, bọc thêm một lần nữa trong TAKE: =TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2) giữ hai hàng đầu tiên (TAKE cần Microsoft 365 hoặc Excel 2024). SORT và SORTBY trình bày các tùy chọn sắp xếp khác.
Lọc các hàng chứa một đoạn văn bản
FILTER không có ký tự đại diện, nên B2:B7="*th*" tìm đúng nguyên văn bản *th*. Để giữ các hàng có tên chứa một đoạn văn bản, hãy kiểm tra từng ô bằng SEARCH, hàm trả về một vị trí khi tìm thấy và báo lỗi khi không tìm thấy, rồi bọc nó trong ISNUMBER:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | Ann | North | 120 | |
| 3 | Ben | South | 80 | Dan | East | 150 | |
| 4 | Cara | North | 200 | ||||
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Công thức này trả về Ann và Dan: SEARCH không phân biệt hoa thường, nên "an" cũng khớp với An trong Ann. Dùng FIND thay cho SEARCH nếu cần phân biệt chữ hoa chữ thường.
Luyện tập: FILTER với hai điều kiện
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Name | Region | Sales | |
| 2 | Ann | North | 120 | ||||
| 3 | Ben | South | 80 | ||||
| 4 | Cara | North | 200 | ||||
| 5 | Dan | East | 150 | ||||
| 6 | Eve | South | 95 | ||||
| 7 | Finn | North | 60 |
Đến lượt bạn: Trong E2, trả về các hàng (cả ba cột) của nhân viên South có doanh số lớn hơn 85.
Luyện tập: FILTER theo một ô, có giá trị dự phòng
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Region | Sales | Region | North | |
| 2 | Ann | North | 120 | |||
| 3 | Ben | South | 80 | Names | ||
| 4 | Cara | North | 200 | |||
| 5 | Dan | East | 150 | |||
| 6 | Eve | South | 95 | |||
| 7 | Finn | North | 60 |
Đến lượt bạn: Trong F3, liệt kê tên (chỉ cột A) của các nhân viên thuộc khu vực được gõ trong F1. Nếu không có ai, hiện None.
Các lỗi thường gặp với FILTER
- Các vùng có chiều cao khác nhau.
=FILTER(A2:C7,B2:B6="North")kiểm tra 5 hàng cho một bảng 6 hàng, và Excel trả về #VALUE!. Hãy choincludebắt đầu và kết thúc ở cùng các hàng vớiarray. - Số 0 ở chỗ dữ liệu gốc trống. FILTER trả về 0 cho một ô trống trong
array. Đổi các ô trống thành văn bản rỗng trước khi lọc:=FILTER(IF(A2:C7="","",A2:C7),B2:B7="North"). - Cả cột.
=FILTER(A:C,B:B="North")vẫn chạy, nhưng nếu chính công thức nằm trong các cột từ A đến C thì nó tham chiếu tới chính nó. Hãy đặt kết quả bên cạnh bảng, hoặc dùng một vùng cố định như A2:C1000. - Đặt số trong dấu ngoặc kép.
C2:C7>"100"so sánh số với văn bản và không giữ lại gì. Hãy viếtC2:C7>100. - Nhầm với nút Filter. FILTER sao chép các hàng khớp sang chỗ mới và để nguyên bảng. Để ẩn hàng trong chính bảng, dùng Data > Filter.
Câu hỏi thường gặp
Cách dùng hàm FILTER trong Excel?
Truyền cho hàm các hàng cần trả về và một điều kiện cho mỗi hàng: =FILTER(A2:C7,B2:B7="North") trả về mọi hàng của A2:C7 có cột B là North. Gõ công thức vào một ô; các hàng khớp sẽ tràn (spill) xuống các ô bên dưới và bên cạnh.
Làm sao dùng FILTER với nhiều điều kiện trong Excel?
Nhân các điều kiện để có AND và cộng chúng để có OR: =FILTER(A2:C7,(B2:B7="North")*(C2:C7>100)) giữ các hàng thỏa cả hai, =FILTER(A2:C7,(B2:B7="North")+(B2:B7="East")) giữ các hàng thỏa một trong hai. Mỗi điều kiện cần có cặp ngoặc riêng.
Vì sao FILTER trả về #CALC!?
Vì không có hàng nào khớp và bạn không truyền tham số thứ ba. Thêm tham số đó để hiển thị nội dung khác: =FILTER(A2:C7,B2:B7="West","No match") hiện No match thay cho lỗi.
Những phiên bản Excel nào có hàm FILTER?
Excel 2021, Excel 2024 và Microsoft 365, cùng với Excel cho web. Excel 2019 trở về trước không có hàm này và hiện #NAME?; ở đó bạn cần nút Filter trên thẻ Data hoặc một công thức mảng INDEX và SMALL.
Làm sao chỉ trả về một số cột với FILTER?
Chỉ lọc những cột bạn cần, hoặc bọc kết quả trong CHOOSECOLS (Microsoft 365 hoặc Excel 2024): =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3) trả về cột thứ nhất và thứ ba của các hàng khớp.
