AGGREGATE

TLDR สรุปสั้นๆ

AGGREGATE ใช้คำนวณสรุปค่า แบบไม่สนใจ Error หรือแถวที่ซ่อนอยู่ได้ มีฟังก์ชันหลากหลายให้เลือก

คำอธิบาย

ฟังก์ชัน AGGREGATE ใน Excel เป็นเครื่องมืออันทรงพลังที่จะช่วยคุณคำนวณหลากหลายด้วยการเลือก Option ได้ว่าจะไม่สนใจแถวที่ซ่อนอยู่หรือค่า Error ได้ด้วย

มีครั้งแรกในเวอร์ชันไหน

2010

รูปแบบคำสั่ง (Syntax)

AGGREGATE(function_num, options, ref1, [ref2], …)

Arguments

• function_num (Required – Number)
  ตัวเลขระหว่าง 1 ถึง 19 ที่บอกว่าต้องการใช้ฟังก์ชันการคำนวณแบบใด เช่น AVERAGE, COUNT, SUM เป็นต้น
  • 1 – AVERAGE
  • 2 – COUNT
  • 3 – COUNTA
  • 4 – MAX
  • 5 – MIN
  • 6 – PRODUCT
  • 7 – STDEV.S
  • 8 – STDEV.P
  • 9 – SUM
  • 10 – VAR.S
  • 11 – VAR.P
  • 12 – MEDIAN
  • 13 – MODE.SNGL
  • 14 – LARGE
  • 15 – SMALL
  • 16 – PERCENTILE.INC
  • 17 – QUARTILE.INC
  • 18 – PERCENTILE.EXC
  • 19 – QUARTILE.EXC
• options (Required – Number)
  ตัวเลขที่กำหนดว่าควรมองข้าม หรือไม่สนใจค่าแบบใดบ้างในการคำนวณ
  • 0 หรือเว้นว่างไว้ – ไม่สนใจค่าจากฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  • 1 – ไม่สนใจแถวที่ถูกซ่อน, ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  • 2 – ไม่สนใจค่าที่เป็นข้อผิดพลาด, ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  • 3 – ไม่สนใจแถวที่ถูกซ่อน, ค่าที่เป็นข้อผิดพลาด, ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  • 4 – ไม่ละเว้นอะไรเลย (เอาตัวเลขมาคิดทุกค่า)
  • 5 – ไม่สนใจแถวที่ถูกซ่อน
  • 6 – ไม่สนใจค่าที่เป็นข้อผิดพลาด
  • 7 – ไม่สนใจแถวที่ถูกซ่อนและค่าที่เป็นข้อผิดพลาด
• ref1 (Required – Range)
  อาร์กิวเมนต์เลขตัวแรกที่ใช้สำหรับฟังก์ชันที่ต้องการหลายอาร์กิวเมนต์เพื่อหาค่าผลลัพท์
• ref2 (Optional – Range)
  อาร์กิวเมนต์ตัวที่สองซึ่งจะเป็นอาร์กิวเมนต์ที่จำเป็นสำหรับบางฟังก์ชัน เช่น LARGE หรือ QUARTILE

ตัวอย่างการใช้งาน (Examples)

• Formula:

  =AGGREGATE(9, 0, MyErrTable[Data])

  Description: หาผลรวม (Function Number 9) ของคอลัมน์ Data โดย เลือก options 0 แปลว่า ไม่สนใจค่าจากฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  Result: DIV/0! (เพราะมีค่า Error ปนอยู่ แล้วไม่ได้สั่งให้ข้ามไป)
  
• Formula:

  =AGGREGATE(9, 2, MyErrTable[Data])

  Description: หาผลรวม (Function Number 9) ของคอลัมน์ Data โดย เลือก options 2 แปลว่า ไม่สนใจค่าที่เป็นข้อผิดพลาด, ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  Result: 14 (มาจาก 2 + 5 +3 + 4 ที่ซ่อนอยู่)
  
• Formula:

  =AGGREGATE(9, 3, MyErrTable[Data])

  Description: หาผลรวม (Function Number 9) ของคอลัมน์ Data โดย เลือก options 3 แปลว่า ไม่สนใจแถวที่ถูกซ่อน, ค่าที่เป็นข้อผิดพลาด, ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  Result: 10 (มาจาก 2 + 5 +3 โดยไม่เห็น 4 ที่ซ่อนอยู่)
  
• Formula:

  =AGGREGATE(14,3, MyErrTable[Data],2)

  Description: หาค่ามากสุดอันดับที่ 2 ด้วย LARGE (Function Number 14) ของคอลัมน์ Data โดย เลือก options 3 แปลว่า ไม่สนใจแถวที่ถูกซ่อน, ค่าที่เป็นข้อผิดพลาด, ฟังก์ชัน SUBTOTAL และ AGGREGATE ที่ซ้อนกัน
  Result: 3 (มากสุดคือ 5 รองลงมาคือ 3 โดยไม่สนใจ 4 เพราะสั่งข้ามแถวที่ถูกซ่อนไป)
  

Tips & Tricks

AGGREGATE มีประโยชน์มากเมื่อคุณต้องการทำให้การคำนวณไม่สะดุดแม้ว่าจะมีค่าผิดพลาด โดยใช้ option 6 เพื่อข้ามค่าผิดพลาด และ option 5 เพื่อข้ามแถวที่ซ่อนอยู่ แล้วยังสามารถใช้ร่วมกับ Conditional Formatting เพื่อเน้นผลลัพธ์ที่สำคัญได้ด้วย

ข้อควรระวัง (Cautions)

ต้องใช้ AGGREGATE กับช่วงข้อมูลแนวตั้ง เพราะจะไม่ทำงานถูกต้องในช่วงเรียงแนวนอน หากซ่อนคอลัมน์ในช่วงแนวนอน การคำนวณจะไม่ถูกปรับเปลี่ยน และต้องมีการระบุ ref ตัวที่สองสำหรับฟังก์ชันเช่น LARGE หรือ SMALL มิฉะนั้นจะทำให้เกิดข้อผิดพลาด #VALUE!

ฟังก์ชันที่เกี่ยวข้อง

• SUBTOTAL
• SUM
• AVERAGE
• COUNT

References

• Microsoft Official Documentation
• Intellezy: Mastering AGGREGATE
• Wall Street Mojo: AGGREGATE Excel Function

ขอบคุณที่เข้ามาอ่านนะครับ ❤️