สูตร INDEX แบบ Array ใน excel ครับ

[eAglet]

เนื่องจากผมทำงานกับ work sheet หนึ่ง เป็นข้อมูลที่ระบุถึง ตารางวัสดุ (BOM : Bill of Material) ที่กำหนดชื่อวัตถุดิบ และจำนวนการใช้ ต่อผลิตภัณฑ์ 1 ชิ้น
เช่น ผลิตภัณฑ์ A (level 1)
ใช้วัตถุดิบ B C และ D ประกอบเข้าด้วยกัน ---> level 2

แต่ B = 2H+I
C = J+4K
D = E+3F+2X+Y เป้นต้น ----> level 3

แต่ใน work sheet นำข้อมูลทั้งหมดมาเรียงลำดับ โดยใช้บรรทัด และ เลข level ระบุลำดับการประกอบ

แต่เนื่องจาก แต่ละวัตถุดิบ มีจำนวน แตกต่างตาม stock ที่มี โจทย์ว่า หากต้องการ ผลิต A จำนวน 100 ชิ้น ควรจะผลิตในแต่ละ level อย่างละเท่าใด
สมมติว่ามี B อยู่แล้ว 102 ชิ้น แสดงว่า ไม่ต้องผลิต B อีก ในขณะเดียวกันก็ไม่จำเป็นต้องผลิต H และ I ด้วย

หากมี C เพียง 80 ชิ้น นั่นแสดงว่า ต้องผลิต C เพิ่ม 20 ชิ้น ----> ซึ่งต้องแสดงว่า ต้องการ C(20) = J (20) + K (4*20) นั่นคือต้องการ J เพิ่ม 20
และ K เพิ่ม = (4*20) ด้วย ทั้งนี้ ต้องมีการหักล้าง stock ที่มีของ J และ K อีกด้วย

มี file Data ดังนี้ครับ ซึ่งเลือกใช้สูตร INDEX มาแก้ปัญหา ในการ refer upper level ครับ
แต่ตัวสูตร ยังผิดพลาด กรณีคำนวณจำนวน ซ้ำซ้อน ซึ่งผมต้องเข้าไปแก้สูตรดังกล่าว แต่ไม่เข้าใจการใช้งาน Index ร่วมกับ array และการอ้างอิงครับ

รบกวนช่วยชี้แนะครับ
ขอบคุณมากครับ

(ขออภัยครับ จะส่ง file มาภายหลังเนื่องจากขนาดใหญ่เกิน 300 KB ครับ)
เครื่องที่ใช้อยู่นี้ ไม่มี MS Excel ครับ

[snasui]

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

[eAglet]

ได้แนบ file ตัวอย่างมาแล้วครับ

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

ผมไม่เข้าใจการใช้สูตร INDEX กับ Array ที่แสดงใน file
หากจะให้ง่ายกว่านี้ ควรใช้สูตรใด?ได้ครับ

[snasui]

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

[eAglet]

ได้แนบตัวอย่าง file มาใหม่นะครับ
ซึ่ง ได้เขียนคำอธิบาย และคำถาม ไว้ใน file แล้วครับ

มีผู้แนะนำ สูตร INX และการใช้ IF แบบ array ว่าน่าจะแก้ปัญหานี้ได้ แต่ผมไม่เคยใช้และไม่เข้าใจ
จึงขอความอนุเคราะห์ ครับ

ขอบพระคุณครับ

[snasui]

ได้แนบตัวอย่าง file มาใหม่นะครับ
** Product ชื่อ VNUS รหัสการผลิต 033742.70.00 ต้องการผลิต 600 ชิ้น
ซึ่ง VNUS 1 ชิ้นประกอบไปด้วยวัตถุดิบ จำนวน 29 item (บรรทัด 6 ถึง 34)
- ใน Column "A" ระบุถึง level ในการผลิต คือ level 3 จะถูกนำไปผ่านกระบวนการ
เพื่อให้กลายเป็น level บน (เช่นบรรทัดที่ 8 วัตถุดิบชื่อ SOCLE RAW PART รหัส 033538.00.00
จะถูกนำไปผ่านกระบวนการ เพื่อให้กลายไปเป็น SOCLE MACHINED PART รหัส 033538.50.00
ซึ่ง 033538.50.00 นั้น จะอยู่ใน level 2
และ 033538.50.00 จะถูกนำไปประกอบเข้ากับ วัตถุดิบ level 2 อีก 3 ตัว
นั่นคือ
033538.50.00 SOCLE MACHINED PART
033549.50.40 GROOVED PIN TYPE C WITH GORGE 4X10
033549.50.45 GROOVED PIN TYPE C WITH GORGE 4X30
033549.50.41 GROOVED PIN TYPE C WITH GORGE 4 X 12

เพื่อให้กลายไปเป็น level 1 (033538.70.00) FOUNDATION CPL

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

ท้ายที่สุด level 1 ทุกชิ้น จำนวนรวม 14 ตัว จะประกอบกันเป็น VNUS:033742.70.00


** สูตรถูกใส่ไว้ใน column "H" ครับ
เพื่อต้องการให้สำแดงว่า หากต้องการผลิตชิ้นงาน 600 ตัว ต้องใช้วัตถุดิบแต่ละชิ้นเท่าใด

ปัญหา คือ
1) ในช่องที่ hilight สีเขียวไว้ จะเห็นว่าขาดงาน 033538.50.00 ไป 550 ชิ้น
ซึ่งต้องใช้ 033538.00.00 จำนวน 550 ชิ้น เพื่อนำมาผลิต จึงจะถูกต้อง
แต่สูตร คำนวนมาผิด
คือ สำแดงว่าต้องใช้ 033538.00.00 จำนวน 600 ชิ้น

ต้องเขียนสูตรอย่างไร?ครับ

เท่าที่ดูจากไฟล์แนบแล้วยังไม่ค่อยเคลียร์ครับ

คอลัมน์ F คือ Stock
คอลัมน์ D คือจำนวนที่ต้องการใช้

ดังนั้นคอลัมน์ H น่าจะบอกว่ามี Stock พอที่จะผลิตหรือไม่ หากไม่พอขาดเท่าใด หากพอสามารถกำหนดให้แสดงจำนวนที่ใช้ผลิต เพื่อสรุปให้ได้ว่าขนาดวัตถุดิบที่ใช้ผลิตอีกเท่าใด เช่นนี้เป็นต้น

ช่วยอธิบายให้อีกหน่อยครับ หรือหากเพื่อนสมาชิกท่านใดเข้าใจ ช่วยอธิบายเพิ่มอีกแรงก็ยินดีครับ

[eAglet]

ขอโทษครับ

ขออนุญาตอธิบายเพิ่มเติม
เนื่องจาก วัตถุดิบ ถูกใช้ในหลายๆ รุ่นผลิตภัณฑ์ ทำให้ต้องหักลบ stock ออกจำนวนหนึ่ง (column "G")

ส่วนที่เหลือจึงจะเป็น stock ส่วนที่ free จริงๆครับ
ส่วน column H แสดงจำนวนที่วัตถุดิบ ขาดไป ถูกแล้วครับ

ใน column H ถูกผูกไว้โดยมีสูตรคำนวน พร้อม สูตร Reference ครับ

[snasui]

และจาก

ปัญหา คือ
1) ในช่องที่ hilight สีเขียวไว้ จะเห็นว่าขาดงาน 033538.50.00 ไป 550 ชิ้น
ซึ่งต้องใช้ 033538.00.00 จำนวน 550 ชิ้น เพื่อนำมาผลิต จึงจะถูกต้อง
แต่สูตร คำนวนมาผิด
คือ สำแดงว่าต้องใช้ 033538.00.00 จำนวน 600 ชิ้น

ต้องเขียนสูตรอย่างไร?ครับ

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

[eAglet]

เซลที่ผิด คือ H8 ครับ ซึ่งระบุว่า "VNUS needs = -600" อันที่ถูกควรเป็น "VNUS needs = -550" ครับ

เนื่องจาก 033538.00.00 SOCLE RAW PART จะถูกนำไปผลิตเป้น 033538.50.00 SOCLE MACHINED PART

แต่ 033538.50.00 SOCLE MACHINED PART มีงานอยู่แล้ว จำนวน 50 pcs. ครับ แสดงว่าต้องการเพิ่มอีกเพียง 550 pcs.
แต่สูตรกลับแสดงว่าขาดไป ถึง -600 pcs. ครับ
****************************************

จากจำนวนที่ต้องการ 033538.50.00 SOCLE MACHINED PART ทั้งหมด 600 ชิ้น
แต่มี stock แล้ว 50 pcs. จึงต้องการผลิตเพิ่มอีก 550 ชิ้น ซึ่งต้องใช้ วัตถุดิบ 033538.00.00 SOCLE RAW PART จำนวน 550 ชิ้นครับ

แต่สูตรแสดง -600 ชิ้น ซึ่งผิด

[snasui]

ยังไม่ค่อยเคลียร์ แต่จะเขียนสูตรให้ทดสอบดูก่อน

ที่ H6 คีย์สูตร

=IF(E6="G",IF(ISNA(G6),"VNUS needs = "&F6-D6/1000,F6-G6-D6/1000),IF(ISNA(G6),"VNUS needs = "&IF(RIGHT(A5)<RIGHT(A6),F5+F6-D6,F6-D6),F6-G6-D6))

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