ทำ Subtotal ใน Excel Worksheet

Subtotal

คือผลรวมย่อย ซึ่งฟังก์ชัน SUBTOTALจะส่งกลับผลรวมย่อยของรายการหรือฐานข้อมูล

SUBTOTAL(function_num, ref1, ref2, ...)

• function_num คือตัวเลข 1 ถึง 11 ที่ใช้เพื่อให้ Subtotal คืนค่าที่คำนวณในฐานข้อมูล
  
  
  
  1. ค่าเฉลี่ย
  2. นับจำนวน
  3. COUNTA นับจำนวนเซลล์
  4. ค่ามากที่สุด
  5. ค่าน้อยที่สุด
  6. ผลคูณ
  7. ส่วนเบี่ยงเบนมาตรฐาน
  8. ส่วนเบี่ยงเบนมาตรฐานของประชากร
  9. ผลรวม
  10. ค่าความแปรปรวน
  11. ค่าความแปรปรวนของประชากร


• ref1, ref2 ... คือ 1-254 การอ้างอิงเพื่อให้ฟังก์ชันหาผลรวมย่อยในข้อมูล

ถ้ากำหนดค่า function_num เท่ากับ 1, Subtotal จะหาค่าเฉลี่ยของค่าอ้างอิงคือ ref1- ref สุดท้ายที่ถูกกำหนดไว้ และถ้า function_num เป็น 9 ก็จะเป็นการหาผลรวมของเงื่อนไข ref ทั้งหมด ซึ่ง function_num ที่ใช้กันบ่อยคือ การหาผลรวม (9), หาค่าเฉลี่ย(1), นับจำนวน(2), หาค่ามากที่สุด (4), หาค่าน้อยที่สุด(5)

---

ตัวอย่างการใช้งาน Subtotal

1. สมมุติให้มีข้อมูล 3 ส่วน D1-D3 แต่ละส่วนมีข้อมูล 5 รายการ 
2. และใช้ Subtotal ให้คืนค่าดังนี้
   • ผลรวม -subtotal(9,(A2:A5))
   • ค่าเฉลี่ย -subtotal(1,(A2:B5))
   • ค่ามากที่สุด - subtotal(4,(A2:C5))
   • และค่าน้อยที่สุด - subtotal(5,(A2:C5))
3. เพื่อให้ใช้งานได้ยืดหยุ่นขึ้นโดยกำหนดให้ตำแหน่ง function_num เป็นเซลล์ใดๆ ตามตัวอย่างของแถวที่ 7 ซึ่งกำหนดให้ B7 เป็นเลข 9 แล้วให้สูตรดึงค่าจาก B7 มากำหนดค่า function_num

	A	B	C	D	D
1	D1	D2	D3	การใช้ Function SUBTOTAL	ผลลัพท์
2	100	30	90	=subtotal(9,(A2:A5))	350.00
3	10	130	50	=subtotal(1,(A2:B5))	76.25
4	200	20	110	=subtotal(4,(A2:C5))	250.00
5	40	80	250	=subtotal(5,(A2:C5))	10.00
6
7		9		=subtotal(B7,(B2:C5))	760.00

นอกจากการใช้ฟังก์ชันหาผลรวมย่อย ใน Excel ยังมีการใช้ Subtotal รูปแบบอัตโนมัติที่แทรกผลรวมย่อยลงในรายการได้เลย

1. เริ่มต้นใช้ Subtotal โดยไปที่เมนูแทป Data เลือก Outline (เค้าร่าง)
2. Excel จะแสดงกล่องโต้ตอบ Subtotal ในแต่ละหัวข้อต้องกำหนดเงื่อนไขเพื่อให้ Subtotal คืนค่าตามแบบที่ต้องการ
   • At each change in: เลือกคอลัมท์ที่ต้องการจัดกลุ่ม ในที่นี้เป็น Category
   • Use function: เลือกใช้ฟังชันก์ใดฟังก์ชันหนึ่งในกลุ่ม Subtotal
   • Add subtotal to: ต้องการแทรกผลรวมย่อยที่คอลัมท์ใดบ้าง
   • Replace current subtotals แทนที่ผลรวมย่อยเดิม (ถ้ามี)
   • Page break between groups แทรกตัวแบ่งหน้าของแต่ละกลุ่มซึ่งในที่นี้ถ้ากำหนดไว้ เส้นแบ่งหน้าจะแบ่งทุก Category
   • Summary below data ผลรวมของข้อมูลทั้งหมด หรือ Grand total




3. จากนั้น ok
4. หน้าตาของเวิร์คชีทจะเปลี่ยนไปโดยเพิ่มพื้นที่ด้านซ้ายขึ้นแยกเป็นสามส่วนโดยที่สามารถคลิกเพื่อดู detail ของรายงานในรูปแบบต่างๆ คือ
   • 1 : เมื่อคลิกที่ 1 ฟังก์ชันจะรวบรายงานเหลือผลของรายงานทั้งหมด Grand total, Grand Average ฯลฯ
   • 2 : รายงานจะแสดงเพียงกลุ่มที่กำหนดไว้ที่ At each change in:
   • 3 : แสดงรายการทั้งหมดที่แสดงผลรวมที่แทรกไว้ด้วย

สิ่งที่สำคัญสำหรับ Subtotal คือ ข้อมูลของกลุ่มที่ต้องการหาผลรวมย่อยควรเรียงลำดับไว้ให้เรียบร้อยก่อน เพื่อให้ข้อมูลไม่สะเปะสะปะ เพราะไม่เช่นนั้นแล้ว เมื่อผลรวมย่อยออกมาแล้วจะพบว่ากลุ่มข้อมูลซ้ำกันหลายรายการ ซึ่งไม่ส่งผลดีต่อการวิเคราะห์ข้อมูล

ข้อดีของการทำ Subtotal คือสามารถย้อนสู่สภาพเดิมได้ โดยไปที่ Data แทป เลือก Outline ไปที่ Subtotal เหมือนเดิม จากนั้นให้เลือก Remove all

---

อีกนิดนึงก่อนจบ Excel Worksheet สามารถตั้งให้บอกค่าเฉลี่ย, จำนวนเซลล์, ยอดรวมได้  ทุกครั้งที่เราเลือกพื้นที่เซลล์ โดยการตั้งค่าที่ Customize Status Bar (คลิกเม้าส์ขวาที่ Status Bar)

อย่างภาพตัวอย่างนี้ เลือกคลุมพื้นที่ของคอลัมท์ Stock level แถวที่ 15 ถึงแถวที่ 21 เมื่อดูที่ Status Bar แสดงค่าเฉลี่ย (average) เท่ากับ 11.85714286 ซึ่งได้จากผลรวม (sum) ของช่วงเซลล์ที่เลือกซึ่งคือ 83 หารด้วยจำนวนเซลล์ (count) ที่มีข้อมูลคือ 7 เซลล์

วีดีโอการใช้ Subtotal ใน Excel Worksheet จะทำให้เข้าใจง่ายขึ้น