บทความนี้จะแสดงวิธีแก้ปัญหา Blending Linear Programming (การผสมผสานเชิงเส้น) ด้วย Excel Solver Linear Programming สำคัญเมื่อต้องการลดต้นทุนหรือเพิ่มกำไรในธุรกิจ และในชีวิตประจำวัน เช่น การปรับพฤติกรรมการกิน Blending LP เป็นกรณีพิเศษที่คำนวณหากำไร/ต้นทุนจากผลิตภัณฑ์ที่ผลิตจากการผสมวัตถุดิบหลายอย่าง เราจะใช้ 2 ตัวอย่างจากชีวิตจริง
Blending Linear Programming คืออะไร?
ในอุตสาหกรรมเคมีหรือโรงงานอาหาร ต้องผลิตสินค้าผสมวัตถุดิบหลายอย่าง เช่น ผลิตยาต้องผสมสารประกอบในสัดส่วนที่กำหนด ต้องคำนวณปริมาณวัตถุดิบที่ซื้อ, คุณภาพสินค้า ฯลฯ ต้องหาสัดส่วนการผสมที่เหมาะสมที่สุด โจทย์นี้คือ Blending Linear Programming ช่วยเรียนรู้การใช้วัตถุดิบในการผลิตอย่างเหมาะสม
2 ตัวอย่างแก้ Blending LP ด้วย Excel Solver
มี 2 ประเภท: Fixed Recipe (สูตรคงที่) และ Flexible Recipe (สูตรยืดหยุ่น)
Fixed Recipe: รู้สัดส่วนผสมแน่นอน เช่น ผลิต 2 สินค้าใหม่จากของเหลว A, B, C ผสม A-B (60-40%) และ A-C (80-20%) มีข้อมูล Revenue/Liter, Cost/Liter, วัตถุดิบที่มีอยู่ เป้าหมาย เพิ่มกำไรสูงสุด
Flexible Recipe: ไม่มีสัดส่วนผสมคงที่ ผลิตเหล็กหลายประเภท (Regular, Exclusive, Super) จากวัตถุดิบต่างๆ มีข้อมูลปริมาณวัตถุดิบ, ต้นทุน/ตัน, คะแนนคุณภาพ, ปริมาณการผลิตที่ต้องการ, ราคา/ตัน, คะแนนขั้นต่ำ มีพารามิเตอร์ Linearized Rating
1. Blending Linear Programming สำหรับปัญหา Fixed Recipe
ขั้นตอน:
- ตั้งสูตรคำนวณ Raw Usage ของวัตถุดิบ: ใน C12 ใส่
=SUMPRODUCT(C5:C9,$G$5:$G$9)(คืนค่า Raw Usage ของวัตถุดิบ A) ลาก Fill Handle ขวาถึง E12 - คำนวณ Revenue ใน I11:
=SUMPRODUCT(G5:G9,H5:H9)*C16(C16 = 3.7854 แปลงแกลลอนเป็นลิตร) - คำนวณ Cost ใน I12:
=SUMPRODUCT(C11:E11,C12:E12)*C16 - คำนวณ Profit:
=I11-I12 - ตั้งค่าขั้นต่ำของการผลิต (Minimum Production Requirement)
- ไป Data >> Solver (ถ้ายังไม่มี ให้เพิ่ม Add-in: File >> Options >> Add-ins >> Excel Add-ins >> Go >> Solver Add-in)
- ตั้งค่า Solver: Set Objective = I13 (Profit) → Max, By Changing Variable Cells = G5:G9 (สัดส่วนผสม)
- เพิ่ม Constraints: Raw Usage (C12:E12) ≤ Available (C14:E14), Production ≥ Minimum Required
- ติ๊ก Make Unconstrained Variables Non-Negative, เลือก Simplex LP > Solve > OK
- ได้ค่าวัตถุดิบที่ควรใช้เพื่อกำไรสูงสุด พร้อม Revenue, Cost, Profit
2. Excel Solver สำหรับ Flexible Recipe Blending LP
ขั้นตอน:
- เซลล์ F5:
=SUM(C5:E5)(รวมปริมาณผลิตเหล็กประเภท 1 จากวัตถุดิบแรก) ลากลงถึง F7 - เซลล์ C8:
=SUM(C5:C7)(รวมปริมาณผลิต Regular Steel) ทำซ้ำสำหรับ D8, E8 - เซลล์ C14:
=SUMPRODUCT($J$5:$J$7,C5:C7)(Linearized Rating) ลากขวาถึง E14 - เซลล์ C16:
=C12*C8(Linearized Rating ของเหล็กที่ผลิต) ลากขวาถึง E16 - เซลล์ I10 (Revenue):
=SUMPRODUCT(C11:E11,C8:E8) - เซลล์ I11 (Cost):
=SUMPRODUCT(I5:I7,F5:F7) - เซลล์ I12 (Profit):
=I10-I11 - ตั้งค่า Solver: Set Objective = I12 (Max), By Changing Variable Cells = C5:E7 (ปริมาณการผลิต)
- Constraints: Linearized Raw Rating (C14:E14) ≥ Linearized Min Required Rating (C16:E16), Production (C8:E8) ≥ Required (C10:E10), Raw Usage (F5:F7) ≤ Available (H5:H7)
- ติ๊ก Non-Negative, เลือก Simplex LP > Solve
- ได้ปริมาณวัตถุดิบที่ควรใช้เพื่อกำไรสูงสุด พร้อม Revenue, Cost, Profit
ส่วนฝึกปฏิบัติ
นี่คือชุดข้อมูลของบทความ สำหรับฝึกปฏิบัติด้วยตัวเอง
สรุป
บทความนี้ให้แนวคิดพื้นฐานการนำ Blending Linear Programming ไปแก้ปัญหาเพิ่มประสิทธิภาพจริงด้วย Excel Solver หากมีวิธีดีกว่าหรือข้อเสนอแนะ กรุณาแชร์ในกล่องความคิดเห็น จะช่วยปรับปรุงบทความต่อไป สำหรับคำถามเพิ่มเติม เยี่ยมชมเว็บไซต์ ExcelDemy
