SUBTOTAL Excel ใช้รวมยอดเฉพาะรายการที่กรองอย่างไร

Wynn
14 ต.ค. 26
17:26
17 views

subtotal excel ใช้รวมยอดเฉพาะรายการที่กรองได้ด้วยสูตร =SUBTOTAL(109,E4:E100) โดยรวมตัวเลขจากแถวที่ยังแสดงอยู่ ไม่นับแถวที่ถูกกรองออกหรือซ่อนด้วยตนเอง ต่างจาก SUM ที่รวมตัวเลขทั้งหมดในช่วงที่กำหนด จึงเหมาะกับตารางยอดขายที่ต้องการตรวจยอดตามประเภทสินค้า (2026-07-24) [1]

วิธีใช้ SUBTOTAL Excel รวมยอดเฉพาะรายการที่กรอง

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

ขั้นตอนที่ 1 เตรียมตารางยอดขาย

เปิด Excel แล้วจัดข้อมูลให้มีหัวคอลัมน์ชัดเจน เช่น วันที่ สินค้า ประเภทสินค้า และยอดขาย โดยกำหนดให้คอลัมน์ E เป็นยอดขาย และบันทึกจำนวนเงินในรูปแบบตัวเลข ไม่ใช่ข้อความ เพื่อให้ SUBTOTAL สามารถนำค่ามาคำนวณได้ถูกต้อง

หากใช้ตาราง รายรับรายจ่าย excel ก็สามารถประยุกต์วิธีเดียวกันได้ โดยเลือกคอลัมน์จำนวนเงินที่ต้องการรวม และใช้ตัวกรองแยกประเภทหรือหมวดหมู่รายการก่อนตรวจยอดรวม

ขั้นตอนที่ 2 เปิดตัวกรองข้อมูล

คลิกเซลล์ภายในตารางยอดขาย แล้วไปที่ Data → Filter จากนั้น Excel จะแสดงลูกศรสำหรับกรองข้อมูลบริเวณหัวคอลัมน์ ให้ตรวจว่าตัวกรองครอบคลุมรายการที่ต้องการสรุปยอดทั้งหมด

Microsoft Support อธิบายว่า Filter ใช้แสดงเฉพาะข้อมูลที่ตรงตามเงื่อนไข และซ่อนรายการอื่นไว้ชั่วคราว โดยไม่ต้องลบหรือย้ายข้อมูลต้นฉบับ (2026-08-25) [2]

ขั้นตอนที่ 3 ใส่สูตร SUBTOTAL

เลือกเซลล์ว่างเหนือหัวตาราง เช่น E1 เมื่อหัวตารางอยู่แถวที่ 3 และข้อมูลยอดขายอยู่ในช่วง E4 จากนั้นพิมพ์สูตร =SUBTOTAL(109,E4) แล้วกด Enter เพื่อแสดงยอดรวมเฉพาะรายการที่มองเห็น

ตัวเลข 109 หมายถึงการรวมยอดโดยไม่นับแถวที่ถูกกรองออกหรือซ่อนด้วยตนเอง ส่วน E4 คือช่วงเซลล์ยอดขายที่ต้องการรวม ควรวางสูตรไว้นอกช่วงข้อมูลที่ถูกกรอง เพื่อให้ยอดรวมยังมองเห็นได้ (2026-07-24) [1]

ขั้นตอนที่ 4 เลือกรายการที่ต้องการกรอง

คลิกลูกศรที่หัวคอลัมน์ประเภทสินค้า แล้วเลือกหมวดหมู่ที่ต้องการดู เช่น เสื้อผ้า หรือกระเป๋า จากนั้นกด OK เพื่อให้ Excel แสดงเฉพาะรายการที่ตรงกับเงื่อนไข

เมื่อกรองข้อมูลแล้ว SUBTOTAL จะคำนวณยอดรวมใหม่จากรายการที่ยังแสดงอยู่ หากต้องการดูหมวดหมู่อื่น สามารถเปลี่ยนตัวเลือกใน Filter ได้โดยไม่ต้องแก้ไขสูตรเดิม

ขั้นตอนที่ 5 ตรวจยอดรวมหลังกรอง

ตรวจว่ารายการที่แสดงตรงกับหมวดหมู่ที่เลือก และยอด SUBTOTAL เปลี่ยนตามข้อมูลที่เหลืออยู่หรือไม่ จากนั้นทดลองล้างตัวกรองเพื่อดูว่ายอดรวมกลับมาครอบคลุมรายการทั้งหมดตามเดิม

หากยอดรวมไม่ตรงกับรายการที่เห็น ให้ตรวจช่วงเซลล์ในสูตรและรูปแบบข้อมูลในคอลัมน์ยอดขาย เพราะจำนวนเงินที่บันทึกเป็นข้อความอาจไม่ถูกรวมเป็นตัวเลขตามที่ต้องการ

ตัวอย่างสมมติ SUBTOTAL Excel หลังกรองข้อมูล

สมมติว่าร้านค้าออนไลน์บันทึกยอดขายสินค้าไว้ใน Excel และต้องการตรวจยอดเฉพาะหมวดหมู่ที่เลือก ตารางต่อไปนี้แสดงผลรวมจากข้อมูลชุดเดียวกันเมื่อเปลี่ยนเงื่อนไขการกรอง

สถานการณ์	รายการที่แสดง	ผลรวม SUBTOTAL
ไม่กรองข้อมูล	สินค้าทุกหมวด	4,200 บาท
กรองเฉพาะเสื้อผ้า	เสื้อยืดและกางเกง	2,700 บาท
กรองเฉพาะกระเป๋า	กระเป๋าและกระเป๋าผ้า	1,500 บาท

จากตัวอย่าง ยอดรวมเปลี่ยนตามรายการที่แสดง โดยไม่ต้องลบข้อมูลหมวดหมู่อื่นออกจากตาราง วิธีนี้ช่วยให้ตรวจยอดขายเฉพาะกลุ่มสินค้าได้สะดวก และยังสามารถกลับไปดูยอดรวมทั้งหมดได้เมื่อล้างตัวกรอง

SUBTOTAL ต่างจาก SUM อย่างไร?

SUBTOTAL และ SUM ใช้รวมตัวเลขได้เหมือนกัน แต่ให้ผลต่างกันเมื่อมีการกรองหรือซ่อนแถว โดย SUBTOTAL สามารถไม่นับรายการที่ไม่แสดง ขณะที่ SUM ยังคำนวณตัวเลขทั้งหมดภายในช่วงที่กำหนด

ประเด็น	SUBTOTAL แบบ 109	SUM
รวมยอดตัวเลข	ได้	ได้
ไม่นับแถวที่ถูกกรองออก	ใช่	ไม่ใช่
ไม่นับแถวที่ซ่อนด้วยตนเอง	ใช่	ไม่ใช่
เหมาะกับ	รายงานที่ต้องกรองข้อมูล	ยอดรวมทั้งหมดในช่วงข้อมูล

SUBTOTAL รวมยอดเฉพาะแถวที่กรองได้จริงหรือไม่?

Exceljet ยกตัวอย่างข้อมูล 13 รายการ โดยมี 4 รายการถูกกรองออก เหลือ 9 รายการที่แสดงบนหน้าจอ เมื่อใช้ SUBTOTAL จึงได้ยอดรวม 21.17 ดอลลาร์ จากเฉพาะรายการที่มองเห็น ไม่รวมค่าจากแถวที่ถูกกรองออก (2023-01-03) [3]

ทำไม SUM ไม่เปลี่ยนเมื่อกรองข้อมูล?

SUM รวมตัวเลขจากช่วงเซลล์ที่กำหนด แม้บางแถวจะถูกซ่อนด้วย Filter จึงอาจแสดงยอดรวมทั้งหมดแทนยอดเฉพาะรายการที่มองเห็น หากต้องการให้ยอดเปลี่ยนตามตัวกรอง ควรใช้ SUBTOTAL แทน

ตัวอย่างเช่น เมื่อกรองดูเฉพาะสินค้าประเภทเสื้อผ้า SUM ยังรวมยอดขายหมวดกระเป๋าที่อยู่ในช่วงเดียวกัน แต่ SUBTOTAL แบบ 109 จะรวมเฉพาะยอดขายจากแถวที่ยังแสดงอยู่

SUBTOTAL ใช้ 9 หรือ 109 ต่างกันอย่างไร?

SUBTOTAL ใช้รหัส 9 เพื่อรวมยอดโดยยังนับแถวที่ซ่อนด้วยตนเอง แต่รหัส 109 จะไม่นับแถวเหล่านั้น ส่วนแถวที่ถูกกรองออกจะไม่ถูกรวมในผลลัพธ์ของทั้งสองรหัส

Microsoft Support ระบุว่ารหัสฟังก์ชัน SUBTOTAL แบ่งเป็น 2 ชุด คือ 1–11 และ 101–111 โดยชุดหลังไม่นับแถวที่ซ่อนด้วยตนเอง สำหรับการรวมยอดจึงใช้รหัส 9 หรือ 109 ตามลักษณะงาน (2026-07-24) [1]

SUBTOTAL Excel ไม่เปลี่ยนยอด แก้อย่างไร?

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

กรองแล้วทำไมยอดยังเท่าเดิม?

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

จากนั้นตรวจช่วงเซลล์ในสูตรว่าครอบคลุมคอลัมน์ยอดขายถูกต้องหรือไม่ หากเพิ่มรายการใหม่ไว้นอกช่วงที่กำหนด ควรปรับช่วงข้อมูลให้รวมรายการนั้นก่อนตรวจยอดอีกครั้ง

ซ่อนแถวแล้วทำไมยอดยังรวมอยู่?

หากใช้คำสั่ง Hide เพื่อซ่อนแถวด้วยตนเอง แต่สูตร SUBTOTAL ยังนับยอดจากแถวที่ซ่อนอยู่ ให้ตรวจว่ากำลังใช้รหัส 9 หรือไม่ เพราะรหัสนี้ยังรวมค่าจากแถวที่ซ่อนด้วยตนเอง

หากต้องการให้ยอดรวมไม่นับแถวที่ซ่อนด้วยตนเอง ให้ใช้รหัส 109 แทน ซึ่งรองรับทั้งกรณีซ่อนแถวด้วย Hide และกรองรายการออกด้วย Filter (2026-07-24) [1]

ทำไม SUBTOTAL แสดงยอดรวมไม่ครบ?

สาเหตุหนึ่งคือช่วงข้อมูลในสูตรไม่ครอบคลุมรายการทั้งหมด เช่น เพิ่มยอดขายใหม่ต่อท้ายตาราง แต่สูตรยังอ้างอิงเฉพาะช่วงเดิม ทำให้ยอดขายที่เพิ่มเข้ามาไม่ถูกรวม

ควรตรวจตำแหน่งเริ่มต้นและสิ้นสุดของช่วงเซลล์ รวมถึงดูว่าจำนวนเงินถูกบันทึกเป็นตัวเลขหรือข้อความ หากพบรายการที่อยู่นอกช่วง ให้แก้ช่วงสูตรให้ตรงกับข้อมูลจริง

สรุป SUBTOTAL Excel ใช้อย่างไร?

SUBTOTAL Excel เหมาะสำหรับรวมยอดเฉพาะรายการที่แสดงหลังกรองข้อมูล โดยใช้สูตร =SUBTOTAL(109,E4:E100) และปรับช่วงเซลล์ให้ตรงกับตารางจริง เมื่อเปลี่ยนตัวกรอง ยอดรวมจะเปลี่ยนตามรายการที่มองเห็น ต่างจาก SUM ที่ยังรวมข้อมูลในช่วงเดิม หากผลลัพธ์ไม่ตรง ควรตรวจตัวกรอง ช่วงเซลล์ และวิธีซ่อนแถวก่อนแก้สูตร