ถ้าคุณเคยสรุปยอดด้วย SUM, AVERAGE แล้วได้ตัวเลข “เกินจริง” เมื่อมีการ Filter หรือ ซ่อนแถว, สูตรที่ตอบโจทย์คือ SUBTOTAL – สรุปผลเฉพาะค่าที่มองเห็น. ฟังก์ชันนี้ออกแบบมาเพื่อทำงานกับ “ข้อมูลที่กรองแล้ว” โดยเฉพาะ ช่วยให้รายงานถูกต้อง ไม่ทับซ้อน และปรับตัวตามการกรองข้อมูลแบบเรียลไทม์ เหมาะมากสำหรับงานยอดขาย, งบประมาณ, รายงานสต็อก และ Dashboard ทุกชนิด
ทำไมต้องใช้ SUBTOTAL?
- สรุปผลเฉพาะแถวที่ มองเห็น หลังกรอง (Filter)
- หลีกเลี่ยงการนับ/รวมแถวที่ถูกซ่อน (ตามที่กำหนด)
- ป้องกันการ “นับซ้ำ” เพราะ SUBTOTAL จะไม่รวมผล SUBTOTAL อื่น ที่อยู่ในช่วง
- อัปเดตผลรวมอัตโนมัติเมื่อมีการกรอง/แสดงแถวเพิ่ม
ใช้สูตรนี้เมื่อใด
- ทำรายงานที่มีการใช้ AutoFilter หรือ Table อยู่เสมอ
- ต้องการแยกผลรวมของกลุ่มข้อมูลที่เปิด/ปิดการแสดงผลเป็นช่วง ๆ
- ต้องการควบคุมว่าจะแนบ/ไม่แนบแถวที่ซ่อนเอง (Manual Hide)
วิธีใช้ SUBTOTAL พร้อมตัวอย่าง
=SUBTOTAL(function_num, ref1, [ref2], ...)
รหัสฟังก์ชัน (function_num) ที่ใช้บ่อย
- 9 = SUM, 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN
- รหัส 1–11 จะ มองข้ามแถวที่ถูกกรอง (Filter) แต่ นับแถวที่ซ่อนด้วยมือ
- รหัส 101–111 จะ มองข้ามทั้งแถวที่ถูกกรอง และแถวที่ซ่อนด้วยมือ
ตัวอย่างที่ 1: SUM เฉพาะแถวที่ถูกแสดงหลัง Filter
=SUBTOTAL(9, D2:D100)รวมยอด เฉพาะแถวที่ไม่ถูกซ่อนด้วย Filter (แถวซ่อนด้วยมือยังถูกนับอยู่)
ตัวอย่างที่ 2: SUM แบบไม่รวมแถวซ่อนด้วยมือ
=SUBTOTAL(109, D2:D100)เหมาะเมื่อคุณใช้ทั้ง Filter และ ซ่อนแถวด้วยมือ ในเวลาเดียวกัน
ตัวอย่างที่ 3: นับจำนวนแถวข้อมูลที่มองเห็น
=SUBTOTAL(3, A2:A100)3 = COUNTA นับ “เซลล์ที่ไม่ว่าง” เฉพาะแถวที่ยังมองเห็น
ตัวอย่างที่ 4: ค่าเฉลี่ยเฉพาะข้อมูลที่มองเห็น
=SUBTOTAL(1, E2:E100)1 = AVERAGE ให้ภาพเฉลี่ยตามข้อมูลที่เหลือหลังกรอง
ตัวอย่างที่ 5: SUBTOTAL ใน Table (อัปเดตตาม Filter)
=SUBTOTAL(9, Table1[Sales])เมื่อคุณกรองคอลัมน์ใด ๆ ใน Table1, ค่านี้จะเปลี่ยนตามทันที
📥 ดาวน์โหลดไฟล์ตัวอย่างที่นี่ ⬇️
ทริคมืออาชีพ
- SUBTOTAL ซ้อน SUBTOTAL: ผลรวมระดับบนจะไม่บวกซ้ำผล SUBTOTAL ย่อย ช่วยทำยอดรายกลุ่มได้สะอาด
- ถ้าต้องการ “สรุปเฉพาะผลลัพธ์ของ FILTER ฟังก์ชัน” (Dynamic Array) ให้ใช้
SUMกับช่วงผลลัพธ์ของFILTERได้เลย (กรณีนั้นไม่จำเป็นต้องใช้ SUBTOTAL) - เลือกใช้ 109 แทน 9 เมื่อองค์กรมีพฤติกรรม “ซ่อนแถวด้วยมือ” บ่อย
ปัญหาที่พบบ่อย + วิธีแก้ + ข้อสังเกต
- ตัวเลขยังไม่เปลี่ยนตาม Filter → ตรวจสอบว่าคุณใช้ SUBTOTAL ไม่ใช่ SUM
- แถวซ่อนด้วยมือยังถูกนับ → เปลี่ยนจากรหัส 9 เป็น 109
- ช่วงอ้างอิงมี SUBTOTAL อยู่ภายใน → ไม่ต้องกังวล ระบบจะไม่รวมซ้ำโดยอัตโนมัติ
- มีค่าข้อความ/ช่องว่าง → ใช้รหัสให้ถูกชนิด เช่น 2 (COUNT) นับเฉพาะตัวเลข, 3 (COUNTA) นับเซลล์ที่ไม่ว่าง
ประโยชน์ของสูตร/เทคนิค
- รายงาน “ถูกต้องตามการมองเห็น” ในทันทีเมื่อมีการกรอง
- ลดความผิดพลาดจากการใช้ SUM/AVERAGE กับข้อมูลที่ถูกซ่อน
- เหมาะกับ Dashboard, Pivot-ก่อนหน้า, รายงานกลุ่มข้อมูลที่เปลี่ยนบ่อย
สูตร SUBTOTAL – สรุปผลเฉพาะค่าที่มองเห็น คือฟังก์ชันหัวใจของรายงานที่มีการกรองข้อมูลอยู่ตลอดเวลา เพราะช่วยให้ตัวเลขสอดคล้องกับข้อมูลที่แสดงจริง สามารถเลือกได้ว่าจะรวม/ไม่รวมแถวที่ซ่อนด้วยมือผ่านรหัส 1–11 และ 101–111. เมื่อทำงานกับ Table หรือ AutoFilter ค่าจะอัปเดตอัตโนมัติ ทั้งแม่นยำและรวดเร็ว—เปลี่ยนวิธีสรุปผลของคุณให้เป็นมืออาชีพในทันที
