ก่อนจะอธิบายสูตร SubTotal หลายคนอาจจะสงสัยว่าทำไมจึงไม่อธิบายวิธีใช้สูตร Sum ไว้ก่อน ทั้งนี้ไม่ใช่เพราะสูตร Sum เป็นสูตรง่ายที่ทุกคนควรรู้จักกันดีอยู่แล้วหรอก แต่เนื่องจาก Sum เป็นสูตรที่เหมาะสำหรับใช้หายอดรวมจากตารางซึ่งมีโครงสร้างแน่นอนถาวร และในอนาคตต้องไม่มีการเปลี่ยนแปลงโครงสร้างอีกต่อไป

image042

ในภาพนี้ เซลล์ B12 หายอดรวมของเซลล์ B2:B11 ด้วยสูตร =SUM(B2:B11) ได้คำตอบยอดรวมเท่ากับ 55

หากต่อมามีการปรับโครงสร้างตาราง โดยกำหนดให้หายอดรวมของเซลล์ B2:B6 เพิ่ม และให้แทรก row ใหม่ระหว่าง row 6 และ row 7 แล้วสร้างสูตร =SUM(B2:B6) ลงไปในเซลล์ B7 ได้ยอดรวมเท่ากับ 15 แต่จะส่งผลให้สูตร Sum เดิมที่หายอดรวมทั้งหมดเท่ากับ 55 กลายเป็นยอดรวมใหม่เท่ากับ 77 ซึ่งเป็นคำตอบที่ผิดเนื่องจากนำยอดรวมของ Sum ในเซลล์ B7 ไปรวมเข้าไปอีก

image043

แทนที่จะเลือกใช้สูตร 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 มาคำนวณซ้ำอีก

image044

สังเกตว่าในเซลล์ 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 เองได้อีกด้วย)

image045

หลังจากที่กรองหรือจัดโครงสร้างตารางเพื่อแสดงผลให้เห็นบนจอตามต้องการได้แล้ว ควรใช้คำสั่ง View > Custom Views > Add เพื่อตั้งชื่อให้กับการแสดงผลตามที่จัดไว้

image046

จากนั้นเมื่อต้องการแสดงโครงสร้างตารางตามแบบใด ให้สั่ง View > Custom Views แล้วคลิกเลือกชื่อแบบที่ตั้งไว้ในช่อง Views แล้วกดปุ่ม Show จะได้โครงสร้างตารางตามแบบที่ตั้งชื่อไว้พร้อมกับสูตร SubTotal แสดงตัวเลขคำตอบเฉพาะเซลล์ที่ยังมองเห็นให้พร้อมกันไป (นอกจากนั้น Custom Views ยังช่วยจำการกำหนด Print Setting แต่ละแบบให้อีกด้วย)

Author: สมเกียรติ ฟุ้งเกียรติEmail: This email address is being protected from spambots. You need JavaScript enabled to view it.

E-Learning

Go to top