Хід роботи

Для виконання роботи використовується пакет EXCEL.

1. Блок вихідних даних X, Y поміщаємо в перші два стовпці, наприклад в A і B і n рядків (нехай n = 13). Наступний (n + 1)-й рядок залишаємо для прогнозних значень. За блоком вихідних даних іде блок проміжних розрахунків. У перший стовпець цього блоку (наприклад стовпець С) поміщаємо добутки x_iy_i (i = ). Для цього в першу комірку стовпця С (наприклад С3) вводимо формулу для знаходження добутку x₁y₁:

= A3*B3.

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

Аналогічно обчислюються значення (i = ) і поміщаються в наступний четвертий стовпець (наприклад D).

Далі обчислюємо суми значень у всіх чотирьох стовпцях

, , ,

і поміщаємо їх в окремому (n + 2)-му рядку. Для визначення сум ви­ко­рис­то­ву­є­мо функцію СУММ. Спочатку позначаємо комірку, куди будемо заносити суму, натискаємо кнопку f_x на панелі інструментів. Відкриється вікно Мастер функций. Обираємо функцію СУММ, виділяємо блок значень, які додаються, і натискаємо кнопку ENTER або OK.

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

Використовуючи вбудовану функцію СРЗНАЧ, обчислюємо середні значення в усіх стовпцях

, , ,

і поміщаємо їх у наступний (n + 3)-й рядок.

Тепер можемо обчислити кореляційний момент K(X, Y) і дисперсію D(X) за формулами (1.5), (1.6). Знайдені значення поміщаємо в деякі комірки під блоком проміжних розрахунків, починаючи з (n + 4)-го рядка.

Далі знаходимо оцінку параметра а за формулою (1.3), а потім оцінку параметра b за формулою (1.4).

У наступний п’ятий стовпець (наприклад Е) заносимо розрахункові значення показника, обчисливши їх за формулою (1.2). Для цього в комірку Е3 вводимо формулу ax₁ + b з абсолютним посиланням координат параметрів a і b та відносним посиланням координати x₁. Наприклад, якщо значення a знаходиться в комірці B20, значення b – в комірці B21, x₁ – в A3, тоді в комірці С3 потрібно написати таку формулу:

= $B$20*A3 + $B$21.

Знак $ можна вставити автоматично за допомогою клавіші F4.

Одержану формулу копіюємо, наприклад, у блок E3:E15.

Далі знаходимо суму та середнє розрахункових значень показника.

Оскільки математичне очікування відхилення фактичних даних від розрахункових дорівнює нулю, то при правильному виконанні розрахунків значення комірок B17 та E17 збігатимуться.

2. Для того, щоб обчислити коефіцієнти кореляції та детермінації за формулами (1.8), (1.10), необхідно знайти D(Y) та D( ). Згідно з (1.9) і (1.11), спочатку обчислюємо та (i = ). Ці значення помістимо в 6 і 7 стовпцях (наприклад F і G). Потім копіюємо формули для обчислення сум і та середніх значень , .

Тепер можемо обчислити D(Y) і D( ) за формулами (1.9) і (1.11).

Знаходимо r(X, Y) і R² за формулами (1.8) і (1.10). Значення коефіцієнта кореляції обчислюються з використанням вбудованої функції КОРЕНЬ.

Якщо обчислення проведені правильно, то повинна виконуватись рівність (1.12).

3. За таблицею t-критерію (див. табл. 6) знаходимо t_крит.. У нашому випадку p = 0,95, n – m – 1 = 13 – 1 – 1 = 11, t_крит. = t(0,95; 11) = 2,201.

За формулою (1.13) обчислюємо t_розр..

На підставі знайдених значень t_розр. і t_крит. робимо висновок про значущість коефіцієнта кореляції.

4. За таблицею F-розподілу (див. табл. 7) знаходимо F_крит.. У нашому випадку p = 0,95, k₁ = 1, k₂ = 13 – 1 –1 = 11, F_крит. = F(0,95; 1, 11) = 4,84.

За формулою (1.14) знаходимо F_розр..

На підставі знайдених значень робимо висновок, чи правильно знайдено коефіцієнт детермінації, чи адекватна модель своїм статистичним даним.

5. Щоб встановити надійні зони (1.15) і (1.16) параметрів a,  і показника Y, спочатку знаходять D(u) за формулою (1.20). Для цього у двох наступних стовпцях (восьмому і дев’ятому) (наприклад H, I) обчислюємо u_i = y_i - та , i = . Підраховуємо суми і середні значення в цих стовпцях. Далі знаходимо D(u), потім . Тепер можемо обчислити a (1.18) і b (1.21), врахувавши (1.6) і (1.19) та встановити надійні інтервали для  і .

Для надійної зони (1.17) показника Y спочатку потрібно знайти , i = . Ці значення поміщаються в наступний 10-й стовпець J. Після цього за формулою (1.22) обчислюємо y_i і поміщаємо їх в 11-й стовпець K. Нарешті, обчислюємо надійні зони для показника = (y_i – y_i), = (y_i + y_i), i = , і поміщаємо їх у 12, 13-му стовпцях L, M.

Задамо прогнозоване значення фактора x_p і поміщаємо його в (n + 1)-й рядок 1-го стовпця A. В (n + 1)-му рядку 5-го стовпця E обчислюємо прогноз показника за формулою (1.23). Для цього копіюємо формулу цього стовпця в (n + 1)-й рядок.

Для надійного інтервалу прогнозу (1.24) знаходимо y_p за формулою (1.25). Спочатку знаходимо ( )², яке поміщаємо в (n + 1)-му рядку 10-го стовпця J. Для цього копіюємо формулу стовпця J в (n + 1)-й рядок. Далі за фор­­мулою (1.25) обчислюємо y_p і поміщаємо його в (n + 1)-й рядок стовпця K.

Тепер копіюємо формули стовпців L і M в (n + 1)-й рядок і в результаті отримаємо надійний інтервал для пронозу.

В останньому 14-му стовпці N обчислюємо коефіцієнт еластичності за формулою (1.26).

6. Для наочного зображення одержаних розрахунків будуємо графіки фактичних даних Y, лінії регресії для базисних даних та пронозу , довірчу зону для базисних даних і прогнозу (Y_min, Y_max), коефіцієнта еластичності K_el.