       План-конспект
Тема: Використання засобу "Пошук рішення".
Мета: Ознайомити учнів із способом оптимізації при розв’язанні задач, застосовуючи засоб “Пошук рішень”
Тип уроку: засвоєння нових знань
Методичне забезпечення: інтерактивна дошка, ПК з програмою Excel, роздатковий матеріал.
СТРУКТУРА УРОКУ
1. Організаційний момент
Перевірка присутності учнів підготовка до уроку
2. Актуалізація опорних знань
Що таке формула?
З чого складається формула в Excel?
Які типи посилань ви знаєте?
Як змінити тип посилання?
Які є типи операторів?
Які оператори належать до адресних?
Які оператори належать до текстових?
Що таке функція?
Як вставити функцію в формулу?
Що називають аргументом функції?
Що може бути аргументом функції?
Які категорії функцій Ви знаєте?
Які функції належать до логічних?
3. Пояснення нового матеріалу
Пошук рішення (Solver) є одним із найбільш ефективних механізмів Excel для вирішення систем рівнянь і оптимізаційних задач. Як правило, такі задачі можуть бути зведені до наступного:необхідно знайти значення аргументів цільової функції, де вона має максимум або мінімум; при цьому задаються обмеження-нерівності на значення самої цільової функції або інших функцій, аргументів.
Засіб Пошук рішення є однією з надбудов Excel. Якщо в меню Сервіс відсутня команда Пошук рішення, то для її установки необхідно виконати команду Сервіс/Надбудови/Пошук рішення. Якщо відвести ряд комірок під значення аргументів, цільової функції і функцій-обмежень на робочому листі, то для запуску процесу необхідно вибрати команду Сервіс/Пошук рішення і заповнити діалогове вікно, що відчинилося, Пошук рішення
(рис. 3.6).
Рис. 3.6. Діалогове вікно Пошук рішення
У поле Установити цільову діалогового вікна Пошук рішення розташовується посилання на комірку із цільовою функцією, максимум чи мінімум якої знаходиться або коли необхідно знайти задане її значення. Тип взаємозв’язку між рішенням і цільовою функцією задається шляхом установки перемикача в групі Рівної. Для знаходження максимального або мінімального значення цільової функції цей перемикач ставиться в положення Максимальне значення або Мінімальне значення, відповідно. Для знаходження значення цільової функції, заданій у полі групи Рівної, перемикач ставиться в положення Значенню. У полі Змінюючи комірки вказуються комірки, що повинні
змінюватися в процесі вирішення задачі, тобто комірки, які відведені під аргументи. Обмеження, що накладаються на аргументи, відображаються в полі Обмеження. Засіб Пошук рішення припускає існування обмежень у вигляді рівнянь, нерівностей, а також дозволяє увести вимогу того, щоб аргументи були цілими. Обмеження додаються по одному. Для введення обмежень необхідно вибрати опцію Додати в діалоговому вікні Пошук рішення (рис. 3.6) і в діалоговому вікні, що відчинилося, Додати обмеження (рис. 3.7) заповнити поля.
Рис. 3.7. Діалогове вікно Додавання обмеження
У поле Посилання на комірку необхідно ввести ліву частину обмеження (наприклад, $D$18), а в поле Обмеження – праву частину нерівності. За допомогою списку, що розкривається (<=), вводиться тип відношення між лівою і правою частинами нерівності
обмеження.
Обмеження бувають: менше і дорівнює (<=); більше і дорівнює (>=); дорівнює (=); обмеження на цілочисельність лівої частини (цілий); обмеження на значення лівої частини, що вибирається з ряду {0 або 1} (двійк.): у правій частині при цьому відображається слово двійкове. Для додавання введеного у відповідні полюси обмеження і продовження роботи з цим вікном необхідно натиснути кнопку Додати, для введення обмеження і переходу на робочий лист – кнопку ОК. Таким чином, можна ввести цілий ряд різноманітних обмежень. Необхідно звернути увагу на те, що обмеження зручніше задавати у вигляді діапазонів.
Для коректування параметрів пошуку рішення необхідно натиснути кнопку Параметри в діалоговому вікні Пошук рішення (рис. 3.6). У діалоговому вікні, що відчинилося, Параметри пошуку рішення (рис. 3.8) можна змінювати умови і варіанти пошуку рішення задачі, а також завантажувати і зберігати моделі, що оптимізуються. Значення і стан елементів управління, які використовуються по замовчанню, підходять для рішення більшості задач.
Рис. 3.8. Діалогове вікно Параметри пошуку рішення
Для запуску процесу необхідно натиснути на кнопку Виконати. Після виконання задачі на робочому листі з’являються нові значення комірок цільової функції і параметрів-аргументів. При цьому виводиться діалогове вікно Результати пошуку рішення (рис. 3.9), що визначать необхідний тип звіту: Результати, Стійкість, Межі.
Рис. 3.9. Діалогове вікно Результати пошуку рішення
У Excel для розв'язання задачі оптимізації передбачений інструмент ПОИСК РЕШЕНИЯ, розташований в меню СЕРВИС. Якщо там його немає, слід включити командою СЕРВИС >НАДСТРОЙКИ > прапорець ПОИСК РЕШЕНИЯ.
Приклад розв'язання задачі оптимізації. Підприємство виробляє 3 модифікації приладів: А, який дає на одиницю 100 грн прибутку; Б, який дає 90 грн прибутку і В –
105 грн прибутку. Для збирання приладів використовуються 4 типи блоків. Склад приладів поданий у таблиці нижче.
В наявності на складі 300 блоків типу 1, 500 – типу 2 і по 400 – типу 3 та 4.
Використовуючи табличний процесор Excel, визначити оптимальні кількості приладів,
які забезпечать найбільший прибуток.
Розв'язання. 1) Записуємо вхідні дані: в клітини G3:I6 - матрицю складу приладів, в клітини F11:H11- прибутки по модифікаціях приладів, а в клітини B3:B6 -
обмеження ресурсів (рис. ).
2) Клітини C3:C6, в яких в результаті розв'язання задачі буде визначено витрачені
ресурси, заповнюємо нулями.
3) Клітини A11:C11, у яких в результаті розв'язання задачі будуть визначені кількості
приладів, заповнюємо нулями.
Рис. 6. До прикладу розв'язання задачі оптимізації. Заповнення полів вікна ПОИСК РЕШЕНИЯ
4) В клітину С3 записуємо формулу визначення ресурсів «Тип 1»:
=$A$11*G3+$B$11*H3+$C$11*I3. Розмножуємо цю формулу на клітини С4:С6 методом протягування.
Рис. 7. До прикладу розв'язання задачі оптимізації
5) У клітині D11 розміщуємо формулу цільової функції: =$A$11*F11+$B$11*G11+$C$11*H11.
6) Виконуємо команду СЕРВИС > ПОИСК РЕШЕНИЯ. Заповнюємо поля вікна ПОИСК РЕШЕНИЯ так, як показано на рис. 6.
7) Натискаємо кнопку ВЫПОЛНИТЬ у цьому вікні. Одержуємо розв'язок (рис. 7). З'явиться вікно РЕЗУЛЬТАТЫ ПОИСКА РЕШЕНИЯ, в якому встановлюємо опцію «Сохранить найденное решение» і натискаємо кнопку ОК. Результат одержимо в клітинах А11:С11, а ресурси, які знадобляться для його одержання - в клітинах С3:С6 (рис. 7 ).
4. Закріплення вивченого матеріалу
1. Для чого призначений засіб Поиск решений?
2. Який перший крок?
3. Якщо в пункті меню Сервис немає засобу Поиск решений, то які дії слід виконати?
4. Як задати обмеження?
5. Які додаткові параметри пошуку рішень можна задати?
6. Що означає встановити цільову комірку?
5. Підсумок уроку
6. Домашнє завдання
|