Головна

Кореляційно-регресійний аналіз засобами MS Excel

  1. Microsoft Excel
  2. PIMS - аналіз
  3. SPACE- аналіз
  4. SWOT - аналіз
  5. Аналіз витрат на операційну діяльність підприємства.
  6. Аналіз економічного зростання підприємства на основі концепції вартості активів
  7. Аналіз ефективності збутового каналу та критерії його вибору. Основні етапи формування каналів розподілу

Приклад використання кореляційно-регресійного аналізу для вивчення взаємозв'язку між реалізованою продукцією (y) та вартістю основних виробничих засобів (x) за даними табл. 8.8. Вихідні значення факторного і результативного показників розміщено в комірках В2:С11.

Для виявлення сили взаємозв'язку в MS Excel обчислюється парний коефіцієнт лінійної кореляції за допомогою спеціальної функції КОРРЕЛ. Для цього необхідно табличний курсор встановити у вільну комірку (В13). На панелі інструментів натиснути кнопку Вставка функции (fx). У діалоговому вікні Мастер функцииобрати категорію Статистические і функцію КОРРЕЛ, після чого натиснути кнопку Ок:

Вводимо діапазон даних «Вартість основних виробничих фондів» в поле Массив 1 (В2:В11). В поле Массив 2 вводимоь діапазон даних «Випуск продукції» (С2:С11):

У комірці А13 з'явиться значення коефіцієнта кореляції - 0,854 (Рис. 8.6).

Рис. 8.6. Результати розрахунку сили зв'язку між ознаками

Значення коефіцієнта кореляції більше 0,85. Значить, можна говорити про те, що на протязі періоду спостереження була висока ступінь прямого лінійного взаємозв'язку між вартістю основних виробничих фондів і випуском продукції.

Разом з кореляційним аналізом ще одним інструментом вивчення стохастичних залежностей є регресійний аналіз.

Для реалізації процедури регресія обираємо у меню «Сервис» пакет «Анализ данных» та інструмент аналізу «Регрессия» (Рис. 8.7).

Рис. 8.7. Реалізації процедури регресія

У першому полі «Входной интервал Y» діалогового вікна введемо інтервал значень результативного показника (С2:С11), у друге - «Входной интервал Х» - інтервал значень факторного показника (В2:В11). За бажанням результати можна вивести на цьому ж листі шляхом відмітки «Параметры вывода» «Выходной интервал» і встановленням курсору в третьому полі та активуванням комірки, з якої почнеться виведення результатів (наприклад, А15). Можна розширити перелік виведених показників та додати графіки, поставивши відмітки у відповідних додаткових полях, наприклад, «График подбора»:

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

На рис. 8.8. коефіцієнти регресії (комірки В31, В32) показують, що в цьому випадку рівняння регресії має вигляд:

= 0,167 + 0,421х.

Лінійний коефіцієнт кореляції (комірка В18) свідчить про те, що між випуском продукції та вартістю основних виробничих фондів існує сильний прямий зв'язок (r=0,854), а коефіцієнт детермінації (комірка В19) показує, що варіація y зумовлюється варіацією x на 72,9%.

Покажемо взаємозв'язок між x та y у вигляді лінійного графіка, для чого використаємо «Мастер диаграмм - Тип - Точечная».

Рис. 8.9. Установлення взаємозв'язку графічним методом

Для того, щоб на графіку додати лінію регресії, необхідно побудувати точковий графік, поставити курсор на будь-яку точку та натиснути праву кнопку миші. У меню, яке після цього з'являється на екрані, обрати команду «Добавить линию тренда» (рис.8.10).

Рис. 8.10.

У діалоговому вікні вибрити тип «Линейная», зайти в підменю «Параметры» та відмітити команду «показывать уравнение на диаграмме». У результаті на графіку буде зображено кореляційне поле, лінію регресії та виведено лінійне рівняння регресії.

Рис. 8.11. Графічне зображення результатів регресійного аналізу

Для виведення на графіку значення коефіцієнта детермінації, необхідно зробити відмітку у полі «Поместить на диаграмму величину достоверности аппроксимации (R^2)».

Приклад використання багатофакторного кореляційно-регресійного аналізу.

Як приклад проведемо оцінку взаємозв'язку між статутним капіталом, кількістю і ціною акцій операторів зв'язку, що виставляються на аукціон за даними табл. 8.4.

Для побудови моделі та одержання показників, які дають змогу оцінити зв'язок і якість моделі, використаємо пакет «Анализ данных» - Инструмент анализа - «Регрессия».

Рис. 8.12. Вихідні дані

Після появи на екрані діалогового вікна у перше поле «Входной интервал Y» необхідно ввести діапазон значень результативного показника у (комірки В2:В11), а у друге поле «Входной интервал Х» - діапазон значень факторних показників - х1, х2 (комірки С2:D11). Для виведення результатів розрахунків на цьому ж робочому листі із заданим рівнем надійності (95%) у полі «Параметры вывода» необхідно поставити відмітку на «Выходной интервал» і активувати комірку, з якої почнеться виведення результатів, наприклад, A13.

Для отримання графічної інтерпретації фактичних і розрахункових даних поставимо відмітку в полі «График подбора», у результаті буде побудовано два графіка для кожного факторного показника.

Результати розрахунків коефіцієнтів регресії (комірки В29:В31) показують, що багатофакторна лінійна регресійна модель має вигляд:

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

Коефіцієнт множинної детермінації (комірка В17) становить 0,53, отже результативний показник залежить від двох факторних на 53%. Коефіцієнт множинної кореляції 0,728 (комірка В16) свідчить про сильний (тісний) зв'язок між показниками.

Перевірка істотності зв'язку за F-критерієм: 3,96>4,103 при a=0,05 і числі ступенів вільності k1=2, k2=10. Отже, зв'язок між показниками, які включено у двофакторну регресійну модель, є істотним (невипадковим).

Питання і завдання для самоконтролю

8.1. Що ви розумієте під:

а) функціональними зв'язками;

б) стохастичними зв'язками;

в) кореляційними зв'язками?

8.2. Які зв'язки бувають за:

а) характером дії;

б) направленістю;

в) аналітичним вираженням;

г) кількістю факторних ознак?

8.3. Назвати методи, які використовують для вивчення функціональних зв'язків? У чому полягає їх сутність?

8.4. Які методи використовують для виявлення можливої наявності зв'язку між факторною і результативною ознаками?

8.5. У чому полягає сутність методу порівняння паралельних рядів?

8.6. Які показники є ступенем тісноти зв'язку між двома ознаками?

8.7. Які показники використовують для виміру ступеня тісноти зв'язку між якісними ознаками?

8.8. У чому полягає значення рівняння регресії?

8.9. Назвати види зображення лінії регресії та охарактеризуйте їх.

8.10. Назвати основні етапи побудови регресійної моделі.

8.11. В яких випадках для вимірювання зв'язку між ознаками використовують:

а) коефіцієнт Фехнера;

б) коефіцієнт кореляції рангів;

в) лінійний коефіцієнт кореляції;

г) теоретичне кореляційне відношення;

д) індекс кореляції?

8.12. Що характеризує емпіричний коефіцієнт детермінації?

8.13. Якщо R2 = 1, то чому будуть дорівнювати міжгрупова і загальна дисперсії?

8.14. За яких умов зв'язок між результативною і факторною ознаками вважається:

а) суттєвим;

б) несуттєвим?

8.15. Який показник застосовується для оцінки адекватності регресійної моделі і як він розраховується? Про що свідчить цей показник?

Література [3; 5; 7; 8; 15-24; 26].

 



  40   41   42   43   44   45   46   47   48   49   50   51   52   53   54   55   Наступна

Поняття про вибіркове спостереження та його основні завдання | Основні умови наукової організації вибіркового спостереження | Методи і способи відбору одиниць у вибіркову сукупність | Чисельності для різних видів вибірок | Задачі для самостійного розв'язання | Зв'язки суспільних явищ і завдання їх статистичного вивчення | Статистичні методи вивчення взаємозв'язків | Кореляційний і регресійний методи аналізу зв'язку | Нелінійні залежності | Багатофакторний кореляційно-регресійний аналіз |

© 2016-2022  um.co.ua - учбові матеріали та реферати