หน้าเว็บ

วันพุธที่ 11 มีนาคม พ.ศ. 2552

การรวมยอดโดยไม่รวมบรรทัดที่ซ่อน

หลาย ๆ ท่านใช้การซ่อนบรรทัด (Hide) ไว้แทนการลบทิ้ง โดยเผื่อไว้ว่าโอกาสหน้าจะกลับมาใช้ จะได้ไม่ต้องคีย์เพิ่มเข้าไปใหม่ แต่หากมีการใช้สูตร Sum เพื่อรวมยอดตัวเลขไว้ ยอดผลรวมนั้นจะรวมบรรทัดที่ซ่อนด้วย

การรวมยอดเพื่อไม่ให้รวมบรรทัดที่ซ่อนไว้สามารถทำได้โดยใช้ฟังก์ชั่น Subtotal ครับ และนอกจากหายอดรวมแล้ว ยังหาค่าเฉลี่ย ค่าน้อยที่สุด ค่ามากที่สุด ค่าเบี่ยงเบนมาตรฐาน การนับเฉพาะตัวเลข การนับทั้งหมดยกเว้นค่าว่าง เป็นต้น

สมมุติข้อมูลอยู่ที่ B2:B50 จะขอยกตัวอย่างบางฟังก์ชั่นที่น่าใช้งานดังนี้ครับ

  1. หากต้องการรวมยอดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
    =Subtotal(109,B2:B50)
  2. หากต้องการนับเฉพาะตัวเลขที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
    =Subtotal(102,B2:B50)
  3. หากต้องการนับทั้งหมดยกเว้นค่าว่างที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
    =Subtotal(103,B2:B50)
  4. หากต้องการหาค่าที่มากที่สุดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
    =Subtotal(104,B2:B50)
  5. หากต้องการหาค่าที่น้อยที่สุดที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
    =Subtotal(105,B2:B50)
  6. หากต้องการหาค่าเฉลี่ยที่ B2:B50 โดยไม่รวมบรรทัดที่ซ่อน ที่ B51 คีย์
    =Subtotal(101,B2:B50

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

101idea กล่าวว่า...

=Subtotal(9,B2:B50)
จะหาผลรวมจากการกรองข้อมูล (Filter)

คนควน กล่าวว่า...

พวกที่เป็นเลขเดี่ยวทั้งหลายจะหาค่าได้ในลักษณะเฉพาะคือจากการ Filter มาเท่านั้นผมจึงไม่ได้กล่าวถึงครับ

ถ้าตั้งใจซ่อนบรรทัดจากการ Filter มาอีกต่อหนึ่ง จะต้องใช้ตามที่ผมแนะนำมา ไม่เช่นนั้นค่าที่ได้จะไม่ถูกต้อง

เช่น Subtotal(9,B2:B50) จะให้ผลจากการ Filter มาถูกต้อง แต่หากเราตั้งใจซ่อนบรรทัด B10:B20 เพื่อตั้งใจจะไม่รวมยอด สูตรข้างต้นจะยังให้ค่าเดิม

การจะให้ค่าที่ถูกต้องโดยไม่รวมบรรทัดที่ตั้งใจซ่อนต้องคีย์สูตร =Subtotal(109,B2:B50)ครับ :)

May_BKK กล่าวว่า...

ดิฉันเข้ามาหาความรู้จากเวปนี้ค่ะ มีประโยชน์ในงานดิฉ้นมากเลย ต้องขอชมเชยด้วยใจจริงว่า "คนควน" เก่งจังเลยนะคะที่ให้ความรู้เรื่องเอ็กเซลล์ได้มากมายขนาดนี้ นับถือจริง ๆ ค่ะ

คนควน กล่าวว่า...

สวัสีดีครับคุณ May_BKK ยินดีที่ช่วยได้ครับ

hunglong กล่าวว่า...

คือผมมีปัญหาว่า แถวข้อมูลในexcel มันซ่อนเองอยู่บ่อย ๆ ควรแก้ไขปัญหานี้ยั้งไงดีครับ พอดีต้องการให้มันโชว์แต่ ลองกด unhide แล้วมันก็ไม่โผล่ออกมา ต้องมาคอยดึงออกทีละแถว ใช้เวลานานครับ พอมีวิธีแก้ไขไหมครับ ขอบคุณครับ

คนควน กล่าวว่า...

ถ้าเลือก Unhide แล้วไม่ แสดงออกมาแสดงว่าไม่ได้ Hide ไว้ก็เป็นได้ครับ

การแก้ไขให้ปรับความกว้างของแถวเสียใหม่ โดย

1. เลือกสี่เหลี่ยมแรกก่อนอักษรคอลัมน์และตัวเลขแถว ซึ่งจะเป็นการเลือกทุกเซลล์ใน Worksheet
2. นำเมาส์ไปวางตรงเส้นแบ่งแถวใด ๆ จนเห็นเมาส์เป็นลูกศร 2 หัวขึ้นบนและลงด้านล่าง
3 ดับเบิ้ลคลิก

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

หรือหลังจากคลุมตามข้อ 1 แล้วสามารถลากด้วยมือเองแทนการดับเบิ้ลคลิกให้มีความสูง 12.75 Point หรือ 17 Pixel ซึ่งเป็นความสูงที่เป็นค่าเริ่มต้นครับ

คนคอน กล่าวว่า...

ขอบคุณคะ

ไม่ระบุชื่อ กล่าวว่า...

ขอบคุณมากๆนะคะสำหรับเทคนิคดีๆแบบนี้..มีประโยชน์ในการทำงานมากเลยค่ะ..^_^

คนควน กล่าวว่า...

ยินดีที่ช่วยได้ครับ

Intarapoj Kennel กล่าวว่า...

แล้วถ้าจะให้นับ A(ตัวอักษร)โดยที่ไม่นับบรรทัดที่ซ่อนจะใช้สูตรไหนครับ เห็นมีสูตรที่ใช้ =Subtotal(109,B2:B50)
ตัวเลขตรงตำแหน่ง 109 คืออะไรครับเห็นมีหลายตัวมันใช้แทนค่าอะไรกันบ้างไม่เข้าใจ

คนควน กล่าวว่า...

การนับอักขระใช้ข้อ 3 คือตัวเลข 103 เลขใดแทนค่าอะไร อธิบายไว้แล้วตามด้านบนครับ

Intarapoj Kennel กล่าวว่า...

ขอบคุณมากครับ เข้ามาตอบเร็วดีกำลังต้องการด่วน

ไม่ระบุชื่อ กล่าวว่า...

ถ้าข้อมูลแยกเป็น size s,m,l แล้วใน s,m,l จะมีจำนวนของมัน แต่ค่าของ s,m,l ไม่เรียงกันคละกัน พอ filter แต่ s แล้ว sum จากนั้นพอปลด fiter ออกผลรวมเปลี่ยน ต้องทำไงค่ะ

Santipong Nasui กล่าวว่า...

สอบถามปัญหาได้ที่ http://www.snasui.com ครับ