Nếu dữ liệu bạn muốn lọc yêu cầu tiêu chí trên nhiều trường, chẳng hạn như lọc theo nhiều điều kiện mà tất cả đều phải đúng hoặc hiển thị các hàng khớp với bất kỳ điều kiện nào trong một số điều kiện khác nhau (chẳng hạn như Loại = "Nông sản" OR Người bán hàng = "Davolio"), bạn có thể sử dụng hộp thoại Bộ lọc Nâng cao.
Để mở hộp thoại Bộ lọc Nâng cao, hãy bấm Nâng cao Dữ>liệu.
| Bộ lọc Nâng cao | Ví dụ |
|---|---|
| Tổng quan về tiêu chí lọc nâng cao | |
| Nhiều tiêu chí, một cột, bất kỳ tiêu chí nào đúng | Nhân viên bán hàng = "Davolio" OR Nhân viên bán hàng = "Buchanan" |
| Nhiều tiêu chí, nhiều cột, tất cả tiêu chí đều đúng | Loại = "Nông sản" AND Doanh số > 1000 |
| Nhiều tiêu chí, nhiều cột, bất kỳ tiêu chí nào đúng | Loại = "Nông sản" OR Nhân viên bán hàng = "Buchanan" |
| Nhiều bộ tiêu chí, một cột trong tất cả các bộ | (Doanh số > 6000 AND Doanh số < 6500 ) OR (Doanh số < 500) |
| Nhiều bộ tiêu chí, nhiều cột trong mỗi bộ | (Người bán hàng = "Davolio" AND Doanh số >3000) OR (Nhân viên bán hàng = "Buchanan" AND Doanh số > 1500) |
| Tiêu chí ký tự đại diện | Nhân viên bán hàng = tên có 'u' là chữ cái thứ hai |
Tổng quan về tiêu chí lọc nâng cao
Bộ lọc Nâng cao hoạt động khác với Bộ lọc theo một số cách quan trọng.
- Nó hiển thị hộp thoại Bộ lọc Nâng cao thay cho menu Tự Lọc.
- Bạn tạo phạm vi tiêu chí (tách biệt các ô ở trên dữ liệu) nơi bạn nhập điều kiện lọc, rồi yêu cầu hộp thoại Bộ lọc Nâng cao sử dụng phạm vi đó.
- Bộ lọc Nâng cao KHÔNG tự động cập nhật khi bạn thay đổi giá trị tiêu chí
Lưu ý
Bộ lọc Nâng cao vẫn sẵn dùng cho các kịch bản lọc phức tạp, mặc dù các tính năng mới hơn như Copilot trong Excel hiện có thể hỗ trợ người dùng phân tích dữ liệu và lọc thông qua truy vấn ngôn ngữ tự nhiên như một phương pháp thay thế cho một số trường hợp sử dụng.
Hiểu rõ AND và Lô-gic OR
| Loại Lô-gic | Cách Thiết lập | Ví dụ | Nội dung tìm thấy |
|---|---|---|---|
| Lô-gic AND (tất cả các tiêu chí đều phải đúng) | Đặt tiêu chí trong cùng một hàng | Nhập = "Nông sản" trong cột 1 Doanh > số 1000 trong cột 2 (cả hai đều ở cùng hàng) |
Chỉ những hàng có Loại IS "Nông sản" AND Doanh số lớn hơn 1000 |
| Lô-gic OR (bất kỳ tiêu chí nào cũng có thể đúng) | Đặt tiêu chí vào hàng khác nhau | Hàng 1: Loại = "Nông sản" Hàng 2: Loại = "Thịt" (các hàng khác nhau, cùng một cột) |
Hàng có Loại IS "Nông sản" OR Loại IS "Thịt" (hoặc cả hai) |
Dữ liệu mẫu
Dữ liệu mẫu sau đây được sử dụng cho tất cả các thủ tục trong bài viết này.
Dữ liệu bao gồm ba hàng trống ở phía trên phạm vi danh sách sẽ được dùng làm phạm vi tiêu chí (A1:C4) và phạm vi danh sách (A6:C10). Phạm vi tiêu chí có các nhãn cột và chứa ít nhất một hàng trống giữa các giá trị tiêu chí và phạm vi danh sách.
Để làm việc với dữ liệu này, hãy chọn nó trong bảng sau đây, sao chép nó, rồi dán nó vào ô A1 của một trang tính Excel mới.
| Loại | Nhân viên bán hàng | Doanh số |
|---|---|---|
| Loại | Nhân viên bán hàng | Doanh số |
| Nước giải khát | Suyama | 5122 $ |
| Thịt | Davolio | 450 $ |
| nông sản | Buchanan | 6328 $ |
| Nông sản | Davolio | 6544 $ |
Trong ví dụ này, trang tính kết quả sẽ trông như thế này, trong đó phạm vi tiêu chí lọc được viền ngoài màu lam và phạm vi danh sách (dữ liệu bạn muốn lọc) được viền ngoài bằng màu đỏ.
Toán tử so sánh
Bạn có thể so sánh hai giá trị bằng cách dùng các toán tử sau đây. Khi hai giá trị được so sánh bằng cách sử dụng những toán tử này, kết quả sẽ là giá trị lô-gic—TRUE hoặc FALSE.
| Toán tử so sánh | Ý nghĩa | Ví dụ |
|---|---|---|
| = (dấu bằng) | Bằng | A1=B1 |
| > (dấu lớn hơn) | Lớn hơn | A1>B1 |
| < (dấu nhỏ hơn) | Nhỏ hơn | A1<B1 |
| >= (dấu lớn hơn hoặc bằng) | Lớn hơn hoặc bằng | A1>=B1 |
| <= (dấu nhỏ hơn hoặc bằng) | Nhỏ hơn hoặc bằng | A1<=B1 |
| <> (dấu khác) | Khác | A1<>B1 |
Sử dụng dấu bằng để nhập văn bản hoặc giá trị
Vì dấu bằng (=) được dùng để chỉ báo công thức khi bạn nhập văn bản hoặc giá trị vào ô, Nên Excel sẽ đánh giá nội dung bạn nhập; tuy nhiên, điều này có thể gây ra kết quả lọc không mong muốn. Để chỉ báo toán tử so sánh đẳng thức cho văn bản hoặc giá trị, hãy nhập tiêu chí dưới dạng biểu thức chuỗi vào ô thích hợp trong phạm vi tiêu chí:
=''=mục nhập''
Trong đó mục nhập là văn bản hoặc giá trị bạn muốn tìm. Ví dụ:
| Những gì bạn gõ vào ô | Những gì Excel đánh giá và hiển thị |
|---|---|
| ="=Davolio" | =Davolio |
| ="=3000" | =3000 |
Cân nhắc phân biệt chữ hoa chữ thường
Khi lọc dữ liệu văn bản, Excel không phân biệt ký tự chữ hoa và ký tự chữ thường. Tuy nhiên, bạn có thể dùng một công thức để thực hiện tìm kiếm phân biệt chữ hoa, chữ thường. Chẳng hạn, hãy xem phần Tiêu chí ký tự đại diện.
Sử dụng tên đã xác định trước
Bạn có thể đặt tên cho một phạm vi là Tiêu chí, và tham chiếu cho phạm vi đó sẽ tự động xuất hiện trong hộp Phạm vi tiêu chí. Bạn cũng có thể xác định tên Cơ sở dữ liệu cho phạm vi danh sách cần lọc và xác định tên Trích xuất cho phạm vi mà bạn muốn dán hàng tại đó và các phạm vi này sẽ tự động xuất hiện tương ứng trong hộp Phạm vi danh sách và Sao chép vào.
Tạo tiêu chí bằng cách sử dụng công thức
Bạn có thể dùng giá trị được tính vốn là kết quả của công thức để làm tiêu chí. Hãy ghi nhớ các điểm quan trọng sau đây:
- Công thức phải đánh giá là TRUE hoặc FALSE.
- Vì bạn đang dùng công thức, hãy nhập công thức như thông thường và không gõ biểu thức theo cách sau đây:
=''=entry'' - Không dùng nhãn cột để làm nhãn tiêu chí; hãy để trống nhãn tiêu chí hoặc dùng nhãn không phải là nhãn cột trong phạm vi danh sách (trong ví dụ dưới đây, đó là Giá trị Trung bình Tính toán và Kết quả khớp Chính xác).
Nếu bạn sử dụng nhãn cột trong công thức thay vì tham chiếu ô tương đối hoặc tên dải ô, Excel sẽ hiển thị giá trị lỗi chẳng hạn như giá trị #NAME? hoặc #VALUE! trong ô chứa tiêu chí. Bạn có thể bỏ qua lỗi này vì nó không ảnh hưởng đến cách lọc phạm vi danh sách. - Công thức mà bạn dùng cho tiêu chí phải sử dụng tham chiếu tương đối để tham chiếu đến ô tương ứng trong hàng dữ liệu đầu tiên.
- Tất cả các tham chiếu khác trong công thức phải là tham chiếu tuyệt đối.
Nhiều tiêu chí, một cột, bất kỳ tiêu chí nào đúng
Logic Boole: (Nhân viên bán hàng = "Davolio" OR Nhân viên bán hàng = "Buchanan")
Sử dụng công thức này khi bạn muốn lọc các hàng có một cột khớp với BẤT KỲ giá trị nào trong một vài giá trị. Cả hai hàng với Davolio AND hàng với Buchanan sẽ được hiển thị.
Để tìm những hàng đáp ứng nhiều tiêu chí cho một cột, hãy gõ tiêu chí này ngay dưới tiêu chí kia trong các hàng riêng biệt của phạm vi tiêu chí. Ví dụ: nhập nội dung sau vào hai hàng đầu tiên của phạm vi tiêu chí:
Loại Nhân viên bán hàng Doanh số ="=Davolio" ="=Buchanan" Bấm vào một ô trong phạm vi danh sách.
Trên tab Dữ liệu, trong nhóm Sắp xếp & Lọc, hãy bấm Nâng cao.
Chọn Lọc danh sách, tại chỗ , ẩn các hàng không khớp với tiêu chí của bạn hoặc Sao chép vào một vị trí khác , sao chép các hàng khớp với tiêu chí của bạn vào một khu vực khác của trang tính.
Trong hộp Phạm vi tiêu chí, hãy nhập tham chiếu cho phạm vi tiêu chí, bao gồm nhãn tiêu chí. Sử dụng ví dụ, hãy nhập $A$1:$C$3.
Sử dụng ví dụ, kết quả đã lọc cho phạm vi danh sách là:
Loại Nhân viên bán hàng Doanh số Thịt Davolio 450 $ nông sản Buchanan 6.328 $ Nông sản Davolio 6.544 $
Nhiều tiêu chí, nhiều cột, tất cả tiêu chí đều đúng
Lô-gic Boolean: (Loại = "Nông sản" AND Doanh > số 1000)
Để tìm các hàng đáp ứng nhiều tiêu chí trong nhiều cột, hãy gõ tất cả các tiêu chí vào cùng hàng của phạm vi tiêu chí. Ví dụ: nhập:
Loại Nhân viên bán hàng Doanh số ="=Nông sản" >1000 Bấm vào một ô trong phạm vi danh sách.
Trên tab Dữ liệu, trong nhóm Sắp xếp & Lọc, hãy bấm Nâng cao.
Chọn Lọc danh sách, tại chỗ , ẩn các hàng không khớp với tiêu chí của bạn hoặc Sao chép vào một vị trí khác , sao chép các hàng khớp với tiêu chí của bạn vào một khu vực khác của trang tính.
Trong hộp Phạm vi tiêu chí, hãy nhập tham chiếu cho phạm vi tiêu chí, bao gồm nhãn tiêu chí. Sử dụng ví dụ, hãy nhập $A$1:$C$2.
Sử dụng ví dụ, kết quả đã lọc cho phạm vi danh sách là:
Loại Nhân viên bán hàng Doanh số nông sản Buchanan 6.328 $ Nông sản Davolio 6.544 $
Nhiều tiêu chí, nhiều cột, bất kỳ tiêu chí nào đúng
Lô-gic Boolean: (Loại = "Nông sản" OR Người bán hàng = "Buchanan")
Để tìm các hàng đáp ứng nhiều tiêu chí trong nhiều cột, trong đó bất kỳ tiêu chí nào cũng có thể đúng, hãy gõ tiêu chí vào các cột và hàng khác nhau của phạm vi tiêu chí. Ví dụ: nhập:
Loại Nhân viên bán hàng Doanh số ="=Nông sản" ="=Buchanan" Bấm vào một ô trong phạm vi danh sách.
Trên tab Dữ liệu , trong nhóm Sắp xếp & lọc , hãy bấm Nâng cao.
Chọn Lọc danh sách, tại chỗ , ẩn các hàng không khớp với tiêu chí của bạn hoặc Sao chép vào một vị trí khác , sao chép các hàng khớp với tiêu chí của bạn vào một khu vực khác của trang tính.
Trong hộp Phạm vi tiêu chí, hãy nhập tham chiếu cho phạm vi tiêu chí, bao gồm nhãn tiêu chí. Sử dụng ví dụ, hãy nhập $A$1:$B$3.
Sử dụng ví dụ, kết quả đã lọc cho phạm vi danh sách là:
Loại Nhân viên bán hàng Doanh số nông sản Buchanan 6.328 $ Nông sản Davolio 6.544 $
Nhiều bộ tiêu chí, một cột trong tất cả các bộ
Logic Boolean: ( (Doanh số > 6000 AND Doanh số < 6500 ) OR (Doanh số < 500) )
Để tìm các hàng đáp ứng nhiều bộ tiêu chí, trong đó mỗi bộ chứa tiêu chí cho một cột, hãy đặt cùng một đầu đề cột cho nhiều cột. Ví dụ: nhập:
Loại Nhân viên bán hàng Doanh số Doanh số >6000 <6500 <500 Bấm vào một ô trong phạm vi danh sách. Sử dụng ví dụ, hãy bấm vào ô bất kỳ trong phạm vi danh sách A6:C10.
Trên tab Dữ liệu, trong nhóm Sắp xếp & Lọc, hãy bấm Nâng cao.
Chọn Lọc danh sách, tại chỗ , ẩn các hàng không khớp với tiêu chí của bạn hoặc Sao chép vào một vị trí khác , sao chép các hàng khớp với tiêu chí của bạn vào một khu vực khác của trang tính.
-
Mẹo
Khi sao chép hàng đã lọc vào vị trí khác, bạn có thể xác định cần đưa cột nào vào thao tác sao chép. Trước khi lọc, hãy sao chép nhãn cột cho các cột mong muốn vào hàng đầu tiên vùng mà bạn dự định dán hàng đã lọc. Khi bạn lọc, hãy nhập một tham chiếu đến các nhãn cột đã sao chép vào hộp Sao chép vào. Sau đó các hàng đã sao chép sẽ chỉ bao gồm các cột mà bạn đã sao chép nhãn.
-
Trong hộp Phạm vi tiêu chí, hãy nhập tham chiếu cho phạm vi tiêu chí, bao gồm nhãn tiêu chí. Sử dụng ví dụ, hãy nhập $A$1:$D$3.
Sử dụng ví dụ, kết quả đã lọc cho phạm vi danh sách là:
Loại Nhân viên bán hàng Doanh số Thịt Davolio 450 $ nông sản Buchanan 6.328 $
Nhiều bộ tiêu chí, nhiều cột trong mỗi bộ
Logic Boolean: ( (Người bán hàng = "Davolio" AND >Doanh số 3000) OR (Người bán hàng = "Buchanan" AND > Doanh số 1500) )
Để tìm các hàng đáp ứng nhiều bộ tiêu chí, trong đó mỗi bộ chứa tiêu chí cho nhiều cột, hãy gõ mỗi bộ tiêu chí vào từng cột và hàng riêng biệt. Ví dụ: nhập:
Loại Nhân viên bán hàng Doanh số ="=Davolio" >3000 ="=Buchanan" >1500 Bấm vào một ô trong phạm vi danh sách. Sử dụng ví dụ, hãy bấm vào ô bất kỳ trong phạm vi danh sách A6:C10.
Trên tab Dữ liệu, trong nhóm Sắp xếp & Lọc, hãy bấm Nâng cao.
Chọn Lọc danh sách, tại chỗ , ẩn các hàng không khớp với tiêu chí của bạn hoặc Sao chép vào một vị trí khác , sao chép các hàng khớp với tiêu chí của bạn vào một khu vực khác của trang tính.
Trong hộp Phạm vi tiêu chí, hãy nhập tham chiếu cho phạm vi tiêu chí, bao gồm nhãn tiêu chí. Sử dụng ví dụ, hãy nhập $A$1:$C$3.
Sử dụng ví dụ, kết quả đã lọc cho phạm vi danh sách sẽ là:
Loại Nhân viên bán hàng Doanh số nông sản Buchanan 6.328 $ Nông sản Davolio 6.544 $
Tiêu chí ký tự đại diện
Lô-gíc Boolean: Nhân viên bán hàng = tên có 'u' là chữ cái thứ hai
Để tìm những giá trị văn bản có chung một số ký tự, nhưng không có chung các ký tự khác, hãy thực hiện một hoặc nhiều thao tác sau đây:
Nhập một hoặc nhiều ký tự mà không có dấu bằng (=) để tìm các hàng có giá trị văn bản trong cột bắt đầu bằng các ký tự đó. Ví dụ, nếu bạn gõ văn bản Dav làm tiêu chí, Excel tìm thấy "Davolio," "David" và "Davis."
Dùng ký tự đại diện.
Sử dụng Để tìm ? (dấu hỏi) Ký tự đơn bất kỳ
Ví dụ: sm?th sẽ tìm "smith" và "smyth"* (dấu hoa thị) Số ký tự bất kỳ
Ví dụ: *east sẽ tìm "Northeast" và "Southeast"~ (dấu ngã) tiếp theo là ?, *, hoặc ~ Dấu chấm hỏi, dấu sao hoặc dấu ngã
Ví dụ: fy91~? tìm "fy91?"
Chèn ít nhất ba hàng trống ở phía trên phạm vi danh sách mà có thể dùng làm phạm vi tiêu chí. Phạm vi tiêu chí phải có nhãn cột. Đảm bảo có ít nhất một hàng trống giữa các giá trị tiêu chí và phạm vi danh sách.
Trong các hàng bên dưới nhãn cột, hãy nhập tiêu chí mà bạn muốn so khớp. Sử dụng ví dụ, hãy nhập:
Loại Nhân viên bán hàng Doanh số ="=Me*" ="=?u*" Bấm vào một ô trong phạm vi danh sách. Sử dụng ví dụ, hãy bấm vào ô bất kỳ trong phạm vi danh sách A6:C10.
Trên tab Dữ liệu, trong nhóm Sắp xếp & Lọc, hãy bấm Nâng cao.
Chọn Lọc danh sách, tại chỗ , ẩn các hàng không khớp với tiêu chí của bạn hoặc Sao chép vào một vị trí khác , sao chép các hàng khớp với tiêu chí của bạn vào một khu vực khác của trang tính.
Trong hộp Phạm vi tiêu chí, hãy nhập tham chiếu cho phạm vi tiêu chí, bao gồm nhãn tiêu chí. Sử dụng ví dụ, hãy nhập $A$1:$B$3.
Sử dụng ví dụ, kết quả đã lọc cho phạm vi danh sách là:
Loại Nhân viên bán hàng Doanh số Nước giải khát Suyama 5.122 $ Thịt Davolio 450 $ nông sản Buchanan 6.328 $
Cách Loại bỏ hoặc Xóa Bộ lọc Nâng cao
Sau khi áp dụng Bộ lọc Nâng cao, bạn có thể muốn loại bỏ nó để xem lại tất cả dữ liệu của mình. Sau đây là cách thực hiện:
- Bấm vào ô bất kỳ trong dải dữ liệu đã lọc của bạn.
- Đi tới tab Dữ liệu.
- Trong nhóm Sắp xếp & lọc, bấm Xóa.
- Tất cả các hàng sẽ được hiển thị lại.
Bạn cần thêm trợ giúp?
Bạn luôn có thể yêu cầu chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.