Snasui Excel Forum

ขอสอบถามอาจารย์ ถึงการปรับสูตรให้แสดง Material คงเหลือในแต่ละเดือนครับ
ซึ่งในไฟล์แนบจะมี 3 Sheet ข้อมูลได้แก่
Sheet : Material Simulation เป็นข้อมูลแสดงรายการ Material คงเหลือแต่ละเดือน หลังหัก Production Plan
Sheet : Usage Part Assy เป็นข้อมูลการใช้ Material ต่อการผลิต Part Assy 1 Lot
Sheet : Production Plan เป็นข้อมูลแผนการผลิต Part Assy ล่วงหน้า 3 เดือน (Lot)
คือผมอยากได้สูตร Material คงแต่แต่ละเดือน โดยหักแผนผลิตออกไปเรื่อยๆ
เคยสอบถามไปแล้ว 1 รอบ และคุณ Bo_ry ได้ให้ตัวอย่างสูตรไว้ สูตรค่อนข้างยาวครับ แต่ยังติดปัญหาบางประการ ซึ่งรายละเอียดผมได้อธิบายในไฟล์แนบแล้วครับ
ขอบคุณมากครับ

ปรับ 'Usage Part Assy'!$B$4:$B$23 เป็น 'Usage Part Assy'!$B$4:$B$305 และไล่ปรับช่วงข้อมูลทั้งหมดให้ตรงกับข้อมูลที่เพิ่มเข้ามาครับ หรือจะใช้การสร้าง range name แบบไดนามิกมาช่วยขยายช่วงข้อมูลอัตโนมัติก็ได้ครับ
Formulas > Name Manager > New >
ตั้งชื่อ Name list ว่า "UPA" > ช่อง refers to ใส่สูตร =OFFSET('Usage Part Assy'!$B$3,1,,COUNTA('Usage Part Assy'!$B:$B)-1,)

สร้างอีกชุด ตั้งชื่อ Name list ว่า "PP" > ช่อง refers to ใส่สูตร =OFFSET('Production Plan'!$B$3,1,,COUNTA('Production Plan'!$B:$B)-1,COUNTA('Production Plan'!$3:$3)-1)

ปรับสูตรเป็น =IFERROR($F4*(COLUMNS($H4:H4)=1)+G4-SUM(IFERROR(VLOOKUP(INDEX('Usage Part Assy'!$D:$D,N(IF(1,SMALL(IF($B4=UPA,ROW(UPA)),ROW(INDIRECT("1:"&COUNTIF(UPA,$B4))))-MOD(SMALL(IF($B4=UPA,ROW(UPA)),ROW(INDIRECT("1:"&COUNTIF(UPA,$B4))))+1,5)))),PP,COLUMNS($G4:H4),)*INDEX('Usage Part Assy'!$C:$C,N(IF(1,SMALL(IF($B4=UPA,ROW(UPA)),ROW(INDIRECT("1:"&COUNTIF(UPA,$B4))))-MOD(SMALL(IF($B4=UPA,ROW(UPA)),ROW(INDIRECT("1:"&COUNTIF(UPA,$B4))))+1,5)))),0)),"") Ctrl+Shift+Enter ครับ

ขอบคุณมากครับ ลองปรับตามนี้ครับ

ไล่ปรับช่วงข้อมูล Usage Part Assy ใหม่ ตาม K. Parakorn แนะนำ โดยปรับแก้ช่วงข้อมูล 2 ช่วงดังนี้
1. Mat Domestic >> Usage Part Assy !$B$4:$B$152
2. Mat Export >> Usage Part Assy !$B$63:$B$314
แต่ก็ยังไม่โชว์ตัวเลขคงเหลือเช่นเดิมครับ หรือมีส่วนไหนผิด มีวิธีปรับแก้ให้ตัวเลขขึ้นไหมครับ ขอบคุณครับตาม
เอกสารที่แก้ตามไฟล์แนบครับ
ขอบพระคุณล่วงหน้าครับ

ที่ H4 ปรับเป็น =IFERROR($F4*(COLUMNS($H4:H4)=1)+G4-SUM(IFERROR(VLOOKUP(INDEX('Usage Part Assy'!$D:$D,N(IF(1,SMALL(IF($B4='Usage Part Assy'!$B$4:$B$152,ROW('Usage Part Assy'!$B$4:$B$152)),ROW(INDIRECT("1:"&COUNTIF('Usage Part Assy'!$B$4:$B$152,$B4))))-MOD(SMALL(IF($B4='Usage Part Assy'!$B$4:$B$152,ROW('Usage Part Assy'!$B$4:$B$152)),ROW(INDIRECT("1:"&COUNTIF('Usage Part Assy'!$B$4:$B$152,$B4))))+1,5)))),'Production Plan'!$B$4:$E$143,COLUMNS($G4:H4),)*INDEX('Usage Part Assy'!$C:$C,N(IF(1,SMALL(IF($B4='Usage Part Assy'!$B$4:$B$152,ROW('Usage Part Assy'!$B$4:$B$152)),ROW(INDIRECT("1:"&COUNTIF('Usage Part Assy'!$B$4:$B$152,$B4))))-MOD(SMALL(IF($B4='Usage Part Assy'!$B$4:$B$152,ROW('Usage Part Assy'!$B$4:$B$152)),ROW(INDIRECT("1:"&COUNTIF('Usage Part Assy'!$B$4:$B$152,$B4))))+1,5)))),0)),"")

แล้วอย่าลืมกด Ctrl+Shift+Enter ด้วยครับ