مقدمهای بر بهینهسازی با افزونه Solver اکسل
21 فروردین 1401
دقیقه
در موقعیتهای زیادی نیازمندیم که بهترین روش را برای انجامدادن کارها پیدا کنیم. اگر بخواهیم رسمیتر صحبت کنیم، میخواهیم مقادیر سلولهای خاصی در کاربرگ را که هدف خاصی را بهینه میکنند (آن را به حداقل یا حداکثر میرسانند) را پیدا کنیم. افزونه Solver به ما کمک میکند تا مشکلات بهینهسازی از قبیل مسائل زیر پاسخ دهیم.
آخرین بهروزرسانی: 27 دی 1401
در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی تحولات در روش تحلیل پرداختیم، در این مقاله بهینهسازی با افزونه Solver اکسل را بررسی می کنیم.
در موقعیتهای زیادی نیازمندیم که بهترین روش را برای انجامدادن کارها پیدا کنیم. اگر بخواهیم رسمیتر صحبت کنیم، میخواهیم مقادیر سلولهای خاصی در کاربرگ را که هدف خاصی را بهینه میکنند (آن را به حداقل یا حداکثر میرسانند) را پیدا کنیم. افزونه Solver به ما کمک میکند تا مشکلات بهینهسازی از قبیل مسائل زیر پاسخ دهیم.
- یک شرکت بزرگ دارویی چگونه محصولات ترکیبی ماهیانه را در مرکز تولید واقع در ایندیاناپولیس مشخص میکند تا باعث افزایش و بالارفتن سود شرکت شود؟
- اگر شرکت مایکروسافت کنسولهای اکس باکس را در سه مکان مختلف تولید کند، چگونه میتواند هزینههای برآورده کردن مطالبات این کنسولها را به حداقل برساند.
- چه قیمتهایی از کنسولهای اکس باکس و بازیها سود شرکت مایکروسافت را از فروش کنسول اکس باکس افزایش میدهد؟
- مایکروسافت مایل است 20 اقدام استراتژیک را متحمل شود تا در طی پنج سال آینده برنامهنویسان ماهر را با دستمزدهای مناسب استخدام کند، اما برای انجام این پنج پروژه منابع مالی کافی در اختیار ندارد. کدام یک از این پروژهها را میبایست به انجام برساند؟
- متصدیان شرطبندی چگونه بهترین مجموعه امتیازات تیمهای NFL را برای کسب تفاوت امتیاز احتمالی (Point Spread) به دست میآورند؟
- چگونه میتوان سبد بازنشستگی را به مواردی چون سهام تکنولوژی پیشرفته، سهام ارزشی، اوراق بهادار، پول نقد و طلا تخصیص داد؟
مدل بهینهسازی دارای سه بخش است: سلول هدف، سلول متغیر و محدودیتها. سلول هدف نمایشگر مقصود یا هدف موردنظر است. معمولاً تمایل داریم محتوای سلول هدف را یا افزایش دهیم و یا کاهش. در مسئله مربوط به محصولات ترکیبی کارخانه داروسازی که پیشازاین مطرح شد، فرض شده بود که مسئول کارخانه هر ماه تمایل دارد که سود کارخانه را بالا ببرد؛ بنابراین سلولی که مخصوص اندازهگیری سود است سلول هدف محسوب میشود. سلولهای هدف در هر موقعیت، در مسئلههای اول فصل که در جدول 1-29 فهرست شدهاند مورد توصیف قرار گرفتهاند. هرچند میبایست بهخاطر داشته باشید که در برخی موقعیتها احتمالاً چندین سلول هدف خواهید داشت. بهعنوانمثال شرکت مایکروسافت ممکن است هدف دومی برای بالابردن سهم بازار اکس باکس داشته باشد.
جدول 1-29 فهرست سلولهای هدف
سلولهای متغیر سلولهایی در کاربرگ هستند که میتوانید آنها را تغییر داده و یا تنظیم کنید تا سلول هدف را بهینهسازی نمایید. در مثال شرکت داروسازی، مسئول کارخانه میتواند در طول یک ماه میزان تولید را برای هریک از محصولات تغییر دهد. در این مدل، سلولهایی که این مقادیر در آنها ثبت شدهاند سلولهای متغیر نامیده میشوند. جدول 2-29 تعاریف مناسب سلولهای متغیر مدلهای توصیف شده در ابتدای فصل را فهرست کرده است و جدول 3-29 محدودیتهای مسئلهها را فهرست نموده است.
جدول 2-29 فهرست سلولهای متغیر
جدول 3-29 فهرست محدودیتهای مسئلهها
بهترین راه برای درک استفاده از ابزار Solver، جستجو در برخی از مثالهای توصیف شده است. در فصلهای بعدی یاد میگیرید که چگونه از ابزار Solver استفاده کنید تا به هریک از موقعیتهای توصیف شده در این فصل را به همراه سایر مسئلههای تجاری مهم دیگر رسیدگی نمایید.
برای نصب افزونه Solver بر روی تب File کلیک کرده، گزینه Options را انتخاب کرده و سپس روی Add-Ins کلیک کنید در کادر محاورهای Add-Ins، چک باکس Sovler Add-in را انتخاب کرده و سپس روی Ok کلیک کنید. پس از آنکه افزونه Solver نصب شد، میتوانید آن را با انتخاب گزینه Solver در گروه گزینههای Analyze Group در تب Data اجرا کنید. تصویر 1-29 کادر محاورهای Parameters Solver را نشان میدهد. در فصلهای بعدی میبینید که چگونه میتوان از این کادر محاورهای استفاده کرد تا سلولهای هدف، متغیر و محدودیتها را در یک مدل Solver شناسایی نمود.
تصویر 1-29 کادر محاورهای Solver Parameters
در تصویر 1-29 به فهرست Select A Solveing method توجه کنید. شما میتوانید از این فهرست، موتور راهحل مناسب برای بهینهسازی مسئله خود را انتخاب کنید.
- موتور The Simplex LP برای حل خطی مسئلههای بهینهسازی مورداستفاده قرار میگیرد. همانطور که در فصلهای 30 الی 34 خواهید دید، مسئله بهینهسازی خطی موردی است که در آن سلول هدف و محدودیتها همه با اضافهکردن شرایط فرمول:
(changing cell)*(constant) ایجاد میشوند.
- موتور غیرخطی GRG برای حل مسئلههای بهینهسازی ای استفاده میشود که در آن سلول هدف و یا برخی از محدودیتها خطی نمیباشند و با استفاده از عملیات ریاضی معمول مثل ضرب یا تقسیم سلولهای متغیر، به توان رساندن سلولهای متغیر، استفاده از عملیات تصاعد یا توابع مثلثاتی در سلولهای متغیر و غیره محاسبه میگردد. موتور GRG شامل گزینه چند آغازه (Multistart) است که کاربر را قادر میسازد تا بسیاری از مسائل را که در نسخههای قبلی اکسل به طرز نادرستی حل شده بود به شیوهای صحیح حل نماید. فصلهای 35 و 36 درباره موتور غیرخطی GRG بحث میکنند.
- موتور Evolutionary Solver(موتور حل تکاملی) به هنگامی که سلول هدف یا محدودیتها حاوی توابع ناهمواری هستند که به سلول متغیر ارجاع دارند مورداستفاده قرار میگیرد. تابع ناهموار (Nonsmooth Function) تابعی است که شیب آن به شکلی ناگهانی تغییر میکند مثلاً وقتی X=0 است، شیب قدرمطلق X به طور ناگهانی از 1- به 1 تغییر پیدا میکند. اگر سلول هدف و یا محدودیتها حاوی توابع IF, SUMIF, COUNTIF,SUMIFS,COUNTIFS,AVERAGEIF,AVERAGEIFS, ABS, MAX, و یا MIN باشد که به سلول متغیر ارجاع داشته باشند، آنگاه از توابع ناهموار استفاده کردهاید و موتور Evolutionary Solver احتمالاً بهترین شانس برای یافتن پاسخی مناسب به مشکل بهینهسازی شما است. موتور Evolutionary Solver در فصلهای 37 و 38 موردبحث قرار گرفته است.
پس از آنکه سلول هدف، سلولهای متغیر و محدودیتها را شناسایی کردید، افزونه Solver چه خواهد کرد؟ برای پاسخ به این سؤال میبایست با پیشینهای از واژهشناسی این افزونه آشنا شوید. هر مشخصه تغییردهنده سلولی که محدودیتهای مدل را برآورده میکند راه حلی عملی بهحساب میآید. مثلاً هر ترکیب محصولی که سه شرط زیر را برآورده میکند میتواند راه حلی عملی محسوب شود:
- از مواد خام یا نیروی کار بیش از حد موجود استفاده نمیکند.
- محصولاتی بیش از آنچه مورد تقاضا است تولید نمیکند.
- مقدار منفی هیچ محصولی تولید نمیکند.
افزونه Solver اساساً تمام راهحلهای عملی را جستجو کرده و راه حلی را که ارائهکننده بهترین مقدار در سلول هدف است را پیدا میکند. (بیشترین مقدار برای بهینهسازیهای حداکثری و کمترین مقدار برای بهینهسازیهای حداقلی) چنین راه حلی راهحل بهینه نامیده میشود. همانطور که در فصل 30: استفاده از افزونه Solver برای مشخصکردن ترکیب محصولات بهینه خواهید دید، برخی از مدلهای Solver دارای راهحل بهینه نیستند و برخی تنها یک راهحل دارند. سایر مدلهای Solver دارای چندین (در حقیقت تعداد بیشماری) راهحل بهینه هستند. در فصل بعدی مثالهای استفاده از افزونه Solver را با بررسی مسئله ترکیب محصولات شرکت دارویی انجام خواهیم داد.
مسئلههای این فصل:
در هریک از موقعیتهای توصیف شده در زیر، سلول هدف، سلول متغیر و محدودیتها را مشخص کنید:
وامی 100000 دلاری با اقساط 15 ساله دریافت کردهام. نرخ بهره سالانه 8 درصد است. چگونه میتوانم پرداختهای ماهیانه اقساط را تعیین کنم؟
یک شرکت اتومبیلسازی چگونه میتواند بودجه تبلیغات خود را به اشکال مختلف تبلیغ تخصیص دهد.
سیستم حملونقل شهری چگونه میبایست با رعایت تعادل نژادی دانشآموزان را به مدارس دورتر منتقل کند؟
اگر شهری تنها یک بیمارستان داشته باشد، آن بیمارستان کجا میبایست قرار گرفته باشد؟
یک شرکت دارویی چگونه میتواند تلاشهای نیروی فروش را به محصولاتش اختصاص دهد؟
یک شرکت دارویی 2 میلیارد دلار برای خرید شرکتهای زیست تکنولوژی (Biotech) اختصاص داده است. کدام شرکتها را باید خریداری کند؟
نرخ مالیات تحمیل شده به یک شرکت دارویی بسته به کشور/ منطقهای دارد که محصول در آن تولید میشود. یک شرکت دارویی چگونه میتواند تشخیص دهد که هر دارو را در کجا تولید کند؟