آموزش سالور (solver) اکسل
این پست از مجموعه آموزش‌های اکسل هست که به آموزش سالور (solver) اکسل می‌پردازد. در صورتی که تمایل دارید بقیه آموزش‌های مرتبط با اکسل را ببینید، از این لینک استفاده کنید. در ضمن اگه حال و حوصله خوندن متن ندارید، از ویدیو آخر این پست استفاده کنید.
پیشنیاز این آموزش آشنایی با اکسل و توانایی فرمول نویسی در این نرم‌افزار هست، پس اگه با اکسل و فرمول نویسی در اکسل آشنایی ندارید، اول اون‌ها را از یکی از هزاران وبسایت فارسی که در گوگل براتون میاره، یاد بگیرید.
سالور (solver) اکسل چیه؟
سالور یکی از امکانات اکسل است که برای بهینه سازی در مقیاس کوچک بکار میره. یعنی باهاش می‌تونید بهترین جواب برای یک مساله را پیدا کنید. بهترین کار اینه که با یک مثال عملی کابرد سالور را برای شما توضیح بدم.
یک روز من و داداشم قرار بود از پیتزارا (یک پیتزا فروشی در اصفهانه، خودم توصیش نمی‌کنم چون بیش از حد معقول گرونه) پیتزا بخریم، چون بودجه ما محدود بود (به عبارت دیگه، خیلی پول نداشتیم) و خیلی هم گرسنه بودیم، قصد داشتیم که با بودجه‌ای که داریم بتونیم بیشترین پیتزا را بخوریم! حالا این یعنی چی؟ اگه به عکس پایین که قسمتی از منو پیتزارا هست نگاه کنید، متوجه میشید که تمام پیتزاهای این رستوران در ابعاد 22، 29 و 40 سانتی متری هستند و هر کدوم از این ابعاد هم قیمت خاص خودش را داره، همچنین تعداد پیتزاها هم خیلی زیاده. در نتیجه ما می‌خواستیم با پولی که داریم، بیشترین مساحت پیتزا را سفارش بدیم و بخوریم. اینجاست که سالور اکسل به کار ما اومد. وقتی مساله را برای سالور مشخص کردیم، بهترین جواب ممکن را به ما داد و ما هم فهمیدیم که از کدوم نوع پیتزا چه ابعادی را باید سفارش بدیم تا بیشتر پیتزا بخوریم. (برای اطلاعات بیشتر در مورد سالور می‌تونید از این لینک که از سایت مایکروسافت هست، استفاده کنید)
ورود داده‌های مساله در اکسل
اولین کاری که لازمه برای کار با سالور کنیم اینه که منو رستوران را وارد اکسل کنیم. من منو را به این شکل در اکسل تایپ کردم:
همینطور که در تصویر مشخصه، در ستون اول اسم انواع پیتزا را آوردم، ستون دوم و سوم و چهارم قیمت هر نوع پیتزا را با توجه به ابعاد پیتزا نشون میده. من اینجا قیمت‌ها را به هزار تومان وارد کردم، یعنی مثلا پیتزا مارگاریتا 40 سانتی 293 هزار تومان قیمت داره.
اضافه کردن متغیرهای تصمیم
در ادامه منو، چهار ستون دیگه وارد می‌کنم که در واقع متغیرهای تصمیم ما هستند:
سه ستون اول اضافه شده را در نظر بگیرید، مقدار فعلی این سلول‌ها را فعلا صفر گذاشتم، بعد که سالور مساله را حل کرد، این سلول‌ها مقدار خواهند گرفت. این سلول‌ها تعداد پیتزایی که باید از هر نوع پیتزا و از هر اندازه سفارش بدیم را به ما نشون خواهند داد (مثلا فرض کنید بعد از حل مساله توسط سالور، سلول مربوط به اندازه 40 پیتزا مامامیا برابر 2 شد، این یعنی سفارش دو عدد پیتزا مامامیا 40 سانتی با توجه به بودجه ما بهینه‌ترین حالت هست).
ستون چهارمی که اضافه کردم، صرفا برای اینه که بتونم ببینم از هر نوع پیتزا چند تا باید سفارش بدم، این سلول وابسته به اندازه پیتزا نیست و صرفا مجموع سه سلول سمت چپ خودش را نشون میده (نیاز به گفتن نداره که با تابع sum برای این سلول فرمول نویسی کردم).
اضافه کردن متغیرهای وابسته به متغیر تصمیم
در این مساله دو تا متغیر وابسته به متغیر تصمیم داریم: 1- مساحت پیتزا سفارش داده شده 2- هزینه سفارش
برای همین شش تا ستون دیگه هم به شکل زیر در ادامه وارد اکسل می‌کنم:
مساحت پیتزا سفارش داده شده
سه ستون اضافه شده، مساحت پیتزای سفارش داده شده را با توجه به تعداد سفارش و اندازه سفارش حساب می‌کنند. مثلا فرض کنید شما یک پیتزای مارگاریتا 22 سانتی سفارش بدید، در این صورت مساحت سفارش داده شده برابر 379.9 سانتی‌متر مربع می‌شود. توجه کنید که قطر پیتزا 22 سانتی‌متر است، پس شعاع آن 11 سانتی‌متر هست و طبق فرمول مساحت دایره (شعاع × شعاع × عدد پی)، مساحت پیتزا را به شکل زیر حساب کردم:
11 × 11 × 3.14 = 379.94
حالا اصلا برای چی مساحت سفارش داده شده را اضافه کردم؟ چون وقتی گفتم هدفم خوردن پیتزای بیشتری هست، این هدف را باید یجوری به سالور در قالب ریاضی و مدل ریاضی حالی کنم، پس خوردن پیتزای بیشتر به ریاضی یعنی سطح بیشتری پیتزا بخورم و برای همین لازم بود که مساحت پیتزای سفارش داده شده را حساب کنم و به سالور بدم تا اون هم سعی کنه مجموع این مساحت‌ها را ماکزیمم کنه.
حواستون باشه که برای همه سلول‌های این سه تا ستون داخل اکسل فرمول نویسی کنید، یعنی برای هر سلول اندازه پیتزا را از سلول مرتبط بخونید، اون را تقسیم بر دو کنید، حاصل را به توان دو برسونید و در عدد پی ضرب کنید، تعداد سفارش اون نوع پیتزا و اون اندازه را از سلول مربوطه بخونید و در عدد حاصل ضرب کنید.
اگه جایی از آموزش تا اینجا براتون مبهم بوده، حتما از ویدیو آخر این پست استفاده کنید، قطعا ابهام‌های شما را می‌تونه برطرف کنه.
هزینه سفارش
سه ستون اضافه شده، هزینه سفارش را برای ما محاسبه می‌کنند. برای همه سلول‌های این سه ستون فرمول نویسی را یادتون نره! یعنی تعداد سفارش از اون نوع پیتزا و اون اندازه را در قیمت پیتزا ضرب کنید.
اضافه کردن سه سلول تکمیلی
در جایی دیگر از صفحه اکسل خودم، سه سلول به شکل زیر اضافه می‌کنم:
سلول اول از بالا، هزینه کل سفارش‌های ما را نشون میده، برای محاسبه این عدد از 36 سلول مربوط به هزینه سفارش با تابع sum مجموع گرفتم. در سلول وسط، میزان پولی که دارید را وارد کنید. چون من قیمت پیتزاها را به هزار تومان وارد کردم، میزان بودجه را هم با همین واحد وارد می‌کنم، یعنی الان من 1.5 میلیون تومان بودجه برای سفارش پیتزا دارم. در سلول آخر هم با تابع sum مجموع 36 سلول مربوط به مساحت را بدست آوردم.
اضافه کردن سالور به اکسل
سالور در اکسل وجود داره و لازم نیست جداگونه اون را نصب کنید ولی به صورت پیشفرض نمی‌تونید اون را در تب‌های اکسل پیدا کنید، برای اینکه سالور را اضافه کنید، مراحل زیر را دنبال کنید:
اول روی تب File کلیک کنید و وارد قسمت Options بشید:
در قسمت Options، روی Add-ins کلیک کنید و از کادری که باز میشه گزینه Solver Add-in را انتخاب کنید و روی دکمه Go کلیک کنید:
در پنجره‌ای که باز میشه، Sovler Add-in را انتخاب کنید و OK بزنید:
حالا در تب Data در قسمت Analyze آیکون Solver برای شما اضافه شده:
شروع کار با سالور
روی سالور کلیک کنید تا پنجره‌ای مطابق تصویر پایین برای شما باز بشه، در قسمت Set Objective سلول هدف را باید وارد کنید. در این مثال، هدف من خوردن بیشترین میزان پیتزاست، پس سلول هدف من سلول مساحت کل هست که دوست دارم بیشترین مقدار ممکن بشه، برای همین در قسمت Set Objective مقدار $S$5 را نوشتم چون مساحت کل من در سلول S5 محاسبه شده:
در پایین حتما گزینه Max را انتخاب کنید چون مساله از نوع پیدا کردن بیشترین مقدار هست.
در قسمت By Changing Variable Cells محدوده متغیرهای هدف خودتون را انتخاب کنید که الان در مثال ما، سلول‌هایی که باید انتخاب کنیم، سلول‌های مربوط به تعداد سفارش هست.
حالا زمان آن رسیده که محدودیت‌های مساله را وارد کنیم، مثلا هزینه کل سفارش‌ها باید حتما از بودجه‌ای که داریم کمتر باشه، متغیرهای تصمیم باید بزرگتر از صفر باشند چون به تعداد پیتزای سفارش داده شده، اشاره دارند، شاید یک نوع پیتزا را دوست نداشته باشیم و… .
برای اضافه کردن محدودیت در پنجره اصلی سالور (Solver Parameters)، روی دکمه Add کلیک کنید تا پنجره پایین باز بشه.
در قسمت Cell Reference سلول‌هایی را انتخاب کنید که دوست دارید محدودیت روی آن‌ها اعمال بشه. در کادر روبرو اون عملگر ریاضی مربوطه را انتخاب کنید و در قسمت Constraint مقدار محدودیت را مشخص کنید. یکی از محدودیت‌های اساسی مدل ما اینه که هزینه کل سفارش‌ها باید از بودجه‌ای که داریم کمتر باشه. برای وارد کردن این محدویت در قسمت Cell Reference سلول مربوط به هزینه سفارش‌ها را انتخاب می‌کنیم و از کادر عملگرها، عملگر => را انتخاب می‌کنیم. همچنین لازمه در قسمت Constraint، سلول مربوط به بودجه‌ای که داریم را انتخاب کنید.
در ضمن در قسمت Cell Reference محدود به انتخاب یک سلول نیستید و می‌تونید بیشتر از یک سلول را انتخاب کنید. با همین نکته می‌تونید محدودیت مربوط به بزرگتر از صفر بودن تعداد سفارش را وارد سالور کنید.
به همین ترتیب همه محدودیت‌های مساله را وارد کنید. اگه جایی از آموزش برای شما مبهم هست، حتما ویدیو آخر پست را ببینید چون قطعا ابهام شما را برطرف می‌کنه.
وقتی همه محدودیت را وارد کردید، در پنجره اصلی سالور در قسمت Select a Solving Method حتما Simplex LP را انتخاب کنید و روی دکمه Solve کلیک کنید. منتظر بمونید تا سالور مساله را حل کنه، در پایان اگه همه چیز درست پیش رفته باشه، برخی از سلول‌های مربوط به تعداد سفارش، عددی به جز صفر دارند و این اعداد به شما نشون میده که با توجه به بودجه‌ای که دارید، بهینه‌ترین حالت سفارش پیتزا چیه!
