Короткі теоретичні відомості. Ця функція використовує метод найменших квадратів, щоб вирахувати пряму лінію, яка найкращим чином апроксимує наявні дані
Функція ЛИНЕЙН
Ця функція використовує метод найменших квадратів, щоб вирахувати пряму лінію, яка найкращим чином апроксимує наявні дані. Функція повертає масив, який описує отриману пряму. Рівняння для прямої лінії має наступний вид:
y = m1x1 + m2x2 + ... + b
або
y = mx + b,
де y - функція незалежного значення x.
m - коефіцієнти, які відповідають кожній незалежній змінній x,
b - постійна.
Треба відмітити, що y, x і m можуть бути векторами.
Функція ЛИНЕЙН повертає масив {mn,mn-1,...,m1,b}. ЛИНЕЙН може також повертати додаткову регресійну статистику.
Синтаксис:
ЛИНЕЙН(известные_знач_y,известные_знач_x, конст, статистика)
Известные_знач_y- це множина значень y, які вже відомі для співвідношення y = mx + b.
Якщо масив известные_знач_y має один стовпчик, то кожний стовпчик масиву известные_знач_x інтерпретується як окрема змінна.
Якщо масив известные_знач_y має один рядок, то кожний рядок масиву известные_знач_x інтерпретується як окрема змінна.
Известные_знач_x - це необов’язкова множина значень x, які вже відомі для співвідношення y = mx + b.
Масив известные_знач_x може містити одну або кілька множин змінних. Якщо використовується тільки одна змінна, то известные_знач_y іизвестные_знач_x можуть бути масивами довільної форми, при умові, що вони мають однакову розмірність. Якщо використовується більше однієї змінної, то известные_знач_y повинні бути вектором (тобто інтервалом з висотою в один рядок або шириною в один стовпчик).
Якщо известные_знач_x опущені, то вважається, що це масив {1,2,3,...} такого ж розміру, як і известные_знач_y.
Конст - це логічне значення, яке вказує, чи потрібно, щоб константа b була рівною 0.
Якщо конст має значення ИСТИНА або опущене, то b обчислюється звичайним чином.
Якщо конст має значення ЛОЖЬ, то b вважається рівним 0 і значення m підбираються так, щоб відповідати співвідношенню y = mx.
Статистика - це логічне значення, яке вказує, чи потрібно повернути додаткову статистику по регресії.
Якщо статистика має значення ИСТИНА, то функція ЛИНЕЙН повертає додаткову регресійну статистику, так що масив, який повертається, буде мати вид {mn, mn-1,..., m1, b; sen, sen-1,..., se1, seb; r2, sey; F, df; ssreg, ssresid}.
Якщо статистика має значення ЛОЖЬ або опущена, то функція ЛИНЕЙН повертає тільки коефіцієнти m і постійну b.
Додаткова регресійна статистика:
Величина Опис
se1,se2,...,sen Стандартні значення похибок для коефіцієнтів m1,m2,...,mn.
seb Стандартне значення похибки для постійної b (seb = #Н/Д, якщо конст має значення ЛОЖЬ).
r2 Коефіцієнт детермінованості. Порівнюються фактичні значення y і значення, отримані з рівняння прямої; за результатами порівняння обчислюється коефіцієнт детермінованості, нормований від 0 до 1. Якщо він дорівнює 1, то має місце повна кореляція з моделлю - немає різниці між фактичним і оціночним значеннями y. В противному випадку, якщо коефіцієнт детермінованості дорівнює 0, то рівняння регресії не підходить для передбачення значень y. Проводячи регресійний аналіз, Microsoft Excel обчислює для кожної точки квадрат різниці між прогнозованим значенням y і фактичним значенням y. Сума цих квадратів різниць називається остаточною сумою квадратів. Потім Microsoft Excel підраховує суму квадратів різниць між фактичними значеннями y і середнім значенням y, яка називається загальною сумою квадратів (регресійна сума квадратів + остаточна сума квадратів). Чим менше остаточна сума квадратів у порівнянні з загальною сумою квадратів, тим більше значення коефіцієнта детермінованості r2, який показує, наскільки добре рівняння, отримане з допомогою регресійного аналізу пояснює взаємозв’язки між змінними.
sey Стандартна похибка для оцінки y.
F F-статистика, або F-спостережуване значення. F-статистика використовується для визначення того, чи є спостережувана взаємозалежність між залежною і незалежною змінними випадковою чи ні.
df Степені свободи. Степені свободи корисні для знаходження F-критичних значень в статистичній таблиці. Для визначення рівня надійності моделі потрібно порівняти значення в таблиці з F-статистикою, яка повертається функцією ЛИНЕЙН.
ssreg Регресійна сума квадратів.
ssresid Остаточна сума квадратів.
Додаткова регресійна статистика повертається в наступному порядку:
| se1 | se2 | ... | sen | seb |
| r2 | sey | |||
| F | df | |||
| ssreg | ssresid |
Точність апроксимації з допомогою прямої, отриманої з допомогою функції ЛИНЕЙН залежить від ступеня розкиду даних. Чим ближче дані до прямої, тим більш точною є модель, яка використовується функцією ЛИНЕЙН.
Функція ЛГРФПРИБЛ
Обчислює експоненційну криву, яка апроксимує дані та повертає масив, що описує цю криву. Рівняння кривої наступне:
y = (b*(m1^x1)*(m2^x2)*_)
або
y = b*m^x ,
де y - функція незалежних значень x.
m - основа для піднесення до степеня x,
b - постійна.
Зауважимо, що y, x та m можуть бути векторами. Функція ЛГРФПРИБЛ повертає масив {mn; mn-1; ...; m1; b}.
Синтаксис функції ЛГРФПРИБЛ аналогічний синтаксису функції ЛИНЕЙН. Величини додаткової регресійної статистики, яку повертає функція ЛГРФПРИБЛ аналогічні відповідним величинам додаткової регресійної статистики, яку повертає функція ЛИНЕЙН. Але додаткова статистика, яку повертає функція ЛГРФПРИБЛ, грунтується на наступній лінійній моделі:
ln y = x1 ln m1 + ... + xn ln mn + ln b
Це слід пам’ятати при оцінці додаткової статистики, особливо значень sei та seb, які слід порівнювати з ln mi та ln b, а не з mi та b.
Функція ТЕНДЕНЦИЯ
Повертає значення у відповідності з лінійним трендом. Апроксимує прямою лінією (за методом найменших квадратів) масиви известные_знач_y і известные_знач_x. Повертає значення y, які відповідають цій прямій для заданого масиву новые_знач_x.
Синтаксис:
ТЕНДЕНЦИЯ(известные_знач_y;известные_знач_x;
новые_знач_x; конст)
Параметри функції ТЕНДЕНЦИЯ известные_знач_y,известные_знач_x та конст аналогічні першим трьом параметрам функції ЛИНЕЙН.
Новые_знач_x - це нові значення x, для яких функції ТЕНДЕНЦИЯ повертає відповідні значення y.
Новые_знач_x повинні містити стовпчик (або рядок) для кожної незалежної змінної, так само, як известные_знач_x. Таким чином, якщо известные_знач_y має один стовпчик, то известные_знач_x та новые_знач_x повинні мати однакову кількість стовпчиків. Якщо известные_знач_y має один рядок, то известные_знач_x та новые_знач_x повинні мати однакову кількість рядків.
Якщо новые_знач_x опущені, то вважається, що вони співпадають з известные_знач_x.
Якщо опущені обидва масива известные_знач_x та новые_знач_x, то вважається, що це масив {1;2;3;...} такого ж розміру, що й известные_знач_y.
Функція РОСТ
Апроксимує експоненціальною кривою известные_знач_y та известные_знач_x і повертає відповідні до цієї кривої значення для значень x, які визначаються аргументом новые_знач_x.
Синтаксис функції аналогічний синтаксису функції ТЕНДЕНЦИЯ: РОСТ(известные_знач_y; известные_знач_x; новые_знач_x; конст).
Функції апроксимації ЛИНЕЙН та ЛГРФПРИБЛ можуть обчислити пряму або експоненційну криву, які найкращим чином описують Ваші дані. Але вирішити, який з двох результатів найбільшою мірою підходить до Ваших даних, Ви повинні самі.
Для перевірки відповідності отриманих залежностей можна використати функцію ТЕНДЕНЦИЯ для прямої або функцію РОСТ для експоненційної кривоі. Ці функції, якщо не задавати аргумент новые_знач_x, повертають масив обчислених значень y для фактичних значень x у відповідності з прямою або кривою.
УВАГА! Формули, які повертають масиви, повинні бути введені як формули масивів, тобто:
1) перед вводом формули треба виділити необхідний для виводу результату діапазон клітинок;
2) ввід формули повинен закінчуватись натисканням комбінації клавіш CTRL-SHIFT-ENTER.
Приклад. Фірма розглядає можливість купівлі невеликої будівлі під офіс серед групи будівель в традиційному діловому районі. Комерційний агент фірми може використати множинний регресійний аналіз для оцінки ціни необхідної для фірми будівлі під офіс на основі наступних змінних.
Змінна Зміст змінної
y Ціна будівлі під офіс
x1 Загальна площа в квадратних метрах
x2 Кількість офісів
x3 Кількість входів
x4 Час попередньої експлуатації будівлі в роках
Припускається, що існує лінійна залежність між кожною незалежною змінною (x1, x2, x3 та x4) і залежною змінною (y), тобто ціною будівлі під офіс в даному районі.
Агент довільно вибирає 11 будівель з наявних 1230 і отримує наступні дані:
Таблиця 1.5 - Вибірка параметрів будівель
| Будівля | X1 | X2 | X3 | X4 | Y |
При вводі в якості масиву функція ЛИНЕЙН повертає наступні результати.
| -220.263 | 2378.639 | 12556.42 | 28.0664 | 51108.91 | |
| 17.41382 | 630.7686 | 509.564 | 6.405821 | 14494.56 | |
| 0.995008 | 1207.569 | ||||
| 298.9901 | |||||
| 1.74E+09 |
Рівняння множинної регресії y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b тепер може бути отримано з першого рядка результатів:
y = 28,07*x1 + 12 556*x2 + 2379*x3+ 220,26*x4 + 51109
Тепер агент може визначити оціночну вартість будівлі під офіс в цьому ж районі, яке має площу 2500 квадратних метра, три офіси, два входи, будівлі 25 років, використовуючи наступне рівняння:
y = 28,07*2500 + 12556*3 + 2379*2 - 220,26*25 + 51109 = 158 195 грн.
Використання F і R2 статистик
Коефіцієнт детермінованості r2 рівний 0,995, що вказує на сильну залежність між незалежними змінними і продажною ціною. Можна використати F-статистику, щоб визначити, чи є цей результат (з таким високим значенням r2) випадковим, чи ні.
Припустимо, що насправді нема взаємозв’язку між змінними, а просто було вибрано рідкісні 11 зразків будівель, для яких статистичний аналіз вивів сильну взаємозалежність. Позначимо через a ймовірність помилкового висновку про те, що є сильна взаємозалежність.
Якщо F-спостережуване більше, ніж F-критичне, то взаємозв’язок між змінними є. F-критичне можна отримати з таблиці F-критичних значень в довіднику з математичної статистики. Для того, щоб знайти це значення, використовуючи односторонній тест, візьмемо a = 0,05, визначимо число ступенів свободи (які позначають звично через v1 і v2):
v1 = k = 4 і v2 = n - (k + 1) = 11 - (4 + 1) = 6, де k - число змінних,
n - число точок даних. З таблиці довідника F-критичне дорівнює 4,53.
Спостережуване F-значення дорівнює 298,9901, що значно більше, ніж F-критичне значення 4,53. Отже, отримане регресійне рівняння придатне для передбачення оціночної вартості будівель в даному районі.
Обчислення t-статистики
Визначимо, чи кожний коефіцієнт нахилу для оцінки вартості будівлі під офіс суттєвий. Наприклад, для перевірки того, що термін попередньої експлуатації будівлі має статистичну значимість, розділимо -220,263 (коефіцієнт нахилу для терміну попередньої експлуатації будівлі) на 17,41382 (оцінка стандартної похибки для коефіцієнта терміну попередньої експлуатації). Нижче наводиться спостережуване t-значення:
t = m4 / se4 = -220,263 / 17,414 =-12,6488
З таблиці довідника з математичної статистики t-критичне з 6 степенями свободи і a = 0,05 дорівнює 1,94 . Оскільки абсолютна величина t, рівна 12,65, більша, ніж 1,94, термін попередньої експлуатації є важливою змінною для оцінки вартості будівлі під офіс. Аналогічним чином можна протестувати всі інші змінні на статистичну значимість. Спостережувані t-значення для кожної з незалежних змінних наступні:
Змінна Спостережуване значення t
Загальна площа 4,38
Кількість офісів 24,6
Кількість входів 3,77
Термін попередньої експлуатації 12,65
Всі ці значення мають абсолютну величину більшу, ніж 1,94. Отже, всі змінні, використані в рівнянні регресії, значимі для передбачення оціночної вартості будівлі під офіс в даному районі.
Питання для самоконтролю
1 Призначення регресійного аналізу.
2 Синтаксис функцій ЛИНЕЙН, ЛГРФПРИБЛ, ТЕНДЕНЦИЯ, РОСТ.
3 Яким чином вводяться формули, що повертають масив?
4 Економічний зміст додаткової регресійної статистики функції ЛИНЕЙН.
5 Які способи для прогнозування економічних моделей Ви знаєте?
Лабораторна робота № 2
Аналіз індивідуального ринку засобами Excel 2000
Використання статистичних функцій Excel 2000
1 Мета роботи: виконати Аналіз індивідуального ринку засобами MS-Excel; навчитися використовувати статистичні функцій MS-Excel та застосовувати MS-Excel для статистичних розрахунків
2Умова задачі:Фірма виходить на ринок з новим товаром. Вона прогнозує рівноважну ціну на ринку і максимально можливий обсяг продаж.
Значення величин попиту на товар фірми і величина пропозиції на нього визначені емпірично за допомогою маркетингових досліджень і занесені в таблиці 1 та 2 (де Рі-ціна в у.о. за одиницю товару; Qi- усереднене значення кількість товару, проданого за певний період по ціні Рі.)
Завдання: 1 Побудувати графіки попиту і пропозиції, визначити графічно ціну рівноваги і рівноважний обсяг попиту і пропозиції, позначити на графіку надлишок та дефіцит товару;
2 Побудувати залежності величини попиту і пропозиції від ціни товару за методом найменших квадратів, вважаючи залежність лінійною (використати функцію ЛИНЕЙН()), обчислити теоретичні значення у (підставити значення х у отриману формулу);
3 Побудувати залежності величини попиту і пропозиції від ціни товару, вважаючи залежність експоненційною (використати функцію ЛГРФПРИБЛ()), обчислити теоретичні значення у (підставити значення х у отриману формулу);
4 Зробити висновок про адекватність лінійної та експоненційної моделі, використовуючи коефіцієнт кореляції, на графіку побудувати лінії тренда, вивести рівняння на діаграмі;
5 За допомогою інструменту ПОДБОР ПАРАМЕТРА визначити рівноважну ціну, а також рівноважний обсяг попиту і пропозиції, вважаючи залежність лінійною.
6 Використавши функцію ТЕНДЕНЦИЯ() спрогнозувати значення попиту і пропозиції при нових значеннях ціни для лінійної залежності та функцію РОСТ() для експоненційної залежності (таблиця 3);
7 За допомогою статистичних функцій EXCEL визначити медіану сукупностей значень попиту і пропозиції (МЕДИАНА()), середнє значення (СРЗНАЧ()), максимальне (МАКС()) та мінімальне значення (МІН()) для обох сукупностей, розмах варіації, коефіцієнт осциляції, визначити дисперсію (ДИСП()), квадратичне відхилення (КВАДРОТКЛ()) та середньо квадратичне відхилення (СТАНДОТКЛОП()); визначити ексцес даних (ЭКСЦЕСС()), визначити коефіцієнт варіації для кожної з сукупностей, визначити за допомогою коефіцієнта кореляції чи існує взаємний вплив між між сукупностями величин попиту і пропозиції (КОРРЕЛ()), визначити критерій Пірсона, що є степенем лінійної залежності між двома множинами даних (ПИРСОН()), обчислити коефіцієнт детермінації, зробити висновок про щільність зв’язку між величинами, перевірити силу зв’язку- критерій Фішера та критерій Стьюдента.
8 Розрахувати коефіцієнти еластичності попиту і знайти ціну, при якій попит є унітарним. При цьому, доцільно використати логічну функцію ЕСЛИ() для вибору точки, для якої попит є унітарним. На якому відрізку ціни попит буде еластичним? Яка ціна забезпечить для фірми оптимальний обсяг продаж з отриманням максимальної виручки від реалізації?
9 Докажіть, що вибрана вами ціна забезпечує максимальну виручку (за формулою TR=P*Qd розраховуємо величину доходу фірми від продажу різних обсягів продукції за різними цінами). Проілюструйте графічно зв’язок між зміною еластичності попиту і зміною ціни.
Приклад виконання лабораторної роботи представлено на рисунку2.1, 2.2, 2.3.