Розрахунок багатофакторних моделей з використанням Excel

Статистичний аналіз даних

Microsoft Excel має широкий набір способів для статистичного аналізу даних. Для спрощеного аналізу даних можна використовувати вбудовані функції з категорії Статистичні. Окрім цих функцій. Excel має спеціальний пакет аналізу статистичних даних.

Для підключення пакету аналізу необхідно виконати команду Сервіс > Настройка > встановити прапорець Пакет аналізу.Пакет аналізу можна використовувати для побудови гістограм, ранжирування (угрупування) даних, для отримання випадкових і періодичних вибірок безлічі даних для проведення дисперсійного, регресійного, кореляційного аналізу даних, обчислення основних статистичних характеристик для вибірки генерування випадкових чисел з різноманітним розподілом і ін.

Для запуску пакету аналізу необхідно виконати команду Сервіс > Аналіз даних.Після цього на екрані з'явиться вікно діалогу Аналіз данихіз списком 19 інструменту аналізів. Після вибору потрібного інструменту необхідно натиснути кнопку ОК.

Серед інструментів статистичного аналізу даних є дисперсійний аналіз, кореляційний аналіз, описова статистика, експоненціальне згладжування, гістограма, регресія, вибірка і ін.

Інструмент Описова статистика

Інструмент Описова статистика дозволяє для заданого діапазону значень обчислити наступн статистичні характеристики:

середнє значення =СРЗНАЧ (число1; число2...)

Стандартна погрішність по вибірці - =СТАНДОТКЛОН (число 1; число2) =

міра того, наскільки широко розкидані

точки даних відносно їх середнього;

Медіана - число, яке є =МЕДИАНА (число1; число2)

серединою безлічі чисел

(1;2;3;4;5)=3

(1;2;3;4;5;6)=3,5

Мода - повертає найчастіше =МОДА (число1; число2...)

значення, що зустрічаються, в масиві

(1,2,4, 4, 5) = 4

Стандартне відхилення по генеральной - СТАНДОТКЛОН (число1; число2;...)

сукупності

Дисперсія вибірки =ДИСП (число1; число2...)

Коефіцієнт ексцесу - характеризує =ЭКСЦЕСС (число 1; число2...)

відносну остроконечность або

згладжена розподілу по порівнянню

з|із| нормальним розподілом;

Коефіцієнт асиметрії повертає =СКОС (число 1; число2...)

асиметрію розподілив. Асиметрія

характеризує ступінь асиметрії

розподіли відносно його середнього

"+" указує на відхилення

розповсюдження убік позитивних

значень; "-" - указує на відхилення

розповсюдження убік негативних

значень;

Інтервал (розмах)=МАХ (число 1; число2...) - MIN (число 1; число2...)

Максимальне значення =МАХ (число1; число2...)

Мінімальне значення =MIN (число 1; число 2...)

Сума значень =СУММ (число 1; число 2...)

Кількість значень =СЧЕТ (знач.1; знач.2)

Для виклику інструменту Описова статистика потрібне:

· Сервіс > Аналіз даних ;

· у списку Інструментивибрати пункт Описова статистика;

· у відкритому вікні Описова статистиказадати діапазон вхідних даних; спосіб розміщення рядів даних; діапазон вхідних даних;

· встановити опції Підсумкова статистика, якщо потрібен повний список характеристик.

Інструмент Гістограма

Інструмент Гістограма призначений для побудови діаграми, в якій для заданої безлічі значень визначається кількість значень (частота), яка потрапляють в задані інтервали.

Інструмент Гістограма потребує завдання діапазону комірок, які містять числові дані, діапазон комірок, який містить значення інтервалу і адреси верхніх і лівого комірки початкового діапазону, в якому виводяться значення частот.

Виклик інструменту Гістограма:

· Сервіс >Анализ даних ;

· виділити діапазон вхідних даних;

· виділити діапазон інтервалів;

· вказати адресу верхнього лівого комірки початкового діапазону частот;

· вказати потрібні параметри виведення результатів.

Розрахунок багатофакторних моделей з використанням Excel

Багатофакторний аналіз полягає в знаходженні залежності одного чинника (результуючого) від декількох інших. Загальний вид багатофакторної функції є:

Y=F(x1, X2, ., Xn),

де F– може бути будь-якої форми (лінійна, логарифмічна, параболічна, гіперболічна, експоненціальна і ін.).

Fможе бути будь-якої форми:

· лінійна;

· параболічна;

· гіперболічна;

· логарифмическая4

· експоненціальна;

· ін.

При розрахунку моделей необхідно підібрати відповідні коефіцієнти так, щоб при підстановці реальних значень змінних Хв рівняння, розрахункові значення Yбули приблизно рівні реальним (або якомога ближче до ним).

Існує стандартний метод розрахунку коефіцієнтів регресійних моделей, який одержав назву методу найменших квадратів. Він полягає в тому, щоб мінімізувати відхилення квадратів відхилень розрахункових значень Y від реальних. Метод детально описаний у відповідній літературі.

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