ตัวกรองขั้นสูงที่มีช่วงเกณฑ์ใน Excel (18 แอปพลิเคชัน)

ใน Microsoft Excel ตัวกรองขั้นสูง ตัวเลือกมีประโยชน์เมื่อค้นหาข้อมูลที่ตรงตามเกณฑ์ตั้งแต่สองเกณฑ์ขึ้นไป ในบทความนี้ เราจะพูดถึงการใช้งาน ตัวกรองขั้นสูง  ช่วงเกณฑ์ ใน Excel

ดาวน์โหลดแบบฝึกหัดได้จากที่นี่

18 การประยุกต์ใช้ช่วงเกณฑ์ตัวกรองขั้นสูงใน Excel

1. การใช้ช่วงเกณฑ์การกรองขั้นสูงสำหรับตัวเลขและวันที่

ก่อนอื่น เราจะมาทำความรู้จักกับชุดข้อมูลของเราก่อน คอลัมน์ B ไปที่คอลัมน์ E แสดงถึงข้อมูลต่างๆ ที่เกี่ยวข้องกับการขาย ตอนนี้เราสามารถดำเนินการได้ที่นี่ ช่วงเกณฑ์ตัวกรองขั้นสูง . ในตัวอย่างนี้ เราจะใช้ Advanced Filter Criteria Range เพื่อกรองตัวเลขและวันที่ เราจะดึงข้อมูลทั้งหมดที่ปริมาณการขายมากกว่า 10 . มาดูขั้นตอนกันเลย

• ประการแรก ใน ข้อมูล แท็บ เลือก ขั้นสูง คำสั่งจาก จัดเรียง &กรอง ตัวเลือก. กล่องโต้ตอบชื่อ ตัวกรองขั้นสูง จะปรากฏขึ้น

• ถัดไป เลือกทั้งตาราง (B4:E14) สำหรับ ช่วงรายการ .
• เลือกเซลล์ (C17:C18) เป็น ช่วงเกณฑ์ .
• กด ตกลง .

• สุดท้าย เราจะเห็นเฉพาะข้อมูลที่มีปริมาณมากกว่า 10 .

หมายเหตุ:
1. เลือกเกณฑ์ที่มีอย่างน้อยสองแถว

2. เราจะใช้ส่วนหัวสำหรับคอลัมน์ที่เกี่ยวข้องซึ่งจะใช้เกณฑ์การกรอง

2. กรองค่าข้อความด้วยเกณฑ์การกรองขั้นสูง

เราสามารถเปรียบเทียบค่าข้อความโดยใช้ตัวดำเนินการเชิงตรรกะนอกเหนือจากตัวเลขและวันที่ ในส่วนนี้ เราจะพูดถึงวิธีที่เราสามารถกรองค่าข้อความด้วยเกณฑ์การกรองขั้นสูงเพื่อให้ตรงกับข้อความ รวมทั้งมีอักขระเฉพาะที่จุดเริ่มต้น

2.1 สำหรับข้อความที่ตรงกันทุกประการ

ในวิธีนี้การกรอง จะส่งคืนค่าที่แน่นอนของข้อความที่ป้อนให้กับเรา สมมติว่าเรามีชุดข้อมูลการขายต่อไปนี้พร้อมกับคอลัมน์ใหม่เมือง . เราจะดึงเฉพาะข้อมูลของเมือง ‘นิวยอร์ก’ . เพียงทำตามขั้นตอนต่อไปนี้เพื่อดำเนินการนี้:

• ในตอนแรก ให้เลือกเซลล์ C18 . ใส่สูตรต่อไปนี้:

• กด Enter .

• ถัดไป เลือกช่วงเกณฑ์การกรองต่อไปนี้:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C17:C18

• กด ตกลง .

• สุดท้ายนี้ เราจะได้รับเฉพาะข้อมูลของเมือง ‘นิวยอร์ก’ .

2.1 มีอักขระเฉพาะที่จุดเริ่มต้น

ตอนนี้ เราจะกรองค่าข้อความสำหรับการเริ่มต้นด้วยอักขระเฉพาะ แทนที่จะเป็นค่าที่ตรงกันทั้งหมด ที่นี่ เราจะแยกเฉพาะค่าของเมืองที่ขึ้นต้นด้วยคำว่า ‘ใหม่’ . เรามาดูวิธีการทำกัน

• ขั้นแรก เลือกช่วงเกณฑ์ใน ตัวกรองขั้นสูง กล่อง:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C18:C19

• กด ตกลง .

• สุดท้ายนี้ เราจะได้ข้อมูลของทุกเมืองที่ขึ้นต้นด้วยคำว่า 'ใหม่' .

3. ใช้สัญลักษณ์แทนด้วยตัวเลือกตัวกรองขั้นสูง

การใช้ ตัวแทน ตัวละคร เป็นอีกวิธีหนึ่งในการใช้ ช่วงเกณฑ์ตัวกรองขั้นสูง . โดยปกติ อักขระตัวแทนใน excel มีสามประเภท:

? (เครื่องหมายคำถาม) – แสดงถึงอักขระตัวเดียวในข้อความ

* (ดอกจัน) – หมายถึงจำนวนอักขระใด ๆ

~ (ตัวหนอน) – แสดงถึงการมีอักขระตัวแทนในข้อความ

เราสามารถค้นหาสตริงข้อความเฉพาะในชุดข้อมูลของเราโดยใช้ ดอกจัน (*) . ในตัวอย่างนี้ เราพบชื่อของพนักงานขายที่ขึ้นต้นด้วยข้อความ 'J' . ในการทำเช่นนั้น เราต้องทำตามขั้นตอนเหล่านี้

• ขั้นแรก เปิด ตัวกรองขั้นสูง หน้าต่าง. เลือกช่วงเกณฑ์ต่อไปนี้:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C17:C18

• กด ตกลง .

• สุดท้ายนี้ เราจะได้เฉพาะชื่อพนักงานขายที่ขึ้นต้นด้วยข้อความ 'J' .

เนื้อหาที่เกี่ยวข้อง: ตัวกรองขั้นสูงของ Excel [หลายคอลัมน์และเกณฑ์ โดยใช้สูตรและสัญลักษณ์แทน]

4. ใช้สูตรที่มีช่วงเกณฑ์การกรองขั้นสูง

อีกวิธีหนึ่งในการใช้ช่วงเกณฑ์การกรองขั้นสูงคือการใช้สูตร ในตัวอย่างนี้ เราจะแยกยอดขายที่มากกว่า $350 . เพียงทำตามขั้นตอนด้านล่างนี้:

• ในตอนแรก ให้เลือกเซลล์ C19 . ใส่สูตรต่อไปนี้:

• กด ตกลง .

สูตรจะวนซ้ำมูลค่าของยอดขายไม่ว่าจะมากกว่า $350 หรือเปล่า

• ถัดไป เลือกช่วงเกณฑ์ต่อไปนี้ใน ตัวกรองขั้นสูง กล่องโต้ตอบ:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C17:C19

• กด ตกลง .

• ดังนั้น เราจะเห็นข้อมูลเฉพาะมูลค่าการขายที่มากกว่า $350 .

5. ตัวกรองขั้นสูงที่มี AND เกณฑ์ลอจิก

ตอนนี้เราจะแนะนำ และตรรกะ ในช่วงเกณฑ์การกรองขั้นสูง ตรรกะนี้ใช้สองเกณฑ์ ส่งกลับค่าผลลัพธ์เมื่อข้อมูลตรงตามเกณฑ์ทั้งสอง ที่นี่เรามีชุดข้อมูลต่อไปนี้ ในชุดข้อมูลนี้ เราจะกรองข้อมูลสำหรับเมือง นิวยอร์ก พร้อมทั้งมีมูลค่าการขาย >=200 . เรามาดูวิธีการทำกัน

• ขั้นแรก ไปที่ ตัวกรองขั้นสูง กล่องโต้ตอบ เลือกช่วงเกณฑ์ต่อไปนี้:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C18:C19

• กด ตกลง .

• สุดท้ายนี้ เราจะได้ชุดข้อมูลเฉพาะเมือง นิวยอร์ก มี ยอดขาย มูลค่ามากกว่า 250 .

6. การใช้ลอจิก OR กับช่วงเกณฑ์การกรองขั้นสูง

ชอบ และ ตรรกะ หรือตรรกะ ใช้สองเกณฑ์ด้วย และ ลอจิกส่งคืนเอาต์พุตหากตรงตามเกณฑ์ทั้งสองในขณะที่ OR ตรรกะจะส่งกลับหากเป็นไปตามเกณฑ์เพียงข้อเดียว ที่นี่เราจะข้อมูลสำหรับเมืองต่างๆ นิวยอร์ก และ เท็กซัส เท่านั้น. เพียงทำตามขั้นตอนด้านล่างเพื่อดำเนินการนี้:

• ในตอนแรก เปิด ตัวกรองขั้นสูง กล่องโต้ตอบ ป้อนช่วงเกณฑ์ต่อไปนี้:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C18:C20

• ตีตกลง

• สุดท้าย เราได้รับชุดข้อมูลสำหรับเมืองต่างๆ เท่านั้น นิวยอร์ก และ เท็กซัส .

7. การรวมกันของ AND &OR Logic เป็นช่วงเกณฑ์

บางครั้งเราอาจต้องกรองข้อมูลตามเกณฑ์หลายเกณฑ์ ในกรณีนั้น เราสามารถใช้ และ . ร่วมกันได้ &หรือ ตรรกะ. เราจะดึงข้อมูลจากชุดข้อมูลต่อไปนี้ตามเกณฑ์ที่กำหนด เพียงทำตามขั้นตอนต่อไปนี้เพื่อดำเนินการนี้:

• ขั้นแรก เปิด ตัวกรองขั้นสูง กล่องโต้ตอบ เลือกเกณฑ์ต่อไปนี้:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C18:C20

• จากนั้นกดตกลง

• ดังนั้น เราจะเห็นเฉพาะชุดข้อมูลที่ตรงกับเกณฑ์ของเราเท่านั้น

8. การใช้ช่วงเกณฑ์การกรองขั้นสูงเพื่อแยกคอลัมน์เฉพาะ

ในตัวอย่างนี้ เราจะกรองเฉพาะบางส่วนของชุดข้อมูล หลังจากกรองแล้ว เราจะย้ายส่วนที่กรองแล้วไปยังคอลัมน์อื่น เราจะใช้ชุดข้อมูลต่อไปนี้เพื่อดำเนินการตามขั้นตอนด้านล่าง

• อันดับแรก จากตัวกรองขั้นสูง กล่องโต้ตอบ เลือกเกณฑ์ต่อไปนี้:

ช่วงรายการ:B4:F14

ช่วงเกณฑ์:C18:C20

• เลือกคัดลอกไปยังตำแหน่งอื่น ตัวเลือก
• ป้อนข้อมูล คัดลอกไปที่ ช่วง H8:I10 .
• ตีตกลง

• ดังนั้นเราจึงได้รับข้อมูลที่กรองแล้วใน H8:I10 ตามเกณฑ์ของเรา

9. Copy Data to Another Worksheet after Filtering

In this example, we will also copy data in another worksheet whereas in the previous example we did it in the same worksheet. Do the following steps to execute it:

• First, go to ‘Another Worksheet-2’ where we will copy data after filtering.

We can see two columns ‘City’ and ‘Sales’ in ‘Another Worksheet-2’ .

• Next, open the ‘Advanced Filter’ กล่องโต้ตอบ

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

List Range:B4:F14

Criteria Range:C18:C19

• Now, select copy to another location ตัวเลือก

• After that, go to ‘Another Worksheet-2’ . Select Copy to Range B2:C4 .
• กด ตกลง .

• 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 หน้าต่าง. 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 .
• กด ตกลง .

• 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

• กด ตกลง .

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

🔎 How Does the Formula Work?

• 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:

• กด Enter .

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

List Range:B4:F14

Criteria Range:C18:C19

• กด ตกลง .

• So, we can see only the values of the weekend in the Date คอลัมน์

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 dialogue box. Input the following criteria range:

List Range:B4:F14

Criteria Range:C18:C19

• กด ตกลง .

• 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

• กด ตกลง .

• 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 dialogue box. 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 คอลัมน์. 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 dialogue box. Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Hit OK .

• Finally, we get the top five records of the Sales คอลัมน์

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

• After that, press OK .

• Lastly, we can see the bottom five values of the Sales คอลัมน์

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 ตัวเลือก. Insert the following criteria range:

List Range:B4:F14

Criteria Range:C17:C18

• Hit 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.

บทสรุป

In this article, we have tried to cover all the methods of the Advanced Filter Criteria Range ตัวเลือก. 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.

บทความที่เกี่ยวข้อง

• 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)