Побудова та розв’язок ЗЛП у середовищі MS EXCEL

Побудова та розв’язок ЗЛП у середовищі MS EXCEL”

[pic 1]

ля того, щоб розв’язати задачу ЛП(лінійного програмування) в табличному редакторі Microsoft Excel, необхідно виконати такі дії.

1. Ввести умову задачі:

а) створити  форму для введення умови задачі:

• змінних;
• цільової функції (ЦФ);
• обмежень;
• граничних умов;

б) ввести початкові дані в  форму:

• коефіцієнти ЦФ;
• коефіцієнти при змінних в обмеженнях;
• праві частини обмежень;

в) ввести залежність з математичної моделі в  форму:

• – формулу для розрахунку ЦФ;
• – формули для розрахунку значень лівих частин обмежень;

г) задати ЦФ (у вікні “Поиск решения”):

• – цільова комірка;
• – напрям оптимізації ЦФ;

д) ввести обмеження і граничні умови (у вікні “Поиск решения”):

• – комірки з значеннями змінних;
• – граничні умови для допустимих значень змінних;
• – співвідношення між правими і лівими частинами обмежень.

2. Розв’язати задачу:

а) встановити параметри розв’язання задачі (у вікні “Поиск решения”);

б) запустити задачу на розв’язання (у вікні “Поиск решения”);

в) вибрати формат висновку розв’язання (у вікні “Результаты поиска решения”).

Приклад розв'язку задачі симлексним методом в середовищі Microsoft Excel

Розглянемо приклад знаходження розв’язку для такої одноіндексної задачі ЛП:

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

Екранна форма для введення умов задачі (1) разом з введеними в неї початковими даними подана на рис.3.1

В екранній формі на рис.3.1 кожній змінній і кожному коефіцієнту задачі поставлена відповідно конкретна комірка в Excel. Ім'я комірки складається з букви, що позначає стовпець, і цифри, що позначає рядок, на перетині яких знаходиться об'єкт задачі ЛП. Так, наприклад, змінним задачі (1) відповідають комірки B3 ((х1), C3 ((х2), D3 ((х3), E3 ((х4), коефіцієнтам ЦФ відповідають комірки B6 ((с1 = 130,5), C6 ((с1 = 20), D6 ((с1 = 56), E6 ((с4 = 87,8), правим частинам обмежень відповідають комірки H10 ((b1 = 756), H11 ((b2 = 450), H12 ((b3 = 89) і т.д.

[pic 3]

Рис. 3.1 Форма для введення даних задачі

В комірку F6, в якій буде відображатися значення ЦФ, необхідно ввести формулу, за якою це значення буде розраховано. Згідно з (1) значення ЦФ визначається виразом

Використовуючи позначення відповідних комірок в Excel (див. рис.2.15.), формулу для розрахунку ЦФ (2) можна записати як суму значень кожної з комірок, відведених для значень змінних задачі (B3, C3, D3, E3), на відповідну комірку, відведену для коефіцієнтів ЦФ (B6, C6, D6, E6), тобто

Щоб задати формулу (3) необхідно в комірку F6 ввести такий вираз і натискувати клавішу “Enter”

де символ “$” перед номером рядка 3 означає, що при копіюванні цієї формули в інші місця листа Excel номер рядка 3 не зміниться;

символ : означає, що у формулі будуть використані всі комірки, розташовані між комірками, вказаними зліва і справа від двокрапки (наприклад, запис B6:E6 указує на комірки B6, C6, D6 і E6). Після цього в цільовій комірці з'явиться 0 (нульове значення) (рис.3.2).

[pic 7]

Рис.3.2  Екранна форма задачі

Примітка. Існує інший спосіб задання функцій в Excel за допомогою режиму “Вставка функций”, який можна викликати з меню “Вставка” або при натисненні кнопки “[pic 8]”на стандартній панелі інструментів. Так, наприклад, формулу (4) можна задати таким чином.

1. Курсор в полі F6.

2. Натисніть кнопку “[pic 9]”,викличте вікно “Мастер функций

3. Виберіть у вікні “Категория” категорію “Математические”.

4. У вікні “Функция”виберітьфункцію СУММПРОИЗВ або SUMPRODUCT.

5. У вікні “СУММПРОИЗВ” (SUMPRODUCT), що з'явилося, в рядок “Массив 1” введіть вираз B$3:E$3, а в рядок “Массив 2” – вираз B6:E6 (рис.2.17).