Тема 18. Електронні таблиці MS Excel:

Підбір параметра. Робота зі сценаріями.

Підбір параметра

Засіб Підбір параметра (Подбор параметра) являє по суті засіб для знаходження кореня рівняння . Для того, щоби знайти корінь рівняння, потрібно задатись деяким початковим значенням . Позначатимемо початкове значення через . Виходячи з цього початкового значення, Excel намагається підібрати таке значення , щоби задовольнити рівняння . Позначимо його через . Якщо початкове значення задано невдало, то корінь не буде не знайдений, незважаючи на те, що він існує. Рекомендації, в загальному випадку, щодо задання початкового значення , дати неможливо, оскільки його вибір залежить від вигляду функції , яка знаходиться в лівій частині рівняння. Єдине, що можна сказати[1], що початкове значення слід вибирати як можна ближче до значення шуканого кореня. Якщо рівняння має декілька коренів (), то щоби знайти їх усі, потрібно декілька разів застосовувати засіб Підбір параметра, вибираючи початкові значення () близькими до коренів, тобто , ... ,. Для знаходження таких значень можна протабулювати функцію і побудувати її графік. За графіком можна наближено визначити корені, а отже і початкові наближення. Можна обійтись без побудови графіка, а використати лише результати табулювання, але використання графіка вносить певну наочність в розв’язок задачі. Щоби протабулювати функцію, необхідно задати проміжок табулювання і крок. Проміжок табулювання повинний містити всі корені (або принаймні ті, які нас цікавлять), а крок повинний бути меншим ніж найменша відстань між коренями. Визначення проміжку табулювання і кроку потребує певних математичних досліджень функції , чим ми займатись не будемо, а відішлемо читача до відповідних математичних курсів. В загальному випадку, при розв’язанні реальних практичних задач, проміжок і крок можна знайти, виходячи з змісту (економічного, фізичного) задачі.

 Розглянемо на прикладі використання засобу Підбір параметра для знаходження коренів рівняння.

Приклад 1

 Знайти корені рівняння .

1) Спочатку табулюємо функцію . За проміжок табулювання виберемо [0; 4], а за крок 0,5.

Міркування, щодо вибору проміжку табулювання такі: оскільки, згідно теореми Вієта, добуток коренів дорівнює вільному члену, а вільний член є додатній (=2,1), то обидва корені є одного знаку. Сума коренів дорівнює коефіцієнту при з протилежним знаком, (тобто =3,5), тому обидва корені додатні. Оскільки їх сума дорівнює 3,5, то кожний з них не може бути більше за суму. Отже, на проміжку [0; 4] знаходяться всі два корені.

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

Для табулювання функції формуємо робочий лист. Для цього, в A1 заносимо нижню границю проміжку табулювання – 0; в A2 заносимо суму нижня_границя+крок, тобто 0+0,5=0,5. Виділяємо комірки A1 і A2. Використовуючи маркер заповнення, заповнюємо комірки A3:A9. Заносимо в комірку B1 формулу для обчислення  при  що дорівнює значенню, яке знаходиться в комірці A1, тобто формулу =A1*A1-3,5*A1+2.1. Копіюємо цю формулу в комірки B2:B9. Копіювання можна здійснити перетягуванням маркера заповнення. Сформований робочий лист у режимі відображення формул приведено нижче:

 В режимі відображення результатів, ми побачимо

 Будуємо графік функції , використовуючи результати табулювання. Нагадаємо, що для цього потрібно виділити комірки A1:B9, після чого клацнути мишею на кнопці Добавить диаграмму  . Як тип діаграми, вказуємо – Точечная.

З побудованого графіка видно, що корені рівняння знаходяться на проміжках [0;1] і [2;3].

 Запускаємо засіб Підбір параметра для знаходження першого кореня. Для цього вибираємо з головного меню Сервис Þ Подбор параметра. На екран виводиться діалогове вікно:

 В полі Установить в ячейке вказуємо комірку B1. Для цього достатньо клацнути на ній мишею (якщо комірка не видима – закривається діалоговим вікном, то можна скористатись кнопкою згортання вікна ). В полі Значение записуємо 0. В полі Изменяя значение ячейки вказуємо комірку A1. Саме це значення буде використано засобом Підбір параметра як початкове значення при пошуку першого кореня. Заповнене діалогове вікно має вигляд:

 Після натискання на кнопку ОК, на екран виводиться діалогове вікно з результатом підбору параметра:

При цьому, в робочому листі в комірці A1 встановлюється підібране значення (=0,77), тобто наближене значення 1-го кореня, а в комірці B1 – значення функції (лівої частини рівняння) при . Ця величина називається нев’язкою. Отже, нев’язка дорівнює 1,5×10^-5.

 Якщо клацнути на кнопці ОК, то ці зміни в робочому листі зафіксуються, тобто в комірках A1 і B1 залишаться знайдені значення. Якщо клацнути на кнопці Отмена, то в цих комірках відновляться значення, що там були до застосування пошуку кореня. Аналогічно здійснюємо пошук другого кореня. За початкове значення візьмемо значення в комірці A7, тобто 3. Підбиратимемо його так, щоби в комірці B7 встановилось значення 0. Діалогове вікно, з введеними для підбору даними показано нижче: Після натискання кнопки ОК, на екран виводиться діалогове вікно

а в комірках A7, B7 на робочому листі виводяться наближене значення другого кореня (2,73) і нев’язка (7,16×10^-5) відповідно: Робота зі сценаріями

 Ми розглянули з засоби автоматизації обчислень такі як таблиці даних (чи по іншому, таблиці підстановок) і підбір параметра. Ще одним з таких засобів є сценарії.

Сценарій - це іменований набір значень, що використовуються для обчислень в робочому листі. Одночасно можна задати значення 32 змінним (коміркам). Для створення сценаріїв використовується так званий диспетчер (менеджер) сценаріїв.

Розглянемо роботу із сценаріями на наступному прикладі.

Приклад 2

 Створимо робочий лист, що дозволяє розрахувати висоту підйому та дальність польоту тіла, кинутого під кутом  до горизонту з швидкістю  (Мал.1).

Почнемо з того, що спочатку сформулюємо математичну модель явища, тобто запишемо співвідношення, що пов’язують вхідні дані (кут, під яким кинуто тіло, та початкова швидкість тіла) і вихідні дані (висота та дальність польоту) у вигляді рівнянь.

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

,

де - вертикальна складова початкової швидкості. Тут  - початкова швидкість,  - кут, під яким кинуто тіло, - прискорення вільного падіння, яке дорівнює .

Час підйому тіла дорівнює часу падіння, якщо нехтувати опором повітря, тому час польоту тіла . Тоді відстань s, яку пролетить тіло, становить

, де - горизонтальна складова початкової швидкості.

 Висота підйому тіла:

 Відмітимо ще, що на практиці кут, в більшості випадків, вимірюють в градусах, а аргументи стандартних тригонометричних функцій Microsoft Excel повинні бути задані в радіанах, тому перед обчисленням і  слід перетворити радіани в градуси. Зв’язок між величиною кута в градусах  і величиною кута в радіанах  дається формулою:

.

Використовуючи приведені співвідношення, сформуємо робочий лист. Заносимо в комірки дані і розрахункові формули, задавши значення початкової швидкості - , значення кута, під яким кидається тіло - 33º, і прискорення вільного падіння - (ліворуч вказані імена комірок, а праворуч – дані, які в них заносяться):

A1 Рух тіла, кинутого під кутом до горизонту

A3 Початкові дані

A4 Початкова швидкість v0=

A5 Кут в градусах alpha=

A6 Прискорення вільного падіння g=

A7 Проміжні обчислення

A8 Кут в радіанах rad=

A9 Горизонтальна складова швидкості vg=

A10 Вертикальна складова швидкості vv=

A11 Час підйому тіла t=

A12 Час польоту тіла tpol=

A13 Результати

A14 Висота підйому тіла H=

A15 Дальність польоту тіла s=

B4 12

B5 33

B6 9,8

 В8 =В5*ПИ( ) / 180 або = РАДИАНЫ(В5)[2]

 В9 =В4*COS(B8)

 B10 =В4*SIN(B8)

 B11 =B10 / B6

 B12 =2*B11

 B14 =B10^2/(2*B6)

 B15 =B9*B12

В результаті одержимо наступний робочий лист (Мал.2):

 Оцінимо тепер, за допомогою створеного робочого листа, дальність і висоту польоту кам’яного ядра балісти (метальної зброї стародавніх греків та римлян), коли відомо, що кут, під яким викидалось ядро, становив 40º, а початкова швидкість залежала від маси ядра:

 - для ядра масою 10 кг вона складала ;

 - для ядра масою 20 кг вона складала .

 Для обчислень досить підставити вхідні дані (по черзі) в робочий лист. В результаті обчислень заходимо, що в першому випадку дальність польоту складає 726,04 м, а в другому – 492,40 м. Висоти підйому ядер відповідно складають 152,31м і 103,29 м.

 Аналогічно, за допомогою створеного робочого листа, можна обчислити дальність і висоту польоту ядер іншої стародавньої зброї – катапульти. Для початкової швидкості  і кута 70º матимемо: дальність польоту складає 321,39м, а висота підйому ядра - 220,76 м.

 Баліста нагадує великий лук, а катапульта – ложку, що кидає каміння по крутій траєкторії (під великим кутом). Дальність метання каменів балістою складала 400-800м, а легких стріл – 1000м. Катапульта кидала каміння на відстань 250-850м.

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

Створення сценарію

 1) Для створення сценарію, по-перше, необхідно присвоїти імена коміркам, які містять вхідні дані (В4:В6), що будуть змінюватись, а по-друге, коміркам, в яких будуть знаходитись результати обчислень (В14:В15). Присвоєння імен можна здійснювати записуючи імена безпосередньо в полі імені Имя (Мал. 3):

або скориставшись командою Вставка Þ Имя Þ Присвоить. Цей, останній, варіант є більш зручним, оскільки Microsoft Excel автоматично пропонує як імена комірок стовпця В імена комірок, що знаходяться в стовпці А (тобто в стовпці зліва), отже імена не прийдеться набирати на клавіатурі. Так, для комірки В4 буде запропоновано ім’я Початкова_швидкість_v0, а для комірки В5 – ім’я Кут_в_градусах_alpha і т. д.[3]

2) Запустити Диспетчер сценаріїв за допомогою команди Сервис ÞСценарии. В результаті на екран виводиться діалогове вікно Диспетчера сценаріїв (Мал. 4):

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

3) Після того, як ми клацнемо мишею на клавіші Добавить, відкривається діалогове вікно Добавление сценария (Мал. 5).

В полі Название сценария слід ввести ім’я сценарію, яке, на відміну від імен комірок може починатись з цифри, а не тільки літери, містити пропуски і мати довжину до 255 символів. Задамо як ім’я нашого першого сценарію ім’я Баліста - легке ядро. В полі Изменяемые ячейки слід вказати адреси комірок, де містяться вхідні дані. В даному випадку, комірки є суміжними, тому відсунувши за допомогою миші діалогове вікно Добавление сценария вбік, виділяємо за допомогою миші ці комірки (Мал. 6). Якщо комірки з вхідними даними є несуміжними, то щоби їх виділити, слід утримувати клавішу Ctrl при клацанні мишею на такій комірці.

Задамо як змінювані комірки – комірки В4:В5. Цим коміркам були присвоєні імена Початкова_швидкість_v0 і Кут_в_градусах_alpha. Після виділення комірок із змінюваними даними, вікно автоматично перейменується з вікна Добавление сценария у вікно Изменение сценария.

В полі Примечание за замовчуванням виводиться ім’я автора сценарію і дата його створення. Можна написати в це поле будь-яку іншу інформацію, що пояснює призначення і використання сценарію.

 4) Клацаємо мишею на кнопці ОК, після чого на екран виводиться діалогове вікно Значения ячеек сценария (Мал. 7). В текстових полях Початкова_шв і Кут_в_граду слід ввести дані, що відповідають початковим умовам легкого ядра, запущеного за допомогою балісти ( і 40º).

 Після вводу даних і натискання кнопки ОК на екран буде виведено знову вікно Диспетчера сценаріїв (Мал. 8):

 5) Далі можна вивести на екран результати розрахунку за створеним сценарієм Баліста - легке ядро (для цього слід натиснути кнопку Вывести ). А можна додати до робочого листа інші сценарії. Повторюючи ті самі дії, як і при створенні сценарію Баліста - легке ядро, створимо ще два сценарії – Баліста - важке ядро і Катапульта, ввівши відповідні дані (кн. Добавить). В результаті, на екран буде виведено вікно Диспетчера сценаріїв, що матиме наступний вигляд (Мал. 9):

 6) Отримаємо результати розрахунків зразу по всіх сценаріях вивівши на екран звіт (кн. Отчет). Після натискання кн. Отчет на екран виводиться діалогове вікно Отчет по сценарию (Мал. 10):

 Нам надається можливість отримати один з двох типів звітів: звіт у вигляді структури або звіт у вигляді зведеної таблиці. Після вибору типу звіту, (виберемо, наприклад, звіт у вигляді структури), слід вказати комірки, які потрібно відображати в звіті (якщо комірки суміжні, – виділити їх за допомогою миші, а якщо є несуміжні, то для їх виділення скористатися клавішею Ctrl). В даному прикладі ми виводимо як результат вміст комірок, що містять висоту підйому і дальність польоту ядра (В14,В15). Можна і безпосередньо написати адреси комірок у текстовому полі Ячейки результата, відокремлюючи адреси суміжних комірок двокрапкою, а несуміжних – крапкою з комою. Клацнувши мишею на кн. ОК, отримаємо на окремому листі звіт (Мал. 11).

 На мал. 11 таблиця розділена на дві частини, оскільки, не вміщується на екрані. Отриманий звіт можна роздрукувати. Якщо він містить непотрібні стовпці, то їх можна видалити зі звіту перед друком. Видалення стовпців здійснюється так само, як і у випадку звичайного робочого листа.

 

 Якщо виводити звіт у вигляді зведеної таблиці, то на екрані будемо мати (Мал. 12):

Як видно з малюнка, в цьому випадку на екран не виводяться початкові дані (початкова швидкість тіла і кут, під яким тіло кинуто до горизонту), а тільки результати розрахунків. Висновки

Зробимо деякі висновки з наведеного вище розгляду.

1. Маючи робочий лист (мал. 2), що дозволяє розраховувати параметри польоту тіла, ми можемо, підставляючи в комірки початкові дані, розрахувати параметри польоту. Якщо виникає потреба знайти параметри польоту для відомої метальної зброї (баліста, катапульта – як в прикладі), то не є зручним, всякий час підставляти її початкові дані, – тим більше, що їх потрібно пам’ятати. Створивши сценарії, як в наведеному вище прикладі, ми в разі потреби, можемо, викликавши відповідний сценарій за його іменем, автоматично отримати результат.

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

Доповнимо створений робочий лист ще двома сценаріями: Африка і Тундра. Як відомо, прискорення вільного падіння g не є величиною сталою – для екватора його величина складає , а для районів Землі, близьких до полюса – .

Присвоїмо комірці B6 ім’я Прискорення_вільного_падіння_g (Вставка Þ Имя Þ Присвоить). Викликаючи Диспетчер сценаріїв (Сервис Þ Сценарии) за допомогою кнопки Добавить, створюємо сценарій Африка, взявши за змінювану комірку В6 і заносячи в неї значення 9,78. Аналогічно створюємо сценарій Тундра, з тією різницею, що в комірку В6 занесемо значення 9,83.

Тепер досить легко комбінувати наявні сценарії. Запустивши Диспетчер сценаріїв, і вибравши сценарій Катапульта, і далі кн. Вывести, отримаємо (мал.13)

Мал. 13.

Вибравши тепер зі списку сценаріїв сценарій Африка, ми одержимо параметри польоту тіла, випущеного з катапульти, що обчислені з врахуванням африканських умов (мал. 14):

 

Мал. 14.

Аналогічно, застосувавши сценарій Тундра, можна отримати значення для параметрів польоту тіла, запущеного з катапульти в північних областях.

Розглянуті приклади показують, наскільки легко могли б планувати свої битви стародавні римські полководці, якби вони вміли користуватись Microsoft Excel, ну і мали б його, звичайно!

Цілком аналогічно застосовуються сценарії і у випадку економічних задач. Якщо, наприклад, робочий лист містить параметр, що характеризує вплив конкурентів, то створивши сценарій, з найменшим значенням цього параметра, і назвавши його, наприклад "Слабкі конкуренти", а також створивши сценарії "Нормальні конкуренти" – з середнім значенням параметра та "Агресивні конкуренти" з максимальним значенням параметра, можна спостерігати, як буде змінюватись економічна ситуація.

[1] у випадку, якщо функція є неперервною

[2] Функція РАДИАНЫ(кут) є стандартною функцією Microsoft Excel, що переводить величину кута, заданого в градусах, у величину кута в радіанах.

[3] Можна обійтись і без присвоєння імен коміркам, але користуватись таким сценарієм буде незручно.