Варіанти індивідуальних завдань

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

Завдання №3 Рівняння регресії

Лінійний регресійний аналіз полягає у визначені параметрів емпіричної залежності , яка описує зв’язок між деяким числом n пар значень x_i та y_i , забезпечив при цьому найменшу середньоквадратичну похибку. Параметри k та b підбираються так, щоб мінімізувати суму квадратів відхилень теоретичних значень залежної змінної y від табличних значень, тобто мінімізувати функцію

,

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

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

.

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

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

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

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

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

До клітини С2 заносимо формулу {=$D$2*A2+$E$2} та копіюємо її в діапазон С3:С7 .

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

Рисунок 3.1 – Вікно пошуку рішення

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

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

ОТРЕЗОК(INTERCEPT) .

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

Синтаксис:

НАКЛОН (відомі значення_y ; відомі значення_x ) .

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

Синтаксис:

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

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

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

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

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

Використовуючи Мастер диаграмм будуємо точечный графік за діапазоном клітин А2:А7 . Виділяємо точки графіка подвійним натиском миші, потім натискуємо праву кнопку миші. В контекстному меню обираємо команду Добавить линию тренда . В діалоговому вікні Линии тренда на вкладці Тип в групі Построение линии тренда (апроксимация и сглаживание) обираємо параметр Линейная , а на вкладці Параметры встановлюємо прапорці Показывать уравнение на диаграмме та Поместить на диаграмме величину достоверности аппроксимации (R^2) , тобто на діаграмі потрібно розмістити значення квадрата коефіцієнта кореляції.

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

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

Синтаксис:

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

де t – точка, для якої потрібно визначити значення функції.

Наприклад, у клітину E7 вводимо значення x₀ = 5,5 , а в клітину F7 формулу

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

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

Рисунок 3.2 – Рішення завдання