ฟังก์ชัน SUBTOTAL: สูตรและวิธีใช้ฟังก์ชัน SUBTOTAL ใน Excel

ฟังก์ชัน SUBTOTAL ใน Excelคืออะไร สูตร SUBTOTAL ใน Excelคืออะไร มาหาคำตอบกับ LuckyTemplates.com กันเถอะ!

Microsoft Excel มีฟังก์ชันมากมายที่ใช้ในการคำนวณผลรวม และ SUBTOTAL ก็เป็นหนึ่งในนั้น อย่างไรก็ตาม ผลรวมย่อยจะแตกต่างจากฟังก์ชัน SUM มันไม่ใช่ผลรวมปกติ แต่จะใช้ในการคำนวณผลรวมย่อยในรายการหรือฐานข้อมูล คำนวณค่าเฉลี่ย และอื่นๆ แทน

เนื้อหาหลักเกี่ยวกับฟังก์ชัน SUBTOTAL

• ฟังก์ชัน SUBTOTAL คืออะไร และใช้เพื่ออะไร
• สูตรผลรวมย่อยใน Excel
• วิธีใช้ฟังก์ชัน SUBTOTAL ใน Excel
  • 1. คำนวณผลรวมของแถวที่กรอง 
  • 2. นับเซลล์ที่กรองซึ่งไม่ว่างเปล่า
  • 3. ละเว้นค่าในสูตรผลรวมย่อยที่ซ้อนกัน
• สิ่งที่ต้องพิจารณาก่อนใช้ฟังก์ชัน SUBTOTAL ใน Excel
• วิธีการเกิดข้อผิดพลาดเมื่อใช้งานฟังก์ชัน SUBTOTAL ใน Excel

ฟังก์ชัน SUBTOTAL คืออะไร และใช้เพื่ออะไร

เมื่อมองแวบแรก คุณอาจสงสัยว่าฟังก์ชัน SUBTOTAL มีจุดประสงค์อะไร ท้ายที่สุดแล้ว การค้นหาผลรวมย่อยจากช่วงของเซลล์โดยใช้ฟังก์ชันที่มีอยู่ เช่นSUMIFหรือAVERAGEเป็น เรื่องง่าย

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

SUBTOTAL ทำงานร่วมกับฟีเจอร์อื่นๆ ของ Excel เช่น การกรองเซลล์ หากคุณกรองตารางตามค่า สูตร SUBTOTAL จะอัปเดต จดจำตัวกรอง และไม่รวมเซลล์ที่เกี่ยวข้อง SUBTOTAL ยังมีประโยชน์สำหรับการละเว้นค่าที่ซ่อนอยู่ ซึ่งฟังก์ชันอื่นๆ (เช่น SUM) ไม่สามารถทำได้

โชคดีที่คุณสามารถใช้ฟังก์ชันอื่นๆ เป็นส่วนหนึ่งของสูตร SUBTOTAL ได้ มีวิธีผลรวมย่อยที่แตกต่างกัน 11 วิธี ซึ่งแต่ละวิธีเหมาะสำหรับฟังก์ชัน Excel ที่มีอยู่ ถ้าคุณต้องการสูตร SUM ที่ใช้งานได้กับเซลล์ที่กรองและละเว้นสูตร SUBTOTAL อื่นๆ วิธีที่ดีที่สุดคือใช้ SUBTOTAL แม้ว่าตารางสรุปจะทำงานในลักษณะเดียวกันก็ตาม

สูตรผลรวมย่อยใน Excel

ฟังก์ชัน SUBTOTAL ทำงานโดยการคำนวณค่ารวมของช่วงของเซลล์ โดยยึดตามฟังก์ชันทางคณิตศาสตร์อื่น SUBTOTAL ใช้ฟังก์ชันทางคณิตศาสตร์ที่แตกต่างกันถึง 11 ฟังก์ชัน (ตั้งแต่ AVERAGE ถึง VAR.P) เพื่อค้นหาค่ารวมที่เหมาะสม ในขณะที่ทำงานภายในพารามิเตอร์ของฟังก์ชัน (นั่นคือ ละเว้นสูตร SUBTOTAL อื่นๆ)

ฟังก์ชัน SUBTOTAL มีไวยากรณ์=SUBTOTAL(function_num, ref1, [ref2],… ) ในนั้น:

• Function_num: ตัวเลขตั้งแต่ 1 ถึง 11 และ 101 ถึง 111 ระบุว่าจะใช้ฟังก์ชันใดในการคำนวณเป็น SUBTOTAL
• Ref1, Ref2, ...: 1 เซลล์ขึ้นไป หรือช่วงของเซลล์ที่จะคำนวณผลรวมย่อย สูงสุด 254

บันทึก:

• ฟังก์ชัน SUBTOTAL ได้รับการออกแบบมาเพื่อคำนวณคอลัมน์ข้อมูลแนวตั้ง
• ถ้าอาร์กิวเมนต์ ref1, ref2,... มีฟังก์ชัน SUBTOTAL ก็จะถูกละเว้นเพื่อหลีกเลี่ยงการนับซ้ำ
• ถ้า function_num มีค่าตั้งแต่ 1 ถึง 11 ฟังก์ชัน SUBTOTAL จะคำนวณรวมค่าที่ซ่อนอยู่ในชุดข้อมูล เนื่องจากแถวที่มีช่วงนั้นถูกซ่อนอยู่ ถ้า function_num อยู่ระหว่าง 101 ถึง 111 ฟังก์ชัน SUBTOTAL จะคำนวณเฉพาะค่าที่ไม่ซ่อนอยู่ในชุดข้อมูล (ไม่นับค่าในแถวที่ซ่อนอยู่)
• สำหรับพื้นที่ข้อมูลที่ซ่อนโดยตัวกรอง SUBTOTAL จะถูกละเว้น

วิธีใช้ฟังก์ชัน SUBTOTAL ใน Excel

1. คำนวณผลรวมของแถวที่กรอง 

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

=ผลรวมย่อย(9,_ช่วง)

pham_range นี่คือพื้นที่ที่คุณต้องการหาผลรวมหลังจากกรองข้อมูลแล้ว

2. นับเซลล์ที่กรองซึ่งไม่ว่างเปล่า

เราจะใช้ SUBTOTAL 3 หรือ SUBTOTAL 103 อย่างไรก็ตาม ในกรณีที่มีแถวที่ซ่อนอยู่ คุณต้องใช้ SUBTOTAL 103 เพื่อนับเซลล์ที่ไม่ว่างที่มองเห็นได้อย่างแม่นยำ

ตัวอย่างเช่น ตารางข้อมูลด้านบนจะซ่อน 2 แถวที่ 4 และ 5 เมื่อใช้ SUBTOTAL 3 หรือ SUBTOTAL 103 จะให้ผลลัพธ์ 2 แบบที่แตกต่างกัน

เราใส่สูตรแล้ว Excel จะแสดงชุดฟังก์ชันให้คุณเลือกโดยอัตโนมัติโดยไม่ต้องจำ

ผลลัพธ์เมื่อใช้SUBTOTAL 3จะเป็น 3 รวมถึงเซลล์ที่ซ่อนอยู่ในแถวด้วย

สำหรับSUBTOTAL 103จะแสดงเฉพาะเซลล์ที่ไม่ว่างที่เราเห็น โดยไม่สนใจแถวที่ซ่อนอยู่

3. ละเว้นค่าในสูตรผลรวมย่อยที่ซ้อนกัน

เช่น เราจะคำนวณค่าเฉลี่ยของกิโลกรัมผ้าทั้งหมดในโกดัง A1 และ A2

สูตรเฉลี่ย สำหรับคลังสินค้า A2= SUBTOTAL(1,C2:C4) และให้ผลลัพธ์ 19

สูตรเฉลี่ยสำหรับคลังสินค้าA1 = SUBTOTAL(1,C5:C7)มีผลลัพธ์ 38

อย่างไรก็ตาม เมื่อคำนวณค่าเฉลี่ยของจำนวนผ้าทั้งหมดในคลังสินค้า 2 แห่ง ผลลัพธ์โดยเฉลี่ยในคลังสินค้า 2 แห่งจะถูกละเว้น เรามีสูตร =SUBTOTAL(1,C2:C9) และผลลัพธ์จะลบผลลัพธ์เฉลี่ยที่คำนวณไว้ก่อนหน้านี้ออกโดยอัตโนมัติ

สิ่งที่ต้องพิจารณาก่อนใช้ฟังก์ชัน SUBTOTAL ใน Excel

แม้ว่า SUBTOTAL จะมีข้อดี แต่ก็มีบางสิ่งที่คุณควรพิจารณาก่อนเริ่มใช้ฟังก์ชันนี้ ซึ่งรวมถึงสิ่งต่อไปนี้:

• ตามที่กล่าวไว้ การใช้ 1-11 สำหรับอาร์กิวเมนต์ function_num จะทำให้ SUBTOTAL มีค่าที่ซ่อนอยู่ ในขณะที่ 101-111 จะละเว้นค่าเหล่านั้น
• การใช้ค่าอื่นที่ไม่ใช่ 1-11 หรือ 101-111 จะทำให้ Excel ส่งกลับข้อผิดพลาด #VALUE! สิ่งนี้จะเกิดขึ้นสำหรับการอ้างอิงเซลล์ 3 มิติด้วย (โดยที่เซลล์เดียวกันในหลายแผ่นงานถูกอ้างอิงในช่วง)
• เมื่อใช้ช่วงเซลล์แนวนอน เช่น A1:D1 ค่าที่ซ่อนไว้จะถูกรวมโดยอัตโนมัติ (ไม่ว่าค่าอาร์กิวเมนต์ function_num จะเป็นเท่าใด) นี่เป็นข้อจำกัดของฟังก์ชัน SUBTOTAL และไม่สามารถเอาชนะได้
• การใช้ SUBTOTAL กับข้อมูลที่กรองจะช่วยให้มั่นใจได้ว่าค่าที่ซ่อนอยู่จะถูกละเว้นเสมอ โดยไม่คำนึงถึงอาร์กิวเมนต์ function_num ที่ใช้
• SUBTOTAL สามารถใช้เป็นฟังก์ชันแบบซ้อนโดยเป็นส่วนหนึ่งของฟังก์ชันอื่นๆ ได้ รวมถึงสูตรที่มีฟังก์ชัน IF
• ถ้าสูตร SUBTOTAL อื่นอยู่ในช่วงข้อมูล ref1 SUBTOTAL จะละเว้นและแยกออกจากการคำนวณโดยรวม
• Excel อนุญาตให้ใช้ช่วงเซลล์ได้สูงสุด 254 ช่วงในสูตร SUBTOTAL แม้ว่าสูตรจะทำงานได้เพียง 1 ช่วงเซลล์ก็ตาม

วิธีการเกิดข้อผิดพลาดเมื่อใช้งานฟังก์ชัน SUBTOTAL ใน Excel

เมื่อคุณคำนวณฟังก์ชัน SUBTOTAL ใน Excel คุณมักจะพบข้อผิดพลาดพื้นฐานบางประการ:

• #VALUE!: ตัวระบุฟังก์ชันไม่อยู่ในช่วง 1-11 หรือ 101-111 หรือมีการอ้างอิง 3 มิติ
• #DIV/0!: เกิดขึ้นเมื่อต้องหารผลรวมที่ระบุด้วย 0 (เช่น การคำนวณค่าเฉลี่ยหรือส่วนเบี่ยงเบนมาตรฐานของช่วงของเซลล์ที่ไม่มีค่าตัวเลข)
• #ชื่อ? ชื่อฟังก์ชัน SUBTOTAL สะกดผิด

เคล็ดลับบางประการเกี่ยวกับวิธีใช้ฟังก์ชัน SUBTOTAL ใน Microsoft Excel ที่คุณอาจไม่รู้

เคล็ดลับที่ 1:สมมติว่าคุณต้องการให้แน่ใจว่าสีเสื้อยืดทุกสีมีจำหน่ายทุกขนาดในคลังสินค้าแห่งใดแห่งหนึ่ง คุณสามารถทำตามขั้นตอนเหล่านี้:

ขั้นตอนที่ 1: คลิกผลรวมย่อย โปรดจำไว้ว่าขั้นตอนนี้คือการเพิ่มเกณฑ์ให้กับข้อมูลผลรวมย่อยที่มีอยู่

ขั้นตอนที่ 2: เลือกCOUNTจากเมนูแบบเลื่อนลง และเลือกขนาดจากช่อง " เพิ่มช่องผลรวมย่อยใน " จากนั้น ยกเลิกการเลือกแทนที่ผลรวมย่อยปัจจุบัน เมื่อคุณคลิกตกลงคุณจะได้รับตารางข้อมูลต่อไปนี้:

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

เคล็ดลับ 2 : การจัดเรียงข้อมูลตามคอลัมน์มีประโยชน์เสมอ

เคล็ดลับ 3:ช่วยให้คุณใส่ป้ายกำกับในแถวแรก

เคล็ดลับ 4:หากคุณต้องการสรุปข้อมูล ให้ยกเลิกการทำเครื่องหมายที่ช่อง “ สรุปด้านล่างข้อมูลเมื่อแทรกผลรวมย่อย ” - สรุปข้อมูลด้านล่างเมื่อแทรกฟังก์ชัน SUBTOTAL

สรุปแล้วฟังก์ชัน Subtotal ใน Excel คืออะไร?

ตามชื่อที่แนะนำ ฟังก์ชัน SUBTOTAL ใน Excel ใช้ในการคำนวณผลรวมย่อย ฟังก์ชัน Excel SUBTOTAL ส่งคืนผลลัพธ์รวมสำหรับค่าที่ระบุ SUBTOTAL สามารถคำนวณผลรวม ค่าเฉลี่ย จำนวน สูงสุด... Excel นี้ยังสามารถรวมหรือแยกค่าในแถวที่ซ่อนอยู่ได้ด้วย

• วัตถุประสงค์: คำนวณผลรวมย่อยในรายการหรือฐานข้อมูล
• ค่าที่ส่งกลับ: ตัวเลขที่แสดงถึงประเภทของผลรวมย่อย
• การโต้แย้ง:
  • function_num- ตัวเลขที่ระบุฟังก์ชันที่จะใช้ในการคำนวณผลรวมย่อยในรายการ
  • ref1- ช่วงที่มีชื่อหรือการอ้างอิงถึงผลรวมย่อย
  • ref2- [ไม่บังคับ] ช่วงที่มีชื่อหรือการอ้างอิงถึงผลรวมย่อย

การใช้ฟังก์ชัน SUBTOTAL สามารถช่วยให้คุณวิเคราะห์ชุดข้อมูลได้อย่างรวดเร็วโดยไม่ต้องอาศัยตารางเดือยที่ซับซ้อนมากขึ้น ถ้าคุณติดขัด คุณยังสามารถใช้ฟีเจอร์ผลรวมย่อยที่พบในแท็บข้อมูลบน Ribbon เพื่อสร้างสูตรผลรวมย่อยโดยอัตโนมัติซึ่งคุณสามารถแก้ไขหรือคัดลอกจากที่อื่นได้

ดูเพิ่มเติม:

• ฟังก์ชัน SUMPRODUCT ใน Excel: คำนวณผลรวมของค่าที่สอดคล้องกัน
• วิธีรวม 2 คอลัมน์ชื่อเต็มและชื่อใน Excel โดยไม่สูญเสียเนื้อหา