เชี่ยวชาญ Excel Subtotals: 7 เทคนิคสร้างสรุปข้อมูลที่แม่นยำ

วิธีที่ 1 – แทรก Subtotals ใน Excel แบบอัตโนมัติ

• เลือกเซลล์สุ่ม > ไปที่แท็บ Data > คลิก Sort

• กล่องโต้ตอบ Sort จะปรากฏขึ้น
• ในฟิลด์ Sort by ให้เลือกคอลัมน์ที่ต้องการใช้แทรก subtotals
• เก็บ Cell Values ไว้ในฟิลด์ Sort On
• เรียงลำดับจากน้อยไปมาก: A to Z
• คลิก OK

• สิ่งนี้จะเรียงคอลัมน์ Sales Reps ตามตัวอักษร

ใช้ฟีเจอร์ที่สร้าง込ม เรียกว่ากลไก Subtotal ใน Excel

• คลิกที่เซลล์ในช่วงข้อมูล > เลือก Subtotal ภายใต้กลุ่ม Outline จากแท็บ Data

• กล่องโต้ตอบ Subtotal จะปรากฏ ตอนนี้เราจะแทรก subtotals โดยใช้ฟังก์ชัน SUM
• ฟิลด์ At each change in ควรเก็บข้อมูลที่จะใช้แทรก subtotals
• ใส่ฟังก์ชัน SUM ในฟิลด์ Use function เพื่อคำนวณค่ารวม
• คลิก OK

คุณจะสามารถแทรก subtotals ใน Excel ได้ คอลัมน์ Sales Reps จะแสดง subtotals และ grand totals

• ไอคอนที่มุมซ้ายบนที่มีตัวเลข 1, 2, 3 จะบอกวิธีใช้ subtotals ใน Excel ช่วยให้จัดการกลุ่มและแสดง/ซ่อน subtotals ได้

วิธีที่ 2 – เพิ่ม Multiple Subtotals

2.1. แทรก Multiple Subtotals ในคอลัมน์ต่างกัน

ในส่วนก่อนหน้าเราแสดงวิธีใช้ subtotal กับคอลัมน์ Sales Reps เพิ่ม subtotal อีกตัวตาม Category

• เปิดกล่องโต้ตอบ Sort จากแท็บ Data
• Sort by Sales Reps
• เพิ่มระดับอีกหนึ่งระดับ
• เราเรียงตาม Sales Reps ตอนนี้จะเรียงตาม Category
• คลิก OK

• เปิดกล่องโต้ตอบ Subtotal จากแท็บ Data ตามที่อธิบายใน วิธีที่ 1
• เลือก Sales Reps ในฟิลด์ At each change in

• Excel จะแทรก Subtotals ให้คอลัมน์ Sales Reps
• เปิดกล่องโต้ตอบ Subtotal อีกครั้ง
• ครั้งนี้ให้เลือก Category ใน At each change in
• ตัวเลือก Replace Current Subtotals ต้องไม่ติ๊ก ถ้าติ๊กจะล้าง subtotal ก่อนหน้าและเหลือแค่ subtotal เดียว
• คลิก OK

จัดกลุ่มหลายครั้งและแทรก subtotal ใน Excel

เลือกการเปลี่ยนแปลงอื่นในกล่อง Subtotal หากต้องการเพิ่ม subtotals เพิ่มเติม

2.2. หา Multiple Subtotals ในคอลัมน์เดียวกัน

แทรก multiple subtotals ในคอลัมน์เดียวกัน หลังจากคำนวณผลรวมแล้ว เราจะคำนวณค่าเฉลี่ยและค่าส่วนเบี่ยงเบนมาตรฐาน คุณจะต้องใช้ฟังก์ชันเพิ่มอีกสองฟังก์ชัน เลือกฟังก์ชัน AVERAGE แล้วตามด้วยฟังก์ชัน StdDev

เริ่มหลังจากแทรก subtotals ให้ Sales Reps (จาก วิธีที่ 1) โดยใช้ฟังก์ชัน SUM

• เปิดกล่อง Subtotal อีก 2 ครั้ง
• เลือกฟังก์ชัน AVERAGE และครั้งที่สองเลือกฟังก์ชัน StdDev

• ฟังก์ชัน AVERAGE จะหาค่าเฉลี่ยของคอลัมน์ที่เลือก
• ฟังก์ชัน StdDev จะคำนวณค่าส่วนเบี่ยงเบนมาตรฐาน

จากฟังก์ชันเหล่านี้ subtotals จะถูกแทรกในข้อมูลของคุณ

วิธีที่ 3 – แทรก Subtotals ในตาราง Excel

คุณไม่สามารถใช้ subtotals โดยตรงในตาราง Excel ได้ ฟีเจอร์ Subtotal จะไม่ทำงานที่นี่

หากต้องการแทรก subtotals คุณต้องแปลงตารางเป็นช่วงข้อมูลทั่วไปก่อน

• เลือกเซลล์สุ่มในตารางของคุณ ไปที่แท็บ Table Design > เลือก Convert to Range ภายใต้กลุ่ม Tools

• Microsoft Excel จะถามว่าต้องการแปลงตารางเป็นช่วงข้อมูลหรือไม่ คลิก Yes เพื่อยืนยัน

Excel จะแปลงตารางเป็นช่วงข้อมูลทั่วไป ตอนนี้คุณสามารถแทรก subtotals โดยใช้ฟีเจอร์ subtotal ได้

วิธีที่ 4 – ใช้สูตร SUBTOTAL ใน Excel พร้อม Filter

เราใช้ฟีเจอร์ Subtotal Excel ยังมี ฟังก์ชัน SUBTOTAL ให้ใช้แทรก subtotals ในข้อมูล ใช้ฟังก์ชันนี้สำหรับ ข้อมูลที่กรองแล้ว

ไวยากรณ์ของฟังก์ชัน SUBTOTAL:

SUBTOTAL(function_num,ref1,[ref2],…)

อาร์กิวเมนต์แรก: function_num ถูกอธิบายโดยตัวเลข (1-9 และ 101-109) ที่แทนฟังก์ชันต่างๆ

4.1. Subtotals ในแถวที่ถูกกรองออก

เราใช้สูตร SUBTOTAL ต่อไปนี้เพื่อแทรก subtotals

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

• คลิกลูกศรดรอปดาวน์ที่คอลัมน์ Sales Reps และเลือกชื่อ (เช่น Adam) เพื่อกรองตามชื่อนั้น

คุณจะสังเกตว่าฟังก์ชันคำนวณ subtotals เฉพาะเซลล์ที่ถูกกรอง จะข้ามส่วนที่เหลือ

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

4.2. Subtotals ในแถวที่ซ่อนด้วยตนเอง

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

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

วิธีที่ 5 – หา Subtotals ด้วยโค้ด VBA

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

• ใส่โค้ด VBA ของคุณในหน้าต่าง Module

โค้ด:

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

• คลิกปุ่ม Run เพื่อรันโค้ด VBA

Excel จะแทรก subtotals ในข้อมูลของคุณตาม Sales Reps

วิธีที่ 6 – เพิ่ม Subtotals ใน Pivot Table ของ Excel

มีสี่ฟิลด์ใน Pivot table และเราได้ จัดรูปแบบ Pivot table โดยใช้ข้อมูลในฟิลด์เหล่านั้น:
1. Filter: Sales Reps

1. Columns: ว่าง
2. Rows: Category, Product
3. Values: Quantity, Unit Price, Total Price, และ Profit

• ไปที่แท็บ Design > คลิกดรอปดาวน์ของตัวเลือก Subtotals ภายใต้กลุ่ม Layout

• เลือก Show all Subtotals at Bottom of Group จากเมนูดรอปดาวน์

Pivot table จะแทรก subtotals ที่ด้านล่างของแต่ละกลุ่มตาม Row Labels เพิ่ม grand total ของกลุ่มที่ด้านล่างของตาราง

วิธีที่ 7 – ใช้ Power Query แทรก Subtotals

ใช้ Power Query เพื่อแทรก subtotals สำหรับกลุ่ม

• คลิกเซลล์ในช่วงข้อมูล > ไปที่แท็บ Data > คลิก From Table/Range ภายใต้ Get & Transform Data

• คลิก OK บนกล่องโต้ตอบ Create Table

• Excel จะนำคุณไปยังหน้าต่าง Power Query Editor
• เลือกคอลัมน์ (เช่น Sales Reps และ Category) ในข้อมูลที่ต้องการ > คลิกไอคอน Group By จากแท็บ Transform

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

• เพื่อคำนวณ subtotals ของ Profit เลือก Profit ในฟิลด์ Column และ Sum ในฟิลด์ Operation ตั้งชื่อ aggregation นี้ (เช่น Profit)
• คลิก OK

• คุณจะเห็น subtotals ถูกแทรกตาม Sales Reps และ Category
• ไปที่แท็บ Home > คลิกดรอปดาวน์ของ Close & Load > เลือก Close & Load

เอดิเตอร์ Power Query จะโหลดข้อมูลที่จัดกลุ่มพร้อม subtotals ไปยังเวิร์กชีต Excel

วิธีลบ Subtotals ใน Excel

• เปิดกล่องโต้ตอบ Subtotals จากแท็บ Data > คลิก Remove all

• ได้ข้อมูลกลับมาเป็นช่วงข้อมูลทั่วไปโดยการลบกลุ่มและ subtotals ออก

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

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

1. ทำไมฉันไม่สามารถลบ subtotals ใน Excel ได้?

Subtotals อาจไม่ทำงานได้หลายเหตุผล

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

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

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

2. ฉันจะยุบหรือขยาย subtotals ใน Excel ได้อย่างไร?

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

3. ฉันสามารถอัปเดต subtotals ใน Excel โดยอัตโนมัติเมื่อข้อมูลเปลี่ยนแปลงได้ไหม?

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

4. ฟังก์ชัน AGGREGATE เหมือนกับฟังก์ชัน SUBTOTAL ใน Excel หรือไม่?

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

สรุปสำคัญจากบทความ

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

ดาวน์โหลดไฟล์ฝึกฝน

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

<< กลับไปที่ Subtotal in Excel | Learn Excel

รับแบบฝึกหัด Excel ขั้นสูงพร้อมเฉลยฟรี!