ผลรวมย่อยของ Master Excel:7 เทคนิคที่ได้รับการพิสูจน์แล้วสำหรับการสรุปข้อมูลที่แม่นยำ

4.1. ผลรวมย่อยในแถวที่ถูกกรองออก

เราใช้ ผลรวมย่อย ต่อไปนี้ สูตรการแทรกผลรวมย่อย

เนื่องจากเราใช้อาร์กิวเมนต์แรก:9 มันจะไม่สนใจเซลล์ที่ถูกกรอง

• คลิกลูกศรแบบเลื่อนลงที่ ตัวแทนฝ่ายขาย และเลือกชื่อ (เช่น อดัม ) เพื่อกรองตามชื่อนั้น

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

รูปภาพด้านล่างแสดงผลลัพธ์ของ SUBTOTAL ฟังก์ชั่นสำหรับอาร์กิวเมนต์ทั้งสอง 9 และ 109 .

4.2. ผลรวมย่อยในแถวที่ซ่อนด้วยตนเอง

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

เปรียบเทียบผลลัพธ์สำหรับอาร์กิวเมนต์ 9 และ 109 . อาร์กิวเมนต์ 9 จะรวมเซลล์ของแถวที่ซ่อนด้วยตนเอง แต่อาร์กิวเมนต์ 109 จะเพิกเฉยต่อสิ่งเหล่านั้น

วิธีที่ 5 – ค้นหาผลรวมย่อยด้วยรหัส VBA

• กด ALT+F11 เพื่อเปิด Visual Basic Editor หน้าต่าง.
• คลิก แทรก บนแถบเครื่องมือ> เลือก โมดูล .

• ใส่โค้ด VBA ของคุณบน โมดูล หน้าต่าง.

รหัส :

Sub Calculate_Subtotal()
Dim iColumn As Integer
Dim iValue As Integer
Dim xValue As Integer
Application.ScreenUpdating = False
iValue = 5
xValue = iValue
Range("B5").CurrentRegion.Offset(1).Sort Range("B6"), 1
Do While Range("B" & iValue) <> ""
    If Range("B" & iValue) <> Range("B" & (iValue + 1)) Then
    Rows(iValue + 1).Insert
    Range("B" & (iValue + 1)) = "Subtotal " & Range("B" & iValue).Value
    For iColumn = 7 To 8 'Columns to Calculate Sum
    Cells(iValue + 1, iColumn).Formula = "=SUM(R" & xValue & "C:R" & iValue & "C)"
    Next iColumn
    Range(Cells(iValue + 1, 1), Cells(iValue + 1, 8)).Font.Bold = True
    iValue = iValue + 2
    xValue = iValue
    Else
    iValue = iValue + 1
    End If
    Loop
Application.ScreenUpdating = True
End Sub

• คลิก เรียกใช้ ปุ่มเพื่อดำเนินการ VBA รหัส.

ขณะนี้ Excel จะแทรกผลรวมย่อยในข้อมูลของคุณตาม ตัวแทนฝ่ายขาย .

วิธีที่ 6 – เพิ่มผลรวมย่อยในตาราง Pivot ของ Excel

ตาราง Pivot มีสี่ช่อง และเรามี ตาราง Pivot ที่จัดรูปแบบแล้ว การใช้ข้อมูลของเราในสาขาเหล่านั้น:
1. ตัวกรอง :ตัวแทนฝ่ายขาย

1. คอลัมน์ :ว่างเปล่า
2. แถว :หมวดหมู่, สินค้า
3. ค่านิยม :ปริมาณ ราคาต่อหน่วย ราคารวม และกำไร

• ไปที่ การออกแบบ แท็บ> คลิกเมนูแบบเลื่อนลงของ ผลรวมย่อย ตัวเลือกภายใต้ เค้าโครง กลุ่ม

• เลือก แสดงผลรวมย่อยทั้งหมดที่ด้านล่างของกลุ่ม จากเมนูแบบเลื่อนลง

ตอนนี้ตารางสรุปจะแทรกผลรวมย่อยที่ด้านล่างของแต่ละกลุ่มตาม ป้ายกำกับแถว . เพิ่มผลรวมทั้งหมดของกลุ่มที่ด้านล่างของตาราง

วิธีที่ 7 – ใช้ Power Query เพื่อแทรกผลรวมย่อย

ใช้ พาวเวอร์คิวรีเพื่อแทรกผลรวมย่อยสำหรับกลุ่ม .

• คลิกเซลล์ในช่วง> ไปที่ ข้อมูล แท็บ> คลิก จากตาราง/ช่วง ภายใต้รับและแปลงข้อมูล .

• คลิก ตกลง บน สร้างตาราง กล่องโต้ตอบ

• Excel จะเลื่อนคุณไปที่ ตัวแก้ไข Power Query หน้าต่าง.
• เลือกคอลัมน์ (เช่น ตัวแทนฝ่ายขาย และ หมวดหมู่ ) ในข้อมูลที่คุณต้องการจัดการ> คลิก จัดกลุ่มตาม ไอคอนจาก การแปลง แท็บ

• ใน จัดกลุ่มตาม กล่องโต้ตอบ ตั้งชื่อคอลัมน์ใหม่
• เลือกประเภทของการดำเนินการที่คุณต้องการดำเนินการ (เช่น SUM)
• เลือก คอลัมน์ ที่คุณต้องการดำเนินการคำนวณ (เช่น ราคารวม)
• คลิก เพิ่มความก้าวร้าว เพื่อจัดกลุ่มเพิ่มเติม

• เพื่อคำนวณผลรวมย่อยของ กำไร , เลือก กำไร ใน คอลัมน์ ฟิลด์และ ผลรวม ใน การดำเนินการ สนาม ตั้งชื่อความก้าวร้าวนี้ (เช่น กำไร ).
• คลิก ตกลง .

• คุณจะเห็นผลรวมย่อยแทรกตามตัวแทนขายและหมวดหมู่
• ไปที่ หน้าแรก แท็บ> คลิกแบบเลื่อนลงของ ปิดและโหลด> เลือก ปิดและโหลด .

Power Query ตัวแก้ไขจะโหลดข้อมูลที่จัดกลุ่มของคุณพร้อมผลรวมย่อยไปยังแผ่นงาน Excel

วิธีลบผลรวมย่อยใน Excel

• เปิด ผลรวมย่อย กล่องโต้ตอบจาก ข้อมูล แท็บ> คลิก ลบทั้งหมด .

• แปลงข้อมูลของคุณเป็นช่วงปกติโดยลบกลุ่มและผลรวมย่อยออกจากช่วงนั้น

หมายเหตุ :การลบผลรวมย่อยจะทำให้ข้อมูลถูกจัดเรียง ไม่เหมือนกับข้อมูลต้นฉบับเหมือนก่อนเรียงลำดับ

คำถามที่พบบ่อย

1. เหตุใดฉันจึงไม่สามารถลบผลรวมย่อยใน Excel ได้

ผลรวมย่อยอาจไม่ทำงานด้วยเหตุผลหลายประการ

i) แผ่นงานที่มีการป้องกัน:คุณต้องยกเลิกการป้องกันแผ่นงานของคุณหากมีการป้องกันด้วยรหัสผ่าน

ii) สมุดงานที่ใช้ร่วมกัน:คุณอาจต้องยกเลิกการแชร์สมุดงานหรือตรวจสอบกับสมุดงานว่ามีการแบ่งปันสมุดงานกับผู้ใช้หลายคนหรือไม่

iii) สูตรหรือมาโครที่กำหนดเอง:หากมีการเพิ่มผลรวมย่อยโดยใช้สูตรหรือมาโครที่กำหนดเอง คุณอาจต้องแก้ไขหรือลบออกเพื่อลบออก

<แข็งแกร่ง>2. ฉันจะยุบหรือขยายผลรวมย่อยใน Excel ได้อย่างไร

คุณสามารถใช้ฟีเจอร์เค้าร่างที่จัดกลุ่มเพื่อยุบหรือขยายผลรวมย่อยใน Excel Excel จะเพิ่มสัญลักษณ์เค้าร่างโดยอัตโนมัติเพื่อให้คุณสามารถยุบหรือขยายกลุ่มผลรวมย่อยได้ การคลิกที่สัญลักษณ์ลบ (-) หรือบวก (+) ถัดจากแถวกลุ่มจะยุบหรือขยายผลรวมย่อยตามลำดับ

<แข็งแกร่ง>3. ฉันสามารถอัปเดตผลรวมย่อยใน Excel โดยอัตโนมัติเมื่อข้อมูลเปลี่ยนแปลงได้หรือไม่

ได้ คุณสามารถตั้งค่าให้ Excel อัปเดตผลรวมย่อยได้เมื่อข้อมูลเปลี่ยนแปลงโดยอัตโนมัติ คุณยังสามารถรีเฟรชผลรวมย่อยได้ด้วยตนเองโดยคลิกขวาที่แถวผลรวมย่อยแล้วเลือก “รีเฟรช” จากเมนูบริบท หากไม่ได้รับการอัปเดตโดยอัตโนมัติ ตรวจสอบให้แน่ใจว่าสมุดงานของคุณคำนวณ สูตรโดยอัตโนมัติ .

<แข็งแกร่ง>4. AGGREGATE เหมือนกับฟังก์ชัน SUBTOTAL ใน Excel หรือไม่

ไม่ มีความแตกต่างระหว่าง AGGREGATE และ ผลรวมย่อย  ฟังก์ชั่นแม้ว่าไวยากรณ์จะคล้ายกัน

ประเด็นสำคัญจากบทความ

• คุณลักษณะ “ผลรวมย่อย” ใน Excel ช่วยให้คุณสามารถแทรกผลรวมย่อยสำหรับกลุ่มข้อมูลตามคอลัมน์ที่ระบุได้
• หากต้องการแทรกผลรวมย่อย ให้เลือกช่วงของข้อมูลและไปที่แท็บ "ข้อมูล" จากนั้นคลิกที่ปุ่ม "ผลรวมย่อย" ในกลุ่ม "โครงร่าง"
• คุณสามารถเพิ่มผลรวมย่อยได้หลายระดับโดยเลือกหลายคอลัมน์เพื่อรวมผลรวมย่อย
• ผลรวมย่อยสามารถยุบหรือขยายได้โดยใช้คุณลักษณะโครงร่างที่จัดกลุ่ม ซึ่งระบุด้วยสัญลักษณ์ลบ (-) และเครื่องหมายบวก (+)
• ผลรวมย่อยสามารถอัปเดตได้โดยอัตโนมัติเมื่อข้อมูลเปลี่ยนแปลงโดยใช้ตารางของ Excel หรือช่วงที่ตั้งชื่อแบบไดนามิก

ดาวน์โหลดหนังสือแบบฝึกหัด

คุณสามารถดาวน์โหลดสมุดแบบฝึกหัดได้จากที่นี่

<<กลับไปที่ผลรวมย่อยใน Excel | เรียนรู้ Excel รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!