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 ปกติทั่วๆไปครับ
| รูปที่ 1 |
ตัวอย่างในรูปที่ 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 นั่นเองครับ
| รูปที่ 2 |
เป็นการหาค่ารวมของจำนวนโดยไม่รวมจำนวนที่เราต้องการซ่อน เช่น ใช้ function "filter"(กรองข้อมูล) และ "hide"(ซ่อนข้อมูล)
วันนี้หวังว่าสูตรที่นำมาฝากท่านคงจะถูกอกถูกใจและท่านสามารถนำไปใช้กับงานในออฟฟิสของท่านได้นะครับ
เทคนิคต่างๆนั้นบางครั้งมันก็ได้มาจากการพลิกแพลงหรือว่าการที่เราคิดต่อยอดจากสูตรที่เขาได้สร้างไว้เดิมๆอยู่แล้วครับ
เรายังมีสูตรหรือว่าเคล็ดลับการใช้งานเอ็กเซลอีกมากมายครับ ขอให้ท่านคอยติดตามเข้ามาอ่าน ติชม หรือว่าบอกความต้องการที่มันตรงกับการใช้งานของท่านมายังเราครับ เราจะค้นคว้าหาคำตอบมาให้ท่านอย่างแน่นอนครับ
Xcel Tip Answer
ลิ้งค์แนะนำ👉 : นับเลขภาษาอังกฤษจาก 0 ถึง 1,000,000
สุดท้ายนี้ ผมหวังว่าสิ่งที่เราสืบค้น เลือกสรรค์ และทดลองใช้ก็ดี สิ่งที่ทางเรามีภูมิความรู้ด้านนี้อยู่แล้วก็ดี ซึ่งเราได้นำมาเขียนเพื่อ share ให้กับท่านในวันนี้นั้น จะมีประโยชน์ต่อธุรกิจ กิจการงานต่อหน่วยงานของท่าน หรือว่าต่อตัวท่านเองไม่มากก็น้อยนะครับ
ชอบช่วย share ให้มากๆเลยนะครับ
ขอขอบคุณมากครับที่ติดตามเรา
สำหรับวันนี้ ผมต้องขอกล่าวคำว่า ขอบคุณ ที่ติดตามเวปของเรา เป็น fan club กันไปให้ตลอดนะครับ
Thanks for reading.
Posted by: Michael Leng
I've known, then I've grown.
- "4 สูตรเอ็กเซล" วิธีการหาผลรวมในรูปแบบที่แตกต่างกันตามความเหมาะสมการใช้งาน แต่ในขณะที่ความแม่นยำเต็มร้อยเปอร์เซ็น
- Custom Search Search "4 สูตรเอ็กเซล" วิธีการหาผลรวมในรูปแบบที่แตกต่างกันตามความเหมาะสมการใช้งาน แต่ในขณะที่ความแม่นยำเต็มร้อยเปอร์เซ็น
- Custom Search Search 3 ขั้นตอนการลบช่องว่าง(Space)ทั้งหมด(ด้านหน้า ระหว่างอักษร ด้านหลัง)ในเอ็กเซลโดย"ไม่"ใช้สูตร | xcel-guru
เอ็กเซลเป็นอะไรที่ใช้ได้ครอบคลุมดีมากครับ ต้องขอขอบคุณทีึ่เอา tips ดีๆมากฝากนะครับ
ตอบลบด้วยความยินดีครับ
ลบ