حل تابع در اکسل فعال کردن، استفاده از حروف با اسکرین شات

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

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

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

چگونه قابلیت «جستجوی راه حل» را روشن کنیم

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

1. با کلیک بر روی نام مناسب، "فایل" را باز کنید.
2. روی بخش "تنظیمات" کلیک کنید.
3. سپس زیربخش "افزونه ها" را انتخاب کنید. تمام افزونه های برنامه در اینجا نمایش داده می شوند ، کتیبه "Management" در زیر ظاهر می شود. در سمت راست آن یک منوی پاپ آپ وجود دارد که در آن باید "Excel Add-ins" را انتخاب کنید. سپس بر روی "برو" کلیک کنید.
   

   

4. یک پنجره اضافی "افزونه ها" روی مانیتور نمایش داده می شود. کادر کنار تابع مورد نظر را علامت زده و OK کنید.
5. تابع مورد نظر در نوار سمت راست بخش "داده ها" ظاهر می شود.

درباره مدل ها

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

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

وظایف بهینه سازی معمولی:

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

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

مرحله آمادگی

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

وظیفه ما محاسبه تخفیفی خواهد بود که مبالغ فروش محصولات مختلف در آن ضرب می شود. این 2 عنصر با فرمولی به صورت زیر به هم متصل می شوند: =D13*$2 G$. جایی که در D13 مقدار کل برای پیاده سازی نوشته شده است و $2 G$ آدرس عنصر مورد نظر است.

استفاده از تابع و تنظیم آن

وقتی فرمول آماده شد، باید مستقیماً از خود تابع استفاده کنید:

1. شما باید به بخش "داده" بروید و روی "جستجوی راه حل" کلیک کنید.

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

3. در مرحله بعد، باید به تنظیمات سایر پارامترها بروید. در قسمت “To:” امکان تعیین حداکثر و حداقل محدودیت یا عدد دقیق وجود دارد.

4. سپس فیلد “تغییر مقادیر متغیرها:” پر می شود. در اینجا داده های سلول مورد نظر وارد می شود که حاوی مقدار مشخصی است. مختصات به طور مستقل ثبت می شوند یا سلول مربوطه در سند کلیک می شود.

5. سپس برگه "با توجه به محدودیت ها:" ویرایش می شود، جایی که محدودیت های داده های اعمال شده تنظیم شده است. به عنوان مثال، کسرهای اعشاری یا اعداد منفی حذف می شوند.

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

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

8. علاوه بر این، امکان انتخاب تنظیمات پیشرفته دیگر نیز وجود دارد. در سمت راست پایین گزینه "گزینه ها" وجود دارد که به شما این امکان را می دهد.

9. در تنظیمات، می‌توانید «دقت محدودیت» و «محدودیت‌های راه‌حل» را تنظیم کنید. در شرایط ما، نیازی به استفاده از این گزینه ها نیست.

10. پس از تکمیل تنظیمات، عملکرد خود شروع می شود - روی "یافتن یک راه حل" کلیک کنید.

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

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

• یافتن راه حل با استفاده از گرادیان تعمیم یافته برای مسائل غیرخطی. به طور پیش فرض از این گزینه استفاده می شود اما امکان استفاده از گزینه های دیگر نیز وجود دارد.
• یافتن راه حل برای مسائل خطی بر اساس روش سیمپلکس.
• استفاده از جستجوی تکاملی برای تکمیل یک کار.

توجه! هنگامی که گزینه های بالا نتوانستند با این کار کنار بیایند، باید دوباره داده ها را در تنظیمات بررسی کنید، زیرا این اغلب اشتباه اصلی در چنین کارهایی است.

14. وقتی تخفیف مورد نظر دریافت شد، برای محاسبه میزان تخفیف برای هر کالا، اعمال آن باقی می ماند. برای این منظور، عنصر اولیه ستون "مبلغ تخفیف" برجسته می شود، فرمول نوشته می شود «=D2*$2 G$» و "Enter" را فشار دهید. نشانه های دلار به گونه ای قرار می گیرند که وقتی فرمول به خطوط مجاور کشیده می شود، G2 تغییر نمی کند.

15. اکنون مبلغ تخفیف برای کالای اولیه دریافت می شود. سپس باید مکان نما را روی گوشه سلول حرکت دهید، هنگامی که به "plus" تبدیل شد، LMB فشار داده می شود و فرمول به خطوط مورد نیاز کشیده می شود.
16. پس از آن، میز در نهایت آماده خواهد شد.

بارگذاری/ذخیره گزینه های جستجو

این گزینه هنگام اعمال گزینه های محدودیت های مختلف مفید است.

1. در منوی گزینه های راه حل یاب، روی Load/Save کلیک کنید.
2. محدوده مربوط به ناحیه مدل را وارد کنید و روی Save یا Load کلیک کنید.

هنگام ذخیره مدل، یک مرجع به 1 سلول از یک ستون خالی که مدل بهینه سازی در آن قرار می گیرد، وارد می شود. در طول بارگذاری مدل، یک مرجع به کل محدوده ای که شامل مدل بهینه سازی است وارد می شود.

مهم! برای ذخیره آخرین تنظیمات در منوی گزینه های راه حل، یک کتاب کار ذخیره می شود. هر برگه در آن گزینه های افزودنی Solver خود را دارد. علاوه بر این، می توان با کلیک بر روی دکمه "بارگیری یا ذخیره" بیش از 1 کار برای یک برگه تنظیم کرد تا تکالیف جداگانه ذخیره شوند.

یک مثال ساده از استفاده از Solver

لازم است ظرف را با ظروف بارگیری کنید تا جرم آن حداکثر باشد. حجم مخزن 32 متر مکعب است. متر یک جعبه پر دارای وزن 20 کیلوگرم، حجم آن 0,15 متر مکعب است. متر جعبه - 80 کیلوگرم و 0,5 مکعب. متر لازم است تعداد کل ظروف حداقل 110 عدد باشد. داده ها به این صورت سازماندهی می شوند:

متغیرهای مدل با رنگ سبز مشخص شده اند. تابع هدف با رنگ قرمز مشخص شده است. محدودیت ها: با کوچکترین تعداد ظروف (بیشتر یا مساوی 110) و وزن (=SUMPRODUCT(B8:C8,B6:C6) - وزن کل وزن در ظرف.

بر اساس قیاس، حجم کل را در نظر می گیریم: =SUMPRODUCT(B7:C7,B8:C8). چنین فرمولی برای تعیین محدودیت در حجم کل ظروف ضروری است. سپس، از طریق "جستجوی راه حل"، پیوندها به عناصر دارای متغیرها، فرمول ها و خود شاخص ها (یا پیوندهایی به سلول های خاص) وارد می شوند. البته تعداد ظروف یک عدد صحیح است (محدودیت هم هست). ما "یافتن راه حل" را فشار می دهیم، در نتیجه زمانی که جرم کل حداکثر است و تمام محدودیت ها در نظر گرفته می شود، چنین تعداد ظروف را پیدا می کنیم.

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

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

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

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

در هر شرایطی، ابتدا باید مدل را برای عدم وجود تضاد محدودیت بررسی کنید. اغلب، این با انتخاب نادرست نسبت یا نشانگر محدود کننده مرتبط است.

در مثال بالا نشانگر حداکثر حجم 16 متر مکعب است. متر به جای 32، زیرا چنین محدودیتی با شاخص های حداقل تعداد صندلی در تضاد است، زیرا با تعداد 16,5 متر مکعب مطابقت دارد. متر

نتیجه

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