Excel Solver چیست؟

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2024-01-07 19:02

افزونه Excel Solver بهینه سازی ریاضی را انجام می دهد. این معمولاً برای تطبیق مدل‌های پیچیده با داده‌ها یا یافتن راه‌حل‌های تکراری برای مشکلات استفاده می‌شود. به عنوان مثال، ممکن است بخواهید با استفاده از یک معادله، منحنی را در برخی از نقاط داده جاسازی کنید. حل کننده می تواند ثابت هایی را در معادله پیدا کند که بهترین تناسب را با داده ها می دهند. کاربرد دیگر جایی است که تنظیم مجدد یک مدل برای تبدیل خروجی مورد نیاز به موضوع معادله دشوار است.

حل کننده در اکسل کجاست؟

افزونه Solver در اکسل گنجانده شده است اما همیشه به عنوان بخشی از نصب پیش فرض بارگیری نمی شود. برای بررسی اینکه آیا بارگذاری شده است، تب DATA را انتخاب کنید و نماد Solver را در بخش Analysis جستجو کنید..

اگر نمی توانید Solver را در برگه DATA پیدا کنید، باید افزونه را بارگیری کنید:

1. برگه FILE را انتخاب کنید و سپس Options را انتخاب کنید. را انتخاب کنید.

   

2. در کادر گفتگوی OptionsAdd-Ins را از برگه‌های سمت چپ انتخاب کنید.

   

3. در پایین پنجره، Excel Add-ins را از منوی کشویی Manage انتخاب کنید و برو…

   

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

   

5. دستور

   Solver اکنون باید در برگه DATA ظاهر شود. شما آماده استفاده از حل کننده هستید.

   

استفاده از حل کننده در اکسل

بیایید با یک مثال ساده شروع کنیم تا متوجه شویم که Solver چه می کند. تصور کنید که ما می خواهیم بدانیم که دایره ای با مساحت 50 واحد مربع چه شعاع به دست می دهد. ما معادله مساحت یک دایره را می دانیم (A=pi r²). البته ما می‌توانیم این معادله را دوباره مرتب کنیم تا شعاع مورد نیاز برای یک منطقه مشخص را بدست آوریم، اما برای مثال، اجازه دهید وانمود کنیم که چگونه این کار را نمی‌دانیم.

یک صفحه گسترده با شعاع B1 ایجاد کنید و مساحت را در B2 با استفاده از معادله =pi محاسبه کنید)B1^2.

می‌توانیم به‌طور دستی مقدار را در B1 تنظیم کنیم تا اینکه B2 مقداری را نشان دهد که به اندازه کافی نزدیک به ۵۰ است. بسته به میزان دقت ما لازم است، این ممکن است یک رویکرد عملی باشد. با این حال، اگر لازم باشد خیلی دقیق باشیم، زمان زیادی طول می کشد تا تنظیمات لازم را انجام دهیم.در واقع، این اساساً همان کاری است که Solver انجام می دهد. تنظیماتی را برای مقادیر در سلول های خاص انجام می دهد و مقدار را در یک سلول هدف بررسی می کند:

1. برگه DATA و Solver را انتخاب کنید تا Solver Parameters کادر گفتگو بارگیری شود.

2. سلول Objective را به عنوان Area، B2 تنظیم کنید. این مقداری است که بررسی می‌شود و سلول‌های دیگر را تنظیم می‌کند تا زمانی که این یکی به مقدار صحیح برسد.

   

3. دکمه را برای مقدار: انتخاب کنید و مقدار 50 را تنظیم کنید. این مقداری است که B2 باید به آن برسد.

   

4. در کادر با عنوان با تغییر سلول‌های متغیر: سلول حاوی شعاع، B1 را وارد کنید.

   

5. سایر گزینه ها را به صورت پیش فرض رها کنید و Solve را انتخاب کنید. بهینه سازی انجام می شود، مقدار B1 تنظیم می شود تا B2 50 شود و گفتگوی Solver Results نمایش داده می شود.

   

6. OK را برای حفظ راه حل انتخاب کنید.

   

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

براساس یک مدل پیچیده با استفاده از افزونه Excel Solver

Excel دارای یک تابع داخلی برای انجام رگرسیون خطی است که یک خط مستقیم را در میان مجموعه ای از داده ها قرار می دهد. بسیاری از توابع غیر خطی رایج را می توان خطی کرد به این معنی که می توان از رگرسیون خطی برای برازش توابعی مانند نمایی استفاده کرد.برای توابع پیچیده تر، حل کننده را می توان برای انجام "مینیمم سازی حداقل مربعات" استفاده کرد. در این مثال، برازش معادله ای از شکل ax^b+cx^d را با داده های نشان داده شده در زیر در نظر خواهیم گرفت.

این شامل مراحل زیر است:

1. مجموعه داده را با مقادیر x در ستون A و مقادیر y در ستون B مرتب کنید.
2. 4 مقدار ضریب (a، b، c، و d) را در جایی از صفحه گسترده ایجاد کنید، به این مقادیر می توان شروع دلخواه داد.

3. با استفاده از معادله ای از فرم ax^b+cx^d که به ضرایب ایجاد شده در مرحله 2 و مقادیر x در ستون A اشاره می کند، ستونی از مقادیر Y متناسب ایجاد کنید. توجه داشته باشید که برای کپی کردن فرمول به پایین در ستون، ارجاع به ضرایب باید مطلق باشد در حالی که ارجاعات به مقادیر x باید نسبی باشند.

   

4. اگرچه ضروری نیست، می توانید با ترسیم هر دو ستون y در برابر مقادیر x در یک نمودار پراکنده XY، یک نشانه بصری از تناسب معادله بدست آورید. استفاده از نشانگرها برای نقاط داده اصلی منطقی است، زیرا این مقادیر گسسته با نویز هستند، و از یک خط برای معادله برازش شده استفاده کنید.

   

5. بعد، ما به روشی برای تعیین کمیت تفاوت بین داده ها و معادله برازش شده خود نیاز داریم. روش استاندارد برای انجام این کار محاسبه مجموع اختلاف مجذور است. در ستون سوم، برای هر سطر، مقدار داده اصلی برای Y از مقدار معادله برازش شده کم می شود و نتیجه مربع می شود. بنابراین، در D2 ، مقدار با =(C2-B2)^2 داده می شود سپس مجموع همه این مقادیر مجذور محاسبه می شود. از آنجایی که مقادیر مربع هستند، فقط می توانند مثبت باشند.

   

6. اکنون آماده انجام بهینه سازی با استفاده از Solver هستید. چهار ضریب وجود دارد که باید تنظیم شوند (a، b، c و d). شما همچنین یک مقدار هدف واحد برای به حداقل رساندن دارید، مجموع اختلافات مجذور. حل کننده را مانند بالا راه اندازی کنید و پارامترهای حل کننده را مطابق شکل زیر برای ارجاع به این مقادیر تنظیم کنید.

   

7. علامت گزینه Make Unconstrained Variables Non-Negative را بردارید، این کار همه ضرایب را مجبور می کند مقادیر مثبت بگیرند.

   

8. حل را انتخاب کنید و نتایج را مرور کنید. نمودار به روز می شود و نشانه خوبی از خوب بودن تناسب را نشان می دهد. اگر حل کننده در اولین تلاش تناسب خوبی ایجاد نکرد، می توانید دوباره آن را اجرا کنید. اگر تناسب بهبود یافته است، سعی کنید از مقادیر فعلی حل کنید.در غیر این صورت، می‌توانید قبل از حل کردن، تناسب را به صورت دستی بهبود دهید.

   

9. وقتی یک تناسب خوب به دست آمد، می توانید از حل کننده خارج شوید.

حل یک مدل بصورت تکراری

گاهی اوقات یک معادله نسبتاً ساده وجود دارد که خروجی را بر حسب مقداری ورودی می دهد. با این حال، زمانی که می‌خواهیم مشکل را معکوس کنیم، نمی‌توان راه‌حل ساده‌ای پیدا کرد. به عنوان مثال، توان مصرفی یک وسیله نقلیه تقریباً با P=av + bv^3 داده می شود که در آن v سرعت، a ضریب مقاومت غلتشی و b ضریب برای کشش آیرودینامیکی اگرچه این یک معادله کاملاً ساده است، اما تنظیم مجدد معادله ای از سرعتی که وسیله نقلیه برای یک ورودی توان معین به آن می رسد آسان نیست. با این حال، می توانیم از حل کننده برای یافتن مکرر این سرعت استفاده کنیم. به عنوان مثال، سرعت به دست آمده با یک توان ورودی 740 وات را پیدا کنید.

1. یک صفحه گسترده ساده با سرعت، ضرایب a و b و توان محاسبه شده از آنها تنظیم کنید.

   

2. حل کننده را راه اندازی کنید و قدرت، B5 را به عنوان هدف وارد کنید. مقدار هدف 740 را تنظیم کنید و سرعت، B2 را به عنوان سلول های متغیر برای تغییر انتخاب کنید. برای شروع راه حل solve را انتخاب کنید.

   

3. حل کننده مقدار سرعت را تا زمانی تنظیم می کند که توان بسیار نزدیک به 740 شود و سرعت مورد نیاز ما را فراهم کند.

   

4. حل مدل‌ها به این روش اغلب می‌تواند سریع‌تر و کم‌تر از معکوس کردن مدل‌های پیچیده باشد.

درک گزینه های مختلف موجود در حل کننده می تواند بسیار دشوار باشد.اگر در به دست آوردن یک راه حل معقول مشکل دارید، اعمال شرایط مرزی برای سلول های قابل تغییر اغلب مفید است. اینها مقادیر محدود کننده ای هستند که بیش از آنها نباید تنظیم شوند. به عنوان مثال، در مثال قبل، سرعت نباید کمتر از صفر باشد و همچنین می توان حد بالایی را تعیین کرد. این سرعتی است که مطمئن هستید وسیله نقلیه نمی تواند سریعتر از آن حرکت کند. اگر بتوانید برای سلول‌های متغیر قابل تغییر مرز تعیین کنید، در این صورت گزینه‌های پیشرفته‌تر دیگر مانند چند استارت بهتر عمل می‌کنند. این کار تعدادی راه حل مختلف را اجرا می کند که از مقادیر اولیه متفاوتی برای متغیرها شروع می شود.

انتخاب روش حل نیز می تواند دشوار باشد. Simplex LP فقط برای مدل های خطی مناسب است، اگر مشکل خطی نباشد با پیامی مبنی بر عدم رعایت این شرط از بین می رود. دو روش دیگر هر دو برای روش های غیر خطی مناسب هستند. GRG Nonlinear سریعترین است اما راه حل آن می تواند به شدت به شرایط شروع اولیه بستگی داشته باشد.این انعطاف پذیری را دارد که نیازی به متغیرها برای تعیین محدوده ندارد. حل‌کننده تکاملی اغلب قابل‌اعتمادترین است، اما به همه متغیرها نیاز دارد که هم مرزهای بالایی و هم پایینی داشته باشند، که ممکن است کار کردن از قبل دشوار باشد.

افزونه Excel Solver یک ابزار بسیار قدرتمند است که می تواند برای بسیاری از مشکلات عملی اعمال شود. برای دسترسی کامل به قدرت اکسل، Solver را با ماکروهای اکسل ترکیب کنید.