SUBTOTAL

คำอธิบาย

เปลี่ยนตารางข้อมูลที่เยอะจนตาลายให้กลายเป็นข้อมูลย่อยยิ่งด้วย SUBTOTAL! ฟังก์ชันนี้ทำให้คุณสามารถคำนวณค่าที่คุณต้องการในตารางหรือฐานข้อมูลขนาดใหญ่ได้อย่างง่ายดาย เช่น ค่าเฉลี่ย, ผลรวม, ค่าสูงสุด-ต่ำสุด และอื่นๆ โดยสามารถเลือกได้ว่าจะรวมค่าที่ซ่อนอยู่หรือเปล่า ทำให้เป็นเครื่องมือที่น่าสนุกมากในการวิเคราะห์ข้อมูลอย่างชาญฉลาด

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

2003 หรือ Version ก่อนหน้า

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

SUBTOTAL(function_num, ref1, [ref2],...)

Arguments

• function_num (Required – integer)
  หมายเลขตั้งแต่ 1-11 หรือตั้งแต่ 101-111 ที่ใช้ระบุฟังก์ชันย่อยที่จะใช้ ตัวเลข 1-11 รวมแถวที่ถูกซ่อนไว้, แต่ตัวเลข 101-111 ไม่รวมแถวที่ถูกซ่อน
• ref1 (Required – range)
  พื้นที่ หรือช่วงของเซลล์ที่คุณต้องการคำนวณหาผลย่อย
• ref2 (Optional – range)
  พื้นที่ หรือช่วงของเซลล์เพิ่มเติม หากมี ถึง 254 รายการ

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

• Formula:

  =SUBTOTAL(9,A2:A5)

  Description: รวมค่าของเซลล์ A2 ถึง A5 โดยใช้ฟังก์ชัน SUM
  Result:ผลรวมของเซลล์ที่ปรากฎในช่วง A2:A5
  
• Formula:

  =SUBTOTAL(1,A2:A5)

  Description: หาค่าเฉลี่ยของเซลล์ A2 ถึง A5 โดยใช้ฟังก์ชัน AVERAGE
  Result:ค่าเฉลี่ยของเซลล์ที่ปรากฎในช่วง A2:A5
  
• Formula:

  =SUBTOTAL(104,A2:A5)

  Description: หาค่าสูงสุดของเซลล์ A2 ถึง A5 โดยใช้ฟังก์ชัน MAX
  Result:ค่าสูงสุดของเซลล์ที่ปรากฎในช่วง A2:A5
  
• Formula:

  =SUBTOTAL(109, C2:C8)

  Description: หาผลรวมของเซลล์ C2 ถึง C8 โดยยกเว้นแถวที่ซ่อนไว้
  Result:ผลรวมของเซลล์ที่ปรากฎในช่วง C2:C8 ที่ไม่ถูกซ่อน
  
• Formula:

  =IF(A1 = "Total", SUBTOTAL(109, B2:B10), IF(A1 = "Average", SUBTOTAL(101, B2:B10), 0))

  Description: สูตรที่ยืดหยุ่นให้เลือกได้จาก drop-down โดยให้หาผลรวมเมื่อเลือก 'Total' และหาค่าเฉลี่ยเมื่อเลือก 'Average'
  Result:ผลลัพธ์คือการคำนวณเฉพาะเมื่อเปลี่ยนตัวเลือกในช่อง A1
  

Tips & Tricks

SUBTOTAL ใช้ร่วมกับคำสั่ง ‘Filter’ เพื่อให้เห็นการเปลี่ยนแปลงของข้อมูลอย่างทันที และสามารถใช้ SUBTOTAL กับฟังก์ชันอื่น ๆ เพื่อให้สามารถประยุกต์ใช้งานได้หลากหลายมากขึ้น.

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

ระวังไม่ใช้ 3-D references เพราะจะทำให้เกิดข้อผิดพลาด #VALUE! และอย่าลืมว่า SUBTOTAL จะไม่สนใจความผิดพลาด (errors) ในฟังก์ชัน ถ้าต้องการให้เฉลี่ยข้อผิดพลาด ควรใช้ AGGREGATE แทน.

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

• SUM
• AVERAGE
• COUNT
• AGGREGATE

ข้อดี / ข้อจำกัด

ข้อดีของ SUBTOTAL คือสามารถทำงานได้ดีมากกับข้อมูลที่ซ่อนหรือถูกกรอง แต่ข้อจำกัดคือไม่สามารถใช้งานได้ดีกับข้อมูลแนวนอน

References

• Microsoft SUBTOTAL function reference
• CareerFoundry guide on SUBTOTAL
• Contextures SUBTOTAL function guide

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