M01590

11

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

12

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

13

2. Лабораторна робота № 2. Тема: “Робота з матрицями”

У Excel існують спеціальні функції для роботи з матрицями: МОБР( ) знаходження оберненої матриці МОПОРЕД( ) знаходження визначника матриці МУМНОЖ( ) добуток двох матриць ТРАНСП( ) транспонування матриць.

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

<Ctrl>+<Shift>+<Enter>.

Завдання до лабораторної роботи

1.Знайти обернену матрицю до матриці А.

2.Знайти визначник матриці А.

3.Обчислити вираз 2А-3С, де А та С надані матриці.

4.Знайти добуток матриць АС.

5.Обчислити значення квадратичної форми Z = BT AT AB .

6.Використовуючи метод оберненої матриці . знайти розв’язати системи лінійних рівнянь А*X=У , де А- матриця коефіцієнтів при невідомих, В- вектор-стовпець вільних членів.

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

15

Вхідні данні, а саме, матриці А, B, та C набрати в діапазонах

A2:D5, F2:F5, H2:K5.

Завдання 1. Виділити клітини для розміщення результату, наприклад діапазон A8:D11. Через майстер функцій або безпосередньо через клавіатуру набрати формулу =МОБР(A2:D5).

Натиснути <Ctrl>+<Shift>+<Enter>.

Завдання 2. У клітині В14 набрати формулу =МОПРЕД(A2:D5) Завдання 3. Виділити діапазон A17:D20 для розміщення результату. Набрати формулу =2*A2:D5-3*H2:K5. Натиснути

<Ctrl>+<Shift>+<Enter>.

Завдання 4. Виділити діапазон для розміщення результату F8:I11. Увести формулу =МУМНОЖ(A2:D5;H2:K5). Натиснути

<Ctrl>+<Shift>+<Enter>.

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

16

Завдання 5. У клітину G14 увести формулу =МУМНОЖ(ТРАНСП(F2:F5);МУМНОЖ(ТРАНСП(A2:D5);

МУМНОЖ(A2:D5;F2:F5))).

Завдання 6. Виділити діапазон G17:G20 для розміщення результатів. Увести формулу =МУМНОЖ(МОБР(A2:D5);F2:F5).

Натиснути <Ctrl>+<Shift>+<Enter>.

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

Результати виконання роботи можуть бути представлені в наступному вигляді (рис 2.1):

Рисунок 2.1. Приклад звіту.

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

17

3. Лабораторна робота №3. Тема: “Рівняння регресії”

Лінійний регресійний аналіз полягає у визначені параметрів емпіричної залежності y(x) = kx + b , яка описує зв’язок між

значень, тобто мінімізувати функцію

n

z= å( yi - kxi - b)2 ,

i=1

де n – кількість табличних значень xi та yi .

Пошук мінімуму функції z знаходимо, вирішуючи систему лінійних рівнянь

Рішенням системи є вирази

Перший спосіб.

Занесемо табличні значення xi та yi в діапазон A2:B7.

Для значень k та b відведемо клітини D2 та E2, а в клітині F2 розмістимо функцію z. У клітині F2 набираємо формулу

{=СУММКВРАЗН(В2:В7;Е2+D2*А2:А7)}.

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

18

У клітині С2 вводимо формулу {=$D$2*A2+$E$2} та копіюємо її

вдіапазон С3:С7.

Далі вибираємо команду Сервис/Поиск решения та заповнюємо діалогове вікно Поиск решения як указано на рис 3.1. Далі натискуємо кнопку Выполнить. В клітинах D2:F2 з’являються результати.

Рисунок 3.1.

Другий спосіб.

Параметри k та b можна також визначити за допомогою функцій НАКЛОН (SLOPE) та ОТРЕЗОК(INTERCEPT).

Функція НАКЛОН визначає коефіцієнт нахилу лінійного тренда. Синтаксис:

НАКЛОН(відомі значення У; відомі значення Х)

Функція ОТРЕЗОК визначає точку перетину лінії лінійного тренда з віссю ординат.

Синтаксис:

ОТРЕЗОК(відомі значення Х; відомі значення У).

У клітинах D3 та E3 знайдені значення k та b відповідно по формулам

=НАКЛОН(В2:В7;А2:А7)

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

19

=ОТРЕЗОК(В2:В7;А2:А7)

Третій спосіб.

Використовуючи Мастер диаграмм будуємо точковий графік по діапазону клітин А2:А7. Виділяємо точки графіка подвійним натиском миші, потім натискуємо праву кнопку миші. В контекстному меню вибираємо команду Линии тренда. В діалоговому вікні Линии тренда на вкладці Тип в групі

Построєние линии тренда(апроксимация и сглаживание)

вибираємо параметр Линейная, а на вкладці Параметрі

встановлюємо прапорці Показывать уравнение на диаграмме та

Поместить на диаграмме величину достоверности аппроксимации (R^2) ( тобто на діаграмі потрібно розмістити значення квадрата коефіцієнта кореляції. По коефіцієнту кореляції можна судити про правомірність використання лінійного рівняння регресії. Якщо він належить діапазону 0,9 –1, то знайдену залежність можна використовувати для прогнозування результату.

Теоретичне значення функції у деякій фіксованій точці можна обчислити і без попереднього визначення коефіцієнтів лінійної моделі за допомогою функції ПРЕДСКАЗ(FORECAST).

Синтаксис:

ПРЕДСКАЗ(t; відомі значення У; відомі значення Х),

де t –точка, для якої потрібно визначити значення функції. Наприклад, у клітину E7 вводимо значення x0 =5,5, а в клітину F7

формулу

=ПРЕДСКАЗ(E7;A2:A7;B2:B7).

Вхідні дані та результати виконання роботи представлені на рис.3.2.

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com

20

Рисунок 3.2.

Варіанти завдань

Для наданих значень xi та yi визначити функцію лінійної регресії трьома вказаними способами. Знайти коефіцієнт кореляції та визначити значення функції у фіксованій точці x0 .

.1.

PDF создан испытательной версией pdfFactory Pro www.pdffactory.com