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

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

شرح وظایف حمل و نقل

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

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

چگونه قابلیت Find Solution را در اکسل فعال کنیم

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

1. منوی "File" را که در گوشه سمت چپ بالای پنجره برنامه قرار دارد، باز کنید.
2. پس از آن، روی دکمه با پارامترها کلیک کنید.
3. بعد، زیربخش "تنظیمات" را پیدا می کنیم و به منوی مدیریت افزونه ها می رویم. اینها برنامه های کوچکی هستند که در محیط Microsoft Excel اجرا می شوند. می بینیم که ابتدا روی منوی «افزونه ها» کلیک کردیم و سپس در قسمت پایین سمت راست مورد «افزونه های اکسل» را تنظیم کردیم و روی دکمه «رفتن» کلیک کردیم. تمام اقدامات لازم با مستطیل ها و فلش های قرمز برجسته می شوند.
4. بعد، افزونه "جستجوی راه حل" را روشن کنید، پس از آن، اقدامات خود را با فشار دادن دکمه OK تأیید می کنیم. بر اساس شرح تنظیمات، می‌توانیم ببینیم که برای تجزیه و تحلیل داده‌های پیچیده، مانند علمی و مالی طراحی شده است.
5. پس از آن، به تب "داده ها" بروید، جایی که دکمه جدیدی را مشاهده می کنیم که همان افزونه نامیده می شود. می توان آن را در گروه ابزار تجزیه و تحلیل یافت.

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

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

علاوه بر وظایف حمل و نقل، این افزونه برای اهداف زیر نیز استفاده می شود:

1. تدوین برنامه تولید. یعنی چند واحد از یک محصول باید تولید شود تا حداکثر درآمد حاصل شود.
2. توزیع نیروی کار برای انواع مختلف کار را به گونه ای بیابید که کل هزینه تولید یک محصول یا خدمات کمترین باشد.
3. حداقل زمان لازم برای تکمیل تمام کارها را تنظیم کنید.

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

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

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

1. تابع Goal Seek با بیش از یک متغیر کار نمی کند.
2. توانایی تعیین محدودیت برای متغیرها را فراهم نمی کند.
3. فقط می تواند برابری تابع هدف را با مقدار معینی تعیین کند، اما یافتن حداکثر و حداقل را ممکن نمی کند. بنابراین برای وظیفه ما مناسب نیست.
4. قادر به محاسبه کارآمد تنها در صورتی که نوع خطی مدل باشد. اگر مدل غیر خطی باشد، مقداری را پیدا می‌کند که نزدیک‌ترین مقدار به مقدار اصلی است.

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

نمونه ای از حل مشکل حمل و نقل در اکسل

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

شرایط وظایف

فرض کنید 6 فروشنده و 7 خریدار داریم. توزیع تقاضا و عرضه بین آنها به ترتیب زیر است: 36، 51، 32، 44، 35 و 38 واحد فروشنده و 33، 48، 30، 36، 33، 24 و 32 واحد خریدار هستند. اگر همه این مقادیر را جمع آوری کنید، متوجه می شوید که عرضه و تقاضا در تعادل هستند. بنابراین این مشکل از نوع بسته است که خیلی ساده حل می شود.

علاوه بر این، ما اطلاعاتی در مورد مقدار هزینه ای که باید برای حمل و نقل از نقطه A به نقطه B خرج کنید (در مثال در سلول های زرد برجسته شده اند) داریم.

راه حل - الگوریتم گام به گام

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

1. ابتدا جدولی متشکل از 6 سطر و 7 ستون درست می کنیم.
2. پس از آن، به هر سلولی که حاوی هیچ مقداری نیست و در عین حال خارج از جدول جدید ایجاد شده است می رویم و تابع را وارد می کنیم. برای این کار بر روی دکمه fx که در سمت چپ خط ورودی تابع قرار دارد کلیک کنید.
3. ما پنجره ای داریم که در آن باید دسته "ریاضی" را انتخاب کنیم. ما به چه کارکردی علاقه مندیم؟ موردی که در این اسکرین شات مشخص شده است. عملکرد SUMPRODUCT محدوده ها یا آرایه ها را در بین خود ضرب می کند و آنها را جمع می کند. فقط چیزی که ما نیاز داریم پس از آن، کلید OK را فشار دهید.
4. بعد، پنجره ای روی صفحه ظاهر می شود که در آن باید پارامترهای عملکرد را مشخص کنید. آنها به شرح زیر هستند:
   1. آرایه 1. این اولین آرگومان است که در آن محدوده ای را می نویسیم که با رنگ زرد برجسته شده است. می توانید پارامترهای عملکرد را با استفاده از صفحه کلید یا با انتخاب ناحیه مناسب با دکمه سمت چپ ماوس تنظیم کنید.
   2. آرایه 2. این آرگومان دوم است که جدول جدید ایجاد شده است. اقدامات به همین ترتیب انجام می شود.

با فشار دادن دکمه OK اقدام خود را تایید کنید.

1. پس از آن، روی سلولی که سمت چپ بالای جدول جدید ایجاد شده است، کلیک چپ می کنیم. حالا دوباره روی دکمه insert function کلیک کنید.
2. ما همان دسته را مانند مورد قبلی انتخاب می کنیم. اما این بار ما به عملکرد علاقه مندیم مبلغ.
3. اکنون مرحله پر کردن استدلال ها فرا می رسد. به عنوان اولین آرگومان، ردیف بالای جدولی را که در ابتدا ایجاد کردیم، می نویسیم. به روش قبلی، این کار را می توان با انتخاب این سلول ها در برگه یا به صورت دستی انجام داد. ما اقدامات خود را با فشار دادن دکمه OK تأیید می کنیم.
4. نتایج را در سلول با تابع خواهیم دید. در این حالت صفر است. بعد، مکان نما را به گوشه پایین سمت راست حرکت دهید، پس از آن یک نشانگر تکمیل خودکار ظاهر می شود. شبیه یک مخمل مشکی کوچک است. اگر ظاهر شد، دکمه سمت چپ ماوس را نگه دارید و مکان نما را به آخرین سلول جدول منتقل کنید.
5. این به ما این فرصت را می دهد تا فرمول را به تمام سلول های دیگر منتقل کنیم و بدون نیاز به انجام محاسبات اضافی نتایج صحیح را بدست آوریم.
6. مرحله بعدی انتخاب سلول بالا سمت چپ و چسباندن تابع است مبلغ به او پس از آن، آرگومان ها را وارد کرده و از نشانگر تکمیل خودکار برای پر کردن تمام سلول های باقی مانده استفاده می کنیم.
7. پس از آن مستقیماً به حل مشکل می پردازیم. برای این کار از افزونه ای که قبلا درج کردیم استفاده می کنیم. به تب "داده" بروید و در آنجا ابزار "جستجوی راه حل" را پیدا می کنیم. روی این دکمه کلیک می کنیم.
8. اکنون پنجره ای جلوی چشم ما ظاهر شده است که از طریق آن می توانید پارامترهای افزونه ما را پیکربندی کنید. بیایید نگاهی به هر یک از این گزینه ها بیندازیم:
   1. بهینه سازی تابع هدف در اینجا باید سلول حاوی تابع را انتخاب کنیم SUMPRODUCT. می بینیم که این گزینه امکان انتخاب تابعی را فراهم می کند که راه حلی برای آن جستجو می شود.
   2. قبل از. در اینجا ما گزینه "حداقل" را تنظیم می کنیم.
   3. با تغییر سلول های متغیرها. در اینجا محدوده مربوط به جدولی را که در همان ابتدا ایجاد کردیم (به استثنای ردیف و ستون خلاصه کننده) نشان می دهیم.
   4. مشروط به محدودیت. در اینجا باید با کلیک بر روی دکمه Add محدودیت ها را اضافه کنیم.
   5. ما به یاد داریم که چه نوع محدودیتی را باید ایجاد کنیم - مجموع ارزش های خواسته های خریداران و پیشنهادات فروشندگان باید یکسان باشد.
9. وظیفه محدودیت ها به شرح زیر انجام می شود:
   1. پیوند به سلول ها در اینجا محدوده جدول را برای محاسبات وارد می کنیم.
   2. مقررات. این یک عملیات ریاضی است که در مقابل آن محدوده مشخص شده در اولین فیلد ورودی بررسی می شود.
   3. مقدار شرط یا محدودیت. در اینجا ستون مناسب را در جدول منبع وارد می کنیم.
   4. پس از اتمام تمام مراحل، روی دکمه OK کلیک کنید و بدین ترتیب اقدامات ما را تأیید کنید.

ما دقیقاً همان عملیات را برای ردیف های بالا انجام می دهیم و شرایط زیر را تنظیم می کنیم: آنها باید برابر باشند.

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

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

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

نتیجه

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