เมื่อเวิร์กชีต Excel ของคุณมีแถวที่ซ่อนอยู่ ข้อมูลที่กรอง หรือข้อมูลที่จัดกลุ่ม ให้ใช้ฟังก์ชัน Excel SUBTOTAL ฟังก์ชัน SUBTOTAL สามารถรวมหรือแยกค่าที่ซ่อนอยู่ในการคำนวณได้ นอกจากการค้นหาผลรวมของกลุ่มข้อมูลแล้ว Excel ยังสามารถคำนวณค่าเฉลี่ย ค่าสูงสุด ค่าต่ำสุด ค่าเบี่ยงเบนมาตรฐาน และความแปรปรวนของข้อมูลของคุณได้ วิธีแทรกผลรวมย่อยใน Excel มีดังนี้
คำแนะนำในบทความนี้ใช้กับ Excel สำหรับ Microsoft 365, Excel 2019 และ Excel 2016
ไวยากรณ์ของฟังก์ชัน SUBTOTAL
ใช้ฟังก์ชัน SUBTOTAL ใน Excel เพื่อสรุปค่าในเวิร์กชีตด้วยวิธีต่างๆ จะเป็นประโยชน์อย่างยิ่งเมื่อเวิร์กชีตของคุณมีแถวที่ซ่อนอยู่ซึ่งคุณต้องการรวมไว้ในการคำนวณ
ไวยากรณ์ของฟังก์ชัน SUBTOTAL คือ:SUBTOTAL(function_num,ref1,ref2,…)
function_num อาร์กิวเมนต์เป็นสิ่งจำเป็นและระบุประเภทของการดำเนินการทางคณิตศาสตร์ที่จะใช้สำหรับผลรวมย่อย ฟังก์ชัน SUBTOTAL สามารถเพิ่มตัวเลข คำนวณค่าเฉลี่ยของตัวเลขที่เลือก ค้นหาค่าสูงสุดและต่ำสุดในช่วง นับจำนวนค่าในช่วงที่เลือก และอื่นๆ
ฟังก์ชัน SUBTOTAL จะละเว้นเซลล์ที่ไม่มีข้อมูลและเซลล์ที่มีค่าที่ไม่ใช่ตัวเลข
อาร์กิวเมนต์นี้เป็นตัวเลขและขึ้นอยู่กับว่าคุณต้องการรวมแถวที่ซ่อนอยู่ในผลลัพธ์หรือแยกแถวที่ซ่อนอยู่ออกจากผลลัพธ์ ตัวกรองอาจซ่อนหรือซ่อนแถวเหล่านี้ด้วยตนเอง
อาร์กิวเมนต์ function_num ได้แก่:
อาร์กิวเมนต์อ้างอิง function_num 1 ถึง 11 รวมเฉพาะค่าในแถวที่ซ่อนอยู่เมื่อใช้คำสั่ง Hide เพื่อซ่อนแถว เมื่อใช้คำสั่ง Filter การคำนวณ SUBTOTAL จะไม่รวมผลลัพธ์การกรองที่ซ่อนอยู่
ref1 จำเป็นต้องมีอาร์กิวเมนต์ นี่คือเซลล์ที่ใช้ในการคำนวณผลลัพธ์ของอาร์กิวเมนต์ function_num ที่เลือก อาร์กิวเมนต์นี้อาจเป็นค่า เซลล์เดียว หรือช่วงของเซลล์
ref2,… อาร์กิวเมนต์เป็นทางเลือก นี่คือเซลล์เพิ่มเติมที่รวมอยู่ในการคำนวณ
ใช้ฟังก์ชัน SUBTOTAL กับแถวที่ซ่อนอยู่
คุณสามารถป้อนฟังก์ชัน Excel ได้ด้วยตนเองหรือโดยใช้กล่องโต้ตอบ อาร์กิวเมนต์ของฟังก์ชัน เพื่อแสดงวิธีการป้อนฟังก์ชันด้วยตนเองโดยใช้แถบสูตร ตัวอย่างต่อไปนี้ใช้อาร์กิวเมนต์ COUNT function_num เพื่อนับจำนวนค่าในแถวที่มองเห็นและทั้งในแถวที่มองเห็นได้และแถวที่ซ่อนอยู่
ในการใช้ฟังก์ชัน SUBTOTAL เพื่อนับจำนวนแถวในเวิร์กชีต:
-
เริ่มต้นด้วยเวิร์กชีตที่มีข้อมูลหลายแถว
-
เลือกเซลล์ที่จะนับจำนวนแถวที่มองเห็นได้
-
ในแถบฟังก์ชัน ให้ป้อน =SUBTOTAL . ขณะที่คุณพิมพ์ Excel จะแนะนำฟังก์ชัน ดับเบิลคลิกที่ SUBTOTAL ฟังก์ชัน
ในการใช้กล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันเพื่อเข้าสู่ฟังก์ชัน SUBTOTAL ให้ไปที่ สูตร และเลือกคณิตศาสตร์และตรีโกณฯ> ผลรวมย่อย .
-
ในเมนูแบบเลื่อนลงที่ปรากฏขึ้น ให้ดับเบิลคลิกที่ 102 – COUNT อาร์กิวเมนต์ function_num
-
พิมพ์เครื่องหมายจุลภาค (, )
-
ในเวิร์กชีต ให้เลือกเซลล์ที่จะรวมไว้ในสูตร
-
กด เข้าสู่ เพื่อดูผลลัพธ์ในเซลล์ที่คุณเลือกในขั้นตอนที่ 2
-
เลือกเซลล์ที่จะนับจำนวนแถวที่มองเห็นและซ่อนไว้
-
ในแถบฟังก์ชัน ให้ป้อน =SUBTOTAL . ขณะที่คุณพิมพ์ Excel จะแนะนำฟังก์ชัน ดับเบิลคลิกที่ SUBTOTAL ฟังก์ชัน
-
ในเมนูแบบเลื่อนลงที่ปรากฏขึ้น ให้ดับเบิลคลิกที่ 2 – COUNT อาร์กิวเมนต์ function_num จากนั้นพิมพ์เครื่องหมายจุลภาค (, )
-
ในเวิร์กชีต ให้เลือกเซลล์ที่จะรวมในสูตร จากนั้นกด Enter .
-
ซ่อนข้อมูลหลายแถว ในตัวอย่างนี้ แถวที่มียอดขายต่ำกว่า 100,000 ดอลลาร์ถูกซ่อนไว้
ใช้ฟังก์ชัน SUBTOTAL กับข้อมูลที่กรองแล้ว
การใช้ฟังก์ชัน SUBTOTAL กับข้อมูลที่กรองแล้วจะละเว้นข้อมูลในแถวที่ตัวกรองลบออกไป ทุกครั้งที่เกณฑ์การกรองเปลี่ยนแปลง ฟังก์ชันจะคำนวณใหม่เพื่อแสดงผลรวมย่อยสำหรับแถวที่มองเห็นได้
ในการใช้ฟังก์ชัน SUBTOTAL เพื่อดูความแตกต่างในผลการคำนวณขณะกรองข้อมูล:
-
สร้างสูตร SUBTOTAL ตัวอย่างเช่น สร้างสูตรเพื่อกำหนดค่าผลรวมย่อยและค่าเฉลี่ยของข้อมูลที่กรอง
ไม่สำคัญว่าคุณจะใช้อาร์กิวเมนต์ function_num สำหรับแถวที่มองเห็นหรือซ่อนอยู่หรือไม่ อาร์กิวเมนต์ทั้งสองให้ผลลัพธ์เดียวกันในข้อมูลที่กรอง
-
เลือกเซลล์ใดก็ได้ในชุดข้อมูล
-
ไปที่หน้าแรก จากนั้นเลือก จัดเรียงและกรอง> ตัวกรอง .
-
ใช้ลูกศรแบบเลื่อนลงเพื่อกรองข้อมูลในเวิร์กชีต
-
สังเกตว่าค่าต่างๆ จะเปลี่ยนแปลงไปในแต่ละครั้งที่คุณเลือกเกณฑ์การกรองที่แตกต่างกันอย่างไร
ใช้ฟังก์ชัน SUBTOTAL กับข้อมูลที่จัดกลุ่ม
เมื่อจัดกลุ่มข้อมูลแล้ว จะมีวิธีการใช้ฟังก์ชัน SUBTOTAL กับแต่ละกลุ่ม แล้วคำนวณผลรวมทั้งหมดสำหรับชุดข้อมูลทั้งหมด
-
เลือกเซลล์ใดก็ได้ในชุดข้อมูล
-
เลือก ข้อมูล > ยอดรวม เพื่อเปิด ผลรวมย่อย กล่องโต้ตอบ
-
เลือก ในแต่ละการเปลี่ยนแปลงใน ลูกศรแบบเลื่อนลงและเลือกการจัดกลุ่มที่จะคำนวณผลรวมย่อยแต่ละรายการ
-
เลือก ใช้ฟังก์ชัน ลูกศรแบบเลื่อนลงและเลือก function_num
-
ใน เพิ่มผลรวมย่อยไปที่ รายการ เลือกคอลัมน์ที่จะใช้สูตร
-
เลือก ตกลง .
-
ผลรวมย่อยจะถูกแทรกสำหรับแต่ละกลุ่มข้อมูล และผลรวมทั้งหมดจะถูกแทรกที่ด้านล่างของชุดข้อมูล
-
หากต้องการเปลี่ยน function_num ให้ไฮไลต์เซลล์ใดๆ ในชุดข้อมูลและเลือก ข้อมูล > ยอดรวม . จากนั้น ให้เลือกตัวเลือกของคุณใน ยอดรวมย่อย กล่องโต้ตอบ
