ใน Excel บางครั้งคุณอาจต้องสร้างรายการตามเกณฑ์ วันนี้เราจะแสดงวิธีสร้างรายการตามเกณฑ์ สำหรับเซสชันนี้ เราใช้ Excel 365 แม้ว่าจะแนะนำให้ใช้เวอร์ชันนี้ แต่โปรดใช้เวอร์ชันของคุณตามสะดวก
ก่อนอื่น เรามาทำความรู้จักกับชุดข้อมูลที่เป็นฐานของตัวอย่างกันก่อน
ที่นี่ เรามีชุดข้อมูลของบุคคลหลายคนจากสถานที่ต่างๆ พร้อมกับยานพาหนะของพวกเขา โดยใช้ข้อมูลนี้ เราจะสร้างรายการตามเกณฑ์
โปรดทราบว่านี่เป็นตารางพื้นฐานที่มีข้อมูลจำลองเพื่อให้ทุกอย่างง่ายขึ้น ในสถานการณ์จริง คุณอาจพบชุดข้อมูลขนาดใหญ่และซับซ้อนกว่ามาก
สมุดแบบฝึกหัด
คุณสามารถดาวน์โหลดสมุดแบบฝึกหัดได้จากลิงค์ต่อไปนี้
สร้างรายการตามเกณฑ์
ตัวอย่างเช่น เราจะสร้างรายชื่อผู้คนตามภูมิภาคของพวกเขา
เนื่องจากเป็นชุดข้อมูลขนาดเล็ก เราจึงทราบว่ามี 4 ภูมิภาค เราเก็บชื่อภูมิภาคไว้และจะค้นหารายชื่อตามภูมิภาค
1. การใช้ชุดค่าผสม INDEX-SMALL เพื่อสร้างรายการ
ที่นี่เราต้องการรายการ ดังนั้นสูตรของเราจึงควรเป็นสูตรที่จะดึงค่าหลายค่าจากตาราง สำหรับงานนั้น เราสามารถใช้ INDEX . ร่วมกันได้ และ เล็ก ฟังก์ชัน
หากต้องการทราบฟังก์ชันเหล่านี้ ให้ตรวจสอบบทความเหล่านี้: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),"")
ที่นี่ทุกฟังก์ชั่นมีจุดประสงค์ ดัชนี ฟังก์ชั่นคืนค่าจากอาร์เรย์ B2:B12 (คอลัมน์ชื่อ) และ เล็กใหญ่ ส่วนระบุหมายเลขแถวที่จะดึงข้อมูล
ถ้า ภายใน เล็ก ตรวจสอบว่าเกณฑ์ตรงกันหรือไม่และ ROW ฟังก์ชันจะวนซ้ำในเซลล์ของคอลัมน์
จากนั้น แถว . ด้านนอก หมายถึงค่าที่ k สำหรับ เล็ก การทำงาน. ฟังก์ชันเหล่านี้ร่วมกันส่งคืนหมายเลขแถวและ 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 ที่ส่วนท้ายสุดของ เล็ก ส่วน แต่ที่นี่เราได้ลบ 1 ภายใน IF ส่วน.
จุดประสงค์ของการลบ 1 คือการแชนเนลไปยังหมายเลขแถวที่เหมาะสม ก่อนหน้านี้เราได้ทำอย่างนั้นในที่สุด ที่นี่ทำก่อนหน้านี้และดำเนินการต่อไป
เขียนสูตรเกณฑ์อื่นๆ ให้ครบถ้วน
อ่านเพิ่มเติม:วิธีสร้างรายการภายในเซลล์ใน Excel (วิธีด่วน 3 วิธี)
2. การใช้ฟังก์ชัน AGGREGATE เพื่อสร้างรายการ
Excel มีฟังก์ชันที่เรียกว่า AGGREGATE ที่คุณสามารถใช้ในการทำงานต่างๆ ที่นี่เราสามารถใช้ฟังก์ชันเพื่อสร้างรายการตามเกณฑ์ได้
รวม ฟังก์ชันส่งคืนการคำนวณรวม เช่น AVERAGE, COUNT, MAX เป็นต้น
ไวยากรณ์สำหรับ AGGREGATE ฟังก์ชันมีดังนี้:
AGGREGATE(function_number,behavior_options, range) function_number: ตัวเลขนี้เป็นตัวกำหนดว่าควรทำการคำนวณใด
พฤติกรรม_ตัวเลือก: ตั้งค่านี้โดยใช้ตัวเลข ตัวเลขนี้แสดงถึงลักษณะการทำงานของฟังก์ชัน
ช่วง: ช่วงที่คุณต้องการรวม
รวม ฟังก์ชั่นทำงานหลายอย่าง ดังนั้นจำนวนของฟังก์ชั่นจึงถูกกำหนดไว้ล่วงหน้า เรากำลังแสดงรายการหมายเลขฟังก์ชันที่ใช้บ่อยบางรายการ
| ฟังก์ชัน | Function_number |
|---|---|
| ค่าเฉลี่ย | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| เล็ก | 15 |
หากต้องการทราบข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน โปรดไปที่ฝ่ายสนับสนุนของ Microsoft เว็บไซต์
มาดูสูตรกัน
=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),"")
ที่นี่พร้อมกับ รวม ฟังก์ชัน เราได้ใช้ INDEX . ดัชนี เก็บอาร์เรย์ที่คืนค่าตามการจับคู่ที่พบในส่วนหลังของสูตร
คุณจะเห็นว่าเราได้ใช้ 15 เป็น function_number ใน รวม . จากตารางด้านบน คุณจะเห็น 15 เรียกร้องให้ เล็ก การทำงานของฟังก์ชัน ตอนนี้คุณเกี่ยวข้องไหม
ใช่ เราได้ดำเนินการ INDEX-SMALL สูตรในลักษณะของ AGGREGATE ฟังก์ชัน
6 สำหรับตัวเลือกพฤติกรรม ซึ่งหมายถึง ละเว้นค่าความผิดพลาด .
เขียนสูตรสำหรับค่าที่เหลือ
การอ่านที่คล้ายกัน
- วิธีการสร้างรายการสิ่งที่ต้องทำใน Excel (วิธีง่ายๆ 3 วิธี)
- การสร้างรายชื่อผู้รับจดหมายใน Excel (2 วิธี)
- วิธีการสร้างรายการลำดับเลขใน Excel (8 วิธี)
3. สร้างรายการที่ไม่ซ้ำโดยใช้ INDEX-MATCH-COUNTIF
เราสามารถสร้างรายการเฉพาะตามเกณฑ์ได้ สำหรับสิ่งนั้น เราสามารถใช้ INDEX . ร่วมกันได้ , MATCH และ COUNTIF .
COUNTIF นับเซลล์ในช่วงที่ตรงตามเงื่อนไขเดียว และ 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 เพื่อดำเนินการตามสูตร
นี่เป็นการกล่าวถึงแนวทางการสร้างรายการที่ไม่เหมือนใครอย่างมีเกียรติ ติดตามบทความนี้เพื่อทราบเกี่ยวกับการสร้างรายการที่ไม่ซ้ำตามเกณฑ์ .
4. การใช้ฟังก์ชัน FILTER เพื่อสร้างรายการตามเกณฑ์
หากคุณกำลังใช้ Excel 365 คุณสามารถทำงานด้วยฟังก์ชันในตัวเดียวที่เรียกว่า ตัวกรอง .
ตัวกรอง ฟังก์ชั่นกรองช่วงของข้อมูลตามเกณฑ์ที่กำหนดและแยกบันทึกที่ตรงกัน หากต้องการทราบเกี่ยวกับฟังก์ชัน โปรดไปที่บทความนี้:ตัวกรอง .
ตอนนี้สูตรของเราจะเป็นดังนี้
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
B2:B12 คืออาร์เรย์ที่จะกรอง จากนั้นเราได้จัดเตรียมเงื่อนไขตามสิ่งที่เราจะสร้างรายการ
ที่นี่คุณไม่จำเป็นต้องลากสูตรลงไป ในครั้งเดียว วิธีนี้จะให้ค่าทั้งหมดและเติมเต็มรายการ
อ่านเพิ่มเติม: วิธีการสร้างรายการตามตัวอักษรใน Excel (3 วิธี)
บทสรุป
นั่นคือทั้งหมดสำหรับวันนี้ เราได้ระบุวิธีสร้างรายการตามเกณฑ์หลายวิธี หวังว่าคุณจะพบว่าสิ่งนี้มีประโยชน์ โปรดแสดงความคิดเห็นหากมีสิ่งใดที่เข้าใจยาก แจ้งให้เราทราบวิธีการอื่นๆ ที่เราพลาดไปที่นี่
อ่านเพิ่มเติม
- วิธีการสร้างรายการสัญลักษณ์แสดงหัวข้อย่อยใน Excel (9 วิธี)
- สร้างรายการคั่นด้วยจุลภาคใน Excel (5 วิธี)
- วิธีการสร้างรายการราคาใน Excel (คำแนะนำทีละขั้นตอน)
