Регресія у Microsoft Excel

Пакет аналізу даних Microsoft Excel надає можливість будувати регресійні моделі, але тільки у випадку лінійної залежності результативної ознаки Y від факторної ознаки Х і тільки для незгрупованих вибіркових даних.

Для побудови лінійної регресійної моделі необхідно:

1) Викликати Сервис – Анализ данных – Регрессия – ОК. З’явиться вікно для надання вхідних даних (рис. 4.5).

2) У графі Входной интервал Y та Входной интервал Х вказати відповідні стовпці даних; у графі Выходной интервал вказати ту клітину, починаючи з якої будуть надаватися вихідні дані – параметри рівняння регресії та результати її статистичного аналізу.

Рис. Діалогове вікно функції Регрессия

Приклад і результати роботи функції Регрессия надано на рис. 4.6.

Рис. Результати регресійного аналізу

В таблиці (рис. 4.6) у графі Коэффициенты вказані значення параметрів моделі а та : - в графі Y-пересечение, а - в графі Переменная X1. Отже, побудована лінійна регресійна модель має вигляд:

.

Для перевірки статистичної значущості моделі надається значення F-статистики у графі F: F = 105,14.

Коефіцієнт детермінації моделі R² надається у графі R-квадрат, R²= 0,97.

Крім того, може бути надано: графік підбору – порівняльна діаграма, що містить емпіричну і теоретичну лінії регресії; таблиця залишків – різниць емпіричних і теоретичних значень Y (рис. 4.7).

Рис. Додаткові результати регресійного аналізу

Завдання для самостійного виконання

4.1. Відомі дані про обсяг виробництва сільськогосподарської продукції (грн.) на 1 особу АР Крим (табл. 4.11). Побудувати регресійну модель за даними таблиці, оцінити її статистичну значущість та адекватність.

Таблиця 4.11

Рік
Обсяг виробництва с/г продукції

4.2. Відомі дані про чисельність науково та науково-технічних працівників, що припадають на 1000 осіб (табл. 4.12). Побудувати регресійну модель за даними таблиці, оцінити її статистичну значущість та адекватність.

Таблиця 4.12

Рік
Чисельність науково та науково-технічних працівників	1,1	1,1	1,0	1,0	1,0	1,0	0,9

4.3. Відомі дані про інвестиції в основний капітал в розрахунку на 1 особу (табл. 4.13). Побудувати регресійну модель за даними таблиці, оцінити її статистичну значущість та адекватність.

Таблиця 4.13

Рік
Інвестиції в основний капітал	376,8	600,2	735,7	955,2	1376,2	1704,1	2375,6

4.4. Відомі дані про обсяг інноваційної продукції в розрахунку на 1 особу (табл. 4.14). Побудувати регресійну модель за даними таблиці, оцінити її статистичну значущість та адекватність.

Таблиця 4.14

Рік
Обсяг інноваційної продукції	38,4	139,0	264,5	172,3	313,1	469,9	282,2

4.5. Відомі дані про обсяг експорту товарів в розрахунку на 1 особу (табл. 4.15). Побудувати регресійну модель за даними таблиці, оцінити її статистичну значущість та адекватність.

Таблиця 4.15

Рік
Обсяг експорту товарів	84,6	107,3	109,2	158,1	137,1	178,1	201,7

4.6 – 4.15. Знайти рівняння регресії, що описує залежність Y від Х за даними кореляційної таблиць 4.16 – 4.17, оцінити її статистичну значущість та адекватність.

Таблиця 4.16