SUBTOTAL(109,ช่วงเซล) สูตรการหาผลรวมแบบไม่รวมค่าของเซลล์ที่ถูกซ่อนไว้(Visible Cells Only) | Xcel-GURU



"SUBTOTAL(109,ช่วงเซล)" สูตรนี้ใช้อย่างไร


สวัสดีครับ ผมชื่อเล้งครับ วันนี้ผมจะนำเคล็ดลับการใช้งานโปรแกรมเอ็กเซล(Excel program) ที่มีประโยชน์มา share ให้ท่านที่สนใจได้รับความรู้และเอาไปใช้ในชีวิตการทำงานประจำวันกัน โดยท่านสามารถเข้ามาอ่านได้ที่บล็อก "เผยเคล็ดลับเอ็กเซล : XCEL-GURU"  ได้ตลอดเวลาที่นี่ครับ

Xcel Tip Quiz


ท่านทราบหรือไม่ว่าการลบขนาดไฟล์เอ็กเซลทำอย่างไร?


และสำหรับวันนี้ผมจะเสนอเคล็ดลับเอ็กเซล ..."SUBTOTAL(109,ช่วงเซล)"... ครับ ซึ่งท่านจะได้สิ่งที่ดี คุ้มค่ากับเวลาที่อ่านบล็อกของเราที่นำมาแนะนำในวันนี้อย่างแน่นอน และที่สำคัญ ท่านสามารถทดลองทำตามเพื่อจะได้เห็นประโยชน์ในการใช้งานได้ในทันทีทันใดครับ

สูตรเอ็กเซลที่นำมาบอกเล่าเก้าสิบกันในวันนี้ ผมรับรองว่ามันดีมากสำหรับคนทำงานที่ใช้เอ็กเซลอยู่เป็นประจำ แต่ท่านอาจจะยังไม่ทราบมากก่อนว่าในการหาผลรวมขอจำนวนต่างๆที่ท่านมีข้อมูลอยู่ใน worksheet ของท่านนั้นมันสามารถทำกันได้มากมายหลายแบบครับ

=SUBOTAL(109,range)


หรือแปลเป็นภาษาไทยได้ว่า

=SUBTOTAL(109,ช่วงเซล)


หรืออ้างอิงจากสูตรต้นฉบับของเอ็กเซล

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


สูตรการหาผลรวม

สูตรการหาผลรวมแบบเฉพาะเซล์ที่มองเห็นโดยจะไม่รวมเซลล์ที่ถูกซ่อนไว้(Ignores hidden values)แปลได้ว่า สูตรจะคำนวณเฉพาะค่าตัวเลขที่ปรากฏให้เห็นเท่านั้นครับ(Visible Cells Only) ซึ่งจะสูตรที่เหมาะกับผู้ที่ใช้งาน function "Filter" ครับ

ตัวอย่างการแทนค่าในสูตร

=SUBTOTAL(109, E3:E14)


ตัวอย่างตามรูปที่ 1

ดังตัวอย่างจะเห็นว่าผมใช้สูตร =SUBTOTAL(109, cell แรก : cell หลัง) กับทุก column ในตารางคำนวณ คือ C, D, และ E

ผลลัพธ์ของสูตรในรูปที่แรกนี้ท่านจะเห็นว่า ยอดจะเป็นการรวมกันตามปกติ คือยอดตั้งแต่เดือน มกราคม ถึงเดือน ธันวาคม

สรุปว่า ผมลัพธ์ตามรูปที่ 1 คือ C1= 173,870 / D1=26,948 / E1=146,922 จะเหมือนกับการใช้สูตร SUM ปกติทั่วๆไปครับ

แสดงการใช้ปุ่มคีย์ลัด SUBTOTAL(109,ช่วงเซล)
รูปที่ 1 
ตัวอย่างตามรูปที่ 2

ตัวอย่างในรูปที่ 2 นี้ ผมจะใช้ข้อมูลเหมือนกันกับรูปจากตารางแรกด้านบนครับ แต่ผมจะใช้ function filter ร่วมด้วยซึ่งจะทำให้ท่านเห็นความแตกต่างระหว่างรูปแรกกับรูปที่สองครับ

ในตารางของรูปที่ 2 ผมใช้ filter กรองเอาจำนวนที่เป็น 0(ศูนย์) ในคอลัมน์ E ออกครับ ผลกระทบที่เกิดขึ้นคือ ยอดในคอลัมน์ C10(ยอด 555) และ D10(ยอด 555) ก็จะถูกซ่อน(Hide)ไปด้วยโดยอัตโนมัติ

ดังนี้ท่านจะเป็นการแสดงค่ารวมยอดที่แตกต่างไปจากเดิม นี่คือผลของการใช้สูตร =SUBTOTAL(109, cell แรก : cell หลัง) ครับ

สรุปว่า ผมลัพธ์ตามรูปที่ 2 คือ C1= 172,328 / D1=25,406 ส่วน E1 จะยังคงเหลือยอดเท่าเดิมคือ 146,922 ครับ

การที่ยอดรวมของคอลัมน์ C1 และ D1 ลดลงไปจำนวนเซลล์ละ 555 ก็เพราะว่า cell c10 และ d10 ถูกซ่อนไว้ ดังนั้นสูตรของ SUBTOTAL(109,ช่วงเซล)" จะไม่นับเซลล์ที่ถูกซ่อนดังตัวอย่างที่นำมาเสนอกันดังรูปที่ 2 นั่นเองครับ

SUBTOTAL(109,ช่วงเซล) ใช้อย่างไรมีภาพประกอบให้ดูเป็นตัวอย่าง
รูปที่ 2
ประโยชน์ของการใช้สูตร Subtotal(109,ช่วงเซล) คือ

เป็นการหาค่ารวมของจำนวนโดยไม่รวมจำนวนที่เราต้องการซ่อน เช่น ใช้ function "filter"(กรองข้อมูล) และ "hide"(ซ่อนข้อมูล)

วันนี้หวังว่าสูตรที่นำมาฝากท่านคงจะถูกอกถูกใจและท่านสามารถนำไปใช้กับงานในออฟฟิสของท่านได้นะครับ

เทคนิคต่างๆนั้นบางครั้งมันก็ได้มาจากการพลิกแพลงหรือว่าการที่เราคิดต่อยอดจากสูตรที่เขาได้สร้างไว้เดิมๆอยู่แล้วครับ

เรายังมีสูตรหรือว่าเคล็ดลับการใช้งานเอ็กเซลอีกมากมายครับ ขอให้ท่านคอยติดตามเข้ามาอ่าน ติชม หรือว่าบอกความต้องการที่มันตรงกับการใช้งานของท่านมายังเราครับ เราจะค้นคว้าหาคำตอบมาให้ท่านอย่างแน่นอนครับ

Xcel Tip Answer

 a. ทำได้โดยการลบ Workbook

 b. ทำโดยการลบ Worksheet

 c. ทำได้ด้วยการลบ Hide sheet

ลิ้งค์แนะนำ👉 : พูดภาษาอังกฤษเป็นด่วน! : English Fast Serve!
ลิ้งค์แนะนำ👉 : นับเลขภาษาอังกฤษจาก 0 ถึง 1,000,000

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

อีกทั้งยังจะช่วยให้ท่านได้นำไปต่อยอดหรือเติมเต็มการใช้งานในแวดวงที่ใช้โปรแกรมเอ็กเซลร่วมอยู่ด้วยได้เป็นอย่างดี...

หากมีสิ่งขาดตก บกพร่องอย่างใด ต้องขออภัยมา ณ โอกาสนี้ด้วยนะครับ

และถ้าสิ่งที่ท่านได้รับในวันนี้ ท่านเห็นดีและมีประโยชน์ ผมความกรุณาให้ท่านช่วย share เพื่อมอบสิ่งดีๆเหล่านี้ให้กับคนที่ท่านรู้จักด้วยจักเป็นพระคุณอย่างยิ่งครับ

ผมขอขอบคุณมากครับที่ติดตามเรา

สำหรับวันนี้ ผมต้องขอกล่าวคำว่า ขอบคุณ ที่ติดตามเวปของเรา เป็น fan club กันไปให้ตลอดนะครับ

Thanks for reading.

Posted by: Michael Leng



I've known, then I've grown.


2 ความคิดเห็น:

  1. ไม่ระบุชื่อ25 กรกฎาคม 2561 11:09

    เอ็กเซลเป็นอะไรที่ใช้ได้ครอบคลุมดีมากครับ ต้องขอขอบคุณทีึ่เอา tips ดีๆมากฝากนะครับ

    ตอบลบ