در این مطلب به موضوع نحوه استفاده از افزونه solver در اکسل و چگونگی فعال کردن آن در نسخه های 2003 تا 2016 میپردازیم. در این مطلب می آموزید چگونه از solver اکسل در یافتن بهینه ترین راه حل ها برای برنامه ریزی خطی و انواع دیگر مسائل استفاده کنید.

همه ما میدانیم که اکسل شامل انواع مختلفی از ابزارها و توابع است که در صورت استفاده صحیح از آنها میتوانید ساعات زیادی از زمان را صرفه جویی کنید. ولی احتمالا تعداد کمی از شما اطلاع دارید که در اکسل ابزاری وجود دارد که شما را در اخذ بهینه ترین تصمیم کمک میکند.

Solver در اکسل چیست؟

Excel Solver به مجموعه ای از دستورات در اکسل گفته میشود که عموما به آن ابزارهای تجزیه و تحلیل what if گفته میشود. از این ابزارعموما برای شبیه سازی و بهینه سازی قسمت های مختلف کسب و کار، و مدل های مهندسی استفاده میشود.

افزونه Solver در اکسل بیشتر برای حل مسائل برنامه ریزی خطی و بهینه سازی مسائل خطی استفاده میشود به همین دلیل بعضی افراد به این افزونه، ابزار برنامه ریزی خطی میگویند. گذشته از این مسائل، از این ابزار میتوان برای حل مسائل غیر خطی نیز استفاده کرد.

اگرچه نمیتوان از ابزار solver در اکسل برای حل تمام مسائل استفاده کرد، اما Solver به خوبی در بهینه سازی تصمیم هایی که در کسب و کار گرفته میشود، کمک کننده است. برای مثال این ابزار میتواند در حداکثر کردن سود سرمایه گذاری، انتخاب بهینه ترین بودجه برای کمپین تبلیغاتی و … به شما کمک کند.

نحوه اضافه کردن افزونه Solver در اکسل

ابزار   از نسخه های 2003 به بعد اکسل موجود است ولی این ابزار به صورت پیش فرض توسط مایکروسافت فعال نشده است و می بایست به صورت دستی آن را فعال کنید. برای انجام این کار به شکل زیر عمل کنید:

1. در اکسل های 2010، 2013 و 2016 ، به File > Options مراجعه کنید
2. در اکسل 2007، دکمه Microsoft Office را انتخاب کنید و Excel Options را کلیک کنید.
3. در پنجره باز شده، Add-Ins را در سمت چپ انتخاب کنید، شبیه به شکل زیر excel add ins را انتخاب کنید و Go را انتخاب کنید.
4. در پنجره Add-Ins گزینه Solver Add-in را انتخاب کنید و OK را کلیک کنید.

برای دسترسی به ابزار Solver در اکسل 2003، به منو Tools مراجعه کنید، Add-ins را انتخاب کنید، در لیست Add-ins گزینه Solver Add-in را انتخاب کنید و OK کنید.

نکته: اگر با پیغامی مبنی بر اینکه افزونه Solver بر روی سیستم شما نصب نشده است مواجه شدید، Yes را انتخاب کنید تا نصب شود.

ابزار solver در اکسل 2007، 2013  و 2016 کجاست؟

در نسخه های جدید اکسل افزونه Solver در تب data قرار دارد.

ابزار Solver  در نسخه 2003 اکسل

ابن ابزار در نسخه 2003 اکسل، در منو Tool قرار دارد.

نکته: مثال های این مطلب با استفاده از ابزار Solver در اکسل 2013 انجام شده است، در صورتی که از نسخه های دیگری از اکسل استفاده میکنید، ممکن است تفاوت هایی در اکسل شما نسبت به تصاویر موجود باشد.

نحوه استفاده از ابزار Solver در اکسل

قبل از اینکه از Solver استفاده کنید، مدل خود را در یک ورک شیت پیاده سازی کنید. در این مطلب میخواهیم یک راه حل برای مسئله بهینه سازی زیر پیدا کنیم:

” فرض کنید شما مالک یک سالن آرایش هستید و در حال برنامه ریزی برای ارائه یک خدمت جدید برای مشتریان خود می باشید. برای انجام این کار، شما می بایست 40000 دلار هزینه کنید که این مقدار می بایست در 12 ماه پرداخت شود”

هدف:

مقدار حداقل هزینه به ازای هر خدمت رابه نحوی  محاسبه کنید که  شما بعنوان مالک قادر به پرداخت هزینه ماهیانه باشید.

برای انجام این کار من مدل زیر را پیاده سازی کرده ام:

حال ببینیم ابزار Solver در اکسل چگونه این مسئله را برای ما حل میکند:

1. ابزار Solver اکسل را فعال کنید.

در تب Data، در گروه Analysis، دکمه Solver را کلیک کنید.

2. تعریف مسئله

پنجره پارامترهای Solver باز میشود و شما می بایست پارامترها خود را به شرح زیر تعریف کنید:

• Objective cell( سلول هدف)
• Variable cells( سلول های متغیر)
• Constraints( محدودیت ها)

اما افزونه  Solver با پارامترهای بالا چه کاری انجام میدهد؟ در واقع این ابزار،با تغییر مقادیر موجود در سلول های variable، و با توجه به محدودیت های تعریف شده در سلول های Constraint،  مقدار بهینه را برای فرمول موجود در سلول Objective پیدا میکند.

Objective

در واقع این سلول شامل فرمولی میشود که نشان دهنده هدف اصلی مسئله است. Objective میتواند حداکثر کردن، حداقل کردن یا رسیدن به اهداف خاصی باشد.

در این مثال، سلول Objective سلول B7 است. این سلول مدت اقساط را با استفاده از فرمول =B3/(B4*B5) محاسبه میکند و نتیجه فرمول می بایست برابر با 12 باشد.

Variable Cells

این پارامتر نشان دهنده سلول هایی است، که شامل داده های متغیری می باشند که می بایست تغییر داده شوند تا به هدف نهایی دست پیدا کنیم . شما میتوانید تا 200 سلول را برای این پارامتر مشخص کنید.

در این مثال، ما مجموعه ای از سلول ها ر به شکل زیر در اختیار داریم که مقادیر آن می بایست تغییر داده شود.

• تعداد مشتریان قابل انتظار در ماه ( سلول B4) که می بایست کوچکتر یا مساوی 50 باشد
• هزینه به ازای هر خدمت( سلول B5) که ما میخواهیم با استفاده از Excel solver آن را محاسبه کنیم.

Constraints

این پارامتر نشان دهنده محدودیت هایی است که در راه حل احتمالی ممکن است وجود داشته باشد. در واقع این محدودیت ها را به صورت شرط هایی تعریف میکنیم.

برای اضافه کردن یک محدودیت، گام های زیر را طی کنید:

• در کنار باکس “Subject to the Constraints” دکمه Add را کلیک کنید.
• در پنجره Constraints، یک محدودیت وارد کنید.
• دکمه Add را کلیک کنید تا محدودیت را به لیست وارد کند.
• به همین صورت تعداد محدودیت های مورد نیاز خود را وارد کنید.
• پس از اینکه آخرین محدودیت را وارد کردید،Ok را کلیک کنید، تا به پنجره Solver Parameters برگردید.

شما میتوانید با استفاده از Solver اکسل ارتباط هایی از قبیل زیر را تعریف کنید:

• کوچکتر مساوی، بزرگتر مساوی یا مساوی: شما میتوانید ارتباط هایی از این قبیل را با انتخاب یک سلول در باکس Cell Reference ، سپس انتخاب یکی از علامت های >=، <= یا = و تایپ کردن یک عدد، آدرس سلول، نام سلول  یا فرمول در باکس Constraints ایجاد کنید( لطفا به تصویر بالا مراجعه کنید. )
• در صورتی که سلول وارد شده در باکس Reference cell می بایست از نوع Integer( عدد صحیح) باشد، int را انتخاب کنید تا integer در باکس Constraints ظاهر شود.
• اگر هرکدام از سلول ها در محدوده آدرس دهی شده می بایست دارای مقادیر منحصر به فرد باشند، گزینه diff را انتخاب کنید تا AllDifferent در باکس constraints ظاهر شود.
• در صورتی که میخواهید محدوده مورد نظر فقط به 0 و 1 محدود شوند، bin را انتخاب کنید تا binary در باکس Constraints ظاهر شود.

نکته:

از int، bin و diff فقط میتوان برای شرط های مربوط به سلول های متغیر استفاده کرد.

برای اصلاح یا حذف یک محدودیت موجود، به شیوه زیر عمل کنید:

• در دیالوگ باکس Solver parameter، محدودیت مورد نظر را انتخاب کنید.
• برای اصلاح این محدودیت change را انتخاب کنید و تغییرات مورد نظر خود را اعمال کنید.
• برای حذف محدودیت، دکمه delete را کلیک کنید .

در این مثال محدودیت های ما عبارت هستند از :

• B3=40000 : هزینه تجهیزات جدید 40000 دلار است.
• B4<=50 تعداد مشتریان در ماه می بایست کمتر از 50 مورد باشد.

3. حل مسئله

بعد از اینکه پارامتر های خود را با موفقیت تعریف کردید، دکمه Solve را در پایین پنجره Solver parameter انتخاب کنید و صبر کنید تا ابزار Solver در اکسل بهینه ترین راه حل را برای مسئله شما محاسبه کند.

بسته به اینکه ظرفیت سیستم شما چقدر است و البته اینکه پیچیدگی مسئله به چه میزان است، ممکن است حل مسئله توسط افزونه Solver چند ثانیه، چند دقیقه و یا حتی چند ساعت زمان ببرد. پس از حل مسئله دیالوگ باکس Solver result نمایش داده میشود و شما میتوانید گزینه  Keep the Solver Solution را انتخاب کنید و OK را کلیک کنید.

با انتخاب این گزینه، پنجره بالا بسته میشود و نتایج در ورک شیت ظاهر میشود . در این مثال، مقدار $66.67 در سلول B5 ظاهر میشود که نشان دهنده مقدار هزینه ای است که می بایست از هر مشتری دریافت شود تا قادر به پرداخت هزینه تجهیزات جدید باشید. البته با در نظر گرفتن این شرط که شما حداقل 50 مشتری در ماه دارید. نکته:

• برای بدست آوردن جزییات بیشتر در مورد مسئله حل شده، یکی از انواع گزارش را در باکس Reports  انتخاب کنید و OK زا بزنید. گزارش در یک ورک شیت جدید ایجاد میشود.

حال که به صورت مقدماتی با ابزار solver در اکسل آشنا شدید، می بایست با حل چند مسئله و استفاده کاربردی از افزونه Solver درک عمیقتری از این خاصیت مهم اکسل بدست آورید.

مثال

شما هم احتمالا بازی magic square را در اوقات فراغت خود انجام داده اید. در این بازی می بایست اعداد به شکلی در ردیف ها و ستون های یک جدول قرار گیرند که تمام ردیف ها، ستون ها و قطرها، یک عدد خاص باشد. برای مثال اگر بخواهیم تمام ردیف ها، ستون ها و مقادیر ضربدری یک مربع سه در سه،  برابر با 15 باشد، چگونه می بایست محاسبات را انجام دهیم؟

احتمالا افراد برای حل این مسئله از آزمون و خطا استفاده میکنند، ولی شما میتوانید با استفاده از Solver این مسئله را بسیار سریعتر حل کنید. اساسی ترین قسمت حل مسئله این است که مسئله را به خوبی تعریف کنید.

برای شروع کار، اعداد را از 1 تا 9 در یک جدول سه در سه وارد کنید. Solver به این جدول هیچ نیازی ندارد ولی انجام این کار به ما کمک میکند که به خوبی مسئله را تصویرسازی کنیم. چیزی که Solver اکسل به آن احتیاج دارد فرمول های Sum است که مجموع هر ردیف، ستون و دو قطر را بدست می آورند.  

پس از اینکه فرمول های بالا را تعریف کردید، Solver را اجرا کنید و پارامترها را به شکل زیر تعریف کنید:

• Set Objective: در این مثال ما نیاز به مشخص کردن هیچ هدفی نداریم. در نتیجه این باکس را خالی میگذاریم.
• Variable Cells: ما میخواهیم اعداد در سلول های B2:D4 قرار گیرند، در نتیجه محدوده B2:D4 را انتخاب میکنیم.
• Constraints: محدودیت های زیر می بایست تعریف شود:

$B$2:$D$4 = AllDifferent : تمام سلول های متغیر می بایست مقادیر مختلف باشند.

$B$2:$D$4 = integer  : تمام سلول های متغیر می بایست integer باشند.

$B$5:$D$5 = 15   : مجموعه مقادیر در هرکدام از ستون های می بایست 15 باشند.

$E$2:$E$4 = 15 : مجموع مقادیر در هر ستون می بایست برابر با 15 باشند.

$B$7:$B$8 = 15 : مجموع مقادیر در هر قطر می بایست 15 باشد.

در نهایت گزینه Solve را کلیک کنید تا نتایج ظاهر شود.