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

Solver คือโปรแกรม Add-in ของ Microsoft Excel ที่คุณสามารถใช้สําหรับ การวิเคราะห์แบบ What-If ใช้ Solver เพื่อค้นหาค่าที่เหมาะสม (สูงสุดหรือต่ําสุด) สําหรับ สูตร ในเซลล์เดียว ซึ่งเรียกว่าเซลล์วัตถุประสงค์ ตามข้อจํากัด หรือขีดจํากัดบนค่าของเซลล์สูตรอื่นๆ บนเวิร์กชีต Solver ทํางานกับกลุ่มของเซลล์ เรียกว่าตัวแปรการตัดสิน หรือเซลล์ตัวแปรที่ใช้ในการคํานวณสูตรในเซลล์วัตถุประสงค์และเซลล์ข้อจํากัด Solver จะปรับค่าในเซลล์ตัวแปรการตัดสินเพื่อให้เป็นไปตามขีดจํากัดของเซลล์ข้อจํากัด และสร้างผลลัพธ์ที่คุณต้องการสําหรับเซลล์วัตถุประสงค์

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

Windows macOS เว็บ

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

ก่อนการประเมินของ Solver

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

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

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

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

หลังการประเมินของ Solver

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

หมายเหตุ: ถ้าคําสั่ง Solver หรือกลุ่ม การวิเคราะห์ ไม่พร้อมใช้งาน คุณจําเป็นต้องเปิดใช้งาน Solver Add-in ดู: วิธีเปิดใช้งาน Solver Add-in
ในกล่อง ตั้งค่าวัตถุประสงค์ ให้ใส่ การอ้างอิงเซลล์ หรือ ชื่อ สําหรับเซลล์วัตถุประสงค์ เซลล์วัตถุประสงค์ต้องมีสูตร
เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้
- ถ้าคุณต้องการให้ค่าของเซลล์วัตถุประสงค์มีค่ามากที่สุดเท่าที่จะเป็นไปได้ ให้คลิก Max
- ถ้าคุณต้องการให้ค่าของเซลล์วัตถุประสงค์มีค่าน้อยที่สุดเท่าที่จะเป็นไปได้ ให้คลิก Min
- ถ้าคุณต้องการให้ค่าของเซลล์วัตถุประสงค์เป็นค่าๆ หนึ่ง ให้คลิก Value of แล้วพิมพ์ค่านั้นในกล่อง
- ในกล่อง โดยการเปลี่ยนเซลล์ตัวแปร ให้ใส่ชื่อหรือการอ้างอิงสําหรับแต่ละช่วงเซลล์ตัวแปรการตัดสิน แยกการอ้างอิงที่ไม่อยู่ติดกันด้วยเครื่องหมายจุลภาค เซลล์ตัวแปรต้องเกี่ยวข้องโดยตรงหรือโดยอ้อมกับเซลล์วัตถุประสงค์ คุณสามารถระบุเซลล์ตัวแปรได้สูงสุด 200 เซลล์
ในกล่อง 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
คลิก Solve แล้วเลือกทำอย่างใดอย่างหนึ่งต่อไปนี้
- เมื่อต้องการเก็บค่าของคำตอบบนเวิร์กชีต ในกล่องโต้ตอบ Solver Results ให้คลิก Keep Solver Solution
- เมื่อต้องการคืนกลับเป็นค่าเดิมก่อนที่คุณจะคลิก Solve ให้คลิก Restore Original Values
- คุณสามารถขัดจังหวะกระบวนการแก้ไขปัญหาได้โดยการกด Esc Excel จะคํานวณเวิร์กชีตใหม่ด้วยค่าสุดท้ายที่พบสําหรับเซลล์ตัวแปรการตัดสิน
- เมื่อต้องการสร้างรายงานที่ยึดตามโซลูชันของคุณหลังจากที่ Solver พบโซลูชัน คุณสามารถคลิกชนิดรายงานในกล่อง รายงาน แล้วคลิก ตกลง รายงานจะถูกสร้างขึ้นบนเวิร์กชีตใหม่ในเวิร์กบุ๊กของคุณ ถ้า Solver ไม่พบโซลูชัน จะมีเพียงบางรายงานหรือไม่มีรายงานที่พร้อมใช้งานเท่านั้น
- เมื่อต้องการบันทึกค่าของเซลล์ตัวแปรการตัดสินของคุณเป็นสถานการณ์สมมติที่คุณสามารถแสดงภายหลังได้ ให้คลิก Save Scenario ในกล่องโต้ตอบ Solver Results แล้วพิมพ์ชื่อของสถานการณ์ในกล่อง Scenario Name

หลังจากคุณกำหนดปัญหา ให้คลิก Options ในกล่องโต้ตอบ Solver Parameters
ในกล่องโต้ตอบ Options ให้เลือกกล่องกาเครื่องหมาย Show Iteration Results เพื่อดูค่าของการลองแทนค่าแต่ละครั้ง แล้วคลิก OK
ในกล่องโต้ตอบ Solver Parameters ให้คลิก Solve
ในกล่องโต้ตอบ Show Trial Solution ให้เลือกทำอย่างใดอย่างหนึ่งต่อไปนี้
- เมื่อต้องการหยุดกระบวนการแก้ไขปัญหา และแสดงกล่องโต้ตอบ Solver Results ให้คลิก Stop
- เมื่อต้องการทำกระบวนการแก้ไขปัญหาต่อ และแสดงการลองแทนค่าถัดไป ให้คลิก Continue

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

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

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

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

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

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

สิ่งสำคัญ: คุณควรเปิดใช้งาน Solver Add-in ก่อน สําหรับข้อมูลเพิ่มเติม ให้ดู โหลด Solver Add-in

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

ตัวอย่างในการประเมินของ Solver

คำบรรยายภาพ 1 เซลล์ตัวแปร

คำบรรยายภาพ 2 เซลล์ที่มีข้อจํากัด

เซลล์วัตถุประสงค์ คำบรรยายภาพ 3

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

ตัวอย่างเช่น การประเมินของ Solver กับค่าใหม่

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

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

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

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

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

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

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

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

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

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

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

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

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

หมายเหตุ:

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

คลิก ข้อมูล > Solver
หลังจากที่คุณระบุปัญหา ในกล่องโต้ตอบ Solver Parameters ให้คลิก Options
เลือกกล่องกาเครื่องหมาย แสดงผลลัพธ์การคํานวณซ้ํา เพื่อดูค่าของโซลูชันเวอร์ชันทดลองใช้แต่ละวิธี แล้วคลิก ตกลง
ในกล่องโต้ตอบ Solver Parameters ให้คลิก Solve

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

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

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

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

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

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

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

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

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

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

วิธีแก้ปัญหา	คำอธิบาย
GRG (Generalized Reduced Gradient) Nonlinear	ตัวเลือกเริ่มต้นสําหรับตัวแบบที่ใช้ฟังก์ชัน Excel ส่วนใหญ่นอกเหนือจากฟังก์ชัน IF, CHOOSE, LOOKUP และฟังก์ชัน "step" อื่นๆ
Simplex LP	ใช้วิธีนี้สําหรับปัญหาการเขียนโปรแกรมเชิงเส้น ตัวแบบของคุณควรใช้ SUM, SUMPRODUCT, + - และ * ในสูตรที่ขึ้นอยู่กับเซลล์ตัวแปร
Evolutionary	วิธีนี้ยึดตามอัลกอริทึมทางพันธุกรรม จะดีที่สุดเมื่อโมเดลของคุณใช้ IF, CHOOSE หรือ LOOKUP กับอาร์กิวเมนต์ที่ขึ้นอยู่กับเซลล์ตัวแปร

หมายเหตุ: ส่วนของโค้ดโปรแกรม Solver เป็นลิขสิทธิ์ 1990-2010 โดย Frontline Systems, Inc. บางส่วนเป็นลิขสิทธิ์ 1989 โดย Optimal Methods, Inc.

เนื่องจากโปรแกรม Add-in ไม่ได้รับการสนับสนุนใน Excel สำหรับเว็บ คุณจะไม่สามารถใช้ Add-in Solver เพื่อเรียกใช้การวิเคราะห์แบบ What-if กับข้อมูลของคุณเพื่อช่วยให้คุณค้นหาโซลูชันที่เหมาะสมที่สุดได้

ถ้าคุณมีแอปพลิเคชัน Excel บนเดสก์ท็อป คุณสามารถใช้ปุ่ม เปิดใน Excel เพื่อเปิดเวิร์กบุ๊กของคุณ เพื่อใช้ Solver Add-in ได้

วิธีใช้เพิ่มเติมเกี่ยวกับการใช้งาน Solver

สําหรับความช่วยเหลือโดยละเอียดเพิ่มเติมเกี่ยวกับที่ติดต่อ Solver:

Frontline Systems, Inc. ตู้ไปรษณีย์ 4288 Incline Village, NV 89450-4288 (775) 831-0300 เว็บไซต์: http://www.solver.com อีเมล: info@solver.comวิธีใช้ Solver ที่ www.solver.com

บางส่วนของโค้ดโปรแกรม Solver เป็นลิขสิทธิ์ 1990-2009 ของ Frontline Systems, Inc. บางส่วนเป็นลิขสิทธิ์ 1989 ของ Optimal Methods, Inc.