حل مدل ریاضی خطی و غیر خطی با Solver اکسل (Excel) +فیلم

حل مدل ریاضی خطی و غیر خطی با Solver اکسل (Excel) +فیلم

در این مطلب آموزشی، به طور کامل، با نحوه حل مدل ریاضی خطی و غیر خطی به کمک Solver اکسل (Excel) آشنا خواهیم شد! و با کلی مثال و تمرین مبحث را یاد خواهید گرفت! این مطلب، فیلم آموزشی نیز دارد! این مبحث در حال حاضر در دو فصل: (مدل های خطی و غیرخطی) آماده شده است!

اگر به هر دلیل تمایل ندارید متن آموزش را مطالعه کنید! می‌توانید به انتهای هر فصل مراجعه کنید و فقط فیلم آن ها را دانلود کنید!

فصل 1: حل مدل ردی میکس با Solver اکسل

ویژگی‌های مدل ریاضی فوق:

• خطی
• همه‌ی متغیرها پیوسته و نامنفی
• نام مدل: ردی میکس!

maxZ=5x1+4x2
6x1+4x2<=24
x1+2x2<=6
-x1+x2<=1
x2<=2
x1,x2>=0

شروع به کار …

اول میایم یه فضایی شبیه زیر در اکسل درست می‌کنیم! البته ما برای فشنگی خیلی از این سلول ها رو تنظیم کردیم! وگرنه خیلی هاشون نیاز نیست! حالا در ادامه بیشتر دستتون میاد!

اول دو متغیر داریم به اسم x1 و x2! این دوتا رو همون طور که در تصویر می‌بینید، به ترتیب با رنگ‌های آبی و قرمز مشخص شده اند! (و مکانشووون هم در اکسل به ترتیب I5 و J5 است!)

آخر کار، مدل مقدار این دو متغیر رو قراره اینجا بنویسه!

یک متغیر دیگه داریم به اسم Z که مقدار تابع هدف رو نشون میده! و برابر است با 5 برابر مقدار متغیر x1 و 4 برابر مقدار متغیر x2! که این رو در سول مربوط به تابع هدف وارد می‌کنیم! (همین فرمول و رابطی که گفتم رو منظورمه!)

خب! حالا باید قید ها رو تعریف کنیم! مثلاً به عنوان نمونه بیاین تعریف قید اول رو بگیم!:

همانطور که در تصویر بالا می‌بینید، قید اول رو تعریف می‌کنیم: 6 برابر مقدار متغیر x1 به علاوه 4 برابر مقدار متغیر x2 ! (دقیقاً همون چیزی که در مدل بود رو وارد کردیم!)

و همین طور الی آخر… برای سایر محدودیت ها نیز تعریف میکنیم! به عنوان یک مثال دیگه، نحوه تعریف قید سوم را در شکل زیر مشاهده کنید!:

پس تا اینجا نحوه تعریف تمام قید ها و تابع هدف را یاد گرفتیم! حالا میخوایم بریم سراغ حل مدل با Solver!

فعال کردن Solver در اکسل (Excel)

اما قبل از هر چیز باید Solver اکسل (Excel) رو فعال کنیم! برای اینکار مسیر رو طی کنید:!

File – Options

پس از اینکه مسیر فوق را طی کردید، پنجره ای مشابه زیر خواهید دید:!

روی قسمت Add-ins (به معنی افزونه!) کلیک کنید تا پنجره شبیه چیزی بشه که در تصویر فوق دارید می‌بینید! و حالا قسمت پایین (همون که در تصویر بالا با آبی نشون دادم!) رو در نظر بگیرید! باید گزینه Manage رو Excel Add-ins باشه و سپس روی Go کلیک کنید تا پنجره ای مشابه تصویر زیر رو ببینید!

فقط کافیه تیک Solver Add-in رو بزنید و بعد روی دکمه OK کلیک کنید! تموم شد! Solver شما در اکسل (Excel) فعال شده است!

حل مدل

حالا به تَب Data برید و مطابق تصویر زیر، Solver رو انتخاب کنید!

سپس مطابق تصویر زیر عمل کنید!

که در قسمت set Objective باید بگید تابع هدف کدومه! که سلول مربوط به z رو مشخص میکنیم! (با خط قرمز وصل کردم!)

در قسمت By Changing Variable Cells هم باید بگید که متغیرهای شما کدوم هاست! که اونجا هم باید به x1 و x2 اشاره کنید، که اون رو هم با خط آبی نشون دادم!

حالا در ادامه نیازه تا محدودیت ها رو وارد کنیم! مثلاً تصویر زیر حالتی رو نشون میده که همه ی قید ها وارد شده و هدف رسیدن به همچین حالتی است!

برای این منظور روی Add کلیک کنید تا یک قید (=محدودیت) جدید اضافه کنیم!

پنجره ای مشابه زیر مشاهده می‌کنید!

در قسمت Cell reference باید اشاره کنید به قیدی که تعریف کردید! و چون قید مدنظر کمتری مساوی است، از بخش بعدی، کمتری مساوی را انتخاب کرده ایم!

همچنین در قسمت Constraint هم عدد سمت راست قید را باید وارد کنیم! (یا ارجاع دهیم به سلولی که مقدار سمت راست در آن قرار دارد، که مثلاً در تصویر بالا از ارجاع دادن استفاده شده است!)

سپس روی OK کلیک کنید و مجدداً برای وارد کردن سایر قید ها به طریق مشابه عمل کنید!

در بخش پایین پنجره، یک گزینه وجود دارد که تیک خورده است! که نوشته:

Make Unconstrained Variables Non-Negative

در در واقع میگه، متغیرهایی که صریحاً براشون قیدی تعیین نشده رو بزرگتر مساوی صفر (یعنی نامنفی) در نظر بگیر! که برای این مسئله ما همین جوری است! بنابراین باید تیک داشته باشه!

روش حل

در بخش Select a solving method هم باید روش حل رو مشخص کنیم! برای یک مسئله خطی مشابه مسئله حاضر از روش Simplex استفاده کنید!

در نهایت گزینه Solve را بزنید تا مدل حل کند! و پنجره زیر باز می‌شود!

در بخش سمت چپ (که دورش دایره قرمز کشیدم!)، گزینه Keep Solver Solution میگه جواب ها رو روی Sheet اکسل (Excel) نگه دار! اما اگر دوست نداشتید، میتونید گزینه دومش رو بزنید که مقادیر رو برگردونه به حالت اول خودش!

در قسمت سمت راست (همون که دورش کادر آبی کشیدم!) اولی یعنی جواب، دومی یعنی تحلیل حساسیت و سومی یعنی حدود! هر کدوم رو که روشون کلیک کنید، به صورت هایلات (highlight) و انتخاب شده در میاد و در نتایج مشاهده خواهید کرد! در نهایت روی OK کلیک کنید!

حالا جواب رو (اگر اگزینه Keep Solver Solution رو زده باشید) در خود Sheet اکسل مشاهده می‌کنید! همچنین در یک Tab دیگه (که بیانگر یک Sheet دیگه از Excel است) هم خروجی و جواب رو مشاهده می‌کنید!

دانلود فیلم های آموزشی حل مدل ریاضی با Solver اکسل (Excel)

قسمت 1: وارد کردن فرمول‌ها و شرایط پیش‌نیاز برای حل مسئله!

در این قسمت هم هنوز وارد حل نشدیم و باید فرمول‌ها رو مشخص کنیم! اینکه چجوری قیدها ساخته میشن و چجوری باید قید رو به مدل بفهمووونیم! چجوری مقدار تابع هدف رو به اکسل (Excel) بفهمونیم تا بعداً از solver استفاده کنیم؟! دیگه این قسمت رو نمی‌تونید نبینید و مهمه واقعا!

دانلود فیلم آموزشی با لینک مستقیم

(حجم: حدود 13 مگابایت) (مدت زمان: حدود 6 دقیقه)

برای دانلود فیلم، روی لینک فوق راست کلیک و سپس Save Link As را بزنید! اما برای مشاهده آنلاین فیلم، فقط کافی است روی آن کلیک کنید!

قسمت 2: حل مدل خطی با Solver نرم افزار اکسل (Excel)

خب نوبتی هم که باشه، نوبت حل مسئله است! در این بخش موارد زیر رو باهم کار می‌کنیم:

• نحوه فعال سازی افزونه Solver در نرم افزار اکسل (Excel)!
• نحوه کار تعریف تابع هدف و انواع آن در solver!
• نحوه تعریف متغیرها!
• نحوه اضافه کردن قیود (محدودیت‌ها)!
• نحوه حل مدل
• نحوه گزارش گیری و تفسیر حل و تحلیل حساسیت مدل!

دانلود فیلم آموزشی با لینک مستقیم

(حجم: حدود 22 مگابایت) (مدت زمان: 8 دقیقه و 24 ثانیه)

برای دانلود فیلم، روی لینک فوق راست کلیک و سپس Save Link As را بزنید! اما برای مشاهده آنلاین فیلم، فقط کافی است روی آن کلیک کنید!

فصل 2: حل یک مدل غیر خطی با Solver اکسل (Excel)

خب! حال بریم سراغ حل یک مدل غیر خطی در اکسل! مدل زیر را در نظر بگیرید! (کادر زرد در شکل زیر منظور است!)

همانطور که مشاهده می‌کنید! ساختار کلی مشابه آن چیزی است که در بخش حل مدل خطی گفتیم! و دقیقا تعریف تابع هدف، متغیرها، مجدودیت‌ها و … دقیقاً مشابه بخش خطی است! تنها فرقی که وجود دارد روش حل است! همین! یعنی تصویر زیر را در نظر بگیرید! (اگر یادتون باشه، کمی بالاتر در مورد این پنجره زیر در بخش خطی هم صحبت کردیم!)

این تصویر همان تصویری است که در بالاتر و در بخش آموزش حل مدل خطی با solver هم دیدید! چون مدل حاضر یک مدل غیر خطی است، کافی است روی GRG Nonlinear کلیک کنید و مدل غیرخطی را حل کنید!

البته! توجه داشته باشید که برای حل مدل ها غیر خطی سخت تر که نمی توان آن ها را با الگوریتم های غیرخطی حاضر حل کرد، میتوانید از روش تکاملی (Evolutionary) خود اکسل استفاده کنید! (البته توجه دارید و میدانید که الگوریتم های تکاملی، لزوماً جواب بهینه را به ما نمی‌دهند! ولی ممکن است جواب بهینه را هم بدهند! اما به طور کلی لزوماً اینگونه نیست که جواب بهینه را بدهند! بلکه در حالت کلی جوابی مساوی یا نزدیک بهینه می‌دهند! البته کیفیت جوابِ حاصل از روش های تکاملی به روش و به تنظیم پارامترهای الگوریتم های تکاملی نیز وابسته است!)

دانلود فیلم آموزشی حل مدل غیرخطی با Solver اکسل (Excel)

دانلود (حجم: حدود 15 مگابایت) (مدت زمان: 8 دقیقه و 46 ثانیه)

برای دانلود فیلم، روی لینک فوق راست کلیک و سپس Save Link As را بزنید! اما برای مشاهده آنلاین فیلم، فقط کافی است روی آن کلیک کنید!

توجه: این دوره در یک فصل طراحی شده است! اما اگر مدل ریاضی دیگری نیاز دارید تا آن را هم در این دوره به صورت رایگان آموزش دهیم، می‌توانید آن را برای ما ارسال کنید، تا آموزش آن را هم به دوره اضافه کنیم! فلذا بقیه‌ی فصول (که در واقع می‌تواند حل مدل ریاضی های دیگر باشد) را موکول می‌کنیم به سوالات شما!

نحوه ارسال سوالات: در بخش نظرات (در بخش پایین همین مطلب)

در ضمن نظرات خودتون رو برای هر چه بهتر شدن دوره های اکسل در زیر این پست اعلام کنید! با تشکر!

مطالب پیشنهادی:

آموزش تابع Lookup در اکسل

آموزش رسم هیستوگرام در Excel