بهینه سازی تحویل

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

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

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

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

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

• جدول زرد روشن (C4:G6) هزینه ارسال یک کالا از هر انبار به هر فروشگاه را توضیح می دهد.
• سلول های بنفش (C15:G14) مقدار کالاهای مورد نیاز برای فروش هر فروشگاه را توصیف می کند.
• سلول های قرمز (J10:J13) ظرفیت هر انبار را نشان می دهد - حداکثر مقدار کالایی که انبار می تواند نگه دارد.
• سلول های زرد (C13:G13) و آبی (H10:H13) به ترتیب مجموع ردیف ها و ستون های سلول های سبز هستند.
• کل هزینه حمل و نقل (J18) به عنوان مجموع محصولات تعداد کالا و هزینه های حمل و نقل مربوطه آنها محاسبه می شود - برای محاسبه، تابع در اینجا استفاده می شود. SUMPRODUCT (مجموع محصول).
  

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

راه حل

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

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

بیایید افزونه را اجرا کنیم:

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

• عملکرد هدف را بهینه کنید (تنظیم tپول سلول) - در اینجا لازم است هدف اصلی نهایی بهینه سازی خود را نشان دهیم، یعنی جعبه صورتی با کل هزینه حمل و نقل (J18). سلول هدف را می توان به حداقل رساند (اگر هزینه باشد، مانند مورد ما)، حداکثر شود (اگر مثلاً سود باشد) یا سعی کنید آن را به مقدار معینی برسانید (مثلاً دقیقاً در بودجه تخصیص یافته قرار گیرد).
• تغییر سلول های متغیر (By متغیر سلول ها) - در اینجا سلول های سبز (C10: G12) را نشان می دهیم، با تغییر مقادیری که می خواهیم به نتیجه خود دست یابیم - حداقل هزینه تحویل.
• مطابق با محدودیت ها (موضوع به la محدودیت ها) – فهرستی از محدودیت هایی که باید هنگام بهینه سازی در نظر گرفته شوند. برای افزودن محدودیت به لیست، روی دکمه کلیک کنید اضافه کردن (اضافه کردن) و در پنجره ظاهر شده شرط را وارد کنید. در مورد ما، این محدودیت تقاضا خواهد بود:

   

  و محدودیت در حداکثر حجم انبارها:

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

برای جلوگیری از این اتفاق، بهتر است چک باکس را فعال بگذارید. متغیرهای نامحدود را غیر منفی کنید یا حتی گاهی اوقات به صراحت چنین لحظاتی را در لیست محدودیت ها ثبت می کنند.

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

در لیست کشویی انتخاب روش حل، علاوه بر این باید روش ریاضی مناسب را برای حل سه گزینه انتخاب کنید:

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

وظیفه ما به وضوح خطی است: تحویل 1 قطعه - 40 روبل خرج شده، تحویل 2 قطعه - 80 روبل خرج شده است. و غیره، بنابراین روش سیمپلکس بهترین انتخاب است.

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

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

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

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

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

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

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