แสดงการใช้ Part ในแต่ละเดือนของแต่ละ Product เมื่อมีการเปลี่ยนแปลงจาก Product หนึ่ง ไปยัง Product หนึ่ง

[Tanthai P.]

สวัสดีครับ,

ผมทำไฟล์เกี่ยวกับการใช้ Part ต่างๆในการผลิต Product A, B และ C ซึ่งความต้องการในแต่ละเดือนจะไม่เท่ากัน ซึ่งเราสามารถเปลี่ยนProduct ได้โดยการเปลี่ยน Part เครื่องจักร แต่ละ Product จะใช้ Part ที่เหมือนกันและแตกต่างกัน เช่น
Product A ใช้ item1, item2
Product B ใช้ item1, item2, item3 ( A B ใช้ item1,item2 เหมือนกัน)

ในไฟล์ที่แนบมา มีตารางอยู่ 3 ตาราง คือ
1.ตาราง Requirement อธิบายถึงตารางความต้องการของแต่ละ Product ในแต่ละเดือน
2.ตาราง Conversion plan คือ ตารางการเปลี่ยนเครื่องจักรจากProduct หนึ่งไปยังProduct หนึ่งเพื่อให้สอดคล้องกับ Requirement
3.ตาราง Part Available คือ ตารางแสดง Part ที่ใช้และ Part ที่มีให้ใช้ในแต่ละ Product ของแต่ละเดือน

ใน Column:Operation
Requirement คือ จำนวน requirement ของ เครื่องจักรในแต่ละเดือน (เอามาจากตาราง requirement)
Available คือ จำนวน part ที่มีให้ใช้ในแต่ละ Product ของแต่ละเดือน
Return คือ จำนวน Part ที่ต้องได้คืนเมื่อมีการเปลี่ยน Product ของในแต่ละเดือน(เอามาตากตาราง Conversion plan) เช่น ในเดือน Jan มีการเปลี่ยน product จาก B ไปเป็น A จำนวน 1 เครื่อง ดังนั้น ในช่อง return ในเดือน Jan ของ Product B จะลดลงไป 1 หรือแสดงเป็น -1 และช่อง return ในเดือน Jan ของ product A จะเพิ่มขึ้น 1

แต่ปัญหา คือ Product B มี item3 ที่ Product A ไม่มี ผมไม่รู้จะทำยังไงให้ item ที่ไม่เหมือนกับ Product ปลายทางแสดงให้เป็น 0



รบกวนด้วยครับ
ขอบคุณครับ

[snasui]

ค่อย ๆ ถามตอบกันไปครับ

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

[Tanthai P.]

ครับ

ถาม: เราจะทราบได้อย่างไรว่าแต่ละ Product มีกี่ Item
ตอบ: จากตาราง Part Available ครับ Column:A คือ Product Column:B คือ Part ดังนั้น Product A จะมีอยู่ 2 Part คือ item1, item2
จากตารางจะเห็นว่า Product A ไม่มี item3 แต่ product B มี item3 ครับ(แถวที่ 30:35)

[snasui]

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

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

ตัวอย่างการนับ Item ของ Product A Item3 เฉพาะที่ระบายสีไว้ในสูตรด้านล่างเท่านั้น ไม่เกี่ยวกับส่วนที่เหลือที่เป็นสูตรที่เขียนมาเองแล้วครับ

=IF(COUNT(IF($A$10:$A$32="A",IF($B$10:$B$32=$B$32,1)))=0,0,IF(LARGE(($I$3:$I$12=D$9)*($J$3:$J$12=$A32)*($L$3:$L$12),1)*-1=0,SUM(($I$3:$I$12=D$9)*($K$3:$K$12=$A32)*($L$3:$L$12)),LARGE(($I$3:$I$12=D$9)*($J$3:$J$12=$A32)*($L$3:$L$12),1)*-1))

[Tanthai P.]

ขอบคุณมากครับ แต่ในสูตรสีแดง "A" อาจจะเป็น Product อื่นที่ไม่ใช่ได้

ผมขออธิบายเรื่องเดิม ด้วยไฟล์ใหม่นะครับ

1.ผมมีตารางแผนงานในการเปลี่ยน Product ของแต่ละ Workweek ใน Sheet:Convert Plan โดยมี field list คือ
-Workweek คือ ช่วงเวลาที่จะConvert Product
-From คือ Product ต้นทาง
-To คือ Product ปลายทาง
-Qty คือ จำนวนเครื่องที่จะต้องเปลี่ยน Product

2.ผมมีตารางเช็คว่า Parts ไหนใช้กับ Product อะไร ซึ่งบางPart จะใช้เหมือนกัน บางอันก็ไม่เหมือนกันจากตาราง CheckParts ใน sheet:CheckParts

สิ่งที่จะขอความช่วยเหลือ คือ
ต้องการเติมจำนวน Parts ที่เปลี่ยนจาก Product ต้นทาง ไปยัง Product ปลายทาง โดยถ้า Product ต้นทางและปลายทางมีParts ที่เหมือนๆกัน(ดูจากตาราง CheckParts) จะให้โชว์เลขเท่ากับ Qty (ถ้าเป็นProduct ต้นทางให้โชว์ -Qty) ของตาราง Convert Product และ ถ้าProduct ใช้ Parts ไม่เหมือนกันจะให้โชว์ 0 ลงในตาราง Return Part Sheet:ReturnParts
เช่น WW1810 จะมี convert จาก Avenger ไปยัง Skybolt SDET จำนวน 3 เครื่อง ซึ่งจะโชว์ตัวเลขดังใน ไฟล์ที่แนบไว้ครับ

ปล.สิ่งที่ยากคือ ในบาง Workweek มีProduct ต้นทางเหมือนกัน แต่Product ปลายทางไม่เหมือนกัน เช่น WW1805 ที่จะมีการ convert จาก Thunderbolt 3 เครื่อง ไปยัง MobulaBP HDET 1 เครื่อง, ValkyrieBP 1 เครื่อง และ Skybolt SDET 1 เครื่อง ซึ่งแต่ละเครื่องก็จะใช้ Part ไม่เหมือนกัน


ขอบคุณที่ช่วยเหลือครับ

ReturnPart.xlsx

[snasui]

ตัวอย่างสูตรที่ L4 ครับ

=CHOOSE(IF(COUNTIFS('Convert Plan'!$B$4:$B$27,ReturnParts!$A4,'Convert Plan'!$A$4:$A$27,L$3+0),1,2),-(IF(COUNTIFS(ReturnParts!$A$4:$A$1183,INDEX('Convert Plan'!$C$4:$C$27,MATCH(1,IF('Convert Plan'!$B$4:$B$27=$A4,IF('Convert Plan'!$A$4:$A$27=L$3+0,1)),0)),ReturnParts!$B$4:$B$1183,B4)=0,0,INDEX('Convert Plan'!$D$4:$D$27,MATCH(1,IF('Convert Plan'!$B$4:$B$27=$A4,IF('Convert Plan'!$A$4:$A$27=L$3+0,1)),0)))),IF(COUNTIFS(ReturnParts!$A$4:$A$1183,INDEX('Convert Plan'!$B$4:$B$27,MATCH(1,IF('Convert Plan'!$C$4:$C$27=$A4,IF('Convert Plan'!$A$4:$A$27=L$3+0,1)),0)),ReturnParts!$B$4:$B$1183,B4)=0,0,INDEX('Convert Plan'!$D$4:$D$27,MATCH(1,IF('Convert Plan'!$C$4:$C$27=$A4,IF('Convert Plan'!$A$4:$A$27=L$3+0,1)),0))))

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

[Tanthai P.]

ขอบคุณครับ ได้ไอเดียใหม่ๆเยอะเลย

สูตรที่ให้มา ตัวอย่างของที่ G1020

=CHOOSE(IF(COUNTIFS('Convert Plan'!$B$4:$B$27,ReturnParts!$A1020,'Convert Plan'!$A$4:$A$27,G$3+0),1,2),-(IF(COUNTIFS(ReturnParts!$A$4:$A$1183,INDEX('Convert Plan'!$C$4:$C$27,MATCH(1,IF('Convert Plan'!$B$4:$B$27=$A1020,IF('Convert Plan'!$A$4:$A$27=G$3+0,1)),0)),ReturnParts!$B$4:$B$1183,$B1020)=0,0,INDEX('Convert Plan'!$D$4:$D$27,MATCH(1,IF('Convert Plan'!$B$4:$B$27=$A1020,IF('Convert Plan'!$A$4:$A$27=G$3+0,1)),0)))),IF(COUNTIFS(ReturnParts!$A$4:$A$1183,INDEX('Convert Plan'!$B$4:$B$27,MATCH(1,IF('Convert Plan'!$C$4:$C$27=$A1020,IF('Convert Plan'!$A$4:$A$27=G$3+0,1)),0)),ReturnParts!$B$4:$B$1183,$B1020)=0,0,INDEX('Convert Plan'!$D$4:$D$27,MATCH(1,IF('Convert Plan'!$C$4:$C$27=$A1020,IF('Convert Plan'!$A$4:$A$27=G$3+0,1)),0))))

ตรงสูตรสีแดง พอลองกด F9 ดูแล้ว มันจะโชว์แค่ค่าเดียว คือ "Mobula HDET" ซึ่งมันต้องมีอีก 2 ค่า คือ "ValkyrieBP" และ "Skybolt SDET" เนื่องจากใน IF('Convert Plan'!$B$4:$B$27=$A1020,IF('Convert Plan'!$A$4:$A$27=G$3+0,1)) มันมีค่า 1 อยู่ 3 ค่า

จะมีทางเป็นไปได้ไหมครับ ถ้าจะให้คิดรวมทั้ง 3 ค่า เพราะ จากตาราง Convert Plan ใน WW1805 ผมมี Thunderbolt 3 เครื่อง ที่จะ Convert ไปเป็น Mobula HDET, ValkyrieBP, Skybolt SDET อย่างละ 1 เครื่อง สูตรที่ให้มาลองไปใช้แล้วค่าที่ออกมา คือ -1 แต่จริงๆแล้วควรจะเป็น -3 ครับ

[snasui]

สูตรนั้นให้ผลลัพธ์เป็นค่าเดียวครับ เป็นการค้นหาแบบมีเงื่อนไข 2 เงื่อนไข เงื่อนไขทั้งหมดคือที่ใช้เครื่องหมายเท่ากับ จะใส่กี่เงื่อนไขก็ได้แต่สูตรโดยรวมต้องไม่เกิน 8,192 อักขระ และซ้อนกันได้ไม่เกิน 64 ชั้นครับ

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

[Tanthai P.]

ตัวอย่างครับ

[snasui]

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

[Tanthai P.]

นี้ครับ อาจารย์

Example2.xlsx

[snasui]

ตัวอย่างสูตรที่ E13 ครับ

=IFERROR(CHOOSE(IF(COUNTIFS($B$4:$B$8,$C13,$A$4:$A$8,E$12+0),1,2),-SUMPRODUCT(COUNTIFS($C$13:$C$32,OFFSET($C$4,MATCH(1,($A$4:$A$8=E$12)*($B$4:$B$8=$C13),0)-1,0,COUNTIFS($A$4:$A$8,L$12,$B$4:$B$8,$C13)),$D$13:$D$32,$D13),OFFSET($D$4,MATCH(1,($A$4:$A$8=L$12)*($B$4:$B$8=$C13),0)-1,0,COUNTIFS($A$4:$A$8,L$12,$B$4:$B$8,$C13))),SUMPRODUCT(COUNTIFS($C$13:$C$32,OFFSET($B$4,MATCH(1,($A$4:$A$8=L$12)*($C$4:$C$8=$C13),0)-1,0,COUNTIFS($A$4:$A$8,L$12,$C$4:$C$8,$C13)),$D$13:$D$32,$D13),OFFSET($D$4,MATCH(1,($A$4:$A$8=L$12)*($C$4:$C$8=$C13),0)-1,0,COUNTIFS($A$4:$A$8,L$12,$C$4:$C$8,$C13)))),0)

Enter > Copy ไปทางขวาและลงด้านล่าง