Bộ lọc nâng cao với phạm vi tiêu chí trong Excel (18 Ứng dụng)

Trong Microsoft Excel, Bộ lọc nâng cao tùy chọn này hữu ích khi tìm kiếm dữ liệu đáp ứng hai tiêu chí trở lên. Trong bài viết này, chúng tôi sẽ thảo luận về các ứng dụng của Bộ lọc nâng cao Phạm vi tiêu chí trong Excel.

Tải xuống sách bài tập thực hành từ đây.

18 Ứng dụng của Phạm vi tiêu chí bộ lọc nâng cao trong Excel

1. Sử dụng Phạm vi tiêu chí bộ lọc nâng cao cho Số và Ngày

Đầu tiên và quan trọng nhất, chúng ta sẽ được giới thiệu về tập dữ liệu của mình. Cột B đến Cột E đại diện cho các dữ liệu khác nhau liên quan đến bán hàng. Giờ đây, chúng tôi có thể triển khai Phạm vi tiêu chí bộ lọc nâng cao tại đây . Trong ví dụ này, chúng tôi sẽ sử dụng Phạm vi tiêu chí bộ lọc nâng cao để lọc số và ngày. Chúng tôi sẽ trích xuất tất cả dữ liệu trong đó số lượng bán hàng lớn hơn 10 . Hãy xem quy trình.

• Đầu tiên, trong Dữ liệu , chọn tab Nâng cao từ lệnh Sắp xếp &Bộ lọc quyền mua. Hộp thoại có tên Bộ lọc nâng cao sẽ xuất hiện.

• Tiếp theo, chọn toàn bộ bảng (B4:E14) cho Phạm vi danh sách .
• Chọn ô (C17:C18) dưới dạng Phạm vi tiêu chí .
• Nhấn OK .

• Cuối cùng, chúng tôi chỉ có thể thấy dữ liệu có số lượng lớn hơn 10 .

Lưu ý:
1. Chọn tiêu chí có ít nhất hai hàng.

2. Chúng tôi sẽ sử dụng tiêu đề cho các cột liên quan nơi các tiêu chí lọc sẽ được áp dụng.

2. Lọc Giá trị Văn bản với Tiêu chí Lọc Nâng cao

Chúng ta có thể so sánh các giá trị văn bản bằng cách sử dụng toán tử logic ngoài số và ngày. Trong phần này, chúng tôi sẽ thảo luận về cách chúng tôi có thể lọc giá trị văn bản với Tiêu chí Bộ lọc Nâng cao để có kết quả khớp chính xác với Văn bản cũng như có một ký tự cụ thể ở đầu.

2.1 Để đối sánh chính xác văn bản

Trong phương pháp này, Lọc sẽ trả lại cho chúng tôi giá trị chính xác của văn bản đầu vào. Giả sử chúng ta có tập dữ liệu sau về doanh số bán hàng cùng với cột mới Thành phố . Chúng tôi sẽ chỉ trích xuất dữ liệu cho thành phố ‘NEW YORK’ . Chỉ cần thực hiện các bước sau để thực hiện hành động này:

• Ở phần đầu, hãy chọn ô C18 . Chèn công thức sau:

• Nhấn Enter .

• Tiếp theo, chọn phạm vi tiêu chí bộ lọc sau:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C17:C18

• Nhấn OK .

• Cuối cùng, chúng tôi sẽ chỉ nhận được dữ liệu cho thành phố ‘NEW YORK’ .

2.1 Có đặc điểm cụ thể ngay từ đầu

Bây giờ chúng tôi sẽ lọc các giá trị văn bản để bắt đầu bằng một ký tự cụ thể hơn là một kết quả khớp chính xác. Ở đây, chúng tôi sẽ chỉ trích xuất giá trị của các thành phố bắt đầu bằng từ ‘Mới’ . Hãy xem cách thực hiện điều đó.

• Trước hết, hãy chọn các phạm vi tiêu chí trong Bộ lọc Nâng cao hộp:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C18:C19

• Nhấn OK .

• Cuối cùng, chúng tôi sẽ nhận được dữ liệu cho tất cả các thành phố bắt đầu bằng từ ‘Mới’ .

3. Sử dụng Ký tự đại diện với Tùy chọn Bộ lọc Nâng cao

Việc sử dụng ký tự đại diện ký tự là một cách khác để áp dụng Phạm vi tiêu chí bộ lọc nâng cao . Thông thường, có ba loại ký tự đại diện trong excel:

? (Dấu hỏi) - Đại diện cho bất kỳ ký tự đơn nào trong văn bản.

* (Dấu hoa thị) - Đại diện cho bất kỳ số ký tự nào.

~ (Dấu ngã) - Đại diện cho sự hiện diện của một ký tự đại diện trong văn bản.

Chúng tôi có thể tìm kiếm một chuỗi văn bản cụ thể trong tập dữ liệu của mình bằng cách sử dụng Dấu hoa thị (*) . Trong ví dụ này, chúng tôi tìm thấy tên của những người bán hàng bắt đầu bằng văn bản ‘J’ . Để làm được điều đó, chúng ta cần làm theo các bước sau.

• Đầu tiên, hãy mở Bộ lọc Nâng cao cửa sổ. Chọn phạm vi tiêu chí sau:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C17:C18

• Nhấn OK .

• Cuối cùng, chúng tôi sẽ chỉ nhận được tên của những người bán hàng bắt đầu bằng văn bản ‘J’ .

Nội dung liên quan: Bộ lọc nâng cao của Excel [Nhiều cột &tiêu chí, sử dụng công thức &với ký tự đại diện]

4. Áp dụng Công thức với Phạm vi Tiêu chí Bộ lọc Nâng cao

Một cách khác để sử dụng Phạm vi tiêu chí bộ lọc nâng cao là áp dụng công thức. Trong ví dụ này, chúng tôi sẽ trích xuất số tiền bán hàng lớn hơn $ 350 . Chỉ cần làm theo các bước dưới đây:

• Ở phần đầu, hãy chọn ô C19 . Chèn công thức sau:

• Nhấn OK .

Công thức lặp lại giá trị của số tiền bán hàng cho dù nó lớn hơn $ 350 hoặc không.

• Tiếp theo, chọn phạm vi tiêu chí sau trong Bộ lọc Nâng cao hộp thoại:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C17:C19

• Nhấn OK .

• Vì vậy, chúng tôi chỉ có thể xem dữ liệu cho các giá trị bán hàng lớn hơn $ 350 .

5. Bộ lọc nâng cao với tiêu chí logic AND

Bây giờ chúng tôi sẽ giới thiệu AND logic trong Phạm vi tiêu chí bộ lọc nâng cao. Logic này sử dụng hai tiêu chí. Nó trả về giá trị đầu ra khi dữ liệu thỏa mãn cả hai tiêu chí. Ở đây chúng tôi có tập dữ liệu sau. Trong tập dữ liệu này, chúng tôi sẽ lọc dữ liệu cho thành phố New York cũng như có giá trị bán hàng > =200 . Hãy xem cách thực hiện điều đó.

• Trước tiên, hãy chuyển đến Bộ lọc Nâng cao hộp thoại chọn phạm vi tiêu chí sau:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C18:C19

• Nhấn OK .

• Cuối cùng, chúng tôi sẽ nhận được tập dữ liệu chỉ cho thành phố New York có doanh số bán hàng giá trị lớn hơn $ 250 .

6. Sử dụng Logic HOẶC với Phạm vi tiêu chí bộ lọc nâng cao

Giống như VÀ lôgic, lôgic HOẶC cũng sử dụng hai tiêu chí. VÀ logic trả về kết quả đầu ra nếu cả hai tiêu chí đều được đáp ứng trong khi HOẶC logic trả về nếu chỉ một tiêu chí được đáp ứng. Tại đây, chúng tôi sẽ cung cấp dữ liệu cho các thành phố New York và Texas chỉ có. Chỉ cần làm theo các bước dưới đây để thực hiện hành động này:

• Lúc đầu, hãy mở Bộ lọc nâng cao Hộp thoại. Nhập phạm vi tiêu chí sau:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C18:C20

• Nhấn OK.

• Cuối cùng, chúng tôi chỉ nhận được tập dữ liệu cho các thành phố New York và Texas .

7. Sự kết hợp của AND &OR Logic làm Phạm vi tiêu chí

Đôi khi, chúng tôi có thể cần phải lọc dữ liệu cho nhiều tiêu chí. Trong trường hợp đó, chúng tôi có thể sử dụng kết hợp VÀ & HOẶC Hợp lý. Chúng tôi sẽ trích xuất dữ liệu từ tập dữ liệu sau dựa trên các tiêu chí đã cho. Chỉ cần thực hiện các bước sau để thực hiện hành động này:

• Trước tiên, hãy mở Bộ lọc Nâng cao Hộp thoại. Chọn các tiêu chí sau:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C18:C20

• Sau đó nhấn OK.

• Vì vậy, chúng tôi chỉ có thể xem tập dữ liệu phù hợp với tiêu chí của chúng tôi.

8. Sử dụng Phạm vi tiêu chí bộ lọc nâng cao để trích xuất các cột cụ thể

Trong ví dụ này, chúng tôi sẽ lọc các phần cụ thể của tập dữ liệu. Sau khi lọc chúng ta sẽ chuyển phần đã lọc sang một cột khác. Chúng tôi sẽ sử dụng tập dữ liệu sau để thực hiện hành động này thông qua quy trình dưới đây.

• Đầu tiên, từ Bộ lọc Nâng cao hộp thoại chọn các tiêu chí sau:

Phạm vi danh sách:B4:F14

Phạm vi tiêu chí:C18:C20

• Chọn sao chép sang một vị trí khác tùy chọn.
• Nhập Sao chép vào phạm vi H8:I10 .
• Nhấn OK.

• Vì vậy, chúng tôi nhận được dữ liệu đã lọc trong H8:I10 theo tiêu chí của chúng tôi.

9. Sao chép dữ liệu sang một trang tính khác sau khi lọc

Trong ví dụ này, chúng tôi cũng sẽ sao chép dữ liệu trong một trang tính khác trong khi trong ví dụ trước, chúng tôi đã thực hiện nó trong cùng một trang tính. Thực hiện các bước sau để thực thi nó:

• Trước tiên, hãy chuyển đến ‘Another Worksheet-2’ nơi chúng tôi sẽ sao chép dữ liệu sau khi lọc.

Chúng ta có thể thấy hai cột ‘Thành phố’ và 'Bán hàng' in ‘Another Worksheet-2’ .

• Next, open the ‘Advanced Filter’ hộp thoại.

• Then go to ‘Another Worksheet-1’ . Select the following criteria:

List Range:B4:F14

Criteria Range:C18:C19

• Now, select copy to another location tùy chọn.

• After that, go to ‘Another Worksheet-2’ . Select Copy to Range B2:C4 .
• Nhấn OK .

• Finally, we can see the filtered data in ‘Another Worksheet-2’ .

10. Extract Unique Records with Advanced Filter Criteria

In this case, we will extract only the unique values from a specific column. From the following dataset, we will extract unique values of cities in another column. Just do the steps:

• In the beginning, open the Advanced Filter cửa sổ. Select the criteria

List range:D4:D14

• Next, select the option Copy to another location .
• Then, input Copy to range as H4:H8 .
• Check the box Unique records only .
• Nhấn OK .

• Finally, we can see the names of cities with unique records only in column H .

11. Find Weekdays with Advanced Filter Criteria Range

We can find Weekdays with Advanced Filter Criteria Range. Here we will use the following dataset to illustrate this process:

• Firstly, select cell C19 . Insert the following formula:

• Next, set the following criteria range in the Advanced Filter dialogue box:

List Range:B4:F14

Criteria Range:C18:C19

• Nhấn OK .

• Finally, we will get the Date values only for weekdays.

🔎 Công thức hoạt động như thế nào?

• WEEKDAY(B5)<>1:1 denotes Sunday. This part set the criteria that the date is not Sunday .
• WEEKDAY(B5)<>7:7 denotes Sunday. This part set the criteria that the date is not Saturday .
• AND(WEEKDAY(B5)<>1,WEEKDAY(B5)<>7): Set the criteria that the day is neither Saturday nor Sunday .

12. Apply Advanced Filter to Find Weekend

We can also use the Advanced Filter Criteria Range to find the Weekend from a Date column. Let’s see how to do that using the following dataset:

• In the beginning select cell C19. Insert the following formula:

• Nhấn Enter .

• Next, from the Advanced Filter dialogue box select the following criteria range:

List Range:B4:F14

Criteria Range:C18:C19

• Nhấn OK .

• So, we can see only the values of the weekend in the Date cột.

13. Use Advanced Filter to Calculate Values Below or Above Average

In this section, we will calculate the below or above average value by using Advanced Filter Criteria Range . Here we will only filter the sales value which is greater than the average sales value.

• First, select cell C19 . Insert the following formula:

• Next, open the Advanced Filter Hộp thoại. Input the following criteria range:

List Range:B4:F14

Criteria Range:C18:C19

• Nhấn OK .

• So, we get only the dataset for sales value greater than the average value.

14. Filtering Blank Cells with OR Logic

If our dataset consists of blank cells, we can extract blank cells by using Advanced Filter .

We have the following dataset. The dataset consists of blank cells . We have set the criteria by using the following formula:

• First, go to the Advanced Filte r dialogue box. Input the following criteria:

List Range:B4:F14

Criteria Range:C17:C22

• Nhấn OK .

• Finally, we get the dataset that only consists of blank cells.

15. Apply Advanced Filter to Filter Non-Blank Cells using OR as well as AND Logic

In this example, we will eliminate blank cells whereas in the previous example we eliminated the nonblank cells. We have set the following criteria for using the formula:

• Firstly, go to the Advanced Filter Hộp thoại. Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:G18

• Now press OK .

• So, we get the dataset free from blank cells.

16. Find First 5 Records Using Advanced Filter Criteria Range

Now we will implement the Advanced Filter option for extracting the first 5 records from any kind of dataset. In this example, we will take the first five values of the Sales cột. To perform this we will first set the criteria based on the following formula:

After that, just do the following steps:

• In the beginning, go to the Advanced Filter Hộp thoại. Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Nhấn OK .

• Finally, we get the top five records of the Sales cột.

17. Use Advanced Filter Criteria Range to Find Bottom Five Records

We can use the Advanced Filter option to find the bottom five records also. To find the bottom five records for the Sales column, we will create the following criteria using the below formula:

Then follow the below steps to perform this action:

• First, insert the following criteria range in the Advanced Filter dialogue box:

List Range:B4:F14

Criteria Range:C17:C18

• Sau đó, nhấn OK .

• Lastly, we can see the bottom five values of the Sales cột.

18. Filter Rows According to a List’s Matched Entries Using Advanced Filter Criteria Range

Sometimes we may need to compare between two columns or rows of a dataset to eliminate or keep particular values. We can use the match entry option to perform this kind of action.

18.1 Matches with Items in a List

Suppose we have the following dataset with two columns of cities. We will take only the matching entries between these two columns. In order to do this we will set the following criteria using the below formula:

Just do the following steps to perform this action:

• In the beginning, open the Advanced Filter quyền mua. Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Nhấn OK .

• Lastly, We can see the same value in two columns of cities.

18.2 Do Not Matches with Items in a List

The previous example was for matching entries whereas this example will filter non-matching entries. We will set the criteria by using the following formula:

Let’s see how to perform this:

• First, from the Advance Filter insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Then, press OK .

• Finally, we will get the values of cities in Column C and Column E that do not match with one another.

Kết luận

In this article, we have tried to cover all the methods of the Advanced Filter Criteria Range quyền mua. Download our practice workbook added to this article and practice yourself. If you feel any confusion or have any suggestions just leave a comment below, we will try to reply to you as soon as possible.

Các bài viết liên quan

• Excel Advanced Filter Not Working (2 Reasons &Solutions)
• Dynamic Advanced Filter Excel (VBA &Macro)
• How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)