Завдання № 2

Ціль: Аналіз даних з використанням методу найменших квадратів (МНК)

1. У цьому практичному завданні необхідно визначити найкращі лінійне наближення у вигляді прямої й показове наближення у вигляді, для заданого набору незалежної змінної й функції. Для цього відкрийте нову книгуExcel і перейдіть на перший аркуш.

2. Уведіть в осередки довільні дані для аргументу й функції як на малюнку.

3. Зробіть осередок З2 поточної й у рядку уведення формул уведіть =ІНДЕКС(ЛИНЕЙН(B2:B21;A2:A21);1).

Функція ЛИНЕЙН повертає коефіцієнти рівняння у вигляді масиву із двох елементів. За допомогою функції ІНДЕКС вибирається потрібний елемент.

4. Зробіть поточний осередок D2. Повторите операції, описані в пункті 3, щоб у підсумку в цьому осередку з'явилася формула: =ІНДЕКС(ЛИНЕЙН(B2:B21;A2:A21);2). Тепер в осередках З2 і D2 обчислені, відповідно, коефіцієнти a і b рівняння найкращій прямій (a = 2,191828, b = -2,4173316).

10. Зробіть поточний осередок З3. Уведіть формулу =ІНДЕКС(ЛГРФПРИБЛ(B2:B21;A2:A21);1).

11. Зробіть поточний осередок D3 і введіть формулу =ІНДЕКС(ЛГРФПРИБЛ(B2:B21;A2:A21);2). Тепер осередку C3 і D3 містять, відповідно, коефіцієнти a і b рівняння найкращого показового наближення (1,146704 і 3,5100146).

12. Для побудови найкращій прямій іншим способом дайте команду Аналіз даних у групі Аналіз, вкладки Дані. (Якщо кнопка Аналіз даних відсутній – виконаєте наступне: Офіс, Параметри Excel, Надбудови, Перейти, Пакет аналізу й ОК).

13. У списку Інструменти аналізу виберіть пункт Регресія.

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

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

16. Установите перемикач Новий робочий аркуш і задайте для нього ім'я Результат розрахунку.

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

Для інтерполяції або екстраполяції оптимальної кривої без явного визначення її параметрів можна використовувати функцію ТЕНДЕНЦІЯ (для лінійної залежності) і РІСТ (для показової залежності).

19. Збережете робочу книгу під ім'ям Лаба 5 МНК.

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

Лабораторна робота № 15 Операції із масивами та матрицями. Розв’язок слау Завдання № 1

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

1. Запустите програму Excel 2007.

2. Розглянемо множення масиву А1:В2 на число. Для цього введіть дані як показано на малюнку на першому листі.

Далі необхідно виділити на робочому листі область, наприклад D1:E2, того ж розміру, що й вихідний масив для результату множення. У всіх випадках при роботі з масивами перед уведенням формул треба виділити область на робочому листі, куди буде виведений результат обчислення.

3. Уведіть формулу =A1:B2*5 . Для цього встановите курсор у рядку формул і закінчите уведення не як звичайно, натисканням клавіші Enter, а натисканням клавіш Ctrl + Shift + Enter. Таким чином, ви повідомили програму, що необхідно виконати операцію над масивом. У результаті операції множення в діапазон D1:E2 буде поміщений результат множення числа на масив. При цьому Excel укладе формулу в рядку формул у фігурні дужки. При роботі з масивами формула діє на всі комірки діапазону.

4. Розглянемо операцію підсумовування (вирахування). На другому листі введіть два масиви (див. рис).

5. Виділите діапазон G1:H2 і введіть формулу =A1:B2+D1:E2, нажавши по закінченні уведення Ctrl + Shift + Enter, як у пункті 3. Після уведення формули результат додавання буде в діапазоні G1:H2.

6. Перейдіть на третій лист і розглянемо заелементне множення (розподіл) масивів. Уведіть дані для цього приклада як на малюнку 2. Виділите діапазон G1:H2 і введіть формулу =A1:B2*D1:E2, закінчивши уведення трьома клавішами (Ctrl + Shift + Enter). Після уведення формули результат буде в діапазоні G1:H2.

7. Розглянемо масив, кожний елемент якого зв'язаний за допомогою деякої функції з відповідними елементами первісного масиву. Уведіть дані в діапазон A1:B2 як на малюнку 2. Виділите діапазон D1:E2 і введіть формулу = SIN(A1:B2), попередньо набравши її в рядку формул. Після уведення формул ви одержите результат як на малюнку.

8. Розглянемо убудовані функції для роботи з матрицями. В Excel є наступні математичні спеціальні функції для роботи з матрицями:

МОБР - Повертає зворотну матрицю. Синтаксис: МОБР(масив).

МОПРЕД - Повертає визначник матриці. Синтаксис МОПРЕД (масив).

МУМНОЖ - Повертає добуток матриць. Синтаксис: МУМНОЖ (масив1;масив2).

ТРАНСП - Повертає транспоновану матрицю. Синтаксис: ТРАНСП (масив). Ця функція перебуває в категорії Посилання й масиви.

При роботі з матрицями перед уведенням формули треба виділяти область на робочому листі, куди буде виведений результат обчислення. Вирішимо як приклад систему лінійних рівнянь:

2х₁+3х₂+7х₃+6х₄=1

3х₁+5х₂+3х ₃+ х₄=3

5х₁+3х₂+х₃ + 3х₄=4

3х₁+3х₂+х₃ + 6х₄=5

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

Рішення лінійної системи АХ = В, де А - матриця коефіцієнтів, Х-Х- стовпець (вектор) невідомих, В - стовпець (вектор) вільних членів має вигляд Х = А^-1У. Матриця А^-1 є зворотною стосовно А. Виділите під вектор рішень діапазон Н1:Н4.

9. Уведіть формулу у виділений діапазон

= МУМНОЖ(МОБР(A1:D4);E1:E4). Уведення формули здійснюється натисканням клавіш Ctrl+Shift+ Enter. Після уведення формули ви одержите результат рішення в діапазоні Н1:Н4.

10. Вирішимо систему лінійних рівнянь А²Х=В. Після перетворення одержимо Х=(А²)^-1У, де ,. Увести в діапазонА1:В2 елементи матриці А, у діапазон D1:D2 - елементи стовпця вільних членів В. Виділите діапазон F1:F2, для результату рішень і введіть формулу =МУМНОЖ(МОБР (МУМНОЖ(A1:B2;A1:B2));D1:D2).

11. Розглянемо приклад обчислення квадратичної форми Z = X^T AX, де А - квадратична матриця, Х - вектор (стовпець), а символ (^Т) обозначающий операцію транспонування (коли рядка й стовпці міняють місцями). Уведіть у діапазон А1:В2 квадратичну матрицю, а в діапазон D1:D2 вектор як на малюнку.

Для обчислення квадратичної форми введіть у комірку F1 формулу

= МУМНОЖ(МУМНОЖ(ТРАНСП(D1:D2);A1:B2);D1:D2). Не забудьте нажати комбінацію клавіш для уведення формули Ctrl+Shift+ Enter. Ви повинні одержати число 24.

12. Аналогічно обчислите значення квадратичної форми z = Y^T A^T AY, де

, . Для цього введіть у діапазон комірокА1:В2 елементи матриці А, а в діапазон D1:D2 - елементи стовпця Y.

13. Для обчислення квадратичної форми введіть у комірку F1 формулу:

=МУМНОЖ(ТРАНСП(D1:D2);МУМНОЖ(ТРАНСП(A1:B2);МУМНОЖ(A1:B2;D1:D2))). Ви повинні одержати число 578.