بهترین تصمیم، نه فقط یک جواب
Goal Seek یک ورودی را تغییر می‌دهد تا به یک عدد برسد. Solver چندین ورودی را هم‌زمان تغییر می‌دهد تا یک هدف را بیشینه یا کمینه کند و در همان حال قیدها را رعایت کند. مسائل کلاسیک: ترکیب تولید، برنامه‌ی شیفت، تخصیص بودجه‌ی تبلیغات و کمینه‌سازی هزینه‌ی حمل.
فعال‌سازی
File ← Options ← Add-ins ← Manage: Excel Add-ins ← Go ← تیک Solver Add-in. دکمه‌ی Solver در انتهای تب Data ظاهر می‌شود.
مسئله: کارخانه‌ی فرش ماشینی در کاشان
کارخانه سه طرح پرفروش تولید می‌کند. سود هر متر مربع، ساعت دستگاه و نخ مصرفی متفاوت است. ظرفیت ماهانه‌ی دستگاه ۶۰۰۰ ساعت و موجودی نخ ۴۵٬۰۰۰ کیلوگرم است و بازار هر طرح سقف تقاضا دارد. چند متر مربع از هر طرح بافته شود تا سود بیشینه شود؟
| ستون | A طرح | B سود هر m² (تومان) | C ساعت دستگاه هر m² | D نخ هر m² (kg) | E تولید (متغیر) | F سقف تقاضا |
|---|---|---|---|---|---|---|
| 2 | افشان ۱۲۰۰ شانه | 450,000 | 0.30 | 2.2 | ؟ | 9,000 |
| 3 | ماهی ۷۰۰ شانه | 260,000 | 0.18 | 1.9 | ؟ | 12,000 |
| 4 | کاشان ۱۰۰۰ شانه | 380,000 | 0.25 | 2.1 | ؟ | 8,000 |
B7 سود کل (هدف) =SUMPRODUCT(B2:B4, E2:E4)
C7 ساعت مصرفی =SUMPRODUCT(C2:C4, E2:E4)
C8 ظرفیت ساعت 6000
D7 نخ مصرفی =SUMPRODUCT(D2:D4, E2:E4)
D8 موجودی نخ 45000
Solver Parameters:
Set Objective: $B$7 To: Max
By Changing: $E$2:$E$4
Subject to: $C$7 <= $C$8
$D$7 <= $D$8
$E$2:$E$4 <= $F$2:$F$4
[x] Make Unconstrained Variables Non-Negative
Solving Method: Simplex LPپس از Solve، گزینه‌ی Keep Solver Solution را بزنید و از فهرست Reports گزارش‌های Answer و Sensitivity را انتخاب کنید. گزارش حساسیت نشان می‌دهد هر ساعت دستگاه اضافه یا هر کیلو نخ اضافه چقدر به سود کل اضافه می‌کند (Shadow Price)؛ اطلاعاتی که برای تصمیم «شیفت اضافه بگذاریم یا نخ بیشتر بخریم؟» حیاتی است.
انتخاب روش حل
| روش | کِی |
|---|---|
| Simplex LP | هدف و قیدها خطی‌اند (جمع حاصل‌ضرب‌ها)؛ سریع و جواب بهینه‌ی سراسری |
| GRG Nonlinear | رابطه‌ی غیرخطی (قیمت وابسته به مقدار)؛ ممکن است بهینه‌ی محلی بدهد |
| Evolutionary | مدل‌هایی با IF، VLOOKUP و توابع ناپیوسته؛ کند و تقریبی |
نکته‌هایی که کمتر کسی می‌داند
- قید int (عدد صحیح) مثلاً برای تعداد تخته فرش، حل را بسیار کندتر می‌کند و گزارش Sensitivity دیگر تولید نمی‌شود؛ اول بدون int حل کنید و تحلیل کنید، بعد در صورت نیاز int اضافه کنید.
- تنظیمات Solver (هدف، متغیرها، قیدها) به‌صورت نام‌های پنهان در همان شیت ذخیره می‌شود؛ هر شیت یک مدل. با دکمه‌ی Load/Save می‌توانید چند مدل را در یک محدوده ذخیره و جابه‌جا کنید.
- در GRG Nonlinear گزینه‌ی Use Multistart (در Options) مسئله را از نقاط شروع متعدد حل می‌کند و احتمال گیر افتادن در بهینه‌ی محلی را کم می‌کند.
- اگر Solver پیام «The linearity conditions required by this LP Solver are not satisfied» داد، یعنی در مدل ضرب دو متغیر یا تابعی مثل IF هست؛ یا مدل را خطی بازنویسی کنید یا روش را عوض کنید.
- Solver را می‌توان از VBA با
SolverSolve UserFinish:=Trueصدا زد (پس از افزودن Reference به Solver در ویرایشگر VBA)؛ برای اجرای ماهانه‌ی یک مدل ثابت.