МІНІСТЕРСТВО ОСВІТИ І НАУКИ, МОЛОДІ ТА СПОРТУ УКРАЇНИ Національний транспортний університет Кафедра інформаційних систем і технологій Модульний контроль № 1 з дисципліни «Комп’ютерні технології статистичної обробки даних» на тему «Парна лінійна регресія засобами MSExcel» Київ 2012 Варіант – 15 Завдання 1. На основі даних показника Υ і фактора X знайти оцінки: коефіцієнта кореляції; параметрів лінії регресії Ϋ = а · Χ+b. Використовуючи критерій Фішера, з надійністю Ρ = 0,95 оцінити адекватність прийнятої економічної моделі статистичним даним. Алгоритм виконання У діапазон комірок (В4:С18) вводяться вхідні дані показників X та Y. Вводиться гіпотеза, що між фактором X та показником У існує лінійна стохастична залежність Y=а · Χ+b. Для знаходження добутку X*Y у комірку D4 вводиться формула =B4*C4 та розповсюджуємо формулу по стовпці (D4:D18). Щоб знайти значення X2в коміркуE4записуємо формулуB4^2і аналогічно копіюємо по стовпці Ев діапазоні (E4:E18). Для визначення сум стовпців в математичних таблицях Excelвикористовуємо кнопку автосумування на панелі інструментів Σ або вбудовану функцію СУММ(БЛОК). Оцінки параметрів а і b парної регресії обчислюються за формулами:
Середні значення Χ, Υ обчислюються в комірках D18, D19 з використанням вбудованої статистичної функції СРЗНАЧ (блок). Для обчислення значення yi(i =1,13) у комірку F4вводимо формулу axi + b з абсолютними посиланнями координат-параметрів a і відносним посиланням координати х1.Копіюємо її у діапазоні комірок (F4:F18). Для оцінки адекватності прийнятої економетричної моделі експериментальним даним використовуємо критерій Фішера. Для визначення розрахункового значення критерію Фішера, оцінки довірчої зони базисних даних та оцінки довірчого інтервалу прогнозу створюємо блок проміжних обчислень(G4:L18). Значення (yi-yp)2, (yi–уc)2, (хi – хc)2 обчислюємо відповідно в блоках (G4:G18), (H4:H18), (I4:I18). Значення Sобчислюється у комірціD24, з формулою: S=
Значення Dyобчислюється у діапазоні комірок (J4:J18)за формулою:
Для оцінки коефіцієнта кореляції будуємо блок (M4:M18). У комірку M4 вводиться формула для обчислення значення (хi-хc)*(yi–уc) та копіюється в блок (M5:M18). Сума блоку (M4:M18)обчислюється у комірці M19. Значення коефіцієнта кореляції обчислюється у комірці F24. Коефіцієнт еластичності для базисних значень та прогнозу обчислюється у блоці (N4:N18). Для наочного уявлення одержаних розрахунків будуємо графіки: фактичних даних (Y), лінії регресії для базисних даних та прогнозу (Ур), довірчу зону для базисних даних і прогнозу (Ymin ,Ymax), коефіцієнта еластичності (Kel).
Рис 1. Знаходження оцінки коефіцієнта кореляції, параметрів лінійної регресії.
Рис 2. Графік залежності ліній регресії

Рис 3. Формули в Excel для знаходження оцінки коефіцієнта кореляції, параметрів лінійної регресії. Висновок: Оскільки Fроз. >Fтаб , то з надійністю P=0,95 можна вважати, що прийнята математична модель адекватна експериментальним даним і на основі цієї моделі можна здійснювати економічний аналіз та знаходити значення прогнозу.