1. Мета роботи

1.1 .Вивчення можливостей пакету Ms Excel при розв’язанні лінійних систем рівнянь.

1.2 .Придбання навиків розв’язання систем лінійних рівнянь алгебри

1.3 .Придбання навиків виконання дій над матрицями засобами пакету.

1.4 .Вивчення можливостей пакету Ms Excel при розв’язанні нелінійних рівнянь і систем.

1.5 .Придбання навиків розв’язання нелінійних рівнянь та їх систем засобами пакету.

1.6 .Вивчення можливостей пакету Ms Excel при розв’язанні завдань лінійного програмування.

1.7 .Придбання навиків розв’язання задач лінійного програмування. .

2. Порядок виконання роботи

2.1. Підготовка до виконання роботи (попереднє ознайомлення з методичними вказівками до лабораторної роботи та опрацювання теоретичного матеріалу за конспектом лекцій і рекомендованою літературою).

2.2. Виконання навчальних завдань (п. 4.).

2.3. Складання звіту про виконання роботи (п. 5.).

2.4. Захист роботи (запитання для захисту роботи – п. 6.).

3. Короткі теоретичні відомості

3.1. Розв’язання систем лінійних алгебраїчних рівнянь (СЛАР)

Неай задана СЛАР наступного вигляду:

Цю систему можна представити в матричному вигляді: AX = b, де

- матриця коефіцієнтів системи рівнянь;

- вектор невідомих

- вектор правих частин.

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

Метод оберненої матриці

Систему лінійних алгебраїчних рівнянь AX = b помножимо зліва на матрицю, обернену до А. Система рівнянь прийме вигляд:

A^-1AX=A^-1b, EX=A^-1b (E - одинична матриця)

Таким чином, вектор невідомих обчислюється за формулою X=A^-1b.

Метод Крамера

В цьому випадку невідомі x1,x2., xn обчислюються за формулою:

де

- визначник матриці A

- визначник матриці, що отримується з матриці А шляхом заміни i-го стовпця вектором b.

Зверніть увагу на особливість роботи з матричними формулами: необхідно заздалегідь виділяти область, в якій зберігатиметься результат, а після отримання результату перетворювати його до матричного вигляду, натиснувши клавіші F2 і Ctrl+Shift+Enter.

Тепер розглянемо розв’язання системи лінійних рівнянь методом оберненої матриці і методом Крамера на прикладах.

ПРИКЛАД 11.1. Розв’язати систему методом оберненої матриці:

В цьому прикладі матриця коефіцієнтів А і вектор вільних коефіцієнтів b мають вигляд:

Введемо матрицю A і вектор b в робочий лист MS Excel (рис. 11.1).

Рис. 1.1

У нашому випадку матриця А знаходиться в осередках B1:Е4, а вектор b в діапазоні І1:І4. Для розв’язання системи методом оберненої матриці необхідно обчислити матрицю, зворотну до A. Для цього виділимо осередки для зберігання оберненої матриці (це потрібно зробити обов'язково!!!); нехай в нашому випадку це будуть осередки B6:E9. Тепер звернемося до майстра функцій, і в категорії Математичні виберемо функцію МОБР, призначену для обчислення оберненої матриці (рис. 11.2), клацнувши по кнопці OK, перейдемо до другого кроку майстра функцій. У діалоговому вікні, що з'являється на другому кроці майстра функцій, необхідно заповнити поле введення Масив (рис. 11.3). Це поле повинне містити діапазон осередків, в якому зберігається початкова матриця, - в нашому випадку B1:E4. Дані в полі введення «Массив» можна ввести, використовуючи клавіатуру або виділивши їх на робочому листі, утримуючи ліву кнопку миші.

Рис. 11.2

Рис. 11.3

Якщо поле «Массив» заповнено, можна натиснути кнопку OK. У першому осередку, виділеного під зворотну матрицю діапазону, з'явиться певне число. Для того, щоб отримати всю обернену матрицю, необхідно натиснути клавішу F2 для переходу в режим редагування, а потім одночасно клавіші Ctrl+Shift+Enter. У нашому випадку робочий лист MS Excel прийме вигляд зображений на рис. 11.4.

Рис. 11.4

Тепер необхідно помножити отриману зворотну матрицю на вектор b. Виділимо осередки для зберігання результуючого вектора, наприклад H6:H9. Звернемося до майстра функцій, і в категорії Математичні виберемо функцію МУМНОЖ, яка призначена для множення матриць. Нагадаємо, що множення матриць відбувається за правилом: рядок на стовпець і матрицю А можна помножити на матрицю В тільки в тому випадку, якщо кількість стовпців матриці А рівно кількості рядків матриці В. Окрім того, при множенні матриць важливий порядок співмножників, тобто АВГВА

Перейдемо до наступного кроку майстра функцій. Діалогове вікно (рис. 11.5), що з'явилося, містить два поля введення Массив1 і Массив2. У полі Массив1 необхідно ввести діапазон осередків, в якому міститься перша з перемножуваних матриць, в нашому випадку B6:E9 (обернена матриця), а в полі Массив2 осередки, що містять другу матрицю, в нашому випадку І1:І4(вектор b).

Рис. 11.5

Якщо поля введення заповнені, можна натиснути кнопку OK. У першому осередку виділеного діапазону з'явиться відповідне число результуючого вектора. Для того, щоб отримати весь вектор, необхідно натиснути клавішу F2, а потім одночасно клавіші Ctrl+Shift+Enter. У нашому випадку результати обчислень (вектор х), знаходиться в осередках H6:H9.

Для того, щоб перевірити, чи правильно вирішена система рівнянь, необхідно помножити матрицю A на знайдений вектор x і отримати в результаті вектор b. Множення матриці A на вектор x здійснюється за допомогою функції МУМНОЖ(В1:Е4;Н6:Н9), способом, описаним вище.

ПРИКЛАД 11.2. Розв’язати систему з ПРИКЛАДУ 11.1 методом Крамера.

Введемо матрицю А і вектор b на робочий лист. Крім того, сформуємо чотири допоміжні матриці, замінюючи послідовно стовпці матриці A на стовпець вектора b (рис. 11.6).

Для подальшого розв’язання необхідно обчислити визначника матриці A. Встановимо курсор в осередок I10 і звернемося до майстра функцій. У категорії Математичні виберемо функціюМОПРЕД, призначену для обчислення визначника матриці, і перейдемо до другого кроку майстра функцій. Діалогове вікно, що з'являється на другому кроці містить поле введення Масив. У цьому полі указують діапазон матриці, визначника якої обчислюють. У нашому випадку це осередки B1:E4.

Для обчислення допоміжних визначників введемо формули:

I11=МОПРЕД(B6:E9), I12=МОПРЕД(B11:E14),I13=МОПРЕД(B16:E19), I14=МОПРЕД(B21:E24).

В результаті в осередку I10 зберігається головний визначник, а в осередках I11:I14 - допоміжні.

Скористаємося формулами Крамера і розділимо послідовно допоміжних визначників на головний. У осередок І17 введемо формулу =I11/$I$10. Потім скопіюємо її вміст в осередки І18, І19 і І20. Система розв’язана.

Рис. 11.6

ПРИКЛАД 11.3. Обчислити матрицю за формулою: C=A²+2AB, де

Введемо початкові дані на робочий лист (рис. 11.7).

Для множення матриці А на матрицю В, виділимо діапазон B5:D7 і скористаємося функцією МУМНОЖ(B1:D3;G1:I3).

Результат обчислення A²=A*A помістимо в осередки G5:I7, скориставшись формулою МУМНОЖ(B1:D3;B1:D3).

Множення (ділення) матриці на число можна виконати за допомогою елементарних операцій. У нашому випадку необхідно помножити матрицю з діапазону B5:D7 на число 2. Виділимо осередки B9:D11 і введемо формулу =2*B5:D7.

Додавання (віднімання) матриць виконується аналогічно. Наприклад, виділимо діапазон G9:I11 і введемо формул =B9:D11+ G5:I7.

Для отримання результату в обох випадках необхідно натиснути комбінацію клавіш Ctrl+Shift+Enter.

Крім того, в рядку формул робочого листа, зображеного на рис. 11.7, показано як можна обчислити матрицю С одним виразом.

Рис. 11.7

3.2. Розв’язання нелінійних рівнянь і систем

Підбір параметра, пошук рішення

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

Підбір параметра - одна з корисних можливостей для проведення аналізу даних. Зазвичай підбір параметра використовується в наступній ситуації: у одному осередку знаходиться функція, залежна від одного або декількох параметрів, розташованих в інших осередках робочого листа. Ми можемо змінювати значення тільки одного з параметрів. Потім ми викликаємо команду менюДанные > Работа с данными >Анализ «что-если»>Подбор параметра (рис. 11.8).

Рис. 11.8 – Діалогове вікно інструменту «Підбор параметра»

Для пошуку рішення команда «Подбор параметра» використовує метод ітерацій. Це означає наступне: спочатку перевіряється початкове значення в осередку що містить параметр. Якщо початкове не підходить, то підбирається нове значення, і так до тих пір, поки не буде відшукано потрібне значення параметра(якщо, звичайно, таке можливе).

ПРИКЛАД 11.4. Знайти корені полінома x³ - 0,01x² - 0,7044x + 0,139104 = 0.

Спершу розв’яжемо рівняння графічно. Відомо, що графічним розв’язком рівняння f(x)=0 є точка перетину графіка функції f(x) з віссю абсцис, тобто таке значення x, при якому функція звертається в нуль.

Проведемо табулювання нашого полінома на інтервалі від -1 до 1 з кроком 0,2. Результати обчислень приведені на рис. 11.9., де в осередок В2 була введена формула: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. На графіку видно, що функція три рази перетинає вісь ОХ, а оскільки поліном третього ступеня має не більше трьох речових коренів, то графічне рішення поставленої задачі знайдене. Інакше кажучи, була проведена локалізація коренів рівняння, тобто визначені інтервали, на яких знаходиться корені даного полінома: [-1,-0.8], [0.2,0.4] і [0.6,0.8].

Рис. 11.9

Тепер можна знайти корені полінома методом послідовних наближень за допомогою команди Сервіс Підбір параметра. Відносна похибка обчислень і граничне число ітерацій (наприклад, 0,00001 і 1000) задаються на вкладці Сервіс Параметри.

Після введення початкових наближень у комірки А14-А16 і значень функції у комірках В14-В16 можна звернутися до пункту меню Подбор параметра і заповнити діалогове вікно таким чином (див. рис. 11.10).

Рис. 11.10

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

Після натиснення кнопки ОК з'явиться діалогове вікно Результат підбору параметра з повідомленням про успішне завершення пошуку рішення, наближене значення кореня буде поміщено в осередок А14.

Два корені, що залишилися, знаходимо аналогічно. Результати обчислень будуть поміщені в осередки А15 і А16.

ПРИКЛАД 11.5. Розв’язати рівняння e^x - (2x - 1)² = 0.

Спочатку проведемо локалізацію коренів нелінійного рівняння.

Для цього представимо його у вигляді f(x)= g(x), тобто e^x = (2x - 1)²або f(x)= e^x, g(x)= (2x - 1)², і знайдемо розв’язок графічно.

Графічним розв’язком рівняння f(x)= g(x) буде точка перетину ліній f(x) і g(x).

Побудуємо графіки f(x) і g(x). Для цього в діапазон А3:А18 введемо значення аргументу. У осередок В3 введемо формулу для обчислення значень функції f(x): = EXP(A3), а в С3 для обчислення g(x): = (2*A3-1)^2.

Результати обчислень і побудова графіків f(x) і g(x) в одній графічній області показані на рис. 11.13.

Рис. 11.13

На графіку видно, що лінії f(x) і g(x) перетинаються двічі, тобто дане рівняння має два рішення. Одне з них тривіальне і може бути обчислено точно:

Для другого можна визначити інтервал локалізації кореня: 1,5 < x < 2.

Тепер можна знайти корінь рівняння на відрізку [1.5,2] методом послідовних наближень.

Введемо початкове наближення в осередок Н17 = 1,5, і саме рівняння, з посиланням на початкове наближення, в осередок I17 = EXP(H17) - (2*H17-1)^2 (див. рис. 11.13).

Далі скористаємося командою Підбір параметра і заповнимо діалогове вікно Підбір параметра (див. рис. 11.14).

Рис. 11.14

3.4. Пошук рішення. Розв’язання завдань лінійного програмування

Завдання оптимізації - це завдання знаходження якнайкращих значень (наприклад, для плану виробництва) при заданих обмеженнях.

Рішення задачі оптимізації на комп'ютері включає наступні кроки.

Примітка

· Якщо на згортку Данные у розділі Анализ відсутня команда «Поиск решения»:

· Викликаємо команду Офис>Параметри Excel>Надстройки> Управление > Настройки Excel> Перейти (не плутати з настройками!)

· Ставимо в списку прапорець Поиск решения

· Натискуємо ОК.

Крок 1. Визначити, що потрібно знайти в завданні (наприклад, план виробництва, план перевезень, раціон годування). Це не те ж саме, що потрібно максимізувати або мінімізувати. Передбачити для цього плану комірки з залежними величинами в таблиці і задати в них будь-які початкові значення. Виділити ці осередки, наприклад, зеленим кольором.

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

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

Крок 3. Виділити один осередок червоним кольором – це буде цільовий осередок. У цьому осередку обчислити те, що потрібно надалі максимізувати (мінімізувати). Перевірити, що значення в цільовому осередку змінюється при зміні плану (заданого зеленим кольором).

Крок 4. Скористатися командою Данные >Анализ>Поиск решення.

Відкриється діалогове вікно:

Задати:

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

змінювані осередки – ті, які задані зеленим кольором.

Додати обмеження: жовті <= (або >=) оранжевих.

Можливо, треба додати обмеження на план: план повинен бути позитивним та цілим.

Для додавання обмежень слід натиснути кнопку «Добавить». Відкриється діалогове вікно:

Задаємо перше обмеження, натискуємо ОК.

При необхідності знову натискуємо Добавить, якщо треба ще додати обмеження.

Після того, як задані всі поля в діалозі, натискуємо «Выполнить». Комп’ютер повідомить про знайдене оптимальне рішення (натискуємо OK, щоб залишити знайдений розв’язок або Отмена, щоб повернутися до попередніх даних), або повідомить про неможливість знайти оптимальне рішення.

ПРИКЛАД 11.6. Розв’язати систему рівнянь:

Розглянемо, як можна вирішити систему рівнянь:

F₁(x)=0,

F₂(x)=0,

F_n(x)=0

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

Знайти мінімум (максимум) функції

при системі обмежень, заданою у вигляді рівності F_i(x)= 0;

Знайти мінімум функції

В цьому випадку завдання вирішується без обмежень.

1-й спосіб. У осередки А1 і А2 вводимо числа 0 (тут ми зберігатимемо x₁ і x₂). У осередки В1 і В2 вводимо обмеження: В1 = 2*А1-3*А2, В2 = А1+А2. У осередок С1 введемо цільову функцію (цей осередок ми мінімізуватимемо): С1 = СУМ(B1:B2). Скористаємося командою Сервіс Пошук Рішення і заповнимо діалогове вікно, що з'явилося, так, як показано на рис. 11.15. В результаті розв’язання поставленої задачі отримаємо розв’язки системи початкових рівнянь: x₁ = 1,6, x₂ = 2,4.

Рис. 11.15 – Розв’язання системи рівнянь

2-й спосіб. У осередках D1 і D2 зберігатимемо змінні x₁ і x₂. У осередки E1 і E2 введемо рівняння системи: E1 = 2*D1-3*D2+4, E2=D1+D2-4. Як цільову функцію в осередок F1 введемо формулу = E1^2+E2^2. Звернемося до обчислювального блоку (див. рис. 11.16) і введемо умову завдання оптимізації. В результаті отримуємо наступне розв’язання системи: x₁ = 1,600000128, x₂= 2,39999949.

Рис. 11.16

Розв’язати задачу лінійного програмування:

У завданнях лінійного програмування завжди необхідно знайти мінімум (або максимум) лінійної функції багатьох змінних при лінійних обмеженнях у вигляді рівності або нерівностей.

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

Для розв’язання подібних завдань в MS EXCEL призначена команда Пошук рішення.

ПРИКЛАД 11.7. Розв’язати задачу лінійного програмування:

L = 5x₁ - 2x₃® min

- 5x₁ - x₂ + 2x₃ ≤ 2

- x₁+x₃ + x₄ ≤ 5

- 3x₁ + 5x₄ ≤ 7

Нехай значення x₁, x₂, x₃, x₄ зберігаються в осередки A1:A4, а значення функції L - в осередку С1. Введемо обмеження:

С2 = -5*A1 - A2 + 2*A3

С3 = -А1 +А3 + А4

С4 = -3*А1 + 5*А4.

Таким чином, було задано умову початкового завдання лінійного програмування.

Виконаємо команду Пошук рішення.

Спрямуємо цільову функцію в осередку C1 до мінімуму. Для цього введемо в поле Встановити цільову функцію значення С1 і встановимо опцію "рівної мінімальному значенню".

У полі Змінюючи осередки необхідно вказати адреси осередків, в яких зберігаються змінні значення. У нашому випадку це осередки А1:А4.

Для додавання обмежень необхідно клацнути по кнопці Додати, з'явиться діалогове вікно Додати обмеження (див. вище).

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

Клацання по кнопці OK означає введення чергового обмеження і повернення до діалогового вікна Пошук рішення.

Клацання по кнопці Додати вводити чергове обмеження, знаходячись у вікні Додати обмеження.

Клацання по кнопці Виконати почне процес рішення задачі, завершиться який появою діалогового вікна представлення результатів пошуку.

У нашому випадку Клацання по кнопці OK приведе до появи в осередку С1 значення цільової функції L, а в осередках A1:A4 - значень змінних x₁-x₄, при яких цільова функція досягає мінімального значення.

Якщо завдання не має рішення або невірно були задані початкові дані, у вікні «Результати пошуку рішення» може з'явитися повідомлення про те, що рішення не знайдене.

Отже, призначення основних кнопок і вікон діалогового вікна Пошук рішення:

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

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

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

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

· Кнопки Додати, Змінити, Видалити дозволяють додати, змінити або видалити обмеження.

· Кнопка Виконати запускає процес рішення певної задачі.

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

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

· Кнопка Скинути очищає всі поточні установки завдання і повертає всі параметри до їх значень за умовчанням.

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

4. Завдання для виконання

Вказівки

Кожне завдання слід виконати на окремому робочому листі з відповідною назвою книги MS Excel.

Для завдань 1-5 кожен студент виконує 2 (ДВА!!!!) віріанти – власний варіант та завдання № (власний варіант +15)

Завдання 1 Системи рівнянь

Розв’язати систему рівнянь методом Крамера.
Розв’язати систему рівнянь за допомогою оберненої матриці.
Виконати операції над матрицями.

При розв’язанні систем рівнянь обов’язково виконати перевірку.

ВАРІАНТИ ЗАВДАНЬ

Варіант №1	1)	2)

Варіант №2	1)	2)

Варіант №3

Варіант №4	1)	2)

Варіант №5	1)	2)

Варіант №6	1)	2)

Варіант №7	1)	2)

Варіант №8	1)	2)

Варіант №9	1)	2)

Варіант №10	1)	2)

Варіант №11	1)		2)
3) (2A-B)(3А+B)-2АВ,

Варіант №12	1)	2)

Варіант №13	1)	2)

3) (A+B)A-B(2А+3В),

Варіант №14	1)	2)

3) A(2A+B)-B(А-В),

Варіант №15	1)	2)

3) 3(A+B)(AВ-2А),

Варіант №16		2)

Варіант №17	1)	2)

3) 2А + 3B(АB-2А),

Варіант №18	1)	2)

Варіант №19	1)	2)

3) 2A - АB(В - А) + В,

Варіант №20	1)	2)

Варіант №21	1)	2)

Варіант №22

Варіант №23	1)	2)

3) А(A - B) + 2В(A + В),

Варіант №24	1)	2)

Варіант №25

Варіант №26

Варіант №27

Варіант №28

Варіант №29

Варіант №30

Завдання 2. Знайти корені полінома

ВАРІАНТИ ЗАВДАНЬ

№	рівняння	№	рівняння
1		16
2		17
3		18
4		19
5		20
6		21
7		22
8		23
9		24
10		25
11		26
12		27
13		28
14		29
15		30

Завдання 3. Знайти розв’язок нелінійного рівняння

ВАРІАНТИ ЗАВДАНЬ

№	Рівняння	№	рівняння	№	рівняння
1		10		19
2		11		20
3		12		21
4		13		22
5		14		23
6		15		24
7		16		25
8		17		26
9		18		27

Завдання 4. Розв’язати систему нелінійних рівнянь

ВАРІАНТИ ЗАВДАНЬ

№	Система рівнянь	№	Система рівнянь	№	Система рівнянь
1		11		21
2		12		22
3		13		23
4		14		24
5		15		25
6		16		26
7		17		27
8		18		28
9		19		29
10		20		30