ใน Excel บางครั้งคุณอาจต้องสร้างรายการตามเกณฑ์ วันนี้เราจะแสดงวิธีสร้างรายการตามเกณฑ์ สำหรับเซสชันนี้ เราใช้ Excel 365 แม้แนะนำให้ใช้เวอร์ชันนี้ แต่โปรดใช้เวอร์ชันที่คุณมี
ก่อนอื่น มารู้จักชุดข้อมูลที่เป็นพื้นฐานของตัวอย่าง
ที่นี่เรามีชุดข้อมูลคนหลายคนจากสถานที่ต่างๆ พร้อมยานพาหนะของพวกเขา ใช้ข้อมูลนี้ เราจะสร้างรายการตามเกณฑ์
โปรดสังเกตว่าเป็นตารางพื้นฐานพร้อมข้อมูลจำลอง เพื่อความเข้าใจง่าย ในสถานการณ์จริง อาจพบชุดข้อมูลใหญ่และซับซ้อนกว่านี้มาก
สมุดงานฝึกฝน
คุณสามารถดาวน์โหลดสมุดงานฝึกฝนจากลิงก์ต่อไปนี้
สร้างรายการตามเกณฑ์
ตัวอย่าง เราจะสร้างรายการคนตามภูมิภาคของพวกเขา
เนื่องจากเป็นชุดข้อมูลเล็ก เรารู้ว่ามี 4 ภูมิภาค เราเก็บชื่อภูมิภาคไว้ และจะหารายการตามภูมิภาค
1. ใช้การรวม INDEX-SMALL สร้างรายการ
ที่นี่เราต้องการรายการ ดังนั้นสูตรควรเป็นแบบที่ดึงค่าหลายค่าจากตาราง สำหรับงานนั้น เราสามารถใช้การรวมของฟังก์ชัน INDEX และ SMALL
เพื่อรู้จักฟังก์ชันเหล่านี้ ดูบทความ: INDEX, SMALL
พร้อมกับสองฟังก์ชันนี้ เราต้องการฟังก์ชันช่วยเหลืออีก 몇 ตัว คือ IF, ROW และ IFERROR ดูบทความเพิ่มเติม: IF, ROW, IFERROR
มาสำรวจสูตร
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
ที่นี่ ทุกฟังก์ชันมีวัตถุประสงค์ ฟังก์ชัน INDEX คืนค่าจากอาร์เรย์ B2:B12 (คอลัมน์ Name) และส่วน SMALL ใหญ่ๆ ให้หมายเลขแถวที่จะดึง
IF ภายใน SMALL ตรวจสอบว่าเกณฑ์ตรงหรือไม่ และฟังก์ชัน ROW ทำซ้ำผ่านเซลล์ของคอลัมน์
จากนั้น ROW ตัวนอก บ่งชี้ค่า k-th สำหรับฟังก์ชัน SMALL ร่วมกัน ฟังก์ชันเหล่านี้คืนหมายเลขแถว และ INDEX คืนผลลัพธ์
IFERROR เพื่อจัดการข้อผิดพลาดที่อาจเกิดจากสูตร
ลากลง คุณจะได้คนทั้งหมดจากภูมิภาคที่กำหนด
เช่นเดียวกัน เขียนสูตรสำหรับภูมิภาคอื่นๆ (สูตรเหมือนกัน แค่เลื่อนเซลล์)
สูตร INDEX-SMALL แบบทางเลือก
เราสามารถเขียนสูตรในรูปแบบทางเลือกได้ ฟังก์ชันที่ใช้จะเหมือนเดิม แค่การนำเสนอที่แตกต่าง
มาดูสูตร
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")อีกครั้ง คุณต้องกด CTRL + SHIFT + ENTER เพื่อรันสูตร
มีความแตกต่างเล็กน้อยระหว่างสองสูตรนี้ คุณแยกแยะได้ไหม?
ใช่ ในสูตรก่อนหน้า เราลบ 1 ที่ท้ายสุดของส่วน SMALL แต่ที่นี่ เราลบ 1 ภายในส่วน IF
วัตถุประสงค์ของการลบ 1 คือการนำทางไปยังหมายเลขแถวที่ถูกต้อง ก่อนหน้านี้เราทำที่ท้ายสุด ที่นี่ทำตั้งแต่ต้นและดำเนินการต่อ
เขียนสูตรสำหรับเกณฑ์อื่นๆ เพื่อทำรายการให้สมบูรณ์
อ่านเพิ่มเติม: วิธีสร้างรายการในเซลล์ใน Excel (3 วิธีเร็ว)
2. ใช้ฟังก์ชัน AGGREGATE สร้างรายการ
Excel มีฟังก์ชันชื่อ AGGREGATE ที่คุณสามารถใช้ทำภารกิจต่างๆ ที่นี่เราสามารถใช้ฟังก์ชันนี้สร้างรายการตามเกณฑ์
ฟังก์ชัน AGGREGATE คืนการคำนวณรวม เช่น AVERAGE, COUNT, MAX เป็นต้น
ไวยากรณ์สำหรับฟังก์ชัน AGGREGATE มีดังนี้:
AGGREGATE(function_number,behavior_options, range)function_number: หมายเลขนี้ระบุว่าควรคำนวณอะไร
behavior_options: ตั้งค่าด้วยตัวเลข หมายเลขนี้แสดงว่าฟังก์ชันจะทำงานอย่างไร
range: ช่วงที่ต้องการรวม
ฟังก์ชัน AGGREGATE ทำหลายงาน ดังนั้นฟังก์ชันจำนวนมากถูกกำหนดไว้ภายใน เราแสดงหมายเลขฟังก์ชันที่ใช้บ่อย
| Function | Function_number |
|---|---|
| AVERAGE | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| SMALL | 15 |
เพื่อรู้เพิ่มเติม เยี่ยมชมไซต์ Microsoft Support
มาดูสูตร
=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")
ที่นี่ พร้อมกับฟังก์ชัน AGGREGATE เราใช้ INDEX INDEX เก็บอาร์เรย์ที่คืนค่าตามการจับคู่ที่พบในส่วนท้ายของสูตร
คุณเห็น เราใช้ 15 เป็น function_number ใน AGGREGATE จากตารางข้างต้น คุณเห็น 15 เรียกใช้การดำเนินการของฟังก์ชัน SMALL ตอนนี้คุณเชื่อมโยงได้ไหม?
ใช่ เราได้รันสูตร INDEX-SMALL ในรูปแบบของฟังก์ชัน AGGREGATE
6 สำหรับตัวเลือกพฤติกรรม ซึ่งหมายถึง ignore error values
เขียนสูตรสำหรับค่าที่เหลือ
การอ่านที่คล้ายกัน
- วิธีสร้าง To Do List ใน Excel (3 วิธีง่าย)
- Creating a Mailing List in Excel (2 Methods)
- วิธีสร้างรายการหมายเลขใน Excel (8 วิธี)
3. สร้างรายการเฉพาะเจาะจงด้วย INDEX-MATCH-COUNTIF
เราสามารถสร้างรายการเฉพาะเจาะจงตามเกณฑ์ได้ สำหรับนั้น เราสามารถใช้การรวมของ INDEX, MATCH, และ COUNTIF
COUNTIF นับเซลล์ในช่วงที่ตรงตามเงื่อนไขเดียว และ MATCH ค้นหาตำแหน่งของค่าค้นหาในช่วง เพื่อรู้เพิ่มเติม อ่านบทความ: MATCH, COUNTIF
มาสำรวจสูตร
=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"") 
ในสูตรนี้: B2:B12 คือช่วงคอลัมน์ที่มีค่าที่ไม่ซ้ำซ้ำกันที่คุณต้องการดึง C2:C12 คือคอลัมน์ที่มีเกณฑ์ที่คุณขึ้นอยู่กับ G2 บ่งชี้เกณฑ์
ภายในฟังก์ชัน MATCH เราให้ 0 เป็น lookup_array และสำหรับ lookup_range เราใช้ส่วน IF ที่มี COUNTIF ดังนั้น ส่วนนี้คืนค่าตราบใดที่พบ 0 ค่าที่นี่ทำหน้าที่เป็นหมายเลขแถวสำหรับ INDEX
ลากลง และคุณจะพบค่าที่ไม่ซ้ำทั้งหมด
อย่าลืมใช้ CTRL+SHIFT + ENTER เพื่อรันสูตร
นี่เป็นการกล่าวถึงอย่างนับถือของวิธีการสร้างรายการเฉพาะเจาะจง ตามบทความนี้ เพื่อรู้เกี่ยวกับการสร้าง unique list based on criteria
4. ใช้ฟังก์ชัน FILTER สร้างรายการตามเกณฑ์
หากคุณใช้ Excel 365 คุณสามารถทำภารกิจนี้ด้วยฟังก์ชันสร้างในตัวเดียวชื่อ FILTER
ฟังก์ชัน FILTER กรองช่วงข้อมูลตามเกณฑ์ที่กำหนดและดึงระเบียนที่ตรงกัน เพื่อรู้จักฟังก์ชัน อ่านบทความ: FILTER
ตอนนี้ สูตรของเราจะเป็นดังนี้
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
B2:B12 คืออาร์เรย์ที่จะกรอง จากนั้นเราให้เงื่อนไข ตามที่เราจะสร้างรายการ
ที่นี่ คุณไม่ต้องลากสูตรลง ที่เดียวจบ จะให้ค่าทั้งหมดและเติมรายการ
อ่านเพิ่มเติม: วิธีสร้างรายการตามตัวอักษรใน Excel (3 วิธี)
สรุป
จบบทความวันนี้ เราแสดงหลายวิธีสร้างรายการตามเกณฑ์ หวังว่าจะเป็นประโยชน์ โปรดคอมเมนต์หากมีอะไรยากที่จะเข้าใจ แจ้งให้เรารู้วิธีอื่นที่เราพลาดไป
การอ่านเพิ่มเติม
- วิธีสร้างรายการจุดใน Excel (9 วิธี)
- Make a Comma Separated List in Excel (5 Methods)
- วิธีสร้างราคารายการใน Excel (แนวทางทีละขั้นตอน)
