Excel 2003 สอบถามการนับผลรวมแบบมีเงื่อนไขครับ

[tccc]

1.ในส่วนของ Sheet "table" ผมต้องการนับผลรวมทั้งปีเป็นครั้งของการ Reject โดยอ้างอิงจาก Sheet "1:12" ในเซลล์ K,L,M,N ของแต่ละชีทว่าสายงานไหนขึ้นสินค้า Reject ไปแล้วกี่ครั้ง
2.ในส่วนของ Sheet "bad" ถ้าจะจัดอันดับการ Reject มากที่สุด 5 อันดับแรกแบบอัตโนมัติ ทำได้มั้ยครับแล้วใช้สูตรอะไรบ้างครับ

รบกวนอาจารย์ คนควน และ อาจารย์คนอื่นๆอธิบายด้วยนะครับ ขอบคุณมากครับ

[snasui]

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

ที่ตอบตามด้านล่างเป็นการใช้สูตรการหาค่าแบบมีเงื่อนไขกรณี 3 มิติ (คือหลายชีท) โดยมีวิธีการคือ

1. List รายชื่อชีทขึ้นมาก่อน จากไฟล์แนบที่ชีท Table ช่วงเซลล์ H3:H14 คือรายชื่อชีททั้งหมด

2. ให้ชื่อกับข้อ 1 โดยคลุม H3:H14 แล้วคลิกช่อง Name Box (ช่องซ้ายสุดของ Formula Bar) จากนั้นคีย์ AllSh > Enter

3. ที่ชีท Table เซลล์ C3 คีย์เพื่อหาค่าที่ขาดเป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$L$5:$L$16")))

Enter > Copy ลงด้านล่าง

4. ที่ชีท Table เซลล์ D3 คีย์เพื่อหาค่าที่เกินเป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$M$5:$M$16")))

Enter > Copy ลงด้านล่าง

5. ที่ชีท Table เซลล์ E3 คีย์เพื่อหาค่าที่ครบเป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$N$5:$N$16")))

Enter > Copy ลงด้านล่าง

6. ที่ชีท Table เซลล์ F3 คีย์เพื่อหาค่าที่อื่น ๆ เป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$O$5:$O$16")))

Enter > Copy ลงด้านล่าง

7. ที่ชีท Table เซลล์ G3 คีย์สูตรเพื่อหายอดรวม

=SUM(C3:F3) > Copy ลงด้านล่าง

8. ที่ชีท Bad เซลล์ D4 หาจำนวนที่มากที่สุดจากข้อ 7

=LARGE(Table!$G$3:$G$44,$B4)

Enter > Copy ลงด้านล่าง

9. ที่ชีท Bad เซลล์ C4 หาสายคนงานตามค่าที่ได้ในข้อ 7

=INDEX(Table!$B$9:$B$44,SMALL(IF(Table!$G$3:$G$44=$D4,ROW(Table!$G$3:$G$44)-ROW(Table!$G$3)+1),COUNTIF($D4:D4,D4)))

ซึ่งต้องกดแป้นให้รับสูตรด้วยการกดแป้น Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter จะกดแป้น Enter ธรรมดาไม่ได้เนื่องจากเป็นสูตร Array หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะีคีย์เข้าไปเองไม่ได้ครับ

ดูไฟล์แนบประกอบตามด้านล่างครับ

[tccc]

ขอบคุณมากครับแต่ผมยังไม่ค่อยเข้าใจเดี๋ยวจะอ่านให้ละเอียดยิบเลยครับ แต่ในส่วนของชีท 1:12 อะครับในเซลล์ L3:M16 ของทุกๆชีทมันเป็นจำนวนของกระสอบ
ส่วน N3:N16 เป็นจำนวนตันครับ ส่วนในเซลล์ O3:O16 ผมจะไม่มานับรวมในการจัดอันดับยอดแย่ของชีท Bad อะครับ

ยังไงต้องขอขอบคุณอาจารย์มากๆเลยครับ

Best Regards
แบดครับ

[snasui]

ครับ ปกติควรที่จะเขียนอธิบายเงื่อนไขและแสดงตัวอย่างคำตอบมาด้วยเพื่อผู้ตอบสามารถเข้าใจได้เลยไม่ต้องเดาเองครับ

[kmb]

3. ที่ชีท Table เซลล์ C3 คีย์เพื่อหาค่าที่ขาดเป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$L$5:$L$16")))

Enter > Copy ลงด้านล่าง

4. ที่ชีท Table เซลล์ D3 คีย์เพื่อหาค่าที่เกินเป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$M$5:$M$16")))

Enter > Copy ลงด้านล่าง

5. ที่ชีท Table เซลล์ E3 คีย์เพื่อหาค่าที่ครบเป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$N$5:$N$16")))

Enter > Copy ลงด้านล่าง

6. ที่ชีท Table เซลล์ E3 คีย์เพื่อหาค่าที่อื่น ๆ เป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$O$5:$O$16")))

Enter > Copy ลงด้านล่าง

ขอบคุณครับ ได้เทคนิคดี ๆ อีกแล้ว
แต่ผมคิดว่าคุณ tccc ต้องการนับจำนวนครั้งหรือเปล่าครับ ดูจาก sheet "1" ค่าที่่ใส่ไว้ที่เซลล์ F314, F317 และ F320
โดยอ้างอิงเพิ่มเติมตามคำอธิบายด้านล่างนี้ก็ไม่นับค่าที่อยู่ในคอลัมน์ O ด้วย

ขอบคุณมากครับแต่ผมยังไม่ค่อยเข้าใจเดี๋ยวจะอ่านให้ละเอียดยิบเลยครับ แต่ในส่วนของชีท 1:12 อะครับในเซลล์ L3:M16 ของทุกๆชีทมันเป็นจำนวนของกระสอบ
ส่วน N3:N16 เป็นจำนวนตันครับ ส่วนในเซลล์ O3:O16 ผมจะไม่มานับรวมในการจัดอันดับยอดแย่ของชีท Bad อะครับ

ยังไงต้องขอขอบคุณอาจารย์มากๆเลยครับ

Best Regards
แบดครับ

ถ้าใช่ ต้องแก้จาก sumif เป็นสูตรอะไรครับ พยายามคิดแล้ว แต่คิดไม่ออกครับ

[tccc]

รับทราบครับผม

[kmb]

เพื่อเป็นการศึกษากรณี array ผมจึงลองทำโจทย์นี้ดูครับ

3. ที่ชีท Table เซลล์ C3 คีย์เพื่อหาค่าที่ขาดเป็นรายคนงาน

=Sumproduct(Sumif(Indirect("'"&Allsh&"'!"&"$K$5:$K$16"),$B3,Indirect("'"&Allsh&"'!"&"$L$5:$L$16")))

Enter > Copy ลงด้านล่าง

ถ้าเป็น excel 2007 ผมใช้ COUNTIFS เพื่อนับได้ ตามสูตรแบบนี้
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&AllSh&"'!"&"$K$5:$K$16"),$B3,INDIRECT("'"&AllSh&"'!"&"$L$5:$L$16"),"<>"))

สำหรับคอลัมน์อื่นก็เปลี่ยนคอลัมน์ไปจาก L --> M, L --> N ส่วนกรณีของคอลัมน์ O ไม่นำมาคิดก็ไม่ใส่สูตรเพื่อนับ
กรณีที่ต้องการนับจำนวน ผลลัพธ์ก็ถูกต้อง

แต่ในกรณีของ 2003 ที่ไม่มี COUNTIFS ผมพยายามลอง SUMPRODUCT ตามนี้ครับ
=SUMPRODUCT(SUMPRODUCT(--(INDIRECT("'"&AllSh&"'!"&"$K$5:$K$16")=$B3),--(INDIRECT("'"&AllSh&"'!"&"$L$5:$L$16")="<>"))) แต่มันได้ #VALUE! คงต้องขอคำชี้แนะครับ

9. ที่ชีท Bad เซลล์ C4 หาสายคนงานตามค่าที่ได้ในข้อ 7

=INDEX(Table!$B$9:$B$44,SMALL(IF(Table!$G$3:$G$44=$D4,ROW(Table!$G$3:$G$44)-ROW(Table!$G$3)+1),COUNTIF($D4:D4,D4)))

ซึ่งต้องกดแป้นให้รับสูตรด้วยการกดแป้น Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter จะกดแป้น Enter ธรรมดาไม่ได้เนื่องจากเป็นสูตร Array หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะีคีย์เข้าไปเองไม่ได้ครับ

ดูไฟล์แนบประกอบตามด้านล่างครับ

ส่วนที่ชีท Bad เซลล์ C4 ผมเปลี่ยนสูตรนิดนึง ตามที่มาร์คสีแดงครับ
=INDEX(Table!$B$3:$B$44,SMALL(IF(Table!$G$3:$G$44=$D4,ROW(Table!$G$3:$G$44)-ROW(Table!$G$3)+1),COUNTIF(D$4:D4,D4)))

ก็ได้ผลลัพธ์ตามไฟล์ที่แนบมาครับ

[snasui]

ขอบคุณที่ช่วยแก้ไขให้ครับ

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

การพลิกแพลงแก้ไขสูตร สามารถพึ่งพาการแกะสูตรด้วย F9 หรือ Evaluate Formula มาช่วยได้ครับ

[kmb]

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

แต่สำหรับกรณีนี้ นอกจาก COUNTIFS แล้วพอจะมีฟังก์ชั่นอื่น แนะนำไหมครับ จะได้ลองศึกษาดู

[snasui]

การใช้ฟังก์ชั่นแบบหลายเงื่อนไขสามารถประยุกต์ได้เยอะมากครับ ขึ้นอยู่กับเงื่อนไขเป็นแบบใด

กรณีหลายเงื่อนไขโดยนำเงื่อนไขมาใช้ตรง ๆ ไม่ใช่นำผลลัพธ์ของสูตรมาเป็นเงื่อนไขอีกที ใน Excel Version 2007 ขึ้้นไป สามารถใช้ Countifs และ Sumifs มาช่วยได้อย่างดีและคำนวณเร็วมากครับ เพราะทำมาเพื่อการนี้โดยเฉพาะ เรื่องความเร็วในทุกการคำนวณแบบมีเงื่อนไขไม่มีฟังก์ชั่นใดสู้ฟังก์ชั่นชนิดนี้ได้ครับ

ส่วน Sumproduct สามารถพลิกแพลงได้มากมีความยืดหยุ่นสูงแม้จะใช้กับเงื่อนไขที่ได้จากสูตรอีกทีก็ไม่มีปัญหา นอกจากนี้สามารถใช้ได้ทุก Version

[tccc]

อ่านของอาจารย์ทั้งสองท่าน รู้สึกเหมือนผมนี่อยู่นอกโลกเลยนะเนี่ยห่างมาก
สุดยอดจริงๆครับ

[kmb]

เป็นกำลังใจให้นะครับ

ผมเองก็เรียนรู้จากบอร์ดนี้ครับ

[snasui]

แต่สำหรับกรณีนี้ นอกจาก COUNTIFS แล้วพอจะมีฟังก์ชั่นอื่น แนะนำไหมครับ จะได้ลองศึกษาดู

ผมคิดว่าอ่านตกประเด็นนี้ เลยไปอธิบายการใช้ฟังก์ชั่นกรณีมีเงื่อนไขแบบทั่ว ๆ ไป

สำหรับตามโจทย์นี้ที่ต้องการประยุกต์เป็นการนับแบบมีเงื่อนไขหลายเงื่อนไขแบบหลายชีท เป็นข้อจำกัดที่ไม่สามารถประยุกต์สูตรได้มาก หากไม่ใช้ Countifs ก็ต้องพึ่ง Add-ins Morefunc เข้ามาช่วย ซึ่งจะมีฟังก์ชั่น Threed เข้ามาช่วยโดยใช้ฟังก์ชั่น Sumproduct ครอบอีกที ดูตัวอย่างการใช้งานที่นี่ครับ http://xcell05.free.fr/morefunc/english/tab3D.htm

[kmb]

ขอบคุณสำหรับคำแนะนำครับ

ผมทดลองที่เซลล์ J10 เพื่อเปรียบเทียบผลลัพธ์ โดยใส่สูตร
=SUMPRODUCT(--(THREED('1:12'!$K$5:$K$16)=$B10),--(THREED('1:12'!L$5:L$16)<>""))

แล้วลองใช้ Evaluate Formula ได้ผลตามรูปครับ ซึ่งมันได้ผลลัพธ์ไม่เหมือนกันระหว่างครั้งที่ 1 และ 2 เหมือนสลับคอลัมน์

นี่รูปครั้งแรก

eva 1.JPG

นี่รูปครั้งที่สอง

eva 2.JPG

รบกวนขอคำแนะนำเรื่องสูตรครับว่าควรแก้ไขอย่างไร
เพราะพอเห็นผลลัพธ์จาก Evaluate Formula ทั้ง 2 ครั้ง ที่ไม่เหมือนกันเลยไม่รู้จะแก้ยังไงเลยครับ

[snasui]

ดูผลลัพธ์แล้วคิดว่าไม่ต้องแก้ไขอะไรครับ เนื่องจากผมไ่ม่สามารถแกะ Code ที่ทางเจ้าของ Add-ins เขาเขียนมาให้ได้ แต่เดาว่ามีการ Active ให้โปรแกรมทำการคำนวณในขั้นตอนการทำ Evaluate เพราะเมื่อกดแป้น F9 ให้ทำการคำนวณ แล้วทำการ Evaluate ใหม่ก็จะกลับมาเป็นในแบบที่ถูกต้อง

[kmb]

ประเด็นสำคัญคือ ผลลัพธ์ที่ได้ไม่ถูกต้องน่ะครับ เพราะคำตอบควรจะได้ 1 เหมือนในเซลล์ C10

[snasui]

สถานการณ์แบบเลือกที่รักมักที่ชังแบบนี้อธิบายลำบากครับ ผมใช้หลายเครื่องและทุกเครื่องไม่เคยมีปัญหากับ Morefunc ดูำภาพการสาธิตตามด้านล่างครับ

[kmb]

ขอบคุณครับ สงสัยอันนี้จะเป็นสาเหตุ ผมเจอข้อความตามรูปเวลาปิดไฟล์ครับ

eva 3.JPG

เดี๋ยวไปลองเครื่องที่บ้านดูก่อนครับว่าเป็นไหม

สอบถามอีกหน่อยครับ ชื่อ worksheet ต้องเป็นตัวเลขหรือไม่ก็ตัวเลขต่อท้ายใช่ไหมครับ ถ้าไม่มีตัวเลขไม่น่าจะใช้ได้ เพราะผมเข้าใจว่าฟังก์ชั่นนี้ทำงานจากค่าน้อยซึ่งในที่นี้คือ 1 จนถึงค่ามากซึ่งในที่นี้คือ 12
=SUMPRODUCT(--(THREED('1:12'!$K$5:$K$16)=$B10),--(THREED('1:12'!L$5:L$16)<>""))

[snasui]

เข้าใจว่าเป็นปัญหาที่ VB ตามที่โปรแกรมฟ้องเลยครับ

สำหรับชื่อชีทเป็นชื่อใด ๆ ก็ได้ครับ ไม่จำกัดว่าจะต้องมีตัวเลขกำกับครับ

[tccc]

ติดตามอย่างไม่ห่าง แต่ยอมรับเลยครับไม่เข้าใจเลยสักนิด จะพยายามต่อครับ