วิธีใช้ฟังก์ชัน SUBTOTAL ใน Excel คำนวณข้อมูลที่กรอง/ซ่อนได้แม่นยำ

เมื่อแผ่นงาน Excel มีแถวที่ซ่อนอยู่ ข้อมูลที่กรอง (Filtered) หรือข้อมูลที่จัดกลุ่ม (Grouped) ให้ใช้ฟังก์ชัน SUBTOTAL ฟังก์ชันนี้สามารถเลือกได้ว่าจะรวมหรือไม่นำค่าที่ซ่อนอยู่ไปคำนวณ นอกจากหาผลรวมแล้ว ยังคำนวณค่าเฉลี่ย ค่าสูงสุด/ต่ำสุด ส่วนเบี่ยงเบนมาตรฐาน และความแปรปรวนได้อีกด้วย

ใช้ได้กับ: Excel สำหรับ Microsoft 365, Excel 2019, Excel 2016

ไวยากรณ์ของฟังก์ชัน SUBTOTAL

SUBTOTAL ใช้สรุปค่าในแผ่นงานหลายรูปแบบ โดยเฉพาะเมื่อมีแถวซ่อนที่ต้องการนำมาคำนวณด้วย

รูปแบบ: SUBTOTAL(function_num, ref1, [ref2], …)

• function_num (บังคับ): รหัสเลขระบุประเภทการคำนวณ เช่น ผลรวม ค่าเฉลี่ย นับจำนวน หาค่าสูง/ต่ำ ฯลฯ
• ref1 (บังคับ): เซลล์หรือช่วงข้อมูลที่นำมาคำนวณ
• ref2,… (ตัวเลือก): ช่วงข้อมูลเพิ่มเติม

หมายเหตุ: SUBTOTAL จะข้ามเซลล์ว่างและเซลล์ที่ไม่ใช่ตัวเลขอัตโนมัติ

ตารางรหัส function_num

สำคัญ: รหัส 1-11 จะนับแถวที่ซ่อนด้วยคำสั่ง Hide แต่ ไม่รวม แถวที่ถูกซ่อนโดย Filter (การกรองข้อมูล)

ใช้ SUBTOTAL กับแถวที่ซ่อน (Hidden Rows)

ตัวอย่าง: นับจำนวนแถวที่มองเห็น vs นับรวมแถวซ่อนด้วย โดยใช้ function_num = 102 (COUNT ไม่นับซ่อน) และ 2 (COUNT นับซ่อน)

1. เปิดแผ่นงานที่มีข้อมูลหลายแถว
2. เลือกเซลล์สำหรับผลลัพธ์ "นับแถวที่มองเห็น"
3. พิมพ์ =SUBTOTAL ในแถบสูตร คลิกคำแนะนำ SUBTOTAL
4. จากเมนูแบบเลื่อนลง เลือก 102 – COUNT
5. พิมพ์ comma (,) แล้วเลือกช่วงข้อมูลที่ต้องการนับ
6. กด Enter ดูผลลัพธ์
7. เลือกเซลล์ใหม่สำหรับ "นับรวมแถวซ่อน" ทำซ้ำขั้นตอน 3-6 แต่เลือก 2 – COUNT แทน
8. ลองซ่อนแถวบางแถว (เช่น แถวที่ยอดขายต่ำกว่า 100,000) ผลลัพธ์ของ 102 จะไม่เปลี่ยน แต่ 2 จะเปลี่ยนตามจำนวนแถวทั้งหมด

ใช้ SUBTOTAL กับข้อมูลที่กรอง (Filtered Data)

SUBTOTAL บนข้อมูลที่กรองจะ ไม่นับ แถวที่ถูกซ่อนโดย Filter ทุกครั้งที่เปลี่ยนเงื่อนไขกรอง ผลลัพธ์จะคำนวณใหม่ให้อัตโนมัติ

1. สร้างสูตร SUBTOTAL สำหรับผลรวมและค่าเฉลี่ยของข้อมูลที่จะกรอง (รหัส 1-11 หรือ 101-111 ให้ผลเหมือนกันกับข้อมูลที่กรอง)
2. คลิกเซลล์ใดๆ ในชุดข้อมูล
3. ไปที่ Home > Sort & Filter > Filter
4. ใช้ลูกศรดรอปดาวน์กรองข้อมูล ตามสังเกตค่า SUBTOTAL จะเปลี่ยนตามข้อมูลที่มองเห็น

ใช้ SUBTOTAL กับข้อมูลที่จัดกลุ่ม (Grouped Data)

Excel มีคุณสมบัติ Subtotal อัตโนมัติสำหรับข้อมูลที่จัดกลุ่ม สร้างผลรวมย่อยแต่ละกลุ่มและผลรวมรวม (Grand Total) ท้ายตาราง

1. คลิกเซลล์ใดๆ ในชุดข้อมูล
2. ไปที่ Data > Subtotal เปิดกล่องโต้ตอบ Subtotal
3. ใน At each change in เลือกคอลัมน์ที่ใช้จัดกลุ่ม
4. ใน Use function เลือก function_num ที่ต้องการ (เช่น Sum, Average)
5. ใน Add subtotal to เลือกคอลัมน์ตัวเลขที่จะคำนวณ
6. กด OK จะมีแถว Subtotal แทรกเข้ามาแต่ละกลุ่ม และ Grand Total ท้ายตาราง
7. ต้องการเปลี่ยนการคำนวณ: ไฮไลท์เซลล์ในข้อมูล > Data > Subtotal > ปรับตัวเลือกในกล่องโต้ตอบ