เมื่อเวิร์กชีต 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 | function_num |
|---|---|---|
| (รวมค่าที่ซ่อนอยู่) | (ไม่รวมค่าที่ซ่อนอยู่) | |
| เฉลี่ย | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| นาที | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
อาร์กิวเมนต์อ้างอิง function_num 1 ถึง 11 รวมเฉพาะค่าในแถวที่ซ่อนอยู่เมื่อใช้คำสั่งซ่อนเพื่อซ่อนแถว เมื่อใช้คำสั่งตัวกรอง การคำนวณ SUBTOTAL จะไม่รวมผลลัพธ์ตัวกรองที่ซ่อนอยู่
จำเป็นต้องมีอาร์กิวเมนต์ ref1 นี่คือเซลล์ที่ใช้ในการคำนวณผลลัพธ์ของอาร์กิวเมนต์ function_num ที่เลือก อาร์กิวเมนต์นี้อาจเป็นค่า เซลล์เดียว หรือช่วงของเซลล์
อาร์กิวเมนต์ ref2, … เป็นทางเลือก นี่คือเซลล์เพิ่มเติมที่รวมอยู่ในการคำนวณ
ใช้ฟังก์ชัน SUBTOTAL ที่มีแถวที่ซ่อนอยู่
ฟังก์ชัน Excel สามารถป้อนได้ด้วยตนเองหรือด้วยความช่วยเหลือของกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชัน เพื่อแสดงวิธีการป้อนฟังก์ชันด้วยตนเองโดยใช้แถบสูตร ตัวอย่างต่อไปนี้ใช้อาร์กิวเมนต์ COUNT function_num เพื่อนับจำนวนค่าในแถวที่มองเห็นและทั้งในแถวที่มองเห็นและซ่อนอยู่
การใช้ฟังก์ชัน SUBTOTAL เพื่อนับจำนวนแถวในเวิร์กชีต:
- เริ่มต้นด้วยเวิร์กชีตที่มีข้อมูลหลายแถว
- เลือกเซลล์ที่จะนับจำนวนแถวที่มองเห็นได้
-
ในแถบฟังก์ชัน ให้ป้อน =SUBTOTAL ขณะที่คุณพิมพ์ Excel จะแนะนำฟังก์ชัน ดับเบิลคลิกที่ฟังก์ชัน SUBTOTAL
ในการใช้กล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันเพื่อเข้าสู่ฟังก์ชัน SUBTOTAL ให้ไปที่ Formulas แล้วเลือก Math & Trig >SUBTOTAL.
Image -
ในเมนูแบบเลื่อนลงที่ปรากฏขึ้น ดับเบิลคลิกที่อาร์กิวเมนต์ 102 – COUNT function_num
Image -
พิมพ์เครื่องหมายจุลภาค (,).
Image -
ในเวิร์กชีต เลือกเซลล์ที่จะรวมในสูตร
Image -
กด Enter เพื่อดูผลลัพธ์ในเซลล์ที่คุณเลือกในขั้นตอนที่ 2
Image - เลือกเซลล์ที่จะนับจำนวนแถวที่มองเห็นและซ่อน
- ในแถบฟังก์ชัน ให้ป้อน =SUBTOTAL ขณะที่คุณพิมพ์ Excel จะแนะนำฟังก์ชัน ดับเบิลคลิกที่ฟังก์ชัน SUBTOTAL
- ในเมนูแบบเลื่อนลงที่ปรากฏขึ้น ดับเบิลคลิกที่อาร์กิวเมนต์ 2 – COUNT function_num จากนั้นพิมพ์เครื่องหมายจุลภาค (,).
-
ในเวิร์กชีต เลือกเซลล์ที่จะรวมในสูตร จากนั้นกด Enter.
Image -
ซ่อนข้อมูลหลายแถว ในตัวอย่างนี้ แถวที่มียอดขายต่ำกว่า $100, 000 เท่านั้นถูกซ่อน
Image
ใช้ฟังก์ชัน SUBTOTAL กับข้อมูลที่กรองแล้ว
การใช้ฟังก์ชัน SUBTOTAL กับข้อมูลที่กรองแล้วจะละเว้นข้อมูลในแถวที่ตัวกรองเอาออก ทุกครั้งที่เกณฑ์การกรองเปลี่ยนแปลง ฟังก์ชันจะคำนวณใหม่เพื่อแสดงผลรวมย่อยของแถวที่มองเห็น
การใช้ฟังก์ชัน SUBTOTAL เพื่อดูความแตกต่างในผลการคำนวณขณะกรองข้อมูล:
-
สร้างสูตร SUBTOTAL ตัวอย่างเช่น สร้างสูตรเพื่อกำหนดค่าผลรวมย่อยและค่าเฉลี่ยของข้อมูลที่กรอง
ไม่ว่าคุณจะใช้อาร์กิวเมนต์ function_num สำหรับแถวที่มองเห็นหรือซ่อนอยู่หรือไม่ อาร์กิวเมนต์ทั้งสองให้ผลลัพธ์เดียวกันในข้อมูลที่กรอง
Image - เลือกเซลล์ใดก็ได้ในชุดข้อมูล
-
ไปที่ Home จากนั้นเลือก Sort & Filter > Filter
Image -
ใช้ลูกศรแบบเลื่อนลงเพื่อกรองข้อมูลในเวิร์กชีต
Image -
สังเกตว่าค่าจะเปลี่ยนไปอย่างไรในแต่ละครั้งที่คุณเลือกเกณฑ์การกรองที่ต่างกัน
Image
ใช้ฟังก์ชัน SUBTOTAL กับข้อมูลที่จัดกลุ่ม
เมื่อข้อมูลถูกจัดกลุ่ม จะมีวิธีการใช้ฟังก์ชัน SUBTOTAL กับแต่ละกลุ่ม แล้วคำนวณผลรวมทั้งหมดสำหรับชุดข้อมูลทั้งหมด
- เลือกเซลล์ใดก็ได้ในชุดข้อมูล
-
เลือก Data > ผลรวมย่อย เพื่อเปิดกล่องโต้ตอบ ผลรวมย่อย กล่องโต้ตอบ
Image - เลือก ที่การเปลี่ยนแปลงแต่ละครั้งในลูกศรแบบเลื่อนลง และเลือกการจัดกลุ่มที่จะคำนวณผลรวมย่อยแต่ละรายการ
- เลือกลูกศรดรอปดาวน์ ใช้ฟังก์ชัน และเลือกฟังก์ชัน_num
- ในรายการ เพิ่มยอดรวมย่อยใน เลือกคอลัมน์ที่จะใช้สูตร
-
เลือก ตกลง.
Image -
ผลรวมย่อยจะถูกแทรกสำหรับแต่ละกลุ่มข้อมูล และผลรวมทั้งหมดจะถูกแทรกที่ด้านล่างของชุดข้อมูล
Image - ในการเปลี่ยน function_num ให้ไฮไลต์เซลล์ใดๆ ในชุดข้อมูลแล้วเลือก Data > Subtotal จากนั้น เลือกในกล่องโต้ตอบ Subtotal
