ค่าสูงสุดและต่ำสุดด้วย LARGE และ SMALL

MAX ให้ค่าที่มากที่สุดเพียงค่าเดียว และ MIN ให้ค่าที่น้อยที่สุดเพียงค่าเดียว แต่หากคุณต้องการค่าที่สูงเป็นอันดับ 2 หรือค่าที่ต่ำเป็นอันดับ 3จะทำอย่างไร

นั่นคือหน้าที่ของ LARGE และ SMALL ฟังก์ชันเหล่านี้ช่วยดึงค่าที่มากเป็นอันดับที่ n หรือค่าที่น้อยเป็นอันดับที่ n จากช่วง ซึ่งเป็นพื้นฐานของรายงานค่า N อันดับสูงสุดและ N อันดับต่ำสุด

LARGE(range, k) ส่งคืนค่าที่มากเป็นอันดับที่ k อาร์กิวเมนต์ที่สอง k ใช้เลือกตำแหน่ง โดย 1 คือค่าที่มากที่สุด 2 คือค่าที่มากเป็นอันดับสอง และต่อไปเรื่อย ๆ

ดังนั้น LARGE(A2:A20,1) จึงเท่ากับ MAX(A2:A20) ขณะที่ LARGE(A2:A20,2) จะให้ค่าที่ได้อันดับสอง

SMALL(range, k) ทำงานเหมือน LARGE แต่เริ่มจัดอันดับจากด้านค่าต่ำ SMALL(A2:A20,1) เท่ากับ MIN(A2:A20) และ SMALL(A2:A20,3) จะส่งคืนค่าที่น้อยเป็นอันดับสาม

ใช้ SMALL เพื่อแสดงผู้ที่มีผลงานแย่ที่สุด ตัวเลือกที่ราคาถูกที่สุด หรือวันที่เร็วที่สุดในชุดข้อมูล

หากต้องการแสดงค่าที่สูงที่สุดสามค่า ให้เรียงสูตร LARGE สามสูตรโดยใช้ k = 1, 2, 3 วิธีที่เป็นระเบียบกว่าคืออ้างอิงตัวเลขช่วย เพื่อให้สามารถเติมสูตรลงด้านล่างได้

หากเซลล์ D2 มีค่า 1 เซลล์ D3 มีค่า 2 และเซลล์ D4 มีค่า 3 สูตรเดียวนี้เมื่อนำไปเติมลงด้านล่างจะสร้างรายการสามอันดับแรกได้ครบถ้วน

คุณสามารถสร้างค่า k โดยอัตโนมัติด้วยฟังก์ชัน ROW จึงไม่จำเป็นต้องมีคอลัมน์ช่วย ROW()-1 จะให้ค่า 1 ในแถวแรก ค่า 2 ในแถวถัดไป และต่อไปเรื่อย ๆ

เมื่อคัดลอกสูตรลงด้านล่าง สูตรนี้จะสร้างรายการจัดอันดับโดยไม่ต้องตั้งค่าเพิ่มเติม ปรับค่าออฟเซ็ตให้ตรงกับจุดเริ่มต้นของรายการ

ใน Excel และ Google Sheets รุ่นใหม่ คุณสามารถกระจายผลลัพธ์ 5 อันดับแรกทั้งหมดด้วยสูตรเดียว โดยส่ง SEQUENCE เป็นอาร์กิวเมนต์ k SEQUENCE(5) จะสร้างค่า 1, 2, 3, 4, 5

จากนั้น LARGE จะส่งคืนค่าทั้งห้าค่าพร้อมกันและกระจายผลลัพธ์ลงด้านล่างโดยอัตโนมัติ สูตรเดียวก็ได้รายการจัดอันดับครบถ้วน

คำถามทางธุรกิจที่พบบ่อยคือ ลูกค้า 3 อันดับแรกของเราสร้างยอดรวมเท่าใด ให้ครอบ LARGE ด้วย SUM และใช้ SEQUENCE (หรือค่าคงที่อาร์เรย์) เพื่อรวมค่าอันดับต้น ๆ

สูตรด้านล่างจะบวกค่าที่มากที่สุดสามค่าในช่วง ทำให้ได้ส่วน योगदानรวมของ 3 อันดับแรกในเซลล์เดียว

หาก k น้อยกว่า 1 หรือมากกว่าจำนวนตัวเลขในช่วง LARGE และ SMALL จะส่งคืนข้อผิดพลาด #NUM!

ตัวอย่างเช่น การขอค่าที่มากเป็นอันดับที่ 30 จากรายการที่มีตัวเลขเพียง 19 ค่า จะล้มเหลว ในรายงาน ให้ป้องกันกรณีนี้ด้วยการครอบการเรียกใช้ฟังก์ชันด้วย IFERROR

การรู้เพียงค่าอย่างเดียวย่อมมีประโยชน์น้อยกว่าการรู้ว่า ใคร เป็นผู้ทำผลงานนั้นได้ รวม LARGE เข้ากับ INDEX และ MATCH เพื่อดึงชื่อที่อยู่คู่กับค่าสูงสุด

วิธีนี้จะค้นหายอดขายที่สูงที่สุด ระบุแถวของยอดขายนั้น แล้วส่งคืนชื่อที่ตรงกันจากคอลัมน์ A ทำให้รายงานแสดงข้อความว่า "ผู้ขายอันดับหนึ่ง: Maria."

เช่นเดียวกับฟังก์ชันทางสถิติอื่น ๆ LARGE และ SMALL จะนับเฉพาะ ตัวเลข โดยจะข้ามเซลล์ที่เป็นข้อความและเซลล์ว่างในช่วง ดังนั้นเซลล์เหล่านั้นจึงไม่ส่งผลต่อการพิจารณาว่าค่าใดมากเป็นอันดับที่ n

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

คุณมียอดขายรายเดือนใน B2:B20 ฝ่ายบริหารต้องการทราบสามเดือนที่ดีที่สุดและแย่ที่สุด

• สามเดือนที่ดีที่สุด: =LARGE($B$2:$B$20,{1,2,3})
• สามเดือนที่แย่ที่สุด: =SMALL($B$2:$B$20,{1,2,3})

วางแต่ละสูตรในเซลล์ที่สามารถกระจายผลลัพธ์ได้ แล้วคุณจะมีภาพรวมของค่าบนสุดและล่างสุดเพื่อใช้ขับเคลื่อนแดชบอร์ดได้ทันที

ทดสอบความเข้าใจของคุณเกี่ยวกับ LARGE และ SMALL

ตอนนี้คุณสามารถใช้ฟังก์ชันที่ละเอียดกว่า MAX และ MIN ได้แล้ว:

• LARGE(range,k) ส่งคืนค่าที่มากเป็นอันดับที่ k ส่วน SMALL(range,k) ส่งคืนค่าที่น้อยเป็นอันดับที่ k
• k = 1 จะให้ผลเช่นเดียวกับ MAX / MIN หากเพิ่มค่า k ก็จะได้ค่าที่อยู่ในอันดับถัดไป
• ส่ง SEQUENCE หรืออาร์เรย์อย่าง {1,2,3} เพื่อสร้างหรือหาผลรวมของรายการ N อันดับแรกด้วยสูตรเดียว
• ใช้ร่วมกับ INDEX-MATCH เพื่อระบุชื่อหรือป้ายกำกับให้ค่า และครอบด้วย IFERROR เพื่อจัดการกรณีที่ k อยู่นอกช่วง