หาตัวเลขมากที่สุดที่เป็นลบหรือบวกใน Excel

บางครั้ง แทนที่จะหาตัวเลขมากที่สุด (maximum) ของข้อมูลทั้งหมด คุณต้องการหาตัวเลขมากที่สุดในส่วนย่อย — เช่น ตัวเลขบวกมากที่สุด หรือตัวเลขลบมากที่สุด

หากปริมาณข้อมูลน้อย งานอาจทำได้ง่ายด้วยการเลือกช่วงที่ถูกต้องสำหรับฟังก์ชัน MAX ด้วยตนเอง

ในสถานการณ์อื่น เช่น ตัวอย่างข้อมูลขนาดใหญ่ที่ไม่ได้จัดเรียง การเลือกช่วงที่ถูกต้องอาจยากหรือเป็นไปไม่ได้

โดยการรวมฟังก์ชัน IF กับ MAX ในสูตรอาร์เรย์ (array formula) เงื่อนไข — เช่น ตัวเลขบวกหรือลบอย่างเดียว — สามารถตั้งค่าได้ง่าย เพื่อให้เฉพาะข้อมูลที่ตรงตามเงื่อนไขเท่านั้นที่ถูกทดสอบโดยสูตร

การแยกส่วนสูตรอาร์เรย์ MAX IF

สูตรที่ใช้ในบทช่วยสอนนี้เพื่อหาตัวเลขบวกมากที่สุดคือ:

=MAX( IF( A1:B5>0, A1:B5 ))

อาร์กิวเมนต์ value_if_false ของฟังก์ชัน IF ซึ่งเป็นออปชัน ถูกละเว้นเพื่อทำให้สูตรสั้นลง ในกรณีที่ข้อมูลในช่วงที่เลือกไม่ตรงตามเกณฑ์ที่ตั้ง — ตัวเลขมากกว่าศูนย์ — สูตรจะคืนค่าศูนย์ (0)

หน้าที่ของแต่ละส่วนของสูตรคือ:

• ฟังก์ชัน IF กรองข้อมูล เพื่อให้เฉพาะตัวเลขที่ตรงตามเกณฑ์ที่เลือกเท่านั้นที่ส่งต่อไปยังฟังก์ชัน MAX
• ฟังก์ชัน MAX หาค่าสูงสุดของข้อมูลที่ถูกกรองแล้ว
• สูตรอาร์เรย์ — ซึ่งบ่งชี้ด้วยเครื่องหมายปีกกา { } ล้อมรอบสูตร — อนุญาตให้อาร์กิวเมนต์ logical_test ของฟังก์ชัน IF ค้นหาทั้งช่วงข้อมูลเพื่อหาค่าที่ตรง — เช่น ตัวเลขมากกว่าศูนย์ — แทนที่จะเป็นเซลล์เดียว

สูตร CSE

สูตรอาร์เรย์ถูกสร้างโดยการกดปุ่ม Ctrl, Shift และ Enter บนคีย์บอร์ดพร้อมกันครั้งเดียว หลังจากพิมพ์สูตรแล้ว

ผลคือ สูตรทั้งหมด — รวมถึงเครื่องหมายเท่ากับ — จะถูกล้อมรอบด้วยเครื่องหมายปีกกา ตัวอย่าง:

{=MAX( IF( A1:B5>0, A1:B5 ) )}

เนื่องจากปุ่มที่กดเพื่อสร้างสูตรอาร์เรย์ จึงมักเรียกว่า CSE formulas

ตัวอย่างสูตรอาร์เรย์ MAX IF แบบ Nested ของ Excel

ตามภาพด้านบน บทช่วยสอนนี้ใช้สูตรอาร์เรย์ MAX IF เพื่อหาค่าบวกมากที่สุดและค่าลบมากที่สุดในช่วงตัวเลข

ขั้นตอนด้านล่างสร้างสูตรหาตัวเลขบวกมากที่สุดก่อน ตามด้วยขั้นตอนหาตัวเลขลบมากที่สุด

การป้อนข้อมูลสำหรับบทช่วยสอน

1. ป้อนตัวเลขตามภาพด้านบนลงในเซลล์ A1 ถึง B5 ของเวิร์กชีต
2. ในเซลล์ A6 และ A7 พิมพ์ป้ายกำกับ Max Positive และ Max Negative

การป้อนสูตร MAX IF แบบ Nested

เนื่องจากเรากำลังสร้างทั้งสูตร Nested และสูตรอาร์เรย์ เราจะต้องพิมพ์สูตรทั้งหมดลงในเซลล์เวิร์กชีตเดียว

หลังจากป้อนสูตรแล้ว อย่า กดปุ่ม Enter บนคีย์บอร์ด หรือคลิกเซลล์อื่นด้วยเมาส์ เนื่องจากเราต้องแปลงสูตรเป็นสูตรอาร์เรย์

• คลิกที่เซลล์ B6 — ตำแหน่งที่จะแสดงผลลัพธ์สูตรแรก
• พิมพ์ดังนี้:

=MAX( IF ( A1:B5>0, A1:B5 ) )

การสร้างสูตรอาร์เรย์

1. กดค้าง Ctrl และ Shift บนคีย์บอร์ด
2. กด Enter บนคีย์บอร์ด เพื่อสร้างสูตรอาร์เรย์
3. คำตอบ 45 ควรปรากฏในเซลล์ B6 เนื่องจากนี่คือตัวเลขบวกมากที่สุดในรายการ

หากคุณคลิกที่เซลล์ B6 สูตรอาร์เรย์สมบูรณ์จะเห็นได้ใน Formula Bar เหนือเวิร์กชีต

{ = MAX( IF( A1:B5>0, A1:B5 ) ) }

การหาตัวเลขลบมากที่สุด

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

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

• คลิกที่เซลล์ B7
• พิมพ์ดังนี้:

=MAX( IF ( A1:B5<0, A1:B5 ) )

• ทำตามขั้นตอนด้านบนเพื่อสร้างสูตรอาร์เรย์
• คำตอบ -8 ควรปรากฏในเซลล์ B7 เนื่องจากนี่คือตัวเลขลบมากที่สุดในรายการ

กรณีได้ #VALUE! เป็นคำตอบ

หากเซลล์ B6 และ B7 แสดงค่าข้อผิดพลาด #VALUE! แทนคำตอบที่ระบุไว้ข้างต้น อาจเป็นเพราะสูตรอาร์เรย์ไม่ได้ถูกสร้างอย่างถูกต้อง

เพื่อแก้ไขปัญหานี้ ให้คลิกที่สูตรใน Formula Bar และกด Ctrl, Shift และ Enter บนคีย์บอร์ดอีกครั้ง