การเพิ่มประสิทธิภาพด้วย Excel Solver
Solver เป็นโปรแกรมเสริมของ Microsoft Excel ที่คุณสามารถใช้เพื่อเพิ่มประสิทธิภาพในการวิเคราะห์แบบ What-if
ตามที่โอไบรอันและมาราคัสกล่าว optimization analysisเป็นส่วนขยายของการวิเคราะห์การแสวงหาเป้าหมายที่ซับซ้อนมากขึ้น แทนที่จะกำหนดค่าเป้าหมายเฉพาะสำหรับตัวแปรเป้าหมายคือการหาค่าที่เหมาะสมที่สุดสำหรับตัวแปรเป้าหมายอย่างน้อยหนึ่งตัวแปรภายใต้ข้อ จำกัด บางประการ จากนั้นตัวแปรอื่น ๆ อย่างน้อยหนึ่งตัวจะถูกเปลี่ยนแปลงซ้ำ ๆ โดยขึ้นอยู่กับข้อ จำกัด ที่ระบุจนกว่าคุณจะค้นพบค่าที่ดีที่สุดสำหรับตัวแปรเป้าหมาย
ใน Excel คุณสามารถใช้ไฟล์ Solver เพื่อค้นหาไฟล์ optimal value (ค่าสูงสุดหรือต่ำสุดหรือค่าหนึ่ง) สำหรับสูตรในเซลล์หนึ่งที่เรียกว่าเซลล์วัตถุประสงค์โดยขึ้นอยู่กับข้อ จำกัด หรือขีด จำกัด บางประการเกี่ยวกับค่าของเซลล์สูตรอื่น ๆ บนแผ่นงาน
ซึ่งหมายความว่า Solver ทำงานร่วมกับกลุ่มของเซลล์ที่เรียกว่าตัวแปรการตัดสินใจที่ใช้ในการคำนวณสูตรในเซลล์วัตถุประสงค์และเซลล์ข้อ จำกัด Solver ปรับค่าในเซลล์ตัวแปรการตัดสินใจเพื่อตอบสนองขีด จำกัด ของเซลล์ข้อ จำกัด และสร้างผลลัพธ์ที่คุณต้องการสำหรับเซลล์วัตถุประสงค์
คุณสามารถใช้ Solver เพื่อค้นหาวิธีแก้ปัญหาที่เหมาะสมสำหรับปัญหาต่างๆเช่น -
การกำหนดส่วนผสมผลิตภัณฑ์รายเดือนสำหรับหน่วยการผลิตยาที่เพิ่มผลกำไรสูงสุด
การจัดตารางการทำงานในองค์กร
การแก้ปัญหาการขนส่ง
การวางแผนการเงินและการจัดทำงบประมาณ
กำลังเปิดใช้งาน Add-in ของ Solver
ก่อนที่คุณจะดำเนินการค้นหาวิธีแก้ปัญหากับ Solver ตรวจสอบให้แน่ใจว่าไฟล์ Solver Add-in เปิดใช้งานใน Excel ดังนี้ -
- คลิกแท็บ DATA บน Ribbon Solver คำสั่งควรปรากฏในกลุ่มการวิเคราะห์ดังที่แสดงด้านล่าง
ในกรณีที่คุณไม่พบคำสั่ง Solver ให้เปิดใช้งานดังนี้ -
- คลิกแท็บไฟล์
- คลิกตัวเลือกในบานหน้าต่างด้านซ้าย กล่องโต้ตอบตัวเลือกของ Excel จะปรากฏขึ้น
- คลิก Add-in ในบานหน้าต่างด้านซ้าย
- เลือก Excel Add-Ins ในกล่องจัดการแล้วคลิกไป
กล่องโต้ตอบ Add-in จะปรากฏขึ้น ตรวจสอบSolver Add-inแล้วคลิกตกลง ตอนนี้คุณควรจะพบคำสั่ง Solver บน Ribbon ภายใต้แท็บ DATA
วิธีการแก้ปัญหาที่ Solver ใช้
คุณสามารถเลือกหนึ่งในสามวิธีการแก้ปัญหาต่อไปนี้ที่ Excel Solver รองรับโดยพิจารณาจากประเภทของปัญหา -
LP Simplex
ใช้สำหรับปัญหาเชิงเส้น กSolver แบบจำลองเป็นเส้นตรงภายใต้เงื่อนไขต่อไปนี้ -
เซลล์เป้าหมายคำนวณโดยการบวกเงื่อนไขของรูปแบบ (เซลล์ที่เปลี่ยนแปลง) * (ค่าคงที่)
ข้อ จำกัด แต่ละข้อเป็นไปตามข้อกำหนดของโมเดลเชิงเส้น ซึ่งหมายความว่าแต่ละข้อ จำกัด จะได้รับการประเมินโดยการบวกเงื่อนไขของรูปแบบ (เซลล์ที่เปลี่ยนแปลง) * (ค่าคงที่) และเปรียบเทียบผลรวมกับค่าคงที่
Generalized Reduced Gradient (GRG) ไม่ใช่เชิงเส้น
ใช้สำหรับปัญหาที่ไม่เป็นเชิงเส้น หากเซลล์เป้าหมายของคุณข้อ จำกัด ใด ๆ ของคุณหรือทั้งสองมีการอ้างอิงถึงเซลล์ที่เปลี่ยนแปลงที่ไม่ใช่ในรูปแบบ (เซลล์ที่เปลี่ยนแปลง) * (ค่าคงที่) แสดงว่าคุณมีแบบจำลองที่ไม่ใช่เชิงเส้น
วิวัฒนาการ
ใช้สำหรับปัญหาที่ไม่เป็นเชิงเส้น หากเซลล์เป้าหมายของคุณข้อ จำกัด ใด ๆ ของคุณหรือทั้งสองมีการอ้างอิงถึงเซลล์ที่เปลี่ยนแปลงที่ไม่ใช่ในรูปแบบ (เซลล์ที่เปลี่ยนแปลง) * (ค่าคงที่) แสดงว่าคุณมีแบบจำลองที่ไม่ใช่เชิงเส้น
การทำความเข้าใจการประเมินผลการแก้ปัญหา
Solver ต้องการพารามิเตอร์ต่อไปนี้ -
- เซลล์ตัวแปรการตัดสินใจ
- เซลล์ข้อ จำกัด
- เซลล์วัตถุประสงค์
- วิธีการแก้ปัญหา
การประเมิน Solver ขึ้นอยู่กับสิ่งต่อไปนี้ -
ค่าในเซลล์ตัวแปรการตัดสินใจถูก จำกัด โดยค่าในเซลล์ข้อ จำกัด
การคำนวณค่าในเซลล์วัตถุประสงค์รวมถึงค่าในเซลล์ตัวแปรการตัดสินใจ
Solver ใช้วิธีการแก้ปัญหาที่เลือกเพื่อให้ได้ค่าที่เหมาะสมที่สุดในเซลล์วัตถุประสงค์
การกำหนดปัญหา
สมมติว่าคุณกำลังวิเคราะห์ผลกำไรที่ได้จาก บริษัท ที่ผลิตและจำหน่ายผลิตภัณฑ์บางอย่าง ระบบจะขอให้คุณค้นหาจำนวนเงินที่สามารถใช้จ่ายในการโฆษณาในอีก 2 ไตรมาสข้างหน้าซึ่งมีจำนวนสูงสุด 20,000 ระดับการโฆษณาในแต่ละไตรมาสมีผลต่อสิ่งต่อไปนี้ -
- จำนวนหน่วยที่ขายโดยทางอ้อมกำหนดจำนวนรายได้จากการขาย
- ค่าใช้จ่ายที่เกี่ยวข้องและ
- กำไร
คุณสามารถดำเนินการต่อเพื่อกำหนดปัญหาเป็น -
- ค้นหาต้นทุนต่อหน่วย
- ค้นหาต้นทุนการโฆษณาต่อหน่วย
- ค้นหาราคาต่อหน่วย
จากนั้นตั้งค่าเซลล์สำหรับการคำนวณที่ต้องการตามที่ระบุด้านล่าง
ดังที่คุณสามารถสังเกตได้การคำนวณจะทำสำหรับ Quarter1 และ Quarter2 ที่อยู่ในการพิจารณาคือ -
จำนวนยูนิตสำหรับขายใน Quarter1 คือ 400 และใน Quarter2 คือ 600 (เซลล์ - C7 และ D7)
ค่าเริ่มต้นสำหรับงบประมาณการโฆษณากำหนดไว้ที่ 10,000 ต่อไตรมาส (เซลล์ - C8 และ D8)
จำนวนหน่วยที่ขายขึ้นอยู่กับค่าโฆษณาต่อหน่วยดังนั้นจึงเป็นงบประมาณสำหรับไตรมาส / ล่วงหน้า ต้นทุนต่อหน่วย โปรดทราบว่าเราได้ใช้ฟังก์ชัน Min เพื่อดูแลเพื่อดูว่าไม่มี ของหน่วยขายใน <= ไม่ จำนวนหน่วยที่มีอยู่ (เซลล์ - C9 และ D9)
รายได้คำนวณเป็นราคาต่อหน่วย * จำนวนหน่วยที่ขาย (เซลล์ - C10 และ D10)
ค่าใช้จ่ายคำนวณเป็นต้นทุนต่อหน่วย * จำนวนหน่วยที่มีอยู่ + ล่วงหน้า ค่าใช้จ่ายสำหรับไตรมาสนั้น (เซลล์ - C11 และ D12)
กำไรคือรายรับ - รายจ่าย (เซลล์ C12 และ D12)
กำไรรวมคือกำไรในไตรมาสที่ 1 + กำไรในไตรมาสที่ 2 (เซลล์ - D3)
ถัดไปคุณสามารถตั้งค่าพารามิเตอร์สำหรับ Solver ตามที่ระบุด้านล่าง -
ดังที่คุณสังเกตได้พารามิเตอร์สำหรับ Solver คือ -
เซลล์วัตถุประสงค์คือ D3 ที่มี Total Profit ซึ่งคุณต้องการเพิ่มสูงสุด
เซลล์ตัวแปรการตัดสินใจคือ C8 และ D8 ที่มีงบประมาณสำหรับสองไตรมาส - ไตรมาสที่ 1 และไตรมาส 2
มีเซลล์ข้อ จำกัด สามเซลล์ - C14, C15 และ C16
เซลล์ C14 ที่มีงบประมาณรวมคือการกำหนดข้อ จำกัด 20000 (เซลล์ D14)
เซลล์ C15 ที่มีเลขที่ ของหน่วยที่ขายใน Quarter1 คือการกำหนดข้อ จำกัด ของ <= no จำนวนยูนิตที่มีอยู่ใน Quarter1 (เซลล์ D15)
เซลล์ C16 ที่มีเลขที่ ของหน่วยที่ขายใน Quarter2 คือการกำหนดข้อ จำกัด ของ <= no จำนวนยูนิตที่มีอยู่ใน Quarter2 (เซลล์ D16)
การแก้ปัญหา
ขั้นตอนต่อไปคือการใช้ Solver เพื่อค้นหาวิธีแก้ปัญหาดังนี้ -
Step 1- ไปที่ DATA> Analysis> Solver บน Ribbon กล่องโต้ตอบ Solver Parameters จะปรากฏขึ้น
Step 2 - ในกล่อง Set Objective เลือกเซลล์ D3
Step 3 - เลือกสูงสุด
Step 4 - เลือกช่วง C8: D8 ใน By Changing Variable Cells กล่อง.
Step 5 - จากนั้นคลิกปุ่มเพิ่มเพื่อเพิ่มข้อ จำกัด สามข้อที่คุณระบุ
Step 6- กล่องโต้ตอบ Add Constraint จะปรากฏขึ้น กำหนดข้อ จำกัด สำหรับงบประมาณรวมตามที่ระบุด้านล่างแล้วคลิกเพิ่ม
Step 7- ตั้งค่าข้อ จำกัด สำหรับจำนวนทั้งหมด ของหน่วยที่ขายได้ใน Quarter1 ตามที่ระบุด้านล่างแล้วคลิกเพิ่ม
Step 8- ตั้งค่าข้อ จำกัด สำหรับจำนวนทั้งหมด ของหน่วยขายใน Quarter2 ตามที่ระบุด้านล่างและคลิกตกลง
กล่องโต้ตอบ Solver Parameters จะปรากฏขึ้นพร้อมกับข้อ จำกัด สามข้อที่เพิ่มเข้ามาในกล่อง - เรื่องของข้อ จำกัด
Step 9 - ในไฟล์ Select a Solving Method ให้เลือก Simplex LP
Step 10- คลิกปุ่มแก้ไข กล่องโต้ตอบ Solver Results จะปรากฏขึ้น เลือกKeep Solver Solution แล้วคลิกตกลง
ผลลัพธ์จะปรากฏในแผ่นงานของคุณ
ดังที่คุณสามารถสังเกตได้ทางออกที่ดีที่สุดที่สร้างผลกำไรรวมสูงสุดภายใต้ข้อ จำกัด ที่กำหนดมีดังต่อไปนี้ -
- กำไรทั้งหมด - 30000
- Adv. งบประมาณไตรมาส 1 - 8000
- Adv. งบประมาณไตรมาส 2 - 12000
ก้าวผ่านโซลูชันการทดลองใช้ Solver
คุณสามารถก้าวผ่านโซลูชันการทดลองใช้ Solver โดยดูที่ผลการทำซ้ำ
Step 1 - คลิกปุ่มตัวเลือกในกล่องโต้ตอบพารามิเตอร์ของ Solver
Options กล่องโต้ตอบปรากฏขึ้น
Step 2 - เลือกช่องแสดงผลลัพธ์การทำซ้ำแล้วคลิกตกลง
Step 3 - Solver Parametersกล่องโต้ตอบปรากฏขึ้น คลิกSolve.
Step 4 - Show Trial Solution กล่องโต้ตอบปรากฏขึ้นโดยแสดงข้อความ - Solver paused, current solution values displayed on worksheet.
ดังที่คุณสังเกตได้ค่าการวนซ้ำปัจจุบันจะแสดงในเซลล์ที่ทำงานของคุณ คุณสามารถหยุดให้ Solver ยอมรับผลลัพธ์ปัจจุบันหรือดำเนินการต่อโดยให้ Solver ค้นหาโซลูชันในขั้นตอนต่อไป
Step 5 - คลิกดำเนินการต่อ
Show Trial Solutionกล่องโต้ตอบจะปรากฏขึ้นในทุกขั้นตอนและในที่สุดหลังจากพบโซลูชันที่เหมาะสมที่สุดแล้วกล่องโต้ตอบ Solver Results จะปรากฏขึ้น แผ่นงานของคุณได้รับการอัปเดตในทุกขั้นตอนสุดท้ายแสดงค่าผลลัพธ์
การบันทึกการเลือก Solver
คุณมีตัวเลือกการประหยัดต่อไปนี้สำหรับปัญหาที่คุณแก้ด้วย Solver -
คุณสามารถบันทึกการเลือกสุดท้ายในกล่องโต้ตอบ Solver Parameters กับเวิร์กชีตโดยบันทึกเวิร์กบุ๊ก
แต่ละแผ่นงานในสมุดงานสามารถมีตัวเลือก Solver ของตัวเองได้และแผ่นงานทั้งหมดจะได้รับการบันทึกเมื่อคุณบันทึกสมุดงาน
คุณยังสามารถกำหนดปัญหาได้มากกว่าหนึ่งปัญหาในแผ่นงานโดยแต่ละปัญหาจะมีการเลือก Solver ของตัวเอง ในกรณีนี้คุณสามารถโหลดและบันทึกปัญหาทีละปัญหาได้ด้วยกล่องโต้ตอบ Load / Save ใน Solver Parameters
คลิก Load/Saveปุ่ม. กล่องโต้ตอบโหลด / บันทึกจะปรากฏขึ้น
ในการบันทึกโมเดลปัญหาให้ป้อนข้อมูลอ้างอิงสำหรับเซลล์แรกของช่วงแนวตั้งของเซลล์ว่างที่คุณต้องการวางโมเดลปัญหา คลิกบันทึก
แบบจำลองปัญหา (ชุดพารามิเตอร์ Solver) จะปรากฏขึ้นโดยเริ่มจากเซลล์ที่คุณให้ไว้เป็นข้อมูลอ้างอิง
ในการโหลดโมเดลปัญหาให้ป้อนการอ้างอิงสำหรับช่วงของเซลล์ทั้งหมดที่มีโมเดลปัญหา จากนั้นคลิกที่ปุ่มโหลด