Практические_расчеты_на_EXСEL

1.4 Організація обчислень для алгоритмів розгалуженої структури

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

У середовищі Excel процеси, що розгалужуються, описуються логічною функцією ЕСЛИ. Її формат такий:

ЕСЛИ(условие;выражение1;выражение2),

де условие – логічний вираз, що має значення Истина або

Ложь;

выражение1 – вираз або рядкова константа, що вибирається при істинності логічного виразу;

выражение2 – вираз або рядкова константа, що вибирається, коли логічний вираз має значення Ложь.

Іншими словами, якщо логічний вираз має значення Истина, то обчислюється выражение1, інакше – выражение2.

Розглянемо приклад використання функції ЕСЛИ. Обчислити й

для K = 3,85; 0,6 ≤ x ≤ 5; x = 0,2 .

Рекомендований порядок організації обчислень наступний. Заповнюємо стовпець A значеннями x, починаючи з комірки A2; в комірку C2 вводимо значення 3,85; переходимо в комірку B2 і за допомогою майстра функцій викликаємо функцію ЕСЛИ. Вид вікна для завдання її аргументів показано на рисунку 1.4. Після закінчення введення функції в комірці B2 буде формула

=ЕСЛИ(A2<=1,5;LN($C$2)+0,9*LOG10(A2);3,2*SIN(A2)+(A2+5)^(1/4))

11

мають значення ИСТИНА, и ЛОЖЬ, якщо хоча б одна умова має значення ЛОЖЬ. У цьому випадку синтаксис функції ЕСЛИ матиме вид:

ЕСЛИ(И(условие1;условие2);выражение1;выражение2)

Якщо условие1 і условие2 істинні, то обчислюється

выражение1, інакше – выражение2.

Коли умови з’єднані логічним ИЛИ, результатом перевірки декількох умов буде значення ИСТИНА, якщо хоча б одне з умов має значення ИСТИНА, и ЛОЖЬ, якщо всі умови мають значення ЛОЖЬ. У цьому випадку синтаксис функції ЕСЛИ матиме вид:

ЕСЛИ(ИЛИ(условие1;условие2);выражение1;выражение2)

Якщо условие1 або условие2 істинне, то обчислюється

выражение1, інакше – выражение2.

2 РІШЕННЯ РІВНЯНЬ І СИСТЕМ ЛІНІЙНИХ АЛГЕБРАЇЧНИХ РІВНЯНЬ

2.1 Рішення рівнянь

Для рішення рівнянь в середовищі MS Excel використовується метод ітерацій. Його суть полягає в наступному. Спочатку задається наближене значення кореня, яке потім послідовно уточнюється доти, поки значення функції не стане рівним нулю.

Якщо результат обчислення за однією формулою відомий, а значення аргументу, від якого залежить цей результат, невідомо, то для пошуку значення аргументу варто використовувати інструмент MS Excel Подбор параметра, що викликається за допомогою команди Подбор параметра в меню Сервис. При його використанні значення комірки, що містить аргумент (параметр), змінюється доти, поки формула, що залежить від цього параметру, не поверне задане значення.

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

13

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

Розглянемо процес рішення нелінійного рівняння на наступному прикладі. Знайти корінь рівняння 4(1− x2 )− e− x = 0 на відрізку [0,2; 1].

Рекомендований порядок рішення задачі наступний. На листі вибираємо дві комірки: в одну з них (наприклад, A2) заносимо початкове значення аргументу x, що належить заданому відрізку (наприклад, 0,2), а в другу (наприклад, B2) – вираз для функції. Приклад заповнення листа Excel для рішення рівняння показано на рисунку 2.1. Після цього слід дати команду Сервис → Подбор параметра і задати потрібні параметри у вікні Подбор параметра, що з'явилося на екрані. Вид цього вікна зображено на рисунку 2.2.

Рисунок 2.1 – Приклад заповнення листа Excel для рішення рівняння

Рисунок 2.2 – Приклад завдання параметрів у вікні засобу Подбор параметра

На екрані з'являється діалогове вікно Результат подбора параметра з повідомленням про результат підбору параметра, вид якого при успішному завершенні операції показаний на рисунку 2.3. В комірці A2 відображається знайдене значення аргументу x, що є коренем рівняння.

14

C – стовпець вільних членів.

Тоді X = CA = A−1 ×C , тобто для рішення системи необхідно зво-

ротну матрицю A-1 помножити на стовпець вільних членів C. Для виконання перевірки рішення треба матрицю коефіцієнтів A помножити на отриманий вектор рішень (у результаті повинен вийти стовпець вільних членів).

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

і виконати перевірку рішення.

Рекомендований порядок рішення задачі наступний. Заносимо на лист Excel вхідні дані й необхідні розрахункові формули. Для цього в комірки (наприклад, A2:D5) вводимо матрицю коефіцієнтів системи, а в комірки E2:E5 – стовпець вільних членів. Щоб одержати зворотну матрицю, виділяємо комірки A7:D10 і за допомогою майстра функцій викликаємо функцію МОБР. Перебуваючи в полі Массив, виділяємо матрицю коефіцієнтів (комірки A2:D5) і натискуємо Ctrl+Shift+Enter. У рядку формул з’явиться формула {=МОБР(A2:D5)}, а в комірках A7:D10 – зворотна матриця.

Щоб знайти рішення системи, виділяємо комірки E7:E10 і за допомогою майстра функцій викликаємо функцію МУМНОЖ. Перебуваючи в полі Массив1, виділяємо зворотну матрицю (комірки A7:D10), переходимо в поле Массив2 і, знаходячись у ньому, виділяємо комірки E2:E5, натискуємо Ctrl+Shift+Enter. У рядку формул з’явиться формула {=МУМНОЖ(A7:D10;E2:E5)}, а в комірках E7:E10 – рішення системи. Для перевірки рішення треба за допомогою функції МУМНОЖ помножити матрицю коефіцієнтів (комірки A2:D5) на отриманий вектор рішень (комірки E7:E10). Повинен вийти вектор

16

ничним умовам.

Цільова комірка (Целевая ячейка) – це комірка, для якої потрібно знайти максимальне, мінімальне або задане значення. Вона повинна містити формулу, прямо або побічно залежну від змінюваних комірок.

Змінювані комірки (Изменяемые ячейки) – це комірки, від яких залежить значення цільової комірки. Поиск решения підбирає значення змінюваних комірок доти, поки не буде знайдене рішення при дотриманні заданих обмежень.

Обмеження (Ограничения) – це умова, що накладається на деяку комірку. Обмеження можуть бути накладені на будь-які комірки таблиці, включаючи цільову комірку і змінювані комірки.

Після запуску процедури пошуку рішення відкривається діало-

гове вікно Поиск решения. У полі Установить целевую ячейку тре-

ба ввести посилання на цільову комірку (виділити її). У полі Изменяя ячейки слід ввести посилання на змінювані комірки. Для завдання обмежень треба клацнути по кнопці Добавить. У діалоговому вікні, що відкрилося, треба:

−у полі Ссылка на ячейку увести посилання на комірку, що містить формулу, яка задає обмеження; формула повинна прямо або побічно залежати від однієї або декількох змінюваних комірок;

−у другому полі вибрати оператор обмеження (>,<, = і т.д.);

−у полі Ограничение ввести значення обмеження;

−для завдання наступного обмеження клацнути по кнопці Добавить й повторити операції завдання обмеження.

Коли всі обмеження задані, клацнути по кнопці ОК, щоб повернутися в діалогове вікно Поиск решения. Додавати, змінювати й видаляти обмеження можна за допомогою кнопок Добавить, Изменить, Удалить.

За допомогою кнопки Параметры викликається діалогове вікно Параметры поиска решения, де можна задати параметри пошуку рішення: максимальний час рішення; граничне число ітерацій; відносну погрішність, припустиме відхилення; збіжність; метод пошуку

(Ньютона або сопряженных градиентов). Якщо відомо, що розв'язу-

вана задача лінійна (тобто залежності між змінними лінійні), то варто включити режим Линейная модель, щоб прискорити процес рішення.

18

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

Для ініціалізації процедури пошуку рішення необхідно клацнути по кнопці Выполнить. Отримані результати будуть виведені на робочий лист. Після завершення процедури рішення на екрані з'являється діалогове вікно Результаты поиска решения з повідомленням про результати пошуку рішення. Воно дозволяє:

−Сохранить найденное решение, щоб зберегти знайдене рі-

шення;

−Восстановить исходные значения, щоб відновити початкові значення на робочому листі;

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

Сервис → Сценарии; − вибрати потрібний тип звіту (Результаты, Устойчивость,

Пределы) в полі Тип отчета. Звіт про результати пошуку рішення створюється на новому робочому листу.

Отчет по результатам містить початкові і кінцеві значення цільової комірки і змінюваних комірок, значення і формули обмежень, а також додаткові відомості про обмеження. Стовпець Статус таблиці Ограничения має значення "связанное", якщо виконується строга рівність, і "не связанное" – в протилежному випадку. Стовпець Разница містить різницю між правою та лівою частинами обмежень.

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

19

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

3.2 Пошук найбільшого і найменшого значення функції при наявності обмежень

Розглянемо цю задачу на наступному прикладі. Знайти найбільше значення функції f =10x1 + 4x2 при наявності системи обмежень

ì 11x2 ³ 6; ïí9x1 + 2x2 £ 95; ïî8x1 - 7x2 ³ 72.

Рекомендований порядок рішення задачі наступний. Заносимо на лист Excel вхідні дані й необхідні розрахункові формули. Для цього в комірки (наприклад, B2:С2) вводимо початкові значення x1 і x2. Їх кінцеві значення будуть одержані в результаті рішення задачі. В комірки B4:C7 вводимо значення коефіцієнтів при x1 і x2 в виразах для обмежень і цільової функції. В комірки D4:D7 вводимо формули для виразів обмежень і цільової функції. В комірки E4:E7 заносимо значення правої частини обмежень. Вигляд моделі, введеної до робочого листа Excel, показано на рисунку 3.1.

Після цього слід дати команду Сервис ® Поиск решения і задати параметри пошуку рішення у вікні, що з'явилося на екрані. Приклад завдання параметрів у вікні засобу Поиск решения показаний на рисунку 3.2. Для запуску процесу рішення необхідно клацнути по кнопці Выполнить. На екрані з'являється діалогове вікно Результаты поиска решения з повідомленням про результати пошуку рішення. Вид цього вікна при успішному завершенні процедури пошуку рішення показаний на рисунку 3.3.

20