Thep Excel
SUBTOTAL – คำนวณผลรวมย่อยพร้อม Filter
SUBTOTAL คำนวณผลรวมย่อยหรือสถิติอื่นๆ ที่สามารถ “ตัดแถวซ่อนออก” ได้อัตโนมัติ ต่างจาก SUM ที่รวมทุกอย่าง
=SUBTOTAL(function_num, ref1, [ref2], ...)
Function Metrics
Popularity
6/10
Difficulty
3/10
Usefulness
6/10
Syntax & Arguments
=SUBTOTAL(function_num, ref1, [ref2], ...)
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| function_num | Number (1-11 or 101-111) | Yes | รหัสฟังก์ชันที่บอกการคำนวณ: 1-11 (รวมแถวซ่อน), 101-111 (ไม่รวมแถวซ่อน) | |
| ref1 | Range | Yes | ช่วงข้อมูลที่ต้องการคำนวณ (เช่น A2:A100) | |
| ref2 | Range | Optional | ช่วงข้อมูลเพิ่มเติม (สามารถเพิ่มได้หลายช่วง) |
Examples
ผลรวมที่ไม่รวมแถวซ่อน (ใช้บ่อยที่สุด)
SUBTOTAL(109, B2:B100)
=SUBTOTAL(109, B2:B100)
ยอดรวมของเซลล์ที่มองเห็น (ถ้าแถวบางแถวซ่อน จะตัดออก)
นับข้อมูลโดยไม่รวมที่ซ่อน
SUBTOTAL(103, A2:A50)
=SUBTOTAL(103, A2:A50)
จำนวนเซลล์ที่ไม่ว่าง (ไม่รวมแถวซ่อน)
ใช้กับ AutoFilter
SUBTOTAL(109, Sales[Amount])
=SUBTOTAL(109, Sales[Amount])
ยอดรวมตามตัวกรอง (Filter ล่าสุด)
เปรียบเทียบ: Code 1 vs 101
Code 1: =SUBTOTAL(1, A2:A10) | Code 101: =SUBTOTAL(101, A2:A10)
Code 1: =SUBTOTAL(1, A2:A10) | Code 101: =SUBTOTAL(101, A2:A10)
Code 1 = 50.5 (รวมแถวซ่อน) | Code 101 = 40.5 (ไม่รวมแถวซ่อน)
FAQs
ต่างกันยังไง SUBTOTAL(109) vs SUM?
SUM รวมทั้งแถวซ่อนด้วย แต่ SUBTOTAL(109) จะตัดแถวซ่อนออก ถ้าต้องสรุปยอดขายเฉพาะ Branch ที่ Filter มา ให้ใช้ SUBTOTAL(109)
ทำไมถึงมี Code 1-11 และ 101-111?
Code 1-11 รวมแถวที่ซ่อนด้วย (Hide Rows) | Code 101-111 ไม่รวม หลังส่วนใหญ่ใช้ เพราะข้อมูลโดยมากต้องการตัดแถวซ่อนออก
SUBTOTAL กับ Filter มีความสัมพันธ์อย่างไร?
แม้เลือก Code 1-11 (รวมซ่อน) SUBTOTAL ยังคงไม่รวมแถว Filter ที่ถูก Hide โดยระบบ Filter จะตัดแถวออกเสมอ ไม่ว่า Code ไหน
ใช้กับ Table ได้ไหม?
ได้ เช่น =SUBTOTAL(109, Sales[Amount]) ซึ่ง Sales[Amount] คือคอลัมน์ Amount ในตาราง Sales
SUBTOTAL vs AGGREGATE ต่างกันไง?
AGGREGATE มีหลายตัวเลือก (รวมถึง Errors) แต่ SUBTOTAL ออกแบบง่ายกว่าและใช้กับ AutoFilter ได้ดีกว่า ส่วนใหญ่ SUBTOTAL เพียงพอ
Resources & Related
Additional Notes
SUBTOTAL เป็นฟังก์ชันที่ออกแบบมาเพื่อทำงานกับข้อมูลที่มี Filter หรือแถวที่ซ่อน ด้วยระบบ “รหัสฟังก์ชัน” (1-111) ที่บอกว่าจะหาผลรวม, นับ, หาค่าเฉลี่ย ฯลฯ และที่สำคัญคือ SUBTOTAL จะ “ตัดแถวที่ซ่อนออก” ได้อย่างชาญฉลาด
ที่เจ๋งของ SUBTOTAL คือมันเข้าใจ “ความแตกต่างระหว่างแถวที่ซ่อน vs แถวที่ถูก Filter” – ถ้าเลือกรหัส 101-111 มันจะตัดเฉพาะแถวซ่อนออก แต่ยังรับรู้ Filter อยู่ ขณะที่รหัส 1-11 ตัดไม่ออก เรื่องนี้เล็กแต่เวิ่นซ้ำเมื่อต้องทำรายงาน
ส่วนตัวผม SUBTOTAL บันทึกชีวิตเมื่อต้องสรุปยอดขายรายเดือนที่มี Filter ทำให้ผู้บริหารเห็นเฉพาะสิ่งที่เลือกเท่านั้น ไม่ต้องกังวลว่าข้อมูลซ่อนจะหลุดออกมา