فرمول بندی مسئله

فرض کنید شرکتی که در آن کار می کنید دارای دو انبار است که از آنجا کالاها به پنج فروشگاه شما که در سراسر مسکو پراکنده هستند می رود.

هر فروشگاه قادر است مقدار مشخصی از کالاهای شناخته شده خود را به فروش برساند. هر کدام از انبارها ظرفیت محدودی دارند. وظیفه این است که به طور منطقی انتخاب کنید که کالا از کدام انبار به کدام فروشگاه تحویل داده شود تا مجموع هزینه های حمل و نقل به حداقل برسد.

قبل از شروع بهینه سازی، لازم است یک جدول ساده در مورد آن تهیه شود صفحه اکسل- مدل ریاضی ما که وضعیت را توصیف می کند:

قابل درک است که:

• جدول خاکستری (B3:G5) هزینه حمل یک واحد از هر انبار به هر فروشگاه را توضیح می دهد.
• سلول های بنفش (C14:G14) میزان کالاهای مورد نیاز برای فروش هر فروشگاه را توصیف می کند.
• سلول های قرمز (J10:J11) ظرفیت هر انبار را نشان می دهد - حداکثر مقدار کالایی که انبار می تواند نگه دارد.
• سلول های زرد (C12:G12) و آبی (H10:H11) به ترتیب مجموع ردیف ها و ستون های سلول های سبز هستند.
• کل هزینه حمل و نقل (E17) به عنوان مجموع محصولات تعداد محصولات و هزینه های حمل و نقل مربوطه آنها محاسبه می شود.

بنابراین، وظیفه ما به انتخاب مقادیر بهینه سلول های سبز کاهش می یابد. علاوه بر این، به طوری که کل مبلغ خط (سلول های آبی) از ظرفیت انبار (سلول های قرمز) بیشتر نشود و در عین حال هر فروشگاه مقدار کالای مورد نیاز خود را برای فروش دریافت کند (مبلغ برای هر فروشگاه در سلول های زرد باید تا حد امکان به نیازها نزدیک باشند - سلول های بنفش).

راه حل

در ریاضیات، چنین مسائلی برای انتخاب توزیع بهینه منابع از دیرباز تدوین و تشریح شده است. و البته راه هایی برای حل آنها مدت هاست که توسعه یافته است. اکسل یکی از آنها را در اختیار کاربر قرار می دهد - با کمک یک افزونه قدرتمند یافتن راه حل(حل کننده)، در اکسل 2003 از طریق منو موجود است سرویس(ابزار)یا از زبانه داده هادر نسخه های جدیدتر اکسل

اگر منو سرویسیا روی برگه داده هااکسل شما چنین دستوری ندارد - اشکالی ندارد - به این معنی است که افزونه هنوز متصل نشده است. برای اتصال آن:

• در اکسل 2003 و بعد از آن - منو را باز کنید خدمات - افزونه ها(ابزار – اضافه کردن- Ins) ، در پنجره ای که ظاهر می شود، کادر را علامت بزنید یافتن راه حل(حل کننده) و فشار دهید خوب. اکسل افزونه انتخاب شده و در منو را فعال می کند سرویس (ابزار) یک فرمان جدید وجود خواهد داشت - یافتن راه حل (حل کننده) .
• در اکسل 2007 و جدیدتر - روی دکمه کلیک کنید دفتر، سپس انتخاب کنید گزینه هابرتری داشتن – افزونه ها – برو(گزینه های اکسل - افزودن Ins - رفتن به) .

بیایید افزونه را اجرا کنیم. پنجره زیر باز خواهد شد:

در این پنجره باید تنظیمات زیر را انجام دهید:

علاوه بر محدودیت‌های آشکار مرتبط با عوامل فیزیکی (ظرفیت انبارها و وسایل حمل و نقل، بودجه و محدودیت‌های زمانی و غیره)، گاهی اوقات لازم است محدودیت‌هایی «ویژه اکسل» اضافه شود. برای مثال، در مورد ما، باید محدودیت زیر را اضافه کنیم:

علاوه بر این روشن می شود که حجم کالاهای حمل شده (سلول های سبز) نمی تواند منفی باشد - برای یک فرد این امر بدیهی است ، اما برای رایانه باید به صراحت نوشته شود.

پس از تنظیم تمام پارامترهای لازم، پنجره باید به شکل زیر باشد:

اکنون که داده های محاسبه وارد شده است، دکمه را فشار دهید اجرا کن(حل)برای شروع بهینه سازی در موارد شدید با تغییر سلول‌ها و محدودیت‌های زیاد، یافتن راه‌حل می‌تواند زمان زیادی طول بکشد، اما وظیفه ما برای اکسل مشکلی نخواهد بود - در چند لحظه ما نتایج زیر را دریافت خواهیم کرد:

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

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

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

مثال در نظر گرفته شده، البته، نسبتاً ساده است، اما به راحتی برای حل مسائل غیرخطی بسیار پیچیده تر مقیاس بندی می شود. مثلا:

• بهینه سازی توزیع منابع مالی بر اساس اقلام هزینه در طرح تجاری یا بودجه پروژه. محدودیت ها در این حالت میزان تامین مالی و زمان اجرای پروژه خواهد بود و هدف از بهینه سازی حداکثر کردن سود و به حداقل رساندن هزینه های پروژه است.
• بهینه سازی زمان بندی کارکنان به منظور به حداقل رساندن صندوق دستمزدشرکت ها محدودیت، در این صورت، خواسته هر یک از کارکنان با توجه به جدول استخدامی و الزامات جدول کارکنان خواهد بود.
• بهینه سازی سرمایه گذاری های سرمایه گذاری - نیاز به توزیع صحیح وجوه بین چندین بانک، اوراق بهادار یا سهام شرکت ها به منظور به حداکثر رساندن سود یا (در صورت مهمتر) به حداقل رساندن خطرات.

در هر صورت افزونه یافتن راه حل(حل کننده)ابزار اکسل بسیار قدرتمند و زیبا است و سزاوار توجه شماست زیرا می تواند در بسیاری از موقعیت های دشواری که در تجارت مدرن با آن روبرو هستید کمک کند.

چه بخواهیم چه نخواهیم دنیا با سرعتی سرسام آور در حال تغییر است. این حقیقت به ویژه برای کاربران رایانه شخصی شناخته شده است. گذشته از همه اینها نرم افزارتغییر می کند و با فرکانس باور نکردنی به روز می شود. خوشبختانه، مجموعه های اداری از این موضوع آسیب چندانی نمی بینند، اما استثناهایی نیز وجود دارد. اهمیت پکیج ها چیست؟ برنامه های اداری? هر برنامه اداریابزار مفیدی است که برای کار با پایگاه های داده طراحی شده است. تعداد عناصر کمکی در این سیستم روز به روز بیشتر می شود.

با کمک ابزارهای تجسم، که فقط در نسخه های جدید چنین برنامه هایی ظاهر می شوند، کار بسیار آسان تر شده است. به لطف وجود یک فیلتر جستجوی جدید، کار به طور قابل توجهی تسریع شده است. و خود مایکروسافت اکسل 2010 سریعتر است. اما، به نظر می رسد، اخیراً، کارکنان اداری بر خرد کار با آفیس 2007 مسلط شده اند. اما ناگهان، ارائه آفیس 2010 اتفاق افتاد، که فقط دردسر بیشتری را برای کاربران بدبخت ایجاد کرد. یک مثال "جستجوی راه حل" در Microsoft Excel 2010 است.

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

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

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

"جستجوی راه حل" در اکسل 2010 چگونه کار می کند؟

به عنوان مثال: به شما وظیفه توزیع پاداش در یک سازمان داده می شود. برای ساده کردن راه حل، فرض می کنیم که باید پاداش را بین همه کارکنان شعبه توزیع کنید. بودجه حق بیمه 100000 روبل است. شما می توانید پاداش را متناسب با میزان دستمزد هر کارمند تقسیم کنید. کار را از کجا شروع کنیم؟ اول از همه، لازم است یک جدول ایجاد کنید، تمام اطلاعات لازم و عبارات فرمول را در آن وارد کنید. ارزش کل مبلغ جایزه به عنوان نتیجه در نظر گرفته می شود. شایان ذکر است که سلول هدف (به عنوان مثال، C8) با بخشی که تغییر خواهد کرد (به عنوان مثال، E2) مرتبط است.

در محدوده C2-C7، ممکن است فرمول های اضافی وجود داشته باشد که با آن می توانید میزان پاداش پرداختی برای هر کارمند را محاسبه کنید. پس از آن، باید افزونه «جستجوی راه حل» را اجرا کنید. سپس مقادیر مورد نیاز در پنجره باز شده تنظیم می شود. باید توجه ویژه ای به این واقعیت داشت که ظاهرپنجره ها می توانند بسیار متفاوت باشند نسخه های مختلف مجموعه اداری. بنابراین در این شرایط باید خودتان با آن کنار بیایید. اما هیچ تفاوت اساسی وجود ندارد، بنابراین مطالعه زمان زیادی نمی برد.

چه گزینه هایی در کادر محاوره ای وجود دارد؟

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

محدودیت ها چگونه اضافه می شوند؟

اگر نیاز به اضافه کردن محدودیت‌هایی به برنامه دارید، باید از دکمه «افزودن» استفاده کنید. در اینجا مهم است که نکته زیر را در نظر بگیرید: هنگام تنظیم چنین مقادیری، باید بسیار مراقب باشید. از آنجایی که افزودنی اکسل "جستجوی راه حل" در عملیات نسبتاً مسئولانه استفاده می شود، دریافت صحیح ترین مقادیر در نتیجه مهم است. خود نتایج به محدودیت ها بستگی دارد. شما می توانید محدودیت هایی را برای سلول های جداگانه و همچنین برای کل محدوده تعیین کنید.

چه انواع فرمول ها و علائم را می توان در این مورد استفاده کرد؟ از علائم زیر می توان استفاده کرد: =، >=،<=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».

کجا می توانم این گزینه را در نسخه های قدیمی برنامه پیدا کنم؟ اگر از اکسل 2007 یا جدیدتر استفاده می کنید، با کلیک بر روی دکمه "گزینه ها" می توانید به این گزینه دسترسی پیدا کنید. در اینجا می توانید مورد "گزینه های راه حل جستجو" را مشاهده کنید.

یافتن یک نتیجه تمام شده

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

در کجا می توان از افزونه "جستجوی راه حل" در اکسل استفاده کرد؟

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

در «جستجوی راه حل» در MS Excel چه اطلاعاتی باید وارد شود؟ لازم است هزینه مصالح ساختمانی، نیاز به آنها در محل ساخت و هزینه حمل مصالح ساختمانی ذکر شود. شما باید هر جفت "تامین کننده-خریدار" را در نظر بگیرید. سلول هدف باید شامل مجموع تمام هزینه های حمل و نقل باشد. اگر همه چیز به درستی انجام شود، عملکرد "جستجوی راه حل" فرصتی برای ایجاد سودآورترین استراتژی که بالاترین درآمد ممکن را به همراه خواهد داشت، فراهم می کند.

Find Solutions در Excel 2007 یک افزونه استاندارد نیست. برای محاسبات پیچیده در حضور بیش از یک مجهول ضروری است. بنابراین، در مجموعه معمولی از گزینه های برنامه گنجانده نشده است. با این حال، هنگامی که نیاز به آن وجود دارد، این افزونه کار کارآمد و همچنین بهره وری بالا را به کاربر ارائه می دهد.

جستجو برای راه حل ها چیست؟

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

حل کننده برای چیست؟ چرا از Find Solutions در Excel 2007 استفاده می کنیم و آیا نصب آن ضروری است؟ اگر کاربر تابع هدفی داشته باشد که به چندین پارامتر بستگی دارد، افزونه راه حل هایی را برای مشکل، مطابق با داده های منبع انتخاب می کند. آنها ممکن است یک متغیر، یک ناشناخته یا مثلاً یک مقدار نهایی باشند. به عبارت دیگر، کاربر ویژگی های اولیه و همچنین پاسخ را خواهد داشت. در مورد خود برنامه، مسیر راه حل را انتخاب می کند، یک فرمول ارائه می دهد. شایان ذکر است که با استفاده از افزونه می توان موارد زیر را پیدا کرد:

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

بنابراین، این برنامه فرصت های بزرگی را باز می کند، اما باید نحوه استفاده صحیح از آن را یاد بگیرید.

حل کننده چگونه کار می کند؟ علاوه بر حل کننده، اکسل عملکردی به عنوان انتخاب پارامتر دارد. زمانی که فقط یک مقدار ناشناخته وجود دارد استفاده از آن ضروری است. این ویژگی به منابع کمتری نیاز دارد، بنابراین نتیجه سریعتر صادر می شود. یافتن راه حل در اکسل 2007 برای مشکلاتی با بیشترین پیچیدگی استفاده می شود که در آن چندین مجهول وجود دارد، اغلب متغیرها مشاهده می شوند. بنابراین، همه چیز را می توان به صورت زیر نشان داد: مجهولات را پیدا کنید - چند "x". مشروط بر اینکه فرمول یا تابع باشد. با محدودیت در این مورد، معمولاً نابرابری یا مقادیر حداقل / حداکثر نشان داده می شود. علاوه بر این، شما باید سلول هایی را که می خواهید محاسبات را با آنها انجام دهید مشخص کنید. حل چندین کار مختلف به صورت همزمان امکان پذیر است، به شرطی که پارامترهای مناسب برای برنامه تنظیم شده باشد.

تنظیم گزینه های حل کننده برای اینکه Excel 2007 Solver به درستی کار کند، باید پارامترهای صحیح را وارد کنید. به عنوان یک قاعده، آنها به چند ویژگی (1-3) محدود می شوند، با این حال، با وظایفی با پیچیدگی بیشتر، یک تنظیم جهانی مورد نیاز است. گزینه های Find Solutions در Office Excel 2007 به شرح زیر است:

1. حداکثر زمان - تعداد ثانیه هایی که کاربر برای حل برنامه اختصاص می دهد، به سطح پیچیدگی کار بستگی دارد.
2. حداکثر تعداد ادغام. در این مورد، ما در مورد تعداد حرکات انجام شده توسط برنامه در هنگام حل مشکل صحبت می کنیم. هنگامی که پارامتر افزایش می یابد، پاسخ نمی رسد.
3. خطا یا دقت، که اغلب در حل کسری اعشاری استفاده می شود (به عنوان مثال، تا 0.0001).
4. مدارا. در فرآیند کار با درصد استفاده می شود.
5. مقادیر غیر منفی. هنگام حل تابعی که دو پاسخ صحیح دارد (مثلاً +/-X) استفاده می شود.
6. نمایش نتایج ادغام ها. این تنظیمات زمانی ارائه می شود که هم نتیجه تصمیمات و هم مسیر آنها مهم باشد.
7. روش جستجو - انتخاب یک الگوریتم بهینه سازی. به عنوان یک قاعده، از "روش نیوتن" استفاده می شود. وقتی همه تنظیمات انتخاب شدند، باید روی دکمه ذخیره کلیک کنید.

پارامترهای وظیفه در تابع "جستجوی راه حل".

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

فرمول در اکسل

شایان ذکر است یک عنصر اجباری است که بدون آن افزونه "جستجوی راه حل ها" در Excel 2007 قادر به کار نیست. در این مورد، ما در مورد فرمول ها صحبت می کنیم. آنها عبارتی هستند که یک محاسبه خاص را انجام می دهند. فرمول ها بدون برابری نمی توانند کار کنند. بنابراین، اگر کاراکتر مربوطه وجود نداشته باشد، برنامه قادر به تشخیص یکی نیست. فرمول از اجزای زیر تشکیل شده است:

1. عملکرد. فرمول استاندارد، که در آن توالی مشخص و خاصی از اقدامات وجود دارد، قابل تغییر نیست.
2. پیوند. تعداد سلول هایی که باید حل شوند را نشان می دهد. در همان زمان، سلول ها را می توان به صورت تصادفی یا به ترتیب خاصی قرار داد.
3. اپراتور. نمادی را نشان می دهد که نوع محاسبه را مشخص می کند (+ - جمع، * - ضرب و غیره).
4. ثابت. یک مقدار ثابت است که همیشه ثابت می ماند. برای بدست آوردن آن نیازی به محاسبه نیست.

فرمول ها از چپ به راست حل می شوند، مهم است که تمام قوانین ریاضی را دنبال کنید.

یک فرمول ایجاد کنید

فرمول ها معادلاتی هستند که برای انجام محاسبات برنامه مورد نیاز هستند. هنگامی که آنها وارد نمی شوند، Excel Solver کار نخواهد کرد. وظایف نیز حل نخواهد شد. بنابراین، برای کار صحیح، باید فرمول را به درستی وارد کنید. محاسبه با برابری شروع می شود. اگر سلول حاوی "=ROOT(شماره سلول)" باشد، از تابع مربوطه استفاده می شود. هنگامی که فرمول اصلی با علامت "=" چاپ می شود، لازم است داده هایی را که با آنها تعامل دارد نشان دهید. از تابع جستجو برای یافتن اطلاعات مورد نیاز استفاده کنید.

Solution Finder یک افزونه مایکروسافت اکسل است که با توجه به محدودیت های مشخص شده توسط کاربر، می تواند برای یافتن بهترین راه حل برای یک مشکل استفاده شود.

ما جستجوی راه حل را در (این افزونه در مقایسه با نسخه قبلی در .
در این مقاله به موارد زیر می پردازیم:

• ایجاد یک مدل بهینه سازی بر روی برگه MS EXCEL
• تنظیمات یافتن راه حل؛
• مثال ساده (مدل خطی).

در حال نصب حل کننده

تیم یافتن راه حلدر یک گروه است تحلیل و بررسیبرگه داده ها.

اگر تیم یافتن راه حلدر یک گروه تحلیل و بررسیدر دسترس نیست، باید افزونه ای به همین نام را فعال کنید.
برای این:

• روی زبانه فایلیک تیم انتخاب کنید گزینه هاو سپس دسته افزونه ها;
• در زمینه کنترلمقدار را انتخاب کنید افزونه های اکسلو دکمه را فشار دهید پرش؛
• در زمینه افزونه های موجودکادر کناری را علامت بزنید یافتن راه حلو روی OK کلیک کنید.

توجه داشته باشید. پنجره افزونه هاهمچنین در برگه موجود است توسعه دهنده. نحوه فعال کردن این تب

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

با استفاده مکرر یافتن راه حلراه اندازی آن از نوار ابزار دسترسی سریع راحت تر است و نه از زبانه داده. برای قرار دادن یک دکمه در پنل، روی آن کلیک راست کرده و انتخاب کنید به نوار ابزار دسترسی سریع اضافه کنید.

درباره مدل ها

این بخش برای کسانی است که به تازگی با مفهوم یک مدل بهینه سازی آشنا می شوند.

مشاوره. قبل از استفاده یافتن راه حلما اکیداً توصیه می کنیم که ادبیات حل مسائل بهینه سازی و ساخت مدل ها را مطالعه کنید.

در زیر یک برنامه آموزشی کوچک در این زمینه ارائه شده است.

روبنا یافتن راه حلبه تعیین کمک می کند بهترین راهانجام دادن چیزی:

• "چیزی" ممکن است شامل تخصیص پول برای سرمایه گذاری، بارگیری یک انبار، تحویل کالا یا هر فعالیت اساسی دیگری باشد که در آن راه حل بهینه مورد نیاز است.
• "بهترین راه" یا راه حل بهینه در این مورد یعنی: حداکثر کردن سود، به حداقل رساندن هزینه ها، دستیابی به بهترین کیفیت و ....

در اینجا چند نمونه معمولی از مشکلات بهینه سازی آورده شده است:

• تعیین حداکثر درآمد حاصل از فروش محصولات تولیدی؛
• تعیین کنید که در آن هزینه کل حمل و نقل حداقل باشد.
• پیدا کنید که هزینه کل تولید حداقل باشد.
• تعیین حداقل زمان برای تکمیل تمام فعالیت های پروژه (مسیر بحرانی).

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

توجه داشته باشید. در ساده ترین حالت، مدل را می توان با استفاده از یک فرمول توصیف کرد. برخی از این مدل ها را می توان با استفاده از . قبل از اولین ملاقات به دنبال راه حلمنطقی است که ابتدا با یک ابزار مرتبط به تفصیل بپردازیم.
تفاوت های اصلی انتخاب پارامتراز جانب یافتن راه حل:

• انتخاب پارامترفقط با مدل هایی با یک متغیر کار می کند.
• تعیین محدودیت برای متغیرها در آن غیرممکن است.
• حداکثر یا حداقل تابع هدف تعیین نمی شود، بلکه برابری آن با مقدار معینی است.
• فقط در مورد مدل های خطی به طور موثر کار می کند، در حالت غیر خطی یک بهینه محلی (نزدیک ترین به مقدار اولیه متغیر) پیدا می کند.

تهیه مدل بهینه سازی در MS EXCEL

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

مشاوره. داده های مدل را طوری سازماندهی کنید که فقط یک مدل در یک صفحه MS EXCEL قرار گیرد. در غیر این صورت، برای انجام محاسبات، باید به طور مداوم تنظیمات را ذخیره و بارگذاری کنید. یافتن راه حل(پایین را ببینید).

ما یک الگوریتم برای کار با آن ارائه می دهیم به دنبال راه حل، که توسط خود توسعه دهندگان توصیه می شود (www.solver.com):

• سلول ها را با متغیرهای مدل (متغیرهای تصمیم) تعریف کنید.
• فرمولی را در یک سلول ایجاد کنید که تابع هدف مدل شما (تابع هدف) را محاسبه کند.
• فرمول هایی را در سلول ها ایجاد کنید که مقادیری را که باید با محدودیت ها مقایسه شوند محاسبه می کنند (سمت چپ عبارت).
• با استفاده از کادر محاوره ای یافتن راه حلپیوندهایی به سلول های حاوی متغیرها، تابع هدف، فرمول های محدودیت ها و مقادیر خود محدودیت ها را وارد کنید.
• اجرا کن یافتن راه حلبرای یافتن راه حل بهینه

بیایید همه این مراحل را با یک مثال ساده انجام دهیم.

مورد استفاده ساده یافتن راه حل

بارگیری کانتینر با کالا ضروری است تا وزن کانتینر حداکثر باشد. کانتینر 32 متر مکعب حجم دارد. کالاها در جعبه ها و جعبه ها قرار دارند. وزن هر جعبه با کالا 20 کیلوگرم، حجم آن 0.15 متر مکعب است. جعبه - به ترتیب 80 کیلوگرم و 0.5 متر مکعب. لازم است تعداد کل ظروف کمتر از 110 عدد نباشد.

ما داده های مدل را به صورت زیر سازماندهی می کنیم (به فایل مثال مراجعه کنید).

متغیرهای مدل (تعداد هر نوع ظرف) با رنگ سبز مشخص شده اند.
تابع هدف (وزن کل همه جعبه ها و جعبه ها) به رنگ قرمز است.
محدودیت های مدل: با حداقل تعداد ظروف (>=110) و با حجم کل (<=32) – синим.
تابع هدف با فرمول محاسبه می شود =SUMPRODUCT(B8:C8,B6:C6)وزن کل تمام جعبه ها و جعبه های بارگذاری شده در ظرف است.
به طور مشابه، ما حجم کل را محاسبه می کنیم - =SUMPRODUCT(B7:C7,B8:C8). این فرمول برای تعیین محدودیت در حجم کل جعبه ها و جعبه ها (<=32).
همچنین، برای تنظیم محدودیت مدل، تعداد کل کانتینرها = SUM (B8: C8) را محاسبه می کنیم.
حالا با کادر محاوره ای یافتن راه حلبیایید ارجاعاتی را به سلول های حاوی متغیرها، یک تابع هدف، فرمول های محدودیت ها و مقادیر محدودیت ها (یا پیوندهایی به سلول های مربوطه) وارد کنیم.
واضح است که تعداد جعبه ها و جعبه ها باید یک عدد صحیح باشد - این یکی دیگر از محدودیت های مدل است.

پس از فشار دادن دکمه راه حلی پیدا کنچنین تعداد جعبه و جعبه ای یافت می شود که در آن وزن کل آنها (تابع هدف) حداکثر است و در عین حال تمام محدودیت های مشخص شده رعایت می شود.

خلاصه

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

حل کننده نتوانست یک راه حل عملی پیدا کند

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

توجه داشته باشید. شما می توانید در مورد تأثیر غیرخطی بودن مدل بر نتایج محاسبات در بخش آخر مقاله بخوانید.

در هر صورت (خطی یا غیرخطی)، ابتدا باید مدل را برای محدودیت‌های سازگاری، یعنی شرایطی که نمی‌توان به طور همزمان برآورده کرد، تحلیل کرد. اغلب این به دلیل انتخاب اشتباه نسبت است (به عنوان مثال،<= вместо >=) یا مقدار مرزی.
اگر به عنوان مثال، در مثال فوق، مقدار حداکثر حجم به جای 32 متر مکعب بر روی 16 متر مکعب تنظیم شود، این محدودیت با محدودیت حداقل تعداد صندلی (110) در تضاد خواهد بود، زیرا حداقل تعداد مکان ها مربوط به حجمی برابر با 16.5 متر مکعب است (110 * 0.15، که 0.15 حجم جعبه است، یعنی کوچکترین ظرف). با تنظیم حداکثر حجم بر روی 16 متر مکعب، یافتن راه حلراه حلی پیدا نخواهد کرد

با محدودیت 17 مترمکعب یافتن راه حلراه حلی پیدا خواهد کرد.

برخی تنظیمات یافتن راه حل

روش حل
مدل در نظر گرفته شده در بالا خطی است، یعنی. تابع هدف (M وزن کل است که می تواند حداکثر باشد) با معادله زیر M=a1*x1+a2*x2 بیان می شود، که در آن x1 و x2 متغیرهای مدل (تعداد جعبه ها و جعبه ها)، a1 و a2 هستند. وزن آنها هستند. در یک مدل خطی، قیود نیز باید توابع خطی متغیرها باشند. در مورد ما، محدودیت حجمی V=b1*x1+b2*x2 نیز به صورت یک رابطه خطی بیان می شود. بدیهی است که یک محدودیت دیگر - حداکثر تعداد کانتینرها (n) - نیز خطی x1+x2 است. مسائل خطی معمولاً با استفاده از روش سیمپلکس حل می شوند. با انتخاب این روش حل در پنجره یافتن راه حلهمچنین می توان خود مدل را برای خطی بودن آزمایش کرد. در مورد مدل غیر خطی، پیام زیر را دریافت خواهید کرد:

در این صورت باید روشی را برای حل مسئله غیرخطی انتخاب کرد. نمونه هایی از وابستگی های غیر خطی: V=b1*x1*x1; V=b1*x1^0.9; V=b1*x1*x2، که x یک متغیر و V یک تابع هدف است.

دکمه های افزودن، ویرایش، حذف
این دکمه ها به شما امکان می دهند محدودیت های مدل را اضافه، اصلاح و حذف کنید.

کلید ریست
برای حذف تمام تنظیمات یافتن راه حلدکمه را فشار دهید بازنشانی کنید- کادر محاوره ای پاک می شود.

این گزینه هنگام استفاده از گزینه های محدودیت های مختلف مفید است. هنگام ذخیره پارامترهای مدل (دکمه بارگیری/ذخیره،بعد روی دکمه کلیک کنید صرفه جویی) پیشنهاد می شود سلول بالایی محدوده (ستون) را انتخاب کنید که در آن قرار می گیرد: پیوندی به تابع هدف، پیوندهایی به سلول ها با متغیرها، محدودیت ها و پارامترهای روش های حل (قابل دسترسی از طریق دکمه گزینه ها). قبل از ذخیره مطمئن شوید که این محدوده شامل داده های مدل نباشد.
برای بارگیری پارامترهای ذخیره شده، ابتدا دکمه را فشار دهید بارگذاری/ذخیره، سپس در کادر محاوره ای که ظاهر می شود، دکمه دانلودو سپس محدوده سلول های حاوی تنظیمات ذخیره شده قبلی را تنظیم کنید (شما نمی توانید فقط یک سلول بالا را مشخص کنید). روی دکمه OK کلیک کنید. تنظیم مجدد تنظیمات کار فعلی و جایگزینی آنها با تنظیمات جدید را تأیید کنید.

دقت
هنگام ایجاد یک مدل، محقق ابتدا تخمینی از دامنه تغییرات تابع هدف و متغیرها دارد. با در نظر گرفتن محاسبات در MS EXCEL، توصیه می شود که این دامنه های تغییرات به طور قابل توجهی بالاتر از دقت محاسبه باشد (معمولاً از 0.001 تا 0.000001 تنظیم می شود). به عنوان یک قاعده، داده های مدل نرمال می شوند به طوری که دامنه تغییرات تابع هدف و متغیرها بین 0.1 - 100000 باشد. البته همه چیز به مدل خاص بستگی دارد، اما اگر متغیرهای شما بیش از 5- تغییر کنند. 6 مرتبه بزرگی، پس شاید باید مدل را "خشن" کنید، به عنوان مثال، با استفاده از عملیات لگاریتم.

افزونه اکسل "جستجوی راه حل" یک ابزار تحلیلی است که به ما امکان می دهد به سرعت و به راحتی تعیین کنیم که تحت شرایط خاص چه زمانی و به چه نتیجه ای خواهیم رسید. قدرت ابزار حل کننده بسیار بالاتر از «انتخاب پارامتر» در اکسل است.

تفاوت های اصلی بین یافتن راه حل و انتخاب یک پارامتر:

1. انتخاب چندین گزینه در اکسل
2. تحمیل شرایط محدود کننده تغییرات در سلول هایی که دارای مقادیر متغیر هستند.
3. امکان استفاده در مواردی که راه حل های زیادی برای یک مشکل وجود دارد.

مثال ها و وظایف برای یافتن راه حل در اکسل

قابلیت های تحلیلی افزونه را در نظر بگیرید. به عنوان مثال، شما باید 14000 دلار در طول 10 سال پس انداز کنید. به مدت 10 سال، شما می خواهید سالانه 1000 دلار با 5٪ در سال در حساب سپرده در یک بانک پس انداز کنید. شکل زیر جدولی در اکسل است که به وضوح مانده وجوه انباشته شده برای هر سال را نشان می دهد. همانطور که مشاهده می شود، در چنین شرایطی از حساب سپرده و مشارکت های انباشت، حتی پس از 10 سال نیز هدف محقق نخواهد شد. دو راه برای حل این مشکل وجود دارد:

1. بانکی را پیدا کنید که نرخ سود بالاتری برای سپرده ها ارائه دهد.
2. میزان کمک های مالی سالانه به حساب بانکی را افزایش دهید.

ما می توانیم مقادیر متغیر را در سلول های B1 و B2 تغییر دهیم تا شرایط لازم برای انباشت مقدار پول مورد نیاز را انتخاب کنیم.

افزونه "جستجوی راه حل" - به ما امکان می دهد به طور همزمان از 2 مورد از این گزینه ها استفاده کنیم تا بهینه ترین شرایط را برای دستیابی به هدف خود به سرعت شبیه سازی کنیم. برای این:

همانطور که می بینید، این برنامه کمی نرخ بهره و میزان مشارکت سالانه را افزایش داد.



محدودیت پارامترها هنگام جستجو برای راه حل

فرض کنید با این جدول به بانک رفته اید، اما بانک از افزایش نرخ سود شما امتناع می کند. در چنین مواقعی باید دریابیم که چقدر برای افزایش میزان سرمایه گذاری سالانه مجبور خواهیم بود. ما باید یک محدودیت سلولی با یک مقدار متغیر تعیین کنیم. اما قبل از شروع، مقادیر موجود در سلول های متغیر را به مقادیر اصلی تغییر دهید: در B1 5٪ و در B2 با -1000$. و اکنون موارد زیر را انجام می دهیم.