Thep Excel
SUBTOTALคำนวณผลรวมย่อยพร้อม Filter
SUBTOTAL คำนวณผลรวมย่อยหรือสถิติอื่นๆ ที่สามารถ “ตัดแถวซ่อนออก” ได้อัตโนมัติ ต่างจาก SUM ที่รวมทุกอย่าง
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | คำอธิบาย |
|---|---|---|
| function_num | Number (1-11 or 101-111) | รหัสฟังก์ชันที่บอกการคำนวณ: 1-11 (รวมแถวซ่อน), 101-111 (ไม่รวมแถวซ่อน) |
| ref1 | Range | ช่วงข้อมูลที่ต้องการคำนวณ (เช่น A2:A100) |
| [ref2]ไม่บังคับ | Range | ช่วงข้อมูลเพิ่มเติม (สามารถเพิ่มได้หลายช่วง) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
SUBTOTAL เป็นฟังก์ชันที่ออกแบบมาเพื่อทำงานกับข้อมูลที่มี Filter หรือแถวที่ซ่อน ด้วยระบบ “รหัสฟังก์ชัน” (1-111) ที่บอกว่าจะหาผลรวม, นับ, หาค่าเฉลี่ย ฯลฯ และที่สำคัญคือ SUBTOTAL จะ “ตัดแถวที่ซ่อนออก” ได้อย่างชาญฉลาด
ที่เจ๋งของ SUBTOTAL คือมันเข้าใจ “ความแตกต่างระหว่างแถวที่ซ่อน vs แถวที่ถูก Filter” – ถ้าเลือกรหัส 101-111 มันจะตัดเฉพาะแถวซ่อนออก แต่ยังรับรู้ Filter อยู่ ขณะที่รหัส 1-11 ตัดไม่ออก เรื่องนี้เล็กแต่เวิ่นซ้ำเมื่อต้องทำรายงาน
ส่วนตัวผม SUBTOTAL บันทึกชีวิตเมื่อต้องสรุปยอดขายรายเดือนที่มี Filter ทำให้ผู้บริหารเห็นเฉพาะสิ่งที่เลือกเท่านั้น ไม่ต้องกังวลว่าข้อมูลซ่อนจะหลุดออกมา
Examples
ผลรวมที่ไม่รวมแถวซ่อน (ใช้บ่อยที่สุด)SUBTOTAL(109, B2:B100)
109 = SUM ที่ไม่รวมแถวซ่อน | ใช้เมื่อต้องการสรุปยอดขายแต่ละเดือนเท่านั้น
นับข้อมูลโดยไม่รวมที่ซ่อนSUBTOTAL(103, A2:A50)
103 = COUNTA ที่ไม่รวมซ่อน | เหมาะสำหรับนับจำนวน SKU ที่มีให้เห็น
ใช้กับ AutoFilterSUBTOTAL(109, Sales[Amount])
เมื่อ User กดปุ่ม Filter ใน Ribbon เซลล์นี้จะอัพเดตอัตโนมัติ โดยรวมเฉพาะแถวที่ Filter มา
เปรียบเทียบ: Code 1 vs 101Code 1: =SUBTOTAL(1, A2:A10) | Code 101: =SUBTOTAL(101, A2:A10)
ถ้าแถวที่ 5 ซ่อนไว้และมีค่า 60 → Code 1 จะรวม 60 เข้า แต่ Code 101 จะไม่รวม
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
ฟังก์ชันที่ผู้เขียนโยงไว้กับ SUBTOTAL จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
AVERAGE คืนค่าเฉลี่ยของกลุ่มตัวเลขที่ระบุ โดยจะนับเฉพาะเซลล์ที่มีตัวเลขและค่า 0 เท่านั้น ส่วนเซลล์ว่างหรือข้อความจะถูกข้ามไป ไม่นำมาเป็นตัวหาร ซึ่งหลายคนมักพลาดความแตกต่างระหว่าง 0 กับเซลล์ว่าง ตรงนี้สำคัญมากครับ เพราะมันทำให้ค่าเฉลี่ยที่ได้ต่างกันเลย
COUNT นับเฉพาะเซลล์ที่มี Data Type เป็นตัวเลข (Number) โดยเพิกเฉยเซลล์ว่าง ข้อความ ค่า Logic และ error values โดยอัตโนมัติ รวมถึงตัวเลขลบ เปอร์เซ็นต์ วันที่ เวลา เศษส่วน และผลลัพธ์จากสูตรที่คืนค่าเป็นตัวเลข ทำให้ไม่ต้องกังวลว่าจะนับข้อมูลผิดถ้ามีข้อความปนอยู่ในช่วง
COUNTA นับจำนวนเซลล์ที่มีข้อมูลทุกประเภท ไม่ว่าจะเป็นตัวเลข ข้อความ ค่า Logic (TRUE/FALSE) Error Values หรือแม้แต่ข้อความว่าง ("") ที่เกิดจากสูตร
.
เรียกได้ว่าเป็นเครื่องมือหลักในการตรวจสอบความสมบูรณ์ของข้อมูล หรือนับจำนวนรายการโดยไม่สนใจว่าข้อมูลจะเป็น Data Type ใดก็ตาม
MAX คืนค่าสูงสุดจากชุดข้อมูลที่มี Data Type เป็นตัวเลข เพิกเฉยเซลล์ว่าง ข้อความ และค่า Logic โดยอัตโนมัติ ทำให้ไม่ต้องกังวลว่าจะมีข้อมูลประเภทอื่นปนอยู่ในช่วง เหมาะสำหรับหาค่าสูงสุดเช่น คะแนนสูงสุด ยอดขายสูงสุด หรือวันที่ล่าสุด และยังใช้เทคนิค Clamp (จำกัดค่า) โดยการใส่ 0 เป็น argument แรกเพื่อบังคับให้ค่าลบกลายเป็น 0 ได้อีกด้วย
MIN คืนค่าต่ำสุดจากชุดข้อมูลที่มี Data Type เป็นตัวเลข เพิกเฉยเซลล์ว่าง ข้อความ และค่า Logic โดยอัตโนมัติ ซึ่งทำให้ไม่ต้องกลัวว่าจะมีข้อมูลปนมารบกวนผลลัพธ์ ใช้ได้กับตัวเลขทั่วไป วันที่ (ค่าน้อยสุด = วันเก่าสุด) และระยะเวลา สามารถใช้ร่วมกับ MATCH เพื่อหาตำแหน่ง หรือใช้ร่วมกับ MAX เพื่อจำกัดค่าอยู่ในช่วงที่กำหนด
AGGREGATE เป็นฟังก์ชันสารพัดประโยชน์ที่รวม 19 ฟังก์ชันต่างๆ (SUM, AVERAGE, COUNT, MAX, MIN ฯลฯ) แต่มีความสามารถพิเศษคือ สามารถข้าม Error ได้ และข้ามแถวที่ถูกซ่อน (Hidden Rows) ได้ เหมาะสำหรับ Dashboard, รายงาน หรือข้อมูลที่อาจมีช่องว่างและข้อมูลผิดพลาด
SUM รวมเฉพาะข้อมูลที่มี Data Type เป็นตัวเลข (Number) เท่านั้น ไม่สนใจข้อความและค่า Logic ทำให้ไม่ต้องกลัวว่าจะรวมข้อมูลผิดถ้ามีข้อความปนอยู่ในช่วง รองรับสูงสุด 255 พารามิเตอร์ และอัปเดตอัตโนมัติเมื่อข้อมูลเปลี่ยน เป็นฟังก์ชันพื้นฐานที่ใช้บ่อยที่สุดในงาน Excel
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่