Лабораторна робота №12.
Використання електронних таблиць MS Excel для аналізу даних. Добір параметра. Пошук рішення.
Мета роботи: навчитись розв’язуватиалгебраїчні рівняння за допомогою інструменту Подбор параметра, навчитись розв’язувати оптимізаційні задачі за допомогою інструменту Поиск решения.
Теоретичний матеріал:повторити типи діаграм та створення графіків функцій, використання інструменту Подбор параметра, використання інструменту Подбор параметра.
ЗАВДАННЯ ДО РОБОТИ:
1. Розв’язування алгебраїчних рівнянь.
1.1. Розв’язати рівняння 5х3-20sinх+3ex=0із точністюε=0,0001.
1.1.1. Визначимо відрізки локалізації коренів, для цього будуємо таблицю значень функції y=f(x) та її графік. Інтервал, на якому будуємо графік має містити значення коренів, тобто точки перетину з віссю Ох.
Як видно з графіка, рівняння має 3 корені: 
, 
та 
. Щоб знайти точні значення коренів слід 3 рази застосувати Підбор параметра.
1.1.2. Встановлюємо потрібну точність командою Сервис ® Параметры ® Вычисления ® Относительная погрешность.
1.1.3. Для того щоб розв’язати рівняння, в комірки А15:А17 занесемо початкові наближені значення коренів, а в комірки С15:С17 – формули f(x):
| А | В | С | |
| Значення х | f(x) | ||
| x1 | -2 | =5*B15^3-20*SIN(B15)+3*EXP(B15) | |
| x2 | =5*B16^3-20*SIN(B16)+3*EXP(B16) | ||
| x3 | 1,1 | =5*B17^3-20*SIN(B17)+3*EXP(B17) |
або у іншому вигляді:
| Значення х | f(x) | |
| x1 | -2 | -21,41 |
| x2 | 3,00 | |
| x3 | -3,67 |
1.1.4.Підключаємо Сервис ® Подбор параметра.
Після натискування клавіші ОК на екрані в комірці В15 одержуємо результат для х1, а потім аналогічно у В16 і В17 для х2 та х3:
| Значення х | f(x) | |
| x1 | -1,60295277 | -3,1427E-05 |
| x2 | 0,18258097 | 1,60022E-06 |
| x3 | 1,20166034 | 2,63498E-05 |
1.1.5. Для одержання результату у зручному вигляді установлюємо в комірках числові формати, що відповідають умові задачі, тобто необхідній точності обчислень.
Остаточно:
| Значення х | f(x) | |
| x1 | -1,6030 | 0,0000 |
| x2 | 0,1826 | 0,0000 |
| x3 | 1,2017 | 0,0000 |
2.Розв’язати рівняння f(x) =0 із точністю ε=0,001.
| Варіант | Функція f(x) |
| 1. | 
|
| 2. | 
|
| 3. | 
|
| 4. | 
|
| 5. | 
|
| 6. | 
|
| 7. | 
|
| 8. |  
|
| 9. | 
|
| 10. | 
|
3.Розв’язування задач на оптимізацію.
3.1.У ресторані готують страви трьох видів (страва А, страва В і страва С) з використанням при виготовленні інгредієнтів трьох видів (інгредієнт 1, інгредієнт 2, інгредієнт 3). Витрати інгредієнтів в грамах на страву задається у наступній таблиці:
| Вид інгредієнту | Страва А | Страва В | Страва С |
| Інгредієнт 1 | |||
| Інгредієнт 2 | |||
| Інгредієнт 3 |
Вартість приготування страв однакова (100 грн.).
Кожного дня в ресторан надходить 5 кг інгредієнта 1 і по 4 кг інгредієнтів виду 2 і 3. Яке оптимальне відношення денного виробництва страв різного виду, якщо виробничі потужності ресторану дозволяють використовувати увесь запас продуктів, що надійшли?
Розв’язання .
Для вирішення задачі введемо помітку: нехай х1 – щоденний випуск блюда А1; х2 –щоденний випуск блюда В;х3 –щоденний випуск блюда С.
складемо цільову функцію – вона заключаеться в стоимости випущенних рестораном блюд:
Z =100*x1 + 100*x2 +100*x3
Определим имеющиеся ограничения (руководствуясь таблицей):
1) 20*x1 + 50*x2 + 10*x3 
5000;
2) 20*x1 + 0*x2 + 40*x3 4000;
3) 20*x1 + 10*x2 + 10*x3 4000;
Крім того, оскільки не можна реализувати частину блюда и кількість блюд не може бути отрицательным, добавимо ще ряд ограничений:
1) х1 
0;
2) х2 0;
3) х3 0;
4) х1 - ціле;
5) х2 - ціле;
6) х3 – ціле.
Далі,
1. Відкрити новий робочий лист.
2. В комірки А2, А3 і А4 занесемо денний запас продуктів – числа 5000, 4000, 4000 відповідно.
3. В комірки С1, Е1, D1 занесемо початкові значення невідомих – х1, х2, х3 - нулі.
4. В комірках діапазону С2:Е4 розмістимо таблицю витрат інгредієнтів.
5. В комірках В2:В4 вкажемо формули для розрахунку витрат інгредієнтів по видам. В комірке В2 формула буде мати вигляд =$C$1*C2+$D$1*D2+$E$1*E2.
6. 
В комірку F1 занесемо формулу цільової функції =100*(C1+D1+C1):
7. Задамо команду Сервис ->Поиск решения відкриється діалогове вікно Поиск решения.
8. У полі Установить целевую ячейку вкажемо комірку, яка містить оптимизируемое значення (F1). Встановимо перемикач Равной в положенні максимальному значенні.
9. У полі Изменяя ячейки задамо діапазон підібраних параметрів – С1:Е1. (рис 2)
10. Щоб визначити набір обмежень, клацнемо на кнопці Добавить. В діалоговому вікні Добавление ограничения у полі Ссылка на ячейку вкажемо діапазон В2:В4. В якості умови задамо <=. У полі Ограничение вкажемо діапазон А2:А4. Ця умова вказує, що денні витрати інгредієнтів не повинен перевищувати запасів. Клацнемо на кнопці ОК. (рис 3).
 |
11. 
Знову клацнемо на кнопці Добавить. У полі Ссылка на ячейку вкажемо діапазон С1:Е1. В якості умови задамо >=. У полі Ограничение задамо число 0. Ця умова вказує, що число виготовлених страв невід’ємне. Клацнемо на кнопці ОК.
12. Знову клацнемо на кнопці Добавить. У полі Ссылка на ячейку вкажемо діапазон С1:Е1. В якості умови виберемо цел. Ця умова не дозволяє виробляти страву частинами. Клацнемо на кнопці ОК.
 |
13. Клацнемо на кнопці Выполнить. По закінченню оптимізації відкриється діалогове вікно Результаты поиска решения.
 |
14. Встановимо перемикач Значения параметров у положенні Сохранить найденное решения, після чого клацнемо на кнопці ОК.
 |
У результаті отримаємо оптимальний набір змінних при даних обмеженнях: страва А – 184 порції, страва В – 24 порції, страва С – 8 порцій. При цьому загальна вартість страв Z буде максимальною і дорівнює 21 600 грн. При цьому залишиться 40 г першого інградієнту.
Контрольні запитання:
1. От каких опций зависит точность вычислений при использовании инструмента Подбор параметра.
2. В каком диалоговом окне можно установить эти опции.