Тема: засоби оптимізації.

Мета
навчальна:

• ознайомити учнів зі способами оптимізації при розв’язуванні задач, застосовуючи засіб Пошук рішень;

• сформувати в учнів навички опрацювання табличних даних за допомогою засобів оптимізації.

• розвивати знання учнів в області інформаційних технологій, вміння аналізувати інформацію та використовувати її для розв’язання поставленої задачі;

• розвивати логічне мислення, увагу, пам'ять.

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

Обладнання: персональні комп'ютери з встановленими ОС і LibreOffice, (дана) інструкція, інструкції з техніки безпеки в комп’ютерному класі.

Структура уроку

1. Організаційний момент.
2. Актуалізація опорних знань.
3. Вивчення нового матеріалу.
4. Інструктаж з ТБ.
5. Закріплення вивченого матеріалу.
6. Підбиття підсумків уроку.
7. Домашнє завдання.

Хід уроку

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

2. Актуалізація опорних знань

1. Що таке формула?
2. З чого складається формула?
3. Які правила введення формул до електронних таблиць?
4. Які типи посилань ви знаєте?
5. Як змінити тип посилання?
6. Які є типи операторів?
7. Що таке функція?
8. Як вставити функцію в формулу?
9. Що називають аргументом функції?
10. Що може бути аргументом функції?
11. Які категорії функцій Ви знаєте?
12. Які функції належать до логічних?

3. Вивчення нового матеріалу

Мотивація вивчення теми
Сучасні програми опрацювання електронних таблиць поряд зі звичайними арифметичними діями надають можливість використовувати спеціальні функції. Зокрема, реалізовано багато статистичних методів. Цікавою видається можливість змінювати окремі параметри в обчисленнях, що залежать від багатьох факторів, і спостерігати, як це впливає на результат. Це так звані розрахунки «що було б, якщо». Наприклад, при розрахунку кредиту шляхом простої зміни періоду, процентної ставки або сум виплати можна відразу побачити, як змінюються інші фактори. Більше того, використовуючи спеціальні засоби опрацювання електронних таблиць, можна знаходити значення параметрів, при яких величина цільової функції (зиск, втрати) досягає екстремального значення. Таким чином пересічний користувач може успішно досліджувати економічні моделі, навіть не уявляючи складності математичного апарату, що лежить в основі такого дослідження (симплекс-метод, опукле програмування тощо).

Пошук рішення (Solver) є одним із найефективніших інструментів LibreOfficeCalc для вирішення оптимізаційних задач. Зазвичай такі задачі формулюють так: знайти величину аргументів певної (цільової) функції, при яких вона досягає максимум (мінімум), при одночасному справдженні (заданих) обмежень-нерівностей щодо величини самої цільової функції або інших функцій чи власне аргументів.

Подамо неповний перелік таких задач.

• Асортимент продукції: максимізувати випуск товарів або зиск при обмеженнях на сировину (чи інші ресурси).

• Штатний розклад: скласти штатний розклад для досягнення найкращих результатів при найменших витратах на утримання персоналу.

• Планування перевезення: мінімізувати витрати на транспортування.

• Складання суміші: отримати суміш заданої якості при найменших витратах.

• Оптимізація фінансових показників: максимізувати зиск за рахунок оптимізації витрат на інвестиційні проекти.

Засіб Пошук рішення є однією з надбудов LibreOfficeCalc. Якщо відвести ряд комірок під значення аргументів, цільової функції і функцій-обмежень на робочому аркуші, то для запуску процесу необхідно використати вказівку меню Засоби / Solver для виклику вікна діалогу Пошук рішення.

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

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

Тип взаємозв’язку між рішенням і цільовою функцією задають перемикачем у групі Оптимізувати результат до. Для знаходження максимального або мінімального значення цільової функції цей перемикач ставлять у положення відповідно Маximum або Мinimum. Для знаходження аргументів, при яких цільова функція набуває певної величини, перемикач Оптимізувати результат до ставлять у положення Значення, а праворуч вводять величину функції.

Змінювані комірки — поле, у якому вказують посилання на комірки, відведені під значення аргументи.

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

Посилання на комірку — набір полів для запису лівих частин обмежень (наприклад, $А$2:$В$2).

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

Оператор — набір полів зі списками для вибору типу відношення між частинами співвідношення:

• не перевищує (<=);
• не менше (>=);
• дорівнює (=);
• обмеження на цілочисельність лівої частини (ціле);
• обмеження на значення лівої частини, що вибирається з множини {0, 1} (двійкове). У правій частині при цьому відображено слово двійкове.

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

Для закриття вікна діалогу необхідно натиснути на кнопку Гаразд.

Закрити — кнопка у вікні діалогу Пошук рішення для припинення діалогу без розв'язання задачі.

Пошук рішення — кнопка у вікні діалогу Пошук рішення для запуску процесу розв'язання задачі. Після розв'язання задачі на робочому аркуші з’являться нові величини у комірках для цільової функції і параметрів-аргументів.

Розрізняють лінійні та нелінійні моделі оптимізаційних задач.

Лінійні моделі — це моделі, в яких залежність цільової функції та обмежень є лінійною щодо змінних. Всі інші моделі називають нелінійними.

Приклад умови задачі оптимізації
Підприємство виробляє 3 модифікації приладів:

• А, який дає на одиницю 100 грн. прибутку;
• Б, який дає 90 грн прибутку;
• В, який дає 105 грн прибутку.

Для збирання приладів використовуються 4 типи блоків. Склад приладів подано такою таблицею.

1. Записуємо вхідні дані:
   • в клітини G3:I6 — матрицю складу приладів;
   • в клітини F11:H11 — прибутки по модифікаціях приладів;
   • в клітини B3:B6 — обмеження ресурсів.

2. Клітини C3:C6, у яких після розв'язання задачі буде визначено витрачені ресурси, заповнюємо нулями.

3. Клітини A11:C11, у яких після розв'язання задачі буде визначено кількості приладів, заповнюємо нулями.

   

4. У клітину С3 записуємо формулу визначення ресурсів «Тип 1»:
   =$A$11*G3+$B$11*H3+$C$11*I3.
   Копіюємо цю формулу на клітини С4:С6 протягуванням.

5. У клітині D11 записуємо формулу цільової функції:
   =$A$11*F11+$B$11*G11+$C$11*H11.

6. Використавши вказівку меню Засоби → Solver, заповнюємо поля вікна діалогу Пошук рішення

   • у поле Цільова комірка розташуємо посилання на комірку із цільовою функцією — $D$11;

   • у полі Оптимізувати результат до перемикач ставимо у положення Маximum;

   • у полі Умови обмеження надаємо обмеження, що накладаються на аргументи — =$A$11:$C$11;

   • у поле Посилання на комірку вводимо ліву частину обмеження, у поле Значення — праву частину нерівності, а у поле Оператор вводимо тип відношення між лівою і правою частинами:
     $A$11:$C$11>=0;
     $C$3:$C$6<=$B$3:$B$6;
     $A$11:$C$11=Ціле.

   

7. Натискаємо кнопку Пошук рішення. У вікні діалогу Результат пошуку рішення натискаємо кнопку Зберегти результат.

   

   Результат отримаємо у клітинах А11:С11, а ресурси, які знадобляться для його одержання - у клітинах С3:С6.

4. Інструктаж з ТБ

5. Закріплення вивченого матеріалу

Завдання 1. Припустимо, що ми вирішили виробляти три види цукерок. Назвемо їх умовно «А», «В», «С». Відомо, що реалізація 10 кг цукерок «А» дає прибуток 9 у.о., «В» — 10 у.о., «С» — 16 у.о. Цукерки можна виробляти в будь-яких кількостях (збут забезпечено), але запаси сировини обмежено. Потрібно визначити, у яких кількостях маємо виробляти цукерки, щоб загальний прибуток від реалізації був максимальним. Норми витрат сировини на виробництві 10 кг цукерок кожного виду поданого такою таблицею.

1. У середовищі LibreOfficeCalc заповніть таблицю відповідно до поданого зразка.
   


2. Введіть формули обчислення прибутку:
   • в комірку С3 введіть =9*В3;
   • в комірку С4 введіть =10*В4;
   • в комірку С5 введіть =16*В5.
3. В комірку С6 введіть формулу знаходження суми всіх прибутків =SUM(C3:C5).
4. Введіть формули підрахунку необхідної кількості сировини:
   • в комірку А10 введіть =18*В3+15*В4+12*В5;
   • в комірку В10 введіть =6*В3+4*В4+8*В5;
   • в комірку С10 введіть =5*В3+3*В4+3*В5;
5. Використайте вказівку меню Засоби → Solver.
6. У вікні діалогу Пошук рішення виконайте такі дії:
   • встановіть цільову комірку $C$6 на досягнення найбільшої величини;
   • вкажіть змінювані комірки $B$3:$B$5;
   • опишіть обмеження:
     $A$10<=360;
     $B$10<=192;
     $C$10<=180;
     $B$3:$B$5>=0.
     
   
   
   
7. Натисніть кнопку Пошук рішення.

8. Переконалися, що оптимальний план випуску передбачає виготовлення 8 кг цукерок «В» і 20 кг цукерок «С», а цукерки «А» виготовляти не варто взагалі. Отриманий прибуток складе 400 у.о.

   
   
9. Результати збережіть у файлі з назвою 1-Ваше прізвище у теці, вказаній вчителем.

• на 100 буклетів витрачають 40 стандартних аркушів межованого паперу, 0,05 кг поліграфічної фарби та 0,5 людино-днів трудовитрат;

• на 100 інформаційних плакатів витрачають 80 листів межованого паперу, 0,08 кг поліграфічної фарби й 0,6 людино-днів трудовитрат.

Прибуток від продажу 100 буклетів становить 60 у.о., а від продажу 100 інформаційних плакатів — 80 у.о. На підприємстві працюють 15 робітників. Удень не можна витратити більше 1500 аркушів межованого паперу та не більше 2 кг фарби. Яку кількість рекламних буклетів та інформаційних плакатів має виготовляти підприємство, щоб прибуток від поліграфічної діяльності був максимальним?
Розв'язання. Побудуємо математичну модель задачі. Позначимо через х₁ та х₂ кількості сотень буклетів та плакатів, що випускатиме підприємство. При таких позначеннях цільова функція — вираз 60х₁ + 80х₂. Обмеження на ресури (папір, фарба, праця) такі:

• папір: 40х₁ + 80х₂ ≤ 1500;
• фарба: 0,05х₁ + 0,08х₂ ≤ 2;
• трудові ресурси: 0,5х₁ + 0,6х₂ ≤ 15.

Усі записані вирази мають лінійний вигляд, тому задача належить до задач лінійного програмування.

1. У середовищі LibreOfficeCalc заповніть таблицю відповідно до поданого зразка.
   

2. Введіть формули обчислення витрат ресурсів при виготовлені х₁ сотень буклетів і х₂ сотень плакатів:

   • у клітинку E3 (папір) введiть =C3*$B$9+D3*$C$9;
   • у клітинку E4 (фарба) введiть =C4*$B$9+D4*$C$9;
   • у клітинку E4 (трудові ресурси) введiть =C5*$B$9+D5*$C$9.

   Знаки $ використано для того, щоб можна було ввести формулу у клітинку Е3 і скопіювати її у діапазон Е4:Е5. Тоді клітинки, позначені знаком $ не зміняться, а інші зміняться у напрямку переміщення. Поки не знайдено оптимальний виробничий план, у цих клітинках мають бути нулі.

3. Використайте вказівку меню Засоби / Solver.

4. У вікні діалогу Пошук рішення виконайте такі дії:

   • встановіть цільову комірку В12 на досягнення найбільшої величини;

   • вкажіть змінювані комірки В9:С9;

   • у полі Посилання на комірку введіть В9:С9, виберіть знак обмеження ≥, а у поле Значення введіть 0. Таким чином встановлено невід'ємність розв'язків;

   • у (наступному) полі Посилання на комірку введіть Е3, виберіть знак обмеження ≤, а у поле Значення введіть В3 — посилання на клітину, що містить обмеження на ресурс паперу. Аналогічно задайте два інші обмеження: на фарбу та трудові ресурси.

   

5. Клацніть на кнопку Пошук рішення. Оптимізаційна програма LibreOfficeCalc виконає пошук рішення, після чого з’явиться вікно Результат пошуку рішення.

   
6. Натисніть кнопку Зберегти результати
7. Відповідь прочитайте у клітинках В9:С9. У клітинках Е3:Е5 показано кількості ресурсів, що необхідних для втілення отриманого оптимального плану.
   
   
   
8. Результати збережіть у файлі з назвою 2-Ваше прізвище у теці, вказаній вчителем.

6. Підбиття підсумків уроку
Контрольні запитання:

1. Що таке процес пошуку рішення?
2. В якому випадку застосовують надбудову Пошук рішення?
3. Як здійснити запуск Пошуку рішень?
4. Що таке цільова комірка?
5. Для чого потрібно використовувати обмеження?
6. Як задати цільову комірку, обмеження та змінювані комірки?
7. Як зберегти результати пошуку?

Текст упорядкувала Наталія Сергіївна Сальницька, вчитель спеціалізованої школи № 196 Святошинського району міста Києва, під час виконання випускної роботи на курсах підвищення кваліфікації з 15.10.2012 по 21.12.2012.