Презентація на тему:
Microsoft Excel у профільному навчанні

Розділ 8 “Розв'язання оптимізаційних задач” Спецкурс «Microsoft Excel у профільному навчанні»

План вивчення теми У цьому розділі буде розглянуто: математичну модель оптимізаційної задачі; інструмент Поиск решения (Пошук розв'язку) та його параметри; обмеження на параметри пошуку розв'язку; звіти про результати пошуку розв'язку.

Математична модель оптимізаційної задачі У багатьох задачах вимагається не просто знайти який-небудь розв'язок, а підібрати серед усіх розв'язків найкращий (оптимальний). Йдеться про такі задачі: підбір збалансованого раціону харчування, оптимізація асортименту продукції, оптимізація транспортних перевезень та багато інших — їх ще називають оптимізаційними.

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

Математична модель оптимізаційної задачі Найпростішою оптимізаційною задачею вважається задача пошуку максимального або мінімального значення функції однієї змінної. Наведемо приклад математичної моделі такої задачі: f(x) = х + sinх min; 0 x 10. Тут змінною є х, цільовою функцією — f(x), критерієм — вимога мінімізації ( min), а обмеженнями — 0 x 10. Добре дослідженим та важливим для планування виробничих процесів різновидом оптимізаційних задач є задачі лінійного програмування (ЗЛП), тобто задачі, в яких цільова функція та обмеження є лінійними. У загальному випадку математична модель ЗЛП має такий вигляд: c1x1+…cnxn min (max); (1) (2) , j=1,…,n. (3) Тут x1, ..., xn — змінні; aij;, bi та ci — деякі числа; (1) — цільова функція разом із критерієм; (2) і (3) — обмеження. Зазначимо, що обмеження (3) називаються прямими, а обмеження (2) — непрямими. У непрямих обмеженнях замість знаків «=» або «= ». Крім того, можуть накладатися додаткові обмеження, наприклад, може вимагатися, щоб змінні були цілочисельними.

Алгоритм розв'язання оптимізаційної задачі

Розв'язання оптимізаційних задач за допомогою Excel У табличному процесорі Microsoft Excel передбачено спеціальний інструмент для розв'язання оптимізаційних задач — Поиск решения (Пошук розв'язку). За замовчуванням в Excel надбудова Пошук розв'язку відключена. Щоб активізувати її в Excel 2007, слід клацнути значок Кнопка Microsoft Office, клацнути Параметри Excel, а потім вибрати категорію Надбудови. У полі Керування вибрати значення Надбудови Excel і натисніть кнопку Перейти. У полі Доступні надбудови встановіть прапорець поруч із пунктом Пошук розв'язку і натисніть кнопку ОК. На стрічці Дані появиться група аналіз з командою Пошук розв'язку. Цей засіб шукає розв'язок оптимізаційної задачі за ітеративним алгоритмом, багаторазово змінюючи значення змінних на малу величину і таким чином наближуючи цільову функцію до оптимального значення. Після виконання команди Дані, Пошук розв'язку відкриється однойменне вікно:

Основні елементи діалогового вікна Пошук розв'язку. У полі Установить целеву ячейку (Встановити цільову клітинку) вказують адресу цільової клітинки (ця клітинка повинна містити формулу цільової функції). За допомогою перемикача Равной (Рівній) вказують, що потрібно зробити з цільовою функцією: максимізувати, мінімізувати або отримати задане значення. У полі Изменяя ячейки (Змінюючи клітинки) вказують адреси клітинок, де містяться аргументи цільової функції. Кнопка Предположить (Припустити) використовується для автоматичного пошуку клітинок, що впливають на цільову функцію. Область Ограничения (Обмеження) призначена для відображення списку граничних умов поставленої задачі. Кнопка Добавить (Добавити) призначена для створення обмежень.

Основні елементи діалогового вікна Пошук розв'язку. Кнопку Изменить (Змінити) використовують для редагування наявних обмежень. Кнопка Удалить (Видалити) призначена для скасування виділеного обмеження. Кнопку Параметры (Параметри) використовують для завантаження або збереження оптимізаційної моделі, визначення граничного часу роботи засобу та настроювання інших параметрів. Кнопка Восстановить (Відновити) призначена для очищення полів вікна Поиск решения (Пошук розв'язку) і відновлення значень параметрів пошуку розв'язку, використовуваних за умовчанням. За допомогою кнопки Выполнить (Виконати) запускають процес пошуку розв'язку. Кнопка Закрыть (Закрити) призначена для виходу з вікна без пошуку розв'язку (усі настройки зберігаються).

Робота з вікном Пошук розв'язку У вікні Поиск решения (Пошук розв'язку) потрібно ввести дані про змінні, цільову функцію та обмеження і клацнути кнопку Выполнить (Виконати). Проте перш ніж скористатися інструментом Поиск решения (Пошук розв'язку), математичну модель необхідно подати у вигляді електронної таблиці. Дії, які потрібно виконати. Визначити, у яких клітинках зберігатимуться значення змінних. Ввести формулу цільової функції у цільову клітинку (у формулі використовуватимуться адреси клітинок змінних). Ввести обмеження. Обмеження, як правило, мають вигляд формула ≥ ( , =) число. Сам знак «≥», « » або «=» вводити не потрібно, адже це буде зроблено у вікні Поиск решения (Пошук розв'язку). Достатньо лише ввести у відповідну клітинку формулу обмеження, а в іншу клітинку — число. Зазначимо, що прямі обмеження в ЗЛП не потрібно вводити в електронну таблицю; їх можна задати безпосередньо у вікні Поиск решения (Пошук розв'язку).

Робота з вікном Пошук розв'язку Коли інструмент Поиск решения (Пошук розв'язку) завершить роботу, буде відображено вікно Результаты поиска решения (Результати пошуку розв'язку). Якщо просто клацнути кнопку ОК, на аркуші моделі буде відображено оптимальне значення цільової функції та значення змінних, за яких воно досягається. Якщо перед тим вибрати перемикач Восстановить исходные значения (Відновити вихідні значення), то усі значення залишаться такими ж, як і до пошуку розв'язку, Якщо вибрати елемент Результаты (Результати) у списку Тип отчета (Тип звіту), то буде створено аркуш з детальним аналізом розв'язку задачі.

Вправа 8-1 Вправа 8.1

Вправа 8-2

Звіт за результатами розв'язування задачі Вправа 8.2