ค้นหาจำนวนค่าลบหรือค่าบวกที่ใหญ่ที่สุดใน Excel

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

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

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

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

MAX IF แจกแจงสูตรอาร์เรย์

สูตรที่ใช้ในบทช่วยสอนนี้เพื่อค้นหาจำนวนบวกที่ใหญ่ที่สุดคือ:

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

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

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

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

สูตร CSE

สูตรอาร์เรย์ถูกสร้างขึ้นโดยการกดปุ่ม Ctrl , กะ และ ป้อน บนแป้นพิมพ์พร้อมกันเมื่อพิมพ์สูตรแล้ว

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

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

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

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

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

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

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

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

การป้อน MAX IF สูตรที่ซ้อนกัน

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

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

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

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

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

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

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

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

การหาจำนวนลบที่ใหญ่ที่สุด

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

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

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

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

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

รับ #VALUE! สำหรับคำตอบ

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

ในการแก้ไขปัญหานี้ ให้คลิกที่สูตรในแถบสูตรแล้วกดปุ่ม Ctrl , กะ และ ป้อน บนแป้นพิมพ์อีกครั้ง