Excel Solver ใช้ง่าย: คู่มือเร็ว Integer Linear Programming

บทความนี้เรียนรู้ การแก้ Integer Linear Programming ใน Excel ใน Microsoft Excel ผู้ใช้แก้โปรแกรมเชิงเส้นจำนวนเต็มได้เร็วด้วย Solver Add-in จะสาธิตกระบวนการขั้นตอนง่าย รวมตัวอย่าง Mixed-Integer Linear Programming ในส่วนท้าย เริ่มกันเลย

ดาวน์โหลดสมุดงานฝึกหัด

ดาวน์โหลดได้ที่นี่

Integer Linear Programming คืออะไร?

Integer linear programming เป็นวิธีคณิตศาสตร์ที่ประกอบด้วยตัวแปรจำนวนเต็ม ฟังก์ชันวัตถุประสงค์เชิงเส้น และสมการ ใช้หาค่าต่ำสุด/สูงสุดของปัญหาภายใต้เงื่อนไข เป็นเครื่องมือจัดสรรทรัพยากรจำกัดอย่างมีประสิทธิภาพ ประกอบด้วยปัจจัยหลัก:

• Decision Variables: ตัวแปรที่ตัดสินใจเพื่อหาค่าต่ำสุด/สูงสุดของฟังก์ชันวัตถุประสงค์
• Objective Function: ฟังก์ชันช่วยหาตัวแปรตัดสินใจ แสดงความสัมพันธ์ระหว่างผลลัพธ์และตัวแปร
• Constraints: ฟังก์ชันเงื่อนไขที่กำหนดเงื่อนไขของคำตอบที่เป็นไปได้

นอกจาก Integer Linear Programming จะมีตัวอย่าง Mixed-Integer Linear Programming ที่มีตัวแปรทั้งต่อเนื่องและจำนวนเต็ม

ขั้นตอนแก้ Integer Linear Programming ใน Excel

ใช้โจทย์ตัวอย่างอธิบาย ขออ่านเข้าใจดีเพื่อหา Constraints และ Objective function

สมมติ เครื่องจักรผลิต 2 ผลิตภัณฑ์สลับกันได้ ความจุรายวัน: สถานที่ 1 ผลิตได้สูงสุด 20 หน่วย ผลิตภัณฑ์ 1 และ 10 หน่วย ผลิตภัณฑ์ 2 หรือปรับเครื่องให้ผลิตได้สูงสุด 12 หน่วย ผลิตภัณฑ์ 1 และ 25 หน่วย ผลิตภัณฑ์ 2 ตลาดต้องการสูงสุด 35 หน่วย รวม กำไรต่อหน่วย $10 และ $12 ต้องเลือกตั้งค่าเครื่องไหน?

ขั้นตอนที่ 1: วิเคราะห์โจทย์และสร้างชุดข้อมูล

• เข้าใจปัญหา Integer Linear Programming และวิเคราะห์
• จากโจทย์ได้:

Decision Variables:

• X1: ปริมาณการผลิต ผลิตภัณฑ์ 1
• X2: ปริมาณการผลิต ผลิตภัณฑ์ 2
• Y: 1 หากเลือกตั้งค่าแรก หรือ 0 หากเลือกตั้งค่าที่สอง

Objective Function:

Z = 10X1 + 12X2

Constraints:

• X1 + X2 <= 35 (ความต้องการตลาดสูงสุด 35 หน่วย)
• X1 - 8Y <= 12 (เงื่อนไขเฉพาะผลิตภัณฑ์ 1)
• X2 + 15Y <= 25 (เงื่อนไขผลิตภัณฑ์ 2)
• Y = {0,1} (Y เป็น 0 หรือ 1)
• X1, X2 >= 0 (ปริมาณผลิตไม่ติดลบ)

• สร้างชุดข้อมูลใน Excel ตามภาพ เก็บเงื่อนไข ฟังก์ชัน ตัวแปร

ขั้นตอนที่ 2: โหลด Solver Add-in ใน Excel

• โหลด Solver Add-in หากโหลดแล้ว ข้ามไปขั้นตอน 3
• คลิกแท็บ File

• เลือก Options มุมซ้ายล่าง
• หน้าต่าง Excel Options เปิด

• เลือก Add-ins > Excel Add-ins > Go ใน Manage
• กล่อง Add-ins ปรากฏ

• ติ๊ก Solver Add-in กด OK

• จะเห็น Solver ในส่วน Analysis ของแท็บ Data

ขั้นตอนที่ 3: ใส่สัมประสิทธิ์เงื่อนไขและฟังก์ชันวัตถุประสงค์

• ใส่เงื่อนไขและฟังก์ชันวัตถุประสงค์ในชุดข้อมูล
• ใส่สัมประสิทธิ์เงื่อนไขและฟังก์ชันวัตถุประสงค์
• เงื่อนไขแรก X1 + X2 <= 35 สัมประสิทธิ์ X1=1, X2=1, Y=1, เครื่องหมาย <=, ขีดจำกัด 35

• ทำซ้ำเติมสัมประสิทธิ์เงื่อนไขทั้งหมด

• เลือก Cell E10 พิมพ์สูตร:

สูตรใช้ SUMPRODUCT คูณตัวแปรตัดสินใจกับตัวแปรเงื่อนไขแล้วบวกกัน B6×B10 + C6×C10 + D6×D10

• กด Enter ลาก Fill Handle ลง

• ใส่สัมประสิทธิ์ฟังก์ชันวัตถุประสงค์ใน Cell B16 ถึง C16
• ฟังก์ชันวัตถุประสงค์: Z = 10X1 + 12X2

• เลือก Cell E16 พิมพ์สูตร:

• กด Enter ได้ชุดข้อมูลสมบูรณ์

ขั้นตอนที่ 4: ใส่พารามิเตอร์ Solver

• ไปแท็บ Data เลือก Solver จาก Analyze จะเปิด Solver Parameters

• Set Objective: เซลล์ค่าฟังก์ชันวัตถุประสงค์ ($E$16)
• เลือก Max (หาค่าสูงสุด)
• By Changing Variable Cells: $B$6:$D$6 (ตัวแปรตัดสินใจ)

ขั้นตอนที่ 5: เพิ่มเงื่อนไข (Subject to Constraints)

• เพิ่มเงื่อนไข ระบุประเภทตัวแปร (binary/integer) และความสัมพันธ์
• กด Add จะเปิด Add Constraint dialog box

• ใน Add Constraint พิมพ์ $D$6 ใน Cell Reference เลือก bin จาก dropdown (D6 คือ Y = 0 หรือ 1 = binary)
• กด OK

• กด Add อีกครั้ง

• พิมพ์ $E$10:$E$12 ใน Cell Reference, เลือก <=, พิมพ์ =$G$10:$G$12 ใน Constraint กด OK

• กด Add อีกครั้งใน Solver Parameters

• พิมพ์ $B$6:$C$6 ใน Cell Reference เลือก int (X1, X2 เป็นจำนวนเต็ม) กด OK

ขั้นตอนที่ 6: เลือกวิธีการแก้ (Solving Method)

• เลือก Simplex LP ใน Select a Solving Method กด Solve
• ติ๊ก Make Unconstrained Variables Non-Negative

• กด Solve จะมี Solver Results ปรากฏ กด OK

ขั้นตอนที่ 7: คำตอบ Integer Linear Programming

• คำตอบจะอยู่ในเซลล์ที่ต้องการ ในกรณีนี้ ตั้งค่าเครื่องที่ 2 ให้ผลลัพธ์ดีที่สุด

ขั้นตอนที่ 8: สร้าง Answer Report

• ใน Solver Results เลือก Answer ใน Reports กด OK

• รายงานจะอยู่ในชีตใหม่

ตัวอย่าง Mixed Integer Linear Programming ใน Excel

ตัวอย่างง่าย Mixed-Integer Linear Programming ทำตามขั้นตอนดังนี้

Objective Function:

Z = 2.39X1 + 1.99X2 + 2.99X3 + 300Y1 + 250Y2 + 400Y3

Constraints:

• X1 + X2 + X3 = 1000
• X1 - 400Y1 <= 0
• X2 - 550Y2 <= 0
• X3 - 600Y3 <= 0

X1, X2, X3 เป็นจำนวนเต็ม Y1, Y2, Y3 เป็น binary หา ค่าต่ำสุดของ Z

ขั้นตอน:

• สร้างชุดข้อมูลเก็บสัมประสิทธิ์ Decision Variables, Constraints, Objective Function

• ใส่สัมประสิทธิ์ฟังก์ชันวัตถุประสงค์

• ใส่สัมประสิทธิ์เงื่อนไข คอลัมน์ Total เว้นว่าง

• เลือก Cell H10 พิมพ์สูตร:

• กด Enter ลาก Fill Handle ลง

• พิมพ์สูตรใน Cell H6:

• กด Enter

• ไปแท็บ Data เลือก Solver เปิด Solver Parameters

• ตั้ง Objective ที่ Cell $H$6 เป็น Min โดยเปลี่ยน $B$6:$G$6

• กด Add เพิ่ม Constraints ทีละอัน เลือก Simplex LP กด Solve

• Solver Results ปรากฏ กด OK

• ผลลัพธ์สุดท้าย

สรุป

บทความนี้สาธิตขั้นตอน แก้ Integer Linear Programming ใน Excel หวังว่าจะช่วยงานได้ง่ายขึ้น มีสมุดงานฝึกหัดด้านบน ดาวน์โหลดทดสอบทักษะได้ เยี่ยมชม เว็บไซต์ ExcelDemy สำหรับบทความเพิ่มเติม หากมีข้อเสนอแนะหรือคำถาม คอมเมนต์ด้านล่างได้เลย