Засоби регресійно-кореляціішого аналізу в Excel

В Excel є статистичні функції (наприклад, ЛИНЕЙН, ЛГРФПРИБЛ, ТЕНДЕНЦИЯ, РОСТ, ДИСПР, ДИСП, СТАНДОТКЛОНП, СТАНД-ОТКЛОН, КОВАР, КОРРЕЛ та ін.) та інструменти з надбудови Пакет анализа (зокрема, РЕГРЕССИЯ, КОРРЕЛЯЦИЯ та ін.).

У діалоговому вікні зазначеної функції або інструменту потрібно:

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

• вибрати уточнюючі параметри;

• вказати кінцевий діапазон для запису результатів. Розглянемо призначення зазначених функцій.

• Функції ЛИНЕЙН, ЛГРФПРИБЛ повертають параметри відповідно лінійної та експоненціальної регресивної моделі. Наприклад, функція ЛИНЕЙН повертає в кінцевий діапазон значення таких параметрів регресії:

тп	mn-1	...	т2	m1	b
sen	sen-1	...	se2	se1	seb
r2	sev
F	df
SSreg	SSresid

Тут т_п — коефіцієнти рівняння регресії, а квадрат коефіцієнта кореляції r² відображає ступінь достовірності отриманої лінійної моделі.

• Функції ТЕНДЕНЦИЯ і РОСТ дають змогу прогнозувати значення в.в. Y, скориставшись лінійною або експоненціальною моделлю залежності між в.в. відповідно Y та X.

• Функції ДИСПР і ДИСП повертають відповідно дисперсію генеральної сукупності статистичного матеріалу та її точкову оцінку за вибіркою.

• Функції СТАНДОТКЛОНП і СТАНДОТКЛОН обчислюють відповідно стандартне (тобто середньоквадратичне) відхилення а генеральної сукупності статистичного матеріалу та його точкову оцінку за вибіркою.

• Функція КОВАР знаходить значення коваріації (тобто кореляційний момент) двох в.в. Коваріація характеризує як міру взаємозв'язку величин, так і їх розсіяння.

• Функція КОРРЕЛ повертає значення коефіцієнта кореляції двох в.в.

Варіанти завдань

Варіант 1

Нехай X — кількість застосованих на фірмі за рік раціональних пропозицій, a Y — річний прибуток фірми. У таблиці наведено значення в.в. У і X із статистичного ряду.

X	2
Y

1 За допомогою відповідних статистичних функцій розрахуйте в окремих комірках аркуша значення таких числових характеристик: s_y, s_x, K_xy, r_xy.

2. Перевірте в окремій комірці правильність розрахунку r _ху, використовуючи формулу r _ху=К_ху/(s_хs_у).

3.За отриманим значенням г сформулюйте висновок про ступінь взаємозв'язку в.в. Y i X.

4. Неістотно змінивши деякі з вихідних даних, дослідіть їх вплив на σ_x, σ_y, K_xy, r_xy.

1. Побудуйте точкову діаграму за вихідними даними, додайте лінію лінійного тренда і покажіть її рівняння на діаграмі.

2.Спрогнозуйте значення Y для X - 23.

Варіант 2

Нехай X — показник якості охоронної системи, що встановлюється на автомобілях певного класу, a Y — вартість автомобіля без урахування вартості охоронної системи. У таблиці наведено значення в.в. Y і Х із статистичного ряду.

X	2
Y

1.За допомогою статистичної функції КОРРЕЛ обчисліть в окремій комірці аркуша значення коефіцієнта кореляції r_xy

2.За отриманим значенням r_xyсформулюйте висновок про ступінь взаємозв'язку в.в. Y i X.

3.Неістотно змінивши деякі з вихідних даних, дослідіть їх вплив на коефіцієнт кореляції r_xy.

4.Побудуйте точкову діаграму, додайте лінію поліноміального трендаі покажіть її рівняння на діаграмі.

5.Спрогнозуйте значення Y для X - 18.

Варіант З

Нехай X — вартість приміщення офісу фірми без урахування вартості охоронної системи, a Y — вартість охоронної системи, яку планується встановити. У таблиці наведено значення в.в. Y і X із статистичного ряду.

X
Y

4.За допомогою статистичної функції КОРРЕЛ обчисліть в окремій комірці аркуша значення коефіцієнта кореляції r_xy.

5. За отриманим значенням r_xy сформулюйте висновок про ступінь взаємозв'язку в.в. Y i X.

6.Неістотно змінивши деякі з вихідних даних, дослідіть їх вплив на коефіцієнт кореляції r_xy.

7.Побудуйте точкову діаграму, додайте лінію логарифмічного трендаі покажіть її рівняння на діаграмі.

8.Спрогнозуйте значення Y для X - 155000.