เพื่อมุ่งให้เกิดคุณภาพจากการอบรมสูงสุด Excel Expert Training ให้การอบรม Excel กลุ่มเล็กๆ ไม่เกิน 6 คนทุกคนสามารถเรียนรู้ Excel อย่างใกล้ชิด จะมาคนเดียวหรือมาเป็นกลุ่มแล้วนัดวันอบรมแบบส่วนตัวก็ได้ ผู้เข้าอบรมทุกคนสามารถติดตามเนื้อหาที่อบรมได้อย่างชัดเจนจากจอภาพด้านหน้าของตัวเอง
Results 1 to 8 of 8

Thread: จุดอ่อนของ SumProduct

  1. #1
    สมเกียรติ
    Guest

    จุดอ่อนของ SumProduct

    SumProduct เป็นสูตรที่ผมพบจุดอ่อนว่า ในบางครั้งหากสร้างสูตรลิงค์หายอดรวมข้ามแฟ้ม จะหายอดรวมไม่ได้หากแฟ้มต้นทางไม่ได้เปิดพร้อมกับแฟ้มปลายทาง

    คราวนี้พบจุดอ่อนอีกอย่างหนึ่ง (หรือผมพลาดตรงไหนก็ไม่ทราบ) ทำไมสูตร SumProduct จึงทำงานไม่ได้ตามต้องการ

    ตามตัวอย่างแนบนี้ โดยทั่วไปสูตรหายอดรวมของชื่อที่กำหนด จะใช้กันตามนี้ใช่ไหมครับ
    =SUMPRODUCT((Name=$F$3)*Amount)
    =SUMPRODUCT(--(Name=$F$3),Amount)

    โดยทั่วไป ในกรณีที่ไม่ได้ใส่ชื่อไว้ในเซลล์ F3 ย่อมหายอดรวมเท่ากับ 0

    แต่คราวนี้ ผมต้องการปรับสูตรให้ใช้เงื่อนไขใหม่ว่า
    ถ้าไม่ได้ใส่ชื่อไว้ในเซลล์ F3
    ให้หายอดรวมทั้งหมดมาใช้แทน

    พบว่าสูตร SumProduct ต่อไปนี้หาค่าไม่ได้ เมื่อมีชื่อกรอกไว้ในเซลล์ F3 แต่จะหายอดรวมทั้งหมดได้ถ้าลบชื่อใน F3 ทิ้ง
    =SUMPRODUCT(IF(ISBLANK($F$3),1,(Name=$F$3))*Amount)
    =SUMPRODUCT(IF(ISBLANK($F$3),1,(Name=$F$3)),Amount)
    =SUMPRODUCT(IF($F$3=0,1,(Name=$F$3))*Amount)
    =SUMPRODUCT(1*IF(ISBLANK($F$3),1,(Name=$F$3)),Amount)

    ส่วนสูตร SumIF Array ต่อไปนี้จะทำงานได้ถูกต้องเสมอ คือ เมื่อกำหนดชื่อในเซลล์ F3 ก็จะหายอดของเฉพาะชื่อนั้น
    หรือเมื่อลบชื่อทิ้ง ก็จะหายอดรวมทั้งหมดมาให้ครับ
    {=SUM(IF(ISBLANK($F$3),1,(Name=$F$3))*Amount)}
    {=SUM(IF($F$3=0,1,(Name=$F$3))*Amount)}

    ฝากพวกเราช่วยกันดูสูตรให้ด้วยครับว่า แทนที่จะสร้างสูตร SumProduct แบบที่ผมทำมา
    จะใช้สูตร SumProduct แบบใด เพื่อคำนวณหายอดรวมของชื่อที่กำหนด
    ต่อมาถ้าไม่ได้ใส่ชื่อไว้ในเซลล์ F3
    ให้หายอดรวมทั้งหมดมาใช้แทน

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

    {=SUM(IF(ISBLANK(cell1),1,(Key1Range=cell1)) * IF(ISBLANK(cell2),1,(Key2Range=cell2)) * IF(ISBLANK(cell3),1,(Key3Range=cell3))*Amount)}

  2. #2
    อรวีร์
    Guest

    Smile

    ลองสูตรแบบนี้ค่ะ
    =SUMPRODUCT( (Name=IF(ISBLANK($F$3),Name,$F$3)) *Amount)
    หรือ
    =SUMPRODUCT(--(Name=IF(ISBLANK($F$3),Name,$F$3)), Amount)

  3. #3
    excel_fever
    Guest
    .............

  4. #4
    สมเกียรติ
    Guest
    Quote Originally Posted by อรวีร์ View Post
    ลองสูตรแบบนี้ค่ะ
    =SUMPRODUCT( (Name=IF(ISBLANK($F$3),Name,$F$3)) *Amount)
    หรือ
    =SUMPRODUCT(--(Name=IF(ISBLANK($F$3),Name,$F$3)), Amount)
    ขอบคุณครับ

    ทำให้เข้าใจแล้วว่า SumProduct ไม่สามารถกระจายเลข 1 ที่กรอกลงไปตามแบบของผม เพื่อทำให้เป็นเลข 1 หลายค่าตามขนาดของข้อมูล
    จึงต้องใช้สูตร Name=IF(ISBLANK($F$3),Name,$F$3) เพื่อทำให้เกิด array ของเลข 1 หรือ true กำกับทุกรายการ

  5. #5
    สมเกียรติ
    Guest
    ที่ผ่านมาเป็นสูตรกรณีที่ข้อมูลของชื่อบันทึกไว้ครบทุกรายการ

    คราวนี้สมมติว่า ข้อมูลของ Name มีเซลล์ที่เป็นช่องว่างปะปนอยู่ด้วย จะหายอดรวมของชื่อที่เป็นช่องว่างได้อย่างไรครับ

    ผมคิดสูตรนี้

    =SUMPRODUCT((Name=IF(ISBLANK($F$3)*(COUNTBLANK(Name)=0),Name,$F$3))*Amount)

    กรณีที่กรอกชื่อใน F3 จะหายอดรวมของชื่อนั้น

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

    มีทางอื่นอีกหรือไม่ครับ

  6. #6
    วานิชย์
    Guest
    ลักษณะคล้ายกันเพียงแต่ไปใช้ EXACT ตรวจสอบแทนการใช้ COUNTBLANK และ ISBLANK


    {=SUM(IF(SUM(--EXACT($F$3,Name))=0,1,EXACT($F$3,Name))*Amount)}


    :smile:

  7. #7
    วานิชย์
    Guest
    เพิ่มเติมครับ

    จากรูปแบบสูตรทั้ง 3 แบบของอาจารย์

    1. =SUMPRODUCT((Name=IF(ISBLANK($F$3)*(COUNTBLANK(Name)=0),Name,$F$3))*Amount)

    2. =SUMPRODUCT(--(Name=IF(ISBLANK($F$3)*(COUNTBLANK(Name)=0),Name,$F$3)),Amount)

    3. {=SUM((Name=IF(ISBLANK($F$3)*(COUNTBLANK(Name)=0),Name,$F$3))*Amount)}


    จะพบว่า หาก Amount มีข้อมูลที่ไม่ใช่ Number ปะปนมา จะทำให้สูตรหมายเลข 1 และ 3 Error #Value ส่วนสูตรที่ 2 ยังคงให้ผลลัพธ์ที่เป็นตัวเลขอยู่ครับ (สาเหตุเนื่องจากการใช้เครื่องหมาย * ) ซึ่งอาจจะมีทั้งผลดีและผลที่ต้องควรระวังในการนำไปใช้งานกับข้อมูลที่มีจำนวนมาก ๆ ครับ

    :smile:

  8. #8
    สมเกียรติ
    Guest
    ผมพบบ่อยๆว่าคนเก็บข้อมูลไม่ระวังในการบันทึกค่า บางที่ก็เคาะวรรคลงไปเล่นๆ บางที่ก็บันทึกหมายเหตุของตัวเองแทนที่จะบันทึกเลขลงไป

    ถ้าใช้สูตร array แบบคูณต่อกันไป ก็จะเกิด error ซึ่งพอใช้สูตรซ้อนลงไปในสูตร array เพื่อปรับค่าให้เหลือเลขเท่านั้น ก็จะทำให้แฟ้มคำนวณช้าลงไปมากเมื่อนำสูตร array ไปใช้หลายๆเซลล์

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

    แต่ถ้าใช้แบบที่ใช้ comma แทนคูณก็ถือว่าแก้ที่ตัวสูตร ดีเหมือนกันครับ เพิ่งเห็นประโยชน์ของแบบ comma (เพิ่งรู้เพราะคุณวานิชย์บอกนี่เอง) :tongue:

    =SUMPRODUCT(--(Name=IF(ISBLANK($F$3)*(COUNTBLANK(Name)=0),Name,$F$3)),Amount)

Similar Threads

  1. ข้อจำกัดของ Sumproduct
    By excel_fever in forum Excel Expert Forum Library 2012 - 2007
    Replies: 4
    Last Post: 23 Sep 2010, 16:32
  2. ปัญหา SUMPRODUCT ไม่คำนวณ
    By zozazozy in forum Excel Expert Forum Library 2012 - 2007
    Replies: 7
    Last Post: 21 Sep 2010, 11:36
  3. Replies: 4
    Last Post: 9 Aug 2010, 18:31
  4. Sumproduct
    By Chowalit02 in forum Excel Expert Forum Library 2012 - 2007
    Replies: 3
    Last Post: 22 Sep 2009, 09:42

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •