การกำหนดและแก้ไขปัญหาโดยใช้ Solver

ในตัวอย่างต่อไปนี้ ระดับการโฆษณาในแต่ละไตรมาสจะส่งผลต่อจํานวนหน่วยที่ขายโดยอ้อมที่กําหนดรายได้จากการขาย ค่าใช้จ่ายที่เกี่ยวข้อง และกําไร Solver สามารถเปลี่ยนงบประมาณรายไตรมาสสําหรับการโฆษณา (เซลล์ตัวแปรการตัดสิน B5:C5) สูงสุดถึงข้อจํากัดงบประมาณรวม $20,000 (เซลล์ F5) จนกว่ากําไรรวม (เซลล์วัตถุประสงค์ F7) จะถึงจํานวนสูงสุดที่เป็นไปได้ ค่าในเซลล์ตัวแปรจะถูกใช้เพื่อคํานวณกําไรสําหรับแต่ละไตรมาส ดังนั้นจึงเกี่ยวข้องกับเซลล์วัตถุประสงค์ของสูตร F7, =SUM (Q1 Profit:Q2 Profit)

1. เซลล์ตัวแปร

2. เซลล์ที่มีข้อจำกัด

3. เซลล์วัตถุประสงค์

หลังจาก Solver ทำงาน ค่าใหม่จะเป็นดังต่อไปนี้

1. บนแท็บ ข้อมูล ในกลุ่ม วิเคราะห์ ให้คลิก Solver
   

   หมายเหตุ: ถ้าคําสั่ง Solver หรือกลุ่ม การวิเคราะห์ ไม่พร้อมใช้งาน คุณจําเป็นต้องเปิดใช้งาน Solver Add-in ดู: วิธีเปิดใช้งาน Solver Add-in

   

2. ในกล่อง ตั้งค่าวัตถุประสงค์ ให้ใส่ การอ้างอิงเซลล์ หรือ ชื่อ สําหรับเซลล์วัตถุประสงค์ เซลล์วัตถุประสงค์ต้องมีสูตร

3. เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้

   • ถ้าคุณต้องการให้ค่าของเซลล์วัตถุประสงค์มีค่ามากที่สุดเท่าที่จะเป็นไปได้ ให้คลิก Max

   • ถ้าคุณต้องการให้ค่าของเซลล์วัตถุประสงค์มีค่าน้อยที่สุดเท่าที่จะเป็นไปได้ ให้คลิก Min

   • ถ้าคุณต้องการให้ค่าของเซลล์วัตถุประสงค์เป็นค่าๆ หนึ่ง ให้คลิก Value of แล้วพิมพ์ค่านั้นในกล่อง

   • ในกล่อง โดยการเปลี่ยนเซลล์ตัวแปร ให้ใส่ชื่อหรือการอ้างอิงสําหรับแต่ละช่วงเซลล์ตัวแปรการตัดสิน แยกการอ้างอิงที่ไม่อยู่ติดกันด้วยเครื่องหมายจุลภาค เซลล์ตัวแปรต้องเกี่ยวข้องโดยตรงหรือโดยอ้อมกับเซลล์วัตถุประสงค์ คุณสามารถระบุเซลล์ตัวแปรได้สูงสุด 200 เซลล์

4. ในกล่อง Subject to the Constraints ให้ใส่ค่าจำกัดใดๆ ที่คุณต้องการนำไปใช้ โดยทำดังต่อไปนี้

   1. ในกล่องโต้ตอบ Solver Parameters ให้คลิก Add

   2. ในกล่อง Cell Reference ให้ใส่การอ้างอิงเซลล์หรือชื่อของช่วงของเซลล์ที่คุณต้องการจำกัดค่า

   3. คลิกความสัมพันธ์ ( <=, =, >=, int, bin หรือ dif ) ที่คุณต้องการระหว่างเซลล์ที่อ้างอิงและข้อจํากัด ถ้าคุณคลิก intจํานวนเต็มจะปรากฏในกล่อง Constraint ถ้าคุณคลิก Binไบนารีจะปรากฏในกล่อง ข้อจํากัด ถ้าคุณคลิก difalldifferent จะปรากฏในกล่อง Constraint

   4. ถ้าคุณเลือก <=, = หรือ >= สำหรับความสัมพันธ์ในกล่อง Constraint ให้พิมพ์ตัวเลข การอ้างอิงเซลล์หรือชื่อ หรือสูตร

   5. เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้

      • เมื่อต้องการยอมรับข้อจำกัดและเพิ่มข้อจำกัดอื่นอีก ให้คลิก Add

      • เมื่อต้องการยอมรับข้อจํากัดและกลับไปยังกล่องโต้ตอบ Solver Parameters ให้คลิก ตกลง
        โน้ต    คุณสามารถใช้ความสัมพันธ์แบบ int, bin และ dif เฉพาะในข้อจํากัดบนเซลล์ตัวแปรการตัดสินเท่านั้น

        คุณสามารถเปลี่ยนหรือลบข้อจำกัดที่มีอยู่โดยการทำดังต่อไปนี้

   6. ในกล่องโต้ตอบ Solver Parameters ให้คลิกข้อจำกัดที่คุณต้องการเปลี่ยนแปลงหรือลบ

   7. คลิก Change แล้วทำการเปลี่ยนแปลง หรือคลิก Delete

5. คลิก Solve แล้วเลือกทำอย่างใดอย่างหนึ่งต่อไปนี้

   • เมื่อต้องการเก็บค่าของคำตอบบนเวิร์กชีต ในกล่องโต้ตอบ Solver Results ให้คลิก Keep Solver Solution

   • เมื่อต้องการคืนกลับเป็นค่าเดิมก่อนที่คุณจะคลิก Solve ให้คลิก Restore Original Values

   • คุณสามารถขัดจังหวะกระบวนการแก้ไขปัญหาได้โดยการกด Esc Excel จะคํานวณเวิร์กชีตใหม่ด้วยค่าสุดท้ายที่พบสําหรับเซลล์ตัวแปรการตัดสิน

   • เมื่อต้องการสร้างรายงานที่ยึดตามโซลูชันของคุณหลังจากที่ Solver พบโซลูชัน คุณสามารถคลิกชนิดรายงานในกล่อง รายงาน แล้วคลิก ตกลง รายงานจะถูกสร้างขึ้นบนเวิร์กชีตใหม่ในเวิร์กบุ๊กของคุณ ถ้า Solver ไม่พบโซลูชัน จะมีเพียงบางรายงานหรือไม่มีรายงานที่พร้อมใช้งานเท่านั้น

   • เมื่อต้องการบันทึกค่าของเซลล์ตัวแปรการตัดสินของคุณเป็นสถานการณ์สมมติที่คุณสามารถแสดงภายหลังได้ ให้คลิก Save Scenario ในกล่องโต้ตอบ Solver Results แล้วพิมพ์ชื่อของสถานการณ์ในกล่อง Scenario Name

1. หลังจากคุณกำหนดปัญหา ให้คลิก Options ในกล่องโต้ตอบ Solver Parameters

2. ในกล่องโต้ตอบ Options ให้เลือกกล่องกาเครื่องหมาย Show Iteration Results เพื่อดูค่าของการลองแทนค่าแต่ละครั้ง แล้วคลิก OK

3. ในกล่องโต้ตอบ Solver Parameters ให้คลิก Solve

4. ในกล่องโต้ตอบ Show Trial Solution ให้เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้

   • เมื่อต้องการหยุดกระบวนการแก้ไขปัญหา และแสดงกล่องโต้ตอบ Solver Results ให้คลิก Stop

   • เมื่อต้องการทำกระบวนการแก้ไขปัญหาต่อ และแสดงการลองแทนค่าถัดไป ให้คลิก Continue

1. ในกล่องโต้ตอบ Solver Parameters ให้คลิก Options

2. เลือกตัวเลือกหรือใส่ค่าสำหรับตัวเลือกใดๆ บนแท็บ All Methods, GRG Nonlinear และ Evolutionary ในกล่องโต้ตอบ

1. ในกล่องโต้ตอบ Solver Parameters ให้คลิก Load/Save

2. ใส่ช่วงของเซลล์สำหรับพื้นที่แบบจำลอง แล้วคลิก Save หรือ Load อย่างใดอย่างหนึ่ง

   เมื่อคุณบันทึกตัวแบบ ให้ใส่การอ้างอิงสําหรับเซลล์แรกของช่วงแนวตั้งของเซลล์ว่างที่คุณต้องการวางแบบจําลองปัญหา เมื่อคุณโหลดแบบจําลอง ให้ป้อนการอ้างอิงสําหรับช่วงของเซลล์ทั้งหมดที่มีแบบจําลองปัญหา

   เคล็ดลับ: คุณสามารถบันทึกส่วนที่เลือกสุดท้ายในกล่องโต้ตอบ Solver Parameters กับเวิร์กชีตได้โดยการบันทึกเวิร์กบุ๊ก เวิร์กชีตแต่ละเวิร์กชีตในเวิร์กบุ๊กอาจมีการเลือก Solver ของตนเอง และเวิร์กชีตทั้งหมดจะถูกบันทึก คุณยังสามารถระบุปัญหาได้มากกว่าหนึ่งปัญหาสําหรับเวิร์กชีตโดยการคลิก โหลด/บันทึก เพื่อบันทึกปัญหาทีละรายการ

คุณสามารถเลือกอัลกอริธึมหรือวิธีแก้ไขปัญหาวิธีใดวิธีหนึ่งจากสามวิธีต่อไปนี้ในกล่องโต้ตอบ Solver Parameters ได้แก่

• Generalized Reduced Gradient (GRG) Nonlinear    ใช้สำหรับปัญหาที่มีลักษณะไม่เป็นเชิงเส้นที่เป็นแบบเรียบ

• LP Simplex    ใช้สำหรับปัญหาที่มีลักษณะเป็นเชิงเส้น

• Evolutionary    ใช้สำหรับปัญหาที่มีลักษณะไม่เรียบ

ในตัวอย่างต่อไปนี้ ระดับการโฆษณาในแต่ละไตรมาสจะส่งผลต่อจํานวนหน่วยที่ขายโดยอ้อมที่กําหนดรายได้จากการขาย ค่าใช้จ่ายที่เกี่ยวข้อง และกําไร Solver สามารถเปลี่ยนงบประมาณรายไตรมาสสําหรับการโฆษณา (เซลล์ตัวแปรการตัดสิน B5:C5) สูงสุดถึงข้อจํากัดงบประมาณรวม $20,000 (เซลล์ D5) จนกว่ากําไรรวม (เซลล์วัตถุประสงค์ D7) จะถึงจํานวนสูงสุดที่เป็นไปได้ ค่าในเซลล์ตัวแปรจะถูกใช้เพื่อคํานวณกําไรสําหรับแต่ละไตรมาส ดังนั้นจึงเกี่ยวข้องกับเซลล์วัตถุประสงค์ของสูตร D7, =SUM(Q1 Profit:Q2 Profit)

เซลล์ตัวแปร

เซลล์ที่มีข้อจํากัด

เซลล์วัตถุประสงค์

หลังจาก Solver ทำงาน ค่าใหม่จะเป็นดังต่อไปนี้

1. คลิก ข้อมูล > Solver

   

2. ใน ตั้งค่าวัตถุประสงค์ ให้ใส่ การอ้างอิงเซลล์ หรือชื่อสําหรับเซลล์วัตถุประสงค์

   หมายเหตุ: เซลล์วัตถุประสงค์ต้องมีสูตร

3. เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้

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

4. ในกล่อง โดยการเปลี่ยนเซลล์ตัวแปร ให้ใส่ชื่อหรือการอ้างอิงสําหรับแต่ละช่วงเซลล์ตัวแปรการตัดสิน แยกการอ้างอิงที่ไม่ติดกันด้วยเครื่องหมายจุลภาค

   เซลล์ตัวแปรต้องเกี่ยวข้องโดยตรงหรือโดยอ้อมกับเซลล์วัตถุประสงค์ คุณสามารถระบุเซลล์ตัวแปรได้สูงสุด 200 เซลล์

5. ในกล่อง เรื่องไปยังข้อจํากัด ให้เพิ่มข้อจํากัดใดๆ ที่คุณต้องการนําไปใช้

   เมื่อต้องการเพิ่มข้อจํากัด ให้ทําตามขั้นตอนต่อไปนี้:

   1. ในกล่องโต้ตอบ Solver Parameters ให้คลิก Add

   2. ในกล่อง Cell Reference ให้ใส่การอ้างอิงเซลล์หรือชื่อของช่วงของเซลล์ที่คุณต้องการจำกัดค่า

   3. บนเมนูป็อปอัพ <= ความสัมพันธ์ ให้เลือกความสัมพันธ์ที่คุณต้องการระหว่างเซลล์ที่อ้างอิงและข้อจํากัด ถ้าคุณเลือก <=, = หรือ >= ในกล่อง Constraint ให้พิมพ์ตัวเลข การอ้างอิงเซลล์หรือชื่อ หรือสูตร

      หมายเหตุ: คุณสามารถใช้ได้เฉพาะความสัมพันธ์แบบ int, bin และ dif ในข้อจํากัดบนเซลล์ตัวแปรการตัดสินเท่านั้น

   4. เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้

   เมื่อต้องการ	ให้ทำสิ่งนี้
   ยอมรับข้อจํากัดและเพิ่มข้อจํากัดอื่น	คลิก เพิ่ม
   ยอมรับข้อจํากัดและกลับไปยังกล่องโต้ตอบ Solver Parameters	คลิก ตกลง

6. คลิก Solve แล้วเลือกทําอย่างใดอย่างหนึ่งต่อไปนี้

   เมื่อต้องการ	ให้ทำสิ่งนี้
   เก็บค่าโซลูชันบนแผ่นงาน	คลิก เก็บโซลูชัน Solver ในกล่องโต้ตอบ Solver Results
   คืนค่าข้อมูลต้นฉบับ	คลิก คืนค่าดั้งเดิม

1. คลิก ข้อมูล > Solver

   

2. หลังจากที่คุณระบุปัญหา ในกล่องโต้ตอบ Solver Parameters ให้คลิก Options

3. เลือกกล่องกาเครื่องหมาย แสดงผลลัพธ์การคํานวณซ้ํา เพื่อดูค่าของโซลูชันเวอร์ชันทดลองใช้แต่ละวิธี แล้วคลิก ตกลง

4. ในกล่องโต้ตอบ Solver Parameters ให้คลิก Solve

5. ในกล่องโต้ตอบ แสดงโซลูชันเวอร์ชันทดลองใช้ ให้เลือกทําอย่างใดอย่างหนึ่งต่อไปนี้:

   เมื่อต้องการ	ให้ทำสิ่งนี้
   หยุดกระบวนการแก้ไขปัญหาและแสดงกล่องโต้ตอบ Solver Results	คลิก หยุด
   ดําเนินกระบวนการแก้ไขปัญหาต่อไปและแสดงโซลูชันรุ่นทดลองใช้ถัดไป	คลิก Continue

1. คลิก ข้อมูล > Solver

   

2. คลิก ตัวเลือก จากนั้นในกล่องโต้ตอบ ตัวเลือก หรือ ตัวเลือก Solver ให้เลือกตัวเลือกใดตัวเลือกหนึ่งต่อไปนี้

   เมื่อต้องการ	ให้ทำสิ่งนี้
   กําหนดเวลาและการคํานวณซ้ําของโซลูชัน	บนแท็บ วิธีการทั้งหมด ภายใต้ ขีดจํากัดการแก้ไข ในกล่อง เวลาสูงสุด (วินาที) ให้พิมพ์จํานวนวินาทีที่คุณต้องการอนุญาตสําหรับเวลาโซลูชัน จากนั้น ในกล่อง รอบการคํานวณซ้ํา ให้พิมพ์จํานวนครั้งสูงสุดที่คุณต้องการอนุญาต หมายเหตุ: ถ้ากระบวนการแก้ไขปัญหาถึงเวลาสูงสุดหรือจํานวนครั้งของการคํานวณซ้ําก่อนที่ Solver จะค้นหาโซลูชัน Solver จะแสดงกล่องโต้ตอบ แสดงโซลูชันทดลองใช้
   ตั้งค่าระดับของความแม่นยํา	บนแท็บ วิธีการทั้งหมด ในกล่อง ความแม่นยําของข้อจํากัด ให้พิมพ์ระดับความแม่นยําที่คุณต้องการ ยิ่งตัวเลขน้อย ความแม่นยําก็จะยิ่งสูงขึ้นเท่านั้น
   ตั้งค่าระดับของการบรรจบกัน	บนแท็บ GRG Nonlinear หรือ Evolutionary ในกล่อง Convergence ให้พิมพ์จํานวนการเปลี่ยนแปลงสัมพัทธ์ที่คุณต้องการอนุญาตในรอบ 5 ครั้งสุดท้ายก่อนที่ Solver จะหยุดด้วยโซลูชัน ยิ่งตัวเลขน้อย ก็ยิ่งอนุญาตให้มีการเปลี่ยนแปลงสัมพัทธ์น้อยลง

3. คลิก ตกลง

4. ในกล่องโต้ตอบ Solver Parameters ให้คลิก Solve หรือ Close

1. คลิก ข้อมูล > Solver

   

2. คลิก โหลด/บันทึก ใส่ช่วงเซลล์สําหรับพื้นที่แบบจําลอง แล้วคลิก บันทึก หรือ โหลด

   เมื่อคุณบันทึกตัวแบบ ให้ใส่การอ้างอิงสําหรับเซลล์แรกของช่วงแนวตั้งของเซลล์ว่างที่คุณต้องการวางแบบจําลองปัญหา เมื่อคุณโหลดแบบจําลอง ให้ป้อนการอ้างอิงสําหรับช่วงของเซลล์ทั้งหมดที่มีแบบจําลองปัญหา

   เคล็ดลับ: คุณสามารถบันทึกส่วนที่เลือกสุดท้ายในกล่องโต้ตอบ Solver Parameters กับแผ่นงานโดยการบันทึกเวิร์กบุ๊ก แผ่นงานแต่ละแผ่นในเวิร์กบุ๊กอาจมีการเลือก Solver ของตนเอง และแผ่นงานทั้งหมดจะถูกบันทึก คุณยังสามารถระบุปัญหาได้มากกว่าหนึ่งปัญหาสําหรับแผ่นงานโดยการคลิก โหลด/บันทึก เพื่อบันทึกปัญหาทีละรายการ

1. คลิก ข้อมูล > Solver

   

2. บนเมนูป็อปอัพ เลือกวิธีแก้ไขปัญหา ให้เลือกอย่างใดอย่างหนึ่งต่อไปนี้: