cgiirbis_64 (6)

172

Перш ніж звернутися до мастера функцій, потрібно виокремити область, де міститиметься результат множення (Gl:G4) (див. рис. 1). Ця область згідно з правилом множення матриць матиме стільки ж рядків, скільки й перша вихідна (у даному прикладі — 4), і стільки стовпців, скільки друга (у прикладі — 1).

Далі в розділі мастер функцій вибираємо функцію МУМНОЖ (рис. 2) із категорії ВСІ і вказуємо ліву верхню та праву, нижню клітинки обох матриць, що перемножуються (рис. 3).

Рис. 2. Крок 1 Мастер функцій

172

вибіркою з генеральної сукупності. Якщо дані являють собою більшу сукупність, потрібно використовувати функцію ДИСПР.

ДОВЕРИТ (альфа; станд_відхил; розмір). Функція обчислює довірчий інтервал для середнього генеральної сукупності. Довірчий інтервал — окіл середнього вибірки (цей інтервал містить значення середнього вибірки, що рівновіддалене від його кінців). Наприклад, якщо товар був замовлений поштою, то з певним рівнем надійності можна визначити найбільш ранню та найбільш пізню дати прибуття товару.

«Альфа» — це рівень значущості, застосовуваний для обчислення рівня надійності. Він дорівнює 100 (1– ). «Альфа», що дорівнює 0.05, означає 95%-й рівень надійності.

«Станд_відхил» — це стандартне відхилення генеральної сукупності для інтервалу даних (передбачається відомим).

«Розмір» — це розмір вибірки.

КВАДРОТКЛ (число 1; число 2;...). Сума квадратів відхилень точок даних від їх середнього. Кількість аргументів має не перевищувати 30. Можна використовувати масив чи посилання на масив замість аргументів, що відокремлені крапкою з комою.

КВПИРСОН (відомі_значення_у; відомі_значення_х;). Обчислює квадрат коефіцієнта кореляції Пірсона для точок даних в аргументах «відомі_значення_х» та «відомі_значення_у». Значення г-квадрат можнаінтерпретуватияквідношеннядисперсіїудодисперсіїх.

«Відомі_значення_у» — це масив чи інтервал точок даних у. Те саме — для х.

КОВАР (масив 1; масив 2). Обчислює коваріацію (середній добуток відхилень для кожної пари точок даних).

КОРРЕЛ (масив 1; масив 2). Коефіцієнт кореляції між інтервалами клітинок «масив 1» та «масив 2». Застосовується для визначення щільності лінійного зв’язку між двома показниками.

ЛИНЕЙН (відомі_значення_у; відомі_значення_х; конст; статистика). Ця функція застосовує метод найменших квадратів, щоб визначити оцінки параметрів а лінійної регресії:

Y a€0 a€1x1 a€2 x2 ... a€k xk ,

або

€ ,

Y XA

де значення у (залежні) є функцією значення х (незалежного); А— вектор значень кутового коефіцієнта підсумовуючої прямої,

яка найкраще апроксимує наявні дані; € — абсциса точки пере-

R0

174

тину прямої з віссю Y. Функція може додатково обчислювати регресійну статистику (рис. 5).

«Відомі_значення_у» — множина значень у. Якщо масив у має один стовпець, то кожний стовпець масиву «відомі_значення_х» інтерпретуються як окрема змінна. Якщо масив «відомі_значення_у» має один рядок, то кожний рядок «відомих_значень_х» інтерпретується як окрема змінна.

«Відомі_значення_х» — множина значень х, що враховує або одну (парна регресія), або кілька змінних (множинна регресія). Якщо «відомі_значення_х» пропустили, то вважається, що це масив {1; 2; 3;...} такого самого розміру, як n «відомих_значень_у».

«Конст» — логічне значення. Якщо «конст» має значення «ложь», то а0 беруть таким, що дорівнює нулю: значення а добирають так, щоб виконувалася рівність Y = XА (модель без вільного члена). Якщо «конст» має значення «истина», то а0 обчислюється традиційно (модель з вільним членом).

«Статистика» — логічне значення, яке вказує, чи потрібно обчислювати додаткову статистику за регресією. Якщо «статистика» має значення «истина», то функція ЛИНЕЙН обчислює додаткову регресійну статистику у вигляді масиву (див. рис. 5).

a€0 — оцінка вільного члена регресії;

sa€j — стандартна похибка оцінки параметра;

R2 — коефіцієнт детермінації;

u — стандартна похибка залишків; F — F-критерій.

175

Ступінь свободи дорівнює (n – m), де n — кількість спостережень, m — кількість змінних у моделі; це значення необхідне для визначення табличного значення F-критерію.

Приклад

Розглянемо побудову економетричної моделі, яку називають виробничою функцією. Нехай модель специфікована у формі лінійної залежності між залежною змінною у і незалежними змінними х1 та х2:

Y — випуск продукції;

Х1 — затрати трудових ресурсів; Х2 — витрати виробничих фондів. Кількість спостережень до-

рівнює 12.

Занесемо вихідні дані в ПК і поруч на екрані виокремимо область для надходження результатів розрахунків (рис. 6).

Рис. 6. Крок 2 Мастера функцій для ЛИНЕЙН

176

F-статистика може підтвердити цей висновок або спростувати його на рівні значущості і для числа ступенів свободи (n – m) та m–1, де т — кількість змінних моделі. Якщо F розрахункове, яке дорівнює 134.508, більше за F критичне, то R2 і побудована модель статистично значущі з імовірністю (1 – а) · 100%. F критичне можна дістати з таблиці F-критичних значень. Задамо а = 0.05; ступені свободи дорівнюють n – m = 9, m – 1 = 2. Згідно з табли- цею-довідником F-критичне дорівнює 4.26. Отже, знайдене регресійне рівняння достовірне і може використовуватись для дослідження випуску продукції.

Застосування t-статистики

Ця статистика дає змогу визначати значущість кожної окремої оцінки параметра моделі.

Якщо t розрахункове для параметра a€j більше за t критичне,

при рівні значущості а та ступені свободи n – m, то параметр аj відмінний від нуля і змінна Хj має істотний вплив на залежну змінну Y.

Щоб дістати розрахункові значення t, потрібно цифри першого рядка результатів поділити на відповідні значення другого рядка результатів:

t j a€€j ,

a j

або

ta€0 = 9.814 / 0.401 = 24.47; ta€1 = 0.432 / 0.089 = 4.85;

ta€2 = 0.346 / 0.077 = 5.14.

Значення t критичне знайдемо з таблиці t-статистик для а = 0.05 та n – m = 9 ступенів свободи, t критичне для двостороннього тесту дорівнює 2.262, що менше від обчислених значень. Отже, усі параметри моделі статистично значущі з імовірністю

0,95.

Застосування сум квадратів відхилень

Ці статистики застосовуються в дисперсійному аналізі.

178

Щоб дістати загальну суму квадратів відхилень залежних змінних від їх середніх значень, потрібно додати числа останнього рядка результатів:

Поділивши суми квадратів відхилень на відповідні ступені свободи, дістанемо значення дисперсії:

дисперсія регресії – регресійна сума квадратів / m – 1 = 25.107 /2 = 12.55;

дисперсія залишків – залишкова сума квадратів / n – m = 0.559/9 = 0.062;

дисперсія залежної змінної Y – загальна сума квадратів / n–1 = 25.666 / 11 = 2,33.

НОРМАЛИЗАЦИЯ (х; середні; стандартне_відхилення). Функція обчислює нормалізоване значення для розподілу, що характеризується середнім та стандартним відхиленнями X- значення, що нормалізується. Для нього задаються середнє арифметичне та стандартне відхилення розподілу. У дужках мають бути числа чи назви, масиви чи посилання, що складається з чисел. EXCEL перевіряє всі числа, які є масивами чи посиланнями. Якщо масив містить порожні клітинки, текстові чи логічні значення, то такі значення ігноруються, але клітинки, що містять нульові значення, враховуються.

ПРЕДСКАЗ (х; відомі_значення_Y; відомі_значення_Х). Розраховує значення функції в точці х, що прогнозується на основі лінійної регресії для масивів відомих значень Х та Y або інтервалів значень. Y—масив залежної змінної. «Відомі_значення_Х» — масив незалежних змінних.

Якщо «відомі_значення_Y» та «відомі_значення_Х» порожні чи містять різну кількість точок даних, то функція ПРЕДСКАЗ повертає значення помилки.

РОСТ (відомі_значення_Y; відомі_значення_Х; нові_значення_х; конст). Функція апроксимує експериментальну криву «відомі_значення_Y» та «відомі_значення_Х» і визначає відповідні цій кривій значення для чисел Y, що визначаються «новими_значеннями_Х».

«Відомі_значення_Y» — це множина значень у, що визначаються відношенням

179