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

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

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

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

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

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

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

กำหนดและแก้ไขปัญหา

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

   หมายเหตุ

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

   

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

3. ให้ทําขั้นตอนใดขั้นตอนหนึ่งต่อไปนี้

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

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

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

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

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

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

   5. ให้ทําขั้นตอนใดขั้นตอนหนึ่งต่อไปนี้

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

      • เมื่อต้องการยอมรับข้อจํากัดและกลับไปยังกล่องโต้ตอบ Solver Parameters ให้เลือก OK

        หมายเหตุ

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

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

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

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

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

แต่ละขั้นตอนในการแก้ไขปัญหาด้วยการลองแทนค่าของ Solver

1. หลังจากคุณกําหนดปัญหา เลือก ตัวเลือก ในกล่องโต้ตอบ Solver Parameters

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

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

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

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

เปลี่ยนวิธีที่ Solver ค้นหาคำตอบ

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

บันทึกหรือโหลดแบบจำลองปัญหา

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

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

   เคล็ดลับ

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

วิธีแก้ไขปัญหาที่ใช้โดย Solver

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

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

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

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

เซลล์ตัวแปร

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

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

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

กำหนดและแก้ไขปัญหา

1. เลือกตัวแก้ปัญหาข้อมูล>

   

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

   หมายเหตุ

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

3. ให้ทําขั้นตอนใดขั้นตอนหนึ่งต่อไปนี้

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

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

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

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

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

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

      หมายเหตุ

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

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

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

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

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

แต่ละขั้นตอนในการแก้ไขปัญหาด้วยการลองแทนค่าของ Solver

1. เลือกตัวแก้ปัญหาข้อมูล>

   

2. หลังจากคุณกําหนดปัญหา ในกล่องโต้ตอบ Solver Parameters ให้เลือก Options

3. เลือกกล่องกาเครื่องหมาย Show Iteration Results เพื่อดูค่าของการลองแทนค่าแต่ละครั้ง จากนั้นเลือก OK

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

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

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

เปลี่ยนวิธีที่ Solver ค้นหาคำตอบ

1. เลือกตัวแก้ปัญหาข้อมูล>

   

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

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

3. เลือก ตกลง

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

บันทึกหรือโหลดแบบจำลองปัญหา

1. เลือกตัวแก้ปัญหาข้อมูล>

   

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

   เคล็ดลับ

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

วิธีแก้ไขปัญหาที่ใช้โดย Solver

1. เลือกตัวแก้ปัญหาข้อมูล>

   

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

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

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