รบกวนถามสูตรเกี่ยวกับ planning หน่อยนะครับ

[irResiSt]

ผมขอถามเกี่ยวกับสูตรของ excel หน่อยนะครับ
คือผมจะทำไฟล์ excel เพื่อช่วยแผนก planning จำลองจำนวน part ที่จะต้องซื้อและผลิตที่ความต้องการต่าง ๆ เช่น
ผมแตก BOM(Bill of Material) ของ part ตัวหนึ่งออกมา ซึ่งpartตัวนี้ประกอบไปด้วย part ย่อย ๆ อีกประมาณพันกว่าตัว -_-! ตัวอย่างตามไฟล์ที่แนบครับ

ผมขออธิบายตารางนะครับ

* level คือระดับขั้นของวัตถุดิบเช่น level .1 คือfinal part เกิดจากการประกอบ part level ..2 ทั้งหมดเข้าด้วยกัน นอกจะนี้จะเห็นว่าpart level ..2 บางตัวจะมี part level ...3 อยู่ข้างล่างหมายความว่าเราต้องใช้ part level...3 มาประกอบเป็น part level ..2, part level ....4 ก็จะใช้ประกอบเพื่อทำเป็น part level ...3

ปัญหาคือ ผมต้องเขียนสูตรเพื่อคำนวณปริมาณของวัตถุดิบที่จะต้องใช้ตามความต้องการที่เปลี่ยนไป เช่น ถ้าเราต้องการผลิต Final part อีก 100 ตัว
* เราต้องซื้อ part A อีก 130 ตัว(100-20+50) (เพราะมี stock อยู่แล้ว 20 ตัวและใช้ part A ผลิต Final part อย่างละ 1 ตัวและต้องการเก็บไว้เป็น spare อีก 50ตัว)
* part B เราต้องผลิต part B เพิ่มอีก 46 ตัว(100-54)
* แต่ part B เกิดจากการนำ part B1 มาประกอบดังนั้นเราต้องสร้าง part B1 ให้พอกับความต้องการที่จะสร้าง part B นั่นก็คือ 34 ตัว(46-12) (เพราะความต้องการที่จะใช้ของ part B ต้องการเพิ่มแค่ 46 ตัว และเรามีสต๊อคของ partB1 แล้ว 12 ตัว)
* แต่part B1 เกิดจากการนำ part B11 มาประกอบดังนั้นเราต้องสั่งซื้อ part B11 เพิ่มอีก 4ตัว (34-30)(เพราะความต้องการที่จะใช้ part B1เพิ่มมี 34 ตัวและเรามีสต๊อคของ part B11แล้ว 30ตัว)
* part C เราต้องผลิต part C เพิ่มอีก 65 ตัว(100-35)
* แต่ part C เกิดจากการนำ part C1,C2,C3,C4,C5 มาประกอบดังนั้นเราต้องสร้าง part C1,C2,C3,C4,C5 ให้พอกับความต้องการที่จะสร้าง part C นั่นก็คือ part C1 = 57 ตัว(65-8), C2 = 106ตัว(65-12*2เพราะใช้ 2 ตัวในการทำ part C 1 ตัว), C3 = 47ตัว(65-18), C4 = 50ตัว(65-15), C5 = 31ตัว(65-34)
* part C1 เกิดจากการนำ part C11 มาประกอบดังนั้นเราต้องสั่งซื้อ part C11 เพิ่มอีก 57ตัว (57-0)(เพราะความต้องการที่จะใช้ part C1เพิ่มมี 57 ตัวแต่เราไม่มีสต๊อคของ part C11เลย)
* part C4 เกิดจากการนำ part C41 มาประกอบดังนั้นเราต้องผลิต part C41 เพิ่มอีก 30ตัว (50-20)(เพราะความต้องการที่จะใช้ part C4เพิ่มมี 50 ตัวและเรามีสต๊อคของ part C41 20ตัว)
* part C41 เกิดจากการนำ part C411 มาประกอบดังนั้นเราต้องซื้อ part C411 เพิ่มอีก 0.61 กิโลกรัม (30*0.037-0.5)(เพราะความต้องการที่จะใช้ part C4เพิ่มมี 1.11 กิโลกรัมและเรามีสต๊อคของ part C411 0.5กิโลกรัม)

ตอนแรกผมจะลองใช้ if ธรรมดาแต่คิดว่าไม่น่าจะได้เพราะเงื่อนไขมันซับซ้อนเกินไป (เนื่องจาก part บางตัวประกอบไปด้วย part ย่อย ๆ หลายตัว และในการที่จะคำนวณ part ย่อย ๆ พวกนั้นต้องคำนวณโดยยึดเงื่อนไขของ part ที่สูงกว่า 1 level ลบด้วยจำนวนสต๊อคคงเหลือ)
ผมลองเขียน flow คร่าว ๆ แบบนี้อะครับ
- ให้ดูที่level ว่าเป็น level 1 ไหม ถ้าใช่ก็ให้เอาความต้องการที่จะผลิตเพิ่ม(demand) ลบจำนวนสต๊อคที่มี = จำนวนที่ต้องผลิตเพิ่ม
- ให้ดูที่level ว่าเป็น level 2 ไหม ถ้าใช่ให้ไปดู level 1 ว่าต้องผลิตเพิ่มเท่าไหร่ ลบด้วยจำนวนสต๊อคที่มี+จำนวนที่ต้องการสำรองไว้สำหรับ spare part
- ให้ดูที่level ว่าเป็น level 3 ไหม ถ้าใช่ให้ไปดู level 2 (ต้องระวังเพราะอาจมี level 2 หลายตัว ให้ดูที่level 2 นับขึ้นไปข้างบนตัวแรกเท่านั้น แล้วเอาจำนวนที่ต้องผลิตเพิ่มของ level 2 ลบด้วยจำนวนสต๊อคที่มีของ level 3+จำนวนที่ต้องการสำรองไว้สำหรับ spare part)
- ให้ดูที่level ว่าเป็น level 4 ไหม ถ้าใช่ให้ไปดู level 3 (ต้องระวังเพราะอาจมี level 3 หลายตัว ให้ดูที่level 3 นับขึ้นไปข้างบนตัวแรกเท่านั้นแล้วเอาจำนวนที่ต้องผลิตเพิ่มของ level 3 ลบด้วยจำนวนสต๊อคที่มีของ level 4
- บาง part อาจต้องใช้มากกว่า 1 ตัวในการผลิต part ที่ level สูงขึ้นไป(ตามจำนวนที่ระบุไว้ใน BOM)

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

[kmb]

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

ที่เซลล์ J2 เอา merge cell ออกก่อน
ที่เซลล์ J3 ใส่ค่า 0

ที่เซลล์ J4 คีย์สูตรนี้ครับ
=IF($A4=".1",$H$2*$D4-F4+H4,IF($A4="..2",$H$2*$D4-F4+H4,IF($A4="...3",LOOKUP(LEFT($C4,LEN(TRIM($C4))-1),TRIM(C$3:C4),K$3:K4)*$D4-F4+H4,IF($A4="....4",LOOKUP(LEFT($C4,LEN(TRIM($C4))-1),TRIM(C$3:C4),K$3:K4)*$D4-F4+H4,IF($A4=".....5",LOOKUP(LEFT($C4,LEN(TRIM($C4))-1),TRIM(C$3:C4),K$3:K4)*$D4-F4+H4,0)))))
Enter > copy ลงด้านล่างได้เลยครับ

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

แนว คิดคือผมเช็คก่อนว่ารายการนั้น ๆ เป็น level ไหน ถ้าเป็น level ย่อยก็จะไปหาค่าของ level ก่อนหน้าโดยใช้คอลัมน์ Description ซึ่งจะมีส่วนหน้าเหมือนกันแต่เพิ่มเลขย่อยมาต่อท้าย

ส่วนเรื่องให้แสดงสัญลักษณ์ ถ้าสต๊อคมีมากกว่าจำนวนที่ต้องการผลิต ก็ใช้ Conditional Formatting ซึ่งมีขั้นตอนดังนี้
Menu "Home" > Conditional Formatting > Highlight Cells Rules
> Less Than > ช่องหน้าใส่ 0 ส่วนช่องหลัง เลือก Custom Format ... จาก Drop down list
> เลือก Format แบบที่ต้องการ ซึ่งตามไฟล์แนบผมให้ระบายสีเหลือง

[irResiSt]

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

พอดี description ของ part ในไฟล์แนบอันแรกเป็นชื่อที่ผมสมมุติเองเพื่อให้เข้าใจง่ายอะครับ แต่จริงๆ แล้วdescription มันไม่มีความเกี่ยวเนื่องกันเลยครับเลยใช้สูตร-1 ไม่ได้อะครับ

ผมได้เปลี่ยน description ใหม่แล้วตามไฟล์แนบนะครับ

รบกวนอีกครั้งนะครับ ^ ^

[kmb]

ลองตามนี้ครับ เพื่อเป็นการเปรียบเทียบ ผมจึงทำตัวอย่างไว้ในคอลัมน์ K เหมือนเดิมครับ

ที่ชีต Sheet2 เซลล์ K4 ลองสูตรนี้ครับ
=IF($A4=".1",$H$2*$D4-F4+H4,IF($A4="..2",$H$2*$D4-F4+H4,IF($A4="...3",INDEX(K$3:K3,MAX(IF($A$3:A3="..2",ROW($A$3:A3)-2)))*$D4-F4+H4,IF($A4="....4",INDEX(K$3:K3,MAX(IF($A$3:A3="...3",ROW($A$3:A3)-2)))*$D4-F4+H4,IF($A4=".....5",INDEX(K$3:K3,MAX(IF($A$3:A3="....4",ROW($A$3:A3)-2)))*$D4-F4+H4,0)))))
Ctrl+Shift+Enter (เป็นสูตร Array หลังกดแล้วจะได้ { } คลุมสูตร) > copy ลงด้านล่างได้เลยครับ

ทั้งนี้ผมคิดว่าถ้ามีการแปลงค่า Level no. ให้เป็นตัวเลขจะทำให้สูตรกระชับกว่าและครอบคลุมถึงกรณีที่เกิดมี Level มากกว่า 5 ด้วย ตัวอย่างอยู่ในชีต Sheet3
=IF($A4<=2,$H$2*$D4-F4+H4,IF($A4>2,INDEX(K$3:K3,MAX(IF($A$3:A3=($A4-1),ROW($A$3:A3)-2)))*$D4-F4+H4,""))
Ctrl+Shift+Enter (เป็นสูตร Array หลังกดแล้วจะได้ { } คลุมสูตร) > copy ลงด้านล่างได้เลยครับ

ลองดูเพิ่มเติมตามไฟล์แนบครับ

[irResiSt]

เย้ ๆ ได้แล้วครับ

ผมลองเอาไปใช้กับ part จริง ๆ ของบริษัทผมซึ่งมี หนึ่งพันกว่าบรรทัดมีถึง ......7 เลเวลเลยครับ

แต่ตอนนี้ได้น่าจะโอเคแล้วครับ

ขอบคุณมากนะครับ ^ ^

[irResiSt]

อ้างอิงจากกระทู้ http://www.snasui.com/viewtopic.php?f=3&t=925

คุณ kmb ได้ทำสูตรให้ผมแล้ว แต่อยากรบกวนขอเพิ่มอีกนิดนะครับ
คือสูตรที่คุณ kmb สร้างเราจะรู้ปริมาณของ part ที่เราต้องสร้างเพิ่มเช่นต้องการผลิต part หลัก(level .1) 100 ตัว ในสูตรก็จะบอกว่าจะต้องผลิต part ย่อย(level ..2, ...3, ...4)อีกกี่ตัว

ในกรณีที่ part ย่อยมีมากกว่า part หลัก สูตรจะแสดงค่าติดลบเช่น ต้องการผลิต part หลัก(level .1) 100 ตัว แต่ part ย่อย(level ..2) มี 130 ตัวสูตรจะแสดงค่า = -30 ถ้า part ย่อย(level ...3) มี stock = 50 ตัว สูตรจะแสดงค่า = -80 (-30+-50) ซึ่งถ้าเราจะดูแค่ว่า part ตัวไหนต้องผลิตเพิ่มก็จะดู part ที่เป็น + ทั้งหมด ส่วนที่เป็น - แสดงว่าเรามีเกินความต้องการ

ปัญหาของผมคือตอนนี้ฝ่ายวางแผนต้องการที่จะจัดการ part ที่มีเกินความต้องการ (scrap หรือขายทิ้ง)..จากสูตรเดิมเราจะไม่ทราบจำนวนที่แท้จริงของ part ที่มีเกินความต้องการ เนื่องจากสูตรจะแสดงว่าเรามีpart level..2 เกินเท่ากับ 30และ part level...3 เกินเท่ากับ 80 (ยอดรวมของ part ที่เกินจะเท่ากับ 110) ซึ่งความจริงควรจะแสดง level..2 เท่ากับ 30และlevel..3 เท่ากับ 50(เท่ากับจำนวนที่เหลืออยู่ใน stock --> ยอดรวมของ part ที่เกินเท่ากับ 80).

ขอรบกวนช่วย update สูตรให้ผมหน่อยนะครับ

ขอบคุณล่วงหน้าครับ ^ ^

[tupthai]

ถ้าผมเข้าใจถูก ลองดูครับ
คอลัมน์ L ใส่ Level no. เป็นตัวเลขตามคำแนะนำของคุณ kmb
ที่ K4 พิมพ์สูตร
=IF($L4=1,$H$2*$D4-$F4+$H4,IF(LOOKUP(2,1/($L4-1=$L$3:$L3),$K$3:$K3)<0,-$F4,LOOKUP(2,1/($L4-1=$L$3:$L3),$K$3:$K3)*$D4-$F4+$H4))

enter>copy ลงด้านล่าง

[irResiSt]

ถ้าผมเข้าใจถูก ลองดูครับ
คอลัมน์ L ใส่ Level no. เป็นตัวเลขตามคำแนะนำของคุณ kmb
ที่ K4 พิมพ์สูตร
=IF($L4=1,$H$2*$D4-$F4+$H4,IF(LOOKUP(2,1/($L4-1=$L$3:$L3),$K$3:$K3)<0,-$F4,LOOKUP(2,1/($L4-1=$L$3:$L3),$K$3:$K3)*$D4-$F4+$H4))

enter>copy ลงด้านล่าง

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