 Дата Клас
ПЛАН УРОКУ
Тема: Використання засобу «Пошук рішення»
Мета: навчити учнів розв’язувати задачі щодо оптимізації із застосуванням засобу Поиск решений
Тип уроку: комбінований
Методичне забезпечення: мультимедійний проектор Epson, мультимедійний планшетInterwrite PRS, ПК з програмою Excel.
СТРУКТУРА УРОКУ
1. Організаційний момент
перевірка присутності учнів
підготовка до уроку
2. Актуалізація опорних знань (10 хвилин)
Що таке формула?
З чого складається формула в Excel?
Які типи посилань ви знаєте?
Як змінити тип посилання?
Які є типи операторів?
Які оператори належать до адресних?
Які оператори належать до текстових?
Що таке функція?
Як вставити функцію в формулу?
Що називають аргументом функції?
Що може бути аргументом функції?
Які категорії функцій Ви знаєте?
Які функції належать до логічних?
3. Пояснення нового матеріалу
Пошук рішення (Solver) є одним із найбільш ефективних механізмів Excel для вирішення систем рівнянь і оптимізаційних задач. Як правило, такі задачі можуть бути зведені до наступного:необхідно знайти значення аргументів цільової функції, де вона має максимум або мінімум; при цьому задаються обмеження-нерівності на значення самої цільової функції або інших функцій, аргументів.
Файл\Параметри\Настройки\Настройки Ексель\Перейти\Поиск решений.
Процедура пошуку рішень дозволяє знайти оптимальне значення формули, яка міститься у комірці, що називається цільовою. Ця процедура працює з групою комірок, що зв'язані з формулою у цільовій комірці. Щоб отримати за формулою, яка міститься в цільовій комірці, певний результат, процедура змінює значення у комірках, що впливають на цей результат. Щоб звузити множину значень, які використовуються у моделі, застосовують обмеження. Вони можуть посилатися на інші комірки, що також впливають на результат.
Для розв'язування завдань оптимізації слід:
записати у комірки електронної таблиці математичну модель завдання;
Завантажити програмне забезпечення Поиск решения через команди Доние\Анализ\Поиск решения і у відповідному діалоговому вікні:
Установить целевую ячейку - встановити адреси комірки, де міститься формула цільової функції, значення якої треба максимізувати,
мінімізуватати або встановити визначене число;
До - встановити варіант оптимізації (максимізація, мінімізація
або підбір певної величини);
Изменяя ячейки - вказати адресу блоку комірок, значення яких змінюються під час пошуку рішення доти, поки не будуть виконані на
кладені обмеження і умова оптимізації значення комірки, вказана в
полі Установить целевую ячейку;
Ограничения - сформувати список обмежень, які накладаються на поставлене завдання;
Добавить - натиснути кнопку для відображення діалогового вікна
Добавить ограничение;
Изменить - натиснути кнопку для зміни обмежень;
Удалить - натиснути кнопку для вилучення обмежень;
Найти решение - натиснути кнопку для розв'язування поставленого завдання;
Закрыть - натиснути кнопку для виходу з вікна діалогу без завантаження програми, при цьому зберігаються установки у вікнах діалогу;
Параметри - натиснути кнопку для відображення відповідного діалогового вікна, в якому можна завантажити або зберегти створену
математичну модель і вказати варіанти пошуку рішень;
Восстановить - натиснути кнопку для очищення полів діалогів і відновлення параметрів пошуку рішення.
Для встановлення адрес комірок у рядках введення діалогового вікна можна користуватися кнопкою  , яка знаходиться в кінці кожного поля введення. Після натиснення відповідної кнопки діалогове вікно набуває вигляду заголовка вікна з полем введення:
Що надає користувачеві можливість введення адрес комірок у самій електронній таблиці - клацаючи мишею у відповідних комірках або виділяючи блоки комірок при натиснутій лівій кнопці миші. Для повернення діалогового вікна на натиснути клавішу <�Еnter>.
Розглянемо приклад розв'язування задачі щодо оптимізації із застосуванням засобу Поиск решений. Кондитерська фабрика для виготовлення карамелі трьох найменувань: «Ромашка», «Вишня», «Ягідка» - використовує цукровий пісок, патоку, фруктове пюре. Норми витрат сировини кожного виду на виробництво однієї тонни карамелі кожного найменування, загальна кількість сировини кожного типу, що може використовуватись фабрикою, а також прибуток від реалізації однієї тонни карамелі кожного найменування наведені у таблиці.
Види сировини
|
Норми витрат сировини на 1 т карамелі, т
|
Загальна кількість сировини, т
|
«Ромашка»
|
«Вишня»
|
«Ягідка»
|
Цукор
|
0,8
|
0,5
|
0,6
|
800
|
Патока
|
0,2
|
0,4
|
0,3
|
600
|
Фруктове пюре
|
0
|
0,1
|
0,1
|
120
|
Прибуток від реалізації 1 т продукції, грн
|
108
|
112
|
126
|
|
Слід визначити план виробництва карамелі, який забезпечить найбільший прибуток від її реалізації.
Порядок розв'язування задачі:
1. Записати математичну модель завдання, де:
х1 - план випуску карамелі «Ромашка»;
х2 - план випуску карамелі «Вишня»;
х3 - план випуску карамелі «Ягідка».
Цільова функція: у = 108х1 + 112х2 + 126х3 → max за умов
2. У комірки електронної таблиці записати математичну модель задачі
таким чином: (демонстрація)
- до комірки А7 ввести формулу цільової функції, використовуючи адреси комірок з початковими значеннями змінних В1:ВЗ:
= 108*ВЗ + 112*В4 + 126*В5
- до комірки А9 ввести формулу першого обмеження:
= 0,8*ВЗ + 0,5*В4 + 0,6*В5
- до комірки А10 ввести формулу другого обмеження:
= 0,2*ВЗ + 0,4*В4 + 0,3*В5
- до комірки А11 ввести формулу третього обмеження:
= 0,1*В4+0,1*В5
3. Виконати команди Дание\Анализ\Поиск решения;
4. Вказати необхідні параметри для розв'язування у діалоговому вікні Поиск решения:
у полі Установить целевую ячейку вказати адресу комірки, яка містить формулу цільової функції - А7;
встановити перемикач Равной на максимальному значенні;
у полі Изменяя ячейки вказати адресу блоку змінних - ВЗ:В5;
у полі Ограничения клацнути на кнопці Добавить, після цього на екрані з'явиться діалогове вікно додавання обмежень, у якому:
слід вказати у полі Ссылка на ячейку — адресу блоку комірок, де
знаходяться формули обмежень - А9:А11;
вибрати знак обмеження зі списку знаків <=;
у полі Ограничение - адресу блоку комірок із значеннями обмежень В9-.В11;
натиснути кнопку <�ОК> і повернутися до діалогового вікна Поиск решения;
клацнути на кнопці Параметри;
прапорці Сделать переменные без ограничений отрицательными;
Виберіт метод ішення : Поиск решений линейных задач симплекс методом;
натиснути кнопку Найти решение і у діалоговому вікні, що з'явиться, вибрати Сохранить найденные значення;
результати розв'язування завдання знаходяться у комірках ВЗ:В5.
Оптимальний план випуску продукції, який забезпечить максимальний прибуток: карамелі «Му-му» - 100 тонн, карамелі «Вишня» - випускати не рекомендується, карамелі «Ягідка» - 1200 тонн.
4. Закріплення вивченого матеріалу
1. Для чого призначений засіб Поиск решений?
2. Який перший крок?
3. Якщо в пункті меню Дание \ Анализ немає засобу Поиск решений, то які дії слід виконати?
4. Як задати обмеження?
5. Які додаткові параметри пошуку рішень можна задати?
6. Що означає встановити цільову комірку?
5. Підсумок уроку
6. Домашнє завдання
А.Ю.Гаєвський ст.275-282, ЕНМК
|