SUBTOTAL ใน Excel รวมยอดเฉพาะแถวที่กรองไว้ ไม่นับแถวที่ซ่อน

เวลากรองข้อมูลด้วย AutoFilter แล้วอยากได้ยอดรวมเฉพาะแถวที่มองเห็น สูตร SUM ธรรมดาจะรวมทุกแถวรวมถึงแถวที่ถูกซ่อนไว้ด้วย สูตร SUBTOTAL แก้ปัญหานี้ได้ มันคำนวณเฉพาะแถวที่แสดงอยู่ และยังฉลาดพอที่จะไม่นับผลลัพธ์ SUBTOTAL ตัวอื่นซ้ำ

รูปแบบของสูตร SUBTOTAL

=SUBTOTAL(หมายเลขฟังก์ชัน, ช่วงข้อมูล)

ชุด 1–11 กับชุด 101–111 ต่างกันตรงที่ ชุด 101–111 จะไม่นับแถวที่เราสั่งซ่อนเอง (คลิกขวา → Hide) ด้วย ส่วนแถวที่ถูกซ่อนเพราะตัวกรอง ทั้งสองชุดไม่นับเหมือนกัน

ตัวอย่างที่ 1: ยอดรวมที่วิ่งตามตัวกรอง

เมื่อกรองเหลือเฉพาะหมวด “อาหาร” (แถว 3, 5, 6 ถูกซ่อน) SUBTOTAL รวมเฉพาะ 250 + 180 + 320 = 750 ถ้าเปลี่ยนตัวกรอง ตัวเลขนี้อัปเดตตามทันที ต่างจาก =SUM(C2:C7) ที่จะรวมทุกแถวเสมอ

ตัวอย่างที่ 2: 9 กับ 109 เมื่อซ่อนแถวเอง

ถ้าทำรายงานที่ผู้ใช้อาจซ่อนแถวเองเพื่อดูเฉพาะบางส่วน แนะนำใช้ชุด 101–111

ตัวอย่างที่ 3: ลำดับที่ของแถวที่มองเห็น

ช่วงอ้างอิงล็อกหัวไว้ที่ $B$2 แล้วปลายขยับตามแถว ทำให้ได้เลขลำดับที่นับเฉพาะแถวที่ยังโชว์อยู่ ถ้ากรองข้อมูลออกไป เลขลำดับก็ยังเรียง 1, 2, 3 ต่อเนื่องไม่ขาด

SUBTOTAL กับ AGGREGATE ต่างกันอย่างไร

AGGREGATE ทำได้ทุกอย่างที่ SUBTOTAL ทำ และเพิ่มความสามารถข้าม Error กับเลือกฟังก์ชันได้มากกว่า (เช่น LARGE, SMALL, PERCENTILE) แต่ SUBTOTAL ใช้ง่ายและเป็นตัวที่ Excel ใส่ให้อัตโนมัติเมื่อกดเมนู Data → Subtotal หรือใช้ Total Row ของ ตาราง (Table)

ข้อควรรู้

• วาง SUBTOTAL นอกช่วงข้อมูล — ปกติวางไว้แถวบนสุดหรือล่างสุด ถ้าวางปนในช่วง มันจะเว้นไม่นับตัวมันเองและ SUBTOTAL ตัวอื่นให้อยู่แล้ว
• ไม่ใช่สูตรกรองตามเงื่อนไข — ถ้าอยากรวมยอดตามเงื่อนไขโดยไม่ต้องกด filter ใช้ SUMIFS จะเหมาะกว่า
• ทำงานกับ 1 คอลัมน์เป็นหลัก — ใส่หลายช่วงได้ แต่ที่ใช้บ่อยคือช่วงเดียว