RSS

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

11 Mar

หลาย ๆ ท่านใช้การซ่อนบรรทัด (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 Comments

Posted by on 11/03/2009 in Hide, Subtotal, Sum

 

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

  1. 101idea

    20/08/2009 at 8:19 am

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

     
  2. คนควน

    20/08/2009 at 8:45 am

    พวกที่เป็นเลขเดี่ยวทั้งหลายจะหาค่าได้ในลักษณะเฉพาะคือจากการ Filter มาเท่านั้นผมจึงไม่ได้กล่าวถึงครับถ้าตั้งใจซ่อนบรรทัดจากการ Filter มาอีกต่อหนึ่ง จะต้องใช้ตามที่ผมแนะนำมา ไม่เช่นนั้นค่าที่ได้จะไม่ถูกต้องเช่น Subtotal(9,B2:B50) จะให้ผลจากการ Filter มาถูกต้อง แต่หากเราตั้งใจซ่อนบรรทัด B10:B20 เพื่อตั้งใจจะไม่รวมยอด สูตรข้างต้นจะยังให้ค่าเดิมการจะให้ค่าที่ถูกต้องโดยไม่รวมบรรทัดที่ตั้งใจซ่อนต้องคีย์สูตร =Subtotal(109,B2:B50)ครับ🙂

     
  3. May_BKK

    17/09/2009 at 1:26 pm

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

     
  4. คนควน

    18/09/2009 at 6:39 pm

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

     
  5. hunglong

    20/11/2009 at 9:33 am

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

     
  6. คนควน

    21/11/2009 at 11:30 am

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

     
  7. คนคอน

    14/02/2011 at 11:23 am

    ขอบคุณคะ

     
  8. Anonymous

    22/03/2011 at 1:54 pm

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

     
  9. คนควน

    22/03/2011 at 2:06 pm

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

     
  10. Intarapoj Kennel

    13/07/2011 at 2:48 pm

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

     
  11. คนควน

    13/07/2011 at 4:25 pm

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

     
  12. Intarapoj Kennel

    14/07/2011 at 8:06 am

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

     
  13. คนขี้สงสัย

    23/08/2013 at 9:51 am

    ผมลองนำไปใช้กับ office 2007 แล้วใช้ได้ดีมากเลย แต่ผมมีข้อสงสัยอยู่นิดนึงครับ เพราะเวลาที่ใส่สูตรเข้าไป จะมี 2 ตัวเลขที่ดูตาม Function แล้วเหมือนกัน คือ 3 กับ 103 จึงอยากท่จะถามว่าต่างกันอย่างไรครับ

     
    • snasui

      25/08/2013 at 6:42 am

      เลข 3 ใช้สำหรับการซ่อนโดยการ Filter หากซ่อนแล้วจะไม่รวมค่านั้นครับ แต่หากเป็น 103 หมายความรวมถึงจะไม่รวมค่าที่ตั้งใจซ่อนด้วย Manual ด้วย เช่นหากเราต้องการซ่อนบรรทัดใด ๆ ที่ไม่ต้องการด้วยตนเอง เมื่อใช้สูตรเป็น 103 ผลลัพธ์จะไม่รวมถึงบรรทัดที่เราตั้งใจซ่อนด้วย Manual ด้วยครับ ในขณะที่ 3 จะยังรวมบรรทัดที่เราซ่อนด้วย Manual เพราะถือว่าไม่ใช่เป็นค่าทีได้มาจากการ Filter

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: