Auto Selected RM

[Praman]

สวัสดีครับ

ผมต้องการสร้างไฟล์วางแผนการผลิตขึ้นมาใหม่ครับ
โดยผมสร้าง Sheet ไว้ 2 Sheet คือ
1) BoM
2) Plan

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

ขอบคุณครับ

[bank9597]

ต้องอธิบายโจทย์มาด้วยครับ พร้อมตัวอย่างคำตอบมาพอสังเขป จะทำให้สามารถคิดสูตรได้ครับ

[Praman]

รบกวนดูไฟล์แนบอีกครั้งครับ
น่าจะชัดเจนขึ้น

ขอบคุณครับ

[Praman]

ไฟล์แนบที่ส่งให้ก่อนหน้านี้
มีความคลาดเคลื่อนนิดหน่อยครับ

รบกวนดูอันล่าสุดนี้ครับ

ขอบคุณครับ

[snasui]

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

[Praman]

สูตรที่มีอยู่ นั้นถูกต้องครับ แต่เป็นการทำงาน mannual สองขั้นตอน คือ
1) ต้องเช็คว่าต้องผลิตสินค้าอะไร ด้วยจำนวนเท่าไหร่
2) นำข้อมูลที่ได้ในข้อ 1 มาหาว่าจะใช้ Semi RM อะไรบ้าง จึงจะใช้สูตร sumproduct ได้

สิ่งที่ต้องการคือ การ integrate ระหว่าง 2 sheet ครับ คือ BoM และ Plan
เพียงวางแผนในการผลิตแต่ละวันว่าต้องการผลิตอะไรบ้าง (Sheet Plan - ตารางสีฟ้า และสีเขียว)

อยากทราบว่าจะต้องใช้สูตรอะไรใน Sheet Plan - ตารางสีชมพู
* สูตรที่สามารถ ดึงชนิดวัตถุดิบจาก Sheet Bom มาเติมในช่อง Semi RM Type ได้ถูกต้องตามแผนการผลิตที่วางไว้
* สูตรที่คำนวณ ปริมาณวัตถุดิบที่ต้องการใช้

หมายเหตุ: การจัดตารางใน BoM ของผม มีจุดอ่อนที่ทำให้ใช้สูตร sumproduct ตรงๆไม่ได้ครับ

ขอบคุณมากครับ

[snasui]

ตอบในตารางสีชมพูซึ่งเป็นการดึงข้อมูลจากตาราง BOM มาคำนวณก่อนครับ ส่วนเรื่อง Product ช่วยอธิบายมาด้วยว่ามีเงื่อนไขการดึงมาอย่างไร

สำหรับตารางสีชมพูลองตามนี้ครับ

1. ที่ F19 คีย์สูตร
   =SUMPRODUCT($N$24:$N$28,INDEX($O$24:$AH$28,0,MATCH($E19,$O$23:$AH$23,0)))
   Enter > Copy ลงด้านล่าง
2. ที่ H19 คีย์สูตร
   =SUMPRODUCT($N$33:$N$39,INDEX($O$33:$AO$39,0,MATCH($G19,$O$32:$AO$32,0)))
   Enter > Copy ลงด้านล่าง

สูตรข้างต้นจะ Dynamic จะเรียงหรือไม่เรียง Product ก็ให้ผลลัพธ์ถูกต้อง

[Praman]

ขอบคุณคุณคนควนมากครับ
ที่อุตสาห์ตอบคำถามทั้งๆที่น่าจะดึกมากแล้ว

สูตรที่คุณคนควนคิดให้ เป็นการนำข้อมูลจากที่ผมคิดแบบ manual มาใช้งานครับ
ขออนุญาติส่งไฟล์ให้ใหม่นะครับ
1) Auto Selected RM (Manual).xls ---- คำนวนแบบ manual ครับ
2) Auto Selected RM (Original).xls ---- ไฟล์ต้นฉบับครับ

รบกวนคิดสูตรจากไฟล์ต้นฉบับด้วยครับ

ขอบคุณครับ

[snasui]

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

ช่วยคิดมาให้ผมดูเป็นตัวอย่างตามไฟล์ที่แนบมาล่าสุดว่า เซลล์ F19 มีค่าเป็น 70 มีวิธีได้มาอย่างไร ดูเงื่อนไขใดบ้างเป็นข้อ ๆ เลยครับ

[Praman]

ขออนุญาติส่งไฟล์ให้ใหม่ครับ

ขอบคุณครับ

[Praman]

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

ช่วยคิดมาให้ผมดูเป็นตัวอย่างตามไฟล์ที่แนบมาล่าสุดว่า เซลล์ F19 มีค่าเป็น 70 มีวิธีได้มาอย่างไร ดูเงื่อนไขใดบ้างเป็นข้อ ๆ เลยครับ

ครั้งแรกสุด เป็นการใช้ไฟล์ต้นฉบับแล้วครับ
แต่ตอบไปตอบมา อธิบายไปเรื่อยๆ กลายเป็นอีกไฟล์ (manual) โดยไม่ตั้งใจครับ
เซล์ที่ F19 ได้ค่าเท่ากับ 7 ครับ ทำ copy แล้ว paste ผิดครับ
แก้ไขและส่งไฟล์ให้ใหม่แล้วครับ

[snasui]

จากไฟล์ Auto Selected RM (Original).xls ช่วยคิดมาให้ผมดูเป็นตัวอย่างว่า เซลล์ F19 มีค่าเป็น 7 มีวิธีได้มาอย่างไร ดูเงื่อนไขใดบ้างเป็นข้อ ๆ เลยครับ

[Praman]

จากไฟล์ Auto Selected RM (Original).xls ช่วยคิดมาให้ผมดูเป็นตัวอย่างว่า เซลล์ F19 มีค่าเป็น 7 มีวิธีได้มาอย่างไร ดูเงื่อนไขใดบ้างเป็นข้อ ๆ เลยครับ

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

[snasui]

ลองตามนี้ครับ

1. ที่ชีท BoM ปรับข้อมูลให้เป็น Database ดูตัวอย่างได้จากไฟล์แนบ
2. ที่ชีท Plan
   1. เซลล์ E16 คีย์
      =SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(BoM!$D$27:$D$161,$E$4:$E$13,0)),IF(BoM!$E$27:$E$161<>0,MATCH(BoM!$C$27:$C$161,BoM!$C$27:$C$161,0))),ROW(BoM!$C$27:$C$161)-ROW(BoM!$C$27)+1),1))
      Ctrl+Shift+Enter
   2. เซลล์ E19 คีย์
      =IF(ROWS(E$19:E19)>E$16,"",INDEX(BoM!$C$27:$C$161,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH(BoM!$D$27:$D$161,$E$4:$E$13,0)),IF(BoM!$E$27:$E$161<>0,MATCH(BoM!$C$27:$C$161,BoM!$C$27:$C$161,0))),ROW(BoM!$C$27:$C$161)-ROW(BoM!$C$27)+1),ROW(BoM!$D$27:$D$161)-ROW(BoM!$D$27)+1),ROWS(E$19:E19))))
      Ctrl+Shift+Enter > Copy ลงด้านล่าง
   3. เซลล์ F19 คีย์
      =IF(E19="","",SUMPRODUCT(CHOOSE({1,2,3},SUMPRODUCT(--(BoM!$D$27:$D$161="Product A"&RIGHT(E19,3)),--(BoM!$C$27:$C$161=E19),BoM!$E$27:$E$161),SUMPRODUCT(--(BoM!$D$27:$D$161="Product B"&RIGHT(E19,3)),--(BoM!$C$27:$C$161=E19),BoM!$E$27:$E$161),SUMPRODUCT(--(BoM!$D$27:$D$161="Product C"&RIGHT(E19,3)),--(BoM!$C$27:$C$161=E19),BoM!$E$27:$E$161))*SUMIF($E$4:$E$13,"Product "&{"A","B","C"}&RIGHT(E19,3),$F$4:$F$13)))
      Enter > Copy ลงด้านล่าง

[bank9597]

ลองตามนี้ครับ

1. ที่ชีท BoM ปรับข้อมูลให้เป็น Database ดูตัวอย่างได้จากไฟล์แนบ
   ที่ชีท Plan
   1. เซลล์ E16 คีย์
      =SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(BoM!$D$27:$D$161,$E$4:$E$13,0)),IF(BoM!$E$27:$E$161<>0,MATCH(BoM!$C$27:$C$161,BoM!$C$27:$C$161,0))),ROW(BoM!$C$27:$C$161)-ROW(BoM!$C$27)+1),1))
      Ctrl+Shift+Enter
   2. เซลล์ E19 คีย์
      =IF(ROWS(E$19:E19)>E$16,"",INDEX(BoM!$C$27:$C$161,SMALL(IF(FREQUENCY(IF(ISNUMBER(MATCH(BoM!$D$27:$D$161,$E$4:$E$13,0)),IF(BoM!$E$27:$E$161<>0,MATCH(BoM!$C$27:$C$161,BoM!$C$27:$C$161,0))),ROW(BoM!$C$27:$C$161)-ROW(BoM!$C$27)+1),ROW(BoM!$D$27:$D$161)-ROW(BoM!$D$27)+1),ROWS(E$19:E19))))
      Ctrl+Shift+Enter > Copy ลงด้านล่าง
   3. เซลล์ F19 คีย์
      =IF(E19="","",SUMPRODUCT(CHOOSE({1,2,3},SUMPRODUCT(--(BoM!$D$27:$D$161="Product A"&RIGHT(E19,3)),--(BoM!$C$27:$C$161=E19),BoM!$E$27:$E$161),SUMPRODUCT(--(BoM!$D$27:$D$161="Product B"&RIGHT(E19,3)),--(BoM!$C$27:$C$161=E19),BoM!$E$27:$E$161),SUMPRODUCT(--(BoM!$D$27:$D$161="Product C"&RIGHT(E19,3)),--(BoM!$C$27:$C$161=E19),BoM!$E$27:$E$161))*SUMIF($E$4:$E$13,"Product "&{"A","B","C"}&RIGHT(E19,3),$F$4:$F$13)))
      Enter > Copy ลงด้านล่าง

มันมาอีกแล้ว สูตร FREQUENCY
(ทำอย่างไร เราถึงจะใช้สูตรนี้ได้บ้างน่ะ อิอิ)

[snasui]

ปรับสูตรที่ F19 ใหม่เป็นตามด้านล่างเพื่อให้สั้นลงครับ

=IF(E19="","",SUM(SUBTOTAL(9,OFFSET(BoM!$E$27,SMALL(IF(E19=BoM!$C$27:$C$161,ROW(BoM!$C$27:$C$161)-ROW(BoM!$C$27)),{1,2,3}),0))*SUMIF($E$4:$E$13,"Product "&{"A","B","C"}&RIGHT(E19,3),$F$4:$F$13)))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[bank9597]

ปรับสูตรที่ F19 ใหม่เป็นตามด้านล่างเพื่อให้สั้นลงครับ

=IF(E19="","",SUM(SUBTOTAL(9,OFFSET(BoM!$E$27,SMALL(IF(E19=BoM!$C$27:$C$161,ROW(BoM!$C$27:$C$161)-ROW(BoM!$C$27)),{1,2,3}),0))*SUMIF($E$4:$E$13,"Product "&{"A","B","C"}&RIGHT(E19,3),$F$4:$F$13)))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

อาจารย์อธิบายผมหน่อยได้ไหมครับ

[snasui]

ควรจะถามในส่วนที่ไม่เข้าใจครับ ไม่เข้าใจท่อนไหนบ้างครับ

[Praman]

ปรับสูตรที่ F19 ใหม่เป็นตามด้านล่างเพื่อให้สั้นลงครับ

=IF(E19="","",SUM(SUBTOTAL(9,OFFSET(BoM!$E$27,SMALL(IF(E19=BoM!$C$27:$C$161,ROW(BoM!$C$27:$C$161)-ROW(BoM!$C$27)),{1,2,3}),0))*SUMIF($E$4:$E$13,"Product "&{"A","B","C"}&RIGHT(E19,3),$F$4:$F$13)))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ขอบคุณมากครับ
เริ่มตั้งแต่ปรับตาราง BoM ใหม่ตามที่คนควนแนะนำ (ทำเสร็จทุกผลิตภัณฑ์ ทุกวัตถุดิบ ใช้ไปเกือบสามพันบรรทัดครับ)
และได้นำบางสูตรไปใช้งานและได้ผลตามที่ต้องการครับ
แต่ยังทำไม่เสร็จทั้งหมด คงต้องใช้เวลาสักหน่อย
หากติดขัดตรงใหน คงต้องขอรบกวนใช้พื้นที่บอร์ดอีกรอบครับ