بهینه سازی ضرایب در اکسل

یادگیری بهینه سازی و کار کردن با نرم افزارها مهندسی نیاز به صرف زمان، تخصص و مهارت کد نویسی دارد.  بهینه سازی ضرایب در محیط اکسل یکی از  مواردی است که. کار کردن با نرم افزار بسیار ساده است حتی کسانی که با اکسل آشنایی چندانی ندارند به راحتی می توانند ضرایب مدل را بهینه کنند. 

برای مثال فرض کنید از نتایج تجربی، نقاط زیر در مختصات (x,y)  بدست آمده است.

برای مثال در مبانی نظری پژوهش، تابع منحنی زیر می تواند بر روی داده ها منطبق شود:

برای بهینه سازی ضرایب ابتدا نرم افزار اکسل نسخه 32 بیتی را باز کنید. از منوی File گزینه Option  را انتخاب کنید. از منوی سمت چپ بر روی گزینه Add-Ins کلیک کنید. در پنجره سمت راست از منوی کرکره ای Excel Add-Ins را انتخاب کنید و بر روی دکمه Go کلیک کنید.

در پنجره باز شده افزونه Solver Add-In را تیک بزنید و بر روی دکمه Ok کلیک کنید.

سپس بر روی آیکن نرم افزار Nonlinear Parameter Estimation بر روی دسکتاپ کلیک کنید تا نرم افزار باز شود.

در ستون مربوط به x و y مختصات نقاط را کپی کنید تا مختصات نقاط بر روی نمودار ترسیم شود. در مرحله بعد می بایست تابع منحنی را در سلول زرد رنگ داخل نرم افزار بنویسید. در هنگام نوشتن فرمول منحنی باید علائم استفاده شده منطبق با استانداردهای نرم افزار اکسل باشد یعنی علامت ضرب با * علامت تقسیم با /  علامت توان با ^ علامت جذر با sqrt()  … نوشته شود. کسانی که با اکسل آشنایی مقدماتی دارند به راحتی می توانند هرگونه تابع را در نرم افزار اکسل نگارش کنند.

در منحنی، دو ضریب a و b وجود دارد که مقدار آنها را نمی دانیم و می خواهیم به کمک اکسل این دو ضریب را بگونه ای تعیین کنیم که منحنی بر روی نقاط منطبق شود. بنابراین یک مقدار اولیه و دلخواه به عنوان مقادیر اولیه این دو ضریب در جدول مربوطه قرار می دهیم تا نمودار منحنی ترسیم گردد. همانطور که مشاهده می شود منحنی بر روی نقاط قرار ندارد بنابراین بر روی دکمه «بهینه سازی ضرایب» کلیک کنید تا نرم افزار در عرض چند ثانیه مقدار بهینه ضرایب را برای شما پیدا کند نتیجه نهایی ضرایب و شکل منحنی بصورت زیر می شود.

همانطور که مشاهده می شود تابع منحنی بر روی نقاط تجربی قرار گرفته است و مقدار بهینه ضرایب a و b در جدول مربوط محاسبه شده است. می توانید به ضرایب مقادیر اولیه متفاوتی بدهید و دوباره بر روی دکمه بهینه سازی کلیک کنید در هر حالت که کمترین مقدار برای مجموع مربعات خطا(Sum of Square) در سلول سبز رنگ بدست امده نشان می دهد که آن ضرایب در بهینه ترین حالت ممکن قرار دارد.

اگر منحنی شما بسیار پیچیده باشد می توانید از نرم افزار GA Solver که در پکیج آموزشی تحلیل آماری قرار دارد نیز استفاده کنید و ضرایب منحنی را به کمک الگوریتم ژنتیک بهینه سازی کنید.  برای این منظور ابتدا نرم افزار GA Solver  را باز کنید سپس نرم افزار Nonlinear Parameter Estimation را باز کنید به این ترتیب نرم افزار الگوریتم ژنتیک در‌منوی Add-In نرم افزار اکسل اضافه می شود و به راحتی می توانید از ترکیب هر دو نرم افزار استفاده کنید.

در نرم افزار الگوریتم ژنتیک مقدار سلول سبز رنگ(Sum of Square) به عنوان تابع هدف و مقادیر سلول های a و b به عنوان متغیرهای تصمیم انتخاب می شود و نوع تابع هدف نیز از نوع min می باشد در نهایت پس از اتمام بهینه سازی مقادیر ضرایب مدل بهینه سازی می شود.