Застосування стандартних статистичних функцій Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

· лінійна;

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

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

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

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

· ін.

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

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

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

У набір стандартних статистичних функцій входить розрахунок 2-х регресійних моделей – лінійної і логарифмічної. Зовнішній вигляд цих моделей такої:

Y=a0+x1a1+x2a2+.+xnan – лінійна модель.

Y=a0*x^a1*x^a2*.*x^an - логарифмічна модель.

Для проведення розрахунків по лінійній моделі необхідно:

1. Створити таблицю по фактичних значеннях чинників.

2. У вибрану, на свій розсуд комірку, ввести формулу ЛИНЕЙН|(Y;X;L)

де:

Y – відомі значення результуючого чинника;

X – відомі значення впливаючих чинників;

F – 1, якщо розраховується модель з вільним коефіцієнтом а0, і 0 в іншому випадку;

L – 1, якщо необхідна додаткова статистична інформація (для довідки за додатковою статистичною інформацією див. довідку Excel по функції ЛИНЕЙН|).

3. Після отримання результату необхідно виділити блок, першим коміркою в якому є той комірка, де написана формула, і який відповідає за розмірами блоку повертаних функцією значень (див. довідку Excel по функції ЛИНЕЙН|), і натиснути комбінацію клавіш [ Ctrl-Shift-Enter ]. Виділений блок заповниться розрахованими значеннями коефіцієнтів моделі, і додатковою статистичною інформацією (якщо L=1).

Для побудови логарифмічної моделі слід поступати аналогічним чином, тільки в цьому випадку використовується функція ЛГРФПРИБЛ (див. довідку Excel).

Дата добавления: 2015-05-24