این آموزش نحوه افزودن و مکان یافتن Solver را در نسخه‌های مختلف اکسل، از سال 2016 تا 2003، توضیح می‌دهد. مثال‌های گام به گام نحوه استفاده از Excel Solver را برای یافتن راه‌حل‌های بهینه برای برنامه‌نویسی خطی و انواع مسائل نشان می‌دهند. همه می‌دانند که مایکروسافت اکسل شامل بسیاری از توابع مفید و ابزارهای قدرتمند است که می‌تواند ساعت‌ها در محاسبات شما صرفه‌جویی کند. اما آیا می دانستید که ابزاری نیز دارد که می تواند به شما در یافتن راه حل های بهینه برای مسائل تصمیم گیری کمک کند؟ در این آموزش از سلام دنیا، ما قصد داریم که تمام جنبه های ضروری افزونه Excel Solver را پوشش دهیم و راهنمای گام به گام نحوه استفاده موثر و کار با ابزار سلور اکسل (Excel Solver) برای حل مدل و معادله ریاضی را ارائه دهیم. با ما همراه باشید.
ابزار سلور اکسل (Excel Solver) چیست؟
Excel Solver متعلق به مجموعه خاصی از دستورات است که اغلب به عنوان ابزارهای تجزیه و تحلیل What-if شناخته می شوند. هدف اصلی آن شبیه سازی و بهینه سازی مدل های مختلف تجاری و مهندسی است. افزونه Excel Solver مخصوصاً برای حل مسائل برنامه نویسی خطی مفید است و به همین دلیل گاهی اوقات به آن حل کننده برنامه نویسی خطی نیز می گویند. جدای از آن، می تواند مشکلات غیرخطی و غیر هموار را حل کند. لطفاً برای جزئیات بیشتر به الگوریتم های حل کننده اکسل مراجعه کنید. در حالی که Solver نمی تواند تمام مشکلات احتمالی را حل کند، در هنگام برخورد با انواع مشکلات بهینه سازی که باید بهترین تصمیم را بگیرید، واقعا مفید است. به عنوان مثال، می تواند به شما کمک کند بازگشت سرمایه را به حداکثر برسانید، بودجه بهینه را برای کمپین تبلیغاتی خود انتخاب کنید، بهترین برنامه کاری را برای کارمندان خود بسازید، هزینه های تحویل را به حداقل برسانید و غیره.
حتما بخوانید: نحوه انتخاب چند ستون در اکسل با فرمول
نحوه اضافه کردن Solver به اکسل
افزونه Solver با تمام نسخه‌های اکسل که از سال 2003 شروع می‌شود، گنجانده شده است، اما به طور پیش‌فرض فعال نیست. برای افزودن Solver به اکسل مراحل زیر را انجام دهید:
- در اکسل 2010 روی File > Options کلیک کنید .
- در اکسل 2007، روی دکمه مایکروسافت آفیس کلیک کنید و سپس روی Excel Options کلیک کنید .
- در گفتگوی گزینه‌های اکسل ، روی Add-Ins در نوار کناری سمت چپ کلیک کنید ، مطمئن شوید که Excel Add-ins در کادر مدیریت در پایین پنجره انتخاب شده است و روی Go کلیک کنید .
- در کادر محاوره‌ای Add-Ins ، کادر Solver Add-in را علامت بزنید و روی OK کلیک کنید.
- برای دریافت Solver در Excel 2003 ، به منوی Tools بروید و روی Add-Ins کلیک کنید . در لیست افزونه‌های موجود ، کادر Solver Add-in را علامت بزنید و روی OK کلیک کنید .
توجه داشته باشید: اگر اکسل پیامی مبنی بر اینکه افزونه Solver در حال حاضر بر روی رایانه شما نصب نیست نشان می دهد، برای نصب آن روی Yes کلیک کنید.
محل قرار گیری Excel solver
در نسخه های مدرن اکسل، دکمه solver در تب Data ، در گروه Analysis ظاهر می شود.
به یاد داشته باشید که Solver در اکسل 2003 پس از بارگیری Solver Add-in در Excel 2003، دستور آن به منوی Tools اضافه می شود. اکنون که می دانید حل کننده را در اکسل کجا پیدا کنید، یک کاربرگ جدید باز کنید.
توجه داشته باشید: مثال‌های مورد بحث در این آموزش از Solver در اکسل 2013 استفاده می‌کنند. اگر نسخه اکسل دیگری دارید، ممکن است اسکرین‌شات‌ها دقیقاً با نسخه شما مطابقت نداشته باشند، اگرچه عملکرد Solver اساساً یکسان است.
حتما بخوانید: آموزش کشیدن و ساخت جدول در اکسل ویندوز و مک
نحوه استفاده از Excel solver
قبل از اجرای افزونه Excel Solver، مدلی را که می خواهید حل کنید در یک کاربرگ فرموله کنید. در این مثال، اجازه دهید راه حلی برای مسئله بهینه سازی ساده زیر پیدا کنیم.
- مشکل: فرض کنید شما صاحب یک سالن زیبایی هستید و در حال برنامه ریزی برای ارائه خدمات جدیدی به مشتریان خود هستید. برای این کار باید یک تجهیزات جدید به قیمت 40000 دلار خریداری کنید که باید ظرف 12 ماه اقساط پرداخت شود.
- هدف : حداقل هزینه هر سرویس را محاسبه کنید که به شما امکان می دهد برای تجهیزات جدید در بازه زمانی مشخص شده بپردازید.
برای این کار ما مدل زیر را ساخته ایم.
بیایید ببینیم که چگونه می توانیم مشکل را از طریق Excel Solver حل کنیم.
نحوه رفع مشکل از طریق Excel solver
1. نحوه رفع مشکل از طریق Excel solver با اجرای Excel Solver
- در تب Data ، در گروه Analysis ، روی دکمه Solver کلیک کنید.
2. نحوه رفع مشکل از طریق Excel solver با تعریف کردن مشکل
پنجره Solver Parameters باز می شود که در آن باید 3 جزء اصلی را تنظیم کنید:
- سلول هدف
- سلول های متغیر
- محدودیت ها
دقیقاً Excel Solver با پارامترهای بالا چه می کند؟ مقدار بهینه (حداکثر، حداقل یا مشخص شده) را برای فرمول در سلول Objective با تغییر مقادیر در سلولهای Variable و مشروط به محدودیت در سلولهای Constraints پیدا می‌کند.
- سلول هدف: سلول هدف ( سلول هدف در نسخه های قبلی اکسل) سلولی حاوی فرمولی است که هدف یا هدف مشکل را نشان می دهد. هدف می تواند به حداکثر رساندن، به حداقل رساندن یا دستیابی به مقداری هدف باشد. در این مثال سلول هدف B7 است که مدت پرداخت را با استفاده از فرمول محاسبه می کند =B3/(B4*B5)و نتیجه فرمول باید برابر با 12 باشد:
- سلول های متغیر: سلولهای متغیر (سلولهای تغییر یا سلولهای قابل تنظیم در نسخه‌های قبلی) سلول‌هایی هستند که حاوی داده‌های متغیری هستند که می‌توان آنها را برای دستیابی به هدف تغییر داد. Excel Solver اجازه می دهد تا حداکثر 200 سلول متغیر را مشخص کنید. در این مثال، ما چند سلول داریم که مقادیر آنها قابل تغییر است:
1. مشتریان پیش بینی شده در ماه (B4) که باید کمتر یا مساوی 50 باشد
2. هزینه هر سرویس (B5) که می خواهیم Excel Solver محاسبه کند.
نکته: اگر سلول‌ها یا محدوده‌های متغیر در مدل شما مجاور نیستند ، اولین سلول یا محدوده را انتخاب کنید و سپس کلید Ctrl را فشار دهید و نگه دارید و سلول‌ها و/یا محدوده‌های دیگر را انتخاب کنید. یا محدوده ها را به صورت دستی و با کاما از هم جدا کنید.
حتما بخوانید: استخراج و انتقال جدول از پی دی اف به اکسل
- محدودیت ها: محدودیت های حل کننده اکسل محدودیت ها یا محدودیت های راه حل های ممکن برای مشکل هستند. به بیان دیگر، محدودیت ها شرایطی هستند که باید رعایت شوند. برای اضافه کردن یک قید، موارد زیر را انجام دهید:
1. روی دکمه Add سمت راست به کادر " موضوع محدودیت ها " کلیک کنید.
2. در پنجره Constraint ، یک محدودیت را وارد کنید.
3. روی دکمه Add کلیک کنید تا محدودیت به لیست اضافه شود.
4. وارد کردن سایر محدودیت ها را ادامه دهید.
5. پس از اینکه محدودیت نهایی را وارد کردید، روی OK کلیک کنید تا به پنجره اصلی Solver Parameters بازگردید .
نکته: Excel Solver اجازه می دهد تا روابط زیر را بین سلول ارجاع شده و محدودیت مشخص کنید.
- کوچکتر یا مساوی، مساوی و بزرگتر یا مساوی: این روابط را با انتخاب یک سلول در جعبه مرجع سلول ، انتخاب یکی از علائم زیر تنظیم می‌کنید: <= ، =، یا >= ، و سپس تایپ کردن یک عدد، مرجع سلول / نام سلول، یا فرمول در کادر محدودیت (لطفاً اسکرین شات بالا را ببینید).
- عدد صحیح: اگر سلول ارجاع شده باید یک عدد صحیح باشد، int را انتخاب کنید و کلمه integer در کادر Constraint ظاهر می شود .
- ارزش های مختلف: اگر هر سلول در محدوده ارجاع شده باید حاوی مقدار متفاوتی باشد، dif را انتخاب کنید و کلمه AllDifferent در کادر Constraint ظاهر می شود .
- باینری: اگر می خواهید یک سلول ارجاع شده را به 0 یا 1 محدود کنید، bin را انتخاب کنید و کلمه باینری در کادر محدودیت ظاهر می شود .
حتما بخوانید: آموزش تبدیل عکس به جدول اکسل در اندروید و آیفون
توجه داشته باشید: روابط int ، bin و dif را فقط می توان برای محدودیت های سلول های متغیر استفاده کرد.
نحوه ویرایش یا حذف یک محدودیت موجود
- در کادر محاوره ای Solver Parameters ، روی محدودیت کلیک کنید.
- برای اصلاح محدودیت انتخاب شده، روی Change کلیک کنید و تغییرات مورد نظر خود را اعمال کنید.
- برای حذف محدودیت روی دکمه Delete کلیک کنید .
در این مثال، محدودیت ها عبارتند از:
- B3=40000 - هزینه تجهیزات جدید 40000 دلار است.
- B4<=50 - تعداد بیماران پیش بینی شده در ماه در زیر 50 نفر.
3. نحوه رفع مشکل از طریق Excel solver با حل کردن مشکل
بعد از اینکه همه پارامترها را پیکربندی کردید، روی دکمه solve در پایین پنجره Solver Parameters کلیک کنید (تصویر صفحه بالا را ببینید) و اجازه دهید افزونه Excel Solver راه حل بهینه را برای مشکل شما پیدا کند. بسته به پیچیدگی مدل، حافظه کامپیوتر و سرعت پردازنده، ممکن است چند ثانیه، چند دقیقه یا حتی چند ساعت طول بکشد. هنگامی که Solver پردازش را به پایان رساند، پنجره گفتگوی Solver Results را نمایش می‌دهد ، جایی که گزینه Keep the Solver Solution را انتخاب کرده و OK را کلیک کنید:
پنجره Solver Result بسته می شود و راه حل بلافاصله در کاربرگ ظاهر می شود. در این مثال، 66.67 دلار در سلول B5 ظاهر می شود، که حداقل هزینه برای هر سرویس است که به شما امکان می دهد هزینه تجهیزات جدید را در 12 ماه بپردازید، مشروط بر اینکه حداقل 50 مشتری در ماه وجود داشته باشد:
نکات:
- اگر Excel solver مشکل خاصی را برای مدت طولانی پردازش می‌کند، می‌توانید با فشار دادن کلید Esc فرآیند را قطع کنید. اکسل کاربرگ را با آخرین مقادیر یافت شده برای سلول های متغیر دوباره محاسبه می کند .
- برای دریافت جزئیات بیشتر در مورد مشکل حل شده، روی یک نوع گزارش در کادر گزارش ها کلیک کنید و سپس روی OK کلیک کنید . گزارش در یک کاربرگ جدید ایجاد خواهد شد:
اکنون که ایده اصلی نحوه استفاده از Solver در اکسل را به دست آورده اید، اجازه دهید نگاهی دقیق تر به چند مثال دیگر داشته باشیم که ممکن است به شما کمک کند درک بیشتری کسب کنید.
نمونه های Excel solver
در زیر دو نمونه دیگر از استفاده از افزونه Excel Solver را مشاهده خواهید کرد. ابتدا راه حلی برای یک پازل شناخته شده پیدا می کنیم و سپس یک مسئله برنامه ریزی خطی واقعی را حل می کنیم.
حتما بخوانید: روش استخراج عکس و تصاویر از فایل اکسل
1. نمونه های Excel solver: مربع جادویی
من معتقدم همه با پازل های "Magic square" آشنا هستند که در آن شما باید مجموعه ای از اعداد را در یک مربع قرار دهید تا همه سطرها، ستون ها و مورب ها به یک عدد خاص جمع شوند. به عنوان مثال، آیا راه حلی برای مربع 3x3 حاوی اعداد 1 تا 9 می دانید که در آن هر سطر، ستون و مورب 15 جمع می شود؟ احتمالاً حل این معما با آزمون و خطا کار مهمی نیست، اما شرط می بندم که حل کننده سریعتر راه حل را پیدا خواهد کرد. بخشی از کار ما این است که مشکل را به درستی تعریف کنیم. برای شروع، اعداد 1 تا 9 را در جدولی متشکل از 3 سطر و 3 ستون وارد کنید. Excel Solver در واقع به آن اعداد نیاز ندارد، اما آنها به ما کمک می کنند تا مشکل را تجسم کنیم. آنچه افزونه Excel Solver واقعا به آن نیاز دارد فرمول های SUM است که مجموع هر سطر، ستون و 2 مورب را شامل می شود:
با همه فرمول ها، Solver را اجرا کنید و پارامترهای زیر را تنظیم کنید:
- تنطیم هدف: در این مثال، ما نیازی به تعیین هدف نداریم، بنابراین این کادر را خالی بگذارید.
- سلول های متغیر: ما می خواهیم اعداد را در سلول های B2 تا D4 پر کنیم، بنابراین محدوده B2:D4 را انتخاب کنید.
- محدودیت ها: شرایط زیر باید رعایت شود:$B$2:$D$4 = AllDifferent - همه سلولهای متغیر باید دارای مقادیر متفاوتی باشند.
- $B$2:$D$4 - عدد صحیح - تمام سلول های متغیر باید اعداد صحیح باشند.
- $B$5:$D$5 = 15 - مجموع مقادیر در هر ستون باید برابر با 15 باشد.
- $E$2:$E$4 = 15 - مجموع مقادیر در هر ردیف باید برابر با 15 باشد.
- $B$7:$B$8 = 15 - مجموع هر دو مورب باید برابر با 15 باشد.
در نهایت، روی دکمه solve کلیک کنید ، و راه حل وجود دارد.
2. نمونه های Excel solver: مسئله برنامه نویسی خطی
این نمونه ای از یک مسئله ساده بهینه سازی حمل و نقل با هدف خطی است. مدل‌های بهینه‌سازی پیچیده‌تر از این نوع توسط بسیاری از شرکت‌ها برای صرفه‌جویی هزاران دلار در سال استفاده می‌شوند.
- مشکل : شما می خواهید هزینه حمل و نقل کالا را از 2 انبار مختلف به 4 مشتری مختلف به حداقل برسانید. هر انبار عرضه محدودی دارد و هر مشتری تقاضای خاصی دارد.
- هدف : به حداقل رساندن کل هزینه حمل و نقل، از مقدار موجود در هر انبار تجاوز نکند و تقاضای هر مشتری را برآورده کند.
داده ی منبع
در اینجا مسئله بهینه سازی حمل و نقل ما به نظر می رسد:
فرمول بندی مدل
برای تعریف مسئله برنامه نویسی خطی برای حل کننده اکسل، اجازه دهید به 3 سوال اصلی پاسخ دهیم:
حتما بخوانید: رفع مشکل ذخیره نشدن فایل اکسل [Document not saved] در ویندوز 11
- چه تصمیماتی قرار است گرفته شود؟ ما می خواهیم مقدار بهینه کالا را برای تحویل به هر مشتری از هر انبار محاسبه کنیم. اینها سلولهای متغیر (B7:E8) هستند.
- محدودیت ها چیست؟ لوازم موجود در هر انبار (I7:I8) قابل تجاوز نیست و باید مقدار سفارش شده توسط هر مشتری (B10:E10) تحویل داده شود. اینها سلولهای محدود هستند.
- هدف چیست؟ حداقل هزینه کل حمل و نقل و این سلول هدف ما (C12) است.
کاری که باید انجام دهید این است که کل مقدار ارسال شده از هر انبار (G7:G8) و کل کالاهای دریافت شده توسط هر مشتری (B9:E9) را محاسبه کنید. شما می توانید این کار را با فرمول های Sum ساده نشان داده شده در تصویر زیر انجام دهید. همچنین، فرمول SUMPRODUCT را در C12 وارد کنید تا کل هزینه حمل و نقل را محاسبه کنید.
آخرین کاری که باید انجام دهید این است که پارامترهای Excel Solver را پیکربندی کنید:
- Objective: آخرین کاری که باید انجام دهید این است که پارامترهای Excel Solver را پیکربندی کنید:
- هدف: هزینه حمل و نقل روی حداقل تنظیم شده است
- سلول های متغیر: Products_shipped
- محدودیت ها: Total_received = Ordered and Total_shipped <= Available
لطفا توجه داشته باشید که ما در این مثال روش حل Simplex LP را انتخاب کرده ایم زیرا با مسئله برنامه ریزی خطی سروکار داریم. اگر مطمئن نیستید که نوع مشکل شما چیست، می توانید روش حل غیرخطی پیش فرض GRG را ترک کنید . برای اطلاعات بیشتر، لطفاً الگوریتم‌های حل‌کننده اکسل را ببینید .
- راه حل: روی دکمهsolve در پایین پنجره Solver Parameters کلیک کنید و پاسخ خود را دریافت خواهید کرد. در این مثال، افزونه Excel Solver مقدار بهینه کالا را برای تحویل به هر مشتری از هر انبار با حداقل هزینه کل حمل و نقل محاسبه کرد:
در اینجا یک مثال دیگر از کاربرد عملی وجود دارد: همه ترکیباتی را که با مجموع داده شده برابر هستند را با Excel Solver پیدا کنید .
نحوه ذخیره و بارگیری سناریوهای Excel Solver
هنگام حل یک مدل خاص، ممکن است بخواهید مقادیر سلول متغیر خود را به عنوان یک سناریو ذخیره کنید که بتوانید بعداً آن را مشاهده یا دوباره استفاده کنید. به عنوان مثال، هنگام محاسبه حداقل هزینه خدمات در اولین مثال مورد بحث در این آموزش، ممکن است بخواهید تعداد متفاوتی از مشتریان پیش بینی شده در ماه را امتحان کنید و ببینید که چگونه بر هزینه خدمات تأثیر می گذارد. در آن زمان، ممکن است بخواهید محتمل ترین سناریویی را که قبلاً محاسبه کرده اید ذخیره کرده و هر لحظه آن را بازیابی کنید. ذخیره یک سناریوی Excel solver به انتخاب محدوده‌ای از سلول‌ها برای ذخیره داده‌ها خلاصه می‌شود. بارگیری یک مدل حل‌کننده فقط به ارائه محدوده سلول‌هایی که مدل شما در آن ذخیره می‌شود به اکسل ارائه می‌کند. مراحل دقیق در زیر دنبال می شود.
حتما بخوانید: آموزش رسم نمودار در اکسل تصویری و گام به گام
نحوه ذخیره مدل
برای ذخیره سناریوی Excel Solver، مراحل زیر را انجام دهید:
- کاربرگ را با مدل محاسبه شده باز کنید و Excel Solver را اجرا کنید.
- در پنجره Solver Parameters ، روی دکمه Load/Save کلیک کنید .
- Excel Solver به شما می گوید که چند سلول برای ذخیره سناریو مورد نیاز است. این تعداد سلول خالی را انتخاب کنید و روی ذخیره کلیک کنید :
- اکسل مدل فعلی شما را ذخیره می کند، که ممکن است چیزی شبیه به این باشد:
در همان زمان، پنجره Solver Parameters نشان داده می شود که در آن می توانید محدودیت های خود را تغییر دهید و گزینه های مختلف "What if" را امتحان کنید.
نحوه لود کردن مدل ذخیره شده
هنگامی که تصمیم به بازیابی سناریوی ذخیره شده دارید، موارد زیر را انجام دهید:
- در پنجره Solver Parameters ، روی دکمه Load/Save کلیک کنید .
- در کاربرگ، محدوده سلول هایی را که مدل ذخیره شده را نگه می دارند انتخاب کنید و روی بارگذاری کلیک کنید :
- در محاوره Load Model ، روی دکمه Replace کلیک کنید :
- با این کار پنجره اصلی Excel Solver با پارامترهای مدل ذخیره شده قبلی باز می شود. تنها کاری که باید انجام دهید این است که روی دکمه solve کلیک کنید تا دوباره محاسبه شود.
الگوریتم های Excel solver
هنگام تعریف مشکل برای حل‌کننده اکسل، می‌توانید یکی از روش‌های زیر را در کادر کشویی Select a Solving Method انتخاب کنید:
- GRG Nonlinear. الگوریتم غیرخطی گرادیان کاهش یافته تعمیم یافته برای مسائلی استفاده می شود که غیرخطی هستند، یعنی حداقل یکی از قیودها تابع غیرخطی هموار متغیرهای تصمیم است.
- LP Simplex . روش حل Simplex LP مبتنی بر الگوریتم Simplex است که توسط دانشمند ریاضی آمریکایی جورج دانتسیگ ایجاد شده است. برای حل مسائل به اصطلاح برنامه ریزی خطی استفاده می شود - مدل های ریاضی که الزامات آنها با روابط خطی مشخص می شود، یعنی شامل یک هدف واحد است که توسط یک معادله خطی نشان داده می شود که باید حداکثر یا حداقل شود.
- Evolutionary. این برای مسائل غیر هموار استفاده می شود، که سخت ترین نوع مسائل بهینه سازی برای حل هستند زیرا برخی از توابع غیر هموار یا حتی ناپیوسته هستند و بنابراین تعیین جهت افزایش یا کاهش یک تابع دشوار است.
برای تغییر نحوه یافتن راه‌حل، روی دکمه Options در کادر محاوره‌ای Solver Parameters کلیک کنید و یکی یا همه گزینه‌ها را در برگه‌های GRG Nonlinear ، All Methods و Evolutionary پیکربندی کنید.
سخن آخر
به این ترتیب می توانید از Solver در اکسل برای یافتن بهترین راه حل ها برای مشکلات تصمیم گیری خود استفاده کنید. از خواندن شما سپاسگزارم. امیدواریم این مطلب از سلام دنیا به شما در نحوه استفاده از Excel solver کمک کرده باشد.
مطالب مرتبط:
50 کلید میانبر در اکسل برای درج تاریخ، نمودار و حذف سطر و ستون
ساخت هدر در اکسل؛ آموزش افزودن سرصفحه و پاصفحه در اکسل