การเพิ่มประสิทธิภาพด้วย 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) จะปรากฏขึ้นโดยเริ่มจากเซลล์ที่คุณให้ไว้เป็นข้อมูลอ้างอิง

    • ในการโหลดโมเดลปัญหาให้ป้อนการอ้างอิงสำหรับช่วงของเซลล์ทั้งหมดที่มีโมเดลปัญหา จากนั้นคลิกที่ปุ่มโหลด