Практична робота № 4.

ТЕМА: Прийняття оптимальних рішень. Розв'язання задач лінійного програмування за допомогою надбудови ТП Excel "Пошук рішення"

МЕТА: Набути навички розв’язування задач лінійного програмування з використанням надбудови «Поиск решения».

ТРИВАЛІСТЬ: 6 години.

ЗАБЕЗПЕЧЕННЯ ЗАНЯТТЯ: ПК, пакет Microsoft Office.

ЗАВДАННЯ ДЛЯ РОБОТИ

Завдання 1. Згідно варіанту побудувати математичну модель економічної задачі. Розв'язати задачу за допомогою надбудови «Поиск решения».

Завдання 2. Згідно варіанту знайти мінімум і максимум цільової функції.

Завдання 3. Оформити звіт з практичної роботи.

Варіанти для завдання 1.

№ студента в журналі

Задача

Підприємство випускає продукцію трьох видів П1, П2 і П3, використовуючи три види сировини С1, С2 і С3, запаси яких обмежені. Витрати сировини кожного виду на виробництво одиниці продукції П1, П2 і П3, прибуток підприємства від продажу одиниці готовій продукції кожного виду приведені в таблиці:

Визначити план випуску для здобуття максимального прибутку.

Фірма займається пошиттям п'яти моделей взуття. Для виготовлення взуття використовується 3 матеріали, запаси яких обмежені. Витрати матеріалу і його запаси, мінімальний тижневий попит моделей і їх відпускна ціна приведені в таблиці:

Визначити план випуску взуття для здобуття максимального доходу.

Цех випускає три види виробів, причому добова програма випуску складає: I виріб - 90 одиниць, II – 70 і III – 60. Добові виробничі можливості цеху і норми витрат виробничих ресурсів на одиницю різних видів виробів приведені в таблиці:

Скласти план виробництва продукції, що забезпечує максимальний дохід від реалізації виробів.

Процес виготовлення двох видів промислових виробів полягає в послідовній обробці кожного з них на трьох верстатах. Час використання цих верстатів для виробництва обмежений 10 годинами на добу. Час обробки і прибуток від продажу одного виробу кожного виду приведені в таблиці. Знайти оптимальний обсяг виробництва виробів кожного виду.

Для виробництва трьох продуктів потрібно два матеріали. Закупівельна ціна і витрата матеріалів приведені в таблиці

Знайти план, при якому загальна собівартість буде мінімальною (при виконанні плану продажів).

Підприємство випускає радіоприймачі трьох різних моделей М1, М2 і М3. Кожна модель характеризується певним часом на виготовлення відповідних деталей, часом збірки виробу і його упаковки:

Визначити план випуску радіоприймачів з метою здобуття максимального прибутку.

Цех для виробництва двох видів продукції використовує чотири групи устаткування:

Знайти варіант завантаження устаткування, що забезпечує максимальний прибуток.

Фірма виготовляє два види продукції А і В. Дані поро витрати сировини, ціну та мінімальний об'єму збуту приведені в таблиці:

Визначити план випуску продукції А і В, що забезпечує максимальний прибуток.

Видавничий будинок видає два журнали: «Садівник» і «Молодь», які друкуються в трьох друкарнях. Загальна кількість годин, відведена для друку і продуктивність друку однієї тисячі екземплярів обмежені і представлені в наступній таблиці:

Визначити оптимальну кількість видаваних журналів, яка забезпечить максимальну виручку від продажу.

Ательє шиє 3 види виробів (плаття, блуза, спідниця). На пошиття використовується 3 види матеріалу, витрати і запаси яких приведені в таблиці:

Потрібно визначити оптимальний план пошиття виробів, що забезпечує максимальний прибуток.

Підприємство електронної промисловості випускає 2 моделі радіоприймачів, причому кожна модель виробляється на окремій технологічній лінії. Добовий обсяг виробництва лінії, витрати електронних схем 2-х типів і прибуток від реалізації приведені в таблиці:

Визначити добовий обсяг виробництва першої і другої моделей, аби прибуток був максимальним.

Цех може виробляти стільці і столи.

Скільки треба зробити стільців і столів, аби отримати максимальний прибуток?

Фірма займається комп'ютерними послугами. Витрати часу 1 людини на кожну послугу, вартість послуги і мінімальний денний попит приведені в таблиці. Робочий день людини складає 8 годин. Знайти денний план роботи, що максимізує дохід.

Компанія спеціалізується на випуску хокейних ключок і наборів шахів. Кожна ключка приносить компанії прибуток у розмірі $2, а кожен шаховий набір - у розмірі $4. На виготовлення однієї ключки потрібно чотири години роботи на ділянці A і дві години роботи на ділянці B. Шаховий набір виготовляється з витратами шести годин на ділянці A, шести годин на ділянці B і однієї години на ділянці C. Доступна виробнича потужність ділянки A складає 120 год, ділянки В - 72 год і ділянка С - 10 годин.

Скільки ключок і шахових наборів повинна випускати компанія щодня, аби отримувати максимальний прибуток?

Виконати замовлення на виробництво 32 виробів В1 і 4 виробів В2 узялися бригади Б1 і Б2. Продуктивність бригади Б1 на виробництво виробів В1 і В2 становить відповідно 4 і 2 вироби за годину, фонд робочого часу цієї бригади 9,5 год. Продуктивність бригади Б2 – відповідно 1 і 3 вироби за годину, а її фонд робочого часу – 4 год. Витрати, пов'язані з виробництвом одиниці виробу, для бригади Б1 рівні відповідно 9 і 20 гр.од., для бригади Б2 – 15 і 30 гр.од.

Складіть математичну модель задачі, яка дозволяє знайти оптимальний обсяг випуску виробів, що забезпечує мінімальні витрати на виконання замовлення.

В інституті проводиться конкурс на кращу стінгазету. Одному студентові дано наступне доручення:

1) купити акварельні фарби за ціною 30 гр.од. за коробку, кольорові олівці за ціною 20 гр.од. за коробку, лінійки за ціною 12 гр.од., блокноти за ціною 10 гр.од.;

2) фарб потрібно купити не менше трьох коробок, блокнотів – стільки, скільки коробок олівців і фарб разом, лінійок не більше п'яти. На покупки виділяється не менше 300 гр.од.

У якій кількості студент повинен купити зазначені предмети, щоб загальне число предметів було найбільшим?

При виготовленні виробів В1 і В2 використовуються сталь і кольорові метали, а також токарські й фрезерні верстати. За технологічними нормами на виробництво одиниці виробу В1 потрібно 300 і 200 станко-годин відповідно токарського й фрезерного устаткування, а також 10 і 20 кг відповідно сталі й кольорових металів. Для виробництва одиниці виробу В2 потрібно 400, 100, 70 і 50 відповідних одиниць тих же ресурсів.

Цех має в своєму розпорядженні 12400 і 6800 станко-годин відповідно токарського й фрезерного устаткування та 640 і 840 кг відповідно сталі й кольорових металів. Прибуток від реалізації одиниці виробу В1 становить 6 гр.од. і від одиниці виробу В2 – 16 гр.од.

Побудувати математичну модель задачі, використовуючи як показник ефективності прибуток і враховуючи те, що час роботи фрезерних верстатів повинен використовуватися повністю.

Господарство займається такими зерновими культурами, як пшениця, овес і ячмінь. Загальна площа ріллі становить 34 га. Наявність ресурсів та їх витрати на виробництво наведені в таблиці.

Виходячи з заданого обсягу виробничих ресурсів знайти максимальний вихід продукції в грошовому еквіваленті.

Виробничі ресурси	Пшениця	Овес	Ячмінь	Загальний об’єм ресурсів
Витрати праці на 1 га, люд-год	5	4	3	25
Добрива на 1 га, кг	2	3	5	35
Вихід продукції з 1га, грн	20000	1400	15000

Для процесу гальванізації готується суміш, якій потрібні два види порошку. Загальна маса двох видів не повинна перевищувати 6 кг, норми витрат початкових заготовок для виробництва 1 кг кожного порошку становить відповідно 2 та 11 кг. Витрати заготовок повинні бути не менше як 22 кг.

Знайти склад суміші з максимальною її вартістю, якщо 1 кг першого порошку коштує 2 грн, а другого – 1 грн.

Два хімічні препарати виготовляються однією установою одночасно. Кількість першого хімічного препарату за технологією виробництва завжди не перевищує кількість другого, причому вихід придатного продукту першого хімічного препарату за добу повинен бути не більше як 200 кг.

На виробництво 1 т кожного препарату витрачається відповідно 4 та 6 кг нафтопродукту, запас якого на добу не перевищує 30 кг.

Знайти виробництво хімічних препаратів з їх максимальною вартістю, якщо 1 кг першого хімічного продукту коштує 2 грн, а другого – 1 грн.

На виготовлення 1 т декораційних плит першого типу витрачається 6 куб. м деревини, а другого – 7 куб. м. Витрати деревини за добу не повинні перевищувати 42 куб. м. При цьому слід мати на увазі, що різниця між кількостями виробництва першого та другого типів не повинна перевищувати 2 т, а найбільш доцільний режим технології виробництва плит – не більше як 4 т плит за добу.

Знайти оптимальний план виробництва декоративних плит, якщо вартість 1 т плит першого типу – 3 тис. грн, а другого – 1 тис. грн.

Три цехи разом виготовляють не більше 500 т будівельного матеріалу за добу. Фонд часу не перевищує 3200 годин, а витрати праці не повинні перевищувати 8000 чол.-днів. Питомі витрати по кожному цеху на виробництво 1 т матеріалу наведені у таблиці:

Ресурси	Цех
Ресурси	1	2	3
Час, год	3	2	4
Витрати праці, чол.-днів	11	15	20

Вихід валової продукції по цехах дорівнює відповідно 400, 300 та 200 грн за 1 т. За добу другий цех повинен виробляти не більше як 200 т матеріалу.

Знайти план виробництва будівельного матеріалу кожним цехом з максимальним виходом валової продукції з точки зору загальної вартості.

У студентській їдальні для виготовлення бутербродів трьох видів використовуються чотири види продуктів, загальні обсяги яких і норми витрат зазначені у таблиці. Відомий також прибуток, одержаний їдальнею від реалізації однієї партії бутербродів кожного виду.

Вид продукту	Норми витрат продуктів (кг) на одну партію бутербродів виду			Наявність продуктів (кг)
Вид продукту	Б1	Б2	Б3	Наявність продуктів (кг)
С1	4	3	1	42
С2	2	5	4	56
С3	3	6	2	38
С4	5	7	3	40
Прибуток, (грн)	5	7	8

Запланувати випуск бутербродів у таких кількостях, щоб загальний прибуток їдальні був максимальним.

Скласти оптимальний план (мінімум капітальних затрат) забудови мікрорайону міста житловими будинками трьох різних типів. Наявність квартир у кожному з типових будинків відображає таблиця.

Планова кількість мешканців у квартирі	Кількість квартир за типом будинку
Планова кількість мешканців у квартирі	Першим	Другим	Третім
2	50	50	60
3	30	100	50
4	120	60	40
Вартість	804 тис. грн	822 тис. грн	602 тис. грн

Демографічний склад майбутнього населення мікрорайону зумовлює необхідність того, щоб було не менше ніж 750 квартир на 2-х мешканців; 1700 квартир на 3-х; 450 квартир на 4-х.

У процесі виробництва двох рецептів використовується три типи лікарських речовин, витрати яких на 1 г кінцевої продукції наведені у таблиці.

Речовина, г	Рецепт
Речовина, г	1	2
1-го типу	5	1
2-го типу	2	5
3-го типу	1,5	4

У виробництві рецептів повинно використовуватись не менше як 20 г першої, як 10 г другої та не більше як 32 г третьої речовин відповідно. Вартість виробництва 10 г продукції за першим рецептом – 1,8 грн, за другим – 1грн.

Варіанти для завдання 2.

№ студента в журналі	Задача	№ студента в журналі	Задача
1	Z=9х₁+10х₂+16х₃ 18х₁+15х₂+12х₃≤ 360 6х₁+4х₂+8х₃≤ 192 -10х₁+3х₂+3х₃≤ 30 х₁≥ 0, х₂≥ 0, х₃≥ 0	14	Z=14х₁+12х₂+14х₃ 14х1+18х2-19х3≤ 150 21х1-25х2+24х3≤ 240 -48х1+30х2+56х3≤ 180 х1≥ 0, х2≥ 0, х3≥ 0
2	Z=7х₁+12х₂+14 х₃ 28х1+25х2+22х3≤ 560 4х1-40х2+6х3≤ 100 -10х1+30х2+5х3≤ 50 х1≥ 0, х2≥ 0, х3≥ 0	15	Z=15х₁+12х₂+14х₃ 15х1+18х2-31х3≤ 150 21х1-25х2+28х3≤ 24 -48х1+30х2+56х3≤ 18 х1≥ 0, х2≥ 0, х3≥ 0
3	Z=2х₁+3х₂+4х₃ -5х1+6х2+7х3≤ 20 8х1-9х2+10х3≤ 30 11х1+12х2-13х3≤ 40 х1≥ 0, х2≥ 0, х3≥ 0	16	Z=16х₁+22х₂+11х₃ 18х1+2х2-20х3≤ 204 16х1-2х2+77х3≤ 31 -48х1+10х2+11х3≤ 204 х1≥ 0, х2≥ 0, х3≥ 0
4	Z=3х₁+4х₂+2х₃ 15х1-16х2+17х3≤ 120 -18х1+19х2+20х3≤ 130 21х1+22х2-23х3≤ 140 х1≥ 0, х2≥ 0, х3≥ 0	17
5	Z=3х₁+4х₂+2х₃ 15х1+16х2-17х3≤ 120 18х1-19х2+20х3≤ 130 -21х1+22х2+23х3≤ 140 х1≥ 0, х2≥ 0, х3≥ 0	18
6	Z=7х₁+12х₂+14х₃ 48х1+25х2+22х3≤ 500 4х1-20х2+6х3≤ 20 -10х1+10х2+5х3≤ 50 х1≥ 0, х2≥ 0, х3≥ 0	19
7	Z=8х₁+11х₂+15х₃ 50х1+26х2-20х3≤ 30 4х1-20х2+6х3≤ 20 -10х1+10х2+5х3≤ 50 х1≥ 0, х2≥ 0, х3≥ 0	20
8	Z=10х₁+20х₂+30х₃ -30х1+40х2+50х3≤ 70 10х1-20х2+20х3≤ 30 20х1+30х2-40х3≤ 50 х1≥ 0, х2≥ 0, х3≥ 0	21
9	Z=15х₁+25х₂+35х₃ 30х₁+40х₂-50х₃≤ 70 10х₁-20х₂+20х₃≤ 30 -20х₁+30х₂+40х₃≤ 50 х₁≥0, х₂≥0, х₃≥0	22
10	Z=10х₁+5х₂+45х₃ 30х1+40х2-50х3≤ 70 10х1-20х2+20х3≤ 30 -20х1+30х2+40х3≤ 50 х1≥ 0, х2≥ 0, х3≥ 0	23
11	Z=5х₁+5х₂+5х₃ 20х1+20х2-20х3≤ 120 30х1-30х2+30х3≤ 80 -40х1+40х2+40х3≤ 90 х1≥ 0, х₂≥ 0, х₃≥ 0	24
12	Z=12х₁+12х₂+12х₃ 12х1+13х2-14х3≤ 12 24х1-25х2+24х3≤ 24 -48х1+48х2+49х3≤ 48 х1≥ 0, х2≥ 0, х3≥ 0	25
13	Z=13х₁+12х₂+14х₃ 13х1+18х2-19х3≤ 120 24х1-25х2+24х3≤ 240 -48х1+30х2+49х3≤ 480 х1≥ 0, х2≥ 0, х3≥ 0

Приклад розв'язання задачі.

Модель задачі має вигляд:

Розв’язання

У стовпчику A будемо записувати пояснення для введених даних. Комірки B1, C1 зарезервуємо для значень невідомих змінних. Під ними у рядку 2 запишемо, які саме змінні знаходяться там, та обмеження їх на знак, якщо воно є. У комірках B3 і С3 запишемо коефіцієнти цільової функції при відповідних змінних, у комірках B4:B6, C4:C6 – коефіцієнти при відповідних змінних у обмеженнях, у комірках F4:F6 – праві частини обмежень. Вираз для цільової функції запишемо у D3, він має бути таким:

=СУММПРОИЗВ($B$1:$C$1;B3:C3)

Скопіювавши цю формулу у три нижні комірки, отримаємо вирази для лівої частини функціональних обмежень. У комірках E4:E6 запишемо тип відповідного обмеження, а у комірках D2:F2 – пояснення до нижніх комірок.

Зайти у меню Сервис → Поиск решения. На екрані з’явиться таке вікно, яке необхідно розмістити так, щоб воно не закривало введену інформацію на робочому листі Excel.

У полі «Установить целевую ячейку:» необхідно вибрати комірку, у якій записано вираз для цільової функції. У нас це комірка D3. Далі пропонується обрати один з трьох взаємовиключних варіантів – типів оптимізації (максимальному значению, минимальному значению, значению). За замовчуванням тип оптимізації – максимізація, як і в нашій задачі, тому переходимо відразу до наступного поля.

У полі «Изменяя ячейки:» вказуються комірки, які зарезервовано під невідомі змінні (х1 та х2), а саме B1, С1. Адреси комірок вводяться через знак «;».

Далі необхідно вказувати типи обмеження. Всі вони вводяться за допомогою кнопки «Добавить». Після натиснення на кнопку з’явиться ще одне віконце, яке має три поля. У полі ліворуч вказується комірка, у якій знаходиться вираз для лівої частини функціонального обмеження. У центральному полі обирається тип обмеження (<=, =, >=) та тип невідомих змінних. За замовчуванням тип змінних дійсний, як і у нашій задачі, тому виконувати якісь дії щодо обрання типу змінних для даної задачі не потрібно. У полі праворуч вказується комірка, у якій знаходиться права частина функціонального обмеження.

При уведенні обмежень на знак у полі ліворуч робиться посилання на відповідну змінну, а у полі праворуч вводиться число «0», при цьому обирається тип нерівності «>=» або «<=». Після введення всіх обмежень необхідно натиснути на кнопку «ОК».

Крок 5. Отримання розв’язку задачі і аналіз результатів

Після того як всі дії виконано, натиснути кнопку «Выполнить». На екрані вікно «Поиск решения» зміниться вікном «Результаты поиска решения».

При появі цього вікна порожні до цього комірки B1 і С1 отримали свої значення, які і є розв’язком задачі. Про те, що вони є оптимальним допустимим розв’язком свідчить надпис у вікні «Решение найдено. Все ограничения и условия оптимальности выполнены». Функції даного вікна дозволяють на окремих робочих листах Excel отримати детальний звіт щодо розв’язку задачі, обравши для цього необхідні звіти у списку «Тип отчета». Про подробиці можна дізнатися у спеціальній літературі.

Використовуючи надбудову «Поиск решения», ми отримали:

1) розв’язок задачі лінійного програмування х1 = 2, х2 = 6;

2) значення ЦФ в оптимальній точці Z = 36.

Контрольні питання

1. Які основні етапи розв’язування задач лінійного програмування в MS Excel?

2. В чому сенс використовування символу $ у формулах MS Excel?

3. Чому при введенні формул в комірки ЦФ і лівих частин обмежень в них відображаються нульові значення?

4. Яким чином в MS Excel задається напрям оптимізації ЦФ?

5. Які комірки екранної форми виконують ілюстративну функцію, а які необхідні для розв’язування задачі?

6. Поясніть загальний порядок роботи з вікном "Поиск решения".

7. Яким чином можна змінювати, додавати, видаляти обмеження у вікні "Поиск решения"?