ก่อนจะอธิบายสูตร SubTotal หลายคนอาจจะสงสัยว่าทำไมจึงไม่อธิบายวิธีใช้สูตร Sum ไว้ก่อน ทั้งนี้ไม่ใช่เพราะสูตร Sum เป็นสูตรง่ายที่ทุกคนควรรู้จักกันดีอยู่แล้วหรอก แต่เนื่องจาก Sum เป็นสูตรที่เหมาะสำหรับใช้หายอดรวมจากตารางซึ่งมีโครงสร้างแน่นอนถาวร และในอนาคตต้องไม่มีการเปลี่ยนแปลงโครงสร้างอีกต่อไป
ในภาพนี้ เซลล์ B12 หายอดรวมของเซลล์ B2:B11 ด้วยสูตร =SUM(B2:B11) ได้คำตอบยอดรวมเท่ากับ 55
หากต่อมามีการปรับโครงสร้างตาราง โดยกำหนดให้หายอดรวมของเซลล์ B2:B6 เพิ่ม และให้แทรก row ใหม่ระหว่าง row 6 และ row 7 แล้วสร้างสูตร =SUM(B2:B6) ลงไปในเซลล์ B7 ได้ยอดรวมเท่ากับ 15 แต่จะส่งผลให้สูตร Sum เดิมที่หายอดรวมทั้งหมดเท่ากับ 55 กลายเป็นยอดรวมใหม่เท่ากับ 77 ซึ่งเป็นคำตอบที่ผิดเนื่องจากนำยอดรวมของ Sum ในเซลล์ B7 ไปรวมเข้าไปอีก
แทนที่จะเลือกใช้สูตร Sum ตั้งแต่แรก ถ้าเปลี่ยน =SUM(B2:B11) ไปสร้างสูตรบวกแต่ละเซลล์เข้าด้วยกันเป็น =B2+B3+B4+B5+B6+B7+B8+B9+B10+B11 แล้วหากภายหลังมีการแทรก row และใส่ค่าใหม่เพิ่มเข้าไปในเซลล์เช่นที่ยกตัวอย่างข้างต้น ก็จะไม่เกิดปัญหาทำให้สูตรบวกคำนวณค่าผิดพลาด เพราะสูตรบวกยังคงเลือกเฉพาะเซลล์ตามเดิมที่กำหนดไว้ให้เสมอ แต่สูตรบวกกันทีละเซลล์ไม่ใช่ทางออกที่ดีนักหากมีเซลล์ที่ต้องการนำมาบวกกันนับพันนับหมื่นเซลล์ วิธีที่ดีกว่าการใช้สูตรบวกหรือสูตร Sum ก็คือ การใช้สูตร SubTotal
การใช้สูตร SubTotal ร่วมกับคำสั่ง Filter และ Custom Views
หากแปลคำว่า SubTotal กันตรงๆอาจเข้าใจว่าสูตร SubTotal นี้ทำหน้าที่หายอดรวม Total ของรายการแต่ละกลุ่มที่ Sub เอาไว้ ซึ่งก็แปลงได้ไม่ผิดนัก แต่สูตรนี้มิได้หาคำตอบให้เฉพาะยอดรวมเท่านั้น หากยังสามารถดัดแปลงให้คำนวณหาค่าเฉลี่ยหรือนับได้อีกด้วย โดยเลือกกำหนดให้สูตร SubTotal คำนวณหายอดตัวเลขได้ถึง 11 แบบตามโครงสร้างสูตรต่อไปนี้
=SubTotal( ตัวเลขประเภทการคำนวณ, พื้นที่เซลล์ )
ตัวเลขประเภทการคำนวณ กำหนดตัวเลขตั้งแต่เลข 101 – 111 แต่เลขที่ใช้กันทั่วไปได้แก่
- 101 กำหนดให้ SubTotal ทำหน้าที่แบบสูตร Average
- 102 กำหนดให้ SubTotal ทำหน้าที่แบบสูตร Count
- 103 กำหนดให้ SubTotal ทำหน้าที่แบบสูตร CountA
- 109 กำหนดให้ SubTotal ทำหน้าที่แบบสูตร Sum
SubTotal มีความสามารถพิเศษเหนือกว่าสูตร Sum กล่าวคือ SubTotal เป็นสูตรที่จะไม่นำค่าจากเซลล์ที่มีสูตร SubTotal มาคำนวณซ้ำอีก
สังเกตว่าในเซลล์ B7 และ B13 ใช้สูตร SubTotal เพื่อหายอดรวมของตัวเลขจากเซลล์ B2:B6 และ B2:B12 ตามลำดับ โดยในเซลล์ B13 ใช้สูตร =SUBTOTAL(109,B2:B12) แม้ว่าได้กำหนดขอบเขตของตารางที่ต้องการหายอดรวมไว้ตั้งแต่เซลล์ B2:B12 ซึ่งรวมถึงเซลล์ B7 ที่มียอดตัวเลขรวมจากเซลล์ B2:B6 ไว้ก็ตาม สูตร SubTotal ในเซลล์ B13 จะละเลยไม่นำค่าในเซลล์ B7 มาคำนวณซ้ำอีก ทำให้ยังคงได้คำตอบเท่ากับ 55
ถ้านำสูตร SubTotal ไปใช้ร่วมกับตารางที่ถูกซ่อน row/column hide หรือกรองจากคำสั่ง Data > Filter จะพบว่า สูตร SubTotal แสดงตัวเลขคำตอบเฉพาะที่ได้มาจากเซลล์ที่ยังมองเห็นตัวเซลล์อยู่เท่านั้น (Visible Cells Only) โดยตัวสูตรเองไม่ได้ต้องเปลี่ยนแปลงใหม่แต่อย่างใด
(Excel รุ่นเก่ากว่า Excel 2003 จะรับตัวเลขประเภทการคำนวณตั้งแต่เลข 1 - 11 และใช้ร่วมกับการ Filter เท่านั้น แต่ถ้าใช้ Excel 2003 เป็นต้นมาแนะนำให้ใช้ตัวเลข 101 – 111 แทน จะสามารถใช้ร่วมกับ Filter หรือจะซ่อน row/column เองได้อีกด้วย)
หลังจากที่กรองหรือจัดโครงสร้างตารางเพื่อแสดงผลให้เห็นบนจอตามต้องการได้แล้ว ควรใช้คำสั่ง View > Custom Views > Add เพื่อตั้งชื่อให้กับการแสดงผลตามที่จัดไว้
จากนั้นเมื่อต้องการแสดงโครงสร้างตารางตามแบบใด ให้สั่ง View > Custom Views แล้วคลิกเลือกชื่อแบบที่ตั้งไว้ในช่อง Views แล้วกดปุ่ม Show จะได้โครงสร้างตารางตามแบบที่ตั้งชื่อไว้พร้อมกับสูตร SubTotal แสดงตัวเลขคำตอบเฉพาะเซลล์ที่ยังมองเห็นให้พร้อมกันไป (นอกจากนั้น Custom Views ยังช่วยจำการกำหนด Print Setting แต่ละแบบให้อีกด้วย)
