ابزار Solver در اکسل

اکسل شامل ابزاری به نام Solver است که از تکنیک های تحقیقات عملیاتی برای یافتن راه‌حل های بهینه برای انواع مسائل تصمیم‌گیری استفاده می‌کند.

برای بارگیری افزونه حل کننده، مراحل زیر را انجام دهید :

1. در تب File، روی Options کلیک کنید.

2. در قسمت Add-ins، Solver Add-in را انتخاب کرده و بر روی دکمه Go کلیک کنید.

3. Solver Add-in را علامت بزنید و OK کنید.

4. می‌توانید Solver را در تب Data، در گروه Analyze پیدا کنید.

formulate model

مدلی که می‌خواهیم حل کنیم در اکسل به صورت زیر است:

1. برای فرمول بندی این مدل برنامه ریزی خطی به سه سوال زیر پاسخ دهید.

• چه تصمیماتی باید گرفته شود؟ برای این مشکل، ما به اکسل نیاز داریم تا بفهمیم که هر محصول (دوچرخه، موتور سیکلت و صندلی کودک) چقدر باید سفارش داد.
• محدودیت های این تصمیمات چیست؟ محدودیت در اینجا این است که میزان سرمایه و ذخیره سازی مورد استفاده محصولات نمی‌تواند از مقدار محدود سرمایه و ذخیره (منابع) موجود بیشتر شود. به عنوان مثال، هر دوچرخه 300 واحد سرمایه و 0.5 واحد ذخیره سازی استفاده می‌کند.
•  معیار کلی عملکرد برای این تصمیمات چیست؟ معیار کلی عملکرد، سود کل سه محصول است، بنابراین هدف به حداکثر رساندن این مقدار است.

 

2. برای سهولت درک مدل، محدوده های نامگذاری شده زیر را ایجاد کنید .

نام محدوده	سلول ها
واحد سود	C4:E4
OrderSize	C12:E12
منابع استفاده شده	G7:G8
منابع موجود	I7:I8
سود کل	I12

 

3. سه تابع SUMPRODUCT زیر را وارد کنید.

توضیح: مقدار سرمایه استفاده شده برابر است با حاصل جمع بازه C7:E7 و OrderSize. مقدار فضای ذخیره سازی استفاده شده برابر است با مجموع محصول محدوده C8:E8 و OrderSize. سود کل برابر است با حاصل جمع UnitProfit و OrderSize.

آزمایش و خطا

با این فرمول، تجزیه و تحلیل هر راه حل آزمایشی آسان می‌شود.

به عنوان مثال، اگر 20 دوچرخه، 40 موتور و 100 صندلی کودک سفارش دهیم، مجموع منابع استفاده شده از منابع موجود بیشتر نمی‌شود. این راه حل دارای سود کل 19000 است.

استفاده از آزمون و خطا ضروری نیست. در ادامه توضیح خواهیم‌داد که چگونه می‌توان از Excel Solver برای یافتن سریع راه حل بهینه استفاده کرد.

Solve the model (مدل را حل کنید)

برای یافتن راه حل بهینه ، مراحل زیر را انجام دهید.

1. در تب Data، در گروه Analyze، روی Solver کلیک کنید .

پارامترهای حل کننده را وارد کنید.

نتیجه باید با تصویر زیر مطابقت داشته باشد.

شما می توانید نام محدوده را تایپ کنید یا روی سلول های صفحه گسترده کلیک کنید.

2. TotalProfit را برای Objective وارد کنید.

3. روی Max کلیک کنید.

4. OrderSize را برای تغییر سلول های متغیر وارد کنید.

5. برای وارد کردن محدودیت زیر روی Add کلیک کنید.

6. “Make Unconstrained Variables Non-Negative” را علامت بزنید و “Simplex LP” را انتخاب کنید.

7. در نهایت روی حل کلیک کنید.

نتیجه:

راه حل بهینه:

نتیجه گیری: سفارش 94 دوچرخه و 54 دستگاه موتورسیکلت بهینه است. این راه حل حداکثر سود 25600 را می‌دهد. این راه حل از تمام منابع موجود استفاده می‌کند.