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



"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

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

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

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

แน่ใจแค่ไหนว่าคุณไม่ตกยุค 2020? ถ้าอ่านลิ้งค์แล้วเข้าใจ คำตอบคือ "คุณใช่!"




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

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

รูปที่ 2
ประโยชน์ของการใช้สูตร Subtotal(109,ช่วงเซล) คือ

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

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

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

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

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

จัดทำโดย:
Thanks for reading.

Posted by: Michael Leng


I've known, then I've grown.





"4 สูตรเอ็กเซล" วิธีการหาผลรวมในรูปแบบที่แตกต่างกันตามความเหมาะสมการใช้งาน แต่ในขณะที่ความแม่นยำเต็มร้อยเปอร์เซ็นต์




เผยเคล็ดลับการใช้สูตร "SUM 4 แบบ" ที่เหมาะกับรายงานที่ท่านต้องการ


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

สำหรับวันนี้ ผมจะนำสูตรมาเขียนให้เพื่อนๆได้นำไปใช้กันซัก 4 สูตรด้วยกันครับ

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

  1. สูตรการหาผลรวมแบบการบวกเพิ่มทีละเซลล์
  2. สูตรรการหาผลรวมแบบการบวกแบบระบุช่วงเซลล์
  3. สูตรการหาผลรวมแบบการบวกแบบระบุช่วงเซลล์มากกว่า 1 ช่วง
  4. สูตรการหาผลรวมแบบการบวกแบบผสมผสาน

1) สูตรการหาผลรวมแบบการบวกเพิ่มทีละเซลล์

สูตรนี้เป็นการหาค่าผลการรวมค่าโดยจะบวกค่าต่างๆทีละเซลล์เรียงกันไปจากเซลล์ เช่น จาก เซลล์ E1 บวก E2 บวก E3...

สูตรคือ =SUM(E1, E2, E3...)



ต.ย.การบวกแบบเพิ่มทีละเซลล์

2) สูตรรการหาผลรวมแบบการบวกแบบระบุช่วงเซลล์

สูตรการบวกค่าต่างๆโดยการะบุช่วงเซลล์ คือการใส่ช่วงของค่าที่อยู่ในแถว(Row)หรือคอลัมน์(Column)

สูตรระบุช่วงแถว(Row)คือ 

=SUM(Begining Cell in a row : Ending Cell in a row)


ต.ย.การบวกแบบระบุช่วงเซลล์ที่อยู่ในแถว(Row)

สูตรระบุช่วงคอลัมน์(Column)คือ 

=SUM(Begining Cell column : Ending Cell in a column)


ต.ย.การบวกแบบระบุช่วงเซลล์ที่อยู่ในคอลัมน์(Column)

ของฝากจากผู้เขียน   Michal Leng คลิกเพื่อดูประวัติ(ภาษาอังกฤษ)
แน่ใจนะว่าลิ้งค์พวกนี้เป็นเพียง 🔺เอามะพร้าวห้าวมาขายสวน🔺 ก็เท่านั้น!"




3) สูตรการหาผลรวมแบบการบวกแบบระบุช่วงเซลล์มากกว่า 1 ช่วง

วิธีนี้คือการหาค่ารวมของช่วงคอลัมน์ใดๆรวมกับช่วงในคอลัมน์อื่นๆหรือแถวอื่นๆตามต้องการ

สูตรระบุช่วงคอลัมน์(Column)คือ 

=SUM(Column 1 cell 1, column 1 cell 2..., Column 2 cell 1, Column 2 cell 2,...)


ต.ย.การบวกแบบระบุช่วงเซลล์ที่อยู่ในคอลัมน์(Column)ใดๆและบวกคอลัมน์อื่นๆ

=SUM(Column 1 cell 1, column 1 cell 2,..., Row 1 cell 1, Row 1 cell 2,...)


ต.ย.การบวกแบบระบุช่วงเซลล์ที่อยู่ในแถว(Row)ใดๆและบวกแถวกับแถวอื่นๆ

4) สูตรการหาผลรวมแบบการบวกแบบผสมผสาน

 คุณสามารถนำสูตรทั้ง 3 ข้อด้านบนที่กล่าวมานั้น เอามาใช้แบบผสมผสานกันได้ทั้งหมดเลยครับ

สูตรการรวมค่าของเซลล์ต่างๆแบบใช้สูตรผสมผสานกัน คือ 

=SUM(Column 1 cell 1, column 1 cell 2,..., Row 1 cell 1: Row 1 cell...,Column 1 cell 1: Column 1 cell...)


ต.ย.การบวกแบบใช้สูตรต่างๆผสมผสานกัน
มาถึงช่วงท้ายที่เราจะต้องกล่าวคำว่า ขอลาไปก่อนแล้วล่ะครับ หวังเป็นยิ่งนักว่าสูตรหรือทิคนิคการบวกหรือการหาผลรวมแบบต่างๆทั้งหมด 4 แบบหลักๆในวันนี้จะเป็นประโยชน์ต่อท่านที่สนใจว่าจะทำอย่างไรให้ชีวิตในการทำงานในออฟฟิสที่มันแสนจะเร่งรีบและงานค่อนข้างจะเยอะให้มันง่ายขึ้น ใช้เวลาน้อยลง แต่ความแม่นยำร้อยเปอร์เซ็นต์(100 % Accuracy)

โปรดติดตามเคล็ดลับดีๆที่จะช่วยให้ชีวิตการทำงานในออฟฟิสของง่ายขึ้นด้วย บล็อก เผยเคล็ดลับเอ็กเซล : XCEL-GURU ที่จะนำเคล็ดลับดีๆมาฝากกันที่นี่เป็นประจำครับ

Thanks for reading.

Posted by: Michael Leng



I've known, then I've grown.