حل مسائل برنامه نویسی خطی در اکسل - چکیده. مشکل برنامه نویسی خطی

برنامه نویسی خطی بخشی است که رشته "برنامه ریزی ریاضی" از آنجا شروع به توسعه کرد. اصطلاح "برنامه نویسی" در نام این رشته هیچ شباهتی با اصطلاح "برنامه نویسی (به عنوان مثال، کامپایل کردن برنامه ها) برای یک کامپیوتر" ندارد، زیرا رشته "برنامه نویسی خطی" حتی قبل از زمانی که رایانه ها به طور گسترده مورد استفاده قرار گرفتند به وجود آمد. در حل مسائل ریاضی و مهندسی، مسائل اقتصادی و غیره. اصطلاح "برنامه ریزی خطی" در نتیجه ترجمه نادرست انگلیسی "برنامه ریزی خطی" به وجود آمد. یکی از معانی کلمه "برنامه نویسی" برنامه ریزی، برنامه ریزی است. در نتیجه، ترجمه صحیح «برنامه‌نویسی خطی» «برنامه‌نویسی خطی» نیست، بلکه «برنامه‌ریزی خطی» است که محتوای رشته را با دقت بیشتری منعکس می‌کند. اما اصطلاح برنامه نویسی خطی، برنامه ریزی غیرخطی و غیره. در ادبیات ما پذیرفته شده اند. مسائل برنامه ریزی خطی یک مدل ریاضی مناسب برای تعداد زیادی از مسائل اقتصادی (برنامه ریزی تولید، مصرف مواد، حمل و نقل و غیره) است. استفاده از روش برنامه ریزی خطی مهم و ارزشمند است - گزینه بهینه از تعداد نسبتاً قابل توجهی از گزینه های جایگزین انتخاب می شود. همچنین تمامی مسائل اقتصادی حل شده با استفاده از برنامه ریزی خطی با راه حل های جایگزین و شرایط محدود کننده مشخص متمایز می شوند.در صفحات گسترده اکسل با استفاده از تابع جستجوی راه حل می توان مقداری را در سلول هدف جستجو کرد و مقدار متغیرها را تغییر داد. در این مورد، برای هر متغیر می توانید محدودیت هایی، به عنوان مثال، یک حد بالا تعیین کنید. قبل از شروع جستجو برای راه حل، لازم است که مشکل حل شده در مدل به وضوح فرموله شود، یعنی. تعیین شرایطی که در طول بهینه سازی باید رعایت شود. نقطه شروع برای یافتن راه حل بهینه، مدل محاسباتی ایجاد شده در کاربرگ است. برنامه جستجوی راه حل به داده های زیر نیاز دارد. 1. سلول هدف، سلولی در یک مدل محاسباتی است که مقادیر آن باید حداکثر، حداقل یا برابر با یک مقدار مشخص خاص باشد. باید حاوی فرمولی باشد که به طور مستقیم یا غیرمستقیم به سلول‌های در حال تغییر اشاره دارد یا خود باید اصلاح شود. 2. مقادیر سلول های در حال تغییر به صورت متوالی (با تکرار) تغییر می کنند تا زمانی که مقدار مورد نظر در سلول هدف به دست آید. بنابراین این سلول ها باید به طور مستقیم یا غیرمستقیم بر ارزش سلول هدف تأثیر بگذارند. 3. شما می توانید محدودیت ها و شرایط مرزی را برای سلول های هدف و تغییر یافته تعیین کنید. همچنین می توانید محدودیت هایی را برای سلول های دیگر تعیین کنید. به طور مستقیم یا غیر مستقیم در مدل وجود دارد. این برنامه امکان تنظیم پارامترهای ویژه ای را فراهم می کند که روند یافتن راه حل را تعیین می کند. پس از تنظیم تمام پارامترهای لازم، می توانید شروع به جستجو برای راه حل کنید. تابع جستجوی راه حل بر اساس نتایج کار خود سه گزارش ایجاد می کند که می تواند در کتاب کار علامت گذاری شود. محدودیت ها شرایطی هستند که ابزار جستجوی راه حل هنگام بهینه سازی مدل باید رعایت شود.

مطالعه ادبیات نشان داد که:

1. برنامه نویسی خطی یکی از اولین و کاملاً مطالعه شده ترین بخش های برنامه ریزی ریاضی است. این برنامه‌ریزی خطی بود که از آن بخش خود رشته «برنامه‌نویسی ریاضی» شروع به توسعه کرد.

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

• · استفاده منطقی از مواد خام و مواد؛ برش مشکلات بهینه سازی؛
• · بهینه سازی برنامه تولید شرکت ها.
• · محل بهینه و تمرکز تولید.
• · تدوین برنامه حمل و نقل بهینه و عملیات حمل و نقل.
• · مدیریت موجودی.
• · و بسیاری دیگر متعلق به حوزه برنامه ریزی بهینه.
• 2. روش گرافیکی برای حل مسائل برنامه ریزی خطی با دو متغیر کاملاً ساده و شهودی است. این مبتنی بر نمایش هندسی راه حل های امکان پذیر و TF های مسئله است.

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

مثالی از حل مسئله برنامه ریزی خطی با استفاده از MSبرتری داشتن

این مزرعه در کشاورزی مزرعه ای برای تولید غلات، چغندر قند و آفتابگردان تخصص دارد. در کشاورزی این بنگاه دارای 3200 هکتار زمین زراعی، نیروی کار به میزان 7000 نفر در روز و کود معدنی به میزان 15000 c.d.w است. لازم است ترکیبی از سطح زیر کشت پیدا شود که حداکثر سود را تضمین کند.

این را نیز باید در نظر گرفت که

- سطح زیر کشت محصولات صنعتی (چغندرقند و آفتابگردان) نباید از 25 درصد کل مساحت زمین زراعی تجاوز کند.

- مزرعه قراردادی برای فروش غلات به مبلغ 65000 سی منعقد کرد.

برای توسعه یک مدل اقتصادی و ریاضی، تهیه اطلاعات ورودی ضروری است (جدول 1).

میز 1

شاخص ها	محصولات کشاورزی
	غلات	چغندر قند	آفتابگردان
بهره وری، c/ha
قیمت فروش 1 سانت محصولات روبل/ج.
هزینه محصولات قابل فروش در هر هکتار، هزار روبل.	5,59	20,62	6,73
هزینه در هر هکتار: MDS، هزار روبل.		12,7
کار، روزهای انسانی
کودهای معدنی، c.d.v.
سود از 1 هکتار، مالش.	2,89	7,93	3,63

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

ایکس 1 - محصولات غلات

ایکس 2- چغندر قند

ایکس 3 - آفتابگردان

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

- مجموع سطح زیر کشت محصولات کشاورزی نباید از سطح موجود در مزرعه (3200 هکتار) تجاوز کند. ضرایب مجهولات در این محدودیت مصرف زمین قابل کشت در هر هکتار از هر محصول را مشخص می کند. در این صورت ضرایب فنی و اقتصادی برای مجهولات برابر با یک خواهد بود. کل مساحت زمین زراعی در سمت راست ثبت شده است.

1) X1+X2+X3<=3200

- مجموع سطوح کشت شده با محصولات صنعتی نباید از سطحی که می توان به این منظور اختصاص داد (3200 * 0.25 = 800 هکتار) بیشتر باشد. ضرایب مجهولات در این محدودیت، مصرف زمین قابل کشت اختصاص داده شده برای کاشت محصولات صنعتی در هر هکتار از هر محصول کشاورزی صنعتی را مشخص می کند. در این صورت ضرایب فنی و اقتصادی برای مجهولات X2 و X3 برابر با یک و برای محصولات کشاورزی غیر فنی (X3) - صفر خواهد بود. در سمت راست حداکثر مساحت زمین زراعی قابل اختصاص برای کاشت محصولات صنعتی نوشته شده است.

2) X2 + X3<=800

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

3) 1.5Х1+4.5Х2+1.5Х3<=7000

4) 2Х1+15Х2+2.3Х3<=15000

- محدودیت پنجم تولید حجم برنامه ریزی شده غلات را تضمین می کند. ضرایب برای متغیرها عملکرد دانه در هر هکتار از سطح زیرکشت کشاورزی است. محصولات زراعی هنگامی که X1 ناشناخته است، این عملکرد دانه است (جدول 1). برای متغیرهای X2 و X3 این ضریب صفر است. در سمت راست طرح تولید غلات است.

5) 26X1>=65000

در نتیجه یک سیستم پنج نابرابری خطی با سه مجهول به دست می آید. لازم است چنین مقادیر غیر منفی این مجهولات پیدا شود X1>=0; X2>=0; X3>=0، که این سیستم نابرابری را برآورده می کند و حداکثر سود را از صنعت تولید محصول به طور کلی تضمین می کند:

Z max = 2.89Х1+7.93Х2+3.53Х3

ضرایب مجهولات در تابع هدف، سود دریافتی از 1 هکتار از سطح زیر کشت محصولات است. این ضرایب بر اساس داده های جدول 1 محاسبه شده است.

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

تصویر 1

تمام اطلاعات توسعه یافته در یک مدل اقتصادی و ریاضی دقیق خلاصه شده و در کاربرگ MS وارد می شودبرتری داشتن. (شکل 2.)

شکل 2

توصیه می شود داده ها را به صورت پیوند به سلول ها با اطلاعات مربوطه در کاربرگ های محاسبه یا کاربرگ هایی با اطلاعات اولیه وارد مدل کنید. شکل 3 نحوه در یک سلول را نشان می دهد F9اطلاعاتی در مورد میزان مصرف کود در هر هکتار کاشت آفتابگردان ارائه شده است.

شکل 3

به ستون ها آ («№»), که در("محدودیت های")، با("واحدها") واچ("نوع محدودیت")، داده های مربوطه مستقیماً در مدل وارد می شوند (شکل 1). آنها در محاسبات استفاده نمی شوند و برای اهداف اطلاعاتی و تسهیل درک محتویات مدل استفاده می شوند. به ستون من("حوزه محدودیت ها")، پیوندها به سلول های حاوی اطلاعات مربوط به نام ستون (مقادیر سمت راست نابرابری های ساخته شده قبلی) وارد می شوند.

برای مقادیر دلخواه متغیرها X1, X2, X3ما سلول های خالی را ترک کردیم - بر این اساس D5, E 5, F 5. در ابتدا سلول های خالی برنامه MS Excel سلول هایی را که مقدار آنها صفر است درک می کند. ستون جی، با ما تماس گرفتیم" مجموع محصولات"، برای تعیین مجموع محصولات مقادیر مجهولات (سلول های D5, E 5, F 5) و ضرایب فنی و اقتصادی با توجه به محدودیت های مربوطه (خط 6-10) و تابع هدف (خط 11). بنابراین، در ستون جیتعریف شده است:

- - مقدار منابع استفاده شده (سلول G6- مساحت کل زمین زراعی؛ G7- زمین های زراعی قابل استفاده برای کاشت محصولات صنعتی؛ G8- منابع کار؛ G9- کودهای معدنی)؛

- - مقدار دانه تولید شده (سلول G10);

- - مقدار سود (سلول G11).

شکل 2 نحوه در یک سلول را نشان می دهد G11ثبت مجموع محصولات مقادیر متغیرها اجرا می شود (مناطق کاشته شده با محصولات کشاورزی - سلول D5, E 5, F 5) برای سود مربوطه از 1 هکتار از محصولات خود (سلول). D11, E 11, F 11)با استفاده از تابع MSبرتری داشتن « SUMPRODUCT" از آنجایی که هنگام نوشتن این فرمول، آدرس دهی مطلق به سلول های از D5قبل ازF 5، این فرمول را می توان در سلول های دیگر کپی کردG 6قبل از G10.

بنابراین، یک طرح مرجع ساخته شد (شکل 2) و اولین راه حل قابل اجرا به دست آمد. ارزش های مجهولات X1, X2, X3برابر با صفر هستند (سلول ها D5, E 5, F 5 -سلول های خالی)، سلول های ستونی جی"مجموع محصولات" در تمام محدودیت ها (خط 6-10) و خط هدف (خط 11) نیز مقادیر صفر دارند.

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

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

پس از انتخاب این دستور، کادر محاوره ای ظاهر می شود (شکل 4).

شکل 4

از آنجایی که ما حداکثر سازی سود را به عنوان یک معیار بهینه سازی در این زمینه انتخاب کردیم سلول هدف را تنظیم کنیدپیوندی به سلول حاوی فرمول محاسبه سود وارد کنید. در مورد ما این سلول است 11 دلار G$. برای به حداکثر رساندن مقدار سلول نهایی با تغییر مقادیر سلول های تأثیرگذار (سلول های تأثیرگذار، در این مورد سلول های در حال تغییر هستند، سلول هایی هستند که برای ذخیره مقادیر مجهولات ناشناخته طراحی شده اند) سوئیچ را روی موقعیت قرار دهید حداکثر مقدار;

در زمینه تغییر سلول هاارجاع به سلول ها را برای تغییر وارد کنید و آنها را با کاما از هم جدا کنید. یا اگر سلول ها مجاور هستند، سلول اول و آخر را نشان می دهد، آنها را با یک کولون از هم جدا کنید ( $ D$5:$F$5).

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

در فصل محدودیت هایکادر محاوره ای یافتن راه حلروی دکمه کلیک کنید اضافه کردن. کادر محاوره ای زیر ظاهر می شود (شکل 5)

شکل 5

در زمینه مرجع سلولیآدرس سلولی که مقدار آن محدود شده است را وارد کنید. در مورد ما، این سلول است $ G$6، فرمول محاسبه زمین زراعی مورد استفاده در طرح فعلی کجاست.

یک عبارت شرطی را از لیست کشویی انتخاب کنید <= ، که باید بین پیوند و محدودیت قرار گیرد.

در زمینه محدودیتپیوندی به سلولی که حاوی مقدار در دسترس بودن زمین قابل کشت در مزرعه است یا پیوندی به این مقدار وارد کنید. در مورد ما، این سلول است $ من 6 دلار

در نتیجه، کادر محاوره ای به شکل زیر خواهد بود (شکل 6).

شکل 6

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

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

شکل 7

برای تغییر یا حذف محدودیت ها در لیست محدودیت هایکادر محاوره ای یافتن راه حلمحدودیتی را که می خواهید تغییر دهید یا حذف کنید مشخص کنید. یک تیم انتخاب کنید تغییر دادنو تغییرات ایجاد کنید یا روی دکمه کلیک کنید حذف.

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

شکل 8

برای شروع کار راه حل، روی دکمه کلیک کنید اجرا کردنو یکی از کارهای زیر را انجام دهید:

- برای بازیابی داده های اصلی، گزینه را انتخاب کنید بازیابی مقادیر اصلی.

شکل 9

برای توقف جستجوی راه حل، کلید را فشار دهید خروج.

برگه مایکروسافت اکسل با در نظر گرفتن مقادیر یافت شده سلول های تأثیرگذار مجدداً محاسبه می شود. در نتیجه حل و ذخیره نتایج جستجو در برگه، مدل به شکل زیر خواهد بود (جدول 10).

شکل 10

در سلول ها D5-F5مقادیر مجهولات مورد نیاز به دست می آید (مساحت محصول برابر است با: دانه - 2500 هکتار، چغندرقند - 661 هکتار، آفتابگردان - 39 هکتار)، در سلول ها G6-G9حجم منابع مورد استفاده تعیین شد (مساحت کل زمین زراعی - 3200 هکتار؛ مساحت زمین قابل استفاده برای کاشت محصولات صنعتی - 700 هکتار؛ نیروی کار - 6781.9 در روز؛ کودهای معدنی - 15000 c.d.v.) ، در سلول G10مقدار دانه تولید شده (65000 سنتر) تعیین شد. با تمام این مقادیر، سود به 12603.5 هزار روبل می رسد. (سلول G11).

اگر جستجو راه حلی را پیدا نکرد که شرایط مشخص شده را برآورده کند، در کادر محاوره ای نتایج جستجوی راه حلیک پیام مربوطه ظاهر می شود (شکل 11).

شکل 11

یکی از رایج ترین دلایل عدم امکان یافتن راه حل بهینه، شرایطی است که در نتیجه حل یک مشکل، مشخص شود که محدودیت هایی وجود دارد که رعایت نشده است. پس از ذخیره راه حل یافت شده در برگه، باید مقادیر به دست آمده از ستون های "Sum of Products" و "Volume of Constraints" را خط به خط مقایسه کنید و بررسی کنید که آیا رابطه بین آنها محدودیت موجود در "نوع از" را برآورده می کند یا خیر. ستون محدودیت ها. بنابراین، با یافتن محدودیت‌های برآورده نشده، لازم است دلایلی را پیدا کرده و از بین ببریم که رعایت این شرط خاص را غیرممکن می‌کند (ممکن است، برای مثال، محدودیت‌های برنامه‌ریزی‌شده خیلی زیاد یا برعکس، بسیار کوچک باشد و غیره).

اگر محدودیت های زیادی در مدل وجود داشته باشد، از نظر بصری مقایسه و بررسی هر خط برای دقت بسیار دشوار است. برای آسان‌تر کردن کار، توصیه می‌شود یک ستون دیگر «Validation» را به مدل اضافه کنید، جایی که از توابع MS استفاده می‌شود.برتری داشتن « اگر"و" گرد» می توانید یک بررسی خودکار را سازماندهی کنید (شکل 12).

شکل 12

باید مشخص شود که تولید محصولات چهار نوع Prod1، Prod2، Prod3، Prod4 در چه مقداری لازم است که تولید آنها به سه نوع منبع نیاز دارد: نیروی کار، مواد اولیه و مالی. مقدار هر نوع منبع مورد نیاز برای تولید یک واحد محصول از یک نوع معین، نرخ مصرف نامیده می شود. نرخ مصرف و همچنین سود دریافتی از فروش یک واحد از هر نوع محصول در شکل 1 نشان داده شده است. 1.

منبع	ادامه 1	محصول 2	محصول 3	محصول 4	امضا کردن	دسترسی
سود
کار یدی
مواد خام
دارایی، مالیه، سرمایه گذاری

تصویر 1.

مدل ریاضی مسئله به شکل زیر است:

که در آن x j مقدار محصولات تولید شده از نوع j است. F – تابع هدف؛ سمت چپ عبارات محدودیت مقادیر را نشان می دهد منبع مورد نیاز، و سمت راست مقدار را نشان می دهد منبع موجود.

وارد کردن شرایط کار

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

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

قسمت های سمت چپ محدودیت ها برای منابع هر نوع در سلول های F8:F10 وارد می شود.

شکل 2.

شکل 3.

حل مسئله برنامه ریزی خطی

برای حل مسائل برنامه نویسی خطی در اکسل از ابزار قدرتمندی به نام استفاده می کنید یافتن راه حل . دسترسی به جستجوی راه حل از منو انجام می شود سرویس ، کادر گفتگوی Search for a Solution روی صفحه ظاهر می شود (شکل 4).

شکل 4.

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

1 یک تابع هدف را با قرار دادن مکان نما در فیلد اختصاص دهید سلول هدف را تنظیم کنید پنجره راه حل را جستجو کنید و در سلول F6 در فرم ورودی کلیک کنید.

2 سوئیچ را برای مقدار تابع هدف روشن کنید، یعنی. آن را نشان دهد برابر با حداکثر مقدار ;

3 آدرس متغیرهایی را که باید تغییر کنند (xj) وارد کنید: برای انجام این کار، مکان نما را در فیلد قرار دهید. تغییر سلول ها پنجره راه حل را جستجو کنید و سپس محدوده سلول های B3:E3 را در فرم ورودی انتخاب کنید.

4 دکمه را فشار دهید اضافه کردن پنجره های جستجوی راه حل برای وارد کردن محدودیت ها برای یک مسئله برنامه ریزی خطی. یک پنجره روی صفحه ظاهر می شود اضافه کردن یک محدودیت (شکل 5) :

شرایط مرزی برای متغیرهای x j (x j ³0) را برای این در فیلد وارد کنید مرجع سلولی سلول B3 مربوط به x 1 را نشان دهید، علامت مورد نظر (³) را از لیست موجود در فیلد انتخاب کنید محدودیت سلول فرم ورودی را که مقدار متناظر شرط مرزی در آن ذخیره شده است (سلول B4) را نشان دهید، روی دکمه کلیک کنید اضافه کردن ; مراحل توضیح داده شده را برای متغیرهای x 2، x 3 و x 4 تکرار کنید.

محدودیت هایی را برای هر نوع منبع در فیلد وارد کنید مرجع سلولی پنجره اضافه کردن یک محدودیت سلول F9 فرم ورودی را نشان می دهد که حاوی بیان سمت چپ محدودیت اعمال شده بر منابع نیروی کار در فیلدها است. محدودیت علامت £ و آدرس H9 را در سمت راست محدودیت نشان دهید، دکمه را فشار دهید اضافه کردن ; به طور مشابه محدودیت هایی را برای انواع دیگر منابع ایجاد می کند.

پس از وارد کردن آخرین محدودیت، به جای اضافه کردن مطبوعات خوب و به پنجره Search for a solution برگردید.

شکل 5.

حل یک مسئله برنامه ریزی خطی با تنظیم پارامترهای جستجو آغاز می شود:

در پنجره یافتن راه حل دکمه را فشار دهید گزینه ها ، یک پنجره روی صفحه ظاهر می شود گزینه های جستجوی راه حل (شکل 6)؛

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

حداکثر تعداد تکرار را مشخص کنید (پیش فرض 100 است که برای حل اکثر مشکلات مناسب است).

کادر را علامت بزنید در صورت نیاز به بررسی تمامی مراحل جستجوی راه حل بهینه؛

کلیک خوب ، به پنجره بازگردید یافتن راه حل .

شکل 6.

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

شکل 7.

برای مثال مورد بررسی، یک راه حل پیدا شده است و نتیجه راه حل بهینه برای مسئله در فرم ورودی نمایش داده می شود: مقدار تابع هدف مربوط به حداکثر سود و برابر با 1320 در سلول F6 نشان داده شده است. فرم ورودی، طرح تولید بهینه x 1 = 10، x 2 = 0، x 3 = 6، x 4 = 0 در سلول های B3:C3 فرم ورودی نشان داده شده است (شکل 8).

مقدار منابع استفاده شده برای تولید محصولات در سلول های F9:F11 نمایش داده می شود: نیروی کار - 16، مواد خام - 84، امور مالی - 100.

شکل 8.

اگر، هنگام تنظیم پارامترها در پنجره گزینه های جستجوی راه حل (شکل 6) چک باکس بررسی شد نمایش نتایج تکرار ، سپس تمام مراحل جستجو به صورت متوالی نشان داده می شود. پنجره ای روی صفحه ظاهر می شود (شکل 9). در این حالت مقادیر جاری متغیرها و توابع هدف در فرم ورودی نشان داده می شود. بنابراین، نتایج اولین تکرار جستجوی راه‌حل برای مسئله اصلی در فرم ورودی در شکل 10 ارائه شده است.

شکل 9.

شکل 10.

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

تجزیه و تحلیل راه حل بهینه

قبل از شروع به تجزیه و تحلیل نتایج راه حل، اجازه دهید مشکل اصلی را در فرم ارائه کنیم

با معرفی متغیرهای اضافی برای i، که مقادیر منابع استفاده نشده را نشان می دهد.

بیایید یک مسئله دوگانه برای مشکل اصلی ایجاد کنیم و متغیرهای دوگانه اضافی v i را معرفی کنیم.

تجزیه و تحلیل نتایج جستجو برای راه حل به ما امکان می دهد آنها را با متغیرهای مسائل اصلی و دوگانه پیوند دهیم.

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

نتایج،

پایداری،

محدودیت ها

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

1 گزارش نتایج(شکل 11) شامل سه جدول است:

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

جدول 2 حاوی مقادیر متغیرهای مورد نیاز x j است که در نتیجه حل مسئله (طرح تولید بهینه) به دست آمده است.

جدول 3 نتایج راه حل بهینه را برای محدودیت ها و برای شرایط مرزی نشان می دهد.

برای محدودیت هایدر ستون فرمولوابستگی هایی که هنگام تنظیم محدودیت ها در پنجره وارد شده اند نشان داده می شوند یافتن راه حل ; در ستون معنیمقادیر منبع مورد استفاده نشان داده شده است؛ در ستون تفاوتمقدار منابع استفاده نشده را نشان می دهد. اگر منبع به طور کامل استفاده شود، در ستون حالتپیام نمایش داده می شود مربوط ; اگر منبع به طور کامل استفاده نشده باشد، این ستون نشان می دهد متصل نیست برای شرایط مرزیمقادیر مشابه با تنها تفاوتی که به جای یک منبع استفاده نشده، تفاوت بین مقدار متغیر x j در جواب بهینه یافت شده و شرایط مرزی تعیین شده برای آن (xj ³0) نشان داده شده است.

در ستون است تفاوتمی توانید مقادیر متغیرهای اضافی y i مسئله اصلی را در فرمول (2) مشاهده کنید. در اینجا y 1 = y 3 = 0، یعنی. مقدار نیروی کار و منابع مالی استفاده نشده صفر است. از این منابع به طور کامل استفاده می شود. در عین حال، مقدار منابع استفاده نشده برای مواد خام y 2 = 26، که به معنی مازاد مواد خام است.

شکل 11.

2 گزارش پایداری(شکل 12) از دو جدول تشکیل شده است.

جدول 1 مقادیر زیر را نشان می دهد:

نتیجه حل مشکل (طرح انتشار بهینه)؛

- نورمیر. قیمت، یعنی مقادیری که نشان می دهد زمانی که یک واحد تولید از نوع مربوطه مجبور می شود در برنامه بهینه گنجانده شود، تابع هدف چقدر تغییر می کند.

ضرایب تابع هدف;

مقادیر حدی برای افزایش ضرایب تابع هدف که در آن برنامه تولید بهینه حفظ می شود.

جدول 2 حاوی داده های مشابه برای محدودیت ها است:

مقدار منابع استفاده شده؛

- قیمت حدودی، نشان می دهد که چگونه تابع هدف زمانی که مقدار منبع مربوطه با یک تغییر می کند تغییر می کند.

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

شکل 12.

گزارش پایداری امکان ارزیابی دوگانه را فراهم می کند.

همانطور که مشخص است، متغیرهای دوگانه z i نشان می‌دهند که وقتی منبع نوع i یکم تغییر می‌کند، تابع هدف چگونه تغییر می‌کند. در یک گزارش اکسل، تخمین دوگانه نامیده می شود قیمت حدودی.

در مثال ما، ماده خام به طور کامل استفاده نشده است و منبع آن y 2 = 26 است. بدیهی است که افزایش مقدار مواد اولیه به عنوان مثال به 111 منجر به افزایش تابع هدف نخواهد شد. بنابراین، برای محدودیت دوم، متغیر دوگانه z 2 = 0. بنابراین، اگر ذخیره ای برای این منبع وجود داشته باشد، پس متغیر اضافیبزرگتر از صفر خواهد بود و ارزیابی دوگانهاین محدودیت صفر است.

در مثال مورد بررسی، منابع نیروی کار و منابع مالی به طور کامل مورد استفاده قرار گرفتند، بنابراین متغیرهای اضافی آنها برابر با صفر است (y 1 =y 3 =0). اگر یک منبع به طور کامل استفاده شود، افزایش یا کاهش آن بر حجم خروجی و در نتیجه مقدار تابع هدف تأثیر می گذارد. تخمین دوگانه محدودیت‌های نیروی کار و منابع مالی با صفر متفاوت است، یعنی. z 1 = 20، z 3 = 10.

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

با افزایش (کاهش) منابع نیروی کار به میزان یک واحد، تابع هدف 20 واحد افزایش (کاهش) و برابر با

F=1320+20×1=1340 (با بزرگنمایی).

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

F=1320+10×1=1330.

در اینجا، در نمودارها افزایش مجازو کاهش مجازجدول 2 محدودیت های مجاز برای تغییر مقدار منابع از نوع j را نشان می دهد. به عنوان مثال، هنگامی که افزایش در ارزش منابع کار از -6 به 3.55 تغییر می کند، همانطور که در جدول نشان داده شده است، ساختار راه حل بهینه حفظ می شود، یعنی بیشترین سود توسط خروجی Prod1 و Prod3 ارائه می شود، اما در مقادیر مختلف

متغیرهای دوگانه اضافی نیز در آن منعکس شده است گزارش پایداریدر ستون نورمیر. قیمتمیز 1.

اگر متغیرهای اصلی در راه حل بهینه گنجانده نشده باشند، یعنی. برابر با صفر هستند (در مثال x 2 = x 4 = 0)، سپس متغیرهای اضافی مربوطه دارای مقادیر مثبت هستند (v 2 = 10، v 4 = 20). اگر متغیرهای اصلی در حل بهینه گنجانده شوند (x 1 = 10، x 3 = 6)، آنگاه متغیرهای دوگانه اضافی آنها برابر با صفر است (v 1 = 0، v 3 = 0).

این مقادیر نشان می‌دهند که تابع هدف چقدر کاهش می‌یابد (بنابراین علامت منهای در مقادیر متغیرهای v 2 و v 4) با آزادسازی اجباری یک واحد از این محصول. بنابراین، اگر بخواهیم یک واحد حاصل از نوع Prod3 را به زور آزاد کنیم، تابع هدف 10 واحد کاهش می یابد و برابر با 1310×10×1320 خواهد بود.

اجازه دهید تغییر در ضرایب تابع هدف را در مدل اصلی (1) با Dсj نشان دهیم. این ضرایب سود دریافتی از فروش یک واحد محصول از نوع j را تعیین می کند.

در نمودارها افزایش مجازو کاهش مجازمیز 1 گزارش پایداریحدود تغییر در Dc j نشان داده شده است که در آن ساختار طرح بهینه حفظ می شود، یعنی. ادامه تولید محصولات از نوع Prodj سودآور خواهد بود. به عنوان مثال، اگر Dc 1 بین -12 £ Dc 1 £ 40 تغییر کند، همانطور که در گزارش نشان داده شده است، تولید محصولات از نوع Prod1 همچنان سودآور خواهد بود. در این حالت مقدار تابع هدف F=1320+x 1 ×Dс j =1320+10×Dс j خواهد بود.

3 گزارش محدودیتدر شکل نشان داده شده است. 13. نشان می دهد که در چه محدوده ای مقادیر x j موجود در راه حل بهینه می تواند تغییر کند در حالی که ساختار راه حل بهینه حفظ می شود. علاوه بر این، برای هر نوع محصول، مقادیر تابع هدف داده می شود، که با جایگزینی به راه حل بهینه مقدار حد پایین تولید محصولات از نوع مربوطه با مقادیر ثابت خروجی سایر محصولات به دست می آید. انواع به عنوان مثال، اگر برای حل بهینه x 1 = 10، x 2 = 0، x 3 = 6، x 4 = 0، x 1 = 0 (حد پایین) را با x 2، x 3 و x 4 بدون تغییر قرار دهیم، آنگاه مقدار مقدار تابع هدف برابر با 60×0+70×0+120×6+130×0=720 خواهد بود.

افزونه ابزاری برای حل مسائل بهینه سازی در MS Excel است یافتن راه حل. روش جستجوی راه حل به شما امکان می دهد مقدار بهینه فرمول موجود در سلولی به نام سلول هدف را پیدا کنید. این روش روی گروهی از سلول‌ها کار می‌کند که به طور مستقیم یا غیرمستقیم با فرمولی در سلول هدف مرتبط هستند. برای به دست آوردن یک نتیجه مشخص از فرمول موجود در سلول هدف، این روش مقادیر سلول های تأثیرگذار را تغییر می دهد.

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

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

در پنجره یافتن راه حلفیلدهای زیر موجود است:

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

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

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

حدس بزن– برای جستجوی خودکار سلول هایی که بر فرمول ارجاع شده در فیلد تنظیم سلول هدف تأثیر می گذارند استفاده می شود. نتیجه جستجو در قسمت Editing Cells نمایش داده می شود.

محدودیت های- برای نمایش لیستی از شرایط مرزی کار است.

اضافه کردن- برای نمایش کادر محاوره ای Add Constraint کاربرد دارد.

تغییر دادن- کادر محاوره ای Edit Limit را نمایش می دهد.

حذف- در خدمت حذف محدودیت مشخص شده است.

اجرا کردن- برای راه‌اندازی جستجو برای راه‌حلی برای یک مشکل مشخص است.

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

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

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

برای حل یک مشکل بهینه سازی، مراحل زیر را دنبال کنید:

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

2. در میدان سلول هدف را تنظیم کنیدآدرس یا نام سلولی را که حاوی فرمول مدلی است که باید بهینه شود را وارد کنید.

3. برای به حداکثر رساندن مقدار سلول هدف با تغییر مقادیر سلول های تأثیرگذار، سوئیچ را روی حداکثر مقدار

برای به حداقل رساندن مقدار سلول هدف با تغییر مقادیر سلول های تأثیرگذار، سوئیچ را روی
حداقل مقدار

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

4. در میدان تغییر سلول هانام یا آدرس سلول‌های موردنظر را که با کاما از هم جدا شده‌اند وارد کنید. سلول هایی که اصلاح می شوند باید مستقیم یا غیرمستقیم با سلول هدف مرتبط باشند. تا 200 سلول متغیر قابل نصب است.

برای یافتن خودکار تمام سلول هایی که بر فرمول مدل تأثیر می گذارند، کلیک کنید فرض.

5. در میدان محدودیت هایتمام محدودیت های اعمال شده در جستجوی راه حل را وارد کنید.

6. روی دکمه کلیک کنید اجرا کردن.

برای بازیابی داده های اصلی، سوئیچ را روی

مرحله C. تجزیه و تحلیل راه حل یافت شده برای مسئله بهینه سازی.

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

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

بازیابی مقادیر اصلی- برای بازیابی مقادیر اصلی سلول های تأثیرگذار مدل عمل می کند.

گزارش ها- برای نشان دادن نوع گزارش قرار داده شده در یک برگه جداگانه از کتاب عمل می کند.

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

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

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

ذخیره اسکریپت- برای نمایش کادر محاوره ای کار می کند ذخیره اسکریپتکه در آن می توانید یک اسکریپت را برای حل یک مشکل ذخیره کنید تا بعداً با استفاده از مدیر اسکریپت MS Excel از آن استفاده کنید. در بخش‌های بعدی، چندین مدل بهینه‌سازی خطی خاص و نمونه‌هایی از راه‌حل‌های آنها را با استفاده از MS Excel در نظر خواهیم گرفت.

2.4 مشکل برنامه ریزی تولید

فرمول بندی مسئله.شرکت باید محصولات تولید کند nانواع: و 1، و 2،...و ص، و مقدار هر محصول تولید شده نباید از تقاضا بیشتر باشد β 1، β 2،...، β nو در عین حال نباید کمتر از مقادیر برنامه ریزی شده باشد b 1 ,b 2 ,...,b nبه ترتیب. برای تولید محصولات استفاده می شود مترانواع مواد اولیه s l ,s 2 ,...,s m، که ذخایر آن به ترتیب با مقادیر γ محدود می شود 1 , γ 2 ,..., γ مترشناخته شده است که برای تولید من-محصول در راه است و ijواحدها j-ام مواد اولیه سود حاصل از فروش محصولات u 1, و 2،...و صبر این اساس برابر است از 1، از 2،...، از ص.باید برای تولید محصولات به گونه‌ای برنامه‌ریزی شود که سود به حداکثر برسد و در عین حال برنامه تولید هر محصول محقق شود، اما از تقاضا برای آن فراتر نرود.

مدل ریاضی.بیایید نشان دهیم x 1، x 2،...x nتعداد واحد محصولات u 1, و 2 و...و pتولید شده توسط شرکت سود حاصل از طرح (تابع هدف) برابر خواهد بود با:

z = z(x 1 , x 2 ,..., x n) = c 1 x 1 + c 2 x 2 + ...+c n x n → حداکثر محدودیت های اجرای طرح به شکل زیر نوشته می شود: x i ≥β iبرای i = 1,2,...,n برای اینکه از تقاضا تجاوز نکنیم، لازم است تولید محصولات را محدود کنیم: x i ≤β iبرای من= 1،2،...n. و در نهایت، محدودیت های مواد خام به صورت یک سیستم نابرابری نوشته می شود:

α 11 x 1 + α 12 x 2 +...+ α 1n x n ≤b 1

α 21 x 1 + α 22 x 2 +...+ α 2n x n ≤b 2

................................................

α m1 x 1 + α m2 x 2 +...+ α mn x n ≤b m

به شرطی که x 1، x 2،...x nغیر منفی

مثال 2.1:

بیایید یک مثال خاص از مشکل را در نظر بگیریم طرح تولیدو دنباله ای از اقدامات لازم برای حل آن را با استفاده از MS Excel ارائه دهید.

وظیفه.این شرکت دو نوع محصول بتن مسلح تولید می کند: پله ها و دال های بالکن. برای تولید یک پله 3.5 متر مکعب نیاز است. بتن و 1 بسته آرماتور و برای تولید اسلب - 1 متر مکعب. بتن و 2 بسته آرماتور. هر واحد تولید به 1 نفر در روز نیروی کار نیاز دارد. سود حاصل از فروش 1 پله 200 روبل و یک تخته 100 روبل است. این شرکت 150 نفر را استخدام می کند و مشخص است که این شرکت بیش از 350 متر مکعب در روز تولید نمی کند. بتن و بیش از 240 بسته آرماتور وارد نمی شود. تدوین برنامه تولید به گونه ای الزامی است که سود حاصل از محصولات تولید شده حداکثر باشد.

راه حل.

1. در برگه ای از کتاب کار MS Excel، جدول پارامترهای وظیفه را پر کنید (شکل 2.2).

2. یک مدل مشکل ایجاد کنید و سلول ها را برای مقادیر متغیر (در ابتدا سلول ها) پر کنید x (و x zپر از مقادیر عددی دلخواه، به عنوان مثال، مقدار 10)، تابع هدف (سلول حاوی فرمول) و محدودیت ها (سلول ها حاوی فرمول هستند)
(شکل 2.2)

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

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

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

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

5. در نتیجه، در سلول های دارای متغیرهای وظیفه، مقادیر مربوط به طرح بهینه (80 پله و 70 تخته کف در روز) و در سلول برای تابع هدف - ارزش سود (23000 روبل) ظاهر می شود. ) مربوط به این طرح (شکل 2.3)

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

ورزش:

سابق. 2.1.این شرکت تلویزیون، سیستم های استریو و سیستم های بلندگو را با استفاده از یک انبار مشترک قطعات تولید می کند. موجودی شاسی در انبار 450 عدد، لوله تصویر - 250 عدد، بلندگو - 800 عدد، منبع تغذیه - 450 عدد، تابلو - 600 عدد. هر محصول به تعداد اجزای مشخص شده در جدول نیاز دارد:

سود حاصل از تولید یک تلویزیون 90 دلار آمریکا، یک سیستم استریو 50 و یک سیستم صوتی 45 دلار است. .

معرفی

4.1. اطلاعات اولیه

4.2. فرمول های محاسباتی

4.3. پر کردن کادر محاوره ای Find Solution

4.4. نتایج راه حل

نتیجه

منابع

معرفی

برنامه نویسی خطی مسئله بهینه سازی اکسل

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

موضوع درس "حل مسائل برنامه نویسی خطی در MS Excel" با استفاده از مثال "مسئله حمل و نقل" برگرفته از رشته انرژی عمومی، برای کسب مهارت های کاربردی در استفاده از صفحات گسترده مایکروسافت اکسل و حل مسائل بهینه سازی برنامه نویسی خطی است. .

1. داده های اولیه برای حل مشکل

داده های اولیه شامل - نمودار طرح حوضه های زغال سنگ (CB) و نیروگاه های برق (PP)، نشان دهنده اتصالات حمل و نقل بین آنها، جداول حاوی اطلاعات مربوط به بهره وری سالانه و قیمت ویژه سوخت CB، ظرفیت نصب شده، تعداد ساعات استفاده از ظرفیت نصب شده و سوخت مصرفی ویژه در ES، فواصل بین UB و ES و هزینه واحد حمل و نقل سوخت در طول مسیرهای UB-ES.

عکس. 1. اطلاعات اولیه

2. اطلاعات مختصری در مورد صفحات گسترده MS Excel

برنج. 2. نمای پنجره برنامه

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

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

2.اتصالات بین سلول ها و جداول را سازماندهی کنید، در حالی که تغییر داده ها در جداول منبع به طور خودکار نتایج را در جداول حاصل تغییر می دهد.

.جداول محوری ایجاد کنید

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

.انجام یکپارچه سازی داده ها (ترکیب داده ها از چندین جدول در یک).

.از اسکریپت ها استفاده کنید - آرایه های نامگذاری شده از داده های منبع، که از آنها مقادیر کل نهایی در همان جدول تشکیل می شود.

.جستجوی خودکار خطاها در فرمول ها را انجام دهید.

.محافظت از داده ها

.از ساختار داده ها استفاده کنید (بخش هایی از جداول را پنهان و نمایش دهید).

.تکمیل خودکار را اعمال کنید.

.از ماکروها استفاده کنید

.نمودارها را بسازید.

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

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

.تبادل داده با سایر برنامه ها

مفاهیم کلیدی:

.کتاب کار - اسناد اولیه، ذخیره شده در یک فایل.

2.برگه (حجم: 256 ستون، 65536 ردیف).

.سلول کوچکترین واحد ساختاری قرار دادن داده است.

.آدرس سلول - موقعیت سلول را در جدول تعیین می کند.

.فرمول یک نماد ریاضی از محاسبات است.

.پیوند - آدرس سلول را به عنوان بخشی از فرمول ثبت کنید.

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

ورود اطلاعات:

داده ها می توانند از انواع زیر باشند -

· شماره.

· متن

· کارکرد.

· فرمول ها.

می توانید وارد شوید -

· در سلول ها

· به نوار فرمول.

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

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

· مرجع سلول (به عنوان مثال، A6).

· تابع.

· عملگر حسابی (+، -، /، *).

· عملگرهای مقایسه (>،<, <=, =>, =).

می‌توانید فرمول‌ها را مستقیماً در یک سلول وارد کنید، اما با استفاده از نوار فرمول وارد کردن راحت‌تر است.

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

مسیر: درج - تابعیا در نوار فرمول بر روی آن کلیک کنید = . یک کادر محاوره ای ظاهر می شود که ده تابع اخیراً استفاده شده را فهرست می کند. برای گسترش فهرست، را انتخاب کنید عملکردهای دیگر...کادر محاوره ای دیگری باز می شود، که در آن توابع بر اساس نوع (دسته) گروه بندی می شوند، شرحی از هدف تابع و پارامترهای آنها ارائه می شود.

شرح کامل کار با صفحات گسترده MS Excel را می توان در کتاب های درسی و راهنماها (تخصصی) یافت.

3. فرمول بندی ریاضی مسئله

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

داده های اولیه مسئله و متغیرهایی که در حین حل آن تعیین می شوند را می توان در قالب جدول 3 ارائه کرد.

تعیین داده ها:

که در دسامبر 1 ، که در ub2 ، که در ub3 - بهره وری از حوضه های زغال سنگ، هزار تن.

با دسامبر 1 ، با ub2 ، با ub3 - هزینه سوخت در حوضه های زغال سنگ، c.u./ton.

L در - طول مسیر راه آهن بین UB تا ES، کیلومتر؛

با در - هزینه ویژه حمل و نقل سوخت در طول مسیر از UB به ES، c.u./ton*km (C 11= سی 12= سی 13= سی 21= سی 22= سی 23= سی 31= سی 32= سی 33);

که در در - حجم سوخت تحویلی از UB به نیروگاه، هزار تن؛

که در ES1 ، که در ES2 ، که در ES3 - تقاضای سوخت سالانه نیروگاه های اول، دوم و سوم به ترتیب هزار تن.

که در در - پارامترهای متغیرهای تابع هدف هستند که در فرآیند حل مسئله تعیین می شوند.

تعیین حجم بهینه سوخت ضروری است (V در ، از UB به هر یک از ES تحویل داده می شود، که در آن کل هزینه سوخت برای هر سه ES حداقل خواهد بود.

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

4. حل مسئله برنامه ریزی خطی

.1 داده های اولیه

برنج. 4. داده های اولیه

4.2 فرمول های محاسبات

شکل 5. محاسبات میانی

4.3 پر کردن کادر گفتگوی "جستجوی راه حل".

برنج. 6. فرآیند بهینه سازی.

شکل 6.1 تنظیم محدودیت ها (سوخت باید> 0 باشد).

شکل 6.2 تنظیم محدودیت ها (تعداد واردات = مقدار سوخت مصرفی).

شکل 6.3 تنظیم محدودیت ها (محموله سالانه، از تولید UB1 تجاوز نکنید).

شکل 6.4 محدودیت های تنظیم (محموله سالانه، از تولید UB2 تجاوز نکنید).

شکل 6.5 تنظیم محدودیت ها (محموله سالانه، از تولید UB3 تجاوز نکنید).

.4 نتایج راه حل

شکل 8. نتایج حل مشکل

پاسخ: مقدار سوخت (هزار تن) تحویل شده به:

ES4 از UB1 118.17 تن است.

ES6 از UB1 545.66 تن است.

ES5 از UB2 19.66 تن است.

ES6 از UB2 180.34 تن است.

ES5 از UB3 277.94 تن است.

ES6 از UB3 526.00 تن است.

ES4 مجموع 118.17 تن;

ES5 مجموع 297.60 تن;

ES6 مجموع 1252.00 تن;

هزینه سوخت معادل (cu):

برای ES4 - 496314.00.

برای ES5 - 227064.75.

برای ES6 - 23099064.78.

کل هزینه ها برای همه ES 23822443.53 USD است.

نتیجه

اطلاعات مختصری در مورد صفحات گسترده MS Excel. حل مسئله برنامه ریزی خطی راه حل با استفاده از ابزارهای مایکروسافت اکسل برای یک مسئله بهینه سازی اقتصادی، با استفاده از مثال "مشکل حمل و نقل". ویژگی های طراحی اسناد MS Word.

کار دوره نحوه ایجاد و کار با طراحی یک سند MS Word را نشان می دهد که در آن راه حل یک مسئله بهینه سازی اقتصادی در نظر گرفته شده است، با استفاده از مثال "مسئله حمل و نقل" برگرفته از حوزه انرژی عمومی، با استفاده از Microsoft Excel. .