2 วิธีใช้สร้างผลรวมย่อย (Subtotal) ในโปรแกรม Excel

ฟังก์ชั่นในโปรแกรม Excel หนึ่งที่หลายคนรู้จักคงจะไม่พ้นฟังก์ชั่นที่เกี่ยวกับการหาผลรวมแน่นอน ซึ่งก่อนหน้านี้ เราได้อธิบายอย่าง การหาผลรวมด้วย SUM() / SUMIF() / SUMIFS ใน Excel หรือ SUBPRODUCT() ใช้ในการหาผลรวมของคอลัมน์ตั้งแต่ 2 คอลัมน์คูณกัน 

ในบทความนี้ เราเสนอตัวอย่างการนำ SUBTOTAL Function มาช่วยลดขั้นตอนการทำงานของคุณ ๆ ว่าแล้วเข้าเรื่องสักที่ดีกว่านะคะ 😉😉😉

SUBTOTAL Function
หมายถึง ฟังก์ชั่นที่ช่วยคำนวณเซลล์ย่อย ๆ ของผลรวมในตาราง โดยมีการทำอยู่ 2 แบบ ดังนี้

ไวยากรณ์

=Subtotal(function_num, ref1, [ref2], ...)

หลักการ

• function_num คือ คุณต้องระบุตัวเลข ซึ่งมี 2 แบบ คือ 1-11 และ 101-111 เพื่อคืนค่าตาม Function ที่ต้องการ โดยเงื่อนไขในการกำหนดอยู่ในตารางที่ 1 
• ref1, [ref2], ... คือ คุณระบุเลข 1-254 การอ้างอิงเพื่อให้ฟังก์ชั่นหาผลรวมย่อยของข้อมูล

ตารางที่ 1 แสดงความหมายของตัวเลขใน Function_num 

Function	คำนวณค่าที่ซ่อน	ไม่คำนวณค่าที่ซ่อน
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
STDEV	7	107
STDEVP	8	108
SUM	9	109
VAR	10	110
VARP	11	111

หมายเหตุ

• ในตาราง Function แต่ละเลข (เช่น 1 กับ 101 / 2 กับ 102 / ...) จะถูกจับคู่ไว้อยู่แล้ว เมื่อคุณเลือก
• Function_num เป็นค่าระหว่าง 1-11 ฟังก์ชั่น SUBTOTAL จะคำนวณตัวเลขทุกตัว แม้ว่าตัวเลขจะถูกซ่อนไว้
• Function_num เป็นค่าระหว่าง 101-111 ฟังก์ชั่น SUBTOTAL จะ ไม่ นำตัวเลขที่ถูกซ่อนไว้มาคำนวณ
• แต่ถ้าคุณทำการกรองข้อมูลด้วย Autofilter แล้วฟังก์ชั่น SUBTOTAL จะไม่สนใจว่าคุณกำหนด Function_Num เป็นอะไร มันจะ ไม่ นำค่าในตารางที่คุณกรองข้อมูลนั้นมาคำนวณเด็ดขาด
• ฟังก์ชั่น SUBTOTAL จะสามารถทำงานตามที่บอกไว้กับข้อมูลแนวตั้ง แต่ถ้าข้อมูลเป็นลักษณะแนวนอน (Horizontal) ฟังก์ชั่น SUBTOTAL จะนำค่าที่ซ่อนไว้มารวมด้วย

ตัวอย่างที่ 1 เขียนฟังก์ชั่นแบบ Command ดังนี้

รูปที่ 1 แสดงตัวอย่าง Subtotal 

จากรูปที่ 1 ถ้าเขียน =SUBTOTAL(9,B1:B3) หรือ =SUBTOTAL(109,B1:B3) จะได้ผลลัพธ์เป็น 9 ทั้ง 2 ฟังก์ชั่น และมีความหมายว่าให้ทำการบวกค่าระหว่าง B1 ถึง B3 แต่ถ้าคุณซ่อน Row 2 ไป ผลลัพธ์ที่ได้จะเป็น 9 และ 5 ตามลำดับ 

แบบที่ 2 ใช้ Subtotal แบบ Outline ดังนี้

รูปที่ 2 แสดงการทำ Subtotal แบบ Outline

ในการทำ Subtotal แบบ Outline จะช่วยให้คุณไม่ต้องมาเขียนสูตรที่ละส่วน แต่การจะใช้เครื่องมือตัวนี้ เราต้องมารู้ก่อนว่ามันมีคุณลักษณะการทำงานอย่างไรบ้าง 

ขั้นตอนการทำงาน Subtotal แบบ Outline
1. ก่อนจะใช้เครื่องมือ Subtotal ได้ประสิทธิภาพที่สุด คุณควรจัดเตรียมข้อมูลให้โปรแกรมสามารถจัดการบริหารกับข้อมูลได้ง่ายขึ้นนั้นก็คือ ต้องใส่ข้อมูลให้เต็มทุกเซลล์ในตาราง / ทำการจัดเรียง (Sort) ข้อมูล ที่จะทำ Subtotal แบบ Outline ดังรูปที่ 3

รูปที่ 3 แสดงการเตรียมข้อมูลให้เหมาะสมกับการทำ Subtotal แบบ Outline

ตามรูปที่ 3 เราได้ทำการเติมข้อมูลให้เต็มและจัดเรียงข้อมูล ในคอลัมน์ปี (Year) นั้นเอง

2. เลือกเซลล์ส่วนใดส่วนหนึ่งบนตาราง และ เลือกที่ Data >> Subtotal โปรแกรมจะแสดง PopUp ดังรูปที่ 4

รูปที่ 4 แสดง Subtotal outline 

แต่ละส่วนของ Subtotal Popup มีดังนี้

• At each change in: เป็นการเลือกคอลัมน์คุม ตารางที่ทำ Subtotal 
• Use Function: เป็นการเลือกว่าจะใช้ Function ใดในการทำ Subtotal 
• Add subtotal to : เป็นการเลือกคอลัมน์ที่ต้องการให้คำนวณ ตาม Function ที่เลือกไว้ 
• Replace current subtotal เป็นการกำหนดให้สามารถเพิ่มผลรวมย่อยได้ โดยจะถูกใช้เมื่อคุณทำ Subtotal ไปก่อนหน้านี้แล้ว แต่ต้องการจะเพิ่ม Subtotal กับคอลัมน์อื่นเพิ่ม โดยไม่ให้กระทบกับข้อมูลที่ทำ Subtotal ไว้ก่อนหน้านี้นั้นเอง
• Page break between groups เป็นการกำหนดให้แบ่งเป็นหน้า
• Summary below data เป็นการกำหนดให้แสดงผลสรุปไว้ท้ายของตาราง

โดยในรูปที่ 4 คือการเลือกคอลัมน์ปี (Year) เป็นคอลัมน์คุมการแสดงผลรวมย่อย (Subtotal) ซึ่งจากการเติมข้อมูลให้เต็มทั้งตารางและจัดเรียงข้อมูลไว้ก่อนหน้านี้แล้ว คุณจะได้ข้อมูลผลรวมย่อยที่นำไปวิเคราะห์ได้ ดังรูปที่ 5

รูปที่ 5 แสดงผลลัพธ์การทำผลรวมย่อย (Subtotal - Outline)

แต่หากคุณขี้เกียจ 😝😝😝 ไม่เติมข้อมูลให้เต็มหรือจัดเรียงข้อมูลให้เรียบร้อยก่อน คุณอาจจะได้ผลรวมย่อย (Subtotal) หลายรายการต่อข้อมูลในคอลัมน์เดียวกัน ซึ่งคุณก็คงต้องเริ่มต้นทำใหม่ ซึ่งถ้าคุณต้องการเริ่มต้นใหม่สามารถคลิกที่ปุ่ม Remove all ได้

นอกจากนี้ คุณยังสามารถทำการเพิ่ม Subtotal ที่ 2 ในตารางได้ โดยคลิกที่ Replace current subtotal แล้วเลือกคอลัมน์ที่ต้องการจะทำ Subtotal ที่ 2 ในส่วน Add subtotal to ดังรูปที่ 6 

รูปที่ 6 แสดงตัวอย่างการทำผลรวมย่อยที่2 (Subtotal แบบ outline) 

โดยในรูปที่ 6 จะทำการคลิกที่ Replace current subtotal และเลือกส่วนของ Add subtotal to: Product ผลลัพธ์ที่ได้เพิ่ม Subtotal ที่ 2 จะแสดงดังรูปที่ 7

รูปที่ 7 แสดงผลลัพธ์ของการทำผลรวมย่อยที่ 2 (Subtotal แบบ Outline)

หวังว่าบทความนี้ คงพอจะให้คุณสามารถนำไปใช้สรุปผลรวมย่อยกับงานของคุณ ๆ ได้อย่างดีนะคะ 

Read More